Duplicate key error

I'm having trouble when trying to apply a snapshot to the subsciber. The
distibution agent reports error "unexpected EOF found in BCP data file error"
in addition it reports " duplicate key error". I have filtered the data
being sent and choosen to delete the rows at the subscriber table that match
the filter. I've checked the subscriber table and all looks ok. The
snapshot application has deleted the rows that are in the filter but when it
inserts the filtered rows it's showing a "duplicate key error". How can this
be? Can any one explain this?
I found that dropping and recreating the table does work however, I don't
want to drop and re-create the table as there is existing data in the
subscriber table that needs to be preserved.
Thanks,
Reana
Can you have a look at the snpashot files to confirm what is happening. There
should be a delete statement with a where clause and the bcp data file should
only have rows that correspond tot he where clause. Also check for any
duplicates in the bcp data files.
Cheers,
Paul Ibison

DTSX package will not run from SQL server agent

When I try to run a DTSX package from SQL server agent, I get this error message:

Started: 3:59:42 PM Error: 2007-08-13 15:59:42.39 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTSroperty" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available. End Error Error: 2007-08-13 16:00:00.98 Code: 0xC001602A Source: CFASerialNoExport Connection manager "FTP Connection Manager" Description: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed . End Error Error: 2007-08-13 16:00:00.98 Code: 0xC002918F Source: FTP Task FTP Task Description: Unable to connect to FTP server using "FTP Connection Manager". End Error DTExec: The packag... The package execution fa... The step failed.

All my other packages run fine. This one contains an FTP task and will not run, although it will run if I execute it through the visual studio.

May be package is set with encryption, save the package with encryption level "Dont Save Sensitive" (if you are using configuration file) or alternatively you can set the encryption level to "rely on server storage for access and roles" in sql server when importing the package.

Thanks

|||

See if this KB article helps:

http://support.microsoft.com/kb/918760

DTSX file

A developer asked for help to schedule a xxxx.dtsx file developed via Visual Studio 2005 on SQL Server 2005 Management Studio Job Agent? Not too familiar just yet with VS2005 and SQL2005. Any help would be appreciated.

ThanksOK, you're in VS, right?

Save your file (File/Save xxxx.dtsx)

Save a copy of your package to the SQL Server (File/Save Copy of xxxx.dtsx As...)

You then get a 'Save copy of Package' dialog.
Enter the servername that you want to store the package in (you're saving it right in the msdb database)

Leave Authentication type as Windows

Got to package path box and click on the ellipsis.
This brings up the SSIS Package dialog. Give the package a name, and press OK to store it under SSIS packages (it's a very good plan to organise folders under SSIS packages, and store packages systematucally, but lets just try to get this going)

You're back in 'Save copy of Package' dialog.. Got to 'Protection Level', press the ellipsis, and from the 'Package protection level' dialog, choose 'Rely on server storage...' (If you're just trying to get this working, all the encryption business is hassle you don't need)

You're back in 'Save copy of Package' dialog. Hit OK. You've saved the package.

Now go into Management Studio. Create a new job. Create a new step. In teh Type drop down, choose SQL Server Integration Services Package.
Leave Package Source as SQL Server.
In Server, enter the name of the server that you entered when you saved the package.
In Package, press teh ellipses, and select the package that you just saved.

Press OK.

As for teh rest, just continue as in any normal job, with scheduling etc.

Now, I can tell you now that you'll almost certainly run into problems with security etc, you'll have to chase all that out on your own. But that's the basic technique that runs all our job / packages.

HTH

If you get really stuck, hit me up with specifics on sammessengerathotmaildotcom
|||

Sam,

I still get an error. The package execution failed

DTSX file

A developer asked for help to schedule a xxxx.dtsx file developed via Visual Studio 2005 on SQL Server 2005 Management Studio Job Agent? Not too familiar just yet with VS2005 and SQL2005. Any help would be appreciated.

ThanksOK, you're in VS, right?

Save your file (File/Save xxxx.dtsx)

Save a copy of your package to the SQL Server (File/Save Copy of xxxx.dtsx As...)

You then get a 'Save copy of Package' dialog.
Enter the servername that you want to store the package in (you're saving it right in the msdb database)

Leave Authentication type as Windows

Got to package path box and click on the ellipsis.
This brings up the SSIS Package dialog. Give the package a name, and press OK to store it under SSIS packages (it's a very good plan to organise folders under SSIS packages, and store packages systematucally, but lets just try to get this going)

You're back in 'Save copy of Package' dialog.. Got to 'Protection Level', press the ellipsis, and from the 'Package protection level' dialog, choose 'Rely on server storage...' (If you're just trying to get this working, all the encryption business is hassle you don't need)

You're back in 'Save copy of Package' dialog. Hit OK. You've saved the package.

Now go into Management Studio. Create a new job. Create a new step. In teh Type drop down, choose SQL Server Integration Services Package.
Leave Package Source as SQL Server.
In Server, enter the name of the server that you entered when you saved the package.
In Package, press teh ellipses, and select the package that you just saved.

Press OK.

As for teh rest, just continue as in any normal job, with scheduling etc.

Now, I can tell you now that you'll almost certainly run into problems with security etc, you'll have to chase all that out on your own. But that's the basic technique that runs all our job / packages.

HTH

If you get really stuck, hit me up with specifics on sammessengerathotmaildotcom|||

Sam,

I still get an error. The package execution failed

DTSRun Encrypted

In a Multi Server Job under SQL Server Agent (MSX), we have a series of
steps like:

DTSRun /~Z0x8E8635E6BBA...

The ~Z means it's an encrypted hexidecimal string. The person who created
this step is no longer accessible, and the string can't be trivially
decrypted, so I don't know what this runs.

Someone once mentioned that I could go to Query Analyzer, run it, and
watch the execution plan. However, if I start the job, the execution
plan simply walks through the steps required to find the job, and doesn't
go into the details of the job. I don't know how to run the DTSRun
command directly within Query Analyzer.

Is there a way I can use the execution plan to determine what package this
DTSRun line is executing? Please give me the appropriate command or
commands.

-AdamIn theory you could do this using profiler, I have never tried it though...

All DTSRun does does is call the loadpackage and then executepackage methods
on the DTS.Package object, as long as the package is a SQL Server package
and hence gets loaded out of msdb the calls to the sp's in msdb should show
up

"Adam Augusta" <roxton@.wpi.edu> wrote in message
news:Pine.LNX.4.58.0309191701430.27905@.ccc8.WPI.ED U...
> In a Multi Server Job under SQL Server Agent (MSX), we have a series of
> steps like:
> DTSRun /~Z0x8E8635E6BBA...
> The ~Z means it's an encrypted hexidecimal string. The person who created
> this step is no longer accessible, and the string can't be trivially
> decrypted, so I don't know what this runs.
> Someone once mentioned that I could go to Query Analyzer, run it, and
> watch the execution plan. However, if I start the job, the execution
> plan simply walks through the steps required to find the job, and doesn't
> go into the details of the job. I don't know how to run the DTSRun
> command directly within Query Analyzer.
> Is there a way I can use the execution plan to determine what package this
> DTSRun line is executing? Please give me the appropriate command or
> commands.
> -Adam

DTS/ SQL Agent question

We're having to install new SQL servers on a new naming convention. We
know the changes we have to make on the applications that hit the SQL boxes.
However, we are not sure what DTS or SQL Agent jobs would need to be
changed.
Is there a way that we can search the DTS & SQL Agent jobs for a literal
that would refer to our old SQL server name and could identify & change
it to run on the new one?
Or,
is there a way to pass a parameter into the DTS or the Agent jobs that
would point them to the new server?
We're running Windows 2000, SQL 2000. We'll be moving to Windows 2003
and SQL 2000.
Any ideas appreciated.
Thanks,
BCFor the agent jobs, you can script them out and search through the SQL that
is created. For DTS, you could probably save it as a VB file and search
through that.
Hope this helps!
Chuck Heinzelman
MCSD, MCDBA
I support the Professional Association for SQL Server (www.sqlpass.org)
This posting is not an endoresment of any product.
Information is provided as-is, and carries no warranties - either express or
implied.
Please respond in newsgroups only.
"Blasting Cap" <goober@.christian.net> wrote in message
news:OtNNUaXaGHA.4780@.TK2MSFTNGP02.phx.gbl...
> We're having to install new SQL servers on a new naming convention. We
> know the changes we have to make on the applications that hit the SQL
> boxes.
> However, we are not sure what DTS or SQL Agent jobs would need to be
> changed.
> Is there a way that we can search the DTS & SQL Agent jobs for a literal
> that would refer to our old SQL server name and could identify & change it
> to run on the new one?
> Or,
> is there a way to pass a parameter into the DTS or the Agent jobs that
> would point them to the new server?
> We're running Windows 2000, SQL 2000. We'll be moving to Windows 2003 and
> SQL 2000.
> Any ideas appreciated.
> Thanks,
> BC

DTS/ SQL Agent question

We're having to install new SQL servers on a new naming convention. We
know the changes we have to make on the applications that hit the SQL boxes.
However, we are not sure what DTS or SQL Agent jobs would need to be
changed.
Is there a way that we can search the DTS & SQL Agent jobs for a literal
that would refer to our old SQL server name and could identify & change
it to run on the new one?
Or,
is there a way to pass a parameter into the DTS or the Agent jobs that
would point them to the new server?
We're running Windows 2000, SQL 2000. We'll be moving to Windows 2003
and SQL 2000.
Any ideas appreciated.
Thanks,
BCFor the agent jobs, you can script them out and search through the SQL that
is created. For DTS, you could probably save it as a VB file and search
through that.
Hope this helps!
Chuck Heinzelman
MCSD, MCDBA
I support the Professional Association for SQL Server (www.sqlpass.org)
This posting is not an endoresment of any product.
Information is provided as-is, and carries no warranties - either express or
implied.
Please respond in newsgroups only.
"Blasting Cap" <goober@.christian.net> wrote in message
news:OtNNUaXaGHA.4780@.TK2MSFTNGP02.phx.gbl...
> We're having to install new SQL servers on a new naming convention. We
> know the changes we have to make on the applications that hit the SQL
> boxes.
> However, we are not sure what DTS or SQL Agent jobs would need to be
> changed.
> Is there a way that we can search the DTS & SQL Agent jobs for a literal
> that would refer to our old SQL server name and could identify & change it
> to run on the new one?
> Or,
> is there a way to pass a parameter into the DTS or the Agent jobs that
> would point them to the new server?
> We're running Windows 2000, SQL 2000. We'll be moving to Windows 2003 and
> SQL 2000.
> Any ideas appreciated.
> Thanks,
> BC

DTS/ SQL Agent question

We're having to install new SQL servers on a new naming convention. We
know the changes we have to make on the applications that hit the SQL boxes.
However, we are not sure what DTS or SQL Agent jobs would need to be
changed.
Is there a way that we can search the DTS & SQL Agent jobs for a literal
that would refer to our old SQL server name and could identify & change
it to run on the new one?
Or,
is there a way to pass a parameter into the DTS or the Agent jobs that
would point them to the new server?
We're running Windows 2000, SQL 2000. We'll be moving to Windows 2003
and SQL 2000.
Any ideas appreciated.
Thanks,
BCFor the agent jobs, you can script them out and search through the SQL that
is created. For DTS, you could probably save it as a VB file and search
through that.
Hope this helps!
--
Chuck Heinzelman
MCSD, MCDBA
I support the Professional Association for SQL Server (www.sqlpass.org)
This posting is not an endoresment of any product.
Information is provided as-is, and carries no warranties - either express or
implied.
Please respond in newsgroups only.
"Blasting Cap" <goober@.christian.net> wrote in message
news:OtNNUaXaGHA.4780@.TK2MSFTNGP02.phx.gbl...
> We're having to install new SQL servers on a new naming convention. We
> know the changes we have to make on the applications that hit the SQL
> boxes.
> However, we are not sure what DTS or SQL Agent jobs would need to be
> changed.
> Is there a way that we can search the DTS & SQL Agent jobs for a literal
> that would refer to our old SQL server name and could identify & change it
> to run on the new one?
> Or,
> is there a way to pass a parameter into the DTS or the Agent jobs that
> would point them to the new server?
> We're running Windows 2000, SQL 2000. We'll be moving to Windows 2003 and
> SQL 2000.
> Any ideas appreciated.
> Thanks,
> BC