OpenQuery and passing variables

Anyone,

Is this possible?

I am connecting to a TeraData server via MS SQL 8.0 using the OpenQuery
statement. I need to pass a list of ever-changing deal numbers My
list of numbers are stored as a table on MS SQL.

So what I want is this

Select * from OpenQuery(TeraSrvr, "
Select Col1
, Col2
, Col3
>From Teradata_Table_1
Where Deal_no in (Select Deal_no from SQLTable)
")

Now I know that wont work, but How can I pass 184 Deal Numbers from my
SQL server to this query before it is sent to the Teradata server to be
done? Do I have to keep re-doing an in statement each month?

Anyone can help?

Doug(douglascfast@.hotmail.com) writes:
> So what I want is this
> Select * from OpenQuery(TeraSrvr, "
> Select Col1
> , Col2
> , Col3
>>From Teradata_Table_1
> Where Deal_no in (Select Deal_no from SQLTable)
> ")
> Now I know that wont work, but How can I pass 184 Deal Numbers from my
> SQL server to this query before it is sent to the Teradata server to be
> done? Do I have to keep re-doing an in statement each month?

To do it with OPENQUERY you would have to use dynamic SQL to build
the SQL statement, and also to execute the OPENQUERY thing, as
OPENQUERY does not take parameters of any kind of whatsoever.

But cannot you not use a linked server instead:

SELECT t.*
FROM TeraSrvr.db.catalog.Teradata_Table_1 t
WHERE t.Deal_no in (Select s.Deal_no from SQLTable s)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for this, I will chekc on the link bit below. Can you help me
with how to do this in Dynamic SQL. I can't find how to get a list of
my numbers into a Var so I can pass them on and use them in the
openquery statement.

Thanks again

Doug

Erland Sommarskog wrote:
> (douglascfast@.hotmail.com) writes:
> > So what I want is this
> > Select * from OpenQuery(TeraSrvr, "
> > Select Col1
> > , Col2
> > , Col3
> >>From Teradata_Table_1
> > Where Deal_no in (Select Deal_no from SQLTable)
> > ")
> > Now I know that wont work, but How can I pass 184 Deal Numbers from my
> > SQL server to this query before it is sent to the Teradata server to be
> > done? Do I have to keep re-doing an in statement each month?
> To do it with OPENQUERY you would have to use dynamic SQL to build
> the SQL statement, and also to execute the OPENQUERY thing, as
> OPENQUERY does not take parameters of any kind of whatsoever.
> But cannot you not use a linked server instead:
> SELECT t.*
> FROM TeraSrvr.db.catalog.Teradata_Table_1 t
> WHERE t.Deal_no in (Select s.Deal_no from SQLTable s)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(douglascfast@.hotmail.com) writes:
> Thanks for this, I will chekc on the link bit below. Can you help me
> with how to do this in Dynamic SQL. I can't find how to get a list of
> my numbers into a Var so I can pass them on and use them in the
> openquery statement.

Alas, in SQL 2000 the only safe way is to run a loop over the table.
There are some shortcuts, but they unrely undefined behaviour, so I
advise against such use.

For dynamic SQL in general, I have a longer article on the topic on
my web site: http://www.sommarskog.se/dynamic_sql.html.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

OpenDataSource "Unspecified Error" connecting to Excel file

OpenDataSource gives "Unspecified Error" when connecting to local Excel
spreadsheet file with account not in local administrators group, on
Microsoft Windows 2003 / Microsoft SQL Server 2000 sp3a.
I am having a permissions problem using the OpenDataSource command to open
an Excel spreadsheet located on our Sql Server. The query is as follows:
SELECT * FROM opendatasource('Microsoft.Jet.OLEDB.4.0', 'Data
Source="C:\myPath\tempExcel.xls";User ID=Admin;Password=;Extended
properties=Excel 8.0')...['US Mail$'] tblImport
As you can see, the file is on the local Sql Server. All domain users have
full control to the myPath directory. The Sql Server service (and Sql
Server agent service) is running as the domain administrator account. We
are using integrated security to authenticate with the Sql Server. I even
tried setting up the proxy account as the domain administrator thinking that
maybe the opendatasource would use that.
The error I get when running this query on a remote machine as a domain user
through Query Analyzer is "Unspecified Error" in the Jet Initialization.
Even if I add domain users to the server administrator role, I still get the
error. However, if I add that domain users account to the local
administrators group on the Sql Server, the query works. I know this is not
an NTFS permission issue, as I have auditing turned on and there are no
object security failures in the event logs.
So my question is: what specific permissions are required for a domain user
to run an OpenDataSource query like this without having to add them to the
local administrators group on the Sql Server? Having to add the domain
users that need to run this query to the Sql Servers local administrators
group is simply unacceptable...
Thanks,
Frank Jones
Hi Frank,
From your descriptions, I understood that your non-admin users are not able
to execute OpenDataSource with the error message "Unspecified Error" while
only those who have system admin permissions could do it. Have I understood
you? Correct me if I was wrong.
First of all, please make sure your domain users have the access permission
to the following Registry Key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance
Name>\Providers\Microsoft.Jet.OLEDB.4.0
value must not exist or be 1.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Providers\Microsoft.Jet.OL
EDB.4.0
DisallowAdhocAccess = 0 should be added to allow access.
NOTE that you *must* Reboot the server for these registry changes to take
effect.
Secondly, please make sure your users have access permission to
C:\WINDOWS\system32\msjet40.dll and its folder
If all above does not work, I would like to suggest you using Regmon to
monitor which registry key was checked and denied for your domain users
with the following steps
1. Select the correct version for your Regmon and then download it from the
web site below
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Install Regmon
3. Login as member of Local Administrator,
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file1
4. Login as member of non-Administrator
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file2
Compare the difference between file1 and file2, give the permissions to
those ACCESSDENY keys to non-admin users.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

OpenDataSource "Unspecified Error" connecting to Excel file

OpenDataSource gives "Unspecified Error" when connecting to local Excel
spreadsheet file with account not in local administrators group, on
Microsoft Windows 2003 / Microsoft SQL Server 2000 sp3a.
I am having a permissions problem using the OpenDataSource command to open
an Excel spreadsheet located on our Sql Server. The query is as follows:
SELECT * FROM opendatasource('Microsoft.Jet.OLEDB.4.0', 'Data
Source="C:\myPath\tempExcel.xls";User ID=Admin;Password=;Extended
properties=Excel 8.0')...['US Mail$'] tblImport
As you can see, the file is on the local Sql Server. All domain users have
full control to the myPath directory. The Sql Server service (and Sql
Server agent service) is running as the domain administrator account. We
are using integrated security to authenticate with the Sql Server. I even
tried setting up the proxy account as the domain administrator thinking that
maybe the opendatasource would use that.
The error I get when running this query on a remote machine as a domain user
through Query Analyzer is "Unspecified Error" in the Jet Initialization.
Even if I add domain users to the server administrator role, I still get the
error. However, if I add that domain users account to the local
administrators group on the Sql Server, the query works. I know this is not
an NTFS permission issue, as I have auditing turned on and there are no
object security failures in the event logs.
So my question is: what specific permissions are required for a domain user
to run an OpenDataSource query like this without having to add them to the
local administrators group on the Sql Server? Having to add the domain
users that need to run this query to the Sql Servers local administrators
group is simply unacceptable...
Thanks,
Frank JonesHi Frank,
From your descriptions, I understood that your non-admin users are not able
to execute OpenDataSource with the error message "Unspecified Error" while
only those who have system admin permissions could do it. Have I understood
you? Correct me if I was wrong.
First of all, please make sure your domain users have the access permission
to the following Registry Key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server\<Instance
Name>\Providers\Microsoft.Jet.OLEDB.4.0
value must not exist or be 1.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\Providers\Microsoft.Jet.OL
EDB.4.0
DisallowAdhocAccess = 0 should be added to allow access.
NOTE that you *must* Reboot the server for these registry changes to take
effect.
Secondly, please make sure your users have access permission to
C:\WINDOWS\system32\msjet40.dll and its folder
If all above does not work, I would like to suggest you using Regmon to
monitor which registry key was checked and denied for your domain users
with the following steps
1. Select the correct version for your Regmon and then download it from the
web site below
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Install Regmon
3. Login as member of Local Administrator,
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file1
4. Login as member of non-Administrator
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file2
Compare the difference between file1 and file2, give the permissions to
those ACCESSDENY keys to non-admin users.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Open default DB in Enterprise Manager upon connecting to server (SQL2000)

Hello,
1. My hosting provider hosts ~500 databases on the server where my DB
resides. To open my database I use SQL Server 2000 Enterprise Manager.
While server connection timeout is short enough (about 1-2 seconds), when I
click "Databases" folder in server hierarchy, trying to enlist all
databases, it takes and extremely long period of time to set up a list.
Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
and its process could be terminated only by Task Manager.
My first question is: why does EM take such a timeout to display a list of
databases and why in most cases it halts?
2. To open my database and to start working with it I each time have to
perform the above steps. However I find it rather silly - to enlist all
databases first and ONLY THEN select mine. Logical approach to the process
of database connection suggests NOT to enlist all databases (I really need
only one!), but configure server (client?) that way that upon connecting to
server it automatically defaults to my DB.
My second question is:
Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
not to enlist all databases upon connecting to server, but switch directly
to mine by default?
Thank you in advance.
Vsevolod.Hi
"Vsevolod Ukrainsky" wrote:
> Hello,
> 1. My hosting provider hosts ~500 databases on the server where my DB
> resides. To open my database I use SQL Server 2000 Enterprise Manager.
> While server connection timeout is short enough (about 1-2 seconds), when I
> click "Databases" folder in server hierarchy, trying to enlist all
> databases, it takes and extremely long period of time to set up a list.
> Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
> and its process could be terminated only by Task Manager.
> My first question is: why does EM take such a timeout to display a list of
> databases and why in most cases it halts?
> 2. To open my database and to start working with it I each time have to
> perform the above steps. However I find it rather silly - to enlist all
> databases first and ONLY THEN select mine. Logical approach to the process
> of database connection suggests NOT to enlist all databases (I really need
> only one!), but configure server (client?) that way that upon connecting to
> server it automatically defaults to my DB.
>
Enterprise manager lists only what is there, 500 databases on a single
instance is an unusually high number of databases. Every database on that
instance will be using the same system databases, therefore potentially
causing significant blocking of other users.
You should consider asking your ISP to have multiple instances with fewer
databases rather than getting MS to change their product for something that
should not be common practice.
> My second question is:
> Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
> not to enlist all databases upon connecting to server, but switch directly
> to mine by default?
No, and at this point in the life cycle of SQL 2000 it is (IMO) unlikely to
happen but you could log a feature request at
https://connect.microsoft.com/SQLServer
> Thank you in advance.
> Vsevolod.
John|||Thank you so much for your help!
"Vsevolod Ukrainsky" <vsevolod@.ukrainsky.ru> wrote in message
news:eobGc2ALHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
<SKIPPED>

Open default DB in Enterprise Manager upon connecting to server (SQL2000)

Hello,
1. My hosting provider hosts ~500 databases on the server where my DB
resides. To open my database I use SQL Server 2000 Enterprise Manager.
While server connection timeout is short enough (about 1-2 seconds), when I
click "Databases" folder in server hierarchy, trying to enlist all
databases, it takes and extremely long period of time to set up a list.
Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
and its process could be terminated only by Task Manager.
My first question is: why does EM take such a timeout to display a list of
databases and why in most cases it halts?
2. To open my database and to start working with it I each time have to
perform the above steps. However I find it rather silly - to enlist all
databases first and ONLY THEN select mine. Logical approach to the process
of database connection suggests NOT to enlist all databases (I really need
only one!), but configure server (client?) that way that upon connecting to
server it automatically defaults to my DB.
My second question is:
Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
not to enlist all databases upon connecting to server, but switch directly
to mine by default?
Thank you in advance.
Vsevolod.
Hi
"Vsevolod Ukrainsky" wrote:

> Hello,
> 1. My hosting provider hosts ~500 databases on the server where my DB
> resides. To open my database I use SQL Server 2000 Enterprise Manager.
> While server connection timeout is short enough (about 1-2 seconds), when I
> click "Databases" folder in server hierarchy, trying to enlist all
> databases, it takes and extremely long period of time to set up a list.
> Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
> and its process could be terminated only by Task Manager.
> My first question is: why does EM take such a timeout to display a list of
> databases and why in most cases it halts?
> 2. To open my database and to start working with it I each time have to
> perform the above steps. However I find it rather silly - to enlist all
> databases first and ONLY THEN select mine. Logical approach to the process
> of database connection suggests NOT to enlist all databases (I really need
> only one!), but configure server (client?) that way that upon connecting to
> server it automatically defaults to my DB.
>
Enterprise manager lists only what is there, 500 databases on a single
instance is an unusually high number of databases. Every database on that
instance will be using the same system databases, therefore potentially
causing significant blocking of other users.
You should consider asking your ISP to have multiple instances with fewer
databases rather than getting MS to change their product for something that
should not be common practice.

> My second question is:
> Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
> not to enlist all databases upon connecting to server, but switch directly
> to mine by default?
No, and at this point in the life cycle of SQL 2000 it is (IMO) unlikely to
happen but you could log a feature request at
https://connect.microsoft.com/SQLServer

> Thank you in advance.
> Vsevolod.
John

Open default DB in Enterprise Manager upon connecting to server (SQL2000)

Hello,
1. My hosting provider hosts ~500 databases on the server where my DB
resides. To open my database I use SQL Server 2000 Enterprise Manager.
While server connection timeout is short enough (about 1-2 seconds), when I
click "Databases" folder in server hierarchy, trying to enlist all
databases, it takes and extremely long period of time to set up a list.
Moreover - 60% of attempts usually fail. - Enterprise Manager hangs deadly
and its process could be terminated only by Task Manager.
My first question is: why does EM take such a timeout to display a list of
databases and why in most cases it halts?
2. To open my database and to start working with it I each time have to
perform the above steps. However I find it rather silly - to enlist all
databases first and ONLY THEN select mine. Logical approach to the process
of database connection suggests NOT to enlist all databases (I really need
only one!), but configure server (client?) that way that upon connecting to
server it automatically defaults to my DB.
My second question is:
Has SQL Server 2000 Enterprise Manager such a feature? Can I configure it
not to enlist all databases upon connecting to server, but switch directly
to mine by default?
Thank you in advance.
Vsevolod.Thank you so much for your help!
"Vsevolod Ukrainsky" <vsevolod@.ukrainsky.ru> wrote in message
news:eobGc2ALHHA.780@.TK2MSFTNGP03.phx.gbl...
> Hello,
<SKIPPED>