January 4, 2012 at 3:17 am
Hi,
I have a query
SELECT '1355' As JurisId, 'TRA' AS ModCode,ci.atoCitation AS ModIDRef, 'V' AS RecType, 0 AS Seqno, 0 AS VecType
, CASE WHEN txtRegState IS NULL AND SUBSTRING(p.txtNumber, 3, 1)
= '-' THEN RIGHT(txtNumber, LEN(txtNumber) - 3) ELSE txtNumber END AS PlateNbr, CASE WHEN txtRegState IS NULL AND SUBSTRING(p.txtNumber, 3, 1)
= '-' THEN substring(p.txtNumber, 1, 2) ELSE txtRegState END AS State, p.txtVin AS VinNbr, p.txtColorId AS Color1, p.txtMakeId AS Make, p.txtModel AS Model,
p.txtVYear AS VehYear, p.txtRegYr AS ExpYear, p.txtPlateType AS PlateType
FROM dbo.tblCitation AS ci INNER JOIN
dbo.tblIncProperty AS p ON ci.atoCitation = p.lngCitation INNER JOIN
OzaukTippsPWSrcPolice
.dbo.SrcTraCE AS s ON s.CeIDRef = ci.atoCitation
i need to pass the database name OzaukTippsPWSrcPolice dynamically as i am going to execute the package on another environment...
so how can i pass it dynamically?
Help me friends
Thanks,
Charmer
January 4, 2012 at 3:35 am
How is the database name determined? If you're always passing in the same name, that doesn't sound very dynamic. Maybe what you need is to use Package Configurations so that if you ever change to a different database, you can just make a change in the config file or table, without having to alter the package.
What do you mean by "other environment" (different database on same server, or different server)?
John
January 4, 2012 at 3:51 am
John Mitchell-245523 (1/4/2012)
How is the database name determined? If you're always passing in the same name, that doesn't sound very dynamic. Maybe what you need is to use Package Configurations so that if you ever change to a different database, you can just make a change in the config file or table, without having to alter the package.What do you mean by "other environment" (different database on same server, or different server)?
John
yes that is different server....i used the database name as "OzaukTippsPWSrcPolice" to test on my local machine....anyway...the structure of the all tables is going to be the same on that server....but i don't know the database name....since i don't have permissions to log onto their server....so i need to use database name through a variable or by some other way....
if you got some other easiest way to do this...let me know, pls...
Thanks,
Charmer
January 4, 2012 at 3:57 am
Well, if you don't know the database name, you're stuck. Can you not just ask the owner of the server? Once you find out the name of the server, use Package Configurations as I suggested.
John
January 4, 2012 at 4:09 am
John Mitchell-245523 (1/4/2012)
Well, if you don't know the database name, you're stuck. Can you not just ask the owner of the server? Once you find out the name of the server, use Package Configurations as I suggested.John
friend, FYI , i found some link which may let us to achieve this...
http://consultingblogs.emc.com/jamiethomson/archive/2005/12/09/2480.aspx
Thanks,
Charmer
January 4, 2012 at 4:16 am
That won't work. Why do you want to build your SQL statement dynamically? It's the same statement you're going to execute no matter what environment you're in. It's only the server name and database name that will change. Once again, use Package Configurations to pass in the connection string from outside of the package.
John
January 4, 2012 at 4:23 am
John Mitchell-245523 (1/4/2012)
That won't work. Why do you want to build your SQL statement dynamically? It's the same statement you're going to execute no matter what environment you're in. It's only the server name and database name that will change. Once again, use Package Configurations to pass in the connection string from outside of the package.John
friend, my doubt is that i am using this SQL command in OLED source ...so if i am going to execute the package on another server...i hope it will look for the same database name like i mentioned in the sql command where it will be having different name...so it would get error on those situations right?
thats what i am wondering for, buddy? because i was experienced this situation last week...
Thanks,
Charmer
January 4, 2012 at 4:31 am
But it won't look for the database name on the other server - that's the whole point. If you don't know the database name then you can't do anything. That's why you need to speak to the owner of the server and find out. The only execption is if the database happens to be the default database for the login your package will use to connect to the server. And if that's the case, you still don't need to specify the SQL statement dynamically - just leave the database name out of the connection string for the connection manager in Package Configurations.
John
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply