I'm trying to call the sql statement below but get the following error. System.Data.SqlClient.SqlException: Conversion failed when converting the varchar value '+@buildingIDs+' to data type int. @"SELECT id, startDateTime, endDateTime From tb_bookings WHERE buildingID IN ('+@buildingIDs+') AND startDateTime <= @fromDate"; buildingID ...
I've got a column in a database table (SQL Server 2005) that contains data like this: TQ7394 SZ910284 T r1534 su8472 I would like to update this column so that the first two characters are uppercase. I would also like to remove any spaces between the first two characters. So T q1234 would become TQ1234. The solution should be able to...
Are there any tools available for validating a database schema against a set of design rules, naming conventions, etc. I'm not talking about comparing one database to another (as covered by this question). I want to be able to say "What in this database doesn't meet this set of rules". Some examples of the type of rules I'm talking a...
I have an XML datatype and want to convert an element into a sql DateTime variable. How? e.g. Declare @Xml Xml Set @Xml = '<Root><DateFrom>2008-10-31T00:00:00</DateFrom></Root>' Declare @DateFrom DateTime Set @DateFrom = ????? How can I set @DateFrom with the date from the xml above? ...
SQL to find duplicate entries (within a group) I have a small problem and I'm not sure what would be the best way to fix it, as I only have limited access to the database (Oracle) itself. In our Table "EVENT" we have about 160k entries, each EVENT has a GROUPID and a normal entry has exactly 5 rows with the same GROUPID. Due to a bug we...
Sybase db tables do not have a concept of self updating row numbers. However , for one of the modules , I require the presence of rownumber corresponding to each row in the database such that max(Column) would always tell me the number of rows in the table. I thought I'll introduce an int column and keep updating this column to keep tra...
I have a very interesting problem on my LinqToSql model. On some of my tables i have a references to other tables and in LinqToSql this is represented by a EnitiyRef class, when you are trying to access the references table LinqToSql will load the reference from the database. On my development machine everything worked fine (the referen...
Specifically MSSQL 2005. ...
Hello, i want to import data from mssql, run it through some sort of regexp to fitler out stuff, and import it into mysql. I then, for each query, wish to display a relevant image from a third database. What would be the easiest way to do this, importing and linking wise? Thankyou Clarification: It is a php application to filter data ...
I'm making a report in Access 2003 that contains a sub report of related records. Within the sub report, I want the top two records only. When I add "TOP 2" to the sub report's query, it seems to select the top two records before it filters on the link fields. How do I get the top two records of only those records that apply to the co...
Is it possible to send a list of IDs to a stored procedure from c# without dirty hacks? UPDATE Germs SET Mutated = ~Mutated WHERE (GermID IN (ids)) ...
SSIS - I have data loaded and various transformations on the data complete, the problem is there is a parent/child relationship managed in the data - best explained by an example each row has (column names are made up) row_key parent_row_key row_name parent_row_name some rows have row_key == parent_row_key (their own parent) some rows ...
I need to generate multiple random values under SQL Server 2005 and somehow this simply wont work with Random(Value) as ( select rand() Value union all select rand() from Random )select top 10 * from Random Whats the preffered workaround? ...
I'm looking to execute a series of queries as part of a migration project. The scripts to be generated are produced from a tool which analyses the legacy database then produces a script to map each of the old entities to an appropriate new record. THe scripts run well for small entities but some have records in the hundreds of thousands ...
How do I fix that error once and for all? I just want to be able to do unions in MySQL. (I'm looking for a shortcut, like an option to make MySQL ignore that issue or take it's best guess, not looking to change collations on 100s of tables ... at least not today) ...
Hello! I'm moving DB from MySQL (used ODBC) to MS SQL and I want to "translate" SQL queries to LINQ. Can someone help me with this (it should SUM Charge column for every location and group result by months): SELECT sum(case when Location="Location1" then Charge else 0 end) as Location1, sum(case when Location="Location2" then Charge el...
I'm using DB2, although a solution using any flavor of SQL would likely be easy enough for me to convert. I didn't design this database, or the application that uses the database. I haven't the power to change this application, how it works, or the data. Because it defies what I consider to be conventional use of a start and an end da...
Let's look at an example - books. A book can have 1..n authors. An author can have 1..m books. What is a good way to represent all of the authors of a book? I came up with an idea to create a Books table and an Authors table. The Authors table has a primary AuthorID key the author's name. The Books table has a primary Book ID and metada...
Suppose there is a fully populated array of data String[n][3] myData. I want to do this: for (String[] row : myData) { SQL = "update mytable set col3 = row[2] where col1 = row[0] and col2=row[1];" } Obviously I've left a lot out, but I want to express the idea as succinctly as possible. Is there a simple way of doing this in ...
I am using a Session Parameter on an ObjectDataSource. It works fine on the local development machine but I get this error after copying the website to the production server: Type 'System.Web.UI.WebControls.SessionParameter' does not have a public property named 'DbType'. ...