Post reply

export dynamic sql to a sql file to be read and executed on another sql machine

  • February 10, 2017 at 9:38 am

    #321535

    Hello everyone,

    I am trying to find a way to use a dynamic sql i created, once its ran on one SQL server machine... machine A i guess we can call it, every night on a job... then it exports the dynamic sql, not the results, but the dynamic sql select statement, onto a unc path, which that path can be read by machine b, to read the .sql file, and run it using hopefully sqlcmd, i dont want to use cmdshell, for security reasons. is there a way on how to do that, can anyone give an example or link on how this can be done?

    Thanks in advance and happy friday 🙂

  • February 10, 2017 at 12:00 pm

    #1927861

    Maybe it would be easier to store the dynamic SQL statement into a table on server B instead of a file?  Just thinking it's easier to avoid the external file system calls.

  • February 10, 2017 at 12:05 pm

    #1927865

    ahh i gotya, but sadly that would require a link server, and for security purposes, they don't want to link servers 🙁

    but that would be a good idea, sadly they are tight security at my place 🙁

  • February 10, 2017 at 12:23 pm

    #1927869

    edit:  oops this idea doesn't work... still coming up with other ideas

  • February 10, 2017 at 12:34 pm

    #1927874

    OK, in your SQL Agent job on ServerA, you can have a step of type "Operating System (CmdExec)" that calls SQLCMD with the -i parameter to read the script that produces the SQL command, and the -o parameter to capture the output to your UNC path, so your script would build the dynamic SQL then use PRINT to output it something like:

    SET NOCOUNT ON;
    declare @sqlcmd nvarchar(4000);
    set @sqlcmd = 'SELECT col1,col2,col3 FROM tab1 WHERE col2 = ''val'';';
    print @sqlcmd;

    then on ServerB SQL Agent job have a step of type "Operating System (CmdExcec)" that also calls SQLCMD using -i parameter as the filename you saved above in the -o parameter.

  • February 13, 2017 at 12:32 pm

    #1928186

    thanks that worked 🙂 now to move onto the other side, and find out how to get this dang .cmd file to read all the files in a folder directory, which i am using https://gallery.technet.microsoft.com/scriptcenter/Run-all-Sql-in-a-Folder-4dd333f1 but he really copied it from: https://sqlandme.com/2013/03/25/sql-server-executing-multiple-script-files-using-sqlcmd/ but he did give credit... but i will post again for this topic as i am having trouble running this :(, Thanks again for your help 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply