Effective Query When There Is Lot Of Rows Involved

Dec 17, 2014

i have 3 tables having refrential integrity on id on column on each table

TABLEA contains 5 million rows
TABLEB contains 50 thousand rows
TABLEC contails 5 thousand rows

i have a proc that have 3 in parameter that are actually values of some of the columns in that table one parameter for each table.what is the optimized way to write query to get records on the basis of in parameters from these tables.

View 7 Replies


ADVERTISEMENT

SQL Server 2012 :: Merging Two Tables Using Effective Start And Effective End Date Fields

Sep 10, 2015

I have two tables. Status and Fourhistory tables.Status table contains a status column with effectivestart and end dates as history. This column is having history at month level.

Fourhistory table maintains 4 columns as part of history with the use of effectivestart and end dates. Here history capturing is at day level.

Desired Result: I want to merge the status column into FourHistory table.Below i have given some possible sample scenarios which i face and the third table contains the expected ouput.how to achieve this in T-SQL query.

create table dbo.#Status(
ID varchar(50),
Status varchar(50),
EffectiveStartDate datetime,
EffectiveEndDate datetime,
Is_Current bit

[code]...

View 4 Replies View Related

Update Query When Joined Tables Are Involved

Sep 30, 2013

I have a query written that filters on joined table data. The SELECT looks like this:

SELECT *
FROM tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')
ORDER BY a.bol_number DESC

If I want to do an UPDATE query that uses those filters in the WHERE clause, how do I go about doing that? It doesn't look like you can used joined tables in the UPDATE line like this:

UPDATE tbl_bol AS a LEFT OUTER JOIN
bol_status AS b ON b.bol_status_id = a.bol_status_id LEFT OUTER JOIN
tbl_carrier AS c ON c.carrier_id = a.carrier_id
SET accrueamt='1348'
WHERE (a.carrier_name LIKE 'five%') AND
(a.accrueamt = 0) AND
(a.imported = 1) AND
(b.description = 'tendered') AND
(a.ship_date BETWEEN '9/1/13' AND '9/30/13')

View 4 Replies View Related

Analysis :: How To Get List Of Attributes Involved In Particular MDX Query

Aug 3, 2015

I have a list of mdx queries extracted by sql server trace file. I want to get list of attributes and measures involved in each query.

View 6 Replies View Related

Why Is Param Query Effective Against SQL Injection?

Mar 12, 2008

Reading about SQL Injection attacks I came across this example:

SSN="172-32-9999';DROP DATABASE pubs --"
SqlQuery = "SELECT au_lname, au_fname FROM authors WHERE au_id = '" + SSN + "'"

One remedy given was a parameterized query as follows:

Dim cmd As new SqlCommand("SELECT au_lname, au_fname FROM authors WHERE au_id = @au_id")
Dim param = new SqlParameter("au_id", SqlDbType.VarChar)
param.Value = SSN
cmd.Parameters.Add(param)

Why does this parameter which is defined as a varchar solve the problem? It's defined as a varchar, basically a string. Why is the result different in the solution? How is the query string in the second sample different from the one in the first? A simple question I know, but I've been wondering.

Thanks
Mike Thomas

View 7 Replies View Related

Data Warehousing :: How To See Text Of Query Involved In Deadlock

Sep 6, 2015

How to see text of the query involved in deadlock ?

View 2 Replies View Related

SQL 2012 :: Parallel Query Worker Thread Was Involved In A Deadlock

Oct 22, 2015

I have a deadlock trace running on a production server that is filling up with the following:

Lock: Deadlock Chain
Parallel query worker thread was involved in a deadlock SPID 8
105 - Resource Type Exchange

Spid 8 is a SQL process LOCK MONITOR

There are no graphs as seen with typical deadlock, so it is not two queries that are deadlocking, and I've never seen a deadlock for "LOCK MONITOR"

View 0 Replies View Related

T-SQL (SS2K8) :: Rank Duplicates But Only Rows Involved In Duplicates?

Oct 22, 2014

I have a table with 22 million Business records. I can see that there are duplicates when I group by BusinessName and Address and Phone. I'd like to place only the duplicates into a table, with a ranking, oldest business key gets a ranking of 1.

