hi, These are some questions i found tricky, please try to answer atleast some of questions. 1.The IF UPDATE(column_name) parameter in a trigger defination will return TRUE in case of an INSERT statement being executed on the triggered table --is this statement true/false. 2. Which ...more >>

Hi, Is there a way to pass parameters to a SQL job? Thanks Saps ...more >>

I am writing a stored procedure which executes a dynamic sql statement on a certain table. Something similar to below Create Procedure dbo.myproc As -- create dynamic sql query Declare @query varchar(4000) set @query = 'Select * from TableA' -- -- -- exec(@query) GO I want...more >>

Hi! Weird thing today! I opened Visual Studio 2005 and I can only see the master model msdb tempdb System Databases With the SQL Server management studio I can still see all my development DB, but NOT fron Visual Studio. There has been some maintenance on the machine done by tha ...more >>

I'd like to use OSQL's -Q option to execute "SELECT * FROM tablename", and have it output an Excel-formatted file (using -o output_file) rather than a straight text file. I would normally use BCP to do this since with BCP if you specify .XLS as your output file extension your file will be E...more >>

Hi all, Trying to get max value in the table for the column of the last column listed in the select? Everything works fine except this part? " maxlength = (Select max(datalength(''COLUMN_NAME'')) from Contact )as MaxLength" I know it is wrong, could someone help me out please? SELEC...more >>

Hi, I am running SQL Server 2005 on a Windows 2003 Server for production and on an XP box for development. While on production sever while running 'SQL Server Management Studio' and I try to connect to my development box I receive a timeout error. If I am on the development (XP) and try to ...more >>

I am using DATEDIFF('n',EndTime, StartTime) to return minutes between 2 datetime columns. The problem is that when the StartTime is 23:00:00 PM and EndTime is 07:00:00 AM I get a negative # of minutes. How can get 480 minutes from this calculation. Note the 23:00:00 is yesterday and the 0...more >>

I have this table: (I know how Mr. Celko likes the DDL!) CREATE TABLE [dbo].[ProgramYears]( [ProgramYear] [int] NOT NULL, [StartDate] [smalldatetime] NOT NULL, [EndDate] [smalldatetime] NOT NULL, [TS] [timestamp] NOT NULL, CONSTRAINT [PK_ProgramYears] PRIMARY KEY CLUSTERED ( [Program...more >>

I'm using SQL Server 2005. It doesn't seem to recognize the TRY/CATCH block. I tried this: BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH END CATCH; GO But got error: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near 'TRY'. Msg 156, Level 15, State 1, Line 3 Incorrect...more >>

Hi, mostly in a stored procedures there is a transact cmmand with a lot of parameters. How can i get the real statement which will be executed in the stored procedure? a very simple example SELECT name from customer WHERE id=@id for the case that the stored procedure will be called wit...more >>

I have set up a small DB to mirror on a different SQL Server 2005. The problem is that the following query gives the NULL values for all the columns: select * from sys.database_mirroring where database_id = db_id('TEST') Here is more info about the setup. A database "Test" is set as a PRIMAR...more >>

In order to purge a huge EventLog table, I've created the following two queries: 1) SET ROWCOUNT 10000 WHILE (1 = 1) BEGIN DELETE el FROM CRPCORE.dbo.EventLog AS el WHERE (el.ReportInstanceID IS NULL ...more >>

In order to purge a huge EventLog table, I've created the following two queries: 1) SET ROWCOUNT 10000 WHILE (1 = 1) BEGIN DELETE el FROM CRPCORE.dbo.EventLog AS el WHERE (el.ReportInstanceID IS NULL AND el.EventTime < DateAdd (Week, -2, GetDate())) IF @@ROWCOUNT = 0...more >>

Hi, I posted this question a week ago and I am still working on this problem. I modified the table a bit so this hopefully will be solved. I have a table with the following fields: ID (primary key) productCode productName manufacturer partNumber price imagelocation I need to filter th...more >>

Hi, I would like to know how my Query will look like if I want to find the missing records in in comparing 2 tables I know I can do something like left outer join and then have the key column = null: SELECT * FROM tableA a LEFT OUTER JOIN tableB b on b.ID = a.ID WHERE b.ID is null ...more >>

Hi, If I have a SQL statement: RAISERROR ('The record doesn't exist',16,1) What do the 2nd and 3rd parameters, 16 and 1, indicate? Thanks! ...more >>

Hi, I have the following SQL statements in a stored procedure: IF @Agent1_TimeDifference >= 100 BEGIN RAISERROR (@ErrorDescription1,16,1) RETURN 1 END IF @Agent2_TimeDifference >= 30 BEGIN RAISERROR (@ErrorDescription2,16,1) RETURN 1 END This seems to retu...more >>

I have had several examples of this with different queries. Each time I have run the queries back to back several times to ensure that both queries took advantage of any caching that ocurred. The difference in the queries is that one uses a variable and the other uses a constant in the where...more >>

Simple question and I can provide data if necessary. How do you allow a subquery to return more that 1 row? ...more >>

Hi expert RM_num trans_Date item_code Qty Trans_O/B O/B 1 01/04/2007 1 Null 1000 1000 2 01/04/2007 1 10 1010 1000 3 01/04/2007 1 ...more >>

hi sql server 2000 and 2005 i have a bunch of .txt files with data that i need to import into my sql database here are the requirements 1. import the txt data into stage tables 2. transform the data into summary tables what's the best and fastest way to do it? i read about bulk...more >>

Hi All, I am using MS SQL server 2005. It seems that LEN function is not giving back the expected value for variables declared as VarChar(MAX). Please see the following snippets: A. Here a variable is declared as VarChar(20). And the behaviour differs. *********************************...more >>

Hi, Trying to insert flat file into table bulk insert dbo.history from 'c:\lvsgc\HistInsertNoOldNoQuote.csv' with (ROWTERMINATOR = char(10)) end get error message Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'char'. The field types in the db and flat file are correct data t...more >>

Hello, I am in the process of patitioning the table. . Each partition is Year based . 1 Partition - Data before Year 2000 2 Partition - 2000 16 Partition - 2014 17 Partition - 2015 18 - 2016 19 - 2017 20 - 2018 and above I wo...more >>

Hello, The following query is a simplified query which returns four fields as shown below select t1.[Name], t2.[CodeName], t2.[FieldName], t3.FixDate, t3.FixValue from table1 t1 inner join table2 t2 on t1.id = t2.id inner join table3 t3 on t2.id=t3.id where t1.IndexID in...more >>

The Stored Procedure below returns article attributes for my articles (tblArticleAttribute). This works fine IF NOT tblArticleAttribute.Content is empty, then it returns all templatedefinitions with the Content. But, if the Content is empty, it also has to return all template definitions, but ...more >>

I have a header table called Batch and the transaction table called GLTRAN. I am trying to identify all batches where the Batch.debittotal does not equal the sum of the Gltran.debitamt in that batch and also the where the Batch.credittotal does not equal the sum of the Gltran.credittotal in tha...more >>

Noob database programmer. I have a user table with fields user.recid and user.fk_sites_RecID making up my primary key. fk_Sites_RecID points to a record in the sites table and user.RecID is the record ID of the user. fk_sites_recid is a new field, introduced after we needed to be able to supp...more >>

e.g. Select * From (EXEC MyStoredProc @param1, @param2) Is this possible? JJ ...more >>