change current database in stored procedure

I am trying to move whole bunch of stored procedures from 1 db to another.
Instead of going into each of them and change the table names to fully
qualified name, is there a command that I can set the current database? I
know USE does not work in sp. Any help will be highly appreciated!
USE will work in dynamic SQL I think, e.g.
EXEC('USE somedb; EXEC someproc;')
However, if you're going to bother going in and putting USE statements or
something similar, why not just go in and put in the fully qualified name?
I don't see what you will be saving by NOT putting in the fully qualified
names...
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>I am trying to move whole bunch of stored procedures from 1 db to another.
> Instead of going into each of them and change the table names to fully
> qualified name, is there a command that I can set the current database? I
> know USE does not work in sp. Any help will be highly appreciated!
|||Thanks for your response, Chew. It was just because we have too many stored
procedures that we need to move. Each of them uses so many tables. Going
into each of them and change the table names will literally kill me.
However, I do think change them to fully qualified names is the right thing
to do. At the mean time, we just want a quick solution. I tried doing
exec('use db') and it did not work.
alter procedure junk
as
begin
exec ('use abc')
select * from iv00101 where itemnmbr = '12345'
end
"Foo Man Chew" wrote:

> USE will work in dynamic SQL I think, e.g.
> EXEC('USE somedb; EXEC someproc;')
> However, if you're going to bother going in and putting USE statements or
> something similar, why not just go in and put in the fully qualified name?
> I don't see what you will be saving by NOT putting in the fully qualified
> names...
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>
>
|||1. Move all the stored procecures from DatabaseA to DatabaseB
2. Create views in databaseB with same name as in DatabaseA
Database A
=======
TableA
CREATE PROC Proc1
AS
SELECT * FROM TableA
DatabaseB
create a View
CREATE VIEW TableA
AS
SELECT * FROM DatabaseA.dbo.TableA
Move the Procedures
This way you don't have to touch you code. All you need to do create buch of
views
Thanks
Ravi
"Ed" wrote:
[vbcol=seagreen]
> Thanks for your response, Chew. It was just because we have too many stored
> procedures that we need to move. Each of them uses so many tables. Going
> into each of them and change the table names will literally kill me.
> However, I do think change them to fully qualified names is the right thing
> to do. At the mean time, we just want a quick solution. I tried doing
> exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
|||Ed wrote:[vbcol=seagreen]
> Thanks for your response, Chew. It was just because we have too many
> stored procedures that we need to move. Each of them uses so many
> tables. Going into each of them and change the table names will
> literally kill me. However, I do think change them to fully qualified
> names is the right thing to do. At the mean time, we just want a
> quick solution. I tried doing exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
EXEC runs in its own context. Just fully qualify using DB.OWNER.SPNAME.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||> exec('use db') and it did not work.
Because that is not what I said to do.
You need to do everything inside a single EXEC.
EXEC('Use somedb; EXEC someproc')

change current database in stored procedure

