Dear Friends,
I have table contain 2000 out of those some are
duplicate when i select duplicate records by using Enterprise Manager
and make modification to one of those duplicate records the following
message flashes/display.
key columen information is insufficient or incorrect.Too many rows were
affected by update
pls suggest what is this and how to solve this problem
Thanks in advance
Dinesh Patwal
This is the case.... I would like to learn the statement that make therelation between these tables.Why? Cos these are separated in two different databases and if a usermake an update in a table from database X these changes must to beapplied in the other table in the another database:The tables are :Principal Database Name : Server Information 2004Table Name : ClientsFields : ID_Client, ClientSecondary Database Name : Index2003Table Name : ContratosFields : ID_Con, ID_Client, ClientI need to write a Trigger for Update the table Contratos everytime auser change the values in Clients.I´m using the follow Trigger :CREATE TRIGGER UPDate_Clients ON dbo.ClientsFOR UPDATEASupdate Contratosset Client = inserted.Clientfrom Clientsinner join inserted on Clients.Client = inserted.ClientWhen I update the register the follow message in the application raise :"Key column information is insufficient or incorrect. Too many rows wereaffected by update."If somebody can help me THANKS A LOT OF....Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 5 Replies View RelatedThis is the case.... I would like to learn the statement that make therelation between these tables.Why? Cos these are separated in two different databases and if a usermake an update in a table from database X these changes must to beapplied in the other table in the another database:The tables are :Principal Database Name : Server Information 2004Table Name : ClientsFields : ID_Client, ClientSecondary Database Name : Index2003Table Name : ContratosFields : ID_Con, ID_Client, ClientI need to write a Trigger for Update the table Contratos everytime auser change the values in Clients.I´m using the follow Trigger :CREATE TRIGGER UPDate_Clients ON dbo.ClientsFOR UPDATEASupdate Contratosset Client = inserted.Clientfrom Clientsinner join inserted on Clients.Client = inserted.ClientWhen I update the register the follow message in the application raise :"Key column information is insufficient or incorrect. Too many rows wereaffected by update."If somebody can help me THANKS A LOT OF....Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedI am using MADC 2.8 to connect to Sql Sever 2000.
The Connection string uses a File DSN.
I get few records into a Record set using Select Command. Then I update one Col in the Recordset and Updates the database table. I open the recordset in
I am getting "Insufficient base table information for updating or refreshing" Error some times. This Code executes in a Multi user Environment.
I tried to recreate this error condition on my local system by Deleteing/Updating the Reocrds from the table and even locking the table.
I get this error "Row cannot be located for updating. Some values may have been changed since it was last read."
Are both of these errors connected? or is there any way i can get rid of this error
The Code is somethng like this
Set objRs = New ADODB.Recordset
objRs.CursorLocation = adUseClient
objRs.Open strSql, SQLCON, adOpenDynamic, adLockOptimistic
If (Not objRs.BOF) And (Not objRs.EOF) Then
objRs.Fields("FLAG") = 1
objRs.Update
VAR1 = objRs.Fields("0")
Else
MsgBox ("No Reocrds exist")
End If
Thanks in Advance for any help
Apologies if this is in the wrong forum.
I've set up an email alert to an Operator who can only be alerted via email. When i view the history of this operator, it has a "most recent notification attempt" value against by net send and not against by e-mail. The value is the correct date of the latest email alert, but is just set against the wrong notification type.
Not a major bug for me, but does anyone else have this problem?
--------------------------------------------------------------------------
DDL statement for operator.
EXEC msdb.dbo.sp_add_operator @name=N'Rich',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N'rich@rich.net,
@category_name=N'[Uncategorized]'
Hiya!
I have a static, optimistic ADO recordset in VB6 selecting rows from a view definition which selects fields (includes all primary keys!) from two tables using a right join on the table whose fields I absolutely need. My update affects only one table (the right one), yet I am getting an error "Insufficient key column info for updating or refreshing", NativeError is 1007, which does not match any SQL error in BOL, and source gives me Microsoft Cursor Engine, although my cursor type is fine for updating. Any clues?
Thanks,
Sarah
I ran this query against the pubs database and it runs successfully
ALTER TABLE publishers ALTER COLUMN state CHAR(25)
I change the table & field names for my db as follows:
ALTER TABLE customquery ALTER COLUMN toclause CHAR(25)
and run against my database and I get the following error - Incorrect syntax near 'COLUMN'.
My column name is correct - I don't know why it would run fine against pubs, but not my db. I do not have quoted identifiers turned on. I have tried using [] around my column name [toclause], but that didn't change anything. Any help would be appreciated.
Thanks.
What query should I run?
1. To determine whether a column is a rowguid or not using C# .NET 1.1
2. To add/modify column information and be able to set/change:
- Primary key
- Column Name
- Data Type
- Length
- Allow Null
- Default value
- Precision
- Scale
- Identity
- Identity Seed
- Identity Increment
- Row guid
Thanks a lot!
I have a table with a column in it called Date, which is of the type DateTime, and for the last two years I have been adding data which I found out was incorrect.
My dates are all a day in the future, so I need to reduce each date by one day.
I can easily use a select script to reveal the 25,000 rows which are all incorrect dates. But I can't figure out how to update each and every row to subtract one day from each date.
So where I have:
26/01/2005
I would like to have:
25/01/2005
and of course for every record. Obviously way too many to do manually :-(
Can anyone show me a script that will get what I'm after.
Tia
Tailwag
Okay, I'll admit it: I am not a Guru. Far from it. So here is one of the zillion tasks I cannot do - unfortunately it is one that I need to have done... :
I need a recordset containing the Name, Datatype, Size, Precision, and Name_of_parent_table of all columns in all non-system tables in a given database.
Most likely this can be accomplished with one or two SPs, but which ones? And how?
Is there any Kind Soul out there, perhaps?
Peter
Hi,
Is it possible to retrieve column information alone in the sp_columns command? I saw that it has an additional option "@column_name". But when I include it in my sql, it doesn't work the way I had expected it to..
I had tried sp_columns @table_name='mytable', @column_name='COLUMN_NAME'
I expect just the column names alone in the resultset. What am I doing wrong here?
Thanks and Regards,
Celia.
I am getting an error from the case part of the select statement below which reads 'Incorrect Column Expression' then it quotes the case statement. All I am trying to do is convert and return the weight value to kilos if it was entered in pounds.
SELECT Salesinv.Unique, Salesinv.SalesNo, Salesinv.PurchNo, Salesinv.SalesInvNo, Salesinv.InvValue,
(case when Salesinv.WUnits = 'Llb' then round(Salesinv.NettWeight/2.2046,0) else Salesinv.NettWeight end)
FROM Salesinv Salesinv
WHERE (Salesinv.Unique>=38397.3092 And Salesinv.Unique<=38537.39885)
Any help would be greatly appreciated, hopefully thanks in advance.
I am working with Excel, then within Excel I am using MS Query to query a database. I am trying to use the CAST function on a field with numbers (1,2 or 3 digits) so I can convert it to a text value with three digits, i.e. 1 would read 001, 12 would read 012, etc.
I am not using CAST in the design grid. Is this even possible?
I am modifying the underlying SQL code. Here is the line that is giving me trouble:
CAST(GL02GLF.GLF_SEQ_NUM as CHAR(3)) as “Sequenceâ€
Hi, all..
I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
Is there any sp for this or any query for this?
Thank you all...
Hello
I'm trying to get data from as400 server to sql server, trough a job.
The job executes a stored procedure that retrieves data from a view in an other database on the same server. That view gets the data from as400.
Now, when i run the mentioned stored procedure in the query analyzer, everything works fine, but when the stored procedure is executed from the scheduled job, it fails each time giving the error
"Could not get the column information from the OLE DB provider 'IBMDA400'. [SQLSTATE 42000] (Error 7350). The step failed."
on random points during that process (by that i mean the lenght of time it runs)
does anyone know what the diffrence is between executing an sp in query analyzer or using a job, keeping in mind retrieving data from as400?
thanks
Hi,
I need to provide a report that lists the databases in each instance, then the tablenames and the number of rows in each table.
Is there any easy way to do this in SQL 2005?
Cheers
Gregg
This works correctly in the Business Intelligence Dev Studio tool and on one of our servers, but fails on the other two servers. The two servers that do not work are current on their patches.
When a matrix has multiple rows and multiple columns, the highest level column subtotal is in the wrong scope. Say we have three row groups (r1, r2, and r3) and two column groups (c1, c2). All five groups have subtotals enabled. We can check the scope of every cell by setting the expression of our Detail/Value textbox like so:
=IIf(InScope("r1"),"r1, ","") &
IIf(InScope("r2"),"r2, ","") &
IIf(InScope("r3"),"r3, ","") &
IIf(InScope("c1"),"c1, ","") &
IIf(InScope("c2"),"c2, ","")
When run, the lowest level "detail" boxes should be in the scope of all groups: "r1, r2, r3, c1, c2,". The subtotal of r3 is in scope for everything except for itself (r1, r2, c1, c2,), and so on (see "Good server" screenshot).
This is how it should work, and it does work this way in the design tool and our one (development) server. The other two servers (which are, unfortunately, the Test and Production servers) have a problem: the subtotal of c1 (the column running furthest along the right) is ONLY r1. Always. Which results in some ridiculously large subtotals for detail rows.
We've tested this with various combinations of 2 - 5 column groups and 2 - 5 row groups and found no difference; the problem still hits the furthest right column in the exact same way. We've changed everything we can think of, and tried multiple reports built from scratch and using different data sources, but the results are still consistent.
Any ideas, explanations, or suggestions would be much appreciated.
Screenshots:
Matrix design: http://farm2.static.flickr.com/1010/1469081648_bc3cafe3dc_o.jpg
Good server: http://farm2.static.flickr.com/1055/1469081658_df8da1c261_o.jpg
Bad server: http://farm2.static.flickr.com/1058/1469081664_c56bc57c1a_o.jpg
STDEV() gives incorrect values with reasonable input.
I have a table filled with GPS readings. I've got a column LATITUDE (FLOAT) with about 20,000 records between 35.6369018 and 35.639890. (Same value to the first 5 digits of precision---what can i say, it's a good gps.)
Here's what happens when I ask SQL Server ("9.00.1399.06 (IntelX86)") to compute the standard deviation of the latitude:
// Transact-SQL StdDev function:
SELECT STDEV(LATITUDE) FROM GPSHISTORY
WHERE STATTIME BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
0
// Zero. ZERO??!?!!
//Let's re-implement Std Dev from the definition using other aggregate functions:
DECLARE @AVERAGE FLOAT;
SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
SELECT SQRT(SUM(SQUARE((LATITUDE - @AVERAGE)))/COUNT(LATITUDE)) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005392E-06
// That's better. Maybe STDEV is using fixed point arithmetic?!?
SELECT STDEV(10 * LATITUDE)/10 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
4.77267753808509E-06
SELECT STDEV(100 * LATITUDE)/100 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
1.66904329068838E-05
SELECT STDEV(1000 * LATITUDE)/1000 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
8.11904280806654E-06
// The standard deviation should, of course, be linear, e.g.
DECLARE @AVERAGE FLOAT;
SELECT @AVERAGE = AVG(LATITUDE) FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
SELECT SQRT(SUM(SQUARE(100*(LATITUDE - @AVERAGE)))/COUNT(LATITUDE))/100 FROM GPSHISTORY
WHERE GPSDATE BETWEEN '2007-10-23 11:21:00.859' AND '2007-10-23 17:00:00.062' AND GPSDEVICEID = 0x004A08BC04050000;
6.03401924005389E-06
// Std Dev is a numerically stable computation, although it does require traversing the dataset twice.
//
// This calculation is not being done correctly.
//
// Incidently, SQRT(VAR(Latitude....)) gives 4.80354E-4, which is also way off.
I will redefine STDEV to use a stored procedure similar to the above, but the algorithm used to compute VAR, STDEV etc should be reviewed and fixed.
-Rob Calhoun
Please provide me a column-level merge replication in SQL Server 2005. In the SQL Sever books online, I could able to get very few informaiton about this topic. Please provide me a best practices for using this kind of conflict resolution in Merge-replication.
View 5 Replies View RelatedIs the SQL Server Profiler Reads Column Incorrect For Parallel Plans?
I often use profiler as one tool to identify bad plans. The reads column gives me a good indication of excessive IO to dig into and correct if necessary. I often use it with Showplan so I can see what a query does, replicate it and fix it.
However I have just lost some faith in it. I am looking at a poorly performing query joining five tables. A parallel plan has been generated and one table is being scanned (in parallel) due to a missing index. This table had in excess of 4 million rows in it. The rest hitd indexes well. However the entire query generates ONLY 12 READS.
Once corrected, a single processor plan is used. This looks really efficient and uses 120 reads. That looks the right figure to me.
Does the profiler only display one thread of a parallel plan perhaps? Or something else?
Hi guys, would appreciate if you can shed some light on this.Sorry to be a pain, can you tell me what is wrong with the following:for /F %%i in ('dir /b /on c:cppc*.txt') do bcp Inventory..pc in%%i -fc:cpcp.fmt -T -S CHICKYywhere CHICKYy is the serverbcp.fmt8.00.19461 SQLCHAR 0 20 ", " 0 filler_1 ""2 SQLCHAR 0 8 "
" 1 computer_name ""3 SQLCHAR 0 20 ", " 0 filler_2 ""4 SQLCHAR 0 16 "
" 2 ip_address ""5 SQLCHAR 0 20 ", " 0 filler_3 ""6 SQLCHAR 0 60 "
" 3 operating_system ""pc1.txt and other *.txt format is:JW_193801,192.168.1.1,Windows XP,when I run it I get:C:cp>for /F %i in ('dir /b /on c:cppc*.txt') do bcp Inventory..pc in%i -fc:cpcp.fmt -T -S CHICKYyC:cp>bcp Inventory..pc in pc1.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc2.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc3.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc4.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileC:cp>bcp Inventory..pc in pc5.txt -fc:cpcp.fmt -T -S CHICKYySQLState = S1000, NativeError = 0Error = [Microsoft][ODBC SQL Server Driver]Incorrect host-column numberfound in BCP format-fileThe sql table has 3 columns:Sorry to be a pain.-----------------------------------------------------------------------"Are you still wasting your time with spam?...There is a solution!"Protected by GIANT Company's Spam InspectorThe most powerful anti-spam software available.http://mail.spaminspector.com
I can preview the SQL command in the OLE DB Source Editor and bring back all columns and results just fine but when I click on the Columns I get
TITLE: Microsoft Visual Studio
------------------------------
The component reported the following warnings:
Error at Data Flow Task [OLE DB Source [1]]: No column information was returned by the SQL command.
The columns are there in the preview - why can't SSIS get the column information?
Hi All,
I am in process of transfering data from Sybase to Sql Server using SSIS 2005
have taken a Data Flow Task in Control Flow tab
In Data flow tab, I have taken one Ole DB Source and One OLe DB Destination
For the source, I am using Sybase Adaptive Server Anywhere Provider 8.0
For Destination, I am using Sql Server 2005 database
In Ole Db Source Editor ,
For OLe Db Connection Manager, I choose Sybase Connection
For Data access mode, I choose Table or View
For Name of the table or the view, I choose a table by name Table1( it lists all the tables from Sybase database)
When i click on preview button or Columns link, I get the following Error
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E21.
Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)
Please help me out.
Thanks in Advance
Srinivas
I often use profiler as one tool to identify bad plans. The reads column gives me a good indication of excessive IO to dig into and correct if necessary. I often use it with Showplan so I can see what a query does, replicate it and fix it.
However I have just lost some faith in it. I am looking at a poorly performing query joining five tables. A parallel plan has been generated and one table is being scanned (in parallel) due to a missing index. This table had in excess of 4 million rows in it. The rest hitd indexes well. However the entire query generates ONLY 12 READS.
Once corrected, a single processor plan is used. This looks really efficient and uses 120 reads. That looks the right figure to me.
Clearly 12 reads is wrong. Does the profiler only display one thread of a parallel plan perhaps? Or something else?
Hi,
I am trying to set up a data flow task. The source is "SQL Command" which is
a stored procedure. The proc has a few temp tables that it outputs the final
resultset from. When I hit preview in the ole db source editor, I see the
right output. When I select the "Columns" tab on the right, the "Available
External Column List" is empty. Why don't the column names appear? What is
the work around to get the column mappings to work b/w source and
destination in this scenario.
In DTS previously, you could "fool" the package by first compiling the
stored procedure with hardcoded column names and dummy values, creating and
saving the package and finally changing the procedure back to the actual
output. As long as the columns remained the same, all would work.
Thats not working for me in SSIS.
Thanks in advance.
Asim.
Hey,
I changed the database name in the initial cataloge in the web.config conncetion string so that it now connects to other databas that contains same tables as the old one,but now i am getting that error at the update stmt !
thank u in advance
Hiba
Hello, I am getting very frustrated! I have got a Foreach loop container which I am processing files within a folder. I have a flatfile connection manager which I have set up using a test file and have updated the expressions attribute to be the package variable I set up in the collection for the loop container however everytime I run it I get the error: 0xC0202094 cannot retrieve the column information from the flatfile connection manager. I can only guess that it is either the variable being passed to the connection manager or the way I set up the connection manager. When I msgbox the variable in a script component before the dataflow step, the variable for the file seems fine. Any suggestions are REALLY appreciated.
Yours
Jayne
Hi,
I have been trying to export data from a Microsoft Excel 2007, Tester.xlsx to a SQL Server 2005 table as :
SELECT * INTO CUSTMAS FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:Tester.xls;Extended Properties=Excel 8.0')...[Providers$]
I have an error:
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server"(null)"
I've developed a package that is working well at development machine from VS 2013 for a sample flat file. Also, over development machine, I've deployed it to SSISDB catalogue and even from there also it is running well for the same file.When the same package is deployed to production server's SSISDB catalogue database, it throws following error while processing the same sample flat file, “Unable to retrieve column information from the flat file connection managerâ€
View 5 Replies View RelatedHello,
I have some troubles with IBM WebSphere Application Server using MS SQL Server 2005 JDBC Driver. I always get the error e.g.
java.lang.SecurityException: class "com.microsoft.sqlserver.jdbc.SQLServerDatabaseMetaData"'s signer information does not match signer information of other classes in the same package
I found this Feedback but it seems to be closed.
A temporary solution for me was to delete the meta-inf directory of the JAR-File, but that can't be the solution.
Can anyone help me with this problem?
Simon
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
Hi,
I get the following error Error: 17803, Severity: 17, State: 17
Insufficient memory available.Source ODS. When I have lot of scheduled jobs are running during the night. Does anyone know why this happens and how it can be fixed. Let me know.
Thanks
About once a week I'm receiving this message in the Sqlserver Log from ODS
Error 17803,Severity: 17, State: 14
Insufficient Memory Available
The machine has 1 gig of RAM and is dedicated to sqlserver7 with sp1. Any ideas of what might be causing this problem? Any help is greatly appreciated.
Chris