Access databases using ADO.Net

Hi
I am using Visual Studio.Net 2003 and SQL 2000
Web applications run using the ASPNET user account.

I have to set up this account and grant it permissions before my Web application will have access to a SQL database.
How do I grant permissions ?.

There are two permissions in SQL Server the Database permissions in the database and the Server permissions under security in Enterprise manager. If you need more help post again. Hope this helps.

access data from another sqlserver d.b

I have two databases on the same server and would like to access data from
both. In oracle there is a 'dblink' function that allows you to do this. Wha
t
is the procedure in sqlserver ?
DonIf its the same server and the same instance you can use the epoint
notation, naming the object sas followed:
Databasename.Owner.ObjectsName
If you want to connect to a remote server you can also expand this to:
Servername.Databasename.Owner.ObjectsName
but therefore you have to configure a linked server.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"don" <don@.discussions.microsoft.com> schrieb im Newsbeitrag
news:D2D6750B-DF74-4B41-B578-864258273034@.microsoft.com...
>I have two databases on the same server and would like to access data
>from
> both. In oracle there is a 'dblink' function that allows you to do this.
> What
> is the procedure in sqlserver ?
> Don|||Assuming two databases in the same SQL Server instance: just qualify with th
e database name:
SELECT ...
FROM loctbl INNER JOIN otherdb.dbo.othertbl
...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"don" <don@.discussions.microsoft.com> wrote in message
news:D2D6750B-DF74-4B41-B578-864258273034@.microsoft.com...
>I have two databases on the same server and would like to access data fro
m
> both. In oracle there is a 'dblink' function that allows you to do this. W
hat
> is the procedure in sqlserver ?
> Don

Access cannot see MSDE when network cable disonnected

When my users are in the field and try to switch databases the
connection tool cannot see any servers even thought I know they are
there. The data link properties dialog does not see them. Also my data
entry program also cannot see them but is using sqldmo to list the
servers. However my SQL Service manager (inside my task bar) Can see
them. Any pointers on how to let the access project or the sqldmo
objects see the servers when they are disconnected. I even tried using
ODBC with no luck.
Thanks for any help.
Shane
Hello,
Personnaly, I use the following
Dim ListSvr As NameList
Set ListSvr = SQLDMO.ListAvailableSQLServers
' I add the servers to a collection after removing the ones I don't want
to show
' After that,
Set oServer = New SQLDMO.SQLServer2
Set ListSvr = oServer.ListInstalledInstances
' This function always work but only for the local instances (named or not)
' I add the needed servers to the other collection but I don't put the
servers already listed before
Marc Allard
Allcomp
Shane Lim wrote:
> When my users are in the field and try to switch databases the
> connection tool cannot see any servers even thought I know they are
> there. The data link properties dialog does not see them. Also my data
> entry program also cannot see them but is using sqldmo to list the
> servers. However my SQL Service manager (inside my task bar) Can see
> them. Any pointers on how to let the access project or the sqldmo
> objects see the servers when they are disconnected. I even tried using
> ODBC with no luck.
> Thanks for any help.
> Shane
>
|||That is PERFECT!!
On Fri, 25 Feb 2005 10:39:39 +0100, Allcomp <marc@.nospam.allcomp.be>
wrote:
[vbcol=seagreen]
>Hello,
>Personnaly, I use the following
>Dim ListSvr As NameList
>Set ListSvr = SQLDMO.ListAvailableSQLServers
>' I add the servers to a collection after removing the ones I don't want
>to show
>' After that,
>Set oServer = New SQLDMO.SQLServer2
>Set ListSvr = oServer.ListInstalledInstances
>' This function always work but only for the local instances (named or not)
>' I add the needed servers to the other collection but I don't put the
>servers already listed before
>Marc Allard
>Allcomp
>
>Shane Lim wrote:

Access and MSDE

how to i can connect the access database and the msde server?
i try whit the "odbc databases" function of access 2000 but not work
sql state 01000
errore di sqlserver
10060 impossibile effettuare la connessione
sql state 08001
errore di sqlserver 17
sorry for my english... ;(
IgnazioC
hi Ignazio,
IgnazioC wrote:
> how to i can connect the access database and the msde server?
> i try whit the "odbc databases" function of access 2000 but not work
> sql state 01000
> errore di sqlserver
> 10060 impossibile effettuare la connessione
> sql state 08001
> errore di sqlserver 17
10060 is a timeout problem. Make sure that you don't have firewall issues
between the client and server. Even personal firewalls on the Server will
block this traffic by default.
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||before it worked,but to the unexpected one it did not work more.
I have changed the name of the pc and all now it works.
why happens this? my name of the pc are "locked" on msde sever? as I
can unlock it?
|||hi Ignazio,
IgnazioC wrote:
> before it worked,but to the unexpected one it did not work more.
> I have changed the name of the pc and all now it works.
> why happens this? my name of the pc are "locked" on msde sever? as I
> can unlock it?
as regard MSDE by it self, there's no problem at all... this was a problem
with SQL Server 7.0/MSDE 1.0 but it is no longer the case...
you can have now problems regarding ie: the registration on Enterprise
Manager, and, re-registering servers on Enterprise Manager UI will solve EM
problems..
or, of course, net problems due to not resolved computer names for firewall
as well...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
sql

Access 97 to SQL 2000

Were looking to migrate all our Access 97 databases to SQL 2000, so this is the scenerio. We want to use Access 97 as the front end and SQL 2000 as the backend (Which will hold the tables, stored procedures and security). We have approximataley 400 users using Windows 2000 Pro and all using Novell 5.1 as our file servers. So we are not authenticating to an NT environment but rather to SQL Server, we only hold 2 CPU license and don't really want to use ODBC.

My questions:

What is the best method for Access 97 to connect to SQL 2000?

Can someone provide me an example of the method of doing the connection to the SQL 2000 in Access 97?

I understand it complex to have all your databases seperately for relationships and its easier to have then all in one database, what's your opinion?

Any opinions and ideas would greatly be appreciated, I really don't want to use Access '97 but its not my call. New project and they want me to make it work, I'm good with Access and have never really used SQL.You have to use the linked table to SQL Server via ODBC connection if you want to use ACCESS as fronted end. You also can execute pass-through query to access SQL Server tables too.

If you know how to programming VB to access database, there is no much difference between accessing ACCESS database or SQL Server database. You can use ADO, DAO or OLEDB to connect and access SQL Server from Modules in Access.

In order to handle about 400 users, you can consider to use SQL Server database roles and application role.

access 2003 to SQL server migration

We are migrating one of our clients databases from an access 2003 database to their sql server 2000 database being that i have mostly worked with oracle datbases I had come up with a list of things that we were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not insert update or delete from any exisitng table and any new tables without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have them promoted to DBO or can you only do that with stored procedures
1. You can make them a member of the datareader role and then they have access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your production server anyway.
Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" <garytNOSPAM@.spatialmapping.com> wrote in message news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 database to their sql server 2000 database being that i have mostly worked with oracle datbases I had come up with a list of things that we were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not insert update or delete from any exisitng table and any new tables without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have them promoted to DBO or can you only do that with stored procedures
|||makes sense on #2 i jsut wanted to rule it out because i'm sure its going to get asked of me.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:%23JvZHzD$FHA.328@.TK2MSFTNGP14.phx.gbl...
1. You can make them a member of the datareader role and then they have access to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your production server anyway.
Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" <garytNOSPAM@.spatialmapping.com> wrote in message news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 database to their sql server 2000 database being that i have mostly worked with oracle datbases I had come up with a list of things that we were going to have to implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not insert update or delete from any exisitng table and any new tables without having to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have them promoted to DBO or can you only do that with stored procedures

access 2003 to SQL server migration

We are migrating one of our clients databases from an access 2003 database t
o their sql server 2000 database being that i have mostly worked with oracle
datbases I had come up with a list of things that we were going to have to
implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not ins
ert update or delete from any exisitng table and any new tables without havi
ng to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have
them promoted to DBO or can you only do that with stored procedures1. You can make them a member of the datareader role and then they have acc
ess to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your production
server anyway.
--
Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" <garytNOSPAM@.spatialmapping.com> wrot
e in message news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 database t
o their sql server 2000 database being that i have mostly worked with oracle
datbases I had come up with a list of things that we were going to have to
implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not ins
ert update or delete from any exisitng table and any new tables without havi
ng to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have
them promoted to DBO or can you only do that with stored procedures|||makes sense on #2 i jsut wanted to rule it out because i'm sure its going to
get asked of me.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message news:%23JvZ
HzD$FHA.328@.TK2MSFTNGP14.phx.gbl...
1. You can make them a member of the datareader role and then they have acc
ess to read all tables but nothing else.
2. Basically no. Development and QA should not be done on your production
server anyway.
--
Andrew J. Kelly SQL MVP
"Gary Townsend (Spatial Mapping Ltd.)" <garytNOSPAM@.spatialmapping.com> wrot
e in message news:ZA0mf.139758$y_1.98475@.edtnps89...
We are migrating one of our clients databases from an access 2003 database t
o their sql server 2000 database being that i have mostly worked with oracle
datbases I had come up with a list of things that we were going to have to
implement but i had some questions.
1.. can you set it so certain roles can only select from a table and not ins
ert update or delete from any exisitng table and any new tables without havi
ng to do it for each individual new table
2.. Can users create tables under thier user and then once it passes QA have
them promoted to DBO or can you only do that with stored procedures

Abstracting cross-databases references with views

On our SQL Server, the databases reference each other quite often. eg. UPDAT
E
SalesDatabase..DailySales SET Flag = 1. We are planning to change that so
that all references to other databases are done through views, because we
want to abstract and centralise all the cross-database references.
So we'll end up with
CREATE View as SELECT * FROM SalesDatabase..DailySales
UPDATE DailySales SET Flag = 1
My question is this. By going through a view, will this have any impact on
performance ?
Thanks, CraigGraig
Generally speaking views are not about performance. Views are about
security. If a performance is important to you I'd suggest to use a stored
procedure instead and make sure that you jave properly defined indexes on
the table.
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:B6A50E38-BCF3-44F1-9179-DE653A340655@.microsoft.com...
> On our SQL Server, the databases reference each other quite often. eg.
> UPDATE
> SalesDatabase..DailySales SET Flag = 1. We are planning to change that so
> that all references to other databases are done through views, because we
> want to abstract and centralise all the cross-database references.
> So we'll end up with
> CREATE View as SELECT * FROM SalesDatabase..DailySales
> UPDATE DailySales SET Flag = 1
> My question is this. By going through a view, will this have any impact on
> performance ?
> Thanks, Craig|||I do use stored procedures and all the tables are indexed.
What I'm not sure about is whether performance will be affected if instead
of selecting / updating / deleting from a table that exists in another
database, I select / update / delete from a local view that selects from tha
t
table in the other database. The SQL I'm talking about would be in a stored
procedure.
BEFORE:
create proc Proc1 as UPDATE OtherDatabase..Sales SET Flag = 1
AFTER:
create view Sales as SELECT ProductID, SalesQty, SalesAmt, Flag FROM
OtherDatabase..Sales
create proc Proc1 as UPDATE Sales SET Flag = 1
The reason for doing is specific to our applications and that is not what I
am asking. What I need to know is whether doing this will affect perfomance.
An extra question is : would the stored procedure (that calls the local view
that calls the table in the other database) be able to use the indexes in th
e
table or would the view in between prevent that from happening.
Any help would be greatly appreciated,
Craig

Absolutely new to databases and incharge of creating one..Please help

Hi,
We have never worked on databases and are currently developing our
vb.net windows application where we want to use some database to store
data. Our application will be geared towards desktop users (single
user using our app and saving data on his computer), a group of users
(who share data stored at a central location) and should be scalable
to support 100 users or so in future.
I think I will have to use SQL Server 2000 but what should I do about
desktop clients who do not have sql server.. can i create the database
in sql server 2000 and then ship it using msde.. Is it possible. Also
we are a small company with simple win xp pro machines.. so I cannot
use enterprise and developer editions of sql server (as they need
server machines to install). So can I install the developer edition
of sql server 2000, create database, manage it etc using the gui tools
and then ship out the app distributing msde for desktop users.. does
it make sense, is it possible. I have enterprise architect version of
vs.net.
Please throw some light
Thanks
nnch> we are a small company with simple win xp pro machines.. so I cannot
> use enterprise and developer editions of sql server (as they need
> server machines to install).
SQL Server Developer Edition runs on XP Pro and is licensed for development
and testing.
> So can I install the developer edition
> of sql server 2000, create database, manage it etc using the gui tools
> and then ship out the app distributing msde for desktop users.. does
> it make sense, is it possible. I have enterprise architect version of
> vs.net.
You should be fine with this, although keep in mind that a desktop edition
database is limited to 2GB.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Developer" <nnch97@.hotmail.com> wrote in message
news:e83edb5a.0401301342.578b9285@.posting.google.com...
> Hi,
> We have never worked on databases and are currently developing our
> vb.net windows application where we want to use some database to store
> data. Our application will be geared towards desktop users (single
> user using our app and saving data on his computer), a group of users
> (who share data stored at a central location) and should be scalable
> to support 100 users or so in future.
> I think I will have to use SQL Server 2000 but what should I do about
> desktop clients who do not have sql server.. can i create the database
> in sql server 2000 and then ship it using msde.. Is it possible. Also
> we are a small company with simple win xp pro machines.. so I cannot
> use enterprise and developer editions of sql server (as they need
> server machines to install). So can I install the developer edition
> of sql server 2000, create database, manage it etc using the gui tools
> and then ship out the app distributing msde for desktop users.. does
> it make sense, is it possible. I have enterprise architect version of
> vs.net.
> Please throw some light
> Thanks
> nnch

Absolutely new to databases and incharge of creating one..Please help

Hi,
We have never worked on databases and are currently developing our
vb.net windows application where we want to use some database to store
data. Our application will be geared towards desktop users (single
user using our app and saving data on his computer), a group of users
(who share data stored at a central location) and should be scalable
to support 100 users or so in future.
I think I will have to use SQL Server 2000 but what should I do about
desktop clients who do not have sql server.. can i create the database
in sql server 2000 and then ship it using msde.. Is it possible. Also
we are a small company with simple win xp pro machines.. so I cannot
use enterprise and developer editions of sql server (as they need
server machines to install). So can I install the developer edition
of sql server 2000, create database, manage it etc using the gui tools
and then ship out the app distributing msde for desktop users.. does
it make sense, is it possible. I have enterprise architect version of
vs.net.
Please throw some light
Thanks
nnch> we are a small company with simple win xp pro machines.. so I cannot

quote:

---

> use enterprise and developer editions of sql server (as they need
> server machines to install).

---

SQL Server Developer Edition runs on XP Pro and is licensed for development
and testing.

quote:

---

> So can I install the developer edition
> of sql server 2000, create database, manage it etc using the gui tools
> and then ship out the app distributing msde for desktop users.. does
> it make sense, is it possible. I have enterprise architect version of
> vs.net.

---

You should be fine with this, although keep in mind that a desktop edition
database is limited to 2GB.
Hope this helps.
Dan Guzman
SQL Server MVP
"Developer" <nnch97@.hotmail.com> wrote in message
news:e83edb5a.0401301342.578b9285@.posting.google.com...

quote:

---

> Hi,
> We have never worked on databases and are currently developing our
> vb.net windows application where we want to use some database to store
> data. Our application will be geared towards desktop users (single
> user using our app and saving data on his computer), a group of users
> (who share data stored at a central location) and should be scalable
> to support 100 users or so in future.
> I think I will have to use SQL Server 2000 but what should I do about
> desktop clients who do not have sql server.. can i create the database
> in sql server 2000 and then ship it using msde.. Is it possible. Also
> we are a small company with simple win xp pro machines.. so I cannot
> use enterprise and developer editions of sql server (as they need
> server machines to install). So can I install the developer edition
> of sql server 2000, create database, manage it etc using the gui tools
> and then ship out the app distributing msde for desktop users.. does
> it make sense, is it possible. I have enterprise architect version of
> vs.net.
> Please throw some light
> Thanks
> nnch

---

about transaction replication...

Hi everybody,
I am new to SQL replication , I use MS SQL 2000 with
SP3 . I use databases of a financial program which has 360 tables. But I
receive that error when replicating data :
Could not drop object 'dbo.TBLSUBELER' because it is referenced by a FOREIGN
KEY constraint.
(Source: UGUR (Data source); Error number: 3726)
Why this error occurs and what can i do ?
Best regards...
This error is caused because sysdepends is out of sync on your publisher.
All RDBMS's have this problem.
The best thing to do is to pre create your schema, save it to a script and
execute it as part of a pre snapshot command.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Uur Ekinci" <ugur@.argeset.com> wrote in message
news:uHgOmTbHFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi everybody,
> I am new to SQL replication , I use MS SQL 2000 with
> SP3 . I use databases of a financial program which has 360 tables. But I
> receive that error when replicating data :
> Could not drop object 'dbo.TBLSUBELER' because it is referenced by a
FOREIGN
> KEY constraint.
> (Source: UGUR (Data source); Error number: 3726)
> Why this error occurs and what can i do ?
> Best regards...
>
|||At what stage during the replication process are you getting this error?
This error is occurring because the specified table is being deleted, but
another table has a foreign key defined on it.
You can get this problem when re-applying the snapshot or running the agent
after the subscription has been reinitialised (which is also re-applying the
snapshot incidentally).
Raj Moloye
"Uur Ekinci" <ugur@.argeset.com> wrote in message
news:uHgOmTbHFHA.2648@.TK2MSFTNGP14.phx.gbl...
> Hi everybody,
> I am new to SQL replication , I use MS SQL 2000 with
> SP3 . I use databases of a financial program which has 360 tables. But I
> receive that error when replicating data :
> Could not drop object 'dbo.TBLSUBELER' because it is referenced by a
> FOREIGN KEY constraint.
> (Source: UGUR (Data source); Error number: 3726)
> Why this error occurs and what can i do ?
> Best regards...
>

About TempDB

TempDB is one of the databases equipped with MSSQL Server by default.
What is the purpose of it?
Why do we use this temp database?

A) Any time you create a temporary table, it's actually created in tempdb:
--Create a temp table
CREATE TABLE #TempTable112233 (somecol INT)
--You can now see it in tempdb's sysobjects table
SELECT *
FROM tempdb..sysobjects
WHERE NAME LIKE '#TempTable112233%'
B) Many operations in SQL Server require temporary/intermediate tablesand/or sorts. For instance, if you select some data from twotables, part of the data from one of the tables may be sorted beforebeing joined with the data in the other table. That sort canoccur in tempdb.

