SSQA.net

Outputting DBCC results

Madhivanan — Sat, 26 Jul 2008 11:29:00 GMT

Sometimes it may be useful to reuse the result of the DBCC commands. If the DBCC command resturns a resultset, it can be outputted to a table. Consider the following command DBCC useroptions It returns a single resultset. To copy the resultset to a table, you can use the following Create table #dbcc_useroptions ( [set option] varchar ( 100 ), [value] varchar ( 100 )) insert into #dbcc_useroptions exec ( 'DBCC useroptions' ) select * from #dbcc_useroptions drop table #dbcc_useroptions Note that it...(read more)

SQL Server data and log files initialization, think multiple files, backup & restore performance!

SQL Master — Fri, 25 Jul 2008 11:01:00 GMT

Whenever you perform a database creation, add a data or log file to database, AUTOGROW process is intiated or restore of database will trigger the file initialization if that is created afresh. In addition to this when you talk about files intialization you must consider the hardware such as RAID, as the software data block size is computed for optimal performance by SQL Server and should not be altered which is set to a maximum 64KB blocksize. Say if you are writing to disk or to tape (high-speed)...(read more)

CLR Stored Procedures - handling security on SQL Server side

SQL Master — Thu, 24 Jul 2008 08:52:00 GMT

Ok this is not new to say you can write stored procedures in any language to manage them in SQL Server, as 2005 version has started this CLR integration functionality that is exposed in an assembly called system.data.dll, which is part of the .NET Framework. So think about code access security in addition to managing security privileges for the user on database side. CLR supports the access security mechanism which is based on the assumption that the runtime can host both fully trusted and partially...(read more)

Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy - Tech-ed Online Panel video available to view

SQL Master — Tue, 22 Jul 2008 11:37:00 GMT

In continuation to Tech-ed North America - what I'm doing there? & Say goodbye to Tech-ed 2008 Developers and say hello to IT-PRO! posts here and to wrap up the earlier posts about Tech-ed Online Panel: Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy the panel discussion video is available to view for the users. You can view the show from this Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy link and also various videos sharing best...(read more)

Leveraging SQL Server Technologies to Build a Solid High-Availability Strategy - Tech-ed Online Panel video available to view

SQL Master — Tue, 22 Jul 2008 11:31:00 GMT

SQL Server Business Intelligence - handling data mining and mission critical applications

SQL Master — Tue, 22 Jul 2008 09:31:00 GMT

Data, data and data everywhere: a typical scenario for an Enterprise DBA to handle the mission critical applications and comprehensive information across the organization using SQL Server technologies. Since the inception of SQL Server 2005 version Business INtelligence has taken new heights for Data Warehousing and Data Mining perspective to manage the show as if it's all right here--from data mining, warehousing, and scripting techniques to MDX queries, KPI analysis, and the all-new Unified Dimensional...(read more)

Ordering Interger values stored in Varchar column

Madhivanan — Mon, 21 Jul 2008 14:21:00 GMT

I have seen many newbies asking "How do I sort the numbers stored in varchar columns?" Here are some methods declare @t table ( data varchar ( 15 )) insert into @t select '6134' union all select '144' union all select '7345' union all select '109812' union all select '100074' union all select '1290' union all select '45764' --Method 1 select data from @t order by cast ( data as int ) --Method 2 select data from @t order by data + 0 --Method 3 select data from @t order by len ( data ), data --Method...(read more)

Cumulative Update 8 package for SQL Server 2005 Service Pack2

SQL Master — Wed, 16 Jul 2008 09:50:00 GMT

I felt like somehow I missed to post the availability of Cumulative Update package releases here and referring the last CU7_Update package blogpost now we have CU8 available for download if the specified problem occurs in your platform as per the KBA 951217 here. Obviously this is for the installations that will have SQL Server 2005 SP2 and includes 50 Resolved Issues & 39 Unique Customer Requests, thats a lot. As usual the best practice note from Microsoft and industry is Cumulative Updates...(read more)

DTS Packages migration webinar - get tips and tricks from a MVP

SQL Master — Tue, 15 Jul 2008 15:44:00 GMT

Brian Kinight is well known SQL Server Professional in the community, every user will know about SQL Server Central website which was founded by him. Join Brian to discuss the strategies of upgrading DTS packages to SQL Server Integration Services (SSIS). As this is a web seminar it is free and will have a definitive showcase an actual DTS package conversion using DTS xChange and other strategies. DTS xChange provides automated DTS to SSIS migration technology for complete SQL Server 2005 upgrades....(read more)

Varchar(max) datatype and Replicate function

Madhivanan — Tue, 15 Jul 2008 13:54:00 GMT

As you know, Replicate function is used to repeat a character expression for a specified number of times. But by default the result is converted to varchar of maximum size 8000 when you dont convert the expression to specific type Consider the following example declare @v varchar ( max ) set @v = replicate ( 'a' , 50000 ) select len ( @v ), datalength ( @v ) Note that the result is not 50000 but 8000 because by default the result is limited to the maximum size of 8000 for varchar/char datatype To...(read more)

Get ready for SQL Server 2008 RTM release!

SQL Master — Mon, 14 Jul 2008 14:56:00 GMT

Are you desperate to get Relase To Manufacturing (RTM) release of SQL Server 2008? Well, long wait for SQL Server 2008 is nearly over. It is an official announcement that SQL Server 2008 will be on the August price list. This means that customers and partners will soon be able to place their orders for SQL Server 2008 as we get ready to RTM in Q3 of this year. As per Bob Kelly, Microsoft Corporate Vice President of Infrstructure Server Marketing announced during Worldwide Partner conference on July...(read more)

TSQL to get TOP 10 queries that are using I/O generation, SQL Server 2005

SQL Master — Mon, 14 Jul 2008 10:26:00 GMT

DMVs are most helpful to find instant information on SQL instance without causing further delays, in any performance degradation time you can execute following TSQL to get top 10 queries that are generating lots of I/O on the server: SELECT TOP 10 total_logical_reads, total_logical_writes, execution_count, total_logical_reads+total_logical_writes AS [IO_total], st.text AS query_text, db_name(st.dbid) AS database_name, st.objectid AS object_id FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(sql_handle)...(read more)

Reporting Services 2008 additional features, other than SQL Server Reporting Services 2005

SQL Master — Fri, 11 Jul 2008 09:38:00 GMT

SQL Server Reporting Services has been introduced in 2000 version but not catched within the user environment until the Reporting Services 2005 has been rolled out. In this regard administering a Reporting Services installation requires that you know how to perform routine maintenance tasks for server components, and how to manage the content and ongoing operations of the report server. Reporting Services components include Report Server Web service, for background processing applications, which...(read more)

Analysis Service error: Either the user, Domain\User, does not have access to the Database Name database, or the database does not exist.

SQL Master — Thu, 10 Jul 2008 08:02:00 GMT

As it seems the error text on the subject looks familiar when you have installed SQL Server or working with SQL Server databases. How about getting the error on Analysis Services databases (OLAP) within your BI Platform. So in the case of handling the Business Intelligence platform you must be aware of necessary data providers that are required in order to connect the end user application to backed database platform. If not various various exceptions can appear, including: "The specified module could...(read more)

Microsoft Security Bulletin Summary for July 2008 - what is it now?

SQL Master — Wed, 09 Jul 2008 08:07:00 GMT

I believe it has been a while that we have seen the security hotfix from Microsoft Security team on SQL Server (alone). The latest security bulletin has announced important security patches that are related to SQL Server in addition to Windows operating system. They quote that "... With the release of the bulletins for July 2008, this bulletin summary replaces the bulletin advance notification originally issued July 3, 2008. For more information about the bulletin advance notification service, see...(read more)

SQLKnowHow - gear up for exclusive training from professionals

SQL Master — Tue, 08 Jul 2008 13:27:00 GMT

SQLKnowHow is the new organisation that is focused on training services based on SQL Server. In this regard they have lined up very interesting list of events in coming months, it will be a great time for the SQL Server Users in United Kingdom to grab the opportunity. For Know How event Know more from following list: Training Events Best Practices in Performance and Availability for SQL Server 2005/2008 Kimberly L. Tripp and Paul S. Randal 1st - 3rd September 2008 Hatfield, Hertfordshire Indexing...(read more)

Quick and simple way to know the stored procedures parameters using TSQL

SQL Master — Tue, 08 Jul 2008 11:13:00 GMT

As you may be aware that you can pass parameters to the stored procedure when you have the selection of data requirement such as stored procedure with data type,length, parameter position and also the mode of parameter (Input or Output). Using INFORMATION_SCHEMA views you can get further information on what kind of parameters are used within a stored procedure without viewing the stored procedure or calling it to execute. select Specific_Name as 'Procedure_Name', Parameter_Name, Ordinal_Position...(read more)

