Changing Connections in DTS Packages

I have 4-5 DTS packages with more than 100 connections. If I want to change the connections of the source (Oracle Database) from Server 1 to Server 2, how do I do it dynamically...? I know that I can go to Disconnected Edit and change the connections manually , but it is a very tedious process as there are more than 100 connections.

Thanks.I'm moving this thread to the Microsoft SQL Server Forum where it will get attention from users with more Microsoft experience.

Do you actually have more than 100 connections (the Oracle Connection icon is the gold twin-disk-drives)? I have not EVER seen that happen, and I've seen a lot of Oracle to SQL Server DTS packages!

-PatP|||Pat

Thanks for the reply. If I add up all the connections in 3-4 packages I will have 100 connections.

Let me know if you know any solution.
Thanks.sql

Changing connections (Does't work with "Script Task"...)

Hi,

I've a simple package which basically copies a few record from a Source connection to a Destination connection.
This package must run ten times, every time with a different Source and Destination.

I dont want to duplicate the tasks inside the package and set the right connections at design time. I'm looking for a way to parametrize the connections.

In DTS2000 I could use an "Activex Script" to access the Package's Connections and change them at design time. The new "Script Task" does not allow access to others task, so it can't be done anymore. (There's is still the "Activex Script" task on my toolbox, but i get errors trying to place it on the design surface).

Package Configurations also does not seems the right way: to use multiple configurations I should invoke the package from the command line multiple times with different command line parameters, while I wuold like to have everything in one place (ie 1 package file instead of 1 batch file, 1 package file and 10 package configurations files).

Regards,
CorradoYou are no longer allowed to access task and package properties at runtime and change things. Part of the rational is that it makes for potentially unstable and unpredicatble packages. So you know have a structured way of doing this through configurations and expressions.

Expressions can be set on connection, task and some component properties. Most UI's expose connections, or they are also available in the VS properties grid.

For example you could use an Exec SQL Task to get a recordset of connection information. You could then use the For Each Loop container to crack this, and for each iteration assign the value(s) to a variable. You can then use the variable(s) within an expression on the connection property(s).

Think expressions instead of ActiveX Script Tasks for glue code.

Changing connection transactions to database transactions

Hi there,

I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just likemyTransaction.Rollback() in asp.net.
But what about exceptions? In asp.net, I am used to doing the following:

<code>
Try
'execute commands
myTransaction.Commit()
Catch ex As Exception
Response.Write(ex.Message)
myTransaction.Rollback()
End Try
</code>
Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other thanrollback transaction?

Any help is greatly appreciated

Try these links for most of your needs, the most important is Transaction Save Point, it keeps your transactions from rolling back to the beginning if there is a roll back. If you need to use triggers look into Instead Of trigger it allows you to put your transactions within the trigger. Run a search for Transaction Save Points and Instead Of triggers in the BOL(books online) Hope this helps.

http://www.codeproject.com/database/sqlservertransactions.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_1ub2.asp

|||

Thanks Caddre,

I checked those links and finally figured it out. I have taken the approach to code my own error messages and put them in an output parameter.

One more thing, is there a way to simulate errors during a transaction? At the client side, I could explicitly throw an exception that would be caught. I don't know if this is possible in stored procedures... just want to test the error handling in my stored procedure. Anyone an idea?

|||Transaction stored procs are in a separate class, you either commit or roll back, that is the reason Savepoint is very important, if there is no Savepoint you can roll back to number one from number nineteen in a twenty transaction stored proc. You could test your Savepoints. Run a search on transaction error handling in the BOL(books online). Hope this helps.

Changing connection string to data source

Can I change databases for a SqlDataSource in code behind and still use the edit,inset,delete capabilities of the SQLDataSource control?

yes u can..

for change of connection string

SqlDataSource1.ConnectionString =ConfigurationManager.ConnectionStrings[0].ToString();

or

SqlDataSource1.ConnectionString ="UR connection string";

Changing Connection String programmatically

How can I change the Connection String programmatically?
(WS2005, Windows forms, C#)
I need to change the server name, or Initial Catalog parts of the string.
My connection strings are in a field of a master SQL database table.
The design of the different databases are the same.
How can I pass it to the _connection in the dataset?
_connection and InitConnection() are private.
I know, that I can edit the MyDbDataSet.Designer.cs, but I don't
belive, that it is good solution.
private void InitConnection() {
this._connection = new System.Data.SqlClient.SqlConnection();
this._connection.ConnectionString = "Data
Source=SQL\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated Security="
+
"True";
}
If the Data Source Confoguration Wizard saves the connection string,
the Settings.Designer.cs has also private properties I don't want edit.
Thanks - ZsoltSince if the SQL Server holding your connection strings is renamed or
changed, and you will not be able to get to the connection strings, that
seems like a bad idea.
Why not save the connection string(s) in the application.config file?
If you are storing connnection string information related to additional
servers, and you will use a confirmed static connection to retreive the
stored strings, just put them in a table. You may wish to have different
fields for server, database, security context, etc.. Create a stored
procedure that will provide you the concatenated string when you pass it a
parameter.
Arnie Rowland
"To be successful, your heart must accompany your knowledge."
"Zsolt" <Zsolt@.nowhere.nospam> wrote in message
news:1173DEA0-9696-4C12-8F58-ACA1926D85D8@.microsoft.com...
> How can I change the Connection String programmatically?
> (WS2005, Windows forms, C#)
> I need to change the server name, or Initial Catalog parts of the string.
> My connection strings are in a field of a master SQL database table.
> The design of the different databases are the same.
> How can I pass it to the _connection in the dataset?
> _connection and InitConnection() are private.
> I know, that I can edit the MyDbDataSet.Designer.cs, but I don't
> belive, that it is good solution.
> private void InitConnection() {
> this._connection = new System.Data.SqlClient.SqlConnection();
> this._connection.ConnectionString = "Data
> Source=SQL\\SQLEXPRESS;Initial Catalog=AdventureWorks;Integrated
> Security=" +
> "True";
> }
> If the Data Source Confoguration Wizard saves the connection string,
> the Settings.Designer.cs has also private properties I don't want edit.
> Thanks - Zsolt
>|||Thank you for your answer, Arnie!
The reason for using different databases of the same design is that
every year we will create new database. After creating the new years
database, we will register it in the master database, entering the
necessary connection data in a table. DataSet should use any years
database, but only one at a time.
My problem is that how can I pass it to the DataSet, because it is
private. Of course I can edit the MyDataSet.Designer.cs file,
change the body of the private void InitConnection(), and it works:
private void InitConnection() {
this._connection = new System.Data.SqlClient.SqlConnection();
// insted of:
// this._connection.ConnectionString = "Data Source=SQL\\SQLEXPRESS;Initial
Catalog=Data2006;Integrated Security=True";
// This:
this._connection.ConnectionString = MyConnStrinProvider.GetConnString();
}
But whenever I make some changes on .xsd, the changes above are lost.
Changing of an <auto-generated> file seems not good.
I think there must be better solution then this.
Thank you again,
Zsolt
"Arnie Rowland" wrote:

> Since if the SQL Server holding your connection strings is renamed or
> changed, and you will not be able to get to the connection strings, that
> seems like a bad idea.
> Why not save the connection string(s) in the application.config file?
> If you are storing connnection string information related to additional
> servers, and you will use a confirmed static connection to retreive the
> stored strings, just put them in a table. You may wish to have different
> fields for server, database, security context, etc.. Create a stored
> procedure that will provide you the concatenated string when you pass it a
> parameter.
> --
> Arnie Rowland
> "To be successful, your heart must accompany your knowledge."
>
> "Zsolt" <Zsolt@.nowhere.nospam> wrote in message
> news:1173DEA0-9696-4C12-8F58-ACA1926D85D8@.microsoft.com...
>
>|||This is a good situation for putting the connection string information in th
e app.config file. You will not have to recompile and redeploy the applicati
on each year, only the app.config file.
Add an Application.config file to the solution, and then add the appSettings
section. You can add as many 'keys' as you wish. They are just name=value p
airs that the application can retrieve.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="ConnectionString" value="DATA Source=ServerName;initial catalog=My
Database;integrated security=True" />
</appSettings>
</configuration>
Then in the application use:
for VB: Dim con As SqlConnection = New SqlConnection(System.Configuration.Co
nfigurationSettings.AppSettings("ConnectionString"))
for C#: SqlConnection con = New SqlConnection(System.Configuration.Configura
tionSettings.AppSettings("ConnectionString"))
You may wish to check further in VS Help about using an app.config file.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Zsolt" <Zsolt@.nowhere.nospam> wrote in message news:DD87AECE-A6FF-4C8B-ABA6-B5B80B89D9A6@.mi
crosoft.com...[vbcol=seagreen]
> Thank you for your answer, Arnie!
>
> The reason for using different databases of the same design is that
> every year we will create new database. After creating the new years
> database, we will register it in the master database, entering the
> necessary connection data in a table. DataSet should use any years
> database, but only one at a time.
>
> My problem is that how can I pass it to the DataSet, because it is
> private. Of course I can edit the MyDataSet.Designer.cs file,
> change the body of the private void InitConnection(), and it works:
>
> private void InitConnection() {
> this._connection = new System.Data.SqlClient.SqlConnection();
> // insted of:
> // this._connection.ConnectionString = "Data Source=SQL\\SQLEXPRESS;Initi
al
> Catalog=Data2006;Integrated Security=True";
> // This:
> this._connection.ConnectionString = MyConnStrinProvider.GetConnString()
;
> }
>
> But whenever I make some changes on .xsd, the changes above are lost.
> Changing of an <auto-generated> file seems not good.
>
> I think there must be better solution then this.
>
> Thank you again,
> Zsolt
>
>
> "Arnie Rowland" wrote:
>|||Connection strings of different years (databases) can be stored in app.confi
g
file (among other places). User can select one, using a ComboBox. Ok.
Using the selected connect string, SqlConnection and SqlCommand can be
constructed, OK!
But DataTableAdapter's System.Data.SqlClient.SqlDataAdapter _adapter member
is declared in MyDataset.Designer.cs as private, so I can't access it. That
is my original problem.
Of course, I can edit the MyDataset.Designer.cs file, it compiles and works
well,
but it is not a clear method, and not recommended at all.
Any changes in MyDataset.xsd file overwrites my editing (program is under
construction).
That's why I am looking for better and clearer solution.
Thank you again,
--
Zsolt
"Arnie Rowland" wrote:
[vbcol=seagreen]
> This is a good situation for putting the connection string information in
the app.config file. You will not have to recompile and redeploy the applica
tion each year, only the app.config file.
> Add an Application.config file to the solution, and then add the appSettin
gs section. You can add as many 'keys' as you wish. They are just name=value
pairs that the application can retrieve.
> <?xml version="1.0" encoding="utf-8" ?>
> <configuration>
> <appSettings>
> <add key="ConnectionString" value="DATA Source=ServerName;initial ca
talog=MyDatabase;integrated security=True" />
> </appSettings>
> </configuration>
> Then in the application use:
> for VB: Dim con As SqlConnection = New SqlConnection(System.Configuration.
ConfigurationSettings.AppSettings("ConnectionString"))
> for C#: SqlConnection con = New SqlConnection(System.Configuration.Configu
rationSettings.AppSettings("ConnectionString"))
> You may wish to check further in VS Help about using an app.config file.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Zsolt" <Zsolt@.nowhere.nospam> wrote in message news:DD87AECE-A6FF-4C8B-AB
A6-B5B80B89D9A6@.microsoft.com...

Changing Connection string in multiple packages.

Scenario:

130 dtsx packages using 4 matching connections.
3 of those connections are stored in an SSIS Configuration table in an Operational database.
The last connection is in a shared data source and points to the Operational database so the packages can grab the others.

Problem:

It's time for deployment and all of those connections must change to production servers.
The 3 are no issue, just change the ConfiguredValue in the SSIS Configuration table on the production box to point to the other production servers.
However, the fourth one... I had made an assumption that when you changed a shared data source it filtered down throughout all the packages. We all know what assumptions do to you.... So. I need a way to change all 130 connections (and be able to change ALL packages quickly and simply for other projects in the future)

Solution:

It has been suggested that we use another package to run though all of the packages and change the connection with a script task. I can live with this (and more importantly so can our DBS's who have to deploy).

I have one snippet of code to ADD a connection using a variable holding the connection string, but we dont' want to add one, just change an existing one.

Has anyone else done this? Or had a similar problem and way to fix?

We are likely to have many projects in which the connections MUST change at deployment, and the idea of going into every package to make the change is sad at best... We would be more likely to move back to a competitor's product that has a connection repository, than continue with SSIS.

Ches Weldishofer
ETL Developer
Clear Channel Communications

Ches Weldishofer wrote:

We are likely to have many projects in which the connections MUST change at deployment, and the idea of going into every package to make the change is sad at best... We would be more likely to move back to a competitor's product that has a connection repository, than continue with SSIS.

Alternatively you could use SSIS's built-in mechanism for doing this. i.e. Configurations.

You can share an XML configuration file between many packages. If your connection managers are identically named in each package then there won't be a problem.

-Jamie

|||

Yes we've discussed using an XML Configuration, however... it causes it's own issues. Our production servers are clustered so anything having to point to a file would have to be set up on many many servers.

And the same path would have to exist on all development servers, all developers workstations and all production servers. Maintenance nightmare.

It may be the way we have to go, but... not really a path we want to go down.

|||

Interesting. Are you clustering your servers that run the ETL jobs? Why can you not run all jobs on a single server? That's not rhetoric, I'm interested to know.

-Jamie

|||

Yes they are :)

Both the actual data servers and the SSIS server are clustered (and seperate). The plan is to have all packages run from one cluster so they're easier to find (compared to spread out over 40-50 servers as our dts packages are now) and easier to manage.

We've discussed using a cluster file share, but again we would have to be able to make that work on so many different servers, every developer every development server... oi.

In theory this is all great, but in practice we were hit with the changing of connections.

|||

Right. Any reason why your ETL machine can't be non-clustered but still keep your data servers as clustered? What's the advantage of clustering your ETL server?

Forgive me if I ask daft questions - I don't know much about clustering.

-Jamie

|||Failover, if the SSIS server goes down, then the second one takes over and continues to run the jobs as scheduled.|||

OK gotcha.

I assume you've read this post on clustering: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx

You know that packages are not dependant on the SSIS service in order to run right?

-Jamie

|||

Nope I wouldn't have read it. :) That's an ops thing. But I'll pass it on to our DBA's. I have to code to the environment I,m given.

One thing about the failover is it works even if the entire server is down, not just the Service. Say they want to put SP1 on the ssis box, this way the put failover to the second box, upgrade the first, it then falls back on the first box and they can update the second (including reboots) and we never lose a step.

|||

Which all leads us back to my original problem.

What I'm looking for is the code/syntax to change a connection in an existing package through another package to use as a deployment utility for chaning dev connections to production ones.

Any pointers as to where to look for the syntax would be greatly appreciated.

|||

This might help: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx

-Jamie

|||

Ches,

I have some script tasks in various packages that change connection manager databases on the fly from within a package. Here is the code:

Dim oCon As ConnectionManager
Dim oProp As DtsProperty
oCon = Dts.Connections("ConnectionManagerX")
oProp = oCon.Properties("InitialCatalog")
oProp.SetValue(oCon, Dts.Variables("DatabaseNameVariable").Value.ToString)
Dts.TaskResult = Dts.Results.Success

Obviously, this runs from within the target package and is pulling the database name from a package variable, which isn't quite what you're looking for.

I think you'd be going for something like this:

Const c_sTargetPkg As String = "This is my package"
Const c_sTargetServer As String = "Server1"
Const c_sTargetConnection As String = "Connection A"
Dim oApp As Application = New Application()
Dim oPkg As Package
Dim oCon As ConnectionManager
Dim oProp As DtsProperty

'Get administrative values from package variables
Dim sConnectionName As String = Dts.Variables("TargetConnection").Value.ToString
Dim sNewServerName As String = Dts.Variables("NewServer").Value.ToString
Dim sNewDatabaseName As String = Dts.Variables("NewDatabase").Value.ToString

'Get a handle on the target package and connection manager
oPkg = oApp.LoadFromSqlServer(c_sTargetPkg, c_sTargetServer, "", "", Nothing)
oCon = oPkg.Connections(sConnectionName)

'Change the server name
oProp = oCon.Properties("ServerName")
oProp.SetValue(oCon, sNewServerName)

'Change the database name
oProp = oCon.Properties("InitialCatalog")
oProp.SetValue(oCon, sNewDatabaseName)

'Save the package
oApp.SaveToSqlServer(oPkg, Nothing, c_sTargetServer, Nothing, Nothing)

It uses some constants and some package variables to load up a target package, adjust a particular connection manager's properties and then saves the package back to the target server. I'm sure you could add all kinds of looping or target package lists to this code to have it handle all packages within one run.

Mike Ogilvie
Pendulum, Inc.
http://www.PendulumSolutions.com

|||

Thank you Mike that's much closer to what I'm looking for, I'll start playing with that and repost when I get a final answer. (or more questions which seems likely with SSIS :)

|||I wrote my own vb app to maintain my connection strings in a data table in sql. I have all of my packages querying it and loading the connection strings to variables in memory and Iset them as an expression for the connection strings that I need.|||

Here's the code I finally used, it's inside a dataset enumerated loop to change which ever packages exist in the msdb root by name supplied in a variable (or % for all).

Worked nicely, the dba is much happier with me now that he doesnt' have to change 130 connections.

Dim c_sTargetPkg As String = Dts.Variables("DTSXName").Value.ToString
Const c_sTargetServer As String = "servername"
Const c_sTargetConnection As String = "ConnectionName"
Dim oApp As Application = New Application()
Dim oPKG As Package
Dim oCon As ConnectionManager
Dim oProp As DtsProperty

'Get administratvive values from package variables
Dim sConnectionName As String = Dts.Variables("ConnectionName").Value.ToString
Dim sNewServerName As String = Dts.Variables("NewServerName").Value.ToString
Dim sNewConnectionString As String = Dts.Variables("NewConString").Value.ToString

'Get package and connection manager
oPKG = oApp.LoadFromSqlServer(c_sTargetPkg, c_sTargetServer, "login", "password", Nothing)
oCon = oPKG.Connections(sConnectionName)

'Change Server name
oProp = oCon.Properties("ServerName")
oProp.SetValue(oCon, sNewServerName)
oProp = oCon.Properties("ConnectionString")
oProp.SetValue(oCon, sNewConnectionString)

'Save the packages
oApp.SaveToSqlServer(oPKG, Nothing, c_sTargetServer, "login", "password")

Dts.TaskResult = Dts.Results.Success

sql

changing connection string from one sql server to another using vb .net

I have developed numerous reports in a vb .net app (Windows Database App)
that is
soon to go into production. When I built each report, CR connected to sql
server on my development platform. The connection string is different than
it will be at my client.
How can I change the connection string so that it recognizes my client's
server instead of mine for each of these reports? This is not technically a
sql server question, but I am hoping some of the sql developers who review
these posts will have run into the same issue,
Thanks for any help.
Bernie YaegerConnection String data is generally stored in a centralized location...
1) config File
2) Registry
3) INI
4) Global.ASAX
hope this helps
Greg J
PDX, Oregon

changing connection string from one sql server to another using vb .net

I have developed numerous reports in a vb .net app (Windows Database App)
that is
soon to go into production. When I built each report, CR connected to sql
server on my development platform. The connection string is different than
it will be at my client.
How can I change the connection string so that it recognizes my client's
server instead of mine for each of these reports? This is not technically a
sql server question, but I am hoping some of the sql developers who review
these posts will have run into the same issue,
Thanks for any help.
Bernie YaegerConnection String data is generally stored in a centralized location...
1) config File
2) Registry
3) INI
4) Global.ASAX
hope this helps
Greg J
PDX, Oregon

Changing connection credentials

Hi,
I would like to change the credentials that are used at run time by the
reportserver service to connect to the SQL Server instance.
Can anybody please help me?
Regards!!Cookie =EB=FA=E1:
> Hi,
> I would like to change the credentials that are used at run time by the
> reportserver service to connect to the SQL Server instance.
> Can anybody please help me?
> Regards!!
http://support.microsoft.com/?id=3D306158
Impersonate a Specific User in Code
To impersonate a specific user only when you run a particular section
of code, use the following code:
<%@. Page Language=3D"C#"%>
<%@. Import Namespace =3D "System.Web" %>
<%@. Import Namespace =3D "System.Web.Security" %>
<%@. Import Namespace =3D "System.Security.Principal" %>
<%@. Import Namespace =3D "System.Runtime.InteropServices" %>
<script runat=3Dserver>
public const int LOGON32_LOGON_INTERACTIVE =3D 2;
public const int LOGON32_PROVIDER_DEFAULT =3D 0;
WindowsImpersonationContext impersonationContext;
[DllImport("advapi32.dll")]
public static extern int LogonUserA(String lpszUserName,
String lpszDomain,
String lpszPassword,
int dwLogonType,
int dwLogonProvider,
ref IntPtr phToken);
[DllImport("advapi32.dll", CharSet=3DCharSet.Auto, SetLastError=3Dtrue)]
public static extern int DuplicateToken(IntPtr hToken,
int impersonationLevel,
ref IntPtr hNewToken);
[DllImport("advapi32.dll", CharSet=3DCharSet.Auto, SetLastError=3Dtrue)]
public static extern bool RevertToSelf();
[DllImport("kernel32.dll", CharSet=3DCharSet.Auto)]
public static extern bool CloseHandle(IntPtr handle);
public void Page_Load(Object s, EventArgs e)
{
if(impersonateValidUser("username", "domain", "password"))
{
//Insert your code that runs under the security context of a specific
user here.
undoImpersonation();
}
else
{
//Your impersonation failed. Therefore, include a fail-safe mechanism
here.
}
}
private bool impersonateValidUser(String userName, String domain,
String password)
{
WindowsIdentity tempWindowsIdentity;
IntPtr token =3D IntPtr.Zero;
IntPtr tokenDuplicate =3D IntPtr.Zero;
if(RevertToSelf())
{
if(LogonUserA(userName, domain, password, LOGON32_LOGON_INTERACTIVE,
LOGON32_PROVIDER_DEFAULT, ref token) !=3D 0)
{
if(DuplicateToken(token, 2, ref tokenDuplicate) !=3D 0)
{
tempWindowsIdentity =3D new WindowsIdentity(tokenDuplicate);
impersonationContext =3D tempWindowsIdentity.Impersonate();
if (impersonationContext !=3D null)
{
CloseHandle(token);
CloseHandle(tokenDuplicate);
return true;
}
}
}
}
if(token!=3D IntPtr.Zero)
CloseHandle(token);
if(tokenDuplicate!=3DIntPtr.Zero)
CloseHandle(tokenDuplicate);
return false;
}
private void undoImpersonation()
{
impersonationContext.Undo();
}
</script>

Changing config table location on the fly

Hi everyone,

I am trying to create a proof of concept to show that we can have packages deployed on 3 different servers and all we need to do is tell the package, upon execution/scheduled task, where to go fetch it's configurations from.

The configurations are using SQL Server as the package configuration. This makes it easier for DBA's to maintain since DBA's are responsible for package executions and job scheduling.

For example, the configuration database and all package configurations would be found on server1, server2 and server3 The difference in values is that on Server1, the configuration for the source data and the destination data point to Server1\DatabaseSource and Server1\DatabaseDestination and on server2, the configs point the source to Server2\DatabaseSource and Server2\DatabaseDestination and for server 3, the same thing but pointing to server3.

There is also a connection for the SSIS_Config database we're getting the configurations from. In theory, if we specify a different server where this SSIS_Config database is found, it should override the settings in the package. No?

When I schedule OR execute the package, it's always getting it's configs from the config specified in the package independent of wether I specify it in the Connection Managers of the Execute Package Utility when I manually execute it OR in the data sources tab when I configure the package to be run as a job in SQL Server 2005.

Am I missing something here?

Thanks,

Rob

You use an XML file to set the "config database" location dynamically. You can't use a SQL Server based config table to control where the "config database" resides because that is what you're trying to change.

So, use an XML file, place it on all of the servers, each pointing to the correct "config database" for that environment.|||

Rob,

it sounds like you want to decide, at execution-time, which set of configurations it should use. Right?

So, in essence you need a way of "parameterising" your package so it needs which set of configurations to use. Right?

The best way to do this is to use the /SET option of dtexec.exe

Regards

Jamie

|||

Thats the one!

Thank you Jamie

|||

Jamie,

I've tried using the /SET option but I can't get it to change the set of configurations to use.

This is the command line options I've used, did I miss something?

/FILE "D:\Documents and Settings\forestr\Desktop\Deployment Test\Deployment Test\bin\Deployment\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Connections[b-ncrsql1.SSIS_CONFIG].Properties[ConnectionString]";"Data Source=b-ncrsql1\test;Initial Catalog=SSIS_CONFIG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||

Jamie,

I've tried using the /SET option but I can't get it to change the set of configurations to use.

This is the command line options I've used, did I miss something?

/FILE "D:\Documents and Settings\forestr\Desktop\Deployment Test\Deployment Test\bin\Deployment\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Connections[b-ncrsql1.SSIS_CONFIG].Properties[ConnectionString]";"Data Source=b-ncrsql1\test;Initial Catalog=SSIS_CONFIG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||

Hmmm, I wonder if the order in which things get applied is a problem? Davide Mauri talks a little avout this here: http://weblogs.sqlteam.com/dmauri/archive/2006/04/02/9489.aspx but I don't think it'll help in your case.

Sorry. I'm a bit stumped. Phil...any ideas?

-Jamie

|||

I've tried a different approach.

Storing the source and destination servers in my SSIS_Config tables on all three servers and storing the source server name for the package configurations in an xml file.

After altering between server1, server2 and server3 in the xml config file, the package always goes directly to the initial location set up in the package: server1.

So I'm confused, no matter what method I use, the configuration I tell the package to use is overridden with the initial configuration in the package.

If I edit the package and set the configuration table to point to server 2, that works but now I can't get it pointing to server 1 or server 3.

There's got to be something I am missing... but what?!

|||

r4enterprises wrote:

I've tried a different approach.

Storing the source and destination servers in my SSIS_Config tables on all three servers and storing the source server name for the package configurations in an xml file.

After altering between server1, server2 and server3 in the xml config file, the package always goes directly to the initial location set up in the package: server1.

So I'm confused, no matter what method I use, the configuration I tell the package to use is overridden with the initial configuration in the package.

If I edit the package and set the configuration table to point to server 2, that works but now I can't get it pointing to server 1 or server 3.

There's got to be something I am missing... but what?!

Make sure that you have that config file listed under "Package Configurations" and that you have package configurations enabled. It works, trust me! I use it all of the time. If it doesn't work for you, then something is wrong.

Know that you must use an absolute file path when pointing to the XML config file.|||If you're going to change the connection string of a connection, I'd use the /CONN switch.|||

Phil,

thanks for your insight. the absolute path might be the problem as when I would build the package, I'd go change the config settings of the xml file that is found in the /bin/Deployment directory and not in the project's folder. I never thought that it would reference an absolute path like that... kinda dumb since that would make packages non-movable between locations.

Please note that when you execute a package with the /conn, that didn't seem to work either. its still pointing to its original location.

I'll try a combination of things when I get to that and keep you guys posted.

Thanks for the help,

Robin

|||

r4enterprises wrote:

I never thought that it would reference an absolute path like that... kinda dumb since that would make packages non-movable between locations.

What if the package is stored in SQL Server? What is a "relative path" and what should it point to? This is why absolute paths are required.sql

Changing computer name

Can anyone advise how to change the computer name of a PC
which is running sql client with msde.
I find if I change the computer name sql server will not
start.
Any advise would be appreciated. Thanks
The following works for the default instance:
DECLARE @.old_server_name SYSNAME
DECLARE @.new_server_name SYSNAME
SET @.old_server_name = @.@.SERVERNAME
SET @.new_server_name = <your new computer name>
EXEC sp_dropserver @.server = @.old_server_name
EXEC sp_addserver @.server = @.new_server_name , @.local = 'LOCAL'
UPDATE msdb..sysjobs
SET originating_server = @.new_server_name
WHERE originating_server = @.old_server_name
You have to restart SQL Server (MSDE), and then change the computername, for
it to take effect though.
Jacco Schalkwijk
SQL Server MVP
"Elayne" <pstjw@.hotmail.com> wrote in message
news:c37501c47a33$c0d79090$a401280a@.phx.gbl...
> Can anyone advise how to change the computer name of a PC
> which is running sql client with msde.
> I find if I change the computer name sql server will not
> start.
> Any advise would be appreciated. Thanks
|||I've never experienced the SQL Server service failing to start after the
computer name was changed. What is the error you get when starting the
service from the command line (net start mssqlserver)? Are you able to
start SQL Server as console app by running "sqlservr.exe -c" from the
command line?
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Changing Computer Name

Here's my situation. We have a production SQL server 2000 with about 18
different databases running on it. The lease is up on the server and now i
must roll everything over to the new server. What i need to do is join the
domain with the new server using a different ip address and name than the
current SQL server, get everything installed, test it, then take both servers
down, rename the new server and change it's ip address to what the old one
was, and put it into production. Does anyone know how painful this will be
and what obstacles i need to look out for? Also, if there's a step by step
guide somewhere that'd be pretty great too. The old server has windows 2000
on it, the new server will have windows 2003. Thanks.
See answer to "Moving SQL Server"
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.
|||Double Post. Answered in "Moving SQL Server".
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.
|||"get everything installed"
Install SQL Server, service packs, restore the databases...
"test it"
How are you going to test it? Can you just leave the new server name in
place and change the connection string to connect to the new computer? I
suppose not, or you would not have the question. Anyway, this is the
easiest option.
"rename the new server"
When the box is restarted SQL Server will display an error message on start.
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
If you have jobs defined on your server you might have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.

Changing Computer Name

Here's my situation. We have a production SQL server 2000 with about 18
different databases running on it. The lease is up on the server and now i
must roll everything over to the new server. What i need to do is join the
domain with the new server using a different ip address and name than the
current SQL server, get everything installed, test it, then take both server
s
down, rename the new server and change it's ip address to what the old one
was, and put it into production. Does anyone know how painful this will be
and what obstacles i need to look out for? Also, if there's a step by step
guide somewhere that'd be pretty great too. The old server has windows 2000
on it, the new server will have windows 2003. Thanks.See answer to "Moving SQL Server"
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||Double Post. Answered in "Moving SQL Server".
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||"get everything installed"
Install SQL Server, service packs, restore the databases...
"test it"
How are you going to test it? Can you just leave the new server name in
place and change the connection string to connect to the new computer? I
suppose not, or you would not have the question. Anyway, this is the
easiest option.
"rename the new server"
When the box is restarted SQL Server will display an error message on start.
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
If you have jobs defined on your server you might have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.

Changing Computer Name

Here's my situation. We have a production SQL server 2000 with about 18
different databases running on it. The lease is up on the server and now i
must roll everything over to the new server. What i need to do is join the
domain with the new server using a different ip address and name than the
current SQL server, get everything installed, test it, then take both servers
down, rename the new server and change it's ip address to what the old one
was, and put it into production. Does anyone know how painful this will be
and what obstacles i need to look out for? Also, if there's a step by step
guide somewhere that'd be pretty great too. The old server has windows 2000
on it, the new server will have windows 2003. Thanks.See answer to "Moving SQL Server"
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||Double Post. Answered in "Moving SQL Server".
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||"get everything installed"
Install SQL Server, service packs, restore the databases...
"test it"
How are you going to test it? Can you just leave the new server name in
place and change the connection string to connect to the new computer? I
suppose not, or you would not have the question. Anyway, this is the
easiest option.
"rename the new server"
When the box is restarted SQL Server will display an error message on start.
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
If you have jobs defined on your server you might have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
--
Keith
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.

Changing computer name

how to change windows 2000 computer name with SQL 2000 Server on itSQLserver2K will will rename it self.
SQL7&6.5 you need to run the set up
/ Hasselblad
"Thomas Mcintee" <anonymous@.discussions.microsoft.com> skrev i meddelandet
news:6DA36872-2875-45F8-B971-D909C694AD1D@.microsoft.com...
> how to change windows 2000 computer name with SQL 2000 Server on it|||Hi
To change the name of the server you can change it in My
Computer/Properties/Network Identification. Once this is done you can rename
SQL server see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
John
"Thomas Mcintee" <anonymous@.discussions.microsoft.com> wrote in message
news:6DA36872-2875-45F8-B971-D909C694AD1D@.microsoft.com...
> how to change windows 2000 computer name with SQL 2000 Server on it|||yes I know where to change it but when i change it SQL tells me it can't find my user name it shows oldname\tom not newname\tom even though it is in windows users and if i try to add it it tells me it doesn't exsist
-- John Bell wrote: --
Hi
To change the name of the server you can change it in My
Computer/Properties/Network Identification. Once this is done you can rename
SQL server see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
John
"Thomas Mcintee" <anonymous@.discussions.microsoft.com> wrote in message
news:6DA36872-2875-45F8-B971-D909C694AD1D@.microsoft.com...
> how to change windows 2000 computer name with SQL 2000 Server on it|||I should use, sp_droplogin on master & Sp_dropuser on Userdb and then
recreate them
//Hasselblad
"Thomas McIntee" <anonymous@.discussions.microsoft.com> skrev i meddelandet
news:003A7ECE-7CD7-490F-B6F6-B3DCEC65A4F3@.microsoft.com...
> yes I know where to change it but when i change it SQL tells me it can't
find my user name it shows oldname\tom not newname\tom even though it is in
windows users and if i try to add it it tells me it doesn't exsist
> -- John Bell wrote: --
> Hi
> To change the name of the server you can change it in My
> Computer/Properties/Network Identification. Once this is done you can
rename
> SQL server see
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
> John
> "Thomas Mcintee" <anonymous@.discussions.microsoft.com> wrote in
message
> news:6DA36872-2875-45F8-B971-D909C694AD1D@.microsoft.com...
> > how to change windows 2000 computer name with SQL 2000 Server on it
>
>sql

Changing Compatibility Level on an Upgraded SQL Server 2000 db to 2005 causing delete to b

I have just upgraded my SQL Server 2000 to SQL Server 2005 and from
what I understand the upgrade will do the database conversions for you.
After the install I tried to delete a row from my Item table which is a
foreign key to about 10 other tables. A simple
Delete Item Where ItemID = 888
In SQL Server 2000 it takes about 9secs. In SQL Server 2005 i have yet
to let it run successfully, i stop it after 5mins.
After the upgrade I changed the Compatibility Level to SQL Server 2005
(90) under Database Properties -> Options
After I changed the Compatibiliity Level back to SQL Server 2000 (80)
the query ran in approx 17 secs.
The Item table is about 8000 rows and the size of the tables
referencing the Item table varies. But why would changing the
Compatibility Level cause such a difference in performance? Did I miss
a step in upgrading? From what I understood, changing the Compatibility
Level just allows you to use new SQL Server 2005 features like MARS,
etc...
Please help.
thanks
benblan
UPDATE STATISTICS
REBUILD INDEXES
"blam" <bendlam@.gmail.com> wrote in message
news:1138810049.298299.209110@.g43g2000cwa.googlegroups.com...
>I have just upgraded my SQL Server 2000 to SQL Server 2005 and from
> what I understand the upgrade will do the database conversions for you.
> After the install I tried to delete a row from my Item table which is a
> foreign key to about 10 other tables. A simple
> Delete Item Where ItemID = 888
> In SQL Server 2000 it takes about 9secs. In SQL Server 2005 i have yet
> to let it run successfully, i stop it after 5mins.
> After the upgrade I changed the Compatibility Level to SQL Server 2005
> (90) under Database Properties -> Options
> After I changed the Compatibiliity Level back to SQL Server 2000 (80)
> the query ran in approx 17 secs.
> The Item table is about 8000 rows and the size of the tables
> referencing the Item table varies. But why would changing the
> Compatibility Level cause such a difference in performance? Did I miss
> a step in upgrading? From what I understood, changing the Compatibility
> Level just allows you to use new SQL Server 2005 features like MARS,
> etc...
> Please help.
> thanks
> ben
>|||thanks for the reply,
I did all that last night, rebuilt the indexes, updated the statistics,
etc...this morning the query still didn't run, i had to stop it after
5mins. Then all I did was change the compatibility level to SQL Server
2000 (80) then reran the query and it took 17secs.
Any other suggestions?|||blam wrote:
> thanks for the reply,
> I did all that last night, rebuilt the indexes, updated the statistics,
> etc...this morning the query still didn't run, i had to stop it after
> 5mins. Then all I did was change the compatibility level to SQL Server
> 2000 (80) then reran the query and it took 17secs.
> Any other suggestions?
Check the query plan with both compatibility levels. That may give a clue.|||Can you compare the execution plan with compatibility level 80 with the
excution plan with compatibility level 90
I found different values in the execution plan when i change the
compatibility level between 70 / 80.|||You may need to change your queries/stored procs. After we migrated to SQL
2005, some of the stored procedures slowed down to a crawl, even after
updating the stats and rebuilding the indexes. Due to changes in the database
engine from version to version (and even from service upgrades), the query
plans may change as well. We had do minor changes to the queries and sps in
order to get it back to the way it was performing on SQL 2000.
"gshadow1" wrote:
> Can you compare the execution plan with compatibility level 80 with the
> excution plan with compatibility level 90
> I found different values in the execution plan when i change the
> compatibility level between 70 / 80.
>
>

Changing compatibility level from 65 to 80 - Whats up?

All all!

My first post! I know that with changing compatibility levels from 65 to 80 means changes have to be made to the database tables and stored procedures. The problem Im having is finding out what exactly needs to be changed in the tables and stored procs.

Can anyone help me out...what exactly will I need to change in the tables and the stored procs when I set a database from level 65 to 80??

Thanks!hello,

I'm facing that problem, do you have any experience of this problem? Did you have any issue when you change compatibility level from 65 to 80?
I appreciate if you give me some advices

Thanks and regards,

changing compatability level from80(2000) to 90(2005)

Hi

I am planning to migrate my database from sql 2000 to sql 2005

I need to know when i change the compatibility from 80 to 90

1) What are the things which i need to look out for. If i have changed every thing as per upgrade advisor will i have to make or anticipate further changes.

2) Please tell me whether if i dont use 90 compatability level will i miss out anything?

What am i missing out ?

Will it be possible to do online index re-build with compatability level set to 80.

Is there a list of new benefits/changes i will be getting when i move to sql 2005 compatability set to 90.

Please do reply .Awaiting your answers,....

Thanks in advance.

The differences are all here (table lists them all about half way down the page) - http://msdn2.microsoft.com/en-us/library/ms178653.aspx
Many other things to watch for are here - http://msdn2.microsoft.com/en-us/library/ms143179.aspx

Online indexing operations are supported in 80 compatibility (on SQL Server 2005 Enterprise as that is the only version that supports them).

|||

Thank you for your reply

I want to know whether i wont be able to use any feature of Management studio or any other feature if i am not changing to compatability level 90

Thanks in advance

|||

Couple that I can think of

You can't use Database Diagrams

You can't use custom reports (on the summary tab) - http://sqlblogcasts.com/blogs/sqldbatips/archive/2006/11/22/ssms-custom-reports-backwards-compatibility-or-lack-thereof.aspx

Changing columns name in a temp table

Dear All,

I'm trying to alter the name of several columns' in a table which gets created in a stored procedure.

trying to use:

exec sp_rename '#tblBd.week1', '2007_18', 'COLUMN'

I get:

Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163
Either the parameter @.objname is ambiguous or the claimed @.objtype (COLUMN) is wrong.

There is no mistype, the table name and column name are correct.

Can temp table's column names not be altered?

If yes, how?

Thanks in advance!Hi gorgenyi

Let's cover the obvious stuff first of all to get an idea of what you are doing and why.
1) Why not define your temp table with the correct column names in the first place?
2) Why not alias your column names in the result set you create from the temp table?|||ok, here it goes.

I created a report with a stored procedure which displays resource allocation to projects in the BD pipeline week by week for the next 10 weeks ahead.

the end reult looks like this:

Project name, w1, w2, w3, w4, w5, w6 etc...
Prj A 1 2 2 1
Prj B 1 2 2 2.5 2 2
Prj C .5 1 1 1.5 1
...

I'm using w1, w2, w3 for the weeks ahead during the stored procedure for multiple joins, however I'd like to change them to the always relevant week numbers programatically in the follwoing format '2007-17', '2007-18', '2007-19' ... etc at the end so I can publish these as the datagrid's header.

Did I explain clearly? What would you recomend? Thanks for your help on this.|||You are making the all too common mistake of trying to use SQL and/or Query Analyzer as a reporting environment. It is not designed for this.
What you are trying to do is modify the way the data is displayed, and this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.
Dynamic column names are BAD, because then whatever reporting tool you use will never know what output schema to expect.|||this is a job for Crystal Reports/Active Reports/Reporting Services/MS Access/Excel, or whatever reporting/interface platform you are using.Somehow you conspired to miss with all those I think. I suspect this is just some .NET front end.

You can (I think) dynamically set up your column headers in a datagrid but I am afraid I cannot remember how.

blindman is right though - this is best handled in your front end rather than SQL.|||...or .Net or ASP.|||It just seemed very simple, and must say I do not have experience of the reporting tools. Here is what I did:

temp table to get next 10 weeks ids
temp table to get active BD items and the 10 columns with 0 values
then a double loop where I update each cell with a resource number to the appropriate BD item and the appropriate week number.

Dispay the data in datagrid, with headings for the weeks as Week 1, Week 2, etc. while adding the sums up / week in ASP.NET and do various other things like colouring background etc.

But I'd like to get the actually internal week reference displayed on the grid, which of course chnage weekly, so want to do it dynamicaly. But yes, point taken on ASP.NET won't know what col name they will be.

And yes, this is ASP.NET front end.|||Some ideas from the web. Maybe consider posting in an ASP.NET site if you can't follow them. You defo do not want to do this at the database.
http://www.thescripts.com/forum/thread320779.html
http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/Q_20976549.html

Changing column values on table insert trigger

Hello,

i have a table and if a record is inserted i will test a numeric value
in this table. If the this value is greather as 1 million, than an
status column should be changed from 'A' to 'B'.

Yes and sorry, this is a newbie question.

On Oracle this work simple:

create trigger myTrigger on tableX
as
begin
if :old.x 100000 then
:new.y:='B'
end if;
end;

Thanks
MaikYou can look up SQL Server Books Online to learn how to write a trigger in
t-SQL. You'll need to utilize the inserted/deleted virtual tables to
accomodate multi-row inserts. The update statement will be something along
the lines of:

UPDATE tableX
SET y = B
WHERE EXISTS ( SELECT *
FROM inserted i
WHERE i.key_col = tableX.key_col
AND inserted.x 1000000 ) ;

Here key_col is any column or set of columns that can uniquely identify a
row in tableX.

--
Anith|||Hi,

Quote:

Originally Posted by

i have a table and if a record is inserted i will test a numeric value
in this table. If the this value is greather as 1 million, than an
status column should be changed from 'A' to 'B'.
>
Yes and sorry, this is a newbie question.
>
On Oracle this work simple:
>
create trigger myTrigger on tableX
as
begin
if :old.x 100000 then
:new.y:='B'
end if;
end;

MS SQL Server doesn't have "before action" triggers, so you
have to update the table and use the INSERTED virtual table
to know what records were inserted into the table.

--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.comsql

changing column type

Hi I have transactional replication and I need a tables column data type
changed from char(30) to varchar(40). What would be the best way and least
dangerous to edit this column and get it to replicate the changes succesfully.
thanks for any advice
Sammy
Sammy,
please check out these articles:
http://www.replicationanswers.com/AddColumn.asp
and
http://www.replicationanswers.com/AlterSchema2005.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

changing column type

hi,
i just want the change the type of a column in a table within sql server
2000 enterprise manager. however it warns that,
'MyTable' table
- Warning: One or more existing columns have ANSI_PADDING 'off' and will be
re-created with ANSI_PADDING 'on'.
- Warning: The table was created with ANSI_NULLS 'off' and will be
re-created with ANSI_NULLS 'on'.
i don't want to get those options changed when i change the type of the
column and this is not the only database on the server that we cannot stop
the server to change the configuration options(ANSI_PADDING, ANSI_NULLS) of
the sql server. it seems that we have to use sp_dboption stored procedure to
change those options for the current database. my main intention is not to
deal with query analyzer to execute sql statements, only get the job done
through enterprise manager. isn't it possible through enterprise manager to
change the database options only for the current database? i looked through
the menus may be i missed.
thanksEM goes through a much more complex CREATE/INSERT/DROP table routine =when changing data types. Have you tried changing the column via Query =Analyzer?
I have not tried it...but I am guessing that it might work better via =Query Analyzer
ALTER TABLE x ALTER COLUMN y DesiredDataTypeGoesHere
You can always try to make the change via Enterprise Manager, but make =sure that you script out the changes instead of having it apply them for =you. You should then be able to modify the code as you wish.
-- Keith
"Richard" <rich@.thetop.com> wrote in message =news:eBhBgH4nDHA.2304@.TK2MSFTNGP11.phx.gbl...
> hi,
> i just want the change the type of a column in a table within sql =server
> 2000 enterprise manager. however it warns that,
> > 'MyTable' table
> - Warning: One or more existing columns have ANSI_PADDING 'off' and =will be
> re-created with ANSI_PADDING 'on'.
> - Warning: The table was created with ANSI_NULLS 'off' and will be
> re-created with ANSI_NULLS 'on'.
> > i don't want to get those options changed when i change the type of =the
> column and this is not the only database on the server that we cannot =stop
> the server to change the configuration options(ANSI_PADDING, =ANSI_NULLS) of
> the sql server. it seems that we have to use sp_dboption stored =procedure to
> change those options for the current database. my main intention is =not to
> deal with query analyzer to execute sql statements, only get the job =done
> through enterprise manager. isn't it possible through enterprise =manager to
> change the database options only for the current database? i looked =through
> the menus may be i missed.
> thanks
> >

Changing Column size/type with Derived Column

I have a number of date columns that are parsed as DT_WSTR (6) and I have written a Derived Column converting them into DT_DATE via this (found on the forums) type expression:
(DT_DATE)(SUBSTRING(Date,6,2) + "-" + SUBSTRING(Date,8,2) + "-" + SUBSTRING(Date,1,5))
But I really want to replace the current column, not create a new one. If I use "replace" the data is forced to be a DT_WSTR (6), and I get a truncation error at run-time.
Simeon
Simeon,
You're stuck with it I'm afraid. You can't change the type of a column in the derived column component. Its not a ahrdship to add it as a new column though, jsut don't use the existing one that's all!

-Jamie|||Could you change the source to return a larger column. This would solve your problem.|||Cheers Jamie,
That's what I thought, I was just hoping there was a way to keep it "clean".
On a related thought, I am finding that each time I alter any component near the top of a data flow, I end up needing to delete and re-add most the down stream components due to fields mismatching. Is this why people appear to be building there packages via code?
Simeon.
|||This does depend on the component, some just need double clicking on and the meta data should correct it self, others require you to select the mapped columns. The latter is generally when you change the names of components and inputs.

You shouldn't have to delete components though, I find that surprising.|||

SimonSa wrote:

Could you change the source to return a larger column. This would solve your problem.

That might work, but the derived column sets the type to DT_WSTR, so I'm not sure that putting a entry that is cast to DT_DATE would not upset it also.
|||

I was finding this while I was developing my source component. I had it wired to Raw Files (then later Trash Destinations) with Data Viewers to inspect the data. Running the package (after reloading BI) would give errors, so I found it easier to delete the source and it four outputs, and re-wire.
But going forward I'll try double clicking, and checking the mappings.

Simeon
|||Do you need to cast it to a date? If you do then you will have to have a new column, and the derived column is the best solution|||I would suggest your source component is recreating outputs when it shouldn't. Thus the metadata the downstream components are based on is no longer valid.|||

SimonSa wrote:

I would suggest your source component is recreating outputs when it shouldn't. Thus the metadata the downstream components are based on is no longer valid.

It was. I was slowly adding support for different data types, then adding support for foreign keys. The source component is like a flat file parser, but it handles files that have different rows (with different columns) that have relationships based on order. So really n tables with the foreign keys implied by what a row follows.
I was (for simplicity) developing support incrementally, with the relations setup by a function. The next step is to put that information into a configuration file.
|||A column is identified by it's lineage Id. Deleteing a column and adding it back, even with the same name and same data type properties will cause it to change lineage Id. This means downstream components that have referenced that column (by lineage Id) are now invalid. Opening the UI should bring up the mapping dialog, and one of the options is Map by Name. This normally solves most issues.

A well behaved component will not recreate the output buffer columns each time, but rather detect invalid columns, and remove, add new columns if required, and fix any columns in can detect on both sides, or leave alone matching columns. This can be a pain, as it is lots more code, but try the samples such as the ADO Source for some good template code.

changing column size on publisher side table

Hello,
If I want to change the column size of a table under replication on the
publisher side, can I do this manually? And then manually change the column
size of the corresponding table on the Subscriber side? Or does this require
the use of Replication SP's? If so, what Replication SPs would I need to use
to change a column size?
Thanks,
Rich
Rich - this largely depends on what version of SQL Server you are using:
http://www.replicationanswers.com/AlterSchema2005.asp
http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Thanks for the reply. I am using Sql Server 2000 Standard Edition, and the
table is under Merge Replication. I wasn't clear on if I could manually
change the size. It looks like I need to use the sp_repladdcolumn and
sp_repldropcolumn to accomplish this. I have, in fact, used these SPs to
add a new column. But I am just checking if I need to use them to change the
size of the column. Yes, No, my choice?
Thanks,
Rich
"Paul Ibison" wrote:

> Rich - this largely depends on what version of SQL Server you are using:
> http://www.replicationanswers.com/AlterSchema2005.asp
> http://www.replicationanswers.com/AddColumn.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>

Changing column size in existing databese?

I must increase column (filed) size in existing datebase but without using
Enterprise manager...(Becouse we use MSDE on our clients PCs)
The Filed is part of primary and foreign key constraints...
And every constraint has diferent index number in each database...
for example (PK_something_9e382hjl8), and I don't know how to pick this
value before "drop constraint" command....

Thank you very much...[posted and mailed, please reply in news]

Poted (dario1975@.post.hnet.hr) writes:
> I must increase column (filed) size in existing datebase but without
> using Enterprise manager...(Becouse we use MSDE on our clients PCs) The
> Filed is part of primary and foreign key constraints... And every
> constraint has diferent index number in each database... for example
> (PK_something_9e382hjl8), and I don't know how to pick this value before
> "drop constraint" command....

Moral: name your constraint explicitly according to some standardized
scheme, so you easily can find the names.

This query gives you the name of the PK for a table:

select name from sysobjects
where xtype = 'PK'
and parent_obj = object_id('tablename')

This query lists all referencing foreign key for a table:

select object_name(constid), object_name(fkeyid), col_name(fkeyid, fkey)
from sysforeignkeys
where rkeyid = object_id('depots')
order by constid, fkey

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Changing column order in a table

This subject has been posted several times, but I haven't seen a good
answer.

Problem:
I want to change the order of the columns in a table using T-SQL only.

Explanation:
After running your code, I want to see the following table...

CREATE TABLE [dbo].[TableName] (
[First_Column] [int] NULL ,
[Second_Column] [varchar] (20) NULL
) ON [PRIMARY]

look like this...

CREATE TABLE [dbo].[TableName] (
[Second_Column] [varchar] (20) NULL ,
[First_Column] [int] NULL
) ON [PRIMARY]

Limitations:
Don't post if your post would fall in the following categories:
1. If you don't think it can be done
2. If you think Enterprise Manager is the only way to do this
3. If you think I should just change the order of my Select
statements
4. If you want to state that order column doesn't matter in a
relational database
5. If you want to ask me why I want to do this

Wish:
Hopefully the answer WON'T involve creating a brand new table, moving
the data from old to new, dropping the old table, then renaming the
new table to the old name. Yes, I can do that. The table I'm working
with is extremely huge -- I don't want to do the data juggling.

Thanks in advance!Dan,

I hope this doesn't fall into your 5 commandments ;-) If by any reason the
order of columns is important to you, you can use a view. I do understand
that I'm not supposed to ask you why you want to do this, but why!!? :-) If
you tell us your original problem then you might be lucky enough to find
somebody that can give you some other alternatives. I'm sure you have a
reason for this. Without revealing much information about what you want to
do it's really hard to find the solution.

Good luck,
Shervin

"Dan Newton" <dnewton@.scriptsave.com> wrote in message
news:280c01a4.0312091312.2f52e02@.posting.google.co m...
> This subject has been posted several times, but I haven't seen a good
> answer.
> Problem:
> I want to change the order of the columns in a table using T-SQL only.
> Explanation:
> After running your code, I want to see the following table...
> CREATE TABLE [dbo].[TableName] (
> [First_Column] [int] NULL ,
> [Second_Column] [varchar] (20) NULL
> ) ON [PRIMARY]
> look like this...
> CREATE TABLE [dbo].[TableName] (
> [Second_Column] [varchar] (20) NULL ,
> [First_Column] [int] NULL
> ) ON [PRIMARY]
> Limitations:
> Don't post if your post would fall in the following categories:
> 1. If you don't think it can be done
> 2. If you think Enterprise Manager is the only way to do this
> 3. If you think I should just change the order of my Select
> statements
> 4. If you want to state that order column doesn't matter in a
> relational database
> 5. If you want to ask me why I want to do this
> Wish:
> Hopefully the answer WON'T involve creating a brand new table, moving
> the data from old to new, dropping the old table, then renaming the
> new table to the old name. Yes, I can do that. The table I'm working
> with is extremely huge -- I don't want to do the data juggling.
> Thanks in advance!|||dnewton@.scriptsave.com (Dan Newton) wrote in message news:<280c01a4.0312091312.2f52e02@.posting.google.com>...
> This subject has been posted several times, but I haven't seen a good
> answer.
> Problem:
> I want to change the order of the columns in a table using T-SQL only.
> Explanation:
> After running your code, I want to see the following table...
> CREATE TABLE [dbo].[TableName] (
> [First_Column] [int] NULL ,
> [Second_Column] [varchar] (20) NULL
> ) ON [PRIMARY]
> look like this...
> CREATE TABLE [dbo].[TableName] (
> [Second_Column] [varchar] (20) NULL ,
> [First_Column] [int] NULL
> ) ON [PRIMARY]
> Limitations:
> Don't post if your post would fall in the following categories:
> 1. If you don't think it can be done
> 2. If you think Enterprise Manager is the only way to do this
> 3. If you think I should just change the order of my Select
> statements
> 4. If you want to state that order column doesn't matter in a
> relational database
> 5. If you want to ask me why I want to do this
> Wish:
> Hopefully the answer WON'T involve creating a brand new table, moving
> the data from old to new, dropping the old table, then renaming the
> new table to the old name. Yes, I can do that. The table I'm working
> with is extremely huge -- I don't want to do the data juggling.
> Thanks in advance!

You will have to create a new table, copy the data, then rename it.
This may be tedious, but it will work and it is supported. The only
'short cut' would be to attempt to hack syscolumns, but since many
columns are 'internal use only' and direct updates to system tables
are not supported, you have no guarantee of success, and no support
from Microsoft if you have problems (which may not appear at first).

Or as Shervin suggested, if you can provide more information on what
your issue is, there may be an alternative solution.

Simon

Changing column names in SSMS for SQL Server 2005

I changed the name of a column in the Summary pane of SSMS. I was a bit surprised (but pleased) to see that I could make the change there.

When I tried to use the new column name in a Query, I received the error message, "column not found."

When I used "modify" in the Object Explorer pane of SSMS I was able to see that "(int, not null)" had been appended to the column name in the table.

I don't know if this is a BUG, or an undocumented feature.

Have others here run into this? I am using SS2005, with SP1.

Dan

"int, not null" is not appended to a column name, it is part of the syntax for defining a column. If you can see the column in Object Explorer after you added and saved it you should be able to refer to it in a query.

Service Pack 2 is out ...

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx

|||

lkh,

Yes, I am sorry to say that "(int, not null)" WAS appended to the column name.

When I went into "Modify" in Object Explorer, I found the column name as

[New_name (int, not null)]. The column characteristics were "(int, not null)".

The square brackets were placed there as a result of my editing the name in the Summary pane.

So yes, "(int, not null)" was part of the new name created when I edited the name in the Summary pane.

Dan

Changing Column Names

for a 100% guarentee to pick up any defects due to the
result of an object rename, build a database from the
source code of the database. Due to problems with
sp_depends and the flaws with the information_schema
views, these are no guarentee that you have highlighted
all your potential problems. If you do a build using your
source code, then all errors will show. Of course
building has it's problems as your build order my be an
issue. These are some of the problems that our software
addresses DB Ghost. It can build a database from source
code and it takes care of all dependancies - flagging up
errors within your source code. This is change management
software and answers the question, What happens if I make
a change?
If your interested why not read our white paper on the
subject
http://www.innovartis.co.uk/pdf/Inn...An_Automated_Ap
proach_To_Do_Change_Mgt.pdf
DB Ghost - the only database change management software
for SQL Server on the market today.

>--Original Message--
>Hey folks,
>I have alot of column names in a database that I'd like
to change however
>there are alot of Stored Procedures that reference the
existing names. Is
>there a way to make SQL Server update my Stored
Procedures whenever I change
>a table's column name?
>Thanks!
>
>.
>Will it go so far as telling you that data will fail to be inserted into a
table because the foreign key dependency data is not in the foreign key
table?
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...[vbcol=seagreen]
> for a 100% guarentee to pick up any defects due to the
> result of an object rename, build a database from the
> source code of the database. Due to problems with
> sp_depends and the flaws with the information_schema
> views, these are no guarentee that you have highlighted
> all your potential problems. If you do a build using your
> source code, then all errors will show. Of course
> building has it's problems as your build order my be an
> issue. These are some of the problems that our software
> addresses DB Ghost. It can build a database from source
> code and it takes care of all dependancies - flagging up
> errors within your source code. This is change management
> software and answers the question, What happens if I make
> a change?
> If your interested why not read our white paper on the
> subject
> http://www.innovartis.co.uk/pdf/Inn...An_Automated_Ap
> proach_To_Do_Change_Mgt.pdf
> DB Ghost - the only database change management software
> for SQL Server on the market today.
>
> to change however
> existing names. Is
> Procedures whenever I change|||yes

>--Original Message--
>Will it go so far as telling you that data will fail to
be inserted into a
>table because the foreign key dependency data is not in
the foreign key
>table?
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...
your[vbcol=seagreen]
up[vbcol=seagreen]
management[vbcol=seagreen]
make[vbcol=seagreen]
http://www.innovartis.co.uk/pdf/Inn...An_Automated_Ap[vbcol=seagreen]
like[vbcol=seagreen]
>
>.
>

Changing Column Names

Hey folks,
I have alot of column names in a database that I'd like to change however
there are alot of Stored Procedures that reference the existing names. Is
there a way to make SQL Server update my Stored Procedures whenever I change
a table's column name?
Thanks!No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> Hey folks,
> I have alot of column names in a database that I'd like to change however
> there are alot of Stored Procedures that reference the existing names. Is
> there a way to make SQL Server update my Stored Procedures whenever I
change
> a table's column name?
> Thanks!
>

Changing Column Names

Hey folks,
I have alot of column names in a database that I'd like to change however
there are alot of Stored Procedures that reference the existing names. Is
there a way to make SQL Server update my Stored Procedures whenever I change
a table's column name?
Thanks!No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> Hey folks,
> I have alot of column names in a database that I'd like to change however
> there are alot of Stored Procedures that reference the existing names. Is
> there a way to make SQL Server update my Stored Procedures whenever I
change
> a table's column name?
> Thanks!
>|||for a 100% guarentee to pick up any defects due to the
result of an object rename, build a database from the
source code of the database. Due to problems with
sp_depends and the flaws with the information_schema
views, these are no guarentee that you have highlighted
all your potential problems. If you do a build using your
source code, then all errors will show. Of course
building has it's problems as your build order my be an
issue. These are some of the problems that our software
addresses DB Ghost. It can build a database from source
code and it takes care of all dependancies - flagging up
errors within your source code. This is change management
software and answers the question, What happens if I make
a change?
If your interested why not read our white paper on the
subject
http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Ap
proach_To_Do_Change_Mgt.pdf
DB Ghost - the only database change management software
for SQL Server on the market today.
>--Original Message--
>Hey folks,
>I have alot of column names in a database that I'd like
to change however
>there are alot of Stored Procedures that reference the
existing names. Is
>there a way to make SQL Server update my Stored
Procedures whenever I change
>a table's column name?
>Thanks!
>
>.
>|||Will it go so far as telling you that data will fail to be inserted into a
table because the foreign key dependency data is not in the foreign key
table?
"mark baekdal" <anonymous@.discussions.microsoft.com> wrote in message
news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...
> for a 100% guarentee to pick up any defects due to the
> result of an object rename, build a database from the
> source code of the database. Due to problems with
> sp_depends and the flaws with the information_schema
> views, these are no guarentee that you have highlighted
> all your potential problems. If you do a build using your
> source code, then all errors will show. Of course
> building has it's problems as your build order my be an
> issue. These are some of the problems that our software
> addresses DB Ghost. It can build a database from source
> code and it takes care of all dependancies - flagging up
> errors within your source code. This is change management
> software and answers the question, What happens if I make
> a change?
> If your interested why not read our white paper on the
> subject
> http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Ap
> proach_To_Do_Change_Mgt.pdf
> DB Ghost - the only database change management software
> for SQL Server on the market today.
> >--Original Message--
> >Hey folks,
> >
> >I have alot of column names in a database that I'd like
> to change however
> >there are alot of Stored Procedures that reference the
> existing names. Is
> >there a way to make SQL Server update my Stored
> Procedures whenever I change
> >a table's column name?
> >
> >Thanks!
> >
> >
> >.
> >|||yes
>--Original Message--
>Will it go so far as telling you that data will fail to
be inserted into a
>table because the foreign key dependency data is not in
the foreign key
>table?
>
>"mark baekdal" <anonymous@.discussions.microsoft.com>
wrote in message
>news:063a01c47ad3$a4bfd520$a301280a@.phx.gbl...
>> for a 100% guarentee to pick up any defects due to the
>> result of an object rename, build a database from the
>> source code of the database. Due to problems with
>> sp_depends and the flaws with the information_schema
>> views, these are no guarentee that you have highlighted
>> all your potential problems. If you do a build using
your
>> source code, then all errors will show. Of course
>> building has it's problems as your build order my be an
>> issue. These are some of the problems that our software
>> addresses DB Ghost. It can build a database from source
>> code and it takes care of all dependancies - flagging
up
>> errors within your source code. This is change
management
>> software and answers the question, What happens if I
make
>> a change?
>> If your interested why not read our white paper on the
>> subject
http://www.innovartis.co.uk/pdf/Innovartis_An_Automated_Ap
>> proach_To_Do_Change_Mgt.pdf
>> DB Ghost - the only database change management software
>> for SQL Server on the market today.
>> >--Original Message--
>> >Hey folks,
>> >
>> >I have alot of column names in a database that I'd
like
>> to change however
>> >there are alot of Stored Procedures that reference the
>> existing names. Is
>> >there a way to make SQL Server update my Stored
>> Procedures whenever I change
>> >a table's column name?
>> >
>> >Thanks!
>> >
>> >
>> >.
>> >
>
>.
>sql

Changing Column Names

Hey folks,
I have alot of column names in a database that I'd like to change however
there are alot of Stored Procedures that reference the existing names. Is
there a way to make SQL Server update my Stored Procedures whenever I change
a table's column name?
Thanks!
No, SQL Server will not rewrite your code for you ... why do you think you
can't suppress the warning from sp_rename: "Caution: Changing any part of an
object name could break scripts and stored procedures. The COLUMN was
renamed to 'brap'."?
You should be able to find most stored procedures that rely on the table by
using sp_depends, but because of deferred name resolution and depending on
the order of your script creation, you might not catch all of them.
Assuming it's not a common word like TO or DATE, you can get the list by
scanning ROUTINE_DEFINITION in INFORMATION_SCHEMA.ROUTINES, but this isn't
flawless either.
http://www.aspfaq.com/
(Reverse address to reply.)
"John Smith" <js@.no.com> wrote in message
news:e1epiOkeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> Hey folks,
> I have alot of column names in a database that I'd like to change however
> there are alot of Stored Procedures that reference the existing names. Is
> there a way to make SQL Server update my Stored Procedures whenever I
change
> a table's column name?
> Thanks!
>