After a windows update, my DVD drive suddenly went missing from explorer.
Device Manager shows a driver problem, but windows said the driver was fine. Uninstall and reinstall had no effect. Reboot had no effect. I reseated the drive several times with no effect.
I downloaded the Optical Disk drive firmware update from IMB's site, but it couldn't even see the drive.
Then I came across this KB
http://support.microsoft.com/gp/cd_dvd_drive_problems
Apparently, the UpperFilters and LowerFilters registry values can sometimes get screwed up when installing updates. The program at the link above fixed the problem right away.
Did this help you? If so, please leave a comment!
David Jedziniak maintains this blog and posts helpful tips and tricks on SQL Server and Dynamics GP development. The opinions expressed here are those of the author and do not represent the views, express or implied, of any past or present employer. The information here is provided without warranty of fitness for any particular purpose.
Thursday, September 30, 2010
The new face of SSRS
The report builder in Visual Studio got a face lift in 2008. I haven't run into anything significant that I can do in 2008 that I couldn't do in 2005, so I must assume this is yet another attempt to drive coders mad.
The first change I noticed was that the data tab was gone from the report. Yes, they included a menu item called Report Data waaaaaay down at the bottom of the view menu in an entirely successful attempt to hide it. Luckily, the GUI designers are powerless to totally foil our productivity, because the guys who actually code this stuff could care less about the interface. Enter the control key! I soon discovered that CTRL + ALT + D pops that sucker right up. It is now a dockable window with all the data objects in it and the ability to drag and drop to the report. You have to create a datasource over here before you can create the datasets (what is the shared datasources in the solution explorer for again?) but once you figure out the redundancies, it is pretty smooth sailing.
You may ask yourself, "Self...why didn't they just add the functionality to the server explorer?" Well, that would be too logicial. However, if you answered yourself you might want to take a crack at GUI design :^))
Did this help you? If so, please leave a comment!
The first change I noticed was that the data tab was gone from the report. Yes, they included a menu item called Report Data waaaaaay down at the bottom of the view menu in an entirely successful attempt to hide it. Luckily, the GUI designers are powerless to totally foil our productivity, because the guys who actually code this stuff could care less about the interface. Enter the control key! I soon discovered that CTRL + ALT + D pops that sucker right up. It is now a dockable window with all the data objects in it and the ability to drag and drop to the report. You have to create a datasource over here before you can create the datasets (what is the shared datasources in the solution explorer for again?) but once you figure out the redundancies, it is pretty smooth sailing.
You may ask yourself, "Self...why didn't they just add the functionality to the server explorer?" Well, that would be too logicial. However, if you answered yourself you might want to take a crack at GUI design :^))
Did this help you? If so, please leave a comment!
Tuesday, September 14, 2010
SQL vs. CLR vs. Standalone
I recently had some friends who are just starting out in the world of development ask a common question. "Can I do a while loop in SQL?" This, of course, sparked a lengthy conversation about the dangers of cursors, merits of CLR procs, and logic flow in general. When thinking about it later, I realized that I get this question in one form or another from time to time and it might be helpful to have an example to point them to.
So here it is. Examples of how to perform the same actions in:
SQL Procs
vs
CLR Procs (C#)
vs
Windows App (C# and VB)
Eventually I will add LINQ examples for windows apps.
Code samples are color coded for easy identification.
I decided to give examples of how to perform the following actions:
1. Mass Delete of all records in two tables
2. Mass Insert of records from one table to another
3. Mass update of all records in one table
4. Mass update of the records in one table by criteria
5. Looping through all records in one table and for each record, performing both an insert on a second table and update on the current table
So first we need to create these tables we will play with. Run the following in SQL Management Studio.
1. Mass Delete of all records in two tables
2. Mass Insert of records from one table to another
3. Mass Update of all records in one table
4. Mass Update of the records in one table by criteria
5. Looping through all records in one table and for each record, performing both an insert on a second table and update on the current table
One thing you will notice is that the SQL code is always smallest. But that doesn't mean it is necessarily the best way to do something.
So here it is. Examples of how to perform the same actions in:
SQL Procs
vs
CLR Procs (C#)
vs
Windows App (C# and VB)
Eventually I will add LINQ examples for windows apps.
Code samples are color coded for easy identification.
I decided to give examples of how to perform the following actions:
1. Mass Delete of all records in two tables
2. Mass Insert of records from one table to another
3. Mass update of all records in one table
4. Mass update of the records in one table by criteria
5. Looping through all records in one table and for each record, performing both an insert on a second table and update on the current table
So first we need to create these tables we will play with. Run the following in SQL Management Studio.
USE [master]OK, so lets get started. You can run these objects after creation and see that the results are the same. Before running CLR Procs, you must install the dll. Details on that and on building windows applications are outside the scope of this post. I am strictly concentrating on what the code looks like in the various technologies.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test_table1]') AND type in (N'U'))
DROP TABLE [dbo].[test_table1]
GO
CREATE TABLE [dbo].[test_table1] (
id int,
description varchar(10),
status varchar(10)
)
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test_table2]') AND type in (N'U'))
DROP TABLE [dbo].[test_table2]
GO
CREATE TABLE [dbo].[test_table2] (
id int,
description varchar(10),
status varchar(10)
)
1. Mass Delete of all records in two tables
SQL Proc: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassDelete]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_MassDelete] GO create procedure SP_MassDelete AS BEGIN delete from test_table1 delete from test_table2 END GO | CLR Proc: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CLR_MassDelete() { SqlConnection conn = new SqlConnection(); try { //we use the existing connection inside SQL conn.ConnectionString = "Context Connection=true"; //this is the command for deleting the records SqlCommand cmd = new SqlCommand("delete from test_table1", conn); //we must issue an open to the connection even though in reality it is already an active connection conn.Open(); //now we run the statements cmd.ExecuteNonQuery(); cmd.CommandText = "delete from test_table2"; cmd.ExecuteNonQuery(); } catch (Exception ex) { SqlContext.Pipe.Send("Error in CLR proc CLR_MassDelete: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } }; |
C# function: SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;"); private void btnMassDelete_Click(object sender, EventArgs e) { try { //this is the command for deleting the records SqlCommand cmd = new SqlCommand("delete from test_table1", conn); conn.Open(); //now we run the statements cmd.ExecuteNonQuery(); cmd.CommandText = "delete from test_table2"; cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Error in btnMassDelete_Click: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } | VB function: Private conn As SqlConnection = New SqlConnection("Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;") Private Sub btnMassDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassDelete.Click Try 'this is the command for deleting the records Dim cmd As SqlCommand = New SqlCommand("delete from test_table1", conn) conn.Open() 'now we run the statements cmd.ExecuteNonQuery() cmd.CommandText = "delete from test_table2" cmd.ExecuteNonQuery() Catch ex As Exception MessageBox.Show("Error in btnMassDelete_Click: " + ex.ToString()) Finally 'remember to close the connection regardless of errors conn.Close() End Try End Sub |
SQL Proc: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassInsert]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_MassInsert] GO create procedure SP_MassInsert AS BEGIN insert into test_table1 select object_id,substring(name,1,10),'Inserted' from sys.objects END | CLR Proc: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CLR_MassInsert() { SqlConnection conn = new SqlConnection(); try { //we use the existing connection inside SQL conn.ConnectionString = "Context Connection=true"; //this is the command for inserting the records SqlCommand cmd = new SqlCommand("insert into test_table1 select object_id,substring(name,1,10),'Inserted' from sys.objects", conn); //we must issue an open to the connection even though in reality it is already an active connection conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { SqlContext.Pipe.Send("Error in CLR proc CLR_MassInsert: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } }; |
C# function: private void btnMassInsert_Click(object sender, EventArgs e) { try { //this is the command for inserting the records SqlCommand cmd = new SqlCommand("insert into test_table1 select object_id,substring(name,1,10),'Inserted' from sys.objects", conn); conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Error in btnMassInsert_Click: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } | VB function: Private Sub btnMassInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassInsert.Click Try 'this is the command for inserting the records Dim cmd As SqlCommand = New SqlCommand("insert into test_table1 select object_id,substring(name,1,10),'Inserted' from sys.objects", conn) conn.Open() 'now we run the command cmd.ExecuteNonQuery() Catch ex As Exception MessageBox.Show("Error in btnMassInsert_Click: " + ex.ToString()) Finally 'remember to close the connection regardless of errors conn.Close() End Try End Sub |
SQL Proc: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassUpdate]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_MassUpdate] GO create procedure SP_MassUpdate AS BEGIN update test_table1 set status='Updated' END GO | CLR Proc: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CLR_MassUpdate() { SqlConnection conn = new SqlConnection(); try { //we use the existing connection inside SQL conn.ConnectionString = "Context Connection=true"; //this is the command for updating the records SqlCommand cmd = new SqlCommand("update test_table1 set status='Updated'", conn); //we must issue an open to the connection even though in reality it is already an active connection conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { SqlContext.Pipe.Send("Error in CLR proc CLR_MassUpdate: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } }; |
C# function: private void btnMassUpdate_Click(object sender, EventArgs e) { try { //this is the command for updating the records SqlCommand cmd = new SqlCommand("update test_table1 set status='Updated'", conn); conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Error in btnMassUpdate_Click: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } | VB function: Private Sub btnMassUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassUpdate.Click Try 'this is the command for updating the records Dim cmd As SqlCommand = New SqlCommand("update test_table1 set status='Updated'", conn) conn.Open() 'now we run the command cmd.ExecuteNonQuery() Catch ex As Exception MessageBox.Show("Error in btnMassUpdate_Click: " + ex.ToString()) Finally 'remember to close the connection regardless of errors conn.Close() End Try End Sub |
SQL Proc: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassUpdatebyCriteria]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_MassUpdatebyCriteria] GO create procedure SP_MassUpdatebyCriteria AS BEGIN update test_table1 set status='Big' where id>100 END GO | CLR Proc: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CLR_MassUpdatebyCriteria() { SqlConnection conn = new SqlConnection(); try { //we use the existing connection inside SQL conn.ConnectionString = "Context Connection=true"; //this is the command for updating the records SqlCommand cmd = new SqlCommand("update test_table1 set status='Big' where id>100", conn); //we must issue an open to the connection even though in reality it is already an active connection conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { SqlContext.Pipe.Send("Error in CLR proc CLR_MassUpdatebyCriteria: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } }; |
C# function: private void btnMassUpdatebyCriteria_Click(object sender, EventArgs e) { try { //this is the command for updating the records SqlCommand cmd = new SqlCommand("update test_table1 set status='Big' where id>100", conn); conn.Open(); //now we run the command cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show("Error in btnMassUpdatebyCriteria_Click: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } | VB function: Private Sub btnMassUpdatebyCriteria_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassUpdatebyCriteria.Click Try 'this is the command for updating the records Dim cmd As SqlCommand = New SqlCommand("update test_table1 set status='Big' where id>100", conn) conn.Open() 'now we run the command cmd.ExecuteNonQuery() Catch ex As Exception MessageBox.Show("Error in btnMassUpdatebyCriteria_Click: " + ex.ToString()) Finally 'remember to close the connection regardless of errors conn.Close() End Try End Sub |
SQL Proc: IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_Loop]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[SP_Loop] GO create procedure SP_Loop AS BEGIN declare @id int, @desc varchar(10), @status varchar(10) declare c_loop cursor for select id,description,status from test_table1 where status='Big' for update of status open c_loop fetch next from c_loop into @id,@desc,@status while @@fetch_status=0 BEGIN insert into test_table2 select (@id/2),@desc,convert(varchar(10),@id) update test_table1 set status='Archived' where current of c_loop fetch next from c_loop into @id,@desc,@status END close c_loop deallocate c_loop END GO | CLR Proc: using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void CLR_Loop() { SqlConnection conn = new SqlConnection(); try { //we use the existing connection inside SQL conn.ConnectionString = "Context Connection=true"; //this is the command for getting the records and inserting and updating SqlCommand cmd = new SqlCommand("select id,description,status from test_table1 where status='Big'", conn); //we must issue an open to the connection even though in reality it is already an active connection conn.Open(); //we can't use a data reader, since we can only have one command active on the connection at a time //so we will use a data adapter to get the records and store them in a datatable in memory so we can work with them SqlDataAdapter sda = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); sda.Fill(dt); //now we will loop through each record and work on it foreach (DataRow dr in dt.Rows) { //first we will insert a new records elsewhere cmd.CommandText = "insert into test_table2 select (" + dr["id"].ToString() + "/2),'" + dr["description"].ToString() + "',convert(varchar(10),'" + dr["id"].ToString() + "')"; cmd.ExecuteNonQuery(); //then we will update the existing record cmd.CommandText = "update test_table1 set status='Archived' where id=" + dr["id"].ToString(); cmd.ExecuteNonQuery(); } } catch (Exception ex) { SqlContext.Pipe.Send("Error in CLR proc CLR_Loop: " + ex.ToString()); } finally { //remember to close the connection regardless of errors conn.Close(); } } }; |
C# function: private void btnLoop_Click(object sender, EventArgs e) { SqlDataReader rdr = null; SqlConnection conn2=new SqlConnection(conn.ConnectionString); try { //this is the command for getting the records SqlCommand cmd = new SqlCommand("select id,description,status from test_table1 where status='Big'", conn); //this is the command for inserting and updating SqlCommand cmd2 = new SqlCommand("",conn2 ); conn.Open(); conn2.Open(); rdr = cmd.ExecuteReader(); while (rdr.Read()) { //first we will insert a new records elsewhere cmd2.CommandText = "insert into test_table2 select (" + rdr.GetInt32(0).ToString() + "/2),'" + rdr.GetString(1) + "',convert(varchar(10),'" + rdr.GetInt32(0).ToString() + "')"; cmd2.ExecuteNonQuery(); //then we will update the existing record cmd2.CommandText = "update test_table1 set status='Archived' where id=" + rdr.GetInt32(0).ToString(); cmd2.ExecuteNonQuery(); } } catch (Exception ex) { MessageBox.Show("Error in btnLoop_Click: " + ex.ToString()); } finally { //remember to close the reader and connection regardless of errors if (rdr != null) { rdr.Close(); } conn.Close(); conn2.Close(); } } | VB function: Private Sub btnLoop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoop.Click Dim rdr As SqlDataReader = Nothing Dim conn2 As SqlConnection = New SqlConnection(conn.ConnectionString) Try 'this is the command for getting the records Dim cmd As SqlCommand = New SqlCommand("select id,description,status from test_table1 where status='Big'", conn) 'this is the command for inserting and updating Dim cmd2 As SqlCommand = New SqlCommand("", conn2) conn.Open() conn2.Open() rdr = cmd.ExecuteReader() Do While rdr.Read() 'first we will insert a new records elsewhere cmd2.CommandText = "insert into test_table2 select (" + rdr.GetInt32(0).ToString() + "/2),'" + rdr.GetString(1) + "',convert(varchar(10),'" + rdr.GetInt32(0).ToString() + "')" cmd2.ExecuteNonQuery() 'then we will update the existing record cmd2.CommandText = "update test_table1 set status='Archived' where id=" + rdr.GetInt32(0).ToString() cmd2.ExecuteNonQuery() Loop Catch ex As Exception MessageBox.Show("Error in btnLoop_Click: " + ex.ToString()) Finally 'remember to close the reader and connection regardless of errors If Not rdr Is Nothing Then rdr.Close() End If conn.Close() conn2.Close() End Try End Sub |
One thing you will notice is that the SQL code is always smallest. But that doesn't mean it is necessarily the best way to do something.
Enjoy!
Did this help you? If so, please leave a comment!
Did this help you? If so, please leave a comment!
Subscribe to:
Posts (Atom)
SQL 2022 TSQL snapshot backups!
SQL 2022 now actually supports snapshot backups! More specifically, T-SQL snapshot backups. Of course this is hardware-dependent. Here ...
-
If you are reading this, you are probably aware of the fact that you cannot deploy SSRS 2008 reports on an SSRS2005 server. SSRS reports ar...
-
SQL Job to refresh TEST from PRODUCTION Last Updated: 2018.11.12 I like to include each of these steps as a separate job step. If you ...
-
I ran into an issue today where I had a report parameter default that I couldn't seem to get rid of. In BIDS, I deleted the defaults f...