DZone Snippets: SQLServer code

Return current system date and time in Microsoft SQL Server

jeffreybarke (Jeffrey Barke) — Tue, 15 Jul 2008 05:28:42 GMT



SELECT GETDATE();

Drop an unnamed constraint from SqlServer 2000

frost137 (Douglas Wyatt) — Wed, 12 Mar 2008 22:00:43 GMT

// description of your code here



declare @name nvarchar(32), 

    @sql nvarchar(1000)



-- find constraint name

select @name = O.name 

from sysobjects AS O

left join sysobjects AS T

    on O.parent_obj = T.id

where isnull(objectproperty(O.id,'IsMSShipped'),1) = 0

    and O.name not like '%dtproper%'

    and O.name not like 'dt[_]%'

    and T.name = 'MyTable'

    and O.name like 'DF__MyTable__MyColu%'



-- delete if found

if not @name is null

begin

    select @sql = 'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @name + ']'

    execute sp_executesql @sql

end



-- do your ALTER TABLE here



-- replace the constraint

select @sql = 'ALTER TABLE [MyTable] ADD CONSTRAINT [' + @name + '] DEFAULT (0) FOR [MyColumn]'

execute sp_executesql @sql

SQL 2005 TSQL Script to list tables, indexes, file groups along with file names

mornlee (mornlee) — Mon, 25 Jun 2007 15:16:09 GMT

// description of your code here



select 'table_name'=object_name(i.id)  ,i.indid

,'index_name'=i.name  ,i.groupid

,'filegroup'=f.name  ,'file_name'=d.physical_name

,'dataspace'=s.name from sys.sysindexes i

,sys.filegroups f  ,sys.database_files d

,sys.data_spaces s

where objectproperty(i.id,'IsUserTable') = 1

and f.data_space_id = i.groupid

and f.data_space_id = d.data_space_id

and f.data_space_id = s.data_space_id

order by f.name,object_name(i.id),groupid

go

XML file to SQL

mornlee (mornlee) — Mon, 18 Jun 2007 09:54:58 GMT

// XML file to SQL



CREATE TABLE XmlImportTest ( xmlFileName VARCHAR(300), xml_data xml ) 

GO 

DECLARE @xmlFileName VARCHAR(300) 

SELECT @xmlFileName = 'c:\TestXml.xml' 

-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET 

EXEC(' INSERT INTO XmlImportTest(xmlFileName, xml_data) SELECT ''' + @xmlFileName + ''', xmlData FROM ( SELECT * FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA ) AS FileImport (XMLDATA) ') 

GO 

SELECT * FROM XmlImportTest 

DROP TABLE XmlImportTest

Accessing SQL Server 2005 from Ruby

radegast (Miro) — Thu, 26 Apr 2007 04:06:00 GMT

The following is improved version of the code created by David Mullet, from
http://rubyonwindows.blogspot.com/2007/03/ruby-ado-and-sqlserver.html



require 'win32ole'



class SqlServer

    # This class manages database connection and queries

    attr_accessor :connection, :data, :fields

	attr_writer :username, :password



    def initialize(host, username = 'sa', password='')

        @connection = nil

        @data = nil

	@host = host

	@username = username

	@password = password

    end



    def open(database)

        # Open ADO connection to the SQL Server database

        connection_string =  "Provider=SQLOLEDB.1;"

        connection_string << "Persist Security Info=False;"

        connection_string << "User ID=#{@username};"

        connection_string << "password=#{@password};"

        connection_string << "Initial Catalog=#{database};"

        connection_string << "Data Source=#{@host};"

        connection_string << "Network Library=dbmssocn"

        @connection = WIN32OLE.new('ADODB.Connection')

        @connection.Open(connection_string)

    end



    def query(sql)

        # Create an instance of an ADO Recordset

        recordset = WIN32OLE.new('ADODB.Recordset')

        # Open the recordset, using an SQL statement and the

        # existing ADO connection

        recordset.Open(sql, @connection)

        # Create and populate an array of field names

        @fields = []

        recordset.Fields.each do |field|

            @fields << field.Name

        end

        begin

            # Move to the first record/row, if any exist

            recordset.MoveFirst

            # Grab all records

            @data = recordset.GetRows

        rescue

            @data = []

        end

        recordset.Close

        # An ADO Recordset's GetRows method returns an array 

        # of columns, so we'll use the transpose method to 

        # convert it to an array of rows

        @data = @data.transpose

    end



    def close

        @connection.Close

    end

end

How to use it:
db = SqlServer.new('localhost', 'sa', 'SOMEPASSWORD')
db.open('Northwind')
db.query("SELECT * from Customers;")
puts field_names = db.fields
cust = db.data
puts cust.size
puts cust[0].inspect
db.close

Search for specific text in all stored procedures

mrtrombone (Mark Easton) — Thu, 26 Apr 2007 03:08:05 GMT



declare @search varchar(50)

SET @search = 'searchterm'



SELECT    

     ROUTINE_NAME,

     ROUTINE_DEFINITION

FROM    

    INFORMATION_SCHEMA.ROUTINES

WHERE    

    ROUTINE_DEFINITION LIKE @search

ORDER BY

    ROUTINE_NAME

Select random row, SQL Server

brook (Robert Brook) — Mon, 26 Feb 2007 11:33:18 GMT

// select a random row in SQL Server, apparently. Untested.



SELECT TOP 1 column FROM table

ORDER BY NEWID()

SQL server ASP database test

spearmahj (j) — Tue, 02 Jan 2007 03:55:42 GMT

// basic sql server database asp code test



<%

str_ConnectionString = "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=database;User Id=user;Password=pwd;"

Set oConn = Server.CreateObject("ADODB.Connection")

oConn.Open str_ConnectionString



set rs = Server.CreateObject("ADODB.recordset")

strSQL="select * from tablename"



rs.Open strSQL, oConn



do until rs.EOF

	for each x in rs.Fields

		response.write x.value & "|"

	next

	rs.MoveNext

	response.write "
"

loop	



rs.Close



oConn.Close

set oConn = Nothing

%>

sql server backup script for all user databases

spearmahj (j) — Tue, 02 Jan 2007 03:35:50 GMT

// This code will create new backup locations on disk on a daily / weekly / yearly basis



-- Declare variables



declare @count int, @max int, @day varchar(10), @path varchar(1000), @dbname varchar(55)

declare @device varchar(255), @physical varchar(1255), @lastbackup smalldatetime



-- Set count to start at database ID +1 

-- master	1

-- tempdb	2

-- model		3

-- msdb		4

-- pubs		5

-- Northwind	6



set @count = 7



-- Set maximum no of databases on server



--set @max = (select count(*) from master..sysdatabases) +1

set @max = 12



-- Set backup path



set @path='D:\SQL\backups\'



-- Get unique cycle number

-- For weekly cycle - datepart(dw,getdate())

-- For monthly cycle - datepart(dd,getdate())

-- For yearly cycle - datepart(dy,getdate())



set @day=datepart(dw,getdate())



-- While the starting number of databases is smaller than the maximum number

-- of databases:

-- 1.	Check if there is already a backup device made for this

-- day of the cycle. If there isn't create the device.

-- 2.	See if a backup has already been done for this day of the cycle. If 

-- it hasn't do a full backup, otherwise do a differential backup.

-- 3.	Repeat for next database.



while (@count < @max)

begin

	

	set @dbname = (select [name] from master..sysdatabases where dbid=@count)

	set @device=@dbname + '_' + @day

	

	if (select count(*) from master..sysdevices where name = @dbname + '_' + @day)=0 begin

		set @physical=@path+@device + '.bak'

		EXEC sp_addumpdevice 'disk', @device, @physical

	end

	

	begin

	

		set @lastbackup=(select max(backup_finish_date) 

		from msdb.dbo.backupmediafamily, msdb.dbo.backupset

		where msdb.dbo.backupset.media_set_id=msdb.dbo.backupmediafamily.media_set_id

		and logical_device_name=@device

		)

		

		if datepart(dd, @lastbackup)!=datepart(dd,getdate()) begin

			BACKUP DATABASE @dbname TO @device WITH INIT

		end

		else begin

			BACKUP DATABASE @dbname TO @device WITH DIFFERENTIAL

		end 

	

	end  

	

	set @count = @count + 1  



continue 

break



end

Connect to MS SQL Server via ODBC vs OLEDB.

MattScilipoti (Matt Scilipoti) — Thu, 05 Oct 2006 02:21:27 GMT

From http://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServer:

Connecting via the OLEDB adaptor seems to have it’s quirks. Another posibility is to connect via ODBC (tested with Rails 1.1.6)

e.g. for a trusted connection



development:

  adapter: sqlserver

  mode: odbc

  dsn: Driver={SQL Server};Server=;Database=;Trusted_Connection=yes;