Post reply

new to calling procedures or scripts

  • March 13, 2013 at 5:54 pm

    #272107

    WHat is the best way to go around this. I know I can just add the code all in one page but I would rather break it up

    so I have 2 .sql files one called createtable.sql and one called cleantheversion.sql

    here are the scripts

    createtable.sql

    if exists(select 1 from sys.tables where object_id = object_id('dbo.BigTable'))

    drop table BigTable; --Drop SOURCE

    --Create a table

    CREATE TABLE BigTable

    (

    software_name_raw VARCHAR(255),

    software_version_raw VARCHAR(255)

    )

    GO

    --Insert records into Source table

    INSERT INTO BigTable

    VALUES

    ('Microsoft Office 2003','11.0.2.1'),

    ('Microsoft 2003 Office','11.0.3.1'),

    ('Microsoft Office 2003 Professional','12.1.2.1'),

    ('Sun Microsystems','11.0.0.1')

    GO

    SELECT * FROM BigTable as Source_TABLE

    GO

    ??

    can i call the other .sql file somehow

    -----EG Call cleantheversion.sql???????????

    ---------------------

    cleantheversion.sql

    BEGIN TRANSACTION Inner3;

    GO

    update dbo.BigTable set BigTable.Software_Version_Raw =

    case

    when CHARINDEX('.',Software_Version_Raw,0) >0 then

    substring(Software_Version_Raw,0, CHARINDEX('.',Software_Version_Raw,0) )+ '.x'

    when isnumeric(Software_Version_Raw)=1 then

    Software_Version_Raw + '.x'

    else

    Software_Version_Raw end

    go

    COMMIT TRANSACTION Inner3;

  • March 13, 2013 at 11:37 pm

    #1596825

    Hi)

    You can use xp_cmdshell and sqlcmd together to be able to run content of file inside of other sql script

    Before using xp_cmdshell you need to configure server:

    SET NOCOUNT ON

    EXEC master.dbo.sp_configure 'show advanced options', 1

    RECONFIGURE

    EXEC master.dbo.sp_configure 'xp_cmdshell', 1

    RECONFIGURE

    Than you can run command like

    declare @command NVARCHAR(70)

    set @command='sqlcmd -S '+@DBServerName + ' -d ' + @DBName + ' -i ' + @FilePathName

    EXEC master..xp_cmdshell @command

  • March 14, 2013 at 2:18 am

    #1596867

    I would recommend staying clear away from xp_cmdshell. It opens up a huge can of worms from a security and auditing standpoint and accessing the file system or the command line from within T-SQL is just plain bad form in my opinion.

    sqlcmd is a great tool to execute scripts from the command line. You could also use Invoke-SqlCmd within PowerShell to execute the SQL scripts if you had some additional tasks to complete within the OS before or after running them that PowerShell might be useful for.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 14, 2013 at 3:35 pm

    #1597281

    Can sqlcmd be invoked from SSMS

  • March 14, 2013 at 3:41 pm

    #1597282

    Sure. Open a query window and on the Query menu select SQLCMD mode.

    http://msdn.microsoft.com/en-us/library/ms174187(v=sql.105).aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • March 14, 2013 at 5:34 pm

    #1597300

    thanks for that

    enjoy your weekend

  • March 14, 2013 at 6:23 pm

    #1597305

    As for the xp_CmdShell thing, if you're afraid to use it on your system, then you either don't know how to use it correctly or your system isn't secure to begin with.

    If you're not afraid and have given people direct access to use it. even through a proxy, instead of using it only through stored procs, then you should be very afraid. 😉

    In either case, you have some serious and very much needed work to do to lock down your system.

    xp_CmdShell is like any other tool. If you know how to use it correctly (which also means securely), there's no more danger to it than a simple SELECT. Keep in mind than any hacker that gets into your system with "SA" privs can turn it on even if you've turned it off. The real key is to keep people other than System Admins from having more than just "PUBLIC" privs.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 14, 2013 at 7:42 pm

    #1597317

    alan_lynch (3/14/2013)

    thanks for that

    enjoy your weekend

    You're welcome. Thanks, you too.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 8 posts - 1 through 7 (of 7 total)

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