I've just moved a database from a SQL 2000 instance to a SQL 2008 instance and have encountered an odd problem which appears to be related to IDENTITY columns and stored procedures. I have a number of stored procedures in the database along the lines of this create procedure usp_add_something @somethingId int, @somethingName nvarchar(1...
Hello, I generate an sql string select statement that is around 25k characters long. Unfortunately the production server is SQL 2000. Is my only option to break the string up into 4k nvarchars? --ex. DECLARE @s1 NVARCHAR(4000) DECLARE @s2 NVARCHAR(4000) DECLARE @s3 NVARCHAR(4000) DECLARE @s4 NVARCHAR(4000) DECLARE @s5 NVARCHAR(4000) DE...
Hi, I have a SQL database where one of my fields 'Status' has one of 10 entries in it (not from a dropdown list). I let some people have access to the data through an Access database, they can add and modify entries. My question is, in Access, how can I make it so that the 'Status' field is a drop-down list from which the users can ch...
I am working on SQL Server 2000. As you know, there is an application that's shows query results (Query Analyzer) The data is shown there, on a grid, and you can save that grid in a csv file. That's great. Now, I want to INSERT those csv values into a Table. Does SQL Server provide any method?? And I mean, automatic or semi-automatic me...
Hi, I have one customer experiencing 5+ deadlocks per day. 170+ other customers don't get any deadlocks at all. I've implemented deadlock tracing and can see the select/update/insert deadlocks occurring. I implemented a with (rowlock) hint and this sorted the initial deadlock out. However, the thing still deadlocks further on down t...
Hello all. Can I set NOCOUNT to be OFF by default for a particular database? I need it for NHibernate (if it's ON, I get exceptions when saving). I can see the setting to disable it for an entire server (where it's set ON), but we're sharing the server with other databases. Is there a way I can set it to OFF just for my database? Or i...
I am writing a small utility to copy a database from a proprietary ODBC database into a SQL Server database. Everything is working great, except when I use SQLBulkCopy to copy over the data. It works in most cases, but not when it's trying to copy a TIME field into a SQL DATETIME. I get this error: The given value of type TimeSpan fr...
How can i know that which of my result has satisfied how many number of conditions/ SELECT [TITLE] FROM [M_TIPS] WHERE [TITLE] LIKE '%VALUE%' OR [TITLE] LIKE '%SQL%'; How to know that which of the result has satisfied only 1 OR condition and which of the result has satisfied both conditions. The number of condit...
I need to select a nullable bit column in a view, but use a default value of FALSE whenever the value is NULL. (For other reasons, I can't add the default value on the source table itself.) Here is what I am doing. CAST ( CASE WHEN bit_column IS NULL THEN 0 ELSE bit_column END AS BIT ) AS bit_column, ... I ha...
I want to use LIKE clause and IN clause together. e.g: Currently my query is - SELECT * FROM [USER_DETAILS] WHERE [NAME] LIKE 'Dev%' OR [NAME] LIKE 'Deb%' OR ...... ...... How can i use IN clause to achieve this? Can someone please help? :) ...
Although I have been able to see the last ran query which is a Stored Procedure executed but I didn't get the parameters values with which the SP was invoked. Rather I got the following: StoredProcedureName;1 from the following command: DBCC INPUTBUFFER(SPID) Where I got the SPID by viewing it in the ObjectExplorer->Management->Act...
I have a script that processes queued records in a SQL table in SQL Server 2000. I now need to add additional instances of my script to process their own subset of the queued records in the table. How can I query the table within each instance so that each instance will return a subset of rows that never overlap with each other? I cou...
I have a project that requires me to do development in SQL Server 2005, but do deployments to a SQL Server 2000 box. For 99% of the SQL code, I have no problems, everything appears to be backwards compatible. Now I am just about to start adding all the Stored Procedures (SPs) to source control, and I like the idea of doing a drop-add e...
I am trying to optimize the below query in SQL Server 2000. Every table has more than 3 Lakh (300,000) rows except the Prod_Lot_Stage_Wise_Detl table which contains around 2 Million records. When I execute the query, below, without including the LotStgWs.Finished_Pcs field (which belongs to the Prod_Lot_Stage_Wise_Detl table), the resu...
I am getting data like Result ------ 10 23 21 But i want to get data in the following format. Result ------ 10, 23, 21 How to get that in a Query? Thanks in advance for any help :) ...
Can you please tell me how to integrate the CR 2008 into VS 2008 Team System as currently VS doen't give me the option to create Crystal reports using a template!!! Please guide me... The data on which the reports are to be based are in SQL Server 2000. ...
Hello everyone. I have a table containing user-account permissions and I'm trying to write a query to return one row for each user-account combination. Here is what I have. CltKey AcctKey TranTypeID Access 10 2499 10 0 10 2499 11 1 10 2499 12 1 10 2764 10 1 10 ...
Hello, I have a bit of SQL that is almost doing what I want it to do. I'm working with three tables, a Users, UserPhoneNumbers and UserPhoneNumberTypes. I'm trying to get a list of users with their phone numbers for an export. The database itself is old and has some integrity issues. My issue is that there should only ever be 1 type of...
Simple question, as the title suggests: What is the syntax to drop a Stored Procedure (SP) in SQL Server 2000, by first checking that the SP exists? Please provide the full code. ...
A slightly tricky SQL question (we are running SQL server 2000). I have the following table, StoreCount - WeekEndDate StoreCount 2010-07-25 359 2010-07-18 359 2010-07-11 358 2010-07-04 358 2010-06-27 358 2010-06-20 358 2010-06-13 358 2010-06-06 359 2010-05-30 360 2010-05-23 360 2010-05-16 ...