CLR programming! 1

[Antzz]

Hi Guys,
Here is a problem I am trying to solve and keep running into roadblocks:

I need to access information from two SQL Servers(physically separate servers), merge the results and send them to the client. I would prefer to code this as a SQL Server process if possible. I am allowed to write stored procedures in only one of them. Linked-servers is not an option.

I was trying to solve my problem by writing a .Net snippet to access the data from the server which am not allowed to write SPs in and then using this as a CLR object from an SP in the SQL Server over which I have control. I was hoping this would work but it does not since I think SQL Server does not like the fact that it is trying to access another server from within its context. I am stamped the assembly with PERMISSION_SET=EXTERNAL_ACCESS(which I was able to do after signing the DLL - which is a whole another process).

This is an architectural as well as a technical problem.

1) Am I doing the right thing while trying to solve my issue?
2) Is there other solutions to this problem?

Thanks a lot for all your feedback in advance.

A

 

[gmmastros]

Have you thought about using the OpenRowset command?

http://msdn2.microsoft.com/en-US/library/ms190312.aspx

-George

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

 

[Antzz]

Ideally, I would like to make the dataset/rowset to be available to the stored procedure.

For the OpenRowset command, it looks like data is available within the .Net code only. Am I correct?

Thanks for the response George.

Anthony

 

[gmmastros]

No.

OpenRowset will make the data available within a stored procedure.

Look at the example from Books On Line.

[COLOR=blue]USE[/color] pubs
[COLOR=blue]GO[/color]
[COLOR=blue]SELECT[/color] a.*
[COLOR=blue]FROM[/color] OPENROWSET([COLOR=red]'SQLOLEDB'[/color],[COLOR=red]'seattle1'[/color];'sa';'MyPass',
   [COLOR=red]'SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname'[/color]) [COLOR=blue]AS[/color] a
[COLOR=blue]GO[/color]

All you need to know is the name of the server and a good username/password.



-George

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

 

[Antzz]

WONDERFUL. I am going to try this out right now.

 

[Antzz]

George,
I got this from the MSDN website:

"Ad hoc distributed queries use the OPENROWSET and OPENDATASOURCE functions to connect to remote data sources that use OLE DB. OPENROWSET and OPENDATASOURCE should be used only to reference OLE DB data sources that are accessed infrequently. For any data sources that will be accessed more than several times, define a linked server."

Looks like there are potential performance issues. What do you think?

 

[gmmastros]

In your original post, you said that a linked server is not an option.

Since this is true, your next best choice is to use a 'slower' technology like OpenRowset. That being said, the performance shouldn't be too bad (unless there are a ton of rows and/or columns).

I would suggest that you try this to see what the performance is like.

-George

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

 

[Antzz]

George,
Thanks a bunch. Will keep you posted.

A

 

[Antzz]

George,
I found a limitation of the OpenRowSet function. It does not allow a variable containing a sql statement inside it.

For example we can use:
select * from
openrowset('SQLOLEDB',
'server_name';'user_id';
'pwd','SELECT top 100 * FROM table t1') as a

But we cannot use:

declare @sql varchar(1000)

set @sql = 'SELECT top 100 * FROM table_name'''

select * from
openrowset('SQLOLEDB',
'idxdbsql';'webidxraddb';
'webidxrad',@sql) as a
-----------------------------------------------

Thanks George.

A

 

[gmmastros]

I'm not really sure what the best way to handle this is, but I found a workaround for the problem.

[COLOR=blue]declare[/color] @sql [COLOR=blue]varchar[/color](1000)

[COLOR=blue]set[/color] @sql = [COLOR=red]'
[/color][COLOR=blue]select[/color] * [COLOR=blue]from[/color] 
openrowset([COLOR=red]''[/color]SQLOLEDB[COLOR=red]''[/color],
[COLOR=red]''[/color]idxdbsql[COLOR=red]''[/color];''webidxraddb'';
[COLOR=red]''[/color]webidxrad[COLOR=red]''[/color], [COLOR=red]''[/color][COLOR=blue]SELECT[/color] top 100 * [COLOR=blue]FROM[/color] table_name[COLOR=red]''[/color]) [COLOR=blue]As[/color] A
[COLOR=red]'
[/color]
[COLOR=blue]Exec[/color] (@SQL)

Unfortunately, this makes things a bit more difficult because if you want to combine this data with data on the local server, you'll need to insert exec the data in to a temp table.

Are you sure you can't set up a linked server?

[COLOR=blue]EXEC[/color] sp_addlinkedserver 
	@server = [COLOR=red]'idxdbsql'[/color], 
	@srvproduct = [COLOR=red]'Linked Server'[/color], 
	@provider = [COLOR=red]'MSDASQL'[/color],
	@provstr = [COLOR=red]'DRIVER={SQL Server};SERVER=idxdbsql;UID=webidxraddb;PWD=webidxrad;'[/color]

-George

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

 

[Antzz]

I can set up a linked server but have faced problems/issues before.

One of my friends is a DBA for Microsoft and he has also advised against using linked servers because of lot of issues. I do not remember them right now but they definitely seemed to be valid when we discussed them.

Using a temporary table in this case is not a problem since I have to merge the results with the data from my local server and send them to the client anyways.

But the problem is the usage of a variable in place of a hardcoded SQL statement since the SQL to be executed is really dynamic. I can make it hardcoded but the code will be longer because of the combinations of the parameters.



Thanks George.