SQL Server Technical Rollup July 2008 - Important & Interested links for you

SQL Master — Mon, 07 Jul 2008 07:51:00 GMT

As you may be aware all the Technical information roll-up on MSDN & Technet is sent out in email format, in this regard I wanted to share the important and interested links for you on SQL Server arena. Downloads Source Code Analyzer for SQL Injection Microsoft Source Code Analyzer for SQL Injection is a static code analysis tool for finding SQL Injection vulnerabilities in ASP code. Customers can run the tool on their ASP source code to help identify code paths that are vulnerable to SQL Injection...(read more)

SQL Server 2005 - How to compare content of tables without using third party tools?

SQL Master — Fri, 04 Jul 2008 08:36:00 GMT

You may be aware about using Third party tools to compare the data between 2 tables. Using SQL Server 2005 you need not entirely dependant on such requirement, TABLEDIFF utility is the new addition to the SQL Server. This utility enables you to compare the table contents by giving consistent information such as whether data is same, are they different and also give what data in the table different. From the command prompt where the SQL Server is installed you can run TABLEDIFF -? to get list of supported...(read more)

FIX: Error message after you run the DBCC DBREINDEX Transact-SQL statement on a table that does not have a unique clustered index on a SQL Server 2005-based computer: "Msg 2511"

SQL Master — Thu, 03 Jul 2008 21:46:00 GMT

The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2005 Enterprise Edition, SQL Server 2005 Standard Edition & SQL Server 2005 Enterprise X64 Edition Notification Contents: New FIX: Error message after you run the DBCC DBREINDEX Transact-SQL statement on a table that does not have a unique clustered index on a SQL Server 2005-based computer: "Msg 2511" http://support.microsoft.com/kb/954734/en-US...(read more)

Internet Database based Services find a suitable service provider

SQL Master — Wed, 02 Jul 2008 08:31:00 GMT

When you have your business activities on the Internet then you have a major requirement to provide Business Continuity in finding the suitable/sustainable service provider. So I believe there are few key factors you need to consider such as the location of the provider, such as how easy it will be for you to reach the provider. Why? The reason I bring up distance is because it is really split in the industry. You have some saying I want it close to my office for convenience, then you have the other...(read more)

SQL Server Express - get sample kits in express way

SQL Master — Mon, 30 Jun 2008 08:30:00 GMT

For every new user in SQL Server world and experienced users to get used to newer version of SQL Server, having sample kit will definetly help out. In this scenario I often get emails asking about any samples or documentation (procedures) to get more about the latest release of SQL Server, in the same fashion thanks to Microsoft learning that such sample kits are available to download for free. The following links source is from Microsoft site: Teacher Starter Kit The Teacher Starter Kit is a rich...(read more)

You receive a "Creating the partitioned snapshot" message when you create a pull subscription that is configured to receive the precomputed partition of a merge publication in SQL Server 2005

SQL Master — Fri, 27 Jun 2008 09:10:00 GMT

The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2005 Enterprise Edition & SQL Server 2005 Standard Edition Notification Contents: New You receive a "Creating the partitioned snapshot" message when you create a pull subscription that is configured to receive the precomputed partition of a merge publication in SQL Server 2005 http://support.microsoft.com/kb/953201/en-US...(read more)

Unable to install SQL Server 2008 RC0 Books Online

SQL Master — Thu, 26 Jun 2008 09:54:00 GMT

In continuation with the BOL update topic on SQL2008-RC0_BOL post here I have had troubles in installing the RC0 Books Online on to my laptop. As per the error below I thought it may be problem with the Windows Installer service on my laptop (Vista Ultimate) and tried to install afresh on a central monitoring server (Windows 2003 server) & local machine (Windows XP), still no luck to install the documentation. On my laptop I have Windows Installer service 3.1 and that is not sufficient to carry...(read more)

SQL Server 2008 new features highlights - a short video samples

SQL Master — Wed, 25 Jun 2008 18:06:00 GMT

I'm sure that by now you are aware about the list of new features within SQL Server 2008 and recently I have had couple of emails from newbie DBA/users in SQL Server asking about any reference they can get on these new features, with a video. In this regard Microsoft learning has provided right to the point of subject references as follows: SQL Server Enterprise Data Platform Vision Watch the video Support for relational data, XML, FILESTREAM, and spatial data Watch the short video . Extended Events...(read more)