Hello everyone, I am using SQL Server 2008 Enterprise. I have a table called Foo and it has a DateTime type column called MyTime. I want to find all records (rows) whose MyTime column value has been elapsed for more than 3 days (elapsed from current time). How to implement this? thanks in advance, George ...
Hi all, I want to know wether or not it is possible to suppress a transaction inside a SQL stored procedure. I have the following situation in my SP (that I want to achieve): WHILE TRUE BEGIN TRY BEGIN TRANSACTION A RECEIVE MESSAGE FROM SSB QUEUE WITH TIMEOUT BEGIN SUPPRESS TRANSACTION WHILE RECORD IN TABLE ...
The Sql Server 2008 R2 instance in question is a heavy load OLTP production server. The deadlock issue came up a few days back and is still unresolved. We received the Xml deadlock report that listed the stored procedures involved in the deadlock and some other details. I'll try to list down the facts from this xml first: Two stored pro...
Hi all, I'd like to convert a stored proc into a trigger. The stored proc is supposed to be run after inserts/updates, but users forget to execute it more often than not! The stored proc inserts recalculated values for some rows: --Delete rows where RowCode=111 DELETE FROM dbo.TableA WHERE [year]>=1998 AND RowCode=111 --Insert new va...
I have a table with stock quotes Symbol Ask Bid QuoteDateTime Using SQL Server 2008, lets say for any given 60 second time period I want to select quotes on all symbols so that there is a record for every second in that time period. Problem is not every symbol has the same number of quotes - so there are some seconds that have no quo...
I have the following tables in a SQL Server 2008 db: tblItem, which has an ItemID field; tblGoodItem, which also has an ItemID field, and has a foreign key pointing to tblItem; tblBadItem, which also has an ItemID field, and also has a foreign key pointing to tblItem. An item cannot be both a good item and a bad item; it must either ...
Have you encountered this exception for a stored procedure which does indeed have a balanced transaction block? I double-checked the stored procedure and it has exactly one TRANSACTION BEGIN and cooresponding TRANSACTION END Error logged SqlException - Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT ...
We are running SQL Server 2008 on 64bit boxes. We've had a request to create a query that has a Where clause that draws data from an Excel table. In pseudo code, the query might look as follows: Select column1, column2, column3 From MyDatabase.MyTable Where column4 IN (Select all values from column 'A' in SubIDs.xls whose sheet name is ...
I have used SQL Server 2008 Management Studio for a long time and all of a sudden I noticed the query design toolbar which has change type on it and allows you to execute query from there and select other options, all greyed out. Does anyone know how to correct this? I have restarted Management Studio numerous times. ...
In my database I have certain data that is important to the functioning of the app (constants, ...). And I have test data that is being generated by testing the site. As the test data is expendable it delete it regularly. Unfortunately the two types of data occur in the same table so I cannot do a delete from T but I have to do a delete ...
If I have a stored proc in SQL Server 2008, I know I can run it from management studio like so: exec rpt_myproc @include_all = 1, @start_date = '1/1/2010' But I'm using an ad-hoc query tool that wasn't returning any results. So I asked it to give me the SQL it was running and it returns this: SELECT DISTINCT TOP 100000 [dbo].[rpt_myp...
Hi folks, I wish to do the following pseduo code in Sql without the use of a CURSOR, if possible. for each zipcode { -- What city is this zipcode in? A zipcode can be in multiple cities, -- but one city area is always way greater that the others. -- Eg. 90210 is 96% in the city of Beverly Hills. -- The remaining 4% is...
I have an xml column and I want to persist a node count in it in an adjacent column. For example, in an xml given below, I have 3 nodes a and so I want to output it. The problem that I have, however, is that I can't manage to create a generic schema-bound function that would take @xml and @nodeName so as to return the count of a speci...
My two questions are: Can I use clustered indexes to speed up bulk inserts in big tables? Can I then still efficiently use foreign key relationships if my IDENTITY column is not the clustered index anymore? To elaborate, I have a database with a couple of very big (between 100-1000 mln rows) tables containing company data. Typically ...
I am optimising a large SP in Sql Server 2008 which uses a lot of dynamic Sql. It is a query which searches the database with a number of optional parameters and short of coding for every possible combination of parameters dynamic sql has proven to be the most efficient method of executing this. The sql striung is built including param...
Hi, i'm developing a simple windows application (C#, WinForms, ADO. NET). Application should have a following functionality: create a login and corresponding user in my database (database1); add roles to new user (role names: rol1, rol2). New login can (1) and (2) and so on. How should I do it ? What I want: script for creating an...
Consider following piece of code: declare @var bit = 0 select * from tableA as A where 1= (case when @var = 0 then 1 when exists(select null from tableB as B where A.id=B.id) then 1 else 0 end) Since variable @var is set to 0, then the result of evaluating searched case operator is 1. In the documentation of case i...
When i attempt to deploy a SQL Server Report, i get the following error: An attempt has been made to use a data extension 'OLEDB-MD' that is either not registered for this report server or is not supported in this edition of Reporting Services. (rsDataExtensionNotFound) This error occurs only when i try to deploy a certain report. Rest...
What SqlDbType enumeration should I use when my column is the Geography type? I'm using MS SQL Server 2008 R2. This is what I'm looking for specifically: // ADO.net - what do I use for the SqlDbType when it's defined // as Geography in the stored proc SqlCommand command = new SqlCommand(); command.CommandText = "dbo.up_Foobar_Insert"...
Hi, I'm having a problem with this trigger: ALTER TRIGGER [dbo].[trg_B_U_Login] ON [dbo].[Login] FOR UPDATE AS BEGIN IF @@ROWCOUNT = 0 RETURN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @Campos nvarchar(500); DECLARE @Old...