Blog Archives
Query Sql Version Microsoft MSSQL
set nocount on go select @@version go select 'Edition: ' + convert(char(30), serverproperty('Edition')) go select 'Product Version: ' + convert(char(20), serverproperty('ProductVersion')) go select 'Product Level: ' + convert(char(20),serverproperty('ProductLevel')) go set nocount off go
References
https://support.microsoft.com/kb/321185
http://blogs.msdn.com/b/euanga/archive/2008/01/23/confused-by-sql-server-version-numbers.aspx
Custom DAL Class SQL ORM ASP .NET
(common.DataObject may be of your choosing or may simply replace with dynamic)
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; using System.Web.UI.WebControls; using System.Web.UI; using System.Data.SqlClient; using AIS.Common; //this is a common namespace I use in examples throughout my site using System.Reflection; using System.Dynamic; //TODO: consider returning ienumerable in sp return values for lazy eval vs .tolist immediate eval namespace AIS.DAL.AppName { public static class StoredProcedures { public delegate void ErrorHandler(Exception ex); /// <summary> /// If no custom error handling is bound to this event, exceptions will be thrown back up to the calling function. /// If custom handling is bound to this event, ensure it does not perform a redirect or kill the thread unless you intend to abort the procedural /// steps following the method/function call which threw the error. /// </summary> public static event ErrorHandler HandleError; #region Unique Procedures public static List<Common.DataObject> LoadUserSessions_All(dynamic o) { return ExecuteRead("an_get_db_fn1", o); } public static List<Common.DataObject> LoadUserSessionsDetails_LiveStream(dynamic o) { return ExecuteRead("an_get_db_fn2", o); } public static List<Common.DataObject> LoadUserSessionsDetails_Live(dynamic o) { return ExecuteRead("an_get_db_fn3", o); } public static int LogChat() { return ExecuteScalar("an_get_db_fn4", null); } public static int LogError() { return ExecuteScalar("an_get_db_fn5", null); } #endregion //TODO: consider hiding from external assemblies which would require strong mappings above #region Execution Logic public static List<Common.DataObject> ExecuteRead(string procedurename, dynamic param) { try { SqlDataSource sds = new SqlDataSource(); sds.ConnectionString = ConfigValues.TrainingPortalConnectionString; sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure; sds.SelectCommand = procedurename; if (param != null) { foreach (PropertyInfo pi in param.GetType().GetProperties()) { object pval = pi.GetValue(param, null); if (pval != null) { sds.SelectParameters.Add(pi.Name, pval.ToString()); } } } List<Common.DataObject> results = new List<Common.DataObject>(); //sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => Load_AddResult<dynamic>(o, ref results)); sds.Select(new DataSourceSelectArguments()).Cast<DataRowView>().ToList().ForEach(o => Load_AddResult<Common.DataObject>(o, ref results)); return results; } catch (Exception ex) { HandleError_Condensed(ex); return null; } } public static void Load_AddResult<t>(Common.DataObject o, ref List<t> results) { try { t r = (t)Activator.CreateInstance(typeof(t)); foreach (PropertyInfo pi in typeof(t).GetProperties()) { object v = o[pi.Name].ToString(); Type pt = Type.GetType(pi.PropertyType.FullName); //try { pi.SetValue(r, Convert.ChangeType(v, pt), null); } //catch (Exception ex) { HandleError_Condensed(ex); } o.Add(pi.Name, Convert.ChangeType(v, pt)); } results.Add(r); } catch (Exception ex) { HandleError_Condensed(ex); } } //public static void Load_AddResult<t>(dynamic o, ref List<t> results) //{ // try // { // t r = (t)Activator.CreateInstance(typeof(t)); // foreach (PropertyInfo pi in typeof(t).GetProperties()) // { // object v = o[pi.Name].ToString(); // Type pt = Type.GetType(pi.PropertyType.FullName); // try { pi.SetValue(r, Convert.ChangeType(v, pt), null); } // catch (Exception ex) { HandleError_Condensed(ex); } // } // results.Add(r); // } // catch (Exception ex) // { // HandleError_Condensed(ex); // } //} public static void ExecuteNonScalar(string procedurename, dynamic param) { try { ExecuteScalar(procedurename, param); } catch (Exception ex) { HandleError_Condensed(ex); } } public static int ExecuteScalar(string procedurename, dynamic param) { try { SqlDataSource sds = new SqlDataSource(); sds.ConnectionString = ConfigValues.TrainingPortalConnectionString; sds.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure; sds.UpdateCommand = procedurename; if (param != null) { foreach (PropertyInfo pi in param.GetType().GetProperties()) { object pval = pi.GetValue(param, null); if (pval != null) { sds.SelectParameters.Add(pi.Name, pval.ToString()); } } } return sds.Update(); } catch (Exception ex) { HandleError_Condensed(ex); return 1; //1 signifies error in tsql } } #endregion private static void HandleError_Condensed(Exception ex) { if (HandleError != null) { HandleError(ex); } else { throw new Exception(ex.Message, ex); } } } }
MSSQL User Defined Functions vs Stored Procedures
I received this question earlier today, and thought it was a valid question often misunderstood, and deserving of a small write-up:
“Should a User Defined Function be your first choice instead of a Stored Procedure?”
While there are many pros and cons of each not covered in this write-up (review your versions on MSDN for details), including some features which may not be apparent until you have an issue to troubleshoot (such as sp_who filtering), you can generally ask yourself a single question up front that can help you determine which you should use.
Simply, if the db functionality you need to implement in the function/procedure requires
any DML (insert/update/delete), then go with a stored procedure. Advanced selects and/or filters are best left up to views/table valued functions.
Additionally, do not be afraid to use a combination of functions and procedures especially if there is a goal of re-usability, in accordance with the design considering the planned growth of your db as your software & db architecture permits. On that note, consider and test the performance differences of these implementations, as a compiled/cached function/procedure containing more logic internally may outperform one utilizing logic that is spread throughout.
References
SP_who filtering UDF vs SP, http://stackoverflow.com/questions/2567141/use-sql-to-filter-the-results-of-a-stored-procedure
Data Manipulation Language, http://en.wikipedia.org/wiki/Data_manipulation_language
(Some) Differences (about.com), http://databases.about.com/od/sqlserver/a/procs_vs_functs.htm
(Some) Differences (stackoverflow), http://stackoverflow.com/questions/2039936/difference-between-stored-procedures-and-user-defined-functions
mssql aes function encrypt decrypt in microsoft sql
Simple symmetric encryption in MSSQL. Copied from MS references below:
USE AdventureWorks2012; --If there is no master key, create one now. IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101) CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj' GO CREATE CERTIFICATE Sales09 WITH SUBJECT = 'Customer Credit Card Numbers'; GO CREATE SYMMETRIC KEY CreditCards_Key11 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Sales09; GO -- Create a column in which to store the encrypted data. ALTER TABLE Sales.CreditCard ADD CardNumber_Encrypted varbinary(128); GO -- Open the symmetric key with which to encrypt the data. OPEN SYMMETRIC KEY CreditCards_Key11 DECRYPTION BY CERTIFICATE Sales09; -- Encrypt the value in column CardNumber using the -- symmetric key CreditCards_Key11. -- Save the result in column CardNumber_Encrypted. UPDATE Sales.CreditCard SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11') , CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary , CreditCardID))); GO -- Verify the encryption. -- First, open the symmetric key with which to decrypt the data. OPEN SYMMETRIC KEY CreditCards_Key11 DECRYPTION BY CERTIFICATE Sales09; GO -- Now list the original card number, the encrypted card number, -- and the decrypted ciphertext. If the decryption worked, -- the original number will match the decrypted number. SELECT CardNumber, CardNumber_Encrypted AS 'Encrypted card number', CONVERT(nvarchar, DecryptByKey(CardNumber_Encrypted, 1 , HashBytes('SHA1', CONVERT(varbinary, CreditCardID)))) AS 'Decrypted card number' FROM Sales.CreditCard; GO
References:
Cannot login logon failed to SQL Server Windows Auth
Locked out of SQL Server? Accidentally right click on your account in Management Studio and hit delete? Or did Sys Admins force you onto the domain and delete your local superman account that you installed SQL under? Oops! No problem! (If You’re a local admin).
Check out these simple steps.
First, run a cmd prompt as administrator. Enter the following. This will start sql in single user mode, enable the built in sa account, and reset the password for it.
net stop mssqlserver net start mssqlserver /c /m /t3604 sqlcmd -E alter login sa with password='1234' go alter login sa enable go exit net stop mssqlserver
Now open your registry (regedt32) and browse to:
HKLM\Software\Microsoft\Microsoft SQL Server\(Find Your Instance)\MSSQLServer\LoginMode
Change the value to 2. Then go back to command prompt and enter:
net start mssqlserver
Voila! You’re back in business. Fire up management studio and enter your instance name and the sa credentials you created, don’t forget to select sql server authentication.
Once you’re in, Microsoft recommends changing back to Windows Auth (adding your domain/local account). This is up to you. Enjoy.
Arithmetic overflow error converting type to data type numeric
This error usually occurs when the type you are trying to convert to a decimal or numeric is larger than the column or casted type you are converting to.
Ex.
insert into tablename (decnumber) values (cast(1000.00 as decimal(5,2)))
Since the number specifed is 7 digits long include after the decimal and the converted type can only contain a maximum of 5 digits, you will receive this error.
Changing the table to hold 7,2 and/or changing this in the cast will resolve it.
References
devshed (forums), http://forums.devshed.com/ms-sql-development-95/arithmetic-overflow-error-converting-numeric-to-data-type-numeric-89157.html
DLL in Stored Procedure MSSQL
Extended Stored Procedures (MSDN), http://support.microsoft.com/kb/190987
Thread Local Storage in an extended stored procedure (MSDN), http://support.microsoft.com/kb/163449
C# MySQL On Windows Named Pipes Development Mode
While outlining the scope for a new project I’ve considered the possibility of using MySQL for budgeting purposes with respect to the potential size and growth rate of the database.
I downloaded and ran through the latest setup for MySQL on Windows (x64 although I opted for the 32 bit binaries) and popped in the MySql Connector DLL (ADO not ODBC) into my project and began whipping up some code in no time.
However, this little trick seemed to be not well known after some google searching on why MySQL Workbench (great tool btw) can connect, but my app could not.
Being a security conscious developer, when installing SQL I selected developer mode and opted for a safer named pipes connection for local development vs socket network connection looping through my localhost via TCP.
If you also selected this option, you may have had trouble getting your app to connect as well.
After searching through various (not necessarily directly related) sites, I found the answer.
See simple connection string snippet below.
Pipe=MySQL;Protocol=pipe;Host=.;Database=[yourdb];User ID=[youruser];Password=[yourpass]
To my surprise, the little note about specifying a period “.” as the host for named pipe connections was not easily found in the references below..
References
PhalangerMySQL (Java2s), https://www.java2s.com/Open-Source/CSharp/Database/PhalangerMySQL/MySql/Data/CatalogData.htm
.Net MySQL Connector Documentation (MySql), http://dev.mysql.com/doc/refman/5.1/en/connector-net.html
Network Traffic Filter Documentation (wireshark), http://wiki.wireshark.org/DisplayFilters
Connecting MySQL via CLI (MySQL), http://dev.mysql.com/doc/refman/5.1/en/connecting.html
Connection String Reference (MySQL), http://dev.mysql.com/doc/refman/5.1/en/connector-net-connection-options.html
MySQL Workbench Documentation (huihoo), http://docs.huihoo.com/mysql/refman-5.5-en/wb-intro.html
Connectionstrings.com, http://www.connectionstrings.com/mysql
codemaker.co.uk, http://www.codemaker.co.uk/it/tips/ado_conn.htm#MySQLNETNativeProvider
MySqlConnection in C# (bitdaddys)http://bitdaddys.com/MySQL-ConnectorNet.html
PHP MySQL Reference, http://php.net/manual/en/function.mysql-connect.php
MySqlCommand in C# (stackoverflow) http://stackoverflow.com/questions/2775692/c-and-mysql-net-connector-any-way-of-preventing-sql-injection-attacks-in-a-ge