Openrow set - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Openrow set

Author

Topic

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-05-29 : 15:02:06

Hello:

I am writing dynamic open row set query.

DECLARE @sql_cmd varchar(4000)
	select @sql_cmd = 'insert into customer..PP 
						select * 
						from OPENROWSet(''MSDASQL'',''SQLSERVER'';''USERNAME'';''PASSWORD'',
						''select * from NAME..TABLEONE (NOLOCK) where cID in (''2222'')'')'
exec (@sql_cmd)

However, I got Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '2222'.

Where I missed? Your help is always appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 15:10:59

try


DECLARE @sql_cmd varchar(4000)
	select @sql_cmd = 'insert into customer..PP 
						select * 
						from OPENROWSet(''MSDASQL'',''SQLSERVER'';''USERNAME'';''PASSWORD'',
						''select * from NAME..TABLEONE (NOLOCK) where cID in (''''2222'''')'')'
exec (@sql_cmd)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-05-29 : 15:28:51

Thank you very much for quick response.

I may need to set up for openrow set login. I got error:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".

Could you tell me how to set up for openrow set log in.

Your help is highly appreicated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 15:32:56

its not login issue
Seems like you dont have provider MSDASQL installed and registered

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-05-29 : 15:46:03

Thank you very much for the quick response. The source server is window 2008. I have no right to install drive etc on that server. Is any fast way to copy table from one server to another server.

Thank you very much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 15:58:59

quote:
Originally posted by snow12

Thank you very much for the quick response. The source server is window 2008. I have no right to install drive etc on that server. Is any fast way to copy table from one server to another server.

Thank you very much!

you can use export import wizard for that

right click db. choose tasks-> import data to launch export import wizard and select correct source destination db and tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snow12
Yak Posting Veteran

74 Posts

Posted - 2012-05-29 : 16:20:11

Thank you very much for the help. yes, I was using export wizard but I have millions records. It have been running 8 hr now. So I think that I need to use open row set to copy.

Is any other fast way to copy large data?

Thank you very much again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 21:18:13

yep..Add a linked server connection to second server and then simply use INSERT....SELECT FROM [Server2].[db].[schema].[table]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 21:20:34

see this article to understand what all things to be taken care of for creating linked server

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=164892

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources