The following code example fails randomly on some PC's. On other PC's the problem cannot be reproduced. All PC's are running .NET 3.5 SP1 on Vista SP1. string connection = @"Data Source=PCNAME\SQLEXPRESS;Database=TestDatabase ;User Id=sa;Password=ThePassword;"; TestDatabase db = new TestDatabase (connection); if (!db.DatabaseExists())...
I thought that must be obvious but I can't figure it out. Say there is a table tblData with a column ID and a table-valued-function (_tvf) that takes an ID as parameter. I need the results for all ID's in tblData. But: SELECT * FROM tblData data INNER JOIN dbo._tvf(data.ID) AS tvfData ON data.ID = tvfData.ID gives me an error: T...
I have a table with varbinary(max) column and nvarchar(max) column. One of them is null and the other has a value. I would like to return the column that has the value as a varbinary(max) column. So far I have tried this, that does not work: SELECT A = CASE A WHEN NULL THEN B ELSE A END FROM Tabl...
On my install of SQL Server if I perform the following SELECT CAST('2008-05-03 00:00:00' AS DATETIME), CAST('2008-05-03T00:00:00' AS DATETIME) Then I get the following result 2008-03-05 00:00:00.000 2008-05-03 00:00:00.000 Now this is odd in itself as I'm not sure why it's parsing the first date as yyyy/dd/mm (my login is set to br...
I am trying to use Sp_configure Proc in another stored procedure, but getting errors. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE Test01 AS BEGIN SET NOCOUNT ON; sp_configure 'show advanced options', 1 RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE Go END GO T...
What is faster in SQL to check value for NULL or 0 I want to have the fastest way to check is value already in table. For example which is faster : IF ((SELECT ID FROM [SomeTable].[dbo].[BlockedSubscriberNumbers] WHERE VALUE = @myVal) is null ) BEGIN .... END ELSE BEGIN .... END or IF ((SELECT ID FROM [SomeTable].[dbo].[Bloc...
I have the following query which runs fine on all of my sql server 2005/2008 databases SELECT sysprocesses.spid FROM master.dbo.sysprocesses However for one of my databases it give me a binding error on the spid column (cannot bind multipart identifier). I've check the compatibility mode of the db and it's set to 2005 so I'm sure th...
i am new to SSRS and i have to generate sales report in which we have to apply different conditions in different columns i have three columns that has three conditions in profit column condition is accountID=13 and TypeID=23 in units column condition is accountID=8 and TypeID=14 in Disbursement column condition is accountID=78 and T...
In the context of MS SQL Server 2005 Simply put is there a way to stop delete, and update sql statements being executed against the database that don't have a WHERE clause? Ideally it would be nice to restrict this 'blocking' to a set of users/roles. ...
I have a function...in which I have return type as table variable... But performance will be increased if we use temp tables..as we have more data. ...
Given the following table: create table TreeNode ( ID int not null primary key, ParentID int null foreign key references TreeNode (ID) ) How could I write a common table expression to start at the root (WHERE ParentID IS NULL) and traverse its descendants until the result set contains some target node (e.g., WHERE ID = n)? It's ea...
Possible Duplicate: SQL Identity (autonumber) is Incremented Even with a Transaction Rollback does a rollback also rollback identity values??? ...
I have a table in SQL Server 2008 R2 consisting of about 400 rows (pretty much nothing) - it has a clustered index on the primary key (which is an identity). The table is referenced via referential integrity (no cascade delete or update) by about 13 other tables. Inserts/Updates/Gets are almost instant - we're talking a split second (a...
I want to number the rows in my result set. I want some way I can have a result set of 3 records use some SQL keyword to generate a column that would read 1,2,3 for watch of the records... I know I can make a temp table with an auto increment column but i wanted to know if there was a way I can get this back from a SQL query? SELECT r...
Hello, I am new to .NET and have heard about several different ways of querying a SQL Server databse such as ADO.NET and the entity framework. Can anyone give me some advise about the best way to go for new applications? Thanks for any help or suggestions. ...
I have a stored proc defines as follows. PROCEDURE [dbo].[GetSales](@dateFilter nvarchar(50)) AS BEGIN SELECT sum(Amount) FROM Sales WHERE SalesDate in (' + @dateFilter + ') group by SalesDate END to select data from this table Id SalesDate Amount 1 Apr-2010 40.25 2 May-2010 12.10 3 Jun-2...
I have setup Database mirroring for SQL Server 2005 for my database on two different SQL Server machines. There is no witness server used here. I also see the status "Principal/Synchronized" and "Mirror,Synchronized/Restoring" respectively on Primary and Mirror servers for my database. Now I wanted to test whether the failover to mirror...
I'm a novice at regexs and am currently trying to come up with a simple regex that searches for a serial number in the following format: 0217103200XX, where "XX" can each be a numeric digit. I'm using SQL Server Management Studio to pass the regex as a parameter in a stored procedure. I'm not sure if the syntax is any different from ot...
i am having a problem with access sql SELECT statement. the problem is that when the backend is ACCESS-2007 it works; however when the backend is sql-server-2008, it is not returning anything. here is the statement: SELECT IIf([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] Like '*1.11*Other*','1.11 Other',[Lab Occurrence For...
SELECT "2 0 Analytical (Testing Phase)" FROM "dbo"."Lab Occurrence Form" WHERE (("Occurrence Date" BETWEEN @P1 AND @P2 ) AND ("2 0 Analytical (Testing Phase)" LIKE ''%2.%'' ) ) ', N'@P1 datetime,@P2 datetime','2010-04-30 00:00:00','2010-04-01 00:00:00' is this the correct format to return records between a certain datetime?...