Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marat
Yak Posting Veteran
85 Posts |
 Posted - 2004-08-22 : 22:07:12
|
| I had written the following T-SQL:SET @cmd = 'bcp "[' + @db + '].[' + @owner + ']".' + @tbl + ' out "\\anatole-martir\Snapshots\Snapshot\' + @db + '.' + @owner + '.' + @tbl + '.txt" -Smds-pum -T -c -t, -q'exec master..xp_cmdshell @cmdand I'm having the following error message:Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database requested in login '[WORK]'. Login fails.Value of @owner = 'Loretta.Garbo'.Everything is fine when @owner = 'L_G'What is wrong with the code?Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-22 : 22:35:50
|
| I seem to remember something about bcp not using the [] to delimit identifiers very well.try"select * from [' + @db + '].[' + @owner + ']".' + @tbl + ' queryout might not work either but I suspect the dot in the owner is the problem.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-08-22 : 22:41:49
|
| Nope just tried it and it works ok.must be because the user you are connecting unser to do the bcp doesn't have access to tables owned by Loretta.Garbo - would need to be sysadmin or dbo.Also try seeing what's in @cmd.Is the table a view on another database?The error is about access to database Work.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
marat
Yak Posting Veteran
85 Posts |
Posted - 2004-08-23 : 00:03:48
|
| Thanks Nigel. |
|
|
|
marat
Yak Posting Veteran
85 Posts |
Posted - 2004-08-23 : 03:31:17
|
| Interesting thing about the script is that the following works:SET @cmd = 'bcp ' + @db + '.[' + @owner + '].' + @tbl + ' out "\\anatole-martir\Snapshots\Snapshot\' + @db + '.' + @owner + '.' + @tbl + '.txt" -Smds-pum -T -c -t,'but the following doesn't:SET @cmd = 'bcp "[' + @db + '].[' + @owner + '].[' + @tbl + ']" out "\\anatole-martir\Snapshots\Snapshot\' + @db + '.' + @owner + '.' + @tbl + '.txt" -Smds-pum -T -c -t, -q' |
|
|
|
|
|
|
ERROR using bcp