Linked Server Query

When I execute the SQL Statement in the SQL Query Analyzer, I get the result
s
returned in 4 seconds but when I use the same query from a stored procedure
and execute it from SQL Analyzer, it is taking too long about 50-55 seconds.
Am I missing something? I have a join between external and internal table.
Thanks!!
Vibs
SELECT
AAPSUser.[ID]
, AAPSUser.First_Name
, AAPSUser.Last_Name
, AAPSUser.Second_Sections,
[UserExtraInfo].[title],
[UserExtraInfo].[profile1_text],
[UserExtraInfo].[profile2_text],
[UserExtraInfo].[profile3_text]
FROM
AAPS.AAPS_Web.DBO.iMIS_Data as AAPSUser
INNER JOIN UserExtraInfo ON AAPSUser.[ID] = UserExtraInfo.Id
WHERE
AAPSUser.[ID] = 003479Are you running the query and the proc containg the query locally i.e, same
SQL Server? (Same security creditials as well?) I.e, log into QA and run
the query then directly below (same window) exectue the proc (same query in
proc right?). You can also compare the execution plans as well for any
visable differences.
HTH
Jerry
"vibs" <vibs@.discussions.microsoft.com> wrote in message
news:6DC8F102-2C0F-4CCE-8831-1F228BAF81AC@.microsoft.com...
> When I execute the SQL Statement in the SQL Query Analyzer, I get the
> results
> returned in 4 seconds but when I use the same query from a stored
> procedure
> and execute it from SQL Analyzer, it is taking too long about 50-55
> seconds.
> Am I missing something? I have a join between external and internal table.
> Thanks!!
> Vibs
> SELECT
> AAPSUser.[ID]
> , AAPSUser.First_Name
> , AAPSUser.Last_Name
> , AAPSUser.Second_Sections,
> [UserExtraInfo].[title],
> [UserExtraInfo].[profile1_text],
> [UserExtraInfo].[profile2_text],
> [UserExtraInfo].[profile3_text]
> FROM
> AAPS.AAPS_Web.DBO.iMIS_Data as AAPSUser
> INNER JOIN UserExtraInfo ON AAPSUser.[ID] = UserExtraInfo.Id
> WHERE
> AAPSUser.[ID] = 003479|||Yes the query and the proc is running from the same query analyzer window on
the same SQL Server. I'll look into the execution plans
Thanks
"Jerry Spivey" wrote:

> Are you running the query and the proc containg the query locally i.e, sam
e
> SQL Server? (Same security creditials as well?) I.e, log into QA and run
> the query then directly below (same window) exectue the proc (same query i
n
> proc right?). You can also compare the execution plans as well for any
> visable differences.
> HTH
> Jerry
> "vibs" <vibs@.discussions.microsoft.com> wrote in message
> news:6DC8F102-2C0F-4CCE-8831-1F228BAF81AC@.microsoft.com...
>
>

linked server problem

I am trying to run this in the SQL Query Analyzer. I am running it from SQL
Server 2000 which links to a MSDE 2000 Server.
declare @.StartDate as nvarchar(12)
declare @.EndDate as nvarchar(12)
declare @.EmpID as nvarchar(4000)
declare @.query1 as varchar(8000)
declare @.empWhere as varchar(4500)
set @.StartDate='7/22/2003'
set @.EndDate='7/22/2003'
set @.EmpID='123456'
if @.EmpID='ALL'
set @.empWhere=''
else
set @.empWhere= ' AND (bh.EmployeeNumber in (''' +
replace(@.EmpID,',',''',''') + ''')) '
set @.query1 = 'SELECT p.FirstName, p.LastName,
dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
p.Id, ' +
'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
'BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID ' +
'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
@.StartDate +
''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) '+ @.empWhere +
'UNION ALL ' +
'SELECT p.FirstName, p.LastName,
dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
p.Id, ' +
'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
'BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID ' +
'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
@.StartDate +
''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) ' + @.empWhere +
'ORDER BY displayname,PunchDate'
print @.query1
EXEC(@.query1)
SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate - 2) AS
Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
INNER JOIN BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
UNION ALL SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate -
2) AS Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
INNER JOIN BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id = bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
ORDER BY p.LastName,p.FirstName,PunchDate
This causes the following error:
Server: Msg 7320, Level 16, State 2, Line 1
Could not execute query against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
Server]Statement(s) could not be prepared.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
Server]Line 1: Incorrect syntax near 'Id'.]
[OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
Server]Unclosed quotation mark before the character string 'Id'.]
We have been able to determine that the error is only generated when we add
a where clause for a text based field. So, if we remove the "AND
(bh.EmployeeNumber in ('123456')) " or use a numeric based field, the query
works correctly. Also, if we run the query from the linked server, the
query works.I still don't know what is causing the error, but i have been able to find a
workaround by using openquery.
"Kent Elmer" <kent_elmer@.nospamforme.com> wrote in message
news:Ojap6xUUDHA.1052@.TK2MSFTNGP09.phx.gbl...
> I am trying to run this in the SQL Query Analyzer. I am running it from
SQL
> Server 2000 which links to a MSDE 2000 Server.
> declare @.StartDate as nvarchar(12)
> declare @.EndDate as nvarchar(12)
> declare @.EmpID as nvarchar(4000)
> declare @.query1 as varchar(8000)
> declare @.empWhere as varchar(4500)
> set @.StartDate='7/22/2003'
> set @.EndDate='7/22/2003'
> set @.EmpID='123456'
> if @.EmpID='ALL'
> set @.empWhere=''
> else
> set @.empWhere= ' AND (bh.EmployeeNumber in (''' +
> replace(@.EmpID,',',''',''') + ''')) '
> set @.query1 = 'SELECT p.FirstName, p.LastName,
> dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
> CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
> p.Id, ' +
> 'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
> BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
> 'BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id
=> bh.PersonID ' +
> 'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
> @.StartDate +
> ''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) '+ @.empWhere +
> 'UNION ALL ' +
> 'SELECT p.FirstName, p.LastName,
> dbo.DisplayEmployeeByID(''bh.EmployeeNumber'') as displayname,
> CONVERT(DateTime, bh.MicroDate - 2) AS Punchdate, bh.ReaderDescription,
> p.Id, ' +
> 'bh.EmployeeNumber, bh.PersonnelTypeDescription FROM
> BADGES.SecurePerfect.dbo.PersonTable p INNER JOIN ' +
> 'BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id
=> bh.PersonID ' +
> 'WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN ''' +
> @.StartDate +
> ''' AND DATEADD(day, 1, ''' + @.EndDate + ''')) ' + @.empWhere +
> 'ORDER BY displayname,PunchDate'
> print @.query1
> EXEC(@.query1)
> SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate - 2) AS
> Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
> bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
> INNER JOIN BADGES.SecurePerfectArchive.dbo.BadgeHistoryTable bh ON p.Id => bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
> AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
> UNION ALL SELECT p.FirstName, p.LastName, CONVERT(DateTime, bh.MicroDate -
> 2) AS Punchdate, bh.ReaderDescription, p.Id, bh.EmployeeNumber,
> bh.PersonnelTypeDescription FROM BADGES.SecurePerfect.dbo.PersonTable p
> INNER JOIN BADGES.SecurePerfectHistory.dbo.BadgeHistoryTable bh ON p.Id => bh.PersonID WHERE (CONVERT(DateTime, bh.MicroDate - 2) BETWEEN '7/22/2003'
> AND DATEADD(day, 1, '7/22/2003')) AND (bh.EmployeeNumber in ('123456'))
> ORDER BY p.LastName,p.FirstName,PunchDate
> This causes the following error:
> Server: Msg 7320, Level 16, State 2, Line 1
> Could not execute query against OLE DB provider 'MSDASQL'.
> [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Statement(s) could not be prepared.]
> [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Line 1: Incorrect syntax near 'Id'.]
> [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Unclosed quotation mark before the character string 'Id'.]
> We have been able to determine that the error is only generated when we
add
> a where clause for a text based field. So, if we remove the "AND
> (bh.EmployeeNumber in ('123456')) " or use a numeric based field, the
query
> works correctly. Also, if we run the query from the linked server, the
> query works.
>sql

linked server and pipe delimted file

I have can query a comma delimited file in query analyzer easily. Content of
mytest1.csv file on txtsrv21 linked server is as follows
/*
c1,c2,c3
11,12,13
22,33,44
55,66,77
88,33,12
*/
When I run the following select:
SELECT c1,c2,c3 FROM txtsrv21...[mytest1#csv]
I get the following results
/*
c1 c2 c3
-- -- --
11 12 13
22 33 44
55 66 77
88 33 12
(4 row(s) affected)
*/
But if I change the file content such that its pipe delimited instead of
comma delimited as follows:
/*
c1|c2|c3
11|12|13
22|33|44
55|66|77
88|33|12
*/
I get following errors when I run the following select
SELECT c1,c2,c3 FROM txtsrv21...[mytest1#csv]
/*
Server: Msg 207, Level 16, State 3, Line 9
Invalid column name 'c1'.
Server: Msg 207, Level 16, State 1, Line 9
Invalid column name 'c2'.
Server: Msg 207, Level 16, State 1, Line 9
Invalid column name 'c3'.
*/
I would like to be able to run select on pipe delimited content just as I
could on comma delimited.
Is there any setting that I am missing?
Google or searching through forums is not turning up any thing.
Please help.
TIA..You'll need to put a file named schema.ini in the same folder with your
mytest1.csv text file. See
http://msdn.microsoft.com/library/d...ma_ini_file.asp
for how to create the schema.ini text file for a custom delimiter, like |
Good luck.
"sqlster" wrote:

> I have can query a comma delimited file in query analyzer easily. Content
of
> mytest1.csv file on txtsrv21 linked server is as follows
> /*
> c1,c2,c3
> 11,12,13
> 22,33,44
> 55,66,77
> 88,33,12
> */
> When I run the following select:
> SELECT c1,c2,c3 FROM txtsrv21...[mytest1#csv]
> I get the following results
> /*
> c1 c2 c3
> -- -- --
> 11 12 13
> 22 33 44
> 55 66 77
> 88 33 12
> (4 row(s) affected)
> */
> But if I change the file content such that its pipe delimited instead of
> comma delimited as follows:
> /*
> c1|c2|c3
> 11|12|13
> 22|33|44
> 55|66|77
> 88|33|12
> */
> I get following errors when I run the following select
> SELECT c1,c2,c3 FROM txtsrv21...[mytest1#csv]
>
> /*
> Server: Msg 207, Level 16, State 3, Line 9
> Invalid column name 'c1'.
> Server: Msg 207, Level 16, State 1, Line 9
> Invalid column name 'c2'.
> Server: Msg 207, Level 16, State 1, Line 9
> Invalid column name 'c3'.
> */
> I would like to be able to run select on pipe delimited content just as I
> could on comma delimited.
> Is there any setting that I am missing?
> Google or searching through forums is not turning up any thing.
> Please help.
> TIA..|||As a follow up, I just tested the following
Create a text file named schema.ini in the same directory with mytest1.csv .
The schema.ini file had the following lines:
[mytest1.csv]
Format=Delimited(|)
The contents of mytest1.csv :
c1|c2|c3
4|5|6
7|8|9
10|11|12
Add the linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'f:\texts',
NULL,
'Text'
GO
SELECT * FROM txtsrv...file1#txt
returns
c1 c2 c3
-- -- --
4 5 6
7 8 9
10 11 12
(3 row(s) affected)
You can even change the delimeter "on the fly," without dropping and
re-creating the linked server. You can even specify different delimiters for
different files within the same folder.
"Mark Williams" wrote:
> You'll need to put a file named schema.ini in the same folder with your
> mytest1.csv text file. See
> http://msdn.microsoft.com/library/d...ma_ini_file.asp
> for how to create the schema.ini text file for a custom delimiter, like |
> Good luck.
> "sqlster" wrote:
>|||Thanks Mark,
It worked..
"Mark Williams" wrote:
> You'll need to put a file named schema.ini in the same folder with your
> mytest1.csv text file. See
> http://msdn.microsoft.com/library/d...ma_ini_file.asp
> for how to create the schema.ini text file for a custom delimiter, like |
> Good luck.
> "sqlster" wrote:
>|||Mark,
What if all the imported files in the designated directory are pipe
delimited? Do I have to keep adding those file names in the schema.ini file?
I will look around for the answer but if you have the solution handy, please
post it here.
Once again, thanks for the fast answer.
"Mark Williams" wrote:
> As a follow up, I just tested the following
> Create a text file named schema.ini in the same directory with mytest1.csv
.
> The schema.ini file had the following lines:
> [mytest1.csv]
> Format=Delimited(|)
> The contents of mytest1.csv :
> c1|c2|c3
> 4|5|6
> 7|8|9
> 10|11|12
> Add the linked server
> EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'f:\texts',
> NULL,
> 'Text'
> GO
> SELECT * FROM txtsrv...file1#txt
> returns
> c1 c2 c3
> -- -- --
> 4 5 6
> 7 8 9
> 10 11 12
> (3 row(s) affected)
> You can even change the delimeter "on the fly," without dropping and
> re-creating the linked server. You can even specify different delimiters f
or
> different files within the same folder.
> "Mark Williams" wrote:
>|||You can edit the registry to set the default delimiter that JET will use. I
haven't tried this. Run regedit, go to the
HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Text key. (your version may be
different, lik3 3.5 instead of 4.0).
Double-click on the Format string value. The default value is CSVDelimited.
Change it to
Delimited(|)
click OK, and exit regedit. This will change the default delimiter character
to a '|'. I am not sure if it will require a restart.
"sqlster" wrote:
> Mark,
> What if all the imported files in the designated directory are pipe
> delimited? Do I have to keep adding those file names in the schema.ini fil
e?
> I will look around for the answer but if you have the solution handy, plea
se
> post it here.
> Once again, thanks for the fast answer.
>
> "Mark Williams" wrote:
>|||Tested this and it works. Don't even need to reboot!
"Mark Williams" wrote:
> You can edit the registry to set the default delimiter that JET will use.
I
> haven't tried this. Run regedit, go to the
> HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Text key. (your version may be
> different, lik3 3.5 instead of 4.0).
> Double-click on the Format string value. The default value is CSVDelimited
.
> Change it to
> Delimited(|)
> click OK, and exit regedit. This will change the default delimiter charact
er
> to a '|'. I am not sure if it will require a restart.
> "sqlster" wrote:
>|||Is there a way other than registry manipulation?
TIA..
"Mark Williams" wrote:
> You can edit the registry to set the default delimiter that JET will use.
I
> haven't tried this. Run regedit, go to the
> HKLM\SOFTWARE\Microsoft\Jet\4.0\Engines\Text key. (your version may be
> different, lik3 3.5 instead of 4.0).
> Double-click on the Format string value. The default value is CSVDelimited
.
> Change it to
> Delimited(|)
> click OK, and exit regedit. This will change the default delimiter charact
er
> to a '|'. I am not sure if it will require a restart.
> "sqlster" wrote:
>|||Not sure if you saw my earlier post, but I did test editing the registry to
define the customer delimiter of |, and it works. Didn't even need to reboot
.
The only thing I would be cautious about is that this will change the defaul
t
delimiter for all Text file connections.
If you don't want to edit the registry, then I'm afraid the only way is to
create that schema.ini file, and have a section for every file in the folder
that the linked server points to. The schema.ini file allows you to override
the default on a file-by-file basis. I tried using wildcards, like
[*.csv]
Format=Delimited(|)
but it did not work.
"sqlster" wrote:
> Is there a way other than registry manipulation?
> TIA..
> "Mark Williams" wrote:
>|||Mark, thank you very much...
"Mark Williams" wrote:
> Not sure if you saw my earlier post, but I did test editing the registry t
o
> define the customer delimiter of |, and it works. Didn't even need to rebo
ot.
> The only thing I would be cautious about is that this will change the defa
ult
> delimiter for all Text file connections.
> If you don't want to edit the registry, then I'm afraid the only way is to
> create that schema.ini file, and have a section for every file in the fold
er
> that the linked server points to. The schema.ini file allows you to overri
de
> the default on a file-by-file basis. I tried using wildcards, like
> [*.csv]
> Format=Delimited(|)
> but it did not work.
> "sqlster" wrote:
>

Linked Server (Index service)

I have added my indexing service catalogs via exec
sp_addlinkedserver from Query Analyzer (all of this is
running on the same server). I am doing this because I
wish to write some asp.net pages to query the catalogs.
However, in Enterprise Manager, when I go to expand
Security->Linked Servers, I can see the catalogs I have
added, with the "tables" and "views" items underneath. If
I right click on either, I get "Error 7301: Could not
obtain a required interface from OLE DB provider MSIDXS"
What the heck?
Please have mercy on my poor soul. I have not used
newsgroups before, so I am not sure how to get a reply,
therefore I added my own email address above.
Thanks in advance,
PeteDoes the Everyone group have Full Control permissions on the
c:\winnt\system32\msidxs.dll (or whatever your actual path is)?
Are you on the latest version of msidxs.dll?
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||This may be completely obtuse, but I am unable to locate that dll anywhere o
n the server in question.
"Cindy Gross (MSFT)" wrote:

> Does the Everyone group have Full Control permissions on the
> c:\winnt\system32\msidxs.dll (or whatever your actual path is)?
> Are you on the latest version of msidxs.dll?
> Cindy Gross, MCDBA, MCSE
> http://cindygross.tripod.com
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||Hi,
I'm getting the same error as you. I've been searching through the web for
a solution but I didn't find anything useful.
The dll does not exist on my server.
Did you find a solution in a meantime?
Thanks a lot
Ronald Selimi
"Pete Wojtkowiak" wrote:
[vbcol=seagreen]
> This may be completely obtuse, but I am unable to locate that dll anywhere
on the server in question.
> "Cindy Gross (MSFT)" wrote:
>