Thursday, May 2, 2013

[SQL Server 2008 issues] Outer apply random value

[SQL Server 2008 issues] Outer apply random value

Outer apply random value

Posted: 01 May 2013 07:15 PM PDT

Hi, I'm having problems getting a random value with outer apply and newid().Here's the DDL: [url=http://sqlfiddle.com/#!6/47f38/2]SQLFiddle[/url]The wanted result would be something like this:Volvo RedFord WhiteBMW RedThanks for your time

Login audit

Posted: 28 Apr 2013 08:27 PM PDT

Hi,I've been asked to produce a report on who has accessed a particular database on an SQL server we have. The system (a third party with no internal auditing) uses sql authentication. Logging on the server is set to record failed and successful login attempts. I can read the log files using xp_readerrorlog but that only gives the the time and name of the user and not the database.Any one got any ideas on how I can do this report?Thanks.

Migration from SQL server 2005 to SQL server 2008

Posted: 01 May 2013 05:53 PM PDT

I am at my client location and they have assigned me a migration task from SQL server 2005 to sql server 2008.I should first do it on the test server and I have never done this before. Could someone please help me how and what to start with. What is the procedure for migration?

Tracking running queries

Posted: 30 Apr 2013 08:31 PM PDT

Hi allIn one of the daily emails (quite a while ago), there was a method for getting server performance and tracking what queries were running, etc.I can't find the link any more, so if anyone has it could they send me a copy please?

Error while taking backup for a user not having sysadmin permission

Posted: 24 Apr 2013 12:52 AM PDT

I am accessing my sql server 2008 r2 express server using SSMS. I have about 5 logins. For a particular login I have all server roles other than sysadmin. It is a sql login(not windows authentication) In user roles it has all permission for almost all database.For some database it has only datareader.Now when i try to take backup of database using SSMS and when try to select backup destination by clicking button i get error like"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\BackupCannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists. If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box."Even though I have db_owner and all permissions i am getting this error. But if I select the path manually in the file name field then it allows to take backup without any error.So why does it happen? I want this user to take backup without these error messages.(But i can't give sysadmin for the user).How can I solve it?(I already some article stating that use sql query and sqlcmd to take backup.but i want it to work using SSMS itself) [url=http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/a0ada2bb-f66e-4af1-a1d7-3057bd5a7c53]Also see my detailed discussion on this problem in msdn forum by me here[/url]

Question about MAX_QUEUE_READERS in Service Broker.

Posted: 01 May 2013 07:52 AM PDT

Guys,I have a Question about MAX_QUEUE_READERS option in Service Broker.In a high transaction environment where inserts/updates/deletes are very high, would it be affected byhaving MAX_QUEUE_READERS value higher than 1 ?What happens when a INSERT statement is issued and then Immediately DELETE is issued for the same recordand because of MAX_QUEUE_READERS being 2 or 3 ...DELETE gets processed before INSERT (Parallel Processing) ?OR I am not correct with my understanding of MAX_QUEUE_READERS ?I have a Production Issue where sometimes Queue backs up because of high User Activity and we have MAX_QUEUE_READERS set as 1.I am thinking of increasing it to may be 5, but would like to know ,that we don't end up with Orphan Records dueto multi-threaded parallel process created by MAX_QUEUE_READERS.Thanks.

Percent to Total - Customer Sales Penetration

Posted: 01 May 2013 01:49 AM PDT

This is related to a previous post, but I didn't have all the information. Now, I'm including the selects to see if I can find a solution, or need to go in a completely different direction. This also relates to SSRS, which is what these queries are built to run this report. I'm hoping to come to some solution, so all suggestions are appreciated! = )I have a somewhat simple query (bear with me) that management has asked for me to add a Customer Penetration field in their report. My problem is not the formula, but how to work with the query that I have now. I don't know if I can paste the entire sql, but I'm thinking there must be some sort of partition/group by function to get the total sales into the main query. I can do the formula in the report once I have the data. I can run the two queries separately, but my other problem is that I need to be able to join the two to be sure it's counting the same customers, not just total sales. One query gives customer and product sales based on several parameters entered. The other query is essentially the same and provides just total customer sales by Branch and Business Unit. There are too many rows to work with in the main query, so that may be part of the problem as well. Any help would be appreciated. This is the main query that I started with:--DECLARE @TransDate date = '3/1/2013'--DECLARE @CompanyID nvarchar(4) = '003'--DECLARE @BU nvarchar(20) = 'BROADLINE'--DECLARE @PC nvarchar(20) = 'ALL'--DECLARE @Family nvarchar(40) = '06 Fzn Groceries And Other'--DECLARE @ProdLine nvarchar(40) = '59 Fzn Breads & Doughs'--DECLARE @ProdGrp nvarchar(40) = 'ALL'SELECTInvoiceDate as MonthDate,Branch,BusinessUnit ,CustID as CustNbr,CustName ,Fam_ID as ProdFamily ,Lin_ID as ProdLine,Grp_ID as ProdGroup,Prod_ID as ProdNbr,NetSalesFROM( SELECT CONVERT(DATETIME,DATEADD(mm, DATEDIFF(mm,0,salestrans.LDayDay), 0),101) AS InvoiceDate,salestrans.Branch ,salestrans.BusinessUnit,salestrans.CustID,customer.CustName,Product.Fam_ID,Product.Lin_ID,Product.Grp_ID,Product.Prod_ID,SUM (ISNULL (CASE WHEN salestrans.pctype IN ('S','A') THEN isnull(salestrans.extprice,0) ELSE 0 END, 0)) as NetSalesFROM bi.salestrans salestransleft outer join bi.star2sales star2sales on (salestrans.Branch = star2sales.Branch and salestrans.InvNo = star2sales.Invno and salestrans.InvDtl = star2sales.Invdtl)inner join abcp.Customer customer on (salestrans.Branch = customer.CompanyID and salestrans.CustID = customer.CustomerID)left join bi.Producton (salestrans.ProdWhs = product.Prod_Whs and salestrans.ProdID = Product.Prod_ID)WHEREsalestrans.LDayDay BETWEEN dateadd(month,datediff(month,0,@TransDate)-0,0) AND dateadd(ms,-3,DATEADD(mm, DATEDIFF(month,0, @TransDate)+1, 0))AND Customer.[Effective Date] <= @TransDateAND (Customer.EndDate > @TransDate OR Customer.EndDate IS NULL)AND Customer.[Excluded?] = 'NO' AND (Customer.CompanyID IN (@CompanyID) --, @CompanyID2) OR 'ALL' IN (@CompanyID) ) --, @CompanyID2))and Product.Prod_Whs in ('008','003','009','020')and salestrans.PcID not in ('65','95')GROUP BY CONVERT(DATETIME,DATEADD(mm, DATEDIFF(mm,0,salestrans.LDayDay), 0),101),salestrans.Branch,salestrans.BusinessUnit,salestrans.CustID,customer.CustName,Product.Fam_ID,Product.Lin_ID,Product.Grp_ID,Product.Prod_ID) as CustVendProdGrpSalesWHERE (PCID IN (@PC) OR 'ALL' IN (@PC))AND (BusinessUnit IN (@BU) OR 'ALL' IN (@BU))AND (Fam_ID IN (@Family) or 'ALL' IN (@Family))AND (Lin_ID IN (@ProdLine) OR 'ALL' IN (@ProdLine))AND (Grp_ID IN (@ProdGrp) OR 'ALL' IN (@ProdGrp));And then I tried to add this column from the query below:,case when Row_Number() over (partition by salestrans.Branch, salestrans.CustId, BusinessUnit,Product.Fam_ID,Product.Lin_ID,Product.Grp_ID--,Product.Prod_IDorder by Product.Grp_ID) = 1 then sum(isnull(TotalCMNetSales,0)) else 0 end as TotalsalestestThis is the total customer sales query. I've tried doing an inner subselect several ways, and I was able to get some results the first time but it was not summing the customer sales correctly. Then, I tried a few other things, but it was "timing out" using up too much memory.( SELECTInvoiceDate as MonthDate,Branch,BusinessUnit,CustID ,SUM(TotalCMNetSales) as TotalCMNetSalesFROM ( SELECT CONVERT(DATETIME,DATEADD(mm, DATEDIFF(mm,0,salestrans.LDayDay), 0),101) AS InvoiceDate,salestrans.Branch ,salestrans.BusinessUnit,salestrans.CustID--,salestrans.ProdID--,Product.Fam_ID--,Product.Lin_ID--,Product.Grp_ID,SUM (ISNULL (CASE WHEN DATEADD(m,DATEDIFF(m,0,salestrans.LDayDay),0) = dateadd(month,datediff(month,0,@TransDate)-0,0)and salestrans.pctype IN ('S','A') THEN isnull(salestrans.extprice,0) ELSE 0 END, 0)) as TotalCMNetSalesFROM bi.salestrans salestransinner join abcp.Customer customer on (salestrans.Branch = customer.CompanyID and salestrans.CustID = customer.CustomerID)left join bi.Producton (salestrans.ProdWhs = product.Prod_Whs and salestrans.ProdID = Product.Prod_ID)WHEREsalestrans.LDayDay BETWEEN dateadd(month,datediff(month,0,@TransDate)-0,0) AND dateadd(ms,-3,DATEADD(mm, DATEDIFF(month,0, @TransDate)+1, 0))AND Customer.[Effective Date] <= @TransDateAND (Customer.EndDate > @TransDate OR Customer.EndDate IS NULL)AND Customer.[Excluded?] = 'NO' AND (Customer.CompanyID IN (@CompanyID) --, @CompanyID2) OR 'ALL' IN (@CompanyID) ) --, @CompanyID2))and Product.Prod_Whs in ('008','003','009','020')and salestrans.PcID not in ('65','95')GROUP BY CONVERT(DATETIME,DATEADD(mm, DATEDIFF(mm,0,salestrans.LDayDay), 0),101),salestrans.Branch,salestrans.BusinessUnit,salestrans.CustID--,salestrans.ProdID--,customer.CustName--,Product.Fam_ID--,Product.Lin_ID--,Product.Grp_ID) as CustSalesWHERE (BusinessUnit IN (@BU) OR 'ALL' IN (@BU))--AND (Fam_ID NOT IN (@Family) or 'ALL' IN (@Family))--AND (Lin_ID NOT IN (@ProdLine) OR 'ALL' IN (@ProdLine))--AND (Grp_ID NOT IN (@ProdGrp) OR 'ALL' IN (@ProdGrp))GROUP BY InvoiceDate ,Branch,BusinessUnit,CustID --,Fam_ID--,Lin_ID--,Grp_ID) as TotalCustSales

SQL Server 2008 cluster node going down unexpectedly

Posted: 01 May 2013 04:17 AM PDT

Last night our primary SQL Server node went down and failed over to the secondary node.I was actually on the server at the moment having just launched a trace to troubleshoot a particular query when suddenly I lost all connectivity to SQL Server.Our setup is:Microsoft SQL Server 2008 R2 (SP1) - 10.50.2796.0 (X64) 2 Node Active/Passive Cluster.Here is what I found in the Administrative Log :[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed[sqsrvres] printODBCError: sqlstate = HYT00; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]Query timeout expired[sqsrvres] CheckQueryProcessorAlive: sqlexecdirect failed[sqsrvres] printODBCError: sqlstate = 08S01; native error = 0; message = [Microsoft][SQL Server Native Client 10.0]The connection is no longer usable because the server failed to respond to a command cancellation for a previously executed statement in a timely manner. Possible causes include application deadlocks or the server being overloaded. Open a new connection and re-try the operation.We have SQL Server and SQL Server agent are running under designated network accounts.SQL Server Browser is running under a Local account.Never had that issue before in 2 years we've been using the server.The SQL Server error log did not reveal much. The very last event in the error log before the node went down is:2013-04-30 20:06:48.970 spid133 SQL Trace ID 2 was started by login "sa". Thank you for your help

