Solved

HELP! SQL 2005 Integration Services - Transfer SQL Server Objects Task - Cant Connect Using SQL Server Authentication

Posted on 2006-11-07
10
1,088 Views
Last Modified: 2008-01-09
Hi Experts,

I am using SQL 2005 Integrations Services and am copying data from my SQL 2005 database to a SQL 2000 database using the Transfer SQL Server Objects Task.

However, if I use windows authentication to connect to the 2000 database if works fine. If I use SQL Server Authentication I get the error: -

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Cannot apply value null to property Login: Value cannot be null..".

The process can see the database and logon as I get the message: -

[Transfer SQL Server Objects Task] Warning: Table "Internet_Brand_Links" already exists at destination.

But it somehow is messing up somehow along the way.

I am logging on using the SA username and password so it is not a permissions thing.

Any Thoughts?
0
Comment
Question by:kennethscff
  • 4
  • 4
10 Comments
 
LVL 28

Expert Comment

by:imran_fast
ID: 17889638
check your sql property
It should be mixed mode under securtiy


if not change it and then restart your machine
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17889645
mixed mode =  windows and sql
0
 

Author Comment

by:kennethscff
ID: 17889688
I can logon to the server with mixed mode using SQL Server Management Studio and do the Export job manually. The problem only occours when trying to do it by executing the Transfer SQL Server Objects Task.

0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17889730
hi kennethscff ,

It has something to do with the connection object.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:kennethscff
ID: 17889765
Thats what I thought. However, 2 things make me thing otherwise.

1. I can open the connection object and click "Test Connction" and it is successful.
2. It can see the table in the destination database before it trys to copy it. It has to be loggin on to see that it is there.
    if I delete the table from the destination server I dont get the message
    "[Transfer SQL Server Objects Task] Warning: Table "Internet_Brand_Links" already exists at destination. "

Very Strange!
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 17889792
check the owner of the table then
0
 

Author Comment

by:kennethscff
ID: 17889823
If I delete the table on the destination server it still doesn't copy. Plus Im using the SA logon!
0
 

Author Comment

by:kennethscff
ID: 17890062
This is a known bug in SQL 2005 and is not fixed yet. Please see

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127100

Hopefully if will be fixed soon!!
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 18298275
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Alter table 4 23
SQL Server - SQL field is defined as text 3 27
TSQL - IF ELSE? 3 26
SQL Server stored proc 2 10
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now