question

Jan Sorenson avatar image
Jan Sorenson asked

Running T-SQL out side of SQL Server

Is it possible to create some kind of T-SQL executable, holding a series of T-SQL statements, that can be run from to OS. We are moving from an Informix DB to SQL Server DB and have developed a number of Informix 4GLs. I would like to be able to execute these T-SQL scripts as part of our Financial Application. Any Ideas on how this might be accomplished.

t-sqlsqlcmdcommand-lineosql
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

5 Answers

·
DaniSQL avatar image
DaniSQL answered

You can run them from command prompt using SQLCMD command line utility.

It looks like something like this:

sqlcmd -S YourServerName -i C:\YourTsqlScript.sql

Hope this helps!

EDIT: Here is a link about SQLCMD utility and example in Books online

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
+1 SQLCMD works great, you can pass it userid and password if the user isn't authenticated to your network or doesn't have rights to the DB. You can compile the script into a .exe or just launch it in batch. It is typically better to compile it if you are having to pass userid and password.
0 Likes 0 ·
Gustavo avatar image
Gustavo answered

Have you tried OSQL or SQLCMD ? They enable you to run a query from other sources.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DaniSQL avatar image DaniSQL commented ·
+1 for the OSQL..
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

In addition to the others suggestions, all good, you can run TSQL through Powershell as well.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image TimothyAWiseman commented ·
Good point (+1), but OSQL and SQLCMD are probably better paths for what the question is positing. Though Powershell will give you full access to SMO if you want that, which OSQL and SQLCMD will not.
0 Likes 0 ·
moi_meme avatar image
moi_meme answered
PowerShell seems to be the way to go, check out [Invoke-Sqlcmd][1] you simply do like this: Invoke-Sqlcmd -Query "SELECT Field FROM Table" Invoke-Sqlcmd -InputFile "file.sql" [1]: http://msdn.microsoft.com/en-us/library/cc281720(SQL.100).aspx
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
I know I'm like 100 years late, but I have to say, if you want to run SQL from your application, you want to run it from your application, rather than going out to an executable at the O/S level. Some day, it won't work. Maybe the user's database is set up in outer mongolian or something, but at that point, unless you have done a *lot* of work to integrate your executable process handler, the only error you will be able to give is 'meh'. If your application is in .NET, then you can run your T-SQL against any target with a relatively small amount of code, and have much better error handling in there. Just my 2c anyway...
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Tim avatar image Tim commented ·
+1, well stated.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.