Sql samples, sql tips

Saturday, April 13, 2013

[SQL Server] Using REPLACE in an UPDATE statement

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

sqlteam.com

[SQL Server] Handling SQL Server Errors

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

sqlteam.com

[MS SQL Server] 2008 R2 : Cannot connect after upgrade to SP1

2008 R2 : Cannot connect after upgrade to SP1
Running SQL Profiler Trace on a specific table
restoring status
procedure cache hit ratio too high?

2008 R2 : Cannot connect after upgrade to SP1

Posted: 12 Apr 2013 11:27 PM PDT

Here it is. In the last month I got 2 hard drive failure, and did have to reinstall SQL server 2008 R2. The 2 first time, no problem everything went smooth.After last hard drive failure, I had reinstalled Windows 7 (x64) and SQL server 2008 R2 ... works fine. No problem to connect to the server using Windows authentication. I install SP1 for 2008 r2 and since then I can't anymore connect to the server (on my local machine).Got error message as below.I do notice that the SQL server service is stopped. If I restart it, try to connect, it will automatically stop.Any hints on how to get that situation resolved ...Thankspascal.TITLE: Connect to Server------------------------------Cannot connect to <Machine>\<Instance>.------------------------------ADDITIONAL INFORMATION:A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10061&LinkId=20476------------------------------BUTTONS:OK------------------------------

Running SQL Profiler Trace on a specific table

Posted: 26 Mar 2013 04:18 AM PDT

Is there a way to run a SQL Trace on a specific table? I've got a table in the production environment, which keeps changing values in certain column, and I'm trying to find what is causing the change.

restoring status

Posted: 12 Apr 2013 08:56 AM PDT

I have restored a database from a backup, and the restore is OK.I went into it checking security is Ok, tables are OK,Then after a few hours I logged in again, and see the database is at restoring status.Why is that?I have no jobs schedules to do that, and I am not aware of anyone else logging to this new server.It happened once, I thought I missed something, I restored again, it is fine, I can open tables, edit, and then after a few hours, I login and see it is at restoring status again...

procedure cache hit ratio too high?

Posted: 20 Jan 2012 04:09 PM PST

Hi,Test Server always using procedure cache hit ratio 90% to 95%, Total server Memory 3 GBMax memory setting – 2048Buffer cache Hit – 100 %Page Life Expectancy – 64,973 secondsDBCC PROCCACHEnum proc buffs - 3973num proc buffs used - 65num proc buffs active - 65proc cache size - 559proc cache used - 13proc cache active – 13how to resolve this issues, how to increase procedure cache size?thanksananda

[SQL Server] Efficiently Reuse Gaps in an Identity Column

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

sqlteam.com

[SQL 2012] Has anyone implemented SQL Server 2012 Always On for disaster recovery

Has anyone implemented SQL Server 2012 Always On for disaster recovery

Posted: 12 Apr 2013 11:31 AM PDT

Am helping a client upgrade his servers (currently SS 2005 with Windows 2003 cluster). Talked to Dell sales rep this afternoon and he had not heard of anyone implementing Always On for DR. He said all his sales for SS2012 have been for active/passive cluster. I've attended several SQL Saturday sessions and webinars on Always On, but now are worried that nobody has really implemented it in production. I don't want the client to be on the technology tip by himself, but would like to take advantage of Always On if it really works.I would appreciate it if anybody could tell me if they have implemented Always On and what pros and cons they have found.Thanks in advance,Mike Byrd

[T-SQL] Odd behaviour from sys.dependencies

Odd behaviour from sys.dependencies
Help comparing a data set within a table.
something like PIVOT?
Trigger that collects data from two tables
Any ideas on how to accomplish this one?

Odd behaviour from sys.dependencies

Posted: 03 Apr 2013 08:46 PM PDT

(SQL Server 2008 R2 SP1)Hi all, I have an SSIS package that dynamically builds a set of SQL statements based on the dependencies in a set of procs. The SSIS package makes use of the dm function [url=http://msdn.microsoft.com/en-us/library/bb677185(v=sql.105).aspx]sys.dm_sql_referenced_entities[/url] to build up a distinct list of columns required from the source system. The code is run against every proc in the database into a working table, then a distinct list is used to pick up data from the source system.We've been testing this for weeks and weeks in UAT, the day we're going live, UAT has fallen over with a really odd issue :crazy:. Basically, very randomly, the values in the ImportColumn are presented with some characters returned with unexpected characters, only ever the first 4 digits, it can be different rows from the resultset and just to make it more complicated how often it returns is completely random. As the SSIS package iterates through each table, I've lifted one of the scripts it builds to explain, this is extracting dependencies from a proc called staging.uspInstrumentHolding.[code="sql"]SELECT DISTINCT Ref.referenced_database_name AS ImportDatabase ,Ref.referenced_schema_name AS ImportViewSchemaName ,SUBSTRING(Ref.referenced_entity_name, (CHARINDEX('_', Ref.referenced_entity_name) + 1), LEN(Ref.referenced_entity_name)) AS TableName ,SUBSTRING(Ref.referenced_entity_name, 1, (CHARINDEX('_', Ref.referenced_entity_name, 1) - 1)) AS ConnectionName ,Ref.referenced_minor_name AS ImportColumnFROM sys.dm_sql_referenced_entities('Staging' + '.' + 'uspInstrumentHolding', 'OBJECT') AS RefWHERE 1 = 1 AND Ref.referenced_minor_name IS NOT NULL AND ref.referenced_schema_name = 'import'[/code]The values that return in a strange state are only from the ImportColumn. I can run the script 7-8 times accurately, then the results will appear like this:[code="plain"]UNPOSTEDꀨ⃗가⃗RLꀨ⃗가⃗ANPCCVRT䀨噠RE䀨噠RT䀨噠EF䀨啨UV䀨啨RT䀨䒰TION䀨䒰VT䀨䒰EF䀨䒰LT䀨䎰RL䀨䎰CTION䒰忰AL䀨亰UP䶸忰Fꀨ⃗ꗐ⃗TEꀨ⃗ꗐ⃗Tꓘ⃗뿰⃗TL[/code]Next time I run them, they look like this:[code="plain"]UNPOSTEDUNSERLUNTRANPCCVRTPCDIREPCINRTPCIREFPCORUVPCSRRTPDACTIONPDINVTPDIREFPDMULTPDSERLPDUACTIONPDUVALRPCOUPRPREFRBDATERBRATRBTITL[/code]As you can see, only SOME of the values have returned in this state (and which values are returned in this state changes), it is only ever the first 4 characters and how often it occurs changes.It's just so random, we're yet to identify a trend or reason for this, can anyone offer any suggestions?ThanksJJ

Help comparing a data set within a table.

Posted: 12 Apr 2013 08:22 AM PDT

The first data set contains a schedule with ten dates and I would like to pull the ScheduleID for data that matches these dates exactly. The First data set is know and the second data set is stored in a table. I have thought of using INTERSECT however this returns true when the dates match and there are additional dates attached to the ScheduleID. I am looking to only return if all dates for a ScheduleID match exactly. I am looking for a scalable solution, I do not want to set a president with my developers by running a cursor!With this example the result set would be:ScheduleID114201222---------------------------------------ScheduleID EndDate103 2009-01-03 00:00:00.000103 2009-01-17 00:00:00.000103 2009-01-31 00:00:00.000103 2009-02-14 00:00:00.000103 2009-02-28 00:00:00.000103 2009-03-14 00:00:00.000103 2009-03-28 00:00:00.000103 2009-04-11 00:00:00.000103 2009-04-25 00:00:00.000103 2009-05-09 00:00:00.000--------------------------------------ScheduleTableScheduleID EndDate114 2009-01-03 00:00:00.000114 2009-01-17 00:00:00.000114 2009-01-31 00:00:00.000114 2009-02-14 00:00:00.000114 2009-02-28 00:00:00.000114 2009-03-14 00:00:00.000114 2009-03-28 00:00:00.000114 2009-04-11 00:00:00.000114 2009-04-25 00:00:00.000114 2009-05-09 00:00:00.000201 2009-01-03 00:00:00.000201 2009-01-17 00:00:00.000201 2009-01-31 00:00:00.000201 2009-02-14 00:00:00.000201 2009-02-28 00:00:00.000201 2009-03-14 00:00:00.000201 2009-03-28 00:00:00.000201 2009-04-11 00:00:00.000201 2009-04-25 00:00:00.000201 2009-05-09 00:00:00.000222 2009-01-03 00:00:00.000222 2009-01-17 00:00:00.000222 2009-01-31 00:00:00.000222 2009-02-14 00:00:00.000222 2009-02-28 00:00:00.000222 2009-03-14 00:00:00.000222 2009-03-28 00:00:00.000222 2009-04-11 00:00:00.000222 2009-04-25 00:00:00.000222 2009-05-09 00:00:00.000

something like PIVOT?

Posted: 12 Apr 2013 07:52 AM PDT

I have a table that stores a tablename, the last time the table was accessed and when that data was put into the table:CREATE TABLE [dbo].[LastSelectCheck]( [TableName] [nvarchar](128) NULL, [LastSelect] [datetime] NULL, [RunDate] [datetime] NOT NULL, [Server] [nvarchar](128) NULL) ON [PRIMARY]GOIt's populated every night by a select from sys.dm_db_index_usage_stats.My question: I'd like to run a query that shows the last select over a week's time, like this:[code="other"]Tablename day1 day2 day3--------------------------------------------------------------------------------tblOrder 2013-04-08 NULL 2013-04-10tblOrder2 2013-04-08 2013-04-09 NULL[/code]I realize I can do this thru a series of CTE, temp tables or joins but it seems that there's something more elegant out there. I tried PIVOT but it doesn't look like it'll work here as I'm not aggregating and the columns are not items from the tables.Any advice? TIA.Sample data:[code="other"]insert into LastSelectCheck2 values ('tblOrder', NULL, '2013-04-08', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder2', '2013-04-08', '2013-04-08', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder3', NULL, '2013-04-08', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder4', '2013-04-08', '2013-04-08', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder', '2013-04-09', '2013-04-09', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder2', '2013-04-09', '2013-04-09', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder3', '2013-04-09', '2013-04-09', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder4', '2013-04-09', '2013-04-09', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder', '2013-04-10', '2013-04-10', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder2', '2013-04-10', '2013-04-10', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder3', NULL, '2013-04-10', 'MyServer1')insert into LastSelectCheck2 values ('tblOrder4', NULL, '2013-04-10', 'MyServer1')[/code]

Trigger that collects data from two tables

Posted: 12 Apr 2013 08:35 AM PDT

Hi,Is it possible to create an After Update trigger on table A that can gather both the delete and insert data for the After Update from columns A.1, A.2, A.3 when A.3 is updated to NULL. But I need that plus the delete and insert data from columns B.1 and B.2 from table B as well when column A.3 is updated and set to NULL? I have the trigger below that will get me what I need from table A but is it possible to also get the data from table B from this trigger? In other words, I'm trying to figure out how to populate the Table B OldWorkFlowStepId and NewWorkFlowStepid columns that are created in the CONTAINER_DEBUG table and declared as variables. The OldWorkFlowStepId value needs to be taken when the delete.CurrentStatusId is taken from the first table and the NewWorkflowStepId value needs to be taken when the insert.CurrentStatusId is taken from the first table. How would I go about that? I am trying to troubleshoot a column that is getting set to NULL when it shouldn't and I'm trying to trap exactly where in the workflow this is happening.[code="sql"]DROP TABLE CONTAINER_DEBUGGOCREATE TABLE CONTAINER_DEBUG( ContainerId CHAR(16) ,ContainerName VARCHAR(256) ,OldCurrentStatusId CHAR(16) ,NewCurrentStatusId CHAR(16) ,OldLastCompletedTaskId CHAR(16) ,NewLastCompletedTaskId CHAR(16) ,OldWorkflowStepId CHAR(16) ,NewWorkflowStepId CHAR(16) ,LastActivityDate DATETIME )GODROP TRIGGER CONTAINER_TRG_BUGOCREATE TRIGGER CONTAINER_TRG_BUON CONTAINERAFTER UPDATE AS IF ( UPDATE (CurrentStatusId) )BEGIN -- DECLARE @OldCurrentStatusId CHAR(16); DECLARE @NewCurrentStatusId CHAR(16); DECLARE @OldLastCompletedTaskId CHAR(16); DECLARE @NewLastCompletedTaskId CHAR(16); DECLARE @OldWorkflowStepId CHAR(16); DECLARE @NewWorkflowStepId CHAR(16); -- SELECT @OldCurrentStatusId FROM deleted; -- SELECT @NewCurrentStatusId FROM inserted; -- IF ( ISNULL(@OldCurrentStatusId,'XXX') <> 'XXX' AND ISNULL(@NewCurrentStatusId,'XXX') = 'XXX' ) -- BEGIN -- INSERT INTO CONTAINER_DEBUG ( ContainerId ,ContainerName ,OldCurrentStatusId ,NewCurrentStatusId ,LastActivityDate ) SELECT i.ContainerId ,i.ContainerName ,d.CurrentStatusId ,i.CurrentStatusId ,i.LastActivityDate FROM Inserted i INNER JOIN Deleted d ON i.ContainerId = d.ContainerId -- RAISERROR ('CurrentStatusId is set to NULL - Aborting...', 16, 10); -- END; --END;GO[/code]

Any ideas on how to accomplish this one?

Posted: 12 Apr 2013 06:44 AM PDT

I've got data that can be similar for a specific account, yet different (one of those "it depends")I need to be able to extract the single account record with all of the distinct fuel types associated with it. In the case they are duplicated, I don't need the duplicated fuel type. In cases where the fuel types are different I need to be able to include it into the same stringConsider:[quote][b]MerchNum [/b]| [b]FuelType[/b]00000026104 Diesel;Gas;Other - Non Fuel;00000026104 Diesel0000000500444 Gas0000000500444 Biodiesel[/quote]What I need to see is:[quote][b]MerchNum [/b]| [b]FuelType[/b]00000026104 Diesel;Gas;Other - Non Fuel;0000000500444 Gas;Biodiesel[/quote]Here is some test data:[code="sql"]CREATE TABLE #Data (AcctNum varchar(20), FuelType varchar(128))INSERT INTO #Data VALUES ('00000026104', 'Diesel;Gas;Other - Non Fuel;')INSERT INTO #Data VALUES ('00000026104', 'Diesel')INSERT INTO #Data VALUES ('0000000500444', 'Gas')INSERT INTO #Data VALUES ('0000000500444', 'Biodiesel')[/code]Bearing in mind the TSQL for this may have to parse thousands of records, does anyone have an idea how I would go about getting the result?

[SQL Server Data Warehousing] Where to find best practices for tuning data warehouse ETL queries?

Hi Everybody,

Where can I find some good educational material on tuning ETL procedures for a data warehouse environment? Everything I've found on the web regarding query tuning seems to be geared only toward OLTP systems. (For example, most of our ETL queries don't use a WHERE statement, so the vast majority of searches are table scans and index scans, whereas most index tuning sites are striving for index seeks.)

I have read Microsoft's "Best Practices for Data Warehousing with SQL Server 2008R2," but I was only able to glean a few helpful hints that don't also apply to OLTP systems:

often better to recompile stored procedure query plans in order to eliminate variances introduced by parameter sniffing (i.e., better to use the right plan than to save a few seconds and use a cached plan SOMETIMES);

partition tables that are larger than 50 GB;

use minimal logging to load data precisely where you want it as fast as possible;

often better to disable non-clustered indexes before inserting a large number of rows and then rebuild them immdiately afterward (sometimes even for clustered indexes, but test first);

rebuild statistics after every load of a table.

But I still feel like I'm missing some very crucial concepts for performant ETL development.

BTW, our office uses SSIS, but only as a glorified stored procedure execution manager, so I'm not looking for SSIS ETL best practices. Except for a few packages that pull from source systems, the majority of our SSIS packages consist of numerous "Execute SQL" tasks.

Thanks, and any best practices you could include here would be greatly appreciated.

-Eric

social.technet.microsoft.com/Forums

Saturday, April 13, 2013

Search This Blog