SQL Server - Execute Snap

A statement like this works fine in SQL Server. How can I get something like this to work in the SQL Server - Execute Snap? There has to be a syntax issue that I have. Any help is appreciated.

DECLARE @sql AS varchar(max) = ‘This is a Test’
PRINT @sql

Hi @sdoscher,

If I understood you correctly, you want to return something from SQL Execute snap?
This syntax can help you “select ‘This is a Test’ as sql”. Put it in the SQL Execute Snap and see the results.

BR,
Marjan

3 Likes

Hi @marjan.karafiloski Yes, this works:
select ‘This is a Test’ as sql.

What I am ultimately trying to get to is for the Execute snap to execute dynamic SQL such as:
DECLARE @sql AS varchar(max)
SET @sql = ’
select top 10 * from dbname.dbo.hc_table

EXEC (@sql)

Can the Execute snap do that?

1 Like

Hi @sdoscher ,
Yes, you can do that.

Here is an image of how to set the query in Execute Snap:

Here is an image of the output of the Execute Snap:
image

BR,
Marjan

5 Likes

Hmmm…that did not work for me. I talked to the SL admin and they said we are on
SQL Server snap pack main11181. That is not the latest version, would that have anything to do with it? What version are you on?

Hi @sdoscher ,

I’m using version main12833. I’ve tried with version main11181 and I got this output:


What is your output?

Maybe the Snap Pack version has to do something with it, change it to the latest main12833 and try again.

BR,
Marjan

2 Likes

There is a change of behaviour w.r.t Execute Snap is all dbs. Till 4.26, the Declare query always gives output containing “message” and “status”, and ignores the result set if any.

In 4.27, we prefer result set. If a result set exists then its displayed. If no result set then we simply display {message=success} output. HTH

2 Likes

Hi @marjan.karafiloski That is the EXACT message I get. I spoke to the SL admin and he is willing to upgrade it. He had upgraded it…but then there was an issue and they rolled back. He said he will try again…TYSM!

1 Like

Hi @smudassir Yes. That correlates to the EXACT issue I am seeing…can 4.27 be upgraded “independently” or does it have to be part of updating the entire SQL Server SnapPack? We are currently running SQL Server main11181, but I know that is not the latest.

May I know what issue you are facing with the 4.27 version?

Hi @smudassir I am getting this output
image
How do I tell if I have 4.26 or 4.27?
From your previous feedback, it is almost certain that I do not have 4.27. For the SQL Server SnapPack, we have main11181. Does that mean I have 4.26?

Hi @sdoscher ,

In the documentation for SQL Server Execute, on the bottom you can see the versions:

BR,
Marjan

2 Likes

@marjan.karafiloski You literally are about 5 minutes ahead of me. :slight_smile: Working with the admin to see how we can upgrade. Thx!

1 Like

Upgrading to 4.27 worked. Thank you so much @marjan.karafiloski and @smudassir for for your help in solving this. It is GREATLY appreciated.

1 Like