Tuesday, March 5, 2013

[SQL Server 2008 issues] transactions

[SQL Server 2008 issues] transactions

transactions

Posted: 04 Mar 2013 04:46 PM PST

how to update 2 tables in 2 different Db within same transaction ?what if 2 Db are in different server ?

Update and Insert Performance

Posted: 04 Mar 2013 04:55 PM PST

In Insert and Update, Multiple Index and Inculude columns is effect performance.

SQL 2008 enterprise to standard degradation

Posted: 03 Mar 2013 11:38 PM PST

Hi Team,I have request from client to degrade few servers due to cost of enterprise edition.Can you suggest me the plan and things to take care.My Plan is as below.1. Backup all the databases.2. Script logins and jobs.3. Dettach the user databases.4. Uninstall the enterprise edition.5. Install the standard edition.6. Attach databases.7. Run login and job scripts.Please suggest.

SQL SERVER database encryption.

Posted: 04 Mar 2013 07:22 AM PST

Hello All,I am in a bind here and hoping someone will be able to help me out in this regard.There is a database in our environment that is using database encryption. The database server cert file and servercerthkey is avialable but I cant find the password. I am totally confused, of all the materiel red online I cant seems to find the answer.how can I restore this database?All the material online explain how to do it will password. what is the password going to be. I don't know who created the password at the time of setting up the encryption, all I have is the database, backup and servercert file and servercertkey file. what are my option. Many thanks in this regard.Is it possible to change/reset the password?Thanks a lot.B

SSIS OLEDB source executing but not returning results

Posted: 04 Mar 2013 04:38 PM PST

Hi There,I have a query in a SSIS package. it has been running fine and returning required results to the destination, suddenly it runs and completes but does not return results.Took it out of the package and ran it on the server it does return results.any idea what could be causing this?it is a straigh forward query with only the period as a parameter.[code="sql"]Select Col1, Col2, Col3 From Table1 Where Period = ?[/code]Period is an int in this format 201302. I tried it as string still got no joy.Please help as the only other option I'm thinking of is putting it in a stored proc.Kind Regards

SQL Server 2008 R2 not starting after moving"model" database

Posted: 04 Mar 2013 10:16 AM PST

SQL Server 2008 R2 not starting after moving model database. Any ideas? Error: 945, Severity: 14, State: 2. Any ideas how to correct without reinstalling?Also Sql 2008 R2 will not start with trace -f -T3608 or single user mode -m. Need params to start SMS to move Model back.Need resolution please.

Copying from one column to another with truncation.

Posted: 04 Mar 2013 02:14 PM PST

Hi GuysI have a table called Mytable.In it are 2 columns, Description(50 Char) and Longdescription(200 char)I need to copy the content of longdescritption(data with more tha 50 characters) into my descritption table.I dont mind if it copys the first 50 characters of the data and truncates the rest.How do I acheive this ?Thanks in advance.A

date format 7/31/2013

Posted: 04 Mar 2013 03:52 AM PST

Hi friends,Im new to TSql -I have requirement to display date as 7/31/2013 but currently I'm getting it from DB as 2013-03-01 18:40:24.000.any help on this will he highly appreciated,ThanksDhananjay

Copy a big table (250G) form one server to another

Posted: 04 Mar 2013 04:56 AM PST

Hi,I need to copy a big table (about 250G, basically used to store images) that I have on a MSSQL Server to a MSSQL Server. I don't need to transform data but I don't if this command will worksINSERT INTO Temp SELECT * FROM [SourceServerName].DBName.dbo.TempThanks for your help

query performance

Posted: 04 Mar 2013 08:53 AM PST

I have a table with 6 million rows. The table has around 200 columns. The data can be sliced and diced by the user in a number of ways. Are there any tips of improving query performance other than adding indexes?Currently some of the queries take around 40 seconds.thanksKK

How to maintain two digit values in sp?

Posted: 15 Mar 2012 03:43 AM PDT

Hi friends,IF OBJECT_ID('Sp_Coded') IS NOT NULL BEGIN Drop procedure Sp_Coded EndgoCREATE PROCEDURE [dbo].[Sp_Coded] (@ParentTableName varchar(50), @ColumnName varchar(40), @CodeID int, @Number bigint, @MaxLength int, @SetID varchar(10), @Flag varchar(10), @SrcTablename varchar(100), @SrcCodeValueColumn varchar(100), @SrcDescColumn varchar(100), @SQL varchar(5000)) ASBEGIN DECLARE @MySql varchar(5000) IF ( @Flag = '2' ) BEGIN SET @MySql = 'SELECT Distinct 0 as PnxJurisID , ' + Cast(@CodeID as Varchar(15)) + ' as CodedID, B.' + Cast(@SrcCodeValueColumn as varchar(50)) + ' as SrcCodeValueRef, B.'+ CAST( @SrcCodeValueColumn as varchar(50)) + ' as SrcCodeValue, B.'+ CAST( @SrcDescColumn as varchar(50)) +' as SrcCodeDescr ' + ' From dbo.' + @ParentTableName + ' A left outer join SrcCodeMap on A.' + @ColumnName + ' = SrcCodeMap.SrcCodeValueRef and SrcCodeMap.CodeID = '+ Cast(@CodeID as Varchar(15)) + ' left outer join '+ @SrcTablename +' B on B.'+ @SrcCodeValueColumn +' = A.' + @ColumnName + ' WHERE SrcCodeMap.JurisID is Null AND A.' + @ColumnName + ' Is Not Null ' END else IF ( @Flag = '1' ) BEGIN SET @MySql = 'SELECT Distinct 0 as PnxJurisID , ' + Cast(@CodeID as Varchar(15)) + ' as CodedID, ' + @ParentTableName+ '.' + @ColumnName + ' as SrcCodeValueRef, CASE WHEN SCTABE.TECOD# IS NULL THEN LTRIM(RTRIM(' + @ParentTableName+ '.' + @ColumnName + ')) ELSE LTRIM(RTRIM(SCTABE.TECOD#)) END as SrcCodeValue, SCTABE.TEDESC as SrcCodeDescr ' + ' From dbo.' + @ParentTableName + ' left outer join SrcCodeMap on ' + @ParentTableName + '.' + @ColumnName + ' = SrcCodeMap.SrcCodeValueRef and SrcCodeMap.CodeID = '+ Cast(@CodeID as Varchar(15)) + ' left outer join SCTABE on rtrim(ltrim(SCTABE.TECOD#)) = rtrim(ltrim('+ @ParentTableName+ '.' + @ColumnName + ')) and SCTABE.TEMST# = ' + Cast( @SetID as Varchar(8)) + ' WHERE SrcCodeMap.JurisID is Null ' END else IF ( @Flag = '3' ) BEGIN SET @MySql = @SQL END print @MySql IF OBJECT_ID('tempSrcCodeDB', 'U') IS NOT NULL DROP TABLE tempSrcCodeDB; CREATE TABLE tempSrcCodeDB (JurisID int, CodedID varchar(15), SrcCodeValueRef Varchar(50), SrcCodeValue varchar(60), SrcCodeDescr varchar(60)) INSERT INTO tempSrcCodeDB (JurisID,CodedID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr) EXEC(@MySql) DECLARE @JurisID int, @CodedID varchar(15), @SrcCodeValueRef varchar(50), @SrcCodeDescr varchar(60), @SrcCodeValue varchar(40), @DecNumber bigint SET @DecNumber = @Number DECLARE MySql CURSOR FOR SELECT * FROM tempSrcCodeDB OPEN Mysql FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr WHILE @@FETCH_STATUS = 0 BEGIN IF ( (LEN(@SrcCodeValue) <= @MaxLength) and ((@SrcCodeValueRef) <> '') and ((@SrcCodeValueRef) <> null) ) BEGIN insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue) Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr, @SrcCodeValue) END else IF (((@SrcCodeValueRef) <> '') and ((@SrcCodeValueRef) is not null) ) BEGIN insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue, SysCodeValue) Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@DecNumber, @DecNumber) SET @DecNumber = @DecNumber + 1 END FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr END CLOSE Mysql DEALLOCATE MysqlENDThis is my stored procedure..here i have a parameter of variable @MaxLength where i pass the value like 2 or 3 or 4 and depends upon the need.if i pass 2, the @DecNumber would start from 10 and gets incremented(SET @DecNumber = @DecNumber + 1) up to the total row countso obviously if the row count exceeds 99, it would go to 3 digit values like 100,101,102 and so on...my problem is if the @maxLength is 2, at any cost it should maintain distinct two digit values in a sequence...like 0 to 99 and instead of 100 it could be A1 ,A2,A3.....A9, B1....B9,C1..C9 and so on...i am not asking that i really want this kinda sequence..it may be any kinda sequence only with 2 digits...I know this question may look odd..but this is my situation..so please help me friends...

SSRS Reporting assitance- consolidated report using 3 Stroed PROC

Posted: 04 Mar 2013 10:48 AM PST

Hi,I have 3 Stored Procedures 1) SP_JobPostingID,2)SP_WorkerOrder, and3) SP_WorkOrderRevisionNote- all the 3 have same number and signature of Columns, what I need to know is how develop an SSRS report using them combined in Microsoft Visual Studio.one way I can think of is creating 3 Datasets for the same Reportdata but wished to know if any other way out .ThanksDhananjay

Transaction log in Subscriber database for Transactional Replication

Posted: 04 Mar 2013 08:31 AM PST

I have created replication between two SQL Server 2008R2 databases and it has been functioning well since the initial setup 3 months ago. The publisher database is the back-end for an OLTP application with 50 users who are working constantly, making changes and add new information. The Subscriber database is used for reporting. Today I noticed that the Subscriber database was set to Full Recovery Mode with no transaction log backups. I panicked for a minute and took a quick look at the Transaction Log drive on the Subscriber server - it has 50% free space, 24 GB. I was amazed that I had been avoiding what normally would cause a drive-full issue.My question is this. Since replication is constantly making changes to the Subscriber database (updating various tables as they change in the Publisher database), why is the Subscriber log file not filling up and giving me grief since I don't run backups on the Subscriber database - and haven't done for the entire three months this has been running? Gail Shaw mentioned in another forum question while I was researching this, that 'Replication does not in any way use the Subscriber's log file'. I'm not sure that I understand the seeming contradiction been the updating going on in the Subscriber database, Full Recovery mode, no Transaction Log backups taking place and the Transaction Log not filling up.Can anyone enlighten me and/or point me to articles that might help me understand this more thoroughly??Thank you. Willem

Including additional columns in a unique clustered index

Posted: 04 Mar 2013 02:58 AM PST

I am creating an indexed view from a business unit table. The goal of the view is to pre-filter the unit list to only those supported by our division. The business hierarchy goes from unit to district to region, with unit # being unique on the table.However, we rarely ever use unit in our applications and instead focus on the district number (as per business requirements.) One of the most common queries performed is to get a distinct list of districts based upon region selections. Only occasionally would the unit # be included too.Should the region and district columns be included in the unique clustered index of the view? Or should they be limited to a separate nonclustered index? I'm leaning toward the latter, but if anyone has a good reason to include them in the clustered index, I'd love to hear it. :-)

sp_updatestats and the default sampling rate

Posted: 04 Mar 2013 08:51 AM PST

Using SQL Server 2008R2As my Production database approaching 500GB and potentially can grow up to 1TB in 2 years, issue with sp_updatestats (after re-indexing part of maint step) using the default sampling rate (which potentially can skew the performance) bothers me.It has been a discussion earlier at that link: http://www.sqlservercentral.com/Forums/Topic1310877-146-2.aspx, but I still confused about using sp_updatestatsCurrently I am performing the following steps during weekend maintenance:1. ALTER Database MyDBSET RECOVERY Simple,AUTO_CREATE_STATISTICS OFF,AUTO_UPDATE_STATISTICS OFF2. My index maintenance routine based on the following criteria:Currently I Reindex Clustered and Non-Clustered Indexes when avg_page_space_used_in_percent < 75 and avg_fragmentation_in_percent > 10 and page_count > 500.Of those selected, if Fragmentation <=30, than I reorganize those Indexes. If Fragmentation > 30, than I rebuild those Indexes. So at the end of the Reindex Maint I have Non-Clustered and Clustered Indexes either Rebuilt or Reorganized.3. Currently I am running the Update Statistics on a whole database after previous reindex step:[b]sp_updatestats[/b]Since sp_updatestats updates statistics by using the default sampling rate, it possibly can deteriorate all my indexes after running reindex routine.[b]3A.[/b] So I was thinking about "… update statistics for all tables which are defragged and don't run update statistics for all tables which are REBUILD"http://sqlserverpedia.com/wiki/Updating_StatisticsSo here is my logic for performing routine in [b]3A[/b]Select indexes which were "REORGANIZE"d (no statistics update) during reindex maintenance along with other indexes, where statistics were either not updated for the last, say, few weeks and run the following:UPDATE STATISTICS Table_Name (IndexName) WITH FULLSCAN, NORECOMPUTE against indexes selected above.By running above I will be able to update statistics without running sp_updatestats4. ALTER Database MyDBSET RECOVERY Full,AUTO_CREATE_STATISTICS ON,AUTO_UPDATE_STATISTICS ONPlease let me know if you have any comments, suggestions, recommendations on [b]step 3A[/b].It has been a suggestion earlier to run: USE dbname;EXEC sys.sp_MSforeachtable @command1 = N'UPDATE STATISTICS ? WITH FULLSCAN, COLUMNS;';But unfortunately it takes way to long on my Production Database, given the time frame I have for the weekend maintenance.

