Hi I'm using OracleCommand.ExecuteNonQuery() to insert into a table. Everything works fine but occasionally records are not inserted. So is it possible that ExecuteNonQuery() doesn't insert the record and also doesn't throw an exception? I'm not using stored procedures. I'm not using a transaction. I am logging any exception that is t...
I would like to change a column in SQL Server from type int, to type text, while maintaining the column name. The table with this column has lots of data, and I do not want to lose it. SQL Server doesn't seem to support implicit or explicit casts from int to text, otherwise this would be pretty simple. So, how would you do it using on...
Hello all. I am trying to get a grip on the pivot query syntax. I have about 20 fields worth of aggregate data all associated with 1 field, which is the category for those 20 fields (by fields I mean columns). It looks like this Category1 column1 column2 column3 Category2 column1 column2 column3 and so on.....
I was reading this article: http://stackoverflow.com/questions/191640/get-null-null-in-sql And the consensus is that when trying to test equality between two (nullable) sql columns, the right approach is: where ((A=B) OR (A IS NULL AND B IS NULL)) When A and B are NULL, (A=B) still returns FALSE, since NULL is not equal to NULL. That...
I have a code that that loads my data into sql table. I want know that is there any way to load data into some other table if there is any duplicate value. if yes can you help me. My query is: "insert into t_DATA_DneFrc (RTN, AccountNbr, FirstName, MiddleName, LastName, Amount) values ('" & rcdDNE("RTN") & "', '" & rcdDNE("AccountNb...
How do you unflatten a delimited string column into separate rows? I couldn't find a good simple example in stackoverflow. Do I need to use the PIVOT function? My example: Table - Toys ID - A01 SendNumber - '200, 203, 205’ Owner - Josh Desired end Table ID SendNumber Owner A01 200 Josh A01 203...
i have a table in a oracle database in which one of the field is a clob field. the clob field is having several xml file. now if i want to export that xml file into a text file on unix how do i do it? NOTE: i dont have any gui like toad installed for the server and i have to only use the unix environment for it. could you please tell m...
I'm trying to simply prove here that this simple function isn't good enough to prevent every sql injection in the world: Function CleanForSQL(ByVal input As String) As String Return input.Replace("'", "''") End Function Here is a typical insert statement from one of our apps: Database.DBUpdate("UPDATE tblFilledForms SET Text1 = '...
i have an update statement,which gives an error "Failed to convert parameter value from a String[] to a String." SqlCommand comm1 = new SqlCommand("UPDATE count set ETR=@ETR WHERE Id IN ( " + itemIDs + ")", connection); comm1.Parameters.Add("@ETR", System.Data.SqlDbType.VarChar, 50); comm1.Parameters["@ET...
I have the following procedure: CREATE PROCEDURE foo () SELECT * FROM fooBar INTO TEMP tempTable; -- do something with tempTable here DROP TABLE tempTable; END PROCEDURE; What happens if there is an exception before the DROP TABLE is called? Will tempTable still be around after foo exits? If so, foo could fail the next ...
I have a query that includes this: ... AND Record.RecordID IN (1,2,3,10,11,12,13,16,17,18,26,27,28,557,31,32,33,36,37,93) AND ... The problem seems to be that if there are 20 items or more in that list, the query takes over 25 seconds to execute. If there are less than 20, it executes immediately. Any ideas on how to optimize? ...
I want to create a sequence of numbers in sql server it will have the minimum value and maximum value, i want to cycle if the number reaches the maximum limit. Can any body help me?? ...
I have this straight forward query: SELECT f.id, f.format_type, h.newspaper_id FROM newspapers_formats f LEFT JOIN newspapers_formats_held h ON (f.id = h.format_id) This returns all format_types, format ids, and the newspaper id that we have for that format. What I need is to then limit it to only newspaper_id = x, however, if I do th...
Is there any way to write a select record starting with a particular record? Suppose I have an table with following data: SNO ID ISSUE ---------------------- 1 A1 unknown 2 A2 some_issue 3 A1 unknown2 4 B1 some_issue2 5 B3 ISSUE4 6 B1 ISSUE4 Can I write a select to start showing records...
Hello, I have a table with about 20+ million records. Structure is like: EventId UNIQUEIDENTIFIER SourceUserId UNIQUEIDENTIFIER DestinationUserId UNIQUEIDENTIFIER CreatedAt DATETIME TypeId INT MetaId INT Table is receiving about 100k+ records each day. I have indexes on each column except MetaId, as it is not used in 'where' clause...
I have some data. 224,000 rows of it, in a SQLite database. I want to extract time series information from it to feed a data visualisation tool. Essentially, each row in the db is an event that has (among other things not strictly relevant) a time-date group in seconds since the epoch and a name responsible for it. I want to extract how ...
im trying to find all the mentions for the matched username (sam) from the database text code with following: $sql = "select * from tweet where feed like '%@sam%'"; this will return all the rows which user have been mention at. but now the problem is, it also returns rows with @sam3, @sam-dadf or anything that is after @sam.. how c...
So, I asked a question this morning, which I did not phrase correctly, so I got a lot of responses as to why NULL compared to anything will give NULL/FALSE. My actual question was, what is the time honored fashion in which db guys test inequalities for two columns that can both be NULL. My question is the exact opposite of this questio...
My eventual goal is to generate tag links for blog posts, the tags relating to the post. Currently my query has a GROUP_CONCAT which grabs relating tags and the returning tags column value is something like: 'Brooklyn, Manhattan, New York' from SELECT post.id, post.name, GROUP_CONCAT( tags.name order by tags.name ) AS tags...
I have a web service class diagram, I want it to show what stored procedures a web method calls. for now, I just use a class with a metod inside to represent a stored procedure, something like storeStuff(in this, in that, out field1, out field2) but this consumes tons of space, any tips? or are there any better ways to do this? tha...