sql-server-2000

find the all the stored procedures and jobs in sql server 2000

Hi, In SQL SERVER 2005 This query works fine : Select * from sys.procedures where object_definition(object_id) like '%J%' SELECT * FROM MSDB.DBO.SYSJOBS WHERE NAME LIKE '%J%' but in sql server 2000 it is not working. Here i need to find the all the stored procedures and jobs which matches my string ? how to find in sql server 2000 ? ...

how to view the job in text in sql server 2005

hi, Ex: for store procedure we use sp_helptext .is there any keyword for viewing jobs in text in sql server 2005 regards kumar ...

How do I UPDATE a Linked Server table where "alias" is required, in SQL Server 2000?

In SQL Server 2005 tablename can be used to distinguish which table you're referring to: UPDATE LinkedServer.database.user.tablename SET val=u.val FROM localtable u WHERE tablename.ID=u.ID In SQL Server 2000 this results in Server: Msg 107, Level 16, State 2 The column prefix 'tablename' does not match with a table name or alia...

Dynamically determining table name given field name in SQL server

Strange situation: I am trying to remove some hard coding from my code. There is a situation where I have a field, lets say "CityID", and using this information, I want to find out which table contains a primary key called CityID. Logically, you'd say that it's probably a table called "City" but it's not... that table is called "Cities"...

Possible connection leaking causing "System.Data.SqlClient.SqlException: Timeout expired" error in SQL Server?

My application requires a user to log in and allows them to edit a list of things. However, it seems that if the same user always logs in and out and edits the list, this user will run into a "System.Data.SqlClient.SqlException: Timeout expired." error. I've read a comment about it possibly caused by uncommitted transactions. And I do ha...

What does xp_qv do in SQL Server?

Last night one of our SQL servers developed some major problems and after a colleague stopped, started, and all the usual things it started checking and rebuilding databases and is now running an extended stored procedure called "xp_qv". The internet seems to be very short of information on what this procedure does or anythign like that...

SQL Server: "Mostly-unique" index

In a table i want to ensure that only unique vales exist over the five-column key: Timestamp Account RatingDate TripHistoryKey EventAction ========= ======= ========== ============== =========== 2010511 1234 2010511 1 INSERT 2010511 1234 2010511 4 INSERT 2010511 1234 2010511 7 ...

Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ...

tsql to know when a database was last shrunk

Hi, In SQL 2000, 2005 and 2008, how can I know when databases were last shrunk on a MS SQL server? I want to do this using TSQL. Regards Manjot ...

VS 2010 and Entity Framework: accessing SQL Server 2000 databases

Consider a Visual Studio 2010 project whose requirement is to model the data using Entity Framework. The datasource is a SQL Server 2000 database. The first step is creating a new ADO.NET Entity Data Model item. The Entity Data Model Wizard prompts for a Data Connection. When creating a new Connection, you will need to use a provide...

SQL Server 2000, how to automate import data from excel

Say the source data comes in excel format, below is how I import the data. Converting to csv format via MS Excel Roughly find bad rows/columns by inspecting backup the table that needs to be updated in SQL Query Analyzer truncate the table (may need to drop foreign key constraint as well) import data from the revised csv file in SQL Se...

How to Use .NET Assembly from Legacy SQL Server 2000 DTS

Hi All, I have a .NET assembly that needs to be called from a DTS package. There are two options I am considering to get this to work: 1) write a COM-callable wrapper for the .NET assembly and have the VBScript create the COM object to use 2) write a .NET command-line exe that uses that .NET assembly and have the VBScript execute tha...

How to find date ranges in records with consecutive dates and duplicate data

There's probably any easy solution for this, but I can't see it. I have a table with consecutive dates and often duplicate associated data for several of these consecutive dates: Date Col1 Col2 5/13/2010 1 A 5/14/2010 1 A 5/15/2010 2 B 5/16/2010 1 A 5/17/2010 1 A 5/18/2010 3 C 5/19/2010 3 C 5/2...

sql server 2000: TSQL special characters handling..

Just using SQL Server 2000 built in features ONLY, what is the best way to handle special characters. I am not sure if Regular Expression could be used by purely using built in features? I would like to search and replace the special characters in my queries. Thanks ...

Most efficient way to move a few SQL Server tables to SQLite?

I have a fairly large SQL Server database; I'd like to pull 4 tables out and dump them directly into an sqlite.db for remote querying (via nightly batch). I was about to write a script to step through(most likely on a unix host kicked off via cron); but there should be a simpler method to export the tables directly (SQLite not an option...

How to create a folder in SQL Server 2000 DTS ActiveX script

I am using timestamp as new folder name. Seems like if the folder is not exist, the file won't save at new path. Can the community advise how to create folder with ActiveX script? Thanks. ...

Does Importing A Text File Using The DTS Wizard Requires Truncate Table?

running on: SQL Server 2000 I am exporting data (csv format) from test DB and import into live DB, so everytime I have to truncate live table and import file. If ther's a key binding, I have to drop the key first, do import, add key back. This task is too inconvenient. How should I improve this? Thanks. ...

Why isn't DBIx::Class::Schema::Loader creating my classes?

I am trying to generate static schemas using DBIx::Class in Perl. The command shown below outputs a Schema.pm and no other files. Any idea what I'm doing wrong, or how to to debug this? U:\wohlfarj\Software\PARS>perl -MDBIx::Class::Schema::Loader=make_schema_at,dump_to_dir:.\lib -e "make_schema_at('PARS::Schema',{debug=>1},['dbi:ODBC:P...

Passing delimited string to stored procedure to search database

How can i pass a string delimited by space or comma to stored procedure and filter result? I'm trying to do something like - Parameter Value -------------------------- @keywords key1 key2 key3 Then is stored procedure i want to first find all records with first or last name like key1 filter step 1 with first or last name...

SQL Duplicates Issue SQL SERVER 2000

I have two tables : Product and ProductRateDetail. The parent table is Product. I have duplicate records in the product table which need to be unique. There are entries in the ProductRateDetail table which correspond to duplicate records in the product table. Somehow I need to update the ProductRateDetail table to match the original (...