How do I create a sql dependency ona table in sql server 2000 and asp.net 2.0?
I need to create a sql dependency on a table in sql server 2000 in my asp.net 2.0 pages. What are the required actions and what is the best way? ...
sql-server
SQL Server sys.databases log_reuse_wait question
I was investigating the rapid growth of a SQL Server 2005 transaction log when I found that transaction logs will only truncate correctly - if the sys.databases "log_reuse_wait" column is set to 0 - meaning that nothing is keeping the transaction log from reusing existing space. One day when I was intending to backup/truncate a log ...
Benefits of SQL Server 2005 over 2000
Could somebody please name a few. I could given time, but this is for somebody else, and I'd also like some community input. ...
MS SQL Server 2005 - How to Auto Increment a field (not primary key)
Hi, I would like to automatically increment a field named `incrementID' anytime any field in any row within the table named 'tb_users' is updated. Currently I am doing it via the sql update statement. i.e "UPDATE tb_users SET name = @name, incrementID = incrementID + 1 .....WHERE id = @id; I'm wondering how I can do this automatically. ...
What are the differences between a clustered and a non-clustered index?
What are the differences between a clustered and a non-clustered index? ...
How can I delete duplicate rows in a table
I have a table with say 3 columns. There's no primary key so there can be duplicate rows. I need to just keep one and delete the others. Any idea how to do this is Sql Server? ...
Add column, with default value, to existing table in SQL Server
How do you add a column, with a default value, to an existing table in SQL Server 2000/2005? ...
How can you get database specific performance metrics for things like CPU/Memory/etc. in SQL Server 2005?
I have a couple databases on a shared SQL Server 2005 cluster instance, that I would like performance metrics on. I have some processes that run for a very long time and suspect that code inefficiencies, rather than insufficient hardware are to blame. I would like some way to get these performance metrics so that I can rule out the data...
How to design a rule engine?
Hi, I'm supposed to create a simple rule engine in C#. Any leads on how I can proceed?. It's a minimalistic rule engine, and would use SQL server as the back end. Do we have any general blueprint or design patterns that generally apply to rule engines? What kind of .Net technologies can I use to design one? Any directions would be helpfu...
SQL Server post-join rowcount underestimate
The Query Optimizer is estimating that the results of a join will have only one row, when the actual number of rows is 2000. This is causing later joins on the dataset to have an estimated result of one row, when some of them go as high as 30,000. With a count of 1, the QO is choosing a loop join/index seek strategy for many of the join...
Can I automatically 'Generate Scripts' in a SQL Server 2005 task?
I'd like to automatically generate database scripts on a regular basis. Is this possible. ...
How do I drop a foreign key in mssql?
I have created a foreign key (in mssql / tsql) by: alter table company add CountryID varchar(3); alter table company add constraint Company_CountryID_FK foreign key(CountryID) references Country; I then run this query: alter table company drop column CountryID; and I get this error: Msg 5074, Level 16, State 4, Line 2 The o...
SQL Server Express DB is 'in recovery' - how to detect / know when it is
when an SQL Server Express DB is 'in recovery', you are unable to connect using SQL Authentication. Is there a simple way of determining the stat of the DB prior to connecting to it? (Using .Net) ...
Inserting a string of form "GUID1, GUID2, GUID3 ..." into an IN statement in TSQL
Hi, I've got a stored procedure in my database, that looks like this ALTER PROCEDURE [dbo].[GetCountingAnalysisResults] @RespondentFilters varchar AS BEGIN @RespondentFilters = '''8ec94bed-fed6-4627-8d45-21619331d82a, 114c61f2-8935-4755-b4e9-4a598a51cc7f''' DECLARE @SQL nvarchar(600) SET @SQL = 'SELECT * FROM Answer ...
How can I maintain consistent DB schema accross 18 databases (sql server)?
We have 18 databases that should have identical schemas, but don't. In certain scenarios, a table was added to one, but not the rest. Or, certain stored procedures were required in a handful of databases, but not the others. Or, our DBA forgot to run a script to add views on all of the databases. What is the best way to keep database...
How to use system_user in audit trigger but still use connection pooling?
I would like to do both of the following things: use audit triggers on my database tables to identify which user updated what; use connection pooling to improve performance For #1, I use 'system_user' in the database trigger to identify the user making the change, but this prevent me from doing #2 which requires a generic connection ...
Sql server triggers - order of execution
Does anyone know how Sql server determines the order triggers (of same type, i.e. before triggers) are executed. And is there anyway of changing this so that I can specify the order I want. If not, why not. Thanks. ...
looking for a good summary of SQL 2005 Partitioning
I'm looking at this as a baseline explanation of the SQL 2005 Enterprise partitioning. Is there a resource that goes deeper into fine points and considerations of this issue. Some more examples would be useful too. My main scenario is a time based partition system. With one partition that has the most accessed last X days. This partitio...
How do I return random numbers as a column in SQL Server 2005?
I'm running a SQL query on SQL Server 2005, and in addition to 2 columns being queried from the database, I'd also like to return 1 column of random numbers along with them. I tried this: select column1, column2, floor(rand() * 10000) as column3 from table1 ...which kinda works, but the problem is that this query returns the same rand...
How to get a table of dates between x and y in sql server 2005
I just want a quick way (and preferably not using a while loop)of createing a table of every date between date @x and date @y so I can left outer join to some stats tables, some of which will have no records for certain days in between, allowing me to mark missing days with a 0 ...