|||The Temp DB in SQL Server 2000 is used for complex intermediate operations and can also be used to as the database to test stored procs if there is no database and tables to test them against because you don't need a database or tables to create stored procs, assuming the feature was not altered with a service pack. There two types of temp tables local with one # sign and global with two ##, the local is the most used but if your operation is long the global is more durable but it has performance impact so you have to know when to use them. Hope this helps.|||I understand from our DBA that tempdb is used to generate a templatefor a new database? Just that I caught a bit of a wigging forcreating a table without the # prefix to its name in that location, andwas told that the tempdb contributed the layout of new databases andother important things.
So what I should like to know is -- is tempdb purely a scratch area, or is it used for several other purposes?
--
My Alias

|||

My Alias wrote:

I understand from our DBA that tempdb is used to generate a template for a new database? Just that I caught a bit of a wigging for creating a table without the # prefix to its name in that location, and was told that the tempdb contributed the layout of new databases and other important things.
So what I should like to know is -- is tempdb purely a scratch area, or is it used for several other purposes?
--
My Alias

Your DBA is wrong Model DB is the template while TempDB is an intermediate or staging place to create tables and run code. Hope this helps.

About new SQL Server 2005 Samples and Sample Databases

There are five msi files listed for downloading.

SqlServerSamples.msi

AdventureWorksDB.msi

AdventureWorksDBCI.msi

AdventureWorksBI.msi

AdventureWorksBICI.msi

What are the AdventureWorksDBCI.ms and AdventureWorksBICI.msi used for? I try to run it, but it return the message "Another version of this product is already installed" and ask me remove the old version of this product. Anybody know what I should remove?

Thanks,

Hi,

Seems that they either need to update their docuementation or change the file names, because the BI (Business Intelligence) was former written wir DW (Datawarehouse). THE CI means (like mentioned in the download page) Case sensitive means, that for example 'A' is not the same as 'a'. more samples about that can be found here:

http://www.databasejournal.com/features/mssql/article.php/3302341

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jen,

Thanks. I are right, the CI means case insensitive.

AdventureWorksDB.msi creates a AdventureWork database with collation Latin1_General_CS_AS in your SQL Server.

AdventureWorksDBCI.msi creates a AdventureWork database with collation Latin1_General_CI_AS in your SQL Server.

Depends on the server collation setting (check the properties of server and see server collation item), you can decide which two msi files should to be downloaded and run on your machine.

Thanks,

about copy a new stored procedure to all databases

Dear all
i have been updating a new stored procedure to all database if it has
that procedure.
i have write something like to test with one database only but it
still fail.
it said i havent declare CID salary.
i cant figure out why and how. hope anyone have experience can help me
out.
Maybe can i update a procedure for a database and then copy them all
to other database.
thanks i really hope someone can help out for this
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
Begin
ALTER PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary[float]output)
AS
IF @.CID = 1234
Begin
xxxxxxxxxxxxxxxxxxxxxxx
/* it is the long statement*/
xxxxxxxxxxxxxxxxxxxxxxx
END
END
CREATE/ALTER PROCEDURE must be the only statement in the batch so
CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
followed by unconditional CREATE:
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
BEGIN
DROP PROCEDURE [dbo].[cal_sal]
END
GO
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary [float] output)
AS
IF @.CID = 1234
BEGIN
/* it is the long statement*/
END
GO
GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1185968371.785331.165490@.x40g2000prg.googlegr oups.com...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>
|||Thanks Dan
you have really help me out
but while i would like to execute the statement, it said Create/Alter
procedure must be the first statement
why was this happen?
so my testing statement is like below:
use test
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END
and finally i would like to use, is this possible ?
exec sp_MSforeachdb
'use [?]
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'
On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> CREATE/ALTER PROCEDURE must be the only statement in the batch so
> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> followed by unconditional CREATE:
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> BEGIN
> DROP PROCEDURE [dbo].[cal_sal]
> END
> GO
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> BEGIN
> /* it is the long statement*/
> END
> GO
> GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <KelvinWon...@.gmail.com> wrote in message
> news:1185968371.785331.165490@.x40g2000prg.googlegr oups.com...
>
>
>
>
>
>
> - -
|||thanks so much
i also got that problem
On 8 1 , 10 47 , NEMA <realja...@.gmail.com> wrote:
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alterproceduremust be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
> - -
|||put a GO before Create can fix the problem
but it still cannot use sp_MSforeachdb as it said there are syntax
error on create and end
|||In article <1185968371.785331.165490@.x40g2000prg.googlegroups .com>,
KelvinWongYW@.gmail.com says...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary[float]output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>
add it to model so included in all new databases created from then
forward
Graham (Pete) Berry
PeteBerry@.Caltech.edu
|||thanks Pete
but how about the exisiting databases?
On 8 2 , 1 57 , Pete Berry <PeteBe...@.Caltech.edu> wrote:
> In article <1185968371.785331.165...@.x40g2000prg.googlegroups .com>,
> KelvinWon...@.gmail.com says...
>
>
>
>
>
>
> add it to model so included in all new databases created from then
> forward
> --
> Graham (Pete) Berry
> PeteBe...@.Caltech.edu- -
> - -
|||> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
You need a GO batch separator before the CREATE. SQL Server tools will send
the preceding batch of statements whenever a GO is encountered.

> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
You'll need to wrap the batches individually with EXECUTE. Assuming you
want to skip system databases, try something like:
EXEC sp_MSForEachDB '
USE ?
IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
DROP PROC [dbo].[cal_sal]'')
EXEC(''CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'')
END
'
This technique is a bit of a kludge, though. IMHO, it's better to create a
script file and run for each database using OSQL or SQLCMD.
Hope this helps.
Dan Guzman
SQL Server MVP
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185979672.379344.178450@.d30g2000prg.googlegr oups.com...
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
>
>
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>
|||Thanks
i am thinking of is there a way to loop through each database and then
Alter the store procedure by using OSQL or SQLCMD
however i cant find a command to loop and some database may not have
that stored procedure
On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
>
> You need a GO batch separator before the CREATE. SQL Server tools will send
> the preceding batch of statements whenever a GO is encountered.
>
>
> You'll need to wrap the batches individually with EXECUTE. Assuming you
> want to skip system databases, try something like:
> EXEC sp_MSForEachDB '
> USE ?
> IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
> BEGIN
> EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
> DROP PROC [dbo].[cal_sal]'')
> EXEC(''CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'')
> END
> '
> This technique is a bit of a kludge, though. IMHO, it's better to create a
> script file and run for each database using OSQL or SQLCMD.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "NEMA" <realja...@.gmail.com> wrote in message
> news:1185979672.379344.178450@.d30g2000prg.googlegr oups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - -
|||Are you saying that you don't know which databases need the stored
procedure?
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1186030181.537666.23780@.x40g2000prg.googlegro ups.com...
> Thanks
> i am thinking of is there a way to loop through each database and then
> Alter the store procedure by using OSQL or SQLCMD
> however i cant find a command to loop and some database may not have
> that stored procedure
>
> On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>

about copy a new stored procedure to all databases

Dear all
i have been updating a new stored procedure to all database if it has
that procedure.
i have write something like to test with one database only but it
still fail.
it said i havent declare CID salary.
i cant figure out why and how. hope anyone have experience can help me
out.
Maybe can i update a procedure for a database and then copy them all
to other database.
thanks i really hope someone can help out for this
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name = 'cal_sal')
Begin
ALTER PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary [float] output)
AS
IF @.CID = 1234
Begin
xxxxxxxxxxxxxxxxxxxxxxx
/* it is the long statement*/
xxxxxxxxxxxxxxxxxxxxxxx
END
ENDCREATE/ALTER PROCEDURE must be the only statement in the batch so
CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
followed by unconditional CREATE:
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name ='cal_sal')
BEGIN
DROP PROCEDURE [dbo].[cal_sal]
END
GO
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary [float] output)
AS
IF @.CID = 1234
BEGIN
/* it is the long statement*/
END
GO
GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>|||Thanks Dan
you have really help me out
but while i would like to execute the statement, it said Create/Alter
procedure must be the first statement
why was this happen?
so my testing statement is like below:
use test
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name ='cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END
and finally i would like to use, is this possible ?
exec sp_MSforeachdb
'use [?]
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name ='cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'
On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> CREATE/ALTER PROCEDURE must be the only statement in the batch so
> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> followed by unconditional CREATE:
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> BEGIN
> DROP PROCEDURE [dbo].[cal_sal]
> END
> GO
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> BEGIN
> /* it is the long statement*/
> END
> GO
> GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <KelvinWon...@.gmail.com> wrote in message
> news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
>
> > Dear all
> > i have been updating a new stored procedure to all database if it has
> > that procedure.
> > i have write something like to test with one database only but it
> > still fail.
> > it said i havent declare CID salary.
> > i cant figure out why and how. hope anyone have experience can help me
> > out.
> > Maybe can i update a procedure for a database and then copy them all
> > to other database.
> > thanks i really hope someone can help out for this
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > ALTER PROCEDURE [dbo].[cal_sal]
> > (@.CID [varchar[50]],
> > @.Salary [float] output)
> > AS
> > IF @.CID = 1234
> > Begin
> > xxxxxxxxxxxxxxxxxxxxxxx
> > /* it is the long statement*/
> > xxxxxxxxxxxxxxxxxxxxxxx
> > END
> > END- -
> - -|||thanks so much
i also got that problem
On 8 1 , 10 47 , NEMA <realja...@.gmail.com> wrote:
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alterproceduremust be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>
> > CREATE/ALTERPROCEDUREmust be the only statement in the batch so
> > CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> > followed by unconditional CREATE:
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > BEGIN
> > DROPPROCEDURE[dbo].[cal_sal]
> > END
> > GO
> > CREATE PROCEDURE[dbo].[cal_sal]
> > (@.CID [varchar[50]],
> > @.Salary [float] output)
> > AS
> > IF @.CID = 1234
> > BEGIN
> > /* it is the long statement*/
> > END
> > GO
> > GRANT EXECUTE ONPROCEDURE[dbo].[cal_sal] TO MyRole
> > GO
> > --
> > Hope this helps.
> > Dan Guzman
> > SQL Server MVP
> > <KelvinWon...@.gmail.com> wrote in message
> >news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
> > > Dear all
> > > i have been updating a newstoredprocedureto all database if it has
> > > thatprocedure.
> > > i have write something like to test with one database only but it
> > > still fail.
> > > it said i havent declare CID salary.
> > > i cant figure out why and how. hope anyone have experience can help me
> > > out.
> > > Maybe can i update aprocedurefor a database and thencopythem all
> > > to other database.
> > > thanks i really hope someone can help out for this
> > > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > > 'cal_sal')
> > > Begin
> > > ALTER PROCEDURE[dbo].[cal_sal]
> > > (@.CID [varchar[50]],
> > > @.Salary [float] output)
> > > AS
> > > IF @.CID = 1234
> > > Begin
> > > xxxxxxxxxxxxxxxxxxxxxxx
> > > /* it is the long statement*/
> > > xxxxxxxxxxxxxxxxxxxxxxx
> > > END
> > > END- -
> > - -- -
> - -|||put a GO before Create can fix the problem
but it still cannot use sp_MSforeachdb as it said there are syntax
error on create and end|||In article <1185968371.785331.165490@.x40g2000prg.googlegroups.com>,
KelvinWongYW@.gmail.com says...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>
add it to model so included in all new databases created from then
forward
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||thanks Pete
but how about the exisiting databases?
On 8 2 , 1 57 , Pete Berry <PeteBe...@.Caltech.edu> wrote:
> In article <1185968371.785331.165...@.x40g2000prg.googlegroups.com>,
> KelvinWon...@.gmail.com says...
>
> > Dear all
> > i have been updating a newstoredprocedureto all database if it has
> > thatprocedure.
> > i have write something like to test with one database only but it
> > still fail.
> > it said i havent declare CID salary.
> > i cant figure out why and how. hope anyone have experience can help me
> > out.
> > Maybe can i update aprocedurefor a database and thencopythem all
> > to other database.
> > thanks i really hope someone can help out for this
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > ALTER PROCEDURE[dbo].[cal_sal]
> > (@.CID [varchar[50]],
> > @.Salary [float] output)
> > AS
> > IF @.CID = 1234
> > Begin
> > xxxxxxxxxxxxxxxxxxxxxxx
> > /* it is the long statement*/
> > xxxxxxxxxxxxxxxxxxxxxxx
> > END
> > END
> add it to model so included in all new databases created from then
> forward
> --
> Graham (Pete) Berry
> PeteBe...@.Caltech.edu- -
> - -|||> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
You need a GO batch separator before the CREATE. SQL Server tools will send
the preceding batch of statements whenever a GO is encountered.
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
You'll need to wrap the batches individually with EXECUTE. Assuming you
want to skip system databases, try something like:
EXEC sp_MSForEachDB '
USE ?
IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
DROP PROC [dbo].[cal_sal]'')
EXEC(''CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'')
END
'
This technique is a bit of a kludge, though. IMHO, it's better to create a
script file and run for each database using OSQL or SQLCMD.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185979672.379344.178450@.d30g2000prg.googlegroups.com...
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
>
>
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> CREATE/ALTER PROCEDURE must be the only statement in the batch so
>> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
>> followed by unconditional CREATE:
>> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> 'cal_sal')
>> BEGIN
>> DROP PROCEDURE [dbo].[cal_sal]
>> END
>> GO
>> CREATE PROCEDURE [dbo].[cal_sal]
>> (@.CID [varchar[50]],
>> @.Salary [float] output)
>> AS
>> IF @.CID = 1234
>> BEGIN
>> /* it is the long statement*/
>> END
>> GO
>> GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
>> GO
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <KelvinWon...@.gmail.com> wrote in message
>> news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
>>
>> > Dear all
>> > i have been updating a new stored procedure to all database if it has
>> > that procedure.
>> > i have write something like to test with one database only but it
>> > still fail.
>> > it said i havent declare CID salary.
>> > i cant figure out why and how. hope anyone have experience can help me
>> > out.
>> > Maybe can i update a procedure for a database and then copy them all
>> > to other database.
>> > thanks i really hope someone can help out for this
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > ALTER PROCEDURE [dbo].[cal_sal]
>> > (@.CID [varchar[50]],
>> > @.Salary [float] output)
>> > AS
>> > IF @.CID = 1234
>> > Begin
>> > xxxxxxxxxxxxxxxxxxxxxxx
>> > /* it is the long statement*/
>> > xxxxxxxxxxxxxxxxxxxxxxx
>> > END
>> > END- -
>> - -
>|||Thanks
i am thinking of is there a way to loop through each database and then
Alter the store procedure by using OSQL or SQLCMD
however i cant find a command to loop and some database may not have
that stored procedure
On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > but while i would like to execute the statement, it said Create/Alter
> >proceduremust be the first statement
> > why was this happen?
> You need a GO batch separator before the CREATE. SQL Server tools will send
> the preceding batch of statements whenever a GO is encountered.
> > and finally i would like to use, is this possible ?
> > exec sp_MSforeachdb
> > 'use [?]
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > DROPPROCEDURE[dbo].[cal_sal]
> > End
> > CREATEPROCEDURE[dbo].[cal_sal]
> You'll need to wrap the batches individually with EXECUTE. Assuming you
> want to skip system databases, try something like:
> EXEC sp_MSForEachDB '
> USE ?
> IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
> BEGIN
> EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
> DROP PROC [dbo].[cal_sal]'')
> EXEC(''CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'')
> END
> '
> This technique is a bit of a kludge, though. IMHO, it's better to create a
> script file and run for each database using OSQL or SQLCMD.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "NEMA" <realja...@.gmail.com> wrote in message
> news:1185979672.379344.178450@.d30g2000prg.googlegroups.com...
>
> > Thanks Dan
> > you have really help me out
> > but while i would like to execute the statement, it said Create/Alter
> >proceduremust be the first statement
> > why was this happen?
> > so my testing statement is like below:
> > use test
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > DROPPROCEDURE[dbo].[cal_sal]
> > End
> > CREATEPROCEDURE[dbo].[cal_sal]
> > (@.CID int,
> > @.Salary float output)
> > AS
> > IF 2=2
> > BEGIN
> > select * from customer
> > END
> > and finally i would like to use, is this possible ?
> > exec sp_MSforeachdb
> > 'use [?]
> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => > 'cal_sal')
> > Begin
> > DROPPROCEDURE[dbo].[cal_sal]
> > End
> > CREATEPROCEDURE[dbo].[cal_sal]
> > (@.CID int,
> > @.Salary float output)
> > AS
> > IF 2=2
> > BEGIN
> > select * from customer
> > END'
> > On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> > wrote:
> >> CREATE/ALTERPROCEDUREmust be the only statement in the batch so
> >> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> >> followed by unconditional CREATE:
> >> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => >> 'cal_sal')
> >> BEGIN
> >> DROPPROCEDURE[dbo].[cal_sal]
> >> END
> >> GO
> >> CREATE PROCEDURE[dbo].[cal_sal]
> >> (@.CID [varchar[50]],
> >> @.Salary [float] output)
> >> AS
> >> IF @.CID = 1234
> >> BEGIN
> >> /* it is the long statement*/
> >> END
> >> GO
> >> GRANT EXECUTE ONPROCEDURE[dbo].[cal_sal] TO MyRole
> >> GO
> >> --
> >> Hope this helps.
> >> Dan Guzman
> >> SQL Server MVP
> >> <KelvinWon...@.gmail.com> wrote in message
> >>news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
> >> > Dear all
> >> > i have been updating anewstoredprocedureto all database if it has
> >> > thatprocedure.
> >> > i have write something like to test with one database only but it
> >> > still fail.
> >> > it said i havent declare CID salary.
> >> > i cant figure out why and how. hope anyone have experience can help me
> >> > out.
> >> > Maybe can i update aprocedurefor a database and thencopythem all
> >> > to other database.
> >> > thanks i really hope someone can help out for this
> >> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name => >> > 'cal_sal')
> >> > Begin
> >> > ALTER PROCEDURE[dbo].[cal_sal]
> >> > (@.CID [varchar[50]],
> >> > @.Salary [float] output)
> >> > AS
> >> > IF @.CID = 1234
> >> > Begin
> >> > xxxxxxxxxxxxxxxxxxxxxxx
> >> > /* it is the long statement*/
> >> > xxxxxxxxxxxxxxxxxxxxxxx
> >> > END
> >> > END- -
> >> - -- -
> - -|||Are you saying that you don't know which databases need the stored
procedure?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1186030181.537666.23780@.x40g2000prg.googlegroups.com...
> Thanks
> i am thinking of is there a way to loop through each database and then
> Alter the store procedure by using OSQL or SQLCMD
> however i cant find a command to loop and some database may not have
> that stored procedure
>
> On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>> > but while i would like to execute the statement, it said Create/Alter
>> >proceduremust be the first statement
>> > why was this happen?
>> You need a GO batch separator before the CREATE. SQL Server tools will
>> send
>> the preceding batch of statements whenever a GO is encountered.
>> > and finally i would like to use, is this possible ?
>> > exec sp_MSforeachdb
>> > 'use [?]
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > DROPPROCEDURE[dbo].[cal_sal]
>> > End
>> > CREATEPROCEDURE[dbo].[cal_sal]
>> You'll need to wrap the batches individually with EXECUTE. Assuming you
>> want to skip system databases, try something like:
>> EXEC sp_MSForEachDB '
>> USE ?
>> IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
>> BEGIN
>> EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
>> DROP PROC [dbo].[cal_sal]'')
>> EXEC(''CREATEPROCEDURE[dbo].[cal_sal]
>> (@.CID int,
>> @.Salary float output)
>> AS
>> IF 2=2
>> BEGIN
>> select * from customer
>> END'')
>> END
>> '
>> This technique is a bit of a kludge, though. IMHO, it's better to create
>> a
>> script file and run for each database using OSQL or SQLCMD.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "NEMA" <realja...@.gmail.com> wrote in message
>> news:1185979672.379344.178450@.d30g2000prg.googlegroups.com...
>>
>> > Thanks Dan
>> > you have really help me out
>> > but while i would like to execute the statement, it said Create/Alter
>> >proceduremust be the first statement
>> > why was this happen?
>> > so my testing statement is like below:
>> > use test
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > DROPPROCEDURE[dbo].[cal_sal]
>> > End
>> > CREATEPROCEDURE[dbo].[cal_sal]
>> > (@.CID int,
>> > @.Salary float output)
>> > AS
>> > IF 2=2
>> > BEGIN
>> > select * from customer
>> > END
>> > and finally i would like to use, is this possible ?
>> > exec sp_MSforeachdb
>> > 'use [?]
>> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> > 'cal_sal')
>> > Begin
>> > DROPPROCEDURE[dbo].[cal_sal]
>> > End
>> > CREATEPROCEDURE[dbo].[cal_sal]
>> > (@.CID int,
>> > @.Salary float output)
>> > AS
>> > IF 2=2
>> > BEGIN
>> > select * from customer
>> > END'
>> > On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
>> > wrote:
>> >> CREATE/ALTERPROCEDUREmust be the only statement in the batch so
>> >> CREATE/ALTER cannot be conditional. Instead, consider a conditional
>> >> drop
>> >> followed by unconditional CREATE:
>> >> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> >> 'cal_sal')
>> >> BEGIN
>> >> DROPPROCEDURE[dbo].[cal_sal]
>> >> END
>> >> GO
>> >> CREATE PROCEDURE[dbo].[cal_sal]
>> >> (@.CID [varchar[50]],
>> >> @.Salary [float] output)
>> >> AS
>> >> IF @.CID = 1234
>> >> BEGIN
>> >> /* it is the long statement*/
>> >> END
>> >> GO
>> >> GRANT EXECUTE ONPROCEDURE[dbo].[cal_sal] TO MyRole
>> >> GO
>> >> --
>> >> Hope this helps.
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> <KelvinWon...@.gmail.com> wrote in message
>> >>news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
>> >> > Dear all
>> >> > i have been updating anewstoredprocedureto all database if it has
>> >> > thatprocedure.
>> >> > i have write something like to test with one database only but it
>> >> > still fail.
>> >> > it said i havent declare CID salary.
>> >> > i cant figure out why and how. hope anyone have experience can help
>> >> > me
>> >> > out.
>> >> > Maybe can i update aprocedurefor a database and thencopythem all
>> >> > to other database.
>> >> > thanks i really hope someone can help out for this
>> >> > IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =>> >> > 'cal_sal')
>> >> > Begin
>> >> > ALTER PROCEDURE[dbo].[cal_sal]
>> >> > (@.CID [varchar[50]],
>> >> > @.Salary [float] output)
>> >> > AS
>> >> > IF @.CID = 1234
>> >> > Begin
>> >> > xxxxxxxxxxxxxxxxxxxxxxx
>> >> > /* it is the long statement*/
>> >> > xxxxxxxxxxxxxxxxxxxxxxx
>> >> > END
>> >> > END- -
>> >> - -- -
>> - -
>

about copy a new stored procedure to all databases

Dear all
i have been updating a new stored procedure to all database if it has
that procedure.
i have write something like to test with one database only but it
still fail.
it said i havent declare CID salary.
i cant figure out why and how. hope anyone have experience can help me
out.
Maybe can i update a procedure for a database and then copy them all
to other database.
thanks i really hope someone can help out for this
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
Begin
ALTER PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary [float] output)
AS
IF @.CID = 1234
Begin
xxxxxxxxxxxxxxxxxxxxxxx
/* it is the long statement*/
xxxxxxxxxxxxxxxxxxxxxxx
END
ENDCREATE/ALTER PROCEDURE must be the only statement in the batch so
CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
followed by unconditional CREATE:
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
BEGIN
DROP PROCEDURE [dbo].[cal_sal]
END
GO
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID [varchar[50]],
@.Salary [float] output)
AS
IF @.CID = 1234
BEGIN
/* it is the long statement*/
END
GO
GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>|||Thanks Dan
you have really help me out
but while i would like to execute the statement, it said Create/Alter
procedure must be the first statement
why was this happen?
so my testing statement is like below:
use test
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END
and finally i would like to use, is this possible ?
exec sp_MSforeachdb
'use [?]
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
'cal_sal')
Begin
DROP PROCEDURE [dbo].[cal_sal]
End
CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'
On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> CREATE/ALTER PROCEDURE must be the only statement in the batch so
> CREATE/ALTER cannot be conditional. Instead, consider a conditional drop
> followed by unconditional CREATE:
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> BEGIN
> DROP PROCEDURE [dbo].[cal_sal]
> END
> GO
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> BEGIN
> /* it is the long statement*/
> END
> GO
> GRANT EXECUTE ON PROCEDURE [dbo].[cal_sal] TO MyRole
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <KelvinWon...@.gmail.com> wrote in message
> news:1185968371.785331.165490@.x40g2000prg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
> - -|||thanks so much
i also got that problem
On 8 1 , 10 47 , NEMA <realja...@.gmail.com> wrote:
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alterproce
duremust be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROPPROCEDURE[dbo].[cal_sal]
> End
> CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - -|||put a GO before Create can fix the problem
but it still cannot use sp_MSforeachdb as it said there are syntax
error on create and end|||In article <1185968371.785331.165490@.x40g2000prg.googlegroups.com>,
KelvinWongYW@.gmail.com says...
> Dear all
> i have been updating a new stored procedure to all database if it has
> that procedure.
> i have write something like to test with one database only but it
> still fail.
> it said i havent declare CID salary.
> i cant figure out why and how. hope anyone have experience can help me
> out.
> Maybe can i update a procedure for a database and then copy them all
> to other database.
> thanks i really hope someone can help out for this
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> ALTER PROCEDURE [dbo].[cal_sal]
> (@.CID [varchar[50]],
> @.Salary [float] output)
> AS
> IF @.CID = 1234
> Begin
> xxxxxxxxxxxxxxxxxxxxxxx
> /* it is the long statement*/
> xxxxxxxxxxxxxxxxxxxxxxx
> END
> END
>
add it to model so included in all new databases created from then
forward
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||thanks Pete
but how about the exisiting databases?
On 8 2 , 1 57 , Pete Berry <PeteBe...@.Caltech.edu> wrote:
> In article <1185968371.785331.165...@.x40g2000prg.googlegroups.com>,
> KelvinWon...@.gmail.com says...
>
>
>
>
>
>
>
>
>
>
>
> add it to model so included in all new databases created from then
> forward
> --
> Graham (Pete) Berry
> PeteBe...@.Caltech.edu- -
> - -|||> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
You need a GO batch separator before the CREATE. SQL Server tools will send
the preceding batch of statements whenever a GO is encountered.

> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
You'll need to wrap the batches individually with EXECUTE. Assuming you
want to skip system databases, try something like:
EXEC sp_MSForEachDB '
USE ?
IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
BEGIN
EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
DROP PROC [dbo].[cal_sal]'')
EXEC(''CREATE PROCEDURE [dbo].[cal_sal]
(@.CID int,
@.Salary float output)
AS
IF 2=2
BEGIN
select * from customer
END'')
END
'
This technique is a bit of a kludge, though. IMHO, it's better to create a
script file and run for each database using OSQL or SQLCMD.
Hope this helps.
Dan Guzman
SQL Server MVP
"NEMA" <realjacky@.gmail.com> wrote in message
news:1185979672.379344.178450@.d30g2000prg.googlegroups.com...
> Thanks Dan
> you have really help me out
> but while i would like to execute the statement, it said Create/Alter
> procedure must be the first statement
> why was this happen?
> so my testing statement is like below:
> use test
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END
> and finally i would like to use, is this possible ?
> exec sp_MSforeachdb
> 'use [?]
> IF EXISTS(SELECT * FROM sys.objects WHERE type = 'P' AND name =
> 'cal_sal')
> Begin
> DROP PROCEDURE [dbo].[cal_sal]
> End
> CREATE PROCEDURE [dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'
>
>
> On 8 1 , 7 58 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>|||Thanks
i am thinking of is there a way to loop through each database and then
Alter the store procedure by using OSQL or SQLCMD
however i cant find a command to loop and some database may not have
that stored procedure
On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
>
> You need a GO batch separator before the CREATE. SQL Server tools will se
nd
> the preceding batch of statements whenever a GO is encountered.
>
>
>
> You'll need to wrap the batches individually with EXECUTE. Assuming you
> want to skip system databases, try something like:
> EXEC sp_MSForEachDB '
> USE ?
> IF DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')
> BEGIN
> EXEC(''IF OBJECT_ID(''''[dbo].[cal_sal]'''') IS NOT NULL
> DROP PROC [dbo].[cal_sal]'')
> EXEC(''CREATEPROCEDURE[dbo].[cal_sal]
> (@.CID int,
> @.Salary float output)
> AS
> IF 2=2
> BEGIN
> select * from customer
> END'')
> END
> '
> This technique is a bit of a kludge, though. IMHO, it's better to create
a
> script file and run for each database using OSQL or SQLCMD.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "NEMA" <realja...@.gmail.com> wrote in message
> news:1185979672.379344.178450@.d30g2000prg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - -|||Are you saying that you don't know which databases need the stored
procedure?
Hope this helps.
Dan Guzman
SQL Server MVP
<KelvinWongYW@.gmail.com> wrote in message
news:1186030181.537666.23780@.x40g2000prg.googlegroups.com...
> Thanks
> i am thinking of is there a way to loop through each database and then
> Alter the store procedure by using OSQL or SQLCMD
> however i cant find a command to loop and some database may not have
> that stored procedure
>
> On 8 2 , 10 50 , "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
> wrote:
>

about catalog view

I can find all databases i created from sys.databases view

but i could not find tables info under relevant database by sys.tables view? and I do not know why sys.tables,sys.columns and other views do not have database_id attribute? any idea? thanks

As the sys.tables and sys.columns are catalog views within a database, they are only relevant to THAT database. Therefore, you don't need a database_id attribute. There are instances of these views in every database.

If you do want to return that attribute when querying the table, just add db_id() to your select statement.

However, if you want to list all tables on an instance, try using the undocumented proc sp_MsForEachDB.

sp_msforeachdb 'select * from ?.sys.tables'

Hope this helps!

|||

thanks in advance

but i could not find any instruction about this from books online?

any advice about useful undocumented proc in MSSQL? thanks