Friday, July 19, 2013

[SQL Server] Using Dynamic SQL in Stored Procedures


Using Dynamic SQL in Stored Procedures




Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.



SQLTeam.com Articles via RSS


SQLTeam.com Weblog via RSS



Read more

sqlteam.com



Posted by at No comments:
Labels: ,

[MS SQL Server] How to apply patches one and more sql at same tme

[MS SQL Server] How to apply patches one and more sql at same tme

How to apply patches one and more sql at same tme

Posted: 19 Jul 2013 02:29 AM PDT

one of the our client ask to me ,300 sql servers need to apply patches same time ?how to do ? Any vb cript or batch command..Plz anyone help on thisThanks,jery

SQL server Service pack Installation

Posted: 19 Jul 2013 04:04 AM PDT

Hi,In our environment ,we are planning on applying service packs to all the sql instances which were old and we have 2005/2008/2008R2.I have searched online but i dont find any particular document for service packs or patches.Can anyone please send me a document or link that shows step by step process for applying the SP's ?I'm looking for the pre-reqs, installation process and post installation with all the details...Also , my main concern is i'm preparing a document with list of sql servers in my environment ( i believe there are around 100 servers ), version,current service pack, latest service pack to apply ...Please help me on finding a script to get all these details at once...like any powershell or cmd script ?Thanks in advance...

Central Management Server left out in the cold

Posted: 18 Jul 2013 01:46 PM PDT

I just set up a Central Management Server and can now check my policies against a whole group of SQL Server instances. This is great! However, it seems the instance that is designated as the Central Management Server is not checked when policies are evaluated. Is there any way to include this instance or do all policies need to be checked twice: once for the central server, and again on the target group?

SQL 2008 cluster installation failed

Posted: 19 Jul 2013 01:25 AM PDT

Today we tried installing SQL 2008 cluster. It failed with error "[b]Wait on the database engine recovery handle failed[/b]". We tried uninstalling it to do a fresh install but uninstall also failed with error "[b]Object reference not set to an instance of an object[/b]". Has anyone came across this? Any suggestion how can we resolve it.Regardssqldba4u

Does a deadlock get recorded in the log without trace flags?

Posted: 19 Jul 2013 12:03 AM PDT

Not the entire deadlock information that's returned when you enable a trace flag (1204 or 1222,) but at least something like "deadlock occurred between SPID X and Y?" Something I can quickly find in the logs?Based on this: [url=http://www.brentozar.com/archive/2011/07/difficulty-deadlocks/]http://www.brentozar.com/archive/2011/07/difficulty-deadlocks/[/url]It sounds like the answer is yes.I'm asking, as I've got one server which was experiencing LOTS of deadlocks, I did have the trace flag 1222 enabled for a time and was using this: [url=http://blogs.msdn.com/b/bartd/archive/tags/sql+deadlocks/default.aspx]http://blogs.msdn.com/b/bartd/archive/tags/sql+deadlocks/default.aspx[/url] to troubleshoot. Since then (and updating statistics, which I should've done sooner seeing as said DBs were migrated from an old server...) the number of deadlocks has fallen precipitously (as in I haven't seen one in a week) so I've turned off the trace flag.I've been checking using XEs but I'd like to double check myself, and the SQL Log would be ideal...Thanks,Jason

Discrepancy between recent activity and transaction log file

Posted: 19 Jul 2013 12:06 AM PDT

Hello --I am monitoring our database server using dbWarden and ApexSQL log viewer. The dbWarden utility reported activity within the past twenty-four hours to a particular database's mdf and ldf files. I opened the latter application to investigate. When I opened ApexSQL viewer, without any filters, the last entry that was displayed was from the July 9 date. Hence there is a discrepancy of ten days between what was supposedly written last night, and what was recorded to the database. To add to the confusion, I checked the directory where the ldf file is located, and according to the details of the file, the last modification date was the July 3 date. What would cause the discrepancy that I described, and what can I do to correct it?Thanks.

Restore SQL Server 2008 R2 database on SQL Server 2008

Posted: 18 Jul 2013 11:03 PM PDT

How can I (if it is possible to) restore SQL Server 2008 R2 database on SQL Server 2008?

alert or trigger on create login

Posted: 18 Jul 2013 08:16 PM PDT

hello,is it possible to have an alert on a create login?with a trigger, an SQL Alert or ...Thank you.

Backup has corrupted .. need to get full recovery for the database

Posted: 18 Jul 2013 05:27 PM PDT

I am taking backups regularly without any issue, suddenly I noticed backup is failed in one day and checked DBCC executed on database. I got result as fatal corruption in the database. In this situation i went for previous day backup to restore the database ( as per the MS recommendation... we will try to restore the DB from the previous good backups.. ) but unfortunately the backup found having issues and its not avalid backup to restore. So i checked before the previous day backup ( 2 days back) is available but don't have t-log backups after this full backups.Can some body please advise what is best options to recovery my database as full recovery...

System-health extended-event session does not capture latest deadlocks

Posted: 20 Dec 2012 08:04 PM PST

While running the following query to capture the latest deadlocks recorded in the default system-health extended-event session, I noticed that the latest deadlock captured was 3 days ago. However, in the SQL ERRORLOG I see that several deadlocks have occurred as recently as today.Has anyone else noticed this issue?[code="sql"]--http://www.quest.com/whitepaper/how-to-use-sql-servers-extended-events-and-notifications816315.aspx;WITH SystemHealthAS ( SELECT CAST ( target_data AS xml ) AS SessionXML FROM sys.dm_xe_session_targets st INNER JOIN sys.dm_xe_sessions s ON s.[address] = st.event_session_address WHERE name = 'system_health')SELECT Deadlock.value ( '@timestamp', 'datetime' ) AS DeadlockDateTime, CAST ( Deadlock.value ( '(data/value)[1]', 'Nvarchar(max)' ) AS XML ) AS DeadlockGraphFROM SystemHealth sCROSS APPLY SessionXML.nodes ( '//RingBufferTarget/event' ) AS t (Deadlock)WHERE Deadlock.value ( '@name', 'nvarchar(128)' ) = N'xml_deadlock_report'ORDER BY Deadlock.value ( '@timestamp', 'datetime' );[/code]
Posted by at No comments:

[Articles] DR Failovers

[Articles] DR Failovers

DR Failovers

Posted: 18 Jul 2013 11:00 PM PDT

We don't often predict for disasters, which is good since we rarely have extensive plans for dealing with them. If we do, do we actually test our plans, or test the systems under full load?

sqlprompt Make working with SQL a breeze
SQL Prompt 5 is the effortless way to write, edit, and explore SQL. It's packed with features such as code completion, script summaries, and SQL reformatting, that make working with SQL a breeze. Try it now.
Posted by at No comments:

[SQL 2012] Truncate permission only on specified tables

[SQL 2012] Truncate permission only on specified tables

Truncate permission only on specified tables

Posted: 19 Jul 2013 02:55 AM PDT

Hi Experts,How can I grant truncate permission to users to only specified tables?Help Please

Security Assessment

Posted: 19 Jul 2013 02:28 AM PDT

Hi guys,I was asked to perform security assessment for the Sql Server 2012.I could find how to perform anywhereAny one do have any idea about any tools used for this or ??on browsing some site mention to download tools, some were defining about it.Rookie here :hehe:

Identity key (unique column) not behaving properly

Posted: 19 Jul 2013 12:01 AM PDT

Hi,Wonder if someone has seen this issue before or if I am missing any recent hotfix here that I should apply.I've seen this error on several of my databases again and again this week:[code="plain"]Violation of PRIMARY KEY constraint 'PK_XXXX'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (whatever number here).The statement has been terminated.[/code]The thing is, the PK is unique, and the INSERT statement does not touch that column, it touches the other ones. So SQL2012 is the one that automatically generates the next and unique available value.How can be possible that the value to be inserted (generated by SQL2012) is a duplicate one? By the way, this was not happening on the previous SQL2005 or SQL2008 server where the databases were being hosted.Here's my SQL 2012 version: 11.0.3000.0 (SP1 applied). Runs on top of a Win2008R2 Cluster.Any hints are highly appreciated!

Notification for Availabilty Group Failover

Posted: 19 Jul 2013 01:20 AM PDT

How can we setup auto notification for Availabilty Group Failover in SQL 2012? Please suggest.Regardssqldba4u

Single Index on multiple tables?

Posted: 18 Jul 2013 11:05 PM PDT

This is a long shot, but is it possible to create an index that references columns on two tables.Scenario: The application tracks internal and external items in separate tables - a new requirement is a reference number that must be unique in both internal and external tables.So we have a unique index on both tables, and a check trigger to make sure reference numbers in insert/updates don;t exist on the other table, So that's 4 T-SQL objects for one business requirement, and its causing maintenance issues.Is there an easier way? - perhaps something to do with indexed views?

Blocked

Posted: 18 Jul 2013 09:48 PM PDT

Hi,How to find out the total number of all blocked sessions by using T-SQL Statement

Accessing MSSQL using a web browser

Posted: 18 Jul 2013 09:01 PM PDT

I have seen various posts showing interest in being able to perform simple queries and execute storedprocs on MSSQL using a web browser. I had a similar interest so I developed a web app to do it. The beta is here: http://esqlclient.azurewebsites.netI'd be interested to know whether people think this is useful and what issues (licensing, security, traffic etc.) there might be. I only tested it on MSSQL 2012 but it may work for earlier versions that are exposed to the internet. It also works for Azure SQL.The beta will be taken down in a few days so feel free to use it until then and let me know what you think. It should be fairly obvious how to connect but let me know if not. Once connected, you can perform T-SQL and execute SPs according to the permissions set by your DBA.
Posted by at No comments:

[T-SQL] where filter col <> 0 returns error, col > 0 works, col contains no 0

[T-SQL] where filter col &lt;&gt; 0 returns error, col &gt; 0 works, col contains no 0

where filter col &lt;&gt; 0 returns error, col &gt; 0 works, col contains no 0

Posted: 18 Jul 2013 03:13 AM PDT

Hi, I can't explain the following behaviour:I join 2 tables and depending on the where filter I get an error: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."table A is a heap, table B has a clustered indexB.Date is CHAR(8)this does not work:SELECT CONVERT(DATETIME,B.Date)FROM AINNER JOIN B ON A.ID = B.ID WHERE A.ID <> 0this works:SELECT CONVERT(DATETIME,B.Date)FROM AINNER JOIN B ON A.ID = B.ID WHERE A.ID > 0I don't have a 0 in Table A.hmm...why do I get an error message with WHERE a.ID <> 0 ,altough I don't have 0 in a.IDthank you for your help!cheersralf

Invalid column name?

Posted: 18 Jul 2013 11:56 PM PDT

I have this query works great - no problem:[code="sql"]select * from (SELECT pehPErcontrol,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2) when '01' then 'January' when '02' then 'Feburary' when '03' then 'March' when '04' then 'April' when '05' then 'May' when '06' then 'June' when '07' then 'July' when '08' then 'August' when '09' then 'September' when '10' then 'October' when '11' then 'November' when '12' then 'December' end as [month],rtrim(eepNameLast) + ', ' + rtrim(eepNameFirst) + ' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name, eepNameLast AS [Last Name],IsNull(eepNameSuffix,'') AS [Suffix],eepNameFirst AS [First Name],IsNull(eepNameMiddle,'') AS [Middle Name],pehCurAmt AS [Current Amount], pehCurHrs AS [Current Hours], pehCoID AS [Company ID], pehEEID AS [EE ID], pehEmpNo AS [Emp No], pehLocation AS [Location], pehJobCode AS [Job Code], pehOrgLvl1 AS [Org Level 1], pehOrgLvl2 AS [Org Level 2], pehOrgLvl3 AS [Org Level 3], pehOrgLvl4 AS [Org Level 4], pehPayGroup AS [Pay Group], pehProject AS [Project], pehShfShiftAmt AS [Shift Amount],pehearncode AS [Earn Code]FROM EmpPers JOIN pearhist ph ON ph.pehEEID = eepEEID join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ON [orglevel] = pehOrgLvl2) t inner join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ob on t.[month] = ob.[month] and orglevel = [org level 2]where pehPerControl > '201301011' [/code]But if I add this to the end:[code="sql"]AND pehearncode = '0002'[/code]I get an Invalid column, how is it invalid? It's referenced and returns in the select??

Read a table within a table

Posted: 18 Jul 2013 06:13 PM PDT

I have a table with articles and a view with additional article information. For an article in the table I can found more records in the view. See example:Table:Article042001204200160420018View:String Name Expr1 Expr2 feature Unit vlg prefix suffix seperator10420012 Merk NULL Mega Mega 1 N N 0420012 Verbinding1 NULL hose tail hose tail 21 N N x 0420012 Nokafstand1MM 40 NULL 40 CD(mm) 28 Y N x 0420012 Materiaal brass NULL brass brass 3 N N 0420012 Maat1Inch 1/2 NULL 1/2 " 5 N Y x 0420012 Maat1MM 13 NULL 13 13 mm 7 N Y x So in my tabel I find article 0420012, in the view I see 6 lines. Now I have to make a SQL what reads the table and concat all values from the view (field feature and unit) in 1 result field, so in the example this must be something like:Mega hose tail 40 CD(mm) brass 1/2" 13mmCan someone advice me how to do this?Bert

Help needed in writing a stored procedure

Posted: 18 Jul 2013 05:35 PM PDT

I have a tablecreate table searchtbl(skills varchar(100),position varchar(40),location varchar(50))with records as skills position locationsqldeveloper contract hydjavadeveloper permanent hydsqldeveloper permanent Bang.netdeveloper contract Bangoracledeveloper contract chennaiphpdeveloper parttime hyd.netdeveloper permanent hydNow,i want to write a stored procedure when i pass a parameter from front end example if i have to seach for .net delvelopr i must only get the details of all .net developers,like that if i want to search hyd employes i must get all the details of hyd people and if i didn't pass any i must get all the records.Is it possible to write all those in on stored procedure.If so how?

Looking for a clever solution

Posted: 18 Jul 2013 11:13 AM PDT

Hi everyone,We have a SP that is quite long and it has a lot of such code:UPDATE table1SET col1 = CASE WHEN ( somecolumn = 'abc' AND somecolumn2 > 1 ) THEN 123 WHEN ( somecolumn = 'abc' AND somecolumn2 <= 1 ) THEN 234 ELSE 0 END, col2 = CASE WHEN ( somecolumn3 = 'abc' AND somecolumn4 > 1 ) THEN 123 WHEN ( somecolumn3 = 'abc' AND somecolumn5 > 1 ) THEN 234 ELSE 0 END As you can see there are a lot of constants here (abc, 1), so if the requirements change and instead of abc, they want to test against bcd, we have to go through the code and make those changes in the code, so that's a maintenance nightmare. How would you handle this kind of code and changing requirements? Thanks in advance.

Error inserting data with T-sql

Posted: 18 Jul 2013 01:53 AM PDT

HelloI am inserting data from one table to another using update,set command and getting the following error.[center]update XDDDepositorSET XDDdepositor.WBeneName = Vendor.RemitName,XDDDepositor.WBeneAddr = Vendor.RemitAddr1,XDDDepositor.WBeneAddr2 = Vendor.RemitAddr2,XDDDepositor.WBeneCity = Vendor.RemitCity,XDDDepositor.WBeneState = Vendor.RemitState,XDDDepositor.WBeneZipPostal = Vendor.RemitZipFrom XDDDepositor, VendorWhere XDDDepositor.vendid like '05%'and XDDDepositor.vendid = Vendor.Vendid and XDDDepositor.WBeneName =''[/center]ERROR: Msg 8152, Level 16, State 14, Line 2String or binary data would be truncated.The statement has been terminated.I know the problem is the character length of the address columns I am inserting data from but is there a way to correct it without altering the columns being updated?WBenBankAddr (char(35),not null) and RemitAddr1 (char(60), not null) WBeneAddr2 (char(35),not null) and RemitAddr2 (char(60), not null)

Can I join on an alias?

Posted: 18 Jul 2013 05:58 AM PDT

Is it possible to create a join on an alias?I have this query that I need to join to a table (month):[code="sql"]SELECT pehPErcontrol,case left(substring(pehPErcontrol,5,len(pehPErcontrol)),2) when '01' then 'January' when '02' then 'Feburary' when '03' then 'March' when '04' then 'April' when '05' then 'May' when '06' then 'June' when '07' then 'July' when '08' then 'August' when '09' then 'September' when '10' then 'October' when '11' then 'November' when '12' then 'December' end as month,rtrim(eepNameLast) + ', ' + rtrim(eepNameFirst) + ' ' + coalesce(substring(eepNameMiddle,1,1) + '.', '') as Name, eepNameLast AS [Last Name],IsNull(eepNameSuffix,'') AS [Suffix],eepNameFirst AS [First Name],IsNull(eepNameMiddle,'') AS [Middle Name],pehCurAmt AS [Current Amount], pehCurHrs AS [Current Hours], pehCoID AS [Company ID], pehEEID AS [EE ID], pehEmpNo AS [Emp No], pehLocation AS [Location], pehJobCode AS [Job Code], pehOrgLvl1 AS [Org Level 1], pehOrgLvl2 AS [Org Level 2], pehOrgLvl3 AS [Org Level 3], pehOrgLvl4 AS [Org Level 4], pehPayGroup AS [Pay Group], pehProject AS [Project], pehShfShiftAmt AS [Shift Amount],pehearncode AS [Earn Code]FROM EmpPers JOIN pearhist ON pehEEID = eepEEID join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] ON [orglevel] = pehOrgLvl2inner join WSISQL4.DASHBOARD.DBO.[OVERTIME_BUDGET_2013] on [month] = v.month[/code]

How to Parse two string Columns using a Function

Posted: 18 Jul 2013 02:35 AM PDT

I need to parse the sample data below as shown. Please help me in writing a TSQL Function. Create Table Ramesh_StringParsing (Col1 Varchar (50),Col2 Varchar(50))Insert Into Ramesh_StringParsing (Col1, Col2) VALUES ('A123|B3456|G546|V897|', 'Add|Delete|Insert|Update|')Insert Into Ramesh_StringParsing (Col1, Col2) VALUES ('C334|','Apple|')Insert Into Ramesh_StringParsing (Col1, Col2) VALUES ('R234|U768|X787','Ram|Sam|John')Insert Into Ramesh_StringParsing (Col1, Col2) VALUES ('F44|K456', 'Paint|Faint') [b]My Output should be: [/b]column1 column2A123 AddB3456 DeleteG546 InsertV897 UpdateC334 AppleR234 RamU768 SamX787 JohnF44 PaintK456 Faint
Posted by at No comments:

[SQL Server 2008 issues] Update Problem

[SQL Server 2008 issues] Update Problem

Update Problem

Posted: 18 Jul 2013 06:22 PM PDT

helloi want to update a data in grid viewi have three columns in grid view first two column are shift id and date these two column are taken from table 1the third column is shift column this column are taken from table 2now i want to update a data where shift id and shift column are sametable 2 have these columnshiftshiftidand table 1 has these columnshift id,dateplease can u provide me a update query syntaxthanks

