• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

Executing -tsql from within stored procedure

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
YZlat
Asked:
YZlat
  • 6
  • 4
1 Solution
 
APoPhySptCommented:
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
 
YZlatAuthor Commented:
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
 
YZlatAuthor Commented:
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
APoPhySptCommented:
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
 
APoPhySptCommented:
Use Notepad++ to replace CR (\r) for CRLF (\r\n) - keep the order, it's important!
0
 
YZlatAuthor Commented:
I would be hesitant to download an unknown product like that
0
 
YZlatAuthor Commented:
I suspect it might have something to do with data type of @SQL variable
0
 
APoPhySptCommented:
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
 
YZlatAuthor Commented:
found the solution myself
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now