SQL Server 2008 R2 slow query performance to Oracle 11g using Openquery

Posted: 01 May 2013 04:17 AM PDT

Gang,I'm completely perplexed on this one. I am running a query against an Oracle 11g database. It looks something like this:select * from openquery(oradb, "select col1, col2, col3....col15 from ora.Table where rownum <= 100000")The query is going against a linked server using the Oracle 11.2 client download. The purpose of the query is to pull in the first 100,000 records from a particular table. The data is about 15MB worth of data. When I run the query on two of my SQL Servers - let's call them A1 and A2 and both running 2008 R2, the query takes about 20 seconds. Here is where it gets wierd.When I run it on my new server - call it B1 also running 2008 R2, it takes EXACTLY 3 minutes and 36 seconds, everytime. The query executes immediately but the data transfer is incredibly slow.We monitored the server and the network performance is only about 40,000 B/sec. If we run the query in SQL Plus (Oracle client), it runs in 20 seconds - just like to other SQL Servers. THe network performance is well over 500,000 B/sec. That level of performance is the same on servers A1 and A2.I'm leaning that SQL Server is throttling this query for some reason. My networking and Oracle teams looked at it and said Oracle & Network are not the issue - I agree since the SQL Plus query runs in 20 seconds.[b]Has anyone else seen this on SQL Server? Are there some networking settings in SQL Server that would cause it to not spike out the network?[/b]I'm completely lost on this one.Dennis

"SET STATISTICS TIME" and "SET STATISTICS IO" questions

Posted: 29 Apr 2013 04:56 PM PDT

1) As I understood from here[url=http://www.sqlservercentral.com/articles/Performance+Tuning/measuringperformance/1323/][/url]"elapsed time" is the total time of statement's execution, but how than it can be less then CPU time, it should includes it?[quote]DBCC execution completed. If DBCC printed error messages, contact your system administrator. SQL Server Execution Times: CPU time = 16 ms, elapsed time = 5 ms.[/quote]2) I receive this message for a statement executed right after DBCC DROPCLEANBUFFERS, why 193 and 203, where from it reads remaining 10 pages, I've just cleaned the cache?[quote]Table 'InsertTest'. Scan count 1, logical reads 203, physical reads 0, read-ahead reads 193, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.[/quote]

?? on Job Steps and how they work

Posted: 01 May 2013 04:33 AM PDT

Hi,I'm trying to setup a job w 2 stepsstep1 run and emails a report via SSRS, I just created a subscription in the front end copied the step out of the job it created.The report sets a flag on a field with an 'x' to know which records to report onstep2 is where I get lost...step2 updates the reported records with a 'y' problem is when the report is emailed the report is blank, I assume the report doesn't get emailed until step 2 is completed?If so, is there away around it? maybe 2 jobs, job2 being dependent on job1 being sucessful?here are my steps...STEP1 exec [ReportServer].dbo.AddEvent @EventType='TimedSubscription', @EventData='89882d84-9810-4cdf-9e13-d74035f3ddcd'STEP2 exec dbo.xmL_test_UPDATEThanksJoe

Sql server 2008 installation problem( must specify valid information for parsing the string)

Posted: 30 Apr 2013 11:15 PM PDT

Hello,When i am trying to install sql server 2008 using iso file , it shows an error message " must specify valid information for parsing the string " . it blocked me to continue. Any idea?? Thanks in advance

TFS Upgrade from 2005 to 2010

Posted: 01 May 2013 02:18 AM PDT

Hi, recently I have been assigned to upgrade our TFS 2005 to TFS 2010. Here is what we are looking to accomplish. 1- Upgrade TFS to 20102- Move all the projects under One Project team. Our current environment has 20 different projects and our approach is to put all those projects under one TEAM project. Can Anybody put a light on technical aspects of how to put current projects into one team projects? what kind of issues we might face and if someone has already done it successfully, it will be helpful to mimic that into our approach?If anyone need more technical detail of our current TFS 2005 environment, please feel free to ask.

Create SSIS package in SQL SERVER 2012 for SQL SERVER 2008

Posted: 06 Jun 2012 01:40 AM PDT

Is it possible to develop SSIS packages in SQL SERVER 20012 for deploying in SLQ SERVER 2008?

Lazy Writes / sec

Posted: 30 Apr 2013 11:31 PM PDT

on one of my servers i checked the Lazy Writes / sec and i get a very very high value of 2635802.everywhere i read that this should be < 20is this a incremental value of last server start and i should get this value on intervals and find the difference or what?!this server has been online and never restarted for 18 months now. Meanning that everything works just fine but this is a counter that i had never looked at and wondering if this is some kinda memory problem.BTW its sql server 2008thanx
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog