vendredi 12 décembre 2008
SQLServer : Convert varchar date part in DateTime
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
mardi 9 septembre 2008
SQL Server 2005 Default Backup Location
They have set this at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\BackupDirectory
vendredi 29 août 2008
consuming SQL Server web services
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

- 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.
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...
mardi 8 janvier 2008
Thread with ASP.NET and Integrated security
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);
...}