Wednesday, May 8, 2013

[SQL Server 2008 issues] Which Stored Sproc is causing Performance Issues

[SQL Server 2008 issues] Which Stored Sproc is causing Performance Issues

Which Stored Sproc is causing Performance Issues

Posted: 07 May 2013 06:39 PM PDT

I don't have a problem at the the mo. But was wandering after making a fairly large deployment of multiple sprocs/views.Is there an easy way of finding out which sproc/view is grinding the system to a halt.DBCC Opentran (to see the longest running transaction)Dbcc INPUTBUFFER(SPID)SELECT * from SYS.sysprocesses where blocked <> 0(find the top block).All very clumpy and slow is there a better solution.Am i missing something.

Run-time error "-2147467259 (80004005):

Posted: 07 May 2013 06:20 PM PDT

heloo dear ..i am facing this error from last 4 days ...... please give me solution .Run-time error "-2147467259 (80004005):[Microsoft][ODBC Driver Manager]Data source name not found and no default driver specifiedONE Shah

BEGIN TRY - Catch not working

Posted: 07 May 2013 07:59 AM PDT

1) Run job say TestJob2) Now execute below Begin try - catch for already running job and you will see below query error outany help?BEGIN TRY EXEC msdb.dbo.sp_start_job N'TestJob'END TRYBEGIN CATCHSELECT ERROR_MESSAGE()END CATCH

INDEXING

Posted: 07 May 2013 10:36 AM PDT

Hello,i have maybe stupid questionwhy sometimes the SELECT use Noncluster index instead cluster index I thought that still use PK like primary, if there is value what I searched, but if I created new one just with the searched value is faster. Does someone any good explain about background indexing and using it?[quote]CREATE NONCLUSTERED INDEX [IX_TB_INSPECTION_DETAIL] ON [dbo].[TB_INSPECTION_DETAIL]( [ORDER_DATE] ASC, [COMMIT_NO] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GO[/quote][quote]ALTER TABLE [dbo].[TB_INSPECTION_DETAIL] ADD CONSTRAINT [PK_TB_INSPECTION_DETAIL] PRIMARY KEY CLUSTERED ( [LINE_CODE] ASC, [ORDER_DATE] ASC, [COMMIT_NO] ASC, [BODY_NO] ASC, [ITEM_CODE] ASC, [DEFECT_CODE] ASC, [CREATE_DTTM] ASC, [POSITION] ASC, [POSITION_CODE] ASC)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]GO[/quote]thx for response

if does not exist then insert row

Posted: 07 May 2013 09:49 AM PDT

Hi Professionals.I have two table which are identical apart from the data in themlookuptable with columns raw_sw_manufacturer, amended_sw_manufacturer,raw_product_name, amended_product_name,raw_product_version, amended_product_versionlookuptable1raw_sw_manufacturer, amended_sw_manufacturer,raw_product_name, amended_product_name,raw_product_version, amended_product_versionI am looking to insert rows into lookuptable based onif lookuptable1's raw_sw_manufacturer,raw_product_name, raw_product_version exist in the lookuptable thendo nothingelseinsert the rowend ifis this possiblethanks in advance

SQL 2008 - File Group change

Posted: 07 May 2013 01:16 AM PDT

Changing Primary filegroup to a new filegroup 1. Any negative impact i should cover before making the above move. It wont affect existing objects in primary and new objects would be created in newfilegroup, unless we dont specfiy filegroup. Is that correct?2. How to delete a filegroup3. If the deleting filegroup is set as Primary, then how to delete it and what would be the premove steps

what all the firewall changes should be done to access named sql server instance that is running on port number 6000

Posted: 07 May 2013 08:58 AM PDT

what all the firewall changes should be done to access named sql server instance that is running on port number 6000,My requiremenet is all the users should be able to connect to this instance using SERVER NAME \INSTANCE name, but not with SERVERNAME,6000 SQL Server browser is running.what all TCP ports and UDP ports should be opened.Thanks all

DENY ALTER DATABASE (or something else) to db_owner?

Posted: 07 May 2013 04:02 AM PDT

We have a number of databases that are now encrypted. Someone deployed a release to one of them and the deployment settings set encryption off. Now... In order to do the deployment, the deployment user has to have db_owner privileges. However, we want to block it from succeeding with this again if the settings "forget" to be changed. I am not as good at security as I should be, so any thoughts appreciated.

DTEXEC Command

Posted: 07 May 2013 07:30 AM PDT

I am trying to run a dtexec command to excute my package utilizing multiple sql server configurations. This was my attempt:DTEXEC /SQL \MasterPackageCore.dtsx /SERVER EMPDB01 /CONN BIETL;BIETL.ETLConfiguration;ORACLE /CONN BIETL;BIETL.ETLConfiguration;REVENUE /CONN BIETL;BIETL.ETLConfiguration;STAGING /CONN BIETL;BIETL.ETLConfiguration;BIETL /CONN BIETL;BIETL.ETLConfiguration;BUETL.ADO.NET /CONN BIETL;BIETL.ETLConfiguration;MSDB /REPORTING VThe connection part is the sql service configuration table information

Monitor EXEC() statement

Posted: 07 May 2013 07:28 AM PDT

I'm trying to run an EXEC() statement against a linked server, and the query I'm running may or may not hang. I want to monitor it to see if it runs for more than 30 seconds, and if so, run some code, otherwise just run the statement and quit. Is that possible? It's something like:EXEC('UPDATE PERSON SET FIRSTNAME = ''Mike'' WHERE ID = 1') AT LINKEDSERVERDid this run for more than 30 secs? If yes, then<more T-SQL to send an email or update a table>else quit since it successfully ran the update statement.Does that make sense?Thanks,Mike

SQL Server 2008 R2 poor performance on VMWare

Posted: 06 May 2013 09:35 PM PDT

We recently migrated our production server from a physical cluster environment to a VM environment, and ever since then the query performance is just bad. We get more time outs, more waits and more deadlocks.The resources assigned to this VM server is similar to what if was on the physical cluster: same CPU count, same disk speeds on the host drives, etc. The only thing that has changed is the memory allocation is less. We had 64 GB and now have 40 GB.When I do Perfmon counters for Memory and CPU checks, the counters are all in the suggested ranges.The CPU graph in task manager runs at an average of 50-70% and memory usage is 32 GB.The DMVs also do not indicated any underlying issue.Why then would we be experiencing such degradation in query performance - mostly on a time out, wait, level?Any suggestions? I have read that it could be that the virtual machines aren't configured correctly for optimal SQL server performance - how could I check this? What should it be?

Writing to XML file on Server via SQL job

Posted: 07 May 2013 07:15 AM PDT

HiI finally have my job working:Thanks for all the Help,I do have more one question...Is there a command to write the same file to a folder on the server?is there a way via the advanced tab?This is now working ... Thanks!!DECLARE @OUTPUT xmlSET @OUTPUT = (SELECT lname as last, fname as first FROM DB.dbo.client where LName = 'wilson' FOR XML PATH('REFERRAL'), root('CLUB'))SELECT @OUTPUTdeclare @attachmentfilename varchar(100)SET @attachmentfilename = REPLACE(CONVERT(VARCHAR,GETDATE(),101),'/','')--REPLACE(CONVERT(VARCHAR,GETDATE,101),'/','')+'Clubhouse.xml' -- formats 05/07/2013 and removes slashes to 05072013--+ 'Attachement.xml'EXEC msdb.dbo.sp_send_dbmail@QUERY= 'SET NOCOUNT ON SELECT lname as last, fname as first FROM DB.dbo.client where LName = ''wilson'' FOR XML PATH(''REFERRAL''), root(''CLUB''),TYPE',@recipients = 'jbalbo@email.org',@subject= 'ClubHouse Download',@body = 'Attached is the daily clubhouse file',@attach_query_result_as_file= 1,@query_attachment_filename= @attachmentfilename,@query_result_width= 32767,@query_no_truncate= 1,@append_query_error= 1,@query_result_header= 1

How do I retrieve a value in a text column that has a XML

Posted: 07 May 2013 03:18 AM PDT

FolksI have a table with a column "message" whose type is "TEXT".The contents inside this column is XML.Here is a sample:<?xml version="1.0" encoding="utf-8"?><Message version="4.21" xmlns="http://www.surescripts.com/messaging"><Header>....<Address><AddressLine1>1991 E APPLE AVE</AddressLine1><City>MUSKEGON</City>How do a write a query to retrieve rows that have a CITY= 'Muskegon'Select top 100 * from surescripts_msg_importwhere???? ( you will the rest ).Thanks in advance

Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.

Posted: 07 May 2013 04:23 AM PDT

hi,I want to grant select on sys.database_mirroring to a monitoring user.When I did this:use msdbgoGRANT SELECT ON sys.database_mirroring TO user1;goI received this error:Msg 4629, Level 16, State 10, Line 2Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.Do you have an idea ?I don't understand the error.regards

Auditing In SQL Server - The What, When & Who And Why CDC and SQL Audit Appear To Be Incomplete Audit Replacements

Posted: 07 May 2013 06:18 AM PDT

Why do you audit changes to your database?How have you enabled DB Auditing in a version of SQL Server prior to CDC and SQL Audit being added to the product? I'm having a tough time trying to wrap my head around the native DB auditing in SQL Server 2008R2 because neither native method, CDC or SQL Audit look to me to be a proper/complete replacement for the old school method of using triggers with audit tables. AN audit table being a duplicate of the table you are monitoring for changes that also includes a few extra audit specific items such as date/time and ID of the user who caused the data change. When we audit data changes we need to capture the WHAT, the WHEN and the WHO. CDC (Change Data Capture) is wonderful for the WHAT and the WHEN but makes no attempt to bother with the WHO. SQL AUDIT from what I can tell is great for capturing the who but it stores the audit data to either external files or the windows logs making it necessary to then bring that info back into the DB if you then want to report on it or use it for any other reason other than reviewing view some log tool. Am I just missing something about either SQL Audit or CDC? Or do I simply have the wrong idea on how auditing changes to your data is normally done?Thanks

XML Error on output..

Posted: 07 May 2013 01:18 AM PDT

With some help from the forum I got my job to run.. thanks...here is the job, I changed the query to an example that I knew ran and I wanted the output to look like[b]JOB...[/b]EXEC msdb.dbo.sp_send_dbmail@profile_name='DBMailProfile',@recipients = 'jbalbo@email.org',@query = 'SELECT ''White'' AS Color1,''Blue'' AS Color2,''Black'' AS Color3,''Light'' AS ''Color4/@Special'',''Green'' AS Color4,''Red'' AS Color5FORXML PATH(''Colors''),ROOT(''SampleXML'')', @body = 'The stored procedure finished successfully.', @subject = 'Automated Success Message', @query_result_header = 1, @exclude_query_output = 1, @append_query_error = 1, @attach_query_result_as_file = 1, @query_attachment_filename = 'qry.xml'[b]THIS IS THE OUTPUT I GET.... in the job[/b]The XML page cannot be displayed Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later. --------------------------------------------------------------------------------Invalid at the top level of the document. Error processing resource 'file:///C:/Users/jbalbo/AppData/Local/Microsoft/window...XML_F52E2B61-18A1-11d1-B105-00805F49916B [b]THIS IS THE OUTPUT IN A QUERY WINDOW[/b]<SampleXML> <Colors> <Color1>White</Color1> <Color2>Blue</Color2> <Color3>Black</Color3> <Color4 Special="Light">Green</Color4> <Color5>Red</Color5> </Colors></SampleXML>ThanksJoe

Better restore solution ?

Posted: 07 May 2013 04:44 AM PDT

We have a production database with size of about 40 GB, it is in full recovery model, and we regualarly make dialy full + diff + log backups.What happened recently to it is that some table was unexpectedly updated, and I was told to restore this db to another server. They needed to restore it to a point prior to that table update. However, they did not know exact datetime or exact statement that has updated it. What they knew is that values for some records should not be 0.What I did is very inefficient way for restore. First, I restored just full backup. With recovery. We copied this table to another db with some suffix like _01. Since I restored it with recovery, I can't apply further log backups. I had to restore full again, with replace, + 1 log with recovery. Again we copied it, and I restored full + 2 log with the last one with recovery. If I don't do recovery, I can't query the database. So I repeated this process 4 times, until we got to 0 values.Is there more efficient way to do it ? Not to repeat same thing multiple times? Thanks.

