OPENQUERY problem

Question

Post reply

OPENQUERY problem

rightfield

SSCrazy

Points: 2418
More actions
February 2, 2011 at 2:28 pm

#244525

I am trying to insert records into a Pervasive database from SQL Server. In SQL Server I have a table containing data to be inserted, sched_repair. The destination in Pervasive is sched. The linked server is Test342011D.

This select works fine so I am sure the connection is good.

SELECT * FROM openquery(Test342011D, 'SELECT * from sched where loc_no=1001')

When I try to execute this insert I get an error:

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'SELECT'.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near ')'.

DECLARE @sql1 VARCHAR(MAX) SET @sql1 = 'INSERT openquery(Test342011D, ''SELECT * from sched'') VALUES (SELECT * FROM sched_repair) ' select @sql1 EXEC(@sql1)

Can anyone tell me where my problem is? I have inserted records into Pervasive before but the VALUES were always simple variables, this time I would like to insert the entire sched_repair table.

Thanks very much.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47068 More actions · Answer 1



DECLARE @sql1 VARCHAR(MAX)

SET @sql1 = 'INSERT openquery(Test342011D, ''SELECT * from sched'') SELECT * FROM sched_repair '

select @sql1

EXEC(@sql1)

"VALUES" is only used for one row of constants/variables.

I would also recommend that you specify the column names in both SELECT statements.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

rightfield SSCrazy Points: 2418 More actions · Answer 2

rightfield

SSCrazy

Points: 2418

February 3, 2011 at 6:09 am

#1281859

Thank you, I guess I will just have to loop through the source one at a time.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

Sorry I don't see why you need to Loop one row at a time.

I am sure if you explain what you are trying to do someone will come up with a set based answer (as you don't seem happy with the set based one I gave...)!

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

rightfield SSCrazy Points: 2418 More actions · Answer 4

Hi MM, thank you for your reply. I did try specifying all column names in both SELECTs but I got the same error. That's why I figured I would have to loop through the sched_repair table and insert all columns one row at a time into the Pervasive table.

For background, the application that uses the Pervasive database allows a user to delete a schedule and someone incorrectly deleted a schedule. Nightly, I refresh my development data and it contained the deleted schedule, all 53 rows, in the sched table. I pulled the development data out and saved it into a SQL Server table, sched_repair. I then wanted to insert all 53 rows of the SQL Server table, sched_repair, into production's Pervasive table, sched. In my few years here this is the first time I have had the need to insert more than one record at a time but it would be nice to have that ability.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 5

Did you read the code I posted - that will do what you want....?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);