As a bonus I'd like each group to have a distinct group name (although not necessary, just want to know how to do this)

Later after I run more verifications to make sure these are not referenced elsewhere I'll delete everything with a matchRank > 1 out of the main Business table.

DROP TABLE [dbo].[TestBusiness];
GO
CREATE TABLE [dbo].[TestBusiness](
[Business_pk] INT IDENTITY(1,1) NOT NULL,
[BusinessName] VARCHAR (200) NOT NULL,
[Address] VARCHAR(MAX) NOT NULL,

[code]....

View 9 Replies View Related

Rows Skipped Out In Stored Procedure While Return All Rows If Query Executed Seprate

Nov 8, 2007

Hi All,

I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.

But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.

There are indexes in the tables.

Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.


But nothing is improving

View 7 Replies View Related

Get Tables Involved In Operation

Jun 17, 2014

Say I have app, I want to know when I perform any operation like logging, changing username etc, can I get all the tables involved for that particular operation.

View 2 Replies View Related

Restoring A Db That Was Involved In Replication

Mar 24, 2004

Hi all

I have restored a db from a live environment to my dev envorinment. This db on live is a replication publisher.

My dev environment has no replication, but when I try to modify the schema in the db I restored I get the following error

"Server: Msg 4932, Level 16, State 1, Line 441
ALTER TABLE DROP COLUMN failed because 'url_web1' is currently replicated."

Does anyone know what I have to do to restore the database on my de box so it 'forgets' its live replication settings?
On my dev server I have no items in the replication folder in Enterprise Manager but it seems replication details are stored somewhere, perhaps in the backup itself?

Matt

View 10 Replies View Related

Views With Parameters Involved

Apr 2, 2008

Hi,

I have done a stored procedure but happend to know that people calling my database cannot access procedures only views. But my select query requires to parameters (two dates) in order to get the correct data.

How can I solve the problem?

View 6 Replies View Related

How To Get Tables Involved In Constraint

Jan 18, 2006

Hi,The following request select a constraint from TABLE_CONSTRAINT withthe name specified in the where clause:select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS whereconstraint_name = 'FK__51OtherParties__51Claims'It returns:http://graphicsxp.free.fr/constraint.JPGSo I have TABLE_NAME that correspond to the first table involved in theconstraint, but how do I get 51Claims ????Thank you

View 2 Replies View Related

Restoring The Database Involved In Replication!

Dec 5, 2003

Do we need to disable replication to do a restore? Any help on this?

View 1 Replies View Related

About Character Set And Sort Order Involved In Upgrade SQL 6.5 To 7.0

Jun 13, 1999

i have 2 questions,

1. cause there is no record of my old SQL 6.5 installation leaft for me,so i try to find the character set and sort order of my old SQL 6.5 installation, and in the MSSQLlog directory the errlog.? file there are these lines:

99/06/11 12:59:24.29 spid1 SQL Server's default sort order is:
99/06/11 12:59:24.29 spid1 'nocase' (ID = 52)
99/06/11 12:59:24.29 spid1 on top of default character set:
99/06/11 12:59:24.29 spid1 'iso_1' (ID = 1)

so my old installation's character set and sort order is as above,am i right?

2. cause i am in chinese so any good idea for me to upgrade this old SQL 6.5 to use chinese character set (cp936) , is there anyway to transfer the old data?

thanks if any suggestions!
welcome to send me mail about the question.

View 1 Replies View Related

INSERT OPENQUERY With XML When Remote Server Involved

Mar 13, 2008



Hi Guys,

We already have work around when it comes to read xml data type from remote servers
remote server (eurodata3) table

create table temp_asaf

(


xmlCol XML

)


SELECT


Cast(a.XML_Data as XML) as XML_Data

FROM

OPENQUERY(eurodata3,'

SELECT TOP 10

Cast(xmlCol as Varchar(MAX)) as XML_Data

FROM

em_port.dbo.temp_asaf'

) a


Here is a question for you. How do I insert data into remote server when data type for a column is xml?


INSERT OPENQUERY (eurodata3, '


SELECT Cast(xmlCol as Varchar(MAX)) AS xmlCol

FROM em_port.dbo.temp_asaf')

VALUES ('<html><body>MS Sql Server</body></html>');



Running above code would give me the following error:

OLE DB provider "SQLNCLI" for linked server "eurodata3" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 7344, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "eurodata3" could not INSERT INTO table "[SQLNCLI]" because of column "xmlCol". The user did not have permission to write to the column.

Location: memilb.cpp:1493

Expression: (*ppilb)->m_cRef == 0

SPID: 59

Process ID: 1660




Don't be misled by permission statement. I could successfully run similar query as long as the column is not xml on remote server. I would appreciate some ideas to get around with it.

View 3 Replies View Related

Using SCOPE_IDENTITY Inside A COM+ Component (Trigger Involved)

Jan 7, 2008



Hi,

I'm experiencing a problem I think I should not in my COM+ application. I will describe the setup first and then will expose the problem.

It's a simple COM+ application (dll). Inside it, there's a method to save an object A. Object A is persisted in a table in SQL Server 2000 that uses an identity field for the primary key. What this method does is the following:
1) Insert the record for Object A via ADO
2) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object
3) Execute an UPDATE statement based on a certain condition via ADO (this UPDATE statement will fire a trigger, however the trigger will not do anything since the record does not answer the criteria for the trigger)
4) Insert a record for another Object A via ADO
5) Retrieve the Id for the object using SCOPE_IDENTITY via ADO and set it on the object

