September 20, 2015 at 11:57 pm
Hello and greetings to all expert, can anyone tell me why I getting this error message below:
SQLState = 42S02, NativeError = 208
Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name 'TEST.dbo.users'.
SQLState = 42000, NativeError = 8180
Error = [Microsoft][SQL Native Client][SQL Server]Statement(s) could not be prepared.
For Code below:
SET @COLUMN_NAME='UserID'
SET @Database='TEST'
SET @table='users'
SET @DyQuery='SELECT '+@COLUMN_NAME+',* FROM '+@Database + '.dbo.' + @table+' WHERE '+@COLUMN_NAME+' LIKE '''' OR '+@COLUMN_NAME+' IS NULL'
SET @FileName=@Database + '.dbo.' + @table
SET @Command = 'bcp "'
+ @DyQuery
+ '" queryout "'
+ 'C:\'
+ @FileName --FileName, IF need a familiar name--
+ '_'
+ CONVERT(VARCHAR,YEAR(GETDATE())) --get year
+ CONVERT(VARCHAR,REPLACE(STR(MONTH(GETDATE()),2),' ','0')) --get month
+ CONVERT(VARCHAR,REPLACE(STR(DAY(GETDATE()),2),' ','0')) --get day
+ '.txt" -c -t -T -U da -P 123456 -S' + @@SERVERNAME + '\SQLEXPRESS'
EXEC xp_cmdshell @command
Is there any missing ? I do not know how to solve it. I already enable sp_configure for xp_cmdshell.
I also try to insert the Server name but it still pop the same error.
September 21, 2015 at 12:31 am
I would put PRINT statements after
SET @DyQuery=...
and before
EXEC xp_cmdshell @command
to see what those two variables contain. I would then try them manually, which will show errors more clearly, and then adjust the source code to generate any changes you make.
That said, they look fine to me.
"Invalid object name 'TEST.dbo.users'."
Does this give you some rows, or an error?
SELECT TOP 10 * FROM TEST.dbo.users
September 21, 2015 at 12:32 am
PS If it all works fine then try logging in as User = "da" Password="123456" and check that the SQL Query runs OK for that user
September 21, 2015 at 6:38 pm
The PRINT function also been test, I think it the query is correct.
I try the SELECT statement and there is a row how data appear. No error occurs.
The username and the password also correct.
Not sure why this error pop out.
Thank you for the respond.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply