copy of table from another database into your database

We copy a table from a progress database into our sql server database that we use as a read-only table. We perform this action through a job.

Is there a way to have a read only table from another database that is put into your database that will always be insinc with the original copy to use as a read only copy, that as the original table is updated, it will proprogate to your copied table?

Would setting up a package to import the data from the other database on a schedule work? In SQL Server, you can do an import and you have the option to save the import as a package and schedule it to run at your discretion.

|||

If you require the tables be kept in sync, then the easiest way would be to use a link from the source to the destination server, and have triggers on the source populate the destination. This does carry a performance penalty, of course.

A more performant solution would be to have the triggers on the source write their updates to an update table on the same database, and poll it for changes every 5 minutes or so, but you're not guaranteed to have the DBs in sync then.

Copy job from one server to another

How do you copy a job from one server to another. Identical databases different servers with different names
Hi,
There are 3 options:-
1.
You can generate the script for all jobs ( Enterprise manager -- Right click
on the jobs
node in SQl Agent -- Jobs| All tasks| Generate sql script, Save it as file).
Run this
script in the destination server.
2.
"Transfer Jobs" Task in DTS, That will transfer the jobs to new server.
3.
Jobs, operators , Alerts ,...are stored in msdb database.If the destination
server
is a new one and if you do not have any existing stuffs in msdb , you can
even restore a msdb backup from the source server.
-
Thanks
Hari
MCDBA
"cbriscoejr" <cbriscoejr@.discussions.microsoft.com> wrote in message
news:B1336E38-C8C8-4F1B-BB8D-87E92ADD338F@.microsoft.com...
> How do you copy a job from one server to another. Identical databases
different servers with different names

Copy files from one server to another

hi,
I have a sql box under diff domain. I login as sqladmin mapped a network
drive pointed to the dir that I need to copy the files.
I created a job under command prompt--copy /Y z:\*'* \\server2\myfoleder\
If I run it under ms dos --OK, but running it through sql server job, got
this error:
The system cannot find the drive specified.
Any ideas
ThanksdOk, it is all a matter of security contexts:
WHO are you when you run from the command line?
WHO are you when you run a SQL Server Job:
If job runs as system administrator?
If job runs as a non-system administrator?
Does whatever account is actually running the job actually have rights to
the drive? Apparently not.
Also, FWIW, it is usually better not to use drive letters. Make a share and
use it with the standard non-dos notation.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:%23THGzfPTGHA.4752@.TK2MSFTNGP10.phx.gbl...
> hi,
> I have a sql box under diff domain. I login as sqladmin mapped a network
> drive pointed to the dir that I need to copy the files.
> I created a job under command prompt--copy /Y z:\*'* \\server2\myfoleder\
> If I run it under ms dos --OK, but running it through sql server job,
> got this error:
> The system cannot find the drive specified.
>
> Any ideas
> Thanksd
>

Copy file on network

I have a job which call a SP and that SP generates a excel file dump using
bcp. Now form my website i want to access that excel file. Both my webserver
and db server is on different meachine.
How can i achive this?
can BCP generate output on network. Or can i copy generated file to
webserver using some command through SP ?Vikram
If I understood properly , you can create a linked server to EXCEL file.
HOWTO: Use Excel w/ SQL Linked Servers &
Distributed Queries
http://support.microsoft.com/suppor...s/q306/3/97.asp
"Vikram" <aa@.aa> wrote in message
news:uNVc7FPPGHA.1460@.TK2MSFTNGP10.phx.gbl...
>I have a job which call a SP and that SP generates a excel file dump using
> bcp. Now form my website i want to access that excel file. Both my
> webserver
> and db server is on different meachine.
> How can i achive this?
> can BCP generate output on network. Or can i copy generated file to
> webserver using some command through SP ?
>
>|||But in linked server i have to have excel file. But i do not want this, i
want to have excel file generated by bcp...
any other way ?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ehRxqMPPGHA.456@.TK2MSFTNGP15.phx.gbl...
> Vikram
> If I understood properly , you can create a linked server to EXCEL file.
> HOWTO: Use Excel w/ SQL Linked Servers &
> Distributed Queries
> http://support.microsoft.com/suppor...s/q306/3/97.asp
>
>
> "Vikram" <aa@.aa> wrote in message
> news:uNVc7FPPGHA.1460@.TK2MSFTNGP10.phx.gbl...
using
>|||DTS?
"Vikram" <aa@.aa> wrote in message
news:%23izbiaPPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> But in linked server i have to have excel file. But i do not want this, i
> want to have excel file generated by bcp...
> any other way ?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ehRxqMPPGHA.456@.TK2MSFTNGP15.phx.gbl...
> using
>|||NO I am suing BCP , calling it from sp using xp_cmdshell
I dont want to use DTS as sp whic return data use temp table and also i have
many sp whose data i have to export to excel
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%2370krlPPGHA.2124@.TK2MSFTNGP14.phx.gbl...
> DTS?
>
> "Vikram" <aa@.aa> wrote in message
> news:%23izbiaPPGHA.3164@.TK2MSFTNGP11.phx.gbl...
i
file.
>|||> How can i achive this?
> can BCP generate output on network. Or can i copy generated file to
> webserver using some command through SP ?
UNC path depending on your permissions.
EXEC master..xp_cmdshell "bcp DB.dbo.Table out
\\SERVER1\Share\test.xls -c -T"
ftp via xp_cmdshell could work.

Copy databases

