Problem with "Transfer SQL Server Objects Task"

Hi everyone,

I'm currently trying to copy a database from one server to another (both SQL2005) using Business Intelligence Development Studio.
I've created an SSIS package. The following parameters are defined:

DropObjectsFirst true
IncludeExtendedProperties true
CopyData true
ExistingData Replace
CopySchema false
UseCollation false
IncludeDependentObjects true
CopyPrimaryKeys true
CopyForeignKeys true

The package fails with the following error:
Violation of PRIMARY KEY constraint 'PK_tblCallStatus' Cannot insert duplicate key in object 'dbo.tblCallStatus'

I know what that error means but I don't understand why I get it.
Isn't the package supposed to completely overwrite the destination database ?
It obviously does not. When I manually delete all records from 'tblCallStatus' in the destination database it works fine. I can't remember I had to do that in a SQL2000 environment using DTS.

Hope anyone can help since this is almost driving me nuts ;-)

Thanks in advance,
Kevin

I've had the same problems, and for what it's worth, here's what I found:

setting copySchema to true fixed a few problems

CopyForeignKeys.....it doesn't work, so don't do it. SSIS seems to try and create foreign keys before it has created all the primary keys, so it can sometimes fall over

check jamie thomson's article for possible workarounds of the foreign key problem:
http://blogs.conchango.com/jamiethomson/archive/2006/02/17/SSIS_3A00_-How-to-load-related-tables.aspx

do you have service pack 2 installed?

michal
|||Hi Michael,

I've also experimented with 'copySchema'. No luck so far.
Service pack 2 is installed on both servers.
I've read the article you're referring to and I just can't believe that the "Transfer... Task" isn't able to perform such a simple thing.
|||Oops, sorry for the chaos.
The previous post was also made by me. I currently have two Passport identities.
I'll try to not use the other one anymore...

*edit*
This is starting to become really weird. I've manually created a database (no tables, sps etc.) on the destination server. When I tell the "transfer... task" to copy the production database from the source server to this new database it gives me the following error:

"Cannot find the object "dbo.tblTasks" because it does not exist or you do not have permissions."

Of course the table doesn't exist. The package is supposed to create it. A lack of permissions can't be the problem as well since I'm sysadmin.

I'm really stuck here. SQL 2005 has been out for quite a while now. I just can't believe that this is a bug.
|||Hi Kevin

"Cannot find the object "dbo.tblTasks" because it does not exist or you do not have permissions."

That means that you have set 'dropObjectsFirst' to true. It's trying to drop a table that doesn't exist. Your best bet is to either drop the tables before hand and set 'dropObjectsFirst' to false, or make sure the objects exist on the target DB.

although you have SP2 on both servers, are you creating the package on one of those servers? Or do you have SP2 on the machine that you are creating the package?

Everything I've read about this suggests using backup/copy/restore, which isn't brilliant.

The only thing I can really suggest is stripping the package right down to simply copying a few tables, then rerunning it over and over adding more and more options until it falls over. Trial and error I know, but at least you will know the SSIS limitations

michal

problem with ##xp_cmdshell_proxy_account##

Hi,
I was trying to debug a situation where we need to use xp_cmdshell in
SQL2005 SP2 build 3175. I had created the ##xp_cmdshell_proxy_account##
proxy credential and wanted to delete it to see that I was getting a message
about the credential missing. After running the sp_xp_cmdshell_proxy_account
stored proc with NULL I ran it again to re-create the proxy credential but
this time I got message: -
Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
An error occurred during the execution of sp_xp_cmdshell_proxy_account.
Possible reasons: the provided account was invalid or the
'##xp_cmdshell_proxy_account##' credential could not be created. Error code:
'997'.
This is on a clustered server so do I need to flip the cluster for the
credential to be completed deleted before trying to add it again?
Thanks
ChrisI am having the same issue. I am simply trying to assign xp_cmdshell a proxy
account and its not allowing me to. I know that the username/password is a
valid one.
Any help would be appreciated. Thanks Amir
"Chris Wood" wrote:
> Hi,
> I was trying to debug a situation where we need to use xp_cmdshell in
> SQL2005 SP2 build 3175. I had created the ##xp_cmdshell_proxy_account##
> proxy credential and wanted to delete it to see that I was getting a message
> about the credential missing. After running the sp_xp_cmdshell_proxy_account
> stored proc with NULL I ran it again to re-create the proxy credential but
> this time I got message: -
> Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
> An error occurred during the execution of sp_xp_cmdshell_proxy_account.
> Possible reasons: the provided account was invalid or the
> '##xp_cmdshell_proxy_account##' credential could not be created. Error code:
> '997'.
> This is on a clustered server so do I need to flip the cluster for the
> credential to be completed deleted before trying to add it again?
> Thanks
> Chris
>
>
>
>|||Amir,
Rather than run the stored proc I just created the proxy credential by using
the create credential T-SQL statement.
Chris
"Amir" <Amir@.discussions.microsoft.com> wrote in message
news:61B89673-4894-419A-9D82-6D99C7559878@.microsoft.com...
>I am having the same issue. I am simply trying to assign xp_cmdshell a
>proxy
> account and its not allowing me to. I know that the username/password is a
> valid one.
> Any help would be appreciated. Thanks Amir
> "Chris Wood" wrote:
>> Hi,
>> I was trying to debug a situation where we need to use xp_cmdshell in
>> SQL2005 SP2 build 3175. I had created the ##xp_cmdshell_proxy_account##
>> proxy credential and wanted to delete it to see that I was getting a
>> message
>> about the credential missing. After running the
>> sp_xp_cmdshell_proxy_account
>> stored proc with NULL I ran it again to re-create the proxy credential
>> but
>> this time I got message: -
>> Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account,
>> Line 1
>> An error occurred during the execution of sp_xp_cmdshell_proxy_account.
>> Possible reasons: the provided account was invalid or the
>> '##xp_cmdshell_proxy_account##' credential could not be created. Error
>> code:
>> '997'.
>> This is on a clustered server so do I need to flip the cluster for the
>> credential to be completed deleted before trying to add it again?
>> Thanks
>> Chris
>>
>>
>>

Problem when rebuild system databases for a clustered instance of

Has anyone rebuilt system databases for a SQL2005 clustered instance?
I have followed the code from "To rebuild system databases for a clustered."
section at http://msdn2.microsoft.com/en-us/library/ms144259.aspx, and keep
getting message saying to add more parameters. I added the parameter
whenever it required, "Group", then "addnode", even INSTALLSQLDATADIR = "S:\"
which data should go.
The summary.txt says "Setup succeeded with the installation". But in the
files\setup_*_core.log for both node2, I have this error:
Error: Action "LaunchLocalBootstrapAction" threw an exception during
execution. Error information reported during run:
"C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\setup.exe"
finished and returned: 0
Aborting queue processing as nested installer has completed
Message pump returning: 0
I want to know whether anyone has similar problem or succeed when rebuild
system DBs for a cluster.
What are all the parameters needed for rebuilding sys databases on a
cluster? Is there anything should be aware of?
Hi Julia
Did you manage to resolve this problem? I am experiencing exactly the
same thing .
Cheers
Jesse
*** Sent via Developersdex http://www.codecomments.com ***
|||No. I have contacted Microsoft but no answer. I would recommend you report
this problem as well to them and get them to look into it.
"Jesse Easton" wrote:

>
> Hi Julia
> Did you manage to resolve this problem? I am experiencing exactly the
> same thing .
> Cheers
> Jesse
> *** Sent via Developersdex http://www.codecomments.com ***
>

Problem when failing over to mirrored database

I'm having a problem with SQL2005 Database mirroring.

I have an ASP application that loops for a certain amount of interations and in each iteration I create a SQL Connection object and use the failover partner in the connection string. The object then writes a simple record and then the connection is closed and the process starts again.

About half way through my loop I force a failover to the server mentioned as the failover partner in the connection string. At this point my application encounters a SQL Exception error and the application fails.

I read in this article, http://msdn2.microsoft.com/en-us/library/ms366199.aspx, that this is expected and that you shoud request a new connection using the same connection string but this isn't working for me and unfortunately no examples are given anywhere.

I would appreciate any help. Thanks

Luis Bonilla

Here is a sample of my code:

Dim k As Integer = 1
Dim constring As String = "Server=SVR01.XXX.XXX.com\InstanceName;Failover Partner=SVR02.XXX.XXX.com\InstanceName;" _
& "Database=test;" _
& "Integrated Security=SSPI;"

Do Until k = 60
Using con As New SqlConnection(constring)

Dim cmd As New SqlCommand()

Try
con.Open() '<--This is where the exception occurs
Catch ex As SqlException
Dim en As String = ex.ErrorCode
Dim em As String = ex.Message
Dim emm As String = ex.Number
Dim enn As Integer = ex.State
End Try

lblprimary.Text = "Current Primary Database = " & con.DataSource

cmd.Connection = con
cmd.CommandText = "INSERT INTO SystemsTest (FirstName, LastName)VALUES ('SQLWriter" & CStr(k) & "','" & con.DataSource & "')"

Try
cmd.ExecuteNonQuery()
Catch
lblresult.Text = "Results = Error with record number " & CStr(k)
End Try

con.Close()
cont:
End Using
Sleep(1000) 'For testing
k = k + 1 'For testing
Loop

Here is the error information that I'm getting whenever I failover to my failover partner.

ErrorCode = -2146232060
Error Message = "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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)"
Error Number = -1

I have already verified that Remove Connections is enabled on both SQL servers.

Any ideas?

|||

Can you connect to the 2nd server (the failover partner) from your app? Can you try temporarily changing the connection string by marking the 2nd server as primary to test it? This will eliminate any network layer level configuration problems.

Thanks

Waseem

|||

Yes, I can connect to the failover server.

If I mark the 2nd server as the primary and the 1st server as the failover failover works fine ONLY ONCE IF the primary role holder is the 1st server. But if failover happens again the same SQLException error occurs.

Problem when creating SP in sql2005

Hi All,

I am trying to create this stored procedure but it keeps complaining about this line:

Naam_Spel = @.Naam_Spel,

probably it is something stupid but i keep staring at it frustrates me!

heres the complete sp:

USE [Ebdata]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC UpdateEprom

@.EpromID bigint,
@.Naam_Spel varchar(50),
@.Versie varchar(10),
@.Checksum1 varchar(8),
@.Checksum2 varchar(8),
@.Omschrijving varchar(MAX),
@.Datum_vrijgave datetime,
@.EpromType int,
@.Land varchar (50),
@.Kabinet varchar(50),
@.Merk int,
@.Wet int,
@.EpromFile varchar(100),
@.EpromSize varchar(100),
@.SourceFile varchar(100),
@.SourceSize varchar(100),
@.Active int,
@.UpdateUser uniqueidentifier

AS
update EB_Eprom
SET
Naam_Spel = @.Naam_Spel,
Versie = @.Versie,
Checksum1 = @.Checksum1,
Checksum2 = @.Checksum2,
Omschrijving = @.Omschrijving,
Datum_Vrijgave = @.Datum_Vrijgave,
EpromType = @.EpromType,
LAnd = =@.Land,
Kabinet = @.Kabinet,
Merk = @.Merk,
Wet = @.wet,
EpromFile = @.EpromFile,
EpromSize = @.EpromSize,
SourceFile = @.SourceFile,
SourceSize = @.SourceSize,
Active = @.Active,
UpdateUser = @.UpdateUser,
UpdateDate = getdate()

where EpromID = @.EpromID

Hope that someone don't have the same stringproblems as i have!

Cheers WimmoMAX isn't a valid dimension as used in @.Omschrijving varchar(MAX),-PatP|||Thanx for your reply and sorry for the double post,

I changed it to 100 but still gives me the same error
btw this is the error i get:

/Msg 102, Level 15, State 1, Procedure UpdateEprom, Line 34
Incorrect syntax near '='.|||This is the table on which the sp should update:

USE [Ebdata]
GO
/****** Object: Table [dbo].[EB_Eprom] Script Date: 04/16/2006 14:48:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EB_Eprom](
[EpromId] [bigint] IDENTITY(100,5) NOT NULL,
[Naam_Spel] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Versie] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Checksum1] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Checksum2] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Omschrijving] [nvarchar](max) COLLATE Latin1_General_CI_AS NULL,
[Datum_vrijgave] [datetime] NULL,
[EpromType] [int] NULL CONSTRAINT [DF_EB_Eprom_EpromType] DEFAULT ((7)),
[Land] [bigint] NULL CONSTRAINT [DF_EB_Eprom_Land] DEFAULT ((0)),
[Kabinet] [int] NULL CONSTRAINT [DF_EB_Eprom_Kabinet] DEFAULT ((0)),
[Merk] [int] NULL CONSTRAINT [DF_EB_Eprom_Merk] DEFAULT ((0)),
[Wet] [int] NULL CONSTRAINT [DF_EB_Eprom_Wet] DEFAULT ((0)),
[EpromFile] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[EpromSize] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[SourceFile] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[SourceSize] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[Active] [int] NULL CONSTRAINT [DF_EB_Eprom_Active] DEFAULT ((2)),
[CreateDate] [datetime] NULL CONSTRAINT [DF_EB_Eprom_CreateDate] DEFAULT (getdate()),
[CreatorID] [uniqueidentifier] NULL,
[UpdateUser] [uniqueidentifier] NULL,
[UpdateDate] [datetime] NULL,
[Downloaded] [bigint] NULL,
[SourceDownloaded] [bigint] NULL,
CONSTRAINT [PK_EB_Eprom] PRIMARY KEY CLUSTERED
(
[EpromId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [Ebdata]
GO
ALTER TABLE [dbo].[EB_Eprom] WITH CHECK ADD CONSTRAINT [FK_EB_Eprom_Alg_Landen] FOREIGN KEY([Land])
REFERENCES [dbo].[Alg_Landen] ([CountryCode])
GO
ALTER TABLE [dbo].[EB_Eprom] WITH NOCHECK ADD CONSTRAINT [FK_EB_Eprom_aspnet_Users2] FOREIGN KEY([CreatorID])
REFERENCES [dbo].[aspnet_Users] ([UserId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[EB_Eprom] WITH NOCHECK ADD CONSTRAINT [FK_EB_Eprom_aspnet_Users3] FOREIGN KEY([UpdateUser])
REFERENCES [dbo].[aspnet_Users] ([UserId])
NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[EB_Eprom] WITH CHECK ADD CONSTRAINT [FK_EB_Eprom_EB_Active] FOREIGN KEY([Active])
REFERENCES [dbo].[EB_Active] ([ActiveID])
GO
ALTER TABLE [dbo].[EB_Eprom] WITH CHECK ADD CONSTRAINT [FK_EB_Eprom_EB_EpromType] FOREIGN KEY([EpromType])
REFERENCES [dbo].[EB_EpromType] ([TypeID])
GO
ALTER TABLE [dbo].[EB_Eprom] WITH CHECK ADD CONSTRAINT [FK_EB_Eprom_EB_Kabinet] FOREIGN KEY([Kabinet])
REFERENCES [dbo].[EB_Kabinet] ([KabinetID])
GO
ALTER TABLE [dbo].[EB_Eprom] WITH CHECK ADD CONSTRAINT [FK_EB_Eprom_EB_Merk] FOREIGN KEY([Merk])
REFERENCES [dbo].[EB_Merk] ([MerkId])
GO
ALTER TABLE [dbo].[EB_Eprom] WITH CHECK ADD CONSTRAINT [FK_EB_Eprom_EB_Wet] FOREIGN KEY([Wet])
REFERENCES [dbo].[EB_Wet] ([WetId])|||The MAX shows in your CREATE TABLE statement too. I suspect that the table structure is somehow corrupt, even though I can't explain why... The table shouldn't even exist as far as I can tell!

Try to rebuild the table structure using something other than MAX to see if that fixes the problem.

-PatP|||The MAX shows in your CREATE TABLE statement too. I suspect that the table structure is somehow corrupt, even though I can't explain why... The table shouldn't even exist as far as I can tell!

Try to rebuild the table structure using something other than MAX to see if that fixes the problem.

-PatP

I don't understand either because i created the table visual using SQL Server management studio where the nvarchar(max) is a default datatype and
varchar(MAX) and Varbinary(MAX)

but changed it to 4000 and still the same error keeps coming back!|||Sorry, brain fart on my part. I was thinking in SQL 2000 terms, not SQL 2005.

I can't connect to a machine with SQL 2005 on it right now, so I can't test your code. Maybe someone else can help before I can connect, if not I'll check it out later.

Sorry for my confusion.

-PatP|||No probs, thought you were thinking sql 2000!
But as far as you can see the syntax isn't wrong right?|||This might be a problem (double =)

LAnd = =@.Land,|||Have you tried putting parentheses around the parameter list, so:

CREATE PROC UpdateEprom

( @.EpromID bigint,
@.Naam_Spel varchar(50),
@.Versie varchar(10),
@.Checksum1 varchar(8),
@.Checksum2 varchar(8),
@.Omschrijving varchar(MAX),
@.Datum_vrijgave datetime,
@.EpromType int,
@.Land varchar (50),
@.Kabinet varchar(50),
@.Merk int,
@.Wet int,
@.EpromFile varchar(100),
@.EpromSize varchar(100),
@.SourceFile varchar(100),
@.SourceSize varchar(100),
@.Active int,
@.UpdateUser uniqueidentifier)

AS
update EB_Eprom
SET
Naam_Spel = @.Naam_Spel,
Versie = @.Versie,
Checksum1 = @.Checksum1,
Checksum2 = @.Checksum2,
Omschrijving = @.Omschrijving,
Datum_Vrijgave = @.Datum_Vrijgave,
EpromType = @.EpromType,
LAnd = =@.Land,
Kabinet = @.Kabinet,
Merk = @.Merk,
Wet = @.wet,
EpromFile = @.EpromFile,
EpromSize = @.EpromSize,
SourceFile = @.SourceFile,
SourceSize = @.SourceSize,
Active = @.Active,
UpdateUser = @.UpdateUser,
UpdateDate = getdate()

where EpromID = @.EpromID|||This might be a problem (double =)

LAnd = =@.Land,

Thank you very much, i have to quit doing these things on an eastern morning!
I am a bit ashamed know!

Cheers Wim

Thanx guys for all the help!!

Problem upgrading clustered node to SP1

Hi

We just upgraded a sql 2K Active\Active 2 node cluster to SQL2005. The upgrade seemed to go OK.

We also managed to apply SP1 to Node 1 after an initial problem

But now when applying SP1 to NODE 2 we are getting this error

"A recently applied update, NULL, failed to install"

We had the same problem on NODE 1. Turns out after installing client connection tools and management tools SP1 succeeded. But it does not seem to work on Node2

And yes both nodes have been rebooted.

Any help is appreciated. thanks

Here is the error log

05/18/2006 11:39:57.414 ================================================================================
05/18/2006 11:39:57.414 Hotfix package launched
05/18/2006 11:39:57.430 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/18/2006 11:39:57.445 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/18/2006 11:39:57.461 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/18/2006 11:39:57.477 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/18/2006 11:39:57.602 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/18/2006 11:39:57.633 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files
05/18/2006 11:39:57.633 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion
05/18/2006 11:39:57.649 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files
05/18/2006 11:39:57.664 Local Computer:
05/18/2006 11:39:57.664 Target Details: OSSQL12
05/18/2006 11:39:57.680 commonfilesdir = C:\Program Files\Common Files
05/18/2006 11:39:57.695 lcidsupportdir = x:\ae3976232fdba78db1a56fa98a9693\1033
05/18/2006 11:39:57.711 programfilesdir = C:\Program Files
05/18/2006 11:39:57.727 supportdir = \\OSSQL12\x$\ae3976232fdba78db1a56fa98a9693
05/18/2006 11:39:57.727 supportdirlocal = x:\ae3976232fdba78db1a56fa98a9693
05/18/2006 11:39:57.742 windir = C:\WINDOWS
05/18/2006 11:39:57.758 winsysdir = C:\WINDOWS\system32
05/18/2006 11:39:57.774
05/18/2006 11:39:57.789 Enumerating applicable products for this patch
05/18/2006 11:39:59.867 The patch installation could not proceed due to unexpected errors
05/18/2006 11:39:59.883
05/18/2006 11:39:59.883 Product Status Summary:
05/18/2006 11:40:00.024 Hotfix package closed

You can failover SQL resources to the second node and then try SP1 on that node. This is a peculiar problem and I doubt you can reproduce the problem.

Problem updating SQL2005 From MSAccess Form

I have recently migrated from SQL2K to SQL2K5 and have a number of front ends in MSAccess Forms that worked fine with SQL2K but are not working with SQL2K5. I am able to connect and view the data in the SQL table using a file DSN but I am unable to update the records from the MSAccess form in SQL2K5. I have checked all of the permissions and have even given the Login dbo privs. I have also allowed remote access through the Surface Area Configuration Tool. What am I missing?

Thanks in advance for any assistance you can provide.

What are you using as the front end?, Access Project or Access mdb? If it's a Project I don't think they can update in SS2005

I've just completed a big Access/SQL Server 2005 project, leaving the front end as an mdb. There are a few quirks, you can't use Views only SP's and pass through queries, but it still works well.

|||I am using Access mdb. Sql tables are Linked tables in the Access mdb. Thanks for the advice. I'll ensure that there are no views involved.|||You should run one of your queries from the query design grid, try to make a change to the returned result set, if you can make an update , you know it's a forms based problem not permissions.|||Same result using the Design Grid. I am able to display the contents of the table but unable to update or append records from the grid. WHen I try a simple update query I get the error "Operation must use an updatable query".|||Problem solved. For some reason when I originally created the Linked Table I was not prompted to identify a unique key in the table for updating. Recreating the Linked Table with the unique fields identified solved the problem.