[Resolved] Connect to SQl Server 2000 from 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Resolved] Connect to SQl Server 2000 from 2005

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-07 : 16:11:48

I am connected to a SQL Server 2005 and need create a stored procedure that gets data from a table on SQL Server 2000. Do I need to define a linked server on the 2005 for my connection or can I specify the full path in my sql select statement like below:

select * from \\vgiwpw03-slq3\EQUENTIALPROD\databases\GoLabor30\dbo.Item

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2008-11-07 : 23:32:36

This will explain your options.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-08 : 01:05:52

You can use OPENROWSET if you dont want to set up a linked server. But this is only prefered for adhoc access.
If you want constant access to server then add it as a linked server and use four part naming convention or use OPENQUERY.

http://blog.sqlauthority.com/2007/10/06/sql-server-executing-remote-stored-procedure-calling-stored-procedure-on-linked-server/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-10 : 13:18:03

Thank you for the update.

I have now defined a linked server on SQL Server 2005 that points to SQL Server 2000. Using the SQL Query Analyser I am trying to call my sp on 2005 which in return should call the sp on 2000.

My call:


declare @Sql nvarchar(500) 
declare @JobNumber int 
declare @Parm as nvarchar(100)
set @Sql = 'sp_Vecellio_ProductionQtyCompare'
set @JobNumber = 1100281
set @Parm = @JobNumber
exec sp_executesql @Sql, @Parm

Error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '1100281'.

My sp on 2005:


CREATE PROCEDURE sp_Vecellio_ProductionQtyCompare  
@JobNumber int
as
exec [VGIWPW03-SQL3\EQUENTIALPROD].goLabor30.dbo.sp_Vecellio_ProductionInquiry @JobNumber 
GO

My sp on 2000:


CREATE PROCEDURE sp_Vecellio_ProductionInquiry  
@JobNumber int
as
SELECT         distinct(dbo.Item.CompanyItemId), substring(dbo.Item.Name, 1, 15) as description, 
                       case dbo.SourceType.CompanySourceTypeId
                        when 'PR' then SUM(dbo.ProductionEvent.Quantity)
                       end
                       AS Ttl_Qty 
FROM             dbo.Batch INNER JOIN
                       dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
                       dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
                       dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
                       dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
                       dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
                       dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
                       dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
      	          dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid

WHERE          dbo.job.CompanyJobId = @JobNumber and dbo.SourceType.CompanySourceTypeId = 'PR'

GROUP BY    dbo.Item.CompanyItemId,  dbo.SourceType.CompanySourceTypeId, dbo.Item.Name
GO

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2008-11-10 : 13:37:52

Got it working this way:

declare @JobNumber int 
set @JobNumber = 1100281
exec sp_Vecellio_ProductionQtyCompare @JobNumber

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources