Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Question Unanswered: Dynamic SQL vs OSQL

    Question for the experts here:

    Is there any advantage of running an SQL statement through osql with the database information over using dynamic sql?

    Example:

    DECLARE @DB Varchar(50)
    DECLARE @SQL Varchar(4000)
    SET @DB = '<nvr_changing_server>.<my_dynamic_db_name>'
    SET @SQL = 'SELECT admissiontype_id AS atype, admissiontype AS atype_desc, start_date, end_date
    INTO tlkAdmitType
    FROM <nvr_changing_server>.<nvr_changing_DB>.dbo.tlkAdm issionTypes'

    exec master..xp_cmdShell 'osql -U sa -P sapwd -S nvr_changing_server -d my_dynamic_db_name -Q @SQL....'

    vs. something like:

    DECLARE @DB Varchar(50)
    DECLARE @SQL Varchar(4000)
    SET @DB = '<nvr_changing_server>.<my_dynamic_db_name>'
    SET @SQL = 'SELECT admissiontype_id AS atype, admissiontype AS atype_desc, start_date, end_date
    INTO ' + @DB +'.dbo.tlkAdmitType
    FROM <nvr_changing_server>.<nvr_changing_DB>.dbo.tlkAdm issionTypes'

    EXEC(@SQL)

    The purpose of all this is...I need to pass a parameter for the DB that I will be inserting into...here we create a new db with a specific name based on quarterly data. We collect, crunch, validate data and ship it. Then when it's old we archive it then eventually delete it.

    I have written a script that makes this quarterly build less painful. In fact I won't have to do it! ...our Sr. Data Analysts will do it now. In order for this beautiful thing (*in my mind anyway*) to work they need to set parameters for which data to pull and where to put it. The DB is scripted into existance and the data is moved into it. So therefore they need to enter the Qtr,Yr and dbname. I have done DSQL before on smaller scripts and I am just curious if the expert pool here can shed some light on this approach. The script will most likely be run in a DTS SQL Task.

    Thanks in advance...R

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I would go for the second option. It will not require you to open up xp_cmdshell to all users (bad security hole). Also, I think the error handling is better with the second option. I am not sure how to get an error back from option 1.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •