I have an MSSQL query that runs order productivity for a time period. Basically it calculates how long an order takes from start date to completion date in minutes. What I am doing is getting the date and timestamp where the order is created(a) and getting the date and time stamp where the order is completed(b) and subtracting them the...
Hi, I have a series of stored procedures that select data from a db. I have a role (cctc_reader) that has execute permissions granted on the procedures. One of the procedure calls another stored procedure called recControl_system_option which in turn queries Information_schema.columns. The problem is that in this proc the query selec...
One table has name "Stages", every Stage can have 0..infinity childrens, in table Stages there are column named Parent. This column is foreigh key for same table Stages. How to make cascade deleting in this tree? I want to on deleting any row in this table, auto delete all their childrens and childrens of their childrens....etc При тако...
I have two (or more) different databases, identically structured but each containing different data. How would I go about merging the data of one database into the other? This is a harder problem than I first thought. I have a table with primary keys, so a simple INSERT INTO db1.table1 SELECT * FROM db2.table1 may fail with clashing p...
I have a setup using Pyodbc, UnixODBC and FreeTDS, but somewhere in there some options are being set and I don't know where. According to SQL Server Management Studio, my program is sending some settings when it opens the connection: set quoted_identifier off set ansi_padding off set ansi_nulls off ... But I need a different set of se...
I have 2 tables Order Table - OrderId- Primary key Order Details Table - OrderID foreign key from order table. Orderdetail table has the information for all the products which were ordered in the specific order For example Order Order Detail Table ID CustomerID OrderDate ID ...
I have two tables, Foo and Bar. Foo contains a foreign key to Bar's primary key (bar_id). Bar is structured to allow a parent/child relationship to itself through a foreign key (bar_parent_id) to another record in Bar. This relationship is limited such that any Bar record that has a parent cannot itself be a parent. However, any given pa...
What are advantages and disadvantages of Microsoft-provided data provider classes in ADO.NET? are they fast? ...
The following code has been created from a different post, but I now have a separate issue. What this code does is basically takes the 2 dates and subtracts weekends from it to get the overall time elapsed in minutes from start to end time excluding weekends. I am now trying to say since there is 11 hours in a work day for me I want to o...
Example 2.938 = 938 Thanks ...
Using SQL Server 2008, I am trying to do something similar to this post regarding splitting a concatenated field into separate parts for normalization purposes. The problem with the solutions in the linked post is that they only work for delimited values. The data I am attempting to split would be similar to: UserID -- ConcatField 1 -...
Is there a better way to get all distinct values from three columns in one table other than using the DISTINCT function? I've also tried GROUP BY, but there doesn't seem to be any noticeable difference in the cost. SELECT DISTINCT Table1.Col1, Table2.Col1, Table1.Col3 FROM Table1 INNER JOIN Table2 ON Table1.FK = Table2.ID WHERE Table1....
Is there a way to stop executing code once I ROLLBACK a transaction in T-SQL? For example, in the code below I want 'Message 1' to print, but not 'Message 2'. BEGIN TRANSACTION GO PRINT 'Message 1' ROLLBACK TRANSACTION GO PRINT 'Message 2' GO ...
After upgrading to Windows 7, I can no longer debug stored procecures in SQL server management studio (MSSSMS). I get the message "Failed to start debugger". Data is Null. This method or property cannto be called on Null values (System.Data). I tried starting MSSMS with "Run as administrator", but the same thing happens. Any ideas? Thank...
I'm trying to create a query that would generate a cross-check table with about 40 custom columns that show Y or N. Right now I have SELECT DISTINCT [Company], [Option1], [Option2], [Option3], CASE WHEN [Table1].[ID1] IN (SELECT ID2 FROM Table2 WHERE Variable = 1 AND Bit = 1) THEN ...
I am running into a very strange problem in a SQL Server stored procedure. I have two databases. One contains the database for my billing system. The other is a reporting system with summarized data. Within this database there is a table with summarized job information. When this data is created, one of the fields, BilledToDate, i...
I have a date parameter so the date and time can always change. For this example the datetime is '2010-07-06 14:46:37.577' I need to see how much time is between this date paramter and the time of '17:00:00.000' The time of 5PM will never change but as I said the date paramter can change. ...
I'm doing a simple preparedstatement query execution and its throwing me this error: java.sql.SQLException: Use of the executeQuery(string) method is not supported on this type of statement at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.notSupported(JtdsPreparedStatement.java:197) at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.e...
I want to use the script from http://www.wisesoft.co.uk/articles/tsql_backup_restore_progress.aspx to get progress information of a specific running SQL-Server restore job. To achive this, I have changed it a little bit: SELECT command, s.text, start_time, percent_complete, CAST(((DATEDI...
I'm executing sql on MS SQL 2008 using ODBC's SQLExecute(). My code calls a stored procedure which becomes a deadlock victim. The server rollsback and the procedure is terminated, but SQLExecute() returns success. The server trace logs clearly show the deadlock, but an ODBC trace has no sign of any problem. The deadlocks are expected...