Erro creating procedure oneligne ok two ligne errors!!

Posted: 07 May 2013 04:07 AM PDT

Hi Everyone,I got an error when I try to create this procedure, when I remove one ligne (like comment one ligne) it's ok, but when I add both of them, I got the error below.Please Help me.create procedure SALEM_DETACHE @TableName varchar(50)asBeginALTER DATABASE @TableName SET SINGLE_USER WITH ROLLBACKEXEC master.dbo.sp_detach_db @dbname = @TableName, @skipchecks = 'false'endMsg 102, Level 15, State 1, Procedure SALEM_DETACHE, Line 6Syntaxe incorrecte vers '@TableName'.Msg 319, Level 15, State 1, Procedure SALEM_DETACHE, Line 6Syntaxe incorrecte près du mot clé 'with'. Si l'instruction est une expression de table commune, une clause xmlnamespaces ou une clause de contexte de suivi des modifications, l'instruction précédente doit se terminer par un point-virgule.

Need to get some disk space back from a table, need to shrink...

Posted: 07 May 2013 02:54 AM PDT

OK, I know all the reasons I shouldn't be shrinking a database, but there's really no other option. Also, this is a [b]one-time only[/b] shrink! Recently I had to add a clustered index to a Heap of about 1/2 billion rows. Problem being, there wasn't enough disk space to just add the CI. BUT there was enough to "duplicate" the table into a new table with the CI already defined. Once this process completed, we truncated and dropped the old table.This freed up about 80GB within the DB file. The problem is, we now only have about 8GB free on the disk, and the SAN admin was complaining the other day about the storage being used by the various servers. So I can't request more storage for this VM...I've read the articles on why shrinking is a bad idea (the fragmentation that *will* result,) and I want to both try to minimize the index rebuild, and the log usage.So my thinking is to shrink the file by a couple GB at a time (maybe 10GB,) then do an index rebuild, then repeat. Possibly if the shrink at that size doesn't take too long, I'll do several in a row. The DB is in Simple recovery, so this should also keep the log from exploding. I can work on this on the weekend without worrying about the users.I'm going to leave some free space in the file to avoid growth later, and the DB doesn't grow all that much (it seems so far they add X rows and delete Y rows, and X almost equals Y)Does this sound like it should work?Thanks,Jason

Transfer Logins(etc) to a server with the same name

Posted: 07 May 2013 03:14 AM PDT

Let's say that we have a test environment that looks just like production.Server1 was P2V'd and exists within an isolated test environment as Server1 (same IP and all).I can see both, but get to the isolated test through a NAT'd address (my host file resolves it for me).Whether I hit that server by ServerName or IP, I still can't get around the whole "The source server can not be the same as the destination server." so I can't transfer logins or jobs without scripting them.I'd like to use SSIS or some other tools to automate this but I can't find a way around this.Anyone have experience with getting around this?Thanks

SSIS - Cannot import config file

Posted: 07 May 2013 02:27 AM PDT

Hello,I have a 2008r2 SSIS package with dtsConfig files for different runtime environments. I have deployed the package to QA. I am pointing to the correct config file, regardless of which way I am executing the package.But at run time I get:Warning: Failure importing configuration file: "... path on disk ..."It does not seem to be permissions. I get this even after making the package executor a server admin.thank for any ideas.Ken

Process stuck in Killed/Rollback status

Posted: 06 May 2013 11:41 PM PDT

I have a process that has been stuck in KILLED/ROLLBACK status for the past 24 hours. The statement is:ALTER SERVER AUDIT ****** WITH (STATE = OFF).All forums I see, suggests that I leave the process and let it finish its rollback, but it is a day later and still not change.The process stays on 16 CPU and 0 IO, and when I do a Kill with StatusOnly, it shows:"transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds."What would you suggest I do about this process?I also read that a service restart will sort that out, but this is a production server and that is not possible.

Excel 1 : Databases 0 - SME addicted to Excel!

Posted: 07 May 2013 12:55 AM PDT

Hi folks,I feel like I'm banging my head against a brick wall so figured I'd ask for some sage advice! :crazy:My company currently employs around 180 people in the UK (including field sales staff) and we've just expanded to a bigger premises while increasing productivity and recruitment. The problem I have is the senior managers still have a small business mentality and although I've mentioned it on a number of occasions are still using Excel as a data store (in particular HR data) and aren't concerned as the spreadsheets are 'password protected'!!!!! :angry:To my mind this is mad and we need to 'grow up' and start using databases to store the data especially the sensitive data relating to HR (ideally a SQL Server solution or I'd be out of a job!). How would you recommend I go about changing their minds and are there any decent (ideally free/open source) HR solutions with SQL back-ends that we could use?Many thanks for your help in advance.

find pairs of NULL records without resorting to CURSORS

Posted: 06 May 2013 10:43 PM PDT

[code="sql"]create table #tmp(ref int,Date datetime,time int,seqno int)insert into #tmp (ref,date,time,seqno) values(34227664,'20130227',0,399850)insert into #tmp (ref,date,time,seqno) values(34227664,'20130304',0,399860)insert into #tmp (ref,date,time,seqno) values(34227664,'20130313',0,399870)insert into #tmp (ref,date,time,seqno) values(34227664,'20130326',40820,NULL)insert into #tmp (ref,date,time,seqno) values(34227664,'20130326',41201,NULL)insert into #tmp (ref,date,time,seqno) values(34227664,'20130326',41287,NULL)insert into #tmp (ref,date,time,seqno) values(34227664,'20130326',44797,NULL)insert into #tmp (ref,date,time,seqno) values(34227664,'20130326',44809,5158850)insert into #tmp (ref,date,time,seqno) values(34227664,'20130326',44820,NULL)insert into #tmp (ref,date,time,seqno) values(34227664,'20130326',46147,5158870)insert into #tmp (ref,date,time,seqno) values(34227664,'20130328',53698,NULL)insert into #tmp (ref,date,time,seqno) values(34227664,'20130328',60518,NULL)SELECT * from #tmp order by date,time,seqno[/code]I am trying to identify the 2nd record in pairs of NULL sequence nos. E.g.'20130326',41201 is the 2nd in a pair,'20130326',44797 is the 2nd in a pair (although they come as a block of 4)'20130328',60518 is the 2nd in a pair.'20130326',44820 is not part of a pair so I am not interested in it.So far I have tried:Rank partition by seqno order by ref,date,time and this gives me 1 for each record which has a seqno and an incrementing counter for each that has null.mod (rnak % 2) to give me 0 or 1 and take the 0 as being the 2nd in the pair but it goes wrong for '20130326',44820 because this is in the NULL ranks but is not paired and it throws the last pair out.Also tried referencing the data to itself using rank = rank -1 in the join but this doesnt help unless we also go for min() and max() to create islands of records which is hidden RBAR and very slow on 1M records.anyone got any bright ideas?

Running a sql server 2008 job to back up to network folder

Posted: 30 Dec 2010 04:40 AM PST

I'm trying to schedule a back up database task to backup some specific databases to a network folder but kept getting this error message-" Executing the query "BACKUP DATABASE [XDevSL7Utilities] TO DISK = N'\\..." failed with the following error: "Cannot open backup device '\\fscorp\DynDB-Backup\Backup-vDB2001\Database_backup_2010_12_30_134701_5618693.bak'. Operating system error 5(Access is denied.). BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:47:00 PM Finished: 1:47:01 PM Elapsed: 1.031 seconds. The package execution failed. The step failed"Any help or guidance will be greatly appreciated.

Replication options - merge or peer-to-peer

Posted: 05 Dec 2012 06:57 AM PST

I have read a lot of articles on this but still have trouble seeing the differences in these and when I should use one over the other.I have 2 servers and need to sync about 30 tables. I need both nodes to be able to update the data. From what I have read, it seems like either merge or peer-to-peer would support this but peer-to-peer requires an enterprise license. Also, since I only want to sync 30 tables (there are other tables with data that is unique to its server and should not sync across), it seems that merge is the better option. With merge, I can create the snapshot containing only those tables. With peer-to-peer, initialization is done via a restore and would have to include all tables. I think the terminology is confusing me - merge says it has one publish and multiple subscribers. Aren't the subscribers actually functioning as publishers as well?

Error message during deletion

Posted: 06 May 2013 08:21 PM PDT

Hi All,I am trying to delete a record from a table using a stored procedure.While executing the SP i am getting the below error."The query processor ran out of stack space during query optimization. Please simplify the query."What could be the reason?Please help

Peer-Peer replication

Posted: 01 May 2013 09:49 PM PDT

I have just created peer to peer replication on couple of test server.Let's say TestServer1 and TestServer2.And test database called Test_Peer.First i have create couple of tables and create new Peer-Peer Replication from TestServer1-> TestServer2.Publication: TestServer1_Peer_TestServer2_SP1.My question is, what if i have created new table and want to put them into Peer-Peer replication.Do i need to create new publication, configure peer-peer technology and also do i need to take backup every time (After creating table) before putting into peer-peer replication?

Installing SQL Server Express 2008 & Studio on Windows 8

Posted: 29 Apr 2013 11:46 PM PDT

Hello all. My first post here. My question probably will seem amateur as I'm sure I is. I've been hired for a job based on my very old, rusty SQL Server skills. I have quiet a bit of experience using and developing with SQL Server 2000. Yep. 13 years ago.I'm trying to brush up on my skills and hit the books hard. I've purchased a new laptop running Windows 8 (ugggh...don't like it!!) and my intention was to install SQL Server 2008 Express and SQL Server Management Studio 2008 on this laptop so I can practice along with the chapters I'm reading. I'm using 2008 since that's what the project I'll soon be working on uses.Last night I was able to install SQL Server Express on this laptop (SP3 I think) but had no luck installing SQL Server 2008 Management Studio.Has anyone been able to use these two products on Windows 8?Or would you recommend I go to the SQL Server 2012 products?Thanks for helping out an old dog learn new tricks!:-)

Split Comma separated string insert into Separate column .

Posted: 06 May 2013 08:28 PM PDT

DECLARE @strCSVString VARCHAR(4000)SET @strCSVString = 'Vinayak,20-07-2013,100,Sagar,23-10-2012,334,Leena,03-08-2014,93,Meera,20-07-2013,100,Ajad,20-07-2013,100,Ajay,23-10-2012,334,Vijay,03-08-2014,93'DROP TABLE #FinalResultDROP TABLE #ResultCREATE TABLE #Result(ColumnValue NVARCHAR(100),cnt INT,rno INT)CREATE TABLE #FinalResult(Name NVARCHAR(100),Date NVARCHAR(100),Num NVARCHAR(100))DECLARE @c INTDECLARE @str VARCHAR(20)DECLARE @idx INTDECLARE @cnt INTDECLARE @rno INTDECLARE @count INTDECLARE @name VARCHAR(20)DECLARE @date VARCHAR(20)DECLARE @num VARCHAR(20) SET @c =1SET @cnt = 1SET @rno = 1IF ISNULL(@strCSVString,'') <> ''BEGIN SET @idx = CHARINDEX(',', @strCSVString) WHILE @idx > 0 BEGIN SET @str = SUBSTRING(@strCSVString, 1, @idx - 1) SET @strCSVString = SUBSTRING(@strCSVString, @idx + 1, LEN(@strCSVString) - @idx) INSERT INTO #Result values (@str,@cnt,@rno) SET @idx = CHARINDEX(',' , @strCSVString) SET @cnt =@cnt+1 IF (@cnt>3) BEGIN SET @cnt =1 SET @rno = @rno +1 END END PRINT 'I am here' SET @str = @strCSVString INSERT INTO #Result values (@str,@cnt,@rno) -- SELECT * FROM #ResultENDSELECT @count=COUNT(*)/3 FROM #ResultWHILE(@count<>0) BEGIN SELECT @name=ColumnValue from #Result WHERE rno=@c and cnt=1 SELECT @date=ColumnValue from #Result WHERE rno=@c and cnt=2 SELECT @num=ColumnValue from #Result WHERE rno=@c and cnt=3 SET @count=@count-1 SET @c=@c+1 INSERT INTO #FinalResult (Name,Date,Num) SELECT @name,@date,@num ENDSELECT * FROM #FinalResult
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog