Help!! Need to get the MSDASQL provider in SQL 2005 EE 64 bit

I understand that the MSDASQL.dll is not available in 64 bit (unless you are running 2008 beta 3). I also understand that you can setup 32 bit ODBC without issues (using ODBCAD32.exe in WOW directory). My issue is how does SQL 2005 64 bit see these ODBC links? What is everyone doing to get around this? I want to be able to use openquery. Any advice would be much appreciated.

Carlton

I have also tried using the IBMDADB2 provider but im getting an interesting message of access denied.

sp_addlinkedserver @.server = N'name', @.srvproduct=N'sourcename', @.provider=N'IBMDADB2', @.provstr=N'DSN=sourcename'

Error using a select statement

SELECT *

FROM OPENQUERY (Name,'SELECT * FROM Database.table')

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDADB2" for linked server "sourcename" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "IBMDADB2" for linked server "sourcename".

Help!! Need to get the MSDASQL provider in SQL 2005 EE 64 bit

I understand that the MSDASQL.dll is not available in 64 bit (unless you are running 2008 beta 3). I also understand that you can setup 32 bit ODBC without issues (using ODBCAD32.exe in WOW directory). My issue is how does SQL 2005 64 bit see these ODBC links? What is everyone doing to get around this? I want to be able to use openquery. Any advice would be much appreciated.

Carlton

I have also tried using the IBMDADB2 provider but im getting an interesting message of access denied.

sp_addlinkedserver @.server = N'name', @.srvproduct=N'sourcename', @.provider=N'IBMDADB2', @.provstr=N'DSN=sourcename'

Error using a select statement

SELECT *

FROM OPENQUERY (Name,'SELECT * FROM Database.table')

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "IBMDADB2" for linked server "sourcename" reported an error. Access denied.

Msg 7350, Level 16, State 2, Line 1

Cannot get the column information from OLE DB provider "IBMDADB2" for linked server "sourcename".

Help! What is the risk for data on insufficient hardware?

I am sertainly no SQL expert so I am looking for a bit of help
(actually quite desperate).

The extra CPU and 1GB of RAM to be added to a server (Compaq DL380 G2:
1.4 GHz PIII with a Gig of RAM) have not come in and we are scheduled
to go live tomorrow with a MS SQL Server web app. Will potentially
insufficient hardware resources endanger the data of other apps
running on the SQL Server or will the system simply be slow for
everyone?

Basically, I need to know whether SQl Server bogs down gracefully
(under 2x-3x an acceptable load) or will we be corrupting the database
with a crash.

Thanks a million,
Bertalbertcraig@.hotmail.com (Bert) wrote in message news:<898d9fd7.0402101736.4b5c3e54@.posting.google.com>...
> I am sertainly no SQL expert so I am looking for a bit of help
> (actually quite desperate).
> The extra CPU and 1GB of RAM to be added to a server (Compaq DL380 G2:
> 1.4 GHz PIII with a Gig of RAM) have not come in and we are scheduled
> to go live tomorrow with a MS SQL Server web app. Will potentially
> insufficient hardware resources endanger the data of other apps
> running on the SQL Server or will the system simply be slow for
> everyone?
> Basically, I need to know whether SQl Server bogs down gracefully
> (under 2x-3x an acceptable load) or will we be corrupting the database
> with a crash.
> Thanks a million,
> Bert

If you overload the system, then things should just get a lot slower.
Even if there is a crash, MSSQL will protect data integrity by rolling
back/forward any uncommitted transactions.

You should also be aware that SQL Server takes as much memory as
possible (by design), but will release it to other applications if
needed. Whether or not that happens fast enough to avoid paging, or
timeouts from other applications isn't really possible to say without
testing.

Simon|||Bert (albertcraig@.hotmail.com) writes:
> The extra CPU and 1GB of RAM to be added to a server (Compaq DL380 G2:
> 1.4 GHz PIII with a Gig of RAM) have not come in and we are scheduled
> to go live tomorrow with a MS SQL Server web app. Will potentially
> insufficient hardware resources endanger the data of other apps
> running on the SQL Server or will the system simply be slow for
> everyone?
> Basically, I need to know whether SQl Server bogs down gracefully
> (under 2x-3x an acceptable load) or will we be corrupting the database
> with a crash.

To add to Simon's response: if you are running more applications on that
machine, you may want to configure SQL Server to not use more than a
certain amount of memory (but don't forget to change the setting when
that extra gig comes in!). SQL Server does yield when other applications
needs memory, but it does not always yield fast enough.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns948CEF61FD7A1Yazorman@.127.0.0.1>...
> Bert (albertcraig@.hotmail.com) writes:
> > The extra CPU and 1GB of RAM to be added to a server (Compaq DL380 G2:
> > 1.4 GHz PIII with a Gig of RAM) have not come in and we are scheduled
> > to go live tomorrow with a MS SQL Server web app. Will potentially
> > insufficient hardware resources endanger the data of other apps
> > running on the SQL Server or will the system simply be slow for
> > everyone?
> > Basically, I need to know whether SQl Server bogs down gracefully
> > (under 2x-3x an acceptable load) or will we be corrupting the database
> > with a crash.
> To add to Simon's response: if you are running more applications on that
> machine, you may want to configure SQL Server to not use more than a
> certain amount of memory (but don't forget to change the setting when
> that extra gig comes in!). SQL Server does yield when other applications
> needs memory, but it does not always yield fast enough.

Thanks gentlemen! In the end we postponed the rollout by two days and
twisted some arms to get some RAM. So, fortunately, we didn't get to
see if the server bogged down. I will, however, have someone look into
controlling the amount of RAM that SQL Server grabs initially.

Thanks,
Bertsql

Help! SQL Collation

Hello All
I'm a bit confused about collation settings so needed some info. on
it. My database server is currently using SortOrder as

Latin1-General, case-insensitive, accent-sensitive,
kanatype-insensitive, width-insensitive for Unicode Data, SQL Server
Sort Order 52 on Code Page 1252 for non-Unicode Data

I have a table:
CREATE TABLE [PD_RUSS3].[pdtable_185]
(
[iso_area_indx_no] [int] NULL ,
[lineid_1] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[iso_type] [smallint] NULL
) ON [PRIMARY]

I have a nonclustered, UNIQUE index located on PRIMARY on
iso_area_indx_no, lineid_1, iso_type

However I'm able to load a file with these entries:
45 '16-XX-WCS' 1
45 '16-xx-WCS' 1

I thought that this would fail because of the above settings. Please
enlighten me on what I'm missing here..??

thanks
Sunit
sunitjoshi@.netzero.netSunit Joshi (sjoshi@.ingr.com) writes:
> I have a table:
> CREATE TABLE [PD_RUSS3].[pdtable_185]
> (
> [iso_area_indx_no] [int] NULL ,
> [lineid_1] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
> [iso_type] [smallint] NULL
> ) ON [PRIMARY]
> I have a nonclustered, UNIQUE index located on PRIMARY on
> iso_area_indx_no, lineid_1, iso_type
> However I'm able to load a file with these entries:
> 45 '16-XX-WCS' 1
> 45 '16-xx-WCS' 1
> I thought that this would fail because of the above settings. Please
> enlighten me on what I'm missing here..??

You have a case-insensitive collation, that is xx, xX, Xx and XX are all
different. Thus, the two entries above should definitely both be loaded.

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

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

HELP! Linked Server Problem

I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005 instanc
e
on a 64bit Win 2003 Server. Both systems are using the same user id &
password.
Both instances use windows authentication mode.
In the SQL Server Enterprise Manager on the SQL 2005 instance I created a
linked server to the SQL 2000 instance. I used the login as the current logi
n
context.
From the server itself, I can execute queries from the linked server.
My problems lies when I use the SQL Server Enterprise Manager in my computer
(I am in the admin group on both machines and I have sysadmin roles on both)
when executing the same query I get:
OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I dont understand this as I'm using Windows Autherntication on everything.Have you tried to use Named Pipes on your client network configuration?
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:BB430B53-5AA8-47FA-B8EB-215ABF91BB49@.microsoft.com...
>I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005
>instance
> on a 64bit Win 2003 Server. Both systems are using the same user id &
> password.
> Both instances use windows authentication mode.
> In the SQL Server Enterprise Manager on the SQL 2005 instance I created a
> linked server to the SQL 2000 instance. I used the login as the current
> login
> context.
> From the server itself, I can execute queries from the linked server.
> My problems lies when I use the SQL Server Enterprise Manager in my
> computer
> (I am in the admin group on both machines and I have sysadmin roles on
> both)
> when executing the same query I get:
> OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18456, Level 14, State 1, Line 0
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> I dont understand this as I'm using Windows Autherntication on everything.|||Tried Pipes, but not the issue.
For some reason, the windows authentication mode is not working properly.
"Immy" wrote:

> Have you tried to use Named Pipes on your client network configuration?
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:BB430B53-5AA8-47FA-B8EB-215ABF91BB49@.microsoft.com...
>
>|||Howard Carr wrote:[vbcol=seagreen]
> Tried Pipes, but not the issue.
> For some reason, the windows authentication mode is not working properly.
> "Immy" wrote:
>
You might take a look at Security Account Delegation in Books On Line.
In order to use Windows authentication you must enable the servers for
Account Delegation, and also the SQL server usr account must be enabled
for delegation. I think the problem is that when you try from your own
machine, you use an account and computer that isn't trusted.
Regards
Steen|||I believe you are experincing the two-hop problem? You can't pass Windows
authentication credentials across a link in SQL Server. If you define a
static SQL ID for the link and use that, you should be fine
"Howard Carr" wrote:

> I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005 insta
nce
> on a 64bit Win 2003 Server. Both systems are using the same user id &
> password.
> Both instances use windows authentication mode.
> In the SQL Server Enterprise Manager on the SQL 2005 instance I created a
> linked server to the SQL 2000 instance. I used the login as the current lo
gin
> context.
> From the server itself, I can execute queries from the linked server.
> My problems lies when I use the SQL Server Enterprise Manager in my comput
er
> (I am in the admin group on both machines and I have sysadmin roles on bot
h)
> when executing the same query I get:
> OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote hos
t.
> Msg 18456, Level 14, State 1, Line 0
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> I dont understand this as I'm using Windows Autherntication on everything.

HELP! Linked Server Problem

I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005 instance
on a 64bit Win 2003 Server. Both systems are using the same user id &
password.
Both instances use windows authentication mode.
In the SQL Server Enterprise Manager on the SQL 2005 instance I created a
linked server to the SQL 2000 instance. I used the login as the current login
context.
From the server itself, I can execute queries from the linked server.
My problems lies when I use the SQL Server Enterprise Manager in my computer
(I am in the admin group on both machines and I have sysadmin roles on both)
when executing the same query I get:
OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I dont understand this as I'm using Windows Autherntication on everything.
Have you tried to use Named Pipes on your client network configuration?
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:BB430B53-5AA8-47FA-B8EB-215ABF91BB49@.microsoft.com...
>I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005
>instance
> on a 64bit Win 2003 Server. Both systems are using the same user id &
> password.
> Both instances use windows authentication mode.
> In the SQL Server Enterprise Manager on the SQL 2005 instance I created a
> linked server to the SQL 2000 instance. I used the login as the current
> login
> context.
> From the server itself, I can execute queries from the linked server.
> My problems lies when I use the SQL Server Enterprise Manager in my
> computer
> (I am in the admin group on both machines and I have sysadmin roles on
> both)
> when executing the same query I get:
> OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18456, Level 14, State 1, Line 0
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> I dont understand this as I'm using Windows Autherntication on everything.
|||Tried Pipes, but not the issue.
For some reason, the windows authentication mode is not working properly.
"Immy" wrote:

> Have you tried to use Named Pipes on your client network configuration?
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:BB430B53-5AA8-47FA-B8EB-215ABF91BB49@.microsoft.com...
>
>
|||Howard Carr wrote:[vbcol=seagreen]
> Tried Pipes, but not the issue.
> For some reason, the windows authentication mode is not working properly.
> "Immy" wrote:
You might take a look at Security Account Delegation in Books On Line.
In order to use Windows authentication you must enable the servers for
Account Delegation, and also the SQL server usr account must be enabled
for delegation. I think the problem is that when you try from your own
machine, you use an account and computer that isn't trusted.
Regards
Steen
|||I believe you are experincing the two-hop problem? You can't pass Windows
authentication credentials across a link in SQL Server. If you define a
static SQL ID for the link and use that, you should be fine
"Howard Carr" wrote:

> I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005 instance
> on a 64bit Win 2003 Server. Both systems are using the same user id &
> password.
> Both instances use windows authentication mode.
> In the SQL Server Enterprise Manager on the SQL 2005 instance I created a
> linked server to the SQL 2000 instance. I used the login as the current login
> context.
> From the server itself, I can execute queries from the linked server.
> My problems lies when I use the SQL Server Enterprise Manager in my computer
> (I am in the admin group on both machines and I have sysadmin roles on both)
> when executing the same query I get:
> OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18456, Level 14, State 1, Line 0
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> I dont understand this as I'm using Windows Autherntication on everything.

HELP! Linked Server Problem

I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005 instance
on a 64bit Win 2003 Server. Both systems are using the same user id &
password.
Both instances use windows authentication mode.
In the SQL Server Enterprise Manager on the SQL 2005 instance I created a
linked server to the SQL 2000 instance. I used the login as the current login
context.
From the server itself, I can execute queries from the linked server.
My problems lies when I use the SQL Server Enterprise Manager in my computer
(I am in the admin group on both machines and I have sysadmin roles on both)
when executing the same query I get:
OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
"Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18456, Level 14, State 1, Line 0
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
I dont understand this as I'm using Windows Autherntication on everything.Have you tried to use Named Pipes on your client network configuration?
"Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
news:BB430B53-5AA8-47FA-B8EB-215ABF91BB49@.microsoft.com...
>I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005
>instance
> on a 64bit Win 2003 Server. Both systems are using the same user id &
> password.
> Both instances use windows authentication mode.
> In the SQL Server Enterprise Manager on the SQL 2005 instance I created a
> linked server to the SQL 2000 instance. I used the login as the current
> login
> context.
> From the server itself, I can execute queries from the linked server.
> My problems lies when I use the SQL Server Enterprise Manager in my
> computer
> (I am in the admin group on both machines and I have sysadmin roles on
> both)
> when executing the same query I get:
> OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote
> host.
> Msg 18456, Level 14, State 1, Line 0
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> I dont understand this as I'm using Windows Autherntication on everything.|||Tried Pipes, but not the issue.
For some reason, the windows authentication mode is not working properly.
"Immy" wrote:
> Have you tried to use Named Pipes on your client network configuration?
> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
> news:BB430B53-5AA8-47FA-B8EB-215ABF91BB49@.microsoft.com...
> >I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005
> >instance
> > on a 64bit Win 2003 Server. Both systems are using the same user id &
> > password.
> > Both instances use windows authentication mode.
> >
> > In the SQL Server Enterprise Manager on the SQL 2005 instance I created a
> > linked server to the SQL 2000 instance. I used the login as the current
> > login
> > context.
> > From the server itself, I can execute queries from the linked server.
> >
> > My problems lies when I use the SQL Server Enterprise Manager in my
> > computer
> > (I am in the admin group on both machines and I have sysadmin roles on
> > both)
> > when executing the same query I get:
> > OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
> > "Communication link failure".
> > Msg 10054, Level 16, State 1, Line 0
> > TCP Provider: An existing connection was forcibly closed by the remote
> > host.
> > Msg 18456, Level 14, State 1, Line 0
> > Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> >
> > I dont understand this as I'm using Windows Autherntication on everything.
>
>|||Howard Carr wrote:
> Tried Pipes, but not the issue.
> For some reason, the windows authentication mode is not working properly.
> "Immy" wrote:
>> Have you tried to use Named Pipes on your client network configuration?
>> "Howard Carr" <HowardCarr@.discussions.microsoft.com> wrote in message
>> news:BB430B53-5AA8-47FA-B8EB-215ABF91BB49@.microsoft.com...
>> I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005
>> instance
>> on a 64bit Win 2003 Server. Both systems are using the same user id &
>> password.
>> Both instances use windows authentication mode.
>> In the SQL Server Enterprise Manager on the SQL 2005 instance I created a
>> linked server to the SQL 2000 instance. I used the login as the current
>> login
>> context.
>> From the server itself, I can execute queries from the linked server.
>> My problems lies when I use the SQL Server Enterprise Manager in my
>> computer
>> (I am in the admin group on both machines and I have sysadmin roles on
>> both)
>> when executing the same query I get:
>> OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
>> "Communication link failure".
>> Msg 10054, Level 16, State 1, Line 0
>> TCP Provider: An existing connection was forcibly closed by the remote
>> host.
>> Msg 18456, Level 14, State 1, Line 0
>> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
>> I dont understand this as I'm using Windows Autherntication on everything.
>>
You might take a look at Security Account Delegation in Books On Line.
In order to use Windows authentication you must enable the servers for
Account Delegation, and also the SQL server usr account must be enabled
for delegation. I think the problem is that when you try from your own
machine, you use an account and computer that isn't trusted.
Regards
Steen|||I believe you are experincing the two-hop problem? You can't pass Windows
authentication credentials across a link in SQL Server. If you define a
static SQL ID for the link and use that, you should be fine
"Howard Carr" wrote:
> I have a SQL 2000 instance on a 32 bit Win2000 server and a SQL 2005 instance
> on a 64bit Win 2003 Server. Both systems are using the same user id &
> password.
> Both instances use windows authentication mode.
> In the SQL Server Enterprise Manager on the SQL 2005 instance I created a
> linked server to the SQL 2000 instance. I used the login as the current login
> context.
> From the server itself, I can execute queries from the linked server.
> My problems lies when I use the SQL Server Enterprise Manager in my computer
> (I am in the admin group on both machines and I have sysadmin roles on both)
> when executing the same query I get:
> OLE DB provider "SQLNCLI" for linked server "SQL2000" returned message
> "Communication link failure".
> Msg 10054, Level 16, State 1, Line 0
> TCP Provider: An existing connection was forcibly closed by the remote host.
> Msg 18456, Level 14, State 1, Line 0
> Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
> I dont understand this as I'm using Windows Autherntication on everything.