Changing a linkedserver datasource

Hi all,

I'm trying to change the datasource of a SQL Server LinkedServer using SMO

I've got the relevant linkedserver as an object, changed the DataSource property, and selected it again to confirm the change.
However, the change is lost as soon as the object is destroyed.
Looking at BoL I think I need to be using alter() method of the linkedserver class but I just get an error
Alter failed for LinkedServer '<servername>'.

any ideas how I should be using this class to do what I want?

I can post my script (PowerShell) if it would help.

Thanks, Robin.

Hi Robin,

The DataSource property of an existing LinkedServer cannot be modified. To modify the DataSource, you need to drop and re-create the linked server.

Thanks,

Kuntal

|||Hi Kuntal,
Could you demonstrate how I would do this in code (any language)
I have tried various ways but either manage to build the linkedserver object with the properties of the old server and then get an error when i try to create it, or i can create it but then not set the properties.

Many thanks,

Robin.
|||

Hi Robin,

Here is a small sample which might be of some help.

Server server = newServer("localhost");

LinkedServer lserver = newLinkedServer(server, "testServer");

lserver.DataSource = "Server1";

lserver.ProductName = "SQLNCLI";

lserver.ProviderName = "SQLNCLI";

lserver.Create();

// now you need to change the datasource and keep the rest of properties the same

lserver.DataSource = "Server2";

StringCollection script = lserver.Script();

lserver.Drop();

server.ConnectionContext.ExecuteNonQuery(script);

The above code first creates a linked server and then executes the script to create the same with a different datasource. Now you can create a new linked server by running that script after dropping the original server.

Hope that helps.

Thanks,

Kuntal

|||Many thanks, that's brilliant!

So just to check I'm understanding correctly, another way to express your solution would be, assuming there was a linked server called TESTLS already defined:

Server server = newServer("localhost");

LinkedServer lserver = server.linkedservers.item("TESTLS")

// now you need to change the datasource and keep the rest of properties the same

lserver.DataSource = "Server2";

StringCollection script = lserver.Script();

lserver.Drop();

server.ConnectionContext.ExecuteNonQuery(script);

Changing a linkedserver datasource

Hi all,

I'm trying to change the datasource of a SQL Server LinkedServer using SMO

I've got the relevant linkedserver as an object, changed the DataSource property, and selected it again to confirm the change.
However, the change is lost as soon as the object is destroyed.
Looking at BoL I think I need to be using alter() method of the linkedserver class but I just get an error
Alter failed for LinkedServer '<servername>'.

any ideas how I should be using this class to do what I want?

I can post my script (PowerShell) if it would help.

Thanks, Robin.

Hi Robin,

The DataSource property of an existing LinkedServer cannot be modified. To modify the DataSource, you need to drop and re-create the linked server.

Thanks,

Kuntal

|||Hi Kuntal,
Could you demonstrate how I would do this in code (any language)
I have tried various ways but either manage to build the linkedserver object with the properties of the old server and then get an error when i try to create it, or i can create it but then not set the properties.

Many thanks,

Robin.
|||

Hi Robin,

Here is a small sample which might be of some help.

Server server = new Server("localhost");

LinkedServer lserver = new LinkedServer(server, "testServer");

lserver.DataSource = "Server1";

lserver.ProductName = "SQLNCLI";

lserver.ProviderName = "SQLNCLI";

lserver.Create();

// now you need to change the datasource and keep the rest of properties the same

lserver.DataSource = "Server2";

StringCollection script = lserver.Script();

lserver.Drop();

server.ConnectionContext.ExecuteNonQuery(script);

The above code first creates a linked server and then executes the script to create the same with a different datasource. Now you can create a new linked server by running that script after dropping the original server.

Hope that helps.

Thanks,

Kuntal

|||Many thanks, that's brilliant!

So just to check I'm understanding correctly, another way to express your solution would be, assuming there was a linked server called TESTLS already defined:

Server server = new Server("localhost");

LinkedServer lserver = server.linkedservers.item("TESTLS")

// now you need to change the datasource and keep the rest of properties the same

lserver.DataSource = "Server2";

StringCollection script = lserver.Script();

lserver.Drop();

server.ConnectionContext.ExecuteNonQuery(script);

Change the InsertCommand of a datasource before insert

Hi,

I am trying to set the InsertCommand of a SqlDataSource prior to a new record being created in DetailsView. It has to be changed because certain fields are not being used in one scenerio and it is causing Null's to be written to the database.

Here is what I have tried:

I set the Insert command on page Load:

protectedvoid Page_Load(object sender,EventArgs e)

{

SqlDataSource3.InsertCommand =

"Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

}

But I don't want to insert the "extraValue if a certain condition is true so I want to change the insert command to SqlDataSource3.InsertCommand ="Insert into table (name,realName,type) VALUES (@.name,@.realName,@.type)

I tried to do that in the following:

protectedvoid DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)

{

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

if (e.Exception !=null)

{

ErrorMessageLabel.Text =

"An error occured while entering this record. Please verify you have entered data in the correct format.";

e.ExceptionHandled =

true;

Response.Write(e.Exception);

}

GridView1.DataBind();

GridView1.DataBind();

}

But it doesn't seem to change,

Any Help would be appreciated,

Doug

