Thursday, August 29, 2013

[MS SQL Server] how to create the alerts for services

[MS SQL Server] how to create the alerts for services

how to create the alerts for services

Posted: 29 Aug 2013 03:14 AM PDT

Hi all.here in my environment we are not using any monitoring tools for alerts.we are planning to create the alerts. if the services are down we need a alert message. how can i configure the alerts without monitoring tool.

Tempdb grows suddenly.

Posted: 28 Aug 2013 08:50 PM PDT

HiI know that TempDB grows if it needs and if you restart the sql server the TempDB resets to it's configured size.On a sql server we have i have never had any problem with TempDB.The size has always been about 2 GB.I have run many big querys against it and it have never grown.Every week a rebuild index is being run on the databas that is about 40GB.But suddenly , the size jump up to 24 GB on the TempDB.I restart the server and everything is normal.But then over a night the size jump up to 42 GB.And during that time the only thing that has happen is the same thing thathas happend every night.If i run:SELECT SUM(unallocated_extent_page_count) AS [free pages], (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]FROM sys.dm_db_file_space_usage;It says that about 40 GB is free space in TempDB.I am very confused.What should i do ?

create user script

Posted: 28 Aug 2013 07:42 AM PDT

I'd like to have a script that contains all the creation of users and statements to add those users to different roles. I have to restored this particular database and on every restore I loose the security assignment on my users. I could use the user interface to reset those users but it would be much easier to use the script since I have to restore every week.I have the following script, but I'd like to assign the dbo as the default schema for each use so I don't end up creating user schemas.CREATE LOGIN [DOMAIN\user1] FROM WINDOWS;GOUSE [Database1]GOCREATE USER "DOMAIN\user1" FOR LOGIN "DOMIAN\user1"EXEC sp_addrolemember N'db_datareader', N'DOMAIN\user1"EXEC sp_addrolemember N'db_executor', N'DOMAIN\user1"The script above works great but when I go to my security for the user on the database I see that a schema "DOMIAN\user1" has been created and assigned to the database user. How can I assign the schema "dbo" to the newly created database user?Thank you.

SQL server startup parameters

Posted: 28 Aug 2013 06:15 AM PDT

Hi,Can anyone provide me the stored proc or script to get all start parameters for an instance.Thanks in advance
Posted by at No comments:

[Articles] The Value of Your Time

[Articles] The Value of Your Time

The Value of Your Time

Posted: 28 Aug 2013 11:00 PM PDT

DBAs are expensive, so isn't their time valuable? Are you aware of what you cost the company and use your time wisely? Steve Jones talks a bit about how to choose on what you should be working.
Posted by at No comments:

[SQL 2012] Connecting SQL servers on the same or different domains with no delegation or linking

[SQL 2012] Connecting SQL servers on the same or different domains with no delegation or linking

Connecting SQL servers on the same or different domains with no delegation or linking

Posted: 29 Aug 2013 01:26 AM PDT

I have been asked to do connect 2 SQL servers that are on different domains (or the same one) [u]without [b][/b][/u]using any delegation or linked servers. I will use Srvr1 and Srvr2 as names.Srvr1 should only grant read only access to a table in a dBSrvr2 should be able to query the dB and table from Srvr1 I do have a VPN tunnel so the data would be secure between the 2 servers on different domains.The servers on the same domain have different service accounts.I have tried only on the same domain so far. The other domain server would be at a separate company.I did grant the Srvr2 service account datareader access to Srvr1 dB and it did not work.In configuration manager I changed the IP1 TCP\IP "IP Addresses" Active=Yes and Enabled=Yes and this didn't help.Any help or ideas would be very welcome. At this point I'm not sure what could work.

Transaction Control in SSIS

Posted: 28 Aug 2013 06:19 PM PDT

Hi Team Require your help on TRANSACTION CONTROL IN SSIS , We have created package with 12 dataflow task in one sequence container based on the sequence. All the dataflow task has SSIS framework implemented , when ever a record does not match certain condition that particular record moves into the error/reject log table but package does not fail it moves to the next record , However my requirnment is if any record move to error/reject log table than that particular record to be rolled back & cannot commit and then pick another record.Package will not fail but rollback has to be implemented for all the records that get captured in error/reject log table Thanks & RegardsRaga

sql schema compare

Posted: 28 Aug 2013 05:32 PM PDT

hi all, yesterday i installed sql server 2012 in my system, but sql schema compare is missing in my data tools(BIDS).is there anything i need to check while installing.thanks sathiyan

Transactional replication - can't create publication

Posted: 28 Aug 2013 11:41 AM PDT

Hi all,I have a newly created Windows 2008R2 (64bit) server with MSSQL 2012 standard edition installed, acting as it's own distributor.I have just gone to create the first publication on this particular server and I am getting an error with the snapshot agent. [i][b]"A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_scriptdropinsreconciliationproc_sqlclr":"[/b][/i] I can be pretty sure it has nothing to do with the subscription, because I didn't even get to that stage.I have done some browsing and all that I can find is something about a known problem after applying the .Net [url=http://omgili.com/thread/7aBdisT0NOqjddVTi0zn3Vy2rCMjaYjk5lgY8Hb3JQThXIKFA5Cc5QkZiUkshOK7XgFTdUbLOtr6VPE_LNekmIo76TvDWjGfzVYoYPneDviJNrOgoO9sJe7pI.CmjMYY3vsZLntwxTJ0adO6v52kb0II.RsR1FguPYLdxXms9vVqKq7RlX2zkccUqcETR5IZUhnsr1g6yRf_C5jcU9O8Arp0mfj.M2k./]update KB2840628[/url]... the only problem is my server has not had the update applied.Any help would be greatly appreciated.David
Posted by at No comments:

[T-SQL] Curious thought on how to approach making a query like this "better'

[T-SQL] Curious thought on how to approach making a query like this "better'

Curious thought on how to approach making a query like this "better'

Posted: 28 Aug 2013 03:01 PM PDT

Troubleshooting queries like this are always fun, this definitely isn't one of the worst I've seen, but its a popular one where the report writers liek to copy and paste this code into other procedures as a basis for others. The temp and parameter tables used, often have hundreds of thousands of rows in them...[b]I don't supposed there's a way to throw an error across the entire server if a temp table exceeds a certain number of rows in there?[/b]Anyone else feel the pain or am I just whining?[code="sql"] SELECT b.PrimaryAccountNumber, b.trantime, b.posttime, ISNULL(b.AuthDateTime,b.posttime), RTRIM(b.storename), ISNULL(CONVERT(decimal(30, 4), c.quantity), 0) AS [Qty], 0, ROUND(CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount] ELSE c.originalamount_ END / (CASE WHEN CONVERT(decimal(30, 4), c.quantity) = 0 THEN 1 ELSE CONVERT(decimal(30, 4), c.quantity) END), 3), CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount] ELSE c.originalamount_ END, 0, b.ProgramName, ISNULL(b.PORefNumber, br.PORefNumber), ISNULL(a.OdometerReading, '0'), RTRIM(ISNULL(b.storecity, br.storecity)), RTRIM(ISNULL(b.storestate, br.storestate)), c.unitofcost, c.RepricedTranAmount, (CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount] ELSE c.originalamount_ END - c.RepricedTranAmount) AS Discount, ROUND((CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN c.FedTaxAmount ELSE 0 END) + (CASE WHEN c.StateFuelTaxExemptStatus = 'Y' THEN c.StateTaxAmount ELSE 0 END) + (CASE WHEN c.CountyFuelTaxExemptStatus = 'Y' THEN c.CountyTaxAmount ELSE 0 END) + (CASE WHEN c.CityFuelTaxExemptStatus = 'Y' THEN c.CityTaxAmount ELSE 0 END) + (CASE WHEN c.LocalSalesTaxExemptStatus = 'Y' THEN c.LocalTax ELSE 0 END) + (CASE WHEN c.Misc1ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount1 ELSE 0 END) + (CASE WHEN c.Misc2ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount2 ELSE 0 END) + (CASE WHEN c.StateSalesTaxExemptStatus = 'Y' THEN c.StateSalesTaxAmount ELSE 0 END) + (CASE WHEN c.CountySalesTaxExemptStatus = 'Y' THEN c.CountySalesTaxAmount ELSE 0 END) + (CASE WHEN c.CitySalesTaxExemptStatus = 'Y' THEN c.CitySalesTaxAmount ELSE 0 END) + (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y' THEN c.MiscSalesTaxAmount ELSE 0 END) + (CASE WHEN c.FreightExemptStatus = 'Y' THEN c.Freight ELSE 0 END), 2) Exemptions, ISNULL(ROUND(RepricedTranAmount - ((CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN c.FedTaxAmount ELSE 0 END) + (CASE WHEN c.StateFuelTaxExemptStatus = 'Y' THEN c.StateTaxAmount ELSE 0 END) + (CASE WHEN c.CountyFuelTaxExemptStatus = 'Y' THEN c.CountyTaxAmount ELSE 0 END) + (CASE WHEN c.CityFuelTaxExemptStatus = 'Y' THEN c.CityTaxAmount ELSE 0 END) + (CASE WHEN c.LocalSalesTaxExemptStatus = 'Y' THEN c.LocalTax ELSE 0 END) + (CASE WHEN c.Misc1ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount1 ELSE 0 END) + (CASE WHEN c.Misc2ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount2 ELSE 0 END) + (CASE WHEN c.StateSalesTaxExemptStatus = 'Y' THEN c.StateSalesTaxAmount ELSE 0 END) + (CASE WHEN c.CountySalesTaxExemptStatus = 'Y' THEN c.CountySalesTaxAmount ELSE 0 END) + (CASE WHEN c.CitySalesTaxExemptStatus = 'Y' THEN c.CitySalesTaxAmount ELSE 0 END) + (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y' THEN c.MiscSalesTaxAmount ELSE 0 END) + (CASE WHEN c.FreightExemptStatus = 'Y' THEN c.Freight ELSE 0 END)), 2), b.transactionamount) AmountDue, 0, (CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN (c.FedTaxAmount * -1) ELSE 0 END) AS fedtax, (CASE WHEN c.StateFuelTaxExemptStatus = 'Y' THEN (c.StateTaxAmount * -1) ELSE 0 END) AS StateTax, (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y' THEN (c.MiscSalesTaxAmount * -1) ELSE 0 END) AS MiscSalesTax, (CASE WHEN c.FreightExemptStatus = 'Y' THEN (c.Freight * -1) ELSE 0 END) AS freight, c.[productcode1], k.LutDescription, '', a.ApprovalCode, b.[txncode_internal], b.[tranid], ISNULL(CASE WHEN c.originalamount_ = 0.00 THEN c.[TransactionAmount] ELSE c.originalamount_ END, b.transactionamount), b.cmttrantype, a.[taxexemptTotalBalance], ISNULL(a.FleetCardIdDriverNo, ar.FleetCardIdDriverNo), a.[ResponseCode], a.EDTransactionFlag, a.PermitState, a.PermitNumber, b.FleetNumber, a.TrailerNumber, b.DriverLicenseNumber, b.TripNumber, RTRIM(a.DealerName), ISNULL(RTRIM(a.DealerAddressLine1), ''), ISNULL(a.txnflag, 'M'), CASE WHEN a.EDTXnFlag = '1' THEN 'E' WHEN a.tranid IN (SELECT tranid FROM #PaymentTranIDs WHERE TranID IS NOT NULL) THEN 'P' --526171 ELSE ISNULL(a.txnflag, 'M') END, CASE WHEN a.tranid IN (SELECT tranid FROM #PaymentTranIDs WHERE TranID IS NOT NULL) THEN (SELECT DISTINCT yy.[LutDescription] FROM THISDATABASE.dbo.CCardLookUp AS yy WITH (READUNCOMMITTED) INNER JOIN THISDATABASE.dbo.Payments AS pp WITH (READUNCOMMITTED) ON pp.pmtpaymenttype = yy.lutcode AND lutid = 'pmtpaymenttype' WHERE pp.tranid = a.tranid) ELSE b.TransactionDescription END TransactionDescription, CASE WHEN a.txnflag IN ('0', '5', '3') THEN 'Card Transactions' WHEN a.txnflag = '1' THEN 'Tire Programs' WHEN a.txnflag = '2' THEN 'Plus Chek Transactions' WHEN a.txnflag = '4' THEN 'Permit Transaction' WHEN a.EDTxnFlag = '1' THEN 'ED' WHEN a.tranid IN (SELECT tranid FROM #PaymentTranIDs WHERE TranID IS NOT NULL) THEN (SELECT DISTINCT yy.[LutDescription] FROM THISDATABASE.dbo.CCardLookUp AS yy WITH (READUNCOMMITTED) INNER JOIN THISDATABASE.dbo.Payments AS pp WITH (READUNCOMMITTED) ON pp.pmtpaymenttype = yy.lutcode AND lutid = 'pmtpaymenttype' WHERE pp.tranid = a.tranid) ELSE 'Miscellaneous Activity' END TxnFlagDesc, b.RevTgt, b.tranref, --FIL.invoicenumber, --b.InvoiceNumber, CONVERT(BIGINT,LTRIM(RTRIM(b.InvoiceNumber))), p.lutdescription, c.producttype, ISNULL(b.TransactionDescription, d.lutdescription), ISNULL(e.Parent01AID, e.acctid), CASE WHEN cl.LutDescription LIKE 'Monthly%' THEN 'Monthly' WHEN cl.LutDescription IN ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday') THEN 'Weekly' WHEN cl.LutDescription LIKE '%Daily%' THEN 'Daily' WHEN cl.LutDescription LIKE '%Bi-Weekly%' THEN 'Bi-Weekly' WHEN cl.LutDescription LIKE '%Annually%' THEN 'Annually' WHEN cl.LutDescription LIKE '%Quarterly%' THEN 'Quarterly' WHEN cl.LutDescription LIKE '%Semi-Monthly%' THEN 'Semi-Monthly' WHEN cl.LutDescription LIKE '%Semi-Annually%' THEN 'Semi-Annually' WHEN cl.LutDescription LIKE '%45 Day Cycle%' THEN '45 Day Cycle' ELSE 'Other' END BillingCycle, u.CheckNumber, u.CheckAmount, Pt.CreditType, CASE WHEN b.cmttrantype LIKE '%[a-z]%' THEN '' WHEN ISNUMERIC(b.cmttrantype) = 1 AND (((CONVERT(int, b.cmttrantype) % 2) = 1 OR b.cmttrantype IN ('110', '116', '118')) AND b.cmttrantype NOT IN ('111', '115', '117')) THEN -1 WHEN ISNUMERIC(b.cmttrantype) = 1 AND (((CONVERT(int, b.cmttrantype) % 2) = 0 OR b.cmttrantype IN ('111', '115', '117')) AND b.cmttrantype NOT IN ('110', '116', '118')) THEN 1 END, COALESCE(a.DriverName, f1.DriverFirstName, f.DriverFirstName, f1r.DriverFirstName, fr.DriverFirstName) AS FirstName, COALESCE(a.DriverMiddleName, f1.DriverMiddleInitial, f.DriverMiddleInitial, f1r.DriverMiddleInitial, fr.DriverMiddleInitial) AS MiddleInitial, COALESCE(a.DriverSurName, f1.DriverSurname, f.DriverSurname, f1r.DriverSurname, fr.DriverSurname) AS Lastname, ISNULL(a.FleetCardVehicleNo,ISNULL(un.UnitNumber, '')), SUBSTRING(b.PrimaryAccountNumber, 15, 4), @acctid, e.atid, NULL AS invoicenumber, --z.invoicenumber, NULL AS invoicedates, --z.invoicedates, NULL AS invoicedate,--z.invoicedate, NULL AS statementid,--z.statementid, FAI.FleetName, FAI.FleetAddress, FAI.FleetAddress2, FAI.FleetContact, FAI.FleetFax, FII.InstitutionID, FII.InstAddress1, FII.InstAddress2, FII.InstPhone, @imagepath, ISNULL(n.ContactNameDBA, ISNULL(n1.ContactNameDBA, '')), ISNULL(mm.[Gallons_YTD], 0.00), ISNULL(mm.MPG_YTD, 0.00), ISNULL(mm.[Amount_YTD], 0.00), ISNULL(mm.CPM_YTD, 0.00), (ISNULL(mm.[Exemptions_YTD], 0.00) * -1), ISNULL(mm.MPG_YTD, 0.00), (ISNULL(mm.[Disc_YTD], 0.00) * -1), ISNULL(mm.CPM_YTD, 0.00), ISNULL(mm.Gross_YTD, 0.00), 0 [mPlusCheck], 0 [mPermit], 0 [mTire], 0 [mMisc], 0 [mEd], 0 [mDirect], 0 [mPayment], 0 [mSummary], 0 [mAccountTotals], 0 [mPromo], CAST(0.00 AS money), CAST(0.00 AS money), CAST(0.00 AS money), CAST(0.00 AS money), c.BillType, CAST('' AS varchar(15)), '', '', 0, '', COALESCE(ml.prevodometer, 0), COALESCE(ml.miles, 0), COALESCE(mm.[Miles_YTD], 0), 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00 FROM THISDATABASE.dbo.ccard_primary AS b WITH (READUNCOMMITTED) INNER JOIN THISDATABASE.dbo.ccard_secondary AS a WITH (READUNCOMMITTED) ON a.tranid = b.tranid INNER JOIN @RD_Fleet_inv_list AS z ON z.tranid = b.tranid LEFT JOIN THISDATABASE.dbo.CCardLineItems AS c WITH (READUNCOMMITTED) ON c.UniqueId = a.TranId LEFT JOIN THISDATABASE.dbo.CCARDLOOKUP AS k WITH (READUNCOMMITTED) ON k.LutCode = c.ProductCode1 AND k.LutId = 'ProductCode' LEFT JOIN THATDATABASE.dbo.CoreLibLUT AS p WITH (READUNCOMMITTED) ON p.LutCode = CONVERT(char(30), b.ProgramName) AND p.LutId = 'ProgramMgt' LEFT JOIN THISDATABASE.[DBO].TrancodeLookup AS d WITH (READUNCOMMITTED) ON d.Lutcode = b.Txncode_Internal AND d.lutid = 'TranCode' LEFT JOIN THISDATABASE.[DBO].bsegment_primary AS e WITH (READUNCOMMITTED) ON e.accountnumber = b.AccountNumber AND e.FleetNumber = b.FleetNumber LEFT OUTER JOIN THISDATABASE.[DBO].ccardlookup AS cl WITH (READUNCOMMITTED) ON cl.LutCode = e.BillingCycle AND cl.LUTid = 'BillingCycle' LEFT JOIN ANOTHERDATABASE.DBO.usedpluscheks AS u WITH (READUNCOMMITTED) ON CONVERT(varchar(24), U.TranID) = b.uniqueid LEFT JOIN THISDATABASE.[DBO].Payments AS Pt WITH (READUNCOMMITTED) ON CONVERT(varchar(24), Pt.TranId) = b.uniqueid LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS f WITH (READUNCOMMITTED) ON (f.accountlevel = '0' AND a.FleetCardIdDriverNo = f.drivernumber AND @AcctID = f.CompanyAcctID) AND ((f.CardNumber IS NOT NULL AND b.primaryaccountnumber = f.CardNumber) OR (f.CardNumber IS NULL)) AND (b.[posttime] BETWEEN ISNULL(f.activedate, '1900-01-01') AND ISNULL(f.inactivedate, '2900-12-31')) LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS f1 WITH (READUNCOMMITTED) ON (a.FleetCardIdDriverNo = f1.drivernumber AND b.primaryaccountnumber = f1.cardnumber AND f1.accountlevel = '1' AND (b.[posttime] BETWEEN ISNULL(f1.activedate, '1900-01-01') AND ISNULL(f1.inactivedate, '2900-12-31'))) LEFT JOIN ANOTHERDATABASE.dbo.UnitNumbers AS un WITH (READUNCOMMITTED) ON b.primaryaccountnumber = un.CardNumber AND b.posttime >= un.ActiveDate AND b.posttime < ISNULL(un.InactiveDate, GETDATE()) LEFT JOIN @RD_Fleet_Acct_Info AS FAI ON FAI.FleetNumber = b.FleetNumber LEFT JOIN @RD_Fleet_Inst_Info AS FII ON FII.InstitutionID = FAI.InstitutionID LEFT JOIN @RD_Fleet_inv_list AS FIL ON FIL.FleetNumber = b.FleetNumber LEFT JOIN THISDATABASE.[DBO].nameaddraccounts AS n WITH (READUNCOMMITTED) ON CASE WHEN e.parent01aid IS NULL THEN e.AcctID ELSE e.parent01aid END = n.parent02aid AND n.AddressType = '0' LEFT JOIN THISDATABASE.[DBO].nameaddraccounts AS n1 WITH (READUNCOMMITTED) ON n1.Acctid = N.parent01aid AND n1.AddressType = '0' LEFT JOIN @Mileage_YTD AS mm ON mm.pan = b.primaryaccountnumber AND b.trantime = mm.trandate LEFT JOIN @Miles AS ml ON ml.pan = b.primaryaccountnumber AND ml.tranid = CONVERT(varchar(20), b.tranid) AND ml.trantime = b.trantime LEFT JOIN THISDATABASE.[DBO].SpecificStatementMessages AS st WITH (READUNCOMMITTED) ON st.AccountID = FAI.CompanyAcctID LEFT JOIN THISDATABASE.dbo.ccard_primary AS br WITH (READUNCOMMITTED) ON br.tranid = b.RevTgt LEFT JOIN THISDATABASE.dbo.ccard_secondary AS ar WITH (READUNCOMMITTED) ON ar.TranId = br.TranId LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS fr WITH (READUNCOMMITTED) ON (fr.accountlevel = '0' AND a.FleetCardIdDriverNo = fr.drivernumber AND @AcctID = fr.CompanyAcctID) AND ((fr.CardNumber IS NOT NULL AND b.primaryaccountnumber = fr.CardNumber) OR (fr.CardNumber IS NULL)) AND (br.[posttime] BETWEEN ISNULL(fr.activedate, '1900-01-01') AND ISNULL(fr.inactivedate, '2900-12-31')) LEFT JOIN ANOTHERDATABASE.dbo.DriverNumbers AS f1r WITH (READUNCOMMITTED) ON (ar.FleetCardIdDriverNo = f1r.drivernumber AND br.primaryaccountnumber = f1r.cardnumber AND f1r.accountlevel = '1' AND (br.[posttime] BETWEEN ISNULL(f1r.activedate, '1900-01-01') AND ISNULL(f1r.inactivedate, '2900-12-31'))) WHERE b.cmttrantype NOT IN ('Nch', 'QNA', '*SCR', '110', '113', '121', '122', '88', '89', '127','114','117','26','24') AND (NOT(LTRIM(RTRIM(ISNULL(b.Transactionidentifier,'0'))) = '1' and b.cmttrantype in ('49','43'))) AND ISNULL(ROUND(RepricedTranAmount - ((CASE WHEN c.FedExciseTaxExemptStatus = 'Y' THEN c.FedTaxAmount ELSE 0 END) + (CASE WHEN c.StateFuelTaxExemptStatus = 'Y' THEN c.StateTaxAmount ELSE 0 END) + (CASE WHEN c.CountyFuelTaxExemptStatus = 'Y' THEN c.CountyTaxAmount ELSE 0 END) + (CASE WHEN c.CityFuelTaxExemptStatus = 'Y' THEN c.CityTaxAmount ELSE 0 END) + (CASE WHEN c.LocalSalesTaxExemptStatus = 'Y' THEN c.LocalTax ELSE 0 END) + (CASE WHEN c.Misc1ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount1 ELSE 0 END) + (CASE WHEN c.Misc2ExciseTaxExemptStatus = 'Y' THEN c.MiscTaxAmount2 ELSE 0 END) + (CASE WHEN c.StateSalesTaxExemptStatus = 'Y' THEN c.StateSalesTaxAmount ELSE 0 END) + (CASE WHEN c.CountySalesTaxExemptStatus = 'Y' THEN c.CountySalesTaxAmount ELSE 0 END) + (CASE WHEN c.CitySalesTaxExemptStatus = 'Y' THEN c.CitySalesTaxAmount ELSE 0 END) + (CASE WHEN c.MiscSalesTaxExemptStatus = 'Y' THEN c.MiscSalesTaxAmount ELSE 0 END) + (CASE WHEN c.FreightExemptStatus = 'Y' THEN c.Freight ELSE 0 END)), 2), b.transactionamount) <> 0.00 GROUP BY c.tranid, b.PrimaryAccountNumber, b.trantime, b.posttime, ISNULL(b.AuthDateTime,b.posttime), a.taxexemptTotalBalance, c.Transactionamount, a.originalamount_, c.RepricedTranAmount, c.StateTaxAmount, c.FedTaxAmount, c.Freight, c.originalAmount_, b.cmttrantype, a.tranid, b.uniqueid, a.OdometerReading, b.storename, c.quantity, b.ProgramName, b.PORefNumber, b.storecity, b.storestate, c.unitofcost, c.FedExciseTaxExemptStatus, c.StateFuelTaxExemptStatus, c.CountyFuelTaxExemptStatus, c.CityFuelTaxExemptStatus, c.LocalSalesTaxExemptStatus, c.Misc1ExciseTaxExemptStatus, c.Misc2ExciseTaxExemptStatus, c.StateSalesTaxExemptStatus, c.CountySalesTaxExemptStatus, c.CitySalesTaxExemptStatus, c.MiscSalesTaxExemptStatus, c.FreightExemptStatus, c.CountyTaxAmount, c.CityTaxAmount, c.LocalTax, c.MiscTaxAmount1, c.MiscTaxAmount2, c.StateSalesTaxAmount, c.CountySalesTaxAmount, c.CitySalesTaxAmount, c.MiscSalesTaxAmount, c.Freight, c.[productcode1], a.[ApprovalCode], b.[txncode_internal], b.[tranid], b.cmttrantype, a.FleetCardIdDriverNo, a.[ResponseCode], a.EDTransactionFlag, a.PermitState, a.PermitNumber, b.FleetNumber, a.TrailerNumber, b.DriverLicenseNumber, b.TripNumber, a.DealerName, a.DealerAddressLine1, k.LutDescription, a.EDTXnFlag, a.[TxnFlag], b.TransactionDescription, b.RevTgt, b.tranref, --FIL.invoicenumber, --b.InvoiceNumber, CONVERT(BIGINT,LTRIM(RTRIM(b.InvoiceNumber))), p.lutdescription, c.[ProductType], d.lutdescription, e.Parent01AID, e.acctid, cl.LutDescription, u.CheckNumber, u.CheckAmount, Pt.CreditType, f1.DriverFirstName, f.DriverFirstName, f1.DriverMiddleInitial, f.DriverMiddleInitial, f1.DriverSurname, f.DriverSurname, a.FleetCardVehicleNo, un.unitnumber, e.atid, --z.invoicenumber, --z.invoicedates, --z.invoicedate, --z.statementid, FAI.FleetName, FAI.FleetAddress, FAI.FleetAddress2, FAI.FleetContact, FAI.FleetFax, FII.InstitutionID, FII.InstAddress1, FII.InstAddress2, FII.InstPhone, n.ContactNameDBA, n1.ContactNameDBA, mm.mpg_YTD, mm.cpm_YTD, c.BillType, b.transactionamount, ml.prevodometer, ml.miles, mm.[Gallons_YTD], mm.[Amount_YTD], mm.[Exemptions_YTD], mm.[Disc_YTD], mm.[Gross_YTD], mm.[Miles_YTD], br.storecity, br.storestate, a.DriverName, a.DriverMiddleName, a.DriverSurName, f1r.DriverFirstName, fr.DriverFirstName, f1r.DriverMiddleInitial, fr.DriverMiddleInitial, f1r.DriverSurname, fr.DriverSurname, br.PORefNumber, ar.FleetCardIdDriverNo [/code]

Query performance

Posted: 29 Aug 2013 12:52 AM PDT

I have the following SQL query which is executed in a dynamic SQL statement:[code="sql"]SELECT a.id, b.id,CRS.* FROM QBMerge.fo_manchester_keys_ AS aINNER JOIN QBMerge.fo_manchester_keys_ AS b ON a.MatchKeyType13 = b.MatchKeyType13 AND a.RAND_ID > b.RAND_ID AND NOT EXISTS (SELECT 1 FROM ##fo_manchester_LargeClusters_ as d WHERE d.MatchKey = a.MatchKeyType13)CROSS APPLY dbo.CompleteRecordScoring( a.NormalisedForename, a.NormalisedMiddleName, a.NormalisedSurname, a.PhoneticForename, a.PhoneticMiddleName, a.PhoneticSurname, a.NormalisedOrganisationName, a.NormalisedOrganisationName2, a.Premise, a.AddressKey, a.POstOut, a.PostIn, a.HomeTelephoneNumber, a.MobileTelephoneNumber, a.EmailAddress, b.NormalisedForename, b.NormalisedMiddleName, b.NormalisedSurname, b.PhoneticForename, b.PhoneticMiddleName, b.PhoneticSurname, b.NormalisedOrganisationName, b.NormalisedOrganisationName2, b.Premise, b.AddressKey, b.PostOut, b.POstIn, b.HomeTelephoneNumber, b.MobileTelephoneNumber, b.EmailAddress, 1) AS CRS[/code]If I run the query without the CROSS APPLY, it takes a few seconds, however when I include the CLR TVF it takes minutes to run.The weird thing is, it's not my CLR TVF that's taking long, I have used this code before and it can process hundreds of thousands of rows in a few seconds. I have also added a start end time for each row returned by the CLR TVF and the times are very low (hundredths of milliseconds if that).The table :[img]http://i.imgur.com/dmogfM2.jpg[/img]The INDEX on MatchKeyType13:[code="sql"]CREATE NONCLUSTERED INDEX [IX_MatchKeyType13] ON [QBMerge].[fo_manchester_keys_]( [MatchKeyType13] ASC, [Hierarchy] ASC, [RAND_ID] ASC)INCLUDE ( [ID], [NormalisedSurname], [NormalisedForename], [NormalisedMiddleName], [PhoneticSurname], [PhoneticForename], [PhoneticMiddleName], [NormalisedOrganisationName], [NormalisedOrganisationName2], [Premise], [AddressKey], [PostOut], [PostIn], [HomeTelephoneNumber], [MobileTelephoneNumber], [EmailAddress]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO[/code]Another interesting observation when I run the same code on other MatchKeyTypes, I notice the CPU utilisation is maxed out and the query can generate more than a million rows per minute, however, on some match keys the CPU utilisation is more erratic and that's when it takes minutes to do a few hundred thousand records!

Drop Constraints, Truncate and Re-Create Constraints

Posted: 28 Aug 2013 10:58 PM PDT

Hi, I am working on a SP that for a given database;1. Drops the referential integrity constraints.2. Truncates the tables.3. Re-creates the integrity constraints.I started testing the code and the code seems to drop the constraints well but it kind of bugs while Truncating the tables. I am using INFORMATION_SCHEMA to generate the temp table of table's constraint list, the dynamic code generation for Truncation seems to fail.I desperately need to fix this on priority. Here is the code:-----------------------------------------------------------------------------------CREATE PROCEDURE usp_TruncateTableData_ConstraintDropAddASBEGIN SET NOCOUNT ON; DECLARE @FK_TableSchema NVARCHAR(200), @FK_TableName NVARCHAR(200), @FK_Name NVARCHAR(200), @FK_ColumnName NVARCHAR(200), @PK_TableSchema NVARCHAR(200), @PK_TableName NVARCHAR(200), @PK_ColumnName NVARCHAR(200), @DROP VARCHAR(MAX), @ADD VARCHAR(MAX), @TRUNC VARCHAR(MAX) DECLARE @table TABLE( RowId INT PRIMARY KEY IDENTITY(1, 1), ForeignKeyConstraintName NVARCHAR(200), ForeignKeyConstraintTableSchema NVARCHAR(200), ForeignKeyConstraintTableName NVARCHAR(200), ForeignKeyConstraintColumnName NVARCHAR(200), PrimaryKeyConstraintName NVARCHAR(200), PrimaryKeyConstraintTableSchema NVARCHAR(200), PrimaryKeyConstraintTableName NVARCHAR(200), PrimaryKeyConstraintColumnName NVARCHAR(200) ) INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName) SELECT U.CONSTRAINT_NAME, U.TABLE_SCHEMA, U.TABLE_NAME, U.COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY' UPDATE @table SET PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintTableSchema = TABLE_SCHEMA, PrimaryKeyConstraintTableName = TABLE_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME UPDATE @table SET PrimaryKeyConstraintColumnName = COLUMN_NAME FROM @table T INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME --SELECT * FROM @table --------------------------------------------------------------------------------------------------------- --DROP CONSTRAINT: DECLARE FK_DROP CURSOR FOR SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintName FROM @table OPEN FK_DROP FETCH NEXT FROM FK_DROP INTO @FK_TableSchema, @FK_TableName, @FK_Name WHILE @@FETCH_STATUS = 0 BEGIN SET @DROP = 'ALTER TABLE [' + @FK_TableSchema + '].[' + @FK_TableName + '] DROP CONSTRAINT ' + @FK_Name EXECUTE(@DROP) --PRINT @DROP FETCH NEXT FROM FK_DROP INTO @FK_TableSchema, @FK_TableName, @FK_Name END CLOSE FK_DROP DEALLOCATE FK_DROP --------------------------------------------------------------------------------------------------------- --TRUNCATE TABLES: DECLARE FK_TRUNC CURSOR FOR SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName FROM @table OPEN FK_TRUNC FETCH NEXT FROM FK_TRUNC INTO @FK_TableSchema, @FK_TableName WHILE @@FETCH_STATUS = 0 BEGIN SET @TRUNC = 'TRUNCATE TABLE [' + @FK_TableName + ']' EXECUTE(@TRUNC) --PRINT @TRUNC FETCH NEXT FROM FK_TRUNC INTO @FK_TableSchema, @FK_TableName END CLOSE FK_TRUNC DEALLOCATE FK_TRUNC --------------------------------------------------------------------------------------------------------- --ADD CONSTRAINT: DECLARE FK_ADD CURSOR FOR SELECT ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintName, ForeignKeyConstraintColumnName, PrimaryKeyConstraintTableSchema, PrimaryKeyConstraintTableName, PrimaryKeyConstraintColumnName FROM @table OPEN FK_ADD FETCH NEXT FROM FK_ADD INTO @FK_TableSchema, @FK_TableName, @FK_Name, @FK_ColumnName, @PK_TableSchema, @PK_TableName, @PK_ColumnName WHILE @@FETCH_STATUS = 0 BEGIN SET @ADD = 'ALTER TABLE [' + @FK_TableSchema + '].[' + @FK_TableName + '] ADD CONSTRAINT ' + @FK_Name + ' FOREIGN KEY(' + @FK_ColumnName + ') REFERENCES [' + @PK_TableSchema + '].[' + @PK_TableName + '](' + @PK_ColumnName + ')' EXECUTE(@ADD) --PRINT @ADD FETCH NEXT FROM FK_ADD INTO @FK_TableSchema, @FK_TableName, @FK_Name, @FK_ColumnName, @PK_TableSchema, @PK_TableName, @PK_ColumnName END CLOSE FK_ADD DEALLOCATE FK_ADD --------------------------------------------------------------------------------------------------------- ENDGO-----------------------------------------------------------------------------------

exists() not working in INSTEAD OF INSERT trigger

Posted: 28 Aug 2013 01:28 AM PDT

I do not understand why the exists() tests are not working. I'm using SQL Server 2008 R2.Here's the code:[code="plain"]create trigger dbo.io_trigger_Insert_Alert on dbo.ALERT instead of insertasbegin set nocount on; -- Row exists if exists (select * from dbo.ALERT a, inserted i where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType) begin -- Move record to history insert into dbo.ALERT_STATUS_HISTORY ( OBJECTID, StopID, RouteID, ServiceCenterID, AlertType, Description, TimeRaised, AlertStatus, HandledBy, HandledDateTime, AlertID) select OBJECTID, StopID, RouteID, ServiceCenterID, AlertType, Description, TimeRaised, AlertStatus, HandledBy, HandledDateTime, AlertID from dbo.ALERT; -- Update AlertStatus if exists (select * from dbo.ALERT a, inserted i where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType and a.AlertStatus = N'X') begin update dbo.ALERT set AlertStatus = N'N' from dbo.ALERT a, inserted i where a.StopID = i.StopID and a.RouteID = i.RouteID and a.AlertType = i.AlertType; end endend[/code]

How to reordering or removing one identity number

Posted: 28 Aug 2013 05:21 PM PDT

Hi,I have set the identity columns to -1,1 but I want remove the number zero from the identity sequence example below.Currently --Identity column -1[b]0[/b]1234And so on.What I want is ---1[b]1[/b]2345And so on.Basically removing the the first zero. Is it possible?I don't mind if I have to truncate the table.Thanks

HElp with a query!!

Posted: 28 Aug 2013 08:28 AM PDT

Hi all i have this data:[code="sql"]DECLARE @data table (id int,name varchar(max),parent_id int,node_type_id int,node_order INT)INSERT @data SELECT 2,'ClarkDietrich',NULL,1,1 UNION ALLSELECT 293,'CLARK_3.29_Testing_Plan',2,5,0 UNION ALLSELECT 294,'3.29',2,6,2 UNION ALLSELECT 295,'Add the Total # of Coils to the Setup Report',294,7,0 UNION ALLSELECT 296,NULL,295,8,0 UNION ALLSELECT 297,'Pull From Stock ? Gauge Code',294,7,1 UNION ALLSELECT 298,NULL,297,8,0 UNION ALLSELECT 299,'Processing Coil Order Summary Report Additional - ENH',294,7,2 UNION ALLSELECT 300,NULL,299,8,0 UNION ALLSELECT 301,'Add Machine validation in Mill Assignment Edit screen',294,7,3 UNION ALLSELECT 302,NULL,301,8,0 UNION ALLSELECT 308,'TS_SYSTEM ADMINISTRATION',311,2,0 UNION ALLSELECT 309,'TS_SHOP ORDER',311,2,7 UNION ALLSELECT 310,'TS_3.29.0.0',2,2,3 UNION ALLSELECT 311,'TS_MASTER',2,2,0 UNION ALLSELECT 312,'FR60_TC_Machine Maintenance',308,3,100 UNION ALLSELECT 313,NULL,312,4,0 UNION ALLSELECT 314,NULL,313,9,0 UNION ALLSELECT 315,NULL,313,9,0 UNION ALLSELECT 316,NULL,313,9,0 UNION ALLSELECT 317,'FR60_TC_Machine Maintenance',325,3,0 UNION ALLSELECT 318,NULL,317,4,0 UNION ALLSELECT 319,NULL,318,9,0 UNION ALLSELECT 320,NULL,318,9,0 UNION ALLSELECT 321,NULL,318,9,0 UNION ALLSELECT 322,NULL,318,9,0 UNION ALLSELECT 323,NULL,318,9,0 UNION ALLSELECT 325,'TS_IM5972',310,2,1 UNION ALLSELECT 326,'TS_SALES_ORDER',311,2,1 UNION ALLSELECT 327,'FR60_TC_Mill Assignment Edit',309,3,1 UNION ALLSELECT 328,NULL,327,4,0 UNION ALLSELECT 329,'TS_RFDEVICES',311,2,2 UNION ALLSELECT 330,NULL,328,9,0 UNION ALLSELECT 331,NULL,328,9,0 UNION ALLSELECT 332,'TC_RF_Pull_From_Stock',17853,3,0 UNION ALLSELECT 333,NULL,332,4,0 UNION ALLSELECT 334,NULL,328,9,0 UNION ALLSELECT 335,NULL,328,9,0 UNION ALLSELECT 336,NULL,328,9,0 UNION ALLSELECT 337,NULL,333,9,0 UNION ALLSELECT 338,NULL,328,9,0 UNION ALLSELECT 339,'FR60_TC_Mill Assignment Edit',325,3,102 UNION ALLSELECT 340,NULL,339,4,0 UNION ALLSELECT 341,NULL,340,9,0 UNION ALLSELECT 342,NULL,340,9,0 UNION ALLSELECT 343,NULL,340,9,0 UNION ALLSELECT 344,NULL,340,9,0 UNION ALLSELECT 345,NULL,340,9,0 UNION ALLSELECT 346,NULL,340,9,0 UNION ALLSELECT 347,NULL,333,9,0 UNION ALLSELECT 349,'TS_IM5995',310,2,2 UNION ALLSELECT 354,'FR60_TC_Validating Other Modules',325,3,103 UNION ALLSELECT 355,NULL,354,4,0 UNION ALLSELECT 356,NULL,355,9,0 UNION ALLSELECT 357,NULL,355,9,0 UNION ALLSELECT 358,NULL,355,9,0 UNION ALLSELECT 359,NULL,355,9,0 UNION ALLSELECT 360,NULL,355,9,0 UNION ALLSELECT 361,NULL,355,9,0 UNION ALLSELECT 362,NULL,355,9,0 UNION ALLSELECT 363,NULL,355,9,0 UNION ALLSELECT 364,NULL,355,9,0 UNION ALLSELECT 365,NULL,355,9,0 UNION ALLSELECT 366,NULL,355,9,0 UNION ALLSELECT 367,NULL,302,10,0 UNION ALLSELECT 368,NULL,333,9,0 UNION ALLSELECT 369,NULL,333,9,0 UNION ALLSELECT 370,NULL,333,9,0 UNION ALLSELECT 371,NULL,333,9,0 UNION ALLSELECT 383,'TC_RF_Pull_From_Stock',349,3,0 UNION ALLSELECT 384,NULL,383,4,0 UNION ALLSELECT 385,NULL,384,9,0 UNION ALLSELECT 386,NULL,384,9,0 UNION ALLSELECT 387,NULL,384,9,0 UNION ALLSELECT 388,NULL,384,9,0 UNION ALLSELECT 389,NULL,384,9,0 UNION ALLSELECT 390,NULL,384,9,0 UNION ALLSELECT 391,'TS_INVENTORY',311,2,3 UNION ALLSELECT 392,'TS_SETUP',391,2,1 UNION ALLSELECT 393,'Change the Customer information on BOL',294,7,4 UNION ALLSELECT 394,NULL,393,8,0 UNION ALLSELECT 395,'TC_SETUP_MAINTENANCE_REPORT',392,3,100 UNION ALLSELECT 396,NULL,395,4,0 UNION ALLSELECT 397,'TS_TRAFFIC',311,2,4 UNION ALLSELECT 398,NULL,396,9,0 UNION ALLSELECT 399,NULL,396,9,0 UNION ALLSELECT 400,NULL,396,9,0 UNION ALLSELECT 401,'FR60_TC_Reprint Bill of Lading',397,3,1 UNION ALLSELECT 402,NULL,401,4,0 UNION ALLSELECT 403,NULL,396,9,0 UNION ALLSELECT 405,'TS_IM5932',310,2,3 UNION ALLSELECT 406,'TC_SETUP_MAINTENANCE_REPORT',405,3,0 UNION ALLSELECT 407,NULL,406,4,0 UNION ALLSELECT 408,NULL,407,9,0 UNION ALLSELECT 409,NULL,407,9,0 UNION ALLSELECT 410,NULL,407,9,0 UNION ALLSELECT 411,NULL,407,9,0 UNION ALLSELECT 412,NULL,402,9,0 UNION ALLSELECT 414,NULL,402,9,0 UNION ALLSELECT 415,NULL,402,9,0 UNION ALLSELECT 416,NULL,402,9,0 UNION ALLSELECT 417,NULL,402,9,0 UNION ALLSELECT 418,'TS_IM6039',310,2,4 UNION ALLSELECT 419,'FR60_TC_Reprint Bill of Lading',418,3,0 UNION ALLSELECT 420,NULL,419,4,0 UNION ALLSELECT 421,NULL,420,9,0 UNION ALLSELECT 422,NULL,420,9,0 UNION ALLSELECT 423,NULL,420,9,0 UNION ALLSELECT 424,NULL,420,9,0 UNION ALLSELECT 425,NULL,420,9,0 UNION ALLSELECT 293,'CLARK_3.29_Testing_Plan',2,5,0 UNION ALLSELECT 294,'3.29',2,6,2 UNION ALLSELECT 295,'Add the Total # of Coils to the Setup Report',294,7,0 UNION ALLSELECT 296,NULL,295,8,0 UNION ALLSELECT 297,'Pull From Stock ? Gauge Code',294,7,1 UNION ALLSELECT 298,NULL,297,8,0 UNION ALLSELECT 299,'Processing Coil Order Summary Report Additional - ENH',294,7,2 UNION ALLSELECT 300,NULL,299,8,0 UNION ALLSELECT 301,'Add Machine validation in Mill Assignment Edit screen',294,7,3 UNION ALLSELECT 302,NULL,301,8,0 UNION ALLSELECT 308,'TS_SYSTEM ADMINISTRATION',311,2,0 UNION ALLSELECT 309,'TS_SHOP ORDER',311,2,7 UNION ALLSELECT 310,'TS_3.29.0.0',2,2,3 UNION ALLSELECT 311,'TS_MASTER',2,2,0 UNION ALLSELECT 312,'FR60_TC_Machine Maintenance',308,3,100 UNION ALLSELECT 313,NULL,312,4,0 UNION ALLSELECT 314,NULL,313,9,0 UNION ALLSELECT 315,NULL,313,9,0 UNION ALLSELECT 316,NULL,313,9,0 UNION ALLSELECT 317,'FR60_TC_Machine Maintenance',325,3,0 UNION ALLSELECT 318,NULL,317,4,0 UNION ALLSELECT 319,NULL,318,9,0 UNION ALLSELECT 320,NULL,318,9,0 UNION ALLSELECT 321,NULL,318,9,0 UNION ALLSELECT 322,NULL,318,9,0 UNION ALLSELECT 323,NULL,318,9,0 UNION ALLSELECT 325,'TS_IM5972',310,2,1 UNION ALLSELECT 326,'TS_SALES_ORDER',311,2,1 UNION ALLSELECT 327,'FR60_TC_Mill Assignment Edit',309,3,1 UNION ALLSELECT 328,NULL,327,4,0 UNION ALLSELECT 329,'TS_RFDEVICES',311,2,2 UNION ALLSELECT 330,NULL,328,9,0 UNION ALLSELECT 331,NULL,328,9,0 UNION ALLSELECT 332,'TC_RF_Pull_From_Stock',17853,3,0 UNION ALLSELECT 333,NULL,332,4,0 UNION ALLSELECT 334,NULL,328,9,0 UNION ALLSELECT 335,NULL,328,9,0 UNION ALLSELECT 336,NULL,328,9,0 UNION ALLSELECT 337,NULL,333,9,0 UNION ALLSELECT 338,NULL,328,9,0 UNION ALLSELECT 339,'FR60_TC_Mill Assignment Edit',325,3,102 UNION ALLSELECT 340,NULL,339,4,0 UNION ALLSELECT 341,NULL,340,9,0 UNION ALLSELECT 342,NULL,340,9,0 UNION ALLSELECT 343,NULL,340,9,0 UNION ALLSELECT 344,NULL,340,9,0 UNION ALLSELECT 345,NULL,340,9,0 UNION ALLSELECT 346,NULL,340,9,0 UNION ALLSELECT 347,NULL,333,9,0 UNION ALLSELECT 349,'TS_IM5995',310,2,2 UNION ALLSELECT 354,'FR60_TC_Validating Other Modules',325,3,103 UNION ALLSELECT 355,NULL,354,4,0 UNION ALLSELECT 356,NULL,355,9,0 UNION ALLSELECT 357,NULL,355,9,0 UNION ALLSELECT 358,NULL,355,9,0 UNION ALLSELECT 359,NULL,355,9,0 UNION ALLSELECT 360,NULL,355,9,0 UNION ALLSELECT 361,NULL,355,9,0 UNION ALLSELECT 362,NULL,355,9,0 UNION ALLSELECT 363,NULL,355,9,0 UNION ALLSELECT 364,NULL,355,9,0 UNION ALLSELECT 365,NULL,355,9,0 UNION ALLSELECT 366,NULL,355,9,0 UNION ALLSELECT 367,NULL,302,10,0 UNION ALLSELECT 368,NULL,333,9,0 UNION ALLSELECT 369,NULL,333,9,0 UNION ALLSELECT 370,NULL,333,9,0 UNION ALLSELECT 371,NULL,333,9,0 UNION ALLSELECT 383,'TC_RF_Pull_From_Stock',349,3,0 UNION ALLSELECT 384,NULL,383,4,0 UNION ALLSELECT 385,NULL,384,9,0 UNION ALLSELECT 386,NULL,384,9,0 UNION ALLSELECT 387,NULL,384,9,0 UNION ALLSELECT 388,NULL,384,9,0 UNION ALLSELECT 389,NULL,384,9,0 UNION ALLSELECT 390,NULL,384,9,0 UNION ALLSELECT 391,'TS_INVENTORY',311,2,3 UNION ALLSELECT 392,'TS_SETUP',391,2,1 UNION ALLSELECT 393,'Change the Customer information on BOL',294,7,4 UNION ALLSELECT 394,NULL,393,8,0 UNION ALLSELECT 395,'TC_SETUP_MAINTENANCE_REPORT',392,3,100 UNION ALLSELECT 396,NULL,395,4,0 UNION ALLSELECT 397,'TS_TRAFFIC',311,2,4 UNION ALLSELECT 398,NULL,396,9,0 UNION ALLSELECT 399,NULL,396,9,0 UNION ALLSELECT 400,NULL,396,9,0 UNION ALLSELECT 401,'FR60_TC_Reprint Bill of Lading',397,3,1 UNION ALLSELECT 402,NULL,401,4,0 UNION ALLSELECT 403,NULL,396,9,0 UNION ALLSELECT 405,'TS_IM5932',310,2,3 UNION ALLSELECT 406,'TC_SETUP_MAINTENANCE_REPORT',405,3,0 UNION ALLSELECT 407,NULL,406,4,0 UNION ALLSELECT 408,NULL,407,9,0 UNION ALLSELECT 409,NULL,407,9,0 UNION ALLSELECT 410,NULL,407,9,0 UNION ALLSELECT 411,NULL,407,9,0 UNION ALLSELECT 412,NULL,402,9,0 UNION ALLSELECT 414,NULL,402,9,0 UNION ALLSELECT 415,NULL,402,9,0 UNION ALLSELECT 416,NULL,402,9,0 UNION ALLSELECT 417,NULL,402,9,0 UNION ALLSELECT 418,'TS_IM6039',310,2,4 UNION ALLSELECT 419,'FR60_TC_Reprint Bill of Lading',418,3,0 UNION ALLSELECT 420,NULL,419,4,0 UNION ALLSELECT 421,NULL,420,9,0 UNION ALLSELECT 422,NULL,420,9,0 UNION ALLSELECT 423,NULL,420,9,0 UNION ALLSELECT 424,NULL,420,9,0 UNION ALLSELECT 425,NULL,420,9,0SELECT * FROM @data[/code]This table have the relationship to itself by the parent_id column, I would sort through so you can display hierarchically.Anyone can help me with this?..thanks!

help for get max column when store with xml

Posted: 28 Aug 2013 08:39 AM PDT

Hello FriendI can not speak good Englishsorryi want for store data in database of c#1-I have an xml string in c#2-I sent the following procedure3-And without any problems.4-store in table.5-My problem is:I need to get the greatest column value from tableI added it to 1 and I recorded the new recordALTER PROCEDURE [dbo].[SpTest] @Xml xmlASBEGIN DECLARE @ih INTEXEC sp_XML_preparedocument @ih output, @XmlINSERT TableNameSELECT *FROM OPENXML(@ih, 'TBL_FACTOR/RD_FACTOR')WITH TableNameEXEC sp_XML_removedocument @ih

How to change this select statement?

Posted: 28 Aug 2013 02:44 AM PDT

[code="sql"]SELECT SUM(ISNULL(ord_totalweight, 0)) AS Expr1 FROM dbo.orderheader WITH (NOLOCK) WHERE (mov_number = TripSegment. 'Move Number')) AS [Total Weight],(SELECT SUM(ISNULL(ord_totalpieces, 0)) AS Expr1 FROM dbo.orderheader WITH (NOLOCK) WHERE (mov_number = TripSegment. 'Move Number')) AS [Total Pieces], [/code]This statement here references the orderheader table to retrieve information about the mov_number's weight information. The weight information here is incomplete and the DB is not normalized - so a better source for the data has been found in a dbo.stops table.I have wrote this statement below, which references the stops table that seems to be the ultimate authority for ord_totalweight as far as I can see. It pulls...the right information at least.[code="sql"]select SUM(ord_totalweight) AS Weight, SUM(ord_totalpieces) AS Pieces from orderheader where ord_hdrnumber in (select distinct ord_hdrnumber from stops where mov_number = '213135')[/code]How can I rewrite the original statement to reference the stops table? I used mov_number 213135 as an example in my query above. because mov_number 213135 does not have its weight information stored in dbo.orderheader. 213135 does have it stored in dbo.stops though.
Posted by at No comments:

[SQL Server 2008 issues] Combining 2 queries

[SQL Server 2008 issues] Combining 2 queries

Combining 2 queries

Posted: 28 Aug 2013 08:17 AM PDT

HiI have two queries in SQL linked to excel.Report 1 showsContract A E N PIncome 5000 6000 8000 4000Costs 4000 7000 6000 3000Report2 shows Contract A B ECustomerA 500 200 400CustomerB 450 100 200Is it possible to combine them so I getContract A B E N PIncome 5000 6000 8000 4000Costs 4000 7000 6000 3000CustomerA 500 200 400CustomerB 450 100 200ThanksSent from my iPad

sql version

Posted: 28 Aug 2013 05:44 PM PDT

we installed sql 2008 r2 version in one of the new servers , insatllation went fine .later we checked the version of sql it;s showing 10.52.4000 but expected as 10.50.--. what is it mean ?

MS Word Mail Merger from SQL view over SSL VPN

Posted: 23 Aug 2013 09:35 AM PDT

Hello,I am trying to mail merge a document over an SSL VPN connection. I can open a Word doc that is on our server, but when I try to mail merge the document, it freezes, or takes about 30 seconds to search 200 records. We have 16,000 records... When I'm in the office it take 2-3 seconds to mail merge.I am using a word data source that connects to a view on our SQL server. I have tried recreating the mail merge with Schema binding so I could create a unique Clustered Index on the base table's primary key, which is also the field we use to search for our records.This they anything I can do to speed things up? I'm open to idea's...As always any help is greatly appreciated,David92595

Using OSQL in VS Command Prompt (2010)

Posted: 28 Aug 2013 10:07 AM PDT

Ok i was given this code below to run in the command prompt.[b]osql -E -S .\SAM -i C:\aspnet.sql[/b]Can i just run the aspnet.sql in the window of SSMS 2008? I don't know what that code above means but i'm having problems running it.

MS SQL 2008 Audit query- Need Server names, DBs name and login information

Posted: 28 Aug 2013 10:00 AM PDT

Hi,I am writing a query to get the following information for my audit report. Can someone please send me script or help with this. Server NameSQL Server Instance NameDatabase NameLogin NameUser NameRole/permission assignedReport Date (current date)Appreciate your help.Thanks Ichbin

Problems with SQL dumps.....A temporary fix to get you by.

Posted: 28 Aug 2013 02:05 AM PDT

I am going to preface this post by saying this solution is TEMPORARY and for emergency situations on production servers. many will disagree with even using this but trust me there are circumstances where you need this...I know because I had a situation that necessitated it......Some background......Recently my company moved from a single on site data center to a dual remote data center environment. This meant i had several SQL clusters to move with some mirroring, etc. A typical farm in a typical environment if typical can be said of anything that is. In the process several of my servers were shut down less than gracefully. In fact one server was bounced back and forth between cluster nodes 10 times or more before someone who knew what they were doing shut it down correctly. this server came up with db's in emergency mode and all sorts of problems. I finally did get all teh databases to a point where I could select data from them. These were SharePoint databases on this instances. There are roughly 40 databases and SharePoint was displaying pages and seemed happy.So I wiped the sweat from my forehead and gave a quick "thank God!" adn went about my day only to be called in the middle of the night because SharePoint would not allow documents to be uploaded. What I said? Why? The drives had multiple 100's of gigs free and we expand roughly 1 gig per day.The issue was that the SQL server was saving dumps to the log drive and overnight had created almost 150 gigs of txt files filling up the whole drive. No this is a production system that was otherwise working. Clearly one of the databases has issues. I need to run some profiles, take them offline, do restores, and possible many other things that would take SharePoint out of service.Ever tried to convince your director that an emergency outage is needed when the service itself is up and reachable? Well lets just say he wasn't to keen on the idea of doing it outside a normal maintenance window. What do I do now? The databases are usable in some fashion because SharePoint is up. No data corruption seems to be taking place as everything put in SharePoint is retrievable and all the services are up.Do I just keep going in to delete logs every 15 minutes until the next window in 2 weeks? This solution is NOT the way to go about doing this long term BUT if you need to get through a week or two until a maintenance window the attached program (with source code) will watch any director for any particular file extension(or all files). When it sees a file being created that matches your criteria (for me it was log, mdmp, and txt files), the file will be automatically deleted. Not sent to the recycle bin btw but completely deleted. This will keep your drive from filling up.If anyone knows how to disable these dumps or move the directory without interrupting the service please do reply with how. I couldn't find a way personally though so I came up with this temporary solution and I'm sharing it with my favorite community. Again...this is NOT a permanent solution BUT it will get you by.....The source code and a compiled binary are in the attached zip file. Its written in c# and requires that .NET 2.0 or above be installed on the server. Its a PE so there is nothing to install. It can also be used remotely if you give it a UNC path to the directory you want to watch and its accessible. Here is an example of how it is used:Watcher.exe D:\MSSQL10_50.MSSQLSERVER\MSSQL\Log, mdmp;txt;log ^ A Comma separates the path from extensions. A semicolon delineates the different extensions to look for. These are the appropriate cmd line args if you are using it for the same purpose I did. Otherwisde it can be used to watch any directory for any files too so maybe it could come in handy for other uses.....Thanks