I am trying to move whole bunch of stored procedures from 1 db to another.
Instead of going into each of them and change the table names to fully
qualified name, is there a command that I can set the current database? I
know USE does not work in sp. Any help will be highly appreciated!USE will work in dynamic SQL I think, e.g.
EXEC('USE somedb; EXEC someproc;')
However, if you're going to bother going in and putting USE statements or
something similar, why not just go in and put in the fully qualified name?
I don't see what you will be saving by NOT putting in the fully qualified
names...
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>I am trying to move whole bunch of stored procedures from 1 db to another.
> Instead of going into each of them and change the table names to fully
> qualified name, is there a command that I can set the current database? I
> know USE does not work in sp. Any help will be highly appreciated!|||Thanks for your response, Chew. It was just because we have too many stored
procedures that we need to move. Each of them uses so many tables. Going
into each of them and change the table names will literally kill me.
However, I do think change them to fully qualified names is the right thing
to do. At the mean time, we just want a quick solution. I tried doing
exec('use db') and it did not work.
alter procedure junk
as
begin
exec ('use abc')
select * from iv00101 where itemnmbr = '12345'
end
"Foo Man Chew" wrote:
> USE will work in dynamic SQL I think, e.g.
> EXEC('USE somedb; EXEC someproc;')
> However, if you're going to bother going in and putting USE statements or
> something similar, why not just go in and put in the fully qualified name?
> I don't see what you will be saving by NOT putting in the fully qualified
> names...
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
> >I am trying to move whole bunch of stored procedures from 1 db to another.
> > Instead of going into each of them and change the table names to fully
> > qualified name, is there a command that I can set the current database? I
> > know USE does not work in sp. Any help will be highly appreciated!
>
>|||1. Move all the stored procecures from DatabaseA to DatabaseB
2. Create views in databaseB with same name as in DatabaseA
Database A
=======TableA
CREATE PROC Proc1
AS
SELECT * FROM TableA
DatabaseB
create a View
CREATE VIEW TableA
AS
SELECT * FROM DatabaseA.dbo.TableA
Move the Procedures
This way you don't have to touch you code. All you need to do create buch of
views
Thanks
Ravi
"Ed" wrote:
> Thanks for your response, Chew. It was just because we have too many stored
> procedures that we need to move. Each of them uses so many tables. Going
> into each of them and change the table names will literally kill me.
> However, I do think change them to fully qualified names is the right thing
> to do. At the mean time, we just want a quick solution. I tried doing
> exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
> > USE will work in dynamic SQL I think, e.g.
> >
> > EXEC('USE somedb; EXEC someproc;')
> >
> > However, if you're going to bother going in and putting USE statements or
> > something similar, why not just go in and put in the fully qualified name?
> > I don't see what you will be saving by NOT putting in the fully qualified
> > names...
> >
> >
> > "Ed" <Ed@.discussions.microsoft.com> wrote in message
> > news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
> > >I am trying to move whole bunch of stored procedures from 1 db to another.
> > > Instead of going into each of them and change the table names to fully
> > > qualified name, is there a command that I can set the current database? I
> > > know USE does not work in sp. Any help will be highly appreciated!
> >
> >
> >|||Ed wrote:
> Thanks for your response, Chew. It was just because we have too many
> stored procedures that we need to move. Each of them uses so many
> tables. Going into each of them and change the table names will
> literally kill me. However, I do think change them to fully qualified
> names is the right thing to do. At the mean time, we just want a
> quick solution. I tried doing exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
>> USE will work in dynamic SQL I think, e.g.
>> EXEC('USE somedb; EXEC someproc;')
>> However, if you're going to bother going in and putting USE
>> statements or something similar, why not just go in and put in the
>> fully qualified name? I don't see what you will be saving by NOT
>> putting in the fully qualified names...
>>
>> "Ed" <Ed@.discussions.microsoft.com> wrote in message
>> news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>> I am trying to move whole bunch of stored procedures from 1 db to
>> another. Instead of going into each of them and change the table
>> names to fully qualified name, is there a command that I can set
>> the current database? I know USE does not work in sp. Any help
>> will be highly appreciated!
EXEC runs in its own context. Just fully qualify using DB.OWNER.SPNAME.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> exec('use db') and it did not work.
Because that is not what I said to do.
You need to do everything inside a single EXEC.
EXEC('Use somedb; EXEC someproc')

change current database in stored procedure

I am trying to move whole bunch of stored procedures from 1 db to another.
Instead of going into each of them and change the table names to fully
qualified name, is there a command that I can set the current database? I
know USE does not work in sp. Any help will be highly appreciated!USE will work in dynamic SQL I think, e.g.
EXEC('USE somedb; EXEC someproc;')
However, if you're going to bother going in and putting USE statements or
something similar, why not just go in and put in the fully qualified name?
I don't see what you will be saving by NOT putting in the fully qualified
names...
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>I am trying to move whole bunch of stored procedures from 1 db to another.
> Instead of going into each of them and change the table names to fully
> qualified name, is there a command that I can set the current database? I
> know USE does not work in sp. Any help will be highly appreciated!|||Thanks for your response, Chew. It was just because we have too many stored
procedures that we need to move. Each of them uses so many tables. Going
into each of them and change the table names will literally kill me.
However, I do think change them to fully qualified names is the right thing
to do. At the mean time, we just want a quick solution. I tried doing
exec('use db') and it did not work.
alter procedure junk
as
begin
exec ('use abc')
select * from iv00101 where itemnmbr = '12345'
end
"Foo Man Chew" wrote:

