How to: create login with 'sa' credentials

CREATE LOGIN loginname
WITH PASSWORD = 'changeme' MUST_CHANGE
,CHECK_EXPIRATION = ON
,DEFAULT_DATABASE = DBName
GO
EXECUTE sp_addsrvrolemember
@loginame = 'loginname',
@rolename = 'sysadmin'

How to: assign IDs/SortVal values using ROW_NUMBER()

I have a lookup table L_MyLookup with columns ID and NAME.

Later, my client wanted a SORTVAL column because they want to define the order of the values through that column instead of ID.

So

1. add the SORTVAL column
2. update the values
3. alter SORTVAL to become not nullable

Like the following code:

ALTER TABLE L_MyLookup

ADD SORTVAL int NULL

go

UPDATE L_MyLookup set SORTVAL = temp.SORTVAL

FROM

(SELECT ID, ROW_NUMBER() over (order by ID) as SORTVAL

FROM L_MyLookup) as temp

WHERE temp.ID = L_MyLookup.ID

go

ALTER TABLE L_MyLookup

ALTER COLUMN SORTVAL int NOT NULL

FTS: Create new Catalog

See: how to drop existing FTS Catalog

EXEC sp_fulltext_database 'enable'
go

CREATE FULLTEXT CATALOG MyFTSCatalog
go

CREATE FULLTEXT INDEX ON MyTable
(
Document
TYPE COLUMN DocumentExtension
LANGUAGE 0x0407 --this is German
)
KEY INDEX PK_MyTable
ON MyFTSCatalog
go

--to populate the catalog,

--do the following when traffic is light:
ALTER FULLTEXT INDEX ON Production.Document SET CHANGE_TRACKING AUTO;
GO

FTS: Drop Catalog

sp_fulltext_table 'MyTable', 'drop'
go
sp_fulltext_database 'enable'
go
sp_fulltext_catalog 'MyFTSCatalog', 'drop'
go
sp_fulltext_service 'clean_up'
go

SSIS auto generate update scripts

Specify the TableName in @TableName variable to create the Update script to be used for SSIS.

DECLARE @counter int;

DECLARE @QueryString nvarchar(2400) = ''

DECLARE @ColumnName nvarchar(120);

DECLARE @TableName nvarchar(120) = 'MyTable'

CREATE TABLE #ColumnList

(

ID int IDENTITY(1,1)

,ColumnName varchar(1200)

)

INSERT #ColumnList

select sys.columns.name from sys.columns

inner join sys.tables on sys.tables.object_id = sys.columns.object_id

where sys.tables.name = @TableName

and sys.columns.name <> 'ID'

SET @counter = 1

SET @QueryString = 'UPDATE dbo.' + @TableName + '

SET '

SET @ColumnName = (SELECT ColumnName FROM #ColumnList WHERE ID=@counter)

SET @QueryString = @QueryString + '

' + @ColumnName+ ' = ? '

SET @counter = @counter + 1;

WHILE @counter <= (SELECT COUNT(*) FROM #ColumnList)

BEGIN

SET @ColumnName = (SELECT ColumnName FROM #ColumnList WHERE ID=@counter)

SET @QueryString = @QueryString + '

,' + @ColumnName+ ' = ? '

SET @counter = @counter + 1;

END

SET @QueryString = @QueryString + '

WHERE ID = ?;'

PRINT @QueryString

DROP TABLE #ColumnList

SSIS How to UPDATE instead of INSERT (UPSERT)

I'm working on several SSIS Transfer Packages right now and I just figured out how to do an "UPSERT": How to insert rows that don't exist in the destination table and how to update rows that exist in both source and destination tables. This will work across servers (which is the environment I'm working on).

This is how one table/view would look like in an SSIS package:

Yes, that means (as far as I know) that you have to create four objects for every table you want to transfer data from/to.

The Source object is, of course, connected to the Source database's table/view. The other objects beneath it (the Lookup, Destination and Update objects) are all connected to the Destination database's table/view.

The Update object is the only tricky element in this package. I specify the update script manually, which looks like:

UPDATE dbo.MyTable

SET Column1 = ?

,Column2 = ?

,Column3 = ?

WHERE ID= ?

(see: Generate SSIS Update script which I use to generate the update script for each table that I have in my SSIS package)

Each "?" will be automatically mapped by the system based on the order you defined it. All you have to do next is to specify the Column Mappings. Make sure that you do it in the right order like the image below:

Note how the ID is specified last -- that's because its corresponding "?" was specified last in the Update statement.

How to select value from strongly typed XML

Table Name: TableHasXML

has Column: XML_Column xml(XMLCollectionSchema)

XML data is similar to:

[root xmlns="http://thingswelovetohate.com/schema/2010/PinayProgrammer.xsd" ....]

[row ]

[column1]Things We Love to Hate[/column1]

[column2]http://www.thingswelovetohate.com[/column2]

[/row]

[row]

[column1]Pinay Programmer[/column1]

[column2]http://beingoyen.blogspot.com[/column2]

[/row]

....

[/root]

(yes, I know that was sloppy but blogger wouldn't let me just copy-paste my actual schema. Blogger treats any greater than/less than signs as HTML tags)

XML_Column.query('declare default element namespace "http://thingswelovetohate.com/schema/2010/PinayProgrammer.xsd";

data(/root/row[Column1="Things We Love to Hate"]/Column2)')

FROM TableHasXML

Result = http://www.thingswelovetohate.com

If your XML column or variable doesn't have a namespace then leave out the namespace declaration as well:

XML_Column.query('data(/root/row[Column1="Things We Love to Hate"]/Column2)') FROM TableHasXML

How to know if a string is part of an XML?

I have a table named "TempTable" with an XML column named "XML_Collection". Now I want to know if the string "My Valentine" appears in any of those XML rows.

I used the string manipulator: PATINDEX

SELECT * from TempTable WHERE PATINDEX('%My Valentine%', convert(varchar(max),XML_Collection)) > 0

Important notes:

• be sure to convert the XML type to varchar(max) first before using any string manipulator functions
• if PatIndex == 0 then that string pattern does not exist
• if PatIndex > 0 then that string pattern exists

TSQL Latin Collation Error

Error encountered:
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

I'm not sure if it's as convenient with the earlier versions but SQL Server 2008 allows you to easily change the collation of your database through the Properties window.

The first thing you would have to do is to drop all connections to the database. This can be easily done by right clicking on you database --> Tasks --> "Detach..." then tick the "Drop all connections" check box.

Then simply restore the database using your back up file (it's usually the same name as your database with a .bak file extension).

Now you're sure no one is initially connected to the database.

Right click on the database --> Properties --> Options then on the top is a drop down for the Collation options. We generally choose "SQL_Latin1_General_CP1_CI_AS".

Select XML Schema Collection type of an XML Column

I spent an afternoon looking for a way to get the XML Schema Collection type of a given column.

I wanted to copy the contents of a table (that has an XML column with a user defines Schema Collection) to another table but I get this error message: Implicit conversion between XML types constrained by different XML schema collections is not allowed. Use the CONVERT function to run this query. 

I understand that I have to CONVERT it to the same XML Schema Collection but how do I do so dynamically?

I ended up resorting to MSDN's SQL forums. The next morning, Peso, an MVP, gives me the answer:

SELECT object_name(ac.object_id) AS TableName,
ac.name as ColumnName,
  x.name AS CollectionName
FROM sys.all_columns AS ac
LEFT JOIN sys.xml_schema_collections AS x ON x.xml_collection_id = ac.xml_collection_id
WHERE object_name(ac.object_id) = 'MyTableNameHere'

Run SQL Script from command line

I have a 1GIG update script generated by my SQL Server Management Studio, how would you run it?

Original story: I have a perfectly running database in 2008 that has to be transferred to a 2005 server. I tried back-up and restore from 2008 to 2005 servers but that got all weird so I needed a new strategy.

Solution: Generate Script.

So I generated a script (destination: file instead of a new query window because openning a file and writing a 1Gig worth of text on it would result to a not-enough memory error). As a result, I have a 1GB update script that I can't open on the Management Studio but I have to run on 2005.

Solution: call it from the command prompt then run it.

Here's how:

1. Make sure that your script has USE Database statement at the very top. You don't have to open your file to check (actually you SHOULDN'T or your machine might hang). Just make sure you toggle the option

2. On command line type: OSQL -U [UserLogin] -i [absolutePathOfSQLscript]

For example:

OSQL -U sa -i C:\Users\Oyen\MyDocuments\1Gig_MergeScript.sql

**Note: Case matters (U != u)

You may also opt to display the script ran with the token -e which would look like: OSQL -U [UserLogin] -e -i [absolutePathOfSQLscript]

3. Password will be prompted if it is required by the login, then simply provide the password **Notice that whatever you type will not be displayed for security.

4. Press enter then wait until the script is finished running.

To note: it took me less than an hour to run a 1gigabyte update script. I hope that would help you estimate how long it will take you to run yours :)

TSQL: Unique Constraint

To add a UNIQUE constraint to a column in a table:

ALTER TABLE {TableName} ADD CONSTRAINT {ConstraintName} UNIQUE({UniqueColumnName})

Such as:

ALTER TABLE Department ADD CONSTRAINT UNIQUE_DepartmentName UNIQUE (NAME)

To add a UNIQUE constraint to two columns in a table:

ALTER TABLE {TableName} ADD CONSTRAINT {ConstraintName} UNIQUE
({UniqueColumnName1},{UniqueColumnName2})

Such as:

ALTER TABLE Department ADD CONSTRAINT UNIQUE_DepartmentNameRoom
UNIQUE(NAME,RoomNumber)

Where is aspnet_regsql.exe

MSDN says

Aspnet_regsql.exe file is located in the
[drive:]\%windir%\Microsoft.NET\Framework\version folder on your Web server

But where exactly is that?

Here's an example of an absolute path of my aspnet_regsql.exe

C:\\Windows\Microsoft.NET\Framework\V2.0\aspnet_regsql.exe

Based on that example, it may be easier for you to determine where exactly your server's aspnet_regsql.exe file is. Be sure to take note of your server's OS (Framework vs Framework64) and its version.

TSQL Script to drop all constraints on a given table

A SELECT statement that will give you the 'drop constraint' queries for all constraints related to MyTable

SELECT 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id) + ' DROP CONSTRAINT ' + f.name
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id) = 'MyTable'
OR OBJECT_NAME (f.referenced_object_id) = 'MyTable'

A SELECT statement that will give you the 'add constraint' queries for all constraints related to MyTable

SELECT 'ALTER TABLE ' + OBJECT_NAME(f.parent_object_id) + ' WITH CHECK ADD CONSTRAINT ' +
f.name + ' FOREIGN KEY(' + COL_NAME(fc.parent_object_id, fc.parent_column_id ) + ') REFERENCES ' + OBJECT_NAME(f.referenced_object_id) + ' (' + COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) + ');'
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.parent_object_id) = 'MyTable'
OR OBJECT_NAME (f.referenced_object_id) = 'MyTable'

TSQL: Rename Tables or Columns

To rename a table:
EXEC sp_rename @objname='{TableName}', @newname='{NewTableName}', @objtype='TABLE'

To rename a column:
EXEC sp_rename @objname='{TableName}.{ColumnName}', @newname='{NewColumnName}', @objtype='COLUMN'

Note: you may refrain from specifying the Stored Procedure's parameters (@objname, @newname, @objtype) if you will specify the input in the proper order as specified above. Thus:

To rename a table:
EXEC sp_rename '{TableName}', '{NewTableName}', 'TABLE'

To rename a column:
EXEC sp_rename '{TableName}.{ColumnName}', '{NewColumnName}', 'COLUMN'

TSQL Error: EXECUTE permission denied on XML Column

I got this error when I created a restricted login for a user. We've only recently been optimizing the use of XML data types in SQL Server so it took me an hour to find the solution -- not on MSDN but on some other programmer's blog (gave my thanks of course! and the link appears on the bottom of this post).

Error we get:
System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The EXECUTE permission was denied on the object {XMLSchema1}, database {DatabaseName}, schema 'dbo'.

Solution was simple:
GRANT EXECUTE ON XML SCHEMA COLLECTION::{XMLSchema1} TO {RestrictedLoginName}

Thanks to Bob Beauchemin.

TSQL Add/Delete User, Login, Role (Code sample)

The first group of code uses the system stored procedures that are still currently supported but will soon be deprecated in future updates. The structure of the following codes (1) try to drop the existing login/role/user; (2) create a new login/role/user; (3) grant permissions only for the first three tables in the current database;

Remember that new roles have no permissions granted on them by default. This assures that they would not be able to access certain tables, views, or stored procedures unless specifically specified.

Old, soon to be deprecated code:

DECLARE @Role varchar(20) = 'RestrictedRole'
DECLARE @DBName varchar(20) = 'DatabaseName'
DECLARE @Login varchar(120) = 'RestrictedLogin'
DECLARE @LoginPword varchar(120) = 'pazzword123'
DECLARE @counter int;
DECLARE @tablename varchar(50);
DECLARE @grantStatement nvarchar(200);

BEGIN TRY exec sp_droplogin @login; END TRY
BEGIN CATCH END CATCH

BEGIN TRY exec sp_dropuser @login; END TRY
BEGIN CATCH END CATCH

BEGIN TRY exec sp_droprole @role; END TRY
BEGIN CATCH END CATCH;

EXEC sp_addlogin @loginame = @Login
,@passwd = @LoginPword
,@defdb = @DBName;

EXEC sp_addrole @Role, 'dbo';

EXEC sp_adduser @loginame = @Login
,@grpname = @Role;

Better/more up to date code uses CREATE LOGIN instead of sys.sp_addlogin

DECLARE @Role varchar(20) = 'RestrictedRole'
DECLARE @DBName varchar(20) = 'DatabaseName'
DECLARE @Login varchar(120) = 'RestrictedLogin'
DECLARE @LoginPword varchar(120) = 'pazzword123'
DECLARE @counter int;
DECLARE @tablename varchar(50);
DECLARE @grantStatement nvarchar(200);

BEGIN TRY
SET @grantStatement = 'DROP LOGIN ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;
END TRY
BEGIN CATCH END CATCH

BEGIN TRY
SET @grantStatement = 'DROP USER ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;
END TRY
BEGIN CATCH END CATCH

BEGIN TRY
SET @grantStatement = 'DROP ROLE ' + @Role
EXEC dbo.sp_executesql @statement=@grantStatement;
END TRY
BEGIN CATCH END CATCH

SET @grantStatement = 'CREATE LOGIN ' + @Login + ' WITH PASSWORD = ''' + @LoginPword + ''', DEFAULT_DATABASE=' + @DBName
EXEC dbo.sp_executesql @statement=@grantStatement;
SET @grantStatement = 'CREATE ROLE ' + @Role
EXEC dbo.sp_executesql @statement=@grantStatement;
SET @grantStatement = 'CREATE USER ' + @Login + ' FOR LOGIN ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;

Then to dynamically grant SELECT, INSERT, UPDATE, DELETE permissions on the first three tables in the current database, then append this code on the end of your choice of login/user/role creation code from above (to reuse the declared variables).

CREATE TABLE #SelectInsertUpdateTable
(
ID int IDENTITY(1,1) PRIMARY KEY
,TableName varchar(50)
)

INSERT #SelectInsertUpdateTable (TableName)
select top 3 @DBName + '.dbo.' + sys.tables.NAME from sys.tables

SET @counter = (SELECT COUNT(*) FROM #SelectInsertUpdateTable);
WHILE @counter > 0
BEGIN
SET @tablename = (SELECT TableName FROM #SelectInsertUpdateTable WHERE ID=@counter);
SET @grantStatement = 'GRANT SELECT, INSERT, UPDATE on ' + @tablename + ' to ' + @Login
EXEC dbo.sp_executesql @statement=@grantStatement;
SET @counter-=1;
END

DROP TABLE #SelectInsertUpdateTable;

TSQL Check Constraint Example

Theoretical table columns:

1. hasSister BIT NOT NULL
   
2. SisterName varchar(250) NULL
3. SisterAge int NULL

Check constraints:

Fill up SisterName if hasSister is true

ALTER TABLE TableName WITH CHECK ADD CONSTRAINT CK_TableName_SisterName CHECK ((hasSister=1 AND SisterName IS NOT NULL) OR (hasSister=0 AND SisterName IS NULL))
GO

ALTER TABLE TableName CHECK CONSTRAINT CK_TableName_SisterName
GO

Fill up SisterAge with positive int if hasSister is true

ALTER TABLE TableName WITH CHECK ADD CONSTRAINT CK_TableName_SisterAge CHECK ((hasSister=1 AND SisterAge IS NOT NULL AND SisterAge > 0) OR (hasSister=0 AND SisterAge IS NULL))
GO

ALTER TABLE TableName CHECK CONSTRAINT CK_TableName_SisterAge
GO

simple Java switch case

a simple problem that I encountered in Yahoo! Answers:

I want to go through the colors in my given Color [] colors; then once it goes through all 5 colors in the array, to start all over

Best solution I can think of is using a switch case:


//define Color[] colors = {color1, color2, color3, color4, color5};
int i=0;
while(true)
{
switch (i)
{
case 1: object.setColor(color1);
break;
case 2: object.setColor(color2);
break;
case 3: object.setColor(color3);
break;
case 4: object.setColor(color4);
break;
case 5: object.setColor(color5);
break;
default: object.setColor(color1); //when all else goes wrong
break;
}

if( i == 5 )
i = 1;
else
i++;
}


where is aspnet_regsql.exe located?

aspnet_regsql.exe takes care of creating/recreating the database tables, stored procedures, schema, and whatever else database objects needed for the ASP.Net Membership.

It is normally located in:

C:\WINDOWS\Microsoft.NET\Framework\(version number)\aspnet_regsql.exe