Parsing XML in sql server stored procedure

Posted: 28 Aug 2013 04:34 AM PDT

I have an XML string I need to parse into fields to be inserted into tables. I have 4 different XML strings being passed into a stored procedure to be parsed into 14 different tables. I have a large volume of XML strings being passed by a Web servers so I am getting Connection refused, Blocked and timeout errors. Currently I am able to handle 250,000 strings per hour but as volume goes above that number the errors start happening. I am using cross apply XML.nodes to parse the data from the string, is there a faster way within Sql that I need to be using. If this is the quickest way to parse XML in sql would using C# program be a better faster way to parse data into the sql tables?

Create link server for Microsoft Access2000

Posted: 28 Aug 2013 06:00 AM PDT

Hello,Can some one help to create link server for Microsoft Access2000, here is what I did but it is not working:EXEC sp_addlinkedserver @server = N'fx', @provider = N'Microsoft.ACE.OLEDB.12.0', @srvproduct = N'OLE DB Provider for ACE', @datasrc = N'\\us-balt-san-1\inhouse$\shipping\FX\DATA\fx.mdb';GO

How to Send Mail Task for Multiple packages

Posted: 28 Aug 2013 01:44 AM PDT

I have multiple execute package task running from one package. I need to send mail to multiple recipients, if it is success or failed. Also i want to show if a particular execute package task failed, send mail for it unles send one e-mail for displaying all tasks are successfully.Shaun

Renaming a Named Instance

Posted: 31 Jan 2012 01:14 PM PST

Is it possible to rename a named instance..?If yes, are there any issues..?

Help - query questions and best practise.

Posted: 27 Aug 2013 10:42 PM PDT

Hi everyone.I have situation that I hope I can get some advice on how to solve. I need to perform a check on a number of servers , post network outages on about 30 SQL servers.These are my constraints.1. Not all SQL servers in same domain so need to connect to them with SQL authentication - which works fine.2. Not all SQL servers are SQL 2008 so can't use CMS to do a multi query.3. I only have 1 machine configured to send DB mail. SMTP rules etc. are in place.So my question is can someone recommend a query to check SQL service status for a number of servers and then email the results out ?Also how do I loop thru a list of servers ? I currently have them all in a .txt file so if that could be used it would be very useful.Appreciate any help on this , was getting a bit caught up in it all therefore need some clarity on best way forward.many thanks

Failback and Restore

Posted: 23 Aug 2013 09:40 AM PDT

We are setting up Database Mirroring in our lab without a Witness. We are able to get the principal and mirror servers synchronized. When we manually failover, through their UI, everything works smoothly. Failing back, again through the UI, works.When we simulate a complete Principal shutdown, our Mirror stays in the Disconnected/In Recovery state. In order to get it out of that state and usable we need to run the following two commands:[code="sql"]ALTER DATABASE <database_name>SET PARTNER OFFRESTORE DATABASE <database_name> WITH RECOVERY[/code]Now to restart mirroring we have to go through the whole process of creating a full backup, tail backup, copy that over to the mirror (the original principal), apply the backup, and go through the Mirroring Wizard, start mirroring, and then, finally, failover to the original primary.I am just wondering if, in the above, is how it is supposed to be?

Accessing Log Shipping data using TSQL

Posted: 28 Aug 2013 01:31 AM PDT

Hi,I am using SQL Server 2008 R2 and would like to access the Transaction Log Shipping Status report using TSQL.To access this report using SSMS you right click on the SQL Server instance and then go to Reports > Standard Reports and Transaction Log Shipping Status report.I want to be able to access the Transaction Log status report data using TSQL.This data is stored in a table somewhere in SQL Server but I'm not sure where.Specifically I need access to all the columns in this report.Any help most appreciated.

SQL 2008 upgrade from standard to Developer

Posted: 27 Aug 2013 09:43 PM PDT

Hi Team,As there is no direct upgrade from sql 2k8 stand to devloper edition.Can anyone suggest how should I procced for that?

SCD not accepting connection for Teradata

Posted: 27 Aug 2013 09:43 PM PDT

HiI have an OLE DB provider for Teradata. I am trying to implement SCD using this connection manager. But the connection manager simply does not show up in the SCD component. Can someone please guide?
Posted by at No comments:
Subscribe to: Posts (Atom)

Search This Blog