ODBC Isolation level: February 2012

Saturday, February 25, 2012

ODBC SQL Driver Timeout Expired from MS Access

Hi

I'm running a machine with windows xp pro sp2. I have a MS Access 2000 database on that machine. The database contains 1 odbc linked table
and 1 Access query.

The odbc linked table is connected to a MS SQL Server 2000 database with a ODBC SQL Server System DSN.

The table on SQL Server to which the table in Access is linked has about
3 million + rows.

When I open the ODBC linked table in Access, it opens without a problem.

However, if I use even so much as one criterion such as "where company = ABC" for example, then it "thinks" for a while and returns the dreaded:
[Microsoft][ODBC SQL Driver] Timeout Expired (#0).

I have to use MS Access 2000 as the front end for this SQL Database table.

How do I get around the timeout issue. Is there a setting in SQL?

Please keep in mind that
I can't write the queries as Store procs because the requirement of the client is that there be one Linked Access table that the users can use
to write various access queries. No user are not techies.

please helpread this post:
http://www.mcse.ms/message1582144.html

I don't test.
Tell me.
Bye.

ODBC SQL 2005 and Named Instance - won't connect

We have two instances of SQL 2005 installed, with static TCP/IP ports, one
instance on one IP address, port 1433 and the other instance on a different
IP address, port 1433.
Try to create an ODBC System DSN using SQL Native Client and it won't
connect.
If we do the same thing but using a default instance of SQL 2005, ODBC
connects fine.
Any help anyone can give as to why ODBC won't connect to the SQL 2005 names
instances?
I've tried searching thru MSDN and TechNet and such but found nothing of any
help.
Michael MacGregor
Database Architect
Never mind, got it to work, having taken a sledgehammer to the server.
Disabled all IP addresses other than the one specified for the instance,
then restarted the whole box, and hey presto it finally worked.
Now we just have to get LDAP to recognise the named instances. Yeah I know,
old technology and all that stuff.
MTM
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:emWQtlFGGHA.3728@.tk2msftngp13.phx.gbl...
> We have two instances of SQL 2005 installed, with static TCP/IP ports, one
> instance on one IP address, port 1433 and the other instance on a
> different IP address, port 1433.
> Try to create an ODBC System DSN using SQL Native Client and it won't
> connect.
> If we do the same thing but using a default instance of SQL 2005, ODBC
> connects fine.
> Any help anyone can give as to why ODBC won't connect to the SQL 2005
> names instances?
> I've tried searching thru MSDN and TechNet and such but found nothing of
> any help.
> Michael MacGregor
> Database Architect
>

ODBC SQL 2005 and Named Instance - won't connect

We have two instances of SQL 2005 installed, with static TCP/IP ports, one
instance on one IP address, port 1433 and the other instance on a different
IP address, port 1433.
Try to create an ODBC System DSN using SQL Native Client and it won't
connect.
If we do the same thing but using a default instance of SQL 2005, ODBC
connects fine.
Any help anyone can give as to why ODBC won't connect to the SQL 2005 names
instances?
I've tried searching thru MSDN and technet and such but found nothing of any
help.
Michael MacGregor
Database ArchitectNever mind, got it to work, having taken a sledgehammer to the server.
Disabled all IP addresses other than the one specified for the instance,
then restarted the whole box, and hey presto it finally worked.
Now we just have to get LDAP to recognise the named instances. Yeah I know,
old technology and all that stuff.
MTM
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:emWQtlFGGHA.3728@.tk2msftngp13.phx.gbl...
> We have two instances of SQL 2005 installed, with static TCP/IP ports, one
> instance on one IP address, port 1433 and the other instance on a
> different IP address, port 1433.
> Try to create an ODBC System DSN using SQL Native Client and it won't
> connect.
> If we do the same thing but using a default instance of SQL 2005, ODBC
> connects fine.
> Any help anyone can give as to why ODBC won't connect to the SQL 2005
> names instances?
> I've tried searching thru MSDN and technet and such but found nothing of
> any help.
> Michael MacGregor
> Database Architect
>

ODBC SPROCS and unnamed parameters

I have a stored procedure outlined below that I access on SQL Server
6.5 via ODBC.
CREATE PROCEDURE usp_SQLREPORTING_TEST @.userid VARCHAR(6) AS
SELECT * INTO #TEMP_CM FROM tblUser WHERE userID LIKE @.userid
SELECT * FROM #TEMP_CM
When I add the data set to SQL reporting services I cannot opt for
command type "Stored procedure" on an ODBC connection , thus I choose
text and execute the dataset with thye following command using a
unnamed parameter.
exec usp_SQLREPORTING_TEST ?
Data is correctly returned when i run the query, BUT I receive no
fields in the data set thus the report never shows anything. A "build"
reveals an error message stating that "the filed in missing from the
returned resultset"
Help!!!!Try clicking the "Refresh Fields" button in Data tab in designer.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ken Shabby" <paul.scott@.klm.com> wrote in message
news:872f3c38.0410060425.3de9c4ce@.posting.google.com...
> I have a stored procedure outlined below that I access on SQL Server
> 6.5 via ODBC.
>
> CREATE PROCEDURE usp_SQLREPORTING_TEST @.userid VARCHAR(6) AS
> SELECT * INTO #TEMP_CM FROM tblUser WHERE userID LIKE @.userid
> SELECT * FROM #TEMP_CM
>
> When I add the data set to SQL reporting services I cannot opt for
> command type "Stored procedure" on an ODBC connection , thus I choose
> text and execute the dataset with thye following command using a
> unnamed parameter.
> exec usp_SQLREPORTING_TEST ?
> Data is correctly returned when i run the query, BUT I receive no
> fields in the data set thus the report never shows anything. A "build"
> reveals an error message stating that "the filed in missing from the
> returned resultset"
> Help!!!!

ODBC source error message on Vista 64

I tried to create an ODBS syste data source but I get this error:

Connection Failed:

SQLState:'01000'

SQL Server Error:10061

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]ConnectionOpen(Connect())

Connection Failed:

SQLState:'08001'

SQL Server Error:17

[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]Server does not Excist or access denied.

Thanks

The connection string you are using is either not right are the server is not reachable. For investigating this issue you should answer some questions. Is the server on the same server as the SQL Server ? Which version are you using ? Are you using a named instance ? Are you using the default port or any other manually configured one ? DO you h ave a firewall enabled, if yes, did you configure exceptions for outgoing communication for the designated port mentioned above ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

ODBC Setup

I have downloaded MS SQL Server for Desktop to upsize my
access database to SQL Server database.
The upsizing is successful and when I setup the DSN
connection. It gives the following error :
Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
[Microsoft][ODBC SQL Server Driver][SQL Server]Login
failed for user 'TIMESWEB\IUSR_TIMESWEB'.
I remember the password. But the username is not
recognised by the SQL Server. Can you please let me know
how I can change the Username & password for MS SQL Server
after installing it. I do not know from where I can
change the username & password.
Kindly assist.
Thanks,
Rajesh.VMSDE does not come with the SQL administrator tools (like Enterprise Manager
or Query Analyzer). In order to manage the database, you need to use T-SQL
commands.
The T-SQL commands that should help you are:
If you want to add a SQL login use the following stored procedures:
sp_password:
http://msdn.microsoft.com/library/d.../>
pz_5x44.asp
sp_addlogin:
http://msdn.microsoft.com/library/d... />
a_0q7i.asp
If you want to use an existing Windows username (ie.
'TIMESWEB\IUSR_TIMESWEB'), then use the following stored procedure to give
the username access to the database:
sp_grantlogin:
http://msdn.microsoft.com/library/d... />
a_0q7i.asp
Brannon Jones
Developer - MDAC
This posting is provided "as is" with no warranties and confers no rights.
"Rajesh" <timesweb@.omantel.net.om> wrote in message
news:01a401c3c3a9$87064410$a001280a@.phx.gbl...

quote:

> I have downloaded MS SQL Server for Desktop to upsize my
> access database to SQL Server database.
> The upsizing is successful and when I setup the DSN
> connection. It gives the following error :
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E4D)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Login
> failed for user 'TIMESWEB\IUSR_TIMESWEB'.
> I remember the password. But the username is not
> recognised by the SQL Server. Can you please let me know
> how I can change the Username & password for MS SQL Server
> after installing it. I do not know from where I can
> change the username & password.
> Kindly assist.
> Thanks,
> Rajesh.V

odbc select where parameters

Im enabling an apllication to use ODBC to connect to sqlserver which currently uses Oracle OCI, i have no prior knowledge about odbc use.
Im unsure how to approach where clause parameters (bind parameters)
ie Oracle OCI
select name into :name from emp where name = :a_name

via ODBC, connecting to sql server i'm attempting

select name from emp where name = ?

with,
sqlprepare
sqlbindparameter
sqlexecute
sqlbindcol
sqlfetch

all seems ok with sqlbindparameter, but sqlexecute fails with sqlstate 22001, String data, right truncation.
The question: can i use ? parameter in where conditions, if not whats the best approach.
Many Thanks.resolved. problem was oci doesnt require null terminated char string, odbc does

ODBC Select Data Source in Crystal reports - continuously prompts

I've created an ODBC entry for crystal reports. When I go to refresh and
run a report, it always prompts me with a select data source and SQL server
is at the bottom of the list under dBase, Excel and MS Access Database. If
I select it, the report runs. How do I have it automatically choose that
source so I don't have to select it each time?
Thank you.You should ask this question in a Crystal support newsgroup or site.
This one is for SQL Server, so your pickings will be slim.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 26 Dec 2003 13:41:50 -0500, "microsoft"
<advice1one@.hotmail.com> wrote:

quote:

>I've created an ODBC entry for crystal reports. When I go to refresh and
>run a report, it always prompts me with a select data source and SQL server
>is at the bottom of the list under dBase, Excel and MS Access Database. If
>I select it, the report runs. How do I have it automatically choose that
>source so I don't have to select it each time?
>Thank you.
>

|||Thank you, I figured it out, I needed to change the ODBC connector to named
pipes.
"Mary Chipman" <mchip@.nomail.please> wrote in message
news:do51vv0gshsu7e2g9mr6bl0i9sinf4bq4t@.
4ax.com...

quote:

> You should ask this question in a Crystal support newsgroup or site.
> This one is for SQL Server, so your pickings will be slim.
> -- Mary
> MCW Technologies
> http://www.mcwtech.com
> On Fri, 26 Dec 2003 13:41:50 -0500, "microsoft"
> <advice1one@.hotmail.com> wrote:
>
server[QUOTE]
If[QUOTE]
>

ODBC security problem

Hi, I've a Access 2003 program with linked tables to a sqlexpress configured
with mixed mode security.
From clients XP no problem.
From clients 2000 MSACCESS don't get the right configuration from ODBC
source, because try to conect with Windows Authentication Mode security, but
the source is configurated with mixed mode. If I refresh the linked tables
and change the password, works fine, but when I close MSACCESS and open
again, fail.
Anybody knows where's the bug? In ODBC driver? MSACCESS?You could be hitting an MDAC bug where the authentication
mode setting is ignored and windows authentication is always
used. Refer to:
FIX: SQL Server ODBC Driver Ignores Authentication Setting
http://support.microsoft.com/?id=279526
You can use component checker to verify the version and MDAC
installation on any of the clients. You can download
component checker and MDAC versions from this site:
http://msdn.microsoft.com/data/ref/mdac/downloads/
-Sue
On Fri, 18 Aug 2006 11:27:01 -0700, Negri
<Negri@.discussions.microsoft.com> wrote:

>Hi, I've a Access 2003 program with linked tables to a sqlexpress configure
d
>with mixed mode security.
>From clients XP no problem.
>From clients 2000 MSACCESS don't get the right configuration from ODBC
>source, because try to conect with Windows Authentication Mode security, bu
t
>the source is configurated with mixed mode. If I refresh the linked tables
>and change the password, works fine, but when I close MSACCESS and open
>again, fail.
>Anybody knows where's the bug? In ODBC driver? MSACCESS?|||Thanks Sue, I'll try it next monday and tell you.
"Sue Hoegemeier" wrote:

> You could be hitting an MDAC bug where the authentication
> mode setting is ignored and windows authentication is always
> used. Refer to:
> FIX: SQL Server ODBC Driver Ignores Authentication Setting
> http://support.microsoft.com/?id=279526
> You can use component checker to verify the version and MDAC
> installation on any of the clients. You can download
> component checker and MDAC versions from this site:
> http://msdn.microsoft.com/data/ref/mdac/downloads/
> -Sue
> On Fri, 18 Aug 2006 11:27:01 -0700, Negri
> <Negri@.discussions.microsoft.com> wrote:
>
>

ODBC Secruity

I have set up a VB front end and use an ODBC to connect to
an SQL database. Is there a way were i can not allow a
user to simply connect to their ODBC link and Delete the
whole thing using access?If the user has access to the server and a login, there's not much you can
do to stop a particular method of accessing the server. If you application
lived on a web server, you could restrict access to the SQL Server from
only that machine using IPSec policies. But, there isn't a good way to
restrict access via ODBC or dbLibrary api for example.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

ODBC Returning success but actually failing (SQL Native)

I'm talking to SQL 2005 using the SQL Native client in ODBC:

Here's what I'm doing:

1) SQLPrepare () -- Prepair an insert stmt
2) SQLBindParameter() -- Binding the parameter
3) Set the Parameter to a value
4) SQLExecute() -- Execute the statment
5) Set the Parameter to a different value
6) SQLExecute() -- Execute the statment

Both calls to SQLExecute() return SUCCESS_WITH_INFO.

The problem is that only the 1st SQLExecute() ever makes it to the database. The second SQLExecute() never gets executed at the database AND it returns a success condition.

When I check what the info is, it's "[Microsoft][SQL Native Client]Fractional truncation". Not an issue since I'm setting a datetime value in the database and I don't care about the milliseconds.

Here's the problems with this:

1) SQLExecute() return success yet it does not execute the statement. How can I tell if the statement fails if SQLExecute() always return success?

2) Why does a previous success with info cause all future executes to not happen?

So, is there a way to tell odbc to ignore the info and allow the next execute to run?

Thanks for any help,

Scott

Could you send some sample code that demonstrates the problem please?

How do you know the 2nd execute is not making it to the server? How you looked at a profiler trace or are you just examining the database?

|||

I'm use SQL Server profiler to determine the second SQLExecute is never making it to the database.

I've done some more testing on this and here's what I have found:

1) The problem exists with the "SQL Native Client" ODBC driver. The "SQL Server" does not have this problem. Switching to the "SQL Server" driver fixes the problem.

2) The problem seems to exist only when using the SQL

Here's how to reproduce the problem:

Step 1: In SQL 2005, create table with a datetime field. Here's the code I used:
CREATE TABLE [dbo].[TestTbl] ( [TestDate] [datetime] NULL )

Step 2: Create an ODBC connection to this table using the SQL Native Client driver. In my example, I called it "TestNative"

Step 3: Here's the VC++ code that uses ODBC to connect to the database and insert data into the TestTbl table:

-- BEGIN CODE BLOCK
CDatabase db;
db.OpenEx ("DSN=TestNative;APP=Test App;WSID=SCOTT1;DATABASE=Test2;Trusted_Connection=Yes", CDatabase::noOdbcDialog);

HSTMT hstmt;
::SQLAllocHandle (SQL_HANDLE_STMT, db.m_hdbc, &hstmt);

LPCTSTR strSQL = ;
::SQLPrepare (hstmt, (SQLCHAR *) "INSERT INTO TestTbl (TestDate) VALUES (?)", SQL_NTS);

SQL_TIMESTAMP_STRUCT sqlTimestamp;
SQLLEN Ind = sizeof (sqlTimestamp);
::SQLBindParameter (hstmt, 1, SQL_PARAM_INPUT, SQL_C_TIMESTAMP, SQL_TIMESTAMP, 0, 4, &sqlTimestamp, Ind, &Ind);

sqlTimestamp.year = 2006;
sqlTimestamp.month = 6;
sqlTimestamp.day = 1;
sqlTimestamp.hour = 1;
sqlTimestamp.minute = 1;
sqlTimestamp.second = 1;
sqlTimestamp.fraction = 0;
::SQLExecute (hstmt);

sqlTimestamp.day = 2;
::SQLExecute (hstmt);

::SQLFreeHandle (SQL_HANDLE_STMT, &hstmt);
-- END CODE BLOCK

Using the ODBC "SQL Server" driver, this code inserts 2 records into TestTbl.
Using the ODBC "SQL Native Client" drivre, only 1 record gets inserted.

Running SQL Server profiler to monitor what happens in the database, when the first SQLExecute() executes, a "exec sp_prepexec ..." statement is run on server. This happens with both ODBC drivers.
The problem is that with the second SQLExecute() nothing happens in the database if the "SQL Native Client" driver is used. If the "SQL Server" driver is used, "exec sp_execute 1,''2006-06-02 01:01:00:000''" is run at the server.

|||

I couldn't get the sample code to run as supplied because of an error in SQLBindParameter. You need ColumnSize and DecimalDigits set to 19, 0 or 23, 3. (See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbccolumn_size.asp for details)

Once I'd fixed the problem with SQLBindParameter I got two rows inserted with both SQL Server and SQL Native Client drivers.

I ran my repro as an ODBC v3 app.

If I ran the app as an ODBC v2 app with your settings for SQLBindParameter then I managed to reproduce your problem, but if I ran with 19,0 or 23,3 I got two rows inserted.

So, if you run as an ODBC v3 app (via SQLSetEnvAttr call before connecting) you'll get better diagnostics from SQLBindParameter. Even if not, if you provide correct values for precision and scale on SQLBindParameter the problem will go away.

We'll investigate why the two drivers behave differently.

ODBC Redux

