How to get a list of all identity columns in a database

Here is some code to get a list of identity columns in database.

select  so.name as TableName,  + o.list as IdentityColumnName 
from    sysobjects so
cross apply
    (SELECT 
        column_name
         + 
           case when exists ( 
        select id from syscolumns
        where object_name(id)=so.name
        and name=column_name
        and columnproperty(id,name,'IsIdentity') = 1 
        ) then
        ''
        end + ' '
     from information_schema.columns where table_name = so.name
    ) o (list)
where   xtype = 'U'
AND o.list is not null
AND name    NOT IN ('dtproperties')
[tweetmeme only_single=”false”]
Advertisements

List of all views and scripts to create

Here is a script to list all views in a database and provide the script to create them.  This helps when moving views to new environments.

SELECT a.name AS VIEW_NAME, LEFT(b.definition, 4000) AS SQL1, SUBSTRING(b.definition, 4001, 4000) AS SQL2, LEN(b.definition) AS SQL_Length 
FROM sys.sql_modules AS b 
INNER JOIN sys.views AS a ON b.object_id = a.object_id 
[tweetmeme only_single=”false”]

How to find out what triggers you have.

I try to avoid using triggers because I always forget they are there.  I have been burned more than once by triggers updating data when I didn’t know they were there.  I have also been troubleshooting someone else’s database and been bitten by triggers.
The code from this post I copied from Chris McGowan’s blog.  He wrote up a great article about trigger status.  His post has code to create a job to email you.
I modified it to just run in SSMS and use a table variable (in case you don’t have the permissions to create a temp table).

This script runs against master and will tell you about all triggers in all databases.

Use master

-- Create table variable
DECLARE @TriggerStatus as Table
(
      DatabaseName SYSNAME,
      TableName VARCHAR(255), 
      TriggerName VARCHAR(255),
      TriggerStatus VARCHAR(8)
);
-- Insert triggers
INSERT INTO @TriggerStatus
EXEC sp_msforeachdb
'
IF ''?'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''distribution'', ''reportserver'', ''reportservertempdb'')
BEGIN
USE [?]; 
SELECT  DB_NAME() AS DatabaseName,
        OBJECT_NAME(parent_id) AS TableName,
        name AS TriggerName,
        CASE is_disabled
          WHEN 0 THEN ''Enabled''
          ELSE ''Disabled''
        END AS TriggerStatus
FROM    sys.triggers WITH ( NOLOCK )
WHERE   is_ms_shipped = 0
        AND parent_class = 1;
END'

SELECT DatabaseName, TableName, TriggerName,  TriggerStatus FROM   @TriggerStatus 

[tweetmeme only_single=”false”]

What little I know about SQL Bulk Insert

I’ve got a stored procedure to upload a csv file to a database table.  From what I’ve read that’s the fastest way to load csv data to SQL.

Cool, but I’ve had errors when I went to deploy to production.

Here are just a few things I’ve learned.
1. The permissions used are different when using windows login vs sql login (to call the stored procedure).
2. If you use a windows login to call the stored procedure it will use the permissions of the user to read the file (no matter what you what the SQL process is set to run).
3. If you use a SQL login then account the SQL process is setup to run as is used.
4.  Bulk Insert doesn’t like to pull data from any server,  but the server that SQL is running on.  If you do try pulling data from another server then make sure you use a UNC path.

After all that I still had permissions trouble.
So my application copied the csv file to a directory on the SQL server.  Then calling the procedure to do the Bulk Insert worked without issue.

Below is my stored procedure.  You just need to pass it the path to the file you want to import.
It will load it to a table dbtemp.
Make sure that table has the same fields as your source file.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_ImportFile] 
    @filename varchar(1000) 

AS
BEGIN

    SET NOCOUNT ON;

declare @sql varchar(4000)
select @sql = '
BULK
INSERT dbtemp
FROM ''' + @filename + '''
WITH
(
FIELDTERMINATOR = ''","'',
FIRSTROW = 2,
ROWTERMINATOR = ''\n''
)'
exec (@sql)
update dbtemp set funding = REPLACE(funding,'"',''), initialdate = REPLACE(initialdate,'"','')

END
[tweetmeme only_single=”false”]

Enable/Disable table indexes

Sometimes you need to disable indexes (and then re-enable them).
Here is the sql to disable indexes on a table.

ALTER INDEX ALL ON <TABLENAME> DISABLE;

Here is the sql to enable indexes on a table.

--Reenable indexes 
ALTER INDEX ALL ON <TABLENAME> REBUILD ;

Notice that to enable the indexes you just need to do a rebuild.

[tweetmeme only_single=”false”]

Undocumented stored procedure for looping through all databases on a server

If need to do something to all your databases there is a stored proc for you.  Its called sp_msforeachdb.
This will loop though all your databases and perform a command for you.  It’s similar to the foreach table sp I wrote about here.
There is one parameter @command.

Let’s get right to an example.

EXECUTE sp_msforeachdb ‘USE ? IF DB_NAME() NOT IN(”master”,”msdb”,”tempdb”,”model”) BACKUP DATABASE ? TO DISK = ”G:?.bak, WITH INIT”’

The ? gets replaced with the database name.
So that will backup any user database to the the G: drive.

This can simplify any looping code you have.

[tweetmeme only_single=”false”]

Enabling SQL Tracing on a TFS 2010 database

If you’ve ever had trouble setting up TFS 2010 (errors, baby that’s what I’m talking about) you might like to have SQL Tracing on TFS database for more info.  By default you don’t have it (and you need a regedit to get it).
(This also refers to the program from SysInternals that is a free download “ DeBug View”)

How about an example!

Let’s say in the Advanced Configuration Wizard you type in the wrong name of the SQL Server Instance you want to use. The error message you will get is:

clip_image002

But look at some of the trace output:

[6468] [Verbose@18:45:56.241] Checking if database master exists on SQL connection Data Source=HORVICKVM-DEV2;Integrated Security=True
[6468] [Error @18:46:21.381] Microsoft.TeamFoundation.Admin.TfsAdminException: TF255049: A connection cannot be made to the server that is running SQL Server. Verify that you have entered the correct name for the server, including the instance name, that the server you are attempting to connect to is online, and that you have the required permissions to connect. —> Microsoft.TeamFoundation.Framework.Server.DatabaseConnectionException: TF246017: Team Foundation Server could not connect to the database. Verify that the server that is hosting the database is operational, and that network problems are not blocking communication with the server. —> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

What’s so great?

Well first the error message is highlighted red (in DbgView it will be white text on a red background given my example above – but here I made it red text on white to be more readable in the blog post).

Also the message contains the true .NET Exception that occurred and the connection string we used. Those are two really useful pieces of information when you are trying to figure out what is happening. More specifically it even told you how the check was being performed (“Checking if database master exists…”).

Oh No! I have Warnings and Errors!

Well – did anything actually go wrong with your configuration? If not – then it’s probably nothing to worry about. We have some messages that come out as warnings or errors but are in fact benign.

Why Don’t I See Server Messages?

This blog post only explains how to enable tracing for the TFS 2010 admin tools. It does not explain how to enable tracing for the server components, SQL, the job service (though we do trace the job service output when a servicing operation is initiated by an admin tool such as tfsmgmt.exe or tfsconfig.exe). This trace data will provide you zero insight into your server’s realtime activities. Only configuration actions.

Here are the steps you need to follow.

Enabling Tracing

  1. Close the TFS 2010 configuration wizard or admin console (if running).
    1. We only check the trace level when the program starts up. So if it’s already running and you haven’t done this – you need to close it!
    2. Oh – if you are running the TFS 2010 Setup and are at the window where you are asked if you want to launch the configuration tool – you can update the registry key now and not need to close the configuration wizard since the MSI and configuration phases are separate executables).
  2. Enable verbose tracing by doing the following:
    1. Open Regedit (must be an Administrator)
    2. Find the key HKLM\SOFTWARE\Microsoft\TeamFoundationServer\10.0\Admin
    3. Edit the value “TraceLevel” (DWORD) – set to “4” (it is probably “0”)
    4. You can do this from an admin command prompt by running:
      1. reg.exe add HKLM\SOFTWARE\Microsoft\TeamFoundationServer\10.0\Admin /t REG_DWORD /v TraceLevel /d 4 /f
  3. Run DbgView
    1. You can download DbgView from http://technet.microsoft.com/en-us/sysinternals/bb896647.aspx
      1. You can also run it right from the website at this link: http://live.sysinternals.com/Dbgview.exe
  4. By default DbgView is ready to go – Under the “Capture” menu make sure that “Capture Win32” (CTRL+W), “Pass-Through” and “Capture Events” (CTRL+E) are enabled. Nothing else is needed.
    1. clip_image002[4]
  5. Start up the TFS Administration Console (or configuration tool, whatever).

When the config tool starts you should see some data streaming in to DbgView – it should look something like this:

[6468] Admin TraceLevel = Verbose

Show Me The Errors!

  1. Bring up the filters screen in DbgView (CTRL+L or clicking on this button: clip_image004).
  2. Leave the Include and Exclude fields alone.
  3. In the Highlight section leave “Filter 1” selected and type “[Error” into the Red text area (please note that I typed an opened brace but not a closing one and also do not type the double quotes). It should look like this:
    1. clip_image006
  4. Now change “Filter 1” to “Filter 3” (Nothing magically about 3. I use 3 because it’s kind of orange – I don’t like the purple of “Filter 2” and I don’t want to get into color changing in this blog post – you want to change colors? Go nuts.).
  5. Add the string “[Warning” – again, no closing brace and don’t use the double quotes.
    1. clip_image008
  6. Now if you happen to have an error it will be much easier to detect.
[tweetmeme only_single=”false”]

Instead of Triggers

A weird feature in SQL Server is the INSTEAD OF trigger.  With this trigger you can divert the database to do something other than what the user/application wanted.  You can have INSTEAD OF triggers for insert/update/delete.

Where would this be useful?  I’ve had application that have to be changed, but the source code is no longer around (had a client with this problem).  The business wanted change what data was written to the database, but they could change the application.  So I added an INSTEAT OF trigger to check the data before it was inserted/updated and have the corrected values inserted/updated instead.

Here is a real simple example.  A test table is created and loaded with one record.  An insert INSTEAD OF trigger is added.  This trigger just returns, so the insert never happens.  No matter what INSERT statement the user/application tries it just gets ignored.

CREATE TABLE Test (
      TestID            int IDENTITY(1,1) NOT NULL
,     Name        varchar(64)             NULL
,   CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([TestID] ASC) 
)
GO

INSERT INTO Test (Name)
VALUES ('Hello World')
GO

SELECT *
  FROM dbo.Test
GO

CREATE TRIGGER tiTest ON Test INSTEAD OF INSERT
AS
BEGIN
      RETURN
END
GO

INSERT INTO Test (Name)
VALUES ('Good Bye World')
GO

SELECT *
  FROM dbo.Test
GO

So you can see here the before and after of the Insert statement.  It just gets ignored.

image

 

Here are a couple of msdn articles (using and designing)  about INSTEAD OF triggers if you want more info.

[tweetmeme only_single=”false”]