daijun

Hi all:

i have one statement in my procedure


Re: Visual Studio Team System - Database Professionals where to add scaripts for Linkserver in DB professional?

Paul Hacker

there are two directories that contain scripts that you can add. The Pre and Post deployment scripts. You can add the setup for the link servers in the pre depoyment script. Thaere a few empty scripts, but you also can add your own. You just have to make sure that you add a reference to any script you create in the pre or post deployment folders parent script. The parent scripts exist in their respective directories.

-paul






Re: Visual Studio Team System - Database Professionals where to add scaripts for Linkserver in DB professional?

Maor David

I add the linked servers in the LinkedServers.sql script which exists at the Pre-Deployment directory.

It works well, try it.

Maor






Re: Visual Studio Team System - Database Professionals where to add scaripts for Linkserver in DB professional?

daijun

Thanks






Re: Visual Studio Team System - Database Professionals where to add scaripts for Linkserver in DB professional?

DBTest1


If added the link servers as a variable in the LinkServers.sql file and it gives me the following Error. How can I make it work by passing in a variable

Error 1 TSD4001: Could not find server '$(LSSDR)' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. (SQL error = 7202)

Thanks





Re: Visual Studio Team System - Database Professionals where to add scaripts for Linkserver in DB professional?

Gert Drapers - MSFT

Where do you have the variable defined

Also you might simply want to check out Service Release one, which adds the concept of database references, which includes 4 part names through a Linked Server.

-GertD





Re: Visual Studio Team System - Database Professionals where to add scaripts for Linkserver in DB professional?

Josh Gallagher

I get the same error. I think this is a bug. I can select * from sys.servers on my local server and see the linked server with precisely the same name as is used in the TSD4001 error. I have put the Linked Servers into the Pre-Deployment script, but that has no effect.

I will check out SR1 when I get the time.





Re: Visual Studio Team System - Database Professionals where to add scaripts for Linkserver in DB professional?

Gweedar

I am also getting this error. I've created a link server in the linkedserver.sql and i continue to get errors when trying to do a build in VS 2005 for DB professionals.

1. I can view the server in sysservers
2. I can query the server in a SQL Management Studio query
3. I can run the stored procs that are not compiling within the SQL Management Studio without error

Is there some way to get VS to recognize that this server is in existence Does it matter if the server is a loopback or points to another instance

Any help would be appreciated, we've got it working here at our site, but on the customer's site it continues to give these errors anytime we add a sproc back into the source that references this linked server.

Thanks in advance

Daryl White




Re: Visual Studio Team System - Database Professionals where to add scaripts for Linkserver in DB professional?

Jamie Laflen MSFT

Are you using SR1






Re: Visual Studio Team System - Database Professionals where to add scaripts for Linkserver in DB professional?

Gweedar

I've got Visual Studio 2005 for Database Professionals SR1, and SQL Server 9.0 SP2 installed. I believe the client's machine is at SR1 and SQL Server 9.0 SP1.

Daryl




Re: Visual Studio Team System - Database Professionals where to add scaripts for Linkserver in DB professional?

Jamie Laflen MSFT

Okay, to make this work with SR1.

1. I took the original openquery call and modified it to just do a select with the openquery in the where.

2. Then, I created a new DBPro project called PandaDB

3. I created the schema I actually wanted to reference and built (in this case the view and underlying table)

4. I added a project reference to the PandaDB project (I could also add a reference to the .dbmeta file)

5. In my reference I made the instance name be $(PandaServer) and the database name by $(PandaDB)

6. I then modified the stored procedure definition to look like this:

CREATE PROCEDURE [dbo].[Procedure1]

@param1 int = 0,

@param2 int

AS

BEGIN

SET NOCOUNT ON

SELECT [Customer Id] ,[Ship To Country] ,[Ship to State]

,[CBN flag] ,[Recycling Registration Id]

FROM openquery([$(PandaInstance)],'select [Customer Id] ,[Ship To Country] ,[Ship to State]

,[CBN flag] ,[Recycling Registration Id] from [$(PandaDB)].dbo.vw_vis_fee_custex_canada')

RETURN 0

END

7. Save and the TSD4001 error is resolved

As you can see, we used the name of the instance as the first argument to the openquery call and then used a database qualified name in the query passed to openquery.






Re: Visual Studio Team System - Database Professionals where to add scaripts for Linkserver in DB professional?

Gweedar

Excellent. I'll give that a try. Thanks for the feedback!

Daryl