sql-server-2000

How do I disable errors on string truncation in SQL Server?

How can I tell SQL Server not to raise an error if I insert or update a string longer than the size of the field - I would like silent truncation in this instance. ...

How can I return a list of records based on contiguous time assignments in SQL Server 2000

I have a Table that looks like this: IP Hostname TransactionDate ------------- ---------- ------------------- 1.1.1.1 A 2009-01-01 01:00:00 1.1.1.1 A 2009-01-02 01:00:00 1.1.1.1 A 2009-01-03 01:45:00 1.1.1.1 B 2009-01-04 01:00:00 1.1.1.1 A 2009-01-05...

MSSQL Select statement with incremental integer column... not from a table

Hi guys I need, if possible, a t-sql query that, returning the values from an arbitrary table, also returns a incremental integer column with value = 1 for the first row, 2 for the second, and so on. This column does not actually resides in any table, and must be strictly incremental, because the ORDER BY clause could sort the rows of ...

How to get only second,third,fourth or fifth row of a query result?(SQL SERVER 2000)

Hi, I would need a suggestion or even a solution, how to get only the second,third... row of a query result with high performance. I know there is the possibility of row_number(SQL Server 2005 or higher) but not for SQL Server 2000 :-( My first try to get the fifth row was: SELECT TOP 1 col FROM (SELECT TOP 5 col ...

How to duplicate a SQL Server 2000 table programatically using .NET 2.0?

I want to backup a table saving the copy in the same database with another name. I want to do it programatically using .NET 2.0 (preferably C#). Someone can point me what should I do? ...

How to temporarily disable the log in SQL2000/2005?

Is there a way to stop the log file from growing (or at least from growing as much) in SQL2000/2005? I am running a very extensive process with loads of inserts and the log is going to the roof. EDIT: please note I am talking about an batch-import process not about everyday update of live-data. Cheers! ...

MS Sql Server 2000:Multiple database query

I am having separate database for each dept.I have all depts list in a master database table.All database are named as a1,a2 etc Now i want to fetch the employee detail from all the dept databses returned by a query.pls help me ...

Dynamic tables in sql

Is there any method for creating dynamic tables in sql server 2000? ...

Determine Old primary key in a SQL Trigger

I've done this before somewhere I'm sure of it! I have a SQL Server 2000 table that I need to log changes to fields on updates and inserts into a second Logging table. A simplified version of the structure I'm using is below: MainTable ID varchar(10) PRIMARY KEY DESCRIPTION varchar(50) LogTable OLDID varchar(10) NEWID varchar(10) Fo...

How to use a single SqlTransaction for multiple SqlConnections in .NET?

I have SQL Server 2000, it doesn't support MultipleActiveResults. I have to do multiple inserts, and it's done with one connection per insertion. I want to begin a transaction before all insertions and finish it after all insertions. How do I do it? ...

Upgrade SQL Server 2000 from x86 to x64?

I have a SQL Server that's currently x86, and I'd like to upgrade it to x64. It's running on an x64 version of Windows, and I think it's as simple as the wrong version having been installed when the server was configured. It's a production server, so my biggest concern is getting the logins over unaltered, since every user has their own ...

SQL Server 2000 command to disable user account

I need to disable some user accounts within a sql server version 2000. the following sql command is giving me an " incorrect syntax near 'Login' " error. The user name is valid and spelled correctly so I'm wondering if the command syntax is different for version 2000. ALTER LOGIN exampleuser DISABLE ...

Slow MS SQL 2000, lots of timeouts. How can I improve performance?

I found this script on SQL Authority: USE MyDatabase GO EXEC sp_MSforeachtable @command1=“print ’?' DBCC DBREINDEX (’?', ’ ’, 80)” GO EXEC sp_updatestats GO It has reduced my insert fail rate from 100% failure down to 50%. It is far more effective than the reindexing Maintenance Plan. Should I also be reindexing master and tempd...

Caching Function Results in SQL Server 2000

I want to memoize function results for performance, i.e. lazily populate a cache indexed on the function arguments. The first time I call a function, the cache won't have anything for the input arguments, so it will calculate it and store it before returning it. Subsequent calls just use the cache. However, it seems that SQL Server 20...

Most Efficient Method to Detect Column Change in MS SQL Server

Our system runs on SQL Server 2000, and we are in the process of preparing for an upgrade to SQL Server 2008. We have a lot of trigger code where we need to detect a change in a given column and then operate on that column if it has changed. Obviously SQL Server provides the UPDATE() and COLUMNS_UPDATED() functions, but these functions...

Getting stored procedure usage data on SQL Server 2000

What is the best way to get stored procedure useage data on a specific database out of SQL Server 2000? The data I need is: Total of all stored procedure calls over X time Total of each specific stored procedure call over X time. Total time spent processing all stored procedures over X time. Total time spent processing specific stored...

What are the new t-sql features sql server 2005?

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. ...

SQL language differences between MSSQL 2000 and 2005

My company has an application developed for SQL Server 2005 (among others), and a potential client is attempting to install it on SQL Server 2000. Naturally, installation failed. Specifically, it failed with syntax errors in our CREATE TABLE statements. Is there a summary of the language differences between these two databases? ...

SQL Server Agent Job for the first 4 days of Month

I was wondering if anyone knew how to create a job that will run the first 4 days of the month in SQL Server 2000? I found how to run a job once on the first or second day, but the only way I figured I could run each of the four days would be to create a job for each day meaning I would have 4 jobs created. I was hoping for a better way....

Query performance of combined index vs. multiple single indexes vs. fulltext index

Background: I have a table with 5 million address entries which I'd like to search for different fields (customer name, contact name, zip, city, phone, ...), up to 8 fields. The data is pretty stable, maximum 50 changes a day, so almost only read access. The user isn't supposed to tell me in advance what he's searching for, and I also w...