lynnton
asked on
link server using variable on local sp
Hi,
two sql server,
one server is at 1.1.1.1
another is the one i'm using is at 2.2.2.2
I have an account with read access only on the other server (1.1.1.1)
on local (2.2.2.2) query analyzer:
exec sp_addlinkedserver
@server='remoteSQL',
@srvproduct='',
@provider='MSDASQL',
@provstr='DRIVER={SQL SERVER};SERVER=1.1.1.1;uid
everything is good, i can see the link server and showing all the tables on the remote server (1.1.1.1)
I created a simple store procedure on the local server (2.2.2.2).
create proc [SP_count] <-----------------this one was stored on 2.2.2.2
as
select top 100 *
from remoteSQL.employee.dbo.tab
dtstart between '1/1/2005' and '1/1/2005 23:59:59'
go
exec [SP_count] <------------takes 9 seconds to finish, which is good. (i'm on 2.2.2.2 using QA)
++++++++++++++++++++++++++
Problem part arrives when I added a variable on the store procedure.
create proc [SP_count2] <-----------------this one was stored on 2.2.2.2
@shiftdate datetime
as
declare @shiftdatestop datetime
select @shiftdatestop=@shiftdate +cast('23:59:59' as datetime)
select top 100 *
from remoteSQL.employee.dbo.tab
where dtstart between @shiftdate and @shiftdatestop
go
exec [SP_count] @shiftdate='1/1/2005' <------------takes 3 hours to finish (i'm on 2.2.2.2 using QA)
Please kindly give guidance what can we do to troubleshoot and find the culprit.
there's something wrong with passing a value to the variable on the local sp
dtstart is index
dtstart is datetime
two sql server,
one server is at 1.1.1.1
another is the one i'm using is at 2.2.2.2
I have an account with read access only on the other server (1.1.1.1)
on local (2.2.2.2) query analyzer:
exec sp_addlinkedserver
@server='remoteSQL',
@srvproduct='',
@provider='MSDASQL',
@provstr='DRIVER={SQL SERVER};SERVER=1.1.1.1;uid
everything is good, i can see the link server and showing all the tables on the remote server (1.1.1.1)
I created a simple store procedure on the local server (2.2.2.2).
create proc [SP_count] <-----------------this one was stored on 2.2.2.2
as
select top 100 *
from remoteSQL.employee.dbo.tab
dtstart between '1/1/2005' and '1/1/2005 23:59:59'
go
exec [SP_count] <------------takes 9 seconds to finish, which is good. (i'm on 2.2.2.2 using QA)
++++++++++++++++++++++++++
Problem part arrives when I added a variable on the store procedure.
create proc [SP_count2] <-----------------this one was stored on 2.2.2.2
@shiftdate datetime
as
declare @shiftdatestop datetime
select @shiftdatestop=@shiftdate +cast('23:59:59' as datetime)
select top 100 *
from remoteSQL.employee.dbo.tab
where dtstart between @shiftdate and @shiftdatestop
go
exec [SP_count] @shiftdate='1/1/2005' <------------takes 3 hours to finish (i'm on 2.2.2.2 using QA)
Please kindly give guidance what can we do to troubleshoot and find the culprit.
there's something wrong with passing a value to the variable on the local sp
dtstart is index
dtstart is datetime
ASKER
rafrancisco,
Change done.
when running >>select top 10 * from LinkServer.Catalog.dbo.tab
Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'PK_calls_1' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].
Change done.
when running >>select top 10 * from LinkServer.Catalog.dbo.tab
Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'PK_calls_1' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].
Try rebuilding the index on that table. It looks like it is corrupted. You can use the DBCC REINDEX command.
ASKER
rafrancisco,
Maybe we can use a different type of link instead of using the link server?
is there anyother way to call a table from another sql server?
This will finally tell us if indeed it's a connection issue.
Thanks.
Maybe we can use a different type of link instead of using the link server?
is there anyother way to call a table from another sql server?
This will finally tell us if indeed it's a connection issue.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
rafrancisco,
First one error returned:
Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_workstation' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].
second one worked.
But when created in a store procedure it won't save. error message as :
Server: Msg 8180, Level 16, State 1, Procedure SP_sample_TEST, Line 5
Statement(s) could not be prepared.
Server: Msg 137, Level 15, State 1, Procedure SP_sample_TEST, Line 5
Must declare the variable '@da1'.
[OLE/DB provider returned message: Deferred prepare could not be completed.]
CREATE PROCEDURE [dbo].[SP_sample_TEST]
@da1 datetime,
@da2 datetime
as
select a.* from openrowset('SQLOLEDB', 'my.com';'no';'no',
'select top 10 * from catalog.dbo.table1 where dtstart between @da1 and @da2') as a
go
Thanks.
First one error returned:
Server: Msg 7319, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' returned a 'NON-CLUSTERED and NOT INTEGRATED' index 'IX_workstation' with incorrect bookmark ordinal 0.
OLE DB error trace [Non-interface error: OLE/DB provider returned an invalid bookmark ordinal from the index rowset.].
second one worked.
But when created in a store procedure it won't save. error message as :
Server: Msg 8180, Level 16, State 1, Procedure SP_sample_TEST, Line 5
Statement(s) could not be prepared.
Server: Msg 137, Level 15, State 1, Procedure SP_sample_TEST, Line 5
Must declare the variable '@da1'.
[OLE/DB provider returned message: Deferred prepare could not be completed.]
CREATE PROCEDURE [dbo].[SP_sample_TEST]
@da1 datetime,
@da2 datetime
as
select a.* from openrowset('SQLOLEDB', 'my.com';'no';'no',
'select top 10 * from catalog.dbo.table1 where dtstart between @da1 and @da2') as a
go
Thanks.
Try this one:
CREATE PROCEDURE [dbo].[SP_sample_TEST]
@da1 datetime,
@da2 datetime
AS
DECLARE @SQL VARCHAR(1000)
SET @SQL = '
select a.* from openrowset(''SQLOLEDB'', ''my.com'';''no'';''no'',
''select top 10 * from catalog.dbo.table1 where dtstart between ''' + CONVERT(VARCHAR(10), @da1, 101) + ''' and ''' + CONVERT(VARCHAR(10), @da2, 101) + ''') as a'
EXECUTE (@SQL)
go
If this gives you the same error as the others, then there is really something wrong with your table and you need to reindex it.
CREATE PROCEDURE [dbo].[SP_sample_TEST]
@da1 datetime,
@da2 datetime
AS
DECLARE @SQL VARCHAR(1000)
SET @SQL = '
select a.* from openrowset(''SQLOLEDB'', ''my.com'';''no'';''no'',
''select top 10 * from catalog.dbo.table1 where dtstart between ''' + CONVERT(VARCHAR(10), @da1, 101) + ''' and ''' + CONVERT(VARCHAR(10), @da2, 101) + ''') as a'
EXECUTE (@SQL)
go
If this gives you the same error as the others, then there is really something wrong with your table and you need to reindex it.
Also, change
@provstr='DRIVER={SQL SERVER};SERVER=1.1.1.1;uid
to
@provstr='DRIVER={SQL SERVER};SERVER=1.1.1.1;uid
The Initial Catalog should be the database name and not the table.
Hope this helps.