How-To Articles

why

Monnezza — Thu, 24 Feb 2011 00:03:57 GMT

I see all threads empty?

Publicare fişier raport Reporting Services prin WEB

bustiuci — Tue, 13 Oct 2009 07:02:59 GMT

Am creat un raport cu SQL Server 2005 BI Studio, şi acum se pune problema să-l public pe un server (Reporting Services) dar via WEB.

De ce aş avea nevoie minimum să pot face acest lucru în condiţii de siguranţă?

Mulţumesc.

import data from a dbf file

sorinh — Tue, 09 Jun 2009 11:52:51 GMT

Salut,

Am o serie de fisiere .dbf care isi vor schimba zilnic numele dar vor pastra aceasi structura. Am nevoie sa le import intr-un server sql 2000 ... preferabil cit mai automatic posibil.

Aveti ceva sugestii ?

New Functionalities in SQL Server 2008 (living document)

xmldeveloper — Fri, 05 Oct 2007 07:37:18 GMT

Transparent data encryption
External key management
Data Auditing
Hot Add CPU
Database Mirroring Improvements

Transparent failover for Database Mirroring
Automatic recovery from page corruption
Log stream compression (Database Mirroring)
New Performance Counters for Database Mirroring

Declarative Management Framework
Server Group Management
Streamlined installation
Enterprise System Management
Performance Data Collection
System Analysis
Data compression
Resource Governor
Query Optimization Modes
Extended Events
Logging Improvements (Minimal Logging = log only what is necessary for rollback)
Entity Data Model
Language Integrated Query (LINQ)
Visual Entity Designers
Entity Aware Adapters
SQL Server Change Tracking
Synchronized Programming Model
Visual Studio Support
SQL Server Conflict Detection
BLOBS in SQL SERVER 2008

FileStream data type
Remote BLOB Store API

Integrated Full-Text Search
Sparse columns
Large user-defined types
Date/Time Data types
LOCATION data type
SPATIAL data type
Backup compression
Partitioned table parallelism
Star join query optimizations
Resource Management
Grouping Sets
Change Data Capture
MERGE SQL statement
Scalable Integration Services
SQL Server Integration Services (SSIS) pipeline improvements
SSIS persistent lookups
Analysis scale and performance
Block computations
Writeback
Enterprise Reporting Engine
Better scale-out configuration
Internet report deployment
Manage the reporting infrastructure
Report Builder enhancements
Built-in forms authentication
Report Server application embedding
Office integration
TABLIX

Add to that:

FORCESEEK table hint
New Dynamic Management Views

Memory Related Dynamic Management Views

Visual designer, and add nodes to a peer-to-peer replication configuration without stopping system activity
Performance Studio to troubleshoot, tune, and monitor
Table-valued parameters
Transact-SQL Row Constructors (Table-valued constructors)
Include Scalar Values in Row Constructors (
INSERT INTO MyTable (ColX,ColY)

VALUES ((SELECT Top 1 ColZ FROM MyOtherTable),

(SELECT Top 1 ColW FROM MyOtherTable));
)
One statement variable declaration/assignment
Compound Assignment Operators
HierarchyID data type
Deprecation Counters
Large User Defined Aggregates
New versions of ODBC driver, OLE DB provider, and ADO.NET data provider
SQLCE Version 3.5
Lock Escalation changes

Unconfirmed yet:

Intellisense
Debug in SSMS

SQL Server 2005 - The Default Trace

xmldeveloper — Mon, 08 Oct 2007 16:33:36 GMT

What's in a name? That which we call this trace by any other word would mean it’s stopped? But nay, it’s running quietly on your server and I will tell you why.

The Default Trace is a light-weight trace that is running by the default on your SQL Server. Its primary role is to help you in troubleshooting various problems. Light-weight means that you shouldn't be too concerned about its impact on your server’s performance.

Let’s get some more information about it. Using the fn_trace_getinfo function, you can get some of its properties. The Default Trace has the id value 1 so I will call the function fn_trace_getinfo using 1 as argument.

Example:
SELECT *
FROM fn_trace_getinfo(1)
GO

--Results
traceid property value
------- -------- ----------------------------------------
1       1        2
1       2        C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_156.trc
1       3        20
1       4        NULL
1       5        1

I will explain the results, don’t worry! The property column has the following meaning: 1= Trace options, 2 = File name, 3 = Max size, 4 = Stop time, 5 = Current trace status. In our case, we see a rollover trace having a maximum size of 20MB. Rollover means that when the maximum size of the file is reached a new file will be created. The number appended to the name of the trace (156 in this case) is used to indicate its sequence. There are only 5 trace files just in case you wonder if it will fill-up your disk drive’s space. The trace file location can be different on your server (and you can see the exact location using the above query).

The next step is to see the events recorded by this trace:

Example:
SELECT T.eventid AS [id], E.name
FROM fn_trace_geteventinfo(1) T
JOIN sys.trace_events E
ON T.eventid = E.trace_event_id
GROUP BY T.eventid, E.name
GO

-- Results
id    name
----- -----------------------------
18    Audit Server Starts And Stops
20    Audit Login Failed
22    ErrorLog
46    Object:Created
47    Object:Deleted
55    Hash Warning
69    Sort Warnings
79    Missing Column Statistics
80    Missing Join Predicate
81    Server Memory Change
92    Data File Auto Grow
93    Log File Auto Grow
94    Data File Auto Shrink
95    Log File Auto Shrink
102   Audit Database Scope GDR Event
103   Audit Schema Object GDR Event
104   Audit Addlogin Event
105   Audit Login GDR Event
106   Audit Login Change Property Event
108   Audit Add Login to Server Role Event
109   Audit Add DB User Event
110   Audit Add Member to DB Role Event
111   Audit Add Role Event
115   Audit Backup/Restore Event
116   Audit DBCC Event
152   Audit Change Database Owner
153   Audit Schema Object Take Ownership Event
155   FT:Crawl Started
156   FT:Crawl Stopped
157   FT:Crawl Aborted
164   Object:Altered
167   Database Mirroring State Change

The names of the events are self-explanatory. You can see that memory changes will be recorded as well as DBCC commands execution, DDL events (creating or dropping objects, schema modifications) etc.

But where you can see the recorded information? You will find the answer to this question looking at the Summary Reports in SQL Server Management Studio. To view these reports, start SQL Server Management Studio, press F7 (or click Summary in the View menu), and finally use the Reports dropdown list to select a report.

As you may expect, you can get the same data by running a SQL query:

Example:
SELECT *
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default)
GO

I won’t include the results because they are pretty large and they don't look as nice as the graphical reports in Management Studio. I will recommend you to try the scripts above and to test-drive the new Summary Reports, I can tell you that it’s worth it. They can give you a good idea of what happens or happened on your server from object creation to configuration changes. In the end, the Default Trace along with the Summary Reports and the new Dynamic Management Views will make your life easier (provided you work with SQL Server of course).

[published in Culminis Compass http://emea.culminis.com/Newsletters/200603_march/0306defaulttrace.htm]

SQL Server Database Publishing Wizard Community Technology Preview 1

xmldeveloper — Mon, 16 Oct 2006 19:17:29 GMT

I tested it and here are my first observations:

It's a command line tool.
To script the AdventureWorksDW database:

I opened a command promt and change the directory to C:\Program Files\Microsoft SQL Server\90\Tools\Publishing\
I typed the following: sqlpubwiz script -d AdventureWorksDW C:\AdventureWorksDW.sql
Unfortunatelly it failed due to an unsupported data type (the XML data type) for a table:
The limitation was known: DPW Support Matrix
I tried with Northwind database typing: sqlpubwiz script -d Northwind C:\Northwind.sql and this time it worked just fine:

Conclusion: pretty good tool at this stage (CTP1) but you should know that it has its limitation such as a limited set of objects supported and several unsupported data types (XML, Varbinary(MAX), VarChar(MAX), NVarChar(MAX) etc.)
In November the CTP2 should be lauched with a GUI version of the tool and enhanced object and data types support.

Dynamic Management Views and Functions – Pain-free Introduction

xmldeveloper — Sat, 16 Sep 2006 10:42:59 GMT

In 1993 the alternative rock group, 4 Non Blondes, were climbing the charts all over the world screaming at the top of their lungs “What’s going on?”. Almost 12 years later, the SQL Server team came with the answer – the Dynamic Management Views and Functions. If you care what’s going on with your server, read on and I’ll try in 10 minutes of your time to get you started.

Dynamic Management Views and Functions expose internal state of SQL Server and also statistical data. Don’t be scared by this phrase. In plain English, you can see through DMVs current sessions, requests, locks, transactions, memory allocation, tempdb usage, index usage and so on. This is so called dynamic metadata that is not persisted physically on disk and lives somewhere in your server memory. You can guess from that what happens if your server gets restarted.

Let’s see them at work by checking the list of the current session. You will find the output similar with the output of sp_who or sp_who2 stored procedures (or sysprocesses virtual table):

Example:
SELECT
      s.session_id
      , DB_NAME(r.database_id) as DBName
      , s.login_name
      , r.blocking_session_id as BlkBy
      , r.open_transaction_count
      , r.status
      , s.host_name
      , s.program_name
      , r.command
      , (SELECT SUBSTRING(text, statement_start_offset/2, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), text)) * 2 ELSE statement_end_offset END -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS QueryText
FROM
      sys.dm_exec_sessions s JOIN
      sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE
      s.is_user_process = 1

Let me explain this first example more detailed. You already met two DMVs (sys.dm_exec_sessions, sys.dm_exec_requests) and one DMF (sys.dm_exec_sql_text). The first information you can get from this objects come from their name. They belong to the sys schema (like other system objects), then the dm prefix is common for all DMVs (and DMFs). The prefix is followed by a category that groups related DMVs – exec in this particular case for execution related DMVs. The last part of the name will tell you what the DMV does, in this example you will get a list of current sessions and associated requests (and the Query Text respectively from the sys.dm_exec_sql_text DMF).

The rows returned by the query look almost identical with the output of sp_who stored procedure so it won’t be a mystery if you are familiar with SQL Server 2000 but I do have some remarks here. The former SPID (Server Process ID) became the session id and to get only the user sessions you have to use the is_user_process column (the old approach – testing for spids greater than 51 is no longer guaranteed). For more information about these DMVs and all the others that will follow please refer to Books Online as I am trying only to whet your appetite here.

Before I move on, I will specify one more use of the sys.dm_exec_requests DMV – progress reporting for some DBCC commands such as DBCC CHECKDB, DBCC CHECKFILEGROUP and DBCC CHECKTABLE. You have two columns for that - the percent_complete column (for the percent of work completed) and the command column (for reporting the current phase of the DBCC command). To test this feature, use the following example:

Example:
-- run this in one window
-- of SQL Server Management Studio
-- or Query Analyzer
DBCC CHECKDB ('AdventureWorks')
GO
-- run this in another window
SELECT
      s.session_id
      ,r.percent_complete
      ,r.command
FROM
      sys.dm_exec_sessions s join
      sys.dm_exec_requests r on s.session_id = r.session_idsession_id
WHERE
s.is_user_process = 1

Optionally you can improve the example by including a specific session id in your WHERE clause.

Another DMV that I want to introduce to you is sys.dm_os_memory_clerks. It displays the memory allocation for server components.

Just run a SELECT * FROM sys.dm_os_memory_clerks to get a feel of it. In time if you investigate further you will know that this DMV is just the top of the iceberg and if you need to you can get the memory allocation at the object level. More than that, you can peek at internal memory notifications and so on. Anyway, don’t worry about that for now.

How about indexes? The existing DMVs (and when I say DMVs I mean also DMFs) can tell you if your indexes are used and how, their fragmentation and even if your performance can benefit from additional indexes. The next example will display the fragmentation of your indexes from AdventureWorks database:

Example:
USE AdventureWorks;
GO
SELECT
      OBJECT_NAME(s.object_id) Table_Name
      ,i.[name] Index_Name
      ,avg_fragmentation_in_percent
FROM
      sys.dm_db_index_physical_stats(DB_ID(N'AdventureWorks'),NULL,NULL,NULL,'SAMPLED') s
JOIN
      sys.indexes i ON i.[object_id]=s.[object_id]
AND
      i.index_id=s.index_id
WHERE
      OBJECTPROPERTY(s.[object_id],'IsUserTable')=1

My last example will concentrate on performance troubleshooting. I found out in my work that the Pareto's Principle (the 80-20 Rule) really works. In the performance area if I will tune 20 percent of the queries that run on my server I will gain the maximum effect (that 80 percent improvement). But how to find that 20 percent of queries? With a DMV of course:

Example:
      SELECT
               SQ.*
      ,(SELECT SUBSTRING(text, statement_start_offset/2, (CASE WHEN statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX), text)) * 2 ELSE statement_end_offset END -statement_start_offset)/2)) AS QueryText
FROM
(
SELECT TOP 20 PERCENT
      sql_handle
      ,SUM(total_worker_time) AS Total_CPU_Time
      ,SUM(execution_count) AS Total_Execution_Count
      ,COUNT(*) AS Nmb_Statements
      ,MIN(statement_start_offset) AS statement_start_offset
      ,MIN(statement_end_offset) AS statement_end_offset
      FROM
            sys.dm_exec_query_stats
      GROUP BY sql_handle
      ORDER BY Total_CPU_Time DESC
) AS SQ
CROSS APPLY sys.dm_exec_sql_text(SQ.sql_handle) AS T

Don’t close this page yet – there’s an explanation for the above query. I find out first in a sub-query the most executed offending queries for me by means of the total CPU time used. I also display the number of times they were executed because it does matter and also the query text.

I will stop for now and I will continue in future articles (and hopefully in a book). If you are not convinced by now that you should at least consider using them, I’ll try one more approach. Think at your server as being a car. What do you do if something seems to be wrong? You open the hood, don’t you? For SQL Server the equivalent is the DMVs. You may find a work-around to avoid them in some cases but why would you do that? So pop-up that hood and open Books Online and see what those DMVs are capable of. Thank you!

Published in Culminis Compass

Just Temporary

xmldeveloper — Sat, 16 Sep 2006 10:47:03 GMT

Almost everything around us is temporary starting with your job and ending with your beer supply. You may argue that a job may last for a life time but even life is temporary. In SQL Server temporary means tempdb, a database where all ephemeral things happen. As you may know by now, SQL Server 2005 uses tempdb more than its predecessors, a lot more. The goal of this article is to tell you why.

SQL Server 2005 has far more features than previous versions of SQL Server, and yes, this is one of the reasons for increased tempdb usage. More features mean a greater need of temporary objects and obviously of tempdb space. Some of the new features that may use tempdb are Service Broker, Database Mail, CTEs (Common Table BLOCKED EXPRESSION, Event Notifications or MARS (Multiple Active Result Sets).

The second reason for an extensive use of tempdb is that some of the features, that do not use tempdb in earlier versions of SQL Server, use tempdb in SQL Server 2005. For example the deleted and inserted tables, used by AFTER triggers, are created in tempdb (in earlier versions the transaction log is used for the same purpose).

The third and the last reason I will mention here, is that some of the features that used tempdb in earlier versions of SQL Server will require more tempdb space in SQL Server 2005. For example the DBCC CHECKDB statement, will use more tempdb space due to more comprehensive logical checks, more complex structures to be checked and some new checks to be performed for the new features in SQL Server 2005.

To get a better image of features that may require tempdb disk space (and because I like lists) I will give you a list with tempdb “offenders”:

Row versioning – row versions are held in the tempdb version store. Row versioning supports features such as online index operations, Multiple Active Result Sets (MARS), and AFTER triggers. They are also used by databases that use read-committed snapshot isolation or snapshot isolation levels.
Bulkload operations with triggers enabled - row versioning is used for this feature.
Keyset-driven cursors and static cursors use work tables built in tempdb.
Common-table-expression (CTEs) queries - work tables are created in tempdb for spool operations when CTEs queries are executed.
Service Broker – uses tempdb for various reasons such as caching and preserving dialog context. Some of the features that rely on Service Broker like Database Mail, Event Notifications and Query Notifications can also use tempdb.
DBCC CHECKDB statement (already mentioned) uses tempdb for work tables.
Indexes – creating and rebuilding indexes can use tempdb when you set the SORT_IN_TEMPDB option to ON. Concurrent user modification (updates or deletes) during online index operations will require tempdb space.
Large object (LOB) data type variables and parameters of types like varchar(max), nvarchar(max), varbinary(max) text, ntext, image, and xml and string functions operating on LOBs may use tempdb.
Multiple Active Result Sets (MARS) can use tempdb
Queries that contain DML statements (INSERT, UPDATE, DELETE) and use internal objects for storing intermediate results (in case of hash joins, hash aggregates, or sorting).
Temporary tables and table variables
Tables returned by table-valued functions
User-defined tables and indexes

Why should you care about tempdb disk space? Because it can become one of your greatest performance pains if the disk space becomes insufficient. I will not give you the cure (the cure being beyond the purpose of this article) but I will give you some resources at the end of the article, resources that do an excellent job in teaching you how to deal with tempdb usage. However I want to mention three of the new Dynamic Management Views and Functions that can help you monitor the tempdb disk space usage:

The sys.dm_db_file_space_usage dynamic management view allows you to monitor the disk space used for each of the tempdb files.
The sys.dm_db_session_space_usage dynamic management view will display the page allocation or deallocation activity in tempdb at the session level.
The sys.dm_db_task_space_usage dynamic management view will do the same thing as the previous DMV (but at the task level).

To get a feel of how you can use them, I will give you an example:

Example:
DECLARE @i int
SET @i = 0
CREATE TABLE #T (c char(8000))

WHILE @i < 100
BEGIN
INSERT INTO #T SELECT ('Test')
SET @i = @i + 1
END

SELECT user_objects_alloc_page_count
FROM sys.dm_db_session_space_usage
WHERE session_id = @@SPID;

--Results
user_objects_alloc_page_count
-----------------------------
104

In this simple example, I’ve created a temporary table and then I used the sys.dm_db_session_space_usage dynamic management view to check the number of pages reserved for user objects in tempdb. I filtered the result for my session using the @@SPID function. You can use the same DMV to build more complex queries and get the pages reserved for internal objects or for row versioning.

I will end my article here, reminding you again that the SQL Server 2005 uses tempdb for more things than SQL Server 2000 does, so please consider this when you upgrade or build your applications using SQL Server 2005.

You have been warned!

Thank you!

Additional Resources:
Books Online – “Troubleshooting Insufficient Disk Space in tempdb” http://msdn2.microsoft.com/en-US/library/ms176029(SQL.90).aspx
Books Online – “Capacity Planning for tempdb” http://msdn2.microsoft.com/en-US/library/ms345368(SQL.90).aspx
“Troubleshooting Performance Problems in SQL Server 2005” http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx#ESVAE

Published in Culminis Compass

Dedicated Administrator Connection (DAC) – a friend in need

xmldeveloper — Sat, 16 Sep 2006 10:45:59 GMT

Sometimes computers fail due to hardware or software. This time your database server is not responding. If it’s SQL Server 2005 you still have an alternative to restarting your production server – Dedicated Administrator Connection. This article will give you an idea on what is DAC, how to use it and when to use it.

What is DAC ?

At a first look, Dedicated Administrator Connection or shorter DAC, is just another connection. And that’s true but this connection is very special. It has its own separate memory area, its own scheduler and even its own port. This way by reserving resources, DAC will remain open for business as long as your instance of Database Engine is still started.

How to use it ?

You can initiate a DAC from SQL Server Management Studio or sqlcmd command-line utility, by prefixing the instance name you will connect to with admin: prefix. In case of sqlcmd you have a second option – the –A switch.

The following examples are equivalent and demonstrate how to connect to a server named MyServer with Integrated Authentication and using DAC.

Example:

sqlcmd –SMyServer -E -A

sqlcmd –Sadmin:MyServer -E

Another thing I want to mention is that only members of sysadmin SQL Server role can use DAC and that by default you can use DAC only locally. For remote connections you have to enable them using the remote admin connections option and sp_configure stored procedure or Surface Area Configuration utility.

Example:

sp_configure 'remote admin connections', 1;

RECONFIGURE;

You can also use DAC with osql but it’s neither recommended nor supported.

To save resources DAC is disabled in SQL Server Express edition but you can turn it on by using a trace flag. Just add "-T7806” to the startup parameters and you are ready to go.

Maybe you are familiar with this story in which a magical creature (a genie or a goldfish) grants three wishes. The Microsoft SQL Server team grants you just one DAC. So if you use it don’t forget to close it.

Another thing that makes DAC special is the ability to view and even update the system tables. In 99.99% of cases, it’s absolutely unnecessary to modify directly the system tables. For the others there is DAC. To test this, just run the following example on a standard connection and then on DAC:

Example:

SELECT TOP 10 * FROM sys.sysschobjs;

You don’t have to know more about the sys.sysschobjs table other than it is a system table. I will not show you how to modify a system table but I will just mention that you have to start your server in single-user mode.

When to use it ?

As the title says, DAC is a friend in need but not for every need. Do not mistake “administrator” for “administrative”. DAC is not meant for administrative tasks. Having a single thread available some administrative commands like BACKUP and RESTORE, or other parallel queries and commands won’t run in DAC. As a rule of thumb if you can’t connect to your server using a standard connection, then try with DAC and proceed with caution. Remember that you’re using DAC because your server is already in trouble and you have the power to make the situation worse by blocking DAC.

To avoid that; don’t run resource-intensive queries and limit yourself to basic diagnostic ones. I recommend you the new Dynamic Management Views (views that expose the internal state of the server) such as sys.dm_tran_locks to get an idea of the current locks, sys.dm_exec_requests and sys.dm_exec_sessions that will return information about the current request and sessions. Add KILL command to this recipe (to help you terminate misbehaving connections) and you have a pretty good toolset at your disposal.

If you want to avoid blocking DAC you can run your queries with a LOCK_TIMEOUT value set. Another option is to use READ UNCOMMITTED transaction isolation level. Or even better use both.

If there is something that I want you to remember is the fact that you should use DAC only if you need it and do that carefully.

Did I mention that DAC won’t timeout? So don’t forget to close it!

Published in Culminis Compass

How to configure SQL Server 2005 to allow remote connections

xmldeveloper — Thu, 06 Apr 2006 08:21:58 GMT

http://support.microsoft.com/?kbid=914277&SD=tech

How to obtain the latest service pack for SQL Server 2005

xmldeveloper — Thu, 06 Apr 2006 08:21:15 GMT

http://support.microsoft.com/?kbid=913089&SD=tech