Is a multiple query within a single OPENROWSET possible?

Question

Hi everyone,

I was wondering if it's possible to make multiple queries within a single OPENROWSET?

I'm using SQL 2000 (I know; I'm a caveman!) and here's an example of what I'd like to do:

SELECT *FROM OPENROWSET('SQLOLEDB', '[SERVER]';'[USERID]';'[PASSWORD]','INSERT INTO [Table_Name] VALUES(123); SELECT SCOPE_IDENTITY FROM [Table_Name]')

The reason I'm asking is that I'd like to minimize the number of OPENROWSET queries made to a server that we call routinely. The connections can be finicky at times and I'm trying to decrease the number of lost connections to that server with these queries.

Thanks for the help!

Answer 1

Hi,

You can use an SQL command for just one return. In your example, use a procedure to INSERT and return the query.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

Hope that helps

Answer 2

Although the query might return multiple result sets, OPENROWSET returns only the first one.

---

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

Answer 3

Thanks guys, that's what I was afraid of. I'll try another way around this.