Category: SQL Server

06 Aug 2008 01:56

Joined:	08/08/2007
Level:	Silver
Points:	213

Points:	1

Working with Openrowset

I am Working with OPENROWSET in my application.Its working fine while i am writing it as a query by suppling directly the servername,userid,pwd.But every time I can not hard code the values directly..So I want to write it as a storeprocedure by supplying all these values as input parameters,but its giving error while doing like that .So I want to know how to write it in a storedprocedure and what is the differece between the usage of linkedserver and openrowset.

Here is my working query

USE MyDB
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','MySErver';'USerID';'PWD',
'SELECT * FROM MyDB.dbo.MyTable ') AS a
GO

Thanks in advance.

#277148

06 Aug 2008 02:01

Joined:	14/07/2008
Level:	Gold
Points:	2024

Points:	6

Both OPENROWSET and OPENDATASOURCE should be used only for accessing external data for ad hoc situations, when it is not possible to configure a permanent linked server. These functions do not provide all of the functionality available from a linked server, such as management of login mappings, ability to query the linked server's meta data, and the ability to configure various connection settings such as time-out values.

The arguments of OPENROWSET and OPENDATASOURCE do not support variables. They have to be specified as string-literal. If variables need to be passed in as arguments to these functions, a query string containing these variables can be constructed dynamically and executed using the EXEC statement.

#277153

06 Aug 2008 02:04

Joined:	14/07/2008
Level:	Gold
Points:	2024

Points:	5

hi,

If you want to use OPENROWSET with
parameters, you need to use dynamic T-SQL statement, for example:
DECLARE @testVariable varchar(10);
SET @testVariable = 'varTest';
EXEC( 'SELECT colID,colName
FROM
OPENROWSET
(
''SQLNCLI'',
''Database=dbIDsAndNames;Uid=sa;Pwd=nosecurity;'',
''SET FMTONLY OFF
EXEC [dbo].[mySproc] ''' + @testVariable + ''')')

Hope this helps.

Note:
kindly mark this content if it is helpful!

#277157

06 Aug 2008 02:06

Joined:	27/08/2007
Level:	Gold
Points:	7822

Points:	5

hi,
it is not possible that store in the sp because there is no any variable that store the value but if you have fixed data then store in the value like this
decalere @Server varchar(50)
decalere @User varchar(50)
decalere @Pwd varchar(50)

set @Server = 'yourservername'
set @User = 'UserName'
set @Pwd = 'Password'

USE MyDB
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB',@Server;@User;@Pwd,
'SELECT * FROM MyDB.dbo.MyTable ') AS a
GO

vipul,
http://dongavipul.blogspot.com

Thanks & Regards
Patel Vipul

#277419

06 Aug 2008 05:58

Joined:	08/08/2007
Level:	Silver
Points:	213

Points:	1

hi all ,
Thanks a lot for ur help,Now i got it with ur code.

Working with Openrowset

About dotnetspider.com

Quick Links

General

Services