InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 08 Nov 2009 01:32:18 GMT20http://www.sqlservercentral.com/Forums/Topic815468-145-1.aspxHello all. First off, im not sure if this is the forum i should be posting this on. If it's not im sorry and please direct me to the correct one.The scenario im in right now got me puzzled as to what to do (and boy, i like being puzzled).Ok, so i have 5 .csv files to import, all separated by TAB (\t) with different amount of columns.The application we have in the company import the files into a single table with the amount of columns i specify in the application, but the problem is: i need to specify the maximum amount of columns(22) but instead of inserting nulls for the columns missing on the files that have lesser columns the app just keeps inserting and filling those columns, which completly ruins queries i need to run later on.Now, i can tell the application to import everything into a single varchar column.what i did then was to add a new column to the table to know which file is which worked well if a cursor.i then added a CHAR(9)(tab) before and after each line so i could separate every field with a tally tableso now i got a 1 column table with everything i need separated by file type, but i need to pivot it back into actual columns so i can create 5 temp tables to work with.im not sure this is the correct approach, perhaps something simpler can be done but at this moment i cant think of any and would love some ideasThiago DantasSat, 07 Nov 2009 12:34:29 GMTdant12http://www.sqlservercentral.com/Forums/Topic655374-145-1.aspxHi,I need to know the difference between LinQ and NHibernate in the following characterstics:a. Speed for insert/update/select/deleteb. Database support (Like MySQL, Oracle, etc.,) (I know that:SQL joins hand with both)c. Performance with heavy datas.d. OthersThanksWed, 11 Feb 2009 21:13:55 GMTjchandramoulihttp://www.sqlservercentral.com/Forums/Topic815106-145-1.aspxHi friends, I created a procedure (converted from Oracle), procedure compiles fine..[code]create proc ref_sed (@com_no int output, @elem no int,..) as declare @val int set @val =0...........if isnull(@elem,0)=0begin exec logerror @com_no, ' ', 'Error in ELEM' set @val=-9 goto endProcendgo[/code] When I compile this proc, I get A GOTO statement references the label 'Endproc' but the label has not been declared. When i take out the line 'goto endproc', it compiles fine...Please help.thanksFri, 06 Nov 2009 11:27:11 GMTnewbieuserhttp://www.sqlservercentral.com/Forums/Topic815327-145-1.aspxIs there a way to force one or more SQL statements to execute together, even if other connections are trying to process transactions simultaneously? Basically I'm looking for a guarantee similar to sychronized methods in Java, but at the database level.Thanks,KevinFri, 06 Nov 2009 23:10:03 GMTkcook-1066767http://www.sqlservercentral.com/Forums/Topic813944-145-1.aspxHi,I created a shell script (Windows XP) that runs a stored procedure (SQL-2005) using OSQL ... and directs output to a file. The problem is that the file is text format and all fields are jammed into one line. Is there a way to output the data into a readable format, like excel?ThanksWed, 04 Nov 2009 18:14:44 GMTalarkhttp://www.sqlservercentral.com/Forums/Topic815200-145-1.aspxRecently i have update vista and now iI have problem starting sql server.When I try to start sqlexpress in services I get 3417 error.I try different soloution but without success.I allowed remote connection in surfer area configuration, changed log on from network service to local system but nothing.Please Help.Thanks in advance.Fri, 06 Nov 2009 14:38:07 GMTmarko01draganhttp://www.sqlservercentral.com/Forums/Topic814370-145-1.aspxPerformance problem with SQL queries having a specific column mentioned in WHERE or GROUP BY clause.**Table structure:** CREATE TABLE [dbo].[RPT_LOG_SEARCH_WEB_T]( [SEARCH_DATE] [smalldatetime] NULL, [IP] [nvarchar](20) NULL, [BAR_ID] [uniqueidentifier] NULL, [QUERY] [nvarchar](2000) NULL, [QUERY_STRING] [nvarchar](3000) NULL, [SRC] [nvarchar](6) NULL, [HTTP_STATUS] [int] NULL, [CS_USER_AGENT] [nvarchar](4000) NULL, [COUNTRY] [nvarchar](6) NULL, [PID] [nvarchar](6) NULL, [TEST_ID] [nvarchar](6) NULL, [TEST_GROUP_ID] [nvarchar](500) NULL, [S_COMPUTERNAME] [nvarchar](50) NULL, [BROWSER_TYPE] [nvarchar](15) NULL, [BROWSER_TYPE_ID] [smallint] NULL, [BROWSER_VERSION] [varchar](10) NULL ) ON [RPT_LOG_SEARCH_WEB_PS]([SEARCH_DATE]) GO SET ANSI_PADDING OFF GO /****** Object: Index [RPT_LOG_SEARCH_WEB_CI] Script Date: 11/05/2009 18:19:33 ******/ CREATE CLUSTERED INDEX [RPT_LOG_SEARCH_WEB_CI] ON [dbo].[RPT_LOG_SEARCH_WEB_T] ( [SEARCH_DATE] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [RPT_LOG_SEARCH_WEB_PS]([SEARCH_DATE])**Row Count:**4,137,403,549**The following query runs fine:** select query, count(*) from dbo.RPT_LOG_SEARCH_WEB_T where SEARCH_DATE > '20091105' group by query**The following query RUNS FOREVER:** select ip, count(*) from dbo.RPT_LOG_SEARCH_WEB_T where SEARCH_DATE > '20091105' group by ipAny help on the subject will be appretiated...Thu, 05 Nov 2009 09:35:08 GMTdaniell-1020276http://www.sqlservercentral.com/Forums/Topic814651-145-1.aspxI have two columns in a table representing the primary key; I want the ability to auto-increment one of these columns relative to the other primary key. For example, lets call the columns PK1 and PK2, and add one more additional column called data, and let's assume the following data is present:PK1 PK2 Data0 0 AAA0 1 BBBB1 0 CCCCCC1 1 DDDDDD1 2 EEEEEEEI want the ability to insert a new row, auto increment PK2, but relative to a given PK1. Let's say the case above, I want to insert a row for PK1 = 0, Data = FFF. If I make PK2 an Identity Column, the upon inserting it adds the following row:PK1 PK2 Data0 3 FFFBut I want it to auto-increment PK2 by 1, relative to the condition PK = 1:PK1 PK2 Data0 2 FFFI can use a SELECT MAX(PK2) + 1 WHERE PK1 = ?, followed by an INSERT INTO. But this has the risk of deadlocks in a multi-threaded, high volume environment.Any thoughts?Thanks,KevinThu, 05 Nov 2009 19:44:18 GMTkcook-1066767http://www.sqlservercentral.com/Forums/Topic814231-145-1.aspxI am getting error "SQLError: [40001],[Microsoft][SQL Native Client][SQL Server]Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."Any suggestion to nail down the actual issue and cause of deadlockThu, 05 Nov 2009 07:32:23 GMTloveleshghttp://www.sqlservercentral.com/Forums/Topic813684-145-1.aspxIn proc, current loggin is impersonated to "somedb-admin" to access objects in other db. So when the procs are called from asp.net, one proc tries to access objects from another data base by impersonation from "sa" to "somedb-admin" and we get this error. The server principal "somedb-admin" is not able to access the database "CAA" under the current security context.Recently db was restored from live to test and data base ids were also corrected. we have also executed EXEC sp_change_users_login 'Report'; to know orphan users, but nothing was returned.cheersSiddarthWed, 04 Nov 2009 09:39:29 GMTsiddartha palhttp://www.sqlservercentral.com/Forums/Topic814167-145-1.aspxHi All, I Have 3 Tables where each table needs to get inserted with 100 Records.1st Table primary key is referenced by 2nd and the 3rd Tables as foreign key.So I can insert one row in the 1st table and get the primary key of the inserted row and insert the second and the third table. I am repeating this process for 100 times .Is there any better way to do it .Thu, 05 Nov 2009 05:52:11 GMTrelango2http://www.sqlservercentral.com/Forums/Topic813530-145-1.aspxHi friends,Presently in order to synchronize two databases w.r.t tables, columns and constraints we use a tool. I was wondering is it possible to have a query to compare the schema of two different dbs keeping one as master and the other as slave and all those master tables that are not in slave should be pointed out and if possible the query should make a DDL for such a table with Create or Alter depending upon their existence and differences.Thanks.Regards,Waqas.Wed, 04 Nov 2009 05:01:11 GMTrwaqashttp://www.sqlservercentral.com/Forums/Topic814086-145-1.aspxDear All,I need to add a row to the result of SQL statement.The Sql Statement is:select ID, name from myTable;the result:ID name1 name12 name23 name3the previous result comes from the table, but I need to add a row in the result which is not in the table.Is it possible to display a row in the result which does not exist in the table?RegardsThu, 05 Nov 2009 02:46:57 GMTobarahmehhttp://www.sqlservercentral.com/Forums/Topic814160-145-1.aspx-- Grant the IMPERSONATE permission on the SQLUser1 user so it can switch execution context to --SQLDBA GRANT IMPERSONATE ON USER:: SQLDBA TO SQLUser1 this commands is used so that SQLUser1 can be impersonated to SQLDB.Now, I want to know with some sql query account SQLuser1 is impersonated with which account.the sql should return SQLDBA as result.Thu, 05 Nov 2009 05:38:30 GMTsiddartha palhttp://www.sqlservercentral.com/Forums/Topic813535-145-1.aspxHi All,I have a table which contains a number of columns all set as NVARCHAR(50).One column in particular i want to query is a column called "Date" (lol)Example date in column "Date" :08/11/199926/11/199926/11/1999Unfortunately the column is set to NVARCHAR(50) so I cannot do:Select *FROM ClientWHERE date >= '08/11/1999'I have tried things like:select cast('bgn date' as datetime)from dbo.ClientDoes not workHow can I resolve this?Wed, 04 Nov 2009 05:17:17 GMTA Little Help Pleasehttp://www.sqlservercentral.com/Forums/Topic813032-145-1.aspxhi i have created a stored procedure in sqlserver 2008 that executes dynamic sql.for ex:create proc test(@empid varchar(30),@fromdate date,@todate date)asbegindeclare @dept varchar(50),@sql nvarchar(max)select @dept=deptname from emp_dept where empid= @empid;set@sql=' select firstname'+'lastname,sal from emp where dept in ('+@dept+')and [b]convert(varchar,joindate,101) between'+ @fromdate+' and '+@todate;[/b]execute(@sql);endi am getting errors related to date conversioncan any one help me on thisThanks [i]Rock[/i]...Tue, 03 Nov 2009 09:41:38 GMTrockingadminhttp://www.sqlservercentral.com/Forums/Topic813536-145-1.aspxi track diskspaces of my servers into the databaseNow, sometimes the script runs multiple days a day and a server can have multiple disks,What i need as result isthe last data for all disks per server This is, for each server, show me te latest caputered data for each disk on a certain dateFor example:For server SRV_ID 17 : captured date 26/10/2009 this would be the records with DSP_ID :41,42,43[quote]CREATE TABLE [Informatica].[tblDiskSpace]( [DSP_ID] [int] IDENTITY(1,1) NOT NULL, [SRV_ID] [int] NULL, [DSP_DISKNAAM] [varchar](2) NULL, [DSP_MAXSPACE_GB] [numeric](14, 4) NULL, [DSP_SPACEFREE_GB] [numeric](14, 4) NULL, [DSP_PROCENTFREE] [numeric](14, 4) NULL, [DSP_DATUM] [datetime] NOT NULL, CONSTRAINT [PK_tblDiskSpace] PRIMARY KEY CLUSTERED ( [DSP_ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOSET IDENTITY_INSERT [Informatica].[tblDiskSpace] ONINSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (1, 13, N'C:', CAST(297.0775 AS Numeric(14, 4)), CAST(237.9588 AS Numeric(14, 4)), CAST(80.0999 AS Numeric(14, 4)), CAST(0x00009CAA010B5764 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (2, 13, N'D:', CAST(1.0015 AS Numeric(14, 4)), CAST(0.9765 AS Numeric(14, 4)), CAST(97.4976 AS Numeric(14, 4)), CAST(0x00009CAA010B5793 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (3, 13, N'F:', CAST(465.7593 AS Numeric(14, 4)), CAST(24.6400 AS Numeric(14, 4)), CAST(5.2903 AS Numeric(14, 4)), CAST(0x00009CAA010B57B8 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (4, 20, N'C:', CAST(10.0044 AS Numeric(14, 4)), CAST(5.5811 AS Numeric(14, 4)), CAST(55.7860 AS Numeric(14, 4)), CAST(0x00009CAA010B5B08 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (5, 15, N'C:', CAST(103.7775 AS Numeric(14, 4)), CAST(15.9317 AS Numeric(14, 4)), CAST(15.3518 AS Numeric(14, 4)), CAST(0x00009CAA010B5EB7 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (6, 15, N'D:', CAST(8.0101 AS Numeric(14, 4)), CAST(0.4320 AS Numeric(14, 4)), CAST(5.3930 AS Numeric(14, 4)), CAST(0x00009CAA010B5EDC AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (7, 22, N'C:', CAST(29.0712 AS Numeric(14, 4)), CAST(15.3241 AS Numeric(14, 4)), CAST(52.7123 AS Numeric(14, 4)), CAST(0x00009CAA010B6265 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (8, 22, N'D:', CAST(38.6390 AS Numeric(14, 4)), CAST(20.8565 AS Numeric(14, 4)), CAST(53.9778 AS Numeric(14, 4)), CAST(0x00009CAA010B628F AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (9, 22, N'H:', CAST(299.9961 AS Numeric(14, 4)), CAST(197.2199 AS Numeric(14, 4)), CAST(65.7408 AS Numeric(14, 4)), CAST(0x00009CAA010B62B5 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (10, 18, N'C:', CAST(19.5332 AS Numeric(14, 4)), CAST(5.2291 AS Numeric(14, 4)), CAST(26.7706 AS Numeric(14, 4)), CAST(0x00009CAA010B6E7B AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (11, 18, N'D:', CAST(113.1914 AS Numeric(14, 4)), CAST(55.2936 AS Numeric(14, 4)), CAST(48.8497 AS Numeric(14, 4)), CAST(0x00009CAA010B6EA0 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (12, 18, N'S:', CAST(4.0039 AS Numeric(14, 4)), CAST(0.9832 AS Numeric(14, 4)), CAST(24.5565 AS Numeric(14, 4)), CAST(0x00009CAA010B6EC1 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (13, 14, N'C:', CAST(15.0067 AS Numeric(14, 4)), CAST(1.7058 AS Numeric(14, 4)), CAST(11.3669 AS Numeric(14, 4)), CAST(0x00009CAA010B74AB AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (14, 21, N'C:', CAST(4.9869 AS Numeric(14, 4)), CAST(1.3925 AS Numeric(14, 4)), CAST(27.9231 AS Numeric(14, 4)), CAST(0x00009CAA010B7818 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (15, 21, N'E:', CAST(199.9974 AS Numeric(14, 4)), CAST(177.6328 AS Numeric(14, 4)), CAST(88.8176 AS Numeric(14, 4)), CAST(0x00009CAA010B7839 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (16, 23, N'C:', CAST(45.0048 AS Numeric(14, 4)), CAST(1.8375 AS Numeric(14, 4)), CAST(4.0829 AS Numeric(14, 4)), CAST(0x00009CAA010B7C44 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (17, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2556 AS Numeric(14, 4)), CAST(17.1405 AS Numeric(14, 4)), CAST(0x00009CAE01009703 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (18, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE01009DC4 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (19, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE01009DFD AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (20, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2556 AS Numeric(14, 4)), CAST(17.1409 AS Numeric(14, 4)), CAST(0x00009CAE010138EF AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (21, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE01013927 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (22, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE0101394D AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (23, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2556 AS Numeric(14, 4)), CAST(17.1409 AS Numeric(14, 4)), CAST(0x00009CAE01018237 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (24, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE01018261 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (25, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE01018290 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (26, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2546 AS Numeric(14, 4)), CAST(17.1278 AS Numeric(14, 4)), CAST(0x00009CAE01046462 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (27, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE01046495 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (28, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE010464C4 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (29, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2546 AS Numeric(14, 4)), CAST(17.1278 AS Numeric(14, 4)), CAST(0x00009CAE010512A1 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (30, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE010512D9 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (31, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE0105130C AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (32, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2546 AS Numeric(14, 4)), CAST(17.1278 AS Numeric(14, 4)), CAST(0x00009CAE0105F0FA AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (33, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE0105F12E AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (34, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE0105F158 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (35, 20, N'C:', CAST(10.0044 AS Numeric(14, 4)), CAST(5.5689 AS Numeric(14, 4)), CAST(55.6648 AS Numeric(14, 4)), CAST(0x00009CAE0106295B AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (36, 15, N'C:', CAST(103.7775 AS Numeric(14, 4)), CAST(15.5394 AS Numeric(14, 4)), CAST(14.9738 AS Numeric(14, 4)), CAST(0x00009CAE01063131 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (37, 15, N'D:', CAST(8.0101 AS Numeric(14, 4)), CAST(0.4320 AS Numeric(14, 4)), CAST(5.3930 AS Numeric(14, 4)), CAST(0x00009CAE01063173 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (38, 22, N'C:', CAST(29.0712 AS Numeric(14, 4)), CAST(15.3224 AS Numeric(14, 4)), CAST(52.7065 AS Numeric(14, 4)), CAST(0x00009CAE01063518 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (39, 22, N'D:', CAST(38.6390 AS Numeric(14, 4)), CAST(20.7474 AS Numeric(14, 4)), CAST(53.6955 AS Numeric(14, 4)), CAST(0x00009CAE0106355A AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (40, 22, N'H:', CAST(299.9961 AS Numeric(14, 4)), CAST(197.2138 AS Numeric(14, 4)), CAST(65.7388 AS Numeric(14, 4)), CAST(0x00009CAE01063588 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (41, 17, N'C:', CAST(7.3251 AS Numeric(14, 4)), CAST(1.2546 AS Numeric(14, 4)), CAST(17.1278 AS Numeric(14, 4)), CAST(0x00009CAE01063B23 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (42, 17, N'D:', CAST(90.4577 AS Numeric(14, 4)), CAST(1.7027 AS Numeric(14, 4)), CAST(1.8823 AS Numeric(14, 4)), CAST(0x00009CAE01063B48 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (43, 17, N'S:', CAST(3.9067 AS Numeric(14, 4)), CAST(2.9081 AS Numeric(14, 4)), CAST(74.4377 AS Numeric(14, 4)), CAST(0x00009CAE01063B72 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (44, 18, N'C:', CAST(19.5332 AS Numeric(14, 4)), CAST(6.8323 AS Numeric(14, 4)), CAST(34.9780 AS Numeric(14, 4)), CAST(0x00009CAE01063ED6 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (45, 18, N'D:', CAST(113.1914 AS Numeric(14, 4)), CAST(55.2156 AS Numeric(14, 4)), CAST(48.7807 AS Numeric(14, 4)), CAST(0x00009CAE01063F17 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (46, 18, N'S:', CAST(4.0039 AS Numeric(14, 4)), CAST(0.9832 AS Numeric(14, 4)), CAST(24.5565 AS Numeric(14, 4)), CAST(0x00009CAE01063F42 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (47, 14, N'C:', CAST(15.0067 AS Numeric(14, 4)), CAST(1.7019 AS Numeric(14, 4)), CAST(11.3410 AS Numeric(14, 4)), CAST(0x00009CAE010645C6 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (48, 21, N'C:', CAST(4.9869 AS Numeric(14, 4)), CAST(1.3900 AS Numeric(14, 4)), CAST(27.8736 AS Numeric(14, 4)), CAST(0x00009CAE0106497E AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (49, 21, N'E:', CAST(199.9974 AS Numeric(14, 4)), CAST(174.0268 AS Numeric(14, 4)), CAST(87.0145 AS Numeric(14, 4)), CAST(0x00009CAE010649AD AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (50, 19, N'C:', CAST(136.7149 AS Numeric(14, 4)), CAST(68.2211 AS Numeric(14, 4)), CAST(49.9002 AS Numeric(14, 4)), CAST(0x00009CAE01064D81 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (51, 23, N'C:', CAST(45.0048 AS Numeric(14, 4)), CAST(1.8177 AS Numeric(14, 4)), CAST(4.0390 AS Numeric(14, 4)), CAST(0x00009CAE01065188 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (52, 25, N'C:', CAST(19.5328 AS Numeric(14, 4)), CAST(8.0782 AS Numeric(14, 4)), CAST(41.3572 AS Numeric(14, 4)), CAST(0x00009CAE010655E8 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (53, 25, N'D:', CAST(48.7969 AS Numeric(14, 4)), CAST(2.4570 AS Numeric(14, 4)), CAST(5.0351 AS Numeric(14, 4)), CAST(0x00009CAE0106562A AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (54, 25, N'E:', CAST(68.3258 AS Numeric(14, 4)), CAST(26.7084 AS Numeric(14, 4)), CAST(39.0898 AS Numeric(14, 4)), CAST(0x00009CAE0106564F AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (55, 24, N'C:', CAST(15.4050 AS Numeric(14, 4)), CAST(9.3587 AS Numeric(14, 4)), CAST(60.7508 AS Numeric(14, 4)), CAST(0x00009CAE0106CAB6 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (56, 24, N'D:', CAST(52.3051 AS Numeric(14, 4)), CAST(23.1079 AS Numeric(14, 4)), CAST(44.1791 AS Numeric(14, 4)), CAST(0x00009CAE0106CAE0 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (57, 24, N'E:', CAST(67.7484 AS Numeric(14, 4)), CAST(12.4204 AS Numeric(14, 4)), CAST(18.3331 AS Numeric(14, 4)), CAST(0x00009CAE0106CB0B AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (58, 24, N'H:', CAST(272.4570 AS Numeric(14, 4)), CAST(84.5100 AS Numeric(14, 4)), CAST(31.0177 AS Numeric(14, 4)), CAST(0x00009CAE0106CB39 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (59, 13, N'C:', CAST(297.0775 AS Numeric(14, 4)), CAST(235.7953 AS Numeric(14, 4)), CAST(79.3717 AS Numeric(14, 4)), CAST(0x00009CAE010A56A4 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (61, 13, N'C:', CAST(297.0775 AS Numeric(14, 4)), CAST(235.7689 AS Numeric(14, 4)), CAST(79.3628 AS Numeric(14, 4)), CAST(0x00009CAE010ADDE2 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (62, 13, N'D:', CAST(1.0015 AS Numeric(14, 4)), CAST(0.9763 AS Numeric(14, 4)), CAST(97.4789 AS Numeric(14, 4)), CAST(0x00009CAE010ADFC5 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (63, 13, N'F:', CAST(465.7593 AS Numeric(14, 4)), CAST(24.6400 AS Numeric(14, 4)), CAST(5.2903 AS Numeric(14, 4)), CAST(0x00009CAE010AE133 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (64, 13, N'C:', CAST(297.0775 AS Numeric(14, 4)), CAST(234.7917 AS Numeric(14, 4)), CAST(79.0338 AS Numeric(14, 4)), CAST(0x00009CAF00C02410 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (65, 13, N'D:', CAST(1.0015 AS Numeric(14, 4)), CAST(0.9762 AS Numeric(14, 4)), CAST(97.4751 AS Numeric(14, 4)), CAST(0x00009CAF00C02448 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (66, 13, N'F:', CAST(465.7593 AS Numeric(14, 4)), CAST(24.6400 AS Numeric(14, 4)), CAST(5.2903 AS Numeric(14, 4)), CAST(0x00009CAF00C0247C AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (67, 15, N'C:', CAST(103.7775 AS Numeric(14, 4)), CAST(15.5859 AS Numeric(14, 4)), CAST(15.0186 AS Numeric(14, 4)), CAST(0x00009CAF00C03397 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (68, 15, N'D:', CAST(8.0101 AS Numeric(14, 4)), CAST(0.4320 AS Numeric(14, 4)), CAST(5.3930 AS Numeric(14, 4)), CAST(0x00009CAF00C033D8 AS DateTime))INSERT [Informatica].[tblDiskSpace] ([DSP_ID], [SRV_ID], [DSP_DISKNAAM], [DSP_MAXSPACE_GB], [DSP_SPACEFREE_GB], [DSP_PROCENTFREE], [DSP_DATUM]) VALUES (69, 15, N'M:', CAST(232.8266 AS Numeric(14, 4)), CAST(11.5426 AS Numeric(14, 4)), CAST(4.9576 AS Numeric(14, 4)), CAST(0x00009CAF00C03411 AS DateTime))SET IDENTITY_INSERT [Informatica].[tblDiskSpace] OFF/****** Object: Default [DF_tblDiskSpace_DSP_DATUM] Script Date: 11/04/2009 13:00:44 ******/ALTER TABLE [Informatica].[tblDiskSpace] ADD CONSTRAINT [DF_tblDiskSpace_DSP_DATUM] DEFAULT (getdate()) FOR [DSP_DATUM]GO[/quote]I tried to capture the data with group by's but this does not the trick: WITH cteSelectIDs AS ( SELECT MAX(DSP_ID) AS DSP_ID FROM Informatica.tblDiskSpace WHERE dbo.DateOnly(DSP_DATUM) = '2009-10-26' AND SRV_ID = 17 GROUP BY DSP_DISKNAAM, SRV_ID, DSP_DATUM ) SELECT * FROM Informatica.tblDiskSpace AS a INNER JOIN cteSelectIDs AS b ON a.DSP_ID = b.DSP_ID Can someone point me out in the right ways of handling stuff like this.Tnx in advance for any remark,Wkr,Van Heghe EddyWed, 04 Nov 2009 05:18:49 GMTeddy-644184http://www.sqlservercentral.com/Forums/Topic813121-145-1.aspxI know there is a bit of sql code here but I need some help with getting the correct dates created when the start date is greater than the user selected days. This project is to mimic the recurrence settings in MS Outlook to a minimum. I am probably not approaching this project from the correct direction but if anyone of the Expert out there could help me get the sql code to create the dates when the start date is greater than the user selected days. Basically what is happening here is a user is looking at the week option of the MS Outlook Recurrence settings and selecting any pattern of days, Sunday thru Saturday with a certain Start Date.Thanks in advance for your help.[code="sql"]use sandbox--test the spDECLARE @iEventID int, @dtStartDate datetime,@dtStartTime datetime,@dtEndTime datetime,@iWeekFreq int,@iDayMask intDECLARE @dtRangeEndDate datetime,@vRangeEndType nvarchar(10),@iRangeEndOccurs int,@vSelectedDays nvarchar(7)DECLARE @iSunday int,@iMonday int,@iTuesday int,@iWednesday int,@iThursday int,@iFriday int,@iSaturday intSET @iEventID = 1SET @dtStartDate = '11/5/2009 00:00:00'SET @dtStartTime = '1/1/1900 11:00:00'SET @dtEndTime = '1/1/1900 12:00:00'SET @iWeekFreq = 1SET @dtRangeEndDate = '12/10/2009 00:00:00'SET @vRangeEndType = 'EndDate'SET @iSunday = 1SET @iMonday = 2SET @iTuesday = 4SET @iWednesday = 8SET @iThursday = 16SET @iFriday = 32SET @iSaturday = 64SELECT @iDayMask = @iMonday | @iWednesday BEGIN --declare @dtNewEndDate and @vDateName for sp DECLARE @dtNewEndDate datetime,@vDateName nvarchar(10),@dtStartDateTime datetime,@dtEndDateTime datetime DECLARE @iDayLoop int,@iNumberOfDays int,@iWeek int,@vSelectedDay nvarchar(10) SET @iNumberOfDays = 6 --6 because I use this in the calc with the Power function 0 to 6 = 7 SET @iWeek = 7 --if @vRangeEndType=EndDate Requires an @dtRangeEndDate value IF @vRangeEndType='EndDate' --loop every @iWeekFreq number until the Range end date is reached WHILE @dtStartDate < @dtRangeEndDate BEGIN --***does this sp need an sp to check for EventID? --loop every SET @iDayLoop = 0 SET @vDateName = DATENAME(weekday,@dtStartDate) --find the first day selected then set the date; store selected day's datetime value --for the next selected day's datetime value calc DECLARE @iSelectedDayValue int,@dtSelectedDate datetime,@iDayValue int,@bitIsDaySelected bit,@iStartDayValue int,@dtAdjStartDate datetime WHILE @iDayLoop <= @iNumberOfDays BEGIN --set bitmask day value SET @iDayValue = POWER(2,@iDayLoop) --set bit Is Day Selected to local variable SET @bitIsDaySelected = (SELECT CASE WHEN @iDayMask & @iDayValue = @iDayValue THEN 1 ELSE 0 END ) IF @bitIsDaySelected = 1 BEGIN --find user selected day value to calc new selected date --set the selected day's value below both case stmts SET @iSelectedDayValue = (SELECT CASE WHEN @iDayValue=1 THEN 1 --Sunday WHEN @iDayValue=2 THEN 2 --Monday WHEN @iDayValue=4 THEN 3 --Tuesday WHEN @iDayValue=8 THEN 4 --Wednesday WHEN @iDayValue=16 THEN 5 --Thursday WHEN @iDayValue=32 THEN 6 --Friday WHEN @iDayValue=64 THEN 7 --Saturday END) --find start date name and adjust the date so the selected date --is correct for the user selected date calc below --this case stmt SET @iStartDayValue = (SELECT CASE WHEN @vDateName='Sunday' THEN 1 --Sunday WHEN @vDateName='Monday' THEN 2 --Monday WHEN @vDateName='Tuesday' THEN 3 --Tuesday WHEN @vDateName='Wednesday' THEN 4 --Wednesday WHEN @vDateName='Thursday' THEN 5 --Thursday WHEN @vDateName='Friday' THEN 6 --Friday WHEN @vDateName='Saturday' THEN 7 --Saturday END) IF @iSelectedDayValue < @iStartDayValue --testing --set the insert start date for selected day SET @dtSelectedDate = (@dtStartDate + (@iSelectedDayValue - @iStartDayValue)) SET @dtStartDateTime = (@dtSelectedDate + @dtStartTime) SET @dtEndDateTime = (@dtSelectedDate + @dtEndTime) IF @dtStartDateTime < @dtRangeEndDate INSERT INTO tblEventSchedules ( [EventId],[StartTime],[EndTime] ) VALUES ( @iEventID,@dtStartDateTime,@dtEndDateTime) --increment @iDayLoop SET @iDayLoop = @iDayLoop + 1 END ELSE SET @iDayLoop = @iDayLoop + 1 END --set the next start date according to the week frequency SET @dtStartDate = @dtStartDate + (@iWeekFreq * @iWeek) END --else if @vRangeEndType=OccurNum Requires an @iRangeEndOccurs value-- IF @vRangeEndType='OccurNum' -- BEGIN-- --delcare and set @dtNewEndDate to include the @iRangeEndOccurs value; we are still in the Days @TypeRequest-- --section so we need to still include the @DayFreq number as well-- SET @dtNewEndDate = (@dtStartDate + ((@iWeekFreq * @iRangeEndOccurs) - @iWeekFreq))-- WHILE @dtStartDate <= @dtNewEndDate-- BEGIN-- --this will insert into the EventId, StartTime and EndTime fields into the tblEventSchedules-- --need the EventID before this can happen-- --***does the sp need to check for EventID and New Start and End times?-- SET @dtStartDateTime = (@dtStartDate + @dtStartTime)-- SET @dtEndDateTime = (@dtStartDate + @dtEndTime)-- INSERT INTO tblEventSchedules ( [EventId],[StartTime],[EndTime] ) VALUES ( @iEventID,@dtStartDateTime,@dtEndDateTime)-- SET @dtStartDate = @dtStartDate + @iWeekFreq-- END-- ENDEND--for testing; comment out when building spSELECT * FROM dbo.tblEventSchedules ORDER BY EventID--______________________________________________________________________________________________--below is the create sql for table dbo.tblEventSchedules--______________________________________________________________________________________________--USE [SandBox]--GO--/****** Object: Table [dbo].[tblEventSchedules] Script Date: 11/03/2009 10:25:06 ******/--SET ANSI_NULLS ON--GO--SET QUOTED_IDENTIFIER ON--GO--CREATE TABLE [dbo].[tblEventSchedules](-- [EventScheduleID] [int] IDENTITY(1,1) NOT NULL,-- [EventID] [int] NOT NULL,-- [StartTime] [datetime] NOT NULL CONSTRAINT [DF_tblEventSchedules_StartTime] DEFAULT (getdate()),-- [EndTime] [datetime] NOT NULL CONSTRAINT [DF_tblEventSchedules_EndTime] DEFAULT (getdate()),-- [Comment] [nvarchar](500) NULL,-- [CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_tblEventSchedules_CreatedOn] DEFAULT (getdate()),-- [ModifiedOn] [datetime] NOT NULL CONSTRAINT [DF_tblEventSchedules_ModifiedOn] DEFAULT (getdate()),-- [DeletedOn] [datetime] NULL,-- [CreatedBy] [nvarchar](100) NOT NULL CONSTRAINT [DF_tblEventSchedules_CreatedBy] DEFAULT ('System'),-- [ModifiedBy] [nvarchar](100) NOT NULL CONSTRAINT [DF_tblEventSchedules_ModifiedBy] DEFAULT ('System'),-- [DeletedBy] [nvarchar](100) NULL,-- [Deleted] [bit] NOT NULL CONSTRAINT [DF_tblEventSchedules_Deleted] DEFAULT ((0)),-- [Active] [bit] NOT NULL CONSTRAINT [DF_tblEventSchedules_Active] DEFAULT ((1)),-- [Hide] [bit] NOT NULL CONSTRAINT [DF_tblEventSchedules_Hide] DEFAULT ((0)),-- [SortOrder] [int] NOT NULL CONSTRAINT [DF_tblEventSchedules_SortOrder] DEFAULT ((0)),-- CONSTRAINT [PK_tblEventSchedules] PRIMARY KEY CLUSTERED --(-- [EventScheduleID] ASC--)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]--) ON [PRIMARY][/code]Tue, 03 Nov 2009 11:37:30 GMTscott-733974http://www.sqlservercentral.com/Forums/Topic813543-145-1.aspxHi , Please help me to write a query which needs to fire if number of rows count increases by 'x' that alert needs to send to "xyz" operator .Please help ...Wed, 04 Nov 2009 05:39:16 GMTsqldba725http://www.sqlservercentral.com/Forums/Topic812192-145-1.aspxHi Friends,Do anyone know how to create a new "User" in SQL Server, and give him such privileges that he can access the Databases via .NET application. But the "User" should not be able to access the SQL Server using SSMS.What I mean is are there such user privileges that lets the "User" run queries from .NET Application against the SQL Server Database, but the "User" can not directly use SSMS to view or query the Database?Cheers,BhaveshSun, 01 Nov 2009 22:16:01 GMTbhavesh_183http://www.sqlservercentral.com/Forums/Topic813137-145-1.aspxI am trying to get percentages. I realize that I am doing integer division and the result will be an integer. I have tried to cast the numerator and the denominator and the entire result. I either get 0.000000 or 1.000000Calculation1: sum(a) / (sum a + b) as Percentage (statement without cast/convert).Calculation1: sum(b) / (sum a + b) as Percentage (statement without cast/convert).I am doing this twice and the percentages will be 99.999365% and 0.000635%.Any ideas?Thanks.Tue, 03 Nov 2009 12:09:38 GMTtjforcehttp://www.sqlservercentral.com/Forums/Topic813005-145-1.aspxHello experts,I’m trying to write a code though which I need to add some data in one of my table. At the moment I’ve defined logic and code works fine and insert data in table. However what I’ve observed is when I try to set more then one variable value (after making decision making with if conditions I’m setting values for @project_no and @job variables) the only first variable value gets set and other’s the only last value gets populated in all the rows. For instance if I’m setting @project_no value first in code then it works absolutely fine but @job value doesn’t get fill in rows accordingly and only last value gets fill in all the columns. To understand it better here is the output 1 ME90 Manager 2009-11-03 10:05:17.1672 ME91 Manager 2009-11-03 10:05:17.1673 ME92 Manager 2009-11-03 10:05:17.1674 ME93 Manager 2009-11-03 10:05:17.1675 ME94 Manager 2009-11-03 10:05:17.1671 ME90 Manager 2009-11-03 10:05:17.1672 ME91 Manager 2009-11-03 10:05:17.1673 ME92 Manager 2009-11-03 10:05:17.1674 ME93 Manager 2009-11-03 10:05:17.1675 ME94 Manager 2009-11-03 10:05:17.167And to reference here is code.declare @i integer, @j integerdeclare @project_no char(4)declare @job char(15) declare @enter_date datetimeset @i = 1set @j = 1set @job = 'Analyst'set @enter_date = getdate() while @i <= 2 begin while @j <= 5 begin if (@j = 1) set @project_no = 'ME90' set @job = 'Analyst' if (@j = 2) set @project_no = 'ME91' set @job = 'Junior Cons' if (@j = 3) set @project_no = 'ME92' set @job = 'Technical Cons' if (@j = 4) set @project_no = 'ME93' set @job = 'Sr.Tech Cons' if (@j = 5) set @project_no = 'ME94' set @job = 'Manager' insert into works_on values(@j, @project_no, @job, @enter_date ) set @j = @j+1 end set @i = @i+1 set @j = 1 endGODoes anybody know what can be the reason for it? What is my mistake?Thanks a lot in advanceTue, 03 Nov 2009 09:07:57 GMTmr_adeelalisyedhttp://www.sqlservercentral.com/Forums/Topic600555-145-1.aspxhi declare @lstr varchar(200)declare @word varchar(20)set @lstr = 'select top 1 name from sysobjects'set @word = exec(@lstr) select @wordi want ot store result comes from dynamic query into variable @word.(or above code is not working)Please help Tue, 11 Nov 2008 04:31:21 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic812728-145-1.aspxSQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x15555555). It occurred during a read of page (1:671644) in database ID 14 at offset 0x00000147f38000 in file 'E:\Database\ProductionRep_Data.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.I got this error while exec queryif any face similar probpls help usMon, 02 Nov 2009 22:29:54 GMTvenkuforeverhttp://www.sqlservercentral.com/Forums/Topic812726-145-1.aspxSQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x15555555). It occurred during a read of page (1:671644) in database ID 14 at offset 0x00000147f38000 in file 'E:\Database\ProductionRep_Data.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.I got this error while exec queryIf any face similar problempls help usMon, 02 Nov 2009 22:22:32 GMTvenkuforeverhttp://www.sqlservercentral.com/Forums/Topic812727-145-1.aspxSQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x15555555). It occurred during a read of page (1:671644) in database ID 14 at offset 0x00000147f38000 in file 'E:\Database\ProductionRep_Data.MDF'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.I got this error while exec queryIf any face similar problempls help usMon, 02 Nov 2009 22:24:21 GMTvenkuforeverhttp://www.sqlservercentral.com/Forums/Topic809219-145-1.aspxHi,I have stored procedure which assings tickets to the users , iam facing a problem here where more than one users are getting the same tickets , could some one help me out here , below are the steps in the stored procedure:1)Select the tickets that were Manually assigned to the user by the manger & put them into temp table.2)Select Top 5 tickets with locked=0 & put them into a temporary table created in first step.3)Update the table where tickets are retreived with locked -1 indicating they cant be retrieved again.4)select the final result from the temporary table.Thanks in Advance.Tue, 27 Oct 2009 05:42:34 GMTgiri_aruruhttp://www.sqlservercentral.com/Forums/Topic811493-145-1.aspxhai friendsi want to display the data like the below structuresfor eg:customername grade height widthsqlserver 1024 1000 2000 1000 3000sqlserver1 1025 500 1000 1000 500 3000 2000here customername,grade are repeating data.so i want to display data like this structure .Any ideas and example is given means which is very helpfull for meFri, 30 Oct 2009 06:11:19 GMTrameshduraikannuhttp://www.sqlservercentral.com/Forums/Topic812393-145-1.aspxhi, when u tried to modify the procedure then it gives me error like this...[b]Procedure tr_ProcedureEvents, Line 6Invalid object name 'DDLTriggerTest..EventTableData'.[/b]please let me know what causes this error and how to resolve that.Mon, 02 Nov 2009 08:33:20 GMTFeebiehttp://www.sqlservercentral.com/Forums/Topic812382-145-1.aspxWe have one sql where 4 tables are joined to get the result set. I just want to get records which are not locked. How to apply readpast hints on the sql where more that one table is involved?Mon, 02 Nov 2009 08:04:51 GMTsiddartha palhttp://www.sqlservercentral.com/Forums/Topic812059-145-1.aspxhi every one, i have a table that is used for storing customer numbers of a user. a user may have multiple customers which was seperated by comma in the same field.ex:table:id int pk identitycno varchar(2000)Data will be like this in a table: id cno 1 12,42 2 123,123,123 3 332,132,12,123,13i want to add quotations to the field values based on id only.declare @var varchar(300)select @var= cno from test where id=1;here i want to set @var='12','42' ( need to add quotations)can any give me some idea?ThanksRock..Sat, 31 Oct 2009 16:51:37 GMTrockingadminhttp://www.sqlservercentral.com/Forums/Topic804154-145-1.aspxWe had a nice dicussion today that I thought you guys would be interested in. Somebody made the statement that the ability to select 'all columns' from an object (e.g. SELECT * FROM OBJECT) should be removed from T-SQL to force programmers and DBA's to always use column names. I can think up of some obvious advantages:- The programmer is forced to think about which columns he will use, and not arbitrarily select 20 columns, only to use 3 or 4 (I've seen this happen in every SQL environment I have seen to date), in addition this could mean obvious performance improvements.- It would fix the issue where certain queries would break if extra columns were added to the table.Some obvious disadvantages would be:- Users would be unable to quickly check what's there in each field by using a 'SELECT TOP 100 * FROM OBJECT' query.- Users would be unable to use dynamic OBJECTS (e.g. views or SP's that return different column names each time you query it).In terms of both disadvantages, I'd say that point 1 is unvalid because you should never 'quickly do A or B' inside SQL server. And for point 2 I'd say that that's bad design to start with (I can see very little real life applications for dynamic columns that could not be handled on a row level).Because we didn't get anywhere near a concensus in this discussion I was wondering what people here think about the statement? I was hoping it would spark a nice bit of discussion. :)Fri, 16 Oct 2009 07:30:01 GMTbas de zwarthttp://www.sqlservercentral.com/Forums/Topic811967-145-1.aspxI hav a java application which using tvp.The javaprogram is giviing thousand records per second to sql server using tvp.but sql server is inserting on 485 rows per second.But when i try to insert 40000 records in sql server using tvp(from sql server side) ,it is only taking 0 sec.So i thought it is due to N/w problem.but when the program run in the localhost also taking same time 3-4 sec per 1000 records.So it is not due to network.when we run in .net,it is working fine...is any problem with jdbc?any suitable driver for sqlserver2008.we are used JTDS and microsoft sql server jdbc driver.but the same result?what is the reason for this?*Note : tvp is only available for sql server 2008My server config isIntel(R) xeon(R) CpuE5430@2.66GHz,2.67GHzRam :4GBAny help is appreciated...Fri, 30 Oct 2009 22:48:07 GMTvarunfilimhttp://www.sqlservercentral.com/Forums/Topic810786-145-1.aspxI need to create a store proc which will monitor my sql jobs and write those auditing information on a sql table. here is the senario, I created a table with five fields servername, dbname, jobname, action, datetime I need to insert 2 records one when job starts and one when job ends. action column is a datetime field which will track the job start time and job end time. and datetime field is just a get date. everytime my job is success I need to run this store proc on next step to gather the job log information on the sql table. how do i create such store procThu, 29 Oct 2009 08:08:28 GMTabhisek karkihttp://www.sqlservercentral.com/Forums/Topic811599-145-1.aspxHi and thank you in advance.My environment is 1 SQL SERVER 2005 which I will refer to as server sql20051 SQL SERVER 2000 which I will refer to as server sql2000Sql2005 has a linkserver connection to sql2000.From Sql2005 I'm running the following:3 Declare table statements andINSERT INTO @Table1EXEC sql2000.db.dbo.sp_Get1INSERT INTO @Table2EXEC sql2000.db.dbo.sp_Get2INSERT INTO @Table3EXEC sql2000.db.dbo.sp_Get3No errors are encountered.The problem is that the 3rd INSERT INTO takes about 1 mminute to execute. If if run "EXEC sql2000.db.dbo.sp_Get3" by itself it executes in 1 sec. Also, the third statement only returns 50 records. This is a very trivial issue.Has anyone encounter this issue before? Or can point me on away to troubleshoot this issue.Thank you,Fri, 30 Oct 2009 07:57:42 GMTSQLHhttp://www.sqlservercentral.com/Forums/Topic810817-145-1.aspxForgive me if this has been discussed, but I couldn't find anything. When I develop I end up with 6 instance of SSMS open and inside each instance 6 - 10 windows for developing procs\tables etc... Has anyone found a toll out there that does a better job at managing multiple tabs open at once. The tabs give very little indication on what is inside each window. It would be nice if you could have a bunch of windows open and be able to hover over something and see a good portion of the code pop up or something. Or is this just me?thanks,Thu, 29 Oct 2009 08:32:09 GMTstevoid1970http://www.sqlservercentral.com/Forums/Topic811458-145-1.aspxHiI've got to bring some data through to a program from a database that I was hoping to do using SQL. I've got a table which contains data concerning services provided to customers. This table amongst others contains 3 columns, one which contains the date of the last callout, one which contains the callout interval e.g. 1,2,4,6,8,10 months, and one which contains the end date of the service (when it should expire).What I need to do with is bring through all records where the last callout + the callout interval is less than the end date. I've managed to bring through the first occurence of when this occurs by using the following code where the end date is '2010-02-28': WHERE '2010-02-28' >= (DATEADD(month,cast(U_Interval as int),U_LastCallGen).Now what I need to do is basically do the same thing as above however I need to change the interval so that it is continously doubled until the date is greater than the End Date and if so bring through this as a new line. E.g. If the U_LastCallGen = '2009-05-22' and the U_Interval = 4 then I would need to bring through 2 records one for '2009-09-22' and one for '2010-01-22'. This would list all of the callouts that need to be provided to the customer before their service ends.I think I may have to use Cursors but I'm not sure if I could do this using a simple While loop.Thanks a lotSteveFri, 30 Oct 2009 04:55:52 GMTsteven.trynerhttp://www.sqlservercentral.com/Forums/Topic811361-145-1.aspxHi All,While trying to develop an online reporting panel I'm stuck on a problem I'm unable to find an answer on my own.Hence, the request for you all to help me.[b]Problem:[/b]One of the reports require me to store output of an existing SP and make some calculations on it.Just to make it easy to understand I'm using some naming here so that I can explain it easily.[font="Arial Black"]Top Level (Problem) SP-> SP3Nested SP-> SP2Another nested SP within SP2-> SP1[/font]When I try to run SP3 I get this error message "An INSERT EXEC statement cannot be nested."Reason: As much as I could debug this issue is coming in SP2 at a statement where SP2 calls and stores the output of SP1 in a temp table.While SP2 runs smoothly on a standalone basis when its output is called in SP3 I get an error.If this particular SP2 statement is commented and values hard-coded i.e. SP1 is never called, SP3 also runs fine.So as the error also says it has something to do with how much deep nested SPs can work?Further, I've tried using TEMP Table, Temporary table variable and defined a proper table to store these nested SPs output thinking just in case TEMP tables have this limitation but nothing worked.Kindly, suggest me how do I get SP3 working with just 2 level deep nested SPs being used.Thanks Ankit MathurThu, 29 Oct 2009 23:28:15 GMTAnkit Mathur-481681http://www.sqlservercentral.com/Forums/Topic811093-145-1.aspxHi, I have a stored procedure where I need to insert data into a table. I need to know how to get the total of records inserted into the table. eg :Insert Table1Select * from Table2I dont want to use the Select count(*) from Table1 because the table has an Ignore Duplicate Key ON and the total of records inserted are around 80millions records. Is any SQL command I can use to get the total of records inserted? any advice?Regards,Thu, 29 Oct 2009 12:19:48 GMTMTY-1082557http://www.sqlservercentral.com/Forums/Topic809463-145-1.aspxWhat SQL Server edition are you using?Tue, 27 Oct 2009 10:47:23 GMTLewis Dowson