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 |
|
NeilG
Aged Yak Warrior
530 Posts |
 Posted - 2009-06-24 : 08:40:26
|
| Hi Guys, Im trying to create a proc to extract out the data into a text file BEGIN --Check for existing Temp table and drop if exists --declare and populate new temp tableDECLARE @TableName varchar(100)DECLARE @SQL nvarchar(max)DECLARE @bcpCommand VARCHAR(8000)DECLARE @FileNamePath varchar(500)SET @FileNamePath = 'FailureLog_'+replace(replace(replace(convert(varchar(23), getdate(), 120), '-',''), ':', ''), ' ', '_') + '.txt'SET @TableName = ( SELECT TOP 1 ss.name+'.'+st.name [TableName] FROM sys.tables st (nolock) INNER JOIN sys.schemas ss (nolock) ON st.schema_id = ss.schema_id GROUP BY ss.name,st.name ORDER BY max(st.create_date) desc )SET @bcpCommand = 'bcp "SELECT * FROM ' + @TableName + ' (NoLock)' + '" queryout 'SET @bcpCommand = @bcpCommand + 'C:\Documents and Settings\geldern\My Documents\Other\' +@FileNamePath+ ' -c -t"|" -S EM-SQL01 -T'EXEC xp_cmdshell @bcpcommandBut then on execution im getting "Unknown argument 'and' on command line".But i can't see what the problem is with the code is this because my server name has a "-" in the middle of it |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-24 : 09:21:59
|
What do you see when you do a "PRINT @bcpCommand"?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-06-24 : 09:51:28
|
quote:
Originally posted by NeilG
Hi Guys, Im trying to create a proc to extract out the data into a text file BEGIN --Check for existing Temp table and drop if exists --declare and populate new temp tableDECLARE @TableName varchar(100)DECLARE @SQL nvarchar(max)DECLARE @bcpCommand VARCHAR(8000)DECLARE @FileNamePath varchar(500)SET @FileNamePath = 'FailureLog_'+replace(replace(replace(convert(varchar(23), getdate(), 120), '-',''), ':', ''), ' ', '_') + '.txt'SET @TableName = ( SELECT TOP 1 ss.name+'.'+st.name [TableName] FROM sys.tables st (nolock) INNER JOIN sys.schemas ss (nolock) ON st.schema_id = ss.schema_id GROUP BY ss.name,st.name ORDER BY max(st.create_date) desc )SET @bcpCommand = 'bcp "SELECT * FROM ' + @TableName + ' (NoLock)' + '" queryout 'SET @bcpCommand = @bcpCommand + '"C:\Documents and Settings\geldern\My Documents\Other\"' +@FileNamePath+ ' -c -t"|" -S EM-SQL01 -T'EXEC xp_cmdshell @bcpcommandBut then on execution im getting "Unknown argument 'and' on command line".But i can't see what the problem is with the code is this because my server name has a "-" in the middle of it
Thanks Peso, i've located the problem with it saying "Unknown argument 'and' on command line"and i figured that it needed some form of quotation to stop it splitting the string |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2009-06-24 : 10:37:35
|
| Now i'm getting a problem when i try to place the file on a different server i.e. \\server01\c$\logBut it's saying that can't find output However i can open that folder etc and place files in it, can't see what the reason it can't place a file in there |
|
|
|
|
|
|
|
|
BCP Command