Link to home
Start Free TrialLog in
Avatar of running32
running32

asked on

BCP Error

When i try to execute this I get and error  Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'test'.


   Set @script = 'BCP "test  '+  @ACCOUNT +'" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P pw '

any help would be appreciated
Avatar of SjoerdVerweij
SjoerdVerweij

What are you doing with @script after this statement... xp_cmdshell?
Avatar of running32

ASKER

Exec master.dbo.xp_cmdshell @script

what I need to do was write to a file test and then the account number.   I have the account number in the variable and that is working.
Looks like it's probably an EXEC where it should be an xp_cmdshell ...
Crossed with yours, sorry.

What is "test"? A stored procedure or something? Try "EXEC test" inside the @script instead of just "test".
Sorry I'm not sure what you mean?
My thoughts exactly muzzy, but apparently not...

Ok, do a Print @script right before the xp_cmdshell call and post the exact output.
Try this - quotes around the account as well:

Set @script = 'BCP "EXEC test  '''+  @ACCOUNT +'''" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P pw '
Nope test just something I wanted outputed to the file.  so the file would be

test 1234567
test 4556667
test 4444444
Avatar of Duane Lawrence
it should look something like this:

Set @script = 'BCP "bcp SELECT * FROM Mydatabase..survey_questions WHERE id >= 6000 AND id <
7000  '+  @ACCOUNT +'" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P pw '


BUT SINCE YOU ARE USING THE QUERYOUT OPTION, BEWARE OF THE BUG WITH THIS.
IT APPLIES TO STANDARD ADDITION
http://support.microsoft.com/?scid=http://support.microsoft.com%2Fservicedesks%2Fbin%2Fkbsearch.asp%3Farticle%3D309555
Here is the output when i print it.


BCP "test  1879840" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P ps
BCP "test  1879840" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P ps
BCP "test  1879840" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P ps
BCP "test  1879840" queryout \\jcleary\c$\estatements\cif.txt -c -U uc-P ps
BCP "test  1879840" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P ps
BCP "test  123456" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P ps
I already have my select statement in a cursor..

--CURSOR FOR ALL NEW ACCOUNTS WHICH HAVE BEEN VERIFIED.

--Declare variables
declare @DB_NAME varchar(128)
declare @DB_Match varchar(128)
declare @ColNameSearchingFor varchar(128)
declare @TabNamec varchar (128)
declare @TabNamed varchar (128)
declare @TabName varchar (128)
declare @tablen varchar (128)
declare @script varchar (128)

--declare vairables

DECLARE @ACCOUNT VARCHAR(128)
DECLARE @APPCODE VARCHAR(128)
DECLARE @DELIVERYTYPE VARCHAR(128)
DECLARE @PASSWORD VARCHAR(128)
DECLARE @BRANCHCODE VARCHAR(128)
DECLARE @CLASSCODE VARCHAR(128)
DECLARE @RECCOUNT INT


SET NOCOUNT ON

--declare cursor
declare eStmt cursor
--Start Loop
for


--Select table name when starts with vtab and add to cursor
     select Account, AppCode, DeliveryType, Password, MarketCodeBranch, MarketCodeClass from useracct where maintdate = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),111))and verifiedDate= CONVERT(datetime,CONVERT(nvarchar,GETDATE(),111))


--open cursor
open eStmt

--Get First record in cursor and save to variable DB_NAME
fetch next from eStmt INTO @ACCOUNT, @APPCODE,@DELIVERYTYPE, @PASSWORD, @BRANCHCODE , @CLASSCODE

--Loop unti end of records in cursor
while @@FETCH_STATUS = 0

    --SET  @RECCOUNT = 1
     begin
     
   Set @script = 'BCP "test  '+  @ACCOUNT +'" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -Ppw'
        --    SET  @RECCOUNT =  @RECCOUNT + 1
        --   Exec master.dbo.xp_cmdshell @script
print @script
     
           --get next table name from cursor
       fetch next from eStmt INTO @ACCOUNT, @APPCODE,@DELIVERYTYPE, @PASSWORD, @BRANCHCODE , @CLASSCODE
           
   end
   IF @@FETCH_STATUS = -1
   CLOSE eStmt
   GO
   DEALLOCATE eStmt
   GO
   SET NOCOUNT OFF
   GO
 



If this is a one-off, then just run this in query analyzer, copy and paste the results into a text file. If it isn't, then this is the query you need to be running using BCP. BCP isn't for outputting a number of ad hoc strings to a single output file in the way you are trying to do it.

select 'test ' + CAST(Account AS varchar(10)) from useracct where maintdate = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),111)) and verifiedDate= CONVERT(datetime,CONVERT(nvarchar,GETDATE(),111))
Try

   Set @script = 'BCP "select ''test  '+  @ACCOUNT +'''" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P pw '

(Cut and paste so you get all the single/double quotes right)
Sjoerd - won't that overwrite the file each time, so you only end up with the last line?
I tried the above code and muzzy2003 is correct it overwrote the output.  Is there anyway to concantate the output and write it to the file all at once?  
please post the contents of @script as requested

but my immediate impression is that
Set @script = 'BCP "test  '+  @ACCOUNT +'" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P pw '

should be more like
Set @script = 'BCP "exec [test ]. ['+  @ACCOUNT +']" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P pw '




 
I posted them earlier but here they are again.  Thanks

Here is the output when i print it.


BCP "test  1879840" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P ps
BCP "test  1879840" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P ps
BCP "test  1879840" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P ps
BCP "test  1879840" queryout \\jcleary\c$\estatements\cif.txt -c -U uc-P ps
BCP "test  1879840" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P ps
BCP "test  123456" queryout \\jcleary\c$\estatements\cif.txt -c -U uc -P ps
Not really. Is the cursor necessary?
I needed the output to be formated which it looks like I cannot do in sql.  I can use a dts to get the information extracted and then format it with a program outside of sql.  It is just a shame sql will not export a line formated.

The reason for the cursor is each record either needs to be written out with an A or a D where test was depending on the date.

Run this. No cursor, no looping through, just a single statement:

EXEC xp_cmdshell 'BCP "select ''test '' + CAST(Account AS varchar(10)) from useracct where maintdate = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),111)) and verifiedDate= CONVERT(datetime,CONVERT(nvarchar,GETDATE(),111))" queryout \\jclearly\c$\estatements\cif.txt -c -U uc -P pw'

Is that anything like what you want?
Tell us the test for the A or D - I'm sure you can do this in a single step.
I guess I do not need to test if it is a A or D.  there are 3 seperate queries each comparing different things.

All I need to do is write an A for Record type and A for Action Code in front of the account number and output to a file.

If I try and run the statement above I get  Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'useracct'.  useracct is a table in my database.  thanks
ASKER CERTIFIED SOLUTION
Avatar of muzzy2003
muzzy2003

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Prefix it with your database name, e.g.

EXEC xp_cmdshell 'BCP "select ''test '' + CAST(Account AS varchar(10)) from databasename..useracct where maintdate = CONVERT(datetime,CONVERT(nvarchar,GETDATE(),111)) and verifiedDate= CONVERT(datetime,CONVERT(nvarchar,GETDATE(),111))" queryout \\jclearly\c$\estatements\cif.txt -c -U uc -P pw'