SQL2000 中的 BCP 输出错误：SQLState = 37000，NativeError = 4060
我创建了一个过程来获取我的机器上本地数据库中的所有用户表。 我希望能够使用 BCP 和 SQL 创建所有表的平面文件。 它是 SQL 2000 中的虚拟数据库，通过 Windows 身份验证进行连接。 我已经在 WinXP SP2 中设置了环境路径变量。 我已经创建了新用户来访问数据库，关闭了防火墙，使用可信连接。 我已经尝试了几十个论坛，但没有运气。
在 dos 命令提示符下我得到同样的错误。
SQL状态 = 37000，本机错误 = 4060 错误 = [Microsoft][ODBC SQL Server 驱动程序][SQL Server]无法打开登录“[HelpDesk-EasyPay”中请求的数据库。 登录失败。
这是我的 SP：
@Path VARCHAR(100),
@UserName VARCHAR(15),
@PassWord VARCHAR(15),
@ServerName VARCHAR(15)
AS
set quoted_identifier off
set nocount on
declare @n int
declare @db varchar(40)
set @db=DB_NAME()
declare @TableName varchar(15)
declare @bcp varchar(200)
select identity(int,1,1) as tblNo,name tblname into #T from Sysobjects where xtype='u'
select @n=COUNT(*) from #T
WHILE (@n>0)
BEGIN
SELECT @TableName=tblname FROM #T WHERE tblno=@n
PRINT 'Now BCP out for table: ' + @TableName
SET @bcp = "master..xp_cmdshell 'BCP " + "[" + @db + ".." + @TableName + "]" + " OUT" + @Path + "" + @TableName+".txt -c -U" + @UserName + " -P" + @PassWord + " -S" + @ServerName + " -T" + "'"
EXEC(@bcp)
SET @n=@n-1
END
DROP TABLE #T
任何人都可以提供建议吗？ 这似乎是连接问题或 BCP 问题？ 没有把握。
编辑：我从查询分析器运行这个，因为我有 118 个表要输出到平面文件。 我似乎同意这是一个身份验证问题，因为我尝试使用用户名 sa 密码 root 连接到主数据库。 这就是它的设置，我得到相同的错误：SQLState = 37000，NativeError = 4060
I have created a proc that grabs all the user tables in a local DB on my machine. I want to be able to create a flat file of all my tables using BCP and SQL. Its a dummy database in SQL 2000 connecting through windows authentication. I have set my enviroment path variable in WinXP SP2. I have created new users to access the db, switched off my firewall, using trusted connection. I have tried dozens of forums, no luck.
In dos command prompt I get the same error.
SQLState = 37000, NativeError = 4060
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login '[HelpDesk-EasyPay'. Login fails.
Here is my SP:
@Path VARCHAR(100),
@UserName VARCHAR(15),
@PassWord VARCHAR(15),
@ServerName VARCHAR(15)
AS
set quoted_identifier off
set nocount on
declare @n int
declare @db varchar(40)
set @db=DB_NAME()
declare @TableName varchar(15)
declare @bcp varchar(200)
select identity(int,1,1) as tblNo,name tblname into #T from Sysobjects where xtype='u'
select @n=COUNT(*) from #T
WHILE (@n>0)
BEGIN
SELECT @TableName=tblname FROM #T WHERE tblno=@n
PRINT 'Now BCP out for table: ' + @TableName
SET @bcp = "master..xp_cmdshell 'BCP " + "[" + @db + ".." + @TableName + "]" + " OUT" + @Path + "" + @TableName+".txt -c -U" + @UserName + " -P" + @PassWord + " -S" + @ServerName + " -T" + "'"
EXEC(@bcp)
SET @n=@n-1
END
DROP TABLE #T
Can anyone advise. This seems to be a connection problem or BCP ? Not sure.
edit: I am running this from query analyzer because I have 118 tables to output to flat file. I seem to agree that its an authentication issue because I tried connecting to master db with username sa password root. which is what its set to and I get the same error: SQLState = 37000, NativeError = 4060
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您的括号延伸到整个限定表名称 - 只有各个组件应该用括号括起来：
应该可以工作，所以您希望：
在您的代码中。 看起来您给出的错误消息已被截断，否则您将能够看到它正在尝试打开数据库“[HelpDesk-EasyPay.dbo.customer]”，当然，该数据库不存在，即使它存在这样做，然后您会收到一个错误，表明未指定表。
Your brackets are extending over the entire qualified table name - only the individual components should be bracketed:
should work, so you want:
in your code. It looks like the error message you gave was truncated, otherwise you would have been able to see that it was attempting to open database "[HelpDesk-EasyPay.dbo.customer]" which, of course, does not exist, and even if it did, you would then get an error that no table was specified.
我对 OUT 有同样的问题（减号字符会杀死所有事件，而 ^ 不起作用）
我用 QUERYOUT 来避免它。 像这样 ：
I have the same issue for the OUT (the minus character kills everything event the ^ don't work)
I avoid it with the QUERYOUT. Like this :
也许有人会派上用场：我对 SQL Server 2017 也有同样的问题。原因是数据库名称周围的方括号。
Maybe someone will come in handy: I have the same issue with SQL Server 2017. The reason was the square brackets around the database name.