|Saturday, November 22, 2003|
Hey have a look at the DBCC Commands
10:29:43 AM trackback  Articulate 
|Friday, October 31, 2003|
Want to achieve it ? (In SQL Query Analyzer)
[WebLogs @ ASP.NET]
7:14:54 AM trackback  Articulate 
|Tuesday, October 28, 2003|
New SQL CE stuff from PDC.
Version 3 of SQL Server CE will eliminate a lot of the problems that developers have neen dealing with, working with SQL Server 2. One of my greatest shocks when I first userd SQL Server CE was that if I left SQLCE Query up and connected to my database, when I ran my applicaiton that accessed it, I got a sharing exception. No more with SQL Server CE 3 (Net yet available, due for Yukon B2 timeframe).
Also, the new Workbench allows you to run SQL CE queries on your handheld from the desktop, and more important, can show the execution plan, and accept hints (implemented slightly differently than on Sql Server).
The bad news? Still no stored procedures, so it is still likely the database layer will have to be seriously refactored for Pocket PC use.[WebLogs @ ASP.NET]
5:00:39 PM trackback  Articulate 
|Saturday, October 25, 2003|
Source: WebLogs @ SqlJunkies.com
The COLLATION of a column defines (among other things) the case sensitivity of search arguments when qualifying rows to be returned from SELECT statements as defined in WHERE clauses. But another important consideration is that a column's collation can also affect the behaviour of a unique / primary key if one exists on a column with collate defined.
For example, the following script demonstrates that uniqueness of rows inserted into a key depends on the collation of the key column. Note carefully that the collation is either “_CS_” or “_CI_” - denoting case sensitive or case insensitive. Depending on how ths collation is set, the “duplicate” rows are either allowed or rejected by SQL Server.
set nocount on
Output from this script is:
Server: Msg 2627, Level 14, State 1, Line 1
The case in point here is simply that the case sensitivity in any given collation does not simply affect row qualification for select statements. It also affects the uniqueness of rows permitted by the column's key (if one exists). In short, take care to get the case sensitivity right (usually case insensitive would be expected) when setting collations on primary keys!
6:30:41 PM trackback  Articulate 
|Thursday, October 23, 2003|
Source: Geekswithblogs.net Main Feed
Simple way to Remove Duplicate Rows from an Existing Table in SQL
9:41:12 AM trackback  Articulate 
|Tuesday, October 14, 2003|
Source: Stephen Forte's WebBlog
I have gotten flack, especially from annoying Australians, about how “complex” the Rozenshtein Method is. So today I found myself writing a crosstab and used the Case Method.
It was an ad-hoc query that I have to run a few times over the next few weeks. It never has to run in another database like Access or Oracle. It was needed to be quick and dirty. I decided NOT to use the Boolean aggregates and use a Case statement. The basic structure of a case statement is as follows:
CASE FieldName WHEN ValueYouAreChecking THEN TrueExpression ELSE FalseExpression END
Pretty easy no? Here is a sample:
SELECT tlkpWeekEnding.WeekEnding_DT as Weekending, DiceCat.DiceCat_NM as 'Job Category', Sum(tblData.TotalListings) AS 'Total Listings', SUM(CASE Service_ID WHEN 1 THEN TotalListings ELSE 0 END) AS HotJobsTotal,
SUM(CASE Service_ID WHEN 2 THEN TotalListings ELSE 0 END) AS MonsterTotal,
SUM(CASE Service_ID WHEN 3 THEN TotalListings ELSE 0 END) AS CareerBuilderTotal
FROM DiceCat INNER JOIN ((tlkpWeekEnding INNER JOIN (trelServiceURL INNER JOIN tblData ON trelServiceURL.URL_ID = tblData.URL_ID) ON tlkpWeekEnding.WeekEnding_ID = tblData.WeekEnding_ID) INNER JOIN
GROUP BY tlkpWeekEnding.WeekEnding_DT, DiceCat.DiceCat_NM
Order by DiceCat.DiceCat_NM
Here are the results, we move rows to columns:
So if this is so easy why oh why do I insist on using the Rozenshtein Method? Well, I don’t insist on using it all the time. I like it because it is super fast and make sense to me (I was into Math as a kid, sorry). I think that you should know both and use the one that you think is most effective for the job at hand.
Happy Crosstabbing! [Stephen Forte's WebBlog]
7:11:53 PM trackback  Articulate 
|Sunday, October 05, 2003|
You haven’t really experienced sheer hell until you have to debug a DTS package. SQL Server development is something completely different from the traditional 4GL languages like VB and C#, you have to think in rows and columns and joins. A lot of 4GL programmers are forced or simply want to get into writing T/SQL Stored Procedures and realize that it is a whole new ballgame. But one that is easy to master if you put the time in. So a lot of beginning SQL Server programmers who came over from the 4GL side ask me often at conferences, “How do I debug a DTS package?” The answer is “You don’t.” (Oh we are all so spoiled by setting a breakpoint in Visual Studio.)
Microsoft has publicly announced some of the new DTS features of
First off, proper design of your package will only make debugging much easier. Use only Stored Procedures (with or without parameters) and if you have to use SQL dynamically utilize Views. This is because the more dependencies on “real” database objects, the easier it will be to track down your problem. Avoid ActiveX Scripts as much as you can-consider an Extended Stored Procedure that calls a DLL wrote yourself (or call the DLL with a CreateObject in your script if you must).
For the actual debugging itself my advice to you is to take everything in steps. You can run each DTS package’s step individually just by right clicking on it in the designed and selecting “Execute Step” from the pop-up menu. That is the first part. Then you can deconstruct the step manually and run those pieces in Query Analyzer. (In theory you can debug your stored procedure in Visual Studio too.) From there it gets easier, small bits and pieces of your step may or may not be working, so start looking at your select statements in QA. Before you know it, you will be in DTS debugging heck instead of hell.[Stephen Forte's WebBlog]
10:29:49 AM trackback  Articulate