DTS Parameterisation
-
I am familiar with the "?" parameter in DTS packages in SQL of the form
select * from client where clientid = ?
The ? variable can then be defined/picked up from, say, an ini file.
What I want to do is build a query of the type
SET @sql = 'select * from mydb.dbo.client'
EXEC(@SQL)
but have the mydb as a variable which I can pick up from the ini file.
Everything I try fails with errors.
Any suggestions?
Thanks.
-
I am having the same problem and would like to know. Thanks.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
-
I did a quick test and one way to get around is to create a proc.
eg.
CREATE PROC dbo.my_test @my_str varchar(500)
AS
exec(@my_str)
GO
1. create a global variable (string type) assign value from ini file.
2. in sql task
exec dbo.my_test ? -- ? = global variable.
Let me know what you think.
-
Personally, I'd just set the properties of the connection to point to the appropriate server and database. Then you don't need to fiddle with dynamic SQL.
If you have to go this path the I'd pass through just the database name, not the whole SQL string.
EG:
CREATE PROC dbo.my_test @my_db sysname AS declare @sql nvarchar(500) SET @sql = 'SELECT * FROM ' + @db + '.dbo.client' EXEC sp_executesql @sql
GO
--------------------
Colt 45 - the original point and click interface
-
How can I pick up a parameter value from an ini file
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
-
Use a Dynamic Properties task.
--------------------
Colt 45 - the original point and click interface -
ini file in this format (not include comments)
[my_var] -- selection = my_var
test_str=select * from sysobjects -- key = test_str
In dynamic property go global variables - > set value (open ini file) --> assign values as shown above.
-
hi
http://www.databasejournal.com/features/mssql/article.php/3073161
hope this helps about how to use ini file
Regards
Urvashi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply