March 24, 2008 at 5:35 am
1) Client_DB on MSSQL has a few views containing client's master data
2) I need to import data from these views into Test_DB master tables (this is the DB for our application)
*Both DBs are on the same machine/server as of now.
Now, my question is:
If I write a select * from Client_DB.dbo.Master_view1 in my import SPs, i'd be hard-coding the client's DB name. Is this right?
I'm obviously expecting them to simply put our application on the live machine later where their DB name might just be different.
What am i to do here? I'm not sure whether I'm completely off track here and am missing out on something very obvious. Do help me, people.
March 24, 2008 at 8:57 am
If your stored procedure is stored IN Client_DB then you do not have to name it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 24, 2008 at 9:01 am
Ooops, my bad. Now I see that you are going DB to DB.
At some point you will have to generate your SQL command as a string replacing 'Client_DB' with the actual name and then use that string as SQL to execute. This can be done either late (Dynamic SQL in your stored procedure) or early (generate new stored procedures once you know the Client_DB name).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 24, 2008 at 10:21 pm
Thanks rbarryyoung 🙂
March 24, 2008 at 10:32 pm
Hi
If u know how to use SSIS then you can import your data with that. You can make the name of the server,db configurable such that they are taken while the SSIS package is executed. This way you need not bother about the servername or the db name.
Other wise follow the dynamic sql method that RBarryYoung was talking about.
"Keep Trying"
March 24, 2008 at 11:19 pm
Yes, Chirag... that's precisely what I've been considering. I'm going to look into this option right away as its a lot more convenient. I have a couple of SPs that were written with cursors for the import of all these masters... I guess I can do without all those.
Thanks.. will keep you updated on my progress 🙂
March 26, 2008 at 1:12 am
Nisha (3/24/2008)
Yes, Chirag... that's precisely what I've been considering. I'm going to look into this option right away as its a lot more convenient. I have a couple of SPs that were written with cursors for the import of all these masters... I guess I can do without all those.Thanks.. will keep you updated on my progress 🙂
All the best 🙂
"Keep Trying"
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply