Browse by Tags

bcp and errorlevel checks

Published 12-17-2007 11:54 PM | jokiz

we have a number of batch scripts at work which executes sql-related tasks for each of our project releases. last time, i incorporated errorlevel checks so we know if there was a problem with the execution and in order to abort the rest of the operations...

Filed under: SQL, tips

Debugging stored procedures on SQL Server 2005

Published 09-26-2007 12:41 PM | jokiz

My current task in our project is majorly in MSSQL. I wanted to debug a chain of stored procedures and I have just found out that MS has removed debugging tools out of SQL Server 2005 client tools and relied more on visual studio's. I have experienced...

Filed under: SQL, visual studio, debugging

SQL Implicit Commands

Published 09-11-2007 5:48 PM | jokiz

Not being an SQL guy, i'm really not used to implicit commands from T-SQL. By implicit, I mean those simplified commands which does the same thing as those documented in every SQL books. I first encountered a query below from a teammate: FROM Table1...

Filed under: SQL

NHibernate, unicodes and index

Published 05-11-2007 4:06 PM | jokiz

My boss is optimizing one of our previous projects. He was specifically looking at the queries used by NHibernate and he noticed that the generated sp_executesql queries are using nvarchar parameters. The query was just a filter for just one column (Position...

Filed under: SQL, NHibernate

handling unique constraint violations

Published 04-24-2007 6:52 PM | jokiz

unique constraints are very common for database table designs in applications nowadays. there are two ways to safely code an application to cater for this unique constraint requirement. (1) do an existence check/query before doing the insert. (2) let...

Filed under: SQL, techie, concurrency

passing keys vs passing instances and the versioned business object problem

Published 03-30-2007 7:08 PM | jokiz

i once joined a team in developing a procurement/bidding system in ASP.NET. it involved "project for bidding" creation, contractor's profile management (with lots of document uploads), and bid evaluation as the core functionalities. contractors have the...

Filed under: SQL, oop

Generate DB script using NAnt's copy-replacetokens task

Published 02-05-2007 8:03 PM | jokiz

most of our .NET projects has a database project with three scripts in it, (1) create database script, (2) database shema script and the (3) default data script. for our pre-release, we packaged the database creation with default data in our Installshield...

Filed under: SQL, continuous integration, NAnt

Revisiting @@IDENTITY AND SCOPE_IDENTITY()

Published 11-15-2006 9:55 AM | jokiz

I previously posted my views regarding @@IDENTITY variable and SCOPE_IDENTITY() functions in sql server here and luckily with the single stored procedure in our project using NHibernate, i was able to revisit some of its definitions. My teammate whom...

Filed under: SQL, stored procedures

Stored Procedure Debugging

Published 11-15-2006 9:42 AM | jokiz

i previously have an experience on debugging stored procedures especially those that involve dynamic sql generation and execution. my experience told me that debugging stored procedure of this type is better with visual studio compared to query analyzer...

Filed under: SQL, visual studio, debugging, stored procedures

CommandTimeout vs ConnectionTimeout

Published 08-15-2006 1:23 PM | jokiz

ConnectionTimeout is the one specified in your connection string and it is the time it takes for a connection.Open() invocation to wait until it gets a connection reference from the connection pool. (Default value is 15 seconds) CommandTimeout on the...

Filed under: SQL, ADO.NET

what's wrong with this code series

Published 08-10-2006 11:30 AM | jokiz

scottallen has started a "what's wrong with this code" series. some items are trivial and good to think about so you might want to check them out... http://odetocode.com/Blogs/scott/archive/2006/07/31/5474.aspx http://odetocode.com/Blogs...

Filed under: .NET, SQL, trivia

connection pooling

Published 04-20-2006 9:13 PM | jokiz

i've been seeing a number of posts lately about ado.net connection pooling in msforums and i noticed that some are rather confused about how they work. i myself was also confused year ago about the concept until i read more about it and examined the ado...

Filed under: .NET, SQL

profiling ado.net queries

Published 04-05-2006 10:23 PM | jokiz

you want to know the queries being executed by your application which uses ADO.NET. sql profiler comes in. however you got agents running (whatever they are), you also browse the tables using Enterprise Manager or Visual Studio and you want them to be...

Filed under: .NET, SQL, tips

Remote SQL Server debugging

Published 03-27-2006 7:47 PM | jokiz

back in my previous company, i was not able to debug stored procedures remotely using either visual studio (i like its debugger by the way) or through Query Analyzer. back then, the KB's that i found just talks about a settings when the SQL 2000 was installed...

Filed under: SQL

@@IDENTITY vs SCOPE_IDENTITY()

Published 03-20-2006 4:26 AM | jokiz

I have read a number of books/articles and I wonder why they always use the global variable @@IDENTITY in obtaining the generated identity after an INSERT operation. They should be using SCOPE_IDENTITY() instead which is safer due to triggers. With SCOPE_IDENTITY...

Filed under: SQL, tips