Saturday, June 15, 2013

[SQL Server 2008 issues] Urgent: find which columns are updated

[SQL Server 2008 issues] Urgent: find which columns are updated

Urgent: find which columns are updated

Posted: 14 Jun 2013 04:18 PM PDT

In want to find which columns are updated in a row of a table, and what is their new value and previous value.i know a trigger can work here, with use of columns_updated(). but i want an exact T-SQL script, and any other alternatives for this process.Thanks a Ton in advance. :-)

SQL Server 2008R2 Cluster SAN

Posted: 14 Jun 2013 01:03 PM PDT

Hi,I would like to know if it's possible to install SQL Server binaries on SAN drives (as we only have 1 local drive reserved for the OS).Thank you,Kukah

Make the load incremental

Posted: 14 Jun 2013 02:34 AM PDT

I have a view that pulls the data from severla large tables and then I load it to another table. Currently, the load takes about 40 mins as it is a full load. I like to make it incremental. But I couldn't find a sets of key that would make the record unique. Any suggestions on how to make it incremental?

using parameters in SSRS 2008R2

Posted: 14 Jun 2013 10:00 AM PDT

Hello,I am new to SSRS. In my report, I have the following parameters created:@startDate@endDate@ReportTypeUnder @reportType, I have two items : New Enrollment and Total Enrollment.If user selects "New Enrollment", I would like to do something like enrollment_date >= @startDate and enrollment <= @endDate; If user selects "Total Enrollment", I would like to do something like (enrollment_date <= @endDate and exit_dt >= @startDate) OR (enrollment_date <= @endDate and exit_date is NULL). I think I need to use expression to do that, but I am not sure how to do it. Any help is appreciatedThanksFrank

Declaring one user variable inside the other

Posted: 14 Jun 2013 08:42 AM PDT

Hey Gurus,I have one string variable that defines the transaction type as "Revenue" or "Finance". I have another variable that am using to execute the appropriate stored procedure based on whether the transaction type is revenue or finance. I want to either use a script task for this or I want to be able to do this in my new variable. Can someone please help me with the appropriate code for either of that?Thanks in advance!

Difference between Re-initialize and Re-create merge repliation?

Posted: 14 Jun 2013 03:27 AM PDT

Hi,I had an issue with merge replication between 2 servers. So, I re-initialized subscription by generating new snapshot. But it took 13 hours to finish the initial load on the subscriber. Usually, I delete the replication and re-create the merge replication which take 2 -3 hours. I checked the connection speed between 2 servers. It was fast enough. Can anyone explain me what is the difference? why re-initialize take more time than the re-create?

Appending a character in Last_Name with a condition.

Posted: 14 Jun 2013 01:03 AM PDT

I am new in SQL server database. I have scenario of deleting middle names in a table. There are two tables in my database to control patient name.PT_RED has a field of ALS_FLGPT_NAM has fields of PT_LST_NAM and PT_MID_NAMThe two tables has a primary key: INTNL_PT_NUMHere is what I need.1. If ALS_FLG is "Y" in PT_RED table, append "$" on PT_LST_NAM in PT_NAM table with the same INTNL_PT_NUM.2. Remove PT_MID_NAM in PT_NAM tableThanks for your help.

***Restore multiple sql databases***

Posted: 14 Jun 2013 03:08 AM PDT

I have about 100 db's with mdf, ldf, and ndf files that I need to restore to the SQL 2008R2 on different instance. I have all the backups in their respective folders. Is there a script out there that will restore all of them in one swoop?? Thank you in advance, as always! God Bless

SQL 2012 and AG's

Posted: 14 Jun 2013 02:40 AM PDT

