Using ActiveX to set query dynamically in SQL Server
Learn how to use ActiveX to dynamically change a query. Continue reading Using ActiveX to set query dynamically in SQL Server
Common Table Expressions (CTE) in SQL Server 2005 and Up
Thanks to the team at TechRepublic.com and BOL, I have used CTEs to delete duplicates with ease in SQL Server 2005 and higher. Learn more about CTEs at TechRepublic. Here meat of the article a sample:
|
1 2 3 4 5 6 7 8 9 10 11 |
;WITH SalesCTE(Product, SaleDate, SalePrice, Ranking) AS (SELECT Product , SaleDate , SalePrice , Ranking = DENSE_RANK() OVER(PARTITION BY Product, SaleDate, SalePrice ORDER BY NEWID() ASC) FROM SalesHistory ) DELETE FROM SalesCTE WHERE Ranking > 1 |
Very easy and handy! Check out BOL for all kinds of information regarding Dense_Rank(), NEW_ID(), and PARTITION BY. While you’re there, check out RANK(), NTILE(), and ROW_NUMBER() I’m sure there is a combination that will help your current situation. Please note, this is for SQL Server 2005 and above. Continue reading Common Table Expressions (CTE) in SQL Server 2005 and Up
Using ActiveX to check the age of a file in SQL Server
Now, you can use ActiveX to check the age of a file before proceeding. The code assumes that you have already assigned the file location elsewhere in the DTS Package Continue reading Using ActiveX to check the age of a file in SQL Server
Using ActiveX to Dynamically set a Variable in an SQL Server DTS Package
From time to time, you will need to dynamically set a variable in the middle of your DTS packages. The following code shows you how easy it is to build a variable dynamically. The variable will be filename which contains … Continue reading Using ActiveX to Dynamically set a Variable in an SQL Server DTS Package
Using NOCOUNT, ANSI Warnings and Temp Tables in SQL Server
Here are a few useful code fragments that I use all the time. You see too many Rows Affected Messages. First, NOCOUNT. If you don’t want SQL Server to return the Row Count
|
1 |
(20 row(s) affected) |
after you execute a query, here is the code: — Turn off the rows affected message
|
1 |
<span style="color: #0000ff;">SET NOCOUNT ON</span> |
Once you have executed your queries, you can enable the row count again
|
1 |
<span style="color: #0000ff;">SET NOCOUNT OFF</span> |
ANSI Warnings interrupt your inserts Next, do you get annoying ANSI errors when inserting data? You can remove those warnings using one simple command.
|
1 |
<span style="color: #0000ff;">SET ANSI_WARNINGS OFF</span> |
If there is a failure, the inserts will fail. However, … Continue reading Using NOCOUNT, ANSI Warnings and Temp Tables in SQL Server
Change a Date Format Using ActiveX and SQL DTS Transformations
Let’s say you are importing data from a flat file to SQL Server via DTS. Now, let’s say the date format in the source file is similar to this: YYYYMMDD. If your destination column is a datetime or smalldatetime, SQL … Continue reading Change a Date Format Using ActiveX and SQL DTS Transformations
Adding Foreign Keys after a table is created in SQL Server
Well, you knew exactly what you needed when you created a table. Only now when you created a child table, the child table does not update with the information from your main table. Or worse you deleted something from your primary table and it still lives in the child table. You need a foreign key, my friend. This is how you define one after you’ve created your table.
|
1 2 3 4 5 |
<span style="color: #0000ff;">ALTER TABLE</span> My_Child_Table <span style="color: #0000ff;">ADD CONSTRAINT</span> FK_User_ID <span style="color: #0000ff;">FOREIGN KEY</span> ( User_ID_In_Child_Table ) <span style="color: #0000ff;">REFERENCES</span> My_Primary_Table ( User_ID_In_Primary_Table ) <span style="color: #0000ff;">ON DELETE CASCADE ON UPDATE CASCADE</span> |
The last two lines are important. In this example, if the user id in the primary table is modified or deleted, the change will cascade to the child table. This ensures … Continue reading Adding Foreign Keys after a table is created in SQL Server