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.tlkAdmissionTypes'
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.tlkAdmissionTypes'
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...RI 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.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment