Configuring a linked server 1

[tbode2006]

Hi all,
I have two computers running SQL 2000.
names: SQL_ST_A and S_IT_A
I am trying to configure the instance on S_IT_A as a linked server in SQL_IT_A. I have chosen the provider: Microsoft OLE DB Provider for SQL Server. The dialog box wants the PRODUCT NAME, DATA SOURCE, and PROVIDER STRING. I am unclear as to how to fill these entries. The help for PRODUCT NAME says "SQL Server", the help for DATA SOURCE says "Network name of SQL Server", and the help for PROVIDER STRING says "This is the entire provider string". I have not been able to get anything to work. Help please?

 

[mrdenny]

Select the radio button that says SQL Server above that and type the name of the SQL Server in the field at the top of the window.

Unless you are trying to name the linked server something other than the physical name of the SQL Server you can just select the SQL Server radio button.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tbode2006]

I had tried that before. I do get the linked server configured. The tables and views show up but I can not open them. How do I issue a SELECT to a linked server table. I am currently connected to SQL_IT_A server in query analyzer and issue the follow command.

select * from s_it_a.manufacturer.tblBOM_Data

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 's_it_a.manufacturer.tblBOM_Data'.

 

[philhege]

Use four-part naming:

select * from s_it_a.manufacturer.dbo.tblBOM_Data

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.

 

[tbode2006]

Thanks Phil. I was missing the dbo when I tried before.

 

[tbode2006]

How to I address this?

SELECT Rotation_Num, Customer_Name, BomPartType, PaintCode, DateChange, FlagChange
FROM  s_it_a.Manufacturer.dbo.tblBOM_Data WHERE s_it_a.Manufacturer.dbo.tblBOM_Data.[FlagChange] = 'N'

Server: Msg 117, Level 15, State 2, Line 14
The number name 's_it_a.Manufacturer.dbo.tblBOM_Data' contains more than the maximum number of prefixes. The maximum is 3.

 

[gmmastros]

[COLOR=blue]SELECT[/color] Rotation_Num, 
       Customer_Name, 
       BomPartType, 
       PaintCode, 
       DateChange, 
       FlagChange
[COLOR=blue]FROM[/color]   s_it_a.Manufacturer.dbo.tblBOM_Data [!]As SomeAlias[/!]
[COLOR=blue]WHERE[/color]  [!]SomeAlias[/!].[FlagChange] = 'N'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[tbode2006]

Here is my complete SP. I made the alias change and get another error.

CREATE PROCEDURE [dbo].[spPaintQueue]  AS

DECLARE @OldPC varchar(12), @NewPC varchar(12), @start int, @finish int, @RN varchar(6)
SET NOCOUNT ON

-- Clean up any temporary table instances
IF exists (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[#PCTempTable]') and OBJECTPROPERTY(ID, N'IsUserTable') = 1)
DROP TABLE [dbo].[#PCTempTable] 

-- Now create a temporary table to receive any new queue records
CREATE TABLE #PCTempTable (RN varchar(6), CN varchar(15), BPT int, PC varchar(12), DC datetime, FC varchar(1))

-- Receive any new queue records
INSERT INTO [dbo].[#PCTempTable]  (RN, CN, BPT, PC, DC, FC )
SELECT Rotation_Num, Customer_Name, BomPartType, PaintCode, DateChange, FlagChange
FROM s_it_a.Manufacturer.dbo.tblBOM_Data AS PCS
WHERE PCS.[FlagChange] = 'N'

-- Now, examine each record and normalize the PAINT CODE
-- Remove all SPACES and DASHES
DECLARE pc_cursor CURSOR FOR
SELECT RN, PC FROM [dbo].[#PCTempTable]

OPEN pc_cursor  
FETCH NEXT FROM pc_cursor INTO @RN, @OldPC

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
 
	BEGIN    
		SET @NewPC = ''
		SET @start = 1
		SET @finish =  len(@OldPC)
		WHILE @start <= @finish
			BEGIN
        				IF (substring(@OldPC,@start,1)<> ' ') and (substring(@OldPC,@start,1) <> '-')
       					BEGIN
               			 		SET @NewPC = @NewPC + substring(@OldPC,@start,1)
        					END

        				SET @start = @start + 1
			END

		UPDATE [dbo].[#PCTempTable] SET PC = @NewPC WHERE RN=@RN
		FETCH NEXT FROM pc_cursor INTO @RN, @OldPC
	END

-- Now insert the processed records into the production table
INSERT INTO tblBOM_Data  (Rotation_Num, Customer_Name, BomPartType, PaintCode, DateChange, FlagChange )
SELECT RN, CN, BPT, PC, DC, FC
FROM [dbo].[#PCTempTable]

-- Done with temporary table, remove it.
DROP TABLE [dbo].[#PCTempTable] 

-- Let PCS server know that we processed some records

UPDATE [manufacturer].dbo.tblBOM_Data
SET [manufacturer].dbo.tblBOM_Data.[flagchange] = 'I' 
WHERE [manufacturer].dbo.tblBOM_Data.[Rotation_Num] = Rotation_Num and [manufacturer].dbo.tblBOM_Data.[flagchange] = 'N'

GO

Error 7405: Heterogeneous queries require the ANSI_NULLS and ASNI_WARNINGS options be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

I found this googling Error 7405

Hi ! Add these two T-SQL
GO
SET ANSI_WARNINGS OFF
GO
SET ANSI_NULLS ON
GO
in your stored procedure & let me know if u still get the error
Best of Luck

but this does not seem to work.

I also tried using the AS Alias for the UPDATE at the end of my SP, but that does not work.

Thanks for the help so far.

 

[mrdenny]

Does the table on the remote server have a primary key on it? You can't do inserts or updates on a remote table without a primary key on the table.

What do you get when you add those commands to the begining of the procedure?

CREATE PROCEDURE [dbo].[spPaintQueue]  AS
SET ANSI_WARNINGS OFF
SET ANSI_NULLS ON
...

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tbode2006]

Got it working. Needed to put the Set ANSI commands before the Create Procedure. Thanks all.