Passing 'date' parameter to Jet 4.0 linked server - collation problem?

Hello,
I've attached Access MDB as a linked server to SQL 2000 server. Now I
want to run distributed queries. I set 'collation compatible' option,
so when I reference an Access MDB table in WHERE clause, the parameter
is passed to Access data provider. But I can't do the same for
Datetime columns! :-((
Unfortunately I have a large legacy application which uses dynamic SQL
creation, so it is VERY painful for me to rewrite&optimize all SQL
statements in order to use OPENQUERY statements :-((
Example:
Select * From MyMDB...Orders Where OrderDate Between '1/1/5' and
GETDATE()
This statement results in scanning all of the Orders table by SQL
server :-(
Please help me!> Select * From MyMDB...Orders Where OrderDate Between '1/1/5'
What the heck kind of date is that? Try '20050101', assuming that is the
date you meant. The fact that *I* don't know what date you are passing
should be some kind of clue as to why the software doesn't understand it.

Passing a parameter from a form to an sql sequel view

Hi,

We have recently upsized an access db to a sequel server db. The queries in access have been made into views in sql. ASP will be the front end. We are having problems passing an input from a form into a view. In access the parameter had to match what was selected on the form but sequel doesn't like it. At the moment, for testing purposes, the value has to be hard coded i.e. case_id = 64. Is there anything is ASP that can help?

Thank you

You stated that you're looking for something in ASP to help you... I assume you are using some version of ASP.NET?

If you are using ASP.NET 1.1, you have a number of options, the easiest of which is creating a SqlCommand or SqlDataReader object and filtering the rows on the parameters you use. You'll need to manually pass the value on your form into the parameter value.

However, if you're on ASP.NET 2.0, the solution is even simpler. Just create an instance of the SqlDataSource class and wire up a SelectParameter to the desired control.

Hope this helps...

pass thru queries to Oracle

I'm trying to pass a query from sql server 2000 to Oracle using linked
servers. I don't want to use DTS. While it's easy enough to use OPENQUERY
to pass thru a query that returns a dataset, I can't seem to pass thru a
query that doesn't return a dataset eg a create table query or a drop table
query. If I try, I get an error saying that the query returns no columns.
Is it possible to pass thru a query that returns no columns using a linked
server?
For example, the following query:
select *
from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
returns the error:
Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
OLE DB provider 'MSDAORA' indicates that the object has no columns.
OLE DB error trace [Non-interface error: OLE DB provider unable to process
object, since the object has no columnsProviderName='MSDAORA', Query=CREATE
TABLE MYTABLE AS SELECT * FROM EMP'].....OR ....
use::
SELECT * INTO #EMP from ORA1..CAT1.EMP
to get the same effect!!
"arch" wrote:

> I'm trying to pass a query from sql server 2000 to Oracle using linked
> servers. I don't want to use DTS. While it's easy enough to use OPENQUER
Y
> to pass thru a query that returns a dataset, I can't seem to pass thru a
> query that doesn't return a dataset eg a create table query or a drop tabl
e
> query. If I try, I get an error saying that the query returns no columns.
> Is it possible to pass thru a query that returns no columns using a linked
> server?
> For example, the following query:
> select *
> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
> returns the error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
> OLE DB provider 'MSDAORA' indicates that the object has no columns.
> OLE DB error trace [Non-interface error: OLE DB provider unable to proces
s
> object, since the object has no columnsProviderName='MSDAORA', Query=CREAT
E
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>|||arch,
Are you simply attempting to create a temp table on the SQL side dependant
on data from the Oracle side?
If so, then the reference to Oracle needs to be on the SELECT object.
We use linked servers from SQL to Oracle as a matter of day-to-day business
and I use something along the following lines (where ORA1 is the linked
server name as listed in the Security tabl of EntMgr):::
CREATE TABLE #EMP (x,y,z)
INSERT INTO #EMP
SELECT X,Y,Z
FROM ORA1..CAT1.EMP a
This, of course, indicates you know the schema or at least the columns you
want (remember you should reduce loads by not selecting redundant data), and
their respective datatypes.
Hope this assists,
Tony
"arch" wrote:

> I'm trying to pass a query from sql server 2000 to Oracle using linked
> servers. I don't want to use DTS. While it's easy enough to use OPENQUER
Y
> to pass thru a query that returns a dataset, I can't seem to pass thru a
> query that doesn't return a dataset eg a create table query or a drop tabl
e
> query. If I try, I get an error saying that the query returns no columns.
> Is it possible to pass thru a query that returns no columns using a linked
> server?
> For example, the following query:
> select *
> from OPENQUERY(ORA8I,'CREATE TABLE MYTABLE AS SELECT * FROM EMP')
> returns the error:
> Server: Msg 7357, Level 16, State 2, Line 1
> Could not process object 'CREATE TABLE MYTABLE AS SELECT * FROM EMP'. The
> OLE DB provider 'MSDAORA' indicates that the object has no columns.
> OLE DB error trace [Non-interface error: OLE DB provider unable to proces
s
> object, since the object has no columnsProviderName='MSDAORA', Query=CREAT
E
> TABLE MYTABLE AS SELECT * FROM EMP'].
>
>|||That's very useful stuff. But what I wanted to do was to create a table in
the oracle database. In microsoft access, it is very easy to execute a pass
thru query to Oracle, including action queries such as creating and dropping
objects. I can't seem to do that using SQL Server unless I use DTS. Is it
possible to do?
"Tony Scott" <TonyScott@.discussions.microsoft.com> wrote in message
news:90D78160-8F51-4BC2-8F06-D2226A10F95A@.microsoft.com...
> arch,
> Are you simply attempting to create a temp table on the SQL side dependant
> on data from the Oracle side?
> If so, then the reference to Oracle needs to be on the SELECT object.
> We use linked servers from SQL to Oracle as a matter of day-to-day
> business
> and I use something along the following lines (where ORA1 is the linked
> server name as listed in the Security tabl of EntMgr):::
> CREATE TABLE #EMP (x,y,z)
> INSERT INTO #EMP
> SELECT X,Y,Z
> FROM ORA1..CAT1.EMP a
> This, of course, indicates you know the schema or at least the columns you
> want (remember you should reduce loads by not selecting redundant data),
> and
> their respective datatypes.
> Hope this assists,
> Tony
> "arch" wrote:
>