How to write this query so that I can pass a variable (from a cursor) for the were ORDER_ID =.
UPDATE OPENQUERY ([DISPATCHER_LIVE], 'select * from DCSDBA.ORDER_HEADER where ORDER_ID = ''405119-RM18''')
SET CONSIGNMENT = 'UNROUTED'
What's up with this?
This takes like 0 secs to complete:
update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
Updating the sp's fixed the error I was getting but the update takes forever on the linked server. Any way to speed things up?
I just reference the server.db.dbo.table in the update statement.
TIA
I know how to do a select statement using linked servers but I need to update a table in Oracle using SQL Server.
For example:
SELECT * FROM OPENQUERY(Oracle, 'SELECT * FROM TEST')
I now need to update TEST using a table in SQL Server. Can someone help?
Hi!
Please
I got the following Error Message when trying to update a AS400 Database
"(Table Name) on (Data Source) not valid for operation"
My question is? Does Sql Server require journaling the files? How Should I change this option?. Please Help!
I did try to fix the above error, but then I got a new message:
OLE DB provider 'MSDASQL' supplied inconsistent metadata. The object '(user generated expression)' was missing expected column 'Bmk1000'.
Could you help me?
I will appreciate your help.
Thanks in advance
Ivette V
Does Anyone know what this means?
I run the query in the attached file on multiple servers with success but some of them refuse to cooperate. I've tried recreating the linked server on the problem servers but this doesn't help. All I want to do is check db file size and free space and get it to work. Why does it have to be so damn complicated?
Any ideas welcome - including an entirely different way of doing this.
The script and table definitions in question are attached
Could not open table '"Helpdesk_New"."dbo"."LogStats"' from OLE DB provider 'SQLOLEDB'. The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowset returned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocate VALUE=True STATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_IRowsetChange VA...
I am getting an error trying to update a table in DB2 via SQL Server using a Linked Server. I can query the table using OPENQUERY but not via four-part name.
I can do this...
SELECT
Select * from OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn = 2')
When I try to Query using four-part names I get the following error...
7399 OLE DB Provider 'MSDASQL' reported an error. The provider does not support the necessary method.
UPDATE
When I try to update using this...
Update OpenQuery(db2link, 'Select frst_nm from yccfssc9.person where id_prsn=2') Set frst_nm = 'Fred'
I get the following error...
The OLE DB provider 'MSDASQL' indicates that the object has no columns.
When I try to update using four part names I get the error that says that the provider does not support the necessary method.
OTHER INFO
I was originially using version 6.1 of the db2 client (db2odbc.dll) but tried the 7.1 client and got the same error.
I believe we are using version 6.1 of db2 connect.
The mainframe db2 is version 7.0 release 1.0
Any help would be greatly appreciated. I referenced Microsoft Knowledge Base Article #270119 to get the workarounds that I attempted...
Gary
Hi,I'm using sql server 2000 sp4.I've 2 databases linked, an instance and my local.I'm getting two different errors when trying to update the remote table(local server) from the instance.There is only one row of data in the table with an identity field.1st sql:-UPDATE [local].[database].dbo.NUMBERS SET [f 1]=3This gives me the error:-Server: Msg 8180, Level 16, State 1, Line 1Statement(s) could not be prepared.Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near '1'.If I was to remove the space from [f 1] and use [f1] it would workfine."select [f 1] from [dev001].[fashion Master].dbo.numbers"will return the correct valueAny Ideas ?2nd sql:-UPDATE [local].[database].dbo.NUMBERS SET [field1]=isnull([field1],0)+1This gives me the error:-Server: Msg 7306, Level 16, State 2, Line 1Could not open table '"fashion Master"."dbo"."NUMBERS"' from OLE DBprovider 'SQLOLEDB'. The provider could not support a row lookupposition. The provider indicates that conflicts occurred with otherproperties or requirements.[OLE/DB provider returned message: Multiple-step OLE DB operationgenerated errors. Check each OLE DB status value, if available. No workwas done.]OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IOpenRowset::OpenRowsetreturned 0x80040e21: [PROPID=DBPROP_BOOKMARKS VALUE=TrueSTATUS=DBPROPSTATUS_CONFLICTING], [PROPID=DBPROP_COMMANDTIMEOUTVALUE=600 STATUS=DBPROPSTATUS_OK], [PROPID=Unknown PropertyIDVALUE=True STATUS=DBPROPSTATUS_OK], [PROPID=DBPROP_IRowsetLocateVALUE=True STATUS=DBPROPSTATUS_CONFLICTING],[PROPID=DBPROP_IRowsetChange VA...If I was to remove the isnull part, then it will work okAny ideas
View 3 Replies View Related
I have 2 database servers ( 2 hardware-servers : A and B ) and I ve written a trigger for update in server A to execute insert statement in server B through linked servers, is there other way to achieve this without linked servers?? All using T-SQL.
Best Regards
Joseph
I have a linked server set up on my local SQL 2000 instance. I try and run an update against an SQL 2005 database and it take 29 seconds. I checked the execution plan and it says it takes the entire time on the Remote Scan. Is there something I need to do to speed this up? There is an index on the PK that I am searching against.
View 2 Replies View Related
I've got the output from a cursor that generates the following sql statement.
Update SERVERNAME.DATABASENAME.dbo.TABLENAME set [update] = 'Y', status = NULL, completed = NULL where trigger_id = 10255
The statement generates the following error if the statement is run remotely
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'update'.
But the statement executes without error as formatted if run directly on the linked server.
Both servers are running 2000 sp 3a. The servers will be converted to 2005/2008 without patching to sp4.
Hi there,I'm pretty new to SQL and am having some porblems with a linked server.I have a table on a SQL server which stores employee information.I also have a view on a linked server which stores the same information.What I would like to happen is, whenever the view changes on the linkedserver I want the information to be changed in the table on my server.I've been trying to write a trigger to do this, but have had noluck so far.Can anyone help me?ThanksSimon--Posted via http://dbforums.com
View 1 Replies View RelatedWhen trying to update records in a Sybase 11 ODBC linked server got this error:
View 5 Replies View RelatedWe have 2 instances. One is sqlgpscluster and another is [sqlcdsclustersqlcds]. We have created linked server between sqlgpscluster and [sqlcdsclustersqlcds]. We run the below query on sqlgpscluster instance
UPDATE [sqlcdsclustersqlcds].CDSBusiness.dbo.tblInsertNowAppMonitoring
SET NoCabsDateTime = GETDATE(),City=@City
,Area = dbo.fn_FindAreaSubAreaBasedOnLatLong(@PickUpLat,@PickUpLng)
WHERE CustomerMobileNo=@CustomerMobileNo
We are seeing the below error:
The OLE DB provider "SQLNCLI" for linked server "sqlcdsclustersqlcds" could not UPDATE table "[sqlcdsclustersqlcds].[CDSBusiness].[dbo][tblInsertNow AppMonitoring]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.
UPDATE CD SET col1=SR.col1,col2=SR.col2,col3=SR.col3,col4=SR.col4,col5=SR.col5,col6=SR.col6,col7=SR.col7,
col8=SR.col8,col9=SR.col9,col10=SR.col10
FROM LNKSQL1.db1.DBO.Table1 CD
join Table2 USRI on USRI.col00 = CD.col00
join table3 SR on USRI.col00 = SR.col00
Here, I'm trying to tun this from an instance and do a remote update. col00 is a primary key and there is a clustered index that exists on this column. When I run this query, it does a 'select * from tabl1' on the remote server and that table has about 60 million rows. I don't understand why it would do a select *... Also, we migrated to SQL 2005 a week or so back but before that everything was running smooth. I dont have the execution plan from before but this statement was fast. Right now, I can't run this statement at all. It takes about 37 secs to do one update. But if I did the update on a local server doing remote joins here, it would work fine. When I tried to show the execution plan, it took about 10 mins to show up an estimated plan and 99% of the time was spent on Remote scan. Please let me know what I can do to improve my situation. Thank you
In an ASP, I have a dynamically created SQL statement that amounts to "SELECT * FROM Server1.myDB.dbo.myTable WHERE Col1 = 1" (Col1 is the table's primary key). It returns the data immediately when executed.
However, when the same record is updated with "UPDATE Server1.myDB.dbo.myTable SET Comments = 'blah blah blah' WHERE Col1 = 1", the page times out before the query can complete.
I watched the program in Profiler, and I saw on the update that sp_cursorfetch was being executed as an RPC once per each row in the table. In a table of 78000 records, the timeout occurs well before the last record is fetched, and the update bombs.
I can run the same statements in Query Analyzer from a linked server and have the same results. The execution plan shows that a Remote Query is occurring on the select that returns 1 row, and a Remote Scan is taking place on the update scanning 78000 rows (I guess this is where all the sp_cursorfetch calls are happening...?).
How can I prevent the Remote Scan? How can I prevent the execution of the RPC sp_cursorfetch for each row in the remote table?
Thank you!
I'd like to modify the dates within this where clause to be dynamic, building the date depending on the current year, but everything I try doesn't seem to be syntactically correct.
SELECT *
FROM Openquery(LS_CIS, 'select * from BI_WRKFLW_TASKS where (BI_EVENT_DT_TM>=''1/1/2011'' and (BI_NEEDED_DT_TM>=''1/1/2011''))OR (BI_EVENT_DT_TM>=''1/1/2011'' and BI_NEEDED_DT_TM is null)') AS derivedtbl_1
I'd like to replace ''1/1/2011'' in the where clause with something like:
CAST(CAST(YEAR (GETDATE())-4 AS varchar) + '-' + CAST(01 AS varchar) + '-' + CAST(01 AS varchar) AS DATETIME)
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
@server = N'Machine123Instance456',
@srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'
i have a table with the following values
iden nam status
----------- -------- --------------------
1 pp NULL
1 kk NULL
2 rr NULL
2 nn NULL
2 jj NULL
3 hh NULL
now i want to update the status cloumn in this table in such a way that the status colum = 'Status is' + iden + nam for all distinct values of iden from the table
how can we do this without using a cursor?
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
Hello folks!I'm have some trouble with a dynamic update system! What I want to do: 1. I want to send in the code behind from any .aspx file values and parameters to a .vb class2. Code (code behind in .aspx file):1 gridupdate.updategrid("tblFeedbackA", e.CommandArgument, "locked=@locked", "@locked", "int", "0") 'Call de .vb class2 getgridfeedback()
3. Code (code in .vb class)1 Public Shared Function updategrid(ByVal tblnaam As String, ByVal x As Integer, ByVal sql As String, ByVal parameters As String, ByVal sqltype As String, ByVal waarden As String 'translation: values)
2 Dim dynstr As String()
3 Dim dynstr2 As String()
4 Dim re As New Regex(",")
5 dynstr = re.Split(parameters)
6 Dim i As Integer
7 dynstr2 = re.Split(sqltype)
8 Dim dynstr3 As String()
9 dynstr3 = re.Split(waarden)
10
11 Dim sqlconn As New SqlConnection(ConfigurationManager.ConnectionStrings("DataBase").ConnectionString)
12 Dim sql2 As String = "UPDATE " & HttpContext.Current.Session("prefix").ToString & tblnaam & " set " & sql & " where id=" & x
13 Dim sqlcmd As New SqlCommand(sql2, sqlconn)
14 sqlconn.Open()
15
16 For i = 0 To dynstr.Length - 1
17 sqlcmd.Parameters.Add(dynstr(i), CType("sqldbtype." & dynstr2(i), System.Data.SqlDbType))
18 sqlcmd.Parameters(dynstr(i)).Value = dynstr3(i)
19 Next
20 sqlcmd.ExecuteNonQuery()
21 sqlconn.Close()
22 Return Nothing
23 End FunctionAll I want to do is to add the sqlparameters dynamically, but I don't find a way to do this :).Can you help me out?Thanks!
In Dot net 2.0 we change using SQLDataSource to Conect with SQLDB.
Now for My case ,the Select SQL is dynamic when differnece user and parameters to the page, So if I want to Update the data input by user,then I must give Update/insert/delelte SQL to SQLDatasource's InsertCommand /UpdateCommand/DeleteCommand .
How to Generate the Insert/update/delete command for the SQLDataSource ? as in dot net 1.1 can use SQLCommandBuilder to generate it,but SQLCommandBuilder just support DataAdeptor not for SQLDataSource, Could any body know how to do it when the SelectCommand is dynamic and need to update data back to DB after edit?
thanks a lot.
Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?
For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...
ALTER PROCEDURE u_sp_x
@semester int
AS
Select Semester@semester
From ThisTable
Just curious.
Thanks,
Steve Hanzelman
In general, What is the best approach in creating a dynamic updatestored procedure, that can handle recieving varying input paramters andupdate the approporiate columns.
View 6 Replies View RelatedHi AllI have a question about generating dynmamicly If Update() statement in atrigger..in My db, there is a table that holds some column names of an another table.for example;Columns Table-A: Col1, Col2, Col3, Col4,Col5Table-B: Col2, Col5 (The selected columns of Table A)Then, in the Trigger of Table-A I use;Select name from syscolumns where id=object_id('Table-A')fetch next from TableA_Cursor into @strColNamethen, I used a statement like this..if UPDATE(' + @strColName + ')But it gives "incorrect syntax" error..How can I write this line?Thanks alot in advance...--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedHiMy problem is as follows:I need to transmit data between two databases on the same server, but Ihave to use dynamic database names (they must be configurable). Forexample I need to achive sth like that:insert into [database1].[dbo].[table1](select columns from [table2])when database1 is not known at implementation stage.I know I can use EXEC @t_sql_code, but I wonder if there is any otherway? (OPENROWSET doesn't seem to suit my needs)Thanks in advanceAmfi
View 1 Replies View Related
OK, I'm trying to add some audit type information to a set of tables in my database. This involves adding 2 columns and sticking some data into those two columns. The columns themselves are added successfully, but I'm having troubles sticking any data into them - I'm getting a lot of errors. What I want to do is put a single character code in one column, and a datetime in the second. Here's the SQL I'm trying...
Code Snippet
SELECT @SQL = N'UPDATE ' + QUOTENAME(@TableName) + ' ' +
N' SET TRANSACTION_CODE = ''' + @TxCode +
N''', EFFECTIVE_DATE = ''' + @TxDateTime + ''';'
SELECT @Params = N'@TxCode varchar(1), @TxDateTime datetime'
EXEC sp_executesql @SQL, @Params, @TxCode, @TxDateTime
But this errors out with:
Msg 241, Level 16, State 1, Line 64
Conversion failed when converting datetime from character string.
I tried changing the datetime column to varchar, and CONVERT(varchar(30),@TxDateTime). That gets rid of the above error, but replaces it with the following (twice, I might add - one for each column?):
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Any help is appreciated - let me know if you need more info.
Hi,
I rewrote my working Sql statement to prevent Sql Injections. I copied some code I used in another project but this time I can't get it to work, possibly because it's an update statement and not an Insert one, which I used before.
Sorry for the boring question, but does anyone have a clue what's wrong with the syntax?
Here's the original code (I changed the parameter names for clarity and security):
Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) Dim strSQL As String = "Update MyTable Set " & typ & num & " = '" & pname & "' WHERE personID = " & fid Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
Here's the code from codebehind:
Dim conn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString) Dim strSQL As String = "Update MyTable Set (" & typ & num & ") Values (@" & typ & num & ") WHERE personID = " & fid Dim cmd As New SqlCommand(strSQL, conn) With cmd.Parameters .Add(New SqlParameter("@" & typ & num, pname)) End With TestLabel.Text = strSQL & " " & pname cmd.Connection.Open() cmd.ExecuteNonQuery() cmd.Connection.Close()
Here's my test message; first the sql, then the new string to be inserted:
Update MyTable Set (picturename) Values (@pname) WHERE personID = 2 2_adin.jpg
Here's my error code:
Line 1: Incorrect syntax near '('. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '('.
Source Error:
Line 489: TestLabel.Text = strSQL & " " & pnameLine 490: cmd.Connection.Open()Line 491: cmd.ExecuteNonQuery()Line 492: cmd.Connection.Close()Line 493: End If
I could understand it if I'd get an error in VWD Express for using dynamic variables, but they work correctly in the text message so I'm clueless. Any help is deeply appreciated!
Pettrer (VB, Sql Server 2000)
Hi, I'm having a problem with my code,I am doing the following - retrieving a field from a table, which is linked to another table, i'm only updating one of the fields in one of the two tables...
Code: ( text )
I'm trying to generate an update statement based off select statement results. A very basic example,
SELECT ListID FROM DListing WHERE Status = 2Results return 3 rows.
1234
2345
3456
How can I take those results and turn them into WHERE criteria for UPDATE statement?
Generated UPDATE statement should look like this.
UPDATE DListing SET Status = 1 WHERE ListID IN (1234,2345,3456)
I have started by creating a temp table to hold my SELECT results, but I don't know how to get those results into format for IN condition. Right now I get 3 UPDATE statements with 1 ListID in each IN condition.
CREATE TABLE #TempStatusUpdate(ListID INT)
INSERT INTO #TempStatusUpdate
SELECT ListID FROM DListing WHERE Status = 2
SELECT 'UPDATE DListing SET Status = 1 WHERE ListID IN (' + CONVERT(VARCHAR(30),ListID) + ') AND Status = 2'
DROP TABLE #TempStatusUpdate
I cant seem to reference columns within a linked Excel server
using MS SQL express
Code:
UPDATE dbo.Items
SET CCC=XL_SERVER.CCC
FROM XL_SERVER...sheet1$
WHERE BBB LIKE '7%'
i.e I want to update column CCC in my Items table from an Excel table column CCC where column BBB begins with 7
I have set up XL_SERVER correctly.
Help!