> USE will work in dynamic SQL I think, e.g.
> EXEC('USE somedb; EXEC someproc;')
> However, if you're going to bother going in and putting USE statements or
> something similar, why not just go in and put in the fully qualified name?
'
> I don't see what you will be saving by NOT putting in the fully qualified
> names...
>
> "Ed" <Ed@.discussions.microsoft.com> wrote in message
> news:41263B68-0B3A-4472-9FFE-54BF6CF9066C@.microsoft.com...
>
>|||1. Move all the stored procecures from DatabaseA to DatabaseB
2. Create views in databaseB with same name as in DatabaseA
Database A
=======
TableA
CREATE PROC Proc1
AS
SELECT * FROM TableA
DatabaseB
create a View
CREATE VIEW TableA
AS
SELECT * FROM DatabaseA.dbo.TableA
Move the Procedures
This way you don't have to touch you code. All you need to do create buch of
views
Thanks
Ravi
"Ed" wrote:
[vbcol=seagreen]
> Thanks for your response, Chew. It was just because we have too many stor
ed
> procedures that we need to move. Each of them uses so many tables. Going
> into each of them and change the table names will literally kill me.
> However, I do think change them to fully qualified names is the right thin
g
> to do. At the mean time, we just want a quick solution. I tried doing
> exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
>|||Ed wrote:[vbcol=seagreen]
> Thanks for your response, Chew. It was just because we have too many
> stored procedures that we need to move. Each of them uses so many
> tables. Going into each of them and change the table names will
> literally kill me. However, I do think change them to fully qualified
> names is the right thing to do. At the mean time, we just want a
> quick solution. I tried doing exec('use db') and it did not work.
> alter procedure junk
> as
> begin
> exec ('use abc')
> select * from iv00101 where itemnmbr = '12345'
> end
> "Foo Man Chew" wrote:
>
EXEC runs in its own context. Just fully qualify using DB.OWNER.SPNAME.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||> exec('use db') and it did not work.
Because that is not what I said to do.
You need to do everything inside a single EXEC.
EXEC('Use somedb; EXEC someproc')

central place for stored procedure variables

Hello,
I have numerous stored procedures with the following. Every time I make a
change I have to update all of my stored procedures. Is there a more
efficient way of doing this?
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
Thanks in advance,
sck10Why don't you put this data in a table?
David Portas
SQL Server MVP
--|||Depending on your overall system architecture, security requirements, system
complexity and maintenance provisions, you can either have the paths
represented as values in a table within the database, or an external config
file, or in rare instances in a registry or in .ini files etc.
Anith|||sck10 wrote:
> Hello,
> I have numerous stored procedures with the following. Every time I
> make a change I have to update all of my stored procedures. Is there
> a more efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx
> varchar(300), @.strPhoto_home varchar(300),
> @.strPhoto_aspx varchar(300), @.strRightArrow_home varchar(300),
> @.strRightArrow_aspx varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img
> src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif" width="../../8"
> height="../../5">'
Put the HTML snippets in a table and write a function to access the
table by string ID. For example:
Create Table HTMLSnippet (
HTMLID CHAR(15) NOT NULL PRIMARY KEY,
HTMLSnip VARCHAR(100),
HTMLDesc VARCHAR(255))
-- insert values into table
Insert Into HTMLSnippet Values ('DOCPATHHOME, '<a href="http://links.10026.com/?link=web_doc/', 'The
Doc path home')
-- etc
Create Function dbo.GetHTMLSnippet (
@.HTMLID CHAR(15) )
RETURNS VARCHAR(100)
AS
DECLARE @.HTMLCode VARCHAR(100)
SELECT @.HTMLCode = HTMLCode From HTMLSnippet Where @.HTMLID = @.HTMLID
RETURN @.HTMLCode
-- to use function
Declare @.test varchar(1000)
Select @.test = dbo.GetHTMLSnippet('DOCPATHHOME') + ...
Not tested, but should give you a good start.
David Gugick
Imceda Software
www.imceda.com|||I agree with David, put it into a table and then SELECT the values in your
SP's. Then if they change you only have to change them in the table on
time, all your SP's will be able to use the updated info appropriately...
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>|||One more vote for using SQL Server to store data! Alternatively you might
consider building something (or finding something) to do macro's for SQL
code to have this automatically done for you (it might be slightly faster to
do what you have done rather than a table, if you have ultra-high
performance needs:
For example in your code put:
--<start macro replace-name_of_macro>
--Set Web Page Path
DECLARE
@.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
@.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
@.strRightArrow_home varchar(300), @.strRightArrow_aspx varchar(300),
--Set path for web pages
SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
width="8" height="5">'
SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
width="../../8" height="../../5">'
--<end macro replace-name_of_macro>
Now it would be easy to go through all of the files with stored procedures
in them and do a find and replace for this stuff. I would probably
suggest just using a table and cross joining to it whenever you need these
values in a few cases to try first. In the end it will be a far better
solution than putting data into variables. SQL Server does not now, and
forseeable future will not have constants like this.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:Oa2Oy0oUFHA.3140@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have numerous stored procedures with the following. Every time I make a
> change I have to update all of my stored procedures. Is there a more
> efficient way of doing this?
>
> --Set Web Page Path
> DECLARE
> @.strWebDocPath_home varchar(300), @.strWebDocPath_aspx varchar(300),
> @.strPhoto_home varchar(300), @.strPhoto_aspx varchar(300),
> @.strRightArrow_home varchar(300), @.strRightArrow_aspx
> varchar(300),
> --Set path for web pages
> SET @.strWebDocPath_home = '<a href="http://links.10026.com/?link=web_doc/'
> SET @.strWebDocPath_aspx = '<a href="http://links.10026.com/?link=../../web_doc/'
> SET @.strPhoto_home = '<img src="http://pics.10026.com/?src=/library/images/photo/'
> SET @.strPhoto_aspx = '<img src="http://pics.10026.com/?src=../../images/photo/'
> SET @.strRightArrow_home = '<img src="http://pics.10026.com/?src=/library/images/icons/rightarrow.gif"
> width="8" height="5">'
> SET @.strRightArrow_aspx = '<img src="http://pics.10026.com/?src=../../images/icons/rightarrow.gif"
> width="../../8" height="../../5">'
> --
> Thanks in advance,
> sck10
>

Category column in sysobjects

Everyone,

Is there a reference to describe the relevance of the Category column in the sysobjects table? I'm trying to track down stored procedures that are set to automatically run at startup and the only thing I can find that is consistent about those that are set to autorun is that they have a value of 16 in the Category column. Is this consistent? What else may I glean from this column?

Thanks

Tim

this will give u the sps which run at startup

Run this in Master db.

SELECT * FROM sysobjects WHERE xtype IN ('X','P') AND ObjectProperty(id, 'ExecIsStartup') = 1

Madhu

|||

Thanks, that is exactly what I needed.

I'm still curious, though, what the category column in this table represents.

Case-sensitive stored procedures

I have a SQL 2000 database server with the server collation set to
SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
Problem I have is that on this server stored procedures are case sensitive
and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
have been written without case sensitivity in mind (the procedures do not
work). So I have a few questions.
1. Is the reason for the stored procedure case sensitivity due to the server
collation? ie. It is server specific rather than DB specific.
2. Can this be changed?
thanks
Gav
The collation that is CI is a default configuration, so that's probably why
the external company gave it that way.
You can modify the collation of the database to be the same as yours.
If it won't affect the sp's speed, you could put collation hints on any
joins between the databases without changing the db collation.
"Gav" <gav@.nospam.com> wrote in message
news:%231EwxS0jIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I have a SQL 2000 database server with the server collation set to
>SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
>SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
>Problem I have is that on this server stored procedures are case sensitive
>and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
>have been written without case sensitivity in mind (the procedures do not
>work). So I have a few questions.
> 1. Is the reason for the stored procedure case sensitivity due to the
> server collation? ie. It is server specific rather than DB specific.
> 2. Can this be changed?
> thanks
> Gav
>

Case-sensitive stored procedures

I have a SQL 2000 database server with the server collation set to
SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
Problem I have is that on this server stored procedures are case sensitive
and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
have been written without case sensitivity in mind (the procedures do not
work). So I have a few questions.
1. Is the reason for the stored procedure case sensitivity due to the server
collation? ie. It is server specific rather than DB specific.
2. Can this be changed?
thanks
GavThe collation that is CI is a default configuration, so that's probably why
the external company gave it that way.
You can modify the collation of the database to be the same as yours.
If it won't affect the sp's speed, you could put collation hints on any
joins between the databases without changing the db collation.
"Gav" <gav@.nospam.com> wrote in message
news:%231EwxS0jIHA.3940@.TK2MSFTNGP05.phx.gbl...
>I have a SQL 2000 database server with the server collation set to
>SQL_Latin1_General_CP850_BIN and one of the databases with a collation of
>SQL_Latin1_General_CP1_CI_AS which has been created by an external company.
>Problem I have is that on this server stored procedures are case sensitive
>and the database (SQL_Latin1_General_CP1_CI_AS) has stored procedures that
>have been written without case sensitivity in mind (the procedures do not
>work). So I have a few questions.
> 1. Is the reason for the stored procedure case sensitivity due to the
> server collation? ie. It is server specific rather than DB specific.
> 2. Can this be changed?
> thanks
> Gav
>