I've got a transaction problem which involves foreign keys. Table Product_Category ----------------------- cat_id PK name Table Product ---------------- prod_id PK cat_id FK Basically i have two tables, i wish to add the category and product at the same time in a transaction. con...more >>

Hi All, I want a query that lists the records in this format. Month LId Count of Counter for each LId The input for this query is going to be the LId and Date Range. (for eg.. 08/01/05 to 09/01/05). The schema is attached below. The Month is date interval that corre...more >>

I know there has to be a simple explanation, but transferring my SQL skills from FoxPro to MSSQL doesn't always go smoothly. For instance, this, an equivalent of which seems to work fine in FoxPro: use northwind select customerid, datepart(year, orderdate) as ordyear, sum(freight) from o...more >>

All: Is there a recommended tool to generate C# or VB.NET (.NET 1.1) code to call exisiting SQL Server (2000 or better) stored procedures? I wrote one a few years ago for Oracle and figured one would exist for SQL Server. I have found: AutoSproc (MSND) SPGen (Code Project) SP/Invoke (Co...more >>

I need to create a list (one column) of distinct values from three fields in a table (LOT_ID, ID_BLENDED_WITH_1, ID_BLENDED_WITH_2). I have included DDL and sample data just in case. I want to be able to query LOT_PROD with something like: SELECT DISTINCT ID_LOT, ID_BLENDED_WITH_1, ID_BLE...more >>

I've Just started using MS SQL 2000 with MS Access 2000. I can select the first 10 products like this :- SELECT top 10 dbo_products.ProductName FROM dbo_Product ORDER BY dbo_Product.ProductName DESC; But when the user wants the next 10, products 11-20 and then maybe products 21-30. How do...more >>

I have been having a similar discussion on the MS Access newsgroup for the last week and I wanted to discuss the issues in the context of MS SQL in addition to MS Access. I hope this form of cross posting is not offensive to anyone. Lets assume I have to relations: student and course and I wan...more >>

Hi, I would like to only bring back results in my database where the app_date is less than six years old. Any ideas on how I can do this? Appreciate the help. Damon ...more >>

Hi I=B4m a mexican SQL Server DBA and I would like telecommute, I can help you on database designs my rates are just $10.00 per hour thanks ...more >>

How does one test for #temp tables in the database. I tried the following: if exists (select * from dbo.sysobjects where id = object_id(N'dbo].[#tempVS2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) --if OBJECT_ID('#tempVS2')is NOT NULL DROP Table #tempVS2 GO CREATE TABLE [#tempVS2] (...more >>

Hi, I have a file loaded into a table as follows Table 1 C1 C2 C3 C4 1 Header 2 LREF A001 3 LN Mr Smith 4 LA Street1 Town 5 LREF A002 6 LN Mr Jones 7 LA Street2 Town What I nee...more >>

Hi, wonder if anyone can help with the following: I need to get the information regarding the parameters (both input and output) accepted by a stored procedure and details of any resultsets returned by it. I can get the former by running sp_sproc_columns (or writing my own query against the...more >>

He, want i want to do ist creating a dynamic query, execute it and access the result via transact-sql. e.g. SELECT * FROM udf_buildquery 'param1' .. WHERE .. The first thing i tried was to use dynamic sql in udf's, but i realised very fast, that this wont work. After that I tried to build ...more >>

Hello, Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger? I get an "invalid object name" with these statement. set @sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted' EXEC sp_executesql @sqlcmd Any other suggestions? Thanks! Per ...more >>

Hi, I have a job that has three steps which does the job but ends up with this status and stop job does not help. How can I find out the spid of the job? to kill it Here are the actual steps 1) exec servername.master.dbo.op_backupUSB 'pdl' where the stored procedure is CREATE procedu...more >>

Hello, I have a general trigger program used for many tables, but how do I refer to the tablename currently been modified in my script? Thanks! Per ...more >>

Dear all, if i delete a row in a table accidently , how can i recover that rows in case 1: just delete it a few second before in case 2: delete it and add several rows after deleted rows...more >>

Ok, I give up. I need to GROUP BY state... keep the newest 10 records in each state... then delete all other records. (Why do the simplest *SOUNDING* queries... always stump me?) CREATE TABLE MyTable ( MyKey Int IDENTITY(1,1) PRIMARY KEY, ...more >>

Hi, Is it possible to pass the table name as parameter in Stored Proc. If possible how to do this? thanks in advance. -DNK ...more >>

Hi Everyone, Sorry for this post, but I am not sure where to post this: I would like to know if anyone has any idea on a good training center in the Asia-Pacific Region for Advanced TSQL programming (SQL 2000) that primarily focuses on optimization. The target audience are Senior Developer...more >>

hi i would like to sort the results of an SP. how do i do that w/o using the order by clause in the SP? because this SP is accessed by other users as well it already sorts on one column. is there any way that i can do select * from (exec dbo.p)as a order by 1. so that i dont alter the te...more >>

Dear How can I use aggregate function or something like that among columns? e.g., columnA, columnB set @varC=something I would like to compare the three values and select max or min value. Something like, select max(columnA, columnB, @varC) from Table DDL -----------------------...more >>