Solved

Executing -tsql from within stored procedure

Posted on 2013-05-15
10
337 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

896 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now