Using OPENROWSET for SQL SERVER

[jharding08]

I am converting some sql statements that were designed for an Access database connection to make them work with SQL Server. Most of the time this has meant to use OPENROWSET for the external SQL Server connection while using a local Access table connection. Can anyone tell me why this statement says it has a problem with the IN Clause?

SELECT * INTO [BarcodeMasks] FROM [BarcodeMasks] IN OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=JEFF;UID=dbo;PWD=',[BarcodeMasks])

 

[vongrunt]

IN() expects single-column noncorrelated subquery.

Wrap OPENROWSET() into ( SELECT ... )


------
"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)

 

[vongrunt]

And btw. where is WHERE clause?

------
"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]

There is no WHERE clause...it should be doing the whole table. ARe you saying i should make the statment like this:

SELECT * INTO [BarcodeMasks] FROM [BarcodeMasks] IN (Select * FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=JEFF;UID=dbo;PWD=',[BarcodeMasks]))

 

[vongrunt]

I guess you need this:

SELECT * 
INTO [BarcodeMasks] 
FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=JEFF;UID=dbo;PWD=', [BarcodeMasks])

That should copy entire table from external source... assuming that BarcodeMasks does not exist in current database.

------
"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]

The table does not exist in the current database, this is part of an 'export table' function. One question, how do I specify what database to use as part of the OPENROWSET function?

 

[vongrunt]

In connection string. Exact syntax depends on provider. I think DATABASE=dbname (...SERVER=JEFF;DATABASE=BLAH;UID=...) should work for MSDASQL but am not 100% sure.

Or you can use DB.owner.object notation for source table - for example myDatabase.dbo.BarcodeMasks.

------
"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 what I currently have that errors out saying 'Syntax error in FROM clause'

sSQL = "SELECT * INTO [BarcodeMasks] FROM OPENROWSET('MSDASQL','DRIVER={SQL Server}; SERVER=JEFF;DATABASE=PTSQL;UID=dbo;PWD=',
[BarcodeMasks])"

 

[jharding08]

If the current connection is an access connection, how would I connect to the SQL Server table to import into access?

sSQL = "SELECT " & Columns & " INTO " & sTable & " FROM " & sTable & " IN '' [MS ACCESS;DATABASE=" & DatabaseFrom & ";TABLE=" & Table & "]"

oConn.Execute sSQL

 

[vongrunt]

Ahh... from Access, SELECT into Access FROM SQL Server...

Then some of Access forums are better places to get that answer, sorry

------
"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)