Solved

Executing -tsql from within stored procedure

Posted on 2013-05-15
10
345 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

707 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