Tuesday, March 19, 2013

[SQL Server 2008 issues] select first alphabet

[SQL Server 2008 issues] select first alphabet

select first alphabet

Posted: 18 Mar 2013 05:27 PM PDT

i have a column in my table which has values like 'value Based', 'value Discount', 'Percentage Discount'.I want to extract first letter from these words as 'VB', 'VD', 'PD'.How to achieve this?

EXEC sp_MSforeachdb @command

Posted: 18 Mar 2013 05:00 PM PDT

Hi All,Apology i'm quite new to commands.I'm trying to loop the below SQL against all the DBs in the instance.But getting tthe below error:Msg 102, Level 15, State 1, Line 11Incorrect syntax near ' +ob.name +'.Anyone can help?thanks!DECLARE @command varchar(1000) SELECT @command = 'USE [?] DECLARE @dbid int SELECT @dbid = DB_ID() SELECT name from sys.databases where database_id=@dbid; SELECT db.name dbname, ps.OBJECT_ID,ob.name table_name, ps.index_id,b.name index_name, ps.page_count , ps.avg_fragmentation_in_percent, CASE when ps.avg_fragmentation_in_percent > 10 then 'ALTER INDEX "' + b.name + '" ON ' +ob.name +' REORGANIZE' END AS 'action_to_take' FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS ps, sys.indexes as b, sys.objects as ob, sys.databases as db where ps.OBJECT_ID = b.OBJECT_ID and ps.object_id=ob.object_id AND ps.index_id = b.index_id and ps.database_id = DB_ID() and ps.database_id=db.database_id and b.name is not null and ps.page_count>1000 and ps.avg_fragmentation_in_percent > 10 ORDER BY ps.OBJECT_ID' EXEC sp_MSforeachdb @command

How to purge Transaction Log

Posted: 18 Mar 2013 05:17 PM PDT

Hi,I tried many thing to purge transaction log as suggested on google,But its size does not reduce.Plz Help.How to do it?

The log for database is not available; Database was shutdown due to error 9001 in routine 'XdesRMFull::Commit'.

Posted: 04 May 2011 07:39 AM PDT

Testing and Production db's have lost connection to the translog 2 days in a row now. Today, my prod db was marked suspect after the issue - SCARY. The other db's did not lose connection. Possibly because there was no activity at that moment. No errors in SQL log, only windows. Server Resources were not necessarily be hammered. I will be scouring the web, but wanted to reach out to all of you as well. See info and errors below.Plenty of available drive space for Log, db, and tempdb partitions. 144gb RAMSQL Server 2008 SP1; Enterprise (64-bit)OS: Win Server 2008 R2 Enterprise[b]Win app logs:[/b]error1- LogWriter: Operating system error 21(The device is not ready.) encountered.error2 - The log for database (testing) is not available. Check the event log for related error messages. Resolve any errors and restart the database.info mess3- Database was shutdown due to error 9001 in routine 'XdesRMFull::Commit'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.[b]2 seconds later prod db goes down:[u][/u][/b]error4- The log for database is not available. Check the event log for related error messages. Resolve any errors and restart the database.error5 - During undoing of a logged operation in database, an error occurred at log record ID (86400:39070:17). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.error6 - fcb::close-flush: Operating system error (null) encountered.error7 - An error occurred during recovery, preventing the database (PRODUCTION :w00t:) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.info mess8 -CHECKDB for database finished without errors on 2011-03-14 12:12:41.503 (local time). This is an informational message only; no user action is required.

Insertion in table

Posted: 17 Mar 2013 09:28 PM PDT

HI,This is the table structure and script below:CREATE TABLE [dbo].[GV_Booklet]( [BookletId] [int] IDENTITY(1,1) NOT NULL, [LeafCount] [nchar](10) NULL, [Denomination] [int] NULL, [VoucherTypeId] [int] NOT NULL, [VoucherNo] [varchar](20) NOT NULL, [Quantity] [int] NULL, [CreateDate] [datetime] NULL, [ModifyDate] [datetime] NULL, [ExpiryDate] [datetime] NULL, [UserId] [int] NULL, [VoucherStatusId] [int] NOT NULL, [TransactionID] [varchar](20) NOT NULL, CONSTRAINT [PK_GV_Booklet] PRIMARY KEY CLUSTERED ( [BookletId] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Script:Declare @vouchertype varchar(20) = 'Percentage Discount', @denomination int, @userid int, @voucherstatus int, @transactionID varchar(20), @quantity int = 2, @count int =1, @innercount int =1, @leaf int =10, @max intWHILE @count <= @quantity BEGIN WHILE @innercount < @leaf BEGIN SET @max = (select vouchervaluebased from gv_maxvouchervalue) SET @transactionID = (select vouchertransactionID from gv_maxvouchervalue) INSERT INTO GV_Booklet VALUES ( @leaf, 100, 1, 'VB' + Replace( Str( @max+1, 7), ' ', '0') , @quantity, GETDATE(), GETDATE(), DATEADD(MM,6,GETDATE()), 1, 1, 'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0') ) SET @innercount = @innercount + 1 UPDATE gv_maxvouchervalue SET vouchervaluebased = @max + 1 END SET @innercount = 1 SET @count = @count + 1 ENDUPDATE gv_maxvouchervalueSET vouchertransactionID = @transactionID + 1select * from GV_BookletNow let me explain you guys the scenario-- This query is for a voucher booklet. If the quantity is 2 and leaf ( describing pages in a booklet) is 10 then 20 unique vouchersno should get generated with 2 unique Transactionno as quantity of booklet is 2. How to achieve this?plz help

Alter numeric(12,6) to numeric(19,4) - is it metadata only or is it recreate table?

Posted: 18 Mar 2013 12:23 AM PDT

Would be most grateful for confirmation/infirmation of theory below:Changing an existing table column from NUMERIC (12,6) to NUMERIC (19,4) should be a metadata change only (in-place - no need to dump the table to tempdb and recreate) as the column size remains the same - 9 bytes. It will require "examination" of all rows but not a recreate.This is extremely important as I intend to alter a table with 1 billion rows.TIA

existing column Level encryption in sql server2008

Posted: 18 Mar 2013 04:32 PM PDT

Hi, how to set the column level encyption in sql server 2008 in production enviroment without drop existing column and what are method available for encrypt the data in sql server 2008 standard edition.

2008 Central Management Server - Schedule Multi Server Query?

Posted: 08 Oct 2010 12:38 AM PDT

Hi Everyone - I have a 2008 Central Management Server that we're able to run queries from against all our SQL servers. I'm wondering if there's a way to do this via the command line or a job so the queries can be automated and rolled up into reporting services reports. Any suggestions?

ROWLOCK

Posted: 18 Mar 2013 05:03 PM PDT

CREATE TABLE [dbo].[TESTROWLOCK]( [ID] [int] NULL, [NAME] [varchar](100) NULL, [SURNAME] [varchar](200) NULL) ON [PRIMARY]GO[b]TAB -1 [/b]I m trying BEGIN TRANSELECT ID FROM TESTROWLOCK WITH(ROWLOCK) WHERE ID=2[b]TAB-2 [/b]SELECT ID FROM TESTROWLOCK WHERE ID=2I want to ask that tab-2 select is give result but tab-1 is not commited why rowlock hint does not lock that rows

reorganize index

Posted: 18 Mar 2013 05:16 PM PDT

Hi Guys,I'm going to reorganize indexes with > 10%.Is there a need to update statistics after the re-org?Understand that if it's a rebuild, SQL server will automatically update the stats.My question is, does re-org need a update statistics? Is the old statistics still accurate after re-org?In Micrsoft Note:Operations such as rebuilding, defragmenting, or reorganizing an index do not change the distribution of data. Therefore, you do not need to update statistics after performing ALTER INDEX REBUILD, DBCC REINDEX, DBCC INDEXDEFRAG, or ALTER INDEX REORGANIZE operations. The query optimizer updates statistics when you rebuild an index on a table or view with ALTER INDEX REBUILD or DBCC DBREINDEX, however; this statistics update is a byproduct of re-creating the index. The query optimizer does not update statistics after DBCC INDEXDEFRAG or ALTER INDEX REORGANIZE operations. thanks

merge statement insert issue

Posted: 18 Mar 2013 04:56 PM PDT

iam using merge statement in my proc.target table have lineid primary key.if i get not matched rescords from source table.below error message is displayedMsg 2627, Level 14, State 1, Line 7Violation of PRIMARY KEY constraint 'PK__t1__32489DA531F75A1E'. Cannot insert duplicate key in object 'dbo.t1'.The statement has been terminated.i given example code below create table t1(id varchar(10),ids int ,sal int,updatedDate datetime,lineId int primary key)insert into t1select 'r1',1,10,getdate(),1union allselect 'r1',3,30,getdate(),2union allselect 'r1',4,40,getdate(),3union allselect 'r1',5,50,getdate(),4union allselect 'r2',1,100,getdate(),5union allselect 'r2',3,200,getdate(),6----------------create table t2(id varchar(10),ids int ,sal int )insert into t2select 'r1',1,110union allselect 'r1',3,30union allselect 'r1',40,400union allselect 'r1',55,550union allselect 'r2',1,101------declare @maxs intselect @maxs=max(lineId) from t1-----------------------------------DECLARE @T TABLE(id varchar(10),ids int);MERGE t1 AS TUSING t2 AS SON t.id = s.id and t.ids = s.ids --WHEN NOT MATCHED BY TARGET -- THEN INSERT VALUES (s.id, s.ids, s.sal,getdate()-1)WHEN MATCHED AND (t.sal != s.sal) THEN --Row exists and data is different UPDATE SET t.sal = s.sal, t.updatedDate=getdate()WHEN NOT MATCHED BY TARGET THEN INSERT(id,ids,sal,updateddate,lineid)VALUES (s.id, s.ids, s.sal,getdate()-1,@maxs+1); how to insert primary key columns.(inserts new records based on maximan lineid+1 )note : here i dont add identity constraint for that line id column.can you give me suitable solution

Print error message

Posted: 18 Mar 2013 12:45 AM PDT

Hi, here is the script below:In this script I am matching voucher type and according to that I am inserting the data in the table. But when I write wrong voucher type script keeps executing so I want to print to stops the execution of the script and prints a error message if voucher type is not matched.Declare @vouchertype varchar(20) = 'Value Based, @denomination int, @userid int, @voucherstatus int, @transactionID varchar(20), @quantity int = 1, @count int =1, @innercount int =1, @leaf int =10, @max intWHILE @count <= @quantity BEGIN WHILE @innercount <= @leaf BEGIN SET @max = (select vouchervaluebased from gv_maxvouchervalue) SET @transactionID = (select vouchertransactionID from gv_maxvouchervalue) if @vouchertype = (select VoucherType from GV_VoucherType where VoucherType = 'Value Based') BEGIN INSERT INTO GV_Booklet VALUES ( @leaf, 500, 1, 'VB' + Replace( Str( @max+1, 7), ' ', '0') , @quantity, GETDATE(), GETDATE(), DATEADD(MM,6,GETDATE()), 1, 1, 'TRN' + Replace( Str( @transactionID + 1, 7), ' ', '0') ) SET @innercount = @innercount + 1 UPDATE gv_maxvouchervalue SET vouchervaluebased = @max + 1 END END SET @innercount = 1 SET @count = @count + 1 UPDATE gv_maxvouchervalue SET vouchertransactionID = @transactionID + 1 ENDselect * from GV_Booklet

Few tricky Questions about SQL Server 2008 that I came across...

Posted: 10 Jan 2012 06:31 AM PST

Few Questions about SQL Server 2008 that I came across. [b]Q: Which of the following allows for the truncation of a transaction log?[/b]A. Transaction logs are truncated when you use SQL Profiler.B. Transaction logs are truncated when you use the following syntax: truncate table AC. Transaction logs are truncated when you backup your database.D. You can not backup transaction log.[b]Q: Which of the following statements about unique Constraints are TRUE?[/b]A. You can have only one unique constraint applied to a table.B. Unique constraints can be used to establish a link between two tables.C. You can only have one column assigned to a unique constraint.D. If a primary key was already assigned to a table, the unique constraint would identify a special column used for indexing, [b]Q: Which of the following is an improved performance feature that uses bitmap filters for selecting minimal rows with queries?[/b]A. Table partitionsB. Star JoinC. Where clauseD. Outer Join[b]Q: Using a join in your select statement instead of a where clause is more efficient because the join pulls less data into your working memory.[/b]A. TrueB. False[b]Q: Full text indexes can be backed up and restored with the rest of the database due to the ...[/b]A. ...new index organization systemB. ...overall size of the index getting smaller.C. ...index becoming a part of the transaction logs.D. ...indexes no longer being stored outside of the database.[b]Q. Which of the following statements, if used for creating a new login id, will result in an error?[/b]A. CREATE LOGIN [HumanResources/JohnSmith] FROM WINDOWSB. CREATE LOGIN AndrewWalters WITH PASSWORD '!^%&7', DEFAULT DATABASE = HRC. CREATE LOGIN John WITH PASSWORD '*&!@*&'D. CREATE LOGIN [HumanResources/Jim] FROM WINDOWS, DEFAULT DATABASE=CRM[b]Q. Which of the following statements can be used to provide sorted result set based in the CustomerName column?[/b]A. SELECT ROW_NUMBER() AS RN, CustomerName from Customers order by CustomerNameB. SELECT ROW_NUMBER() OVER (ORDER BY CustomerName) AS RN, CustomerName FROM CustomersC. SELECT ROW_NUMBER() (ORDER BY CustomerName) AS RN, from CustomersD. SELECT ROW_NUMBER() AS RN, CustomerName from Customers

Trace - to Capture all or most queries

Posted: 18 Mar 2013 01:49 AM PDT

A. Are black box and defaula traces same?B. Is it possible to capture all the queires in the black/default trace, if it does not degrade sql performance.

SSRS 2008R2 showing counties (not just states)

Posted: 08 Mar 2013 01:53 PM PST

I have a hopefully simple question. SSRS 2008R2 can do maps by state just fine... is there an easy way to get City>County>State rollup data somewhere or get shapefiles for Counties in the US? I'm working on a database for someone that would be infinitely more useful if I could show like a heat map for sales by county across the country... there's a really cool map [url=http://www.mssqltips.com/sqlservertip/2552/creating-an-ssrs-map-report-with-data-pinpoints/]here[/url] that shows the county lines in it... and that's the part I want - the Counties. The granularity of the data I have is not too good, so county-level or so is about right.Is there an easy way to create a map like that? (like a color-coded map from election night, but a county-by-county instead of state-by-state?) If so, how? And where would I get the shapefiles for the counties?Thanks!Pieter

entity framework database

Posted: 18 Mar 2013 10:38 AM PDT

We are going to develop a new .net application that we think we may use entity framework model.And as I understand it can generate code like insert, update but not use stored procedures.Can anyone recommend articles or tutorial of how to develop the model and also database development cycle. and pros and cons by using insert, update in code in this model instead of using stored proceduresThanks

Adding a min, max and avg column

Posted: 18 Mar 2013 09:11 AM PDT

I have a table like so.Table A [Job Lot] [Job ID] [Child Job ID] [Time Completed] [Order of Jobs Done] 1 11 100 10 Pos 1 1 11 101 20 Pos 2 1 11 103 30 Pos 3 2 12 101 30 Pos 1 2 12 105 10 Pos 3Each Child Job ID represents a delivery to a particular site like New York, Washington or LA each week.So I need to work work out say for Child ID 101 what is the average postion . I see in JOB ID 11 it has Child Job ID 100 in POS 2 and in JOB ID 12 it is in POS 1, so the min is POS1 , the max is POS2, and the average is POS 1.5. So what is the spread of CHILD JOB ID and position is the real question , so out of 4 CHILD JOB IDS of say 101 what is the spread, is it always usuallt at postion 1.For CHILD JOB ID 10 , I want the lowest rank of Position 0 , the highest rank value of Position 3 and the Average vlaue not considering any null values and only divide by number of non null vlaues. In this case the the average rank would be Postion 2.So I need to add 3 columns to store these values ? How can I do this in one query ? Do I need to pivot the data first ?SELECT [JOB LOT],[CHILD JOB ID], MIN([ORDER OF JOBS DONE])AS [MIN],MAX([ORDE OF JOBS DONE])AS [MAX] FROM TABLE A WHERE [ORDER OF JOBS DONE] IS NOT NULL group by [JOB LOT],[ORDER OF JOBS DONE]

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.

BombProof Data Import from Excel ?

Posted: 18 Mar 2013 06:22 AM PDT

I have a very large spreadsheet of 125,000 rows, 200 columns, some of which contain a lot of text. I have tried every method I can think of to import the data into SQL such as converting to .csv, tab delimited text, xls with just 60000 rows ... I keep getting hung up with data truncation or something, even when I tell the Import wizard to ignore truncation. and my SQL table has all varchar(max) columns.Is there some foolproof way force the data into a sql table, either predfined or created on the fly, then I can worry about what the data looks like after it's in SQL ? I tried BULK INSERT 2 and get non-useful errors and just get the column headers.I installed the Office 12.0 OLE DB Provider, but get errors when trying to use it with a .XLSX file .... very frustrating 6 hours. I have a SQL 2008 (R0) server and SQL 2005 servers to work with.Suggestions ?

deadlock graph no statement information

Posted: 18 Mar 2013 08:03 AM PDT

In a deadlock graph, hovering over the oval for the victim only shows "Statement:". The other oval shows Statement: followed by the stored procedure that was executed.Does anyone know why the victim oval isn't displaying the statement information?Microsoft SQL Server 2008 R2 SP2 Enterprise Edition (64-bit)

sql agent job output file to different server has error

Posted: 18 Mar 2013 03:47 AM PDT

I have a SQL agent job that selects from a table and using the advanced options I can send the results to a test file. It works fine. But I'd like to send the file to a different server and am getting an error: "Executed as user: MGH\sqlagent. Unable to open Step output file. The step succeeded."I added permission for the sql agent service account on the other server. I'm using output path like: \\servername\foldername\filename.txtIs that syntax correct? Am I missing some permission on the other server?Thanks very much for any help.

Static port on named instance in a cluster.

Posted: 18 Mar 2013 05:38 AM PDT

I have a two node SQL cluster (2008R2). Each node on the cluster hosts a named instance. I am having a problem upgrading my VMWare vCenter to 5.1. Here is an article I found about the issue and the resolution. [url]http://kb.vmware.com/kb/2039092[/url]My question is can I make this change without screwing up my cluster? Will it have ill effect duing a failover event? If I can make this change what issues might I run into later since I have switched from dynamic to static ports? Can I make this change on only one named instand and not the other?

query help

Posted: 08 Mar 2013 03:11 AM PST

Hi All,can any one help on below query declare @tbl table (TBLID int identity(1,1),ID int,patientName varchar(10),age int,city varchar(100),Mobile int)insert @tbl(ID,patientName,age,city,Mobile) select 1,'Ramesh',20,'HYD',12345678 union all select 1,'Ramesh new',20,'HYDERABAD ' ,12345678 union all select 1,'Ramesh new',20,'HYDERABAD ' ,87654321 select * from @tbl TBLID ID patientName age city Mobile1 1 Ramesh 20 HYD 123456782 1 Ramesh 24 HYD 123456783 1 Ramesh new 20 HYDERABAD 87654321i want output as mentioned below format which columns data got changed Columns OLDDATA NEWDATApatientName Ramesh Ramesh newCity HYD HYDERABAD Mobile 12345678 87654321please prepare any dynamic query

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?

Help with XML Explicit and nesting

Posted: 18 Mar 2013 03:30 AM PDT

Hello,I currently have a very basic SQL statement to dump my results out in XML format:SELECT 1 AS tag, NULL AS parent, BookingID as [Event!1!BookingID!cdata], WebTitle as [Event!1!EventName!cdata], Room AS [Event!1!Room!Element], Audience as [Event!1!Audience!cdata] FROM MyEvents ORDER BY TimeBookingStart FOR XML EXPLICITThe resulting XML looks like this:<Event> <BookingID> <![CDATA[14957]]> </BookingID> <WebTitle> <![CDATA[This is the title]]> </WebTitle> <Room> Room 1255 </Room> <Audience> <![CDATA[Private]]> </Audience></Event>Now I would like to add a new element for EventType. There can be more than one EventType specified per <Event>. Can someone show me how to add an additional element that may include several distinct values? For example, booking Id 14957 has two records in the database each with a different eventtype value.Thank you in advance.

How to Zip a folder with files using Execute process task IN ssis

Posted: 17 Mar 2013 11:07 PM PDT

Hi,i need to zip a file in a folder using SSIS , so i tried with "Execute Process Task".I have a problem there what should i give in Executables , Arguments,Working directory options inExecute process taskThanks in Advance:-)

Differential backups being blocked, CHECKPOINT command

Posted: 18 Mar 2013 03:04 AM PDT

All my differential backups are being blocked by a SPID which has a command of CHECKPOINT.Is this something to be concerned about ? If so how would one go about resolving it?Many Thanks

Top N makes query run faster

Posted: 17 Mar 2013 11:13 PM PDT

I have a select query which brings back 35k rows. it runs in approx 5 seconds.I moved this to a new server and it runs for around an hour before i give up and kill the process.I put in select top 35000 .......... at the start of the query and i can get it to run in just under 4 minutes.The query runs across two servers to fetch the data using a left outer join to pull back everything from server 1 regardless of a match on server 2Where do i start looking to find the bottle neck ?I've attached the plan.

First and Last number

Posted: 17 Mar 2013 10:14 PM PDT

I have table in which i have data like - Columns are VoucherNo and TransactionIDPD0000021 TRN0000003PD0000022 TRN0000003PD0000023 TRN0000003PD0000024 TRN0000003PD0000025 TRN0000003PD0000026 TRN0000003PD0000027 TRN0000003PD0000028 TRN0000003PD0000029 TRN0000003PD0000030 TRN0000003VB0000001 TRN0000001VB0000002 TRN0000001VB0000003 TRN0000001VB0000004 TRN0000001VB0000005 TRN0000001VB0000006 TRN0000001VB0000007 TRN0000001VB0000008 TRN0000001VB0000009 TRN0000001VB0000010 TRN0000001Now here there are no of vouchersno for a particular TransactionID. I want first and last voucher for every transactionID e.g. For TRN0000003 i want PD0000021 and PD0000030

SQL Server Database Error

Posted: 17 Mar 2013 10:29 PM PDT

Hi Experts,Here is an error that has just posted in the system center operation manager(SCOM).Event ID: 9511 from Source Office Server Search on agent computer server_namehas triggered this Alert Description : Errors occurred during database operation,please make sure the SQL server is running and there is sufficient disk space available on the database drives.Context: Application 'database_name', Catalog 'Portal_Content'summary:In a search-enabled server farm, the index server periodically writes to or reads from the SQL Server database. This alert indicates that an unexpected SQL Server database error occurred while the Office SharePoint Server Search (OSearch) service that is running on the index server attempted to communicate with the database.i will appreciate your efforts.Regards.shafiq

Full Text Search with multiple keyword or Text Phrase

Posted: 17 Mar 2013 11:26 PM PDT

Hi All,Recently, we had a requirement to implement keyword based search & user can enter only one "word" means one keyword. So, I used LIKE 'keyword%. & It was working fine.But now user can enter a keyword or keywords or any text phrase. So, I decided to use Full Text Search & implemented FTS. But now there is an issue. Desired Results are not coming.I am not able to use CONTAINS because it is giving error due stopwords. So, I am using FREETEXT.I just consider a very simple common example.E.g: 1. suppose user enter a Text "mobile" & this returns 1000 rows(this count is just an example. My query will return several thousands of rows).2. Now, If I change the text to "samsung mobile" then it should return those results which contain "samsung" and "mobile" both keywords.But FREETEXT returning all those rows which contains either "samsung" or "mobile" or both.3. Now, I again change search to "samsung mobile for sale in delhi". The result set should be narrow. But this is not happening. As you see, this search text contains stopwords, so I am not able to use "CONTAINS()", so I used FREETEXT().I have read several articles on FTS, but i didn't find the solution of this problem.Please provide me some guidance to sort this issue.
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog