[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 619
  • Last Modified:

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=user;pwd=user;initial catalog=employee.dbo.table1'

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.table1
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.table1
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
0
lynnton
Asked:
lynnton
  • 4
  • 3
1 Solution
 
rafranciscoCommented:
Try changing the @provider in the sp_addlinkedserver from MSDASQL to SQLOLEDB.  MSDASQL is the Microsoft OLE DB Provider for ODBC  while SQLOLEDB is the Microsoft OLE DB Provider for SQL Server.  

Also, change

@provstr='DRIVER={SQL SERVER};SERVER=1.1.1.1;uid=user;pwd=user;initial catalog=employee.dbo.table1'

to

@provstr='DRIVER={SQL SERVER};SERVER=1.1.1.1;uid=user;pwd=user;initial catalog=employee'

The Initial Catalog should be the database name and not the table.

Hope this helps.
0
 
lynntonAuthor Commented:
rafrancisco,

Change done.

when running >>select top 10 * from LinkServer.Catalog.dbo.table1


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.].
0
 
rafranciscoCommented:
Try rebuilding the index on that table.  It looks like it is corrupted.  You can use the DBCC REINDEX command.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
lynntonAuthor Commented:
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.
0
 
rafranciscoCommented:
Here are 2 ways that you can try:

SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=1.1.1.1;User ID=user;Password=user'
         ).Northwind.dbo.Categories

SELECT a.*
FROM OPENROWSET('SQLOLEDB','1.1.1.1';'user';'user',
   'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname') AS a
GO
0
 
lynntonAuthor Commented:
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.
0
 
rafranciscoCommented:
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.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now