SQLServer : Convert varchar date part in DateTime

DECLARE @month int
SET @month=1
DECLARE @day int
SET @day=7
DECLARE @year int
SET @year = 2004

DECLARE @dte varchar(10)
SET @dte = RIGHT(CAST('0000' + CAST(@year as varchar) as varchar), 4) +RIGHT(CAST('00' + CAST(@month as varchar) as varchar), 2) + RIGHT(CAST('00' + CAST(@day as varchar) as varchar), 2)
DECLARE @myDate datetime
SET @myDate=convert(datetime,@dte,112)
SELECT @myDate

SQL Server 2005 Default Backup Location

Why didn't they put this available through SSMS ?
They have set this at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\BackupDirectory

consuming SQL Server web services

As everyone knows, it's now possible to create - quickly - a web service on sql server.
This web service can contains several methods which are all linked to one stored procedure.

This is done with an EndPoint
Ex :
We have a table called "buildings" where all the building of our organization are stored
We have 2 stored procedures

• up_building_getall() : gets all the building like the figure above (+ other information)
  
• up_building_getone(buildingid,textid) : gets one building by it's id and/or by it's textid.
  

Creating a web service :

DROP ENDPOINT BuildingsEndPoint;
GO

CREATE ENDPOINT BuildingsEndPoint
STATE = STARTED
AS HTTP(
PATH = '/buildings',
AUTHENTICATION = (INTEGRATED ),
PORTS = ( CLEAR),
site = 'mydbServer'
)
FOR SOAP (
WEBMETHOD 'GetOne' (NAME='mydb.dbo.Up_Building_GetOne'),
WEBMETHOD 'GetAll' (NAME='mydb.dbo.Up_Building_GetAll'),
WSDL = DEFAULT,
SCHEMA = STANDARD,
DATABASE = 'mydb'
);
GO

The WSDL for this web service can be accessed at http://mydbserver/buildings?wsdl and gives this nice output :

Add a web reference to your project :





And it's done.

You now have in your project under "Web References" a folder "mydbserver".
Anywhere in your code you can now create a mydbserver.BuildingsEndPoint as in the example here under :

static void Main(string[] args)
{
try
{
TmpConsole.mydbserver.BuildingsEndPoint be = new TmpConsole.mydbserver.BuildingsEndPoint();
be.UseDefaultCredentials = true;

object[] products = be.GetAll();
if (products[0].ToString() == "System.Data.DataSet")
{
DataSet ds = (System.Data.DataSet)products[0];
DataTable dtt = ds.Tables[0];
//simple console method that display a datatable
interpretDataTable(dtt);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}

public static void interpretDataTable(DataTable dtt)
{
if (!(dtt == null || dtt.Rows == null))
{
foreach (DataRow dr in dtt.Rows)
{
Console.Write("- {0}={1}", dtt.Columns[0].ColumnName, dr[0].ToString());
for (int i = 1; i < dtt.Columns.Count; i++)
Console.Write("/{0}={1}", dtt.Columns[i].ColumnName, dr[i].ToString());
Console.WriteLine();
}
}
else
Console.WriteLine("Null or no rows.");
}


Watch out for the "UseDefaultCredentials"
Otherwise, the great HTTP 401 may show it's strength...

Thread with ASP.NET and Integrated security

Recently, we decided to move to integrated security on all our Dev machines so that we won't need complex security management on the dev db.
Therefore, we used the "impersonate" tag in the web/machine .config as following :
<system.web>
<identity impersonate="true" userName="domain\user" password="password" />

Everything was fine except for the process that created thread for launching parallel DB actions.

Ex of Non working code :

threads.Add(new Thread(new ThreadStart(this.LaunchOneSqlCommand)));

The method "LaunchOneSqlCommand" pops one SQLCommand from the stack of commands to execute and launch them with integrated security specified in the connection string :

public void LaunchOneSqlCommand(object windowsIdentity)

{

SqlCommand com = null;

using (SqlConnection con = new SqlConnection(connectionString))

{

try

{

com = popOne();

if (com != null)

con.Open();

while (com != null)

{

com.Connection = con;

int res = com.ExecuteNonQuery();

...

com = popOne();

}

con.Close();

}

catch (Exception ex)

{
...

throw new MultiThreadedSqlCommandsException(ex.Message);

}

}

...

}

This gave us the terrific error :
Login failed for user ''. The user is not associated with a trusted SQL Server connection.

After some test, we guessed that the thread launched by a Dll under asp.net use the same windowsIdentity as aspnet, and not the one specified with the "impersonate" tag.

Therefore, we had to do some adaptations :

threads.Add(new Thread(new ParameterizedThreadStart(this.LaunchOneSqlCommand)));

And the start must be as following :
for (int i = 0; i < threads.Count; i++)
{
((Thread)thrds[i]).Start(System.Security.Principal.WindowsIdentity.GetCurrent());
}

And the method LaunchOneSqlCommand became

public void LaunchOneSqlCommand(object windowsIdentity)

{

string[] rolesArray = {"role1"};

((WindowsIdentity)windowsIdentity).Impersonate();

Thread.CurrentPrincipal = new System.Security.Principal.GenericPrincipal((IIdentity )windowsIdentity, rolesArray);

...
}