Solved

Executing -tsql from within stored procedure

Posted on 2013-05-15
10
339 Views
Last Modified: 2013-05-20
I have a stored procedure that generates t-sql for shrinking log files for every user database.

Basically the script generated looks something like this:

USE [DB1] 
GO 
DBCC SHRINKFILE (N'DB1_log' , 0, TRUNCATEONLY) 
GO 

USE [DB2] 
GO 
DBCC SHRINKFILE (N'DB2_log' , 0, TRUNCATEONLY) 
GO 

USE [DB3] 
GO 
DBCC SHRINKFILE (N'DB3_Log' , 0, TRUNCATEONLY) 
GO 

Open in new window


Then I put all that t-sql into a variable and execute it:
EXEC @SQL

That gives me an error that seems to be truncated:


Msg 203, Level 16, State 2, Procedure usp_ProcedureName Line 50 The name '

where line 50 is here:

SELECT @i = MIN(database_id)
                FROM sys.databases  
                WHERE database_id > @i
                AND (owner_sid <> 1 AND is_distributor<>1)     <-- line 50
                AND state_desc='ONLINE'     

Open in new window



if I copy the t-sql generated into another query window, it executes successfully. Also if I write it to a .sql file and execute the file from the same stored procedure it also executes sucessfully. Why doesn't EXEC @SQL work?
0
Comment
Question by:YZlat
  • 6
  • 4
10 Comments
 
LVL 6

Expert Comment

by:APoPhySpt
ID: 39168037
maybe your @SQL variable cannot hold that full query, for example you may be trying to save a value of a 1000 chars into a @sql variable that can only hold 500.. check you variable lenghts and the size of the the contents of your @sql and check if the contents of that variable is correct
0
 
LVL 35

Author Comment

by:YZlat
ID: 39168087
That's not the case because before executing I print out the variable and it looks fine. Also i write out the same variable to a file and the file executes fine as well
0
 
LVL 35

Author Comment

by:YZlat
ID: 39168115
I also tried executing it using

EXECUTE sp_executesql @SQL

and here is the output I got:

USE [DB1] 
GO 
DBCC SHRINKFILE (N'DB1_log' , 0, TRUNCATEONLY) 
GO 

USE [DB2] 
GO 
DBCC SHRINKFILE (N'DB2_log' , 0, TRUNCATEONLY) 
GO 

USE [DB3] 
GO 
DBCC SHRINKFILE (N'DB3_Log' , 0, TRUNCATEONLY) 
GO  

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 10
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 20
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 25
Incorrect syntax near 'GO'.
 

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 6

Expert Comment

by:APoPhySpt
ID: 39168144
I read online:

"SQL Server Management Studio can't handle some non printable characters.

Check the newline characters, probably you have Linux (LF) or Mac style (CR) instead of Windows style (CR and LF). You can check with any advanced text editor, for example Notepad++·" check for special chars
0
 
LVL 6

Expert Comment

by:APoPhySpt
ID: 39168157
Use Notepad++ to replace CR (\r) for CRLF (\r\n) - keep the order, it's important!
0
 
LVL 35

Author Comment

by:YZlat
ID: 39168168
I would be hesitant to download an unknown product like that
0
 
LVL 35

Author Comment

by:YZlat
ID: 39168173
I suspect it might have something to do with data type of @SQL variable
0
 
LVL 6

Expert Comment

by:APoPhySpt
ID: 39168185
hum... notepadd++ is save, still, try it with some other text editor to see if the linebreaks are visible.

you can also erase any and all  linebreackers ( all enters, all lines, and make the query simple) this way instead of replacing it you can try to remove then,
0
 
LVL 35

Accepted Solution

by:
YZlat earned 0 total points
ID: 39168208
0
 
LVL 35

Author Closing Comment

by:YZlat
ID: 39180430
found the solution myself
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 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