Please Assist With Common Error 2147467259
Aug 16, 2007
I'm not sure if I'm even starting at the right place.
We have a web portal that we use to send email TSN Templates. When we press this link, it queries our SQL database but then times out. When we go to our logs to see waht has happened, this is what we get: Can anyone point me in the right direction?
Timestamp: 15/08/2007 9:00:13 AM
Message: HandlingInstanceID: 5228cfc1-fbc3-4c64-98ab-468d0a99ba85
An exception of type 'System.Web.HttpException' occurred and was caught.
------------------------------------------------------------------------
08/15/2007 19:00:13
Type : System.Web.HttpException, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
Message : Request timed out.
Source :
Help link :
ErrorCode : -2147467259
Data : System.Collections.ListDictionaryInternal
TargetSite :
Stack Trace : The stack trace is unavailable.
Additional Info:
MachineName :
TimeStamp : 15/08/2007 9:00:13 AM
FullName : Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.0.2697.40343, Culture=neutral, PublicKeyToken=d6be776f5b1b3d1f
AppDomainName : /LM/W3SVC/1/ROOT-1-128316415134243452
ThreadIdentity :
WindowsIdentity : NT AUTHORITYNETWORK SERVICE
Category: General
Priority: 0
EventId: 100
Severity: Error
Title:Enterprise Library Exception Handling
Machine:
Application Domain: /LM/W3SVC/1/ROOT-1-128316415134243452
Process Id: 36976
Process Name: C:WINDOWSSysWOW64inetsrvw3wp.exe
Win32 Thread Id: 35804
Thread Name:
Extended Properties:
For more information, see Help and Support Center at
View 1 Replies
ADVERTISEMENT
May 5, 2003
Hi everybody,
I do custom log shipping over network like
step 1 backup log to C:..
step 2 zip log file c:... zip
step 3 copy zip to \sm01
estorelogs...
step 4 unzip file
step 5 restore log with stand by
Few times a month it fails with
Error = -2147467259 (80004005) Error string: Timeout expired Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: Timeout expired Error source: Microsoft OLE DB Provider for SQL Server
Any idea ,what might cause it ?
Thank you
Alex
View 3 Replies
View Related
Jan 7, 2008
Hello there.
1. Can someone help me?
I am running an eCommerce website using SQL Server 2000, as of last week it was working just fine, today I try to access the sight and I get the following error below.
I haven't changed anything on the server, the SQL Server services is running so is the agent.
Operation: Creating Engine object
Error number: -2147467259
Error source: Microsoft OLE DB Provider for SQL Server DatabaseUtils.OpenCommand DatabaseFile.IsDatabaseByteArrayUpdated SchemaFile.IsDatabaseSchemaUpdated Environment.InitEnvironment, Step: Loading database schema Engine.Init Engine.Refresh
Error description: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
2. When I refresh the browser I get this error:
Operation: Creating Engine object
Error number: 424
Error source: Microsoft VBScript runtime error
Error description: Object required
Thanks in advance
View 2 Replies
View Related
Jan 26, 2007
Hello,
I am importing an excel spreadsheet into SQL Server 2000. The dts package runs perfectly when you execute it under the Local Package pane.
My problem is when i schedule a job to execute the DTS package giving me the same error. I can only successfully execute the package under local Packages. Under Jobs i receive this error:
Executed as user: H-OBAdministrator. ...rt: Copy Data from 'Xagent - Report$' to [CIMPRO1].[dbo].[Xagent_Star_Coater_Points] Step DTSRun OnError: Copy Data from 'Xagent - Report$' to [CIMPRO1].[dbo].[Xagent_Star_Coater_Points] Step, Error = -2147467259 (80004005) Error string: 'R:sharedstar coaterMonthly Points RptCurrent_Month_Points.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft JET Database Engine Help file: Help context: 5003044 Error Detail Records: Error: -2147467259 (80004005); Provider Error: -534774783 (E01FFC01) Error string: 'R:sharedstar coaterMonthly Points RptCurrent_Month_Points.xls' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Error source: Microsoft JET Database Engine ... Process Exit Code 1. The step failed.
Any help on this error would be more than appreciated.
View 2 Replies
View Related
Apr 27, 2004
We have a SQL Server 2000 in a Windows 2003 Server, and we were working very well with the VB 6.0, but after an update in the Windows 2003, the Open method in ADO results in an error -2147467259 sometimes, but if we try the same code in a computer with another domain there are not any problem, some advice?
View 1 Replies
View Related
Dec 5, 2007
Hello, we've developed an application that runs on AXIM X51, if it is an important to know.
The data layer handles the connection using the singleton pattern, a bit of code of this is:
(....)
Private Shared ceConnection As SqlCeConnection
(....)
Public Shared Function GetConnection() As SqlCeConnection
Try
If (ceConnection Is Nothing) Then
ceConnection = New SqlCeConnection(Me.connectionString)
End If
If (ceConnection.State = ConnectionState.Closed) Then
ceConnection.Open()
End If
Return ceConnection
Catch ex As Exception
Throw ex
End Try
End Function
(....)
So far nothing strange. We started experiencing problems when the end-users get an "Unspecified error" which later debugging it we got the href value inside the exception object is -2147467259 and nothing else. And if that wasn't enough I couldn't replicate the error in my pocket.
The only way I got the same error was removing the SD Card where the db is located at the same time the application was still open.
Does anybody experienced the same problem??? I researched about this kind of errors "when the device is just restored from the suspend mode" but with out luck.
Please, I really appreciate any help with this issue. Any comment will be more than welcome.
View 3 Replies
View Related
Apr 6, 2015
I have a procedure that works fine from SSMS but sometimes fails when running via SQL Agent. The SP sends emails..Here is the error message "Failed to initialize sqlcmd library with error number -2147467259"
<p>Javier Villegas | <a href="http://www.twitter.com/javier_vill" target="_blank" title="Twitter" rel="nofollow"> @javier_vill</a> | <a href="http://sql-javier-villegas.blogspot.com/"
rel="nofollow">http://sql-javier-villegas.blogspot.com/</a></p>.
View 3 Replies
View Related
Feb 5, 2008
Hi All,
I've had a DTS Package scheduled to run every morning since June last year, however on Monday and again this morning, the DTS package has failed on two of its steps. This DTS package runs on SQL Server 2000 and the server is a Windows 2000 Server.
Basically, the DTS Package steps that fail are populating a "cache" table from a "live" table and then performing Inserts / Updates on the "live" table from a CSV file depending upon whether data exists in the "cache" table and whether the CSV file contains an "Insert" or "Update" flag. The live table has just over 800,000 rows of data and our nightly CSV extract, containing both inserts and updates, has about 6000 rows.
The error / failure has only happened in the last two days and I'm not too sure if there's any "timeout" feature in DTS and if there is, how I can modify it, or whether this may be something to do with long transactions or temp dbspace running out.
The time that the DTS package runs was modified last week to run two hours earlier (03:00 instead of 05:00) and has ran okay for a few days. I don't know whether this change is a red herring, as I have checked other DTS packages on the machine and none appear to run at the same time.
Here's the message from the third step in the DTS Package (second one that's failing).
Executed as user: SOEincaservice. ...
Start: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_10
DTSRun OnError: DTSStep_DTSExecuteSQLTask_1,
Error = -2147467259 (80004005)
Error string: Timeout expired
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005);
Provider Error: 0 (0)
Error string: Timeout expired
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnError: DTSStep_DTSExecuteSQLTask_10,
Error = -2147467259 (80004005)
Error string: Timeout expired
Error source: Microsoft OLE DB Provider for SQL Server
Help file:
Help context: 0
Error Detail Records:
Error: -2147467259 (80004005);
Provider Error: 0 (0)
Error string: Timeout expired
Error source: Micros... Process Exit Code 2.
The step failed.
Any help is greatly appreciated as I've searched these forums and the web and can't really find any answers.
Cheers,
David
View 4 Replies
View Related
Feb 28, 2008
Hi - have 150,000,000 row table receiving BULK INSERTS all day - I have also found out my client likes to performan READ opertions simultaniously against this table with of course chronic contention issues.
Need to provide a quick-fix, will index views enable me to abstact the reporting data away from the BULK INSERT table and is there any connection issues between the base table and the Index View?
View 2 Replies
View Related
Sep 14, 2007
We are runnig Windows server 2003 R2 and SQL Server 2005 Standard on the same computer.
We have workstations that log into our domain with Windows Authentication. The software loads just fine and then later up pops the error noted above.
Any help you can give me is greatly appreciated.
Thanks
Paul
View 1 Replies
View Related
Jun 13, 2007
I am working on building a template/design pattern for a DTS to SSIS upgrade project.
During our ETL processing, if we encounter a record that cannot be inserted into a destination table, we'd like to be able to write the entire record out to a common error/reject table. The obvious problem is that every SSIS package that is using this template will of course be dealing with varying table schemas.
I was thinking that if there were a way that I could transform the error record/buffer row into XML, I could then achieve my goal of having a common table to receive errors/rejects.
Has anyone done something like this, or have suggestions on how we might accomplish?
View 7 Replies
View Related
Dec 17, 2007
Hello friends,
i am newly installed my client server from there i suppose to access the application.... when i clicked on the application icon it showing he following message:
Please note down the Error and Call EDP!!!!
Error number->[-2147467259]
Error Text->[[DBNETLIB][ConnectionOpen(Connect()),]SQL Server does not exit or access denied,]
am getting the above error..... can any one through your view plz.........
ENV: windows 2003 small business server, MSSQL (server)
windowsXP (client)
its working fine with all other client mechines.........
View 1 Replies
View Related
Aug 3, 2006
I am having this error when using execute query for CTE
Help will be appriciated
View 9 Replies
View Related
Mar 13, 2007
I'm building an exception management system into my application.
What are the most common errors when working with a DAL and a SQL server.
The biggest problem can be Database connection.
My questions:
* Are all SQL related errors catched by the SqlExeption class?
* Which are the most common sql errors?
View 1 Replies
View Related
Mar 31, 2004
I have a stored procedure I use to fill my datagrid..... While doing this is there a way to get the number of records returned...
I use a datareader........any help
View 2 Replies
View Related
Aug 14, 2005
I just inherited a Java application with a fairly complex data modelthat does not yet have any indexes except those on primary keys. It isstill in development and before I get to do any performance testing Iwant to put some default indexes on it. I realize that this a veryvague request but it's better than nothing so I want to start withthose that are generally a good idea and then tune those areas thatrequire more fine grained approach. By the way, I'm mostly a Javaprogrammer so please forgive my DB ignorance and I thank you for anyhelp.Since the full schema is quite large I will use a simple example (fullycited below my questions). Here is list of indexes that I think wouldbe a good idea. If anyone can add to it or comment on my questions Iwould appreciate it.1. Index on primary keys in all three tables. My understanding thatthis indexing happens automatically just by declaring that a column isa PK.ALTER TABLE employees ADD PRIMARY KEY (emp_id);ALTER TABLE employee_addresses ADD PRIMARY KEY (address_id);ALTER TABLE departments ADD PRIMARY KEY (dept_id);Question: Does index get created automatically because this is a PK?2. Index on foreign keys in the children tables to prevent deadlocksand lock escalations.CREATE INDEX fk_index ON employee_addresses (emp_id)3. Indexes on common queries on all three tables.CREATE INDEX common_query_idx on employees(last_name, first_name,position)CREATE INDEX common_query_idx on departments(last_name, first_name,position)CREATE INDEX common_query_idx on employee_addresses(street, city)Question: Given that the searches can be on any field separately andin any combination should I also put an index on each columnindividually or will the composite index take care of individualsearches as well? For example, will the above indexes be sufficientfor the following SELECT:SELECT e.last_name, e.first_name from employees e, departments d,employee_addresses ea, dept_employees de WHERE e.emp_id = de.emp_id ANDd.dept_id = de.dept_id AND ea.emp_id = e.emp_id AND e.position ='master chief' AND d.dept_name = 'galactic affairs' AND ea.city='LosAngeles'4. Unique index on the association table. Again this is accomplishedusing PKALTER TABLE dept_employees ADD PRIMARY KEY (dept_id, emp_id)Question: Is the index on (dept_id, emp_id) automatic because of PK?5. The association table has to go both ways and PK takes care only ofthe first half. Thus add an index to go the other way.create unique index dept_employee_idx on dept_employee(emp_id,dept_id)Question: should I use INDEX ORGANIZED TABLE?Question: should I have UNIQUE qualifier on the second index given thatPK already takes care of it?Thanks,Robert===== EXAMPLE ======1) An employee can be in many departments and a department can containmany employees.2) Common searches for employees are on last_name, first_name,position, department_name, department_location separately and in anycombination.3) There are common searches for departments that contain certainemployees e.g. find all departments containing John Smith.CREATE TABLE employees(emp_id INTEGER NOT NULL,last_name VARCHAR(50) NOT NULL,first_name VARCHAR(25) NOT NULL,position VARCHAR(10) NOT NULL);CREATE TABLE employee_addresses(address_id INTEGER NOT NULL,emp_id INTEGER NOT NULL,street VARCHAR(50) NOT NULL,city VARCHAR(25) NOT NULL,);CREATE TABLE departments(dept_id INTEGER NOT NULL,dept_name VARCHAR(50) NOT NULL,dept_location VARCHAR(25) NOT NULL,);CREATE TABLE dept_employees(dept_id INTEGER NOT NULL,emp_id INTEGER NOT NULL,);ALTER TABLE employee_addresses ADD FOREIGN KEY (emp_id) REFERENCESemployees(emp_id)ALTER TABLE dept_employees ADD FOREIGN KEY (emp_id) REFERENCESemployees(emp_id)ALTER TABLE dept_employees ADD FOREIGN KEY (dept_id) REFERENCESdepartments(dept_id)
View 7 Replies
View Related
Aug 19, 2005
Hi, I would like to setup a common password for all the MS SQL Server Users. Is there any generalized script for the same ? Thanks In Advance.
View 7 Replies
View Related
May 28, 2008
Hi
I was studying Common Table expression in Sql server 2005.
I have written the code
Declare @PictureArray as varchar(200)
Set @PictureArray = '';
with UserProfile_CTE(UserPicture)
As(
select @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
select @PictureArray
)
select * from UserProfile_CTE
But I am getting the error
Incorrect syntax near '='
I am getting the error in the lineselect @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102
But I don't know the reason for this,
Kindly advice
Regards
Karan
View 3 Replies
View Related
Jan 7, 2006
hi i am using MS SQL Server 2000, i want to delete the common row from a table
table name EMP
rows
Eno Name Sal
1 Jim 1000
2 Mark 1200
1 Jim 1000
2 Mark 1250
1 Jim 1300
no primary key defile in to the table ....
i want to delete the row of
1 Jim 1000
please anybody can can help me ...
samarjit
View 1 Replies
View Related
Dec 1, 2005
I have a database with 10 tables
I have a field named CustID .... I want to find out how many tables have this field CustID
Now one way was check up the design of every table & see whether its there or not (the field)
is there any query through which we can get this info Please help
View 4 Replies
View Related
May 11, 2006
In my experience of going through plenty of interviews, I have came across a question that I am reminded of by someone's signature.
The common question in this instance is:
What is the advantage of a cursor over a stored procedure? Vice Versa?
I could only, in my so few experiences, have answered that stored procedure consumes less system resources than a cursor. The advantage a cursor has is record by record examination and looping capabilities.
Needless to say that whenever asked those questions, I haven't landed the position. So I'm wondering, what is the more appropriate answer?
View 20 Replies
View Related
Aug 8, 2006
Hi,
Do anyone know or have a suggestion how to get commun columns name between several table in a SQL server database
For example I have table_1(name, age, school) table_2(name, address, city) and table_3(name, department, company)
name is the common column name (I don't care about the data) in this case. Is there a way to do it simple and easy ?
THANK YOU
View 6 Replies
View Related
Sep 21, 2007
Hi - I'm trying to construct a select statement from the following tables(see example)
_________________________________
|Customer | Product |Shop |
|----------|----------|---------|
|Customer1 | Milk |Dairy |
|Customer2 | Cream |Dairy |
|Customer3 | Milk |Dairy |
|Customer1 | Trainers |Sports|
|Customer2 | Football |Sports|
_________________________________
REQUIRED RESULT SET: Milk, Cream.
Basically I want to select all of the Products (no duplicates) where EVERY customer(1, 2 & 3) have bought a from a common shop type: i.e 'Trainers' and 'Football' should not be selected as Customer3 has not bought any goods from a sports store.
This is an example of a larger problem where there can be numerous products, customers and shops. Here's a DESCRIBE of the relevent columns in each of the tables:
CUSTOMER:
customerID
PRODUCT:
productID
customerID
shopID
SHOP:
shopID
This looks like it should be easy but my SQL isn't the best ;-)
I'd really appreciate any help you could give!!
Cheers!
View 1 Replies
View Related
Jan 14, 2008
I try to find the best design for a SQL Server 2005 database structure.
I have the following 'objects':
Facilities
Departments - 'dependents' of a Facility
Users
Addresses - each of the above can have none or more Addresses.
The basic tables design would be:
CREATE TABLE [dbo].[Facility](
[cFacilityID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED ,
[cFacilityName] [nvarchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Department](
[cFacilityID] [nvarchar](5) NOT NULL FOREIGN KEY REFERENCES [dbo].[Facility] ([cFacilityID]) ON UPDATE CASCADE ON DELETE CASCADE,
[cDepartmentID] [nvarchar](3) NOT NULL,
[cDepartmentName] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED ( [cFacilityID], [cDepartmentID])
)
GO
CREATE TABLE [dbo].[User](
[cUserID] [nvarchar](5) NOT NULL PRIMARY KEY CLUSTERED,
[cUserName] [nvarchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Addresses](
[pkAddress] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[cAddress] [nvarchar](255) NULL,
[cZip] [nvarchar](5) NULL,
[cEmail] [nvarchar](255) NULL
)
GO
The question regards the Addresses table:
How can I design the Addresses table(s) so I can enforce relational integrity and cascade update and delete for each Facility, Department and User tables?
I figured out the following options, but none achives my requirements:
1. An Address table for each 'object': FacilityAddress, DepartmentAddress, UserAddress with the corresponding foreign key in each Address table. This solution duplicates all the addresses fields (which are more than I showed in the example).
2. A common Addresses table with a discriminator field (1=Facility, 2=Department, 3=User). This doesn't allow me to enforce referential integrity with constraint, maybe only with triggers.
3. A common Addresses table with a foreign key for each 'object (fkFacility, fkDepartment, fkUser) allowing nulls. In this case, the 'dependence' between Facility and Department stops me to enforce cascade deletes/updates on both Facility and Department foreign key (circular...).
4. An intermediate (link) table between each 'object' and Addresses table with the foreign keys (Facility, Addresses). Again, no referential integrity.
So, is there a method to design such structure?
PS. Please keep in mind that this is just a simplified example (reduced at the key information) of my real structure.
Thanks,
Ioan
View 8 Replies
View Related
Oct 2, 2007
At the risk of asking a stupid question -Is anyone familiar with either a query against the systables or maybean outside tool that will provide a list of the most common joins thathave been made in user created views? I'm not talking about tablerelationships that are established at the database level, but rather,I'm referring to the ability to find which joins have been utilized inpoorly constructed databases where no relationships were establishedin the first place.Thanks in advance -Cindy T.
View 2 Replies
View Related
Jul 20, 2005
What is the SQL Server equivalent of DB2 common table expressions? Forexample,with gry(year,count) as(select floor(sem/10),count(distinct ssn)from gradesgroup by floor(sem/10))select year,sum(count) Head_Count from grygroup by yearhaving year >= 1980;N. ShamsundarUniversity of Houston
View 2 Replies
View Related
Dec 18, 2006
I see that part of SP2 is the new CC certification -- can anyone give me some more details. What level is it at? EAL4? I can't seem to find the certificate on the CC Web site but it could be because it's not SP2 yet.
View 3 Replies
View Related
Jan 22, 2008
Hi there!
I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.
When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.
First, what do you think of that implementation?
We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...
What is the common solution for this need?
Thanks
Frantz
View 4 Replies
View Related
Jul 19, 2007
The comapny i work for has a server running the following.
Opteron 246x2
2 Gig memory
320 Gig Sata 2 drives
Windows 2003 Standard Edition
SQL Server 2005 Express Edition. The Free one.
There are approx 10 users that connect to the server.
There are two programs which seem to use sql server. Act 7.0 and service ceo.
When the computer is rebooted its at 0% for sqlserver.exe
Than when all connect it maxes it 50% and its steady there. Seems Service ceo affects it the most.
Told by comapny need to buy full blown sql server to resolve problem. But i dont think this is the problem.
Questions: Is it common for server to be at 50% all the time with sql server running?
And if its not is there a way to reduce the sql cpu usage.
I am new to sql server and have done alot of research and fixes. Ive unsitalled and reinstalled all sql instances and done the tweaks suggested. Any fresh ideas would be great thanks.
John
View 3 Replies
View Related
Feb 14, 2008
I need to get the 5 most common occurences from a table. Not really sure of the SQL statement I would use to do that. Would someone be kind enough to throw out a select statement that would do this? psuedo code would be fine, I just need to know the jest of how to do it. I originally thought Select Top 5 would work, but then I actually started thinking and realized it wasnt it. So now im stuck. Any help would be appreciated.
Thanks,
Steve
EDIT: I should add that this will be coming from a view with multiple tables..thanks
View 3 Replies
View Related
Apr 17, 2007
I need to display the company logo on 100 odd reports that run from different folders on the report manager. Since the company logo has changed before and we had to change every report, I want the image stored on a server (with SSL on) and want the reports to point to the server. The server is going to be different in development and production environments.
When I try to add the image using "Image Wizard" I get the error "Invalid image.". The wizard wouldn't let me use a function to build the path based on the environment and select the image from the correct server. It doesn't accept anything that doesn't start with "http:" when I select "web" as the image source.
Please Help!!!
View 4 Replies
View Related
Jan 12, 2007
Hi
The white paper which compares SQL Express and SQL Compact says that they both share Common Features
Can someone please indicate if SQL Compact will support functions like
Average, STDEv etc
Thanks
Touraj
View 7 Replies
View Related
Aug 31, 2006
Hi guys,
I know its not a good idea to give you a big query...but/...
I have a query (given below) with Common table expressions. It is giving me error : The multi-part identifier "dbo.tmpValidServices_ALL.Service" could not be bound. for all the fields .. Do you have any idea.. I fed up with analysing query.. made me mad.. please help me...
----------------------------------------------
DECLARE @sql nvarchar(MAX), @paramlist nvarchar(4000)
SET @sql = 'WITH Q1 (SiteID,Programme,Complete,PID,COC,NotionalSum,TVMinsSUM,Postcode,Suburb,Qty)
AS
(SELECT
dbo.tmpValidServices_ALL.SiteID,
dbo.tmpValidServices_ALL.Programme,
dbo.tmpValidServices_ALL.Complete AS Complete,
dbo.tmpValidServices_ALL.RID AS PID,
dbo.tmpValidServices_ALL.COC# AS COC,
(dbo.lkpService.Notional$) AS NotionalSum,
(TVMins) AS TVMinsSUM,
dbo.tmpDemographics_ALL.Postcode,
dbo.tmpdemographics_ALL.Suburb,
count(*) as Qty
FROM lkpService
INNER JOIN dbo.tmpValidServices_ALL vs ON dbo.lkpservice.code = dbo.tmpValidServices_ALL.Service
INNER JOIN dbo.tmpDemographics_ALL ON dbo.tmpValidServices_ALL.RID = dbo.tmpDemographics_ALL.RID '
IF @COCGroup IS NOT NULL
SELECT @sql = @sql + 'LEFT OUTER JOIN dbo.lkpCOC c ON dbo.tmpValidServices_ALL.COC = c.pvcode '
SELECT @sql = @sql + 'LEFT OUTER JOIN dbo.lkpAgency ag ON vs.SiteID = ag.EXACT# '
SELECT @sql = @sql + 'WHERE dbo.lkpService.Schedule = @Schedule '
IF @StartDate IS NOT NULL
SELECT @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete >= @StartDate ) '
IF @EndDate IS NOT NULL
SELECT @sql = @sql + ' AND (dbo.tmpValidServices_ALL.Complete <= @EndDate ) '
IF @SiteID IS NOT NULL
SELECT @sql = @sql + 'AND (ag.EXACT# = @SiteID) '
IF @COCGroup IS NOT NULL
SELECT @sql = @sql + ' AND (c.pvcode = @COCGroup OR c.pvcode IN (SELECT COC FROM lkpCOCGroup WHERE COCGroup = @COCGroup)) '
IF @Provider IS NOT NULL
SELECT @sql = @sql + 'AND (dbo.tmpValidServices_ALL.Provider = @Provider) '
IF @Postcode IS NOT NULL
SELECT @sql = @sql + 'AND (dbo.tmpdemographics_ALL.Postcode = @Postcode) '
SELECT @sql = @sql + ' GROUP BY dbo.tmpValidServices_ALL.SiteID,
dbo.tmpValidServices_ALL.Programme,
dbo.tmpValidServices_ALL.Complete ,
dbo.tmpValidServices_ALL.RID ,
dbo.tmpValidServices_ALL.COC# ,
dbo.tmpDemographics_ALL.postcode,
dbo.tmpdemographics_ALL.Suburb,
(dbo.lkpService.Notional$),
(dbo.lkpService.TVMins) )
SELECT COUNT(DISTINCT PID + CAST(Complete AS varchar(20)) + CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Visits,
COUNT(DISTINCT PID + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20))) AS Patients,
COUNT(DISTINCT CAST(COC AS varchar(20)) + CAST(SiteID AS varchar(20)) + CAST(Programme AS varchar(20)) + CAST(PID AS varchar(20))) AS COCs,
SUM(NotionalSum) AS NotionalSum,
SUM(TVMinsSUM) AS TVMinsSUM,Postcode,Suburb,sum(Qty) as Qty
FROM Q1
Group by Postcode,Suburb
Order by Postcode,Suburb '
SET @paramlist = '@SiteID as numeric,
@COCGroup as varchar(20),
@Postcode as varchar(20),
@StartDate DateTime,
@EndDate DateTime,
@Schedule varchar(20),
@Provider varchar(20)'
EXEC sp_executesql @sql, @paramlist, @SiteID, @COCGroup, @Postcode, @StartDate, @EndDate, @Schedule, @Provider
View 2 Replies
View Related
