.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Linked server and sensitive to register name of table. Problem with UPDATE.

Posted By:      Posted Date: September 07, 2010    Points: 0   Category :Sql Server
Hi All. I try to work with table with "sensitive to register" name through Linked Server (MSSQL 2005/2008) and get the problem with UPDATE statement. Reason: MSSQL generates UPDATE statement with "un-quoted" table name. With SELECT/INSERT/UPDATE - no any problems. ----- Linked Database Information: 1. Firebird 2.5 2. OLEDB Provider: IBProvider v3 3. Database dialect: 3 Metadata: CREATE GENERATOR "GEN_ID_TableWithMixName1"; CREATE TABLE "TableWithMixName1" ( TEST_ID INTEGER NOT NULL, "Col" VARCHAR(100), DUMMY_COL INTEGER, CONSTRAINT "PK_TableWithMixName1" PRIMARY KEY (TEST_ID) ); CREATE TRIGGER "BI_TableWithMixName1_TEST_ID" FOR "TableWithMixName1" BEFORE INSERT AS BEGIN IF(NEW."TEST_ID" IS NULL)THEN NEW."TEST_ID" =GEN_ID("GEN_ID_TableWithMixName1",1); END; ------- MSSQL Test 1. MSSQL: select * from IBP_TEST_FB25_D3_V3...TableWithMixName1; IBProvider: Command_Execute   SELECT "Tbl1002"."TEST_ID" "Col1004",         "Tbl1002"."Col" "Col1005",         "Tbl1002"."DUMMY_COL" "Col1006"   FROM "TableWithMixName1" "Tbl1002" No Problem ------- MSSQL Test 2. MSSQL: delete from IBP_TEST_FB25_D3_V3...Tabl

View Complete Post

More Related Resource Links

Problem doing an update to a table in sql server


I have a form that is being populated with data from a sql table.  The user can update via textboxes information on the form and click update.  The data should then be updated back to the table.

For some reason, When I add the parameters I need for the query, it is not grabbing the update from my textbox, it is showing the value that was populated upon load.  I tried to change the customer's phone number.  When I run my code it is pulling in the original phone number and not picking up my change.  Any ideas??

Here is my code:

//Define query
        string sql = "UPDATE Customer SET FirstName=@FirstName, LastName=@LastName, Email=@Email, Password=@Password, Address1=@Address1, Address2=@Address2, City=@City, State=@State, Zip=@Zip, Phone=@Phone, Fax=@Fax Where CustomerID=@CustomerID";

        //Declare the Command
        SqlCommand cmd = new SqlCommand(sql, Conn);

        //Add the parameters needed for the SQL query
        cmd.Parameters.AddWithValue("@CustomerID", Request.QueryString["ID"]);

Linked Server. DBTYPE_DBTIME2. Problem with Precision and Scale

Hi I added to my OLEDB provider (for IB/FB) the support of DBTYPE_DBTIME2 (from MSSQL Native Client) FB/IB supports a data type "TIME" with four digit in fraction of second. By other words: "hh:mm:ss.ffff" So, I define Precision=13, Scale=4 When I try to work with this DBTIME2-column through linked server, MSSQL 2008 returns the error: Msg 7356, Level 16, State 1, Line 7 OLE DB "LCPI.IBProvider.3" "IBP_TEST_FB25_v3" . "COL_TYPE_TIME"  .... "LENGTH" at compile time: 5, at execute time: 4. Ok. I change the Precision to 16 and Scale to 7 (as in SQLNCLI) With this settings, MSSQL 2008 can to work (read/write) with my DBTIME2-column. I tried to use new column flags [DBCOLUMNFLAGS_SS_ISVARIABLESCALE]. With (16/7) - no problem. With (13/4) - I get the same error. Question: How to define my DBTIME2-column with required precision and scale (13/4)? --- I tried to explore how SQLNCLI works with the time(4) column (precision:13, scale:4) But did not found anything unexpected, except DBCOLUMNFLAGS_SS_ISVARIABLESCALE

Problem when adding Linked Server...

Hi @all,I try to add a new Linked Server to my DB in Microsoft SQL Server Management Studio Express...The following error is returned (SERVER2 is the Linked Server to add): There is no remote user 'sa' mapped to local user '(null)' from the remote server 'SERVER2'. (.Net SqlClient Data Provider)I entered the user sa and the correct password for SERVER2 but it doesen't work...When I connect to SERVER2 in Management Studio with the same infos it works fine...So please help...

Linked Server Problem

We migrated our production SQL Server 2000 database to a new machine and all seems to be working well except for our linked server on our SQL Server 2005 instance that points to our SQL Server 2000 database that was just moved.  We get the following error when we try to query a table on the 2000 database.  I have read this article in the Books online but that does not seem to apply to us ( http://msdn2.microsoft.com/en-us/library/ms175496.aspx ). OLE DB provider "SQLNCLI" for linked server "SQLPRD-DBS" returned message "Communication link failure". Msg 233, Level 16, State 1, Line 0 Named Pipes Provider: No process is on the other end of the pipe. Msg 18456, Level 14, State 1, Line 0 Login failed for user 'sa1'.   Anyone have any ideas? Thanks!!!

Linked Server UPDATE issue


Hello forum,

I'm doing a simple data load between two linked servers, and everything is working fine until I include an UPDATE statement, that's when I receive the following error message.

OLE DB provider "SQLNCLI10" for linked server "ServerName" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

Msg 7391, Level 16, State 2, Line 28

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "ServerName" was unable to begin a distributed transaction.

the Distribute Transaction Coordinator service is running on all machines, and I'm able to do INSERTs between the two servers. Here is a part of the code I'm using. It works fine if I don't include the UPDATE. 

			,FirstName VARCHAR(MAX)
			,LastName VARCHAR(MAX)

problem with sql server update


I have a problem with updating records in a database table. Background: The user can fetch data that belongs with an image. The
data is displayed in a form. In this form the user can add/change data and then save this to the database. This part works fine.

My next step is to let the user click a checkbox in the form to choose one image. The chosen image gets the value "1" in column Publish. This also works fine. 

Here's my problem: I also want to let the user choose another image, this image does get a "1" in column Publish. But as the change is saved I want to update the column Publish so that the rest of the images gets a "0" in column Publish (there can be only one). This does not work for me. I'm not sure if the error lies in the sql UPDATE or somewhere else. Thankful for help!

protected void ButtonImgTextToDB_Click(object sender, EventArgs e)
           string BildID = TextBoxStartPictID.Text;
           string Rubrik = TextBoxHeadLineStart.Text;<

Save/Update/Delete Sql Server data base table using Sharepoint Designer


Can some one help me to achieve below task

Save/Update/Delete Sql Server data base table using Sharepoint Designer.

In a trigger can up insert data into a table located on a linked server?


EXEC master.dbo.sp_addlinkedserver @server = N'ServerB', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ServerB',@useself=N'False',@locallogin=NULL,@rmtuser=N'lnklogin',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'ServerB', @optname=N'query timeout', @optvalue=N'0'

Another Oracle linked server problem


I have trouble making a linked server from a SQL Server 2008 R2 64bit to an Oracle database.  I have a linked servers on a few other PCs working fine to this same Oracle server.

On this particular one, I configured the following and Command Test connection shows that it is working fine:


But when I tried to create the linked server as shown below, it fails.

The error is: 

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot initialize the data source object of OLE DB provider "OraOLEDB.Oracle" for linked server "CARE".
OLE DB provider "OraOLEDB.Oracle" for linked server "CARE" returned message "ORA-12154: TNS:could not resolve the connect identifier specified". (Microsoft SQL Server, Error: 7303)

I can't find what other parameters I could adjust.  How should I go about solving this problem.  Thanks.

Very strange Linked Server problem



I've got a server setup with SQL2008R2. It connects (linked server) to another SQL server which runs SQL2005. The link works fine in management studio, and it also works ok in code (where I use it to query the data). But after a few minutes of inactivity, suddenly the link begins to fail in code (SQLException; System.Data.SqlClient.SqlException: SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF]. ). I then open up management studio, click "Test connection"; management studio says it's ok. Running the code again, and it suddenly works fine for a few minutes, untill the same problem appears.

Seems like a bug, but is there a solution for this problem?

MCTS in Web Application Development in .NET 2.0

Very-NewBie Linked Server Problem


I'm having a problem selecting from an Excel linked server...


1. Create a new simple Excel file at C:\DeleteMe.xls and add a couple column headers and a row of data. e.g.



ColA | ColB |


123  | 456  |


How update SQL Server table through Excel?



for example is table:

Name     Age

John       28

Mark      30


I can create Excel with the same data.

Is it possible when user change data in Excel it would updated in SQL Server Database?

For example:

John     29


I only need to update. There won't be new records. I know that possible to create SSIS package and then run JOB through Reporting Services. But i would like update directly through Excel.


Linked Server using Microsoft.Jet.OLEDB.4.0 problem


Hi all,

I'm using following code to create linked server and then access data

EXEC sp_addlinkedserver N'XLS',

'Jet 4.0',




'Excel 5.0;'

EXEC sp_addlinkedsrvlogin N'XLS', FALSE, NULL, Admin, NULL


SharePoint Server 2010 October Cumulative Update My site Problem


Hi everyone

I have created a small farm with a default installation. I have 2 web application configured using Claims authentication.

The first web application is hosting a site called http://sharepoint.domain.com running a standard team site.
The second web application is hosting a site called http://my.sharepoint.domain.com and is running the Mysite.

When I install the October cumulative update the My Site links vanish and the My site is no longer accessible showing a sharepoint Access Denied page.

Has anyone else experience this? Do you know how to fix it?


Chris Hughes

Execute stored procedure on linked DB2 server from MS SQL 2008 SP1 64 bit problem

Hi everybody. I am trying to execute stired procedure on linked DB2 server from MS SQL 2008 x64
I installed IBM Access client x64 and on provider tab showed up 3 providers IBMDASQL,IBMDA400,IBMDARLA
I installed linked server as shown on this two links:


I installed linked server using all this 3 providers

    @srvproduct=N'DB2 UDB for iSeries',
    @provider=N'IBMDASQL',-- provider for example
    @datasrc=N'ASTEST', -- mydatasource
exec sp_addlinkedsrvlogin DB2,false,null,'telebank','password'

I can run procedure from the extended stored procedure, but when i try to execute it as shown on the linkes above i get the following error:
Could not execute statement on remote server 'DB2'.

  @branch    as varchar(4),
  @cli   as varchar(6),
  @suffix    as varchar(3),
  @date1     a

Linked server login problem -- Msg 15007


I need to have an access database as a linked server, which can be queried from a web application.  It's all set up on an old machine with 2003 server and SQL 2005 express, but now I am trying to migrate to a new machine with 2008 everything.  The linked server itself was not a problem, but creating a login refuses to work.  I tried the following:

sp_addlinkedsrvlogin 'nameoflinkedserver', false, 'NT AUTHORITY\IUSR', 'Admin', NULL

which is roughly what worked on the old system.  Alas, it fails with:

Msg 15007, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 76
'NT AUTHORITY\IUSR' is not a valid login or you do not have permission.

That does seem to be a valid login; 'NT AUTHORITY\IUSR' is listed as one of the server's logins, and is the one that gives the web application access to the SQL server database itself.  I am logged in as administrator on the machine and can do everything else regarding SQL Server, so it's odd that I would not have permission for this.  Does anyone have a guess why this happens?

Can't update table oracle using a liked server on sql server


I have a stored procedure in SQL2005 that updates data base oracle's tables by a linked server. When run into a transaction I get the following error message:

Msg 7391, Level 16, State 2, Procedure CSGICONTABILINS, Line 69

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "LS_JBSQLT01_SGI_GLHI" was unable to begin a distributed transaction.


ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend