November 2, 2012 at 9:12 am
GilaMonster (11/2/2012)
Not even with SET NOEXEC ON?
And THANKS a ton Gail! This got us on the right track at least. 🙂
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgNovember 2, 2012 at 9:48 am
Jason Selburg (11/2/2012)
Eugene Elutin (11/2/2012)
Try this:
DECLARE @sql VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'
SET @sql = 'SET NOEXEC ON;
' + @sql + '
SET NOEXEC OFF;'
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT 'Error here: ' + ERROR_MESSAGE()
END CATCH
SET NOEXEC OFF;
Almost there!!!!!
It seems multi-line statements/batches seem to have problems and are returned as invalid. :hehe:
Do you mean multi-line statements/batches separated with "GO"?
If yes, you should be aware that "GO" is just a batch separator but it is not part of T-SQL.
So even one line valid "batch" with "GO" is not valid. Try this:
SELECT 1;
GO
And this:
EXEC ('
SELECT 1;
GO')
And see the difference...
One of the ways to make it work for you could be separating all you batches by "GO" into separate SQL's statements to validate.
November 2, 2012 at 9:56 am
SET PARSEONLY ON/OFF works as well.
i'm having some fun trying to get a CLR to work as a proof of concept; i'll post it as soon as it returns results the way i'd expect.
DECLARE @sql VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'
SET @sql = 'SET PARSEONLY ON;
' + @sql + '
SET PARSEONLY OFF;'
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT 'Error here: ' + ERROR_MESSAGE()
END CATCH
SET NOEXEC OFF;
Lowell
November 2, 2012 at 3:35 pm
Lowell (11/2/2012)
SET PARSEONLY ON/OFF works as well.i'm having some fun trying to get a CLR to work as a proof of concept; i'll post it as soon as it returns results the way i'd expect.
DECLARE @sql VARCHAR(8000) = 'SELECT * FROM FROM sys.objects'
SET @sql = 'SET PARSEONLY ON;
' + @sql + '
SET PARSEONLY OFF;'
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
PRINT 'Error here: ' + ERROR_MESSAGE()
END CATCH
SET NOEXEC OFF;
I don't believe that will work if the @sql created a stored procedure like Jason wanted though. You'd probably get an error something about the CREATE PROCEDURE statement needing to be the first statement in the batch.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply