Easy way to update multiple databases

SQL 2K, Win 2K
My company is in the process of developing a new app that will have a lot of
client specific databases (30 to 50) with identical schemas and stored
procedures. My job as the DBA will be to make the schema and SP changes to
all of these when we have updates.
I have a similar app now that I have a script with several Use Database
commands and I just paste the changes in between the Use Database commands,
but I only have to do this for a few databases.
Does anybody know of a way to automate this with scripts or a tool to apply
the schema and SO change to all user databases on a server.
Thanks.
MikePerhaps DB Ghost would fill your needs.
www.dbghost.com
or
http://www.innovartis.co.uk/Home.aspx
-- Keith
"Mike" <Mike@.Comcast.net> wrote in message =news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> > My company is in the process of developing a new app that will have a =lot of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP =changes to
> all of these when we have updates.
> > I have a similar app now that I have a script with several Use =Database
> commands and I just paste the changes in between the Use Database =commands,
> but I only have to do this for a few databases.
> > Does anybody know of a way to automate this with scripts or a tool to =apply
> the schema and SO change to all user databases on a server.
> > Thanks.
> > Mike
> >|||Hi
If you are using a version control system to maintain your database code.
The most obvious way to upgrade would be use scripts called from osql/isql
or possibly DMO.
John
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||Check out if www.red-gate.com or www.dbghost.com does what you want.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Mike" <Mike@.Comcast.net> wrote in message
news:OUpLTC1uDHA.2180@.TK2MSFTNGP09.phx.gbl...
> SQL 2K, Win 2K
> My company is in the process of developing a new app that will have a lot
of
> client specific databases (30 to 50) with identical schemas and stored
> procedures. My job as the DBA will be to make the schema and SP changes to
> all of these when we have updates.
> I have a similar app now that I have a script with several Use Database
> commands and I just paste the changes in between the Use Database
commands,
> but I only have to do this for a few databases.
> Does anybody know of a way to automate this with scripts or a tool to
apply
> the schema and SO change to all user databases on a server.
> Thanks.
> Mike
>|||I wrote a little vb app that gets a list of db's to apply
changes to. you just paste the SQL syntax (ie "Alter
Table x add constraint y...") and click go, and it runs
the command on all dbs. I populate a simple table w/ the
names of db's to run the command against.
Also, I put together an app that uses a template database
(with the tables, sp's, triggers, that you want), and
pushes the schema out to a list of databases (in my case,
that list is in a table).
If you want the VB code for either, just let me know.
>--Original Message--
>SQL 2K, Win 2K
>My company is in the process of developing a new app that
will have a lot of
>client specific databases (30 to 50) with identical
schemas and stored
>procedures. My job as the DBA will be to make the schema
and SP changes to
>all of these when we have updates.
> I have a similar app now that I have a script with
several Use Database
>commands and I just paste the changes in between the Use
Database commands,
>but I only have to do this for a few databases.
>Does anybody know of a way to automate this with scripts
or a tool to apply
>the schema and SO change to all user databases on a
server.
>Thanks.
>Mike
>
>.
>|||I would appreciate it if you could share the app with me. It doesn't look
like DBGhost or SQL Compare will do it.
Thanks.
Mike
"Gene Daigle" <anonymous@.discussions.microsoft.com> wrote in message
news:024101c3bb73$7b7e3e10$a401280a@.phx.gbl...
> I wrote a little vb app that gets a list of db's to apply
> changes to. you just paste the SQL syntax (ie "Alter
> Table x add constraint y...") and click go, and it runs
> the command on all dbs. I populate a simple table w/ the
> names of db's to run the command against.
> Also, I put together an app that uses a template database
> (with the tables, sp's, triggers, that you want), and
> pushes the schema out to a list of databases (in my case,
> that list is in a table).
> If you want the VB code for either, just let me know.
>
> >--Original Message--
> >SQL 2K, Win 2K
> >
> >My company is in the process of developing a new app that
> will have a lot of
> >client specific databases (30 to 50) with identical
> schemas and stored
> >procedures. My job as the DBA will be to make the schema
> and SP changes to
> >all of these when we have updates.
> >
> > I have a similar app now that I have a script with
> several Use Database
> >commands and I just paste the changes in between the Use
> Database commands,
> >but I only have to do this for a few databases.
> >
> >Does anybody know of a way to automate this with scripts
> or a tool to apply
> >the schema and SO change to all user databases on a
> server.
> >
> >Thanks.
> >
> >Mike
> >
> >
> >.
> >

easy way to list tables & columns?

I have a newbie question about MS SQL EM:
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
BobStep-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software

easy way to list tables & columns?

I have a newbie question about MS SQL EM:
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
Bob
Step-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */
|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software

easy way to list tables & columns?

I have a newbie question about MS SQL EM:
Is there an easy process to list all the tables and columns of a particular
database? I just got this task and I wouldn't know where to start just yet.
What I'd like to see in my newly inherited database servers is a way to
quickly generate a list of a database's tables and columns within those
tables.
Is that too basic? I wouldn't mind seeing step-by-step, if somebody decides
to answer this.
Thanks very much,
BobStep-by-step comments are inline...
USE <your_database_name> /* this is the database for which you want to
see tables and columns */
SELECT sysobjects.name AS tablename /* our tables are listed in the
sysobjects system table (see where clause for filter)*/
, syscolumns.name AS columnname /* our columns are listed in
the syscolumns system table */
FROM sysobjects
INNER JOIN syscolumns
ON sysobjects.id = syscolumns.id /* we use the table id to
identify which columns belong to the table */
WHERE Objectproperty(sysobjects.id,N'IsUserTable') = 1 /* this will
list all the user tables and leave out system tables, stored
procedures, views, etc */
ORDER BY sysobjects.name /* list the tables in alphabetical order */
, syscolumns.name /* list the columns in alphabetical order
within their table */|||Bob wrote:
> I have a newbie question about MS SQL EM:
> Is there an easy process to list all the tables and columns of a
> particular database? I just got this task and I wouldn't know where
> to start just yet.
You can query the INFORMATION_SCHEMA.COLUMNS table for this information.
You can also query the system tables directly in each database:
sysobjects (type = 'U') and syscolumns for the column information.
Some sample ADO code to do this:
http://www.avdf.com/aug98/art_vb006.html
David Gugick - SQL Server MVP
Quest Software

EA - Managment - Process Info

Dear All,
Within this screen I am getting the same person in the
same databases duplicated.
Is this
1. The fault of the application thats creating the
connection
2. SQL Server doing something
3. Something that I shouldn't be worried about.
Thanks
PeterHi,
First one will happen if you are not closing the connection made. This you
need to
really worry and need to rectify inside your code by closing the connection
as soon as the task is completed.
2. SQL Server will create mutiple threads, those you do not want to worry.
It will be cleared automatically
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:16e1401c4487d$676fab60$a301280a@.phx
.gbl...
> Dear All,
> Within this screen I am getting the same person in the
> same databases duplicated.
> Is this
> 1. The fault of the application thats creating the
> connection
> 2. SQL Server doing something
> 3. Something that I shouldn't be worried about.
> Thanks
> Peter|||Thanks Hari
Peter

>--Original Message--
>Hi,
>First one will happen if you are not closing the
connection made. This you
>need to
>really worry and need to rectify inside your code by
closing the connection
>as soon as the task is completed.
>
>2. SQL Server will create mutiple threads, those you do
not want to worry.
>It will be cleared automatically
>Thanks
>Hari
>MCDBA
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
> news:16e1401c4487d$676fab60$a301280a@.phx
.gbl...
>
>.
>

Dynamicaly add charts to report

I need display some processes (lines in a chart) in the report. One process
per chart. The number of processes is not known at the design time and is
defined by the result of the underlying report query, so I do not know how
many chart objects to place on the report at design time. Is it possible to
add charts dynamically to the report during the run time?
Any hint would be greatly appreciated.
Thanks.Maybe this will help!
I create a group (detail) line and I add a chart to this group by defining a
chart in one of the text boxes...
hen do the normal things with a chart - but the chart is automatically
GROUPED for you based upon the results of your query (in line or T-SQL)
"Simon Gold" wrote:
> I need display some processes (lines in a chart) in the report. One process
> per chart. The number of processes is not known at the design time and is
> defined by the result of the underlying report query, so I do not know how
> many chart objects to place on the report at design time. Is it possible to
> add charts dynamically to the report during the run time?
> Any hint would be greatly appreciated.
> Thanks.
>

Dynamically creating cube

Hi guys

I'm investigating whether if its possible to Dynamically create a cube. Then Process this cube before exporting it to a .cub file.

I know that DTS in sqlserver is able to process a cube given at a scheduled time interval. But I'm not sure how I can export a cube to a offline .cub file dynamically. The only way that i know to create a .cub file is via PivotTable in Excel.

Any help in pointing me to the right direction is appreciated.

Thankyou
TomCan you please explain the scenario where you think we may need such a functionality.|||The reason I ask is because I need to generate sales data to various company however each company requries different sets of cube data being generated. Where some should not see the sales figures of another company. As a result i need to generate this dynamic cube and have it filled and exported as a local cube so that it can be downloaded for offline browsing.

any idea how I can achieve this is appreciated?
Thanks
Tom|||Did you ever figure out how to do accomplish this?|||yes, using CREATE CUBE statment, and specify the path and it will create it automatically

Dynamically creating cube

Hi guys

I'm investigating whether if its possible to Dynamically create a cube. Then Process this cube before exporting it to a .cub file.

I know that DTS in sqlserver is able to process a cube given at a scheduled time interval. But I'm not sure how I can export a cube to a offline .cub file dynamically. The only way that i know to create a .cub file is via PivotTable in Excel.

Any help in pointing me to the right direction is appreciated.

Thankyou
TomCan you please explain the scenario where you think we may need such a functionality.|||The reason I ask is because I need to generate sales data to various company however each company requries different sets of cube data being generated. Where some should not see the sales figures of another company. As a result i need to generate this dynamic cube and have it filled and exported as a local cube so that it can be downloaded for offline browsing.

any idea how I can achieve this is appreciated?
Thanks
Tom|||Did you ever figure out how to do accomplish this?|||yes, using CREATE CUBE statment, and specify the path and it will create it automatically

Dynamic Windows Authentication

Hi

The Parent Package has a Loop Container. The Loop Container calls the Child Package and in the process the connection manager properties are changed using package configurations.

When the packages are tested in BIDS both Windows and SQL authentication work.

When the packages are scheduled on SQL authentication works and Windows authentication fails. (The account which executes the schedule has permissions to all the data sources required. The SQL Agent Service Account has permissions. In addition a crendential was created using a different account which also has permissions and this also failed.) If the connection managers are made static then the schedule executes.

Any ideas would be appreciated.

Thanks

Try this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

The error seems to be:

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".

The dynamic windows authentication works interactively in BIDS but when scheduled it fails in the parent and child packages. An expression is used in the parent package and configurations are used in the child package. If the connections are made static the packages execute succesfully.

The KB on Troubleshooting Kerberos Delegation (http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx) alludes to NTLM authentication as being the source for null user problems but why would the static package work and not the dynamic one?

Thanks

Dynamic Windows Authentication

Hi

The Parent Package has a Loop Container. The Loop Container calls the Child Package and in the process the connection manager properties are changed using package configurations.

When the packages are tested in BIDS both Windows and SQL authentication work.

When the packages are scheduled on SQL authentication works and Windows authentication fails. (The account which executes the schedule has permissions to all the data sources required. The SQL Agent Service Account has permissions. In addition a crendential was created using a different account which also has permissions and this also failed.) If the connection managers are made static then the schedule executes.

Any ideas would be appreciated.

Thanks

Try this technique: http://blogs.conchango.com/jamiethomson/archive/2005/10/10/2253.aspx

-Jamie

|||

The error seems to be:

An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host. ". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".

The dynamic windows authentication works interactively in BIDS but when scheduled it fails in the parent and child packages. An expression is used in the parent package and configurations are used in the child package. If the connections are made static the packages execute succesfully.

The KB on Troubleshooting Kerberos Delegation (http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx) alludes to NTLM authentication as being the source for null user problems but why would the static package work and not the dynamic one?

Thanks

Dynamic SQL Question

Hey all,I am trying to write a query against Teradata using a dynamic SQL Statement. Here's what I got:Sub Process(ByVal senderAs Object,ByVal eAs EventArgs)
Dim testAs New TextBox

Dim strSQLAs System.Text.StringBuilder =New StringBuilder("SELECT TELEPHONE, BUSINESS_NAME, TRADESTYLE, SECOND_TRADESTYLE, PHYSICAL_STREET_ADDRES, SECOND_STREET_ADDRESS, PHYSICAL_CITY, PHYSICAL_STATE, MAIL_ADDRESS, MAIL_ADDRESS_2, MAIL_CITY, MAIL_STATE, DUNS_NUMBER , PARENT_DUNS_NUMBER, HEADQUARTERS_DUNS_NUMB, GLOBAL_ULT_DUNS_NUMBER, DOMESTIC_ULT_DUNS_NUMB, PARENT_HQ_NAME, GLOBAL_ULT_BUSINESS_NA, DOMESTIC_ULT_BUSINESS_, FAMILY_UPDATE_DATE FROM VLBS_ECV.DNB_CORE")

If Len(NAME.Text) > 0Then
strSQL.AppendFormat(" WHERE BUSINESS_NAME LIKE'{0}%'", NAME.Text)
End If

If Len(Address.Text) > 0 Then
strSQL.AppendFormat(" {1} PHYSICAL_STREET_ADDRES LIKE'{0}%'", Address.Text, (strSQL.ToString().Contains("WHERE")) & " AND " & "WHERE")

End If


Dim strSQL1As String = strSQL.ToString()
test.Text = strSQL1
Dim connStrAs String ="Dsn=Connection Data"
Dim DBConnectionAs OdbcConnection =New OdbcConnection(connStr)
Dim dsAs New DataSet

Dim tblAdapterAs New OdbcDataAdapter(strSQL1, connStr)
tblAdapter.Fill(ds)
'Assign the datagrid's datasource to the datatable
SearchResults.DataSource = ds
SearchResults.DataBind()
End Sub If I put the name in, I get the query to work. But if I add another line, this is what is getting passed over:"SELECT TELEPHONE, BUSINESS_NAME, TRADESTYLE, SECOND_TRADESTYLE, PHYSICAL_STREET_ADDRES, SECOND_STREET_ADDRESS, PHYSICAL_CITY, PHYSICAL_STATE, MAIL_ADDRESS, MAIL_ADDRESS_2, MAIL_CITY, MAIL_STATE, DUNS_NUMBER , PARENT_DUNS_NUMBER, HEADQUARTERS_DUNS_NUMB, GLOBAL_ULT_DUNS_NUMBER, DOMESTIC_ULT_DUNS_NUMB, PARENT_HQ_NAME, GLOBAL_ULT_BUSINESS_NA, DOMESTIC_ULT_BUSINESS_, FAMILY_UPDATE_DATE FROM VLBS_ECV.DNB_CORE WHERE BUSINESS_NAME LIKE 'california%'True AND WHERE PHYSICAL_STREET_ADDRES LIKE '12345%'"ThatTRUE clause is causing my query to blow up. Anybody have ideas why that is doing this?

Why are you using dynamic SQL anyway? A stored procedure would be much more appropriate, even if it's just to protect your application from SQL Injection.

|||

Because I don't have access in the Warehouse to write stored procs. I have select only. Also, I'm putting some validators on the page to prevent malicious code.

|||

In that case you may be stuck but I'd try to get access if you can otherwise you could leave yourself open to attack, and it may also affect the performance of your query.

As for why you are getting the error, theContainsmethod returns a boolean value based on whether the string exists or not.

|||

Why are you checking the length of the input fields? If the length is 0, LIKE '%' will return true...

cm1jm1:

Because I don't have access in the Warehouse to write stored procs. I have select only. Also, I'm putting some validators on the page to prevent malicious code.

This doesn't mean that u should make an SQL string like this. U should use aparameterized query instead, something like:

"SELECT ... FROM VLBS_ECV.DNB_CORE WHERE BUSINESS_NAME LIKE @.name AND PHYSICAL_STREET_ADDRES LIKE @.address"

Create a command object, set this string as the commandText property, add 2 parameter objects (Values name.Text + "%" AND Address.Text + "%") and thats it

|||

Are some of the address columns really spelled ADDRES and other spelled ADDRESS?

|||

I think you are trying achieve something like below, I've modified your code:

Sub Process(ByVal senderAs Object,ByVal eAs EventArgs)Dim testAs New TextBoxDim strSQLAs System.Text.StringBuilder =New StringBuilder("SELECT TELEPHONE, BUSINESS_NAME, TRADESTYLE, SECOND_TRADESTYLE, PHYSICAL_STREET_ADDRES, SECOND_STREET_ADDRESS, PHYSICAL_CITY, PHYSICAL_STATE, MAIL_ADDRESS, MAIL_ADDRESS_2, MAIL_CITY, MAIL_STATE, DUNS_NUMBER , PARENT_DUNS_NUMBER, HEADQUARTERS_DUNS_NUMB, GLOBAL_ULT_DUNS_NUMBER, DOMESTIC_ULT_DUNS_NUMB, PARENT_HQ_NAME, GLOBAL_ULT_BUSINESS_NA, DOMESTIC_ULT_BUSINESS_, FAMILY_UPDATE_DATE FROM VLBS_ECV.DNB_CORE where 1=1")If Len(NAME.Text) > 0Then strSQL.AppendFormat(" and BUSINESS_NAME LIKE'{0}%'", NAME.Text) End If If Len(Address.Text) > 0 Then strSQL.AppendFormat(" and PHYSICAL_STREET_ADDRES LIKE'{0}%'", Address.Text)End If Dim strSQL1As String = strSQL.ToString() test.Text = strSQL1Dim connStrAs String ="Dsn=Connection Data"Dim DBConnectionAs OdbcConnection =New OdbcConnection(connStr)Dim dsAs New DataSetDim tblAdapterAs New OdbcDataAdapter(strSQL1, connStr) tblAdapter.Fill(ds)'Assign the datagrid's datasource to the datatable SearchResults.DataSource = ds SearchResults.DataBind()End Sub

I've changed the way your query is being generated. See the Where 1=1 in your basic query. Then you can just go on to add the clauses with " and ".

Hope this will help.