Tuesday, April 30, 2013

[SQL Server 2008 issues] "SET STATISTICS TIME" and "SET STATISTICS IO" questions

[SQL Server 2008 issues] "SET STATISTICS TIME" and "SET STATISTICS IO" questions

"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]

SQL Server Activity Monitor

Posted: 29 Apr 2013 05:25 PM PDT

Good Day . We are running SQL Server 2008R2 as a named instance on a virtual Server . I am able to view the Activity Monitor except for the % Processor time that is grayed out. I was granted Sysadmin Privileges in SQL Server as well as View Server State Permissions . Any ideas ? There is an office policy that only the server administrators may have Admin Privileges.

MDX Query

Posted: 29 Apr 2013 04:03 PM PDT

Hi,While running the MDX query to return prevoius month data, i am getting getting Null for previous month data.Withmember [measures].[T1_pytd_sales]assum(parallelperiod([Time].[Fiscal].[Year],1,[Time].[Fiscal].currentmember),[Measures].[Spend])member [measures].[T1_ytd_sales] assum([Time].[Fiscal].currentmember,[Measures].[Spend])member [measures].[DiffTier1] as'[measures].[T1_ytd_sales]-[measures].[T1_pytd_sales]'seelct (descendants({Closingperiod([Time].[Fiscal].[Year])},[Time].[Fiscal].[Month])*{[T1_ytd_sales],[measures].[T1_pytd_sales],[measures].[DiffTier1]}) on columnsfrom cubeActually we have two time dimesions. Its working fine for one dimension, but its getting Null for other time dimension.For T1_pytd_sales, i am getting NULL for Month wise even for yearly also.Can anyone what could be the reason?Thanks in advance.

Moving multiple similar access databases to SQL

Posted: 29 Apr 2013 08:35 AM PDT

I'm planning on moving around 4 or 5 MS Access databases to SQL Server and need some insight on what might be the best approach in regards to the following:First what the issue is: I have a client database which has pretty generic information about some people (Jobs, referral info, etc..) I will refer to this as my master database. These other databases (let's call them program databases) all have similar information, but are not connected to the master. :angry:[b]I'm trying to figure out if moving them to 1 database is the answer, or to each their own database on SQL.[/b]The master will have all things client/job/referral related to it and will definitely need to be available for read/write for all the program databases.Each time a client enrolls into a program, they will have to be in the master first and then they would be available for use for the programs.These program databases are for grant based programs and can easily come and go, so there is a good chance that at some time, the tables or databases will no longer be used once the grant expires or some other reason. One of the other gotcha's is that I'm a creature of habit and each one of the program databases has a couple tables that are named the same. That being said, I just need a little direction from anyone that may have some pros and cons on separating these out by databases or just by tables. If more specifics are needed let me know, I didn't want to get too detailed and make my question convoluted.Thanks,Jeff

SSIS 2008 - renaming files

Posted: 29 Apr 2013 03:28 AM PDT

Hi,I have placed a file system task inside foreach loop container and the operator is rename file. I'm wondering if it's possible to find all files (regardless of file extensions) that contain "circle" and then replace it with "square"? If so, how I can accomplish this?Thanks!

SQL QUERY to find maximum and minimum column value of a primay key column in a year

Posted: 29 Apr 2013 09:19 AM PDT

Hi Experts,C_KEY ENTER_DATE1 2008-12-31 00:00:00.0002 2008-12-31 00:00:00.0003 2008-12-31 00:00:00.0004 2009-12-31 00:00:00.0005 2009-12-31 00:00:00.0006 2009-12-31 00:00:00.0007 2010-12-31 00:00:00.000 8 2010-12-31 00:00:00.0009 2010-12-31 00:00:00.00010 2010-12-31 00:00:00.000can someone help me in finding min and max c_key for all years.for EG:YEAR Min C KEY MAX CKEY2008 1 32009 4 62010 7 10

Join Issues - Customer Penetration project

Posted: 29 Apr 2013 01:48 PM PDT

Maybe I'm just trying to get confirmation that this is possible, but essentially I'm trying to run the main query for a month or more date range and join to another dataset (basically the same data) but with rolled up sales for a Total Sales # by Customer (no parameters). I know, you're thinking why not eliminate the join and just start with the wider dataset?? Well, yes, and I might still need to do that, but because of the way several queries have already been built in SSRS, and management is asking after the fact to add a Customer Penetration field to the report, I'm trying to add this join to what is already built.So, I will try to summarize how I think the select should be built (please be gentle, I'm still fairly new at SSIS). In order to get customer penetration the formula is customer sales on one or more product group(s)*100 / total customer sales (same customers). So this is where I have to join these specific customers.DECLARE and variables...selectbranch,customer,product group,Monthly Sales ,Monthly Total Sales,Customer Penetration calcfrom (select branch, customer, product group, etc.... from salestransinner join to date...inner join to customer...left join to product...left outer join to billbacks...full outer join (to total sales for customer penetration) (select branch, customer, total sales, etc ) as TotalCustSales on (TotalCustSales.branch = salestrans.branch and TotalCustSales.CustID = salestrans.CustID and TotalCustSales.Branch = customer.CompanyID and TotalCustSales.CustID = customer.CustomerID) where salestrans conditions, etc...group by...) as CustProdGrpSaleswhere Variable/parameter conditions...Keep in mind that the main query and the customer penetration query run fine separately, but when joined I'm getting a divisor=0 error, which tells me that the total sales is not coming out correctly. Again, the total sales select is essentially the same as the main query, but just stripped down to get customer sales totals. But I'm also getting an error at the end of the main select outside of the parentheses where I called it ) as CustProdGrpSales. It says something about "No column name was specified for column 29 of 'CustVendProdGrpSales' ". Haven't seen this one before, but it just means my syntax is off somewhere. This is the only line that has red squiggles, so I can't tell where the error lies. However, maybe someone has a better suggestion on how to build this? Thanks in advance!

Index maintenance increase log size

Posted: 28 Apr 2013 10:39 PM PDT

In SQL Server 2008R2, we are running an Index maintenance job on Sunday.It rebuilds/reorganize the indexes ONLINE on a database in a loop. The max size in Indexes is 16GB , but my log size grows upto 160 GB.There are around 200 indexes. I understand that ONLINE rebuild causes log size to grow but I also think that once the rebuild of one index is complete, the log space should be available for reuse but that doesn't seem to be happening. Can it be a reason that since rebuild happens in a loop and loop runs fast , so essentially there are several rebuilds happening in parallel? Will putting a waitperiod of ,say 10 sec, after each rebuild help?

Reporting in records in the right order

Posted: 29 Apr 2013 12:21 AM PDT

hi guysI have some data from a system upgrade where stuff didn't get the right sequence numbers [code="sql"]create #test( ref int, oldvalue nvarchar, newvalue nvarchar, seqno int)insert into #test (ref,oldvalue,newvalue,seqno) values (100,null,'A',1)insert into #test (ref,oldvalue,newvalue,seqno) values (100,'B','C',2)insert into #test (ref,oldvalue,newvalue,seqno) values (100,'A','B',3)insert into #test (ref,oldvalue,newvalue,seqno) values (100,'B','X',4)insert into #test (ref,oldvalue,newvalue,seqno) values (100,'C','B',5)[/code]the correct order for these records is actually 1,3,2,5,4 (oldvalue = newvalue from previous record). The challenge is that from seqno 3 you have two potential choices (2 or 4) but only the combination above collects all of the records.I can't see how to do this without resorting to cursors or other RBAR solutions. any help would be appreciated. The dataset is approximately 900K records spread over 250K ref#s

TDE and Page-Level Compression

Posted: 29 Apr 2013 06:03 AM PDT

I cannot find any information specifically on this at this point, but I was curious if anyone knew if removing page-level compression on an encrypted database will decrypt it? We had a database decrypt this weekend after a deployment, and the only think that logically makes sense to me (since there was no specific decrypt command issued) is that removing page-level compression required the database to be decrypted. Any thoughts?

Database Engine vs. UI SQL Statements

Posted: 29 Apr 2013 04:43 AM PDT

I recently had a conversation with a friend of mine who is an Oracle developer. I was discussing how we develop in MS SQL and that we create stored procedures to handle all of our data manipulation. He immediately made a face at me and stated "Why in the world would you do that?!?" His claim is that it is far better to pass SQL statements directly from the code reducing the MS SQL server workload.I stopped for a minute to think, could this be true? Have I been developing the wrong way for all of these years? Unfortunately, I am not fast enough to respond but after some thought I am not sure I would change a thing since I do not want my UI developers to have to understand the structure of my database. In addition, since I have customer sites hosting their own copy of our software. If I make a SQL change, I just send a script and all is good. My friend's way, I would have to send out a completely new UI to all of my customers and go through a full implementation process.Can anyone shed some light on best practices for coding a UI that interacts with MS SQL and whether or not one should use stored procedures for the SELECT/INSERT/UPDATE/DELETE statements? I just can't believe that it is good practice to code your SQL code in application and not use the database. After all the database engine is going to have to run the statement that is passed to it and their is no precompiled execution plan for that SQL string.

Need to Decrypt an Encrypted column that is selected in a view

Posted: 29 Apr 2013 05:50 AM PDT

Hi,I have a document imaging application that connects to my database through a view and it can only use the view. One of the table fields selected in the view has now been encrypted for PCI and HIPAA compliance. I need to be able to decrypt this field for the view but due to MS design, the view does not let me open the key. I've also read where it is not recommended to use the openrowset to execute a stored procedure in the view. Does anyone have a suggestion to accomplish this task? Thanks in advance.

SQL Server outof space question.

Posted: 29 Apr 2013 12:52 AM PDT

Hello Experts,One of our clients server ran into this issue. The server has both application and SQL Server installed on it. The hard drive is completely full and we don't know what is causing it to be full but because it is full, we can't login to the SQL Server because there is not enough space to write to the transaction log. We can't stop the MSSQLSERVER service because there's no space and there's not anything I know of clearing to be able to clear enough space so we can delete the transaction log. What else could I do other than attaching another HDD and move/add data/log file to the new volume?Thanks in advance!

Huge performance impact after migration from SQL 2005 to SQL 2008 R2 - Urgent

Posted: 29 Apr 2013 03:13 AM PDT

We recently migrated our database from SQL 2005 to SQL 2008 R2. Application is performing pretty fast and as expected. However when any queries are called from linked servers then it just hangs. Select top 5 * from linked server takes forever and when killed takes forever to roll back. This impacts the whole system big time. Suggestions?

Query re-write application

Posted: 29 Apr 2013 03:32 AM PDT

Just wondering if anyone knows of a application that can intercept a certain query on the fly and re-write it? We have a troublesome query in our application that uses an INNER LOOP JOIN that the vendor has not been able to correct. It is dynamic and embedded deeply in their code. I just need to remove the LOOP keyword and the query flies. Thank you!

SQL Client Access Licence requirements

Posted: 14 Feb 2012 04:55 AM PST

HiSQL 2008R2 Std Ed.We are having an internal debate on what does (does not) require a SQL CAL for the end user.We only use SQL for reporting..data being sourced from external (non SQL) dbsThe majority of our users only require static non updatable reports and therefore, where we can, we would like to reduce our CAL licensing costsIf there are any licensing "experts" prepared to comment on what we believe below, then it will be appreciatedScenariosMS Excel pivot table accessing a SSAS Cube....CAL requiredMS Excel directly querying SQL via MS Query...CAL requiredMS Access ADP linked to SQL...CAL required User accessing a static Excel file created thro TSQL/SSIS .....CAL not requireduser receiving an email generated via TSQL/SSIS ....CAL not required.Any access to SSRS...CAL required.Thanks.

asp_net user personalization copy

Posted: 29 Apr 2013 02:08 AM PDT

I have a web site running with asp_net personalization for some web parts on the page. I am wondering if it is possible to take the pagesettings from one user and copy them over in the aspnet_PersonalizationPerUser table for a new user so that the page configuration would be the same... I cant seem to get it to work.thanksJason

Msg 468, Level 16, State 9, Procedure "procedurename", Line 129 Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Posted: 12 Apr 2012 01:51 AM PDT

Dear All,I have a stored procedure and I keep getting the following error message:Msg 468, Level 16, State 9, Procedure "procedurename", Line 129Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.I looked at the Collation for the Database and it is SQL_Latin1_General_CP1_CI_AS but I don't know what else I need to do to get it working please?Thank you in advance!

How to Determine New SQL Box Specs for Migration from 2005 to 2008

Posted: 29 Apr 2013 01:01 AM PDT

So I've been researching this, but can't get a clear how to on this. I just joined a new job and one of my first projects is to migrate a 2005 sql server to 2008. I need to give specs to my infrastructure guy for new environment. We have an application running on Windows 2003 Box (4 processors, 4GB of ram) with SQL 2005 SP4. We are running on VM Ware with Net App. We need to get to Windows 2008 with SQL Server 2008 R2.How do I determine Specs for the New Box. For example, how many CPU (and their speed), how much RAM, etc.Is there a tutorial you guys can point me to?

Order issues

Posted: 29 Apr 2013 12:13 AM PDT

Hello!I am importing data from a source system running IBM iSeries. I use OPENQUERY to query the source system.The source system is a bit peculiar, so in table tblModelsAccount I have the following fields (my example is simplified)AccountNo ModelCode12345 A12345 BSo which one is the current ModelCode for AccountNo 12345? Well, the not so obvious business rule is that the first one is always the current. That means A.So I import all rows to a table I have in my SQL Server DB. Like this INSERT INTO MYDATABASE.dbo.ModelAccounts(AccountNo, ModelCode)SELECT * FROM OPENQUERY(SourceDSN,'SELECT AccountNo, ModelCode FROM tblModelsAccount ')Then I do a Select from my import tableSELECT * FROM MYDATABASE.dbo.ModelAccountsInteresting enough, the rows have shifted places.AccountNo ModelCode12345 B12345 AObviously it is hard to use the rule "first one is the current", when this happens. But exactly what is deciding the order of rows in the SQL Server table when doing an insert like this? I would have expected the rows to be ordered like in the source system.

Truncate Log Script

Posted: 28 Apr 2013 10:12 PM PDT

Hi Buddies,I got a Script for Truncate Logs...Please SUGGEST how to make this runnable***************************************************************DECLARE c CURSOR FOR SELECT database_id, name, recovery_model_desc FROM sys.databasesDECLARE @dbname VARCHAR(1024); DECLARE @rmod VARCHAR(1024); DECLARE @id INT; DECLARE @lfile VARCHAR(1024); OPEN c; FETCH NEXT FROM c INTO @id, @dbname, @rmod; WHILE @@FETCH_STATUS = 0 BEGINIF @rmod = ''FULL''BEGINSET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1)PRINT @lfileEXEC(''alter database ['' + @dbname + ''] set recovery simple '')EXEC(''USE [''+@dbname+'']; DBCC SHRINKFILE([''+@lfile+''], 1)'')EXEC(''ALTER DATABASE ['' + @dbname + ''] SET RECOVERY FULL '')END ELSEIF @rmod = ''SIMPLE''BEGINSET @lfile = (SELECT name FROM sys.master_files WHERE database_id = @id AND type=1) PRINT @lfileEXEC(''USE [''+@dbname+'']; DBCC SHRINKFILE([''+@lfile+''], 1)'')ENDFETCH NEXT FROM c INTO @id, @dbname,@rmod; END; CLOSE c DEALLOCATE c GO***************************************************************EXECUTING THIS ERROR COMES---:w00t:Msg 156, Level 15, State 1, Line 14Incorrect syntax near the keyword 'FULL'.Msg 156, Level 15, State 1, Line 18Incorrect syntax near the keyword 'alter'.Msg 102, Level 15, State 1, Line 18Incorrect syntax near ''.Msg 156, Level 15, State 1, Line 19Incorrect syntax near the keyword 'USE'.Msg 911, Level 16, State 1, Line 19Could not locate entry in sysdatabases for database '''+@dbname+'''. No entry found with that name. Make sure that the name is entered correctly.

Precedence Constraint editor

Posted: 28 Apr 2013 11:22 PM PDT

Hi any one explain how the precedence constraints works lfor logical Or and logical And. and in values what is success,failure and completion

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.

Mega Problems installing SQL Server 2008 R2 Developer

Posted: 28 Apr 2013 09:50 PM PDT

I normally install SQL Server 2008 R2 Developer without any problems, for example on an unupdated version of Windows XP. I have also installed it on Windows 7 which may have had the initial 500-600MB or so of updates although I am not sure if these help with a new installation of SQL Server 2008 R2 Developer. I think that version may have had Visual Studio 2010 or 2008 on it first, although I can't remember the sequence of installs.I had a problem of not being able to go into SSIS a few days ago. This may have been something to do with SQL Server 2012 RC being uninstalled and problems with shared .dll's. After much irritation I did a clean install of Windows 7 SP1.I then tried to install SQL Server 2008 R2 Developer on Windows 7 HP (without initial updates) but this came up with errors. I barely slept over the weekend as the whole reinstallation process of my laptop has taken ages.If I try to install Visual Studio 2008, on its own, a msg appears:[i]An earlier version of Microsoft Visual Studio 2008 has been detected on the system that must be updated to SP1 before installation can proceed. Please update all other versions of Visual Studio 2008 to SP1 by visiting Microsoft Update and then install Visual Studio 2008 Express SP1.[/i] At the same time Visual Studio 2008 SP1 (which I downloaded as does not install now) I get the following message:[i]A compatible version of Visual Studio 2008 was not detected on the system. This update is designed for only the Microsoft Visual Studio 2008 (ENU) product family, and is not compatible with any Express editions.[/i]Should this not install without problems on a Windows 7 HP SP1 that has not been updated? The initial Windows Updates don't seem to have anythhing to do with impacting on this installation and are just security updates for Visual C++ 2005-2008.If this happens again I will just have to try to problem solve, but I would rather do another install now on a clean install of Windows 7 as the whole process of trying to fix the existing setup and previous setup is way to complicated and I have seen solutions involving copying shared .dll's, going into the registry and restore points (I tried the shared .dll's and restore points with no luck). I don't favour these solutions and would like to see if there is something that can be installed first as once SQL Server is installed it is very difficult to uninstall or correct.Many thanks for any suggestions to how to prepare for a clean install of SQL Server 2008 R2 Developer on a clean install of Windows 7 HP SP1.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog