August 21, 2009 at 7:09 am
I need to create a job that calls a stored procedure that resides in a directory on a different server. Can this be done through T-SQL within a job step? What qualifier syntax will I need to identify to the job step the path where the stored procedure can be located? A linked server exists between the two servers involved.
For example, if the server name the job runs on is Server1, the server the stored procedure resides on is Server2, and the stored procedure a .sql file and is saved on \\Server2\C:\Procedures and the file name is spUpdateColumns.sql, what syntax would need to be entered in the job step in order to call that external stored procedure?
Calling an external stored procedure is due to our source control approach.
Many thanks,
Kay
August 21, 2009 at 7:42 am
First Kay, I am, going to call into question the source control approach, SQL objects ought to be in ... SQL Servers.. I am curious what the justification is for this. And wonder if it is based on a fundamental misunderstanding of the way SQL Server works.
Also, where do you want the stored procedure to run, on the server calling it? Is the script represented by that filename a Create Procedure or just a script of code to run? Lots of unknowns.
You cannot reference a sproc in a file from SQL, period. What you CAN do from a job step, is call SQLCMD or OSQL using the Operating System subsystem and reference that file and the database you want to start in and execute the file that way. But depending on the contents of the file it may only compile the sproc.
I think this REALLY needs to be rethought.
CEWII
August 21, 2009 at 7:59 am
Thanks for the reply.
My comment about source control was misleading, and I apologize for creating confusion.
You've raised many great points about how this task is being approached.
The fundamental need is to automate a move to production process. We need to gather all changes to be made to the production database throughout the development/testing cycle (such as creating tables, new columns added to tables--generally CREATE TABLE, ALTER TABLE and UPDATE statements to initialize those columns.) Once all changes are tested and ready to move to production, we would like a job to process all those statements.
The intention is to have a .sql file in this folder where we can paste all database change statements, create one cumulative .sql script, and execute it from a job. I wasn't sure this could be done.
Perhaps what we need to do is create a text file with those statements, but instead of trying to execute this as a .sql file, read the contents of the .txt file and execute the statements contained in the file. Does that make more sense?
August 21, 2009 at 10:32 am
You might look at:
http://www.sqlservercentral.com/Forums/Topic764448-146-1.aspx
I've done what you are talking about in only a slightly different way.. What is your source control system? I have primarily used VSS and TFS, and only a very little subversion..
CEWII
August 21, 2009 at 11:55 am
We're currently using subversion for our source control. When I mentioned source control in my original post, I really should have referred to our move to production process, although whatever process we do construct will ultimately reside in our source control application.
I've spoken to a .NET developer here, and he believes my task would be better suited to a .NET program, so I am analyizing that possibility.
Thanks for your input, and if I come up with something brilliant I will update this post for the benefit of other rookies. We need all the help we can get.
August 21, 2009 at 12:09 pm
Something caught my eye in your last post, your .NET programmer recommended a .NET solution, while I'm NOT saying he is wrong, I am always wary of what I call the hammer and nail problem. When all you have is a hammer, everything looks like a nail, when all you know is .NET or SQL then every problem should be solved with that tool.
Also I was thinking, is this a single database solution or is it a multi-server, multi-database kind of app?
I'm not sure what your .NET tool would do, you can easily package up a directory of .sql files with something like this script that I wrote that I call combine.cmd:
@ECHO OFF
@ECHO ----------------------
@ECHO Combining *.sql files
@ECHO ----------------------
IF EXIST combined.sql DEL /Q combined.sql
COPY *.sql combined.sql.temp
RENAME combined.sql.temp combined.sql
@ECHO ----------------------
@ECHO ------- DONE -------
@ECHO ----------------------
PAUSE
I have used tweaked versions of it to combine up and write the scripts in the parent directory.
The one thing that REALLY helps, is that if all scripts, sprocs, views, tables, everything ends with GO and a CR/LF. Then the scripts can be added together and you won't have as many issues.
Also, you should look into a tool either like the DB edition of VSTS 2008 or the Red-Gate SQL Compare tool. That makes schema changes MUCH easier, but when you change structures you usually end up re-writing the scripts generated by the tools, such as renamed columns or a column split into two or more fields, or a new column that gets built from other data. I have almost never used the scripts fresh out of the tools.
I have considered writing a tool to do this but there are SOOOO many possibilities that I'm a little daunted..
CEWII
August 21, 2009 at 12:21 pm
That's a useful utility you have written. My dilemma is that I need to retrieve the scripts from a different server and run them from a job on the production server. I'm sure your script could be modified to do that, but I don't know the syntax to do it.
I know there are probably many different ways to do this task, but my T-SQL skills are not yet up to the challenge.
The .NET approach would be to create a console project creating an executable that is called as an Operating system (CmdExec) step. We would use the .exe to read the file from the other server and execute the statements. I don't know all of the syntax to do this either, but I have help if I decide to go this route.
The other suggestion was to use iSQL, which is simpler (but again, I need to learn how to do it this way.) Would you consider iSQL an option?
August 21, 2009 at 1:07 pm
I don't think I would, that is really just a front-end tool. You are really looking at something to do it in the background. So you are doing auto deploys? Thats kind of gutsy.. OSQL or SQLCMD are the command line tools for SQL..
CEWII
August 21, 2009 at 1:46 pm
Well, we HOPE to do auto deploys...it depends on how quickly I can figure out a solution to this. Ours is a very manual process in our move to production method that includes third-party application code, file copies, stored procedure updates and database updates. It isn't fun or efficient.
We already have processes unrelated to our moves to production that were developed in C#, so that's the option I'm currently exploring. If the solution works out, I'll post what I can for others' reference.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply