All of the web applications that i am responsible to maintain have lots of stored procedures. And to many of them builds a SQL string and executes at the end of the procedure. Previous programmers wrote these SPs in that way because they don’t know how to handle comma separated ID values. For these cases […]
SQL Server Object Dependency
It’s always hard to make modifications to code or database design especially if you don’t know anything about the business process. In sql server 2005 (or 2008) you can use sp_depends stored procedure to obtain dependency between the objects such as table, view or stored procedure. For example just run ‘sp_depends Users’ script to determine […]
Deadlock Resources
If you encounter a deadlock problem this resources can help to understand and solve the problem. Deadlocking http://msdn.microsoft.com/en-us/library/ms177433.aspx Minimizing Deadlocks http://msdn.microsoft.com/en-us/library/ms191242.aspx Detecting and Ending Deadlocks http://msdn.microsoft.com/en-us/library/ms178104.aspx
Combine column values as one string
You can use T-Sql’s COALESCE function to combine your data into a single string. Here is a simple sample: Result: Ilbay|Talha|Roberte|Theıoden|Cihan|Sagolee|Kazım|Mehmet|Recep|Alp|Haci|Ömer Happy coding…
Linked server authentication problem
I was gettin this error when i try to execute a stored procedure from linked server : Executed as user: XXX\yyy. The OLE DB provider “SQLNCLI” for linked server “LSQL02” reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider “SQLNCLI” for linked server “LSQL02”. [SQLSTATE […]
Switch bit field value with single T-SQL
You can switch the bit field’s value with single update statement by using exclusive or operator. Happy codding… Referance: ^ (Bitwise Exclusive OR) (Transact-SQL)
Timespan and Databases
When you are developing application you may need to store timespan database. (Assuming you are using Sql Server 2005) TimeSpan.TotalSeconds can be used to store. This returns a double. And maps to decimal data type of the sql server. For reverse action to get the time span you can use TimeSpan.FromSeconds method. This will give […]