Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sahu74
Posting Yak Master
100 Posts |
 Posted - 2005-04-07 : 16:25:33
|
I have the following code to execute in a DTS package. The code in red doesnot execute since the DTS package resides on SERVER1 and the query looks for data from SERVER2. Is there away I can get that value of Fiscal year as a dynamic variable and pass it on to this query. Some sample code will help.Any help will be appreciated.Thank you.PKS.SELECT T1.EMPLID,T1.EMPLNAME,T1.EFFECTIVE_DATEFROM EMPLOYEE.DBO.TBL1 T1WHERE T1.ORGID = 'TT'AND T1.ENTYCODE = 'T'AND T1.FISCAL_YR = (Select Fiscal_Year from SERVER2.Fac.dbo.Fac_Ext_Parms P1 Where P1.Parm_Key = 1) |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-07 : 19:16:39
|
| You can find code examples of dynamic variables over at sqldts.com.But your code should work. Do you get an error? Does SERVER2 exist as a linked server on SERVER1? What happens when you run this code on SERVER1 from within Query Analyzer?Tara |
 |
|
|
sahu74
Posting Yak Master
100 Posts |
Posted - 2005-04-07 : 20:57:03
|
| Tara,The query runs fine on Query Analyzer. I have a correction though. This is the scenario. The DTS package resides on SERVER2 and NOT on SERVER1. I have 2 datasources. The first datasource is connected to SERVER1 which has the Employee database. The workflow has the query I am having trouble with. The workflow connects the 2nd datasource connected to SERVER2. If I have the subquery as it is...the error says that the format SERVER2.Fac.dbo.Fac_Ext_Parms doesnot get parsed.The exact error is "Deferred prepare could not be completed." OLE DB provider 'SERVER2' doesnot contain table ""Fac"."dbo"."Fac_Ext_Parms"". The table either does not exist or the current user doesnot have permission on that table...Could you suggest an alternative. If you think the dynamic variable is the way, could you suggest some links or steps?Thank you.PKS. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-04-08 : 12:39:04
|
| I did suggest a link: sqldts.com. They have an article on how to use dynamic variables.Tara |
|
|
|
sahu74
Posting Yak Master
100 Posts |
Posted - 2005-04-08 : 13:46:35
|
| I have added a global variable 'FY' to the DTS package and used dynamic properties task to set its value. Now, how do I reference a global variable from a query? Can I have some help please?Thank you.PKS. |
|
|
|
karuna
Aged Yak Warrior
582 Posts |
Posted - 2005-04-09 : 03:05:04
|
| Put a ? in your query where you want the global variable. Then select the global variable from the parameter list.Hope this helps.Karunakaran |
|
|
|
rjseker
Starting Member
2 Posts |
Posted - 2005-04-09 : 13:06:58
|
| Hi,There is a task named dynamic Propieties task. The you can set up anything you want in the DTS. Create the Global Variable for the Year, and in the list look for it, double click and then select in the Source ComboBox: Query.Here you can put a Connection that is pointing to the other server and then do your query, and the result of the query is the new value of the Global Variable |
|
|
|
|
|
|
Passing dynamic variable in a DTS