Saturday, July 6, 2013

[SQL Server 2008 issues] Incorrect totals when summing counts for past 12 month

[SQL Server 2008 issues] Incorrect totals when summing counts for past 12 month

Incorrect totals when summing counts for past 12 month

Posted: 05 Jul 2013 07:08 PM PDT

Hello everyone,I have run into an issue where the query below returns monthly totals that include the counts with "lastupdatedate" on the 1st day of the next month as well. In other words, counts in [b]April 2013[/b] also include records with "lastupdatedate" value of 05/01/2013 as well. I can't seem to find a solution to this one. Any help is appreciated!The data type of the "lastupdatedate" column is datetime.[code="sql"]SELECT DATEDIFF(SECOND,{d '1970-01-01'}, MIN(ins.lastUpdateDate)) AS Ids, CASE WHEN MONTH(ins.lastUpdateDate) = 1 THEN 'Jan, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) WHEN MONTH(ins.lastUpdateDate) = 2 THEN 'Feb, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) WHEN MONTH(ins.lastUpdateDate) = 3 THEN 'Mar, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) WHEN MONTH(ins.lastUpdateDate) = 4 THEN 'Apr, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) WHEN MONTH(ins.lastUpdateDate) = 5 THEN 'May, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) WHEN MONTH(ins.lastUpdateDate) = 6 THEN 'Jun, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) WHEN MONTH(ins.lastUpdateDate) = 7 THEN 'Jul, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) WHEN MONTH(ins.lastUpdateDate) = 8 THEN 'Aug, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) WHEN MONTH(ins.lastUpdateDate) = 9 THEN 'Sep, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) WHEN MONTH(ins.lastUpdateDate) = 10 THEN 'Oct, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) WHEN MONTH(ins.lastUpdateDate) = 11 THEN 'Nov, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) WHEN MONTH(ins.lastUpdateDate) = 12 THEN 'Dec, ' + CAST(YEAR(ins.lastUpdateDate) as nvarchar(4)) END AS Groups, COUNT(ins.Id) AS Counts FROM tblInsp ins WHERE ins.custID = 1234and ins.lastUpdateDate > Dateadd(year,-1,getdate()) GROUP BY MONTH(ins.lastUpdateDate), YEAR(ins.lastUpdateDate)[/code]Thanks a bunch!

george carlin quotes religion

Posted: 05 Jul 2013 05:53 PM PDT

[b][url=http://www.searchquotes.com/search/George_Carlin_Religion/]george carlin quotes religion[/url][/b]

How to Store Data in Database using Data table

Posted: 01 Jul 2013 05:10 PM PDT

c# - Insert data from DataTable to database table

Effect of Clustered Index on Non-Clustered Index

Posted: 02 Jul 2013 01:50 AM PDT

Not remotely a DBA so forgive me if this is a novice question, but will having a clustered index in a database improve the performance of non-clustered indexes?Sean

collation with OPENROWSET BULK, file in different collation than server default

Posted: 05 Jul 2013 10:55 AM PDT

I am having a difficulty with SQL Server collations (2008 R2) that is driving me up the wall. I've learned everything I can about MSSQL and Windows collations and character sets (lots of crazy details!), but I still can't tell what's going wrong. Hoping somebody here can save me...My goal is simple:-- I have a program that takes text files (all in Windows Code Page 1252) and bulk inserts them into database tables. -- Every CHAR and VARCHAR field in every table has collation SQL_Latin1_General_CP1_CI_AS. -- The format files mark these fields as SQLCHAR, likewise with collation SQL_Latin1_General_CP1_CI_AS.-- The database and table default collations are Latin1_General_CI_AS.-- The server default collation is Japanese_CI_AS-- The result of sp_helpsort is Japanese, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitiveI'm using MERGE commands, with the file as a source table. The relevant part:[CODE]SELECT mycol1, mycol2 FROM OPENROWSET (BULK 'C:\my\file.txt', FORMATFILE='C:\my\format.fmt', FIRSTROW = 2) AS Z[/CODE]I receive the error:[CODE]Msg 7339, Level 16, State 1, Line 2OLE DB provider 'BULK' for linked server '(null)' returned invalid data for column '[BULK].title'.[/CODE]I've narrowed the problem down to accented "é" characters. When I remove those from the file, it loads properly. Since the file itself, the format file, and the table all understand the single-byte é, I don't see why it should cause problems.The only idea I have -- and I hope this isn't right -- is if MSSQL is trying to convert the data to Unicode even though it's coming from and going to Windows-1252, due to the Server default of Japanese_CI_AS. Unfortunately, this page suggests that might be the case:[URL]http://msdn.microsoft.com/en-us/library/ms191145(v=sql.90).aspx[/URL][QUOTE]"The use remote collation option specifies whether the collation of a remote column or of a local server will be used... When false, distributed queries always use the default collation of the local server instance, and collation name and the collation of remote columns are ignored...."Using the linked server options is the only way to enable using remote collations. Therefore, queries that are constructed that use ad hoc names provided by OPENROWSET and OPENDATASOURCE cannot use the collation information of remote character data. Additionally, all linked servers in SQL Server version 7.0 that are upgraded to SQL Server 2000 or later are set to use remote collation = false."[/QUOTE]I interpret this as meaning that it will read my Windows-1252 file as Unicode instead (Japanese_CI_AS). That would explain the error, since "é" is a two-byte character in Unicode and the one-byte equivalent wouldn't be understood. But why are my settings in the format file and table structure being ignored? More importantly, is there a way around this? This is an automated process and never processes the same file twice, so setting up the every new file as a "remote database" isn't an option unless that can also be automated.Is there anything I can change to fix this? I can change the format file, the table settings, or the database settings. I can change the query, but only somewhat since I have to use MERGE as these are UPSERT operations. I can't change the server settings or the data files themselves.

SELECTING A CASE VALUE ???

Posted: 05 Jul 2013 07:44 AM PDT

HiI am using the following to retrive a clients "secure" name if alias-type of 004 exists.Problem I have is if the client has more than one alias type(001,002, 003) they all show upWhat I need to do is if a client has an alias code of 004 then use that no matter how many they haveif they don't have an oo4 and have multiple say 003's then use the client name ..Thanks SELECT CASE WHEN at.CODE = '004' THEN a.FName + ' ' + isnull(a.MName, '') + ' ' + + a.lname ELSE c.FName + ' ' + ISNULL(c.MName, '') + ' ' + c.LName END AS Tag_Data FROM Client AS C LEFT JOIN -- Note the order of the clausing here. It causes an -- inner join to the rest of the tables BEFORE the -- outer join is included. Client_To_Alias_Collection AS AC INNER JOIN Alias AS A ON AC.OID_Link = A.OID INNER JOIN Alias_Type AS AT ON AT.OID = A.Alias_Type_Moniker ON c.oid = ac.oid AND A.EXPDate IS NULL WHERE c.OID = '12345'

Implicit conversion using COALESCE or CASE

Posted: 03 Jul 2013 06:07 AM PDT

Hi,I've discovered that I always seem to get an implicit conversion to INT when using this syntax:SELECT ... ORDER BY COALESCE(SectionOrder, 99); (Column [SectionOrder] is defined as SMALLINT).The execution plan COMPUTE SCALAR operation shows this syntax as being changed to a CASE statement:CASE WHEN SectionOrder IS NOT NULL THEN convert_implicit(int,SectionOrder,0) ELSE (99) ENDIs this conversion a "feature" of SQL Server? Any info is appreciated.~ Jeff

Curious question about Transaction Replication from a backup

Posted: 05 Jul 2013 06:31 AM PDT

I've read various articles on how to do this but tried a different approach and while it appears to work for the most part, there were still some issues that I'm hoping someone can point out the obvious. We have replication already set up and going on a reporting server. There is a separate publisher, distributor, and subscriber server. Our attempt is to recreate "clone" the existing replication set up on the existing reporting server to a new reporting server.Our process: - We stopped the distributor agent - Waited for replicated transactions to complete at the subscriber - Took a full backup of the subscribed databases - Restored these databases on the new reporting server (at this point both subscribers should be 100% the same) - Created a new subscription to the existing publications on the new server - Re-enabled the distribution agent and waited for replicated commands to hit both subscribersThis seems to work, but for some tables we're off by a few hundred, to a few thousand rows...The only error we can find is "The row was not found at the Subscriber when applying the replicated command" - which to keep things flowing we added -SKIPERRORS 20598It appears very random, but is keeping things in sync for the most part (minus the original missing rows)Any suggestions or ideas as to we are getting these errors? Shouldn't both database servers be exactly the same in the method we took?

credential/proxy

Posted: 30 Jun 2013 01:05 PM PDT

I have a proc as below ALTER PROCEDURE [dbo].[sp_proc_test] WITH EXECUTE AS 'ABCD\svcaccount'AS EXEC msdb.dbo.sp_start_job @job_name = 'JB_ABC_Run'GO I setup a proxy account for windows login and mapped a SQL login to it. When I try to execute through a SQL login, I am getting following error. The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'. I have made sure the windows (ABCD\svcaccount) login has full access (owner) to the msdb db. What am I missing?

Issue while creating indexed view after joining two non related tables

Posted: 04 Jul 2013 09:00 PM PDT

I am trying an create the indexed view after joining two non related tables. Please correct me if my approach is wrong and add your thoughts for the issue I had mentioned below.[b]Syntax used for creating view[/b]CREATE VIEW [i]MyViewName[/i]WITH SCHEMABINDINGASselect field1, field 2 from tableOne (not the exact query)FULL OUTER JOIN select field1, field2 from tableTwo (not the exact query)[b]Syntax used for creating index on view[/b]CREATE UNIQUE CLUSTERED INDEX ClusteredIndexTestON dbo.[i]MyViewName[/i](field1,field2)[b]Issue occurred while trying to create index[/b]Msg 10109Cannot create index on view "db.dbo.MyViewName" because it references derived table "tableOne" (defined by SELECT statement in FROM clause). Consider removing the reference to the derived table or not indexing the view.

How to Check Change in the Status

Posted: 02 Jul 2013 06:34 AM PDT

I have a table for instructors which contains subjects, date and time and Status information about their classes.Once they register on the website they get the pending status, and somebody from administration either Approve, deny or Cancel their class.so the status changes from Pending to either Approv, deny Or cancel.I want to write a query that to piush the information to instructors if their Status gets changed from Pending to Approve, Deny Or CancelHow do I do that.Thaks,Blyzzard

Error descriptions

Posted: 05 Jul 2013 04:54 AM PDT

Is the quality of error descriptions better trapped at the ssis package level rather than at the sql agent job level?We have ssis packages executed from sql agent jobs, but if the job fails, we get 'The job failed. The job was invoked by user xxxx The last step to run was step 1'Instead of this, is there any value to logging the error earlier from the ssis package level by using a script in the error event handler? Do the ssis errors give a better, more detailed explanation of what might have gone wrong?It turned out that a stored procedure was changed that the ssis package depended on, but it was not obvious from the error message. So I was wondering whether ssis error collection would have told us that, instead of having to figure it out from starting from the sql job level...iow, I thionk what happened is the package error bubbled up to the job, but the detail arising from ssis was not trapped or available from there.thanks a lot

Practical Use of Filegroups, Multi-data files, and other IO Tweaks

Posted: 05 Jul 2013 04:24 AM PDT

I'm curious to hear from folks that have decided to deploy (or I guess also decided not to deploy) various IO tweaks in their environment on a properly sized, multi-use (many databases, mixed load OLTP) SQL server.Here's why. For lots of reasons I won't go into...our DB servers are not in our virtual racks...nor are they on our SAN. They are still dedicated chassis with cherry picked IO systems. They sport a single RAID for all things data (meaning, not: OS, program files, swap files, etc. Just data) That RAID is usually around 30 x 15K RPM SAS drives with max battery backed RAID cache (1.5GB currently) and I jump around between RAID 5 and 6...with additional hot spares...point here is I can usually lose up to 3 drives before I'm in trouble).So, my IO approach has always been to over-provision. Buy more space than needed, by even more IO throughput than needed, buy a big cache card, and let the RAID & IO subsystem work out the details. One major reason for this is my DB servers are usually sporting 100+ databases with a mix of vendor apps, SharePoint, custom DB's APP's, all serving a combination of OLTP and big reporting loads. Breaking out log files per the typical definition of IO isolation seems senseless in that I would be separating out 100+ log files (thereby turning 100+ synchronous workloads into something inherently chaotic and therefore back to something non-sequential).mdf, ldf, and index isolation (by physical disks) always seems heavily laced with pro vs con. If I broke up my 30 drives into say, 3 sets of 10 drive arrays, trying to do my own IO isolation seems like it could just as often introduce as many problems as I solve (the thought being that I would occationally experience a bottleneck on the 10 drive array I would not have experienced on my original 30 drive array).So as to stop rambling I'll open the floor to other folks that may have a test environment and better opportunities for testing different IO isolation...Am wrong headed here? Are there folks out there with a similar environment that are breaking apart big arrays, engaging in custom IO islolation (using any SQL methods available) and finding it worth the trouble.Thanks.

Mixed Mode Authentication not consistent between Windows Users using Mgmt Studio

Posted: 05 Jul 2013 12:28 AM PDT

Currently experiencing strange issue I have not seen before on a SQL Server 2008 R2 instance running on Windows Server 2008 R2. Access to SQL Server via Mgmt Studio under the 'sa' user does not work regardless of which Windows User is logged in. The only way to access SQL Server via Mgmt Studio is to log into Windows with the administrator user and use Windows Authentication to authenticate against SQL Server.[u]SQL Server Configuration[/u]:Mixed Mode AuthenticationShared Memory Prorocol Access Only (Other protocols have been tried)SQL Native Client 10 being used to access Sql ServerAll of the above is only experienced when using Management Studio. When accessing via ODBC programmatically, configured SQL Server user works just fine regardless of which Windows user is logged in.

Deadlocks after migration from SQL server 2005 to 2008R2

Posted: 05 Jul 2013 12:46 AM PDT

Have migrated the SQL server 2005 to 2008R2 and we are now facing lots of deadlocks issues. Have enabled the trace flag 1222 and can see numerous deadlocks loaded in error logs. One of them is below. Can somebody please help what may be the issue?------------------------------------------------------------------------------07/05/2013 15:38:08,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000005B5989950 Mode: Sch-M SPID:80 BatchID:0 ECID:0 TaskProxy:(0x0000000D5FF36540) Value:0xd297600 Cost:(0/0)07/05/2013 15:38:08,spid12s,Unknown,Victim Resource Owner:07/05/2013 15:38:08,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:07/05/2013 15:38:08,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000800845F0 Mode: Sch-S SPID:139 BatchID:0 ECID:0 TaskProxy:(0x0000000F5CE68518) Value:0xd1c3640 Cost:(0/0)07/05/2013 15:38:08,spid12s,Unknown,Requested by:07/05/2013 15:38:08,spid12s,Unknown,Input Buf: RPC Event: Proc [Database Id = 12 Object Id = 1508968502]07/05/2013 15:38:08,spid12s,Unknown,SPID: 80 ECID: 0 Statement Type: ALTER TABLE Line #: 2507/05/2013 15:38:08,spid12s,Unknown,Owner:0x000000000CFCDC80 Mode: Sch-M Flg:0x40 Ref:0 Life:20000000 SPID:80 ECID:0 XactLockInfo: 0x00000005B598999007/05/2013 15:38:08,spid12s,Unknown,Grant List 0:07/05/2013 15:38:08,spid12s,Unknown,OBJECT: 2:1250113027:4 CleanCnt:2 Mode:Sch-M Flags: 0x107/05/2013 15:38:08,spid12s,Unknown,Node:207/05/2013 15:38:08,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:07/05/2013 15:38:08,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000005B5989950 Mode: Sch-M SPID:80 BatchID:0 ECID:0 TaskProxy:(0x0000000D5FF36540) Value:0xd297600 Cost:(0/0)07/05/2013 15:38:08,spid12s,Unknown,Requested by:07/05/2013 15:38:08,spid12s,Unknown,Input Buf: RPC Event: Proc [Database Id = 12 Object Id = 1508968502]07/05/2013 15:38:08,spid12s,Unknown,SPID: 139 ECID: 0 Statement Type: UPDATE Line #: 37107/05/2013 15:38:08,spid12s,Unknown,Owner:0x000000000C5DC5C0 Mode: Sch-S Flg:0x40 Ref:1 Life:00000000 SPID:139 ECID:0 XactLockInfo: 0x0000000E570E1BA007/05/2013 15:38:08,spid12s,Unknown,Grant List 0:07/05/2013 15:38:08,spid12s,Unknown,OBJECT: 2:1250113027:6 CleanCnt:2 Mode:Sch-S Flags: 0x107/05/2013 15:38:08,spid12s,Unknown,Node:107/05/2013 15:38:08,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:07/05/2013 15:38:08,spid12s,Unknown,Wait-for graph07/05/2013 15:38:08,spid12s,Unknown,Deadlock encountered .... Printing deadlock information07/05/2013 15:37:51,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000D38AE59D0 Mode: Sch-S SPID:179 BatchID:0 ECID:0 TaskProxy:(0x0000000EB2060518) Value:0xcdfc380 Cost:(0/0)07/05/2013 15:37:51,spid12s,Unknown,Victim Resource Owner:07/05/2013 15:37:51,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:07/05/2013 15:37:51,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x00000000B5490E80 Mode: Sch-M SPID:96 BatchID:0 ECID:0 TaskProxy:(0x0000000CE0EAE540) Value:0xdc44280 Cost:(0/0)07/05/2013 15:37:51,spid12s,Unknown,Requested by:07/05/2013 15:37:51,spid12s,Unknown,Input Buf: RPC Event: Proc [Database Id = 12 Object Id = 1508968502]07/05/2013 15:37:51,spid12s,Unknown,SPID: 179 ECID: 0 Statement Type: UPDATE Line #: 11607/05/2013 15:37:51,spid12s,Unknown,Owner:0x000000000C6B0C40 Mode: Sch-S Flg:0x40 Ref:1 Life:00000000 SPID:179 ECID:0 XactLockInfo: 0x000000011F019BA007/05/2013 15:37:51,spid12s,Unknown,Grant List 0:07/05/2013 15:37:51,spid12s,Unknown,OBJECT: 2:1401065560:10 CleanCnt:2 Mode:Sch-S Flags: 0x107/05/2013 15:37:51,spid12s,Unknown,Node:207/05/2013 15:37:51,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:07/05/2013 15:37:51,spid12s,Unknown,ResType:LockOwner Stype:'OR'Xdes:0x0000000D38AE59D0 Mode: Sch-S SPID:179 BatchID:0 ECID:0 TaskProxy:(0x0000000EB2060518) Value:0xcdfc380 Cost:(0/0)07/05/2013 15:37:51,spid12s,Unknown,Requested by:07/05/2013 15:37:51,spid12s,Unknown,Input Buf: RPC Event: Proc [Database Id = 12 Object Id = 1508968502]07/05/2013 15:37:51,spid12s,Unknown,SPID: 96 ECID: 0 Statement Type: ALTER TABLE Line #: 2407/05/2013 15:37:51,spid12s,Unknown,Owner:0x000000000EAE1D00 Mode: Sch-M Flg:0x40 Ref:0 Life:20000000 SPID:96 ECID:0 XactLockInfo: 0x00000000B5490EC007/05/2013 15:37:51,spid12s,Unknown,Grant List 1:07/05/2013 15:37:51,spid12s,Unknown,OBJECT: 2:1401065560:6 CleanCnt:2 Mode:Sch-M Flags: 0x107/05/2013 15:37:51,spid12s,Unknown,Node:107/05/2013 15:37:51,spid12s,Unknown,Log Viewer could not read information for this log entry. Cause: Data is Null. This method or property cannot be called on Null values.. Content:07/05/2013 15:37:51,spid12s,Unknown,Wait-for graph07/05/2013 15:37:51,spid12s,Unknown,Deadlock encountered .... Printing deadlock information

Massive slowdown in SELECT statement in Cursor from SQL 2000 to 2008 -- need help!

Posted: 28 Jun 2013 04:46 AM PDT

I have a SELECT statement in a cursor in a stored procedure that ran extremely fast in SQL Server 2000. I just migrated our database to SQL Server 2008 R2 (SP2) for a customer, and the same SELECT now takes fifty times longer to execute. I have logging that measure this performance, which is how I found out. The database on the new 2008 R2 server is set to 2008 compatibility. The slow performance occurs in both 2000 and 2008 compatibility mode. Some notes: I need to keep the SELECT statement inside the cursor. It calculates the number of cross sales for an employee in a given day, which is then used to figure out what incentive earnings they get for the cross sales. The SVAccountsHistory table has three million rows; SVCrossSales has 16,000; SVSales has 74,000; SVAccounts has 90,000. The joins between table use the primary keys in these tables. I have a number of other SELECT statements that run just fine for the incentive calculations that I do elsewhere in the stored procedure, so it's just this SELECT in particular. What I'm guessing is the problem in SQL 2008 is that it doesn't like the DATEDIFF compare or the COUNT(SELECT DISTINCT xxx) calculation. It's extremely maddening, I have to say. I'm at my wit's end, and desperately need to figure out what SQL 2008 is choking on in comparison to SQL 2000. Thank you for your help!Here's the SELECT statement:[quote]SELECT @tmpCalcValue1 = COUNT(DISTINCT SVCrossSalesHistory.SalesNum) * @EmpRuleValue1FROM SVCrossSalesHistory INNER JOIN SVSales ON ( SVCrossSalesHistory.INum = SVSales.INum AND SVCrossSalesHistory.SalesNum = SVSales.SalesNum AND SVSales.SaleDate = @tmpDate AND -- Go back to the day of consideration SVSales.OrgNum = @OrgNum AND -- Selected emp SVSales.DeleteFlg = 'N') INNER JOIN SVGoalProdXref ON ( SVSales.INum = SVGoalProdXref.INum AND SVSales.ProdNum = SVGoalProdXref.ProdNum AND SVGoalProdXref.GoalNum = @GoalNum AND -- Go against all the prods for the selected goal SVGoalProdXref.DeleteFlg = 'N') INNER JOIN SVAccounts ON ( SVSales.INum = SVAccounts.INum AND SVSales.FullAcctNum = SVAccounts.FullAcctNum AND ( -- If the account was closed, determine if the minimum # active days was met during the life of the account DATEDIFF(DD,OpenDate, CASE WHEN CloseDate = '1/1/1900' THEN NULL ELSE CloseDate END) > @EmpRuleValue2 OR -- @EmpRuleValue2 = 'x # days active' DATEDIFF(DD,OpenDate, CASE WHEN CloseDate = '1/1/1900' THEN NULL ELSE CloseDate END) IS NULL)) INNER JOIN SVAccountsHistory ON ( SVAccounts.INum = SVAccountsHistory.INum AND SVAccounts.FullAcctNum = SVAccountsHistory.FullAcctNum AND SVAccountsHistory.HistoryDate = @StartTime AND -- Today SVAccountsHistory.Balance > ISNULL(@EmpRuleValue5,0)) -- '$' value in 'balance > $'WHERE SVCrossSalesHistory.INum = @INum AND SVCrossSalesHistory.CSFlg = 'Y' AND -- Must be a cross sale SVCrossSalesHistory.IsNewCustFlg = 'Y' -- Consider new customers only[/quote]

getting all the days of week eevn if data doesnt exist

Posted: 05 Jul 2013 01:39 AM PDT

Hi,I've tried this before but just not getting it... sorry..I have an aux.date table with date info date, dayofweek, kindofday etcI want to have a start and end date(using SSRS) to find recorded servicesso lets say the put in 7/1/13 thru 7/5/13 and 7/4 is a holiday and no recorded services existI want output similar to:[b]date record serv KINDOFDAY[/b]7/1/13 recservdata1 .. Weekday7/1/13 recservdata2... WeekDay7/2/13 recservdata3... Weekday7/3/13 recservdata4... Weekday7/4/13 NULL Holiday7/5/13 recservdata5... Weekdayetc... I have a view which grabs the days of the week (rpt_cal_Select)then I attached to this but on get records with data... SELECT dbo.RECORDED_SERVICE.SERVICE_ITEM_MONIKER, dbo.rpt_Cal_Select.Date AS Expr1, dbo.SERVICE_ITEM.Code, dbo.rpt_Cal_Select.*, dbo.RECORDED_SERVICE.OIDFROM dbo.rpt_Cal_Select INNER JOIN dbo.RECORDED_SERVICE ON dbo.rpt_Cal_Select.Date = DATEADD(dd, 0, DATEDIFF(dd, 0, dbo.RECORDED_SERVICE.STARTTIME)) LEFT OUTER JOIN dbo.SERVICE_ITEM ON dbo.RECORDED_SERVICE.SERVICE_ITEM_MONIKER = dbo.SERVICE_ITEM.OIDThanksJoe

I can't get my fulltext catalog to populate automatically

Posted: 05 Jul 2013 01:01 AM PDT

I have one fulltext index I created three days ago in my default fulltext catalog. It's set for automatic change tracking, but it doesn't seem to be updating automatically. Not sure where I'm going wrong. I had assumed that, after creation, a fulltext index would automatically update itself. Any ideas?

help with joining to table with non unique index and duplicate rows

Posted: 04 Jul 2013 09:43 PM PDT

Hi,I have the below query to join several tables, and retrieve customer data:[code="sql"]select sv.SVSEQ, sc.SCACO, ac.f1 as AccountNo, ne.NEAN, ne.NEEAN,bg.BGCFN1,bg.BGCFN2, bg.BGCFN3, bg.BGCUS, sv.SVNA1 ,sv.SVNA2, sv.SVNA3, sv.SVNA4, sv.SVNA5, sv.SVPZIP,sv.SVCSA, sc.SCACO,sx.SXDLMfrom NEPF ne inner join tmpAccountList ac on NEEAN=ac.f1 inner join BGPF bg on bg.BGCUS=ne.NEAN inner join SCPF sc on sc.SCAN=bg.BGCUS and bg.BGCUS=ne.NEAN and sc.SCAN=LEFT(ac.f1,6) inner join sxpf sx on sx.SXCUS=LEFT(ac.f1,6) INNER JOIN SVPF SV ON sv.SVDLM=sx.SXDLM[/code]my issue is with the table [b]SVPF[/b] which has thousands of duplicate records.There is a column [i]SVSEQ[/i] which is numbered, but not uniquely. I want to join to that table, but only get one row for each individual Sequence in my results set.Without that table, I have 150 records, with that table, I have several hundred thousand.How can I add that table using the [i]SVSEQ[/i] column without getting duplicates?

problem select insert

Posted: 04 Jul 2013 11:32 PM PDT

hi in database have many tables for selectionfor example table 1phone kod1 kod2 adress kod3 email kod4 kod5--- --- --- --- --- ------- ---- --- ---- ----table2name1 kod1---- ----christie 1james 2table3name2 kod2------- -------jhony 1paul 2table3 name2 kod2----- -------hasan 1rashid 2 table4 name4 kod4------ ---asif 1natiq 2table5 name5 kod5---------- -------arnold 1vandame 1insert table111111 ,james ,jhony,chicago,hasan,www.mail.ru , asif,arnoldafter inserted table 1phone kod1 kod2 adress kod3 email kod4 kod5--- --- --- --- --- ------- ---- --- 11111 2 1 chicago 1 www.mail.ru 1 1

SQL Varchar(max)

Posted: 04 Jul 2013 11:36 PM PDT

Hi Team,In sql server 2008R2, what is the max characters for Varchar(max).i've declared a variable in a stored proc as Temp Varchar(max), but am passing a parameter with 95000 characters, is it Work..?Please Suggest...

The system has rebooted from a Automatic Server Recovery (ASR) event.

Posted: 01 Jul 2013 11:44 PM PDT

We are facing a frequent restart of our production server once in a month or so. The event log is as below..[i]User ActionDetermine the nature of the Automatic Server Recovery (ASR) event, and take corrective action. WBEM Indication PropertiesAlertingElementFormat: 1 0x1 (Other)AlertType: 5 0x5 (Device Alert)BladeBay: "11"BladeName: "DBServer.histogenetics.com"Description: "The system has rebooted from a Automatic Server Recovery (ASR) event."EnclosureName: "OB-005DD323242"EventCategory: 16 0x10 (System Power)EventID: "1"ImpactedDomain: 3 0x3 (Enclosure)IndicationIdentifier: "{B211AC7F-9D6F-435C-825E-560CB382D0CF}"IndicationTime: "20130701224253.923000-240"NetworkAddresses[0]: "fe80::a8d7:c0f3:ac5f:b15d%10"NetworkAddresses[1]: "xxx.xxx.xxx.xx"OSType: 103 0x67 (Microsoft Windows Server 2008 R2)OSVersion: "6.1.7601"PerceivedSeverity: 5 0x5 (Major)ProbableCause: 111 0x6f (Timeout)ProbableCauseDescription: "ASR Reboot Occurred"ProviderName: "HP Recovery"ProviderVersion: "2.7.0.0"RackName: ""RackUID: ""RecommendedActions[0]: "Determine the nature of the Automatic Server Recovery (ASR) event, and take corrective action."Summary: "ASR reboot occurred"SystemCreationClassName: "HP_WinComputerSystem"SystemFirmwareVersion[0]: "2010.03.30"SystemFirmwareVersion[1]: "2010.03.30"SystemGUID: "33424235-3532-584D-51343-425235235"SystemModel: "ProLiant BL460c G6"SystemName: "DBServer.histogenetics.com"SystemProductID: "785725-K22"SystemSerialNumber: "XDfs898sd"TIME_CREATED: 130172065740131089 0x1ce76cdda1e8b11[/i]Did anybody faced a similar problem and get it solved..

Dynamicly make datamarts fore bussiness

Posted: 04 Jul 2013 10:39 PM PDT

Hello,I am trying to make a SSIS load package with I can make datamarts/Exports fore the bussines.I want to do the setup of al the exports in 1 table like:IDNameDescriptionFrequentieSourse SQL statementTarget DatabaseTarget SchemeTarget TableI have build a SSIS package with a Execute SQL task to get the setup variables.That go to a For Each ContainerWith a Data Flow task with a source with the Source SQL statement variable.But then i have problems to setup a destination and to dynamicly drop and create the destination table.I am so close but can't find a solution.Hope you could help me on how to do the rest.If you need more information please ask.

Days Since Last Backup

Posted: 04 Jul 2013 10:20 PM PDT

Hi How to calculate Days since last Backup

How to do tempdb utilazation??

Posted: 04 Jul 2013 08:15 PM PDT

How to do tempdb utilazation in mssq; server ?

SQL Server replication service Status

Posted: 04 Jul 2013 09:48 PM PDT

Hi How to get the status of the SQL Server replication service by T-SQL Statement
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog