the row no in query output
I have a numeric field (say num) in table along with pkey. select * from mytable order by num now how I can get the row no in query output of a particular row for which I have pkey. I'm using sql 2000. ...
sql-server-2000
Determining sql command type in the trigger in sql server 2000
Is there more recomended way of determining command type in the trigger then testing DELETED and INSERTED tables? Currently i'm using approch: (EXISTS (select 1 from INSERTED) AND NOT EXISTS (select 1 from DELETED)) = INSERT (EXISTS (select 1 from INSERTED) AND EXISTS (select 1 from DELETED)) = UPDATE (NOT EXISTS (select 1 from INSERT...
SQL Server: Code page translations are not supported for the text data type.
When inserting text into a TEXT column in SQL Server using ADO, i get the error: Code page translations are not supported for the text data type. From: 1257 To: 1252. Now it is true that i've changed my Windows code page to 1257 (Estonian). My question is: How does SQL Server know what code page i'm running in? All strings sent t...
sql server 2000 TSQL: creating index on table variable
Is the following possible? I am unable to do so. Do I have to have a permanent table to create index? declare @Beatles table ( LastName varchar(20) , FirstName varchar(20) ) CREATE CLUSTERED INDEX Index_Name_Clstd ON @Beatles(LastName) ...
SQL Profiler (SQL Server 2000), how to filter only my activities?
We have a big system with several hundred concurrent users so sql profiler gives a bit too much information without appropriate filters. I'd like to see what SQL commands are run under my account and my account only. With account I mean the username I use to log in to the system. These user names are stored in a regular database table an...
how to call procedure in database asynchronously from java 1.4 code?
platform: Sql server 2000 java 1.4 ejb 3.0 ...
Script to write out stored procedures
I am looking for a script that will write out all the sprocs in a SQL Server 2000 database to a textfile, preferably as a series of "CREATE PROCEDURE" statements. ...
How to delete unique index referenced by foreign keys?
I have a table, let's call it Users. This table has primary key called Id. Despite having Id as primary key (unique clustered), it has other index (unique nonclustered) on the same column(Id). I would like to drop this constraint, but foreign keys reference this unique nonclustered index and I get The constraint ... is being referenced...
Problem using Query Analyzer to debug stored procedure
I am attempting to debug a stored procedure using the Debug option is SQL Query Analyzer (Version 8.00). However, the debugger run through the whole stored procedure as soon as I start and if add a break-point and try re-executing the sp, the break-point is ignored. What do I need to do to enable debugging? The server is SQL Server 2000 ...
Getting bcp.exe to escape terminators
I need to export some data using SQL Server 2000's BCP utility. Sometimes my data contains characters, such as \t and \n, that I need to use as column and row terminators. How do I get BCP to escape characters it's using as terminators as it outputs the data, so that I can actually import the data in another program? For example, one ...
Aggregate Functions and Group By Problems
If we start with the following simple SQL statement which works. SELECT sor.FPARTNO, sum(sor.FUNETPRICE) FROM sorels sor GROUP BY sor.FPARTNO FPartNo is the part number and the Funetprice is obviously the net price. The user also wants the description and this causes a problem. If I follow up with this: SELECT sor.FPARTNO, sor.fdes...
How to copy an ntext column's value to a new non-null column
I have a table that has an ntext column defined as [value1] [ntext] NOT NULL. I want to add another ntext column to this table that is basically a copy of this column's values (they don't need to stay in sync). To do this, I am using the following SQL: ALTER TABLE [table] ADD [value2] [ntext] NULL UPDATE [table] SET [value2] = [value1] ...
grouping items in sql query
I have a table with columns like (in sql server 2000) MailCode Mode Name Group -------- ----- --------- ------- 1 1 abc 0 1 1 def 0 1 1 qwe 1 2 2 aaw 0 2 2 aad 0 I want to group the Name field based on the rest of the fil...
Trying to audit deletions with a half baked system.
My ERP system has a half baked deletion tracking system which inserts the following info into a table called M2MDeleteLog. I have left out unnecessary columns such as RecordId for simplicity. LogDate Workstation LogInfo 1/7/2010 11:01:51 TECH-M2MTEST Deleting 1 Rows From SOMast 1/7/2010 11:01:51 TECH...
Updating table with random data With NEWID() does not work
SQL SERVER 2000: I have a table with test data (about 100000 rows), I want to update a column value from another table with some random data from another table. According to this question, This is what I am trying: UPDATE testdata SET type = (SELECT TOP 1 id FROM testtypes ORDER BY CHECKSUM(NEWID())) -- or even UPDATE testdata SET typ...
Sql server query to flattening a hierarchy of records
I have a table that describes a hierarchy: Name MemberName A B A C B D D E F G MemberName references the Name column of the same table. From this table, I can easily query to see that B and C are members within A, D is a member of B, E is a member of D and G is a member of F. Based on this structur...
Faking SQL Server
I have application that requires SQL Server 2000 as database storage. I do not really want to use SQL Server 2000, but I can user MySQL Server instead. Application uses ODBC to connect to SQL Server Database. I would like to know if it is possible to make fake SQL Server which will send and receive data to/from MySQL Server applicatio...
SQL Server: How to Join to first row
i'll use a concrete, but hypothetical, example. Each Order normally has only one line item: Orders: OrderGUID OrderNumber ========= ============ {FFB2...} STL-7442-1 {3EC6...} MPT-9931-8A LineItems: LineItemGUID Order ID Quantity Description ============ ======== ======== ================================= {098...
ADO Command Object Records Affected broken when moving to SQL Server 2008
My company is in the process of moving a database from SQL 2000 to SQL 2008. I am in the process of testing some of our legacy applications and came across a VB6 app that is running into a bug. The code is attempting to run a simple update for a record and check that the Execute method has affected more then one row using the RecordsAf...
Comparing Query Changes. Is there a better way.
When I am writing queries, I will do so in steps. Sometimes, in the process I will realize that I've made a "mistake" such as ending up with extra or losing records. So, I will typically compare the two queries like so: (Select blah blah blah ) Mine Inner join ((Select blah blah blah ) Orig Where Mine.PK <> Orig.PK or if I'm lookin...