executing sp_changeownerdb

Hi,
I've a problem executing sp_changedbowner in my stored procedure which
is saved in the master's stored procedures.
The code fails after executing @.proc1. The errors says that i could not
find proc2.
Here's the code:
CREATE PROC usp_RestoreDB(
@.db_name varchar(10),
@.backup_location varchar(255),
@.login varchar(20)
)
AS
DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
SET @.proc1 = @.db_name + '..sp_fixusers'
SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
RESTORE DATABASE @.db_name
FROM DISK = @.backup_location
WITH REPLACE
Begin
EXEC @.proc1
EXEC @.proc2
ENDTry,
...
exec (@.proc1)
exec (@.proc2)
...
AMB
"Jason" wrote:

> Hi,
> I've a problem executing sp_changedbowner in my stored procedure which
> is saved in the master's stored procedures.
> The code fails after executing @.proc1. The errors says that i could not
> find proc2.
> Here's the code:
> CREATE PROC usp_RestoreDB(
> @.db_name varchar(10),
> @.backup_location varchar(255),
> @.login varchar(20)
> )
> AS
> DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
> SET @.proc1 = @.db_name + '..sp_fixusers'
> SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
>
> RESTORE DATABASE @.db_name
> FROM DISK = @.backup_location
> WITH REPLACE
> Begin
> EXEC @.proc1
> EXEC @.proc2
> END
>|||On Thu, 29 Sep 2005 16:21:18 +0200, Jason wrote:

>Hi,
>I've a problem executing sp_changedbowner in my stored procedure which
>is saved in the master's stored procedures.
>The code fails after executing @.proc1. The errors says that i could not
>find proc2.
>Here's the code:
>CREATE PROC usp_RestoreDB(
>@.db_name varchar(10),
>@.backup_location varchar(255),
>@.login varchar(20)
> )
>AS
>DECLARE @.proc1 varchar(100), @.proc2 varchar(100)
>SET @.proc1 = @.db_name + '..sp_fixusers'
>SET @.proc2 = @.db_name + '..sp_changedbowner @.loginame = ' + @.login
>
>RESTORE DATABASE @.db_name
> FROM DISK = @.backup_location
> WITH REPLACE
>Begin
>EXEC @.proc1
>EXEC @.proc2
>END
Hi Jason,
Try changing the logic for proc2 to
(...)
SET @.proc2 = @.db_name + '..sp_changedbowner'
(...)
EXEC @.proc2 @.loginame = @.login
(...)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Executing DTS from within SSIS

Hi All,

I have a scenario where I am executing bunch of DTS packages from within an SSIS package. These packages are saved as structured files and everything works fine. However I am concerned about one thing which is whether these SSIS/DTS packges will run fine on my production server where I dont have any SQL SERVER 2000 /2005 installed , it just has SSIS installed on it. Appreciate all help.

Thanks

I did a little research on MSDN and other websites and I think it should not be any problem executing the DTS packages from within SSIS packages on a server having just Integration Services Installed on it.

http://msdn2.microsoft.com/en-us/library/ms137907.aspx

Thanks

|||

Installing SSIS gives basic DTS suport for free, so it will already be there. Notable exceptions are the DTS AS Processing Task and Data Mining Task.

Installing Run-time Support for SQL Server 2000 DTS Packages

You can run DTS packages and SSIS packages on the same computer.

When you select Integration Services for installation, Setup also installs support for DTS packages, including the DTS runtime and DTS package enumeration in SQL Server Management Studio. Support in the runtime is enhanced to enable DTS packages to access SQL Server 2005 data sources.

SQL Server 2005 Integration Services Backward Compatibility
(http://msdn2.microsoft.com/en-us/library/ms143706.aspx#)

Executing 2 SQL script files

I have 2 Transact-SQL scripts saved in 2 files :
ClearData - HR.sql (DDL and DML)
ClearData - Others.sql (only DDL)
I want to write a Transact-SQL script which can executes the above 2 files.
And I want to save it in a seperate file (.sql extension). How can I do
that?
Thanks
RizwanI know I can do it if I created 2 stored procedures for "ClearData - HR.sql"
and "ClearData - Others.sql" respectively and then calling these stored
procedures from my script.
But what I would love to do is call a sql script file from another sql
script file and execute it. Any input?
Thanks
Rizwan
"Rizwan" <hussains@.pendylum.com> wrote in message
news:2CT4e.16736$Fy3.977058@.news20.bellglobal.com...
> I have 2 Transact-SQL scripts saved in 2 files :
> ClearData - HR.sql (DDL and DML)
> ClearData - Others.sql (only DDL)
> I want to write a Transact-SQL script which can executes the above 2
files.
> And I want to save it in a seperate file (.sql extension). How can I do
> that?
> Thanks
> Rizwan
>|||Hi
I am not sure exactly what you require but there is the :r command when
running osql interactively see
http://msdn.microsoft.com/library/d...br />
1wxl.asp
This may also help:
http://groups-beta.google.com/group...
d4ca6efcd9ac3
John
"Rizwan" wrote:

> I know I can do it if I created 2 stored procedures for "ClearData - HR.sq
l"
> and "ClearData - Others.sql" respectively and then calling these stored
> procedures from my script.
> But what I would love to do is call a sql script file from another sql
> script file and execute it. Any input?
> Thanks
> Rizwan
>
> "Rizwan" <hussains@.pendylum.com> wrote in message
> news:2CT4e.16736$Fy3.977058@.news20.bellglobal.com...
> files.
>
>sql

Execute sql scripts saved in a text column of a table

How can I execute sql scripts saved in a text column of a table?
Thanks,
PeterUse dynamic sql statement
EXAMPLE (using Northwind database)
DECLARE @.vSQL VARCHAR(1000), @.numrows INT
SELECT @.numrows = 25
SELECT @.vSQL = 'SELECT TOP ' + CONVERT(VARCHAR, @.numrows) + ' * FROM
Products ORDER BY ProductName'
EXECUTE(@.vSQL)
This evaluates to:
SELECT TOP 25 * FROM Products ORDER BY ProductName
Good luck and hope it helps!
"Peter" wrote:

> How can I execute sql scripts saved in a text column of a table?
>
> Thanks,
> Peter|||Hi,
Go for a cursor that will loop through all the rows and get the text field
in a variable and use dynamic SQL (EXEC or sp_Executesql )
But again, is it a text field or varchar field?
If its text and can span over 8000 characters.
Then you will have to split it to 8000 char length strings, say str1, str2
then you can use
exec(str1 +str2)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Peter" wrote:

> How can I execute sql scripts saved in a text column of a table?
>
> Thanks,
> Peter|||If one has a query exceeding 8000 characters, using dynamic SQL is the least
of the worries.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:85C96AC5-6DD2-44B8-B0EE-79737C41C94B@.microsoft.com...
> Hi,
> Go for a cursor that will loop through all the rows and get the text field
> in a variable and use dynamic SQL (EXEC or sp_Executesql )
> But again, is it a text field or varchar field?
> If its text and can span over 8000 characters.
> Then you will have to split it to 8000 char length strings, say str1, str2
> then you can use
> exec(str1 +str2)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
> "Peter" wrote:
>

Execute package from query analyzer

I have SQL 2K and I have created a local package to import data from Access
mdb and I have saved it.
I would like to know if this can be executed from SQL query analyzer.
Thanks
DimitrisHi
Use xp_cmdshell with DTSRun program that run the Package
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||If you create a job for your package you can :-
USE msdb
EXEC sp_start_job @.job_name = 'Nightly Backup'
If not you can utilise DTSRUN :-
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
/Mpackage_password
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||I have tried:
USE msdb
EXEC sp_start_job @.job_name = 'FACOM IMPORT'
and I get an error:
The specified @.job_name ('FACOM IMPORT') does not exist.
The package I have created is saved in Data Transformation Services -->
Local Packages
Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
> If you create a job for your package you can :-
> USE msdb
> EXEC sp_start_job @.job_name = 'Nightly Backup'
>
> If not you can utilise DTSRUN :-
> To execute a DTS package saved in the SQL Server msdb database, use:
> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
> /Mpackage_password
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>>I have SQL 2K and I have created a local package to import data from
>>Access mdb and I have saved it.
>> I would like to know if this can be executed from SQL query analyzer.
>> Thanks
>> Dimitris
>|||sp_start_job starts a SQL Server agent job, not a package. You can create a job start in turn starts
the package, or perhaps use xp_cmdshell as already suggested.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:%23$uWg0MeGHA.4304@.TK2MSFTNGP05.phx.gbl...
>I have tried:
> USE msdb
> EXEC sp_start_job @.job_name = 'FACOM IMPORT'
> and I get an error:
> The specified @.job_name ('FACOM IMPORT') does not exist.
> The package I have created is saved in Data Transformation Services --> Local Packages
> Ï "Ryan" <Ryan_Waight@.nospam.hotmail.com> Ýãñáøå óôï ìÞíõìá
> news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>> If you create a job for your package you can :-
>> USE msdb
>> EXEC sp_start_job @.job_name = 'Nightly Backup'
>>
>> If not you can utilise DTSRUN :-
>> To execute a DTS package saved in the SQL Server msdb database, use:
>> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password
>>
>> --
>> HTH. Ryan
>>
>> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>>I have SQL 2K and I have created a local package to import data from Access mdb and I have saved
>>it.
>> I would like to know if this can be executed from SQL query analyzer.
>> Thanks
>> Dimitris
>>
>

Execute package from query analyzer

I have SQL 2K and I have created a local package to import data from Access
mdb and I have saved it.
I would like to know if this can be executed from SQL query analyzer.
Thanks
DimitrisHi
Use xp_cmdshell with DTSRun program that run the Package
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||If you create a job for your package you can :-
USE msdb
EXEC sp_start_job @.job_name = 'Nightly Backup'
If not you can utilise DTSRUN :-
To execute a DTS package saved in the SQL Server msdb database, use:
dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
/Mpackage_password
HTH. Ryan
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>I have SQL 2K and I have created a local package to import data from Access
>mdb and I have saved it.
> I would like to know if this can be executed from SQL query analyzer.
> Thanks
> Dimitris
>|||I have tried:
USE msdb
EXEC sp_start_job @.job_name = 'FACOM IMPORT'
and I get an error:
The specified @.job_name ('FACOM IMPORT') does not exist.
The package I have created is saved in Data Transformation Services -->
Local Packages
"Ryan" <Ryan_Waight@.nospam.hotmail.com>
news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
> If you create a job for your package you can :-
> USE msdb
> EXEC sp_start_job @.job_name = 'Nightly Backup'
>
> If not you can utilise DTSRUN :-
> To execute a DTS package saved in the SQL Server msdb database, use:
> dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name
> /Mpackage_password
>
> --
> HTH. Ryan
>
> "Dimitris Nikolakakis" <dn@.hol.gr> wrote in message
> news:uWxXelMeGHA.3556@.TK2MSFTNGP02.phx.gbl...
>|||sp_start_job starts a SQL Server agent job, not a package. You can create a
job start in turn starts
the package, or perhaps use xp_cmdshell as already suggested.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dimitris Nikolakakis" <dn@.hol.gr> wrote in message news:%23$uWg0MeGHA.4304@.TK2MSFTNGP05.phx
.gbl...
>I have tried:
> USE msdb
> EXEC sp_start_job @.job_name = 'FACOM IMPORT'
> and I get an error:
> The specified @.job_name ('FACOM IMPORT') does not exist.
> The package I have created is saved in Data Transformation Services --> Lo
cal Packages
> "Ryan" <Ryan_Waight@.nospam.hotmail.com>
> news:OmFQkrMeGHA.3900@.TK2MSFTNGP05.phx.gbl...
>

Execute DTS from .sql batch

1)
How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
sql batch ?
2)
How do i execute DTS job "myDTS" which is saved on sql server ? from sql
batch ?1) You can run it via xp_cmdshell using the dtsrun command line tool. See
BOL for details e.g
exec xp_cmdshell 'dtsrun /Ffilename /Npackage_name'
2) If you mean a SQL job then you can call sp_start_job. See BOL for
details.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:F2732A01-18FE-4326-BAF7-286DE1110DC1@.microsoft.com...
> 1)
> How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
> sql batch ?
> 2)
> How do i execute DTS job "myDTS" which is saved on sql server ? from sql
> batch ?

Execute DTS from .sql batch

1)
How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
sql batch ?
2)
How do i execute DTS job "myDTS" which is saved on sql server ? from sql
batch ?
1) You can run it via xp_cmdshell using the dtsrun command line tool. See
BOL for details e.g
exec xp_cmdshell 'dtsrun /Ffilename /Npackage_name'
2) If you mean a SQL job then you can call sp_start_job. See BOL for
details.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:F2732A01-18FE-4326-BAF7-286DE1110DC1@.microsoft.com...
> 1)
> How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
> sql batch ?
> 2)
> How do i execute DTS job "myDTS" which is saved on sql server ? from sql
> batch ?

Execute DTS from .sql batch

1)
How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
sql batch ?
2)
How do i execute DTS job "myDTS" which is saved on sql server ? from sql
batch ?1) You can run it via xp_cmdshell using the dtsrun command line tool. See
BOL for details e.g
exec xp_cmdshell 'dtsrun /Ffilename /Npackage_name'
2) If you mean a SQL job then you can call sp_start_job. See BOL for
details.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Mikus" <Mikus@.discussions.microsoft.com> wrote in message
news:F2732A01-18FE-4326-BAF7-286DE1110DC1@.microsoft.com...
> 1)
> How do i execute myDTS.dts file which is located on c:\...\myDTS.dts from
> sql batch ?
> 2)
> How do i execute DTS job "myDTS" which is saved on sql server ? from sql
> batch ?