Recent post from Brent Ozar. Is what he is saying correct.In SQL 2012, if your primary drops offline or the cluster loses quorum, the readable replica databases drop offline. (This is hilarious because it's right when you really want to be able to query the secondaries.) No way to control it – it's totally automatic. In SQL 14, the secondaries remain online and readable when the primaries aren't available.Also are secondaries always just readable copies and therefore can not be used in a DR plan.

Performance DIES when REMOVING Where Clause

Posted: 14 Jun 2013 02:29 AM PDT

I've never seen anything like it. I have a simple common table expression that returns 7 rows of data.When you run Select * From cte, it takes 25 seconds to return the data.When you run Select * From cte WHERE X=1, it takes 1 secondWhen you add anything BUT "=" , Select * from cte WHERE X > 0, it takes 25 seonds.I've got an index on that field. The only thing I can add is the field is type Numeric (4,1)Thanks in advance.

Find out if replication failed.

Posted: 14 Jun 2013 04:31 AM PDT

Is there a way to find out (perhaps using DMVs) through TSQL if Replication failed between a given time frame? Thanks.

SQL Server Version

Posted: 13 Jun 2013 11:16 PM PDT

I want see how fast the migration to new versions

How to get results from an SP into a table.

Posted: 09 Jun 2013 11:13 PM PDT

Dear reader,Below is an example to get data from a stored procedure into a table.Is there a more generic method or better method to do this. (Generating a resulttable for example which works for 'any' sp). sp_spaceused is used as an example. Thanks in advance,ben[code="sql"]---- 20130610-- ben brugman---- EXAMPLE:-- two databases-- three tables-- get the space used (6 rows) into a table.---- Question:-- How do I get the info of a stored procedure in a table.------ Create a result table.--select CONVERT(varchar(30), '') as name,CONVERT(varchar(30), '') as rows,CONVERT(varchar(30), '') as reserved,CONVERT(varchar(30), '') as data,CONVERT(varchar(30), '') as index_size,CONVERT(varchar(30), '') as unusedinto ##A where 1 = 1-- second result table with an extra columnselect CONVERT(varchar(30), 'Database1') as DB_name,* Into ##B from ##A---- Fill the first result table.--insert into ##A EXEC sp_spaceused 'table_name1'insert into ##A EXEC sp_spaceused 'table_name2'insert into ##A EXEC sp_spaceused 'table_name3'-- Fill the second result table with extra info.insert into ##B select CONVERT(varchar(30), 'Database1') as DB_name, * from ##A-- Repeat process for a second database.Use database2delete ##Ainsert into ##A EXEC sp_spaceused 'table_name1'insert into ##A EXEC sp_spaceused 'table_name2'insert into ##A EXEC sp_spaceused 'table_name3'insert into ##b select CONVERT(varchar(30), 'Database2') as DB_name,* from ##A---- Show all results.--select * from ##B-- Clear updrop table ##Adrop table ##B[/code]

Help with Pivot Queries

Posted: 14 Jun 2013 03:43 AM PDT

I am trying to create a PIVOT table that produces the sum of balances that newly became exactly 1, 10, 21, 31, 61, 91, 121, 151, 181, 211 days past due each day since December 1, 2012. The data I need is entirely contained in a historical data warehouse called ACCT_MASTER_HISTORY. Here are my queries...select ASOFDATE, DAYSPD, COUNT (account) as 'Acct', sum (ttl_bal-disc) as 'Balance', sum (ttl_bal) as 'TotalBal'into #tempfrom dbo.ACCT_MASTER_HISTORYwhere DAYSPD in (1, 10, 21, 31, 61, 91, 121, 151, 181, 211) and TTL_BAL>0 and ASOFDATE > 1130531 and CLASS_CD not between '90' and '99' group by ASOFDATE, DAYSPDorder by asofdate, DAYSPDselect DAYSPD as Bucket, [1],[10],[21],[31],[61],[91],[121],[151],[181],[211]from (select asofdate, totalbal from #temp) as SourceTablePivot(asofdate, totalbal for dayspd in ([1],[10],[21],[31],[61],[91],[121],[151],[181],[211])as PivotTable;I am new to this type of query and usually would dump the raw to Excel and do the pivot there. Any help would be greatly appreciated.Thanks all!

SQL Server 2008 Database Mail - Bulk Mail Problem

Posted: 14 Jun 2013 03:39 AM PDT

I set up a job to bulk mail about 3,200 of our customers using database mail. The SMTP mail is being relayed by one of our Exchange 2010 servers. The first night I ran the job 600+ emails were sent. The remainder had a sent_status of "failed." I modified the job to run again and check if the customer's email address was in the recipients in msdb..sysmail_sentitems so they would not be sent again. The second night 600+ more went out. The same thing on the third night.We received a complaint from one customer today that she received 3 emails. Looking at msdb..sysmail_allitems, SQL Server logged the first 2 attempts as failures, but they actually were sent. Most failures are getting logged as mail server errors with this message, "Exception Message: Cannot send mails to mail server. (The operation has timed out.)"Does anyone have any ideas on how to better determine if the mail went, or how to get fewer failures from Exchange?

Concurrency With #Temp Tables in Sprocs

Posted: 14 Jun 2013 02:43 AM PDT

Afternoon AllI have a quick question.I'm am in the process of re-writing some sprocs and rather than use udf's or table variable I am using #temp tables.In essence they follow a similar format[code="sql"]CREATE PROCEDURE AASBEGIN IF OBJECT_ID('tempdb..#TEMP') IS NOT NULL DROP TABLE #TEMPCREATE TABLE #Temp(A INT)INSERT INTO #Temp< Do some bits > DROP TABLE #TempEND[/code]My question is could the temp table cause any concurrency issues either from the other sprocs creating a temp table or another user calling the sproc at the same time?I'm thinking not but I want to make sure :ermm:Thanks Andy

sqlMonitor alert:sql server instance unreachable

Posted: 12 Jun 2013 03:10 AM PDT

Hi EveryoneOn our monitoring tool sqlMonitor we got an alert called 'sql server instance unreachable' which lasted for about 2 minutes. I have checked the Cpu queue length for this period and it spikes at around a value of 17 and the counter 'Machine:Processot time' peaks 100% and stays there for about 1 minute. How can i find out what caused this as having the data is good but i need a way of analyzing the query that may have caused this ?

Stored Proc takes along time to run

Posted: 14 Jun 2013 12:34 AM PDT

My problem is that i am having a stored proc that takes too long to run. I have linked servers configured and when i run the proc from server2 it takes over 20 seconds and does a 'REMOTE SCAN' in the execution plan.The stored proc is:[code="sql"]CREATE PROCEDURE dbo.SP_MyProc@UserID uniqueidentifier,@ServiceID intASBEGINUPDATE TABLEASET Comment = @SERVICEIDWHERE UserID = @UserIdend[/code]When i run this code as simple inline update statement on both servers such as:[code="sql"]UPDATE [server2].DatabaseA.TableASET Comment = '237'WHERE UserID = '6FE30BDA-A38B-4D0D-B10F-DC0D324E7883'[/code]it runs in under 1 second but when i call the SP it takes over 20 seconds and this is causing time outs.Can anybody advise as to why when i run it as the simple inline UPDATE it works fine but not so when i call the SP ?

Truncate Table Load and Rename Always Available

Posted: 12 Jun 2013 08:38 PM PDT

Hi Guys,I'm creating a ssis package to run hourly but I need the tables to always be available.I was going to load the data into a Load table then use sp_rename to rename the table to existing table minimizing the downtime on the table.I have a few concerns regarding Indexes and primary key constraints, should I be dropping and recreating prior to using sp_rename?Any thoughts or alternatives would be much appreciated.Thanks,Rich

BULK INSERT with Data Containing Single Quotes

Posted: 14 Jun 2013 01:40 AM PDT

Let's say I have a data file with last names:[code="plain"]SMITHJONESO'HARA (Note that this one has a single quote!)CARTER[/code]that I want to insert into a table:[code="sql"]create table last_names( name varchar(MAX))[/code]I can write a stored procedure[code="sql"]create procedure sp_insert_last_name(@last_name varchar(MAX))asbegin insert into last_names(name) values (@last_name)end[/code]And some higher level code (maybe C, C#, whatever)[code="plain"]while (not end-of-data-file) last_name = read(data-file) last_name.replace("'","''",globally) // globally escape single quotes call sp_insert_last_name(last_name)end while[/code]This works because I coded to escape the single quote.What if I wanted to use BULK INSERT to do this? Will the single quote be handled correctly or will the last name O'HARA cause an error?

?? on xp_cmdshell not writing to file

Posted: 13 Jun 2013 11:40 PM PDT

Hi,Below will not write to file. I did a similar test on a more basic query and it worked.I'm assuming it's the " somewhere, but can't find it..any ideas would be appreciated...ThanksJoedeclare @sql varchar(8000)set @sql='bcp"select CAD19 as ContractNumber, CAD65 as ClientID, client.LName + ", " + CLIENT.FName as Name, convert(varchar, client.BDate, 101)as DOB, CAD300 as HSTSentDate, CAD302 as MemberStartDate, CAD18 as IntakeNotCompletedID, CAD301 as IntakeNotEnrDate, CAD754 as IntakeOtherReason FroM test.dbo.USER_DEFINED_DATA INNER JOIN test.dbo.ASSESSMENT ON test.dbo.USER_DEFINED_DATA.ASSESSMENT_MONIKER = test.dbo.ASSESSMENT.OID INNER JOIN test.dbo.Client ON test.dbo.USER_DEFINED_DATA.ATTACHED_TO_OID = test.dbo.Client.OIDWHERE (test.dbo.ASSESSMENT.Code = "CH1") AND (test.dbo.USER_DEFINED_DATA.CAD750 = "Final") AND (test.dbo.USER_DEFINED_DATA.Expdate IS NULL) and CAD700 = "Processed" FOR XML PATH("REFERRAL"), root("CLUB"),type" queryout "c:\temp\' + REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')+'Clubhouse.xml' + '" -c -T -SUCDB01 -Usa -Ppas'exec master..xp_cmdshell @sql

Repeatable statements that cause need for DBCC checktable repair_rebuild

Posted: 13 Jun 2013 11:09 PM PDT

I have a process that imports some data and massages it by running standard CRUD statements. I then call the following to make sure that my DB is happy. I do this because the process migrates a ton of data.[code] declare @t varchar(250) select table_name INTO #tables from information_schema.tables where TABLE_TYPE = 'Base Table' while EXISTS(select table_name from #tables) BEGIN select top 1 @t = table_name from #tables print('Reindexing ' + @t) DBCC DBReindex (@t, '', 80) delete #tables where TABLE_NAME = @t END -- Update all statistics print('Updating statistics') EXEC sp_updatestats drop table #tables[/code]I then massage the data some more and run the reindex statement again. I have a certain data set that after running the statement the second time show the following:[code]DBCC results for 'sys.sysidxstats'.Msg 2511, Level 16, State 1, Line 1Table error: Object ID 54, index ID 2, partition ID 562949956960256, alloc unit ID 562949956960256 (type In-row data). Keys out of order on page (1:3757), slots 10 and 11.There are 901 rows in 33 pages for object "sys.sysidxstats".[/code]This is repeatable. It clears up after I run DBCC checktable ('sys.sysidxstats', REPAIR_REBUILD). Oddly, with a different (even larger) set of data and the same steps, the issue does not appear. That makes me think it's something in the source data that's causing it.My question: Am I causing this by calling reindex twice? Is something in my data causing this? Could my indexes be bad and be getting screwed up by my CRUD statements? One concern is that I plan on performing the reindexing statement in production once a week. Should I?Any help would be greatly appreciated.ThanksST

Insert is taking long time.

Posted: 13 Jun 2013 10:35 PM PDT

hi team,Insert one records is taking 8 to 1o seconds, indexes,stats every thing is good.i've rebuild the indexes also, same table in other environment is taking fraction of second.any suggestion please...

Backup set expire

Posted: 13 Jun 2013 11:10 PM PDT

Hi all, can any one explain what this mean ...Backup set expire date .. What is meant by expiring here. The backup will be there but we cant use or what..

Error Log

Posted: 13 Jun 2013 08:46 PM PDT

Hi Team,In my database, insertions into error log is more than 150000 records per day, when i want to retrieve error log for particular date (1 week before records) by giving time stamp, it is taking morethan 45 mins to retieve records.is there any easy way to retrive fast or insert particular date error logs in to .sql, .txt, .xls file.Please need you help.

Alias using sp_addlinkedserver?

Posted: 05 Apr 2010 02:52 AM PDT

I have a several sprocs that need to use a linked server. The linked server will change between dev, test and prod (at least), so i would love to have an alias for the linked server. The following shows how to do that using gui tools:[url]http://alexpinsker.blogspot.com/2007/08/how-to-give-alias-to-sql-linked-server.html[/url]I would much prefer to be able to script it since the linking will be done from multiple sites.Possibly? SQL Server version dependent?Thanks.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog