Friday, June 14, 2013

[SQL Server] Using REPLACE in an UPDATE statement


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

Posted by at No comments:
Labels: ,

[SQL Server] Handling SQL Server Errors


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

Posted by at No comments:
Labels: ,

[SQL Server] Using Dynamic SQL in Stored Procedures


Using Dynamic SQL in Stored Procedures




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

Posted by at No comments:
Labels: ,

[Articles] Do You Want a Meritocracy at Work?

[Articles] Do You Want a Meritocracy at Work?

Do You Want a Meritocracy at Work?

Posted: 13 Jun 2013 11:00 PM PDT

The best at their crafts are usually the most rewarded in sports, but not necessarily in other fields. This week Steve Jones asks if you would like it to be different?
Posted by at No comments:

[MS SQL Server] Minimum Permission to create a database snapshot

[MS SQL Server] Minimum Permission to create a database snapshot

Minimum Permission to create a database snapshot

Posted: 14 Jun 2013 02:24 AM PDT

Hi Experts,I know the permission required to create a DB snapshot is same as creating a database. Can anyone please let me know how to give minimum permission for creating the same other than dbcreator.Thanks in Advance

Query to determine if SQL Agent is up?

Posted: 13 Jun 2013 08:43 AM PDT

To check for recent failures of maintenance jobs, a person can run a query such as -- Identify SQL Agent jobs (and maintenance plan subplans) that have failed in the last few days.-- If the Agent is not running, jobs will not start and this query will not find any problems. use msdb go select convert(char(13),server ) as Server , convert(char(60),name ) as JobName , convert(char( 7),step_id ) as StepNum , convert(char(35),step_name) as StepName , dbo.agent_datetime(run_date, run_time) as RunDateTimefrom sysjobhistory, sysjobswhere sysjobhistory.job_id = sysjobs.job_id and -- join tables step_id <> 0 and -- do not select job overview, only the details run_status <> 1 and -- select failed jobs datediff -- last few days ( day, dbo.agent_datetime(run_date, run_time), getdate() ) <= 8order by Server, JobName, StepNum, StepName, RunDateTime desc -- most recent jobs firstThat's all very nice but does not help much if the Agent is down and jobs don't even start running. Is it possible to run a T-SQL query to determine if the agent service is running? If you've got a lot of database servers to check, it would sure be convenient to run a query on all of them to check agent jobs and another query to make sure the agent service is up.

To create updatable subscription in single side replication

Posted: 13 Jun 2013 06:23 AM PDT

Suppose i have to replicate a table from production to reporting server. Production is publisher and report server is subscriber. It is also required that table data may be updated at subscriber. However updates on subscriber should not be send back to publisher. Is it possible to configure such a setting for replication?Thanks
Posted by at No comments:

[SQL 2012] Database Tuning Advisor broken

[SQL 2012] Database Tuning Advisor broken

Database Tuning Advisor broken

Posted: 14 Jun 2013 01:59 AM PDT

Before I say anything else let me just acknowledge that DTA isn't the best or most liked tool out there, but I have been asked to use it so I did, well sort of......But it's broken - each time I use it, it churns away for anything up to an hour and then stops with an error message about it exiting unexpectedly. No other details than that! I've made sure it's not hitting a time limit in the options tab before starting it.I've run the SQL Profiler (which does work) and captured the trace results to a database table (I did also try log files but this didn't change anything) which I use as the load for the DTA to consume.The DTA starts and then some time later stops with the error either during Submitting Configuration Information (most often) or during Consuming Workload.I asked Google and found a Microsoft hotfix for 2012 SP1 ([url]http://support.microsoft.com/kb/2728419[/url]) which documents this error but even after applying this patch I still get the error.Does anyone know what's going on? Am I doing something fundementally wrong? This is a possibility as I have no previous experience of this tool, however, I have successfully used it on a 2008 R2 box so can't think that what I'm doing is flawed, but I'm happy to be corrected if that is the case.Thanks in advance

SQL Server TCP Port for a User

Posted: 14 Jun 2013 01:38 AM PDT

Is its possible to to configure a particular TCP port for an individual SQL Server Principal? I have a requirement where we require to access SQL Server via a firewall from a non trusted domain. I would like only 1 single user to be able to access SQL Server over this port and a separate TCP port which would allow other users. Thanks in advance.

Updating First Field Based on the First of Grouped Rows

Posted: 13 Jun 2013 05:47 AM PDT

I have the following table.Month EmpNum Prod intRate YTDRate1 1111 prd1 2 3.1232408142 1111 prd2 2 1.2053884623 1111 prd1 2 0.7891720694 1111 prd1 2 3.5661128845 1111 prd2 2 2.6691550991 2222 prd4 2 0.0543454622 2222 prd1 2 2.3697770673 2222 prd2 1.25 6.3458340074 2222 prd1 2 3.4960475615 2222 prd1 2 4.0542200566 2222 prd2 2 2.7600991612 3333 prd2 1.25 3.9345911853 3333 prd2 2 2.2208531364 3333 prd1 2 1.718195 3333 prd2 1.25 1.0837347476 3333 prd2 1.25 3.84453072I wrote the following query to find the first month in which YTDRate > intRate by Prod and by employee No.[code="sql"] SELECT MIN(t1.Trans_Month) as Trans_Month, t1.EmpNum, t1.Prod, t1.intRate, t1.YTDRate FROM Sales_Data t1 JOIN (SELECT Trans_Month, EmpNum, Prod, intRate, YTDRate FROM Sales_Data t2 where YTDRate > intRate GROUP BY Trans_Month, t2.EmpNum, Prod, intRate, YTDRate) t3 ON t3.EmpNum = t1.EmpNum AND t3.YTDRate = t1.YTDRate AND t3.intRate = t1.intRate AND t3.Prod = t1.Prod AND t3.Trans_Month = t1.Trans_MonthGROUP BY t1.Trans_Month, t1.EmpNum, t1.Prod, t1.YTDRate, t1.intRate[/code]Now I want to update a field Status with text "Exceeds intRate". How do I modify the queyr to do the update?Any help would be greatly appreciated.Thanks

The transaction log for database 'tempdb' is full due to 'NOTHING' (during cluster install)

Posted: 13 Jun 2013 06:10 AM PDT

I'm wondering if anyone has seen this rather peculiar error while installing SQL 2012 on a cluster before, specifically if putting tempdb on the local disks during setup.There's no serious urgency on my end, I'm literally messing around with the cluster installation on a test cluster exactly for this reason - to learn about any new good-to-know gotchas before I do the real thing. Already found a couple minor things (like the cluster machine account needs "Create Computer Object" on the pre-staged computer account's [u]OU[/u] ... not just full control on the account itself, which used to be good enough in 2008).This one has me a tad stumped though; during the "Advanced cluster completion" phase (both nodes were already prepared successfully, with no issues):[b]The transaction log for database 'tempdb' is full due to 'NOTHING'[/b]Never mind it being at least a candidate for a "best errors" list...Again this is during installation, there is no actively running instance involved. I'm installing a clustered SQL, default instance, with tempdb on the local disks. It's not the usual "log full" for a good reason; the instance isn't even fully installed yet. It's not disk space; both nodes have 20+ GB free on the path I'm putting it in. It's not a mistyped path or permissions; SQL setup clearly found it and was able to work with it, at least at some point, because the MSSQLSERVER service account (NT SERVICE\MSSQLSERVER) was added to the folder's ACL - with Full Control no less - and I didn't do it. There are no tempdb files (or anything else) in it though. There's nothing special about the folder at all; it's just an ordinary subfolder I created, C:\TEMPDB. It's not even a mounted volume (on the real cluster the intent is to put tempdb on lettered, local SSD drives).I'll keep poking at it, but meanwhile if anyone has already encountered this and knows what particular weirdness I've run into here... please let me know. I know in principle it works - I've already tested installing on a cluster a couple times before, including with tempdb on the C:\ drives of the nodes. But something strange happened here, and I'd really like to know what in case I run into it again.

Restore a database using a SAN snapshot

Posted: 13 Jun 2013 04:37 AM PDT

We are using SAN Snapshots as a quick way to recovery our SQL databases. We are taking nightly full backups using DPM, however on a 1TB database, a restore will take hours. We also take log backups every 30 minutes which are shipped off server. We are using SQL 2012 SP1.If we had to use a 2 hour old SAN snaphot to recover our data drive for something like complete logical disk corruption, how could we roll the database forward using the transaction log backups and the LDF file after the snapshot was presented to the server? Our SAN does not have the ability to mount the database in a NORECOVERY state, so the database comes online just fine, but the data is simply 2 hours old.

Upgrading MSSQL 2008 to MSSQL 2012

Posted: 13 Jun 2013 04:37 AM PDT

We are upgrading our MSSQL2008 DB server to MSSQL2012. Being new to MSSQL2012. My question is will the SSIS packages we have stored in the database be convert as well or do we have to reload them after they have been convert from VS2008 to VS2012? Thanks in advance.Kimball

Where is the Download for SQL Server Native Client 11.0 for Windows 7 (32 bit)

Posted: 30 May 2013 02:13 AM PDT

SQL Server Native Client 11.0 download for Windows 7 32 bit It is suppose to work. After searching an hour, MS claims it can be installed as part of the new SQL Server in one of the directories.Our development group doesn't have access to the servers.Where can this installation (or dll) be located? Microsoft has made this extremely difficult.So difficult that there is an exploit phishing site that pretends to download the driver, but actually installs maleware.We are also running a SQL Server 2008 on a server. Do we also need to upgrad it to this newer DLL? Would it help?
Posted by at No comments:

[T-SQL] Help with Locking Tables for Stored Proc

[T-SQL] Help with Locking Tables for Stored Proc

Help with Locking Tables for Stored Proc

Posted: 13 Jun 2013 11:56 PM PDT

Instead of placing the database into single user mode, while we load new data, I am trying to figure out the SQL command: SET TRANSACTION ISOLATION LEVEL, but there is not a lot of information on the MS page. (only one example)What I would like to do is put the locks in place (so we can ovoid the deadlocks we are getting with users during the load process, since it always kills the stored proc).From what I am reading.. the SQL would look something like..SET TRANSACTION ISOLATION LEVEL TABLOCK HOLDLOCKGOBEGIN TRANSACTION(CURRENT SQL CODE)END TRANSACTIONIs that correct?? Wouldn't I also need to change the transaction isolation back to normal?

Need help to create a column, add values and then compare with another table

Posted: 14 Jun 2013 12:50 AM PDT

Hi allI need some basic help in adding values to a column in a table(like making a key in EXCEL) and making a similar key in a different table, and then compare which rows are present in one and not in another table and vice versa. For example, one table contains values:ANUM BNUM STARTDATE DURATION260975649803 0965000066 2013-04-20 00:02:55 49260976522522 0977144491 2013-04-20 10:23:35 161260977313710 0977809127 2013-04-20 07:21:48 272260977206661 0976320516 2013-04-20 20:39:36 51260977948010 0966952330 2013-04-20 00:34:04 55260979121229 0977261566 2013-04-20 00:33:25 13Note that all field types are varchar. I want to make a key which will give me substring(ANUM,4,10) &"_"& substring(BNUM,2,10) &"_"& only the HHMM of the STARTDATE &"_"& DURATION.i have altered the same table and created a column "KEY_IN" with varchar(50) but need help in populating the values from the relevant column fields into it. My expected result for the above sample rows should look like:ANUM BNUM STARTDATE DURATION KEY_IN260975649803 0965000066 2013-04-20 00:02:55 49 975649803_965000066_00:02_49 260976522522 0977144491 2013-04-20 10:23:35 161 976522522_977144491_10:23_161260977313710 0977809127 2013-04-20 07:21:48 272 977313710_977809127_07:21_272260977206661 0976320516 2013-04-20 20:39:36 51 977206661_976320516_20:39_51 Thanks in anticipation!

Need help in soling a SQL issue

Posted: 14 Jun 2013 12:49 AM PDT

Hi ,I am trying to get the schedule for a resource. It may have multiple weekly schedules & onetime schedule.If it is onetime schedule it always take reference over weekly.But while checking the records for a resource we have to make sure there exists at least one row for IsResSchedule = 1.CREATE TABLE [dbo].[W_O_Schedule]( [LocationId] [int] NOT NULL, [resourceid] [int] NOT NULL, [availability] [varchar](11) NOT NULL, [ScheduleRepeat] [varchar](7) NOT NULL, [StartTime] [datetime] NULL, [EndTime] [datetime] NULL, IsResSchedule int ) ON [PRIMARY]GOInsert into [W_O_Schedule]Values--Only row 1 and 2 from here (1, 2, 'Available', 'Weekly', '2000-01-03 09:00:00', '2000-01-03 12:00:00',0),(1, 2, 'Available', 'Weekly', '2000-01-03 13:00:00', '2000-01-03 17:00:00',0),(1, 2, 'Available', 'Weekly', '2000-01-03 09:00:00', '2000-01-03 17:00:00',1),--From the below 2 I wasnt only row with 'Unavailable' (1,4, 'Available', 'Weekly', '2000-01-03 09:00:00', '2000-01-03 17:00:00',1),(1,4, 'Unavailable', 'OneTime', '2013-06-03 00:00:00', '2013-06-04 00:00:00',0),--Nothing from here since IsResSchedule = 0 for both rows (1,5, 'Available', 'Weekly', '2013-06-03 00:00:00', '2013-06-03 20:00:00',0),(1,5, 'Available', 'OneTime', '2013-06-03 00:00:00', '2013-06-03 30:00:00',0)--If I want to get schedule for '2013-06-03' for resource 2, 4 & 5--My result sould be(1, 2, 'Available', 'Weekly', '2000-01-03 09:00:00', '2000-01-03 12:00:00',0)(1, 2, 'Available', 'Weekly', '2000-01-03 13:00:00', '2000-01-03 17:00:00',0)(1,4, 'Unavailable', 'OneTime', '2013-06-03 00:00:00', '2013-06-04 00:00:00',0)

Need help with an insert

Posted: 13 Jun 2013 06:45 AM PDT

Hello,I am trying to insert into a log table that stores the reason behind the non execution of a stored proc. The stored proc runs only if a specific condition is met and for that to execute I am using an if condition. Now the log table comes into picture in the else part and here is what I have in my code for logging:[code="sql"]else select @ID1=ID from [CBH] where [SID] = 2select @ID2=ID from [PBH] where [SID] = 2INSERT into dbo.Logtable ( AdditionalInfo ) values('Another Process with ID:'+@ID1+'is currently in use')INSERT into dbo.Logtable ( AdditionalInfo ) values('Another Process with ID:'+@ID2+'is currently in use')ENDend[/code]But what I really want to do is:If @ID1 exists then insert that value in to the logtableand if @ID2 exists then insert that value in to the logtable.If both @ID1 and @ID2 exists then insert both into the logtable with the same text.Please note that the @ID1 and @ID2 are of type bigint and I am trying to insert that along with the text into the log table.Thanks for your help on this.

Work out 2nd, 3rd, 4th Puchases

Posted: 13 Jun 2013 09:49 PM PDT

Hey guys,I am querying a database to find out the dates of customers 1st, 2nd, 3rd, 4th purchases.I am so far querying the first transaction (using MIN) and the most recent transaction (using MAX).My question is: how can I query the database further to get the 2nd, 3rd and 4th purchases etc?My query is this:SELECT mem.MemberID, FirstName + ' ' + LastName AS 'Customer', MIN(TransactionDate) AS 'First Transaction', MAX(TransactionDate) AS 'Most Recent Transaction', COUNT(*) AS 'Total Number Of Purchases'FROM Transaction t INNER JOIN Members m ON t.MemberId = m.MemberIdGROUP BY mem.MemberId, FirstName + ' ' + LastNameHAVING NOT FirstName + ' ' + LastName = '' --where members name has not been provided AND COUNT(*) > 1 AND MIN(TransactionDate) > '2011-01-01'ORDER BY COUNT(*) DESCAll advice will be greatly appreciated.Thanks,Dan

Ranking and Summing

Posted: 13 Jun 2013 11:01 AM PDT

Any help would be appreciated. I need to sum per product type and then I need to also sum depending on whether the product ranking is good or bad.I could probably do this with multi-stage temporary table type processing but I imagine a single select could it as well. I just can't imagine the specifics.So total fruit sales was 139. Good fruit sales was 84. So 60% of fruit sales was good, 40 bad.Desired results set is like this:Fruits Good Grade 60Fruits Bad Grade 40Vegetables Good Grade 80Vegetables Bad Grade 20Dairy Good Grade 38Dairy Bad Grade 61CREATE TABLE #Products( ProductID int, ProductType char(10), ProductSales int, ProductRank int) INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (1,'Fruits',55,3)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (2,'Fruits',57,5)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (3,'Fruits',27,6)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (4,'Vegetables',67,1)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (5,'Vegetables',46,5)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (6,'Vegetables',73,5)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (7,'Vegetables',124,9)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (8,'Vegetables',27,6)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (9,'Dairy',5,7)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (10,'Dairy',97,3)INSERT INTO #Products ( ProductID, ProductType, ProductSales,ProductRank) VALUES (11,'Dairy',56,8)SELECT CASE WHEN ProductRank >= 5 THEN 'Good Grade' WHEN ProductRank < 5 THEN 'Bad Grade' END AS Grade, * FROM #ProductsDROP TABLE #Products

PatIndex or CharIndex

Posted: 13 Jun 2013 09:52 AM PDT

Hi, can someone help me with PatIndex or CharIndex?I have a table belowselect 2011 as Year, 1111 as Count into #tinsert into #t select 2012, 2222 insert into #t select 2013, 3333-- Year is smallint typeselect * from #t where Year in (2011,2012)Can I use PatIndex or CharIndex or any other ways to do same thing as above result using parameter @Year below?declare @Year varchar(50)set @Year = '2012,2011'select * from #t where Year ......
Posted by at No comments:
Subscribe to: Comments (Atom)

Search This Blog