Hi, I have a table with a column that needs the data type upgrading. However running any alter script causes errors due to a unnamed unique constraint. I need to drop this constraint but unfortunately I do not know the name. I have a script which currently lists all the unique constraints on the table but I need to find out how to go...
I've been playing with sets in SQL Server 2000 and have the following table structure for one of my temp tables (#Periods): RestCTR HoursCTR Duration Rest ---------------------------------------- 1 337 2 0 2 337 46 1 3 337 2 ...
Hi, After searching couple of days (here and here), I believe I've found the problem and it seems identical to this one. However, I have captured two execution plans to compare it but, they seem to be identical except execution time. (The Database server is SQL SERVER 2000) The Query executed in VB6 : EventClass : SQL:BatchCompleted ...
I'm trying to write a query that will return all QUERY_ID values alongside all matching TABLE_ID values, where QUERY_ID is not specified in any table, and I can't create tables, so have to specify it in the query itself: QUERY_ID TABLE_ID 1 1 2 NULL 3 3 4 4 5 NULL I feel like there ...
I need to run a query against a legacy table that stores URL encoded text. I need this text to be decoded in my results. How to I achieve this? ...
Hi, I have read that the ODBC API "SQLDescribeCol" could be used to retrieve the SQLDataType of a column. Similarly, is there any ODBC API which could be used to retrieve UserDefinedType of a column? -Vengatesh ...
I've got a table Folders with hierarchical information about folders: FolderID FolderName ParentID 1 Folder1 0 2 Folder2 1 3 Folder3 2 4 Folder4 3 For Folder4 I would like to get the parent folders in the following format: Folder1\Folder2\Folder3\ Not...
Does anybody know what the recommend method is to copy data from one table to another using a DTS? Should we use the standard Insert/Select statement or a DTS transformation between two connections? Which one is faster? Has anybody done every any performance test on this? I am using SQL Server 2000 right now, but would also like to know...
Can anyone find my error in this query? I'm using sql server 2000 and I want to update all entries in the CostEntry table to the corresponding value in the ActiveCostDetails table. The where clause DOES work with a select statement. UPDATE CostEntry CE INNER JOIN ActiveCostDetails As AD ON CostEntry.lUniqueID = ActiveCostDetails....
I have a table with columns: Date, Phone, Name, and Event I need a query that will first recognize the duplicate phone entry, and then assign the name of whichever one has the earlier date to the event column. ...
I'm developing an ETL application with batch processing. There is low (i.e. no) concurrency for updates. I'd like to avoid the overhead of granular locks and lock escalation by merely locking the entire table. I'd like to avoid having to specify TABLOCK in every statement. Is there any way to set the locking granularity at the top of...
What is the locking granularity used for local temp tables in MSSQL? Given that local temp tables are local to sessions, and sessions are the same as connections in MSSQL2K, and that there is no way to execute any statements or other code in parallel on the same connection through TSQL or other means (I believe), then intuitively the DB...
I have a .NET 2010 app hitting a SQL2000 db. The code is pretty basic. When I insert a record, the record is inserted, but the id is not returned. The id column is an int and it is an Idetity. Here is the stored proc... ALTER PROCEDURE Insert_Vendor @CorpID as varchar(255), @TaxpayerID as varchar(255) AS Insert into dbo.Vendor ( vdr...
Hi, I want to generate a temp table having following columns: WeekNO StartDate EndDate using an UDF having a year parameter (YEAR(YYYY)). ...
I have a server with about 3 replications configured. 3 databases being published to about 3 other servers there's a central Distributor I need to know if I can change the distributor for one of the publishings and let the others or if I can only change all of the replication distributors at the publisher server. And How to do this...
I have an application which connects to SQL Server 2000 (using a generic SQL Login and SQL Authentication). I would like to implement some logging via triggers to track data changes. I can't use USER_NAME() because that returns the generic account. I've poked through master..sysprocesses and it doesn't seem to record the username (alt...
hi everybody, i am fresher in SQL,i want write a query to get the last 24 hour jobs record from msdb.dbo.sysjobhistory,but i cant get because,here i get the run_date and run_time as like the number.how i will convert the run_date and run_time into datetime and get the last 24 hour job record. i am using SQL Server 2000 thanks in advanc...
I have a table named 'Customer'. This table contains a unique Identification field for each customer named 'CustomerNo'. It should be in the format : First an alphabet Starting from A-Z. Then a four digit number starting from 1-9999 and the next is a three digit number starting from 1-999.This is a unique field.So it shoudn't repeat in...
Hi, I had a SQL Server 2000 MDF on a SQL Server 2005 server (moved from prior version), there was a hardware failure. After sending harddisk, it was supposed the mdf / ldf files were recovered, but i am unable to attach. Something curious is that the MDF file has a date on disk from june 2010 and the problem happened in sept 2010. I ...
I have a table named Table1 which contains an ID and TimeStamp. Table structure ID TimeStamp 1 0x0000000000047509 But when I compare the combination of these fields, it always shows false. What is the reason for this? My Query is : DECLARE @ID int DECLARE @TimeStamp timestamp SET @ID = 1 SET @TimeStamp = 0x000000000004750...