Shrink database log file
— Shrink the Transaction Log USE DatabaseName GO DBCC SHRINKFILE(, 1) BACKUP LOG WITH TRUNCATE_ONLY DBCC SHRINKFILE(, 1) GO
[HowTo] Remove Time from date in SQL
–The correct way (new since Sql Server 2008): cast(getdate() As Date) –The correct way (old): dateadd(dd, datediff(dd,0, getDate()), 0) –This is older now, but it’s still worth knowing because it can also easily adapt for other time points, like the first moment of the month, minute, hour, or year. –The fast way: cast(floor(cast(getdate() as float)) […]
[HowTo] [SQL] Insert data in a column with default value with Linq
Il suffit de jouer avec les paramêtres AutoSync et IsDbGenerated dans les propriétés du champs que l’on veut traiter. il suffit de mettre la valeur AutoSync.OnInsert dans la propriété AutoSync et true dans la valeur IsDbGenenrated Voici plus de détails : [Column( Storage=”_Id”, AutoSync=AutoSync.OnInsert, DbType=”Int NOT NULL IDENTITY”, IsPrimaryKey=true, IsDbGenerated=true )] Ces paramètres sont accessibles […]
[MSSQL] Concatenating Rows Values in Transact-SQL
see the “The blackbox XML methods” here : https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
[How To] script data in MSSQL Server 2008 R2 using Script Wizard
Right clicking on the DB Click on tasks Click on generate scripts Go through the wizard and select your tables On the options page click the Advanced button Change the “Types of data to script” option, from the default “Schema only” to “Schema and data”.
[HowTo] Can’t save table modifications in ms sql manager : Saving changes is not permitted.
In mssql server manager, when trying to update tables design, you get this message : Saving changes is not permitted. The changes you have made require the following tables to be dropper and re-created. Here is the solution… uncheck the “prevent saving changes that require table re-creation” checkbox as follow :
Handling Multiple Result (Shapes) from SPROCs
Handling Multiple Result Shapes from SPROCs [Function(Name = “spGetWebAccount”)] [ResultType(typeof(Client))] [ResultType(typeof(User))] [ResultType(typeof(UA_User))] public IMultipleResults spGetWebAccount(System.Nullable<Int64> ClientID, System.Nullable<Int64> UserID) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), ClientID, UserID); return (IMultipleResults)result.ReturnValue; } Source : http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx
How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database
How to upload a .SQL file to a Hoster and Execute it to Deploy a SQL Database @ ScottGU BLOG
[MSSQL] Reset Identity Column
If you are using an identity column on your SQL Server tables, you can set the next insert value to whatever value you want. An example is if you wanted to start numbering your ID column at 1000 instead of 1. It would be wise to first check what the current identify value is. We […]
Linq to SQL Like Operator
Starting from a simple query from Northwind Database; var query = from c in ctx.Customers where c.City == “London” select c; The query that will be sent to the database will be: SELECT CustomerID, CompanyName, … FROM dbo.Customers WHERE City = [London] There are some ways to write a Linq query that results in using […]