Showing posts with label SQL Server. Show all posts

Import IIS logs into SQL Server table

There are lots of IIS log parsers out there, but I found a simple SQL script that will load them into your own SQL Server table for you to query how you like.

CREATE TABLE dbo.IISLOG (

[DATE] [DATE] NULL,

[TIME] [TIME] NULL,

[s-ip] [VARCHAR] (48) NULL,

[cs-method] [VARCHAR] (8) NULL,

[cs-uri-stem] [VARCHAR] (255) NULL,

[cs-uri-query] [VARCHAR] (2048) NULL,

[s-port] [VARCHAR] (5) NULL,

[s-username] [VARCHAR] (128) NULL,

[c-ip] [VARCHAR] (48) NULL,

[cs(User-Agent)] [VARCHAR] (1024) NULL,

[cs(Referer)] [VARCHAR] (4096) NULL,

[sc-STATUS] [BIGINT] NULL,

[sc-substatus] [INT] NULL,

[sc-win32-STATUS] [INT] NULL,

[time-taken] [INT] NULL

)

BULK INSERT dbo.IISLOG

FROM 'c:\temp\u_ex171205.log'

WITH (

FIRSTROW = 5,

FIELDTERMINATOR = ' ',

ROWTERMINATOR = '\n'

)

SELECT [cs-uri-stem], avg([time-taken])

FROM dbo.IISLOG
WHERE [cs-uri-stem] like '%.svc'
GROUP BY [cs-uri-stem]
ORDER BY avg([time-taken]) desc

This tip is based on this post, but contains fixed field lengths and bulk import statement.

How to script SQL server database role permissions

SELECT 'GRANT ' + database_permissions.permission_name +

CASE database_permissions.class_desc

WHEN 'SCHEMA' THEN ' ON ' +schema_name(major_id)

WHEN 'OBJECT_OR_COLUMN' THEN

CASE WHEN minor_id = 0 THEN ' ON ' +object_name(major_id) COLLATE Latin1_General_CI_AS_KS_WS

ELSE ' ON ' +(SELECT object_name(object_id) + ' ('+ name + ')'

FROM sys.columns

WHERE object_id = database_permissions.major_id

AND column_id = database_permissions.minor_id) end

ELSE ''

END +

' TO ' + database_principals.name COLLATE Latin1_General_CI_AS_KS_WS

FROM sys.database_permissions

JOIN sys.database_principals

ON database_permissions.grantee_principal_id = database_principals.principal_id

LEFT JOIN sys.objects --left because it is possible that it is a schema

ON objects.object_id = database_permissions.major_id

WHERE permission_name in ('SELECT','INSERT','UPDATE','DELETE','EXECUTE') AND database_principals.NAME = ''

How do I add an active directory security group to a sql server sysadminrole

Question: How do I add an active directory security group to a sql server role such as sysadmin role

Answer:

EXEC master..sp_addsrvrolemember @loginame = N'\', @rolename = N'sysadmin'

How to open firewall ports for SQL Server V2

This is an updated version of the original script posted here (http://shortfastcode.blogspot.com/2012/07/how-to-open-firewall-ports-for-sql.html) updated for Windows Server 2012.

netsh advfirewall firewall add rule name="Open Port 80" dir=in action=allow protocol=TCP localport=80

@echo ========= SQL Server Ports ===================

@echo Enabling SQLServer default instance port 1433

netsh advfirewall firewall add rule name="SQL Server" dir=in action=allow protocol=TCP localport=1433

@echo Enabling Dedicated Admin Connection port 1434

netsh advfirewall firewall add rule name="SQL Admin Connection" dir=in action=allow protocol=TCP localport=1434

@echo Enabling Conventional SQL Server Service Broker port 4022

netsh advfirewall firewall add rule name="SQL Service Broker" dir=in action=allow protocol=TCP localport=4022

@echo Enabling Transact SQL/RPC port 135

netsh advfirewall firewall add rule name="SQL Debugger/RPC" dir=in action=allow protocol=TCP localport=135

@echo ========= Analysis Services Ports ==============

@echo Enabling SSAS Default Instance port 2383

netsh advfirewall firewall add rule name="Analysis Services" dir=in action=allow protocol=TCP localport=2383

@echo Enabling SQL Server Browser Service port 2382

netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=TCP localport=2382

@echo ========= Misc Applications ==============

@echo Enabling HTTP port 80

netsh advfirewall firewall add rule name="HTTP" dir=in action=allow protocol=TCP localport=80

@echo Enabling SSL port 443

netsh advfirewall firewall add rule name="SSL" dir=in action=allow protocol=TCP localport=443

@echo Enabling port for SQL Server Browser Service's 'Browse' Button

netsh advfirewall firewall add rule name="SQL Browser" dir=in action=allow protocol=UDP localport=1434

@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)

netsh firewall set multicastbroadcastresponse ENABLE

SQL Server write to EventLog

Getting debug information from a complex stored procedure can sometimes be a challenge in sql server. Do you write to a log table or return the information as part of the query response? Why not use the event log?

Turns out writing to the event log from within a stored procedure is really easy:

DECLARE @@MESSAGE varchar(255)
set @@MESSAGE = 'executing sub query XXX'
EXEC xp_logevent 60000, @@MESSAGE, informational

Determine the file size of a database using t-sql

In SQL Server there are a number of ways to determine how large a database is. Right clicking and selecting properties > files in management studio for example is probably the easiest. However if you do not have very high permissions on the database you are interested in this t-sql may be what you need.

This script requires the least permissions of all the different methods I could find and is fairly simple which is a bonus.

select a.FILEID,
      [FILE_SIZE_MB] = convert(decimal(12,2),round(a.size/128.000,2)),
      [SPACE_USED_MB] = convert(decimal(12,2),round(fileproperty(a.name,'SpaceUsed')/128.000,2)),
      [FREE_SPACE_MB] = convert(decimal(12,2),round((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)),
      NAME = left(a.NAME,15),
      FILENAME = left(a.FILENAME,30)
from dbo.sysfiles a

Tested in SQL Server 2005+

How to open firewall ports for SQL Server

Slow Way: Real the Books Online article "The Configuring the Windows Firewall to Allow SQL Server Access" (http://msdn.microsoft.com/en-us/library/cc646023.aspx) which contains the information to how to open the required ports in Windows Firewall.

Fast Way: Use this bat script taken from the KB Article "How to open the firewall port for SQL Server on Windows Server 2008" (http://support.microsoft.com/kb/968872)

@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"
@echo Enabling conventional SQL Server Service Broker port 4022
netsh firewall set portopening TCP 4022 "SQL Service Broker"
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"
@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser"
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

You can download the bat file from here: https://dl.dropbox.com/u/677104/shortfastcode/OpenSqlServerPort.bat

Works with Windows Server 2008, Windows Vista/7

Shrink all SQL Server Databases

A quick way to ensure all the databases on your dev pc/server are all set to simple backup and log files are shrunk to avoid wasting disk space.

You can easily add databases to the list you don't want affected.

declare @ssql nvarchar(4000)

set @ssql= '

if ''?'' not in (''tempdb'',''master'',''model'',''msdb'')

begin

use [?]

Alter database [?] SET Recovery simple

end'

exec sp_msforeachdb @ssql

set @ssql= '

if ''?'' not in (''tempdb'',''master'',''model'',''msdb'')

begin

use [?]

Declare @LogFileLogicalName sysname

select @LogFileLogicalName=Name from sys.database_files where Type=1

--print @LogFileLogicalName

DBCC Shrinkfile(@LogFileLogicalName,1)

end'

exec sp_msforeachdb @ssql

How to get sysadmin access on SQL Server 2008

If you've ever inherited a PC with SQL Server 2008 installed on it have probably hit the situation where you are a local admin but you can't access the SQL Server instance.This is because SQL Server 2008 unlike earlier versions of SQL Server doesn't by default give sysadmin access to the local Administrators group.

Here is a list of steps that will allow you to gain administrator access - provided you are a windows local admin.

Force SQL server to support mixed-mode authentication

Run REGEDIT
Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer
Set "LoginMode" to 2.
(Source: http://support.microsoft.com/kb/285097)
Restart SQL Server.

Force SQL server to let you in temporarily

Go to services.
Stop SQL Server.
Grab the SQL server command-line (right click the service - properties). Mine is:
"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -sSQLEXPRESS
Open an administrative command prompt.
Run the command-line from step 3, but add -m -c for single-user maintenance mode command-line.
Open another administrative command prompt.
Run "sqlcmd -S localhost\SQLEXPRESS" from that same directory (replace with your server and instance name)
Now you can do all the stuff everyone told you to do that didn"t work. For example, to create a hero user with administrative access:
CREATE LOGIN hero WITH PASSWORD="123", DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
EXEC sys.sp_addsrvrolemember @loginame = "hero", @rolename = "sysadmin"
GO
QUIT and close the command-prompt
Go to the SQL Server command-line window and hit ctrl+C. It will prompt "Do you wish to shutdown SQL Server (Y/N)?" and enter Y.
Close the command-prompt
(Source: http://msdn.microsoft.com/en-us/library/dd207004.aspx)

Finally, login using your hero

Restart the SQL Server service
Login using SQL Server authentication as the user "hero" with password "123"
*BAM* now you are in. Now give yourself sysadmin access, etc, etc.

How to check the progress when Shrinking Database SQL Server

Compacting a large databases in SQL Server can take what feels like forever. If you're like me self doubt is sure to set in as there is no way to tell that the command is progressing in SSMS!
However you can use the system views in SQL Server to see how its going using the following query:

SELECT percent_complete, start_time, status, command, estimated_completion_time, cpu_time, total_elapsed_time
FROM sys.dm_exec_requests

Works on SQL Server 2005+

Source: http://blogs.msdn.com/psssql/archive/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000.aspx

Bulk renaming auto-named default constraints

When you create a default constraint on a column you do not have to specify a name for it. In this case SQL Server will assign each generate an ugly name for you like "DF__ ExampleTa __BitF__42D9640B". This can become a real problem if you have multiple instances of your database as the name of the constraint can be different in each database.

This script renames anything named automatically by sql server eg “DF__ExampleTa__BitF__42D9640B” to something that makes sense and will be the same across all databases where you run the script i.e. “DF_ExampleTable_BitFlag”.

BEGIN TRANSACTION

DECLARE @tname nvarchar(MAX)

DECLARE @cname nvarchar(MAX)

DECLARE @dname nvarchar(MAX)

DECLARE @ddef nvarchar(MAX)

WHILE EXISTS (SELECT * from sys.default_constraints d WHERE d.name LIKE '%[_][_]%')

BEGIN

select top 1 @tname=t.name, @cname=c.name, @dname=d.name, @ddef=d.definition

from sys.tables t

join

sys.default_constraints d

on d.parent_object_id = t.object_id

join

sys.columns c

on c.object_id = t.object_id

and c.column_id = d.parent_column_id

WHERE d.name LIKE '%[_][_]%'

PRINT 'alter table [dbo].['+@tname+'] drop constraint ['+@dname+']'

exec('alter table [dbo].['+@tname+'] drop constraint ['+@dname+']')

PRINT 'alter table [dbo].['+@tname+'] add constraint [DF_'+@tname+'_'+@cname+'] DEFAULT '+@ddef+' FOR '+@cname

exec('alter table [dbo].['+@tname+'] add constraint [DF_'+@tname+'_'+@cname+'] DEFAULT '+@ddef+' FOR '+@cname)

END

COMMIT

SQL Server: Can’t access sys.dm_tran_current_transaction

Problem:

User doesn’t have access to sql server management views

select transaction_id from sys.dm_tran_current_transaction

Msg 297, Level 16, State 1, Line 1

The user does not have permission to perform this action.

Solution:

Run the following script. Must be run as a server admin.

USE master;
GRANT VIEW SERVER STATE TO <User>;

ms186327.aspx

Get Last Running Query Based on SPID

Handy for tracking down long running queries, use in conjunction with sp_who2

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = <SPID>
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)

Source: http://blog.sqlauthority.com/2009/07/19/sql-server-get-last-running-query-based-on-spid/

Getting around sql server print 8000 max length

Use this stored proc. THe only down side is you get a line break every 8000 charachters :(

CREATE PROCEDURE [dbo].[LongPrint]
      @String NVARCHAR(MAX)

AS

/*
Example:

exec LongPrint @string =
'This String
Exists to test
the system.'

*/

/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).

It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.

If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.

If it is passed a null value, it will do virtually nothing.

NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
 */

DECLARE
               @CurrentEnd BIGINT, /* track the length of the next substring */
               @offset tinyint /*tracks the amount of offset needed */

set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1
BEGIN

IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
    BEGIN

SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
    END
    ELSE
    BEGIN
           SET @CurrentEnd = 4000
            set @offset = 1
    END

PRINT SUBSTRING(@String, 1, @CurrentEnd)

set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)

END /*End While loop*/

This was originally posted on SQLServerCentral.com at http://www.sqlservercentral.com/scripts/Print/63240/

Execute all SQL Scripts in a Folder

How to quickly execute a collection of SQL Scripts against a SQL Server Database.

The simplest solution I have found is to use a quick batch script and leverage the osql command. The osql utility is a Microsoft® Win32® command prompt utility for ad hoc, interactive execution of Transact-SQL statements and scripts.

To use the script you will need to:
1. download it from here, This script will execute every .sql in the folder it is executed in.
2. open it up in a text editor of your choice and substitute your connection information for the dummy "[Your XX]" placeholders.
3. Close the file and double click on it to execute.

RunScript.bat

FOR %%x IN (*.sql) DO OSQL -U [Your UserName] -P [Your Password] -S [My Server] -d [My Database] -i "%%x" >> Log.txt

%%x is a variable for the current file. (*.sql) is what selects the files in the folder. Everything after the Do is what is executed for every file.

Note: scripts will execute in alphabetical order. As a bonus the script logs the output to Log.txt

How to select a list of every table in a sql server database and row count in each table

How to select a list of every table in a sql server database and row count in each table. I've found this very handy over the years when doing data migrations and comparing databases.

Tested on SQL Server 2005 & 2008

CREATE TABLE #temp (
table_name sysname ,
row_count int,
reserved_size varchar(50),
data_size varchar(50),
index_size varchar(50),
unused_size varchar(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
count(*) as col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY table_name --CAST(Replace(a.data_size, ' KB', '') as integer) desc
DROP TABLE #temp

Grant exec on all stored procs

Its so annoying that sql server doesn't come with a default role to just have exec permissions on all stored procs. Oh well.

I love this little stored proc. Its usually a good idea to prevent your database user from having direct read/write access to your database to tricks some nasty hacker might throw at you - Its usually good practice to give your user just access to the stored procedures. I usually run it at the end of every release to ensure the user can exec any stored proc.

use master
go
create procedure sp_grantexec(@user sysname,@pattern sysname = NULL,@debug int = 0)
as
set nocount on
declare @ret int
declare @sql nvarchar(4000)
declare @db sysname ; set @db = DB_NAME()
declare @u sysname ; set @u = QUOTENAME(@user)

set @sql ='select ''grant exec on '' + QUOTENAME(ROUTINE_SCHEMA) + ''.'' +
QUOTENAME(ROUTINE_NAME) + '' TO ' + @u + ''' FROM INFORMATION_SCHEMA.ROUTINES ' +
'WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),''IsMSShipped'') = 0'

if @pattern is not null
set @sql = @sql + N' AND ROUTINE_NAME LIKE ''' + @pattern + ''''

if @debug = 1 print @sql
else
exec @ret = master.dbo.xp_execresultset @sql,@db

If @ret <> 0
begin
raiserror('Error executing command %s',16,1,@sql)
return -1
end

Script to kill all connections to a database

When restoring/moving databases its a pretty common requirement to kill all the connections to the database in question otherwise you can't get a lock on it. Killing all the connections to a database can be a pain though if you have to use the UI. This script is much quicker and can be a time saver. If you’re super keen you could modify the script to make it a stored procedure so it’s always ready to go. If you do want to use the UI here is a blog post on how to do it.

--
-- Kill connections to a given sql server database
--

declare @execSql nvarchar(MAX), @databaseName varchar(100)
set @databaseName = '<your database name here>'

set @execSql = ''
select @execSql = @execSql + 'kill ' + convert(char(10), spid) + ' '
from master.dbo.sysprocesses
where db_name(dbid) = @databaseName
and
DBID <> 0
and
spid <> @@spid
exec(@execSql)

How to login to sql server using windows authentication over a VPN

This only applies to people that want to connect to a SQL Server instance over VPN to a domain that doesn't have a trust relationship with the source domain and don't want to have to use remote desktop. The method described below will allow you to access the SQL database seamlessly as if it were a database insist your domain.

1) Open a command prompt window

2) runas /user:"domain\user" /netonly "c:\program files\microsoft sql server\100\tools\binn\vsshell\common7\ide\ssms.exe" - replacing "domain\login" with your login details obviously

3) The command prompt with ask for "domain\login"'s password

4) SMSS will then run and you can connect to the database over VPN using the Windows Authentication option.

Insert binary data like images into SQL Server

A fairly common problem is inserting data directly into binary data columns in SQL Server without a front-end application. The example below is the simplest solution I've seen and can be run straight from SQL Server Management Studio (SSMS).

In the below example I'm updating the an image column 'Icon' with an image file saved on the hard drive.

Update myTable
set Image = (
SELECT *
FROM OPENROWSET(BULK N'C:\image.png', SINGLE_BLOB) test)
where ImageID = 1