It has been a continuing saga trying to produce a local database development atmosphere using isolated development atmosphere configurations. I've two database projects, a server project that specific a shared SQL Server 2005 instance along with a database project that targets my database.
I've determined that implementing the server project in your area is difficult because of all the various logins not recognized on my small PC and so on, however, I want the linked servers in the server work for my database project. To beat the problems with implementing the server project, I switched off deployment around the server project and added these claims towards the PreDeployment script of my database project.
IF DB_ID('CreditScores') is null CREATE DATABASE CreditScores EXEC sp_changedbowner 'sa' EXECUTE sp_dropserver N'CREDIT_SCORES_CALENDAR', 'droplogins'; EXECUTE sp_addlinkedserver @server = N'CREDIT_SCORES_CALENDAR', @srvproduct = N'vm-2cbstest', @provider = N'SQLNCLI', @datasrc = N'vm-2cbstest'; EXECUTE sp_addlinkedsrvlogin @rmtsrvname = N'CREDIT_SCORES_CALENDAR', @useself = N'FALSE', @rmtuser = N'cdtscr';
The database deployment works to some extent, however I get the error message below if this tries to give a saved method that refers back to the linked server. The issue I am speculating would be that the login that's saved within the server work for the linked server includes a password that's not set when it's being produced depsite the truth that it appears just like a password is completed. My real question is, how do you assign this password without really understanding the password?
Creating [dbo].[report_open_close]... OLE DB provider "SQLNCLI" for linked server "CREDIT_SCORES_CALENDAR" returned message "Communication link failure". C:\SourceCode2010\CreditScores\Development\CreditScores\TECO.Database.SQLServer2005.CreditScores\sql\debug\TECO.Database.SQLServer2005.CreditScores.sql(150,0): Error SQL01268: .Net SqlClient Data Provider: Msg 10054, Level 16, State 1, Line 0 TCP Provider: An existing connection was forcibly closed by the remote host. C:\SourceCode2010\CreditScores\Development\CreditScores\TECO.Database.SQLServer2005.CreditScores\sql\debug\TECO.Database.SQLServer2005.CreditScores.sql(150,0): Error SQL01268: .Net SqlClient Data Provider: Msg 18456, Level 14, State 1, Line 0 Login failed for user 'cdtscr'. An error occurred while the batch was being executed. Done executing task "SqlDeployTask" -- FAILED. Done building target "DspDeploy" in project "TECO.Database.SQLServer2005.CreditScores.dbproj" -- FAILED. Done executing task "CallTarget" -- FAILED. Done building target "DBDeploy" in project "TECO.Database.SQLServer2005.CreditScores.dbproj" -- FAILED. Done building project "TECO.Database.SQLServer2005.CreditScores.dbproj" -- FAILED. Build FAILED.