When I get to step (5), an error is raised because SCOPE_IDENTITY returns NULL. This is as if it was returning the Identity value for the trigger that did not cause any INSERT on the UPDATE statement in (3). All the steps are performed using a single connection.

The trigger will duplicate the updated record in another table if a certain flag is set, so in my case, it was not set yet.

It's just weird that this would happen. If I delete the trigger, everything works fine. @@IDENTITY gives me the same problem. It's really as if the trigger was taking over or something and unless I put something between the two steps I get this error. There's one thing though. In step (3), I was using the adCmdText flag for the ADO statement. If I use adExecuteNoRecords it works fine. However I don't understand why it would be this way, I'm trying to understand why it's not working to begin with, even though the sequence of the steps performed should.

Any idea why this would happen?

Thanks,

Greg

View 3 Replies View Related

EFFECTIVE SQL STATEMENT SYNTAX

Oct 3, 2001

Hi all,

I am a java developer having little bit knowledge in sql statements.
can any body guide me what is the effective way of using sql statement
to achieve the following goal.

here is the requirement.

1. There are 2 tables A & B.

2.The table 'B' may or maynot have an equivalent entry for the primary
key of table a.

3. select column1 (of A), column 2(of B) from table a and b.

4. now column2 should give a value, if there is a corresponding
entry in table b.other wise null.

5. at any time there may be 0 to 1 record in table b for the primary
of table a.


thanks in advance
krishna

View 1 Replies View Related

History Using Effective Dating

Apr 4, 2006

I did a search of the forums and couldn't find any other threads on "Effective Dating" so please forgive me if this topic has already been covered.

Does anyone know of any support articles or tutorials covering the topic of "Effective dating" as a means for keeping a history for data?

I would like to keep a infinite history on certain tables and would like to avoid duplicating data structures if possible. Any suggestions?

Thanks,

JAndrews

View 4 Replies View Related

Triggers - Possible To Specify Effective Timeframe?

Oct 6, 2006

My gut tells me this can't be done quite the way I'm hoping, but maybe someone has a little insight...

Is it possible to specify a specific timeframe for which triggers should be effective? I have some make-shift replication tasks which are currently done via pure insert/update queries and various times throughout the day. In the am our "target" table is completely wiped and repopulated from our "source" table which resides on a different server.

I'm hoping to setup update triggers throughout the day to catch changes to our "source" table. These triggers should only be active for a six-hour period throughout the day. Unfortunately true replication isn't an option here.

I can think of a couple ways to take care of this such as checking GETDATE() from the trigger itself and deciding whether or not to do anything, or perhaps scheduling a job to enable and disable triggers at the appropriate time. Is there a way to setup such a schedule for the trigger itself?

View 4 Replies View Related

Effective Permission For A Group

Feb 8, 2007

