Running fn_trace_gettable
Can I read a tracefile on 2000 SQL server using fn_trace_gettable without having to stop the trace? ...
sql-server-2000
How to tell what temporary tables are currently in scope in SQL Server?
I often get the errors: Msg 208, Level 16, State 0, Line 1 Invalid object name '#foo'. Msg 3701, Level 11, State 5, Line 1 Cannot drop the table '#foo', because it does not exist in the system catalog. How do I know what temporary tables there are in scope? They obviosly don't show up in SSMS like base tables do. ...
Getting a query to index seek (rather than scan)
Running the following query (SQL Server 2000) the execution plan shows that it used an index seek and Profiler shows it's doing 71 reads with a duration of 0. select top 1 id from table where name = '0010000546163' order by id desc Contrast that with the following with uses an index scan with 8500 reads and a duration of about a secon...
How to insert JPEG into a SQL Server 2000 database field of image type using Transact SQL
Hi, I'm trying to figure out how to insert a .JPG file into a SQL Server 2000 database field of type image using Transact SQL. Thanks. ...
How do I TryParse in SQL 2000?
I have a stored procedure in an old SQL 2000 database that takes a comment column that is formatted as a varchar and exports it out as a money object. At the time this table structure was setup, it was assumed this would be the only data going into this field. The current procedure functions simply this this: SELECT CAST(dbo.member_cate...
Trigger in subscriber is getting deleted when replication is created.
I have a table A in publisher database and there is trigger which inserts data into table B when an entry happens in table A. I have the same configuration in subscriber database, that is Table A, Table B and the trigger. I have done transactional replication for both the DB and after the establishing the replication, trigger in subscrib...
Having some Dynamic SQL issues with INT type
Hello Im using SQL2000 so I build a Dynamic Query and in the last case I have this : IF (@apepac is not null and @nompac is not null and @month is not null ) SELECT @DynaSQL_1= @DynaSQL_1 + ' AND PACIENTE.apellidos like ''' + @apepac + '%'''+ ' AND PACIENTE.nombres like ''' + @nompac + '%'''+ ' AND DA...
SQL command not changing data
Hi all, I'm using the SQL command below to update a column called Address, the value contains line feeds and I want to replace them with a semi-colon. In Query Analayzer the preview looks perfect but when I check the data in Enterprise Manager (MS SQL 2000) I still see the squares i.e. the line feeds. Am I not commiting the change? Wher...
How can I do server side pagination of results in SQL Server 2000?
In SQL Server 2005, there is a feature called row_number() which makes pagination very simple. SELECT * FROM table WHERE row_number() between x and y Is there any SQL server way to do the same thing in SQL Server 2000? (Note: I don't have access to a unique sequential numbering for the query, i.e. something which would be a synonym f...
LINQ to SQL and SQL 2000 - Extensibility Method Definitions gone
I have just had to use LINQ to SQL on a SQL 2000 database and I have noticed that it does not include all the "Extensibility Method Definitions" actions, why is this? ...
SQL 2000 - DRVTBL?
Hi All, I'm trying to understand a historical stored procedure I need to fix. I found this DRVTBL key word, and I couldn’t find out what it means??? (This is a sql2000 database) SELECT ... FROM ( ...) ) DRVTBL ...
'Database may not be activated yet or may be in transition' error
I have this heavily nested sql statement which works well in my sql server 2008 express. [code block below] However, when I move it over to our preliminary test server (sql server 2000) it doesn't work unless I use fully resolved table references in the from clauses of each statement. Which I can't do as the database name varies by inst...
Serializing a object with an image to be saved to SQL database
I have the following object: [Serializable] public class ExampleImage { public int ID { get; set; } public string Filename { get; set; } public byte[] Content { get; set; } } I store this in a List<ExampleImage> which I then pass to the following function to serialize it to a string: static string SerializeObjectToXmlStri...
SQL Server 2000 - Default Value for varchar fields
I have a sql server nvarchar field with a "Default Value or Binding" of empty string. It also happens to be a not null field. Does this mean that there is no default or that it is a default of a string with no characters in it. If I don't insert a value, will it insert with an empty string or fail with a "not null" error? ...
Will an EXISTS query still lock a table?
IF NOT EXISTS(SELECT * FROM MyTable WITH(nolock) WHERE Key = 'MyKey') INSERT MyTable(Key) Values('MyKey') If The value does not exist in the table, does the query aquire a lock? ...
SQL Server Connection Problems
I have one server out of three which cannot connect to our sql server 2000 using the FQDN but can connect using the non FQDN. None of the the other servers are having this problem. This problem occurs under ADO connections to a SQL Server 2000 database. The database has encryption turned on. I don't want to turn it off. Does anyon...
SQL setting dictating how floats are stored
Good day to all. I fond an odd difference between my test environment and my production environment. I have a table which stores data as a float. I run the following script on both with different results: insert into myTable(myFloat) select top 1 26.1295 as foo Select myFloat from myTable Server A = 26.1295 Server B = 26.129499435...
SQL Server FOR XML adding in a tag
Heya, So I am pulling data from a SQL Server 2000 DB then converting it to XML using FOR XML AUTO. The XML I get back looks like this. <Order OrderNumber="2000004" DeliveryPickupCharge="5.9900" SalesTaxTotal="0.0000" SubTotal="0.0000" Total="5.9900"> <Customer FirstName="Anthony" LastName="Caporale"> <Product ProductName="Pap...
Why my ADO.net Connection was Closed but sp_who2 indicates "awaiting command" connections?
We are facing serious problems with performance in the database. After making a lot of changes in the procedures, trying to minimize locks, we still have some problems of performance. When I make a sp_who2 in the database, there are several connections in sleeping mode, "Awaiting command". Maybe if I close these connections automatical...
SQL server 2000 paging on a table with three keys
I've been trying to solve this problem for a few days now without much luck. I have found loads of resources that talk about paging on SQL Server 2000 both here and on codeproject. The problem I am facing is trying to implement some sort of paging mechanism on a table which has three keys which make up the primary key. Operator, Custome...