Post reply

Deploying Scripts with SQLCMD

  • March 24, 2009 at 12:35 am

    #219408

    Comments posted to this topic are about the item Deploying Scripts with SQLCMD

    LinkedIn Profile

  • March 24, 2009 at 2:41 am

    #965019

    Good stuff!

    Have you ever had the (mis)fortune to use MSbuild too?

    Adam

  • March 24, 2009 at 5:11 am

    #965068

    Fatal scripting error. Cannot open connection specified in the SQLCMD script.

    Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

    i got like that error when i do connect with the use of the sqlcmd.

  • March 24, 2009 at 5:13 am

    #965069

    Excellent!

    I have reached the stage in life where if it isn't scripted, I screw it up. How nice to learn about yet another scripting strategy.

  • March 24, 2009 at 5:31 am

    #965075

    for the most bravest here is a perl script to run multiple sql scirpts ...

    requirement: the scirpts should be numbered:

    1.RUN.scriptname.sql

    2.RUN.scriptName2.sql

    3...

    n. RUN.scirptname3.sql

    and placed in the same directory as the perl script

    You can create a command line executable for it ( does not required Perl on the running machine ) with par or perl2exe ...

    Anybody interested in ... I could put a link for binary download it ...

    Grab the source at :

    http://ysgitdiary.blogspot.com/2009/03/perl-script-to-run-sql-scripts-for-sql.html

    Comments , shouts boos etc. would be highly appreciated .. .

  • March 24, 2009 at 6:00 am

    #965083

    Great article, David.

    I had not seen examples of the error and standard outputs before.

    Kuldip: it looks as though your windows login does not have windows integrated access to the server. You need to supply a SQL user name and password as part of the SQLCMD connect command or on the command line.

    kuldip.bhatt (3/24/2009)

    Fatal scripting error. Cannot open connection specified in the SQLCMD script.

    Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

    i got like that error when i do connect with the use of the sqlcmd.

  • March 24, 2009 at 6:07 am

    #965089

    how i give the usrename and password in the sql command?

    :setvar Maindbserver "NHSserver"

    :setvar Maindb "NHS_031209"

    :Connect $(Maindbserver)

    go

    $ maindb

    this is my code.

    in this script how i can give user name and password?

    My question is if diff servers has diffrent password then

    what is the solution for this.

    you must give Password and usrename.

  • March 24, 2009 at 6:27 am

    #965101

    OOh yes. SQLCMD! I agree wholeheartedly that it is an essential for quickly automating complex processes across several servers. I love it. It is so good that one is tempted to keep it to oneself, and amaze people with one's wizardry.

    Robyn and I wrote a workbench a while back which might help as a follow-up to David's excellent introduction. The SQLCMD workbench[/url]

    I notice that the SQL Server 2008 version of SQLCMD is very similar to that of SQL Server 2005. Have they improved anything, or is SQLCMD one of those tools they have left to fester?

    Best wishes,
    Phil Factor

  • March 24, 2009 at 6:59 am

    #965127

    good stuff, we're not under the same constraints here but using this we can build a deployment pack and execute it. the way it is now it's a manual process of running different scripts as specified by dev

  • March 24, 2009 at 7:28 am

    #965159

    kuldip.bhatt (3/24/2009)

    how i give the usrename and password in the sql command?

    :setvar Maindbserver "NHSserver"

    :setvar Maindb "NHS_031209"

    :Connect $(Maindbserver)

    go

    $ maindb

    :connect $(Maindbserver) -U YourUserName -P YourPassword

    Personally I would avoid SQL standard security for scripts like the plague. It would mean you have a clear text file with credentials exposed.

    LinkedIn Profile

  • March 24, 2009 at 7:32 am

    #965165

    Great Article, very powerfull, simple and low 'Black box' rating, unlike other apps.

    For our automation, I have wrapped the Commandline version into a VB.NET program. The only problem we see is that I can only output 4000 bytes per script. Sometimes I need to see a top 10 of some table that was just created and 4000 bytes is nothing.

    Any suggestions to get around this limit, or it this a DOS thing?

  • March 24, 2009 at 12:14 pm

    #965481

    Excellent article; I was thinking of doing something similar.

    As I been tasked with automating releases; so once developer submit it it gets executed in proper server/database automatically and sends an email alert saying it's done.

    I was thinking of using SQLCMD and its abilities to achive this. Thanks much sir!

    Mohit.

    [font="Arial"]---

    Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
    Microsoft FTE - SQL Server PFE

    * Some time its the search that counts, not the finding...
    * I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

    How to ask for help .. Read Best Practices here[/url].

  • March 24, 2009 at 1:43 pm

    #965562

    Good stuff. One suggestion, especially since you mention deploying to many servers. Remove the :setvar statements, and declare them externally (probably as environment variables if you're going to run this from SSMS). I've done this with "build" scripts created from Visual Studio. I have a .bat file that executes the scripts via SQLCMD, and cycles through my servers executing each script.

    Using SQLCMD mode from SSMS is nice in that it gives us the opportunity to test the scripts before putting them into production.

  • March 24, 2009 at 4:10 pm

    #965685

    Sounds promising but I seem to be missing something. I have scripts I need to run on all user databases. I've tried using sp_MSforeachDB with "Use DB_ID('?')" and dynamic sql. But since some of my scripts have nested sql statements, it's been a nightmare. I've found a few good articles on dynamic sql and I'm somewhat successful.

    So then I found this article on SQLCMD mode using variables. It's promising. However, I can run a procedure on one database just fine. The err.out file works great.

    However, Does anyone know how to use this to run one or more scripts against many databases on the same server?

    This is what I am using for one DB:

    ***********************

    :setvar SQLInstance "DV-FS49"

    :setvar ScriptDirectory "\\FP49\Database\Scripts\Upgrade to 2005\Preparation scripts\"

    :error $(ScriptDirectory)Script.err

    :out $(ScriptDirectory)Script.out

    :connect $(SQLInstance)

    :setvar DB "BarCodes"

    USE $(DB)

    :r $(ScriptDirectory)CreateFunctionGetObjectDefinition.sql

    ***********************

    Thanks

  • March 24, 2009 at 6:05 pm

    #965741

    Great article. Thank you for providing this information David.

    Your article saved me a great deal of work this morning thanks to the SQLCMD information you provided.

    Regards

    Scott

Viewing 15 posts - 1 through 15 (of 23 total)

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