Excuse me, maybe I missed it, but where did you change the insert command of SqlDataSource3? I saw you assign the same string to the insert command of SqlDataSource3 twice:

The first time:

protected void Page_Load(object sender, EventArgs e)

{

SqlDataSource3.InsertCommand =

"Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

}

And the second time:

protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)

{

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)";

|||

Sorry I made the same reply by mistake

|||

Some logic for this:

On you page load event:

IF condition1 is true THEN

SqlDataSource3.SelectCommand=" SELECT name,realName,type,extraValue FROM table"

SqlDataSource3.InsertCommand =

"Insert into table (name,realName,type,extraValue) VALUES (@.name,@.realName,@.type,@.extraValue)"

ELSE

SqlDataSource3.SelectCommand ="SELECT name,realName,type FROM table"

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type) VALUES (@.name,@.realName,@.type)

END IF

}

|||

Sorry, the second should be:

protected void DetailsView1_ItemInserted(Object sender, System.Web.UI.WebControls.DetailsViewInsertedEventArgs e)

{

SqlDataSource3.InsertCommand ="Insert into table (name,realName,type) VALUES (@.name,@.realName,@.type)

That is actually what I tried. But it doesn't seem to change..

|||

Thanks for taking a look,

Unfortunately the condition doesn't change on page load, it changes when a radio button is clicked within this page:

protectedvoid Type_Changed(object sender,EventArgs e)

{

string aha = ((RadioButtonList)DetailsView1.FindControl("type")).SelectedItem.Value;if (aha =="Mortgage")

{

DetailsView1.Fields[6].Visible =

true;

DetailsView1.Fields[7].Visible =

true;

DetailsView1.Fields[8].Visible =

true;

}

else

{

DetailsView1.Fields[6].Visible =

false;

DetailsView1.Fields[7].Visible =

false;

DetailsView1.Fields[8].Visible =

false;

}

}

I tried to change the Sql Insert command here but it doesn't seem to work.

Thanks again,

Doug

Change Report Datasource

I have a c# assembly that uses the reporting services webservice to render reports.

Every user accessing this component needs to render this report passing different datasource to it in order to browse data in different databases (es.: test databse server, production database server)

Is it possible to create a linked report and change the connection only to it?

Other suggestions?

Thanks

Hi,

you can change the connection string property depending on the user (role or something you want to differentiate with). I don′t know if that is actually a solution for your problem but this is worth a try. using an expression could be something like

="Data Source=" & IIF(SomeExpression,"TestServer","ProductionServer") (...Rest of the connection string here...)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Yes, it works great with reporting services 2005, but i forgot to tell you that i have to do the same with reporting services 2000...that's the problem. Is it possible under 2000 to do the same thing? If "No", are there other ways to programmatically call a report with dinamyc conection string.

The only solution i've found is as follows:

==========

rs.render --> obtain the byte stream from REPORT_1

rs.CreateReport --> create REPORT_2 using the stream of REPORT_1

rs.GetReportDataSources(REPORT_2) --> to obtain datasource of REPORT_2

Modify the property ".connectString" of this DataSourceDefinition

rs.render to render the newly created report

finally delete the new report.

===================

In this solution, copying the report slows down execution time i suppose...... Another solution might be to directly modify datasourcedefinition of REPORT_1 but if two or more users try to call the same report with different connectionstrings i think there might be problems.

Any suggestions?

|||This is something I would like to do. Where exactly would I enter that expression? In VS Report Designer, when I create a new Data Source, the "fx" button is grayed out. Is that where I should be entering this expression? Is there a reason why the builder is disabled?

Thanks,|||

With Reporting Services 2005, open the report in the solution and switch from design to code view (xml structure) and modify connectstring node as follows:

<ConnectString>="data source=" &amp; Parameters!ServerName.Value &amp; ";initial catalog=Northwind;"</ConnectString>

You also have to create the parameter "ServerName".

If you want, change also the Catalog Name using another parameter...

With reporting services 2000 you cannot do the same...try reading my code example reading abow...that works fine for me

Change password in ODBC..?

Hi all,
I have an Access appl. with linked SQL-server tables.
The user have a SQL-server login from a ODBC datasource.
How can I get the user to change password themselves after a temporary
password?
Kent J.
Hi Kent,
I am not sure if I understand the problem but, if the password of the login
was changed on SQL Server, then you will need to find the ODBC Data Source
Name and update its password too. Go to Control Panel, Adminstrative Tools
and Data Sources (ODBC).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Kent J" wrote:

> Hi all,
> I have an Access appl. with linked SQL-server tables.
> The user have a SQL-server login from a ODBC datasource.
> How can I get the user to change password themselves after a temporary
> password?
> Kent J.
>

Change password in ODBC..?

Hi all,
I have an Access appl. with linked SQL-server tables.
The user have a SQL-server login from a ODBC datasource.
How can I get the user to change password themselves after a temporary
password?
Kent J.Hi Kent,
I am not sure if I understand the problem but, if the password of the login
was changed on SQL Server, then you will need to find the ODBC Data Source
Name and update its password too. Go to Control Panel, Adminstrative Tools
and Data Sources (ODBC).
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Kent J" wrote:

> Hi all,
> I have an Access appl. with linked SQL-server tables.
> The user have a SQL-server login from a ODBC datasource.
> How can I get the user to change password themselves after a temporary
> password?
> Kent J.
>