You might get the following error when trying to connect to a SQL Server CE edition on a network share SQL Server Compact does not support opening database files on a network share. (System.Windows.Forms).     So much for me trying to put my SQL Server CE database on my cluster her at home.  One interesting thing…you can’t connect to it via a network share, but you can create it on a network share and connect to it via the node that you happen to be on. WTF? Below, I am on one of my cluster nodes and have a SQL Server Compact Edition database open on my network share (M:).  Let’s dork around with this some more. I’ll get back to ya. Lee   --------------------------

You might get the following whilst trying to attach a .sdf file (SQL Server Compact) in SQL Server Management Studio: Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) Additional Information: An exception occurred while executing a Transact-SQL statement or batch. ‘path’.sdf is not a primary database file. Microsoft SQL Server, Error: 5171   What has happened – you tried to attach a SQL Server Compact Edition file to SQL Server Management Studio, but in the wrong connection method. Don’t do a connect within the SQL Engine. Go to the menu and do File >>Connect Object Explorer, and then in Server Type choose SQL Server Compact. Thanks, Lee   ----------------------- SQL Server Compact Edition – oh the power at-hand!

Posted in: SQL Administration  Tags:
Lee posted on January 16, 2012 19:38
SQL Server 2012 has given us a new team and a new blog – The SQL Server AlwaysOn Team Blog – to support one of the coolest features (I believe) in SQL Server 2012. If you haven’t read about AlwaysOn, or haven’t had a chance to do the kick ass SQL Server 2012 AlwaysOn Availability Groups (SQL 142) Hands on Lab, you really need to do both because it’s going to be a really strong new addition to our product and I really like it. Check out their new blog and all of the current posts (like 16 of them so far for Jan 2012) and get started on High Availability. You might also want to read Brent Ozar’s most excellent effort on “AlwaysOn”. Thanks for reading, Lee   ----------------   http://blogs.msdn.com/b/sqlalwayson/archive/2012/01.aspx SQL Server 2012: AlwaysOn Availability Groups (SQL 142) http://www.brentozar.com/archive/2010/11/sql-server-denali-database-mirroring-rocks/                           

Posted in: SQL Administration , SQL Server 2012  Tags:
Lee posted on January 14, 2012 15:00
Installing SQL Server 2008R2, you might get the following...SQL Server starts to load, and then it craps out: Managed SQL Server Landing Page has stopped working     The problem is that – for SQL Server 2008R2 circa Jan 2012, you got to overly excited and downloaded and installed the .Net Framework 4.0. One catch though. When you go to Uninstall a Program in the Control Panel, I found that you do not need to uninstall the Microsoft .Net 4 client profile, only the Microsoft .Net 4 Extended. The consequences of leaving the client profile installed I have no idea, but I know that it works because I just installed three instances of SQL Server 2008R2 as I prepare to set up a SQL Server Mirror on my network, and had to do this uninstall three times; each time I did not uninstall the client profile.         Thanks for reading, Lee   -----------------------  

Posted in: SQL Administration , SQL Server 2008/R2  Tags:
Making changes to an existing table – in this case NULLability – with indexes on the table and you may get the following error: Msg 5074, Level 16, State 1, Line 2 The index 'idx_test' is dependent on column 'isDeleted'. Msg 4922, Level 16, State 9, Line 2 ALTER TABLE ALTER COLUMN isDeleted failed because one or more objects access this column.   In this instance, you’ll have to drop the index(es) referencing the column before you can alter the column. If you try to disable the index via ALTER INDEX indexname on table DISABLE, you’ll get the same error…no can do. Have to get rid of it first. Here’s an example: DROP TABLE idxTest GO CREATE TABLE idxTest (ID INT ,isDeleted BIT NULL ) GO INSERT idxTest VALUES (1,0) GO CREATE INDEX idx_test ON idxTest (ID, isdeleted) GO   --ALTER TABLE idxTest -- ALTER COLUMN isDeleted BIT NOT null GO   DROP INDEX idxTest.idx_test GO ALTER TABLE idxTest ALTER COLUMN isDeleted BIT NOT null GO CREATE INDEX idx_test ON idxTest (ID, isdeleted) GO   Notice that I’ve commented out the ALTER TABLE, but you can uncomment and give it a try. Thanks for reading, Lee   ----------------------    

Posted in: Beginner , SQL Administration , TSQL  Tags:
Lee posted on January 11, 2012 12:00
TFS was just installed at my company, so I wanted to go out and take a look at the TFS SQL Server database jobs for database backups, transaction log backups, and anything else I could find. So I crack open SQL Server Management Studio (SSMS) on the server and start snooping around…to find nothing. No maintenance plans, SQL Agent jobs, nothin’. I immediately thought to myself “Oh no, they’re using task scheduler to handle it”. Surely not, right? Surely yes they are. <cough hack belch>. Manage the TFS SQL Server databases through the Team Foundation Administrative Console and not through SSMS for backups. I would imagine that creating maintenance plans for indexes and statistics, or any other job that you want, would probably be Ok.       Lee   ---------------------------  

Posted in: Etc. (Off-topic) , SQL Administration  Tags:
lee posted on January 10, 2012 20:35
I’ve created little utility functions for over 10 years now and I can honestly say that I don’t have a single one saved off somewhere to look back to. Actually, since I wrote one, they’ve changed the objects, methods, properties, etc. so much that it wouldn’t matter. So I’m dorking with scripting a little bit, and I’ve always enjoyed it. Since I am doing more DBA type stuff these days, I want a .exe to script databases on a number of servers daily and save the files off. I always like to have files handy just in case. Here’s one that I wrote today; I’ll be posting a few more of these as I play around with them and make them more robust. My idea comes from the fact that when you script in SQL Server 2008 R2, you can’t save the wizard screens! And, because we have encrypted stored procedures, I have to remember which ones to exclude because if you don’t uncheck and leave those out, the script wizard bombs out.  Here’s a little code that you can try or make better. using System; using System.Collections.Generic; using System.Linq; using System.Text; using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Sdk.Sfc; using System.Collections.Specialized;   namespace Script { class GenerateSQLScript { private static ScriptingOptions Options() { ScriptingOptions options = new ScriptingOptions();   options.ScriptBatchTerminator = true; options.Triggers = true; options.PrimaryObject = true; options.IncludeHeaders = false; options.ScriptData = false; options.IncludeIfNotExists = false; options.ScriptSchema = true; options.ToFileOnly = true; options.AppendToFile = true; options.WithDependencies = false; options.ScriptDrops = false; options.FileName = @"\\" + Environment.MachineName + "\\c$\\temp\\Script_" + string.Format("{0:yyyyMMdd}", DateTime.Now) + ".txt";   return options; }   public static void Main(string[] args) { Server srv = new Server(); Database db = srv.Databases["AdventureWorksDW2008R2"]; Scripter scrp = new Scripter(srv); scrp.Options = Options();   foreach (ScriptSchemaObjectBase Type in db.StoredProcedures) if (!((StoredProcedure)Type).IsSystemObject && !((StoredProcedure)Type).IsEncrypted) scriptObject(Type, scrp);   foreach (ScriptSchemaObjectBase Type in db.UserDefinedFunctions) if (!((UserDefinedFunction)Type).IsSystemObject && !((UserDefinedFunction)Type).IsEncrypted) scriptObject(Type, scrp);   foreach (ScriptSchemaObjectBase Type in db.Tables) if (!((Table)Type).IsSystemObject) scriptObject(Type, scrp);   foreach (ScriptSchemaObjectBase Type in db.Views) if (!((View)Type).IsSystemObject) scriptObject(Type, scrp); }   static void scriptObject(ScriptSchemaObjectBase Type, Scripter scrp) { Urn[] smoObjects = new Urn[1]; smoObjects = new Urn[1]; smoObjects[0] = Type.Urn; scrp.EnumScript(smoObjects); } } } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }   Thanks for reading, Lee   ---------------------

Posted in: Etc. (Off-topic) , SQL Administration  Tags:
When upgrading a SQL Server 2008 R2 cluster to service pack 1 (SP1), you might get the following message as you’re plowing through the wizard screens: The 'WmiPrvSE.exe' process is not controlled by the update wizard. You have to manually stop this process to avoid a computer restart. You might find several threads in task manager for WmiPrvSE.exe, and trying to kill them will only find them restarting/returning back. Instead, do Start>>Run>>Services.msc, locate the Windows Management Instrumentation service, and stop the service. Then you can continue the install on n node without having to reboot that node. Restart the service and you’re good. Thanks, Lee   ----------------------

Posted in: SQL Administration  Tags:
I ran across a blog on federations with SQL Azure, and then happened on a cool video that shows how easy it is to create a federation. More importantly I guess, the video shows all of us where Microsoft is in all of this cloud stuff, and gives us a feel for the features and how far along they are with the technology.  I tried to embed the vid but it’s a Silverlight, so I skipped searching for it and just enclosed the link. It’s below. I do recommend the vid. Pretty cool stuff! Think I’ll go dork with one now. Lee   ------------------- Love this scale-out stuff, but wish all of the splitting and whatnot was automatic     http://www.microsoft.com/en-us/showcase/details.aspx?uuid=955b52c5-3784-4594-a9ff-104491ac5cc0 http://blogs.msdn.com/b/windowsazure/archive/2011/12/13/building-large-scale-elastic-database-tiers-with-sql-azure-introducing-federations.aspx

Posted in: Etc. (Off-topic) , SQL Administration  Tags:
lee posted on December 27, 2011 14:37
There’s probably a gazillion stored procedures out on the internet to grant permissions, and I needed one, so I cruised over to this site and found one. A couple of exceptions here to what I wanted to do, so I made a few changes to his base and created a new one. Here are the changes that I made: Added a cursor for ease of use, and got rid of the temp table Joined to sys.schemas to get the schema, and added the Schema, since we have so many schemas here at my shop Identified the TF and FN objects, and CASEd the appropriate permission type (EXEC or SELECT) for each Added QUOTENAME so that it would work properly for our users IF object_id('usp_sql_grant_execute_permission') IS NOT NULL DROP PROCEDURE [usp_sql_grant_execute_permission] GO   CREATE PROCEDURE [dbo].[usp_sql_grant_execute_permission] @login VARCHAR(50), @RunEXEC BIT = 1 --1 will run the statements, 0 = Prints them AS SET NOCOUNT ON   DECLARE @procname VARCHAR(150), @schema VARCHAR(10), @xtype VARCHAR (10), @sql VARCHAR(255)   -- Populate the temporary table with user defined stored procs and functions DECLARE CS CURSOR FOR SELECT s.[name], x.[name], s.xtype FROM sys.sysobjects s INNER JOIN sys.schemas x ON s.uid = x.schema_id WHERE (xtype = 'P') OR (xtype = 'FN' OR xtype = 'TF')   OPEN CS   FETCH NEXT FROM cs INTO @procname, @schema, @xtype   -- Iterate through each of the rows on the table WHILE @@fetch_status=0 BEGIN -- Prepare and execute SQL statement SET @sql = 'GRANT ' + CASE WHEN @xtype != 'TF' THEN 'EXEC ' ELSE 'SELECT ' END +'ON ' + @schema  + '.' + @procname + ' TO ' + QUOTENAME(@login) -- do we run or print them out IF @RunEXEC = 1 EXEC (@sql) ELSE PRINT @SQL FETCH NEXT FROM cs INTO @procname, @schema, @xtype END   CLOSE cs DEALLOCATE cs   GO .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }   I used to do a lot of administration, then didn’t do any for 100 years, and now I’m back doing some again. It’s fun for sure. Thanks to blog.strictly-software.com for contributing this code – we appreciate you! Lee   ----------------------------   http://blog.strictly-software.com/2010/01/grant-execute-permission-to-all-stored.html

Posted in: SQL Administration  Tags:
I noticed something today that hadn’t before…when you start SQL Server in Single User Mode using the –f switch, tempdb gets recreated back to where the model database is destined. First I run the following: You can see that I have four tempdb files on the F:\ drive. Now I start SQL Server in the minimal mode with –f: Stop and start the services and you see that tempdb goes back to its original locale: Restarting with the –f switch taken out, and tempdb is once again recreated back in the F:\ location with four tempdb files. Trying the same with the –m option (single-user) and you don’t find the same behavior. The tempdb database does not get moved back to the default location with the original default configuration; all of the files stay in the same location. Read more on starting SQL Server at the following links: http://msdn.microsoft.com/en-us/library/ms188236.aspx http://msdn.microsoft.com/en-us/library/ms180965.aspx http://msdn.microsoft.com/en-us/library/ms191193.aspx Thanks for reading, Lee   ------------------------

Posted in: SQL Administration  Tags:
Logging in to SQL Server after starting in single-user mode using –m, you might get the following: Cannot connect to.. Additional information: Login failed for user ‘DOMAIN\user’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461) Workaround- try to stop the Reporting Services Service and/or the SQL Agent Service if running. This will ensure that only one connection is made by an administrator. Thanks, Lee   -----------------------------

Posted in: SQL Administration  Tags:
You might get the following error in SQL Server Management Studio version 2008 R2: An error occurred while executing batch. Error message is: Error processing execution plan results. The error message is:The type initializer for 'Microsoft.SqlServer.Management.SqlMgmt.ShowPlan.XmlPlanNodeBuilder' threw an exception.There is not enough space on the disk. I believe that the error was caused in my situation by RedGate’s Data Compare/Deploy feature and running out of space. I got the error while trying to do an execution plan, but had previously gotten a similar error when I did a large data compare; I noticed that RedGate creates a temp file on c:\ and that’s not a good place if you’re doing a large compare.  After I cancelled out and had been working for a bit, I tried a Display Execution Plan and got the error again.  Close out of SSMS and try again to clear up the error. Thanks, Lee   -------------------------

Posted in: SQL Administration  Tags:
I was scripting out some stored procedures to the clipboard and new window (and then every other way) and the damn thing stopped 1/2 way through. No error, message, warning, flag, flashing yellow light, nothing. WTF? I ran the thing a few times and then thought, Ok, let’s hit the Save Report button, so I did. What ensued – some Einstein encrypted a few stored procedures, and the first one that it hit, it died. Now, I don’t think it’s necessary for the most part to encrypt stored procedures. If you’re a third-party with some app, maybe. Otherwise, not so much.  Beside the point. No, my complaint here is that I don’t get an error telling me what the hell it was that caused the generate scripts to die. Nice. Here’s what I get with the save report: Preparing dbo.myProcedure Failed Microsoft.SqlServer.Management.Smo.PropertyCannotBeRetrievedException: Property TextHeader is not available for StoredProcedure '[dbo].[myProcedure]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)   So, the next time you run into somebody encrypting stored procedures in your database, tell them to go ahead and stop anytime they like because they’re creating a mess. Thanks for reading! Lee   ---------------------------

Posted in: SQL Administration  Tags:
I was geeking last weekend, no wait, actually three weeks ago, and then last night and tonight…it doesn’t really matter, with CASCADE DELETES in SQL Server 2008 R2, and was curious about whether or not I could trace Cascade Referential Integrity in SQL Server Profiler.  Do you use Cascade Deletes in your foreign keys? Me neither that much, but I find them sort of fascinating.  I should run a test comparing them to coded deletes – I bet they would leave delete statements in the dust. Anytime that SQL Server can do something natively, I’m putting my money on the engine that it can process faster whatever you or I might be trying to do in code. Create the tables to set up our little experimentation: /****** Object: Table [dbo].[tbl_1] Script Date: 11/15/2011 20:57:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_1]( [Id1] [int] NOT NULL, [Value1] [varchar](20) NULL, CONSTRAINT [PK_tbl_1] PRIMARY KEY CLUSTERED ( [Id1] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[tbl_2] Script Date: 11/15/2011 20:57:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_2]( [Id2] [int] NOT NULL, [Id1] [int] NULL, [Value2] [varchar](20) NULL, CONSTRAINT [PK_tbl_2] PRIMARY KEY CLUSTERED ( [Id2] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[tbl_3] Script Date: 11/15/2011 20:57:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_3]( [Id3] [int] NOT NULL, [Id2] [int] NULL, [Value3] [varchar](20) NULL, CONSTRAINT [PK_tbl_3] PRIMARY KEY CLUSTERED ( [Id3] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: Table [dbo].[tbl_4] Script Date: 11/15/2011 20:57:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[tbl_4]( [Id4] [int] NOT NULL, [Id3] [int] NULL, [Id2] [int] NULL, [Value4] [varchar](20) NULL, CONSTRAINT [PK_tbl_4] PRIMARY KEY CLUSTERED ( [Id4] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO /****** Object: ForeignKey [FK_tbl_2_tbl_1] Script Date: 11/15/2011 20:57:06 ******/ ALTER TABLE [dbo].[tbl_2] WITH CHECK ADD CONSTRAINT [FK_tbl_2_tbl_1] FOREIGN KEY([Id1]) REFERENCES [dbo].[tbl_1] ([Id1]) ON DELETE CASCADE GO ALTER TABLE [dbo].[tbl_2] CHECK CONSTRAINT [FK_tbl_2_tbl_1] GO /****** Object: ForeignKey [FK_tbl_3_tbl_2] Script Date: 11/15/2011 20:57:06 ******/ ALTER TABLE [dbo].[tbl_3] WITH CHECK ADD CONSTRAINT [FK_tbl_3_tbl_2] FOREIGN KEY([Id2]) REFERENCES [dbo].[tbl_2] ([Id2]) ON DELETE CASCADE GO ALTER TABLE [dbo].[tbl_3] CHECK CONSTRAINT [FK_tbl_3_tbl_2] GO /****** Object: ForeignKey [FK_tbl_4_tbl_3] Script Date: 11/15/2011 20:57:06 ******/ ALTER TABLE [dbo].[tbl_4] WITH CHECK ADD CONSTRAINT [FK_tbl_4_tbl_3] FOREIGN KEY([Id3]) REFERENCES [dbo].[tbl_3] ([Id3]) ON DELETE CASCADE GO ALTER TABLE [dbo].[tbl_4] CHECK CONSTRAINT [FK_tbl_4_tbl_3] GO .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } I’ve created four tables, all with a PK and an FK that include DELETE CASCADE; so what’s happening here is that when I delete a record in tbl_1, the foreign keys through to tbl_4 will delete the associated foreign key records. Rather than write some trigger or delete the associated rows in some other fashion, I’m letting SQL Server do it. Compile this procedure (it’s complicated I know right?) and then take a look at the execution plan. If you forgot, just highlight the stored procedure name in a management studio query window and type control + L: DROP PROCEDURE proc_cascadetest GO CREATE PROCEDURE proc_cascadetest AS DELETE tbl_1 WHERE Id1=1 GO Notice that the execution plan shows the CASCADE DELETE action for the stored procedure; each step for the foreign key tables is clearly found in the plan. This sort of coincides with what Microsoft said awhile back in the following post on Microsoft Support. You cannot trace cascading deletes and updates in the context of regular events such asTSQL:StmtCompleted and TSQL:BatchCompleted. However, when you execute a statement that performs cascading updates or deletes in SQL Server Profiler, you can trace these cascade actions in the execution plan. So, these in fact cannot be traced in SQL Profiler. Then, I remember the hundreds or maybe thousands of blogs by Jonathan Keyhaias on the internet about Extended Events in SQL Server 2008 R2. I think this dude maybe invented them and put them into the SQL Engine or something, I’m not quite sure. Since these are supposed to be the all-powerful, all-knowing for internals or so I’m told or led to believe, I thought I’d give them a shot and see if they could serve up the SQL text since profiler can’t. Let’s give it a go. Run the following: First get the data into our tables…all that I’m doing here is creating the FK equal to the PK + 1, with the FK inserted alongside. Run the script and then go look at the data:  DECLARE @t1_insert int ,@t2_insert int ,@t3_insert int ,@t4_insert intSET @t1_insert=1 SET @t2_insert = @t1_insert+1 SET @t3_insert = @t2_insert+1 SET @t4_insert = @t3_insert+1 WHILE (@t1_insert < 5) BEGIN INSERT tbl_1 VALUES (@t1_insert, 'tbl1 val') INSERT tbl_2 VALUES (@t2_insert, @t1_insert, 'tbl2 val') INSERT tbl_3 VALUES (@t3_insert, @t2_insert, 'tbl3 val') INSERT tbl_4 VALUES (@t4_insert,@t3_insert, @t2_insert, 'tbl4 val') SET @t1_insert+=1 SET @t2_insert+=1 SET @t3_insert+=1 SET @t4_insert+=1 ENDGO .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Run this code now to start up extended events session. Make sure and change the sqlserver.database_id for your particular database ID. And, don’t run the script below all at once – maybe just the first part to create the session, then run the stored procedure, and then the part to kill the session: -- Create the event session and add some events DROP EVENT SESSION track_sprocs ON SERVER GO CREATE EVENT SESSION track_sprocs ON SERVER ADD EVENT sqlserver.module_end (ACTION (sqlserver.tsql_stack, sqlserver.sql_text) WHERE sqlserver.database_id=6), ADD EVENT sqlserver.sql_statement_starting (ACTION (sqlserver.tsql_stack, sqlserver.sql_text) WHERE sqlserver.database_id=6), ADD EVENT sqlserver.sql_statement_completed (ACTION (sqlserver.tsql_stack, sqlserver.sql_text) WHERE sqlserver.database_id=6) ADD TARGET package0.ring_buffer WITH (MAX_DISPATCH_LATENCY = 1 SECONDS) GO -- Start the session ALTER EVENT SESSION track_sprocs ON SERVER STATE = START GO -- Add another event ALTER EVENT SESSION track_sprocs ON SERVER ADD EVENT sqlserver.lock_released -- Run the test procedure EXEC proc_cascadetest GO -- Drop the events now ALTER EVENT SESSION track_sprocs ON SERVER DROP EVENT sqlserver.module_end, DROP EVENT sqlserver.sql_statement_completed, DROP EVENT sqlserver.lock_released, DROP EVENT sqlserver.sql_statement_starting GODROP EVENT SESSION track_sprocs ON SERVER GO .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Now run either or both of the following: I don’t see the code that shows me that the delete has happened in the CASCADE action that I coded. WTF? SELECT top 1000 event_xml.value('(./@name)', 'varchar(100)') as [event_name], event_xml.value('(./data[@name="object_name"]/value)[1]', 'varchar(255)') as [object_name], CAST(event_xml.value('(./action[@name="tsql_stack"]/value)[1]','varchar(MAX)') as XML) as [stack_xml], event_xml.value('(./action[@name="sql_text"]/value)[1]', 'varchar(max)') as [sql_text] FROM #xml_event_data CROSS APPLY xml_data.nodes('//event') n (event_xml) .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } SELECT TOP 20 SUBSTRING(st.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text,* FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st ORDER BY Creation_Time DESC .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Both images above clearly show that the code that does the RI isn’t in the session that I ran. My conclusion – none. I tried adding several events in my Extended Event session (is this the same as what people are calling “xevents”?) and couldn’t get anything to pop out. Anyone have the phone number to SQLSkills.com? Thanks for reading, Lee Everest   ------------------------- I laid an egg on this one – you try it and give me a shout if you figure it out. I’m gonna go drink a beer. Bye.

Posted in: SQL Administration , SQL Server 2008/R2 , TSQL  Tags:
I’m reposting a social.msdn.com question that I found…all of the developers that I work with are changing their colors in VS 2010 and (trying to) in Management Studio, going with the old mainframe-ish black background and mostly yellow or white fonts. Pretty cool these app-devs are. So one of my colleagues asked me about saving changes in SSMS, and I told him that I don’t dork much with the colors, but a quick search I found a nice post here.  Simply follow what “Scott M” says and you’ll be good to go: Figured this one out.  Once you set your colors to your liking in S.S.M.S you can back them up via the following registry key: HKEY_CURRENT_USER\Software\Microsoft\Microsoft SQL Server\100\Tools\Shell\FontAndColors Marked As Answer byscott_mMonday, November 15, 2010 7:39 PM Edited byscott_mMonday, November 15, 2010 8:23 PMtypo Find the registry key above, hit export, and this will include all of the registry keys below the folder. Move the file to wherever your destination is, double-click on it, and it will add it to the registry of the new computer. Your new settings will be transferred to the SQL Server Management Studio. Here’s mine.  …Mmmmmmmmmmm, no. I’m going back to the old.   Thanks for reading, Lee   ----------------------       http://social.msdn.microsoft.com/Forums/en/sqltools/thread/00552b92-3d47-477e-aafc-fb2480519fb3

