[SQL 2012] label policy-row level security

---

• label policy-row level security
• AAG Backup
• RBS (Remote BLOB Storage)
• Creating scalar functions in tsql
• How to login using sqlcmd
• Is there a sql server equivalent of whoami?

label policy-row level security Posted: 01 Sep 2013 11:18 PM PDT Hi all, anyone have any idea what is label policy?whether it will affect performance of application?thanks sathiyan

AAG Backup Posted: 02 Sep 2013 02:31 AM PDT Hi all I have 2 doubts on this can anyone please clarify: On primary for system database backup I have not selected copy only backup, will that be okie? On secondary while configuring Txlog backup If I select all databases backup plan is failing where as if I choose user databases backup it is running fine. Don't we have txlog bkp for system databases backups in AAG?

RBS (Remote BLOB Storage) Posted: 01 Sep 2013 08:51 PM PDT Hi all,I'm looking into creating a new database for a fairly large application, which includes a feature for creating and storing many thousands of Word and PDF documents. The application that's being replaced stores the documents in a share on a NAS physically separated from the database server, for performance reasons, but the documents aren't in the database - the database records just include filepath pointers to the documents. Indexing of the documents' contents is done using a 3rd party tool.I'd like to have the documents stored in the database as BLOBs, ideally, to allow for more advanced indexing and querying, but from what I'm reading it's only possible to store the documents on a different server than the database if we have the Enterprise edition of MSSQL. Is that correct? If so, that seems a bit ridiculous - who would want to store the files on the same server as the database?I've enquired and found that Enterprise edition would be prohibitively expensive for us. If I'm correct that Enterprise edition is the only way to store files off-server, is there a workaround? Has anybody had experience doing this sort of thing before?

Creating scalar functions in tsql Posted: 01 Sep 2013 09:36 PM PDT Hi all,Firstly many apologies if this is in the wrong place. I'm basicly trying to create a scalar function that returns a nvarchar (15) variable.The Function has two input variables namely [category] and [AreaCode] (both nvarchar(15)) with an output variable [Team Line]. I've constructed this without any problem via a case statement (see below), but as I need to use this statement repeatingly I thought a scalar function would be the answer!I'm new to sql functions so any good advice would be much appreciated!select,case when when [Category]='A' then 'Team A' when [Category]='B' then 'Team B' when [Category]='C' then 'Team C' when [Category]='K' and [AreaCode] in ('Acct','Desp','Analysis') then 'Team D' when [Category]='L' and [AreaCode] not in ('Lark') then 'Team E'else 'Others' end as [Team Line]Kindest regards,J

How to login using sqlcmd Posted: 01 Sep 2013 09:25 PM PDT Hi,my rdbms version :2012 sqlexpressI created the login using the below commands:[code="sql"]SETUSERGOUSE masterGOIF EXISTS( SELECT * FROM sysdatabases WHERE name='permissionsDB' ) DROP DATABASE permissionsDBGOIF EXISTS( SELECT * FROM sys.sql_logins WHERE name='schemaUser' ) DROP LOGIN schemaUserGOCREATE DATABASE permissionsDBGOCREATE LOGIN schemaUser WITH PASSWORD='Pass#123',DEFAULT_DATABASE=permissionsDBGOGRANT CONNECT SQL TO schemaUserGOUSE permissionsDBGOCREATE USER schemaUser WITH DEFAULT_SCHEMA=dboGO[/code]what is the correct option to login as schemaUser using sqlcmd?http://technet.microsoft.com/en-us/library/ms162773.aspxI try the following it fails[code]C:\Users\user>sqlcmd -S user-pc\sqlexpress -U schemaUserPassword: Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failedfor user 'schemaUser'..C:\Users\user>sqlcmd -S user-pc\sqlexpress -U schemaUserPassword: Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failedfor user 'schemaUser'..[/code]any assistance is really appreciated!thanks

Is there a sql server equivalent of whoami? Posted: 01 Sep 2013 09:19 PM PDT Hi,my rdbms version is 2012 sqlexpress.is there a way to find out what login am I currently login using a command.believe that unix has a command called whoamihttp://linux.about.com/library/cmd/blcmdl1_whoami.htmthanks a lot!

You are subscribed to email updates from SQLServerCentral / SQL Server 2012 / SQL 2012 - General To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[T-SQL] Use SUM() with GROUP BY?

---

• Use SUM() with GROUP BY?
• Proplem Put Filter ("And" or "Or") in Query
• Finding row number which caused the error in Table value parameter insertion
• sql query
• Executed as user: MVS\sqlserver. Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. [SQLSTATE 42000] (Error 468). The step failed.
• Split Data
• sql query
• Query

Use SUM() with GROUP BY? Posted: 02 Sep 2013 12:47 AM PDT Is it possible to use SUM() in a SELECT query that uses a series of CASE statements to group data together? I have a procedure that generates an age-range report using case into different groups, I would like to combine that report with a report that SUM's 7 different fields by category. Right now, I take the output of the age -range report and through code, sum up all of the fields by separate calls. It's decently fast but I would like to know if I can move the SUM calculation into the case query if that's even possible.

Proplem Put Filter ("And" or "Or") in Query Posted: 01 Sep 2013 07:32 PM PDT Table With two Feild Name,Tools Example Table :1-Mike Walker2-Mike Cane3-Steve Walker4-Mitchell CaneI want someone who uses both(Walker and Cane ) to exhibit = MikeQuery : Select Name From TableTools WHERE (Tools = N'Walker') AND (Tools = N'Cane')Result=0With By Or Result=4How to write this queryPlz Help Me

Finding row number which caused the error in Table value parameter insertion Posted: 01 Sep 2013 09:08 PM PDT HiI am trying to write a Bulk import procedure, i am using table value parameter to insert thousands of rows in the table.The problem is if any error happens,i am not able to know which row in the table value parameter caused it.I am not using transaction in stored procedurehere is the code[code="sql"]--Table creation CREATE TABLE DataImport (id int identity(1,1),Name varchar(2))Insert INTO DataImportvalues('aa'),('bb'),('cc'),('dd')--SELECT * FROM DataImport--Table type creationCREATE TYPE udt_Table AS TABLE (ud_name varchar(3))--stored procedure CREATE PROC Usp_DataImport@TVP [dbo].[udt_Table] READONLYASBEGIN DECLARE @Insert Table (ActionType nvarchar(10),NewestName varchar(3),OldestName vaRCHAR(3)) MERGE DataImport AS [TARGET] USING @TVP AS [SOURCE] ON [TARGET].Name = [SOURCE].ud_name WHEN MATCHED THEN UPDATE SET Name = [SOURCE].ud_name WHEN NOT MATCHED THEN INSERT (Name) VALUES([SOURCE].ud_name) OUTPUT $ACTION, Inserted.Name,Deleted.Name INTO @Insert; SELECT * FROM @InsertEND--Executing Procedure--First proc Exection sucessfully doneDECLARE @dd AS dbo.udt_Table INSERT INTO @ddvalues('ee'),('aa'),('XX'),('GG'),('66')EXEC Usp_DataImport @TVP = @dd--Second proc Exection fail this will generate error due to varchar length (String validation can be done frontend to avoid this problem...i am using this to generate the error)Here i want to get at which row error happens...so that i will return to application to make changes and then insertDECLARE @dd AS dbo.udt_Table INSERT INTO @ddvalues('ee'),('aa'),('XX'),('GG'),('GGG')('66')EXEC Usp_DataImport @TVP = @dd[/code]Thanks

sql query Posted: 01 Sep 2013 08:14 AM PDT Hello friends,Please help me in finding the correct query!!Question:You need to find duplicate account's in an Account table. The Account table has 15 columns in it. What is the problem with this query?SELECT * FROM AccountWHERE CreateDate > '2/1/2013'GROUP BY AccountIDHAVING COUNT(AccountID) >1Thanks,RT.

Executed as user: MVS\sqlserver. Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation. [SQLSTATE 42000] (Error 468). The step failed. Posted: 01 Sep 2013 06:02 PM PDT Hi Guys,I need help in sorting out this job its begin failing for a week now.The code is as follows:--==================================Get Box_no, MSISDN from Navision================================================== --Pulls MSISDN info from DynamicsNavdatabase SELECT DISTINCT CAST(REPLACE(si2.[Serial No_],'AC1','BX1') AS VARCHAR(15)) AS Box_no, si.[Serial No_] AS SIM, si.[MSISDN Serial No_] AS MSISDN INTO #NavisionUnitSerials -- drop table #NavisionUnitSerials FROM DynamicsNavdatabase.dbo.[MIX TELEMATICS$Serial No_ Information] si LEFT JOIN DynamicsNavdatabase.dbo.[MIX TELEMATICS$Serial No_ Information] si2 ON si2.[SIM Serial No_ (Embeded)] = si.[Serial No_] WHERE si.[Item No_] = '1000851' AND si.[MSISDN Serial No_] IS NOT NULL AND si.[MSISDN Serial No_] <> '' AND si2.[Serial No_] <> '' AND si2.[Serial No_] IS NOT NULL; CREATE NONCLUSTERED INDEX IDX_NAV_BOX ON #NavisionUnitSerials ( Box_no ); --=============================Get last passed test date of RSLead & MiXControl========================================= SELECT unitid AS [Box_No], createdate AS [CreateDate], 1 as tab INTO #LastPassedTestDate_Init -- drop table #LastPassedTestDate_Init FROM RSLead.dbo.unittesthead H WITH (NOLOCK) WHERE (testrefcode IS NOT NULL OR testrefcode <> '') --IF HAS REF THEN TEST PASSED AND H.unittestheadno IN (SELECT MAX(td2.unittestheadno) FROM RSLead.dbo.unittesthead td2 WITH (NOLOCK) WHERE h.unitid = td2.unitid COLLATE DATABASE_DEFAULT GROUP BY td2.unitid) UNION ALL SELECT [Box_No], [CreateDate], 2 FROM [MixControl].[dbo].[UnitTestResultHead] H WITH (NOLOCK) WHERE TestStatus = 3 AND TestReference IS NOT NULL AND H.[UnitTestResultHeadID] IN (SELECT MAX(td2.[UnitTestResultHeadID]) FROM [MixControl].[dbo].[UnitTestResultHead] td2 WITH (NOLOCK) WHERE h.[Box_No] = td2.[Box_No] COLLATE DATABASE_DEFAULT GROUP BY td2.[Box_No]); CREATE CLUSTERED INDEX IDX_LastPassedTestDate_Init ON #LastPassedTestDate_Init ( [Box_No] ); SELECT [Box_No], MAX([CreateDate]) AS [CreateDate] INTO #LastPassedTestDate -- drop table #LastPassedTestDate FROM #LastPassedTestDate_Init GROUP BY Box_No; CREATE CLUSTERED INDEX IDX_LastPassedTestDate ON #LastPassedTestDate ( [Box_No] ); --=============================Get Company RegNo on MiXControl========================================= Select ClientID,CompanyRegistrationNumber into #tempCompanyReg from dbo.ClientCompany where CompanyRegistrationNumber <> '' --==========================================Get Unit Info============================================================= DECLARE @debperiodno INT; SET @debperiodno = ( SELECT DISTINCT(periodno)FROM rsdebtors.dbo.period WHERE curflag = 1 ); INSERT INTO [AllUnitsReportDaily] SELECT Distinct UT.UnitTypeDesc AS [TYPE], C.ContractID, U.Box_No, A.RegistrationNumber, CL.AccountName, rscon.chargecode, --CASE -- WHEN (cch.escalchargeflag = 1) THEN rscon.chargeamt -- WHEN (rscon.incrperiod > @debperiodno + 1) THEN rscon.chargeamt -- ELSE cch.chargeamt -- END AS 'ChargeAmt', rscon.chargeamt AS 'ChargeAmt', cch.chargeamt as NormalChargeAmt, rscon.startperiod, C.CreateDate AS [ContractDate], rscon.accountno AS [AccountNo], C.salespersonid AS [SalesPersonID], b2.brokername as SalesPersonDesc, C.FitmentCentreID AS [FitmentCentreID], case when left(u.Box_No,2) = '04' then i.name when LEFT(u.box_no,2) = '35' then i.name else b1.brokername end as [FitmentCentreName], CASE WHEN (cch.escalchargeflag = 1) THEN 'RENTAL CONTRACT' ELSE 'CASH CONTRACT' END AS 'ContractType', ISNULL(CCT.contracttypedesc,CCT1.contracttypedesc) AS ContractTypeDesc, CAST(ISNULL(aic.PolicyNumber,'') AS CHAR(30)) AS 'PolicyNumber', ISNULL(C.UniqueNumber,'') AS 'UniqueNumber', ISNULL(l.leadno,ML.LEADID) AS LeadNo, ISNULL(l.brokerno,LP.BrokerID) AS BrokerNo, ISNULL(ISNULL(B.brokercode,LP.ExternalPartnerCode),'') AS ExternalPartnerCode, ISNULL(L.idno,LP.IDNumber) AS IDNumber, zl.MSISDN, LPT.CreateDate AS LastPassedTestDate, a.ChassisNumber, tcr.CompanyRegistrationNumber, ISNULL(l.createopr,ML.CreateOpr) AS LeadCreatedBy, c.editOpr as [ContractModifiedBy], ama.AssetMakeDesc, am.AssetModelDesc FROM MixControl.dbo.unit U WITH (NOLOCK) JOIN MixControl.dbo.unittype UT WITH (NOLOCK) ON U.UnitTypeID = UT.UnitTypeID JOIN MixControl.dbo.ContractAsset CA WITH (NOLOCK) ON CA.AssetID = U.AssetID JOIN MixControl.dbo.CONTRACT C WITH (NOLOCK) ON C.ContractID = CA.ContractID JOIN MixControl.dbo.Asset A WITH (NOLOCK) ON CA.AssetID = A.AssetID JOIN MixControl.dbo.assetmodel AM WITH (NOLOCK) ON a.assetmodelid = am.assetmodelid JOIN MixControl.dbo.assetmake AMA WITH (NOLOCK) ON am.assetmakeid = ama.assetmakeid JOIN MixControl.dbo.Client CL WITH (NOLOCK) ON CL.ClientID = C.ClientID JOIN RSDebtors.dbo.contractcharge rscon WITH (NOLOCK) ON rscon.contractno = C.ContractID JOIN RSDebtors.dbo.charge cch WITH (NOLOCK) ON rscon.chargecode = cch.chargecode LEFT JOIN RSlead.dbo.lead L WITH (NOLOCK) ON l.contractid = c.ContractID AND l.eventtypecode = 'PUSHTOFX' LEFT JOIN RSlead.dbo.broker B WITH (NOLOCK) ON L.externalpartnerno = B.brokerno LEFT JOIN MixControl.dbo.lead ML WITH (NOLOCK) ON ML.contractid = c.ContractID LEFT JOIN MixControl.dbo.LeadPerson LP WITH (NOLOCK) ON LP.LeadPersonID = ML.LeadPersonID LEFT JOIN MixControl.dbo.AssetInsuranceCompany AIC WITH (NOLOCK) ON CA.AssetID = AIC.AssetID LEFT JOIN RSLead.dbo.contracttype CCT WITH (NOLOCK) ON CCT.contracttypeno = L.contracttypeno LEFT JOIN RSLead.dbo.contracttype CCT1 WITH (NOLOCK) ON CCT1.contracttypeno = ML.contracttypeID LEFT JOIN #navisionUnitSerials zl ON U.Box_No = zl.box_no COLLATE DATABASE_DEFAULT LEFT JOIN #LastPassedTestDate LPT WITH (NOLOCK) ON U.BOX_NO = LPT.Box_No COLLATE DATABASE_DEFAULT left join #tempCompanyReg tcr with(nolock) on cl.ClientID = tcr.ClientID left join RSlead.dbo.broker b1 on c.FitmentCentreID = cast(b1.brokerno as varchar(10)) left join RSlead.dbo.broker b2 on c.SalespersonID = b2.brokercode left join Installer i on c.FitmentCentreID = cast(i.installerID as varchar(15)) WHERE rscon.endperiod > @debperiodno --and rscon.startperiod < = @debperiodno AND U.IsActive = 1 AND CA.IsActive = 1 AND C.IsActive = 1 order by U.Box_No desc ; --=========================================CA with service charges========================================================= INSERT INTO [AllUnitsReportDaily] SELECT Distinct UT.UnitTypeDesc AS [TYPE], C.ContractID, U.Box_No, A.RegistrationNumber, CL.AccountName, 'MixServices', SUM(conserv.serviceamt) AS [chargeamt], '0.00' as NormalChargeAmt, conserv.startperiod, C.CreateDate AS [ContractDate], conserv.accountno AS [AccountNo], C.salespersonid AS [SalesPersonID], b2.brokername as SalesPersonDesc, C.FitmentCentreID AS [FitmentCentreID], b1.brokername as [FitmentCentreName], CASE WHEN l.contracttypeno = 2 THEN 'RENTAL CONTRACT' ELSE 'CASH CONTRACT' END AS 'Contract Type', ISNULL(CCT.contracttypedesc,CCT1.contracttypedesc) AS ContractTypeDesc, CAST(ISNULL(aic.PolicyNumber,'') AS CHAR(30)) AS 'PolicyNumber', ISNULL(C.UniqueNumber,'') AS 'UniqueNumber', ISNULL(l.leadno,ML.LEADID) AS LeadNo, ISNULL(l.brokerno,LP.BrokerID) AS BrokerNo, ISNULL(ISNULL(B.brokercode,LP.ExternalPartnerCode),'') AS ExternalPartnerCode, ISNULL(L.idno,LP.IDNumber) AS IDNumber, zl.MSISDN, LPT.CreateDate AS LastPassedTestDate, a.ChassisNumber, tcr.CompanyRegistrationNumber, ISNULL(l.createopr,ML.CreateOpr) AS LeadCreatedBy, c.editOpr as [ContractModifiedBy], ama.AssetMakeDesc, am.AssetModelDesc FROM unit U WITH (NOLOCK) JOIN unittype UT WITH (NOLOCK) ON U.UnitTypeID = UT.UnitTypeID JOIN ContractAsset CA WITH (NOLOCK) ON CA.AssetID = U.AssetID JOIN CONTRACT C WITH (NOLOCK) ON C.ContractID = CA.ContractID JOIN Asset A WITH (NOLOCK) ON CA.AssetID = A.AssetID JOIN dbo.assetmodel AM WITH (NOLOCK) ON a.assetmodelid = am.assetmodelid JOIN dbo.assetmake AMA WITH (NOLOCK) ON am.assetmakeid = ama.assetmakeid JOIN Client CL WITH (NOLOCK) ON CL.ClientID = C.ClientID JOIN rsdebtors.dbo.contractservice conserv WITH (NOLOCK) ON conserv.contractno = ca.ContractID LEFT JOIN AssetInsuranceCompany AIC WITH (NOLOCK) ON CA.AssetID = AIC.AssetID LEFT JOIN rslead.dbo.lead l WITH (NOLOCK) ON CA.ContractID = l.contractid AND l.eventtypecode = 'PUSHTOFX' LEFT JOIN RSlead.dbo.broker B WITH (NOLOCK) ON L.externalpartnerno = B.brokerno LEFT JOIN MixControl.dbo.lead ML WITH (NOLOCK) ON ML.contractid = c.ContractID LEFT JOIN MixControl.dbo.LeadPerson LP WITH (NOLOCK) ON LP.LeadPersonID = ML.LeadPersonID LEFT JOIN RSLead.dbo.contracttype CCT WITH (NOLOCK) ON CCT.contracttypeno = L.contracttypeno LEFT JOIN RSLead.dbo.contracttype CCT1 WITH (NOLOCK) ON CCT1.contracttypeno = ML.contracttypeID LEFT JOIN #navisionUnitSerials zl ON U.Box_No = zl.box_no COLLATE DATABASE_DEFAULT LEFT JOIN #LastPassedTestDate LPT WITH (NOLOCK) ON U.BOX_NO = LPT.Box_No left join #tempCompanyReg tcr on cl.ClientID = tcr.ClientID left join RSlead.dbo.broker b1 on c.FitmentCentreID = cast(b1.brokerno as varchar(10)) left join RSlead.dbo.broker b2 on c.SalespersonID = b2.brokercode WHERE @debperiodno BETWEEN conserv.startperiod AND conserv.endperiod AND U.UnitTypeID = 7 --ca AND U.IsActive = 1 AND CA.IsActive = 1 AND c.IsActive = 1 GROUP BY C.ContractID, U.Box_No, A.RegistrationNumber, CL.AccountName, conserv.startperiod, C.CreateDate, conserv.accountno, C.salespersonid, b2.brokername, C.FitmentCentreID, b1.brokername, CASE WHEN l.contracttypeno = 2 THEN 'RENTAL CONTRACT' ELSE 'CASH CONTRACT' END, CCT.contracttypedesc, CCT1.contracttypedesc, aic.PolicyNumber, C.UniqueNumber, UT.UnitTypeDesc, ISNULL(l.leadno,ML.LEADID), ISNULL(l.brokerno,LP.BrokerID), ISNULL(B.brokercode,LP.ExternalPartnerCode), ISNULL(L.idno,LP.IDNumber), zl.MSISDN, LPT.CreateDate, a.ChassisNumber, tcr.CompanyRegistrationNumber, ISNULL(l.createopr,ML.CreateOpr), c.editOpr, ama.AssetMakeDesc, am.AssetModelDesc ; --============================================DISPLAY DATA============================================================= --RUNTIME AROUND 35S /* --compare to excel file SELECT [TYPE], COUNT(UnitId) AS Amount FROM AllUnitsReportDaily GROUP BY [TYPE] ORDER BY [TYPE]; SELECT * FROM AllUnitsReportDaily order by CONTRACTDATE DESC */ /* --Drop tables --=================================-- DROP TABLE #navisionUnitSerials; DROP TABLE #LastPassedTestDate; DROP TABLE #LastPassedTestDate_Init --=================================-- --SELECT * FROM AllUnitsReportDaily ORDER BY CONTRACTDATE DESC

Split Data Posted: 01 Sep 2013 05:16 PM PDT Hi All,I have written 3 queries and put them in 3 cte statements each is a different report. 1. FCR 2. FCP 3. DFPC. The code is putting each report after the other in one row, when I need each type under each other where the division and currency are the same.Current Data:Division,Code,FCR,jan,feb,mar,total,division,code,FCP,jan,feb,mar,total,division,code,DFPC,Jan,Feb,Mar,totalABCD SGD FCR -28901.4 71343.22 113375.79 155817.61 ABCD SGD FCP 0 50507.58 -2068.07 48439.51 ABCD SGD DFCP 0 0 0 0ACME HKD FCR 161.74 182.24 19963.11 20307.09 ACME HKD FCP 1875.94 2347.15 -4324.07 -100.98 ACME HKD DFCP 0 0 150.01 150.01ACME MYR FCR 0 0 150.01 150.01 ACME MYR FCP 195.85 -34239.96 -2754.33 -36798.44 ACME MYR DFCP 0 0 0 0[code="sql"];withcteFCR(DIVName1,CCYCODE,title,FCRJan,FCRFeb,FCRMar,total)AS (select d.DIVName1 ,c.CCYCode ,'Foreign Currency Receivables' as [Title] ,SUM(CASE WHEN r.ACRFSMID IN (128) THEN r.ACRBalanceBase ELSE 0 END) AS [January 13] ,SUM(CASE WHEN r.ACRFSMID IN (129) THEN r.ACRBalanceBase ELSE 0 END) AS [Feburary 13] ,SUM(CASE WHEN r.ACRFSMID IN (130) THEN r.ACRBalanceBase ELSE 0 END) AS [March 13] ,SUM(CASE WHEN r.ACRFSMID IN (128, 129, 130) THEN r.ACRBalanceBase ELSE 0 END) AS [Total]from vwAccountsReceivable rinner join vwDivision don r.ACRDIVID = d.DIVIDinner join vwCurrency con r.ACRTransactionCCYID = c.CCYIDgroup by d.DIVName1 ,c.CCYCODE),cteFCP(DIVName1,CCYCODE,title,FCPJan,FCPFeb,FCPMar,total)AS (select d.DIVName1 ,c.CCYCODE ,'Foreign Currency Payables' as [Title] ,SUM(CASE WHEN p.ACPFSMID IN (128) THEN p.ACPBalanceBase ELSE 0 END) AS [January 13] ,SUM(CASE WHEN p.ACPFSMID IN (129) THEN p.ACPBalanceBase ELSE 0 END) AS [Feburary 13] ,SUM(CASE WHEN p.ACPFSMID IN (130) THEN p.ACPBalanceBase ELSE 0 END) AS [March 13] ,SUM(CASE WHEN p.ACPFSMID IN (128, 129, 130) THEN '$' + (CONVERT(money,p.ACPBalanceBase,1))ELSE 0 END) AS [Total] from vwAccountsPayable pinner join vwDivision don p.ACPDIVID = d.DIVIDinner join vwCurrency con p.ACPTransactionCCYID = c.CCYIDgroup by d.DIVName1 ,c.CCYCODE),cteDFCP(DIVName1,CCYCODE,title,DFCPJan, DFCPFeb, DFCPMar,total)AS(Select ISNULL(pvt.[Division Buyer],0) as[DIVName1] ,pvt.currency as [CCYCode] ,'Draft Foreign Currency Payables' as [Title] ,ISNULL(pvt.[January],0) as [January] ,ISNULL(pvt.[Feburary],0) as [Feburary] ,ISNULL(pvt.[March],0) as [March] ,SUM(ISNULL(pvt.[January],0)+ ISNULL(pvt.[Feburary],0) + ISNULL(pvt.[March],0)) as [Total]from(SELECT h.optional_1 as [Division Buyer] ,currency ,DATENAME(Month,h.issuedate) as [Month] ,SUM(h.invoicetotal) as [Invoice Total] FROM Companies c JOIN Invoice_head h ON h.company_id = c.company_id JOIN Invoice_lines l ON l.invoice_id = h.invoice_id WHERE l.approvedate is null and h.company_id in (36,37,38,39,40,41,42,43,44,45,46,47,99,103,114) GROUP BY h.optional_1 ,currency ,h.issuedate)as screPIVOT (SUM([Invoice Total]) for [Month] IN( [January] ,[Feburary] ,[March] ))as pvtGROUP BY pvt.[January] ,pvt.[Feburary] ,pvt.[March] ,pvt.[Division Buyer] ,pvt.currency )select r.DIVName1 as [Division] ,r.CCYCODE as [Code] ,r.title as [Foreign Currency Receivables] ,r.FCRJan as [Jan] ,r.FCRFeb as [Feb] ,r.FCRMar as [Mar] ,r.total as [Total] ,p.DIVName1 as [Division] ,p.CCYCODE as [Code] ,p.title as [Foreign Currency Payables] ,p.FCPJan as [Jan] ,p.FCPFeb as[Feb] ,p.FCPMar as [Mar] ,p.total as [Total] ,d.DIVNAME1 as [Division] ,d.CCYCODE as [Code] ,d.title as [Draft Foreign Currency Payables] ,d.DFCPJan as [Jan] ,d.DFCPFeb as [Feb] ,d.DFCPMar as [Mar] ,d.total as [Total]from cteFCR rinner join cteFCP pon r.DIVName1 = p.DIVName1 and r.CCYCODE = p.CCYCODEinner join cteDFCP don r.DIVName1 = d.DIVName1 and r.CCYCODE = d.CCYCODEGROUP BY r.DIVName1 ,r.CCYCODE ,r.title ,r.FCRJan ,r.FCRFeb ,r.FCRMar ,r.total ,p.DIVName1 ,p.CCYCODE ,p.title ,p.FCPJan ,p.FCPFeb ,p.FCPMar ,p.total ,d.DIVNAME1 ,d.CCYCODE ,d.title ,d.DFCPJan ,d.DFCPFeb ,d.DFCPMar ,d.total[/code]Expected:Division, Currency, Report, Jan, Feb, Mar, TotalABCD SGD FCR -28901.4 71343.22 113375.79 155817.61ABCD SGD FCP 0 50507.58 -2068.07 48439.51ABCD SGD DFCP 0 0 0 0ACME HKD FCR 161.74 182.24 19963.11 20307.09ACME HKD FCP 1875.94 2347.15 -4324.07 -100.98ACME HKD DFCP 0 0 150.01 150.01ACME MYR FCR 0 0 150.01 150.01ACME MYR FCP 195.85 -34239.96 -2754.33 -36798.44ACME MYR DFCP 0 0 0 0Thanks for any help.

sql query Posted: 01 Sep 2013 08:15 AM PDT Hello friends,Please help me in writing a query!!Question:Write a query based off of the following data stored in the AccountingEntry table. The query needs to return running totals and should produce the exact results for each customer listed below.Deposits – amounts added to an accountCharge – amounts should be subtracted from a depositCancel/Refund – amount returned to a customer, should be subtracted from a depositAccountingEntryID CustomerID Date Type Amount 1 1 1/1/2013 Deposit 400 2 3 1/23/2013 Deposit 900 3 19 2/28/2013 Deposit 250 4 23 3/15/2013 Charge 175 5 1 2/1/2013 Charge 350 6 15 4/1/2013 Deposit 2000 7 3 2/23/2013 Charge 500 8 15 4/1/2013 Charge 100 9 1 2/23/2013 Deposit 100 10 23 3/15/2013 Charge 175 11 15 4/1/2013 Charge 750 12 1 2/15/2013 Charge 25 13 15 4/1/2013 Cancel/Refund 1150 14 3 1/25/2013 Deposit 100 15 15 4/1/2013 Deposit 750 16 19 3/28/2013 Charge 100 17 15 4/1/2013 Deposit 100 18 3 3/23/2013 Charge 500 19 23 3/15/2013 Deposit 400 20 19 4/28/2013 Charge 100 21 1 3/15/2013 Cancel/Refund 125 Results should look like AccountingEntryID CustomerID Date Type Amount Running Deposit RunningAmount Charged RunningAmt Remaining1 1 1/1/2013 Deposit 400 400 0 4005 1 2/1/2013 Charge 350 400 350 5012 1 2/15/2013 Charge 25 400 375 259 1 2/23/2013 Deposit 100 500 375 12521 1 3/15/2013 Cancel/Refund 125 500 375 0Thanks,RT

Query Posted: 01 Sep 2013 01:17 PM PDT I need a T sql query which should read and load text files data from the directory one file after another file and load the file data into a table.Thanks for your help in advance

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[SQL Server 2008 issues] fill the missing/incorrect values for the gender column

---

• fill the missing/incorrect values for the gender column
• Backup job fails but maintenance plan succeeds
• export to csv
• windows installer fot sql installation
• BIDS Debug issue with single solution

fill the missing/incorrect values for the gender column Posted: 01 Sep 2013 04:20 PM PDT HiI have a gender column like this:Gender--------FAAAMnullFMnullCan some give me an idea how can I create and implement a strategy to fill the missing/incorrect values for the gender column

Backup job fails but maintenance plan succeeds Posted: 29 Nov 2011 12:20 AM PST The backups are in the desired destination, they restore just fine, and the maintenance plan log reports no errors.The agent job fails, but there are so many backups in the plan that there is no useful error information. I've pasted the step log here, unfortunately it is long but at the bottom you can see an error, but nothing that says why the job is failing:[code="plain"]Date 11/29/2011 2:00:00 AMLog Job History (User Databases Full Backup Plan.Subplan_1)Step ID 1Server xxxJob Name User Databases Full Backup Plan.Subplan_1Step Name Subplan_1Duration 00:37:23Sql Severity 0Sql Message ID 0Operator Emailed Operator Net sent Operator Paged Retries Attempted 0MessageExecuted as user: xxx\xxx. ...on 10.0.1600.22 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:00:00 AM Progress: 2011-11-29 02:00:01.07 Source: {196E9554-8336-487A-A0F2-F002F92CCB12} Executing query "DECLARE @Guid UNIQUEIDENTIFIER EXECUTE msdb..sp...".: 100% complete End Progress Progress: 2011-11-29 02:00:03.78 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 7% complete End Progress Progress: 2011-11-29 02:00:03.78 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 14% complete End Progress Progress: 2011-11-29 02:00:03.79 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 21% complete End Progress Progress: 2011-11-29 02:00:03.79 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 28% complete End Progress Progress: 2011-11-29 02:00:03.79 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 35% complete End Progress Progress: 2011-11-29 02:00:03.79 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 42% complete End Progress Progress: 2011-11-29 02:00:03.79 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 50% complete End Progress Progress: 2011-11-29 02:00:03.79 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 57% complete End Progress Progress: 2011-11-29 02:00:03.79 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 64% complete End Progress Progress: 2011-11-29 02:00:03.81 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 71% complete End Progress Progress: 2011-11-29 02:00:03.81 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 78% complete End Progress Progress: 2011-11-29 02:00:03.81 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 85% complete End Progress Progress: 2011-11-29 02:00:03.81 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 92% complete End Progress Progress: 2011-11-29 02:00:03.81 Source: Back Up Database (Full) Executing query "EXECUTE master.dbo.xp_create_subdir N'\\xxx\bac...".: 100% complete End Progress Progress: 2011-11-29 02:14:10.75 Source: Back Up Database (Full) Executing query "BACKUP DATABASE [xxx] TO DISK = N'\\xxx\backup...".: 100% complete End Progress Progress: 2011-11-29 02:36:09.81 Source: Back Up Database (Full) Executing query "BACKUP DATABASE [xxx] TO DISK = N'\\xxx...".: 100% complete End Progress Progress: 2011-11-29 02:36:15.84 Source: Back Up Database (Full) Executing query "BACKUP DATABASE [xxx] TO DISK = N'\\xxx\back...".: 100% complete End Progress Progress: 2011-11-29 02:36:17.12 Source: Back Up Database (Full) Executing query "BACKUP DATABASE [xxx] TO DISK = N'\\xxx\...".: 100% complete End Progress Progress: 2011-11-29 02:36:21.36 Source: Back Up Database (Full) Executing query "BACKUP DATABASE [xxx] TO DISK = N'\\xxx...".: 100% complete End Progress Progress: 2011-11-29 02:36:21.87 Source: Back Up Database (Full) Executing query "BACKUP DATABASE [xxx] TO DISK = N'\\xxx\back...".: 100% complete End Progress Progress: 2011-11-29 02:36:33.00 Source: Back Up Database (Full) Ex... The package execution fa... The step failed.[/code]The system db backup job succeeds. The differential jobs between the full backups succeed. This is not a terrible crisis, as the full backups themselves are actually succeeding, but it raises my blood pressure to come in every morning and see that my backup jobs have failed, and it wastes my time having to go check that they did not actually fail but rather agent threw up on me. This has been happening for about a week.Any suggestions??

export to csv Posted: 01 Sep 2013 09:08 AM PDT Hi ProfessionalsI have a script thats exports to a csv file[code]exec exporttocsv 'select top 5 * from newtable', 'test.csv'[/code]the problem I am encountering is that some of the csv files are over 100MB making it virtually impossible to open, manipulate or email to work colleagues.i am sure it is possible but do not know the correct syntax but is there a way to select say the first 100,000 rows then the next 100,000 rows and so on then finallly however may rows are left into more than one csveg[code]exec exporttocsv 'select top 100 * from newtable', 'test.csv'exec exporttocsv 'select NEXT100,000 rows from newtable', 'test1.csv'exec exporttocsv 'select REMAINING rows from newtable', 'test2.csv'[/code]

windows installer fot sql installation Posted: 31 Aug 2013 10:38 PM PDT why do one require windows installer for installing sql 2005/2008 setup.

BIDS Debug issue with single solution Posted: 01 Sep 2013 01:09 AM PDT This is driving me nuts. I hope someone can assist me with the resolution.Out of the dozens and dozens of SSIS solutions we have at the office, only one solution does this. When I open the solution, open a single package and try to debug that package, the solution opens up other packages and starts running those instead.Now this solution is unique in that I have multiple projects. One for SSIS packages, and one for SSRS objects. But the SSRS objects aren't even opening. This is the SSIS project only that has this issue and none of our other SSIS solutions / projects have this problem.Can someone point me to what setting or problem I might be having that could cause this? It's getting so bad that I have to remember to export the package to a misc. solution in order to fix any issues then import the package back into that solution on top of the old copy.

You are subscribed to email updates from SQLServerCentral / SQL Server 2008 - General / SQL Server 2008 To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610

[how to] Does limit impact affected rows or not?

---

• Does limit impact affected rows or not?
• Restoring tab exports to a mysql database with integrity
• MySQL: update instead of delete if foreign key constraint?
• Relation between max values for table_open_cache and open_files_limit?
• Expanding from one to one to one to many relationship for 2 tables
• How to automatically backup MongoDB?
• Stored procedure to handle empty sets
• best ETL design to transfer transaction tables records into the data-warehouse
• Trying to create PostGIS table
• MySQL Replication fails - commands received in wrong order?
• How to access a SQL Server database from other computer connected to the same workgroup?
• my.cnf validation
• How do I migrate varbinary data to Netezza?
• How can I get my linked server working using Windows authentication?
• Connection to local SQL Server 2012 can be established from SSMS 2008 but not from SSMS 2012
• Design consideration regarding state handling: how to store multiple, variable states for one entity
• How to handle "many columns" in OLAP RDBMS
• Mysql DB server hits 400% CPU
• What are the different ways to keep track of active and archived data?
• Database stuck in restoring and snapshot unavailable
• Multiple database servers for performance vs failover
• Access 2003 (SQL Server 2000) migration to SQL Azure
• Truncate table is taking too long in PostgreSQL
• InnoDB - How to get top locked tables and rows which are locked
• PostGIS: Remove stale/obsolete geometry columns from the geometry_columns table [on hold]
• PgAdmin III - How to connect to database when password is empty?

Does limit impact affected rows or not? Posted: 01 Sep 2013 05:31 PM PDT I have this table: CREATE TABLE IF NOT EXISTS `usergroups` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL, `group_id` smallint(5) unsigned NOT NULL, PRIMARY KEY (`group_id`,`user_id`), KEY `id` (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=5496 ; when run this query: EXPLAIN SELECT `UserGroup`.`user_id` FROM `usergroups` AS `UserGroup` WHERE `UserGroup`.`group_id` = 1 limit 30 its out put is: 1 SIMPLE UserGroup ref PRIMARY PRIMARY 2 const 543 Using index I think something is wrong! because it affected 543 rows but I think it should affect 30 rows at maximum. Is it true?

Restoring tab exports to a mysql database with integrity Posted: 01 Sep 2013 02:33 PM PDT I have a database with a lot of data for a client that's on a shared web host. Doing a plain mysqldump is causing the job to get killed on the server, so we were looking at backing up the tables individually. I've found that the --tab option will automatically create separate files for each table, but my question comes to restoring them while maintaining integrity. This site shows various ways to write shell scripts that loops through the list of backup files and loads them one-by-one. However I am suspicious that this may not guarantee integrity of data. Is there a more automated way I can lock the database and load the individual tab files, to ensure that I have the database restored to the state it was backed up in?

MySQL: update instead of delete if foreign key constraint? Posted: 01 Sep 2013 07:46 PM PDT I have a bit of a strange question. I know about insert on duplicate key update. My question is, is there something similar for deletes that fail because of foreign key constraints? For example: delete from table1 where value='something'; But, table2 has a foreign key that depends on the the value I want to delete in table1, so the delete fails. I'd like to do something like this: delete from table1 where value='something' on foreign key fail update some_other_value='something else'; I know that looks really weird, but I have a good reason for doing it (without getting into details, it has to do with versioning historical data that can't be destroyed in the event that a value is referenced elsewhere.) I can figure out how to do this with more than one query, of course, but I'd like to do it in a single query if I can. I'm pretty sure it's not possible, but I'd like to ask before giving up :) Thanks!

Relation between max values for table_open_cache and open_files_limit? Posted: 01 Sep 2013 09:44 AM PDT From MySQL documentation: Max value limit for open_files_limit is 65536 and table_open_cache is 524288. When I applied max limit, mysql server started but with following warnings in error log: 2013-09-01 11:34:42 57231 [Warning] Buffered warning: Could not increase number of max_open_files to more than 65536 (request: 1048687) 2013-09-01 11:34:42 57231 [Warning] Buffered warning: Changed limits: table_cache: 32713 (requested 524288) I see that max-limit for open_files_limit is less than table_open_cache. I feel this is strange and open_file_limit should be always greater than table_open_cache. Neverthless, I set open_file_limit to larger than 65536 and it worked. I am now wondering, how these are related? Is there any mistake in MySQL documentation? p.s. I modified /etc/security/limits.conf on way as for such high limits.

Expanding from one to one to one to many relationship for 2 tables Posted: 01 Sep 2013 12:34 PM PDT I have 4 tables tblSData,tblGF,tblGFAlert and tblEAlert. For each data in tblSData it can have a data in tblGFAlert or tblEAlert. The problem now each main data (tblSData) may have more then one data in tblEAlert. So for each main data now I can pick nicely and show cause I have their foreign key in the tblSData. Below are table and current queries how to over come this problem ? SELECT tblSData.header, tblGFIn.gFName as gFNameIn, tblGFOut.gFName as gFNameOut, CAST(Date_Add(tblSData.dateTimer , Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimer, CAST(tblSData.sInsertDateTime AS CHAR) As sInsertDateTime , tblGFAIn.gMessage, tblGFAOut.gMessage, tblEAlert.eMessage FROM tblSData LEFT JOIN tblGF As tblGFIn ON tblSData.gFInID=tblGFIn.gFID LEFT JOIN tblGF As tblGFOut ON tblSData.gFOutID=tblGFOut.gFID LEFT JOIN tblGFAlert AS tblGFAIn ON tblSData.gFAInID = tblGFAIn.gAlertID LEFT JOIN tblGFAlert AS tblGFAOut ON tblSData.gFAOutID=tblGFAOut.gAlertID LEFT JOIN tblEAlert ON tblSData.eAlertID=tblEAlert.eAlertID Where tblSData.aID=".$aID." Order By tblSData.dateTimer Asc CREATE TABLE IF NOT EXISTS `tblSData` ( `sDataID` int(11) NOT NULL AUTO_INCREMENT, `header` varchar(255) NOT NULL, `aID` int(5) NOT NULL, `gFInID` int(5) NOT NULL, `gFOutID` int(5) NOT NULL, `gFAInID` int(5) NOT NULL, `gFAOutID` int(5) NOT NULL, `eAlertID` int(5) NOT NULL, `dateTimer` datetime NOT NULL PRIMARY KEY (`sDataID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `tblGAlert` ( `gAlertID` int(11) NOT NULL AUTO_INCREMENT, `eID` int(5) NOT NULL, `aID` int(5) NOT NULL, `gEntryStatus` enum('Do','Ad','Ej') NOT NULL, `gDateTime` datetime NOT NULL, `gInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `gMessage` varchar(255) NOT NULL, PRIMARY KEY (`gAlertID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; CREATE TABLE IF NOT EXISTS `tblEAlert` ( `eAlertID` int(11) NOT NULL AUTO_INCREMENT, `sDataID` int(5) NOT NULL, `eID` int(5) NOT NULL, `aID` int(5) NOT NULL, `eDateTime` datetime NOT NULL, `eInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `eMessage` varchar(255) NOT NULL, PRIMARY KEY (`eAlertID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; Below is a sample data so if looking at tblEAlert for line 1 & 2 is linked to tblSData 1 and 3,4,5 and 6 is linked tblData 2 Sample data for tblSData 1,"A1",1122,100,102,1,2,1,2013-07-13 15:30:19 2,"A3",1122,104,103,3,4,3,2013-07-13 15:45:19 3,"A4",1122,105,108,5,6,7,2013-07-13 15:55:19 Sample data for tblEAlert 1,1,1,1122,2013-07-13 15:30:19,2013-07-13 15:30:19,"Alert 1" 2,1,2,1122,2013-07-13 15:30:19,2013-07-13 15:30:19,"Alert 2" 3,2,2,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 2" 4,2,3,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 3" 5,2,4,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 4" 6,2,5,1122,2013-07-13 15:45:19,2013-07-13 15:45:19,"Alert 5"

How to automatically backup MongoDB? Posted: 01 Sep 2013 07:51 AM PDT How to automatically backup MongoDB ? for example daily ps : daily , monthly

Stored procedure to handle empty sets Posted: 01 Sep 2013 04:12 AM PDT How can I make a stored procedure to print a specific message if an empty set wa returned by the query?

best ETL design to transfer transaction tables records into the data-warehouse Posted: 01 Sep 2013 11:19 AM PDT I have 2 type of tables to populate the data-warehouse with every day, lookup tables or configuration tables with few 100s records, and thats easy where i just truncate and refill the table. but for transaction tables, that have many records, i usually increment, that is i run the ETL daily to add yesterdays records. i have 2 problems that i face always when the job fails for any reason (i lose that Days transactions) when for any reason the job run twice or i run it twice (i get duplicates) now i am trying to design a way where i over come these 2 problems as well as am trying to develop the ETL in such a way that it can auto fix it self incase any of these events occur. i want it to check if there are missing days and run run the ETL for that day, and check if there are duplicates and delete them. below are ways i though of 1. i take in the last 5 days regardless, every day the ETL runs, deletes the last 5 days and refill. 2. i check the destination tables if they have missing dates in the last month and then i query the source with the missing days. keeping in mind that the source is a huge table in a production environment that i have to optimize my query to the maximum when requesting from it. thanks

Trying to create PostGIS table Posted: 31 Aug 2013 11:04 PM PDT I have create a database following the hompage http://postgis.refractions.net/documentation/manual-1.5/ch02.html#id2648455 and now I can't create a simple non-spatial table with I have use sudo su - postgres createdb lab psql lab CREATE TABLE lab_member I use \dt to get table I have now and there is just two table not I create List of relations Schema | Name | Type | Owner --------+------------------+-------+---------- public | geometry_columns | table | postgres public | spatial_ref_sys | table | postgres (2 rows) I want to COPY a csv table into Postgis database but i can't even create a empty table to do it? please help me to create a table!

MySQL Replication fails - commands received in wrong order? Posted: 31 Aug 2013 11:59 PM PDT The other day, I had this issue with one of our MySQL 5.1.60 master and 5.1.61 slave setups: Last_Error: Error 'Can't create table 'foobar.#sql-4b87_2' (errno: 150)' on query. Default database: 'foobar'. Query: 'alter table picture add index FKDD905 (pictureSource_id), add constraint FKDD905 foreign key (pictureSource_id) references picturesource (id)' Got to mention, that we just "host" the MySQL servers/setups; the content (including INDEXes and such) comes from the customer. We just run it. Okay, so here's what happened, as far as I understand it: On the master, the customer created the table "picturesource" On the master, they added the index "FKDD905" to the table "picture", referencing the field "id" from "picturesource" as a Foreign Key The slave tried to execute "alter table add index…". The slave tried to execute "add table picturesource…". In that order. On the slave, that failed. It failed, because the table picturesource did not exist on the slave. How can that happen? Why did the slave try to add the index BEFORE creating the table? I mean, on the master, the table MUST have existed before they added the key. Don't know if it's important, but binlog_format = MIXED. I manually added the (empty) table picturesource and could then restart the slave process (→ START SLAVE;). In a subsequent SHOW FULL PROCESSLIST\G, I then saw, that it did a copy to tmp table for the picture table; the picture table is huge (~7 GB). Can someone clarify?

How to access a SQL Server database from other computer connected to the same workgroup? Posted: 01 Sep 2013 12:46 AM PDT I have created a C# application which uses a SQL Server database. I have other computers connected to me and to each other in a workgroup. I have shared my C# application with others. When they open the application they get the error A network related or instance-specific error occured while establishing a connection to SQL Server. the server was not found or was not accessible But the application is working fine on my PC. The connection string I am using is Data Source=ASHISHPC1\SQLEXPRESS;Initial Catalog=ACW;User ID=ash159;Password=ashish159 which is stored in a .config file. The application is working fine on my PC. What must I do? I have enabled the TCP/IP in the server but the same error persists. Some change in connection string or something else? Please help.. Thank you..

my.cnf validation Posted: 01 Sep 2013 01:18 PM PDT We have moved our server from old 8 GB RAM Server to new 16 GB RAM server so that we could have better performance. The server is still consuming lot of MEMORY. The tables in the database are not designed for InnoDB. The DB physical file size is approximately 2.8 GB. my.cnf parameters are : [client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking #skip-bdb#niraj skip-external-locking key_buffer = 128M max_length_for_sort_data = 1024 max_tmp_tables = 32M table_cache = 64 max_allowed_packet = 128M sort_buffer_size = 32M read_buffer_size = 10M join_buffer_size = 256M read_rnd_buffer_size = 64M myisam_sort_buffer_size = 256M thread_cache_size = 64 query_cache_size = 256M thread_concurrency = 8 max_connect_errors = 100 log-bin=mysql-bin server-id = 1 set-variable = max_connections = 10000 set-variable = connect_timeout = 280 set-variable = interactive_timeout = 280 set-variable = net_read_timeout = 300 innodb_buffer_pool_size = 3G innodb_additional_mem_pool_size = 32M innodb_log_file_size = 768M innodb_log_buffer_size = 16M #innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 64M [mysql] no-auto-rehash [isamchk] key_buffer = 64M sort_buffer_size = 256k read_buffer = 256k write_buffer = 256k [myisamchk] key_buffer = 64M sort_buffer_size = 256M read_buffer = 256k write_buffer = 256k [mysqlhotcopy] interactive-timeout Please any one can validate my.cnf and suggest why taking much memory.

How do I migrate varbinary data to Netezza? Posted: 01 Sep 2013 03:18 PM PDT I got a warning message while migrating DDL from SQL Server to Netezza: Warning: [dbo].[spec_binarymessage].[blobdata] data type [varbinary] is not supported the target system and will be scripted as VARCHAR(16000). I'm wondering whether this kind of data conversion will cause some issues such as truncation of data etc.?

How can I get my linked server working using Windows authentication? Posted: 01 Sep 2013 04:18 PM PDT I'm trying to get a linked server to ServerA created on another server, ServerB using "Be made using the login's current security context" in a domain environment. I read that I'd need to have SPNs created for the service accounts that run SQL Server on each of the servers in order to enable Kerberos. I've done that and both now show the authentication scheme to be Kerberos, however, I'm still facing the error: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'". In Active Directory, I can see that the service account for ServerB is trusted for delegation to MSSQLSvc, but I noticed that the service account for ServerA does not yet have "trust this user for delegation" enabled. Does the target server also need to have that option enabled? Is anything else necessary to be able to use the current Windows login to use a linked server?

Connection to local SQL Server 2012 can be established from SSMS 2008 but not from SSMS 2012 Posted: 01 Sep 2013 05:18 PM PDT I have two local SQL Server instances running on my local machine. The first is SQL Server 2008 R2 Enterprise Edition (named MSSQLSERVER) and the 2nd is SQL Server 2012 Business Intelligence Edition. My problem is with SSMS 2012 which can connect to distant servers but not the local 2012 instance; I can however connect to this instance from SSMS 2008. The error message I get when trying to login is Login Failed. The login is from an untrusted domain and cannot be used with Windows Authentication. (Microsoft SQL Server, Error: 18452) I must point out that I don't have the necessary privileges to access SQL Server Configuration Manager (blocked by group policy). Any help would be appreciated.

Design consideration regarding state handling: how to store multiple, variable states for one entity Posted: 01 Sep 2013 10:18 AM PDT First I have to admit that I'm not a database professional neither are my colleagues. For a new project my colleagues and me came to a design question we couldn't really solve easily. And all the ideas had some disadvantages, so we could't figure out what's the best way to go. We have a main entity "Transaction" which should be processed by "ProcessingRules". The processing rules can be configured by the users in the Web application (each rule has a different execution scheduling. One might be running every hour, whereas the others might run nightly). Lets say Transaction gets 10'000 new records a day. This would lead to a DB design where I need to keep the State "Processed YES/NO" for each "ProcessingRule" and "Transaction". I thought the proper way is to have a relation table between the "ProcessingRoles" and the "Transaction". If no record present, the record has not been processed by this role yet. Transaction [0..1] ------ [*] TransactionRuleProcessing [*] ------- [0..1] ProcessingRule But when I think of the Query, this would lead into a WHERE NOT EXISTS (SELECT 1 FROM TransactionRuleProcessing...) query for the rule to identify new or unprocessed records. If we have a large amount of rows in Transaction, I think this will affect performance because the NOT EXISTS will have to join the whole table to the state table. If I'm not mistaken, this might cause a performance issue. On the other side, if we had only one state directly on the Transaction table, we could add an index and there would be no join between the large Transaction table and the state table. Question: Is it true that such a NOT EXISTS query would have to join the whole Transaction table with the TransactionRuleProcessing table to identify non existing (processed) rows? How could this affect performance of the database with a large Transaction table? What would be other recommendations to flag a record by a various amount of states? Any ideas very much appreciated

How to handle "many columns" in OLAP RDBMS Posted: 01 Sep 2013 02:18 PM PDT I have a fact that has around 1K different numerical attributes (i.e. columns). I would like to store this in to a column-oriented DB and perform cube analysis on it. I tried to design a star schema, but I'm not sure how to handle this many columns. Normalising it sounds wrong, but I can't just have flat columns either. The combination of attributes are also too diverse to have a simple dimension table for this, even if I'd reduce the numerical values into categories (ranges), which is an option. I thought about storing them as XML or JSON for each row, but that doesn't sound great either. If it helps, I'm planning to use Amazon's redshift for the DB. Note: We have strong preference for RedShift as it fits perfectly for at least other few operations we do on this data. Hence I want to avoid other technologies like HBase if possible.

Mysql DB server hits 400% CPU Posted: 01 Sep 2013 12:18 PM PDT I have been facing problem with my database server quite a month, Below are the observations that I see when it hits the top. - load average 40 to 50 - CPU % - 400% - idle % - 45% - wait % - 11% - vmstat procs r-> 14 and b-> 5 And then drains down within 5 minutes. And when I check the show processlist I see queries for DML and SQL are halted for some minutes. And it processes very slowly. Whereas each query are indexed appropriately and there will be no delay most of the time it returns less than 1 second for any query that are being executed to server the application. Mysql Version : 5.0.77 OS : CentOS 5.4 Mem: 16GB RAM (80% allocated to INNODB_BUFFER_POOL_SIZE) Database Size: 450 GB 16 Processor & 4 cores Not in per-table model. TPS ranges 50 to 200. Master to a Slave of the same configuration and seconds behind is 0. Below url shows show innodb status \G and show open tables; at the time spike. And this reduced within 5 minutes. Sometimes rare scenarios like once in two months I see the processes takes more than 5 to 8 hours to drain normal. All time I notice the load processor utilization and how it gradually splits its task and keep monitoring the process and innodb status and IO status. I need not do anything to bring it down. It servers the applications promptly and after some time it drains down to normal. Can you find anything suspicious in the url if any locks or OS waits any suggestion to initially triage with or what could have caused such spikes ? http://tinyurl.com/bm5v4pl -> "show innodb status \G and show open tables at DB spikes." Also there are some concerns that I would like to share with you. Recently I have seen a table that gets inserts only about 60 per second. It predominantly locks for a while waiting for auto-inc to get released. And thus subsequent inserts stays in the processlist tray. After a while the table gets IN_USE of about 30 threads and later I don't know what it makes to free them and clears the tray. (At this duration the load goes more than 15 for 5 minutes) Suppose if you say application functionality should be shapped to best suite the DB server to react. There are 3 to 5 functionalities each are independent entities in schema wise. Whenever I see the locks it gets affected to all other schemas too. Now what makes best fuzzy is the last one. I see slave keeps in synch with master with a delay of 0 second all time whereas slave has a single thread SQL operation that is passed from relay IO that which acts in FIFO model from the binary logs where Master had generated. When this single headed slave can keep the load less and have the operations upto-date, should the concurrent hits are really made to be concurrent for the functionalities which I assume making the possible IO locks in OS level. Can this be organized in application itself and keep the concurrent tenure density thinner?

What are the different ways to keep track of active and archived data? Posted: 01 Sep 2013 02:18 AM PDT I'm looking for different ways to keep track of both active and archived data so I can pro and con them. The system: I have a computer with a database on it. The database has several tables in it; one of which contains a list of users that can use the computer; and several tables for auditing (user 1 did this, user 2 did that, etc). This database is a slave of a master database in which a Content Management System is used to say, add a new user and see reports on what user did what. Example: As stated above, I have a table (lets call it users) that keeps track of all the users that are allowed to use the computer. As time goes by users will be added and removed. The problem is the audit tables keep track of a user ID so if the user is removed I lose the user information because the rows can't be joined. One idea I had was to use MySql's triggers so that if a user is added, an insert trigger is triggered and inserts a copy of the data to an 'archived' user table (lets call it users_archive). That way the computer can use users to determine if the user has permission to use it and reports can use users_archive for reports. This seems like the easiest and most simple way to do it, but I can't find any other ways via google search to see if there are any other ways to do something like this.

Database stuck in restoring and snapshot unavailable Posted: 01 Sep 2013 11:18 AM PDT I tried to restore my database from a snapshot. This usually took around a minute to complete the last couple of times. When I did it today, it didn't complete for around 30 minutes and the spid was in a suspended state. I stopped the query and now my database is stuck in restoring state and my snapshot is unavailable. Am I screwed? USE master; RESTORE DATABASE QA from DATABASE_SNAPSHOT = 'QA_Snap_Testing'; GO

Multiple database servers for performance vs failover Posted: 01 Sep 2013 07:18 PM PDT If I have two database servers, and I am looking for maximum performance vs high-availability, what configuration would be best? Assuming the architecture is two load-balanced web/app servers in front of two db servers, will I be able to have both db servers active with synced data, with web1 to db1, web2 to db2 setup? Is this active/active? I'm also aware that the two db servers can have their own schema to manually 'split' the db needs of the app. In this case daily backups would be fine. We don't have 'mission critical data.' If it matters, we have traffic around 3,000-7,000 simultaneous users.

Access 2003 (SQL Server 2000) migration to SQL Azure Posted: 01 Sep 2013 08:18 AM PDT As my old Windows 2003 RAID controller started throwing errors, I am seriously thinking about switching current Access 2003 (adp/ADO) clients to use a Windows SQL Azure solution, in place of current SQL Server 2000. Does anybody knows if this is a feasable/painless operation?

Truncate table is taking too long in PostgreSQL Posted: 01 Sep 2013 05:57 AM PDT I have many databases on the same server, all with same templates. When I execute truncate command on exceptions table in each database, it works fine and executes immediately but on database named db_edr_s1 the same truncate command on exceptions table is taking too much, approx. 5 minutes. The version is 9.1. Any other info needed?

InnoDB - How to get top locked tables and rows which are locked Posted: 01 Sep 2013 03:18 AM PDT I was searching for a tool OR query which can give me top locked tables and which particular row is locked, Is it possible to get ?

PostGIS: Remove stale/obsolete geometry columns from the geometry_columns table [on hold] Posted: 31 Aug 2013 10:22 PM PDT I understand that the function Populate_Geometry_Columns() inserts records in the geometry_columns table for geometry columns that are not yet listed there. However, an invocation of Probe_Geometry_Columns() reports: select probe_geometry_columns(); probe_geometry_columns --------------------------------------------------- probed:1086 inserted:0 conflicts:1086 stale:53612 (1 row) This is after a call to Populate_Geometry_Columns(). Is there an easy/documented way how to get rid of the conflicts and the stale entries? EDIT: As per Darrell Fuhriman's answer on GIS.SX, there are three ways: truncate the geometry_columns table and rerun SELECT probe_geometry_columns() Run a custom script that operates on PostGis's internal tables Upgrade to PostGis 2.0 or later

PgAdmin III - How to connect to database when password is empty? Posted: 01 Sep 2013 07:00 PM PDT I have installed PostgreSQL 9.1 on my PC (Win 7). I have a small Java application connecting successfully to it with login=sa and password="". The connection works. However, it is refused from PgAdmin III itself. I get: Error connecting to the server: fe_sendauth: no password supplied How do I connect to my database from PgAdmin III with an empty password? EDIT This is just a test, not production code.

You are subscribed to email updates from Recent Questions - Database Administrators Stack Exchange To stop receiving these emails, you may unsubscribe now.	Email delivery powered by Google
Google Inc., 20 West Kinzie, Chicago IL USA 60610