April 7, 2009 at 12:00 am
IN SQL 2000 DTS , i created linked server using following SQL as Execute SQL Task :
************
IF (SELECT COUNT(*) FROM MASTER.DBO.SYSSERVERS WHERE SRVNAME = 'TEST') > 0
EXEC sp_dropserver 'TEST', 'droplogins'
DECLARE
@SERVER VARCHAR(8000),
@USER VARCHAR(8000),
@PASSWORD VARCHAR(8000),
@DATABASE VARCHAR(8000)
SET @SERVER = ?
SET @USER = ?
SET @PASSWORD = ?
SET @DATABASE = ?
EXEC sp_addlinkedserver
@server = 'TEST',
@srvproduct = '',
@provider = 'SQLOLEDB',
@datasrc = @SERVER,
@catalog = @DATABASE
EXEC sp_addlinkedsrvlogin 'TEST', 'false', NULL,@USER,@PASSWORD
*****************
Same thing i need to create in SQL 2005 SSIS , how i can do it? can any one help me regarding this? as when i try to create Execute SQl task with EXEc statements , its showing "not supported".
please guide me ...
April 7, 2009 at 5:31 pm
You can create the linked server using your existing code.
To configure the Execute SQL Task Editor, select your connectionType, create a Connection and test the connection to ensure that it works fine. Then set the SQLSourceType to Direct input and paste your sql code in the SQLStatement.
Create package variables to correspond with the sql code parameters.
Then click on Parameter Mapping to associate the package variables to your sql code parameters.
Ensure that you create the parameters in the same order of the SET @SERVER = ? parameters in the sql code, or make the Parameter Name order (in the parameter mapping view) correspond to the order of the parameter in your sql code. Just make sure the order matches.
I have attached a screen shot for the query and parameter mapping.
I hope this helps.:-)
April 7, 2009 at 11:29 pm
Thanks a lot............. 🙂
It helped me clearly, thanks again for you detailed explanation and for the screenshot.
Thanks & regards
Raghav
April 8, 2009 at 12:54 am
I have followed the instructions as mentioned by you.
but now i'm getting error as "The EXEC SQL construct or statement is not supported."
when i click on the build query button.
is this an error? or something else i need to do ?
April 8, 2009 at 1:54 am
Nothing else to do and you don't need to click on the build query button.
Just click on the Ok button, and to test if it works fine, right-click on the task and execute it.
It should execute successfully, then you can check your server to confirm the linked server is created.
Cheers!
April 8, 2009 at 4:59 am
I tried with the same with out success
Here is the result :
[Execute SQL Task] Error: Executing the query "DECLARE @LINKEDSERVERNAME VARCHAR(8000), @SERVER VARCHAR(8000), @USER VARCHAR(8000), @PASSWORD VARCHAR(8000), @DATABASE VARCHAR(8000) SET @LINKEDSERVERNAME = ? IF (SELECT COUNT(*) FROM MASTER.DBO.SYSSERVERS WHERE SRVNAME = @LINKEDSERVERNAME) > 0 EXEC sp_dropserver @LINKEDSERVERNAME, 'droplogins' SET @SERVER = ? SET @USER = ? SET @PASSWORD = ? SET @DATABASE = ? EXEC sp_addlinkedserver @server = @LINKEDSERVERNAME, @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @SERVER, @catalog = @DATABASE EXEC sp_addlinkedsrvlogin @LINKEDSERVERNAME, 'false', NULL, @USER, @PASSWORD" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
April 8, 2009 at 5:16 am
Instead of Variable Mapping i tried with hard coded values like below, but still i got differenr error , :
IF (SELECT COUNT(*) FROM MASTER.DBO.SYSSERVERS WHERE SRVNAME = 'TEST') > 0
EXEC sp_dropserver 'TEST', 'droplogins'
DECLARE
@SERVER VARCHAR(8000),
@USER VARCHAR(8000),
@PASSWORD VARCHAR(8000),
@DATABASE VARCHAR(8000)
SET @SERVER = 'BLRDXP-RUPPUNDA'
SET @USER = 'Raghav'
SET @PASSWORD = 'admin'
SET @DATABASE = 'TEST_CMEA380'
EXEC sp_addlinkedserver
@server = 'TEST',
@srvproduct = '',
@provider = 'SQLOLEDB',
@datasrc = @SERVER,
@catalog = @DATABASE
EXEC sp_addlinkedsrvlogin 'TEST', 'false', NULL, @USER, @PASSWORD
and the error i got was
[Execute SQL Task] Error: Executing the query "IF (SELECT COUNT(*) FROM MASTER.DBO.SYSSERVERS WHERE SRVNAME = 'TEST') > 0 EXEC sp_dropserver 'TEST', 'droplogins' DECLARE @SERVER VARCHAR(8000), @USER VARCHAR(8000), @PASSWORD VARCHAR(8000), @DATABASE VARCHAR(8000) SET @SERVER = 'BLRDXP-RUPPUNDA' SET @USER = 'Raghav' SET @PASSWORD = 'admin' SET @DATABASE = 'TEST_CMEA380' EXEC sp_addlinkedserver @server = 'TEST', @srvproduct = '', @provider = 'SQLOLEDB', @datasrc = @SERVER, @catalog = @DATABASE EXEC sp_addlinkedsrvlogin 'TEST', 'false', NULL, @USER, @PASSWORD" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
please can anyone guide me ?
April 8, 2009 at 8:47 am
When configuring the Execute SQL Task, set the resultSet to "None" and Bypass Prepare to "True"
and you should be fine. 🙂
Try this with your initial code.
Cheers!
April 9, 2009 at 1:36 am
After setting result set to None , the hard coded query worked fine but not variable mapped query.
any idea why variables are not mapping ?
April 9, 2009 at 4:40 am
after working for some time , i was able to find that
parameters passed to a query will work fine
but parameter passed to a Declared variable was giving syntax error ?
any idea why this is happening ?
April 9, 2009 at 8:35 am
Did you set Bypass Prepare to "True"?
April 12, 2009 at 11:02 pm
Thanks a lot....
after setting ByPass Prepare to TRUE it worked exactly fine as you mentioned.
last time i missed that one..sorry it was my mistake.
and again thanks thanks a looooooooooooooooot 😀
July 8, 2009 at 9:13 pm
where or how to set the bypass prepare to true?
July 8, 2009 at 9:14 pm
where or how to set the bypass prepare to true?
August 7, 2009 at 11:57 am
Setting ByPassPrepare=FALSE(TRUE)
Right click on the Execute Sql Task Editor. Go to properties.
In the 'general' group, it should be the last item.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply