Changing to other Database by command

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Changing to other Database by command

Author

Topic

anilyadav83@gmail.com
Starting Member

6 Posts

Posted - 2008-09-16 : 01:00:26

How can i change to other database using command.
In stored procedure I am unable to write "USE DataBaseName"

Is there any other command?

declare @TargetDB as varchar(100)
declare @DBQry as varchar(100)

select @TargetDB = 'CL_T'
select @DBQry= 'use ' +@TargetDB
select @dbqry
exec(@dbqry)

I am running above script with no error but unsuccessful in getting what i wanted. Please help

http://www.anil83.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 01:09:41

What are you trying to do by changing database? can you explain?

anilyadav83@gmail.com
Starting Member

6 Posts

Posted - 2008-09-16 : 01:13:08

I want to run queries in source and target database inside a loop so need to move to source and target database one by one.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 01:16:36

quote:
Originally posted by anilyadav83@gmail.com

I want to run queries in source and target database inside a loop so need to move to source and target database one by one.

try using sp_msforeachdb system stored procedure.

anilyadav83@gmail.com
Starting Member

6 Posts

Posted - 2008-09-16 : 01:30:24

I have two databases with same structure.
I want to transfer source data to target.
In source database i want to run queries
declare @username
declare @password

select @username=username,@password=password from temp

EXEC TargetDatabasename..AddUser @username=@username,@password=@username

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 01:49:00

quote:
Originally posted by anilyadav83@gmail.com

I have two databases with same structure.
I want to transfer source data to target.
In source database i want to run queries
declare @username
declare @password

select @username=username,@password=password from temp

EXEC TargetDatabasename..AddUser @username=@username,@password=@username

then cant you use a DTS/SSIS package to achieve the same?

anilyadav83@gmail.com
Starting Member

6 Posts

Posted - 2008-09-16 : 06:19:33

I wanna to do it through application

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 06:24:43

quote:
Originally posted by anilyadav83@gmail.com

I wanna to do it through application

then use different connection strings first to source and then to destination.

anilyadav83@gmail.com
Starting Member

6 Posts

Posted - 2008-09-16 : 07:32:44

Actually I m doing everything in stored procedure and calling single procedure from c# application.

declare @vchCourseName varchar(100)
declare @iCourseCategoryID int
declare @iInsertByID int
declare @new_iSchoolId int
declare @vchCourseAbbreviation varchar(20)

set @vchCourseName ='1 a b c'
set @iCourseCategoryID =1
set @iInsertByID=1
set @vchCourseAbbreviation=null
set @new_iSchoolId =86

declare @dbMaster as varchar(100)
set @dbMaster='CL_T'

EXEC ( @dbMaster + '..sis_iCourse @iSchoolID =' + @new_iSchoolId + ',

@vchCourseName =' + @vchCourseName + ',

@vchCourseAbbreviation='+@vchCourseAbbreviation +',

@iCourseCategoryID = ' + @iCourseCategoryID + ',

@iCourseGUID = null, @iInsertByID=' + @iInsertByID )

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 07:35:36

quote:
Originally posted by anilyadav83@gmail.com

Actually I m doing everything in stored procedure and calling single procedure from c# application.

declare @vchCourseName varchar(100)
declare @iCourseCategoryID int
declare @iInsertByID int
declare @new_iSchoolId int
declare @vchCourseAbbreviation varchar(20)

set @vchCourseName ='1 a b c'
set @iCourseCategoryID =1
set @iInsertByID=1
set @vchCourseAbbreviation=null
set @new_iSchoolId =86

declare @dbMaster as varchar(100)
set @dbMaster='CL_T'

EXEC ( @dbMaster + '..sis_iCourse @iSchoolID =' + @new_iSchoolId + ',

@vchCourseName =' + @vchCourseName + ',

@vchCourseAbbreviation='+@vchCourseAbbreviation +',

@iCourseCategoryID = ' + @iCourseCategoryID + ',

@iCourseGUID = null, @iInsertByID=' + @iInsertByID )

i dont think you can cahnge database context inside a stored procedure unless you use sp_msforeachdb

anilyadav83@gmail.com
Starting Member

6 Posts

Posted - 2008-09-16 : 07:55:00

It is working for integer parameters but not for varchar.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 08:01:05

quote:
Originally posted by anilyadav83@gmail.com

It is working for integer parameters but not for varchar.

didnt get that. which parameters?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources