Results 1 to 13 of 13
  1. #1
    Join Date
    May 2005
    Posts
    119

    Unanswered: Bulk export Not working

    bcp "viewname" queryout filename.txt -c -T

    (filename and viewname changed for security purposes)

    errors with
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'queryout'.

    Any ideas what I'm doing wrong? Maybe there's a better way to export data from a view to a .csv file?

    TIA,
    Krista

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    where are you executing this? In Management Studio or DOS? What is the exact code?

    Why are we whispering?

  3. #3
    Join Date
    May 2005
    Posts
    119
    I am trying to automate an FTP process. I need to send a file every four hours to update an external website. I used to use DTS packages to do this, but in SQL 2005, that no longer exists (that I can find), and I've long since removed the old server that containded the file, so I can't use the import wizard.

    Basically, that is the code: I just replaced the actual query name with "query name" and the actual file name with "filename".

    If you really need to know the view is called "srbcdata" and the filename is "srbc.csv".

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ah sorry - that is not what I meant - I don't need to know that.

    OK - you aren't using DTS - that's a good thing. SSIS is the 2005 replacement.

    What I meant was are you executing:
    Code:
    bcp "viewname" queryout filename.txt -c -T  
    or
    Code:
    EXEC master.dbo.xp_cmdshell 'bcp "viewname" queryout filename.txt -c -T'  
    ????

  5. #5
    Join Date
    May 2005
    Posts
    119
    Just the plain ole bcp statement. I will try the other, though.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Quote Originally Posted by Krista327
    I am trying to automate an FTP process. I need to send a file every four hours to update an external website. I used to use DTS packages to do this, but in SQL 2005, that no longer exists (that I can find), and I've long since removed the old server that containded the file, so I can't use the import wizard.

    Basically, that is the code: I just replaced the actual query name with "query name" and the actual file name with "filename".

    If you really need to know the view is called "srbcdata" and the filename is "srbc.csv".
    You can install the SQL DTS Designer addon for 2005 if it comes down to getting the old DTS job up and operating.

  7. #7
    Join Date
    May 2005
    Posts
    119
    how do you enable xp_cmdshell? My error msg states that it is not enabled.

    Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
    SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.


    Surface area configuration was not found in help.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    xp_cmdshell allows you to shell commands out the OS. Remember that this includes stuff that can format drives and bugger about with networks and all that.

    Anyhoo - SACM is a tool. Start -> Programs _> SQL Server 2005 -> SACM

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    also, you need to use 3 part naming unless the view happens to be in the default database of the NT user running this. do this intead:

    bcp "select * from MyDatabase.dbo.viewname" queryout filename.txt -c -T

    or better:

    bcp "MyDatabase.dbo.viewname" out filename.txt -c -T

    EDIT: must have a query if using "queryout", or object name if using "out"
    Last edited by jezemine; 02-19-08 at 14:07.

  10. #10
    Join Date
    May 2005
    Posts
    119
    I've tried this several different ways, and still am getting an error: cwa_srbcoutput is a view that contains cast statements for max/min/average over a four hour period.

    EXEC master.dbo.xp_cmdshell bcp "select * from runtime.dbo.cwa_srbcoutput" queryout C:\srbc.csv -c -t

    error:
    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near 'select * from runtime.dbo.cwa_srbcoutput'.

    bcp "cwa_srbcoutput" out C:\srbc.csv -c -t

    error:
    Msg 179, Level 15, State 1, Line 3
    Cannot use the OUTPUT option when passing a constant to a stored procedure.

  11. #11
    Join Date
    May 2005
    Posts
    119
    Darn it!! The command works in the "dos" cmd screen; but not in a sql query! What's up with that?!

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    declare @cmd varchar(4000)
    set @cmd='bcp "select * from runtime.dbo.cwa_srbcoutput" queryout C:\srbc.csv -c -t'
    EXEC master.dbo.xp_cmdshell @cmd

    I think you would do well to read this: http://www.simple-talk.com/sql/datab...ed-procedures/

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Krista327
    I've tried this several different ways, and still am getting an error: cwa_srbcoutput is a view that contains cast statements for max/min/average over a four hour period.

    EXEC master.dbo.xp_cmdshell bcp "select * from runtime.dbo.cwa_srbcoutput" queryout C:\srbc.csv -c -t

    error:
    Msg 102, Level 15, State 1, Line 4
    Incorrect syntax near 'select * from runtime.dbo.cwa_srbcoutput'.

    bcp "cwa_srbcoutput" out C:\srbc.csv -c -t

    error:
    Msg 179, Level 15, State 1, Line 3
    Cannot use the OUTPUT option when passing a constant to a stored procedure.
    xp_cmdshell is a procedure - it accepts a string input for the parameter. You need to enclose the input in quotes. It is just a string that is executed in DOS.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •