sql-server-2000

SQL Server 2000/2005 identity column + replication

I have looked at some resources already and just want to clarify and get an opinion. First of all to totally avoid any problems we could just not bother using identity columns as primary keys instead have them generated ourselves and just have those values replicated both ways presuming they are always unique at any time of creation. F...

Best Approach to Brownfield Database Development in SQL Server 2000/2005

I've recently taken over development on a SQL Server 2000 database that needs some help. We're planning on upgrading it to SQL Server 2005 soon. This database has no audit fields on the tables (CreatedBy, CreatedDate, etc.), no foreign keys, and terrible overall design. There are half a dozen programs that directly access the database...

Microsoft.SqlServer.Management.Smo and its brothers works on SQL2000?

As title, I need that to run an import script generated by SQL Server DB Publishing Tool. Would that work on Sql2000 server too? Also I have seen ppl reporting missing library issues related to GAC, which libraries I precisely need to include if I am not controlling the deployment server? To know how this thing works you can check here ...

Can I recover international characters mistakenly stored in a varchar field?

My client has an old MS SQL 2000 database that uses varchar(50) fields to store names. He tried to use this database to capture some data (via a web form). Some of the form-fillers are from other countries, and the varchar fields went nutty when some of these folks entered their names. Is it possible to recover the data somehow? Maybe by...

What is the best way to execute an MS SQL Server 2000 DTS package from a trigger?

I looked around and found some ideas about how to do this, but no definitive best way. One of the ideas was to use sp_start_job to kick off an SQL Server Agent job that runs the DTS package. If this is the best way to do it, then the next question would be, "How do I schedule a DTS package from a job and make it non-recurring?" Thanks...

Schema, Owner for objects in MS SQL

By default, objects (tables, stored procedures, etc) are set up with the dbo owner/schema (I think ms sql 2000 calls it owner, while ms sql 2005 calls it schema) The owner/schema is really a role or user in the database. I've always left the default of dbo, but I've recently seen some examples in microsoft training books where some of ...

How do I determine the optimal number of connections that can be open on my SQL Server 2000 DB?

What is the optimal number of connections that can be open on a SQL Server 2000 DB. I know in the previous company I was working for, on a tru 64 box with Oracle 8i, 8 processor machine we'd figured out that 8*12= 96 connections seemed to be a good number. Is there any such calc for SQL Server 2000. The DB runs on a 2-processor(hyper thr...

Is there any way to get a list of open/allocated cursors in SQL server?

I have a stored procedure that creates and opens some cursors. It closes them at the end, but if it hits an error those cursors are left open! Then subsequent runs fail when it tries to create cursors since a cursor with the name already exists. Is there a way I can query which cursors exists and if they are open or not so I can close...

Upgrade SQL Server 2000

Hi , If I need to upgrade MSSQL 2000 TO MSSQL 2005 which is installed inside the citrix box. How can I check if it is compatible or not. Thanks ...

How do you restore DTS permissions after database restore on SQL 2000

As part of the way we handle support for our clients for an enterprise application, we have them backup their database, I restore it on our server, do some maintenance, backup it up, and have them restore it on their server. This works very well, but I usually have to reset the users permissions for that database. But at the moment I h...

Regular Expressions in SQL Server servers?

Is it possible to make efficient queries that use the complete regular expression feature set. If not Microsoft really should consider that feature. ...

BCP out Error in SQL2000: SQLState = 37000, NativeError = 4060

Hi, I have created a proc that grabs all the user tables in a local DB on my machine. I want to be able to create a flat file of all my tables using BCP and SQL. Its a dummy database in SQL 2000 connecting through windows authentication. I have set my enviroment path variable in WinXP SP2. I have created new users to access the db, swi...

Connect to an Oracle database with SQL Server 2000 Reporting Services?

I am trying to create a report in SSRS2000 that will query an ORACLE database and pass one parameter, I am getting the following error message: ORA-01036: illegal variable name/number But have been unable to find much help elsewhere on the web with this error code I was hoping that this would be fairly simple to do, does anyone have a...

Alternative to BACKUP LOG WITH TRUNCATE_ONLY

This piece of T-SQL is deprecated in 2005: BACKUP LOG [DB_NAME] WITH TRUNCATE_ONLY I don't need to keep a backup the log for my db - but I do not want the piece of code to stop working if we port to SQL2008 or successive versions in future. Cheers ...

Speed up sql JOIN

First of all, some background. We have an order processing system, where staff enter billing data about orders in an app that stores it in a sql server 2000 database. This database isn't the real billing system: it's just a holding location so that the records can be run into a mainframe system via a nightly batch process. This batch...

Stored Procedure failing on a specific user

I have a Stored Procedure that is constantly failing with the error message "Timeout expired," on a specific user. All other users are able to invoke the sp just fine, and even I am able to invoke the sp normally using the Query Analyzer--it finishes in just 10 seconds. However with the user in question, the logs show that the ASP alwa...

What's the best way to store a title in a database to allow sorting without the leading "The", "A"

I run (and am presently completely overhauling) a website that deals with theater (njtheater.com if you're interested). When I query a list of plays from the database, I'd like "The Merchant of Venice" to sort under the "M"s. Of course, when I display the name of the play, I need the "The" in front. What the best way of designing the ...

Compatibility

Hi Friends, Actually I have to find out the compatibilty of the upgrade of MSSQL 2000 to MSSQL 2005. The MSSQL 2000 was installed inside the Citrix box . I did a side by side upgrade by backing up the database and restoring in my local machine. Now I have to figure out if the applications will work in MSSQL 2005.Can somebody please, give...

Sending Email alert in SQL Server 2000

Hi would like to send an email alert after checking the result of a query which will return the numbers of rows in a table. Does anyone have any ideas how I could do this in SQL Server 2000 in 2005 I would use a maintenence plan but not sure how in 2000? ...

SQL: Numbering the rows returned by a SELECT statement.

Suppose I have a SELECT statement that returns some set of results. Is there some way I can number my results in the following way: SELECT TOP 3 Name FROM PuppyNames ORDER BY NumberOfVotes would give me... Fido Rover Freddy Krueger ...but I want... 1, Fido 2, Rover 3, Freddy Krueger where of course ...