database metric

Posted: 04 Mar 2013 12:34 AM PST

Is it possible to produce any sort of database level performance metric in SQL Server. Let's say how many queries are being processed by that database? what's the response time. average response time or processing time. Anything that can help me track performance of a specific database on the SQL Server instance.thanks

How to uncommit the transaction and execute the next statement in the cursor?

Posted: 03 Mar 2013 10:17 PM PST

Hi Friends, [code="sql"]DECLARE @JurisID int, @CodedID int, @SrcCodeValueRef varchar(100), @SrcCodeDescr varchar(100), @SrcCodeValue varchar(100), @DecNumber bigintSET @DecNumber = @Numberdeclare @result varchar(50), @chars varchar(36), @IntFlag intSET @chars = N'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' DECLARE MySql CURSOR FOR SELECT * FROM #tempSrcCodeDB where SrcCodeValueRef is not null and SrcCodeValueRef not like '' and LEN(LTRIM(RTRIM(SrcCodeValue))) > @MaxLengthOPEN MysqlFETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescrWHILE @@FETCH_STATUS = 0 BEGIN IF ((LEN(@DecNumber)= @MaxLength ) and LEN(LTRIM(Rtrim(@SrcCodeValue))) > @MaxLength ) /* If Number is greater than the maxlength */ BEGIN insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue, SysCodeValue, VendorCode,CreatedDttm) Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@DecNumber, @DecNumber,@VendorCode,GETDATE()) set @DecNumber = @DecNumber - 1 GOTO FETCHNEXT ENDELSE BEGIN set @IntFlag = 0 WHILE (@IntFlag = 0) /* We used to generate random(alpha-numeric) values when we run out of Number */ BEGIN IF @MaxLength = 1 BEGIN SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) END IF @MaxLength = 2 BEGIN SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) END IF @MaxLength = 3 BEGIN SET @result = SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) + SUBSTRING(@chars, CAST((RAND() * LEN(@chars)) AS INT) + 1, 1) END IF NOT EXISTS(select * from SrcCodeMap where CodeID = @CodeID and LTRIM(RTRIM(PnxCodeValue)) = @result ) --IF (select COUNT(distinct PnxCodeValue) from SrcCodeMap where CodeID = @CodeID and LTRIM(RTRIM(PnxCodeValue)) = @result ) = 0 BEGIN insert into SrcCodeMap (JurisID, CodeID, SrcCodeValueRef, SrcCodeValue, SrcCodeDescr, PnxCodeValue, SysCodeValue, VendorCode,CreatedDttm) Values (@JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescr,@result, @result, @VendorCode,GETDATE()) GOTO FETCHNEXT END ELSE continue END ENDFETCHNEXT:FETCH NEXT FROM Mysql INTO @JurisID, @CodedID, @SrcCodeValueRef, @SrcCodeValue, @SrcCodeDescrENDprint @@ROWCOUNTCLOSE Mysql DEALLOCATE MysqlEND[/code] I have written this cursor(I showed you part of it) to get some distinct random values based on the length given by user (this cursor is called by a stored procedure). As you see, i declared a variable containing '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ' . So the function will generate random values from here. My problem is, if i have to generate random values of one char length, i can generate upto 36 different values...If the input given by the customer, I mean the input is always going to be coming from the select statement like "select column1 from table. " So if the count of the data exceeds more than 36, this cursor can't generate any more values of length 1. So the cursor is keep on looping and it is uncommitted...So i am not able to move to the next input given by the end user. So gimme your suggestions to avoid if any of the input is keep on looping due to this kinda problem....i want to uncommit it and move to the next statement. Please gimme any kinda your suggestion if you really think there might be another way to handle his. Any suggestions would be really appreciated.

Huge Transaction on Database with Merge Replication, Simple Recovery Model

Posted: 03 Mar 2013 11:41 PM PST

We have a database which normally has a 100GB transaction log.This database is a publisher in a merge replication with one subscriber.The transaction log has grown to 235GBWhen I query sys.databases for log_reuse_wait_desc I get "REPLICATION" for this database.We recently had a corruption issue (CHECKSUM ERROR) in this database, which was fixed last thursday by running DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS option (only 1 page was flagged as corrupt and business decided it was better to lose this than have to restore the database)The affected table is part of the publication.dbcc opentran with tableresults gives:<databasename>OPENTRANREPL_DIST_OLD_LSN (0:0:0)REPL_NONDIST_OLD_LSN (4582486:1093:1)Checking the transaction log: select [Current LSN],[Operation],[Transaction ID], Left([Description],20) from::fn_dblog('0x0045EC56:00000445:0001','0x0045EC56:00000445:0010')GivesCurrent LSN Operation Transaction ID (No column name)0045ec56:00000445:0001 LOP_BEGIN_XACT 0000:903b4a9b CheckDb;0x01050000000045ec56:00000445:0002 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0000045ec56:00000445:0003 LOP_LOCK_XACT 0000:903b4a9b 0045ec56:00000445:0004 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0020045ec56:00000445:0005 LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:0006 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0070045ec56:00000445:0007 LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:0008 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0070045ec56:00000445:0009 LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:000a LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0070045ec56:00000445:000b LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:000c LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0000045ec56:00000445:000d LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:000e LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:0010045ec56:00000445:000f LOP_HOBT_DELTA 0000:903b4a9b 0045ec56:00000445:0010 LOP_MODIFY_ROW 0000:903b4a9b Deallocated 0001:002As far as I understood, merge replication isn't supposed to have an impact on transaction log. The transaction log appears to be growing ever since.Can anyone tell me why this is happening?Thanks

Primary Key is not AutoGenerating

Posted: 04 Mar 2013 07:49 AM PST

I am new to SQL Server, but have exposure to databases on Mainframe & MS Access, but this is giving me a headache ...I am using VB2010 Express and used Project Add New Item to add a SQLCE 3.5 Database to my small program.I then created 2 Tables, called "Leagues" & "Teams", by right clicking on the Tables tab in Database Explorer.I have used the Solution Explorer to open the DataSet and add a Foreign Key. This is what I now have :[b]Table1 : Leagues[/b]ID - PK, AutoIncrement = True, 1, 1, DataType = System.Int32, Unique = TrueName - DataType = System.String[b]Table2 : Teams[/b]ID - PK, AutoIncrement = True, 1, 1, DataType = System.Int32, Unique = TrueName - DataType = System.StringLeagueID - FK, AutoIncrement = False, DataType = System.Int32, Unique = FalseHowever, if I right-click on the Table name in Database Explorer & choose Show Table Data, so I can enter a Test entry, it gets rejected with an error message telling me that the ID column cannot be Null ... so why is it not AutoIncrementing ?!? This happens if I try to enter a Test entry into either Table ... BTW, I have read on this site already that some people consider using UniqueKey as a bad thing, but in this case, it is a single user database, so won't be compromised and there is no chance of a lockout, it's just my own small program (that doesn't work !!!) ...

CLR Stored Procedures

Posted: 04 Mar 2013 05:52 AM PST

Hi All,I am trying to create a CLR stored procedure. What I was hoping for was a stored procedure that would understand what my object was. Ex: I have a MedicalRecord.dll that I developed. I want to be able to set a reference to my MedicalRecord.dll. How can this be done? I'm not simply looking for a substitute for a T-SQL insert/update statement. Thanks,Bill

Automated Index Maintenance

Posted: 04 Mar 2013 06:37 AM PST

I'm looking for a product to Automate our Index Maintenance. Currently we have a product that allows scheduling but uses its own proprietary scheduler. I'm looking for something that would allow for either a CLI or SQL Server Job to execute the analysis and maintenance.

Querying the ringbuffer target - only returning the first half of the data

Posted: 04 Mar 2013 06:25 AM PST

I am running the query below and seeing deadlocks from 2013-03-01 17:27:44.213 to 2013-03-03 06:51:22.317. It is currently 2013-03-04 15:20. You may say "Well then, there are no deadlocks after 6:51!" I wuold say that is true, except we record them in the error log and I know there are plenty. Also... If I wait a couple of minutes and run my query again, I will get deadlocks from 2013-03-01 17:30:42.153 to 2013-03-03 06:54:51.211. What is going on? Am I only able to pick up the first half (or some percent) of the ring buffer?[code="sql"]select XEventData.XEvent.value('@timestamp', 'datetime') AS DeadlockDateTime, CONVERT( xml, XEventData.XEvent.value('(data/value)[1]', 'varchar(max)')) as DeadlockGraphFROM (select CAST(target_data as xml) as TargetDatafrom sys.dm_xe_session_targets st WITH(NOLOCK)join sys.dm_xe_sessions s WITH(NOLOCK) on s.address = st.event_session_addresswhere name = 'system_health') AS DataCROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'OPTION(MAXDOP 1)[/code]

how to get list of databases and updte support group for them in SQL CMS

Posted: 04 Mar 2013 05:30 AM PST

i dont know what I am askingfor is possible or not so just want to give a try here...:-)I want to get a list of all databases and servers registered in SQL Central Management server and populate a sql table "A". I will have another table "B" with the database names and database supportgroups i.e, supportgroup1, supportgroup2 and supportgroup3.After that I want to create a job that looks for the newly created databases and updates the tabe-A or creates a new table if needed , with data supportgroups and if there is no support group associated with a database, I get an email to update the table with right support group.

Date Add in table

Posted: 04 Mar 2013 05:17 AM PST

HelloI need one help. I need to add missing date in the table so here is one sample on some data so please let me know how is it possible.[code="sql"]create table #x1(SchoolID int,CalendarDate datetime)insert into #x1 values (101,'2012-08-18')insert into #x1 values (101,'2012-08-19')insert into #x1 values (101,'2012-08-20')insert into #x1 values (101,'2012-08-21')insert into #x1 values (101,'2012-08-29')insert into #x1 values (101,'2012-08-30')insert into #x1 values (101,'2012-08-31')[/code]I got output as below[code="plain"]SchoolID CalendarDate101 2012-08-18 00:00:00.000101 2012-08-19 00:00:00.000101 2012-08-20 00:00:00.000101 2012-08-21 00:00:00.000101 2012-08-29 00:00:00.000101 2012-08-30 00:00:00.000101 2012-08-31 00:00:00.000[/code]But desired output is[code="plain"]SchoolID CalendarDate101 2012-08-18 00:00:00.000101 2012-08-19 00:00:00.000101 2012-08-20 00:00:00.000101 2012-08-21 00:00:00.000101 2012-08-22 00:00:00.000101 2012-08-23 00:00:00.000101 2012-08-24 00:00:00.000101 2012-08-25 00:00:00.000101 2012-08-26 00:00:00.000101 2012-08-27 00:00:00.000101 2012-08-28 00:00:00.000101 2012-08-29 00:00:00.000101 2012-08-30 00:00:00.000101 2012-08-31 00:00:00.000[/code]can anyone please tell me is it possible in SQL SERVER?

Left join a query result to a table

Posted: 04 Mar 2013 05:15 AM PST

In Table A I have a list of projects (tbprojects).In Table B I have a list of status updates for projects with a month and year columns (tbstatus).I want to create a query that will return all projects in the list and then show all status updates for a specific month and year.If aproject has no status update for that given month, i still want it to show up in the query result, but with simply "NULL" values for the fields from the status update table.How would I write a query like that?I tried this:[b]select * from tbprojects left join tbstatus on tbprojects.id = tbstatus.projectid where month = 2 and year = 2013[/b]But this only returns the projects from tbprojects that have a status update for the given month and year. I need all of the projects to appear in the result. How can i modify my query to do that?

Primary key or not to primary key, that is the question

Posted: 04 Mar 2013 02:58 AM PST

Yeah, bad taste on the subject but nonetheless, you're reading my post :). I'd like to pose a scenario and see what other developers are doing out there (or would recommend at least).We have most of our tables defined with a PRIMARY KEY constraint over a single column that is defined as UNIQUEIDENTIFIER with a default of NEWID(). This is never clustered unless the table is small. We use this in our child tables as the foreign key reference like so (excuse the pseudo table definitions):Table:ParentID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,Field VARCHAR(30) NOT NULL,Code INT NOT NULLStatus VARCHAR(3) NOT NULLTable:ChildSurrogateID INT IDENTITY(1, 1) NOT NULL, --has a unique clustered index definedID UNIQUEIDENTIFIER NOT NULL DEFAULT NEWID() PRIMARY KEY,Field1 VARCHAR(30) NOT NULL,ParentID UNIQUEIDENTIFIER NOT NULL (FK defined to Parent.ID)Obviously the primary key constraint maintains an index behind the scenes. When we query between the two tables it will look soemthing like this:SELECT P.ID, P.Field, P.Code, P.Status, C.ID, C.Field1FROM dbo.Parent AS P INNER JOIN dbo.Child AS C ON P.ID = C.ParentIDLooks simple right? This query should (and does for me) do an index seek using the primary key constraint. But it also generates an expensive key lookup (clustered) over the clustered index. The only way to resolve that is to add included columns in the primary key to resolve it.Yes... primary keys indeed *DO NOT* allow included columns to be defined. At this point I have one of two options:- Drop the PRIMARY KEY and simply make a unique index with the necessary included columns- Create a unique index over Parent.ID with the neccessary included columns. However this results in doubling the index storage required for the Parent.ID column.So the question is this, do you:- drop the primary key and create the unique index with included columns- duplicate the primary key and create the unique index with included columnsI'm leaning towards dropping the PK and creating the unique index myself. Thoughts?

Choose between two date fileds

Posted: 04 Mar 2013 04:39 AM PST

I am trying to finish a report in BIDS that will let the user choose between two fields of the choosing, not one I specified. I am having trouble with this. The field I am using is DD.DateDesc to pull the date. PLEASE HELP?select dimUser.EmpNo AS [User Number],dimUser.EmpFullName1 [User Name],activityname [Activity Name],CONVERT(VARCHAR(10), DD.DateDesc, 101) [Completion Date],MAX(AL.CECredits) [Contributing Credits],CER.CERegion_Name License,dimUser.OptEmp_Txt1 [License Type],dimUser.OptEmp_Txt2 [License Number],CASE RTRIM(LTRIM(V.VenName)) WHEN 'BayCare Education Services' THEN '50-201' WHEN 'Morton Plant Mease Health Care' THEN '50-1378' WHEN 'St. Josephs Hospital' THEN '50-1867' END ProviderId,AL.ApprovalCode [Approval Code],AO.txt1 [Credit Category],dimUser.PrimaryJobName [Primary Job], dimUser.PrimaryOrgName From factAttempt fact INNER JOIN dimActivity A ON A.ID = fact.ActivityIDINNER JOIN TBL_TMX_ActLinks AL ON AL.ActivityFK = A.ActivityFK AND AL.LinkType = 3 INNER JOIN dimUser ON dimUser.ID = fact.UserID INNER JOIN TBL_TMX_ActOpt AO ON AO.ActivityFK = A.ActivityFK INNER JOIN dimDate DD ON DD.DateID = fact.EndDtID INNER JOIN tblEmpCERegion ER ON ER.EmpCERegion_EmpFK = dimUser.EmpFK INNER JOIN CERegion CER ON CER.CERegion_PK = ER.EmpCERegion_CERegionFK AND CER.CERegion_Name LIKE '%-%' INNER JOIN factResourceVen AV ON AV.ActivityID = A.ID INNER JOIN dimVen AS V ON V.ID = AV.VenID left outer join TBL_TMX_ActCECC CECC on CECC.ActivityFK = A.ID left outer join CreditCatType cct on cct.CreditCatType_PK = CECC.CreditCatTypeFK where CER.CERegion_PK = 5 and RegistrationStatusID = '4'and fact.EndDtID >= '-1' group by dimUser.EmpFullName1, activityname, CER.CERegion_Name, V.VenName, AL.ApprovalCode, cct.CreditCatType_Name, dimUser.EmpNo, AO.txt1, dimUser.PrimaryJobName, dimUser.PrimaryOrgName, dimUser.EmpStartDt, dimUser.OptEmp_Txt1, dimUser.OptEmp_Txt2, dd.datedesc, RegistrationStatusID

Remove "SQL Server 2005 (64-bit)" after in-place upgrade to 2008 R2?

Posted: 04 Mar 2013 03:53 AM PST

Hi all,When upgrading SQL, we normally either detach/reattach or backup/restore. However, we have just been handed a server that had an "in-place" (?) upgrade performed from SQL 2005 to 2008 R2. This seems to have left SQL 2005 on the Programs List. In other words, if I go to Add/Remove programs, I see all the usual entries for 2008 (such as "[b]Microsoft SQL Server 2008 (64-bit)[/b]" and "[b]Microsoft SQL Server 2008 Native Client[/b]" and "[b]Microsoft SQL Server 2008 Browser[/b]", etc...).However, there are also two entries for 2005:[b]Microsoft SQL Server 2005 (64-bit)Microsoft SQL Server 2005 Backward compatibility[/b]Note that, in the services list, I [b][i]only [/i][/b]see services for the expected 2008 R2 instance and the users have had no problems connecting to and using the upgraded SQL DBs.So, [b][u]is it safe to remove the two SQL 2005 items listed in the add/remove programs list[/u][/b]? I ask because the files are apparently throwing up red flags when we run our patch/hotfix scans.Thanks!

performance issues in parsing multiselect string paramters in SSRS, pfb the code

Posted: 04 Mar 2013 02:46 AM PST

i am having trouble in parsing organisation name around(2000) and getting data doing filteration on these names - please note that organisation and organisation_id are not one to one mapped, need distinct organisations in the parameters drop down and all id's for calculation, for which i am putting where condition on organisation names.select vd.Month, vd.month_No, vd.Year, vd.Week, vd.Date, vd.Organisation, vd.[Business Line], vd.[Sales Executive], count(distinct vd.CSTMR_KEY)[Total no of Lines], ( case when SUM(vd.[Total Amount Due])>0 then COUNT(distinct vd.CSTMR_KEY) else 0 end )[Total No Of Unpaid Lines], SUM(vd.[Total Amount Due])[Total Amount Due] from IV_DBT_RPT_FR_ORGNSTN VD where (vd.Date>=@FromDate and vd.Date<=@ToDate)AND VD.Organisation IN (@Organisation)AND VD.[Business Line] IN (@BusinessLine)AND VD.[Sales Executive] IN (@SalesExecutive)group by vd.AR_BHVR_KEY, vd.[Business Line], vd.C, vd.CNTRCT_DD, vd.CSTMR_KEY, VD.Year, vd.Date, vd.month_No, vd.Month, VD.Week, vd.Organisation, vd.[Sales Executive]--order by VD.Date

Install SQL Server 2008 R2 on new PC

Posted: 04 Mar 2013 02:54 AM PST

I replaced my PC with one that has windows 7 (Home Edition). I removed SQL Server 2008 R2 Developer application from the old PC and attempt to install the application on the new one, using the original CD supplied with my original purchase.It seems that I cannot install this version on the new PC without applying Service Pack 3. However, I can't apply Service Pack 3 until I get it installed.Can someone tell me what I am missing?Thanks in advance.

(Could not find stored procedure 'msdb.dbo.sp_dts_getfolder'. - sql 2005 - &gt; 2008 R2

Posted: 29 Aug 2011 09:12 AM PDT

I have a package on 2008r2 and job on 2005 pointing to the package on 2008r2. When i run the job i get the following message 'Description: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80040E14 (Could not find stored procedure 'msdb.dbo.sp_dts_getfolder'.). Source"..Does anyone know how to fix this?

Accessing Linked Server using PowerPivot

Posted: 04 Mar 2013 01:42 AM PST

I am completely at a loss. Does anyone know the steps in connecting to a Linked server using Excel?Thanks

Question on Replication..

Posted: 04 Mar 2013 12:39 AM PST

Can I setup replications between two disconnected networks..?? For example..my company is a Web Application Development company and they sell their products through out the country to different clients.. Clients often use own web server to host their applications.. Some times they use their own.. This is the process. So our new requirement is replicating our database to a client in differnt location of the country... We cannot give access to the client to use our main database. But in order to do replication the subscriber should be able to connect to the publisher??? I am confused. Can I set up replication between two disconnected servers.???

Searching for Solution

Posted: 28 Feb 2013 04:26 PM PST

Hello All, I have one query (it may sound stupid though). We have one SQL Server 2008 (Microsoft SQL Server 2008 (SP2) - 10.0.4266.0 (X64)) and on my machine I've installed the client tools. We have some process where we create soem bulky temp tables after doing some heavy processing on sybase and thereafter through a java code the data in those temp tables in Sybase are transferred to SQL batchwise. I think this is not safe and efficient strategy and somewhat time consuming and also feel that it should be handled using SSIS packages (?). Can anyone suggest on that ?Secondly, if creating SSIS package is good option then will I have to uninstall the SQL client on my machine and install the developer edition (with business studio) ? Is there license requirement (we have license for SQL Server Enterprise Edition) ?

SELECT list block with CASE?

Posted: 03 Mar 2013 09:49 PM PST

is something like this possible?SELECTCASE WHEN (@X = 1) THEN COLUMN1, COLUMN2, COLUMN3ELSE COLUMN4, COLUMN5, COLUMN6,ENDFROM TABLE1

An issue with my Execution plan

Posted: 03 Mar 2013 09:27 PM PST

I have a view. I made some changes (included few CASEs in SELECT list for a business requirement)i did a relative cost check between these old and new script with estimated and actual execution plans.it was 50-50%. so, i conclude that i didnt disturb my query performance.But, the perfo. went bad.so, where am i wrong.Help me pls.Appreciating your helps always.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog