SQL Statement From Access To SQL Server

[jharding08]

I have to do a data transfer between two databases. The code was already written to move data between two access MDB files. I am changing the backend database to SQL Server, which inevitably will change the SQL statement. here is the statement for two MDB's -

"UPDATE Packages P INNER JOIN " & _
"[MS Access;DATABASE=" & DatabaseFrom & ";].Packages MSP " & _
"ON MSP.PackageID = P.PackageID SET " & _
"P.TrackingNo = MSP.TrackingNo, " & _
"P.RecipientID = MSP.RecipientID, " & _
"P.PackageTypeID = MSP.PackageTypeID, " & _
"P.LocationID = MSP.LocationID, " & _
"P.CarrierID = MSP.CarrierID, " & _
"P.MailRoomID = MSP.MailRoomID, " & _
"P.ReceivedUserID = MSP.ReceivedUserID, " & _
"P.ReceivedDate = MSP.ReceivedDate, " & _
"P.DeliveredUserID = MSP.DeliveredUserID, " & _
"P.DeliveredDate = MSP.DeliveredDate, " & _
"P.DeliveryStatusID = MSP.DeliveryStatusID, " & _
"P.RecipientSignatureID = MSP.RecipientSignatureID, " & _
"P.Comments = MSP.Comments, " & _
"P.LastUpdated = NOW() " & _
"WHERE P.LastUpdated<>MSP.LastUpdated AND P.LastUpdated<MSP.LastUpdated"


When i run the execute with DATABASEFROM being the path to a UDL file pointing to the SQL Server Database, I get the error : "Syntax Error Near P". Any SQL Server experts out there?

 

[jharding08]

What should i test next? i am still getting errors..

 

[vongrunt]

I'm running outta ideas...

Post EXACT sSQL value here - the one given by VB debugger or Response.Write or whatever.

And also plz. post UDL file contents - with masked username/password of course.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jharding08]

Here is the ssql:

? ssql

Update P SET TrackingNo = MSP.TrackingNo, RecipientID = MSP.RecipientID, PackageTypeID = MSP.PackageTypeID, LocationID = MSP.LocationID, CarrierID = MSP.CarrierID, MailRoomID = MSP.MailRoomID, ReceivedUserID = MSP.ReceivedUserID, ReceivedDate = MSP.ReceivedDate, DeliveredUserID = MSP.DeliveredUserID, DeliveredDate = MSP.DeliveredDate, DeliveryStatusID = MSP.DeliveryStatusID, RecipientSignatureID = MSP.RecipientSignatureID, Comments = MSP.Comments, LastUpdated = GETDATE() FROM Packages P INNER JOIN OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\Program Files\IntelliTrack\Package Track\Transfer12212005112817.mdb';;, Packages ) MSP ON MSP.PackageID = P.PackageID Where P.LastUpdated <> MSP.LastUpdated And P.LastUpdated < MSP.LastUpdated

This is the UDL contents:

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=sa;Initial Catalog=PTSQL;Data Source=JEFF

 

[vongrunt]

And that statement pasted into Query Analyzer runs OK?

Can you also post complete error description?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jharding08]

it runs fine in query analyzer. here is the error desc:

[OLE/DB provider returned message: 'C:\Program Files\IntelliTrack\Package Track\Transfer12212005115147.mdb' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.]

 

[gmmastros]

I still think the problem is with the spaces in the path.

As a debugging step, please try this...

1. Move the MDB file to C:
2. Change the path (in the query) to 'C:\Transfer12212005115147.mdb'


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jharding08]

Its wierd, I did that, and it now says it can't find the file and I can see it on the C:\...wierd

 

[vongrunt]

And your locally installed SQL server instance is named JEFF?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jharding08]

yep

 

[gmmastros]

I'm now thinking that your SQL Server may not have the appropriate permissions to certain folders and such.

Do this...

Click Start -> Run
Type services.msc

scroll down to MSSQLSERVER
Right Click -> Properties

Click the "Log On" tab.

Is it logging on as "Local System Account"?



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jharding08]

It works...I had a UDL in the development directory and it was set to something else. i changed it and it ran. so what happens if the udl points to another server that isnt local?

 

[jharding08]

just as a reminder, how would i convert this string?

sSQL = "INSERT INTO Packages " & _
"SELECT * FROM [MS Access;DATABASE=" & DatabaseFrom & ";].Packages MSP WHERE " & _
" MSP.PackageID NOT IN (SELECT PackageID FROM Packages) AND MSP.PackageID NOT IN (SELECT PackageID FROM Deleted)"