Create a text file from SQL data
-
I need to create a text file from the data I have extracted with a stored procedure. I have tried thru Reporting Services and creating a file out of Query Analyzer. My problem is the data is 600 characters long and Query Analyzer only outputs 256 characters. Reporting services does not create a text file. Can anyone let me know the easiest way to do this?
-
> Query Analyzer only outputs 256 characters
Not necessarily. Try changing the setting in 'Tools -> Options -> Results -> Maximum characters per column'. Maximum setting is 8192.
Maybe that will give you what you need?
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
you could also try using bcp utility as well.
-
From command line use BCP. For help, just type bcp /?
It is by far the fastest and easiest way to get data in and out of SQL.
-
There's also DTS to export to a text file.
-
And OpenRowSet
N 56°04'39.16"
E 12°55'05.25" -
Thanks all. I am going to use the bcp utility with the xp_cmdshell procedure to create the text file. I found a good article on SQLTeam.com that explains everything I wanted to do. That article can be found at http://www.sqlteam.com/item.asp?ItemID=4722.
Again thanks for your help.
-
"Drop Database" is the fastest way to get data out of SQL server.
-
Hi, My name is Juan F. and I am new with this tool but in reference to bcp utility, I have a problem. I am able to execute the bcp utility to export data to an output text file. The problem I am having is that the output file is replaced with a new one everytime I execute the bcp. What I want to do is to append the data to the existing output file. Here is my format:
--If error found then extract process data to an output file using bcp.
IF @n_NumErrors = 1
BEGIN
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = 'c:\itops.txt'
SET @bcpCommand = 'bcp "SELECT ML.ErrorLogDesc FROM SWAProdSupport..V_SWA_MONITOR_LOG ML (Nolock),'
SET @bcpCommand = @bcpCommand + '(SELECT MAX(UpdatedDate) as "MaxDate", LogMntrID FROM SWAProdSupport..V_SWA_MONITOR_LOG ML (Nolock) '
SET @bcpCommand = @bcpCommand + 'WHERE LogMntrID = ' + CONVERT(VARCHAR(6),@i_MntrID) + ' AND UpdatedDate >= DATEADD(day,-25,getdate()) GROUP BY LogMntrID) MaxResults '
SET @bcpCommand = @bcpCommand + 'WHERE ML.LogMntrID = MaxResults.LogMntrID AND ML.UpdatedDate = MaxResults.MaxDate" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U sysuser -P frogbull -c'
--Export data out to a text file executing the call with xp_cmdshell
EXEC Master..xp_Cmdshell @bcpCommand
END
Can you please help?
-
Hello Juan.
Once the file is generated(bcp step is complete), you can run this dos command as next step in the process. This will rename the file, append date to the filename. Hope this helps.
rename c:\itops.txt itops-%date:~10,4%%date:~4,2%%date:~7,2% -
Be careful about using DOS commands.
This defeats the concept of access security.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply