The pay period at my company is from sunday to saturday. How can I calculate these pay periods for the next 5 years. Example: StartPeriod EndPeriod 20050612 20050618 20050619 20050625 20050626 20050702 Thanks, Ninel -- Message posted via http://www.sqlmonster.com...more >>

I used to use the 'FOR' keyword with 'INTO' and 'DO' to take specific actions for each row returned by a SELECT statement. Now I can't find the docs on it and I can't remember it well enough to make it work. Can anyone clarify or point me towards some samples?...more >>

Hi, Is it possible to place a CASE statement within a CASE statement? Thanks Jerry ...more >>

Hi, I would like to know how I can make my T-sql script abend in the middle of my code. Here is my script: set xact_abort on declare @sql varchar(200) declare @p1 varchar(10) declare @p2 varchar(10) set @sql = N'select * from metric_denom_8' print @sql exec sp_executesql N'select * fro...more >>

Can someone help on this. I am having a problem equating two tables in this situation. I am calculating visits by cost centers and hospital campus on the dates when they were opened but for reporting purposes, i need to display each date for the time period hence needed to fill one temporary ...more >>

Hi all, I have created a Reporting Services Report and now I am trying to schedule it to be emailed every week. I made sure the RSReportServer.config file has correct values in SMTPServer(this is set to IP Address of SMTP server) and From(This is set to the e-mail address we created to use...more >>

I have used SQL Loader from Oracle to get data into tables, but i cant find how to convert datetimes. I have the following file that i cant change code description xxxx 06/02/05 this date is in latin format dd/mm/yy but bcp understand this like mm/dd/yy Can i use convert to proper...more >>

I have a stored procedure that inserts a record and creates a unique number from the last maximum unique number + 1. I need to pass that number back to the calling routine (or have the calling routine do a select on the maximum unique directly after). At the moment, I am doing an insert fo...more >>

Hi, I have a file Script.sql. The Scrpit file has a Bulk insert statement like - @qry = 'bulk insert #tempTable from '''+@path+N''' with (DATAFILETYPE=''char'',ROWTERMINATOR=''\r'',CODEPAGE=''RAW'')' exec(@qry) Here @path is the path of another file Script1.sql. The file Script1.s...more >>

The <> in the where clause is causing this query to be extremely slow. Any way I can re-write this query? select * from FROM dbo.ORDER_DETAIL A INNER JOIN dbo.PRODUCT B ON A.PRODUCT_CODE = B.PRODUCT_CODE INNER JOIN dbo.PRODUCT C ON A.PARENT_PRODUCT = C.PARENT_PRODUCT WHERE B.P...more >>

Can anyone tell me if there are a date function that will tell me what day a date fall on like the month function. SELECT DATENAME(month, '03/12/2005') AS 'Month Name' Go This will tell me the month is March… I need to know that March 12,05 falls on a Saturday. ...more >>

This query runs for ever bcos of the <> in the where clause. Any way I can get around it? select * FROM dbo.ORDER_DETAIL A INNER JOIN dbo.PRODUCT B ON A.PRODUCT_CODE = B.PRODUCT_CODE INNER JOIN dbo.PRODUCT C ON A.PARENT_PRODUCT = C.PARENT_PRODUCT WHERE B.PRODUCT_CODE <> B.PAREN...more >>

hi, I read the BOL and it said we may use Openquery to do the Insert, Update, and Delete. Does anyone know how? I tired Select * from openquery(LinkedServerName, 'Insert into customer values ("111")') and of course, it doesn't work please help Ed ...more >>

I see how to remove replication objects and configurations from a replication distributor, disregarding any existing dependencies, by running the following SQL at the distributor server: sp_dropdistributor @no_checks=1 This suffices for my present servers configuration. However, I would...more >>

Is it always possible to rewrite a SELECT statement with a NOT IN subquery as a SELECT statement with a join? The following statement works but I want to return a field in the subquery in my main SELECT. There are no nulls in any of the fields. As I did not provide DDL and sample data an untested...more >>

ALTER TABLE tblTable DISABLE TRIGGER ALL If I put this lines in a proc, will it be for my proc or for the whole database (every user) ALTER TABLE tblTable ENABLE TRIGGER ALL Regards Kenneth ...more >>

I need to create a query that iwll find all patrons who have seen a play in a weeks time. However the patron can have more than 1 ticket to a show. Here is the query that I have now: SELECT rpsClientName,rpsEvent,rpsSeriesDescr,rpsEventDate FROM reportstatic WHERE ...more >>

Hey folks. I have a delete query that, when run from QA takes less than 1 second everytime. However, this delete is part of a stored procedure that contains a lot of other deletes. The procedure takes over a minute to run (causing the UI to timeout waiting for a reply). The execution plan ind...more >>

Can you alter numerous columns? Alter table Test Alter column Col1 int Alter table Test Alter column Col2 int Can i put these together?...more >>

Hi, Is anyone aware of a third party product that can suggest indexes based on a query that works off current table statistics, etc... much like the way the query execution plan estimator works? It seem to me that the query plan estimator could/should have the functionality to suggest inde...more >>

Hi all, Distributed transaction does not rolls back. I have two sqlserver 2000 sp3 standard edition. In server A I defined a basic stored procedure 'KKTest'. It inserts a record into table zPais. The server B is linked to the server A and enabled to run distributed transactions. When I make th...more >>

I'm trying to create an left outer join of a table on itself to fond missing records. I can get it to work if i create temp tables and then join those but not when i use the table itself WORKS: select h.stkhstcsisym into #t1 from stkhst h where h.stkhstdate = 20050601 order by h.stkh...more >>

Hello, A while back I found this way of concatenating from a select statement into one variable. It's very handy in a lot of situations: declare @test varchar(8000) select @test = '' select @test = @test + ', ' + name from sysobjects where type = 'u' order by name select @Test In the ...more >>

An old, reliable COBOL application of ours that connects to our SQL 2000 database suddenly returned this error: SQLCODE:-0000019703 Timeout expired ....and we're not sure why or what it means. Where can I look to find the various SQLCODE values described? I don't see anything in SQL S...more >>

How can I find what tables have a column with IDENTITY property? Thanks. ...more >>

Hi, I want to display float number as 2.3 - 3.5 returned by sql statement. e.g select cast(h.low +' '+h.high as varchar) from .... does not return as expected above. Is there any other way to retrieve in this format. Thank you. Shailesh ...more >>

CREATE PROCEDURE arg_financial_ratio_compare @compID1 int,@compID2 int,@compID3 int,@compID4 int,@sector char(1),@from int as /* create table #temp(pricePerShare float) insert into #temp select pricePerShare from tblxmldata where companyId = @companyID */ create table #retTable...more >>

I'm attemping to Add an index to a SQL VIEW Using the foillowing SQL Script. Create UNIQUE CLUSTERED INDEX CNSRECID On vw_Lead_Generated_By_Detail(RecID) But get the following messages, 'CONTSUPP' is the only table in the VIEW Server: Msg 1935, Level 16, State 1, Line 1 Cannot create i...more >>

hi Can any one give tell when Session_OnEnd event works , cause even when Session.Abandon or TimeOut , the event is not worlking renjith...more >>

Hello everybody I have a problem in passing a variable into the Update command. Thi is my stored procedure CREATE PROCEDURE UpdateTable @tsID INT, @UpdateDesc varchar(1000 A DECLARE @TableNumber IN DECLARE @TableName NVARCHAR(100 DECLARE @SQL VARCHAR(100 /* I have to get the name of the ...more >>

Hi : How to remove time value in datetime field or smalldatetime field for example invoicemaster.invdate = 2005-5-13 09:03:12 update invoicemaster set invdate = dateonly(invdate ) it will return : invoicemaster.invdate = 2005-5-13 How can do it? Thx ...more >>

Hi Guys, I have a small problem, It's like this, I have a PC which has no LAN connection (MSDE Running), I am connecting to that PC tru a Dial-Up line from another PC, when I run the Query Analyzer for that PC IP, it doesn't get connect, but when I go there and restart the SQL Serve...more >>

I have a table, tblRes, to which Public is denied all access, and no other groups have any access granted. If, from a user xuser, I try "select * from tblRes" I get, as expected "SELECT permission denied on object 'TblRes', database 'DB_117033', owner 'dbo'." I have created a stored proc...more >>