sql-server-2000

The result of stored procedure to a table, without creating the table manually in SQL Server

What I need to do is, put the results of stored procedure (with lots of parameters) in to a table. Something like that: SELECT * INTO Table_1 FROM ( EXEC [MY_DataBase].[dbo].[GET_Report] '%%', '%%', '%%', 'x', 'x', 'x', 'x', 'x', 'x' .... ) X However this seems to be an incorrect syntax, I searched for it and people are first creatin...

How to identify last row on a insert statement

Hello, I want to know which record is the last inserted on Detail Table B in the following order... for example If I have : 1 row Header Table A -------------------- 1 row Detail Table B 2 row Detail Table B 3 row Detail Table B 4 row Detail Table B (last) I want to do some T-SQL or run a procedure if the 4 row is inserted... Thanks i...

TSQL to know database role members

Hi, I am using SQL 2000 and SQL 2005. I want to know which logins have db_owner or db_accessadmin rights to which databases. I can click on users or database roles in every database to see that. Could this be done in an easier way using TSQL? Thanks in advance ...

SQL query with multiple indexes - SQL server 2000

I use a similar query like this select.....from.. with... (INDEX=IX_TABLE_1, INDEX=IX_TABLE_2)... I get the following error Only one list of index hints per table is allowed This seems to be working well with SQL Server 2005. Is it an issue with SQL server? ...

Finding Orphaned Records in SQL Server 2000

I am saddled with an ERP database which lacks any foreign keys and therefore lacks referential integrity. I am writing a script to check most of the major tables in our database for ophaned records. For example, in this case I'm working with our Sales tables. SLCDPM - Customer Master Table SOMAST - Sales Order Master SOITEM - Sales...

Simpilify query for SQL table with 12 columns for month

I have a table in production with 12 columns for each month. I need to create a SP where I pass in a productID, Customer and Month parameter and retrieve the sum of that month. currently my logic is if month = 1 then select sum(JAN) from table where productID = @id and customer = @cust if month = 2 then select SUM(FEB) from table ...

SQL Question: Getting Datediff in days elapsed for each record in a group.

Given this table: How can I get the datediff in days between each status_date for each group of ID_Number? In other words I need to find the number of elapsed days for each status that the ID_Number has been given. Some things to know: All ID_Number will have a received_date which should be the earliest date for each ID_Number (bu...

SQL Server: what can a stored procedure do that a user defined function cannot?

Can you tell me what is the need for a stored procedure when there is UDF? ...

Specific Subsonic Queries Not Working on SQL Server 2000

I'm using Subsonic 3 and am just starting the process of putting my application into production. Locally, I develop using SQL Server 2008 but the production environment is using SQL Server 2000. I am getting the following error for paging: 'ROW_NUMBER' is not a recognized function name. Description: An unhandled exception occurred du...

NAMED PIPES sql server

i know Named Pipes are Protocols. but i have not seen even 1 example how to use them? can u tell me what are the Named pipes ? ...

How to Copy a database in sql 2000?

How to create a new database that is the exact copy of an existing one(in sql 2000)? e.g:Existing database : DB1 I have to create a new database named 'DB2' that is the exact copy of old database 'DB1'.How i can create it in sql 2000? ...

SQL Server 2000, Do queries executed sequentially ?

I am not sure if my question is related with the tool for executing the queries. (I am using Query Analyser, Access and AQT generally). For example I have two queries. SELECT * FROM Table1 SELECT * FROM Table2 So, do the queries executed sequentially or at the same time? If the are executed at the same time, how to make them execute...

SQL: Query to return inaccurate data entry

My company needs to write a query that determines when data is being entered inaccurately. We have a table in our db called "ProductChanges". It looks (something) like this - Product User ChangeDate OldValue NewValue 2344 John 24/01/10 10:00 2344 4324 6435 Greg 28/01/10 13:30 ...

Wrong week number using DATEPART in SQL Server

I've got the problem that select datepart(ww, '20100208') is returning as result week 7 in SQL Server 2000. But 08.02.2010 should be week 6 according to the ISO 8601 specification! This is causing problems in delivery week calculations. What should I do to get week number values according to ISO 8601? ...

Concatenate rows (sql server 2000)

I have the following problem when using SQL Server 2000 The following table has around 200 rows. Company / Employee 1005 / A 1005 / B 1005 / C 1010 / X 1010 / Y 1020 / L 1020 / M etc etc I wish to create the following (comma separated) output: Company / Employees 1005 / A, B, C 1010 / X, Y 1020 / L, M etc etc I'm having a really...

Subsums in SQL Server 2000

Is there any nice way to get subsums in one query for data like this in sql server 2000? Input: Date Value 2008-06-20 10 2008-08-20 20 2008-10-05 5 2008-10-09 30 Desired output: 10 --sum of 1st value 30 --sum of 1st and 2nd values.. 35 65 ...

SQL Server Trigger loop

Hi, I would like to know if there is anyway I can add a trigger on two tables that will replicate the data to the other. For example: I have a two users tables, users_V1 and users_V2, When a user is updated with one of the V1 app, it activate a trigger updating it in users_V2 as well. If I want to add the same trigger on the V2 table...

SQL Server money data type problem

We have SQL Server 2000 database with money data type columns and we have strange problem with numbers within money columns in which we store numbers with 2 decimal places. For o long time everything was OK. But now I see that in some rows where was number 47.22 is now number 47.2178. When i select CAST(COLUMN as CHAR) result is 47.22 bu...

How can I find sql server port number from windows registry?

How can I find sql server port number from windows? Is there any generic way to find port number for sql server 2000, 2005 and 2008? ...

How to check if a Statistics is auto-created in a SQL Server 2000 DB using T-SQL?

Hi all. A while back I had to come up with a way to clean up all indexes and user-created statistics from some tables in a SQL Server 2005 database. After a few attempts it worked, but now I gotta have it working in SQL Server 2000 databases as well. For SQL Server 2005, I used SELECT Name FROM sys.stats WHERE object_id = object_id(@t...