Posted in: SQL Administration , Etc. (Off-topic)  Tags:
My good friend Lori Brown just had an article in this month’s SQL Server Magazine (November 2011), so go check it out at the link and read up on this great technique of capturing actions on a SQL Server via SQL Profiler, and then replaying those actions on another machine. It’s an OLTP thing, mostly, but can be used in the data warehouse as well. The concept is, on a high level, that you back up your database, take a trace of an operation/set of operations, restore your database, and run them again via the SQL Trace facility. It’s a cool way to check your environment when major changes have taken place: a new version of SQL Server install, a new server, new operating system…this may even be a great way to load test a virtual machine to see how it performs to the old box. I actually just thought of this as I’m typing, and it’s a great idea since people are so paranoid about how “performant” these Hyper-V boxes are – this would be a great way of testing a move to VMs! By the way, the graph that Lori gave to SQL Server Mag (below)…this picture, right…it’s mine and I drew it. Where’s my credit, Lori?  Just kidding.   This sort of test can also be done – maybe even a bit easier than the native way described – by using the Scalability Experts SQL Server Upgrade Assistant. Not to be confused with the SQL Server Upgrade Advisor, this tool is a download for doing basically the same thing, and the best part is that it automates a goodly portion of the tasks in the above picture. My picture!!! Check out Lori’s great article, and try this technique sometime. It works very well. It’s not easy and it takes some time, but it’s worth it, especially with SQL 2012 coming out next March. Thanks for reading, Lee   ------------------------   http://www.sqlmag.com/article/performancetuning/sql-server-trace-replay-tool-139800 http://www.scalabilityexperts.com/tools/downloads.html http://www.windowsitpro.com/content/content/139800/Brown-SQL2144-Fig1-lg.jpg

Posted in: SQL Administration , SQL Server 2008/R2  Tags:

by Lee Everest, M.S.

Poll

Do you use Azure or cloud in your organization?
Show Results

Ads

Search


Month List

Calendar

«  February 2012  »
MoTuWeThFrSaSu
303112345
6789101112
13141516171819
20212223242526
2728291234
567891011
View posts in large calendar

Tags

Disclaimer
The opinions, code, examples, et.al. expressed herein are my own personal opinions and do not represent my employer's view in any way, shape form, or fashion.  All code for demonstration purposes - no guarantees, either written or implied, are made.

© Copyright 2012 Lee Everest's SQL Server, etc. weblog