tag:blogger.com,1999:blog-36042809690849411892024-03-13T11:41:25.306-07:00SQL and BeyondA developer's notes - SQL ServerAlexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.comBlogger48125tag:blogger.com,1999:blog-3604280969084941189.post-921743778155585142014-03-04T19:59:00.001-08:002014-03-04T20:03:32.444-08:00SQL Service failed to start. TCP provider failed to listen on [ 'any' 1433]. Tcp port is already in use.Today I got an error when I tried to start SQL Server on my PC.<br />
It's been working fine so far and I did not install anything new on my PC, so it was kind of strange...<br />
<br />
To see what is going on I looked at Event Viewer (eventvwr.exe) and found the following error messages.<br />
<br />
<i>Server TCP provider failed to listen on [ 'any' <ipv4> 1433]. Tcp port is already in use.</i><br />
<br />
<i>TDSSNIClient initialization failed with error 0x2740, status code 0xa. Reason: Unable to initialize the TCP/IP listener. Only one usage of each socket address (protocol/network address/port) is normally permitted.</i><br />
<br />
So it looks like other application occupies SQL Server default instance port TCP 1433.<br />
To find out what is using 1433, I ran netstat.exe as follows:<br />
<br />
<a href="http://2.bp.blogspot.com/-yCgcJqLFejM/UxaaE61_sfI/AAAAAAAAAao/oQI6Uhf08S8/s1600/check-port-1433.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="http://2.bp.blogspot.com/-yCgcJqLFejM/UxaaE61_sfI/AAAAAAAAAao/oQI6Uhf08S8/s1600/check-port-1433.png" /></a><br />
<br />
<br />
<br />
<br />
<br />
I found one process using TCP 1433 and its process ID was 5136 as you can see at the end of the output above.<br />
<br />
And now I ran Process Explorer (procexp.exe) to check out which application it is. (Sure, you can run Task Manager or any other process monitoring utility) It turns out that PID 5136 was Google Chrome (surprisingly!) and it was connecting to Google owned 1e100.net server (173.194.33.146).<br />
<br />
I killed Google Chrome and restarted SQL Server... and everything worked fine...<br />
<br />
<br />Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-39921783528809140002013-11-19T20:39:00.001-08:002013-11-19T20:39:47.816-08:00bcp -t field terminatorTypical bcp out uses comma (csv) or tab (tsv) but there was a special request that fields should be separated by char "|". bcp.exe has a special -t option to change default field terminator (tab) to something else. So tried to use this option.<br />
<br />
<pre>C:> bcp "select au_id, au_lname from pubs.dbo.authors" queryout "1.csv" -S. -t | -c -T
'-c' is not recognized as an internal or external command,
operable program or batch file.
</pre>
<br />
The problem is | in "-t | -c " is not considered as field terminator. In command shell, | is considered as pipe command. so prior to | is one command and the result is passed to next to | command, which is "-c" here. But there is no -c command, hence the error.<br />
<br />
So how to solve this problem? Fortunately, we can use 1 byte hex value in -t option. So since Ascii hex value for | character is 0x7C, we can put this value in -t option to avoid command pipe.<br />
<br />
<pre>C:> bcp "select au_id, au_lname from pubs.dbo.authors" queryout "1.csv" -S. -t 0x7C -c -T
Starting copy...
23 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 1 Average : (23000.00 rows per sec.)
</pre>
<br />Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com1tag:blogger.com,1999:blog-3604280969084941189.post-14165549147485857862013-11-16T22:48:00.001-08:002013-11-16T22:48:03.629-08:00How to force users to execute Stored Procedures only<div style="clear: both;">
</div>
Let's say there is a situation we want to prevent any SQL users from directly accessing any underlying tables and views. And we want SQL users to access underlying tables via stored procedures only. (I understand this is a special case and typically people don't want to do that.)<br />
<br />
How can we implement this scenario?<br />
If we only block one user, we first deny all DML operations against the user and then grant EXECUTE permission to the user as follows:<br />
<br />
<b>USE TestDB</b><br />
<b>DENY SELECT, INSERT, DELETE, UPDATE to User1</b><br />
<b>GRANT EXECUTE TO User1</b><br />
<br />
If we want to apply this to any database users, we can use PUBLIC role as follows:<br />
<br />
<b>DENY SELECT, INSERT, DELETE, UPDATE to PUBLIC</b><br />
<b>GRANT EXECUTE TO PUBLIC</b><br />
<br />
<div>
<br /></div>
Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-89708141000477985282013-09-24T15:48:00.000-07:002013-09-24T15:48:11.239-07:00SSAS - cannot start service after specifying incorrect path in Server PropertiesI accidentally specified invalid path (strictly speaking, it is actually a valid path but not correct path for Analysis Services) in Analysis Server Properties dialog from SSMS. This caused the Analysis services to be unable to restart.<br />
<br />
Here is what I did.<br />
1) Run SSMS.exe (SQL 2008) and connect to Analysis Service.<br />
2) Rightclick on Analysis Service server from Object Explorer and choose [Properties].<br />
3) Add a path (C:\Temp) in DataDir. (Please note that separator is "|" between multiple paths.)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-I1WWqqEZ8vc/UkIUeV38rPI/AAAAAAAAAZU/HB2LgV1xpxs/s1600/SSAS-server-properties.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="572" src="http://2.bp.blogspot.com/-I1WWqqEZ8vc/UkIUeV38rPI/AAAAAAAAAZU/HB2LgV1xpxs/s640/SSAS-server-properties.png" width="640" /></a></div>
<br />
<br />
4) Click OK. This will bring up the following message box.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-unAudWW_GFA/UkIVLVFFwqI/AAAAAAAAAZc/juuYdh5G0sc/s1600/SSAS-restart-required.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="145" src="http://4.bp.blogspot.com/-unAudWW_GFA/UkIVLVFFwqI/AAAAAAAAAZc/juuYdh5G0sc/s400/SSAS-restart-required.png" width="400" /></a></div>
<br />
<br />
5) So rightclick on Analysis Server (in Object Explorer) and restart service. And then, the following error occurred.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-Qws0JDAm3HY/UkIVmORa1CI/AAAAAAAAAZk/8C_oLhpIoqM/s1600/SSAS-server-control-error.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="145" src="http://3.bp.blogspot.com/-Qws0JDAm3HY/UkIVmORa1CI/AAAAAAAAAZk/8C_oLhpIoqM/s400/SSAS-server-control-error.png" width="400" /></a></div>
<br />
<br />
<strong><em><u>HOW TO RESOLVE</u></em></strong><br />
<br />
Here is what I did to resolve this issue.<br />
<br />
1) Goto SSAS OLAP config folder (In my case, C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Config\msmdsrv.ini )<br />
<br />
2) Edit msmdsrv.ini and remove invalid path in DataDir element.<br />
<br />
<ConfigurationSettings><br /> <DataDir><span style="color: red;">C:\Temp|</span>C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Data</DataDir><br /> <LogDir>C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Log</LogDir><br /> <BackupDir>C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup</BackupDir><br /> <AllowedBrowsingFolders>D:\OLAPBackup|C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Backup\|C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\Log\</AllowedBrowsingFolders><br /> <CollationName>Latin1_General_CI_AS</CollationName><br /> <Language>1033</Language><br />
<br />
3) Save and restart SSAS service. <br />
<br />
After this simple fix, the service start worked fine :-)<br />
<br />
<br />
<br />
Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-83073576167858878422013-07-26T21:21:00.001-07:002013-07-26T21:21:48.726-07:00SSAS - create dimension hierarchies for composite primary key tableCreating hierarchical dimension in SSAS is a common task, but if one uses existing table as source and the table has composite primary key it might not be simple task. Let's take an example. Here is an table [TypeTable] that is using composite primary key with two columns - TypeId and SubTypeId.<br />
<br />
<b><i><u>TABLE: TypeTable</u></i></b><br />
TypeId : int<br />
SubTypeId : int<br />
TypeName : nvachar(100)<br />
SubTypeName : nvachar(100)<br />
<br />
<strong><em><u>Primary Key</u></em></strong><br />
TypeId + SubTypeId<br />
In order to build hierarchical dimension from this table:<br />
<br />
1) In SQL Business Intelligence Development Studio, open Data Source View design view.<br />
<br />
2) Rightclick on the table [TypeTable] and choose [New Named Calculation...]. Key columns are TypeId + SubTypeId but there is no Name column corresponding for this composite key. So new name column should be added. So enter calculation column name and an appropriate expression. In our example, it can be specified as below:<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-gQ-sMAPqK7s/UfNCV8j2g2I/AAAAAAAAAYY/zrrCVUuDybo/s1600/new-named-calculation.png" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img bba="true" border="0" src="http://3.bp.blogspot.com/-gQ-sMAPqK7s/UfNCV8j2g2I/AAAAAAAAAYY/zrrCVUuDybo/s1600/new-named-calculation.png" /></a></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: start;">
3) Launch new Dimension Wizard from Dimension folder. In Dimension wizard, choose [Use an existing table] and select key columns and [TypeKeyName] in Name column.</div>
<div class="separator" style="clear: both; text-align: start;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-u3WIh9Ex3BE/UfNENSlCLrI/AAAAAAAAAYo/Sb8N9bYqjo0/s1600/dimension-wizard-source.png" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img bba="true" border="0" height="582" src="http://4.bp.blogspot.com/-u3WIh9Ex3BE/UfNENSlCLrI/AAAAAAAAAYo/Sb8N9bYqjo0/s640/dimension-wizard-source.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: start;">
<br /></div>
<div class="separator" style="clear: both; text-align: start;">
<br /></div>
<div class="separator" style="clear: both; text-align: start;">
4) Go next. In Select Dimension Attributes, select all attributes. Go next. Give [DimType] for diemension name. </div>
<div class="separator" style="clear: both; text-align: start;">
<br /></div>
<div class="separator" style="clear: both; text-align: start;">
5) Now in Dimension design window, click [Type Id] in [Attributes] pane. This is the key column generated from Dimension Wizard. Rename it to [TypeKey] (well, in order not to be confused with TypeId column name) Please note that Key Column, Name Column, Usage properties for [TypeKey] are automatically and correctly set by Wizard. But other attributes such as [Type Name] and [Sub Type Name] are not.</div>
<div class="separator" style="clear: both; text-align: start;">
<br /></div>
<div class="separator" style="clear: both; text-align: start;">
6) Update Key Column, Name Column properties for [Type Name] and [Sub Type Name]. That is, for [Type Name] attribute, specify [TypeId] in KeyColumn and [TypeName] in NameColumn property.</div>
<div class="separator" style="clear: both; text-align: start;">
For [Sub Type Name] attribute, specify [TypeId] + [SubTypeId] in KeyColumn and [SubTypeName] in NameColumn property.</div>
<div class="separator" style="clear: both; text-align: start;">
<br /></div>
<div class="separator" style="clear: both; text-align: start;">
7) Now it is time to build dimension hierarchy. Drag and drop TypeName, SubTypeName one by one from Attributes pane to Hierarchies pane.</div>
<div class="separator" style="clear: both; text-align: start;">
<br /></div>
<div class="separator" style="clear: both; text-align: start;">
8) In Attributes pane, you will see [Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.] message when hovering over [DimType] node. To remove this warning, click [TypeName] in [Attributes] pane and change property [AttributeHierarchyVisible] to False. Repeat same thing to [SubTypeName] and [TypeKey].</div>
<div class="separator" style="clear: both; text-align: start;">
<br /></div>
<div class="separator" style="clear: both; text-align: start;">
9) Goto Attribute Relationship tab. Drag [SubTypeName] and drop onto [TypeName]. And click hallow Arrow between TypeKey and SubTypeName and change [RelationType] property to Rigid.</div>
<div class="separator" style="clear: both; text-align: start;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-VjWFWWo5yIU/UfNJYUELEEI/AAAAAAAAAY4/TAfkCg6IqDg/s1600/dim-attribute-relationships.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img bba="true" border="0" src="http://1.bp.blogspot.com/-VjWFWWo5yIU/UfNJYUELEEI/AAAAAAAAAY4/TAfkCg6IqDg/s1600/dim-attribute-relationships.png" /></a></div>
<div class="separator" style="clear: both; text-align: start;">
<br /> </div>
Now new dimension hierarchy is built from composite key table.Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-31534870033591543722013-07-15T14:29:00.001-07:002013-07-15T14:29:18.895-07:00Change default SQL instance to another versionI happened to install SQL Server 2000 on the machine where I already had SQL Server 2008 as default instance. I forgot to install SQL Server 2008 as named instance, so it actually overwrote SQL Server 2008 default instance. After installing SQL 2000, I was able to use SQL 2000 as default instance. After a few testing, I decided to uninstall SQL 2000 but there was some failure to do so because somehow uninst.isu cannot be found.<br />
<br />
I didn't have much to dig into it since I need to use SQL 2008 right away. So I tried to change default SQL instance to SQL 2008. Of course this is not recommended by Microsoft and never will be. Please note this is only to change default instance location and does not uninstall SQL 2000, which should be done later seperately.<br />
<br />
The steps are:<br />
<br />
1) Stop SQL Server and SQL Agent services<br />
2) To change default instance location, I did change the ImagePath of the following registry.<br />
<br />
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\ImagePath<br />
<br />
Old path pointed to SQL 2000 and I changed it to:<br />
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlservr.exe<br />
<br />
3) To change default SQL agent instance, I also change this registry:<br />
<br />
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT<br />
<br />
Old path pointed to SQL Agent 2000 path and changed to :<br />
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn\sqlagent.exe<br />
<br />
4) Start SQL Server and SQL Agent services.<br />
<br />
It worked for me, but might not work for you. I am documenting this for my future reference :-)<br />
Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-8790215210156020652013-05-22T15:32:00.001-07:002013-05-22T15:32:21.168-07:00Autogrow of file 'DB_log' in database was cancelled by user or timed out after xxxxx millisecondsRecently a production SQL Server caught my attention. Database users complained that they got an application exception "This SqlTransaction has completed; it is no longer usable." And more and more people got thie exceptions and during peak time no one actually was unable to make any transaction. <p>When I checked SQL Server Error log, the following exceptions kept occurred every 30 secs.<br/>
<b>Autogrow of file 'DB1_log' in database 'DB1' was cancelled by user or timed out after 18422 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.</b>
</p>
<div style="clear:both"></div>
As the error message suggested, I tried to reduce FILEGROWTH from 200MB to 100MB.
<pre>
ALTER DATABASE [DB1] MODIFY FILE ( NAME = N'DB1_log', MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
GO
</pre>
But it did not make big difference. The next day people complained again in peak time.
So I checked the DB log space.
<pre>
DBCC SQLPERF(logspace)
</pre>
This command displays current log size and its usage percentage for all databases.<br/>
It turned out that the database 'DB1' was 95GB big and 99.9% used. And it eventually reached 100%...<br/>
<br/>
Why did SQL Server fail to increase log file even if there are enough big disk space? <br/>
Well, do not know exactly. But since this was production server (and I am not a DBA for the database servers :-)), it has to come to usual state.
As you know, primarily this is a DBA job, which maintains transaction log properly.
And on a second thought, this might be a SQL Server bug (Note: SQL Server version was SQL 2008 v10.0.5768).
<p>I ended up restarting SQL Server service since no one actually can make single transaction.
And changed database recovery model from Full Recovery to Simple Recovery, which immediately release all log spaces.
Sure, this is not always good choice for everyone. Changing to Simple Recovery means that it can lose the data since last backup if any bad thing happens to the database. Fortunately the database was in RAID and also was allowed to take a risk to lose 1 day data. If you have finantial data in your database, sure, you can not take this approach.
</p>
<p>
In SQL 2008, BACKUP LOG DB1 WITH NO_LOG (or WITH TRUNCATE_ONLY) cannot be used. Instead, one has to switch recovery mode to Simple and then the log will be truncated. After log truncation, DB backup is normal required to be safe. And one can reconsider to change recovery mode back to Full recovery.</p>
Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-20964066347736558622013-04-08T17:00:00.001-07:002013-04-08T17:00:41.629-07:00How to deploy Analysis Service XMLA with PowershellOne of the ways to deploy SSAS OLAP database is to use .XMLA file. And this post explains a simple way of doing deployment using Powershell and XMLA. In order to deploy XMLA in Powershell, we use ADOMD to submit XMLA data to OLAP server.
As shown in the example below, first create an .NET connection instance from AdomdConnection class and specifiy data source to target server (which is localhost in this case). Please also note that you can specify port number (8080) after the server name, if SSAS server listens to non-default port (by default SSAS listens to TCP 2383).
<pre>
#
# NOTE: If machine did not enable Powershell script execution policy yet,
# first use this command to enable it (Run as Administrator) :
# PS> Set-ExecutionPolicy RemoteSigned
#
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices.AdomdClient") | out-null
# Open Server Connection
$serverName = "Data Source=localhost:8080"
$conn = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdConnection $serverName
$conn.Open()
# Read XMLA
$xmla = Get-Content .\MyOLAP.xmla
# Execute XMLA
$cmd = New-Object Microsoft.AnalysisServices.AdomdClient.AdomdCommand $xmla, $conn
$cmd.ExecuteNonQuery();
</pre>
Once the connection is open, read .xmla file and save file content to a powershell variable. And simply executes XMLA content against the connection by using ExecuteNonQuery() method.
As a side note, I used to use a batch file to deploy OLAP database with the following command.
<pre>
Microsoft.AnalysisServices.Deployment.exe .\StarliteOLAP.asdatabase /s
</pre>
This worked fine until I noticed that production server has non-default port number.
There seems a bug in Microsoft.AnalysisServices.Deployment.exe (SSAS 2008) which cannot deploy when port number is specified (technically when colon is specified).
When trying to run the command with :8080 is specified in target server, the following error occurred.
<pre>
The 'Name' property cannot contain any of the following characters: . , ; ' ` : / * | ? " & % $ ! + = ( ) [ ] { } < >
</pre>
Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-45107591886140864412013-03-29T10:26:00.003-07:002013-03-29T10:26:55.082-07:00Enable Trace Flag 1222Deadlock graph can be detected from SQL Profiler client tool <a href="http://sqlbeyond.blogspot.com/2012/12/how-to-capture-deadlock-for-database.html" target="_blank">as explained here</a>. This approach requires to keep the client tool running during monitoring periods, and it will probably be ideal for intensive monitoring work. If deadlock occurs seldom but we don't want to miss the deadlock details, you might want to enable SQL Server engine trace flag 1222. <br />
<br />
Trace flag 1222 outputs deadlock detail information to SQL Server Error log. It dumps the deadlock details in text format, so there is no fancy UI as in SQL Profiler. But the log contains all the same information as in SQL Profiler Deadlock Graph event, so some extra detective analysis will give deadlock picture.<br />
<br />
To enable trace flag 1222, go to SQL Server Configuration Manager (assuming SQL 2008).<br />
<div class="separator" style="text-align: center;">
<a href="http://1.bp.blogspot.com/-N8QrVGhCVLU/UVXNCZ-pi4I/AAAAAAAAAXE/iXgvAKrzsSY/s1600/T1222.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"></a><a href="http://1.bp.blogspot.com/-N8QrVGhCVLU/UVXNCZ-pi4I/AAAAAAAAAXE/iXgvAKrzsSY/s1600/T1222.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="326" src="http://1.bp.blogspot.com/-N8QrVGhCVLU/UVXNCZ-pi4I/AAAAAAAAAXE/iXgvAKrzsSY/s400/T1222.png" width="400" /></a></div>
<div style="clear: both;">
</div>
Click SQL Server Services on the left tree and doubeclick SQL Server instance.<br />
Go to Advanced tab and find [Startup Parameters].<br />
Add ";-T1222" and click OK.<br />
Semicolon is needed to separate each parameter and -T option means SQL engine trace flag.<br />
<br />
One drawback(?) of using SQL Server trace flag is that you need to restart SQL Server services to take effect. So in production server, maintenance should be scheduled properly.<br />
<br />
<br />
Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-72367304470478427062013-03-07T16:54:00.002-08:002013-03-07T16:54:42.764-08:00Convert rows to columns - PIVOTSuppose there is a table having multiple rows with a single column and you want to convert the data to a row with multiple corresponding columns. For example, you have the following table A that has only one column (col). The table A has 4 rows. Now let's say you want to convert it to a single row with 4 columns. <br />
<div style="clear: both;">
</div>
<br />
<pre>CREATE TABLE A(col int)
GO
insert A values (10)
insert A values (20)
insert A values (30)
insert A values (40)
GO
</pre>
<br />
<br />
This conversion can be done by using PIVOT. To make things easy, we can add another computed column (r) that has row number. First, we select the single column (col) and computed row number from source table. For each row, PIVOT statement asks to calcualte SUM of col, which basically the same col value in this case since there is no aggregation here. If column type is non-numeric value such as string type, other aggregate function such as MIN() can be used. So from those four rows, the pivot returns 4 column data.
<br />
<br />
<pre>SELECT [1],[2],[3],[4]
FROM (SELECT col, row_number() over (order by col) r
FROM A) AS sourceTable
PIVOT
(
SUM(col) FOR r IN ([1],[2],[3],[4])
) AS p
GO
</pre>
<br />
The result is as follows.
<br />
<br />
<a href="http://3.bp.blogspot.com/-zFuPxC8kNYI/UTk1fEHAFfI/AAAAAAAAAW0/qUrXpo40w5c/s1600/pivot-result.png" imageanchor="1"><img border="0" src="http://3.bp.blogspot.com/-zFuPxC8kNYI/UTk1fEHAFfI/AAAAAAAAAW0/qUrXpo40w5c/s320/pivot-result.png" /></a>
<br />
<br />
If rows are unknown, we can use dynamic pivot by adding the corresponding number of columns in IN clause and use <i>SELECT *</i> instead of <i>SELECT [1],[2],[3],[4]</i>. And the dynamic SQL will be run by using EXECUTE().
Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-9578737000209452032013-03-05T17:23:00.000-08:002013-03-05T17:33:48.751-08:00Locks in nested stored procedureWhen a stored procedure(SP) calls nested stored procedure, if both the outer SP and inner SP have begin tran - commit tran statement, how long are the (exclusive) locks for the inner DML statement going to be held? That is, if the inner SP commits transaction, will the locks that acquired in the inner SP transaction be released? Or are their locks going to last all the way up to commit statement of outmost SP?
<br />
<div style="clear: both;">
</div>
The short answer is it will last until the commit of the outmost SP.<br />
Here is an simple experiment that can provide some proof.<br />
<pre>CREATE PROC InnerSP
AS
BEGIN
BEGIN TRAN
UPDATE dbo.Tab1
SET Name='Name1'
WHERE Id=1
SELECT 'After Update Tab1'
SELECT * FROM sys.dm_tran_locks
WHERE resource_type != 'DATABASE'
UPDATE dbo.Tab2
SET City='Seattle'
WHERE Id=1
COMMIT TRAN
SELECT '(InnerSP) After Commit'
SELECT * FROM sys.dm_tran_locks
WHERE resource_type != 'DATABASE'
END
GO
CREATE PROC OuterSP
AS
BEGIN
BEGIN TRAN
EXEC InnerSP
-- cut --
COMMIT TRAN
SELECT '(OuterSP) After Commit'
SELECT * FROM sys.dm_tran_locks
WHERE resource_type != 'DATABASE'
END
GO
EXEC OuterSP
</pre>
<br />
OuterSP calls InnerSP and InnerSP has BEGIN TRAN - COMMIT TRAN. If the exclusive locks are released after innerSP commit,
dm_tran_locks will show no locks are held against Tab1 and Tab2 tables. But as shown below, the result is that the X locks are still intact after inner SP commit. Their locks are only released when outer SP commit is called.
<br />
<a href="http://3.bp.blogspot.com/-G8r4o8jmuGw/UTaUpmJPwbI/AAAAAAAAAWk/PjP5JYNHPHY/s1600/nested-sp.png" imageanchor="1"><img border="0" height="467" src="http://3.bp.blogspot.com/-G8r4o8jmuGw/UTaUpmJPwbI/AAAAAAAAAWk/PjP5JYNHPHY/s640/nested-sp.png" width="640" /></a>
<br />
<br />
The example above has UPDATE statements whose lock type is X (exclusive) lock. For Insert, Update, Delete, it holds X lock within a transaction, but for SELECT - within a transaction - the Shared (S) lock will be released once the rows are read in the default READ COMMITTED isolation level.
If transaction isolation level is higher, S lock will last until it meets COMMIT.
<br />
On another note, if ROLLBACK is called in nested SP, the entire transaction is rolled back regardless of nested SP level. For example, when you have a SP that calls nestedSP1 that calls nestedSP2, if you call ROLLBACK in nestedSP2, the whole transaction from outmost SP will be rolled back. To avoid this, you can limit the transaction scope by using save transaction point. An example shown below rolls back nestedSP transaction only and UPDATE in outerSP is still valid.<br />
<pre>
CREATE PROC NestedSP
AS
BEGIN
BEGIN TRY
SAVE TRAN T2
UPDATE dbo.Tab SET City='Redmond' WHERE Id=1
RAISERROR ('Error', 16, 1 );
COMMIT TRAN T2
END TRY
BEGIN CATCH
ROLLBACK TRAN T2
END CATCH
END
GO
CREATE PROC OuterSP
AS
BEGIN
BEGIN TRAN T1
EXEC NestedSP
-- cut --
UPDATE dbo.Tab SET City='Seattle' WHERE Id=1
COMMIT TRAN T1
END
GO
EXEC OuterSP
</pre>Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-81781262933600440452013-02-19T20:59:00.000-08:002013-02-19T20:59:01.139-08:00How to log value in SQL trace<a href="http://sqlbeyond.blogspot.com/2013/02/how-to-check-sp-output-value-in-profiler.html" target="_blank">Previous post</a> explains how to check OUTPUT value of stored procedure in SQL Profiler. But, if you have nested stored procedure and want to see output parameter value, it's not easy. This is becasue the nested stored procedure is not RPC call and so is not captured in RPC:Completed event. Nested SP will be captured in SP:Staring and SP:Complated events. So even if you check RPC Output Parameter, the value will not be shown in SQL trace.<br />
<br />
So how can we see the output value? If you use interactive tools such as SSMS, SQLCMD, this task is trivial. You can simply add PRINT or SELECT statement. But the problem is this statement does not print the value to SQL trace.<br />
<div style="clear: both;">
</div>
<div style="clear: both;">
Obviously there is no standard trace event class that captures this kind of output, but we can use user defined event, so-called UserConfigurable event class, to get around this problem.</div>
<br />
<br />
To create this event data from TSQL statement, we use <a href="http://msdn.microsoft.com/en-us/library/ms177548.aspx">sp_trace_generateevent</a> which generates trace event with user defined text value (and binary value if specified).<br />
<br />
The below GetNext SP has nested SP called GetNextNestedSP. Once the nest SP is called, we want to display OUTPUT parameter value to SQL trace. <br />
<br />
<pre>CREATE PROCEDURE [GetNext]
( @p1 NVARCHAR(32), @p2 NVARCHAR(32))
AS
BEGIN
DECLARE @out BIGINT
DECLARE @text nvarchar(128)
EXEC [GetNextNestedSP] @p1, @p2, @out OUTPUT
SET @text = CONVERT(nvarchar(32), @out)
EXEC master..sp_trace_generateevent 82, @text
-- cut --
END
</pre>
<br />
sp_trace_generateevent takes 3 parameters. First parameter is eventId for SQL trace. As MSDN says, this value is predefined and must be one of the event numbers from 82 through 91. ID 82 corresponds to [UserConfigurable:0] in SQL Profiler event class. 2nd parameter is text string that we want to send to SQL trace. This can be any string but there is a string length limitation (MSDN: 128 chars). 3rd parameter is optional binary data if needed. So when this SP (GetNext) is called from client application, how can we capture user defined event in SQL Profiler? <br />
<br />
<a href="http://3.bp.blogspot.com/-SO-0MdYyqfg/USQqjyOiaqI/AAAAAAAAAWU/78mIsk-vyME/s1600/userconfigurable-select.png" imageanchor="1"><img border="0" height="408" src="http://3.bp.blogspot.com/-SO-0MdYyqfg/USQqjyOiaqI/AAAAAAAAAWU/78mIsk-vyME/s640/userconfigurable-select.png" width="640" /></a> <br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-JqFf5ho_Kfg/USQmG3JX6jI/AAAAAAAAAWE/BFPUSW6b-78/s1600/userconfigurable-event.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="252" src="http://1.bp.blogspot.com/-JqFf5ho_Kfg/USQmG3JX6jI/AAAAAAAAAWE/BFPUSW6b-78/s640/userconfigurable-event.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-35769456505054890062013-02-19T20:56:00.000-08:002013-02-19T20:56:41.430-08:00How to check SP output value in ProfilerWhen stored procedure is called from client application, the call is typically made by using RPC call. So in order to capture SP traces from Profiler, typically RPC:Starting event and RPC:Completed event are checked and those settings are enough in most times. However, sometimes there are chances you want to check out output value of the stored procedure call. Especially where many concurrent calls occur in production server.<br />
<br />
<div style="clear: both;">
</div>
<div>
To capture the return value of SP, we need to enable RPC Output Parameter event. </div>
<br />
1) Run Profiler.exe<br />
2) Connect to SQL Server<br />
3) In Trace Properties dialog, select TSQL_Replay template (or other appropriate for your purpose) in [Use this template].<br />
4) Goto [Event Selection] tabe<br />
5) Make sure [RPC Output Parameter] is selected.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-NjEO5hAEEHc/USQRDCUeFxI/AAAAAAAAAVU/MSpUGLT6jaU/s1600/rpc_output_parameter.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="408" src="http://4.bp.blogspot.com/-NjEO5hAEEHc/USQRDCUeFxI/AAAAAAAAAVU/MSpUGLT6jaU/s640/rpc_output_parameter.png" width="640" /></a></div>
<br />
<br />
6) Start trace.<br />
<br />
The result of OUTPUT parameter will be shown in RPC:Completed event. When the row for RPC:Completed is selected in trace, the bottom pane show TextData and you will see output parameter value before SP call. In the example below, output @p3 is set to 562. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-3IFotgdrQ9M/USQSKfly9XI/AAAAAAAAAVg/1s2RjVYGvvU/s1600/rpc_output_result.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="286" src="http://3.bp.blogspot.com/-3IFotgdrQ9M/USQSKfly9XI/AAAAAAAAAVg/1s2RjVYGvvU/s640/rpc_output_result.png" width="640" /></a></div>
<br />
<br />Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-72975633370820317652013-02-07T17:16:00.002-08:002013-02-07T17:17:41.011-08:00SQL Backup Error : Cannot open backup device NT AUTHORITY\SYSTEM Even if backing up database directly to remote server, some people often do this because they worry about the backup file loss in case of the DB machine crash. Sure better approach will be to backup DB to local disk and copy the .bak file to remote file share.<br />
Anyway, the topic of this post is about database backup error 3201. SQL Server service was running as NT AUTHORITY\SYSTEM account and daily backup job was scheduled as SQL Agent job. Backup job directly backs up database to remote file share. When the job was run, the following error occurred.<br />
<div style="clear: both;">
</div>
<br />
Message
Executed as user: NT AUTHORITY\SYSTEM. Backing up DB to \\BAKSERVER\DBBackups\DB_FULL_Daily_2013_02_01_14_00_00.bak... [SQLSTATE 01000] (Message 0) Cannot open backup device '\\BAKSERVER\DBBackups\DB_FULL_Daily_2013_02_01_14_00_00.bak'. Operating system error 5(Access is denied.). [SQLSTATE 42000] (Error 3201) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013)Unable to open Step output file. The step failed.
<br />
<br />
The error message is clear - access denied. The machine cannot access the remote file share.
<br />
This is because the BACKUP statement is run by SQL Server service account which is SYSTEM account and the SYSTEM cannot access remote fileshare. Even if SYSTEM account can do everything in local machine, it does not have permission to other machines.<br />
<br />
So the possible solution might be (1) change SQL Server service account to other domain account (2) add machine account to a domain (or machine) group and give share permission to the group.
<br />
<br />
<b>How to add machine account manually</b><br />
<br />
1) Open Computer Management<br />
2) Goto [Local Users and Groups] - [Groups]<br />
3) Doubleclick a group you want to use<br />
4) By default, [Computer] object type is unchecked, so you have enable the [Type].<br />
5) Click [Object Types] button<br />
6) Check [Computer] and OK<br />
<br />
<a href="http://3.bp.blogspot.com/-obAWq7c6RAM/URRNj_fpAGI/AAAAAAAAAVE/x7XB0PoiQ4g/s1600/add-computer-account.png" imageanchor="1"><img border="0" height="285" src="http://3.bp.blogspot.com/-obAWq7c6RAM/URRNj_fpAGI/AAAAAAAAAVE/x7XB0PoiQ4g/s400/add-computer-account.png" width="400" /></a>
<br />
<br />
7) And then add machine name
<br />
<br />
<b>How to add machine account by using script</b><br />
<br />
The following is a VBScript example that adds a machine account to local Administrators group.
<br />
<pre>Set Netw = WScript.CreateObject("WScript.Network")
Local = Netw.ComputerName
DomainName = "MyDomain"
MachineAccount = "DBSERVER1"
Set Group1 = GetObject("WinNT://"& local &"/Administrators")
Group1.Add "WinNT://"& DomainName &"/"& MachineAccount &"$"
</pre>
<br />
<br />Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-3633969269584280092013-01-23T16:59:00.001-08:002013-01-23T16:59:09.853-08:00SET NOEXEC ON : avoid invalid object errorWhen SET NOEXEC is ON, SQL Server compiles Transact-SQL statements but does not execute them. If one uses this SET command in the middle of SQL scripts, the SQL statements are not executed until it meets SET NOEXEC OFF.<br />
So this SET NOEXEC is pretty convenient when we want to block some portion of the codes, but there is some limitation. Since SET NOEXEC <b>compiles</b> TSQL statements, if there is any compilation error, it will raise an error. For example, if there is invalid object such as invalid table name or invalid database name in the script, error will be raised. Sometimes this is not the desired result.
<div style="clear:both"></div>
Say, we might want to use [USE db] statement even when the DB is not already created since the script will not be executed anyway. Take the following example. If TEST db does not exist, the script will raise an error and depending on the application running this script, the error might become an exception and no further statements will be executed.
<pre>
-- cut above --
SET NOEXEC ON
USE [TEST]
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = 'Tester')
BEGIN
CREATE USER [Tester] FOR LOGIN [Tester]
EXEC sp_addrolemember N'db_datareader', N'Tester'
EXEC sp_addrolemember N'db_datawriter', N'Tester'
END
GO
SET NOEXEC OFF
USE master
GO
SELECT * FROM sys.data_spaces;
-- cut below --
</pre>
So the solution? I put the the statement that might cause an error into EXEC(). This way we can postpone any error until we actually execute them. So there will be no compilation error and the script will run even if there is no TEST db.
<pre>
-- cut above --
SET NOEXEC ON
EXEC ('USE [TEST];
IF NOT EXISTS(SELECT * FROM sys.database_principals WHERE name = ''Tester'')
BEGIN
CREATE USER [Tester] FOR LOGIN [Tester]
EXEC sp_addrolemember N''db_datareader'', N''Tester''
EXEC sp_addrolemember N''db_datawriter'', N''Tester''
END')
GO
SET NOEXEC OFF
USE master
GO
SELECT * FROM sys.data_spaces;
-- cut below --
</pre>Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-14106791245974691032013-01-16T18:00:00.002-08:002013-01-16T18:00:49.137-08:00SQLCMD - Calling :r in the middle of batch?When we run external .SQL file from a SQL script, we can call the external script file by using :r command. For example, <b>:r test.sql</b> reads the test.sql and append it to the statement cache and run it. So this is pretty convenient command when one script calls multiple external SQL files. But can we use the :r command in the middle of a SQL batch? I recently got some trouble of using :r command in the middle of a batch. Especially if the external script file has multiple batches separated by GOs, using :r in the middle seems almost impossible.<br />
<br />
<div style="clear: both;">
</div>
Let us take a look at a simplified example. I have 3 branches in terms of control flow. <br />
(1) First if [MainControl] table does not have Stop sign, we go to main script which is CREATE DATABASE MyDB statement.<br />
(2) If [MainControl] table has Stop sign and [SubControl] has [RunScript1_And_Exit] sign, we need to run external Script1.sql file and exit immediately.<br />
(3) If [MainControl] table has Stop sign and [SubControl] does not have [RunScript1_And_Exit] sign, we exit immediately.<br />
So we probably bring up this kind of solution as you see below. And of course, please note this is SQLCMD mode script.
<br /><br />
<pre>IF EXISTS (SELECT * FROM [MainControl] WHERE FLAG = 'Stop')
IF EXISTS (SELECT * FROM [SubControl]
WHERE FLAG = 'RunScript1_And_Exit')
:r Script1.sql
ELSE
RAISERROR('Stop. Exit now.', 20, -1) WITH LOG;
GO
CREATE DATABASE MyDB
GO
-- MORE SCRIPTS HERE
--
</pre>
<br />And sure it did not work. Calling Script1.sql in the middle of a batch overrode existing control flow and all messed up thereafter.
The solution that I bring up is (1) use :r command in separate single batch (2) and build control flow around :r command. I used temporary table since it can be used in multiple batches. (3) Use SET NOEXEC ON/OFF to control :r execution.
<br />
<br />
<pre>CREATE TABLE #RunScript1_Exit(flag BIT)
IF EXISTS (SELECT * FROM [MainControl] WHERE FLAG = 'Stop')
IF EXISTS (SELECT * FROM [SubControl]
WHERE FLAG = 'RunScript1_And_Exit')
INSERT #RunScript1_Exit VALUES (1)
ELSE
RAISERROR('Stop. Exit now.', 20, -1) WITH LOG;
GO
IF NOT EXISTS (SELECT * FROM #RunScript1_Exit)
SET NOEXEC ON
GO
:r Script1.sql
GO
SET NOEXEC OFF
GO
IF EXISTS (SELECT * FROM #RunScript1_Exit)
BEGIN
RAISERROR('Ran Script1.sql and now Exit.', 20, -1) WITH LOG;
END
GO
CREATE DATABASE MyDB
GO
-- MORE SCRIPTS HERE
--
</pre>
<br /><br />
SET NOEXEC ON ignores all the SQL statements until it meets SET NOEXEC OFF. So by using this, we can control whether we can run :r command or not. And also RAISERROR WITH LOG was used to exit the script. RAISERROR with sevirity 20 terminates connection and exits immediatly.
<br />Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-68321073249811125352012-12-23T20:37:00.000-08:002013-10-01T14:19:13.160-07:00Deadlock in highly concurrent situationI recently experienced a deadlock case which happened in highly concurrent situation. So many concurrent access were requested at the same time against a single stored procedure and that caused a lot of deadlock situations. I think real case was more complex than what I describe here, but I just want to summarize some interesting deadlock case here.<br />
<br />
The deadlock case occurred around a stored procedure called GetNextId. This SP returns a next job id from Job and JobQueue tables based on certain condition.<br />
<br />
CASE 1: Here is the SP that caused deadlock. JobJobQueueView is a simple view that joins Job an JobQueue table based on JobId. So the SP selects a next job id from the view with certain where condition. It works fine in normal sitation but if highly concurrent calls are made, deadlock situation occurred.<br />
<br />
<pre>WITH CTE1 (NextJobId) AS
(
SELECT TOP 1 JobId
FROM JobJobQueueView WITH (HOLDLOCK)
WHERE (JobType = @JobType)
AND (SentToMachine IS NULL)
ORDER BY Priority DESC, CreateTime ASC
)
UPDATE JobQueue
SET @JobId = JobId, SentToMachine = @MachineName,StartTime = GETDATE()
FROM CTE1
WHERE JobId = COALESCE(CTE1.NextJobId, -1);
-- @JobID is OUTPUT
</pre>
<br />
The problem is : one SPID (55) holds Shared (S) lock on JobQueue table (and Job) when executing SELECT statement. At the same time another SPID (56) hold Shared lock on JobQueue while running SELECT. Since it has HOLDLOCK lock hint, the Shared locks are not released. Now SPID 55 tried to acquire exclusive X lock on JobQueue table to update data but cannot get it because SPID 56 holds Shared lock. At the same time, SPID 56 tried to get X lock on JobQueue but failed because SPID 55 still holds the S lock. Hence the deadlock. HOLDLOCK has the same effect of setting transaction isolation level to SERIALIZABLE.<br />
<br />
CASE 2: To eliminate the side effect of HOLDLOCK (or SERIALIZABLE level), the HOLDLOCK lock hint was removed. Now no deadlock occurred but there is another side effect which only occurs in highly concurrent situation - the race condition. Some GetNextId requests got the same Id.<br />
<br />
<pre>WITH CTE1 (NextJobId) AS
(
SELECT TOP 1 JQ.JobId
FROM Job J, JobQueue JQ
WHERE J.JobId = JQ.JobId
AND (SentToMachine IS NULL)
ORDER BY Priority DESC, CreateTime ASC
)
UPDATE JobQueue
SET @JobId = JobId, SentToMachine = @MachineName, StartTime = GETDATE()
FROM CTE1
WHERE JobId = COALESCE(CTE1.NextJobId, -1);
-- @JobId is OUTPUT
</pre>
<br />
The problem is : one SPID (55) holds Shared (S) lock on JobQueue and Job tables while executing SELECT, and at the same time another SPID (56) holds Shared (S) lock on JobQueue during SELECT, so they have the same next job id. But each Shared lock will be released as soon as the SELECT ends. One of them, say SPID 55, goes into UPDATE and hold exclusive lock on JobQueue table. SPID 56 will wait for X lock release. As soon as X lock is released from 55, 56 will update with the same id. There are other cases similar to this race condition. For example, SPID 55 release S lock after SELECT and if SPID 56 manages to read and write the same next job id before SPID 55 gets X lock on JobQueue, it is also possible for two SPIDs to have the same id.<br />
<br />
CASE 3: To eliminate the side effect of case 2, I put UPDLOCK lock hint on JobQueue table. UPDLOCK allows other SPIDs to have shared lock but disallows others to have exclusive or update lock. This prevents the same id to be allowed at the same time.<br />
<br />
<pre>WITH CTE1 (NextJobId) AS
(
SELECT TOP 1 JQ.JobId
FROM Job J, JobQueue JQ WITH (UPDLOCK)
WHERE J.JobId = JQ.JobId
AND (JobType = @JobType)
AND (SentToMachine IS NULL)
ORDER BY Priority DESC, CreateTime ASC
)
UPDATE JobQueue
SET @JobId = JobId, SentToMachine = @MachineName, StartTime = GETDATE()
FROM CTE1
WHERE JobId = COALESCE(CTE1.NextJobId, -1);
-- @JobId is OUTPUT
</pre>
<br />
By holding Update lock on JobQueue table during SELECT, another SPID cannot acquire UPDLOCK at the same time and should wait until the U lock is released. When one SPID (55) holds UPDLOCK in SELECT, it allows others to get S lock but disallows U or X lock. Another SPID (56) enters SELECT statement but cannot acquire U lock since it is already held by 55. SPID 56 will simply wait until 55 is done with update. Once 55 completes UPDATE statement, SPID 56 can proceed with SELECT statement where it will get next job id. When 55 executes UPDATE JobQueue statement, 55 will release U lock and acquire X lock (can be expressed as converting U to X lock) and so no other SPIDs or threads cannot access this resource.<br />
<br />
Note: I wrote a C# test application to simulate deadlock. <a href="http://dotnetbeyond.blogspot.com/2012/12/c-simulate-concurrent-calls-to-sql.html" target="_blank">Click here</a> for details.Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-56428006349946322842012-12-23T11:05:00.004-08:002013-02-14T14:25:42.981-08:00Detect SQL script changes by using .SQL file checksumI recently spent some time to find out the way of detecting the modification of SQL script file. Why I needed it is not that important, but quick summary is 'there is a application running a SQL script every 15 mins; if there is any change in script it runs the script; otherwsie it exits; to specify the script changes, the script has version number variable (in SQLCMD variable) and compare it with a SQL table (version table containing latest version data).' The downside of using version number in SQL script is that everytime someone changes the script, s/he also should manually increment version number. Sometime people forgot to do it, hence the bothersome problem.<br />
<div style="clear: both;">
</div>
So the solution I bring up is to detect SQL script file change by using the hash checksum of the file. <br />
First step is to read the SQL script file in BLOB format by using OPENROWSET().<br />
<br />
<pre>:setvar script1 C:\Script1.sql
DECLARE @val BIGINT
SELECT @val = master.sys.fn_repl_hash_binary(BulkColumn)
FROM OPENROWSET(BULK '$(script1 )', SINGLE_BLOB) AS A
</pre>
<br />
OPENROWSET(BULK ...) uses BULK rowset provider that accepts full file path and read mode such as BLOB, CLOB. The sentance above returns binary data of the file in BulkColumn.<br />
<br />
Next step is to hash the binary data. SQL Server has a built-in function called fn_repl_hash_binary() that accepts binary data and returns a hash value.<br />
<br />
So by comparing this @val with the version column value of version table, I was able to decide whether the SQL script is modified or not.<br />
<br />
One of the problem I ran into though is OPENROWSET(BULK) only accepts FULL file path. I only can specify relative path in my case. So it was a headache... Fortunately the SQL script used SQLCMD mode,so I can get around this problem by using this approach.<br />
<br />
(1) First at the begining of main script, I added the following statements. It calls VersionCheck.cmd batch file (see (2)) that will create _ChkSum.sql. Running _ChkSum.sql actually calculates the checksum of SQL script file(s) and save the result to #ChkSum temp table. Once _ChkSum.sql is run, I read the #ChkSum temp table and fetch the chksum value. Then I simply compare the variable with stored SQL table value.<br />
<br />
<pre>-- Run version check. If there is no script change, exit.
:!! VersionCheck.cmd
:r _ChkSum_.sql
GO
DECLARE @chk NUMERIC(38)
SELECT TOP 1 @chk = chk FROM #ChkSum
IF EXISTS (SELECT * FROM Master.Sys.Databases WHERE name = 'MyDB')
IF EXISTS (SELECT * FROM $(dbName).Sys.Tables WHERE name = 'VersionControl')
IF EXISTS (SELECT * FROM $(dbName).dbo.VersionControl
WHERE Id=1 AND ChkSum=@chk)
BEGIN
RAISERROR('No change. Exit now.', 2, 1);
END
GO
</pre>
<br />
<br />
(2) Here is VersionCheck.cmd. This file contains OPENROWSET() statement that calculates SQL Script file checksum. The example below calculate the checksum of two SQL files and add them up. <br />
<br />
<pre>@echo off
@echo :setvar script1 script1.sql > _ChkSum_.sql
@echo :setvar script2 script2.sql >> _ChkSum_.sql
@echo SET NOCOUNT ON >> _ChkSum_.sql
@echo DECLARE @val BIGINT >> _ChkSum_.sql
@echo DECLARE @chk NUMERIC(38) >> _ChkSum_.sql
@echo SELECT @val = master.sys.fn_repl_hash_binary(BulkColumn) >> _ChkSum_.sql
@echo FROM OPENROWSET(BULK '%~dp0$(script1)', SINGLE_BLOB) AS A >> _ChkSum_.sql
@echo SET @chk = CAST(@val as NUMERIC(38)) >> _ChkSum_.sql
@echo SELECT @val = master.sys.fn_repl_hash_binary(BulkColumn) >> _ChkSum_.sql
@echo FROM OPENROWSET(BULK '%~dp0$(script2)', SINGLE_BLOB) AS A >> _ChkSum_.sql
@echo SET @chk = @chk + CAST(@val as NUMERIC(38)) >> _ChkSum_.sql
@echo CREATE TABLE #ChkSum(chk NUMERIC(38)) >> _ChkSum_.sql
@echo INSERT #ChkSum VALUES (@chk) >> _ChkSum_.sql
@echo GO >> _ChkSum_.sql
</pre>
<br />
<ul>
<li>To specify absolute full file path, I used <strong>%~dp0 </strong>in OPENROWSET(). This will be replaced by current working directory.</li>
<li>Please note that I used @chk variable as NUMERIC(38) because arithmetic overflow can occur if @chk is defined as BIGINT.</li>
<li>After sum of each checksum is added up, I saved the value to temp table (#ChkSum). Temp table can last in session so it can read in main script.</li>
</ul>
As a downside, using this approach might be slow if file is huge since it requires file reading. <br />
But with relatively big file, this approach worked fine to me.Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-1115050269264813332012-12-22T21:25:00.003-08:002012-12-22T21:25:21.138-08:00How to capture DeadLock for a databaseThis article briefly explains how to capture DEADLOCK error that occurs at one database. In many cases, we want to know where the deadlock occurs for a specific target database.<br />
<br />
(1) Start SQL Profiler<br />
(2) Connect to SQL server<br />
(3) In [Trace Properties] dialog, select [TSQL-Locks] template in [Use this template].<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-c71rG7kItnk/UNaR57l9mlI/AAAAAAAAATo/H7Ys7u_MTy0/s1600/tsql_locks.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" eea="true" height="407" src="http://1.bp.blogspot.com/-c71rG7kItnk/UNaR57l9mlI/AAAAAAAAATo/H7Ys7u_MTy0/s640/tsql_locks.png" width="640" /></a></div>
<br />
(4) In [Trace Properties] dialog, click [Events Selection] tab. Uncheck all unwanted events. I tend to uncheck all events except [Deadlock graph] when profiling against production server to reduce noise.<br />
(5) In [Events Selection] grid, select [Database Name] column header.<br />
(6) In [Edit Filter] dialog, expand [Like] and type your target database name. Click OK.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-HVOKeNytWCI/UNaSEry3OGI/AAAAAAAAATw/Kl_n0dLj5rQ/s1600/deadlock.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" eea="true" height="406" src="http://3.bp.blogspot.com/-HVOKeNytWCI/UNaSEry3OGI/AAAAAAAAATw/Kl_n0dLj5rQ/s640/deadlock.png" width="640" /></a></div>
<br />
(7) Click [Run] to start profiling.<br />
(8) Once captured all deadlock information you need, click [Stop] toolbar button.<br />
(9) To save trace, save .trc file using File->Save. <br />
<br />
(10) Now to review deadlock graph, click [Deadlock graph] row in the grid and the graph will be shown at the bottom. By rightclicking in the graph pane, you can select [Zoom to fit] to adjust size. A circle with blue X is the deadlock victim. If you put mouse over the circle, you will see TSQL statement in the process was executing. Owner mode means the process (spid) owned the specific lock and Request mode means the process tried to get the lock but failed to get it.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://2.bp.blogspot.com/-FmJ9TwtEjwc/UNaUEAK_KWI/AAAAAAAAAUA/9eULa1WXpAY/s1600/deadlock_exam.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" eea="true" height="418" src="http://2.bp.blogspot.com/-FmJ9TwtEjwc/UNaUEAK_KWI/AAAAAAAAAUA/9eULa1WXpAY/s640/deadlock_exam.png" width="640" /></a></div>
<br />
(11) If deadlock graph is pretty complex, you might want to save the trace as XML file since it will have a lot more details. XML trace file contains all TSQL statements, call frames and resource lock relations. To save trace as XML, goto File->Save As -> Trace XML file. Reading XML trace file needs more detective work but sometimes deadlock graph UI does not provide clear view due to complex lock relationship and in that case reading XML trace will help a lot.<br />
<br />
Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com1tag:blogger.com,1999:blog-3604280969084941189.post-39533187538702573722012-11-15T22:47:00.002-08:002012-11-16T11:52:13.662-08:00Login failed. User must change password at next login<div style="clear: both;">
When new SQL login is created by using SSMS - New Login dialog, we tend to give a SQL Login name and its password, and probably set default DB. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-bvHRROmPn5Q/UKaZCxkEemI/AAAAAAAAANA/rQSAdE_nzmM/s1600/createLogin.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="358" src="http://3.bp.blogspot.com/-bvHRROmPn5Q/UKaZCxkEemI/AAAAAAAAANA/rQSAdE_nzmM/s400/createLogin.png" width="400" /></a></div>
<br />
We, then, use the login information to connect to SQL Server in our application. If it's an ASP.NET web page, some kind of DB processing codes as below would be added.<br />
<br /></div>
<pre>public partial class _default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string strConn = "Data Source=(local);Initial Catalog=MyDB;User Id=MyUser;Password=myPasswd!1";
DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(strConn))
{
conn.Open();
string sql = "SELECT * FROM Customer";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
adapter.Fill(ds);
}
Gridview1.DataSource = ds.Tables[0];
Gridview1.DataBind();
}
}
</pre>
<br />
If the web page are published to local IIS web application and we browse the web page in IE, we might get HTML 404 error as follows.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-p6bILJmwpFc/UKXfgpZcWiI/AAAAAAAAAMo/cxOGr_y2tRc/s1600/html-error.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="295" rea="true" src="http://4.bp.blogspot.com/-p6bILJmwpFc/UKXfgpZcWiI/AAAAAAAAAMo/cxOGr_y2tRc/s400/html-error.png" width="400" /></a></div>
<br />
In order to figure out what's going on, if we debug the web page in Visual Studio, we will find more clue about the error : SQL login failure. Even though username and password are correct, we might get login failure. <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-oHAIsv6_rOo/UKXgLtoYGcI/AAAAAAAAAMw/vyIpJO8zOCA/s1600/login-failed.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="238" rea="true" src="http://3.bp.blogspot.com/-oHAIsv6_rOo/UKXgLtoYGcI/AAAAAAAAAMw/vyIpJO8zOCA/s400/login-failed.png" width="400" /></a></div>
<br />
So the reason is SSMS New Login dialog basically enables [User must change password at next login] by default. Since ASP.NET application cannot show new password dialog, it just throws exception and simply dies.<br />
<br />
To address this login failure, we can reset password and turn off expiration check as follows.<br />
<br />
<pre>ALTER LOGIN [MyUser2] WITH PASSWORD = 'myPasswd!1'
GO
ALTER LOGIN [MyUser] WITH CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
</pre>
<br />
Once the TSQL is run, refreshing the ASP.NET webpage should work.Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-65816955098098127332012-09-19T16:04:00.001-07:002012-09-19T16:09:39.711-07:00How to enable FileStream in C#FILESTREAM can be enabled typically either during SQL installation or using SQL Configuration Manager. This post shows how to enable FILESTREAM in C#. This example can apply to any SQL Server 2005 or above if you specify WMI namespace correctly. (For more details about SQL WMI namespace, please refer to <a href="http://sqlbeyond.blogspot.com/2010/02/sql-wmi-provider.html">this POST (SQL WMI Provider)</a>).
<div style="clear:both"></div>
The C# code below is similar to the example of my previous post (<a href="http://sqlbeyond.blogspot.com/2012/04/how-to-check-if-filestream-is-enabled.html">How to check if the FILESTREAM is enabled</a>).
The difference is this time we call a method to change FILESTREAM setting instead of reading its properties.
<pre>
public class SqlWmi
{
const int FILESTREAM_FULL_ACCESS = 3;
const uint RESTART_REQUIRED = 0x80070bc3;
string DEFAULT_SHARE = string.Empty;
string SqlWmiPath = @"\\.\root\Microsoft\SqlServer\ComputerManagement11";
string WqlSqlFs = "SELECT * FROM FileStreamSettings WHERE InstanceName='SQLEXPRESS'";
public bool EnableFileStream()
{
ManagementScope scope = new ManagementScope(SqlWmiPath);
int arch = Environment.Is64BitOperatingSystem ? 64 : 32;
scope.Options.Context.Add("__ProviderArchitecture", arch);
scope.Connect();
WqlObjectQuery wql = new WqlObjectQuery(WqlSqlFs);
ManagementObjectSearcher searcher = new ManagementObjectSearcher(scope, wql);
ManagementObjectCollection moColl = searcher.Get();
foreach (ManagementObject m in moColl)
{
ManagementBaseObject param = m.GetMethodParameters("EnableFileStream");
param["AccessLevel"] = FILESTREAM_FULL_ACCESS;
param["ShareName"] = DEFAULT_SHARE;
var output = m.InvokeMethod("EnableFileStream", param, null);
if (output != null)
{
uint retValue = (uint)output.GetPropertyValue("ReturnValue");
if (retValue == 0 || retValue == RESTART_REQUIRED)
{
return true;
}
}
}
return false;
}
}
</pre>
FileStreamSettings SQL WMI class has only one method called EnableFileStream. When calling this method, we pass two parameters - one for AccessLevel, the other for Share Name. Valid values for Access Level are 0,1,2,3. If you want to give FULL access, you specify 3. For the second parameter Share Name, most likely we use default share name and if you want to specify default share name, you simply pass empty string (rather than full instance name, even though that will also work).
Calling a SQL WMI method is done by using InvokeMethod() of ManagementObject instance. There is an output of this method and it has return value from SQL WMI call. If everything is fine, it returns zero but you might get 0x80070bc3 which is a predefined value for [restart required] in the SQL WMI provider. For this case, you need to restart SQL Server instance.
Some bullet points to add:
<ul>
<li>You have to run the program in administrator permission</li>
<li>This sample code points to SQL Server 2012. If you want to use 2005-2008, you need to change SqlWmiPath to ComputerManagement (2005) or ComputerManagement10 (2008/R2)</li>
<li>To make the code work in both 64bit and 32bit, __ProviderArchitecture was added. (for more details, please refer to <a href="http://sqlbeyond.blogspot.com/2010/03/how-to-call-32bit-64bit-sql-wmi.html">this POST</a>)</li>
</ul>
Lastly, <b><u>can we disable FILESTREAM using SQL WMI?</b></u>
Yes, you simply set 0 to AccessLevel parameter before you call EnableFileStream() method...
Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-55422452680699625472012-09-08T21:25:00.001-07:002012-09-08T21:34:58.453-07:00SQL 2012 setup error : SQL Server setup media does not support the language of the OS<div style="margin: 0in 0in 0pt;">
<span style="color: black;"><span style="font-family: Verdana;">Problem : I ran into the following Setup error when trying to install SQL Server 2012. The message box showed up as soon as I ran setup.exe.</span></span></div>
<div style="margin: 0in 0in 0pt;">
<br /></div>
<div style="margin: 0in 0in 0pt;">
<span style="color: black;"><span style="font-family: Verdana;"><div style="margin: 0in 0in 0pt;">
<span style="color: black; font-size: 8pt;"><span style="font-family: Verdana; font-size: small;"><em>SQL Server setup media does not support the language of the OS, or does not have the SQL Server English-language version installation files. Use the matching language-specific SQL Server media; or install both the language specific MUI and change the format and system locales through the regional settings in the control panel.</em></span></span></div>
</span></span><div style="margin: 0in 0in 0pt;">
</div>
<div style="margin: 0in 0in 0pt;">
<br /></div>
<div style="margin: 0in 0in 0pt;">
This error occurred even if I tried to install english SQL Server on english Windows 7 OS (with default Regional setting en-US)</div>
<div style="margin: 0in 0in 0pt;">
<br /></div>
<div style="margin: 0in 0in 0pt;">
<span style="color: black;"><span style="font-family: Verdana;">My Steps:</span></span></div>
<div style="margin: 0in 0in 0pt;">
<span style="color: black;"><span style="font-family: Verdana;">1) Downloaded SQL Server 2012 Developer Edition (ENU) from MSDN subscription web site.</span></span></div>
<div style="margin: 0in 0in 0pt;">
<span style="color: black;"><span style="font-family: Verdana;">2) Extract .iso by using WinZIP</span></span></div>
<div style="margin: 0in 0in 0pt;">
<span style="color: black;"><span style="font-family: Verdana;">3) Ran setup.exe from the extraction target folder</span></span></div>
<div style="margin: 0in 0in 0pt;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-dJumYndRYMQ/UEwYK-9DFUI/AAAAAAAAAL8/hTb4buUB-48/s1600/setup-error.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" hea="true" src="http://4.bp.blogspot.com/-dJumYndRYMQ/UEwYK-9DFUI/AAAAAAAAAL8/hTb4buUB-48/s1600/setup-error.png" /></a></div>
<div style="margin: 0in 0in 0pt;">
</div>
<div style="margin: 0in 0in 0pt;">
<span style="color: black; font-size: 8pt;"><div style="margin: 0in 0in 0pt;">
<span style="color: black; font-size: 8pt;"><span style="font-family: Verdana; font-size: small;">What I did:</span></span></div>
<div style="margin: 0in 0in 0pt;">
<span style="color: black; font-size: 8pt;"><span style="font-family: Verdana; font-size: small;">Downloaded WinRAR and extact .iso again. It worked fine to me.</span></span></div>
<div style="margin: 0in 0in 0pt;">
</div>
</span></div>
</div>
Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com0tag:blogger.com,1999:blog-3604280969084941189.post-27443477044303187252012-08-29T11:14:00.003-07:002016-03-30T20:04:34.526-07:00VS 2012 Error : Could not load file or assembly 'Microsoft.SqlServer.Management.Sdk.Sfc, Version=11.0.0.0<span style="font-family: "segoe ui";">When I tried to create new Connection in Server Explorer in Visual Studio 2012, I ran into the following error.</span><br />
<u><span style="font-family: "segoe ui"; font-size: x-small;"></span></u><br />
<u><span style="font-family: "segoe ui"; font-size: x-small;">Could not load file or assembly 'Microsoft.SqlServer.Management.Sdk.Sfc, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.</span></u><br />
<u><span style="font-family: "segoe ui";"></span></u><br />
<span segoe="segoe" style="font-family: "segoe ui";" ui="ui"></span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://4.bp.blogspot.com/-vPphjUuw9qE/UD5W7mvohuI/AAAAAAAAALI/0RgUP8pVxJs/s1600/add-connection.bmp" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="400" src="https://4.bp.blogspot.com/-vPphjUuw9qE/UD5W7mvohuI/AAAAAAAAALI/0RgUP8pVxJs/s400/add-connection.bmp" width="272" /></a></div>
<br />
<u><span style="font-family: "segoe ui";"></span></u><br />
<span style="font-family: "segoe ui";">When clicked [Test Connection], it was succeeded but when OK button is clicked, I got the [assembly not found] error.</span><br />
<span style="font-family: "segoe ui";"></span><br />
<span style="font-family: "segoe ui";">From the error message, we can see there is some missing component in VS 2012 installation.</span><br />
<span style="font-family: "segoe ui";">The assembly in question - Microsoft.SqlServer.Manangement.Sdk.Sfc version 11 - is a component DLL for SMO assemblies (even though SFC was originally created for more ambitious SSMS extension), and it is included in <span style="font-family: "times new roman"; font-size: small;">SQL Server 2012 Shared Management Objects.</span></span><br />
<span style="font-family: "segoe ui"; font-size: x-small;"></span><br />
<span segoe="segoe" style="font-family: "segoe ui";" ui="ui">[SQL 2012 Shared Management Objects] has dependency on [SQL System CLR types] component, so you have to install SQL Server System CLR Types first.</span><br />
<span style="font-family: "segoe ui"; font-size: x-small;"></span><br />
<span style="font-family: "segoe ui";">[SQL Server 2012 System CLR Types] can be found in the middle of SQL 2012 Feature Pack web page ( <a href="http://www.microsoft.com/en-us/download/details.aspx?id=29065">http://www.microsoft.com/en-us/download/details.aspx?id=29065</a> )</span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-glQcU1_TckU/UD5beXF4NkI/AAAAAAAAALk/WT7RdTKyQVs/s1600/SystemCLRTypes.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="201" src="https://3.bp.blogspot.com/-glQcU1_TckU/UD5beXF4NkI/AAAAAAAAALk/WT7RdTKyQVs/s640/SystemCLRTypes.png" width="640" /></a></div>
<br />
<span style="font-family: "segoe ui"; font-size: x-small;"></span><br />
<span style="font-family: "segoe ui"; font-size: x-small;"></span><br />
<span style="font-family: "segoe ui";">And SharedManagemmentObjects 2012 also can be found in the same SQL 2012 Feature Pack web page </span><br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-foG0IbSZwYU/UD5aw_Kq9bI/AAAAAAAAALc/0W8pcFHmX1o/s1600/SharedManagementObjects.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://3.bp.blogspot.com/-foG0IbSZwYU/UD5aw_Kq9bI/AAAAAAAAALc/0W8pcFHmX1o/s1600/SharedManagementObjects.png" /></a></div>
<span style="font-family: "segoe ui";">Once both components are installed, I was able to create new Connection.</span><br />
<span style="font-family: "segoe ui"; font-size: x-small;"></span><br />
<span style="font-family: "segoe ui";">By the way, as a side note, if you already have SQL Server 2012 installed on the machine, they are supposed to be installed already.</span><br />
<span style="font-family: "segoe ui";"><br /></span>
<span style="font-family: "segoe ui";">NOTE: This article only talked about VS 2012 and SQL 2012. More people ran into the same issue for other VS / SQL versions. I wrote an additional article for other versions of VS and SQL. </span><span style="font-family: "segoe ui";">Please refer to </span><a href="http://csharp.tips/tip/article/958-VS---Could-not-load-file-or-assembly-Microsoft-SqlServer-Management-Sdk-Sfc" style="font-family: 'segoe ui';" target="_blank">this article</a><span style="font-family: "segoe ui";"> if this article instruction does not work for you.</span><br />
<span style="font-family: "segoe ui";"><br /></span>Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com43tag:blogger.com,1999:blog-3604280969084941189.post-59652340602393259422012-06-12T16:33:00.000-07:002012-06-12T16:33:15.698-07:00Export SQL tables to Access dbMigrating Access DB to SQL Server is common for users who want to upgrade their database. So the opposite direction, that is, moving SQL data to Access might not be common needs. However, for whatever reason, if you need to export SQL tables to Access, here are simple steps.<br />
<br />
1. Run SQL Server Management Studio (ssms.exe or sqlwb.exe depending on your SQL version)<br />
2. Connect to SQL server<br />
3. Go to the database you want to export (ex: MyDB)<br />
4. Rightclick from the DB and choose Tasks and then Export Data.... menu<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-q-LA7YnQ5tk/T9fGk-d-UHI/AAAAAAAAAJ0/BDw0eKbbkB0/s1600/export_wizard.png" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" fba="true" height="400" src="http://3.bp.blogspot.com/-q-LA7YnQ5tk/T9fGk-d-UHI/AAAAAAAAAJ0/BDw0eKbbkB0/s400/export_wizard.png" width="336" /></a></div>
<div style="clear: both;">
</div>
5. Click Next on Welcome page / Click Next on your Data Source once your confirm<br />
<br />
6. In Choose a Destination page, select [Microsoft Access] in [Destination] combo box if you want to export data to .MDB file. You have to open existing .mdb file. If you don't have one, you need to create one. (For .accdb, goto step 7)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://3.bp.blogspot.com/-sT5JBaPoZuk/T9fPaK2YwhI/AAAAAAAAAKA/6lWNgmDhEfI/s1600/mdb.png" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" fba="true" height="400" src="http://3.bp.blogspot.com/-sT5JBaPoZuk/T9fPaK2YwhI/AAAAAAAAAKA/6lWNgmDhEfI/s400/mdb.png" width="390" /></a></div>
<div style="clear: both;">
7. If you want to export SQL data to .accdb Access file, select [Microsoft Office 12.0 Access Database Engine OLE DB provider] and click [Properties] button. As said in step 6, you have to have .accdb file created on your disk. The wizard does not create new one for you.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-thTmiaUSDlg/T9fQJ_sN7nI/AAAAAAAAAKI/uE9v6qu55uU/s1600/accdb.png" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" fba="true" height="166" src="http://1.bp.blogspot.com/-thTmiaUSDlg/T9fQJ_sN7nI/AAAAAAAAAKI/uE9v6qu55uU/s400/accdb.png" width="400" /></a></div>
<div style="clear: both;">
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-uRhAbKP6Fug/T9fQS1N9TxI/AAAAAAAAAKQ/yCdCzjR2wHw/s1600/accdb_file.png" imageanchor="1" style="clear: left; cssfloat: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" fba="true" height="400" src="http://1.bp.blogspot.com/-uRhAbKP6Fug/T9fQS1N9TxI/AAAAAAAAAKQ/yCdCzjR2wHw/s400/accdb_file.png" width="318" /></a></div>
<br />
If you click [Properties] button, you will see Data Link Properties dialog, which is common dialog for OLEDB provider data source. Specify your .accdb file path in Data Source textbo and click OK.</div>
</div>
<div style="clear: both;">
8. Next, select [Copy data from one or more tables or views] option in [Specify Table Copy or Query]</div>
<div style="clear: both;">
9. Select source tables and views </div>
<div style="clear: both;">
10. Click Next in [Save and Run Package]. Then the wizard will create Access tables and data from SQL tables you just selected.</div>
<div style="clear: both;">
</div>
<div style="clear: both;">
</div>Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com3tag:blogger.com,1999:blog-3604280969084941189.post-77229557419097726022012-04-12T13:50:00.001-07:002012-08-14T16:57:43.635-07:00How to check if FileStream is enabled using SQL WMIFILESTREAM can be enabled during SQL installation or by using SQL Configuration Manager. And there is no TSQL statement that enables FILESTREAM. The following code snippet shows how to check whether the SQL Server enabled FILESTREAM in full access mode. <br />
<div style="clear: both;">
</div>
<br />
<pre>private bool IsFileStreamFullAccess()
{
string path= @"\\.\root\Microsoft\SqlServer\ComputerManagement10";
string wql = "SELECT * FROM FileStreamSettings " +
"WHERE InstanceName = 'SQLEXPRESS'";
const int FILESTREAM_FULL_ACCESS = 3;
try
{
ManagementScope scope = new ManagementScope(path);
scope.Connect();
WqlObjectQuery wqlObj = new WqlObjectQuery(wql);
ManagementObjectSearcher searcher =
new ManagementObjectSearcher(scope, wqlObj);
ManagementObjectCollection moColl = searcher.Get();
foreach (ManagementObject m in moColl)
{
return m["AccessLevel"].ToString() ==
FILESTREAM_FULL_ACCESS.ToString();
}
}
catch
{
}
return false;
}
</pre>
<br />
The sample code inspects FileStreamSettings class and checks AccessLevel property. AccessLevel property can be 0, 1, 2, 3. If it is zero, FILESTREAM is disabled. If access level is 1, it enables FILESTREAM for TSQL access only. The below picture shows access level 1 in a SQL Configuration Manager dialog.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://1.bp.blogspot.com/-8G6NDHWUzEg/T4c-q9NcNQI/AAAAAAAAAJg/n6UI1DqnW1w/s1600/filestream-accesslevel.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://1.bp.blogspot.com/-8G6NDHWUzEg/T4c-q9NcNQI/AAAAAAAAAJg/n6UI1DqnW1w/s320/filestream-accesslevel.png" width="290" /></a></div>
<br />
<br />
Access level 2 includes Access Level 1 and also file I/O streaming access. Lastly, access level 3 includes [Allow remote clients...] on top of access level 2.<br />
<br />
Windows share name is typically the same as InstanceName. In SQL WMI, it can be retrieved by accessing ShareName property of FileStreamSettings class.<br />
<br />
<strong><span style="color: red;">[Additional Note]</span></strong><br />
Donnel's comment below reminds me that I should have mentioned this in the post...<br />
You might get 0 for moColl (ManagementObjectCollection ) which means there is no WMI information for FileStream. This can happen when you installed SQL Server on x64 machine and your C# application target platform is x86. 32 bit application will invoke 32 bit WMI, but 64bit SQL Server instance cannot be retrieved by 32bit WMI. An easy way of solving this problem is change your platform target to x64 in your C# project properties page. If you want to support both 32bit and 64bit or want to do this in programming way, you can read this post : <a href="http://sqlbeyond.blogspot.com/2010_03_01_archive.html" target="_blank">How to call 32bit/64bit SQL WMI providers</a> .Alexhttp://www.blogger.com/profile/14466854366502861778noreply@blogger.com5