Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Executing -tsql from within stored procedure

Posted on 2013-05-15
10
Medium Priority
?
349 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

636 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