SQL 2008 T SQL output Stored Procedure to .txt
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.
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.
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 @ExportTXTThe 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.
ASKER
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.
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.
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.
ASKER
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?
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
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
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window