Hi,
Maybe a stupid question, but I'm trying to copy some databases from one server to another. The copy databases wizard says the job is successful and I can see that the job has been done on the remote server. But the copied databases are not there. What do I do?
Anyone?|||Make sure you connected (during the copy) to the right (the same as you have inspected later)instance of SQL Server.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||How do you know if the databases are not there? do select * from sys.databases and see if they are there.|||Aren't they supposed to show up in the database tree in Object explorer? I can't see them...only the ones that I exported, but copying is supposed to be a better method.
|||

they are but you need to refresh the tree, by running the query I asked you to I was asking to check if they were really there.

Are the databases you are looking for in the resultset from the query I asked you to run?

|||I did and no they are not there.
|||Hmmm very strange, my only suggestion is to try again and look for errors.|||'Execute SQL Server Agent job' generates an error and the log says 'Login failed for user'.
|||So I think some Agent debugging might be in order then to make sure it is working ok, Management Tools Group folks can help, then come back.sqlsql

Copy Database Wizard

When I choose to have the database copy occur on a daily
basis, I can't find the job or package (whichever it is
creating) anywhere. Does anyone know how this gets
saved/scheduled or where I can view the properties/add
steps?The package that gets created should be on the destination server.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Copy Database problems SP2

Last night I installed SQL Server 2005 SP2. The install went fine and everything seemed to be fine. However a scheduled Database Copy job failed. I deleted the current job and recreated it using the "Copy Database" wizard. I used all the default values with the following exceptions: I used the "SQL Management Object Method" instead of the detach database and I use the "drop the database on destination". I tell it to run the job immediately and the job runs fine.

I then try to run the job again from SQL Agent -> jobs and it fails. I run it again and it runs fine. The pattern repeats, succeed -> fails -> succeed -> fails....

Here is the error message I receive in the history:

Message
Executed as user: EVAASSQLDEV\sqlagent. ...00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:30:06 AM Progress: 2007-03-30 09:30:07.41 Source: EVAASSQLDEV_evaassqldev_Transfer Objects Task Task just started the execution.: 0% complete End Progress Error: 2007-03-30 09:30:16.14 Code: 0x00000000 Source: EVAASSQLDEV_evaassqldev_Transfer Objects Task Description: Alter failed for Database 'PathwaysNewDev_new'. StackTrace: at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl() at Microsoft.SqlServer.Management.Smo.Database.Alter(TerminationClause terminationClause) at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.SwitchDatabaseAccess(Database database, DatabaseUserAccess desiredAccess) at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.CheckLocalandDestinationStatus(Database srcDatabase, DatabaseInfo dbDetail) at Mic... The package execution fa... The step failed.
How about privileges for that SQLAgent service account on both the servers?|||It is the same server. Just copying a development db to a testing db. One more thing though, when it fails it puts the destination DB in single user mode. I'm guessing it is trying to get exclusive control over the destination.

copy database feature

Hello,
I am trying to use the copy database funtion from sql2000 to sql2005. It
fails miserably and logs only says:
step 0: The job failed. The Job was invoked by User domain\user. The last
step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
step 1: Executed as user: domain\user. The package execution failed. The
step failed.
There is no destination db already existing, the feature drop any database
on the destination server is activated, it fails at Execute SQL Server Agent
Job.
Although the database is created there is no tables created.
The user in the source and destination server is the same for the sql
service and the agent.
I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
Thanks for the help
Wilfrid
If you are copying the whole db why not just restore from a full backup of
the 2000 db on 2005?
Andrew J. Kelly SQL MVP
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:4399b19d$0$22164$afc38c87@.news.easynet.fr...
> Hello,
> I am trying to use the copy database funtion from sql2000 to sql2005. It
> fails miserably and logs only says:
> step 0: The job failed. The Job was invoked by User domain\user. The
> last step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
> step 1: Executed as user: domain\user. The package execution failed. The
> step failed.
> There is no destination db already existing, the feature drop any database
> on the destination server is activated, it fails at Execute SQL Server
> Agent Job.
> Although the database is created there is no tables created.
> The user in the source and destination server is the same for the sql
> service and the agent.
> I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
> Thanks for the help
> Wilfrid
>
|||The process of dumping a db from one server and then restore it to another server takes longer than just using a feature
available form sql2k5.
I was using, with sql2k, the exporting objects functionnality from one server to another but this feature is not
available anymore with sql 2k5 and I believe it has been replaced with the copy db.
Andrew J. Kelly wrote:
> If you are copying the whole db why not just restore from a full backup of
> the 2000 db on 2005?
>
|||The copy db functionality was available in 200 as well and is not new. But
it is usually not faster or cleaner than a backup and restore. You should
already be doing regular backups so all you have to do is issue the restore.
You don't even have to copy the file first. As long as the folder is
accessible from the other server you can restore directly from there.
Andrew J. Kelly SQL MVP
"grille11" <grille11@.yahoo.com> wrote in message
news:439b429e$0$22164$afc38c87@.news.easynet.fr...[vbcol=seagreen]
> The process of dumping a db from one server and then restore it to another
> server takes longer than just using a feature available form sql2k5.
> I was using, with sql2k, the exporting objects functionnality from one
> server to another but this feature is not available anymore with sql 2k5
> and I believe it has been replaced with the copy db.
>
> Andrew J. Kelly wrote:

copy database feature

Hello,
I am trying to use the copy database funtion from sql2000 to sql2005. It
fails miserably and logs only says:
step 0: The job failed. The Job was invoked by User domain\user. The last
step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
step 1: Executed as user: domain\user. The package execution failed. The
step failed.
There is no destination db already existing, the feature drop any database
on the destination server is activated, it fails at Execute SQL Server Agent
Job.
Although the database is created there is no tables created.
The user in the source and destination server is the same for the sql
service and the agent.
I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
Thanks for the help
WilfridIf you are copying the whole db why not just restore from a full backup of
the 2000 db on 2005?
Andrew J. Kelly SQL MVP
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:4399b19d$0$22164$afc38c87@.news.easynet.fr...
> Hello,
> I am trying to use the copy database funtion from sql2000 to sql2005. It
> fails miserably and logs only says:
> step 0: The job failed. The Job was invoked by User domain\user. The
> last step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
> step 1: Executed as user: domain\user. The package execution failed. The
> step failed.
> There is no destination db already existing, the feature drop any database
> on the destination server is activated, it fails at Execute SQL Server
> Agent Job.
> Although the database is created there is no tables created.
> The user in the source and destination server is the same for the sql
> service and the agent.
> I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
> Thanks for the help
> Wilfrid
>|||The process of dumping a db from one server and then restore it to another s
erver takes longer than just using a feature
available form sql2k5.
I was using, with sql2k, the exporting objects functionnality from one serve
r to another but this feature is not
available anymore with sql 2k5 and I believe it has been replaced with the c
opy db.
Andrew J. Kelly wrote:
> If you are copying the whole db why not just restore from a full backup of
> the 2000 db on 2005?
>|||The copy db functionality was available in 200 as well and is not new. But
it is usually not faster or cleaner than a backup and restore. You should
already be doing regular backups so all you have to do is issue the restore.
You don't even have to copy the file first. As long as the folder is
accessible from the other server you can restore directly from there.
Andrew J. Kelly SQL MVP
"grille11" <grille11@.yahoo.com> wrote in message
news:439b429e$0$22164$afc38c87@.news.easynet.fr...[vbcol=seagreen]
> The process of dumping a db from one server and then restore it to another
> server takes longer than just using a feature available form sql2k5.
> I was using, with sql2k, the exporting objects functionnality from one
> server to another but this feature is not available anymore with sql 2k5
> and I believe it has been replaced with the copy db.
>
> Andrew J. Kelly wrote:

copy database feature

Hello,
I am trying to use the copy database funtion from sql2000 to sql2005. It
fails miserably and logs only says:
step 0: The job failed. The Job was invoked by User domain\user. The last
step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
step 1: Executed as user: domain\user. The package execution failed. The
step failed.
There is no destination db already existing, the feature drop any database
on the destination server is activated, it fails at Execute SQL Server Agent
Job.
Although the database is created there is no tables created.
The user in the source and destination server is the same for the sql
service and the agent.
I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
Thanks for the help
WilfridIf you are copying the whole db why not just restore from a full backup of
the 2000 db on 2005?
--
Andrew J. Kelly SQL MVP
"Wilfrid" <grille11@.yahoo.com> wrote in message
news:4399b19d$0$22164$afc38c87@.news.easynet.fr...
> Hello,
> I am trying to use the copy database funtion from sql2000 to sql2005. It
> fails miserably and logs only says:
> step 0: The job failed. The Job was invoked by User domain\user. The
> last step to run was step 1 (CDW_DBSERVERNAME_USERMACHINE_1_Step).
> step 1: Executed as user: domain\user. The package execution failed. The
> step failed.
> There is no destination db already existing, the feature drop any database
> on the destination server is activated, it fails at Execute SQL Server
> Agent Job.
> Although the database is created there is no tables created.
> The user in the source and destination server is the same for the sql
> service and the agent.
> I am a bit lost. Is ity possible to do that between sql2k and sql2k5?
> Thanks for the help
> Wilfrid
>|||The process of dumping a db from one server and then restore it to another server takes longer than just using a feature
available form sql2k5.
I was using, with sql2k, the exporting objects functionnality from one server to another but this feature is not
available anymore with sql 2k5 and I believe it has been replaced with the copy db.
Andrew J. Kelly wrote:
> If you are copying the whole db why not just restore from a full backup of
> the 2000 db on 2005?
>|||The copy db functionality was available in 200 as well and is not new. But
it is usually not faster or cleaner than a backup and restore. You should
already be doing regular backups so all you have to do is issue the restore.
You don't even have to copy the file first. As long as the folder is
accessible from the other server you can restore directly from there.
--
Andrew J. Kelly SQL MVP
"grille11" <grille11@.yahoo.com> wrote in message
news:439b429e$0$22164$afc38c87@.news.easynet.fr...
> The process of dumping a db from one server and then restore it to another
> server takes longer than just using a feature available form sql2k5.
> I was using, with sql2k, the exporting objects functionnality from one
> server to another but this feature is not available anymore with sql 2k5
> and I believe it has been replaced with the copy db.
>
> Andrew J. Kelly wrote:
>> If you are copying the whole db why not just restore from a full backup
>> of the 2000 db on 2005?

Copy Database error on Execute SQL Server Agent Job

Interestingly I can get further with Copy Database than I did in the last beta. It still won't finish successfully though. Here's a log from one of my attempts.

Thanks in advance!

#Fields: event,computer,operator,source,sourceid,executionid,starttime,endtime,datacode,databytes,message
OnPreValidate,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPreValidate,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPostValidate,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPostValidate,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
PackageStart,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,Beginning of package execution.

Diagnostic,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,Maximum concurrent executables are set to 1.

OnPreExecute,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPreExecute,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPreValidate,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnPostValidate,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,(null)
OnProgress,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,Task just started the execution.
OnInformation,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,Connecting to server CSIADRESKO
OnInformation,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:35 PM,11/15/2005 1:15:35 PM,0,0x,Connecting to server CSIADRESKO
OnError,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,Failed to connect to server CSIADRESKO.
StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)
InnerException-->Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
OnError,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,Failed to connect to server CSIADRESKO.
StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)
InnerException-->Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParser.ConsumePreLoginHandshake(Boolean encrypt, Boolean trustServerCert, Boolean& marsCapable)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
OnProgress,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,100,0x,Transfer objects finished execution.
OnTaskFailed,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,(null)
OnPostExecute,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,(null)
OnWarning,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,-2147381246,0x,The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

OnPostExecute,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,(null)
PackageEnd,RATBERT,NT AUTHORITY\SYSTEM,CDW_CSIADRESKO_RATBERT_0,{00F97964-A8B9-4816-9A87-A39C952A31AA},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,1,0x,End of package execution.

////////////////
HERE'S ANOTHER entry from the event log.

Event Name: OnError

Message: Failed to connect to server CSIADRESKO.

StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)

InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

StackTrace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)

at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)

at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

at System.Data.SqlClient.SqlConnection.Open()

at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)

at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()

Operator: NT AUTHORITY\SYSTEM

Source Name: CDW_CSIADRESKO_RATBERT_1

Source ID: {37C99953-92D4-40ED-B1CA-943B9D08D513}

Execution ID: {45DAB9CD-CEF6-4F40-8FAA-5F79967B9B70}

Start Time: 11/15/2005 1:25:56 PM

End Time: 11/15/2005 1:25:56 PM

Data Code: 0

For more information, see Help and Support Center at

Hello Alex Dresko

The error seems to be a time-out error that happened when the SSIS task was attempting to connect to the SQL server.

This could be a generic time-out that happens for all connections to the remote server. You could try to connect to the remote server using object explorer / query analyzer (or) execute the task [Agent job] manually on the remote server to see if that solves the issue.

If a time-out occurs only when using CDW, please provide details on what version of SQL server your source server is, what trasnfer type you are using (Detach&Attach / SMO transfer) , any other environment specific information that you suspect here.

Thanks,
[Venkat]|||

This appears to be an SSIS configuration issue:

OnError,RATBERT,NT AUTHORITY\SYSTEM,CSIADRESKO_RATBERT_Transfer Objects Task,{F7EFCB8D-7553-4E4D-9859-7345D3F6D922},{3F1C9CB0-A9E8-47E8-B0FA-E07F7EA9AE55},11/15/2005 1:15:56 PM,11/15/2005 1:15:56 PM,0,0x,Failed to connect to server CSIADRESKO.
StackTrace: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.OpenConnection(Server& server, ServerProperty serverProp)
This error may be related to whether the server is configured to allow Named Pipe connections:

InnerException-->An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

Have you configured your server to accept Named Pipe connections? You can use the Surface Area Configuration tool (Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration) to do that.

You might also consider posting this issue in the SSIS forum (http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=80&SiteID=1) - they might have additional information on how to configure SSIS to do what you need.

|||Sweet! My server was set up to use TCP only, while my workstation was set to Local only. Both were upgrades from betas.

I changed both to tcp/named pipes and everything works!

Thanks SO much! I would have never thought about that.

COpy Database

I ran the copy database wizard to set up a job to copy three dbs from one
server to another. The job copied two of the db's but not the third one.
It is possible that there was an open connection to that db. Will that
prevent a copy from executing? Also, it appears that this process is
accomplished by detaching the dbs as when I came in the two databases that
were copied were not reattached on the main server. Would the failure to
copy the other db prevent the reattach from executing?
Barry
looks like it didn't finishing attaching dbs. copy db wizard does detach and
attach the database. That's why I usually don't like to use it.
<barryfz@.home.com> wrote in message
news:ubR8Y%23zlEHA.396@.tk2msftngp13.phx.gbl...
> I ran the copy database wizard to set up a job to copy three dbs from one
> server to another. The job copied two of the db's but not the third one.
> It is possible that there was an open connection to that db. Will that
> prevent a copy from executing? Also, it appears that this process is
> accomplished by detaching the dbs as when I came in the two databases that
> were copied were not reattached on the main server. Would the failure to
> copy the other db prevent the reattach from executing?
> Barry
>
|||Hi Barry,
From your descriptions, I understood that your Copy Database Wizard failed
to transfer the third database while the first two succeed. Have I
understood you? If there is anything I misunderstood, please feel free to
let me know.
Based on my scope, to avoid data loss or inconsistency, you must be sure
there are no active sessions attached to the database being moved or
copied. If there are active sessions, the Copy Database Wizard will not
run. You could also find the descriptions above in BOL, Using the Copy
Database Wizard. Which is to say it is entirely possible the active session
in your third database lead to that copy failure.
Additionaly, here are some documents for you on how to transfer databases
between SQL Servers.
Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default...b;EN-US;224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default...B;en-us;314546
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Barry,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

COpy Database

I ran the copy database wizard to set up a job to copy three dbs from one
server to another. The job copied two of the db's but not the third one.
It is possible that there was an open connection to that db. Will that
prevent a copy from executing? Also, it appears that this process is
accomplished by detaching the dbs as when I came in the two databases that
were copied were not reattached on the main server. Would the failure to
copy the other db prevent the reattach from executing'
Barrylooks like it didn't finishing attaching dbs. copy db wizard does detach and
attach the database. That's why I usually don't like to use it.
<barryfz@.home.com> wrote in message
news:ubR8Y%23zlEHA.396@.tk2msftngp13.phx.gbl...
> I ran the copy database wizard to set up a job to copy three dbs from one
> server to another. The job copied two of the db's but not the third one.
> It is possible that there was an open connection to that db. Will that
> prevent a copy from executing? Also, it appears that this process is
> accomplished by detaching the dbs as when I came in the two databases that
> were copied were not reattached on the main server. Would the failure to
> copy the other db prevent the reattach from executing'
> Barry
>|||Hi Barry,
From your descriptions, I understood that your Copy Database Wizard failed
to transfer the third database while the first two succeed. Have I
understood you? If there is anything I misunderstood, please feel free to
let me know.
Based on my scope, to avoid data loss or inconsistency, you must be sure
there are no active sessions attached to the database being moved or
copied. If there are active sessions, the Copy Database Wizard will not
run. You could also find the descriptions above in BOL, Using the Copy
Database Wizard. Which is to say it is entirely possible the active session
in your third database lead to that copy failure.
Additionaly, here are some documents for you on how to transfer databases
between SQL Servers.
Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224071
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?scid=KB;en-us;314546
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Barry,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

copy a job from one database to another

Hi, new to sql server 2000, wondering if there is a easy way to copy a job
from my test database to production database...they are on same instance...
Thanks,
GerryYou can right click on the Job, select [All Tasks], and select [Generate SQL
Script].
Then edit the file, changing the name of the server, and run the script on
the Production server.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"gerry m" <gerrym@.discussions.microsoft.com> wrote in message
news:706D9450-E992-4B7C-811F-745E3DA3C270@.microsoft.com...
> Hi, new to sql server 2000, wondering if there is a easy way to copy a job
> from my test database to production database...they are on same
> instance...
> Thanks,
> Gerry|||Thank you Anne...I will give this a try tonite...
Gerry
"Arnie Rowland" wrote:
> You can right click on the Job, select [All Tasks], and select [Generate SQL
> Script].
> Then edit the file, changing the name of the server, and run the script on
> the Production server.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
> news:706D9450-E992-4B7C-811F-745E3DA3C270@.microsoft.com...
> > Hi, new to sql server 2000, wondering if there is a easy way to copy a job
> > from my test database to production database...they are on same
> > instance...
> >
> > Thanks,
> >
> > Gerry
>
>|||Anne, is there a way to keep it structured with steps and to set up on a new
schedule (like it is in the test db?)
Thanks,
Gerry
"gerry m" wrote:
> Thank you Anne...I will give this a try tonite...
> Gerry
> "Arnie Rowland" wrote:
> > You can right click on the Job, select [All Tasks], and select [Generate SQL
> > Script].
> >
> > Then edit the file, changing the name of the server, and run the script on
> > the Production server.
> >
> > --
> > Arnie Rowland
> > Most good judgment comes from experience.
> > Most experience comes from bad judgment.
> > - Anonymous
> >
> >
> > "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
> > news:706D9450-E992-4B7C-811F-745E3DA3C270@.microsoft.com...
> > > Hi, new to sql server 2000, wondering if there is a easy way to copy a job
> > > from my test database to production database...they are on same
> > > instance...
> > >
> > > Thanks,
> > >
> > > Gerry
> >
> >
> >|||The script will contain the same steps and the same schedule. If you need to
change the schedule, it is best done after the Job is in the new server.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"gerry m" <gerrym@.discussions.microsoft.com> wrote in message
news:CF0C8A51-3738-4DC2-A508-36DC1C2FE1AB@.microsoft.com...
> Anne, is there a way to keep it structured with steps and to set up on a
> new
> schedule (like it is in the test db?)
> Thanks,
> Gerry
> "gerry m" wrote:
>> Thank you Anne...I will give this a try tonite...
>> Gerry
>> "Arnie Rowland" wrote:
>> > You can right click on the Job, select [All Tasks], and select
>> > [Generate SQL
>> > Script].
>> >
>> > Then edit the file, changing the name of the server, and run the script
>> > on
>> > the Production server.
>> >
>> > --
>> > Arnie Rowland
>> > Most good judgment comes from experience.
>> > Most experience comes from bad judgment.
>> > - Anonymous
>> >
>> >
>> > "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
>> > news:706D9450-E992-4B7C-811F-745E3DA3C270@.microsoft.com...
>> > > Hi, new to sql server 2000, wondering if there is a easy way to copy
>> > > a job
>> > > from my test database to production database...they are on same
>> > > instance...
>> > >
>> > > Thanks,
>> > >
>> > > Gerry
>> >
>> >
>> >|||Arnie, I am a bit confused...I generated the script and edited it for the new
database name, but am at a loss to work all that script into the 'create new
job' box without having to break it all down into steps
"Arnie Rowland" wrote:
> The script will contain the same steps and the same schedule. If you need to
> change the schedule, it is best done after the Job is in the new server.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
> news:CF0C8A51-3738-4DC2-A508-36DC1C2FE1AB@.microsoft.com...
> > Anne, is there a way to keep it structured with steps and to set up on a
> > new
> > schedule (like it is in the test db?)
> >
> > Thanks,
> >
> > Gerry
> >
> > "gerry m" wrote:
> >
> >> Thank you Anne...I will give this a try tonite...
> >>
> >> Gerry
> >>
> >> "Arnie Rowland" wrote:
> >>
> >> > You can right click on the Job, select [All Tasks], and select
> >> > [Generate SQL
> >> > Script].
> >> >
> >> > Then edit the file, changing the name of the server, and run the script
> >> > on
> >> > the Production server.
> >> >
> >> > --
> >> > Arnie Rowland
> >> > Most good judgment comes from experience.
> >> > Most experience comes from bad judgment.
> >> > - Anonymous
> >> >
> >> >
> >> > "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
> >> > news:706D9450-E992-4B7C-811F-745E3DA3C270@.microsoft.com...
> >> > > Hi, new to sql server 2000, wondering if there is a easy way to copy
> >> > > a job
> >> > > from my test database to production database...they are on same
> >> > > instance...
> >> > >
> >> > > Thanks,
> >> > >
> >> > > Gerry
> >> >
> >> >
> >> >
>
>|||You take the script that was generated, and you execute the entire script in
Query Analyzer. It will then recreate the Job and you can edit using
Enterprise Manager.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"gerry m" <gerrym@.discussions.microsoft.com> wrote in message
news:1639671C-E028-4910-AA43-38012BB3AFFC@.microsoft.com...
> Arnie, I am a bit confused...I generated the script and edited it for the
> new
> database name, but am at a loss to work all that script into the 'create
> new
> job' box without having to break it all down into steps
> "Arnie Rowland" wrote:
>> The script will contain the same steps and the same schedule. If you need
>> to
>> change the schedule, it is best done after the Job is in the new server.
>> --
>> Arnie Rowland
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
>> news:CF0C8A51-3738-4DC2-A508-36DC1C2FE1AB@.microsoft.com...
>> > Anne, is there a way to keep it structured with steps and to set up on
>> > a
>> > new
>> > schedule (like it is in the test db?)
>> >
>> > Thanks,
>> >
>> > Gerry
>> >
>> > "gerry m" wrote:
>> >
>> >> Thank you Anne...I will give this a try tonite...
>> >>
>> >> Gerry
>> >>
>> >> "Arnie Rowland" wrote:
>> >>
>> >> > You can right click on the Job, select [All Tasks], and select
>> >> > [Generate SQL
>> >> > Script].
>> >> >
>> >> > Then edit the file, changing the name of the server, and run the
>> >> > script
>> >> > on
>> >> > the Production server.
>> >> >
>> >> > --
>> >> > Arnie Rowland
>> >> > Most good judgment comes from experience.
>> >> > Most experience comes from bad judgment.
>> >> > - Anonymous
>> >> >
>> >> >
>> >> > "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
>> >> > news:706D9450-E992-4B7C-811F-745E3DA3C270@.microsoft.com...
>> >> > > Hi, new to sql server 2000, wondering if there is a easy way to
>> >> > > copy
>> >> > > a job
>> >> > > from my test database to production database...they are on same
>> >> > > instance...
>> >> > >
>> >> > > Thanks,
>> >> > >
>> >> > > Gerry
>> >> >
>> >> >
>> >> >
>>|||Jobs are server specific, not database. So, you may want to script the job
in Enterprise Manager (right click on the job in Enterprise Manager, to get
to the scripting options) and recreate the job with a different name (you
have to edit the sp_add_job in the script). Once the job is recreated with a
different name, you can edit it to change the database context.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"gerry m" <gerrym@.discussions.microsoft.com> wrote in message
news:706D9450-E992-4B7C-811F-745E3DA3C270@.microsoft.com...
> Hi, new to sql server 2000, wondering if there is a easy way to copy a job
> from my test database to production database...they are on same
> instance...
> Thanks,
> Gerry|||Arnie, thanks for your help and time, this worked fine.
Thanks again,
Gerry
"Arnie Rowland" wrote:
> You take the script that was generated, and you execute the entire script in
> Query Analyzer. It will then recreate the Job and you can edit using
> Enterprise Manager.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
> news:1639671C-E028-4910-AA43-38012BB3AFFC@.microsoft.com...
> > Arnie, I am a bit confused...I generated the script and edited it for the
> > new
> > database name, but am at a loss to work all that script into the 'create
> > new
> > job' box without having to break it all down into steps
> >
> > "Arnie Rowland" wrote:
> >
> >> The script will contain the same steps and the same schedule. If you need
> >> to
> >> change the schedule, it is best done after the Job is in the new server.
> >>
> >> --
> >> Arnie Rowland
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
> >> news:CF0C8A51-3738-4DC2-A508-36DC1C2FE1AB@.microsoft.com...
> >> > Anne, is there a way to keep it structured with steps and to set up on
> >> > a
> >> > new
> >> > schedule (like it is in the test db?)
> >> >
> >> > Thanks,
> >> >
> >> > Gerry
> >> >
> >> > "gerry m" wrote:
> >> >
> >> >> Thank you Anne...I will give this a try tonite...
> >> >>
> >> >> Gerry
> >> >>
> >> >> "Arnie Rowland" wrote:
> >> >>
> >> >> > You can right click on the Job, select [All Tasks], and select
> >> >> > [Generate SQL
> >> >> > Script].
> >> >> >
> >> >> > Then edit the file, changing the name of the server, and run the
> >> >> > script
> >> >> > on
> >> >> > the Production server.
> >> >> >
> >> >> > --
> >> >> > Arnie Rowland
> >> >> > Most good judgment comes from experience.
> >> >> > Most experience comes from bad judgment.
> >> >> > - Anonymous
> >> >> >
> >> >> >
> >> >> > "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
> >> >> > news:706D9450-E992-4B7C-811F-745E3DA3C270@.microsoft.com...
> >> >> > > Hi, new to sql server 2000, wondering if there is a easy way to
> >> >> > > copy
> >> >> > > a job
> >> >> > > from my test database to production database...they are on same
> >> >> > > instance...
> >> >> > >
> >> >> > > Thanks,
> >> >> > >
> >> >> > > Gerry
> >> >> >
> >> >> >
> >> >> >
> >>
> >>
> >>
>
>

copy a job from one database to another

Hi, new to sql server 2000, wondering if there is a easy way to copy a job
from my test database to production database...they are on same instance...
Thanks,
GerryYou can right click on the Job, select [All Tasks], and select [Gene
rate SQL
Script].
Then edit the file, changing the name of the server, and run the script on
the Production server.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"gerry m" <gerrym@.discussions.microsoft.com> wrote in message
news:706D9450-E992-4B7C-811F-745E3DA3C270@.microsoft.com...
> Hi, new to sql server 2000, wondering if there is a easy way to copy a job
> from my test database to production database...they are on same
> instance...
> Thanks,
> Gerry|||Thank you Anne...I will give this a try tonite...
Gerry
"Arnie Rowland" wrote:

> You can right click on the Job, select [All Tasks], and select [Ge
nerate SQL
> Script].
> Then edit the file, changing the name of the server, and run the script on
> the Production server.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
> news:706D9450-E992-4B7C-811F-745E3DA3C270@.microsoft.com...
>
>|||Anne, is there a way to keep it structured with steps and to set up on a new
schedule (like it is in the test db?)
Thanks,
Gerry
"gerry m" wrote:
[vbcol=seagreen]
> Thank you Anne...I will give this a try tonite...
> Gerry
> "Arnie Rowland" wrote:
>|||The script will contain the same steps and the same schedule. If you need to
change the schedule, it is best done after the Job is in the new server.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"gerry m" <gerrym@.discussions.microsoft.com> wrote in message
news:CF0C8A51-3738-4DC2-A508-36DC1C2FE1AB@.microsoft.com...[vbcol=seagreen]
> Anne, is there a way to keep it structured with steps and to set up on a
> new
> schedule (like it is in the test db?)
> Thanks,
> Gerry
> "gerry m" wrote:
>|||Arnie, I am a bit confused...I generated the script and edited it for the ne
w
database name, but am at a loss to work all that script into the 'create new
job' box without having to break it all down into steps
"Arnie Rowland" wrote:

> The script will contain the same steps and the same schedule. If you need
to
> change the schedule, it is best done after the Job is in the new server.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
> news:CF0C8A51-3738-4DC2-A508-36DC1C2FE1AB@.microsoft.com...
>
>|||You take the script that was generated, and you execute the entire script in
Query Analyzer. It will then recreate the Job and you can edit using
Enterprise Manager.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"gerry m" <gerrym@.discussions.microsoft.com> wrote in message
news:1639671C-E028-4910-AA43-38012BB3AFFC@.microsoft.com...[vbcol=seagreen]
> Arnie, I am a bit confused...I generated the script and edited it for the
> new
> database name, but am at a loss to work all that script into the 'create
> new
> job' box without having to break it all down into steps
> "Arnie Rowland" wrote:
>|||Jobs are server specific, not database. So, you may want to script the job
in Enterprise Manager (right click on the job in Enterprise Manager, to get
to the scripting options) and recreate the job with a different name (you
have to edit the sp_add_job in the script). Once the job is recreated with a
different name, you can edit it to change the database context.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"gerry m" <gerrym@.discussions.microsoft.com> wrote in message
news:706D9450-E992-4B7C-811F-745E3DA3C270@.microsoft.com...
> Hi, new to sql server 2000, wondering if there is a easy way to copy a job
> from my test database to production database...they are on same
> instance...
> Thanks,
> Gerry|||Arnie, thanks for your help and time, this worked fine.
Thanks again,
Gerry
"Arnie Rowland" wrote:

> You take the script that was generated, and you execute the entire script
in
> Query Analyzer. It will then recreate the Job and you can edit using
> Enterprise Manager.
> --
> Arnie Rowland
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "gerry m" <gerrym@.discussions.microsoft.com> wrote in message
> news:1639671C-E028-4910-AA43-38012BB3AFFC@.microsoft.com...
>
>

copy a job

Hi,
I need to copy a job to the same istance of Microsoft SQL 2000 (SP4). I'd
like to rename it modify some parameters and re-schedule the database.
I can't find nothing for copy or duplicate the job.. any help? by Enterprise
Manager and Transact-SQL?
Thank's
AndreaRight-click the job, select All Tasks > Generate SQL script. Specify a
filename and click OK.
This generates a script which you can modify and then run to create a
new job.
David Portas
SQL Server MVP
--

copy a job

Hi,
I need to copy a job to the same istance of Microsoft SQL 2000 (SP4). I'd
like to rename it modify some parameters and re-schedule the database.
I can't find nothing for copy or duplicate the job.. any help? by Enterprise
Manager and Transact-SQL?
Thank's
Andrea
Right-click the job, select All Tasks > Generate SQL script. Specify a
filename and click OK.
This generates a script which you can modify and then run to create a
new job.
David Portas
SQL Server MVP

copy a job

Hi,
I need to copy a job to the same istance of Microsoft SQL 2000 (SP4). I'd
like to rename it modify some parameters and re-schedule the database.
I can't find nothing for copy or duplicate the job.. any help? by Enterprise
Manager and Transact-SQL?
Thank's
AndreaRight-click the job, select All Tasks > Generate SQL script. Specify a
filename and click OK.
This generates a script which you can modify and then run to create a
new job.
--
David Portas
SQL Server MVP
--

Converting Traceid issue

Hi,

I am trying to automate a SQL Trace via a stored procedure and a job. The job
executes the stored procedure to start the trace and every 15 minutes, the
job is supposed to stop the trace, clear it from memory, rename the trace
file, and start a new trace so I can select the average duration for this
process. I am getting the following error message:

Procedure expects parameter '@.traceid' of type 'int'

When I try to run this portion of the script (@.traceid is declared as an INT
at the beginning of the job):

Set @.traceid = (select distinct(convert(int,traceid)) from ::fn_trace_getinfo
(default) where value = 'D:\MSSQL\JOBS\HCMDB_RequestQueue_Trace.trc')-- the
name of my trace file

print 'Stop current trace'
exec sp_trace_setstatus @.traceid,0

print 'Erase current trace from memory'
exec sp_trace_setstatus @.traceid,2

print 'Moving file to _1'
exec master..xp_cmdshell 'move D:\MSSQL\JOBS\HCMDB RequestQueue Trace.trc D:\
MSSQL\JOBS\HCMDB_RequestQueue_Trace1.trc',
NO_OUTPUT

I know I must be missing something obvious, but I haven't been able to figure
it out. Any assistance is greatly appreciated.

Thanks,
Michael

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200602/1michael via SQLMonster.com (u13012@.uwe) writes:
> I am trying to automate a SQL Trace via a stored procedure and a job.
> The job executes the stored procedure to start the trace and every 15
> minutes, the job is supposed to stop the trace, clear it from memory,
> rename the trace file, and start a new trace so I can select the average
> duration for this process. I am getting the following error message:
> Procedure expects parameter '@.traceid' of type 'int'
> When I try to run this portion of the script (@.traceid is declared as an
> INT at the beginning of the job):
> Set @.traceid = (select distinct(convert(int,traceid)) from
> ::fn_trace_getinfo> (default) where value =
> 'D:\MSSQL\JOBS\HCMDB_RequestQueue_Trace.trc') -- the
> name of my trace file
> print 'Stop current trace'
> exec sp_trace_setstatus @.traceid,0
>...
> I know I must be missing something obvious, but I haven't been able to
> figure it out. Any assistance is greatly appreciated.

Obvious and obvious... First a hint. Try this:

DECLARE @.traceid int
exec sp_trace_setstatus @.traceid,0

This give the same error as you get. sp_trace_setstatus does not
like the NULL value.

So presumably, you fail to set @.traceid. The value column of
fn_get_tracestatus is sql_variant. Per the conversion rules in
SQL Server, the string literal is converted to sql_variant. I believe
that for to sql_variant values to be equal, they must have the same
base type. But value for the file name, is surely nvarchar.

So adding an N before string literal to make it nvarchar may work.
I would recommand to explicitly convert value to nvarchar(4000).
is sql_variaamt

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for your response. I think I might not have stated the issue clearly
or am not understanding your response. My issue is that the traceid is not
being returned as an INT. There is no problem with the file name and the
nvarchar.

Of the PRINT statements in my code below, I get everything up to and
including 'Stop current trace'. My SET does return the traceid, but not in
integer format.

I'll do further research on the sql_variant though and see if I can get
further with that.

Thanks

Erland Sommarskog wrote:
>> I am trying to automate a SQL Trace via a stored procedure and a job.
>> The job executes the stored procedure to start the trace and every 15
>[quoted text clipped - 17 lines]
>> I know I must be missing something obvious, but I haven't been able to
>> figure it out. Any assistance is greatly appreciated.
>Obvious and obvious... First a hint. Try this:
> DECLARE @.traceid int
> exec sp_trace_setstatus @.traceid,0
>This give the same error as you get. sp_trace_setstatus does not
>like the NULL value.
>So presumably, you fail to set @.traceid. The value column of
>fn_get_tracestatus is sql_variant. Per the conversion rules in
>SQL Server, the string literal is converted to sql_variant. I believe
>that for to sql_variant values to be equal, they must have the same
>base type. But value for the file name, is surely nvarchar.
>So adding an N before string literal to make it nvarchar may work.
>I would recommand to explicitly convert value to nvarchar(4000).
>is sql_variaamt

--
Message posted via http://www.sqlmonster.com|||Okay, looks like I've got it working now. I needed to use CAST instead of
CONVERT for the traceid to get it to read as an INT.

Thanks again for the feedback.

michael wrote:
>Thanks for your response. I think I might not have stated the issue clearly
>or am not understanding your response. My issue is that the traceid is not
>being returned as an INT. There is no problem with the file name and the
>nvarchar.
>Of the PRINT statements in my code below, I get everything up to and
>including 'Stop current trace'. My SET does return the traceid, but not in
>integer format.
>I'll do further research on the sql_variant though and see if I can get
>further with that.
>Thanks
>>> I am trying to automate a SQL Trace via a stored procedure and a job.
>>> The job executes the stored procedure to start the trace and every 15
>[quoted text clipped - 19 lines]
>>I would recommand to explicitly convert value to nvarchar(4000).
>>is sql_variaamt

--
Message posted via http://www.sqlmonster.com