Hugo Estrada's House of Wonder

These guys will first allow you to insert an identity into a sql server table, and then turn off that power.

SET IDENTITY_INSERT tablename ON
SET IDENTITY_INSERT tablename OFF

bogus example:

SET IDENTITY_INSERT funkyFooTable ON

insert into funkyFooTable
(id, funkyBarColumn) values (31415, 'Tasty pie....')

SET IDENTITY_INSERT funkyFooTable OFF

And here is the source of this tidbit with a better explanation on how to use it. Thanks, Ben Nadel!
http://www.bennadel.com/blog/24-Turning-Off-and-On-Identity-Column-in-SQ...

Today I couldn't move some geometries because it was throwing some weird error. The answer that let me do it was on the link:

http://mapwrecker.wordpress.com/2009/03/06/using-sql-geometry-with-ssis-...

SELECT * FROM information_schema.tables

http://stackoverflow.com/questions/175415/how-do-i-get-list-of-all-table...

I forget this one, so...
select column_name,* from information_schema.columns
where table_name = 'YourTableName'
order by ordinal_position

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/5995c1d...

I keep looking this up. so it has gain its place in this site:

GRANT EXECUTE ON procedureName to username

This was the only code that I found where it made it easy to execute a query to a MS SQL Server db. If you are running it on windows, the libary, win32ole, comes with the installation. It is a small class that handles the connection and execution of the db.

I normally copy interesting code here and give the link, but the post is so short and to the point that I will just thank David Mullet, for this great piece of code. It made my day :)

http://rubyonwindows.blogspot.com/2007/03/ruby-ado-and-sqlserver.html

I changed my mind. Here is the text of the post:

Ruby, ADO, and SQLServer

Ruby and ActiveX Data Objects (ADO) make working with Microsoft SQL Server databases simple. Here's a simple example of a class that manages the SQL Server database connection and queries:

require 'win32ole'

class SqlServer
# This class manages database connection and queries
attr_accessor :connection, :data, :fields

def initialize
@connection = nil
@data = nil
end

def open
# Open ADO connection to the SQL Server database
connection_string = "Provider=SQLOLEDB.1;"
connection_string << "Persist Security Info=False;"
connection_string << "User ID=USER_ID;"
connection_string << "password=PASSWORD;"
connection_string << "Initial Catalog=DATABASE;"
connection_string << "Data Source=IP_ADDRESS;"
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

You can then use this class as follows:

db = SqlServer.new
db.open
db.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';")
field_names = db.fields
players = db.data
db.close

The above code is, of course, incomplete and can certainly be improved and extended (error handling, etc.). But, hopefully, it provides you with a solid foundation on which to build.

UPDATE: You might like to know that you can automate many of your SQL Server administrative tasks by leveraging Distributed Management Objects (SQL-DMO). I've explained this in a later article here.

Thanks for stopping by!

I often forget this, so....

MySQL autoincrement == IDENTITY(1,1) as in

id int IDENTITY(1,1) PRIMARY KEY,