When I click on the properties of a 2005 sql server database and thenpermissions.If I select a group and then effective permission, I got an error,saying that "cannot execute as the principal server because the 'xxxgroup' does not exist, this type of principal cannot be impersonated,or you do not have permission (Microsoft SQL Server; Error: 15406)"I guess the error is valid, since the group does not exist in thedatabase as a user. If so, how do I get the effective permissions of agroup?

View 1 Replies View Related

SSIS Package Runs Fine; SQL Job Fails (DBF Involved)

Jun 14, 2007

I have created an SSIS package which, as its 2nd step, picks up a .dbf table and does a Data Flow Task of putting the .dbf table into a SQL Server 2005 table (which was just truncated in Step 1).



The Connection Manager that works on the .dbf table is Native OLE DBMicrosoft Jet 4.0 OLE DB Provider.



The entire SSIS package runs perfectly when i run it from my development environment.



I then assigned this Package to a SQL Server 2005 Job. The entire package is the sole step of the job.



But when I run the SQL Job, the Job fails immediately on Step 2.



Here is the error message in View History of the SQL Job:

-----------------------------------------------------------------------------

Executed as user: DBASE01SQLExec. ...on 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:48:37 AM Progress: 2007-06-14 11:48:37.91 Source: DBF to ScanUS_Process Validating: 0% complete End Progress Progress: 2007-06-14 11:48:37.91 Source: DBF to ScanUS_Process Validating: 33% complete End Progress Error: 2007-06-14 11:48:37.91 Code: 0xC0202009 Source: DBF to ScanUS_Process ScanUS DBF Output File [1] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37. End Error Error: 2007-06-14 11:48:37.91 Code: 0xC02020E8 Source: DBF to ScanUS_Process ScanUS DBF Output File [1] Description: Opening a rowset for "scanus_process" failed. Check that the object exists in the database. End Error Error: 2007-06-14 11:48:37.95 Code: 0xC004706B Source: DBF to ScanUS_Process DTS.Pipeline Description: "component "ScanUS... The package execution fa... The step failed.

-----------------------------------------------------------------------------



Could someone suggest why this works OK within SSIS but not when the SSIS Pakcage is called from SQL?



thx very much/spirits.



seth j hersh

View 7 Replies View Related

What Is The Best And Most Effective Way To Find The Most Related Phrases?

Dec 10, 2007

Hi, I am looking for a sql statemnt that find the most related phrases, how to do that? For example, I want to find the related phrases bellow for the following phrase: "How to install the software"
1- "Installation does not work"
2- "installation"
3- "steps to install the software"
 
It's obvoius that phrase number 2 and 3 are related to that phrase, how to do that using sql?
 
Regards.

View 1 Replies View Related

How 2 Create An Effective Search On NTEXT ?

Nov 25, 2003

Dear SQL,

since I create some multi-language table - I want to allow finding unicode text
so I made the field: Key_Words" (ntext)

It will have a string that can include some words in different languages, so that I can find by using:
SELECT Key_Words FROM MyTable WHERE Key_Words LIKE '%" & MyVar & "%' "...

The problem is that I can not apply clustered index on ntext field (or *any* index...)

Any ideas how to deal with it ?

View 2 Replies View Related

What Is The Most Effective Method Of Record Locking?

Feb 25, 2004

I have built my own functions in the past that involve a timestamp and record lock column in each table (to support some other DB).

I am currently using SQL 2000 and was wondering if there is a better (OK, easier) way to lock records and even prevent edits from taking place when a record is open.

Any input appreciated.

View 25 Replies View Related

Tracking History Using Effective Dating

Apr 4, 2006

Can anyone provide me with some insight on tracking the history of my data using "Effective Dating"? I would like to keep track of an infinite history for many of the tables in my database and am trying to avoid duplicating data structures if possible. 
Any advice on this topic would be appreciated, I am most interested in any links to tutorials or support articles on subject that you may know about.  I have searched the web and the forums section and have not come across much.
Thanks,
JAndrews

View 4 Replies View Related

Working With Single Effective Date

May 20, 2008

I have a table with a single effective date, rather than both a start and stop date. I have to be able to match up this table to another one with service information in it and am not sure how to get the correct record selected.

So in table one I have a personID, effective date, and lots of other fields. There are also multiple records for each personID, so say personID 1 has records with effective dates of 1/1/2007, 6/1/2007, and 1/1/2008.

Table two has personID, Service Date, and lots of other fields.

So if I am looking to match up the effective row from table one to a record in table two with a service date of 8/1/2007, how do I get the db to locate and return the record with an effective date of 6/1/2007, and only this record?

View 6 Replies View Related

Most Effective Method Of Managing The Data?

Mar 2, 2004

I have a large SQL Database im building, in the database there is a table for each user with certain things listed. One problem is, sometimes in a few collums, there will be like 100things listed, instead of one line.


For Example:

Products in that some users will have 1 product like "apples" but others will have many more products.

What is the most effective way of listing the data? Create new tables? Seperate the products with comma's or spaces?


How do I do it, and keep the overall db size smaller?

Thanks

View 5 Replies View Related

I Need To Find The Most Recent Effective Date

Mar 10, 2008

Hi,

Not sure if you could help or not, but I need to pull the most recent effective date for this report I am trying to run, but I am getting know where. If someone can take a look at this, it would be great.
Example….
pcs number 00004344 effective dates 5/1/2006 and 5/1/2007. I need it to be the most recent effective date which would be, 5/1/2007 date.

Can someone help me?
USE [Impact_PROD]
GO
/****** Object: StoredProcedure [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES] Script Date: 03/10/2008 09:18:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[p_PrepareMalPracticeReportDataBYCPTCODES]

AS
BEGIN
SET NOCOUNT ON

DECLARE @STARTTIME DATETIME, @ENDTIME DATETIME
SET @STARTTIME = GetDate()

PRINT 'SP started on: ' + CAST(@StartTIME as varchar)
PRINT ''

DECLARE @PKey varchar(16), @pcsi_id1 varchar(8), @pcsi_id2 varchar(4) /**@pcsi_id3 varchar(4),@Lpcsi_id3 varchar(4)**/
DECLARE @LplID varchar(12), @LTrm Datetime, @Eff Datetime, @Trm Datetime, @Gap int, @Corrected bit
DECLARE @CTrm DATETIME, @i varchar(8), @LastID varchar(8), @LEff Datetime, @FinalEff DATETIME


SET @i = 0


IF OBJECT_ID('tempdb..#pcsiData') IS NOT NULL
DROP TABLE #pcsiData
IF OBJECT_ID('tempdb..#HoldKey') IS NOT NULL
DROP TABLE #HoldKey

IF OBJECT_ID('tempdb..#HoldKey2') IS NOT NULL
DROP TABLE #HoldKey2
SET DATEFORMAT mdy;

SELECT pcsi_id1 + pcsi_id2 AS pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
INTO #pcsiData FROM pcsi p
WHERE (SELECT COUNT(pcsi_id1 + pcsi_id2) FROM pcsi WHERE pcsi_id1 = p.pcsi_id1) > 1 --AND p.pcsi_prd = 'dgh'
ORDER BY p.pcsi_id1 + p.pcsi_id2 ASC, p.pcsi_eff1 ASC

--SET TRM DATES TO NULL WHERE DATE IS 1977-03-23 00:00:00.000
--(IMPACT XSQL process uses that date in place of null!)
UPDATE #pcsiData
SET pcsi_trm1 = null
WHERE pcsi_trm1 = '1977-03-23 00:00:00.000'

SELECT pcsi_id1 + pcsi_id2 as Pkey, (COUNT(pcsi_id1 + pcsi_id2)) AS DupCount --pcsi_eff1, pcsi_trm1, COUNT(pcsi_id1 + pcsi_id2) AS DupCount
INTO #holdkey
FROM #pcsiData
GROUP BY pcsi_id1 + pcsi_id2
HAVING count(*) = 1

IF EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[NonDuppcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE NonDuppcsiDataForMalPracticeReport

SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1 INTO NonDuppcsiDataForMalPracticeReport
FROM #pcsiData
WHERE pcsi_id1 + pcsi_id2 IN(SELECT pkey from #Holdkey)

DELETE FROM #pcsiData
WHERE pcsi_id1 + pcsi_id2 IN (SELECT pkey from #HoldKey)

DROP TABLE #HoldKey

SELECT pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1, count(*) as NoofDup
INTO #HoldKey2
FROM #pcsiData
GROUP BY pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
HAVING count(*) > 1

SET NOCOUNT OFF
DELETE #pcsiData
FROM #pcsiData, #holdkey2
WHERE #pcsiData.pcsi_id1 = #holdkey2.pcsi_id1
AND #pcsiData.pcsi_id2 = #holdkey2.pcsi_id2


drop table #holdkey2

SET NOCOUNT ON

IF EXISTS
(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[pcsiDataForMalPracticeReport]') AND OBJECTPROPERTY(id, N'IsTable') = 1)
DROP TABLE pcsiDataForMalPracticeReport

--CREATE TABLE pcsiDataForMalPracticeReport (pcsi_pkey varchar(16) PRIMARY KEY, pcsi_id1 varchar(8), pcsi_id2 varchar(4), pcsi_id3 varchar(4), pcsi_eff1 varchar(8), pcsi_trm1 varchar(8), Corrected bit)

SELECT pcsi_id1 + pcsi_id2 as pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
INTO pcsiDataForMalPracticeReport
FROM #pcsidata p
WHERE pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM #pcsidata WHERE pcsi_id1 = p.pcsi_id1 AND pcsi_id2 = p.pcsi_id2)


DECLARE cur CURSOR FAST_FORWARD FOR
SELECT pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
FROM #pcsiData
--group by pcsi_pkey
Order By pcsi_id1 + pcsi_id2, pcsi_eff1 ASC

OPEN Cur
FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm

SET @lplID = @pcsi_id1 + @pcsi_id2
SET @LEff = @Eff
SET @Ltrm = @Trm


FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @i = 2


DELETE FROM tmppcsiDatesWithGaps --Clear table used for debugging

WHILE @@FETCH_STATUS = 0
BEGIN --Begin While Loop

IF @pcsi_id1 + @pcsi_id2 = @lplID
BEGIN --If current record is for the same provider location as the last then...

SET @Gap = DATEDIFF(day, @Ltrm, @Eff)
IF @Gap > 2
BEGIN --If there is a gap greater than 1 day...
PRINT ''
PRINT 'GAP between fetch ' + str(@i - 1) + ' and ' + str(@i) + ' (' + @pcsi_id1 + ' ' + @pcsi_id2 + ' ' + '): ' + str(@gap) + ' days! '
PRINT 'Last Trm: ' + CAST(@LTrm AS varchar) + ' Eff: ' + CAST(@eff AS Varchar)
PRINT ''
--IF EXISTS (SELECT * FROM pcsiDataForMalPracticeReport WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2)
--IF @pcsi_id1 + @pcsi_id2 NOT IN (SELECT pcsi_id1 + pcsi_id2 FROM tmppcsiDatesWithGaps)
--BEGIN --Begin if effective date was not already updated
--IF @Leff > @Eff
UPDATE pcsiDataForMalPracticeReport
SET pcsi_eff1 = @Eff-- pcsi_Ltrm = @LTrm
WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2
--ELSE
--UPDATE pcsiDataForMalPracticeReport
--SET pcsi_id3 = @lpcsi_id3, pcsi_eff1 = @LEff-- pcsi_Ltrm = @LTrm
--WHERE pcsi_id1 = @pcsi_id1 AND pcsi_id2 = @pcsi_id2

--ELSE
--INSERT INTO pcsiDataForMalPracticeReport (pcsi_pkey, pcsi_id1, pcsi_id2, pcsi_id3, pcsi_eff1, pcsi_trm1)
--VALUES (@pcsi_id1 + @pcsi_id2 + @pcsi_id3, @pcsi_id1, @pcsi_id2, @lpcsi_id3, @LEff, @Ltrm)

INSERT INTO tmppcsiDatesWithGaps (pcsi_id1, pcsi_id2, lpcsiid, EffectiveDate) VALUES (@pcsi_id1, @pcsi_id2, @lplid, @Eff)
--END --End if effective date was not already updated
END --End if there is a gap greater than 1 day
END --End if the provider location is different than the last row

--Set current rows data in last rows variables...

SET @lplID = @pcsi_id1 + @pcsi_id2
SET @LEff = @Eff
SET @Ltrm = @Trm

--Get next row of data

FETCH NEXT FROM cur INTO @pkey, @pcsi_id1, @pcsi_id2, @eff, @trm
SET @i = @i + 1 --increment i
PRINT 'Iteration #' + str(@i) + ' -- ' + @pkey + ' Eff: ' + Cast(@Eff as varchar) + ' ' + ' Trm: ' + cast(@trm as varchar)



END --End While Loop

INSERT INTO pcsiDataForMalPracticeReport
SELECT distinct pcsi_id1 + pcsi_id2, pcsi_id1, pcsi_id2, pcsi_eff1, pcsi_trm1
FROM NonDuppcsiDataForMalPracticeReport
/***
UPDATE #pcsidata
SET pcsi_trm1 = '20470101'
WHERE pcsi_trm1 is null OR pcsi_trm1 = ''
/**
SELECT p.pcsi_id1, p.pcsi_id2, MAX(p.pcsi_trm1)
INTO #HoldKey2
FROM #pcsidata p
group by p.pcsi_id1, p.pcsi_id2
ORDER BY p.pcsi_id1

UPDATE R SET pcsi_trm1 = I.pcsi_trm1 FROM pcsiDataForMalPracticeReport R
INNER JOIN #HoldKey I
ON r.pcsi_id1 = I.pcsi_id1
AND r.pcsi_id2 = I.pcsi_id2
**/

Print ''
Print ''
Print ''
Print 'SETTING MAX TERM VALUES NOW....(This may take a while)'
Print ''
Print ''
UPDATE pcsiDataForMalPracticeReport
set pcsi_trm1 = jp.MaxTrm
FROM pcsi p JOIN (SELECT pcsi_id1, pcsi_id2, MAX(pcsi_trm1) as maxtrm FROM pcsi p2
--WHERE p2.pcsi_id1 + p2.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2
GROUP BY p2.pcsi_id1, p2.pcsi_id2) jp ON (jp.pcsi_id1 + jp.pcsi_id2 = p.pcsi_id1 + p.pcsi_id2)

DECLARE @NotTermed int
SET @NotTermed = (SELECT COUNT(*) FROM pcsiDataForMalPracticeReport WHERE pcsi_trm1 = '20470101')
PRINT''
PRINT 'Total non-duplicate records not termed: ' + str(@NotTermed)


--UPDATE pcsiDataForMalPracticeReport
--SET pcsi_trm1 = NULL
--WHERE pcsi_trm1 = '20470101'
**/
UPDATE pcsiDataForMalPracticeReport
SET pcsi_trm1 = NULL


PRINT ''
PRINT 'STEP TWO.......................'
PRINT 'Preparing the table names...tmpMalPracticeEffectiveDates'
PRINT''
--This step updates tmpMalPracticeEffectiveDates with the desired effective date and most recent termination date
--if there are no current records with a termination date = NULL

TRUNCATE TABLE tmpMalPracticeEffectiveDates

SET NOCOUNT OFF

PRINT 'Inserting new data into tmpMalPracticeEffectiveDates'

INSERT INTO tmpMalPracticeEffectiveDates
(pcsi_id1, pcsi_id2, pcsi_eff1)
SELECT DISTINCT pcsi_id1, pcsi_id2, pcsi_eff1
FROM pcsi p
WHERE p.pcsi_eff1 = (SELECT MIN(pcsi_eff1) FROM pcsi p2
WHERE p2.pcsi_id1 = p.pcsi_id1 AND p2.pcsi_id2 = p.pcsi_id2)
ORDER BY pcsi_id1, pcsi_id2
------------------------------
--Set temp bogus date to distinguis which records are current in
--subsequent statement
PRINT 'Setting bogus date to distinguish pcsi records that are not termed'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = '12/21/2049'
WHERE '03/23/1977' IN (SELECT pcsi_trm1 FROM pcsi p WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1 AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2)
-------------------------------
PRINT 'Setting most recent term date for pcsi records that are not currently active'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = (SELECT MAX(pcsi_trm1) FROM pcsi p
WHERE p.pcsi_id1 = tmpMalPracticeEffectiveDates.pcsi_id1
AND p.pcsi_id2 = tmpMalPracticeEffectiveDates.pcsi_id2)
WHERE tmpMalPracticeEffectiveDates.pcsi_trm1 is NULL
-------------------------------
PRINT 'Setting bogus dates back to NULL'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_trm1 = NULL
WHERE pcsi_trm1 = '12/21/2049'
-------------------------------
--CORRECT EFFECTIVE DATES WITH GAPS...
PRINT 'Correcting Effective Dates for those records with gaps in credentialing records'
UPDATE tmpMalPracticeEffectiveDates
SET tmpMalPracticeEffectiveDates.pcsi_eff1 = t.EffectiveDate
FROM tmppcsiDatesWithGaps t
WHERE tmpMalPracticeEffectiveDates.pcsi_id1 = t.pcsi_id1
AND tmpMalPracticeEffectiveDates.pcsi_id2 = t.pcsi_id2

----END OF SP---


DECLARE @Diff decimal
SET @ENDTIME = getdate()
PRINT ''
PRINT ''
DECLARE @GapCount int
SET @GapCount = (SELECT COUNT(*) FROM tmppcsiDatesWithGaps)
PRINT 'Total number of non-distinct provider locations: ' + Str(@i) + '.'
PRINT 'Total number of gaps found: ' + Str(@GapCount) + '.'
PRINT 'FINISHED ON: ' + cast(@ENDTIME as varchar)
SET @Diff = CAST(DATEDIFF(second, @StartTime, @EndTIME) AS varchar)
PRINT ''
PRINT 'Time elapsed: ' + str(@Diff) + ' seconds.'
PRINT ' = ' + str(@Diff/60) + ' Minutes!'


END

View 7 Replies View Related

Report Effective Permissions For All Users?

Jul 20, 2005

As our customers demand that we tighten our IT security in the company,I've been asked to prepare a report quarterly showing, for each user inActive directory, what his effective permissions are for every table inevery database that he has permission for on our SQL Server 2000 server. Isearched a bit for a tool to do this, but all I found was the PERMISSIONS()function for showing effective permissions of the current user. Is thereany way to do it for an arbitrary user, without logging in as them?

View 5 Replies View Related

Server Properties - Effective Permissions

Jun 10, 2007

In SSMSE, if I open Server Properties and then Permissions, I can highlight a user or user group and click the 'Effective Permissions' button.



For entries like 'BUILTINUsers' or 'BUILTINAdminsitrators' I get the following error message. I don't understand what it's telling me - can anyone explain?



TITLE: Microsoft SQL Server Management Studio Express
------------------------------

Cannot show requested dialog.

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

------------------------------

Cannot execute as the server principal because the principal "BUILTINUsers" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15406)



Notes: as fara as I know, BUILTINUsers does exist, and I do have permission.

View 1 Replies View Related

Lookup Transformation Using Effective Dates

Jul 13, 2006

Hi,

I need to perform a lookup based on a business key and a date in the source that must be between an effective from and effective to date in the reference table. I've been able to achieve this by updating the SQL caching command on the advanced tab but the performance is very slow. I have 6 lookups of this type in the data flow with a source SQL statement that returns approx 1 million rows and this package takes over 90 minutes to run.

The caching SQL command now looks like this

select * from
(select * from [ReferenceTable]) as refTable
where [refTable].[Key] = ? and ? BETWEEN [refTable].[StartDate] AND [refTable].[EndDate]

and I've set up the parameters so that the business key is the first parameter and the source date is the second.

I have another lookup in the flow that does a straight equality comparison using 2 columns and the Progress tab shows that this lookup is cached (even though I haven't enabled it on the Advanced tab of the transformation editor) but none of the other lookups (using the date) appear to be cached, even though I have enabled them to be.

Can anyone suggest how I can improve the performance?

Thanks.

View 2 Replies View Related

SQL 2012 :: Access Rights To Execute Stored Procedure If There Is No DML Involved

Jul 2, 2014

I would like to provide the db_datareader and db_executor role to a particular SQL Server Login in a database
But, I would like to avoid any INSERT's, UPDATE's or DELETE's that may happen by calling the stored procedures

I tried assigning the db_denydatawriter role but it doesn't seem to be doing the trick as the INSERT's, UPDATE's and DELETE's were still working

Is there any way to provide the db_datareader and db_executor role but avoid any DML actions.

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved