Tuesday, August 6, 2013

[Articles] Separation of Duty

[Articles] Separation of Duty

Separation of Duty

Posted: 05 Aug 2013 11:00 PM PDT

We want to ensure that the data we gather accurately reflects the state of some system or event. The analysis should decide how we interpret the data gathered. Neither should influence the other.

SQL Data Generator Generate realistic test data, fast
"In less than the time it took me to get my coffee, I had a database with 2 million rows of data for each of 10 tables." Stephanie Beach, QA Manager. Try SQL Data Generator now.
Posted by at No comments:

[MS SQL Server] Bulk Insert text file into SQL table

[MS SQL Server] Bulk Insert text file into SQL table

Bulk Insert text file into SQL table

Posted: 06 Aug 2013 02:54 AM PDT

Hi,I have a requirement to load sql server table from text file using the below command....and the file is in the local server....its throwing an error...could someone help me on this please....it urgent...BULK INSERT dbatest.dbo.test FROM 'L:\test.log' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = \r')Error :Msg 4866, Level 16, State 1, Line 1The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.Msg 7399, Level 16, State 1, Line 1The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7330, Level 16, State 2, Line 1Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".Thanks in advance...

Repair instance

Posted: 05 Aug 2013 10:21 PM PDT

Hi Experts,What exactly happens when we try to repair the instance using the REPAIR option under maintenance in Installation.Thanks in Advance

how to script out create table script of all tables in a database

Posted: 06 Aug 2013 12:52 AM PDT

I would like to script out all create table script in a database and save it in a table. How to perform this operation?

SQL Server SSIS Packaage Error Code 1073741819.

Posted: 05 Aug 2013 11:33 PM PDT

Dear All,We have faced the following Error Recently in SSIS Package Scheduling.Error Code :The step did not generate any output. The return value was unknown. The process exit code was -1073741819. The step failed.Detailed Error List :Date 06/08/2013 10:02:25Log Windows NT (Application)Source Application ErrorCategory Application Crashing EventsEvent 1000Computer MessageFaulting application name: dtexec.exe, version: 2009.100.1600.1, time stamp: 0x4bb679aaFaulting module name: ntdll.dll, version: 6.2.9200.16420, time stamp: 0x505aaa82Exception code: 0xc0000374Fault offset: 0x000da94fFaulting process id: 0x3a78Faulting application start time: 0x01ce925df1afd4c7Faulting application path: D:\Program Files (x86)\Microsoft SQL Server\100\DTS\binn\dtexec.exeFaulting module path: C:\Windows\SYSTEM32tdll.dllReport Id: 313df978-fe51-11e2-93ff-b4b52f53d6fdFaulting package full name: Faulting package-relative application ID:any one can have a solution pl postRegards,Dhanya

need to get the all jobs information

Posted: 05 Aug 2013 10:12 PM PDT

HIwe are planning to configure the replication. so we are planning to schedule the snapshot, before scheduling we have to find what ever the jobs are running on the instance and time.how can we find the jobs details with scheduled time is there any query for this?

Page file best practices and memory upgrade.

Posted: 05 Aug 2013 06:03 PM PDT

Hi, we are planning to upgrade the ram in our SQL 2008 R2 cluster running on Windows 2008 R2. Both SQL and Windows are Ent edition. currently the two nodes have 32GB of memory each, we already purchased 2 x 32GB memory upgrades for these servers. Now normally i just hard configure the Windows pagefile to the Windows recommend size but in this case when we upgrade the memory in the server to 64GB the C: probably wont have much free space left to set the Page file to 96GB.If i set the page file to say 72GB the system meory plus 8GB will this be enough for successful crash dumps?Recommendations?
Posted by at No comments:

[SQL 2012] Small transaction logs and/or transations logs with no transactions and need to restore

[SQL 2012] Small transaction logs and/or transations logs with no transactions and need to restore

Small transaction logs and/or transations logs with no transactions and need to restore

Posted: 05 Aug 2013 07:35 AM PDT

We are running SQL Server 2012 SP1 64-Bit EE on Windows Server 2008 R2 SP1. We are taking a full backup on Sunday night, a diff backup Monday through Saturday night, and trans log backups Monday through Friday every 10 minutes from 7 A.M. to 6 P.M. We only have a test database set up for right now. I noticed that the TLog Backup for 7 A.M. is 15,524 KB and the TLog Backups from 7:10 to 6 P.M. are only 99 KB each. I understand the TLog backups from 7:10 A.M. to 6 P.M. are small and the same size because of little or no activity on the database. My question is, if there is no (zero) updates on the database, would there be any transactions in the TLog Backup? If there are no transactions in the TLog Backup is it still required for it to be restored in sequence during a recovery (after the full backup restore and diff backup restore)?Thanks in advance, Kevin

Query on a shared and exclusive lock

Posted: 05 Aug 2013 10:43 PM PDT

I have been doing good some research on locks and isolation levels within sql server. From my understanding an exclusive lock will block a request to a shared lock. So if an update is being carried out on a table and another session attempts to carry out a select 7query on the same table, then it will block the select until the update has been carried out (this is Assuming we use read committed isolation). Is this correct?

Table data in tabular form

Posted: 05 Aug 2013 10:20 PM PDT

CREATE TABLE [dbo].[LeaveEntry]( [LeaveId] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, [LeaveTypeName] [int] NOT NULL, [StartDate] [datetime] NULL, [EndDate] [datetime] NULL, )INSERT INTO LeaveEntry VALUES (1, 'A','OUT','2013-08-29 00:00:00.000','2013-09-29 00:00:00.000'), (2, 'B','LON','2013-08-29 00:00:00.000','2013-09-29 00:00:00.000'), (3, 'C','OUT','2013-08-29 00:00:00.000','2013-09-29 00:00:00.000'); I want to do a report in ssis where I have to send an email i a tabular form for each weeksomething likeWeek 1<table border="1px"><tr><td></td><td>A</td> <td>B</td> <td>C</td> </tr><td>Monday</td><td>OUT</td><td>LON</td><td></td></tr><tr><td>Tuesday</td><td></td><td></td><td>LON</td></tr><tr><td>Wednesday</td><td>OUt</td><td></td><td>LON</td></tr><tr><td>Thurday</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td></td><td>OUT</td><td></td></tr></table> Week 2<table border="1px"><tr><td></td><td>A</td> <td>B</td> <td>C</td> </tr><td>Monday</td><td>OUT</td><td>LON</td><td></td></tr><tr><td>Tuesday</td><td></td><td></td><td>LON</td></tr><tr><td>Wednesday</td><td>OUt</td><td></td><td>LON</td></tr><tr><td>Thurday</td><td></td><td></td><td></td></tr><tr><td>Friday</td><td></td><td>OUT</td><td></td></tr></table>

SSIS Question

Posted: 05 Aug 2013 10:47 PM PDT

In training kit(70-463) book .I see following question.What's you guys think should be the answer?As per me its A and C...As per book writer option B is also correct..2. On which SSIS objects can you set checkpoints to be active? (Choose all that apply.)a. Data flow taskB. Control flow tasksC. Sequence containerD. Sort transformation

Semantic search and filetable excel files

Posted: 06 Aug 2013 12:38 AM PDT

Does anyone know if SEMANTICKEYPHRASETABLE and SEMANTICSIMILARITYTABLE work with Excel and CSV files.I'm working on an ap and need to suggest 'tags' for files that users upload, and this is working fine for doc, rtf, pdf and ppt files etc, but the functions return nothing for csv and xls files.I've checked the documents have actually been indexed (by using a freetext query) and it seems like they are..I seem to remember something about the language used for word-breaking being taken from the document metadata (which isn't there on a csv file!) so might this have something to do with it?Any ideas appreciated.Jeff

SQL Server Express with Tools\Advanced for SQL Agent

Posted: 05 Aug 2013 10:25 PM PDT

Hi, I'd heard that if i installed this new SSMS tool on an instance that had Express already installed on it I'd get SSMS which included the Agent etc. So I installed fine and there is an Agent service which started ok. However there is no SQL Agent under SSMS? Can anyone confirm one way or another if there is any Express option that comes with the SQL Agent. If not why does it install with the SQL Agent service?Thank-you

SQL Detection by Edition/Version

Posted: 05 Aug 2013 06:11 AM PDT

This is rather general, not really limited to 2012. My company has requested I find a way to automatically detect which edition/version is installed. Are there file paths, trace files or registry entries that once found on a host id, would tell me the edition/version installed. Looking for a listing of signatures or tags that might assist me in this detection task. I have the ability to customize a signature with all the parameters/variables required, I am just not sure what the parameters might be. Thank you.:

Manage Log file growth

Posted: 05 Aug 2013 04:59 AM PDT

All,We made the switch to Sql Server '12 over the weekend and our main app database log file is slowly growing, past the point where I feel comfortable. We have run a full backup, a differential last night and transaction logs every 15 minutes. But the log file keeps growing and is not shrinking. We need to have the database in full recovery as it is the primary database for a transactional replication publication and the primary database in a availability group. Replication is only 6 second delay and the availability group is up to the second. Any suggestions?Thanks in advance,DK

Transaction Log corruption and SQLVDI

Posted: 05 Aug 2013 07:30 AM PDT

Hello, I have an interesting issue, the cause of which remains a little elusive. We have recently had multiple failures in our transaction log backups, each time seems to result in a corrupt tlog in a similar sequence of events. We are on SQL Server 2012 Enterprise (recently upgraded), running on top of Windows Server 2008R2 Enterprise and are using Red Gate as our third-party backup solution. Twice now, we have suffered a break in our tlog chain, only to find that the tlog which was the breaking point was 'successfully' created, but with issues that are only logged in the Red Gate error log. The error apparently does not get passed up high enough to be trapped in our SQL error checking embedded in the script which does the backup, therefore the backup job does not fail. The error we are seeing is: [quote]----------------------------- ERRORS AND WARNINGS -----------------------------8/4/2013 12:00:14 PM: 8/4/2013 12:00:14 PM: Verifying files:8/4/2013 12:00:14 PM: K:\Backups\[ServerName]_XXXXX\Tranlogs\FileNameX\LOG_NameX_20130804120000.sqb8/4/2013 12:00:14 PM: 8/4/2013 12:00:14 PM: Thread 0 error: Process terminated unexpectedly. Error code: -2139684860 (An abort request is preventing anything except termination actions.)8/4/2013 12:00:14 PM: 8/4/2013 12:00:14 PM: SQL error 3013: SQL error 3013: VERIFY DATABASE is terminating abnormally.8/4/2013 12:00:14 PM: SQL error 3254: SQL error 3254: The volume on device 'SQLBACKUP_0C2E9FDE-8A43-41AC-B7C1-2CBE70956DA2' is empty.8/4/2013 12:00:14 PM: Validation of all backup files failed.----------------------- PROCESSES COMPLETED SUCCESSFULLY --------------------8/4/2013 12:00:00 PM: Backing up XXXXX (transaction log) on InstNameX instance to: 8/4/2013 12:00:00 PM: K:\Backups\InstNameX\Tranlogs\FileNameX\LOG_NameX_20130804120000.sqb8/4/2013 12:00:00 PM: BACKUP LOG [NameX] TO VIRTUAL_DEVICE = 'SQLBACKUP_8798FF6C-E2C5-4FCB-8728-EB49BA1335B0' WITH BUFFERCOUNT = 6, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576, NAME = N'Database (NameX), 8/4/2013 12:00:00 PM', DESCRIPTION = N'Backup on 8/4/2013 12:00:00 PM Server: NameX\NameX Database: NameX', FORMAT8/4/2013 12:00:13 PM: Backup data size : 15.625 MB8/4/2013 12:00:13 PM: Compressed data size: 2.790 MB8/4/2013 12:00:13 PM: Compression rate : 82.15%8/4/2013 12:00:13 PM: Processed 1203 pages for database 'NameX', file 'NameXlog' on file 1.8/4/2013 12:00:13 PM: BACKUP LOG successfully processed 1203 pages in 0.609 seconds (15.420 MB/sec).[/quote]The only pattern we can find is a concurrent SQLVDI error which is appearing in the servers Event Logs, which specifically states: [quote]SQLVDI: Loc=SVDS::Open. Desc=Bad State. ErrorCode=(-1). Process=564. Thread=11324. Server. Instance=NameX. VD=Global\SQLBACKUP_A9FCA3B4-CB68-4C45-985F-DB89C6E60D6A_SQLVDIMemoryName_0. [/quote]When the tlog attempts to restore, SQL Server generates the error: [quote]The volume on device 'SQLBACKUP_59E6E383-98CA-4179-8721-FB8379EB6817' is empty.[/quote] ...which appears to indicate corruption in the tlog because it is NOT empty and is the correct and expected size. We have ruled out corruption during the network copy of the tlog to the log-shipped server, since the error is happening closer to home as the backup is actually occurring. Other than the SQLVDI error above, we have not been able to locate any other errors at the SAN, Server, or OS level. The box this is running on is a monster with 1 TB of RAM that is not remotely over-stressed, so I don't believe any sort of memory pressure is the culprit, outside of a buried config that we haven't found yet. The occurrences of this issue have also not followed a day/time pattern and do not coincide with any obvious maintenance or business processes that might be a source of interference. Has anyone experienced similar issues, particularly related to SQL Server 2012 and SQLVDI errors? Would love to hear any experiences at all that might help shed some light on this odd issue. Thank you!
Posted by at No comments:

[T-SQL] Subquery returned more than 1 value error message

[T-SQL] Subquery returned more than 1 value error message

Subquery returned more than 1 value error message

Posted: 05 Aug 2013 10:33 PM PDT

HiI'm receiving the following message:Msg 512, Level 16, State 1, Line 4Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expressionSo I'm guessing that in one of the sub-queries, I'm returning more than 1 row of data. Problem is that after dissecting this for hours, I can't find where. I can't see where I can replacing an '=' with 'IN', 'MAX', 'Top' etc. might be appropriate either.Strangely when I run the code against another schema in the same database it works fine.Help please!The code is:[code="sql"]declare @Locality varchar(max)set @Locality = 'Locality Yate'SELECT T.Locality,GS2.CodeDescription AS 'Specialty',Status,Count(T.ClientID) AS 'Number' FROM (SELECT REF.ClientID,REF.ReferralNumber,SpecialtyReferredTo,ServiceTeam,ISNULL((SELECT GS.CodeDescription FROM dbo.vwSGReferrals As REF2LEFT OUTER JOIN SchemaSG.GenServiceTeam AS GST ON GST.CodeDescription=REF2.ServiceTeamLEFT OUTER JOIN SchemaSG.AmsSpecialtyTeams AS AST ON GST.Code=AST.TeamLEFT OUTER JOIN SchemaSG.GenSpecialty AS GS ON AST.Specialty=GS.CodeWHERE REF.ClientID=Ref2.ClientID AND REF.ReferralNumber=REF2.ReferralNumberAND DischargeDateTime IS NULLAND AST.Specialty Like '%LOC%'),'No Locality') AS 'Locality' ,CASE WHEN (SELECT Max(Apps2.ContactID)FROM ABI_RiO.dbo.vwSGReferrals Refs2LEFT JOIN ABI_RiO.dbo.vwSGAppointmentsPD Apps2 ON Refs2.ClientID = Apps2. ClientID AND Refs2.ReferralNumber = Apps2.ReferralNumberLEFT OUTER JOIN ABI_RiO.SchemaSG.AmsOutcome AS AOUT ON AOUT.Code=APPs2.OutcomeWHERE Refs2.ClientID = Ref.ClientIDAND Refs2.ReferralNumber = Ref.ReferralNumberAND NationalCode=5) IS NOT NULL Then 'Active'ELSE 'Waiting'END As StatusFROM dbo.vwSGReferrals As REFWHERE DischargeDateTime IS NULL)TLEFT OUTER JOIN SchemaSG.GenSpecialty AS GS2 ON T.SpecialtyReferredTo=GS2.CodeWHERE (ISNULL(Locality,'NULL') IN (SELECT * FROM fnSplitList(@Locality, ','))) GROUP BY T.Locality,T.SpecialtyReferredTo,GS2.CodeDescription,Status;[/code]CheersTim

select DISTINCT cost too high?

Posted: 05 Aug 2013 10:50 PM PDT

Hi,Pl. suggested me, how to resolve this issuse? In select statment sub-tree DISTINCT cost is 79%. as per atttached actual exec.plan. Is it possible wirtting query alternative ways?[code="sql"]UPDATE CSV_Details_MainFile SET Mark_Rev_No = (CASE WHEN (SELECT COUNT(mark_rev_no) as mark_rev_no FROM CSV_Details_MainFile H1 WHERE H1.GA_Drg_NO = CSV_Details_MainFile.GA_Drg_NO AND H1.Mark_No = CSV_Details_MainFile.Mark_No) != 0 THEN (SELECT ISNULL(mark_rev_no,0) AS mark_rev_no FROM CSV_Details_MainFile H1 WHERE H1.GA_Drg_NO = CSV_Details_MainFile.GA_Drg_NO and H1.Rev_NO = CSV_Details_MainFile.Rev_NO AND H1.Mark_No = CSV_Details_MainFile.Mark_No and DeleteFlag='1' GROUP BY H1.GA_Drg_NO,H1.Mark_No ,H1.mark_rev_no) ELSE '1' END) WHERE CSV_Details_MainFile.GA_Drg_NO='C63-GPE105-499-005' AND Rev_NO = '1' AND CSV_Details_MainFile.Mark_No in (SELECT DISTINCT Mark_No FROM CSV_Details_MainFile WHERE GA_Drg_NO = 'C63-GPE105-499-005' AND Rev_NO ='1' and DeleteFlag is null) [/code]Thanksananda

Want to re-write a query to not use a cursor...

Posted: 06 Aug 2013 12:28 AM PDT

I came up with a query to give me an idea when the various SQL logins on my servers might expire / require a password change or if the account has been locked out (Devs trying to test stuff as the account, and using an incorrect password...)Now, I know there's going to be some howling, but I wound up using a cursor to accomplish this. I know for something like this, that's a run it once in a while, a cursor isn't really such a bad thing, but I'd like to see what would be done differently to get it to work without the cursor.So, the basics:The cursor grabs non-disabled, SQL type logins from sys.server_principals, then uses LOGINPROPERTY to collect the info I want.The code:[code="sql"]declare @user varchar(50)create table #userinfo ( Username varchar(50), badpasswordcount int, badpasswordtime datetime, dayuntilexpire datetime, lockouttime datetime, islocked int, passwordlastsettime datetime, likelynextchange datetime )declare usr_cursor cursor for select name from sys.server_principals where type = 'S' and is_disabled = 0 order by nameopen usr_cursorfetch next from usr_cursor into @userwhile @@FETCH_STATUS = 0begin insert into #userinfo select @user as 'User name', convert(int, LOGINPROPERTY(@user, 'BadPasswordCount')), convert(datetime, LOGINPROPERTY(@user, 'BadPasswordTime')), convert(datetime, LOGINPROPERTY(@user, 'DaysUntilExpiration')), convert(datetime, LOGINPROPERTY(@user, 'LockoutTime')), convert(int, LOGINPROPERTY('loginname', 'IsLocked')), convert(datetime, LOGINPROPERTY(@user, 'PasswordLastSetTime')), DATEADD(DD, 90, (convert(datetime, LOGINPROPERTY(@user, 'PasswordLastSetTime')))) fetch next from usr_cursor into @user endclose usr_cursordeallocate usr_cursorselect * from #userinfodrop table #userinfo;[/code]This is NOT a homework-type thing, this is me looking to expand my skills / knowledge, and looking for some help.Thanks all,Jason

SQL Query help

Posted: 05 Aug 2013 10:52 PM PDT

[code="sql"]CREATE TABLE [dbo].[dp]( [nr] [char](12) NOT NULL, [type] [char](12) NOT NULL, [DT] [int] NOT NULL,)INSERT INTO [dbo].[dp] VALUES ('1','OF',49),('2','OF',49),('3','OF',49),('4','CF',49),('5','CF',49),('6','CF',49),('7','CF',49),('1','AV',11),('2','HK',11),('3','HK',11),('1','AV',67),('2','HK',67),('1','AV',18),('2','SA',18),('1','AV',80),('2','AV',80)CREATE TABLE [dbo].[ln]( [nr] [char](12) NOT NULL, [type] [char](12) NOT NULL, [DT] [int] NOT NULL,)INSERT INTO [dbo].[ln] VALUES ('1','IL',70),('2','IL',70),('1','IL',69),('2','IL',69),('1','MI',89),('2','MI',89),('3','MI',89),('4','MI',89)[/code][b][i]I want a query that will only retrieve all DT that have only one type of nr[/i][/b]i.e, the outcome should be like below and the results should be union as the two table have two different functions with some minor difference in the structure[code="sql"]from the dp table the result expected should be'1','AV',80'2','AV',80from the ln table the result expected should be'1','IL',70'2','IL',70'1','IL',69'2','IL',69'1','MI',89'2','MI',89'3','MI',89'4','MI',89[/code]Thank youCarnalito

Query Challenge

Posted: 05 Aug 2013 08:21 PM PDT

The time interval on the table increments to approximate 20~25 sec , need to group based on the time ,the iterations are 2 0r 3 in the table in each 20 sec Table data DateS Value2013-08-06 08:01:01.430 82013-08-06 08:01:20.430 22013-08-06 08:01:40.240 22013-08-06 08:05:41.810 12013-08-06 08:06:20.217 12013-08-06 08:10:21.750 12013-08-06 08:10:40.657 52013-08-06 08:11:02.217 12013-08-06 08:15:02.177 12013-08-06 08:15:20.413 22013-08-06 08:15:40.180 12013-08-06 08:19:42.117 12013-08-06 08:20:00.307 22013-08-06 08:24:21.493 52013-08-06 08:24:40.637 22013-08-06 08:25:00.353 22013-08-06 08:29:01.930 22013-08-06 08:29:20.277 12013-08-06 08:29:40.400 12013-08-06 08:33:42.830 12013-08-06 08:34:00.320 12013-08-06 08:34:20.477 1Expected DateS Value GroupNumber2013-08-06 08:01:01.430 8 12013-08-06 08:01:20.430 2 12013-08-06 08:01:40.240 2 12013-08-06 08:05:41.810 1 22013-08-06 08:06:20.217 1 22013-08-06 08:10:21.750 1 32013-08-06 08:10:40.657 5 32013-08-06 08:11:02.217 1 32013-08-06 08:15:02.177 1 42013-08-06 08:15:20.413 2 42013-08-06 08:15:40.180 1 42013-08-06 08:19:42.117 1 52013-08-06 08:20:00.307 2 52013-08-06 08:24:21.493 5 62013-08-06 08:24:40.637 2 62013-08-06 08:25:00.353 2 62013-08-06 08:29:01.930 2 72013-08-06 08:29:20.277 1 72013-08-06 08:29:40.400 1 72013-08-06 08:33:42.830 1 82013-08-06 08:34:00.320 1 82013-08-06 08:34:20.477 1 8 Final Expected from Group--Need to take average of Value and Max of dates on each GROUP DateS Value GroupNumber2013-08-06 08:01:01.430 8 12013-08-06 08:01:20.430 2 12013-08-06 08:01:40.240 2 12013-08-06 08:05:41.810 1 22013-08-06 08:06:20.217 1 2Final result MaxDateS AvgValue GroupNumber2013-08-06 08:01:40.240 3 1 2013-08-06 08:06:20.217 1 2[code="sql"]CREATE TABLE #LOGTABLE ( DateS DATETIME ,Value INT )INSERT INTO #LOGTABLE ( DateS ,Value ) SELECT '2013-08-06 08:01:01.430',8UNION ALL SELECT '2013-08-06 08:01:20.430',2UNION ALL SELECT '2013-08-06 08:01:40.240',2UNION ALL SELECT '2013-08-06 08:05:41.810',1UNION ALL SELECT '2013-08-06 08:06:00.763',2UNION ALL SELECT '2013-08-06 08:06:20.217',1UNION ALL SELECT '2013-08-06 08:10:21.750',1UNION ALL SELECT '2013-08-06 08:10:40.657',5UNION ALL SELECT '2013-08-06 08:11:02.217',1UNION ALL SELECT '2013-08-06 08:15:02.177',1UNION ALL SELECT '2013-08-06 08:15:20.413',2UNION ALL SELECT '2013-08-06 08:15:40.180',1UNION ALL SELECT '2013-08-06 08:19:42.117',1UNION ALL SELECT '2013-08-06 08:20:00.307',2UNION ALL SELECT '2013-08-06 08:20:21.133',1UNION ALL SELECT '2013-08-06 08:24:21.493',5UNION ALL SELECT '2013-08-06 08:24:40.637',2UNION ALL SELECT '2013-08-06 08:25:00.353',2UNION ALL SELECT '2013-08-06 08:29:01.930',2UNION ALL SELECT '2013-08-06 08:29:20.277',1UNION ALL SELECT '2013-08-06 08:29:40.400',1UNION ALL SELECT '2013-08-06 08:33:42.830',1UNION ALL SELECT '2013-08-06 08:34:00.320',1UNION ALL SELECT '2013-08-06 08:34:20.477',1[/code]

RECONFIGURE inside trigger

Posted: 05 Aug 2013 02:44 AM PDT

Hello SQL fansI am working on one project and got to the point where I have a problem and need your help.I have a after insert trigger and in it I want to execute Ole Automation Procedures.The problem is, that sql doesn't allow RECONFIGURE statement within a trigger.All I want is to execute this lines of code before procedure executen EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ole Automation Procedures', 1 RECONFIGUREand disabling it after my stored procedure.Here is my code[code="sql"]create TRIGGER tr_ORS_CostDrvAfterInsert ON _tmp1AFTER INSERTAS BEGIN SET NOCOUNT ON; declare @URI varchar(2000) , @methodName varchar(50), @requestBody varchar(8000) = '', @SoapAction varchar(255), @UserName nvarchar(100), @Password nvarchar(100), @responseText varchar(8000); set @URI = 'http://MyURI.php'; set @methodName = 'GET'; set @requestBody = ''; set @SoapAction = 'Method'; set @UserName = ''; set @Password = ''; EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ole Automation Procedures', 1 RECONFIGURE exec [dbo].[sp_ORS_MS_Request] @URI, @methodName, @requestBody, @SoapAction, @UserName, @Password, @responseText output; EXEC sp_configure 'Ole Automation Procedures', 0 RECONFIGURE EXEC sp_configure 'show advanced options', 0 RECONFIGURE END[/code]

How to select range of consecutive events

Posted: 05 Aug 2013 06:15 AM PDT

This is my first cry for help, so be gentle with me. :)I've been searching the forum for a clue but I'm still having trouble figuring out how to produce the query results I need. and boy does it hurt my pride to say that... :)The data is fairly simple, a Station ID and a Start & End time. (along with a bunch of other data that needs to be added or dissected.)I have a list of events for every day and I need to identify blocks of two or more consecutive events and return the first start time and the last end time. This would also be the MIN(start) and MAX(End) of those group of consecutive events.I can identify the first, last and "middle" records by doing a join back to the table linking the start time of one record to the end time of the other, but I can't figure out how to group one "group" of consecutive events, as there could be multiple groups in a day.There are some events which are "singular", meaning that they have no adjoining records, and the group could be 2 or 20 consecutive events, and could span midnight if a show runs over.Here is what the sample data would look like and the output I'm trying to accomplish below that.I can turn all of this into a make table & insert statements if you want data to play with.I appreciate the help.Thanks,TadStation Start EndWKBW 7/5/13 11:00 AM 7/5/13 11:30 AMWKBW 7/5/13 12:00 PM 7/5/13 12:30 PMWKBW 7/5/13 12:30 PM 7/5/13 1:00 PMWKBW 7/5/13 1:00 PM 7/5/13 1:30 PMWKBW 7/5/13 1:30 PM 7/5/13 2:00 PMWKBW 7/9/13 11:30 AM 7/9/13 12:00 PMWKBW 7/9/13 12:00 PM 7/9/13 12:30 PMWKBW 7/12/13 12:30 PM 7/12/13 1:00 PMWKBW 7/12/13 1:00 PM 7/12/13 1:30 PMWKBW 7/12/13 1:30 PM 7/12/13 2:00 PMWKBW 7/17/13 12:00 PM 7/17/13 1:00 PMWKBW 7/18/13 1:30 PM 7/18/13 2:00 PMWKBW 7/19/13 12:30 PM 7/19/13 1:00 PMWKBW 7/19/13 1:00 PM 7/19/13 1:30 PMWKBW 7/19/13 1:30 PM 7/19/13 2:00 PMWKBW 7/19/13 3:00 PM 7/19/13 4:00 PM KFUN 7/22/13 4:30 AM 7/22/13 7:00 AMKFUN 7/22/13 7:00 AM 7/22/13 9:00 AMKFUN 7/22/13 12:00 PM 7/22/13 12:30 PMKFUN 7/22/13 5:00 PM 7/22/13 6:30 PMKFUN 7/23/13 4:30 AM 7/23/13 7:00 AMKFUN 7/23/13 7:00 AM 7/23/13 9:00 AMKFUN 7/23/13 9:30 AM 7/23/13 10:00 AMKFUN 7/23/13 10:00 AM 7/23/13 10:47 AMKFUN 7/23/13 10:53 AM 7/23/13 11:18 AMKFUN 7/23/13 11:18 AM 7/23/13 11:54 AMKFUN 7/23/13 12:00 PM 7/23/13 12:30 PMKFUN 7/23/13 5:00 PM 7/23/13 6:30 PMKFUN 7/23/13 9:00 PM 7/23/13 10:30 PMKFUN 7/24/13 4:30 AM 7/24/13 7:00 AMKFUN 7/24/13 7:00 AM 7/24/13 9:00 AMKFUN 7/24/13 9:00 PM 7/24/13 10:30 PMKFUN 7/25/13 4:30 AM 7/25/13 7:00 AMKFUN 7/25/13 7:00 AM 7/25/13 9:00 AM OUTPUT KFUN 7/22/13 4:30 AM 7/22/13 9:00 AMKFUN 7/23/13 4:30 AM 7/23/13 9:00 AMKFUN 7/23/13 9:30 AM 7/23/13 10:47 AMKFUN 7/23/13 10:53 AM 7/23/13 11:54 AMKFUN 7/24/13 4:30 AM 7/24/13 9:00 AMKFUN 7/25/13 4:30 AM 7/25/13 9:00 AMWKBW 7/5/13 12:00 PM 7/5/13 2:00 PMWKBW 7/9/13 11:30 AM 7/9/13 12:30 PMWKBW 7/12/13 12:30 PM 7/12/13 2:00 PMWKBW 7/19/13 12:30 PM 7/19/13 2:00 PM

How to improve the performance of this query?

Posted: 05 Aug 2013 06:20 PM PDT

The current result when take from INFORMATION_SCHEMA.COLUMNSTABLE COLUMNTable1 Column1Table1 Column2Table1 Column3Table2 Column1Table2 Column2Table2 Column3Expected resultTABLE COLUMNSTable1 Column1,Column2,Column3Table2 Column1,Column2,Column3I have tried 2 methods Method 1SELECT c1.Table_name, STUFF((Select ',' + c2.COLUMN_NAME AS [text()] from INFORMATION_SCHEMA.COLUMNS c2 where c1.TABLE_NAME=c2.TABLE_NAME and c1.column_name=c2.column_name --Order by c2.TABLE_NAME for xml path ( '' )), 1, 1,'' ) as "Column_names" from INFORMATION_SCHEMA.COLUMNS c1group by Table_nameWhich throws the error Msg 8120, Level 16, State 1, Line 4Column 'INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.But adding column_name to the group by clause does not give the desired resultMethod 2;WITH CTE (SRNO,Table_name,column_name)AS(SELECT ROW_NUMBER() OVER (PARTITION BY Table_name ORDER BY Table_name,column_name) AS SRNO,Table_name,CAST(column_name AS VARCHAR(MAX)) FROM INFORMATION_SCHEMA.COLUMNS),CTE1(SRNO,Table_name,column_name)AS(SELECT * FROM CTE WHERE SRNO=1UNION ALLSELECT CTE.SRNO AS SRNO, CTE.Table_name,CAST (CTE1.column_name + ',' + CTE.column_name AS VARCHAR(MAX)) FROM CTE INNER JOIN CTE1ON CTE.Table_name=CTE1.Table_name AND CTE.SRNO=CTE1.SRNO+1)SELECT Table_name, MAX(column_name) AS CSV FROM CTE1 GROUP BY CTE1.Table_nameoption (maxrecursion 100)But this takes a very long time.Kindly help me get the desired result in the most optimum way

SELECT ... INTO NewTable without nulls

Posted: 05 Aug 2013 05:05 AM PDT

I am creating a table by using theSELECT column1, column2INTO NewTableFROM OldTablemethod. Is there a way of creating a table in this manner and saying if the columns should be null or not null? Currently I am just using an alter statement to change a column from null to NOT NULL, but was wondering if there is a way to eliminate this step. Relatedly, can anyone think of a way for me to stop getting obsessed with ways of cutting out 1-2 lines of code and wasting all this time figuring out things I've already found solutions for? :crazy:Thank you,Amy

Query Help - Sum by Month

Posted: 05 Aug 2013 01:48 AM PDT

I have a table of values by date. I have a requirement to group and sum them by month (to use to plot on a chart). I'm not really sure how to go about grouping by a range. That is if I have:01/03/2012 $30001/12/2012 $25002/05/2012 $20002/07/2012 $30002/15/2012 $400I need it grouped as two rows01/2012 $55002/2012 $900Thanks for any help.Sean
Posted by at No comments:

[SQL Server 2008 issues] What happen to the redo queue?

[SQL Server 2008 issues] What happen to the redo queue?

What happen to the redo queue?

Posted: 05 Aug 2013 06:54 PM PDT

In Mirroring ,If the principal server suddenly fails or crashes when running synchronously,what happen to the redo queue ?Are these loges hardened to the mirror db?These loges ,on principal do not be harden ,and if we have automatic failover when the session reconnects,it acts as mirror and they run synchronously.Do I understand correctly?

Database defragmentation and autogrowth settings

Posted: 19 Jul 2013 01:21 AM PDT

We do have some maintenance plan for our sql server 2008 r2 express.Every month we do defragment of the database if any table has page count more tahn 50 for any table and average fragmentation more than 20.If the database log size>2 MB, then the recovery mode is made as simple, and it is shrinked, and the recovery mode is set back to FULL.If the Page_count>50 and avg_fragmentation_in_percent > 30 then the index is REBUILD.And if Page_count>50 and avg_fragmentation_in_percent > 5 and <30 then the index is REORGANIZE.This is what we are doing till now.But we found that autogrowth events are resource incentive and it should not happen repeatedly. Now for all database autogrowth is set to MB for mdf file and 10% for ldf file which is default value while creating new database.We are planning to increase the autogrowth values for the database depending on how much database is getting bigger every day.But i want to know how much autogrowth events is ideal for the database.Should i set autogroth so that it happens only once a day,week or month etc.So please help me to set the autogrowth value for my database.Also there is another problem.If i do monthly defragmentation of database then it will be shrinked. So after this for all database for which i did shrink autogrowth occurs once when new data is written to it.So there will be so many autogrowth events. So whether it will be a problem?Please tell me a solution.

Get year in which most touchdowns scored?

Posted: 05 Aug 2013 12:44 PM PDT

NEVER MIND

Intermittent pre-login handshake error

Posted: 05 Aug 2013 03:24 PM PDT

Environment:SQL 2008 R2 ClusterRunning fine for over 6 monthsEvery once in a while I get some time-outs from our web apps with a SQLAsync Exception:Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=1; handshake=15003; Anyone get this before? Again the server is listening on port 1433 and the connection strings are using TCP\IP. This is a intermittent error. I remember something like this that had to do with multiple instances and Named Pipes and TCP or the client is trying to connect via TCP\IP v6 first then 4 but I have checked all that and it seems fine.thanks in advance

Log Shipping Transfer Logins

Posted: 05 Aug 2013 06:19 AM PDT

Hello,I have setup a log shipping for a couple of my databases (SQL 2008). I would like keep any additional logins created on the primary database in sync on the log shipping server. I would like to use the SSIS transfer logins task to accomplish this goal and run via a SQL Agent job. However, when I run the job I get the following error.[Transfer Logins Task] Error: Execution failed with the following error: "Database '[i]DBNAME[/i]' cannot be opened. It is in the middle of a restore.".I have the following settings on the task:AllLoginsFromSelectedDatabasesIf objects exist = SkipCopySids = TrueI understand the logins cant be added while the database is restoring. Is there anyway around this issue?Thx

Run querys on different database with Progress

Posted: 05 Aug 2013 09:43 AM PDT

Hi guys,I am using ODBC to connect Progress from Sql server 2008, But I need to connect with two differents databases on the same query, It is possible?I am using Progress OpenEdge 11.2Any help would be greatly appreciated.Thanks,Sandra

ADSI linked server and AD Description field

Posted: 05 Aug 2013 09:30 AM PDT

