SQL Server name changed

Question

SQL Server name changed

raj-247939

SSCertifiable

Points: 5999
More actions
October 15, 2005 at 4:19 am

#67142

Hi,
One of my company client has changed there database name.Now the problem is ,
we have 45 DTS packages for diffrent perpose asn they use sql server connection .
As the database name is changed these DTS packages are not working as they use old database name.
Is there any script or process frm which i can change the source sql server database name in the dts pacakges.
and also do re-mapping of fields.
Hopu u understand .
Thanx in advance.

from
Killer

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Anatol Romanov Old Hand Points: 349 More actions · Answer 1

Dear admins,

Please do something to improve usability if these forums! I just wasted 20 min typing a detailed answer to this post to find out the post was not saved after I pressed the Post button! It is either a timeout or something else

This happened to me several times and I find it really frustrating.

Regards,

Anatol Romanov

philcart SSC-Forever Points: 47794 More actions · Answer 2

Anatol Romanov, it's the session timeout. Given the number of users accessing the system I wouldn't expect the SQLServerCentral guys to hold onto a session for 20 minutes. Just imagine the overhead that this would create.

I'd suggest you take the approach of many on this site and get into the habit of copying your post before pressing submit. Even if it's just a short reply, bettter to be safe than sorry.

raj, not knowing how the packages are setup, your best method to make sure nothing is missed is manually save each package to a VB file. Edit the file making changes where appropriate.

In the generated file there is a line to execute the package and a commented out line for saving the package. To save the changed package back to the server uncomment the "save" line and comment out the "execute" line.

Once you have made the changes and got your packages working again, get the programmers to re-design the packages so that this doesn't happen again. This article is a good step in the right direction,

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_busintbpwithdts.asp

--------------------
Colt 45 - the original point and click interface

swapnil pattewar Grasshopper Points: 13 More actions · Answer 3

Raj,

While in the design mode of the DTS package you can set the server name to any development server you want and can test the package by running the package in the design mode. To change the server name and variable (like filenames, properties etc) you can use dynamic properties object in the DTS. To use the dynamic properties you need to define global variable first and then set the value of the required items in the dts package to these global variables using the dynamic properties object.

From the front end you can pass values to these global variable.

1) Through VB.net : by referring to these in the code and setting the values.

2) Through stored procedure : run the DTSRun.exe using the xpcmdexec procedure and passing the values of the global variables.

let me know if you want any more details.

raj-247939 SSCertifiable Points: 5999 More actions · Answer 4

HI,

The project i am working is bit typical for me.

Few days back one of the DBA chnaged the database name not thinking about the pros and cons.

Now the director of the company is saying to keep the new name for the database.

We have 120 procedures for diffrent process and 65 DTS packages.We have 5 OLAP server and they have more DTS packages.and jobs.

now if we strat manually then it will take long time.

So i need a way easy way.

I no i have to end up with manually.

But for future i dont want to repeat the problem.

from

Killer

philcart SSC-Forever Points: 47794 More actions · Answer 5

I'm presuming you have stored procedures in one database that reference data in another database. There is really no way to avoid some editing when the database name changes. You could have the stored procedures reference views, but that would just shift the editing to the view. Best and easiest method is to generate a script file for all the stored procedures. open the file in Query Analyzer and do a Find+Replace, then run the script to re-create the procedures in the database.

For the DTS packages, as mentioned above, the best way is to save each of the packages to a VB file and do the same Find+Replace operation within those files. Then uncomment the line to save the package back to the server.

If the DTS packages are designed to be dynamic, using things like INI files, global variables, etc... then you wouldn't need to do any editing.

The article in the link I provided outlines a number of best practices particularly the section headed "Metadata-Driven Approach"

I've also put together a couple of blog entries on how DTS packages are setup were I work,

http://sqlservercentral.com/cs/blogs/phil_cart/archive/2005/07/18/54.aspx

http://sqlservercentral.com/cs/blogs/phil_cart/archive/2005/07/19/59.aspx

One of these days when I have some more spare time at work to sit down and write more than a few paragraphs I'll get to finish the series off.

--------------------
Colt 45 - the original point and click interface

raj-247939 SSCertifiable Points: 5999 More actions · Answer 6

Thanx Phill,

I will edit my DTS pacakeges to be dynamic now.

Thanx once again.

It is too much for me to do this stup.editing

From

Killer