database.in simple recovery

Posted: 18 Jul 2013 03:20 PM PDT

hi guys, i have a reporting database in simple recovery mode, i need to.make.a full backup to be able to restore it in my development environment. Since my database.is.in.simple recovery mode is.it necesary for.me to also.create a log backup?,i read somewhere i need to do it to clear out the log and.prevent it from growing

SSRS requirement query help

Posted: 18 Jul 2013 06:14 PM PDT

Material Container InformationEAS Material container code <MaterialsItemsContainers.materialItemContainerCode> Date received <MaterialsItemsContainers.receptionDate>Registered by <MaterialsItemsContainers.receivedBy> Received from <MaterialsItemsContainers.supplier> (cf. DMS_03)Gross amount rec'd <MaterialsItemsContainers.grossAmount> Gross amount unit <MaterialsItemsContainers.grossAmountUnit>Net amount rec'd <MaterialsItemsContainers.netAmount> Net amount unit <MaterialsItemsContainers.netAmountUnit>Site <MaterialsItemsContainers.site> Other reference <MaterialsItemsContainers.otherReference>Comments I have a report requirement like this:I am pretty new to this. If the requirement was like this fashion below,EAS Material container code I Registered by I Gross amount rec'd I Net amount rec'd I Site ie. The fields in listed horizontally, I know how to do the report, but it is in a vertical listing fashion. How to do that part?. Please help on the query part to achieve this.

Identity property

Posted: 17 Jul 2013 11:04 PM PDT

Hi,I have a table with some columns likeTable name:Mydatacolumns:sid,sname,coursewith some data.so, can i add a Identity property to the sid column after data inserted in to the table?

New server. How to migrate?

Posted: 17 Jul 2013 08:10 PM PDT

My web site uses MS SQL Server. I ordered a new server for DB and want to migrate from old one. Both servers have the same [b]MS SQL Server 2008 Web Edition[/b]The problem is that database is about 500GB. Simple attach/detach procedure will take about an HOUR to copy database files. I'm not very happy that my site will be offline during such a long period of time. I need a solution that leads to 1-2 min of site unavailability maximum.Is there a way to migrate database on new server online?What I think should be possible: I would like to perform some setup on new server while old database is online. After that setup two databases are continuously syncronized. Then I just swith site to new DB.How to implement that scenario?Looking forward for an advice.

Memory Hogger

Posted: 18 Jul 2013 02:54 AM PDT

I'm running Sql Server 2008 R2. Resource center says i have 200mb available on a 12 gb drive. Doing the math i can see what is being used in the resource list, it only adds up to 2gb. So my question is where is the other 10 gb going? Is there a way i can see what is using it? Or a setting i can set to avoid this? One thing i did do is sql management studio, properties and memory and set minimun and maximum to 6 gb each. This freed up some but over the last two weeks it was all hogged up again.

Any suggestion on tunning this Stored procedure

Posted: 18 Jul 2013 07:56 AM PDT

USE [myNIC_PROD]GO/****** Object: StoredProcedure [dbo].[NIC_REPORTS_GetReferal] Script Date: 07/18/2013 20:51:38 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[NIC_REPORTS_GetReferal]@lPhysician int,@lOffice int,@SDate datetime,@EDate datetimeASSET TRANSACTION ISOLATION LEVEL READ COMMITTEDDECLARE @lEnterprise intSELECT @lEnterprise=lEnterprise FROM Office WHERE lID=@lOffice/*select b.szlast + ', ' + b.szfirst as szPhysicianName, c.szlast + ', ' + c.szfirst as szPatientName, a.dDateCreated, a.dCheckForReport, a.nPriority from consultants a, physician b, patient c where a.lphysician = b.lid and a.lphysician in (select lPhysician from map_physiciantooffice where loffice = @lOffice) and c.lid in (select lpatient from address where lid = a.lpatientaddress)and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 order by a.dDateCreated, szPatientName, a.nPriority desc*/select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ap.szfirst + ' ' + ap.szlast as szphysicianname, ap.szorganization as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN alliedHealthProviders AP ON a.lAlliedProvider=AP.lid LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere a.nRecordStatus=1 AND DATEDIFF(d, @SDate, a.dDateCreated) >= 0 AND DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, r.szfirst + ' ' + r.szlast as szphysicianname, r.szdepartment as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToRolodex d ON a.lID = d.lConsultants JOIN Rolodex r ON r.lID = d.lRolodex LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directoryAB ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directoryab' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directorybc ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directorybc' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directorynb ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directorynb' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directoryns ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directoryns' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directoryon ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directoryon' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directorysk ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directorysk' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLorder by szPatientName, a.dDateCreated, szfacility, a.nPriority desc

SQL Service Restarted - Fault with MSVCR80.dll

Posted: 10 May 2013 12:30 AM PDT

Hi All,Our production SQL Server service stopped and restarted automatically in 2 mins. It is SQL Server 2008 R2 Enterprise on Windows Server 2008 R2 Enterprise. Active Passive cluster. It did not fail over. SQL Restarted and no errors logged in SQL Error log. However I do see below errors in Windows application log.Faulting application name: sqlservr.exe, version: 2009.100.4266.0, time stamp: 0x5063d9bcFaulting module name: MSVCR80.dll, version: 8.0.50727.6195, time stamp: 0x4dcdd833Exception code: 0xc0000409Fault offset: 0x000000000000caddFaulting process id: 0x2b14Faulting application start time: 0x01ce493690d265c8Faulting application path: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exeFaulting module path: C:\Windows\WinSxS\amd64_microsoft.vc80.crt_1fc8b3b9a1e18e3b_8.0.50727.6195_none_88e41e092fab0294\MSVCR80.dllReport Id: dd42d838-b902-11e2-b963-d4ae52e84a03Fault bucket , type 0Event Name: BEX64Response: Not availableCab Id: 0Problem signature:P1: sqlservr.exeP2: 2009.100.4266.0P3: 5063d9bcP4: MSVCR80.dllP5: 8.0.50727.6195P6: 4dcdd833P7: 000000000000caddP8: c0000409P9: 0000000000000000P10: Analysis symbol: Rechecking for solution: 0Report Id: dd42d838-b902-11e2-b963-d4ae52e84a03Report Status: 4Any suggestions please?

Stored procedure working very slow

Posted: 18 Jul 2013 06:17 AM PDT

CREATE PROCEDURE dbo.NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS@lPatient int,@nRecordStatusint,@nClassLevel int=5,@nSecurityLevelint=0,@lMap_UserToOfficeint=0ASSET TRANSACTION ISOLATION LEVEL READ COMMITTEDBEGINDeclare @lUser int,@lUserActionMappingTable intselect @luser=lUser from map_userToOffice where lid=@lMap_UserToOfficeset @lUserActionMappingTable=32--Disable the AOS SECURITY FOR CPPif @nSecurityLevel=2 set @nSecurityLevel=1if @lMap_UserToOffice>0 and @nSecurityLevel>=2 -- if there is a user and masking is on(bit 1=enabled masking)begin------------- declare @dCurDate datetime, @lOffset int--set @dCurDate=getDate()-- may need to adjust this for users timezoneexec SESSION_GetUserDate @lUser =@luser ,@dUserDate =@dCurDate OUTPUT,@lOffset=@lOffset OUTPUT--This gets the date in the users timezone/*Get the permission ID for this section....*/declare @lCustodianPermission intselect @lCustodianPermission =lid from Custodian_Permissions where szcode='CPP'declare @PhysiciansAccess table (lPhysician int primary key)insert into @PhysiciansAccess (lPhysician)select m.lPhysician from map_UserToCustodian minner join map_UserToCustodian_permissions cp on cp.lmap_UserToCustodian=m.lid and cp.lCustodianpermission=@lCustodianPermissionwhere m.lUser=@luser and m.nRecordStatus=1 and m.bAllPatient=1 and isNull(m.dStartDate,'1900-01-01')<=@dCurDate and isnull(m.dEndDate,'9000-01-01')>=@dCurDateUnionselect lPhysician from map_UserToCustodian minner join map_UserToCustodian_Per_patient p on p.lmap_UserToCustodian=m.lidinner join map_UserToCustodian_permissions cp on cp.lmap_UserToCustodian_Per_Patient=p.lid and lCustodianpermission=@lCustodianPermissionwhere lUser=@luser and m.nRecordStatus=1 and p.nRecordStatus=1 and bAllPatient=0 and p.lPatient=@lPatientand isNull(p.dStartDate,'1900-01-01')<=@dCurDate and isNull(p.dEndDate,'9000-01-01')>=@dCurDate--only get if the expiry dates are valid--select * from @PhysiciansAccessdeclare @OfficeAccess table (lOffice int primary key)/*go get all offices that the patient is a member offilter out offices that this patient has a custodain and that custodain did not allow access to this userNote: there is no need to filter by section for this query. The Section has been filtered above, and is used in the @PhysiciansAccess listNOTE: the default values for office is -1 if the information was recorded without an office. This means that it is always accessable.*/ insert into @OfficeAccess (lOffice) values(0)insert into @OfficeAccess (lOffice) select map.loffice from map_PatientToOffice mapinner join map_PatientAtOfficeToCustodian mapc on mapc.lpatient=map.lpatient and mapc.loffice=map.lofficeinner join @PhysiciansAccess PA on PA.lphysician=mapc.lPhysicianwhere map.lpatient=@lPatient--and PA.lPhysician is not nullunion select map.loffice from map_PatientToOffice map left join map_PatientAtOfficeToCustodian mapc on mapc.lpatient=map.lpatient and mapc.loffice=map.loffice where map.lpatient=@lPatient and isNull(mapc.lPhysician,0) <=0-----------SELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory, p.dDateCollected,Case when exists (select * from mask_role inner join map_UserAtOfficeToRole mapRole on mapRole.lUserRole=mask_Role.lRoleinner join map_userTOoffice map on map.lid=mapRole.lMap_userToOfficewhere mask_role.nMappingTable=@lUserActionMappingTableand map.lUser=@luserand mask_role.lRowID=p.lidand mask_role.nMaskStatus=1)THEN 1WHen Mu.lid is not nullTHEN 1else 0ENDas bMaskFROM [PhysicianLabReportsTests] a JOIN [PhysicianLabReports] p ON p.lID=a.lLabReportJOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTestleft Join Mask_User MU on MU.nMappingTable=@lUserActionMappingTable and MU.lUser=@luser and MU.lRowID=p.lid and MU.nMaskStatus=1left join @PhysiciansAccess PA on (PA.lPhysician=p.lPhysician) --LEFT Join for all the physicains the user has access to ALL patient data recorded under that physicianleft join @OfficeAccess O on O.loffice=p.loffice--LEFT Join for all the offices the user has access to ALL patient data in that officeWHERE (p.lPatient=@lPatientAND p.bSignedOff=1AND p.szFiledBy<>'auto'AND a.nRecordStatus=@nRecordStatusand p.nclassificationlevel>=@nClassLevelAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusAND y.bSignedOff=1)) = 0)or (p.lPatient=@lPatient-- AND p.bSignedOff=1AND p.szFiledBy='auto'AND a.bAdd2CPP = 1AND a.nRecordStatus=@nRecordStatusand p.nclassificationlevel>=@nClassLevelAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusand Z.bAdd2CPP = 1-- AND y.bSignedOff=1)) = 0)and isnull(PA.lPhysician,O.lOffice) is not null--Only return records if EITHER of the left joins returned a matching recordORDER BY b.szDescriptionendelse if @lMap_UserToOffice>0 and @nSecurityLevel>=1 -- if there is a user and masking is on(bit 1=enabled masking)beginSELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory, p.dDateCollected,Case when exists (select * from mask_role inner join map_UserAtOfficeToRole mapRole on mapRole.lUserRole=mask_Role.lRoleinner join map_userTOoffice map on map.lid=mapRole.lMap_userToOfficewhere mask_role.nMappingTable=@lUserActionMappingTableand map.lUser=@luserand mask_role.lRowID=p.lidand mask_role.nMaskStatus=1)THEN 1WHen Mu.lid is not nullTHEN 1else 0ENDas bMaskFROM [PhysicianLabReportsTests] a JOIN [PhysicianLabReports] p ON p.lID=a.lLabReportJOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTestleft Join Mask_User MU on MU.nMappingTable=@lUserActionMappingTable and MU.lUser=@luser and MU.lRowID=p.lid and MU.nMaskStatus=1WHERE (p.lPatient=@lPatientAND p.bSignedOff=1AND p.szFiledBy<>'auto'AND a.nRecordStatus=@nRecordStatusand p.nclassificationlevel>=@nClassLevelAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusAND y.bSignedOff=1)) = 0)or (p.lPatient=@lPatient-- AND p.bSignedOff=1AND p.szFiledBy='auto'AND a.bAdd2CPP = 1AND a.nRecordStatus=@nRecordStatusand p.nclassificationlevel>=@nClassLevelAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusand Z.bAdd2CPP = 1-- AND y.bSignedOff=1)) = 0)ORDER BY b.szDescriptionendelsebeginSELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory,0 as bMask, p.dDateCollectedFROM [PhysicianLabReportsTests] a JOIN [PhysicianLabReports] p ON p.lID=a.lLabReportJOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTestWHERE (p.lPatient=@lPatientAND p.bSignedOff=1AND p.szFiledBy<>'auto'AND a.nRecordStatus=@nRecordStatusAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusAND y.bSignedOff=1)) = 0)or (p.lPatient=@lPatient-- AND p.bSignedOff=1AND p.szFiledBy='auto'AND a.bAdd2CPP = 1AND a.nRecordStatus=@nRecordStatusAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusand Z.bAdd2CPP = 1-- AND y.bSignedOff=1)) = 0)ORDER BY b.szDescriptionendENDGO

Automate restore script

Posted: 18 Jul 2013 05:07 AM PDT

Hi,We have full, differential and log backups into different folders as below:1. Z:\Backups\db_name\full\DB_name_FULL_20130714_013225.bak -- Weekly once2. Z:\Backups\db_name\diff\DB_Name_DIFF_20130717_190949.bak --Daily once3. Z:\Backups\db_name\log\DB_Name_LOG_20130718_110116.trn --every 15 minsRestore:1) I'm able to restore full and diff with no difficulties2) As log backups are running every 15 mins and there are hundreds of files to restore and it's taking hours to restore one-by one.Is there any scripts which will give the auto generated scripts for at least log backups?How you guys do these kind of restores in enterprise level organizations?Thanks

join tables on columns that have different data

Posted: 18 Jul 2013 03:15 AM PDT

I have two tables that have a column representing the same value, but entered differently and I need to join them in a query. What I mean is this:tableA has a column called 'city' and many rows with a value of 'Boston'tableB has a column called 'city' and many rows with a value of 'BO' (BO stands for Boston in this system)I need to join on these columns, but I cannot do:join on tableA.city = tableB.city because the values are not the same. But they represent the same so there has to be a way to do this join.Any help?

Query help

Posted: 18 Jul 2013 03:17 AM PDT

I need to run this query in 100 databases. How to make this as dynamic script and run in all databases at once and get results?SELECT * FROM Docs WHERE (VirusStatus > 0) AND (VirusStatus IS NOT NULL)

SSIS in cluster

Posted: 12 Jul 2013 03:53 AM PDT

I have two nodes nodeA and nodeB on a sql server on a fail over clustered environment. Each of these nodes have two sql server databases that I am interested in. I need to deploy SSIS packages to this cluster environment using XML configuration and file system deployment. Now in the config file i am not quite sure which node names(server names) i should use because the nodes name should switch when a failure occurs? Please advise.

HEAP

Posted: 18 Jul 2013 02:21 AM PDT

Hi,I am looking for a script that will give all Heaps (user tables only ) in a sql server 2008 database. I have the following script but getting all of the tables/objects. Thanks.use AdventureWorksGOSELECT SCHEMA_NAME(o.schema_id) AS [Schema],object_name(A1.object_id ) AS [Table Name] , B1.rows ,user_seeks ,user_scans,user_lookups ,user_updates ,last_user_seek ,last_user_scan ,last_user_lookupFROM sys.indexes A1INNER JOIN sys.objects o ON A1.object_id = o.object_idINNER JOIN sys.partitions B1 ON A1.object_id = B1.object_id AND A1.index_id =B1.index_idLEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON A1.object_id =ius.object_id AND A1.index_id = ius.index_idWHERE A1.type_desc = 'HEAP'ORDER BY rows desc

How to dynamically create my FILEGROUP during a restore?

Posted: 18 Jul 2013 12:22 AM PDT

How can I dynamically create my FILEGROUP during a restore?My FILELISTONLY renders[font="Courier New"]LogicalName PhysicalName Type FileGroupName=========== ============ ==== ============= MyDBData_Primary G:\SQLData\Archive_Primary.mdf D PRIMARYMyDBData_1 G:\SQLData\MyDB_1.ndf D DataFileGroupMyDBData_2 G:\SQLData\MyDB_2.ndf D DataFileGroupMyDBLog L:\SQLLogs\MyDB.ldf L NULL[/font]How can I tweak the following RESTORE command to dynamically generate the FileGroupName called 'DataFileGroup' listed above?RESTORE DATABASE MyDB FROM DISK='H:\SQLBACKUPS\MyDB.bak' WITH RECOVERY, REPLACE, MOVE 'MyDBData_Primary' TO 'E:\SQLData\MyDB_Primary.mdf', MOVE 'MyDBData_1' TO 'E:\SQLData\MyDB_1.ndf', MOVE 'MyDBData_2' TO 'E:\SQLData\MyDB_2.ndf', MOVE 'MyDBLog' TO 'F:\SQLLogs\MyDB.ldf'GO

Force SQL jobs to run serially, not in parallel

Posted: 17 Jul 2013 10:24 PM PDT

In SQL 2008 R2 (sp2) our vendor requires that thier 14 SQL database FULL backups execute in a sequential order. (to preserve data integrity in the event of a restore) EG. DB_1 gets backed up, when finished, DB_2 gets backed up, etc. (thes 14 FULL Backups cannot run in parallel)I'm familiar with starting a job via: EXEC msdb.dbo.sp_start_job @ScheduledJobName --- but thinking, if I submit 14 of these EXEC msdb.dbo.sp_start_job statements -- they'll initiate asynchronously and run in paralllel.Anyone have a script or suggestion which will run jobs, serially, waiting for the previous job to complete? thx

Active Users

Posted: 17 Jul 2013 08:19 PM PDT

Hi,How do i find out the number of users having an active transaction at the moment by using T-SQL Statement

SQL Replication details

Posted: 17 Jul 2013 07:36 PM PDT

HiCan anyone point me to any DMV or anything else that can provide a history of when information was replicated? I need to try and find out when the busiest times of replication are.Thanks
Posted by at No comments:

Thursday, July 18, 2013

[SQL Server] Table Join on three columns

[SQL Server] Table Join on three columns

Table Join on three columns

Posted: 17 Jul 2013 05:07 PM PDT

I have two tables with identical structure. one table is for month1 the other is for month2i want to compare the two. the data is issued to us and i am trying to report on the differences. I am trying to compare based on a group of columns Level, code, and area. I cant trust that the groups will be the same in both tables so i'll probably add another table with the unique list of the groupings for each table after wards and use the method to get the Qty from both tables based on the group of columns. i have attached an image to help explain. Thanks
Posted by at No comments:
Subscribe to: Comments (Atom)

Search This Blog