Solved

SQL 2008 T SQL output Stored Procedure to .txt

Posted on 2013-01-20
7
613 Views
Last Modified: 2013-05-02
Using SQL 2008, I'm trying to use a Stored Procedure to write the contents of a SP to a .txt file. I can select the SP definition and can write content to .txt no problem. When trying to write the SP definition to .txt, assigned to a @str varchar(max), nothing is written to the file.

DECLARE @str varchar(max), @filename varchar(max), @ExportTXT varchar(max)
SET @filename = 'c:\test.txt'
SET @str = (select top 1 definition from sys.sql_modules, information_schema.routines where LEFT(information_schema.routines.routine_definition,200) = LEFT(sys.sql_modules.definition,200)  

SET @ExportTXT = 'echo foo: ' + '>>' + @filename
EXEC master..xp_cmdshell @ExportTXT

SET @ExportTXT = 'echo ' + @str + '>>' + @filename
EXEC master..xp_cmdshell @ExportTXT

Open in new window


The above writes the line 'foo:' but nothing after it.

Related to this question, how do I force a line break without using a new echo command + call to EXEC master..xp_cmdshell. I'd rather write once to the .txt file than multiple times.
0
Comment
Question by:drl1
  • 3
  • 3
7 Comments
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38799812
Try something like this.  When using Echo, you need to have the string enclosed in " "

SET @ExportTXT = 'echo "' + @str + '" >>' + @filename
EXEC master..xp_cmdshell @ExportTXT 

Open in new window

0
 

Author Comment

by:drl1
ID: 38815821
The @str variable is defined as a string, is already encapsulated in single quotes in T SQL (i.e. a string) and works fine if I assign a short string such as 'foobar' (without single quotes) to @str and don't use your suggested double quotes, so that's not the issue.

I think i've narrowed this down to the size of the Stored Proc being too large to be held in the @str variable despite this variable being defined as varchar(max). The SP is around 40,000 characters including blank spaces.
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 38815858
Sorry, I did not read that it was the "contents" of the stored proc.  You have a limit of 256 characters on that command line. So 40,000 will be outside that range.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:drl1
ID: 38815957
Within the Echo command line? That would explain it. How can I dump 40k chars to a txt file or more simply just have the stored proc save a copy of itself as a txt file?
0
 
LVL 35

Expert Comment

by:David Todd
ID: 38815983
Hi,

I'm wondering if using the SQL command mode may help here. Its not a stored procedure, but would be a sql script that can be scheduled and automated.

Just a thought

Regards
  David
0
 

Author Comment

by:drl1
ID: 38815997
Would the SP call the sql script which in turn saves a copy of the SP to txt? It's not something that I need scheduling as the dump to txt needs to run at each point that the SP is fired, which is currently a manual process.
0
 
LVL 24

Accepted Solution

by:
DBAduck - Ben Miller earned 500 total points
ID: 38816001
You could use something like this:

SET @str = 'sqlcmd -E -Sinstancename -Q "EXEC sp_helptext ''' + @procname + '''" -o "' + @filename + '"'
EXEC master..xp_cmdshell @str

Open in new window

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question