|
greek, noun, pron. "archarios" - beginner, learner, neophyte, novice
-
Posted by MarlonRibunal
I was having a problem accessing a folder via an extended procedure. The extended procedure accepts a DirectoryInfo variable (File Path) and it returns the File Size and File Count within the folder (much like right-clicking a folder and choosing the “Properties” from the context menu). I need the info to monitor the file size of specific folders for a SSRS report.
Executing the extended procedure causes the SQL Server to throw the following error:
The SQL Server Error Log translates this to be a “EXCEPTION_ACCESS_VIOLATION”. Switching to Windows Authentication would not solve the problem. I could not really drill down to the error because it is apparent that this is a Windows Operating System error. My lack of familiarity with the network environment makes it hard for me to investigate the error. The Windows Event Viewer could not give me useful information pertaining to the error. I do not want to tweak the access rules on the folders that my extended procedure needs in fear of opening holes of vulnerability (these folders are being used by a ASP.NET Web application for file storage).
To circumvent a possible security violation and to keep the security compliance intact, I decided to map the Domain Account to a SQL Server Login. That solved the access violation error.
This is how you create a SQL Server Login from a Domain Account:
[Cross-Posted from my other blog: http://dbalink.wordpress.com]
|
-
In this post let’s move a data file from its default location to another location. I have created a database I called “Test” for the demo. First let’s just check where the data file is currently stored:
The “Test” database needs to be put offline before we can move the data file. We do that by issuing the following ALTER DATABASE statement:
To actually move the data file, we issue another ALTER DATABASE statement:
After you issue that command, you should get the following message:
The file “TEST” has been modified in the system catalog. The new path will be used the next time the database is started.
Now, we can move the data file to the new directory we specified (Cut-and-Paste or Drag-and-Drop, your choice) . Let’s double check if we have successfully moved the data file to the new location; but, we need to put the TEST database back online first:
Let’s query the sys.master_files again to make sure we have our data file where we want it:
[Cross-posted from my other blog http://dbalink.wordpress.com ]
|
-
This question posted on StackOverflow.com caught my attention. This is a common data requirement specially in Data Analysis. It goes like this:
I have a table like as follows:
SoftwareName Count Country
Project 15 Canada
Visio 12 Canada
Project 10 USA
Visio 5 USA
How do I query it to give me a summary like...
SoftwareName Canada USA Total
Project 15 10 25
Visio 12 5 17
How to do in T-SQL?
Here's my solution to the problem - A Walkthrough:
Let's create our test table ("SoftwareDemo")
USE [Test]
GO
CREATE TABLE [dbo].[SoftwareDemo](
[SoftwareName] [nvarchar](50) NOT NULL,
[Count] [int] NOT NULL,
[Country] [nvarchar](50) NOT NULL
) ON [PRIMARY]
Then, let's insert the sample data:
INSERT INTO SoftwareDemo VALUES ('Project', 15, 'Canada')
INSERT INTO SoftwareDemo VALUES ('Visio', 12, 'Canada')
INSERT INTO SoftwareDemo VALUES ('Project', 10, 'USA')
INSERT INTO SoftwareDemo VALUES ('Visio', 5, 'USA')
Here's our PIVOT query:
SELECT Softwarename, Canada, USA, Canada + USA AS TOTAL FROM SoftwareDemo
PIVOT
(
SUM([Count])
FOR Country
IN (Canada, USA)
) AS x
SoftwareName Canada USA Total
-------------------------------------------------- ----------- ----------- -----------
Project 15 10 25
Visio 12 5 17
(2 row(s) affected)
(1 row(s) affected)
Here's our PIVOT TSQL in action:
[Cross-Posted from my other blog http://dbalink.wordpress.com - Marlon Ribunal ]
|
-
Okay, these are at least the two ways you can query the Metadata in SQL Server (TSQL). The first one is like what I’ve already shown here, which has the following TSQL code:
SELECT T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE],
P.MAX_LENGTH AS[SIZE],CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE’;
The TSQL above is querying the Object Catalog Views. For like any other querying tasks in SQL Server, there are lots of options in doing the same thing. To achieve a similar result as what the above TSQL will return, we can query the SQL Server Information Schema Views. Here’s how:
SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION
FROM ADVENTUREWORKS.INFORMATION_SCHEMA.COLUMNS
The Object Catalog Views contain the information about the database objects such as partitions, procedures, constraints, events, tables, views, triggers, etc.
The Information Schema Views are also another way of looking at the same metadata. “Information schema views provide an internal, system table-independent view of the SQL Server metadata. Information schema views enable applications to work correctly although significant changes have been made to the underlying system tables. The information schema views included in SQL Server comply with the ISO standard definition for the INFORMATION_SCHEMA. [MSDN]“
[Crosss-Post from my other blog http://dbalink.wordpress.com/ - Marlon Ribunal]
|
-
One of the most common business requirements for SQL Server Reporting Services is that an SSRS Report should be dynamic. Dynamic in a sense that the report consumer can query the report in a variety of ways. In this post, I will demonstrate how you can add a parameter value “ALL” to return all of the desired result.
I have four parameters in this report; all of them are optional (can be NULL). I will demonstrate the optionality in a different post. For the mean time, let me demonstrate how to add “ALL” in a parameter.
My parameter, which we alias here as “param1″, is optional. Here is how I created my statement in TSQL and wrapped it in a Stored Procedure:
As you can see, a simple “ Union ALL” statement can do the job. I can include NULL value in param1 because that parameter is meant to be optional in the report. Here is the result and the intended purpose of the value “ALL” in param1.
[A Cross-Post from my other blog http://dbalink.wordpress.com ]
-Marlon Ribunal
|
-
I have created a Backup Script to backup all the databases in our SQL Server 2005. I want to keep old backups for sometime for some reason; so I want to create subdirectory with the run date as the name of the folder to separate the set of backups by date.
I need to use DOS Command from within my TSQL Code to create the subdirectory. This feature is turned off by default. I am making sure that I am turning this off after I run the script:
-
EXEC SP_CONFIGURE 'show advanced options', 1
-
GO
-
-
RECONFIGURE
-
GO
-
-
EXEC SP_CONFIGURE 'xp_cmdshell', 1
-
GO
-
-
RECONFIGURE
-
GO
Then here's how I execute DOS Command from within my TSQL:
-
DECLARE @path VARCHAR (200) -- Path of the Backup Files
-
DECLARE @folderdate VARCHAR (50) -- The subdir for my backups with Format YYYYMMDD
-
DECLARE @cmd VARCHAR (4000) -- The command to create Subdir
-
DECLARE -- Other Variables for my backup scripts...
-
-
SELECT @folderdate = CONVERT(VARCHAR(20), GETDATE(), 112) -- gives us YYYYMMDD
-
-
SET @path = N'C:\Backup\' + @folderdate + '\' -- Path as C:\Backup\YYYYMMDD
-
-
SELECT @cmd = 'md "' + @path + '"'
-
-
EXEC master..xp_cmdshell @cmd, no_output
-
-
-- now I can direct all the backup file to the created subdirectory
-
-- like,
-
-- SET @filename = @path + [other_variable/s] + '.BAK'
For security reason, I want to make sure that I turn my xp_cmdshell off. So at the very end of my script:
-
EXEC SP_CONFIGURE 'xp_cmdshell', 0
-
GO
-
-
RECONFIGURE
-
GO
-
-
EXEC SP_CONFIGURE 'show advanced options', 0
-
GO
-
-
RECONFIGURE
-
GO
--Marlon Ribunal Please Help Me: Here's How <Link>
[As posted in my blog at http://dbalink.wordpress.com]
|
-
For some reasons, DBA's need to rename their Databases. One of the reasons might be to comply to Best Practices Policy changes. Here we are going to rename our Database named "Bad_Ass_DB" into "Best_Practice_DB".
This is how Database renaming with TSQL is done in SQL Server 2005:
First we need to put our Bad_Ass_DB offline or alter it to single-user mode. You cannot rename a database if it is online or in multi-user mode. And prior to that, there should be no active connection to the database (user, application, etc):
-
USE master
-
ALTER DATABASE Bad_Ass_DB
-
SET single_user
-
GO
Now we can rename the Database:
-
ALTER DATABASE Bad_Ass_DB
-
MODIFY NAME = Best_Practice_DB
-
GO
Then we can set the database back to multi-user mode. Then, real hard work begins: mapping all your codes, processess, applications, web.config, etc to the new name.
-
ALTER DATABASE Best_Practice_DB
-
SET multi_user
-
GO
-Marlon Ribunal Please Help Me: Here's How <Link>
[As Posted in my other blog http://dbalink.wordpress.com/]
|
-
-
-
Here's a quick TSQL solution that you can use to find all the User Tables and their Columns, inluding Data Types, and Column size. This is useful when you need a quick way of finding info on creating your Database's documentation. Whenever I am asked to document a new system/application or review an existing one, I always want to start from the very core of the system - mostly, this is a Database backend.
-
SELECT t.name AS [TABLE Name], c.name AS [COLUMN Name], p.name AS [DATA Type], p.max_length AS[SIZE], CAST(p.PRECISION AS VARCHAR) +'/'+ CAST(p.scale AS VARCHAR) AS [PRECISION/Scale]
-
FROM sys.objects AS t
-
JOIN sys.columns AS c
-
ON t.OBJECT_ID=c.OBJECT_ID
-
JOIN sys.types AS p
-
ON c.system_type_id=p.system_type_id
-
WHERE t.type_desc='USER_TABLE';
-Marlon Ribunal
[Cross-Posted From My Other Blog http://dbalink.wordpress.com]
|
-
One of the most popular trick questions in most DBA interviews is about Isolation Level/Locks. The same is true with any jobs related to BI and Data Warehouse.
It is a common thing that sometimes we come across errors that came out of anomalous data caused by conflicting transactions and concurrency issues. Dirty Reads only give you Dirty Data. Data inaccuracy is real dirty!
The only way to protect transactions that are prone to conflicts with other transactions is to "Isolate" them from the others. How to protect these critical transactions? Obtain "Locks".
I found this cool introductory to Isolation Level in SQl Server 2005. This article has a downloadable article that came with it. Just in case you miss it, here's the pdf that runs down the Isolation Levels in SQL Server 2005.
| |
Dirty Reads |
Lost Updates |
Nonrepeatable reads |
Phantom reads |
Concurrency model |
Conflict Detection |
| Read Uncommitted |
Yes |
Yes |
Yes |
Yes |
Pessimistic |
No |
| Read Committed |
No |
Yes |
Yes |
Yes |
Pessimistic |
No |
| Repeatable Read |
No |
No |
No |
Yes |
Pessimistic |
No |
| Serializable |
No |
No |
No |
No |
Pessimistic |
No |
| Snapshot |
No |
No |
No |
No |
Optimistic |
Yes |
| Read Committed Snapshot |
No |
Yes |
Yes |
Yes |
Optimistic |
No |
You will find the explanation of the this table in the downloadable pdf.
Thanks to Philippe Almog ("SQL Practices: RDBMS Programming") for this helpful resources. You can find related topics in his site, http://sqlpractices.wordpress.com/.
Out of topic (Bonus!), you can download a copy of the "SQL Server 2005 Failover Clustering" White Paper here. This White Paper is a "comprehensive document about implementing failover clustering for SQL Server 2005 and Analysis Services."
-Marlon Ribunal
[Cross-Posted from my other blog http://dbalink.wordpress.com]
|
-
Here's what Steven Harman has found out in a recent trip: TDD is Gaining Some Ground somewhere...
 [Taken by Steven Harman at Crowne Plaza front desk in West Michigan]
|
-
[This 3-part series was written by Douglas Reilly who died early 2007. The articles were written in February to June of 2006.]
This is the Part I of the series: The Value of Conferences
And here is the Part II: The Benefit of Forum
Finally, Part III: Importance of Books and the Constant Change
Author profile: Douglas Reilly
The late Douglas Reilly was the owner of Access Microsystems Inc., a
small software development company specializing in ASP.NET and mobile
development, often using Microsoft SQL Server as a database. He died
early in 2007 and is greatly missed by the SQL Server community as one
of the industry’s personalities.
[Courtesy of http://www.simple-talk.com]
Please do read these articles with much solemnity. And I broke into tears when I read this. Here's a picture of Doug and his wife, Jean
-Marlon Ribunal
|
-
-
More Posts Next page »
|
|
|