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')

Change config file

Do I have to reboot after changing RSReportServer.config?
I am working with the alias and host names. I have the alias set to
true, but it only puts in john_g.
I need it to add '@.domain.com' Can that be done in the config using the
DefaultHostName? If so, just put domain.com in?
Thanks.You don't have to reboot, but you should restart the ReportServer service
(in Control Panel) after changing config files.
You might also want to run iisreset.exe, depending on the changes you are
making -- but that's more for when you have GUI code changes or are tweaking
custom extensions.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"John Geddes" <john_g@.alamode.com> wrote in message
news:%231A%23vsN9EHA.3792@.TK2MSFTNGP10.phx.gbl...
> Do I have to reboot after changing RSReportServer.config?
> I am working with the alias and host names. I have the alias set to true,
> but it only puts in john_g.
> I need it to add '@.domain.com' Can that be done in the config using the
> DefaultHostName? If so, just put domain.com in?
> Thanks.
>|||Yes, you want to use the defaultHostName element. You do not need to reboot
after changing the config file.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Geddes" <john_g@.alamode.com> wrote in message
news:%231A%23vsN9EHA.3792@.TK2MSFTNGP10.phx.gbl...
> Do I have to reboot after changing RSReportServer.config?
> I am working with the alias and host names. I have the alias set to true,
> but it only puts in john_g.
> I need it to add '@.domain.com' Can that be done in the config using the
> DefaultHostName? If so, just put domain.com in?
> Thanks.
>

change column names

i am trying to change column names in a bunch of tables.

why is this not right?

is there any sp that i can use as i have to change this in a lot of tables across two databases?

Alter Table Answers

Change Product NewProduct varchar(35)

This works.

EXEC sp_rename 'Answers2.[Product]', 'NewProduct', 'COLUMN'

now how do i do multiple columns.......

if anyone can figure faster?

|||You can't, just execute a batch query (a normal query with more queries in it) like this:

EXEC sp_rename 'Answers2.[Product]', 'NewProduct1', 'COLUMNA' GO

EXEC sp_rename 'Answers3.[Product]', 'NewProduct2', 'COLUMNB' GO

EXEC sp_rename 'Answers4.[Product]', 'NewProduct3', 'COLUMNC' GO

|||You can do some very clever things with sysobjects and syscolumns, provided all the column names are the same that you want to rename. Write a stored procedure that gets a list of the tables that contain that column. Then write a dynamic sql statement or just a standard sql statement that ouputs an alter command for each row returned. Check the syntax of the coded created and execute if it is OK.|||

select 'exec sp_rename ''' + obj.name + '.[' + col.name + ']'', ' + ' ''NewProduct'' go'

from syscolumns col

join sysobjects obj

on col.id = obj.id

where col.name = 'Product'

-

That will output a load of dynamic SQL. Now copy and paste into Query Analyser or the query execution window, test and execute.

For more SQL Server tips, check out my blog:

http://blogs.claritycon.com/blogs/the_englishman/default.aspx

HTH

|||

You may need to alter the query to:

:

select 'exec sp_rename ''' + obj.name + '.[' + col.name + ']'', ' + ' ''NewProduct'' go'

from syscolumns col

join sysobjects obj

on col.id = obj.id

where col.name = 'Product'

and obj.[type] = 'U'

-

To ensure only tables are returned from sysobjects

|||Yes you can, with syscolumns and sysobjects. See my post|||Not a very good idea messing around with system tables. Write a small SP for creating DDL and execute that DDL script.

change column names

i am trying to change column names in a bunch of tables.

why is this not right?

is there any sp that i can use as i have to change this in a lot of tables across two databases?

Alter Table Answers

Change Product NewProduct varchar(35)

This works.

EXEC sp_rename 'Answers2.[Product]', 'NewProduct', 'COLUMN'

now how do i do multiple columns.......

if anyone can figure faster?

|||You can't, just execute a batch query (a normal query with more queries in it) like this:

EXEC sp_rename 'Answers2.[Product]', 'NewProduct1', 'COLUMNA' GO

EXEC sp_rename 'Answers3.[Product]', 'NewProduct2', 'COLUMNB' GO

EXEC sp_rename 'Answers4.[Product]', 'NewProduct3', 'COLUMNC' GO

|||You can do some very clever things with sysobjects and syscolumns, provided all the column names are the same that you want to rename. Write a stored procedure that gets a list of the tables that contain that column. Then write a dynamic sql statement or just a standard sql statement that ouputs an alter command for each row returned. Check the syntax of the coded created and execute if it is OK.|||

select 'exec sp_rename ''' + obj.name + '.[' + col.name + ']'', ' + ' ''NewProduct'' go'

from syscolumns col

join sysobjects obj

on col.id = obj.id

where col.name = 'Product'

-

That will output a load of dynamic SQL. Now copy and paste into Query Analyser or the query execution window, test and execute.

For more SQL Server tips, check out my blog:

http://blogs.claritycon.com/blogs/the_englishman/default.aspx

HTH

|||

You may need to alter the query to:

:

select 'exec sp_rename ''' + obj.name + '.[' + col.name + ']'', ' + ' ''NewProduct'' go'

from syscolumns col

join sysobjects obj

on col.id = obj.id

where col.name = 'Product'

and obj.[type] = 'U'

-

To ensure only tables are returned from sysobjects

|||Yes you can, with syscolumns and sysobjects. See my post|||Not a very good idea messing around with system tables. Write a small SP for creating DDL and execute that DDL script.

Change Column Names

Can someone help with a query to change the column names in a table
Thanks
ChrisEXEC sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:4388A639-BA52-481D-9B69-A64420E5B59E@.microsoft.com...
> Can someone help with a query to change the column names in a table
> Thanks
> Chris

challenging search task is not working as expected

Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1

-

-searchword- column name

--

Learn more about melons row0

--

%.txt row1

-

table2

-testname- column name

--

FKOV43C6.EXE

-
frusdr.txt

-
FRUSDR.TXT

SPGP_FWPkg_66G.zip

readme.txt

--
README.TXT

-
watermelon.exe

-
Learn more about melons read me.txt

-

Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================

select * from @.table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @.table1 ta

JOIN @.table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @.table1)

)

===============================================================================

script to create tables

============================================================================

DECLARE @.table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @.table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @.table1 (

searchword

) VALUES ( '%.txt' )

DECLARE @.table2 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @.table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @.table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @.table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @.table2 (

testname

) VALUES ( 'readme.txt' )

INSERT INTO @.table2 (testname

) VALUES ('README.TXT' )

INSERT INTO @.table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @.table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @.table2

DECLARE @.table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )

===================================================================================

Here it is:

DECLARE @.num AS int;
SELECT @.num = COUNT(*) FROM @.table1;

SELECT tb.[testname] FROM @.table1 ta
cross JOIN (select distinct * from @.table2) tb
where tb.[testname] LIKE + '%' + ta.searchword + '%'
group by tb.[testname] having count(ta.searchword) = @.num
|||

maybe you also should take an look on "full text index":

f. ex. contains-function

|||

Hi Zuomin,

Thank you very much.It worked perfectly. Thanks for spending your valuable time.

challenging search task is not working as expected

Hi Guys,

I have two tables called table1 and table2.

table1 has search words and table2 has file names as below and want to get file names from table2 those match with all search words.

table1

-

-searchword- column name

--

Learn more about melons row0

--

%.txt row1

-

table2

-testname- column name

--

FKOV43C6.EXE

-
frusdr.txt

-
FRUSDR.TXT

SPGP_FWPkg_66G.zip

readme.txt

--
README.TXT

-
watermelon.exe

-
Learn more about melons read me.txt

-

Here is the script what I have tried...............I hope some one will help to come out this loop.Thanks in advance.

===============================================================================

select * from @.table2 t2 where t2.[testname] in (

SELECT tb.[testname] FROM @.table1 ta

JOIN @.table2 tb ON '% ' + tb.[testname] + ' %' LIKE '% ' + ta.searchword + ' %'

group by tb.[testname] having count(*) = (SELECT COUNT(*) FROM @.table1)

)

===============================================================================

script to create tables

============================================================================

DECLARE @.table1 TABLE (

searchword VARCHAR(255)

)

INSERT INTO @.table1 (

searchword

) VALUES ( 'Learn more about melons' )

INSERT INTO @.table1 (

searchword

) VALUES ( '%.txt' )

DECLARE @.table2 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ( 'FKOV43C6.EXE' )

INSERT INTO @.table2 (

testname

) VALUES ('frusdr.txt' )

INSERT INTO @.table2 (

testname

) VALUES ('FRUSDR.TXT' )

INSERT INTO @.table2 ( testname

) VALUES ( 'SPGP_FWPkg_66G.zip' )

INSERT INTO @.table2 (

testname

) VALUES ( 'readme.txt' )

INSERT INTO @.table2 (testname

) VALUES ('README.TXT' )

INSERT INTO @.table2 (testname) VALUES (

'watermelon.exe' )

INSERT INTO @.table2 (

testname

) VALUES ('Learn more about melons read me.txt' )

SELECT * FROM @.table2

DECLARE @.table3 TABLE (

testname VARCHAR(255)

)

INSERT INTO @.table2 (

testname

) VALUES ('Melon release NOTES 321.xls' )

===================================================================================

Here it is:

DECLARE @.num AS int;
SELECT @.num = COUNT(*) FROM @.table1;

SELECT tb.[testname] FROM @.table1 ta
cross JOIN (select distinct * from @.table2) tb
where tb.[testname] LIKE + '%' + ta.searchword + '%'
group by tb.[testname] having count(ta.searchword) = @.num
|||

maybe you also should take an look on "full text index":

f. ex. contains-function

|||

Hi Zuomin,

Thank you very much.It worked perfectly. Thanks for spending your valuable time.