I asked the following a few days ago. Am I asking the wrong group?
I am attempting to put a couple of Oracle 8i views in a report project I am
building. When I attempt to build the connection I have two drivers to
choose from one is the Microsoft OLE DB provider for Oracle and the other is
"Oracle Provider for OLE DB". When I use the first I do not get all of the
views and when I get the second I get an error saying that the service name
cannot be resolved. I have created a regular DSN and looked at the data in
Access so I know the views I want are there but I cannot seem to make it
happen in Reporting services. Any Ideas?
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor CompanySometimes when you ask a question there isn't anyone who knows the solution.
I don't know the solution but I do have some ideas on what you can try.
First, do not pick the Microsoft OLE DB Provider for Oracle. If you do it
will be trying to use OLEDB from the query designer and managed dotnet
provider when you run it. The dotnet provider requires the 9i client (not
sure if a 9i client can go against 8i). If you pick the Microsoft one it
might work from the designer but it would most likely not run when you
preview the report.
A DSN has nothing to do with OLEDB, that is an ODBC thing. So, as far as the
Oracle OLEDB provider you are missing a step in configuring, i.e. you have
not setup the service name (which is what the error says). I haven't worked
for awhile with Oracle so I can't give you the exact steps. Google OLEDB and
Oracle.
My suggestion is to go with the oledb provider for odbc because then you can
pick the DSN that you setup previously.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> I asked the following a few days ago. Am I asking the wrong group?
> I am attempting to put a couple of Oracle 8i views in a report project I
am
> building. When I attempt to build the connection I have two drivers to
> choose from one is the Microsoft OLE DB provider for Oracle and the other
is
> "Oracle Provider for OLE DB". When I use the first I do not get all of the
> views and when I get the second I get an error saying that the service
name
> cannot be resolved. I have created a regular DSN and looked at the data in
> Access so I know the views I want are there but I cannot seem to make it
> happen in Reporting services. Any Ideas?
>
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
>|||Oracle behaves differently when you use the QBE layout and the text layout.
More precisely, two different mechanisms are used to get to the database. I
think you're supposed to use the text layout for entering your query into
the datasource.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
>I asked the following a few days ago. Am I asking the wrong group?
> I am attempting to put a couple of Oracle 8i views in a report project I
> am
> building. When I attempt to build the connection I have two drivers to
> choose from one is the Microsoft OLE DB provider for Oracle and the other
> is
> "Oracle Provider for OLE DB". When I use the first I do not get all of the
> views and when I get the second I get an error saying that the service
> name
> cannot be resolved. I have created a regular DSN and looked at the data in
> Access so I know the views I want are there but I cannot seem to make it
> happen in Reporting services. Any Ideas?
>
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
>|||Sorry if I sounded testy. I plead innocent due to the limitations of text.
There are a lot of messages that get posed here and I assumed that mine
either got lost in the mass or it was considered off topic hence the first
sentence of the message.
Now, that being said I am running the Oracle 9i client and when I try to use
the Microsoft OLE DB driver for ODBC I get the same error that the service
name cannot be resolved. I can connect any other tool using the ODBC DSN
except for the MS tool.
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor Company
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OasNm5O%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> Sometimes when you ask a question there isn't anyone who knows the
solution.
> I don't know the solution but I do have some ideas on what you can try.
> First, do not pick the Microsoft OLE DB Provider for Oracle. If you do it
> will be trying to use OLEDB from the query designer and managed dotnet
> provider when you run it. The dotnet provider requires the 9i client (not
> sure if a 9i client can go against 8i). If you pick the Microsoft one it
> might work from the designer but it would most likely not run when you
> preview the report.
> A DSN has nothing to do with OLEDB, that is an ODBC thing. So, as far as
the
> Oracle OLEDB provider you are missing a step in configuring, i.e. you have
> not setup the service name (which is what the error says). I haven't
worked
> for awhile with Oracle so I can't give you the exact steps. Google OLEDB
and
> Oracle.
> My suggestion is to go with the oledb provider for odbc because then you
can
> pick the DSN that you setup previously.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > I asked the following a few days ago. Am I asking the wrong group?
> >
> > I am attempting to put a couple of Oracle 8i views in a report project I
> am
> > building. When I attempt to build the connection I have two drivers to
> > choose from one is the Microsoft OLE DB provider for Oracle and the
other
> is
> > "Oracle Provider for OLE DB". When I use the first I do not get all of
the
> > views and when I get the second I get an error saying that the service
> name
> > cannot be resolved. I have created a regular DSN and looked at the data
in
> > Access so I know the views I want are there but I cannot seem to make it
> > happen in Reporting services. Any Ideas?
> >
> >
> > --
> > Andrew C. Madsen
> > Network Specialist
> > Harley-Davidson Motor Company
> >
> >
>|||Hmm, there should not be any difference between Access (which you says works
with the DSN you have created) and using MS OLEDB Driver for ODBC. Is this a
system DSN or a user DSN. I used ODBC against Sybase and I see no
difference between using the DSN with Access (which I have done) and using
it in RS.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:%23rFT7wX%23EHA.2580@.TK2MSFTNGP15.phx.gbl...
> Sorry if I sounded testy. I plead innocent due to the limitations of text.
> There are a lot of messages that get posed here and I assumed that mine
> either got lost in the mass or it was considered off topic hence the first
> sentence of the message.
> Now, that being said I am running the Oracle 9i client and when I try to
use
> the Microsoft OLE DB driver for ODBC I get the same error that the service
> name cannot be resolved. I can connect any other tool using the ODBC DSN
> except for the MS tool.
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OasNm5O%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > Sometimes when you ask a question there isn't anyone who knows the
> solution.
> > I don't know the solution but I do have some ideas on what you can try.
> > First, do not pick the Microsoft OLE DB Provider for Oracle. If you do
it
> > will be trying to use OLEDB from the query designer and managed dotnet
> > provider when you run it. The dotnet provider requires the 9i client
(not
> > sure if a 9i client can go against 8i). If you pick the Microsoft one it
> > might work from the designer but it would most likely not run when you
> > preview the report.
> >
> > A DSN has nothing to do with OLEDB, that is an ODBC thing. So, as far as
> the
> > Oracle OLEDB provider you are missing a step in configuring, i.e. you
have
> > not setup the service name (which is what the error says). I haven't
> worked
> > for awhile with Oracle so I can't give you the exact steps. Google OLEDB
> and
> > Oracle.
> >
> > My suggestion is to go with the oledb provider for odbc because then you
> can
> > pick the DSN that you setup previously.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > > I asked the following a few days ago. Am I asking the wrong group?
> > >
> > > I am attempting to put a couple of Oracle 8i views in a report project
I
> > am
> > > building. When I attempt to build the connection I have two drivers to
> > > choose from one is the Microsoft OLE DB provider for Oracle and the
> other
> > is
> > > "Oracle Provider for OLE DB". When I use the first I do not get all of
> the
> > > views and when I get the second I get an error saying that the service
> > name
> > > cannot be resolved. I have created a regular DSN and looked at the
data
> in
> > > Access so I know the views I want are there but I cannot seem to make
it
> > > happen in Reporting services. Any Ideas?
> > >
> > >
> > > --
> > > Andrew C. Madsen
> > > Network Specialist
> > > Harley-Davidson Motor Company
> > >
> > >
> >
> >
>|||Ok this suggestion worked (in a kluge kind of way) even though I could not
see the view in the picker list if I entered the name manually it then
appeared. Thanks for the help.
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor Company
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> Oracle behaves differently when you use the QBE layout and the text
layout.
> More precisely, two different mechanisms are used to get to the database.
I
> think you're supposed to use the text layout for entering your query into
> the datasource.
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> >I asked the following a few days ago. Am I asking the wrong group?
> >
> > I am attempting to put a couple of Oracle 8i views in a report project I
> > am
> > building. When I attempt to build the connection I have two drivers to
> > choose from one is the Microsoft OLE DB provider for Oracle and the
other
> > is
> > "Oracle Provider for OLE DB". When I use the first I do not get all of
the
> > views and when I get the second I get an error saying that the service
> > name
> > cannot be resolved. I have created a regular DSN and looked at the data
in
> > Access so I know the views I want are there but I cannot seem to make it
> > happen in Reporting services. Any Ideas?
> >
> >
> > --
> > Andrew C. Madsen
> > Network Specialist
> > Harley-Davidson Motor Company
> >
> >
>|||So did you end up using Oracle OLEDB provider?
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:eunzNeY%23EHA.2552@.TK2MSFTNGP09.phx.gbl...
> Ok this suggestion worked (in a kluge kind of way) even though I could not
> see the view in the picker list if I entered the name manually it then
> appeared. Thanks for the help.
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
> "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > Oracle behaves differently when you use the QBE layout and the text
> layout.
> > More precisely, two different mechanisms are used to get to the
database.
> I
> > think you're supposed to use the text layout for entering your query
into
> > the datasource.
> >
> > --
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > >I asked the following a few days ago. Am I asking the wrong group?
> > >
> > > I am attempting to put a couple of Oracle 8i views in a report project
I
> > > am
> > > building. When I attempt to build the connection I have two drivers to
> > > choose from one is the Microsoft OLE DB provider for Oracle and the
> other
> > > is
> > > "Oracle Provider for OLE DB". When I use the first I do not get all of
> the
> > > views and when I get the second I get an error saying that the service
> > > name
> > > cannot be resolved. I have created a regular DSN and looked at the
data
> in
> > > Access so I know the views I want are there but I cannot seem to make
it
> > > happen in Reporting services. Any Ideas?
> > >
> > >
> > > --
> > > Andrew C. Madsen
> > > Network Specialist
> > > Harley-Davidson Motor Company
> > >
> > >
> >
> >
>|||What I did:
I had created a DSN Called EMC. For the Data Source I used Microsoft OLE DB
connector for ODBC and selected the DSN. This gave me a list of tables and
views in the Table Picker but the views I wanted were not in the list so I
had to not use the picker and enter the SQL manually by using the syntax:
userview.viewname (stsview.ST_HOST_ARRAY in this case). When I entered that
the table picker then showed that table.
--
Andrew C. Madsen
Network Specialist
Harley-Davidson Motor Company
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OdsdGzY%23EHA.3988@.TK2MSFTNGP11.phx.gbl...
> So did you end up using Oracle OLEDB provider?
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> news:eunzNeY%23EHA.2552@.TK2MSFTNGP09.phx.gbl...
> > Ok this suggestion worked (in a kluge kind of way) even though I could
not
> > see the view in the picker list if I entered the name manually it then
> > appeared. Thanks for the help.
> >
> > --
> > Andrew C. Madsen
> > Network Specialist
> > Harley-Davidson Motor Company
> > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > > Oracle behaves differently when you use the QBE layout and the text
> > layout.
> > > More precisely, two different mechanisms are used to get to the
> database.
> > I
> > > think you're supposed to use the text layout for entering your query
> into
> > > the datasource.
> > >
> > > --
> > > Cheers,
> > >
> > > '(' Jeff A. Stucker
> > > \
> > >
> > > Business Intelligence
> > > www.criadvantage.com
> > > ---
> > > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > > >I asked the following a few days ago. Am I asking the wrong group?
> > > >
> > > > I am attempting to put a couple of Oracle 8i views in a report
project
> I
> > > > am
> > > > building. When I attempt to build the connection I have two drivers
to
> > > > choose from one is the Microsoft OLE DB provider for Oracle and the
> > other
> > > > is
> > > > "Oracle Provider for OLE DB". When I use the first I do not get all
of
> > the
> > > > views and when I get the second I get an error saying that the
service
> > > > name
> > > > cannot be resolved. I have created a regular DSN and looked at the
> data
> > in
> > > > Access so I know the views I want are there but I cannot seem to
make
> it
> > > > happen in Reporting services. Any Ideas?
> > > >
> > > >
> > > > --
> > > > Andrew C. Madsen
> > > > Network Specialist
> > > > Harley-Davidson Motor Company
> > > >
> > > >
> > >
> > >
> >
> >
>|||Thanks, I wondered what ended up working for you in case I see someone else
with the problem. I wonder if the views have some special naming to them
that causes them to not show in the list.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:e2wfiXZ%23EHA.1524@.TK2MSFTNGP09.phx.gbl...
> What I did:
> I had created a DSN Called EMC. For the Data Source I used Microsoft OLE
DB
> connector for ODBC and selected the DSN. This gave me a list of tables and
> views in the Table Picker but the views I wanted were not in the list so I
> had to not use the picker and enter the SQL manually by using the syntax:
> userview.viewname (stsview.ST_HOST_ARRAY in this case). When I entered
that
> the table picker then showed that table.
> --
> Andrew C. Madsen
> Network Specialist
> Harley-Davidson Motor Company
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OdsdGzY%23EHA.3988@.TK2MSFTNGP11.phx.gbl...
> > So did you end up using Oracle OLEDB provider?
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > news:eunzNeY%23EHA.2552@.TK2MSFTNGP09.phx.gbl...
> > > Ok this suggestion worked (in a kluge kind of way) even though I could
> not
> > > see the view in the picker list if I entered the name manually it then
> > > appeared. Thanks for the help.
> > >
> > > --
> > > Andrew C. Madsen
> > > Network Specialist
> > > Harley-Davidson Motor Company
> > > "Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
> > > news:u1bOydR%23EHA.2196@.TK2MSFTNGP11.phx.gbl...
> > > > Oracle behaves differently when you use the QBE layout and the text
> > > layout.
> > > > More precisely, two different mechanisms are used to get to the
> > database.
> > > I
> > > > think you're supposed to use the text layout for entering your query
> > into
> > > > the datasource.
> > > >
> > > > --
> > > > Cheers,
> > > >
> > > > '(' Jeff A. Stucker
> > > > \
> > > >
> > > > Business Intelligence
> > > > www.criadvantage.com
> > > > ---
> > > > "Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
> > > > news:%23Xwk2oO%23EHA.3932@.TK2MSFTNGP10.phx.gbl...
> > > > >I asked the following a few days ago. Am I asking the wrong group?
> > > > >
> > > > > I am attempting to put a couple of Oracle 8i views in a report
> project
> > I
> > > > > am
> > > > > building. When I attempt to build the connection I have two
drivers
> to
> > > > > choose from one is the Microsoft OLE DB provider for Oracle and
the
> > > other
> > > > > is
> > > > > "Oracle Provider for OLE DB". When I use the first I do not get
all
> of
> > > the
> > > > > views and when I get the second I get an error saying that the
> service
> > > > > name
> > > > > cannot be resolved. I have created a regular DSN and looked at the
> > data
> > > in
> > > > > Access so I know the views I want are there but I cannot seem to
> make
> > it
> > > > > happen in Reporting services. Any Ideas?
> > > > >
> > > > >
> > > > > --
> > > > > Andrew C. Madsen
> > > > > Network Specialist
> > > > > Harley-Davidson Motor Company
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

ODBC readonly (viewonly) linked tables....

Hi,

I have a prod database (main bread & bread DB) and have a small access
database that sales team is using... I created a login inside SQL 2000
and gave db_read permission and SELECT permissions... and public.
Role of public is EXEC store procs and view some systables...
I linked those tables that I gave permissions through ODBC link into
access db with the user that I created inside SQL as readonly user...
but to my surprise when I ran a insert query from access on SQL
tables... I was able to update and insert data... if my ODBC link is
only for readonly.. why was I able to unpdate & insert data into SQL
table?
I don't want to give write/update/insert permissions for SQL tables to
sales team... or anyone outside SQL 2000 server DB.
Is there any easy way to create viewonly users inside SQL?
I created users like this: security -logins -new login...
select none serverrole.. db access (bread & butter DB)

Thank you,
hj

******
Pardon my English...Hitesh Joshi (hitesh287@.gmail.com) writes:

Quote:

Originally Posted by

Exactly how does the connection information for the ODBC link look like?
(Minus any password of course).

Exactly how did you set up this user as a readonly user?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

ODBC Query ... getting Where Clause

OK ... I am using UPS Worldship that issues an ODBC query to my MS2K
server ... Worldship can query either a table or a view and retreive
shipping info for a supplied orderid.

I need to create a DB table that will track the orderids requested
from Worldship so that I can stop doubleships. That is to set up a
function to allow the info to be sent only once to worldship.

I need to execute a stored procedure to write to a table and enforce
biz logic.

So .. I've created a view that Worldship can execute an ODBC query
against (v_upsPull) ... in which I guess the query issued will be
like: SELECT * FROM v_upsPull WHERE orderid = 123456

The view is:

CREATE VIEW dbo.v_upsPull
AS
SELECT * FROM OPENROWSET ( 'SQLOLEDB', '[db]'; '[user]'; '[password]',
'exec sp_ups_pull')

When the ODBC query calls the view the sp_ups_pull store procedurer is
executed.

However ... I do not have access to the original Where clause in the
ODBC query in the stored procedurer.

Is there a way I can get access to the ODBC Where clause and pass it
into the stored procedurer?

If not is there some other way I can create a DB table and run a
select against it ... based on the Worldship query?wjreichard@.comcast.net wrote:

Quote:

Originally Posted by

OK ... I am using UPS Worldship that issues an ODBC query to my MS2K
server ... Worldship can query either a table or a view and retreive
shipping info for a supplied orderid.
(..)
If not is there some other way I can create a DB table and run a
select against it ... based on the Worldship query?

IMHO you won't get successful this way...

Perhaps SQL Server trace would be a solution?
You could capture all select queries executed against shipping
table/view and insert collected data into a table and then enforce
particular logic. The question is: how fast do you need the information
that certain orderid was retrieved? The only problem with trace-based
solution is that it can be potentially not fast enough.. (you can't read
the most current trace file if the trace is still running).

You may also try to implement some kind of 'select trigger':
http://solidqualitylearning.com/blo.../11/25/214.aspx
--
Best regards,
Marcin Guzowski
http://guzowski.info|||OK ... I think I got something that might work? I will create a unique
SQL login for the Worldship application and then using the above
method posted in my 1st post execute a stored procedurer and then
access the ODBC SQL with code prototyped in the following SP:

CREATE PROCEDURE dbo.sp_ups_pull AS
DECLARE @.spid int
DECLARE @.dbcc_cmd varchar(512)

CREATE TABLE #who (
spid int,
ecid int,
status varchar(255),
loginname varchar(255),
hostname varchar(255),
blk int,
dbname varchar(255),
cmd varchar(2048)
)
INSERT INTO #who EXEC ('sp_who worldshipuser')

SET @.spid = (SELECT spid FROM #who)
SET @.dbcc_cmd = 'DBCC INPUTBUFFER(' +
rtrim(ltrim(convert(char,@.spid))) + ')'

CREATE TABLE #buffer (
EventType varchar(512),
Parameters int,
EventInfo varchar(2048)
)

INSERT INTO #buffer EXEC (@.dbcc_cmd)

SELECT EventInfo FROM #buffer

[Additional biz logic etc.]
GO

------------------------------------------
Which return the ODBC SQL ... which I will parse the orderid from the
WHERE cluase.

So does anyone see major issues using this method? How do Input
Buffers relate to ODBC connections ... I guess I will need to ensure
that there is only ever one row returned from sp_who for my unique DB
users, Any one see other problems ... or a better solution?|||(wjreichard@.comcast.net) writes:

Quote:

Originally Posted by

OK ... I think I got something that might work? I will create a unique
SQL login for the Worldship application and then using the above
method posted in my 1st post execute a stored procedurer and then
access the ODBC SQL with code prototyped in the following SP:
>
CREATE PROCEDURE dbo.sp_ups_pull AS

Don't use sp_ as the first letters in your object names. This prefix
is reserved from system objects.

Quote:

Originally Posted by

Which return the ODBC SQL ... which I will parse the orderid from the
WHERE cluase.
>
So does anyone see major issues using this method? How do Input
Buffers relate to ODBC connections ... I guess I will need to ensure
that there is only ever one row returned from sp_who for my unique DB
users, Any one see other problems ... or a better solution?

A variation which is possibly even uglier, but nevertheless somewhat
more robust. Write a table-valued function. From this function call
xp_cmdshell, to start a new session in OSQL that runs the DBCC INPUTBUFFER
command. The point here is that you can read @.@.spid in the function
and this to the command string to OSQL. So at least that part is nicer.
But on the other hand you must arrange for worldshipuser to have privleges
to run xp_cmdshell, which is not to take lightly.

ODBC Provider for DSN through connection object?

Is it possible to find out Name or description of the ODBC driver using connection object?
I am not setting up the provider in the code. Only thing I set as connection string is the name of DSN. This DSN could point to any database, I need to figure out which databse it is (As in SqlServer or Oracle)! Hopefully I can do this using a connection object.

Can someone please direct me to the property that is can use for this? An exaple would be helpful, any help is appreciated.

--Shilpa

I know through non-managed code you can access this information through SQLGetInfo calls. For example:

SQLGetInfo(..., SQL_DBMS_NAME, ...) --> returns name of driver used in connection string

SQLGetInfo(..., SQL_DRIVER_NAME, ...) --> returns name of driver's DLL

SQLGetInfo(..., SQL_DRIVER_VER, ...) --> returns version of driver

There are many other keywords you can use to determine at runtime many attributes about the driver and the server. There's a more complete listing on http://msdn2.microsoft.com/en-us/library/ms131672.aspx

As an alternatve, and if you have access, you could browse your registry since this is where your DSN information is stored. (HKEY_LOCAL_MACHINE\SOFTWARE\ODBC for system DSN's, HKEY_CURRENT_USER\Software\ODBC for user DSN's).

~Warren

ODBC Program & Heterogeneous queries problem

Best All,

What I am doing is following:
1. I created a stored procedure "testit" which select records from another data source in another SQL Server, like:

------------
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
go

create procedure testit
as begin
select * from [AnotherSQLServer].bugtest.dbo.mssent
end

grant execute on testit to public
------------

2. I can execute this stored procedure in SQL Query Analyser with success.

3. BUT: now I write a ODBC program trying to call this stored procedure, then it give me following "famous" error:
42000 [Microsoft][ODBC SQL Server Driver][SQL Server]Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

The ODBC program looks like this:
--------
strcpy((char *) sqltext, "{call testit}");
rc = SQLPrepare(hstmt, (unsigned char *) sqltext, SQL_NTS);
rc = SQLExecute(hstmt);
--------

My Question:
1. Does ODBC API support Heterogeneous queries ?
2. If well, how to solve above problem?

Thanks a lot,

MelThe solution can be found here:

http://dbforums.com/t905850.html

use some thing like this (before calling SQLConnect() ):
SQLSetConnectAttr( hdbc,SQL_COPT_SS_ANSI_NPW,(void *) SQL_AD_ON, 0);

odbc problems with Linked server in sql 2005

Hi guys,

I've a Linked server en sql 2000 with oledb provider for odbc drivers (Progress software) to do some queries, and works fine, I m trying to do the same in sql 2005, i set up exactly as i do in sql 2000 but when I try to view the tables: there are nothing the list is missing and obviosly when i try to query, I got errors:

OLE DB provider "MSDASQL" for linked server "test" returned message "[DataDirect-Technologies][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed.".

OLE DB provider "MSDASQL" for linked server "test" returned message "[DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Access denied(Authorisation failed)".

OLE DB provider "MSDASQL" for linked server "test" returned message "[DataDirect-Technologies][ODBC PROGRESS

ex:

works perfectly in sql 2000, not in 2005

SELECT * FROM OPENQUERY (MY_LINKED_SERVER, 'SELECT * FROM AFN_cotizaseguro_rmt')

some body knows if i have to set up additional stuff in sql 2005?

This seems to be a discussion on ProgressTalk that refers to your problem:

http://www.progresstalk.com/showthread.php?t=103352

If the connection works in the ODBC administrator Test Connection then they seem to suggest checking the security context setting in the security page of the linked servers settings in SQL Management studio.

I hope this is some use to you.

|||

Thank u Dhericean, you really help me, now I 'm working with my linked servers in sql 2005, it was just a click in the security, awesome !!!

Regards

ODBC problems

I have next to no experience with databases, so please bear with me. We have an application that requires setting up a system DSN with a SQL Server driver. I am able to do so on one computer. When I go to another I start the process to add the DSN, I type in the server name and then I have to select the default database on that server. In the dropdown list of databases, only 5 or 6 appear... none of which is the one that I need. I am going through the same steps on each computer and do not know why it would not be recognizing it. Does anyone have any ideas?By the way, I have SQL Server 2000 on Windows Server 2003. All machines are Windows XP with Windows firewall disabled.|||More than likely it is a permissions issue. You would have to grant access to the target database for the login that you are using.

In Enterprise Manager, expand the server node, expand the security node, click on the logins node. In the right-hand pane, select the login you are using and double click. On the database access tab, check the databases to which the login will have access. Also check the db_owner item in the right-hand pane**.

** Note that this is a BAD practice. However, you are indicating that you have no db experience and this will ensure that you get your users up and running quickly with minimal issues which good security can sometimes cause. You are essentially exposing your data to the user community and they may be able to update, delete data or even whole tables with the db_owner permission. Read up on user security and tighten the security back down when you understand better what your user requirements are.

Regards,

hmscott

ODBC Problem after install SP3 on SQL 2000

After install SP3 on SQL server 2000, some computers can't connect to the SQL server through ODBC.

Connection failed:
SQL State: '01000'
SQL Server Error: 10060
[Microsoft][ODBC SQL Server Driver][DBNETLIB]Connection Open(Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driber][DBNETLIB]SQL Server does not exist or
access denied

Please helpRE:
Q1 After install SP3 on SQL server 2000, some computers can't connect to the SQL server through ODBC. Please help

A1 Consider checking general network connectivity (ping the server from the workstations). If network connectivity is present consider verifying / checking the DSNs in use (verify protocol settings, etc., and test the connection) and / or use the MDAC compatibility checking tool to assess MDAC component status. See: http://www.microsoft.com/data/download.htm#CCinfo
http://download.microsoft.com/downl...XP/EN-US/cc.exe|||Ping the server OK
Already checked Client Network Utility and Server Network Utility. Both use TCP and NamePipe.
Run MDAC check already.

ODBC still don't work.

ODBC problem - see out, but cant see in

Windows 2000 Advanced Server (latest service pack)
SQL Server 2000 Enterprise Edition (8.00.2187)

1. Server can see other machines in network through Windows Explorer
2. Server can connect to other machines in network through ODBC
3. Other machine can't connect to my server through ODBC
4. Other machine can browse shared folders on server through Windows Explorer
5. Worked fine yesterday, ODBC went out sometime before midnight last night
6. No events pointing to problems in the event log.
7. Problem is fixed with reboot.
8. Automatic updates is turned off
9. All updates were downloaded and installed about 1 1/12 weeks ago.
10. Server can be seen through Enterprise manager on server
11. Query analyzer cannot connect from remote machine
12. Query analyzer can connect if ran from server

What should I be looking for to make sure that this doesn't happen again? (I know that there has been more updates since then)You don't have DHCP turned on on the server, do you?|||Just client not server. This machine isn't a domain controller. It logs into another domain and isn't part of a cluster.|||On point 7, which machine (client or server) is rebooted to fix the problem? Also, can you check on the server using the Server Network Utility tool to see what protocols are supported there, and in what order? Do the same on the client using the Client Network Utility (cliconfg.exe, if it does not show up in Programs). Also check for aliases on the client (same utility, different tab).|||Server rebooted to fix problem.
Server - Only protocol enabled is TCP/IP.
Server - No aliases.
Client - TCP/IP & Named Pipes
Client - No aliases.|||Next time you get the error, check the exact wording of the message. If possible post it. I am out of ideas for the moment.

There are no firewalls, or other filtering devices between the two machines, right?|||It worked fine till last night. I haven't applied any hotfixes to SQL server in months.

The error was the same one that you get when you type the name of the server wrong or put in the wrong password - 'Server does not exist or access denied'.

I can't verify it, but I think some network 'tweaking' has been happening in the area. I can just blame it on the network guys, but I need to be sure that there's nothing wrong with my server.|||I can't find anything wrong with my machine. Everywhere I looked pointed to a problem with the firewall or a network problem. Since the reboot fixed everything and I haven't seen a problem since I am going to assume that there was some firewall changes or something like that.

Thanks for all the help everyone gave. Hopefully this problem will not come back.|||Did you try creating an Alias?

Could be a DNS thing

ODBC problem

I have been trying to reconfigure specific System DSN's in the ODBC System A
dministrator thru the Control Panel. When I click on Add or Configure button
s, nothing happes. I have tried this at the Domain and Local level with the
same results. I have tried
it as a user and administrator with nothing. This is a W2k PC with SP4 (just
updated) which had MDAC 2.7. I tried to update to MDAC 2.8 which did not so
lve the problem. I reinstalled the MDAC 2.7 and the result was the same. Wha
t could be stopping the Ad
ding or Configuring process from the ODBC Control Panel?
Thanks for any help in this matter.Hello,
double click on your existing dsn and see if configure window is appear
or not.
tell me .
Thanks,
Warm Regards,
Ayaz Ahmed
Software Engineer & Web Developer
Creative Chaos (Pvt.) Ltd.
"Managing Your Digital Risk"
http://www.csquareonline.com
Karachi, Pakistan
Mobile +92 300 2280950
Office +92 21 455 2414
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

ODBC problem

I am trying to set up a connection on a Windows 2003
Terminal Server. I am using the Microsoft ODBC for
Oracle driver to create a System DSN. Under the
administrator account I create it and the application
works. When I log into Terminal Services as a user or
even a power user the connection will not work. How can
I create a System DSN that all users will be able to use
without granting administrator privileges?
Thanks
.Try granting Everyone read access to the following registry on the TS
machine:
HKLM\Software\ODBC\ODBC.INI.
This is the key where all system DSNs are stored.
Rand
This posting is provided "as is" with no warranties and confers no rights.

ODBC Port

Hi, I have just installed a default instance of SQL server 2000 and can
connect ok with Query Analyser. I can connect ok with ODBC via a DSN if I
don't specify the port. However if I specify the port (which is 1433 I've
double checked) in the DSN or a JDBC connection it fails! Can somebody help
me?
Thanks
Adam Sankey
on the sql serve box :
check the programs->sqlserver2000>sql server network utilitity
click tcp and check properties
you should be able to see the port configured, if tcp is enabled
thanks
"Adam Sankey" <AdamSankey@.discussions.microsoft.com> wrote in message
news:5F774885-4F36-436F-9AF9-6AA5A4586426@.microsoft.com...
> Hi, I have just installed a default instance of SQL server 2000 and can
> connect ok with Query Analyser. I can connect ok with ODBC via a DSN if I
> don't specify the port. However if I specify the port (which is 1433 I've
> double checked) in the DSN or a JDBC connection it fails! Can somebody
> help
> me?
> Thanks
> Adam Sankey

ODBC Port

Hi, I have just installed a default instance of SQL server 2000 and can
connect ok with Query Analyser. I can connect ok with ODBC via a DSN if I
don't specify the port. However if I specify the port (which is 1433 I've
double checked) in the DSN or a JDBC connection it fails! Can somebody help
me?
Thanks
Adam Sankeyon the sql serve box :
check the programs->sqlserver2000>sql server network utilitity
click tcp and check properties
you should be able to see the port configured, if tcp is enabled
thanks
"Adam Sankey" <AdamSankey@.discussions.microsoft.com> wrote in message
news:5F774885-4F36-436F-9AF9-6AA5A4586426@.microsoft.com...
> Hi, I have just installed a default instance of SQL server 2000 and can
> connect ok with Query Analyser. I can connect ok with ODBC via a DSN if I
> don't specify the port. However if I specify the port (which is 1433 I've
> double checked) in the DSN or a JDBC connection it fails! Can somebody
> help
> me?
> Thanks
> Adam Sankey

ODBC Permissions from Custom Data Processing Extension

Hi
I am trying to create a custom data processing extension that queries a Sybase database (ie odbc), then on the basis of this data queries a SQL Server database. However, when I get to querying the Sybase database, the method instantly crashed with a SecurityException "Request Failed." exception being thrown. I have so far being guessing that this is due to Permissions.
I have tried to create a OdbcPermission Permission Set which I have assigned to the relevant code group, and also have put a OdbcPermission attribute at the start of the method which is to access the Sybase database, but to no avail.
Does anybody have any idea what I can do to resolve this problem?
Thanks
Jan
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=72424bb9b1394d278da3c0fb33e5c352
*****************************************Almost certain that it is a Permissions issue now, as I set it to FullTrust and it made a connection to the Sybase database.
Can anybody tell me how to set up the permissions for connecting to an ODBC database for use in a custom assembly?
Would be much appreciated!
*****************************************
* A copy of the whole thread can be found at:
* http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/4587
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=b41e9858e9da41d88067b4f8fb5cabe6
*****************************************|||Hi Jan,
the ODBCPermission class is intended for future use when the .NET Framework
Data Provider for ODBC is enabled for partial trust scenarios. The .NET
Framework Data Provider for ODBC currently requires FullTrust permission. At
present, using the OdbcPermission class has no effect. You may also want to
check this MSDN article:
Code access security and ADO.NET
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcodeaccesssecurityadonet.asp
HTH, Robert
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jan Bodey via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:b41e9858e9da41d88067b4f8fb5cabe6@.SQLMonster.com...
> Almost certain that it is a Permissions issue now, as I set it to
FullTrust and it made a connection to the Sybase database.
> Can anybody tell me how to set up the permissions for connecting to an
ODBC database for use in a custom assembly?
> Would be much appreciated!
> *****************************************
> * A copy of the whole thread can be found at:
> * http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/4587
> *
> * Report spam or abuse by clicking the following URL:
> *
http://www.sqlmonster.com/Uwe/Abuse.aspx?aid=b41e9858e9da41d88067b4f8fb5cabe6
> *****************************************

ODBC password not saved in configuration file

Hi,

I have an SSIS package that pipes data from an iSeries data source to a SQL Server table. The connection manager I am using is the .Net Providers\Odbc Data Provider

When I run this SSIS package on my developer PC (from inside BIDS or using the Management Studio Execute Package Utility) it works. However when I run the package from within a SQL Server Agent Job it fails.

I have enabled logging and the error message given is:

System.Data.Odbc.OdbcException: ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4 ERROR [28000] [IBM][iSeries Access ODBC Driver]Communication link failure. comm rc=8002 - CWBSY0002 - Password for user MALLPRESS on server SMICHC is not correct, Password length = 0, Prompt Mode = Never, System IP Address = 172.16.0.4

It gives the message “Password length = 0” despite the fact that I have specified a password in the package.

To try to get around this I have added an XML configuration file to the SSIS package. In this configuration file I’ve set values for the ConnectionString, UserName, and Password.

I still get the above error about a password length of 0.

I have also tried to execute the package using dtexec, but this gives a similar error.

The SQL used to run this is:

xp_cmdshell 'dtexec /sq "\SCSRV-06\DTS Packages\Staging\StagingBranchImport"'

This has got me stumped, and I’m not sure where to go from here as these packages need to be programmatically run each morning. At the moment I am manually running them from within BIDS.

All packages that don’t use an iSeries connection manager run successfully in SQL Agent jobs.

I have also tried using the OLEDB connection manager (ibm db2 udb for iseries ibmda400 ole db provider) without success.

Any help would be very appreciated.

Thanks, Matt

SSIS will NOT store the password in the file for you. You have to edit the file manually to put the password in there. Have you done that?

-Jamie

|||

Hi Jamie,

Yes I have manually edited the config file and entered the password.

Matt

|||

Just to let anyone who's interested know....

I now have it working. The problem was that I did not have administrator access to the server, and so was using a network share to store the configuration file. I now how admin access to the windows server, and have moved the config file to the server's C:\, which has fixed the problem.

Matt

|||

Thanks for posting the solution, I am just ran into that problem today - ODBC works fine on local machine but when I move to production I get the password length error, even though I set the password in my package. I will try the package configuration file tomorrow.

I'm having the same problem with the iSeries Access OLEDB drivers - I can't seem to get them to work. Did you ever get them to work? I'm using 5v3 iSeries Access.

ODBC password not saved in configuration file

Hi,

I have an SSIS package that pipes data from an iSeries data source to a SQL Server table. The connection manager I am using is the .Net Providers\Odbc Data Provider

I have enabled logging and the error message given is:

It gives the message “Password length = 0” despite the fact that I have specified a password in the package.

To try to get around this I have added an XML configuration file to the SSIS package. In this configuration file I’ve set values for the ConnectionString, UserName, and Password.

I still get the above error about a password length of 0.

I have also tried to execute the package using dtexec, but this gives a similar error.

The SQL used to run this is:

xp_cmdshell 'dtexec /sq "\SCSRV-06\DTS Packages\Staging\StagingBranchImport"'

This has got me stumped, and I’m not sure where to go from here as these packages need to be programmatically run each morning. At the moment I am manually running them from within BIDS.

All packages that don’t use an iSeries connection manager run successfully in SQL Agent jobs.

I have also tried using the OLEDB connection manager (ibm db2 udb for iseries ibmda400 ole db provider) without success.

Any help would be very appreciated.

Thanks, Matt

SSIS will NOT store the password in the file for you. You have to edit the file manually to put the password in there. Have you done that?

-Jamie

|||

Hi Jamie,

Yes I have manually edited the config file and entered the password.

Matt

|||

Just to let anyone who's interested know....

Matt

|||

I'm having the same problem with the iSeries Access OLEDB drivers - I can't seem to get them to work. Did you ever get them to work? I'm using 5v3 iSeries Access.

ODBC over VPN

We have designed a thick client accounting system using Microsoft Access 200
3.
The thick clients connect to MS SQL Server 2000 or 2005 via ODBC.
This works well in a LAN environment. We have customers that want us to
connect their many branches together now. These branch offices are located i
n
different geographical regions, which could stretched over 100 to 200km apar
t.
We know that having these systems designed in Web would solve our problems,
but since we do not have much time and the budget to convert all these
systems from Access 2003 to web, we thought that the fastest way out is to
connect the thick clients (located in these branch offices) to the Head
Quarters (which has the centralised MS SQL server databases) is still using
ODBC over a high speed broadband from between 1 MBps to 3 MBps.
To increase security, we though of creating different VPNs connect each
branch to the HQ, and have all these branch systems linked via ODBC over VPN
to the HQ.
Please advise me whether the above concept is alright or not, in terms of:
1. whether such networking will work in the first place? any need of doing
further software modification?
2. whether using VPN would solve security problems during data transfer
between offices?
3. whether the existing broadband speeds, as mentioned above, is sufficient
to do the job?
thank you all in advance
JOSEPH YOONGJoseph,
I think you'll find that ODBC over a VPN connection to have very poor
performance. The overhead of the VPN, combined with the overhead at the
application layer is a bad recipe for client/server connectivity. I've
seen this with a couple different OBDC/SQL based applications. The
bottom line is almost always to either port the application to something
web-based or to use Terminal Services with or without Citrix.
Greg
*** Sent via Developersdex http://www.codecomments.com ***|||"Greg Lara" wrote:

> Joseph,
> I think you'll find that ODBC over a VPN connection to have very poor
> performance. The overhead of the VPN, combined with the overhead at the
> application layer is a bad recipe for client/server connectivity. I've
> seen this with a couple different OBDC/SQL based applications. The
> bottom line is almost always to either port the application to something
> web-based or to use Terminal Services with or without Citrix.
> Greg
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
Hi Greg
Thanks a million for the reply.
I understand what you mean.
In my country, I have tested this ODBC over VPN with a 2MB fibre optic
broadband speed and it works OK for now.
This is only 1 client connected to 1 server. The distance between the client
and server is about 2km away. Not sure how this will fair if we have the
following scenarios:
1. distances between 50km to 500km or
2. clients between 5 to 100 per server?
I am not sure whether 2MB (upload and download speed will be sufficient for
now)
Web-based solution will definitely solve my situation here, I know. But I am
not willing to spend another 2 years for a major re-write. I have taken abou
t
6 years already to complete this software, when web based solutions were
still in their infancy stage. .NET technologies were not yet available then.
Is there a quick way of transferring my MS Access 2003 windows based thick
client to .NET 2 web based soluton? if yes, do let me know. I don't mind
spending some money (not time) to do the conversion.
Otherwise, please elaborate how terminal services can work. What I know
about terminal services is that everything is done by the server. The client
PC merely acts as a "dummy" terminal, only transferring key strokes to the
connected server. How can this improve the performance of client / server?
I have heard of Citrix before, but frankly not sure what this is. Could you
please help me understand.
Thanks
regards
joseph yoong

odbc over internet

We are running an sql server database on a Windows 2000 server, aproximately
15 clients on our lan use odbc connections to connect with acces 2002 front
end. No problem. Then i came up with the idea to connect a home-worker via
internet, using a vpn tunnel over isdn. Somehow this vpn-over-isdn
connection is unstable, as a matter of fact only few records can be entered
before the connection is lost. Is this a limitation of odbc connections or
just a miserable isdn solution wich need to be replaced?"b.binnenweg" <bobby01@.nospamxs4all.nl> wrote in message
news:402e0d12$0$557$e4fe514c@.news.xs4all.nl...
> We are running an sql server database on a Windows 2000 server,
aproximately
> 15 clients on our lan use odbc connections to connect with acces 2002
front
> end. No problem. Then i came up with the idea to connect a home-worker via
> internet, using a vpn tunnel over isdn. Somehow this vpn-over-isdn
> connection is unstable, as a matter of fact only few records can be
entered
> before the connection is lost. Is this a limitation of odbc connections or
> just a miserable isdn solution wich need to be replaced?
>
That sounds like a connection issue... are you experiencing high latency
from the client to the server? Also, linked Access tables are not that
efficient and not recommended for WAN access.
Steve

odbc open query syntax to db2

Ok all you Gurus,
Here is a problem we are having, we have a sql server 2000 and we have
another server which is a DB2 server. The db2 server is linked. We are
trying to run an open query and I do not think I have the right syntax
to accomplish this task. Please see query following
go
SELECT *
FROM OPENQUERY(Bacarditest,'SELECT top 10* FROM USA.accts')
go
I do not know if this is correct concerning the syntax for db2 that
would be the sytax inside of the ()
this query runs fine with a select * instead of the top 10
please advise
thanx
Bill
*** Sent via Developersdex http://www.codecomments.com ***
"Bill" <nospam@.devdex.com> wrote in message
news:%23UWynax4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> Ok all you Gurus,
> Here is a problem we are having, we have a sql server 2000 and
> we have
> another server which is a DB2 server. The db2 server is linked.
> We are
> trying to run an open query and I do not think I have the right
> syntax
> to accomplish this task. Please see query following
>
> go
> SELECT *
> FROM OPENQUERY(Bacarditest,'SELECT top 10* FROM USA.accts')
> go
> I do not know if this is correct concerning the syntax for db2
> that
> would be the sytax inside of the ()
> this query runs fine with a select * instead of the top 10
> please advise
> thanx
> Bill
Try this Bill (untested),
SELECT *
FROM OPENQUERY(Bacarditest,'SELECT * FROM USA.accts FETCH FIRST
10 ROWS ONLY')
- Arnie

odbc open query syntax to db2

Ok all you Gurus,
Here is a problem we are having, we have a sql server 2000 and we have
another server which is a DB2 server. The DB2 server is linked. We are
trying to run an open query and I do not think I have the right syntax
to accomplish this task. Please see query following
go
SELECT *
FROM OPENQUERY(Bacarditest,'SELECT top 10* FROM USA.accts')
go
I do not know if this is correct concerning the syntax for DB2 that
would be the sytax inside of the ()
this query runs fine with a select * instead of the top 10
please advise
thanx
Bill
*** Sent via Developersdex http://www.codecomments.com ***"Bill" <nospam@.devdex.com> wrote in message
news:%23UWynax4GHA.2144@.TK2MSFTNGP04.phx.gbl...
> Ok all you Gurus,
> Here is a problem we are having, we have a sql server 2000 and
> we have
> another server which is a DB2 server. The DB2 server is linked.
> We are
> trying to run an open query and I do not think I have the right
> syntax
> to accomplish this task. Please see query following
>
> go
> SELECT *
> FROM OPENQUERY(Bacarditest,'SELECT top 10* FROM USA.accts')
> go
> I do not know if this is correct concerning the syntax for db2
> that
> would be the sytax inside of the ()
> this query runs fine with a select * instead of the top 10
> please advise
> thanx
> Bill
Try this Bill (untested),
SELECT *
FROM OPENQUERY(Bacarditest,'SELECT * FROM USA.accts FETCH FIRST
10 ROWS ONLY')
- Arnie

ODBC on SQL 2005

Hi,
I need to set up an ODBC Connection to a Navision DB. Under SQL 2000 I used
the regular ODBC driver and it worked fine.
With SQL2005 I just can select ".net Framework Data Provider for ODBC" and I
can't get the connection up and running. Does there exist any other ODBC
drivers which work on SQL2005 ?
Hope anyone can help !
Thanks
AxelWhat errors are you getting? Have you went to the surface area configuratio
n
tool in SQL 2005 and made sure remote connections are enabled?
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Axel" wrote:

> Hi,
> I need to set up an ODBC Connection to a Navision DB. Under SQL 2000 I use
d
> the regular ODBC driver and it worked fine.
> With SQL2005 I just can select ".net Framework Data Provider for ODBC" and
I
> can't get the connection up and running. Does there exist any other ODBC
> drivers which work on SQL2005 ?
> Hope anyone can help !
> Thanks
> Axel
>
>

ODBC on remote computer won't access

I'm attempting to connect to an SQL Server Express database from a remote PC, but it isn't connecting. I have a user account created in the database, and I'm using the SQL authentication with that user account. I have success with this if I'm on the server that the database is on, but not remotely. Both are on the same network, even plugged into the same hub. There doesn't appear to be any network problems.

Any ideas? Thanks!

Please follow this instruction to enable remote connections: http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

If you still see problem, follow the instruction here and give us more details http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=362498&SiteID=1

ODBC on 64 bit Enterprise SQL Server 2005

We are preparing to migrate all our 2000 databases to SQL Server 2005
and have run into an unexpected issue. We pull in a lot of Legacy Data
from a Thorougbred Basic main system. We use their 32 bit ODBC driver
and they have not released a 64 bit driver. These jobs are done mostly
through DTS and I was preparing to re-write all these existing
programs in SSIS, but I just realized that the new 64 bit edidtion of
SQL Server can't see the legacy 32 bit driver. Is there any work-
around for this in SSIS? Most companies are releasing 64 bit
compatable drivers (DB2, JODBC etc). I have tried entering the keys in
the registry etc. to no avail. I need to create a SYSTEM DSN.
Again, we have to use ODBC.
Thanks in advance,
Kristina
Hi Kristina
"Kristina" wrote:

> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
to set up the DSN. I am not sure if that would allow you to configure a DSN
that you can use!
John
|||On Mar 14, 2:25 pm, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
> Are you using the 32bit ODBC administrator c:\windows\syswow64\odbcad32.exe
> to set up the DSN. I am not sure if that would allow you to configure a DSN
> that you can use!
> John- Hide quoted text -
> - Show quoted text -
Yes, I was able to do that, but the functionality is not availible
when I go to choose a .NET connection (ODBC) in SSIS. In the old DTS,
it would show me the fields, lengths etc and everything in the
database. I also haven't been able to get a linked server set up
which we need for open rowset queries.
|||You're right, there is no 64 bit Jet driver; so you cannot directly use an
ODBC connection to JET from a program running under the 64 bit mode.
However, to solve your problem, you have many other possibilities:
1- Run your DTS package from a second machine (could be a virtual machine),
preferably one with SQL-Server 2000 but you could also use an installation
of SQL-Server 2005 running in 32 bit mode and with the special add-in for
using DTS installed.
Of course, instead of using a second machine or a virtual one, you could
also install a second instance of SQL-Server (either 2000 or 2005) but this
time running under the 32 bit mode.
2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
bit mode but now with SSIS instead of DTS.
3- Use the 32 bit version of dtexec.exe (the one that you will find under
the "Program Files (x86)" folder branch). However, I don't know if you must
have an installation of SQL-Server running under 32 bit to do that or if you
can also execute DTS package.
4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
64 bit to the 32 bit ODBC driver through it, see:
http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-200564.html
In your case, as you have already all the necessary DTS packages, I would
use them with an instance of SQL-Server running in 32 bit mode on one side
and SQL-Server 2005 64 bit on the other side as the target instead of
rewriting them with SSIS.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173886263.138387.294260@.d57g2000hsg.googlegr oups.com...
> We are preparing to migrate all our 2000 databases to SQL Server 2005
> and have run into an unexpected issue. We pull in a lot of Legacy Data
> from a Thorougbred Basic main system. We use their 32 bit ODBC driver
> and they have not released a 64 bit driver. These jobs are done mostly
> through DTS and I was preparing to re-write all these existing
> programs in SSIS, but I just realized that the new 64 bit edidtion of
> SQL Server can't see the legacy 32 bit driver. Is there any work-
> around for this in SSIS? Most companies are releasing 64 bit
> compatable drivers (DB2, JODBC etc). I have tried entering the keys in
> the registry etc. to no avail. I need to create a SYSTEM DSN.
> Again, we have to use ODBC.
> Thanks in advance,
> Kristina
>
|||On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
blanks, no spam please)> wrote:
> You're right, there is no 64 bit Jet driver; so you cannot directly use an
> ODBC connection to JET from a program running under the 64 bit mode.
> However, to solve your problem, you have many other possibilities:
> 1- Run your DTS package from a second machine (could be a virtual machine),
> preferably one with SQL-Server 2000 but you could also use an installation
> of SQL-Server 2005 running in 32 bit mode and with the special add-in for
> using DTS installed.
> Of course, instead of using a second machine or a virtual one, you could
> also install a second instance of SQL-Server (either 2000 or 2005) but this
> time running under the 32 bit mode.
> 2- Same thing than 1) with an installation of SQL-Server 2005 running in 32
> bit mode but now with SSIS instead of DTS.
> 3- Use the 32 bit version of dtexec.exe (the one that you will find under
> the "Program Files (x86)" folder branch). However, I don't know if you must
> have an installation of SQL-Server running under 32 bit to do that or if you
> can also execute DTS package.
> 4- Install SQL-Server 2005 Express and make your calls from SQL-Server 2005
> 64 bit to the 32 bit ODBC driver through it, see:
> http://gorm-braarvig.blogspot.com/2005/11/access-database-from-sql-20...
> In your case, as you have already all the necessary DTS packages, I would
> use them with an instance of SQL-Server running in 32 bit mode on one side
> and SQL-Server 2005 64 bit on the other side as the target instead of
> rewriting them with SSIS.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
> "Kristina" <Kristina...@.gmail.com> wrote in message
> news:1173886263.138387.294260@.d57g2000hsg.googlegr oups.com...
>
>
>
> - Show quoted text -
thanks for all the good advice. You clarified the problem for me. I
think we are leaning toward using the DTS on the 32 bit 2000 machine
and then replicating the data from 2000 to 2005. That brings up
another point..Am I correct in assuming that replication (probably
transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
would work? I would do a pull subscription from the 2005 box to the
2000 box.
|||Can't DTS connect to 64-bit SQL 2005?
Why couldn't you just move the data directly to 2005,
just use the 2000 DTS?

>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.- Hide quoted text -
> - Show quoted text -
|||On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> Can't DTS connect to 64-bit SQL 2005?
> Why couldn't you just move the data directly to 2005,
> just use the 2000 DTS?
>
>
>
>
> - Show quoted text -
I see your point but DTS is a lot more cumbersome than the new SSIS
and hard to keep updated. We move approx 20 gigs of data on a nightly
basis and then transform it into other relational databases. We don't
want to be forced to use the legacy DTS on the 64 bit 2005 SQL
edition. If we have to use the DTS we will keep it on an older 2000
box.
|||Hi Kristina
"Kristina" wrote:

> On Mar 14, 5:52 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
> blanks, no spam please)> wrote:
>
> thanks for all the good advice. You clarified the problem for me. I
> think we are leaning toward using the DTS on the 32 bit 2000 machine
> and then replicating the data from 2000 to 2005. That brings up
> another point..Am I correct in assuming that replication (probably
> transactional) from SQL Server 2000 32 bit to SQL Server 2005 64 bit
> would work? I would do a pull subscription from the 2005 box to the
> 2000 box.
>
I would not expect you do need to use replication, as you can define an ODBC
datasource on your 32 bit instance that connects to your SQL 2005 server.
John
|||Even if you use the 2000 DTS or the 2005 SSIS, you don't have to first ship
the data to SQL-Server 2000 or 2005 running in 32 bit mode, you can send
them directly to the SQL-Server 2005 running in 64 bit mode using either DTS
or SSIS 32 bit. The only obligation is that the program making the
connection with the 32 bit ODBC driver must be running itself in the 32 bit
mode but that doesn't forbid it to communicate directly with the SQL-Server
2005 64 bit at the other side of the transformation.
Also, even if you are running DTS on a SQL-Server 2000 or SSIS on a 32 bit
version of SQL-Server 2005, you don't have to send or retrieve data from one
of these two servers; are they are used only as the host for running the DTS
or the SSIS programs in 32 bit mode.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Kristina" <KristinaDBA@.gmail.com> wrote in message
news:1173967890.365144.154580@.l75g2000hse.googlegr oups.com...
> On Mar 15, 10:02 am, "raibeart" <raibe...@.gmail.com> wrote:
> I see your point but DTS is a lot more cumbersome than the new SSIS
> and hard to keep updated. We move approx 20 gigs of data on a nightly
> basis and then transform it into other relational databases. We don't
> want to be forced to use the legacy DTS on the 64 bit 2005 SQL
> edition. If we have to use the DTS we will keep it on an older 2000
> box.
>
|||On Mar 15, 10:20 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi Kristina
>
>
> "Kristina" wrote:
>
>
>
>
>
>
>
>
> I would not expect you do need to use replication, as you can define an ODBC
> datasource on your 32 bit instance that connects to your SQL 2005 server.
> John- Hide quoted text -
> - Show quoted text -
You guys are right. Thanks for the help!
Talk to you later with my next crisis moment....

Saturday, February 25, 2012

ODBC SQL Driver Timeout Expired from MS Access

ODBC SQL 2005 and Named Instance - won't connect

ODBC SQL 2005 and Named Instance - won't connect

ODBC SPROCS and unnamed parameters

ODBC source error message on Vista 64

ODBC Setup

odbc select where parameters

ODBC Select Data Source in Crystal reports - continuously prompts

ODBC security problem

ODBC Secruity

ODBC Returning success but actually failing (SQL Native)

ODBC Redux

ODBC readonly (viewonly) linked tables....

ODBC Query ... getting Where Clause

ODBC Provider for DSN through connection object?

ODBC Program & Heterogeneous queries problem

odbc problems with Linked server in sql 2005

ODBC problems

ODBC Problem after install SP3 on SQL 2000

ODBC problem - see out, but cant see in

ODBC problem

ODBC problem

ODBC Port

ODBC Port

ODBC Permissions from Custom Data Processing Extension

ODBC password not saved in configuration file

ODBC password not saved in configuration file

ODBC over VPN

odbc over internet

odbc open query syntax to db2

odbc open query syntax to db2

ODBC on SQL 2005

ODBC on remote computer won't access

ODBC on 64 bit Enterprise SQL Server 2005

ODBC Isolation level

Blog Archive

About Me