February 10, 2017 at 9:38 am
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
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
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
edit: oops this idea doesn't work... still coming up with other ideas
February 10, 2017 at 12:34 pm
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
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