SQLServer DTS Query

May 7, 2005

Hi,

I have a DTS query to move data from Access to an existing SQLServer table. There is a date column. When I build the query and choose "all rows" I get a good transfer. The problem is when I choose criteria (from the date column) and select a value from the box that comes up with a list of dates--the dates that are in the date column are what comes up, obviously...so if I choose one, then my query ends up looking something like this:

...WHERE [table].'CurrentEndDate'>='5/6/2005'...

If I run the DTS package like this, I get a failure and an error message that reads "data type mismatch in criteria expression". I don't understand this error message since when I run for "all rows" the data transfers just fine. The Access and SQLServer colums are both formatted "DateTime".

Can anyone help me on how to fix this...I'm trying to set up the package so when it runs, it only selects the rows added to the ACCESS table since the last transfer. If there is a way to script that, plz advise.

Thx much,

Reid C.

View 2 Replies

Problem With Sub Query In Sqlserver

May 1, 2007

Hi
I am new user to sqlserver, I have been using access db for my project, and now decided to upsize it to sqlserver. I did, the db is converted to sql, using the upsize wizard in MS Access. Now I have two problems with sql server.
No. 1: An embedded query that I wrote for data grid paging isnt working in sql server and giving me an error message, saying Incorrect syntax near the keyword 'order'. The query that is generated is:
Select cust_order.order_id as order_id,customers.fname as fname,customers.lname as lname, cust_order.order_date as order_date,cust_order.order_status as order_status from (select top 20 order_id,fname,lname,order_date,order_status from (select top 580 order_id,fname ,lname,order_date,order_status from cust_order,customers where customers.customer_id=cust_order.customer_id order by order_id) order by order_id desc) order by order_id;
There are actually three different queries, one (the simpler one) for page one is working, the other query is for page to till second last page that returns fix size of 20 records, while the last page returns the remaining 20 or less records: the above query is actually the one for last page. Here is the three queries:
If PN = 1 Then
SelectQuery = "select top " & perpage & " cust_order.order_id as order_id,customers.fname as fname , customers.lname as lname, cust_order.order_date as order_date,cust_order.order_status as order_status from cust_order,customers where customers.customer_id=cust_order.customer_id order by cust_order.order_id;"
Else
If PN = totPages And Remand > 0 Then
SelectQuery = "select cust_order.order_id as order_id,customers.fname as fname,customers.lname as lname, cust_order.order_date as order_date,cust_order.order_status as order_status from (select top " & Remand & " order_id,fname ,lname,order_date,order_status from (select top " & CLng(PN) * perpage & " order_id,fname,lname,order_date,order_status from cust_order,customers where customers.customer_id=cust_order.customer_id order by order_id) order by order_id desc) order by order_id;"
Else
SelectQuery = "select cust_order.order_id as order_id,customers.fname as fname,customers.lname as lname, cust_order.order_date as order_date,cust_order.order_status as order_status from (select top " & perpage & " order_id,fname,lname,order_date,order_status from (select top " & CLng(PN) * perpage & " order_id,fname ,lname,order_date,order_status from cust_order,customers where customers.customer_id=cust_order.customer_id order by order_id) order by order_id desc) order by order_id;"
End If
End If
I hope I can find someone help me with this. Its working in access just fine but not in sql server.

View 3 Replies View Related

SqlServer Query Visualizer

Dec 4, 2007

I added the SqlServerQueryVisualizer dll to D:Program FilesMicrosoft Visual Studio 9.0Common7PackagesDebuggerVisualizers and I can see the sql statement generated by the LINQ object, however when I click on Execute an expcetion is thrown saying log in failed for user sa...
The user and password I'm using are 100% correct.
Any ideas ?