Pass Through

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm not that familiar with pass through queries but I'd like to write one to
avoid the tedious task of entering a username and password when querying a
certain table. The table that asks me for a username and password resides on
SQL Server 2000. If anyone has any ideas, suggestions, or sample it would be
greatly appreciated. Thanks in advance.
 
Mike C said:
I'm not that familiar with pass through queries but I'd like to write one
to
avoid the tedious task of entering a username and password when querying a
certain table. The table that asks me for a username and password resides
on
SQL Server 2000. If anyone has any ideas, suggestions, or sample it would
be
greatly appreciated. Thanks in advance.
Click to expand...

You should be prompted for the ODBC credentials when you click in "ODBC
Connect Str" in your PTQ's properties box.

Keith.
www.keithwilby.com
 
Keith,
Thanks for a quick response. I am able to put in a connect string but the
query that I have gives me an ODBC error (ODBC - Connect Call Failed). Below
is the Connect String and the SQL I'm trying to pass through.

PTQ Connect String:
ODBC;DSN=IE;Description=IE;UID=ner1avk;PWD=conduct;DATABASE=IE

SQL in PTQ:
SELECT dbo_OnRoad_Estimate.Slic
FROM dbo_OnRoad_Estimate
WHERE (((dbo_OnRoad_Estimate.Slic)="x"));
 
Mike,

not sure about the connection, but the text of your SQL must match SQL
Servers syntax requirements, which use the single quote rather than the
double quote to delineate text. Try changing the SQL where clause to:

WHERE dbo_OnRoad_Estimate.Slic='x'

HTH
Dale
 
Thank Dale.

I tried single quotes and no good. A coworker had suggested that I set the
return results field in properties from "Yes" to "No" and to set the ODBC
timeout field from 60 to 0. Unfortunately neither suggestion worked.
 
Mike C said:
Keith,
Thanks for a quick response. I am able to put in a connect string but the
query that I have gives me an ODBC error (ODBC - Connect Call Failed).
Below
is the Connect String and the SQL I'm trying to pass through.

PTQ Connect String:
ODBC;DSN=IE;Description=IE;UID=ner1avk;PWD=conduct;DATABASE=IE
Click to expand...

How did you arrive at that string?
 
Hi Mike,

The first thing that jumps out to me
is that you are using "linked-table"
underscore to qualify "dbo" instead
of a period.

So I might start by rewriting

SQL in PTQ:
SELECT t.Slic
FROM dbo.OnRoad_Estimate AS t
WHERE (((t.Slic)='x'));

I don't know if that is the "connect" problem,
but I would start there.

I would have thought the connect string
would have taken care of itself within the
design start of the passthru?

If you have permissions, I really would create
a stored procedure on the server, then create
passthru to this stored procedure.

When you want to change 'x' for your
requirements, you then just rewrite the
passthrough SQL in code.

It has been awhile since I did what you
are trying, but I believe you might also
want to start the passthru with

SET NOCOUNT ON

SQL in PTQ:
SET NOCOUNT ON
SELECT t.Slic
FROM dbo.OnRoad_Estimate AS t
WHERE (((t.Slic)='x'));

I (almost) always start my stored procs
with this to keep Access from misinterpreting
message of "how many records" as the
query result.

I cannot remember when (for sure) that bites
you in the butt, so just always include it.

FYI here be what I imagine a connect
string to look like:

ODBC;DSN=MyDSN;UID=MyID;PWD=MyPswd;DATABASE=MyDB;Network=DBMSSOCN;QuotedId=No

good luck,

gary
 
Gary,
Taking note of your "dbo" observation I trimmed off "dbo_" from the the
table name and it works just fine. Thanks for your help.
Mike
 
Back
Top