Entries Tagged as SQL Server

Resetting your Identity Column (SQL Server)

August 25, 2004 · 2 Comment s

Just a handy little bit of SQL for when you need to reset an Identity column back to it's initial starting value.
DBCC CHECKIDENT ('tablename',RESEED,0)
Useful for when you want to re-initialise your identity columns after testing.

→ 2 Comment s Tags: SQL Server

Dynamic SQL

June 08, 2004 · 2 Comment s

A problem I came up against a short while ago was how to get Stored Procedures in SQL Server to handle lists. The task in question involved inserting, updating or indeed deleting x number of rows in my database. Now, I could have initiated my <cfstoredproc></cfstoredproc> (or <cfquery></cfquery>) x number of times but to me this seemed unnecessary, after all why can't you just pass a coma-delimited list in? Well, by default there is no way for SQL Server to handle a list, or indeed an array but as always there is at least one way around it, and my solution is posted below.
CREATE PROCEDURE usp_save_changes
@id varchar(1000) -- this should be big enough to handle a lengthy list
AS
SET NOCOUNT ON
DECLARE @separator_position int -- used to locate each separator character in list
DECLARE @list_value varchar(1000) -- holds each list value as it's returned
DECLARE @separator char(1) -- this is our separator character, which we are going to SET to a comma below
SET @separator=',' -- We could easily change the Stored Procedure so that the separator character can be passed in

-- We need an extra separator character at the end of the list we are passing in
SET @id = @id + @separator

-- Loop through list searching for separator characters
WHILE patindex('%' + @separator +'%', @id) <> 0
BEGIN
-- patindex matches the pattern against a string
SELECT @separator_position = patindex('%' + @separator + '%', @id)
SELECT @list_value = LEFT(@id,@separator_position -1)

-- NOW DO YOUR ACTION HERE (COULD BE INSERT/UPDATE/DELETE)
UPDATE newsletter
set active=1
WHERE id=@list_value

-- Replace what we just processed with an empty string
SELECT @id = stuff((@id, 1, @separator_position, ' ')
END
And there you have it, Dynamic SQL at work.

→ 2 Comment s Tags: SQL Server

Locked out of SQL Server?

June 08, 2004 · 10 Comment s

A month or two back I helped out a friend who had inadvertently locked themselves out of SQL Server. Basically, my friend was trying to lock down SQL Server to make it more secure and so removed the Windows group 'BUILTIN\Administrators' from the sysadmins group in SQL Server and also changed the Authentication method from Mixed Mode to Windows Authentication. Removing the BUILTIN\Administrators group isn't a problem as you can still access SQL Server using the sa account ... except you just changed authentication mode, which locks out the sa account - whoops! So, how do you get around this? Well, the first thing you need to do is Re-install SQL Server ... nah! only kindding :) The key to getting back in is to modify the following registry key: In SQL Server 7.0
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft \MSSQLServer\MSSQLServer\LoginMode
And in SQL Server 2000
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\
MicrosoftSQLServer\<instance_name>\MSSQLServer\LoginMode
The value of LoginMode is 1 for Windows Authentication and 2 for Mixed Mode. So change the value to 2, restart SQL Server and log back in using the sa account. OK, so you're back in but you'd still like to tighten things down, by using Windows Authentication. Well here's how to do it. 1) Create a Windows 2000 or Windows NT group and assign members to it. For example, call this group "SQLAdmins". 2) Map SQLAdmins to a Windows Authenticated login in your SQL Server and assign this login to the sysadmin server role. 3) Delete the BUILTIN\Administrators login or remove it from the sysadmin server role (if you haven't already done so). 4) Change your SQL Server authentication mode to Windows Authentication only. 5) Restart SQL Server to reflect the changed authentication mode.

→ 10 Comment s Tags: SQL Server