Hi all.We have a requirement to query our Active Directory for description fields from SQL server.We of course get the same error as a multitude of other people who have posted across the internet."Could not convert the data value due to reasons other than sign mismatch or overflow."I have read this was "fixed" from 2003 onwards.I have read there are hot fixes for Windows 2003 and 2000I cannot find any KB articles for later versions like 2008.We can happily query single valued attributes but not description.Can anyone answer definitively if this can be achieved or not?Query:[code="sql"]SELECT [Description]FROM openquery (ADSI,'SELECT description FROM ''LDAP://DC=OurDomain,DC=x,DC=y,DC=z'' WHERE objectCategory = ''Computer''' )[/code]I have domain admin rights across all servers to am able to make changes to settings if required or can escalate a change to associated admin.I notice there is a service pack 2 for 2008 R2 but can see no mention of any related fixes there.[url=http://support.microsoft.com/kb/2630458][/url]SQL Server Specks:@@versionMicrosoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

Bizarre 'divide by zero' - no division happening!

Posted: 05 Aug 2013 04:52 AM PDT

I have a query that's run as part of a data import process that has suddenly broken on one server while working perfectly on another against the exact same import data. Both are running SQLServer 2008 R2.The query starts something like this: select i.ImportID, i.some_ID, @ReportDate, b.someotherID, b.yetanotherID, b.yep_an_id, b.lastID_i_promise, i.othervalue, b.I_Lied_ID, Asset, 'some static value', i.Currency, Total * fx.rate, [01D] * ISNULL(fx.rate,0), [01W] * ISNULL(fx.rate,0), [02W] * ISNULL(fx.rate,0), [01M] * ISNULL(fx.rate,0), [02M] * ISNULL(fx.rate,0), [03M] * ISNULL(fx.rate,0), [04M] * ISNULL(fx.rate,0), [05M] * ISNULL(fx.rate,0), [06M] * ISNULL(fx.rate,0), [09M] * ISNULL(fx.rate,0), [01Y] * ISNULL(fx.rate,0), [18M] * ISNULL(fx.rate,0), [02Y] * ISNULL(fx.rate,0), [03Y] * ISNULL(fx.rate,0), [04Y] * ISNULL(fx.rate,0), [05Y] * ISNULL(fx.rate,0), [06Y] * ISNULL(fx.rate,0), [07Y] * ISNULL(fx.rate,0), [08Y] * ISNULL(fx.rate,0), [09Y] * ISNULL(fx.rate,0), [10Y] * ISNULL(fx.rate,0), [11Y] * ISNULL(fx.rate,0), [12Y] * ISNULL(fx.rate,0), [15Y] * ISNULL(fx.rate,0), [20Y] * ISNULL(fx.rate,0), [25Y] * ISNULL(fx.rate,0), [30Y] * ISNULL(fx.rate,0), [40Y] * ISNULL(fx.rate,0), [50Y] * ISNULL(fx.rate,0) from... followed by a series of somewhat complex sub-queries which a) all work on one of the servers and b) all work by themselves on the "bad" server. The basic idea is to apply some criteria to move some values into time "buckets" and multiply by an exchange rate.Starting today, running this has been producing a divide by zero error. I thought it odd enough to get divide by 0 on a query that doesn't divide anything, but that's just the beginning. After much tinkering to try to find if one specific calculation was causing the problem, I discovered the following:Of the 42 columns returned by the query, commenting out any arbitrary combination of 14 of them will make the query work. Doesn't matter which ones I take out, as long as there are a maximum of 28 columns being returned. When I un-comment that 29th column, no matter which one - DIVIDE BY ZERO.Anyone have any clue as to what might actually be happening here?

SQL Query

Posted: 05 Aug 2013 07:34 AM PDT

Hi, In need of help with an SQL Query. What i need is to be able to extract specific characters from data. Eg name 1.2-KPIA1-App-00001 this is a name i require, but i also require the '1.2' and the 'KPIA1' to be displayed in new columns in the resultsi.e. 1.2-KPIA1-App-00001 1.2 KPIA11.2 (3 characters) will not always be the same, there will be some instances of 5 characters, e.g. 2.1.1KPIA1 (5 characters) will not always be the same, there may be some instances of 6 characters, eg KPIC10.so, examples may include;1.2-KPIA1-App-000012.1.1-KPIA2-APP-000082.4-KPIC10-App-00010

All jobs fail when manually run, scheduled OK

Posted: 05 Aug 2013 05:37 AM PDT

All jobs fail immediately when run manually with the error "Supply either @job_id or @job_name to identify the job. (Microsoft SQL Server, Error:14294). Nothing gets written to the job history. Same error if the step is running an SSIS or T-SQL. All jobs run successfully when scheduled.

Update w/ SELECT ???

Posted: 05 Aug 2013 05:39 AM PDT

Hi Trying to update a bunch of records , figured I'd use the following by using an excel SS with concatenate..I have a synax error somewhere but can't find it ??[code="sql"]UPDATE dbo.CLIENT_IDENTIFIER, (SELECT * FROM dbo.Client INNER JOIN dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION ON dbo.Client.OID = dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID INNER JOIN dbo.CLIENT_IDENTIFIER ON dbo.CLIENT_TO_CLIENT_IDENTIFIER_COLLECTION.OID_LINK = dbo.CLIENT_IDENTIFIER.OID WHERE(dbo.Client.ID ='123')SET dbo.CLIENT_IDENTIFIER.client_identifier = 'xx071023' where dbo.CLIENT_IDENTIFIER.client_identifier ='xx071540' ; [/code]ThanksJoe

SSIS - Exec SP in Data flow

Posted: 05 Aug 2013 02:50 AM PDT

I have a SP that does bunch of stuff. It ends with a select statement from a temp table. How do I invoke that SP and send the output to a Destination in Data Flow?

Clustering Question

Posted: 05 Aug 2013 02:48 AM PDT

Hello All,I have a question on SQL Server 2008 R2 Std Edition clustering. I am trying to setup clustering for our SQL Server staging environment. We have a lot of applications and all of them are using the name of our database server which is CPNDB01 in their app config connection strings. But if I setup clustering and bring up a new node CPNDB02 and perform a failover do you think I need to change the name of the database server in the connection string to CPNDB02 for all of our applications?I know we need to create a virtual ip address for SQLServer as well and we can use that in the connection string to prevent the name change every time a failover or a failback is performed. But what if I have to use only Server name but no IP in my app connection string?Thanks for your inputs

Delete a records in all Tables

Posted: 05 Aug 2013 01:41 AM PDT

Hi Team,I've having 120 tables in that 80+ tables have "emp_id" as a column_name, mistakenly i've inserted a record in to Employee table, there are many triggers on that table, it will automatically inserts records into many tables with column_name "emp_id"now i want to delete all the records in all tables with emp_id=136,how to delete all the records in all tables contains emp_id=136Please suggest..!

SQL Cluster Migration

Posted: 05 Aug 2013 01:59 AM PDT

Hi there,I have the task of migrating two 2-node SQL 2008 R2 clusters on to new hardware. Currently the plan is to create new clusters from scratch, script all the logins, jobs, linked servers etc. and then just restore all the databases onto it. Finally I will alter the DNS name of the cluster to be the same as the existing one so I don't have to modify all the applications.This will probably be fairly time consuming as there are a large number of databases on the servers.Is this the best way to do it or could someone suggest a easier method?! ....or do I just need to stop whinging and get on with it? ;-)Thanks,Matt

Using IF in a Stored Procedure. Syntax help neeed.

Posted: 05 Aug 2013 01:58 AM PDT

Hello all.I am trying to use my first IF statement within a SP. The concept is simple. I have a aspx web form where the user can select a CATEGORY (sends a Cat ID # which is a Int) and a "search phrase". Variables are sent to SQL Server via SP and results are spit out onto a page. The drop down list for "categories" also has a "ALL Categories" option in case the user wants to search them all. I have hard coded this "All" value to 9999. So the HTML behind the drop down list might look like this:<select name="ddlCategories" ....><option value="">**Please Select**</option><option selected="selected" value="9999"><ALL Categories></option><option value="5">Application Fees</option>...etc...My code then (asp.net) then passes the form values to the SP and gets the results back. Simple.I am trying to write the SP to use one of two SELECT Statements. This is what I have below but it seems to error on the last "END".Little help??Create Procedure [dbo].[sp_SearchDownloadsResults-Beta]@strSearchPhrase varchar(25),@intCategoryID IntASBEGIN-- User did NOT Select ALL Categories and searched by a specific category.If @intCategoryID <> 9999 Begin SELECT ....stufff.....FROM...stuff.... WHERE ((dbo.Downloads.Active = 1)) AND ( (dbo.Downloads.Title LIKE '%' + @strSearchPhrase + '%' ) OR (dbo.Downloads.Description LIKE '%' + @strSearchPhrase + '%' ) ) AND ([b]dbo.Downloads.DownloadCategoryID = @intCategoryID[/b]) ORDER BY dbo.Downloads.SortDate DESC, dbo.Downloads.Title ASCEnd--User opted to search by ALL categories.Else Begin SELECT ....etc.... WHERE ((dbo.Downloads.Active = 1)) AND ( (dbo.Downloads.Title LIKE '%' + @strSearchPhrase + '%' ) OR (dbo.Downloads.Description LIKE '%' + @strSearchPhrase + '%' ) ) ORDER BY dbo.Downloads.SortDate DESC, dbo.Downloads.Title ASC[b]End[/b] <--- This is where is seems to expect more.....

sp_spaceused columns?

Posted: 05 Aug 2013 12:55 AM PDT

Hi can someone help pleasei just did sp_spaceused '[db].[db].[demo]'where demo is my tablebut i would like to get the rows, reserverd , data , index_size and unused only for some column in my demo tablehow can i write this please

i am a newbie . help me to learn sql server

Posted: 20 Jul 2012 04:09 AM PDT

hi all, please guide me with some links to learn the basics of sql server

Convertion from Varchar to Datetime

Posted: 04 Aug 2013 09:45 PM PDT

How do I convert a string of format -> mmddyyyy into datetime in SQL Server 2008?My target column in 'DateTime'I have tried with Convert and most of the Date style values - I get a 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.' error message.

Export Contacts from Outlook to SQL Server 2008

Posted: 28 Feb 2013 06:28 AM PST

Hi All, I would like to export all the mail id's which are available in my outlook inbox and store those id's into my SQL Server 2008 table.Is there any chance do like this?If Yes can you please share your ideas

Analytical function for my Q

Posted: 29 Jul 2013 09:13 AM PDT

Hi,Is there any analytical function in sql server for achieve results like below:I did this with 2 temp tables, but might be something already there to look it nicer.I also need this group by DateIn, and display % for each delDays categoryThanks allM[code]create table #log (shipID int, datein datetime, delDays int)INSERT INTO #LOG (shipid , datein, delDays) select 1001, '2012-01-06', 9 union allselect 1002, '2012-01-06', 11 union allselect 1002 , '2012-01-06', 11 union allselect 1003 , '2012-01-06', 11 union allselect 1004 , '2012-01-06', 11 union allselect 1005 , '2012-01-06', 11 union allselect 1006 , '2012-01-06', 11 union allselect 1007 , '2012-01-06', 11 union allselect 1008 , '2012-01-06', 11 union allselect 1009 , '2012-01-06', 11 union allselect 1010 , '2012-01-06', 22 union allselect 2001 , '2012-02-06', 15 union allselect 2002 , '2012-02-06', 16 union allselect 2003 , '2012-02-06', 33 union allselect 2004 , '2012-02-06', 22 -- Need this result result:DateIn | <20days | <30days | >30days |----------|----------|-----------------------2012-01-06| 90% | 10% | | 2012-02-06| 50% | 25% | 25% | [/code]
Posted by at No comments:

Monday, August 5, 2013

[SQL Server] Get last transaction from multiple groups of records

[SQL Server] Get last transaction from multiple groups of records

Get last transaction from multiple groups of records

Posted: 05 Aug 2013 02:31 AM PDT

I need to pull the last transaction that occurred before a specific type of transaction, using the transaction dates. It should return 2 records, the Last_Dt for c_Dt 2010-09-06 and the Last_Dt for c_Dt 2010-12-22, but is returning all the records for both groups:c_id c_Dt Last_Dt c_rep123 2010-09-06 2010-09-06 25456 2010-09-06 2010-08-06 23789 2010-09-06 2010-07-06 25123 2011-12-06 2011-09-06 25456 2011-12-06 2011-08-06 23789 2011-12-06 2011-07-06 25This is the code:SELECT DISTINCT a.c_id, b.c_Dt, MAX(d.c_dt) AS LastDt, a.c_repFROM tbl_c_master AS a INNER JOIN tbl_s_det AS d ON a.c _id = d.c_id INNER JOIN(SELECT c_id, c_rep, MIN(DISTINCT c_dt) AS CpDt FROM vw_C_Pd GROUP BY c_id, s_code, c_repHAVING (s_code = '01') OR (s_code = '10') OR (s_code LIKE '14') OR (s_code = '24') OR (s_code = '20')) AS b a.c_id = b.c_idGROUP BY a.c_id, b.c_Dt, a.c_rep, d.c_dt HAVING (a.c_id = b.c_id) AND (MAX(d.c_dt) < b. CpDt)ORDER BY a.c_id, b.c_DtI thought MAX would do it, but it made no difference. I also tried with a CTE but it came up empty (no records returned). Any suggestions greatly appreciated!

Collation issues

Posted: 04 Aug 2013 11:24 PM PDT

Hello all,My first post here.I am a total newb and have been battling my way through setting up a slightly more robust membership system than is provided out-the-box using MS Visual Studio 2010.I have reached the point where everything that I need from it has been achieved.However, when I transfer the SQL Server Xpress DB from my local machine to a SQL Server 2008 DB at my web host (shared server) I receive collation errors.The process I am following is:1. The "Publish to Provider" option on the Xpress DB from within VS2010. An error free script is generated2. Using SQL Management Studio I then execute this script to the SQL Server 2008 DB on my shared server.The errors reported are:[i]Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 53Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 58Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 87Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_RemoveUsersFromRoles, Line 92Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 48Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 52Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 79Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 83Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 468, Level 16, State 9, Procedure aspnet_UsersInRoles_AddUsersToRoles, Line 93Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Msg 15151, Level 16, State 1, Line 1Cannot find the object 'aspnet_UsersInRoles_AddUsersToRoles', because it does not exist or you do not have permission.Msg 15151, Level 16, State 1, Line 1Cannot find the object 'aspnet_UsersInRoles_RemoveUsersFromRoles', because it does not exist or you do not have permission.[/i]I have no idea where to start starting this little lot out :Wow:Any help appreciated.Tx,Martin

Get last transaction from multiple groups of records

Posted: 05 Aug 2013 02:28 AM PDT

DUPLICATE POST - NEED TO DELETE

Stored Procedure syntax

Posted: 05 Aug 2013 12:38 AM PDT

Hello All,I'm using the procedure to return results to an Access 2010 .adp:I would like to return a result if the user enters the following:The search criteria SubjectNumber = 'UK01' would return all SubjectNumbers starting with 'UK001...'. and so on. However, no matter how I write the SQL, it will only return a result for a complete SubjectNumber ('UK010001').I have tried the following in an SQL Server query, which returns the result I expect. So what's wrong with the SQL in my stored procedure?Thanks for any help.[code="sql"]select *from tblPersonwhere SubjectNumber like '%'+ 'UK' +'%'[/code]Stored Procedure:[code="sql"]@SubjectNumber char(9)ASSELECT PersonID, SubjectNumber, NHSNo, PostcodeFROM tblPersonWHERE (@SubjectNumber IS NULL OR SubjectNumber LIKE '%' + @SubjectNumber + '%')[/code]

concatenate ' and % in like estatement

Posted: 04 Aug 2013 08:41 PM PDT

Hi, I have a stored procedure for search data.I want add % to it, so we are not have to search with exact values.I already tried something like N'' + % + @name + % + '' but it throw exception.This code works fine with exact values.[code="sql"]PROCEDURE search(@name nvarchar(20)=null)select * from TableNamewhere FirstName like N'' + @name + ''[/code]Thank you for help.
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog