March 22, 2011 at 1:43 am
Hi
I have a linked server DB and i'm wondering if this can be done, what i have is a parameter being passed into an SP @workorder and its supposed to query the LS db...
--But im not sure how to do it?
SELECT TB.name, TB.column
FROM [10.146.22.35].smartreports.sys.tables AS TB
WHERE TB.column = @workorder
AND TB.SCHEMA_ID = 6
And from this I'm wanting to bring back data for certain columns to match the declarations so that i can then do an insert into a sql DB table...
DECLARE @Docyear AS VARCHAR(20)
DECLARE @movementtype AS VARCHAR(18)
DECLARE @BatchNumber AS VARCHAR(20)
DECLARE @Quantity AS int
DECLARE @MaterialNumber AS VARCHAR(18)
--I need to get values brought back from select query to help with insert below
@Docyear = TB.name.column @movementtype = TB.name.column
@BatchNumber = TB.name.column
@Quantity = TB.name.column
@MaterialNumber = TB.name.column
INSERT INTO wp4tbl_TblCompVerifySapBatch(Workorder,Docyear,movementtype,BatchNumber,Quantity,MaterialNumber)
VALUES (@Workorder,@Docyear,@movementtype,@BatchNumber,@Quantity,@MaterialNumber)
Thanks
March 22, 2011 at 1:57 am
SELECT TB.name, TB.column
FROM [10.146.22.35].smartreports.sys.tables AS TB
WHERE TB.column = @workorder
AND TB.SCHEMA_ID = 6
I dont see any problem in your select ... from LS query assuming you following :-
select column/s from [servername].[databasename].[scemaname].[tablename/objectname].
But what the other thing you can try is fetch the value in some temp table of local server so that you do not face any problem if your linked server is broken.
What I am saying is :-
select column/s into #temp from [servername].[databasename].[scemaname].[tablename/objectname].
You can insert record in some permanent temp table as well and later can delete it.
In this way your record will always be available to your local server and you will save cost in querying LS as well will be safe if LS is broken.
----------
Ashish
March 22, 2011 at 2:02 am
Hi crazy4sql Im not the best with sql could you show me an example of what you mean?
I 'll be able to do the select part that will be fine, its just getting the temp table with values to then do insert?
Thanks for reply
March 22, 2011 at 2:12 am
First of all my previous reply was using LS properly in query and havent looked in your where condition or table name.
FROM [10.146.22.35].smartreports.sys.tables
Now, looking into your select query:-
SELECT TB.name, TB.column
FROM [10.146.22.35].smartreports.sys.tables AS TB
WHERE TB.column = @workorder
AND TB.SCHEMA_ID = 6
1)seems like you querying the sys.tables in linked server smartreports database. But in sys.tables you will not have any result for tb.column, so not sure what you referring here.
2) Also from where are you getting the value in @workorder and what type of variable is this(int/varchar/date....)?
----------
Ashish
March 22, 2011 at 2:21 am
Hi
The select query for the LS i need to get that correct that was just a quick example of what im trying to achieve.
hope below makes sense.
and this is my SP...
CREATE PROCEDURE [dbo].[w4sp_getSAPData]
@Workorder nvarchar(12)
AS
SET NOCOUNT ON
GO
DECLARE @Docyear AS VARCHAR(20)
DECLARE @movementtype AS VARCHAR(18)
DECLARE @BatchNumber AS VARCHAR(20)
DECLARE @Quantity AS int
DECLARE @MaterialNumber AS VARCHAR(18)
begin
INSERT INTO wp4tbl_TblCompVerifySapBatch (Workorder,Docyear,movementtype,BatchNumber,Qty,MaterialNumber)
VALUES (@Workorder,@Docyear,@movementtype,@BatchNumber,@Qty,@MaterialNumber)
-- I need to change to table rather than sys.tables
SELECT TB.name, TB.column
FROM [10.146.22.35].smartreports.tablename AS TB
WHERE TB.column = @workorder
--once select query above comes back with values get into temp table to help with insert
--need this populated from returned data to supply the insert above
@Docyear = TB.name.column
@movementtype = TB.name.column
@BatchNumber = TB.name.column
@Quantity = TB.name.column
@MaterialNumber= TB.name.column
end
SET NOCOUNT OFF
March 22, 2011 at 2:43 am
begin
INSERT INTO wp4tbl_TblCompVerifySapBatch (Workorder,Docyear,movementtype,BatchNumber,Qty,MaterialNumber)
VALUES (@Workorder,@Docyear,@movementtype,@BatchNumber,@Qty,@MaterialNumber)
-- I need to change to table rather than sys.tables
SELECT TB.name, TB.column
FROM [10.146.22.35].smartreports.tablename AS TB
WHERE TB.column = @workorder
--once select query above comes back with values get into temp table to help with insert
--need this populated from returned data to supply the insert above
@Docyear = TB.name.column
@movementtype = TB.name.column
@BatchNumber = TB.name.column
@Quantity = TB.name.column
@MaterialNumber= TB.name.column
end
SET NOCOUNT OFF
insetead of this you can have:-
begin
insert into wp4tbl_TblCompVerifySapBatch(Workorder,Docyear,movementtype,BatchNumber,Qty,MaterialNumber)
(select @workorder,TB.name.column,TB.name.column,TB.name.column,TB.name.column,
TB.name.column from table
)
end
----------
Ashish
March 22, 2011 at 2:44 am
I didn't realise you could write Select * FROM dbserver1.dbname1.table1 when dbserver1 is a different server from the one the query is being run on. What sort of authentication is in play here?
March 22, 2011 at 2:49 am
What sort of authentication is in play here?
Authentication you define in secuirty tab when you configuring the LinkedServer
----------
Ashish
March 22, 2011 at 2:50 am
OK, so you think this possible...
CREATE PROCEDURE [dbo].[w4sp_getSAPData]
@Workorder nvarchar(12)
AS
SET NOCOUNT ON
GO
begin
INSERT INTO wp4tbl_TblCompVerifySapBatch (Workorder,Docyear,movementtype,BatchNumber,Qty,MaterialNumber)
VALUES (@Workorder,TB.name.column,TB.name.column,TB.name.column,TB.name.column,TB.name.column)
SELECT TB.name.column, TB.name.column, TB.name.column, TB.name.column, TB.name.column
FROM [10.146.22.35].smartreports.tablename AS TB
WHERE TB.name.column = @workorder
end
SET NOCOUNT OFF
March 22, 2011 at 3:18 am
Is it also possible to add a delete from table first then do insert and select?
DELETE from wp4tbl_TblCompVerifySapBatch where EXISTS
(SELECT TB.name.column, TB.name.column, TB.name.column, TB.name.column, TB.name.column
FROM [10.146.22.35].smartreports.tablename AS TB
WHERE TB.name.column = @workorder)
INSERT INTO wp4tbl_TblCompVerifySapBatch (Workorder,Docyear,movementtype,BatchNumber,Qty,MaterialNumber)
VALUES (@Workorder,TB.name.column,TB.name.column,TB.name.column,TB.name.column,TB.name.column
Thanks for your prevoius reply crazy4sql!
March 22, 2011 at 3:32 am
March 22, 2011 at 8:32 am
Also keep in mind SQL Server will likely not have stats on the table in the linked server, so any join performance with a linked table is impacted by that. SQL Server will estimate the number of rows (usually I see 50,000)
March 22, 2011 at 8:34 am
OK thank you.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply