We have a very large table (> 77M records and growing) runing on SQL Server 2005 64bit Standard edition and we are seeing some performance issues. There are up to a hundred thousand records added daily. Does anyone know if there is a limit to the number of records SQL server Standard edition can handle? Should be be considering moving t...
Hi, I have a situation where before doing a particular task I have to check whether a particular flag is set in DB and if it is not set then rest of the processing is done and the same flag is set. Now, in case of concurrent access from 2 different transactions, if first transaction check the flag and being not set it proceeds further. ...
Hi, I have a windows application connecting to Database to read some data. Since the database is setup for resilience, my application needs to connect to one of the two databases. Can someone specify the what the syntax would be to specify the failover partner in a connection string using sql server authentication. Any help is greatly a...
table 'sample' contaning fields [AffiliateId] as a primary key, [AffiliateCode] L, [Name] , [AddressId] [, [Url] , [EmailId] , [ShopId] , i need a stored procedure for custom paging ...
declare @test varchar(20) set @test = 'VALUE' exec(' select '+@test+' ') This returns: Invalid column name 'VALUE'. Is there an alternate method to display the variable value on the select statement? ...
Hey folks, In Report Builder 2008 I use code such as =Chr(224) in textbox expressions to get Wingdings chars such as the up, down, straight arrows, etc. I run the report and it looks fab on screen, as well as exported to Excel. But if I export to PDF, I've noticed any Wingdings with character values above 127 do not display inside...
I've got a table that is comprised of the following structure. CREATE TABLE [dbo].[tblData]( [ID] [numeric](18, 0) NOT NULL, [QID] [varchar](25) NOT NULL, [Data] [nvarchar](255) NULL, CONSTRAINT [PK_tblData] PRIMARY KEY CLUSTERED ( [ID] ASC, [QID] ASC ) ) ON [PRIMARY] In the above example, the column...
Within SQL Server 2005 T-SQL, I would really like to pull these columns: constraint_type, constraint_name, and constraint_keys from the output of sp_HelpConstraint. However it returns 3 result sets (2 if you pass in 'nomsg'), so you can't do this to capture it: CREATE TABLE #Constraints ( ... ) INSERT INTO #Constraints (...
What is the ideal way to check if a database exists on a SQL Server using TSQL? It seems multiple approaches to implement this. ...
We're just starting to look at moving to SQL 2008 from SQL 2000 and are noting the new CROSS APPLY and INNER APPLY syntax that allows a form of 'joining' against either table-valued parametrized UDFs or correlated subqueries. Obviously it would be nice to be able to encapsulate logic in a UDF and be able to reuse it in different queries...
I have a real mystery with the T-SQL below. As it is, it works with either the DATAP.Private=1, or the cast as int on Right(CRS,1). That is, if I uncomment the DATAP.Private=1, I get the error Conversion failed when converting the varchar value 'M' to data type int, and if I then remove that cast, the query works again. With the cast ...
I need to track anonymous users with enabled cookies. Basically, they will go through the site, interact with it, and I would like to give them the best possible experience without requiring actual registration. Later on, if they want, they can register and their site activity will be tied to their new account. Something like Stackov...
Using ASP.NET 2.0 SqlMembershipProvider with MSSQL 2005. To illustrate issue : 1) Create simple ASP.NET app with a System.Web.UI.WebControls.Login control on a page. Change Web.config to use forms authentication and System.Web.Security.SqlMembershipProvider. 2) Run web app in browser and login. Close browser. 3) Using some SQL too...
Where do I find a complete list of new T-SQL features in sql server 2005 comparing with 2000? A few ones that I know: Pivot, Output, Try..Catch. Anything else? Thank you. ...
I've got a table that has rows that are unique except for one value in one column (let's call it 'Name'). Another column is 'Date' which is the date it was added to the database. What I want to do is find the duplicate values in 'Name', and then delete the ones with the oldest dates in 'Date', leaving the most recent one. Seems like a ...
I wrote this sql query to search in a table: Select * from TableName where Name Like '%spa%' The table contain these row for example: 1 Space Company. 2 Spa resort. 3 Spa hotel. 4 Spare Parts. 5 WithoutTheKeyword. I want to know how to edit this query so it return the results sorted like this: 2 Spa resort 3 Spa hotel 1 Space Compa...
How can I use ORDER BY CASE @AccountOperator WHEN '>' THEN AccountTrxValue END ASC, CASE @AccountOperator WHEN '<' THEN AccountTrxValue END DESC when AccountTrxValue is an alias and a result of the following in the "select"? CASE WHEN PAA.RedeemValue>0 THEN PAA.RedeemValue * -1 ELSE PAA.EarnValue END AS AccountTrxValu...
How to take table level backup (dump) in MS SQL Server 2005/2008? ...
I'd like to provide a command line interface to my db that allows the user to enter MULTIPLE database commands or queries (separated by line breaks in the textarea) For each line, if its a query must return the results and if its a command, whether or not it was successful - thus allowing the user to paste a script into the text area an...
Hello everyone, I've recently been reading Louis Davidson's book on Sql Server Database Design and found it quite informative. I've picked up on alot of concepts that I didn't previously know alot (or anything) about. Primarily - I picked up on a way to set up database relationships that I hand't tried before. Basically you use a surro...