Saturday, March 2, 2013

[SQL Server 2008 issues] Deletion of database

[SQL Server 2008 issues] Deletion of database

Deletion of database

Posted: 01 Mar 2013 03:34 PM PST

Hi, is there any way we can find the details of the deleted database. means who deleted and when deleted.

mirroring

Posted: 01 Mar 2013 05:44 PM PST

how to find the synchronisation of data of the previous month when the database is configured in mirroring ?

a tricky query

Posted: 01 Mar 2013 03:43 AM PST

I came across a certain query which was as follows:Find employees having salary greater than avg salary of their departmentThe solution was :[CENTER][CODE]select * from student e where salary > (select avg(salary) from student f where f.dep=e.dep);[/CODE][/CENTER]But when I did it as[CODE] select * from student where salary > (select avg(salary) from student e where dep=e.dep);[/CODE]It showed me a different result can someone please explain the difference?

MS SQL to Postgres ETL / Replication / Synch

Posted: 08 Feb 2013 01:59 PM PST

I've recently been tasked getting my SQL Server 2008 R2 data into a Postgres 9.2.2 server on Linux.My source SB is roughly 100GB in size, probably only 15% of which changes daily. I need to propogate those changes to the Postgres server but I'm having trouble with the speed of the various methods I've tried. I've tried using the 32bit Postgres ODCB connection in SSIS, this was very slow. On the order of 100 Rows/sec inserting to Postgres. I could not get the 64bit connection to work at all. I've tried using CloverETL and open source ETL platform, performance was again pretty slow, On the order of a 1000 rows/sec inserting to Postgres. I've also tried Kettle/Spoon ETL platform, which is getting me around 5000 rows/sec into Postgres, which is still very slow for my dataset.I think I'm going to have to resort to a BCP out ---> COPY FROM solution. Does anyone have any guidance or insight into how best to propagate data changes into Postgres?

Denormalising report from normalised nested source data

Posted: 28 Feb 2013 11:29 PM PST

HiI have an application that stores normalised and nested data. Each top level instance is identified by an instance Id. Each line of data has a Lineage and NestedIndexId such that it's child has the same instance Id with the Lineage = Parent Lineage+'_'+ Parent NestedIndexId. The data item is indicated by columns [DataName] and [DataValue]This structure allows a flexible data storage for any number of records and level of nesting. Using an object model on the application side to represent and manage this is easy enough, but I am battling with a way to run a flat report to get the data out in a usable format.Hopefully the code below describes what I am trying to explain, together with where I am with pivoting the data:[code="sql"]CREATE TABLE #Data (InstanceId INT, Lineage VARCHAR(12), IndexId INT, DataName VARCHAR(20),DataValue VARCHAR(100))INSERT INTO #Data ( InstanceId , Lineage , IndexId , DataName , DataValue )VALUES ( 1, '0' ,0,'Parent' ,'1 Parent 0') ,( 1, '0_0' ,0,'Child' ,'1 Child 0 0') ,( 1, '0_0_0',0,'Grandchild','1 Grandchild 0 0') ,( 1, '0_0_0',1,'Grandchild','1 Grandchild 0 1') ,( 1, '0_0' ,1,'Child' ,'1 Child 0 0') ,( 1, '0_0_1',0,'Grandchild','1 Grandchild 1 0') ,( 1, '0_0_1',1,'Grandchild','1 Grandchild 1 1') ,( 2, '0' ,0,'Parent' ,'2 Parent 0') ,( 2, '0_0' ,0,'Child' ,'2 Child 0 0') ,( 2, '0_0_0',0,'Grandchild','2 Grandchild 0 0') ,( 2, '0_0_0',1,'Grandchild','2 Grandchild 0 1') ,( 2, '0_0' ,1,'Child' ,'2 Child 0 0') ,( 2, '0_0_1',0,'Grandchild','2 Grandchild 1 0') ,( 2, '0_0_1',1,'Grandchild','2 Grandchild 1 1') SELECT * FROM( SELECT InstanceId ,Lineage ,IndexId ,DataValue ,DataName FROM #Data) SourceDataPIVOT ( MAX(DataValue) FOR DataName in ([Parent],[Child],[GrandChild])) As PVT ORDER BY InstanceId,Lineage + '_'+CAST(IndexId AS VARCHAR(12))DROP TABLE #Data[/code]I need the output of the query to be formatted in the following fashion:[code="other"]Parent Child GrandChild1 Parent 0 1 Child 0 0 1 Grandchild 0 01 Parent 0 1 Child 0 0 1 Grandchild 0 11 Parent 0 1 Child 0 0 1 Grandchild 1 01 Parent 0 1 Child 0 0 1 Grandchild 1 12 Parent 0 2 Child 0 0 2 Grandchild 0 02 Parent 0 2 Child 0 0 2 Grandchild 0 12 Parent 0 2 Child 0 0 2 Grandchild 1 02 Parent 0 2 Child 0 0 2 Grandchild 1 1but the closes I am able to get from a query like the one above is InstanceId Lineage IndexId Parent Child GrandChild1 0 0 1 Parent 0 1 0_0 0 1 Child 0 0 1 0_0_0 0 1 Grandchild 0 01 0_0_0 1 1 Grandchild 0 11 0_0 1 1 Child 0 0 1 0_0_1 0 1 Grandchild 1 01 0_0_1 1 1 Grandchild 1 12 0 0 2 Parent 0 2 0_0 0 2 Child 0 0 2 0_0_0 0 2 Grandchild 0 02 0_0_0 1 2 Grandchild 0 12 0_0 1 2 Child 0 0 2 0_0_1 0 2 Grandchild 1 02 0_0_1 1 2 Grandchild 1 1[/code]Any ideas appreciatedGavin

DateTime Rows to Columns

Posted: 01 Mar 2013 03:06 AM PST

HiI have something like a table of users and other table with dates and working time for the users, i want to display the working hours for the users, but i need to see the date in columns. I created a little script to show what i want, but i'm having a problem with the duplicate users.[code="sql"]DROP TABLE #UsersDROP TABLE #WorkingCREATE TABLE #Users (Num INT, NAME NVARCHAR(5))CREATE TABLE #Working (WorkDay SMALLDATETIME,Num INT, TimeWorked INT)INSERT INTO #UsersSELECT 1,'User1'UNION ALLSELECT 2,'User2'UNION ALLSELECT 3,'User3'INSERT INTO #WorkingSELECT '01/01/2013',1,2UNION ALLSELECT '02/01/2013',1,0UNION ALLSELECT '03/01/2013',1,1UNION ALLSELECT '04/01/2013',1,4UNION ALLSELECT '05/01/2013',1,0UNION ALLSELECT '01/01/2013',2,0UNION ALLSELECT '02/01/2013',2,0UNION ALLSELECT '03/01/2013',2,20UNION ALLSELECT '04/01/2013',2,20UNION ALLSELECT '05/01/2013',2,20UNION ALLSELECT '01/01/2013',3,30UNION ALLSELECT '02/01/2013',3,30UNION ALLSELECT '03/01/2013',3,0UNION ALLSELECT '04/01/2013',3,0UNION ALLSELECT '05/01/2013',3,0SELECT * FROM #UsersSELECT * FROM #WorkingSELECT DISTINCT U.Num, U.NAME, [1] = CASE WHEN MONTH(W.WorkDay) = 1 THEN W.TimeWorked ELSE 0 END, [2] = CASE WHEN MONTH(W.WorkDay) = 2 THEN W.TimeWorked ELSE 0 END, [3] = CASE WHEN MONTH(W.WorkDay) = 3 THEN W.TimeWorked ELSE 0 END, [4] = CASE WHEN MONTH(W.WorkDay) = 4 THEN W.TimeWorked ELSE 0 END, [5] = CASE WHEN MONTH(W.WorkDay) = 5 THEN W.TimeWorked ELSE 0 ENDFROM #Users U INNER JOIN #Working W ON U.Num = W.Num[/code]In the end the query will be built on the fly, with the help of the tally table, i just pass 2 paramteres to the sp, the year and the month...Thanks

how does sql read tempdb multiple .ldf files

Posted: 01 Mar 2013 04:49 AM PST

Does anybody know how sql uses multiple tempdb .ldf files when there is a restricted file growth limit on the data files? So, let's say I have tempdb1.mdf, tempdb2.ldf and tempdb3.ldf data files. When let's say tempdb2.ldf get's full does it move to the next data file (tempdb3.ldf) withough throwing a tempdb full error message? Or, does sql only throw the tempdb full error message when ALL the data files are full?Because a client has this set up with multiple tempdb data files with restricted file growth (not sure why cause i know this is not right practice). Appreciate the help.

SQL Agent skipping job execution

Posted: 01 Mar 2013 01:04 AM PST

I am experiencing an issue with a couple of SQL Agent jobs on SQL Server 2008 R2. The job will execute and you can see it in the history, but the run time is typically less than 10 seconds and the code itself did not execute. Actually, some of the code is executing, but not all of it. Strange as hell. And it is infrequent. Some days it works, some days it doesn't. Mr. Google shows me that other people have experienced this issue going back to SQL2005, however, nobody seems to have a solution. These are things it is not: - It is not a security issue. The job is running as system administrator - It is not due to other jobs running at the same time. - It is not due to SQL Agent or even SQL Server needing to be restarted or the server being rebooted (all of that has been done). - It is not specific to an edition of SQL. I'm seeing the problem in Enterprise and Standard.Here is one of the jobs that is showing this behavior:[code="sql"]DECLARE @Threads INT = 5DECLARE @ReorgThreshold INT = 30DECLARE @Rows BIGINT = 10000DECLARE @ByPassGetStats BIT = 0DECLARE @ByPassDefrag BIT = 0 DECLARE @DatabaseID INT;DECLARE @DatabaseName VARCHAR(255);DECLARE @TableName VARCHAR(255);DECLARE @SchemaName VARCHAR(255);DECLARE @ThreadCounter INT;DECLARE @SQL NVARCHAR(4000);CREATE TABLE #IndexFrag ( DatabaseName VARCHAR(255) NULL, ObjectID INT, IndexID INT, PartitionNumber INT, FragmentationPerc FLOAT, Pages INT, Records BIGINT, IndexName VARCHAR(255), SchemaName VARCHAR(255), TableName VARCHAR(255), AllowPageLocks INT);CREATE TABLE #TableList ( DatabaseName VARCHAR(255) NULL, SchemaName VARCHAR(255) NULL, TableName VARCHAR(255) NULL, Records BIGINT );IF @ByPassGetStats = 0BEGIN --Get the index fragmentation DECLARE ReorgIndexCursor01 CURSOR FOR SELECT [name], database_id FROM master.sys.databases WHERE [name] NOT IN ('master', 'model', 'tempdb') ORDER BY [name]; OPEN ReorgIndexCursor01; FETCH NEXT FROM ReorgIndexCursor01 INTO @DatabaseName, @DatabaseID; WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #IndexFrag (DatabaseName, ObjectID, IndexID, PartitionNumber, FragmentationPerc, Pages, Records) SELECT @DatabaseName, ps.OBJECT_ID, ps.index_id, ps.partition_number, ps.avg_fragmentation_in_percent, ps.page_count, ps.record_count FROM master.sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL , NULL, N'SAMPLED') ps WHERE ps.index_id > 0 OPTION (MaxDop 1); --Update the table with the schema, table, and index names SELECT @SQL = 'USE [' + @DatabaseName + '] UPDATE #IndexFrag SET IndexName = i.name, SchemaName = s.name, TableName = o.name, AllowPageLocks = i.allow_page_locks FROM #IndexFrag ti INNER JOIN sys.objects o ON ti.ObjectID = o.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.indexes i ON o.object_id = i.object_id WHERE ti.DatabaseName = ' + CHAR(39) + @DatabaseName + CHAR(39) + ' AND i.index_id = ti.IndexID '; EXEC (@SQL); FETCH NEXT FROM ReorgIndexCursor01 INTO @DatabaseName, @DatabaseID; END CLOSE ReorgIndexCursor01; DEALLOCATE ReorgIndexCursor01; --Update the PNGCORP_IndexList table UPDATE msdb.dbo.PNGCORP_IndexList SET FragmentationPerc = f.FragmentationPerc, Pages = f.Pages, Records = f.Records, LastChecked = GETDATE() FROM #IndexFrag f INNER JOIN msdb.dbo.PNGCORP_IndexList il ON il.DatabaseName = f.DatabaseName AND il.ObjectID = f.ObjectID AND il.IndexID = f.IndexID AND il.PartitionNumber = f.PartitionNumber; --Insert new indexes into the PNGCORP_IndexList INSERT INTO msdb.dbo.PNGCORP_IndexList (DatabaseName, ObjectID, IndexID, PartitionNumber, FragmentationPerc, Pages, Records, IndexName, SchemaName, TableName, AllowPageLocks, LastChecked) SELECT DatabaseName, ObjectID, IndexID, PartitionNumber, FragmentationPerc, Pages, Records, IndexName, SchemaName, TableName, AllowPageLocks, GETDATE() FROM #IndexFrag f WHERE ( SELECT COUNT(*) FROM msdb.dbo.PNGCORP_IndexList il WHERE il.DatabaseName = f.DatabaseName AND il.ObjectID = f.ObjectID AND il.IndexID = f.IndexID AND il.PartitionNumber = f.PartitionNumber) = 0;END--Get the tables we need to reindexINSERT INTO #TableList (DatabaseName, SchemaName, TableName, Records)SELECT DatabaseName, SchemaName, TableName, MAX(Records)FROM msdb.dbo.PNGCORP_IndexListWHERE FragmentationPerc >= @ReorgThreshold AND Records >= @RowsGROUP BY DatabaseName, SchemaName, TableNameORDER BY MAX(Records) DESC--Cycle through the problem indexes and insert them into the PNGCORP_IndexReorg# tables.SET @ThreadCounter = 1; IF @Threads > 5 SET @Threads = 5;TRUNCATE TABLE msdb.dbo.PNGCORP_IndexReorg1;TRUNCATE TABLE msdb.dbo.PNGCORP_IndexReorg2;TRUNCATE TABLE msdb.dbo.PNGCORP_IndexReorg3;TRUNCATE TABLE msdb.dbo.PNGCORP_IndexReorg4;TRUNCATE TABLE msdb.dbo.PNGCORP_IndexReorg5;DECLARE ReorgIndexCursor02 CURSOR FORSELECT DatabaseName, SchemaName, TableNameFROM #TableListORDER BY Records DESC;OPEN ReorgIndexCursor02;FETCH NEXT FROM ReorgIndexCursor02 INTO @DatabaseName, @SchemaName, @TableName;WHILE @@FETCH_STATUS = 0BEGIN SET @SQL = ' INSERT INTO msdb.dbo.PNGCORP_IndexReorg' + CAST(@ThreadCounter AS VARCHAR(1)) + ' (DatabaseName, SchemaName, TableName, IndexName, AllowPageLocks) SELECT DISTINCT i.DatabaseName, i.SchemaName, i.TableName, i.IndexName, i.AllowPageLocks FROM msdb.dbo.PNGCORP_IndexList i INNER JOIN #TableList t ON t.DatabaseName = i.DatabaseName AND t.SchemaName = i.SchemaName AND t.TableName = i.TableName WHERE i.DatabaseName = ''' + @DatabaseName + ''' AND i.SchemaName = ''' + @SchemaName + ''' AND i.TableName = ''' + @TableName + ''' AND i.FragmentationPerc >= ' + CAST(@ReorgThreshold AS VARCHAR(25)) + ' AND i.Records >= ' + CAST(@Rows AS VARCHAR(25)) + '; '; EXEC (@SQL); SET @ThreadCounter = @ThreadCounter + 1; IF @ThreadCounter > @Threads SET @ThreadCounter = 1; FETCH NEXT FROM ReorgIndexCursor02 INTO @DatabaseName, @SchemaName, @TableName;ENDCLOSE ReorgIndexCursor02;DEALLOCATE ReorgIndexCursor02;DROP TABLE #TableList;DROP TABLE #IndexFrag;--Start the index jobsIF @ByPassDefrag = 0BEGIN EXEC msdb.dbo.sp_start_job @Job_Name = 'Database Maintenance.IndexReorg1'; IF @Threads >= 2 EXEC msdb.dbo.sp_start_job @Job_Name = 'Database Maintenance.IndexReorg2'; IF @Threads >= 3 EXEC msdb.dbo.sp_start_job @Job_Name = 'Database Maintenance.IndexReorg3'; IF @Threads >= 4 EXEC msdb.dbo.sp_start_job @Job_Name = 'Database Maintenance.IndexReorg4'; IF @Threads = 5 EXEC msdb.dbo.sp_start_job @Job_Name = 'Database Maintenance.IndexReorg5';END[/code]What we are doing here is finding all of the indexes with a fragmentation > 30% and putting them into 5 different tables. Then we kick off five other jobs to perform the index maintenance. On some days, this jobs executes perfectly. It normally takes about an hour to run (there are some large databases on this server). On other days, it takes less than 10 seconds to execute, the code that updates the index list isn't executed. But what is really strange is the last couple of lines of code are ALWAYS executed and the five "IndexReorg" jobs are kicked off. I have the same issue with a db check job. Some days it executes, some days it doesn't. SQL Agent always reports that the job executed successfully, but you could tell by the runtime that nothing was done.[code="sql"]SET NOCOUNT ONDECLARE @SQL NVARCHAR(MAX)DECLARE @DatabaseName VARCHAR(255)DECLARE CheckDatabaseIntegrityCursor CURSOR FORSELECT [name]FROM master.sys.databasesWHERE [name] NOT IN ('model', 'tempdb') ORDER BY [name]OPEN CheckDatabaseIntegrityCursorFETCH NEXT FROM CheckDatabaseIntegrityCursor INTO @DatabaseNameWHILE @@FETCH_STATUS = 0BEGIN PRINT @DatabaseName + ' ---------------------------------------------------------------' SELECT @SQL =' USE [' + @DatabaseName + '] DBCC CHECKDB(N' + CHAR(39) + @DatabaseName + CHAR(39) + ') ' PRINT @SQL EXEC master.dbo.sp_executesql @SQL FETCH NEXT FROM CheckDatabaseIntegrityCursor INTO @DatabaseNameENDCLOSE CheckDatabaseIntegrityCursorDEALLOCATE CheckDatabaseIntegrityCursor[/code]I've seen a few posts on various forums where folks have reporting such issues, but nobody has any solution. Anyone else see anything like this?

Kill alter Index rebuild Jobs

Posted: 01 Mar 2013 01:23 AM PST

Hi allWhat is you opinion about killing alter index rebuild jobs? Can this be done easily, never ever or depends? I'm asking because recently we had the issue that an alter index rebuild job had to be killed because it's a huge performance impact and the system was performing poorly. After killing this jobs the system was almost unusable and under heavy load from the rollback. So, should I never kill an alter index job again?Regards, Christian

How to change the named instance name to default instance name on SQL Server 2008 R2?

Posted: 01 Mar 2013 01:15 AM PST

Recently I was deployed SQL Server 2008 R2 on my windows server 2003 box with named instance, already 2005 default instance running on this box. Now i am going to uninstall the 2005 server on this box, so i would like to change the 2008 R2 named instance to default instance is that possible, please give me the suggestions.Thanks,Giri

MsxEncryptChannelOptions and Multi-server administration

Posted: 01 Mar 2013 01:30 AM PST

Setting up the multi-server administration failed for me because the MsxEncryptChannelOptions registry value on the server I am attempting to make the target is set to '2'. According to Microsoft and everyone else on the interwebz, changing it to '0' resolves this issue. My question is what are the effects of changing the value to '0'? Seeing as this is a production server, making changes to the registry without knowing what else it will effect kinda makes me nervous. What else uses this registry entry or is it exclusively used by the multi-server admin functions?

How to change the data retention period from 5 yr to 20yrs in SQL 2005

Posted: 01 Mar 2013 01:34 AM PST

hi guys I am working on one scenario to figure out How to change the data retention period from 5 yr to 20yrs in SQL 2005.when this server was setup the retention period was set up by someone to 5 yrs but this year they want to change it to 20yr so how to do ?Any ideas?Thanks for Great Help.

Need help with a select, insert

Posted: 22 Feb 2013 09:00 AM PST

GOAL: insert into tblcompanyassignments from tbluserassignments. Only attempt the insert for userid that exist in both tbluserassignments and tblexistingusers. For existing users, further filter down the insert attempt for only companies that exist. Final requirement, don't attempt to insert if there is already an existing user, company combination tbluserassignmentsusernamecompanynamerecord 1: userA, companyArecord 2: userA, companyBrecord 3: userB, companyBrecord 4: userB, companyCrecord 5: userC, companyArecord 5: userC, companyB tblexistingusersuseridrecord 1: userArecord 2: userBtblexistingcompaniescompanyidrecord 1: companyArecord 2: companyBtblcompanyassignmentsuseridcompanyidrecord 1: userA, companyArecord 2: userB, companyB

Problem with sp_refreshsqlmodule

Posted: 01 Mar 2013 03:27 AM PST

Hi,We are using sp_refreshsqlmodule to find out if there are any integrity issues with the database.The process is that we generate scripts by dropping and recreating stored procedures, functions or views every time we change any of the objects.I noticed today that when I execute sp_refreshsqlmodule procedure after running my scripts, SQL Server is reverting my changes done. For e.g. If a dropped a stored procedure and created it again using a drop and create script, SQL Server is reverting the change I have done with my new script for that procedure.Has anyone else faced a similar issue? I am interested in knowing the underlying cause as to why SQL Server is behaving in this manner?Thanks!

DB Mail - Controling What apears in the FROM setcion of an email

Posted: 27 Feb 2013 12:52 AM PST

We recently upgraded from SQL 2005 to SQL 200R2. I used DB Mail on the SQL 2005 Box and am doing the same on the 2008R2 box. I have created a profile and account on DB Mail on the 2008R2 server that is identical to the profile & account on the 2005 server with the exception that anywhere I had 'SS2005' on the acount or profile on my 2005 box I now have set to 2008R2 on the 2008R2 box. I am using the exact same (unchanged) email address on each and am using teh same mail server IP and port. I have taken screenshots and checked and double checked and I can find nothing different between the 2 and yet an email from each comes into OUtlook with very different FROM values. Using the exact same Call msdb.dbo.sp_send_dbmail on each box (with the diference being the subject line )...On my 2005 box the FROM is shown as SQL04\2005.DBA and the same call to this SP on the 2008R2 server produces an email with my name (format is FirstName Lastname) in the FROM field.Does anyone knwo if something changed with DB Mail between 2005 & 2008R2 that coudl cause this? Its important because this tag line in the FROM field lets me easily detremine which serevrt an email is coming from as we have a number of SQL Servers that use the sp_send_dbmail SP to send info throughout the day.night. Thoughts?If a screenshot is necessary I can do it but my IT guy tells me I have to use fake values for the vairous fields for security reasons and thats why I haven;t included it in the post. If its necessary I can do it though.

Query Help

Posted: 01 Mar 2013 01:24 AM PST

Hello EveryoneI need one help to develop my logic[code="sql"]create table #x2(CourseID varchar(10),ScheduleTerms Varchar(2))insert into #x2 values ('000541','Q1')insert into #x2 values ('000541','Q2')insert into #x2 values ('000541','Q3')insert into #x2 values ('000541','Q4')insert into #x2 values ('001951','Q1')insert into #x2 values ('001951','Q2')insert into #x2 values ('001951','Q3')insert into #x2 values ('001951','Q4')[/code]It gives me output as below[code="plain"]CourseID ScheduleTerms000541 Q1000541 Q2000541 Q3000541 Q4001951 Q1001951 Q2001951 Q3001951 Q4[/code]The desired output is [code="plain"]CourseID Q1 Q2 Q3 Q4000541 1 1 1 1001951 1 1 1 1[/code]Please help me to display in the bit format as desired output.Thanks

Testing scenario, physical reads.

Posted: 25 Feb 2013 08:16 PM PST

Hello in a testing scenario, I want to have access to the physical reads for every individual query.With statistics io on, I get the physical reads in text format.Is it possible to get these number in a table format, or query for these numbers ?Thanks,Ben Brugman

how to store DB roles before restoring db

Posted: 28 Feb 2013 10:14 PM PST

Hi guys is there anyway we can script out or store the database roles and permission before we restore that DB.If anyone can help me out with this Thanks And Happy Fri

Script Out Database Mail Settings?

Posted: 08 Sep 2010 06:30 AM PDT

Anyone bother to have created a script to Reverse Engineer / Script out your EXISTING database mail settings?I set up a profile to use gMail, and it seems logical for me to export out the settings to a script, then run the script on my laptop, other servers, etc.There's no built in option, so I figured i'd ping the forum before i do it myself.by the way, my google-fu [i]is strong[/i], and there are example scripts where you fill in the blanks, examples how to set up dbmail, but i did not find anything that scripts out existing settings.[img]http://www.stormrage.com/SQLStuff/mail_gmail.gif[/img]

Adding new xml tag with a value takeen from another column

Posted: 28 Feb 2013 11:39 PM PST

I have a table with xml column named xmlvalue.let xml value in those column are like shown below<z><a><b>1</b><c>2</c></a><a><b>4</b><c>5</c>...so many tags similar to above..</a></z>I want to add a new node called <new>here value should betaken from another column</new> inside the <z><a> for every row in that column with <new> tag having a value which is taken from another column "abc" for that row.(column abc is not a xml column) which is in same table.How can i do it using xml query or sql query?Thank you

SSAS caching resulting in inaccurate results being returned

Posted: 29 Jan 2013 12:38 AM PST

I have a question about some weird behavior we're seeing in SSAS 2008 (SP3), and I was wondering anyone out there would be able to help me?Short Story:SSAS seems to be doing some level of caching on its own. However, this caching is resulting in inaccurate results to be returned in some queries for our business users. That is leading them to question the data, and lose confidence in the queries that they are running. I'm wondering if there are settings that we can change with SSAS to keep it from caching results like this so the business will see accurate data when running their queries.Long Story:We have a measure group set up with about 47 million rows in it that we are trying to query through Report Builder. We pulled in one amount from that measure group. We then pulled in an attribute from a coverage dimension. Everything is good here, and the results are based on the 47-million rows in our measure group.Next we pull in an attribute from a coverage-specific table (fire in this case). That fire table only contains 5 million rows. That table also is not a direct foreign key to the measure/fact table. This is a referenced dimension through the coverage table that I mentioned earlier. Anyway, pulling in that attribute from the fire table limits the number of rows coming back to only the 5-million rows in our measure that have an associated row in the fire table. Again, everything is working as expected.Then we add another attribute from another dimension. This pulls in just fine.Then we realize that we really didn't want that fire attribute added. So we removed the fire attribute from our query. However, our results didn't expand back to look at all 47-million rows. Instead, it is still limiting the result set to only the 5-million rows that have a relationship with the fire table, even through fire is no longer included in our query.I tried this multiple times with the same results. I then opened a new report builder session and tried to create the report from scratch. I did not pull in anything from fire this time, but the results were still limited to just the 5-million rows from fire.I copied the MDX from the query designer window and ran that in Management Studio. Same results (limited to only fire rows). I verified that there is no reference to the fire dimension anywhere in the MDX. Then I took that MDX and ran it on a different computer, and the results were still limited to just the 5-million rows from fire. And finally I asked a co-worker to do the query, and still the same results.I did a little internet searching, and I found a way to clear the SSAS cache. I ran that on the SSAS database, and then we got the correct results.We are not using proactive caching on any of our dimensions or measures.So, I'm wondering if there is a setting (or settings) I can change with SSAS so it will not cache results like it appears to be doing? And if not, do you have a recommendation on how often we should be clearing out the cache to try to prevent the business users from seeing situations like I described again? It is crucial that the business have confidence in the data and the results of their queries run against the cube!!!!Thanks a lot for any help anyone may be able to provide!!!

GRANT VIEW DEFINITION

Posted: 28 Feb 2013 06:50 PM PST

Hi allI am running the following on one of my DBs - I am using SSMS 2012 against SQL 2008R2GRANT VIEW Definition TO [XXX\XXX]When I check the securables for that user it doesn't list anything as having being granted and if I run sp_helpprotect there is nothing in there either. The command executes successfully.Any ideas?Thanks

Can't get Sp_create_plan_guide to execute successfully

Posted: 28 Feb 2013 07:04 PM PST

Hi,I am not a DBA. We have a stored procedure used by our app that is using inefficient query plans. The sproc needs rebuilding and has been rebuilt for an up-coming hot-fix but we need an interim work-around (which cannot include altering the sproc). Re-indexing and updateStats works sporadically but not reliably.As a last resort I am attempting to use a Plan Guide to force the sproc to use a specific execution plan extracted from when the sproc was functioning well. If I alter the sproc and simply add the OPTION (USE PLAN N'<XML>') to the problematic statement, the sproc is altered successfully, the sproc runs efficiently and the application runs well again.However, if I use Sp_create_plan_guide, I keep getting the below error even with the exact same plan xml that works by altering the sproc:[font="Courier New"]Error detected at line 3: Incorrect syntax near the keyword 'ASC'[/font]I think it relates to this part of the plan (there are a few similar occurrences but this is the first)[code="xml"] <DefinedValue> <ColumnReference Column="Expr1185" /> <ScalarOperator ScalarString="CASE WHEN [@SortDirection]=N''ASC'' THEN [DataBaseName].[dbo].[Delegate].[DelegateIdentity] ELSE NULL END"> <IF> <Condition> <ScalarOperator> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Column="@SortDirection" /> </Identifier> </ScalarOperator> <ScalarOperator> <Const ConstValue="N''ASC''" /> </ScalarOperator> </Compare> </ScalarOperator> </Condition> <Then> <ScalarOperator> <Identifier> <ColumnReference Database="[DataBaseName]" Schema="[dbo]" Table="[Delegate]" Column="DelegateIdentity" /> </Identifier> </ScalarOperator> </Then> <Else> <ScalarOperator> <Const ConstValue="NULL" /> </ScalarOperator> </Else> </IF> </ScalarOperator> </DefinedValue>[/code]which relates to this part of the sproc[code="sql"] ORDER BY CTE.LocationFullName ASC , CASE WHEN @SortDirection = N''ASC'' THEN CTE.DelegateIdentity END ASC , CASE WHEN @SortDirection <> N''ASC'' THEN CTE.DelegateIdentity END DESC[/code]However, if I go through and remove all the '' (2 single quotes) then it the error goes away but I get another error relating to another literal value in the script.If I change all occurrences of ''ASC'' to just ASC then the sproc Sp_create_plan_guide runs successfully and the plan guide is successfully mapped to the statement (as confirmed with profiler's PlanGuideSuccessful event). But, I am reluctant to use this in production because I have 'hacked' the xml plan which MS advise not to do.I am pretty sure the string I am passing to @hints is correct, that is, if I use PRINT with the same value it comes out as I would expect. However, I did exchange all single quotes for 2 x single quote in the plan xml that I retrieved from Profiler originally.Any advice or similar experiences? Let me know if you need the entire syntax I am using, it is quite large.Thanks in advance.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog