FlatFile connection and security

My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:

Error: 2005-06-24 12:35:03.33
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This error just started when we tried to point to a file on a network server instead of a file local to the machine running the package. The other person definitely has access rights to that network server, but the package fails anyway.

One possible solution might be to specify a username/password for the file, but I don't see how to include that in the connection string. Alternatively, this may be a more general security issue with the whole package. I first turned to BOL for some explanations and answers, but wasn't able to find anything specific to my problem.

Any help or suggestions will be greatly appreciated. Thanks!
Phil
This is because the default protection level is to encrypt with a user key. That means only a user on his/her machine can open the package.

You probably want to use Package Password here instead.|||

I am getting a similar error on a DTS package created by me and migrated by me and executed by me via Dtexec. Shouldn't ssql 2000 ownership carry over to 2005? The package is not a flat file. And, by the way, where does the "DTS Property" come in?

thanks

Ted

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:24:56 AM
Error: 2006-02-08 09:24:56.50
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
Error: 2006-02-08 09:24:56.51
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error

|||

SSIS is a completely new product so I don't think it follows that DTS behaviour should make it into SSIS. Furthermore, there's been a very high profile tightening up of security within Microsoft products and the behaviour you're seeing here is probably as a result of that.

The reason you see references to "DTS" in various places is for legacy reasons. Alot of the product had already been built before the name change came about.

-Jamie

sql

FlatFile connection and security

My DTS package, deployed and run from the file system, works just fine for me, but fails when someone else runs it. The only explicit error from the dtexec command is:

Error: 2005-06-24 12:35:03.33
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available.
End Error

This error just started when we tried to point to a file on a network server instead of a file local to the machine running the package. The other person definitely has access rights to that network server, but the package fails anyway.

One possible solution might be to specify a username/password for the file, but I don't see how to include that in the connection string. Alternatively, this may be a more general security issue with the whole package. I first turned to BOL for some explanations and answers, but wasn't able to find anything specific to my problem.

Any help or suggestions will be greatly appreciated. Thanks!
PhilThis is because the default protection level is to encrypt with a user key. That means only a user on his/her machine can open the package.

You probably want to use Package Password here instead.|||

I am getting a similar error on a DTS package created by me and migrated by me and executed by me via Dtexec. Shouldn't ssql 2000 ownership carry over to 2005? The package is not a flat file. And, by the way, where does the "DTS Property" come in?

thanks

Ted

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Started: 9:24:56 AM
Error: 2006-02-08 09:24:56.50
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Property" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error
Error: 2006-02-08 09:24:56.51
Code: 0xC0016016
Source:
Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
the correct key is available.
End Error

|||

SSIS is a completely new product so I don't think it follows that DTS behaviour should make it into SSIS. Furthermore, there's been a very high profile tightening up of security within Microsoft products and the behaviour you're seeing here is probably as a result of that.

The reason you see references to "DTS" in various places is for legacy reasons. Alot of the product had already been built before the name change came about.

-Jamie

Flat File Destination Filename with Date ?

Is there a way to append a date at the end of the filename for whenever a Flat File Destination is run? So far, I only see the ability to browse to a static filename.For the Flat File Connection, which is being used by the Flat File Destination, you can set the ConnectionString using Expressions, to set your file name with dates.|||Do you have any resources on syntax for this?|||

"C:\\file" + (DT_WSTR, 2)month(getdate()) + (DT_WSTR, 2)day(getdate()) + (DT_WSTR,4)year(getdate()) + ".txt"

For example, if i use the above expression for Connection string of the Flat file connection, it will create a file

C:\file12192005.txt

if i run it today.

|||Are you talking about File Connection Manager when you click browse to specify the File Path? If I put that in there of course it doesn't like it. Where exactly are you placing in this script, what component, what tab, what property?|||

For the Flat File Connection Manager, on Properties tab, you see Expressions, when you click there, you can set expressions for some of the properties for the Flat File Connection Manager. In this case, I am asking you to set the ConnectionString property as an expression.

you should see

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/a4bfc925-3ef6-431e-b1dd-7e0023d3a92d.htm

in BOL for further details.

|||Oh, I see, I was double-clicking the file connection manager using the properties there, I didn't think to look at the VS properties pane in VS, thanks!|||

either I'm missing something or this link doesn't work:

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/extran9/html/a4bfc925-3ef6-431e-b1dd-7e0023d3a92d.htm

|||Open BOL and paste this Url in the url box. It is not a http link.sql

FK is showing PK ID instead of 'Name'

I have 2 tables, OT_ClaimantDetails and OT_OTDetails. PK in OT_OTDetails is OTDetailsID which is also FK in OT_ClaimantDetails. I am trying to run a select query to display certain data but instead of displaying the Name of the OT it displays the OTDetails ID.

SELECT OT_ClaimantDetails.ClaimantFactID, OT_ClaimantDetails.ClaimantDetailsOTDetailsID, OT_ClaimantDetails.ReferanceDIMS, OT_ClaimantDetails.DateReceived, OT_ClaimantDetails.DateEntered, OT_ClaimantDetails.DateCompleteCalc
FROM OT_ClaimantDetails, OT_OTDetails
WHERE OT_ClaimantDetails.ClaimantDetailsOTDetailsID=OT_OTDetails.OTDetailsID

How should I change my query?

Is Name a column in one of the tables? If so, which one? If not, then where does it reside?

|||No, 'Name' is the users name I'm refering to. So basically instead of showing me 'ID 5' it should show me users number 5, for example, jack. In the OT_OTdetails table I have OTDetailsID and OTName. THe OTName is the 'Name' I would like to see instead of the OTDetailsID.|||

Unless there's something I'm missing you should be able to just add it to the select list.

Code Snippet

SELECT OT_ClaimantDetails.ClaimantFactID, OT_ClaimantDetails.ClaimantDetailsOTDetailsID, OT_ClaimantDetails.ReferanceDIMS, OT_ClaimantDetails.DateReceived, OT_ClaimantDetails.DateEntered, OT_ClaimantDetails.DateCompleteCalc, OT_OTDetails.OTName

|||tried it, doesn't work. What I have done, I have changed the FK (used to be OT_ClaimantDetails.OTDetailsID) to OT_ClaimantDetails.ClaimantDetailsOTDetailsID, because I received a "Ambiguous column name" error in my browser. This solved the problem to display my results again, but now I see OTDetailsID instead of the OTName.|||

Can you post your DDL?

I don't see where you're referencing any OT_OTDetails columns in your select.

This doesn't make sense.

|||

I'm new at this, what do you mean by "post DDL"?

Wouldn't the referance to the Foreign Key, "OT_ClaimantDetails.ClaimantDetailsOTDetailsID", get the data from the OT_OTDetails column?|||

DDL = Data Definition Language....your table definitions.

>>Wouldn't the referance to the Foreign Key, "OT_ClaimantDetails.ClaimantDetailsOTDetailsID", get the data from the OT_OTDetails column?

No.

But you are joining the two tables together on the OTDetailsID (in your where clause).

So columns from the OT_OTDetail should be available.

When you put OTName in the select list, do you qualify it with OT_OTDetails. (not OT_ClaimantDetails)?

|||

It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 and to http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )

The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games.

|||Standby guys, I'm going to try and get the DDL posted, maybe this will help you to help me.|||

I'm using SQL 2005.

Column OT_ClaimantDetails

USE [jwest_absolutedi]

GO

/****** Object: Table [dbo].[OT_ClaimantDetails] Script Date: 05/31/2007 17:07:41 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[OT_ClaimantDetails](

[ClaimantDetailsID] [int] IDENTITY(1,1) NOT NULL,

[ReferanceDIMS] [varchar](50) NULL,

[ReferanceClient] [varchar](50) NULL,

[DateReceived] [datetime] NULL,

[DateEntered] [datetime] NULL,

[DateContactClaimant] [datetime] NULL,

[DateContactClient] [datetime] NULL,

[DateAppointm] [datetime] NULL,

[DateComplExpect] [datetime] NULL,

[DateCompleteActual] [datetime] NULL,

[DateCompleteCalc] [nchar](10) NULL,

[ReportOT] [varchar](250) NULL,

[ReportClient] [varchar](250) NULL,

[ClaimantFactID] [int] NULL,

[CompanyDetailsID] [int] NULL,

[ClaimantDetailsOTDetailsID] [int] NULL,

[BenefitsID] [int] NULL,

[DiagnosisID] [int] NULL,

[EvaluationID] [int] NULL,

[OccupationalID] [int] NULL,

[RecommendationID] [int] NULL,

CONSTRAINT [PK_OT_ClaimantDetails] PRIMARY KEY CLUSTERED

(

[ClaimantDetailsID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_Benefits] FOREIGN KEY([BenefitsID])

REFERENCES [dbo].[OT_Benefits] ([BenefitsID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_Benefits]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_ClaimantFact] FOREIGN KEY([ClaimantFactID])

REFERENCES [dbo].[OT_ClaimantFact] ([ClaimantFactID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_ClaimantFact]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_CompanyDetails] FOREIGN KEY([CompanyDetailsID])

REFERENCES [dbo].[OT_CompanyDetails] ([CompanyDetailsID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_CompanyDetails]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_Diagnosis] FOREIGN KEY([DiagnosisID])

REFERENCES [dbo].[OT_Diagnosis] ([DiagnosisID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_Diagnosis]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_Evaluation] FOREIGN KEY([EvaluationID])

REFERENCES [dbo].[OT_Evaluation] ([EvaluationID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_Evaluation]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_Occupational] FOREIGN KEY([OccupationalID])

REFERENCES [dbo].[OT_Occupational] ([OccupationalID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_Occupational]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_OTDetails] FOREIGN KEY([ClaimantDetailsOTDetailsID])

REFERENCES [dbo].[OT_OTDetails] ([OTDetailsID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_OTDetails]

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] WITH CHECK ADD CONSTRAINT [FK_OT_ClaimantDetails_OT_Recommendation] FOREIGN KEY([RecommendationID])

REFERENCES [dbo].[OT_Recommendation] ([RecommendationID])

GO

ALTER TABLE [dbo].[OT_ClaimantDetails] CHECK CONSTRAINT [FK_OT_ClaimantDetails_OT_Recommendation]

--

COLUMN OT_OTDETAILS

USE [jwest_absolutedi]

GO

/****** Object: Table [dbo].[OT_OTDetails] Script Date: 05/31/2007 17:09:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[OT_OTDetails](

[OTDetailsID] [int] IDENTITY(1,1) NOT NULL,

[OTName] [varchar](50) NULL,

[OTLastName] [varchar](50) NULL,

[Tel] [nchar](10) NULL,

[Fax] [nchar](10) NULL,

[Cell] [nchar](10) NULL,

[Email] [varchar](50) NULL,

[City] [varchar](50) NULL,

CONSTRAINT [PK_OT_OTFact] PRIMARY KEY CLUSTERED

(

[OTDetailsID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

Let me know if this helps..

THX

|||

Thanks Jackow, that helped better understand your situation.

From you original description, I think this is closer to your objective:

Code Snippet

SELECT
d.OTName
c.ClaimantFactID,
c.ClaimantDetailsOTDetailsID,
c.ReferanceDIMS,
c.DateReceived,
c.DateEntered,
c.DateCompleteCalc
FROM OT_ClaimantDetails c
JOIN OT_OTDetails d
ON c.ClaimantDetailsOTDetailsID = d.OTDetailsID

|||

Thanks jackow. Very helpful.

Exactly Arnie...looks like the original concept should be working.

|||

HI Guys,

Sorry for my delayed response. Thanks for the help. We will chat soon!!

FK is showing PK ID instead of 'Name'

I have 2 tables, OT_ClaimantDetails and OT_OTDetails. PK in OT_OTDetails is OTDetailsID which is also FK in OT_ClaimantDetails. I am trying to run a select query to display certain data but instead of displaying the Name of the OT it displays the OTDetails ID.

SELECT OT_ClaimantDetails.ClaimantFactID, OT_ClaimantDetails.ClaimantDetailsOTDetailsID, OT_ClaimantDetails.ReferanceDIMS, OT_ClaimantDetails.DateReceived, OT_ClaimantDetails.DateEntered, OT_ClaimantDetails.DateCompleteCalc
FROM OT_ClaimantDetails, OT_OTDetails
WHERE OT_ClaimantDetails.ClaimantDetailsOTDetailsID=OT_OTDetails.OTDetailsID

How should I change my query?

Is Name a column in one of the tables? If so, which one? If not, then where does it reside?

|||No, 'Name' is the users name I'm refering to. So basically instead of showing me 'ID 5' it should show me users number 5, for example, jack. In the OT_OTdetails table I have OTDetailsID and OTName. THe OTName is the 'Name' I would like to see instead of the OTDetailsID.|||

Unless there's something I'm missing you should be able to just add it to the select list.

Code Snippet

SELECT OT_ClaimantDetails.ClaimantFactID, OT_ClaimantDetails.ClaimantDetailsOTDetailsID, OT_ClaimantDetails.ReferanceDIMS, OT_ClaimantDetails.DateReceived, OT_ClaimantDetails.DateEntered, OT_ClaimantDetails.DateCompleteCalc, OT_OTDetails.OTName

|||tried it, doesn't work. What I have done, I have changed the FK (used to be OT_ClaimantDetails.OTDetailsID) to OT_ClaimantDetails.ClaimantDetailsOTDetailsID, because I received a "Ambiguous column name" error in my browser. This solved the problem to display my results again, but now I see OTDetailsID instead of the OTName.|||

Can you post your DDL?

I don't see where you're referencing any OT_OTDetails columns in your select.

This doesn't make sense.

|||

I'm new at this, what do you mean by "post DDL"?

Wouldn't the referance to the Foreign Key, "OT_ClaimantDetails.ClaimantDetailsOTDetailsID", get the data from the OT_OTDetails column?|||

DDL = Data Definition Language....your table definitions.

>>Wouldn't the referance to the Foreign Key, "OT_ClaimantDetails.ClaimantDetailsOTDetailsID", get the data from the OT_OTDetails column?

No.

But you are joining the two tables together on the OTDetailsID (in your where clause).

So columns from the OT_OTDetail should be available.

When you put OTName in the select list, do you qualify it with OT_OTDetails. (not OT_ClaimantDetails)?

|||

It would help us better assist you if you could include table DDL, query strategy used so far, sample data in the form of INSERT statements, and an illustration of the desired results. (For help with that refer to: http://www.aspfaq.com/5006 and to http://classicasp.aspfaq.com/general/how-do-i-make-sure-my-asp-question-gets-answered.html )

The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you. Without this effort from you, we are just playing guessing games.

|||Standby guys, I'm going to try and get the DDL posted, maybe this will help you to help me.|||

I'm using SQL 2005.

Column OT_ClaimantDetails

USE [jwest_absolutedi]

GO

/****** Object: Table [dbo].[OT_ClaimantDetails] Script Date: 05/31/2007 17:07:41 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGON

GO

CREATETABLE [dbo].[OT_ClaimantDetails](

[ClaimantDetailsID] [int] IDENTITY(1,1)NOTNULL,

[ReferanceDIMS] [varchar](50)NULL,

[ReferanceClient] [varchar](50)NULL,

[DateReceived] [datetime] NULL,

[DateEntered] [datetime] NULL,

[DateContactClaimant] [datetime] NULL,

[DateContactClient] [datetime] NULL,

[DateAppointm] [datetime] NULL,

[DateComplExpect] [datetime] NULL,

[DateCompleteActual] [datetime] NULL,

[DateCompleteCalc] [nchar](10)NULL,

[ReportOT] [varchar](250)NULL,

[ReportClient] [varchar](250)NULL,

[ClaimantFactID] [int] NULL,

[CompanyDetailsID] [int] NULL,

[ClaimantDetailsOTDetailsID] [int] NULL,

[BenefitsID] [int] NULL,

[DiagnosisID] [int] NULL,

[EvaluationID] [int] NULL,

[OccupationalID] [int] NULL,

[RecommendationID] [int] NULL,

CONSTRAINT [PK_OT_ClaimantDetails] PRIMARYKEYCLUSTERED

(

[ClaimantDetailsID] ASC

)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]

)ON [PRIMARY]

GO

SETANSI_PADDINGOFF

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Benefits] FOREIGNKEY([BenefitsID])

REFERENCES [dbo].[OT_Benefits] ([BenefitsID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Benefits]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_ClaimantFact] FOREIGNKEY([ClaimantFactID])

REFERENCES [dbo].[OT_ClaimantFact] ([ClaimantFactID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_ClaimantFact]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_CompanyDetails] FOREIGNKEY([CompanyDetailsID])

REFERENCES [dbo].[OT_CompanyDetails] ([CompanyDetailsID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_CompanyDetails]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Diagnosis] FOREIGNKEY([DiagnosisID])

REFERENCES [dbo].[OT_Diagnosis] ([DiagnosisID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Diagnosis]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Evaluation] FOREIGNKEY([EvaluationID])

REFERENCES [dbo].[OT_Evaluation] ([EvaluationID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Evaluation]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Occupational] FOREIGNKEY([OccupationalID])

REFERENCES [dbo].[OT_Occupational] ([OccupationalID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Occupational]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_OTDetails] FOREIGNKEY([ClaimantDetailsOTDetailsID])

REFERENCES [dbo].[OT_OTDetails] ([OTDetailsID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_OTDetails]

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] WITHCHECKADDCONSTRAINT [FK_OT_ClaimantDetails_OT_Recommendation] FOREIGNKEY([RecommendationID])

REFERENCES [dbo].[OT_Recommendation] ([RecommendationID])

GO

ALTERTABLE [dbo].[OT_ClaimantDetails] CHECKCONSTRAINT [FK_OT_ClaimantDetails_OT_Recommendation]

--

COLUMN OT_OTDETAILS

USE [jwest_absolutedi]

GO

/****** Object: Table [dbo].[OT_OTDetails] Script Date: 05/31/2007 17:09:58 ******/

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

SETANSI_PADDINGON

GO

CREATETABLE [dbo].[OT_OTDetails](

[OTDetailsID] [int] IDENTITY(1,1)NOTNULL,

[OTName] [varchar](50)NULL,

[OTLastName] [varchar](50)NULL,

[Tel] [nchar](10)NULL,

[Fax] [nchar](10)NULL,

[Cell] [nchar](10)NULL,

[Email] [varchar](50)NULL,

[City] [varchar](50)NULL,

CONSTRAINT [PK_OT_OTFact] PRIMARYKEYCLUSTERED

(

[OTDetailsID] ASC

)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON [PRIMARY]

)ON [PRIMARY]

GO

SETANSI_PADDINGOFF

Let me know if this helps..

THX

|||

Thanks Jackow, that helped better understand your situation.

From you original description, I think this is closer to your objective:

Code Snippet

SELECT
d.OTName
c.ClaimantFactID,
c.ClaimantDetailsOTDetailsID,
c.ReferanceDIMS,
c.DateReceived,
c.DateEntered,
c.DateCompleteCalc
FROM OT_ClaimantDetails c
JOIN OT_OTDetails d
ON c.ClaimantDetailsOTDetailsID = d.OTDetailsID

|||

Thanks jackow. Very helpful.

Exactly Arnie...looks like the original concept should be working.

|||

HI Guys,

Sorry for my delayed response. Thanks for the help. We will chat soon!!

FK Constraint Problem

Hi All,
I use several SP's to create tables and I have run into a problem I do
not understand. All the SP's create a FK constraint when the table is
built and they all work, except for 1.
CREATE TABLE dbo.ZipCodes (
CountryID int NOT NULL,
StateID int NULL,
CountyID int NULL,
ZipCodeID int CONSTRAINT PK_ZipCodes PRIMARY KEY IDENTITY (1, 1) NOT NULL,
ZipCodeStatus smallint NOT NULL,
ZipCode nvarchar (25) NOT NULL,
CONSTRAINT FK_Counties_ZipCodes FOREIGN KEY (CountyID) REFERENCES
Counties (CountyID) ON DELETE NO ACTION ON UPDATE NO ACTION,
* CONSTRAINT FK_ZipCodes_Cities FOREIGN KEY (ZipCodeID) REFERENCES
Cities (ZipCodeID) ON DELETE CASCADE ON UPDATE CASCADE
)
The last FK constraint (*) will cause an error and the table is not
built. Once I add some data to the table then I can manually setup the
same constraint.
How do I get this to work when the SP is executed, before any data is added?
Thanks for any help,
Charles
On Tue, 14 Mar 2006 15:05:21 -0600, Charles E Finkenbiner wrote:

>Hi All,
>I use several SP's to create tables and I have run into a problem I do
>not understand. All the SP's create a FK constraint when the table is
>built and they all work, except for 1.
>CREATE TABLE dbo.ZipCodes (
> CountryID int NOT NULL,
> StateID int NULL,
> CountyID int NULL,
> ZipCodeID int CONSTRAINT PK_ZipCodes PRIMARY KEY IDENTITY (1, 1) NOT NULL,
> ZipCodeStatus smallint NOT NULL,
> ZipCode nvarchar (25) NOT NULL,
> CONSTRAINT FK_Counties_ZipCodes FOREIGN KEY (CountyID) REFERENCES
>Counties (CountyID) ON DELETE NO ACTION ON UPDATE NO ACTION,
>* CONSTRAINT FK_ZipCodes_Cities FOREIGN KEY (ZipCodeID) REFERENCES
>Cities (ZipCodeID) ON DELETE CASCADE ON UPDATE CASCADE
>)
>The last FK constraint (*) will cause an error and the table is not
>built. Once I add some data to the table then I can manually setup the
>same constraint.
>How do I get this to work when the SP is executed, before any data is added?
Hi Charles,
What is the error message you get?
Also, are you sure that this is the constraint you need to define? It's
highly unusual to have an identity column as a foreign key column.
Are you sure that the constraint should not go on the ZipCode column?
(And a foreign key constraint on a CityID column would make even more
sense, but there is not even a column CityID in the table).
Or did you intend to add a foreign key to the Cities table that
references the ZipCodes table?
Hugo Kornelis, SQL Server MVP
|||On 3/14/2006 4:36 PM, Hugo Kornelis wrote:
> Hi Charles,
> What is the error message you get?
> Also, are you sure that this is the constraint you need to define? It's
> highly unusual to have an identity column as a foreign key column.
> Are you sure that the constraint should not go on the ZipCode column?
> (And a foreign key constraint on a CityID column would make even more
> sense, but there is not even a column CityID in the table).
> Or did you intend to add a foreign key to the Cities table that
> references the ZipCodes table?
>
Hi Hugo,
The exact error message is: "There are no primary or candidate keys in
the referenced table 'Cities' that match the referencing column list in
the foreign key 'FK_ZipCodes_Cities'."
The ZipCodes table contains each zip code, once. There can be many
cities that use that zip code (this is to maintain the history of the
changes a zip code goes through, it may be Unacceptable to the PO now to
use a certain city name but my data goes back to the DOS days and I flag
that city name as unacceptable).
The Cities table does contain a column called ZipCodeID and it is
defined as int, so a 1 to many relationship exists between ZipCodes and
Cities.
Also, I am no expert with SQL but I do have many years experience with
relational databases since the DOS days. It is possible that I have
messed up my logic.
Thanks for any help,
Charles
|||On 3/14/2006 5:36 PM, Charles E Finkenbiner wrote:
> On 3/14/2006 4:36 PM, Hugo Kornelis wrote:
> Hi Hugo,
> The exact error message is: "There are no primary or candidate keys in
> the referenced table 'Cities' that match the referencing column list in
> the foreign key 'FK_ZipCodes_Cities'."
> The ZipCodes table contains each zip code, once. There can be many
> cities that use that zip code (this is to maintain the history of the
> changes a zip code goes through, it may be Unacceptable to the PO now to
> use a certain city name but my data goes back to the DOS days and I flag
> that city name as unacceptable).
> The Cities table does contain a column called ZipCodeID and it is
> defined as int, so a 1 to many relationship exists between ZipCodes and
> Cities.
> Also, I am no expert with SQL but I do have many years experience with
> relational databases since the DOS days. It is possible that I have
> messed up my logic.
>
> Thanks for any help,
> Charles
Hi,
I just noticed something else. I do not need to add any data at all.
After the ZipCodes table is built I can manually define the constraint
with no error message.
Primary key table: ZipCodes
Primary key column: ZipCodeID
Foreign key table: Cities
Foreign key column: ZipCodeID
So, I can do this manually but not with code. Any ideas?
Thanks for any help,
Charles
|||On Tue, 14 Mar 2006 17:46:40 -0600, Charles E Finkenbiner wrote:
(snip)
>I just noticed something else. I do not need to add any data at all.
>After the ZipCodes table is built I can manually define the constraint
>with no error message.
> Primary key table: ZipCodes
>Primary key column: ZipCodeID
> Foreign key table: Cities
>Foreign key column: ZipCodeID
>So, I can do this manually but not with code. Any ideas?
Hi Charles,
A foreign key constraint is always defined on the referencing column,
which "lives" in the table on the "many" side of the one-to-many
relationship. You tried to define it in the ZipCodes table, whereas it
should have been defined on the Cities table.
ALTER TABLE Cities
ADD CONSTRAINT FK_ZipCodes_Cities
FOREIGN KEY (ZipCodeID)
REFERENCES ZipCodes (ZipCodeID)
ON DELETE CASCADE ON UPDATE CASCADE
Hugo Kornelis, SQL Server MVP
|||On 3/15/2006 2:28 PM, Hugo Kornelis wrote:
> On Tue, 14 Mar 2006 17:46:40 -0600, Charles E Finkenbiner wrote:
> (snip)
>
> Hi Charles,
> A foreign key constraint is always defined on the referencing column,
> which "lives" in the table on the "many" side of the one-to-many
> relationship. You tried to define it in the ZipCodes table, whereas it
> should have been defined on the Cities table.
> ALTER TABLE Cities
> ADD CONSTRAINT FK_ZipCodes_Cities
> FOREIGN KEY (ZipCodeID)
> REFERENCES ZipCodes (ZipCodeID)
> ON DELETE CASCADE ON UPDATE CASCADE
>
Hi Hugo,
I see the error of my ways. :D) My brain must have been on vacation
because I never caught that mistake. I guess using Access as my SQL
database designer has gotten me confused. I am in the ZipCodes table
when I define the constraint and it works in Access XP (2002). At the
same time I have other SP's that define constraints in the 'many' side
table and I never connected the two. Sorry for the newbie mistake and
thanks for taking the time to let me know.
Thanks for your help,
Charles

First sp after re-start fails because of User-defined type

Hi,
I'm having a problem with SQL Server 7. If I restart the
server, and the first stroed proc I run contains a
reference to a user defined data type then the query fails
because the data type is not recognised. (In this case I'm
creating a temporary table in the stored proc, where one
column in the table uses the user defined data type)
A workaround is to run another stored proc first, and then
everything is fine.
Has anyone got a more elegant solution or reason why this
would happen?
Thanks,
Neil
Seems to be some issue with a user defined datatype not existing in tempdb. Hard to ell without a
repro. Perhaps it is as easy as creating that dt in the model database so you know it will exist in
tempdb?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> Hi,
> I'm having a problem with SQL Server 7. If I restart the
> server, and the first stroed proc I run contains a
> reference to a user defined data type then the query fails
> because the data type is not recognised. (In this case I'm
> creating a temporary table in the stored proc, where one
> column in the table uses the user defined data type)
> A workaround is to run another stored proc first, and then
> everything is fine.
> Has anyone got a more elegant solution or reason why this
> would happen?
> Thanks,
> Neil
|||I don't think so - In the SP,before I create the temporary
table, I copy all the user data types to the tempdb, but
this isn't being run. In fact, I put some logging calls in
the SP and ran it from Query analyzer, and nothing is run.
It seems that SQL Server is doing some sort of
compilation/validation on the SP and failing it before any
attempt is made to run it. Running some other query seems
to make it 'aware' of the user defined data types, and
then it is happy with the original SP.
Thanks anyway,
Neil

>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
fails[vbcol=seagreen]
I'm[vbcol=seagreen]
then[vbcol=seagreen]
this
>
>.
>
|||It seems I spoke too soon! The following code reproduces
the problem:
CREATE PROCEDURE MyUDTTest AS
BEGIN
declare @.stmt as nvarchar(255)
set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
sp_addtype ''udtBuySell'' , ''char(1)'''
exec sp_executesql @.stmt
CREATE TABLE #MyTable (MyColumn udtBuySell)
END
trying to run this SP after a stop/start gives the
following error:
Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
Line 10
Column or parameter #1: Cannot find data type udtBuySell.
in tempdb create the udtBuySell data type. Then drop it.
Now the sp will run.
Any thoughts?
Neil

>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
fails[vbcol=seagreen]
I'm[vbcol=seagreen]
then[vbcol=seagreen]
this
>
>.
>
|||This is a scoping problem. Since the UDT is created in dynamic SQL, the datatype doesn't exist when
SQL Server parses the code in the procedure. Create the datatype in model instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:244901c4a176$b0fdea60$a601280a@.phx.gbl...[vbcol=seagreen]
> It seems I spoke too soon! The following code reproduces
> the problem:
> CREATE PROCEDURE MyUDTTest AS
> BEGIN
> declare @.stmt as nvarchar(255)
> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
> sp_addtype ''udtBuySell'' , ''char(1)'''
> exec sp_executesql @.stmt
> CREATE TABLE #MyTable (MyColumn udtBuySell)
> END
> trying to run this SP after a stop/start gives the
> following error:
> Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
> Line 10
> Column or parameter #1: Cannot find data type udtBuySell.
> in tempdb create the udtBuySell data type. Then drop it.
> Now the sp will run.
> Any thoughts?
> Neil
>
> existing in tempdb. Hard to ell without a
> model database so you know it will exist in
> message
> fails
> I'm
> then
> this
|||Tibor,
Thanks very much for your help. Creating the data type in
model has been ruled out from above (!) as it would mean
remembering to change it in two places (in the working db
and in model).
I'm curious as to why the procedure should work after the
datatype has been created then dropped in tempdb. I can
see what you are saying about scoping, but why would it be
OK after creating/dropping the type in tempdb? It still
doesn't exist in tempdb after all...
Our current plan is to run a job when SQLAgent starts to
copy the user types from our working db to tempdb (so now
we have to remember to restart the server after a change
to the user types - where will it all end!)
Thanks again for your interest in this problem,
Neil

>--Original Message--
>This is a scoping problem. Since the UDT is created in
dynamic SQL, the datatype doesn't exist when
>SQL Server parses the code in the procedure. Create the
datatype in model instead.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
MyUDTTest,[vbcol=seagreen]
udtBuySell.[vbcol=seagreen]
the[vbcol=seagreen]
case[vbcol=seagreen]
one
>
>.
>

First sp after re-start fails because of User-defined type

Hi,
I'm having a problem with SQL Server 7. If I restart the
server, and the first stroed proc I run contains a
reference to a user defined data type then the query fails
because the data type is not recognised. (In this case I'm
creating a temporary table in the stored proc, where one
column in the table uses the user defined data type)
A workaround is to run another stored proc first, and then
everything is fine.
Has anyone got a more elegant solution or reason why this
would happen?
Thanks,
NeilSeems to be some issue with a user defined datatype not existing in tempdb. Hard to ell without a
repro. Perhaps it is as easy as creating that dt in the model database so you know it will exist in
tempdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> Hi,
> I'm having a problem with SQL Server 7. If I restart the
> server, and the first stroed proc I run contains a
> reference to a user defined data type then the query fails
> because the data type is not recognised. (In this case I'm
> creating a temporary table in the stored proc, where one
> column in the table uses the user defined data type)
> A workaround is to run another stored proc first, and then
> everything is fine.
> Has anyone got a more elegant solution or reason why this
> would happen?
> Thanks,
> Neil|||I don't think so - In the SP,before I create the temporary
table, I copy all the user data types to the tempdb, but
this isn't being run. In fact, I put some logging calls in
the SP and ran it from Query analyzer, and nothing is run.
It seems that SQL Server is doing some sort of
compilation/validation on the SP and failing it before any
attempt is made to run it. Running some other query seems
to make it 'aware' of the user defined data types, and
then it is happy with the original SP.
Thanks anyway,
Neil
>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> Hi,
>> I'm having a problem with SQL Server 7. If I restart the
>> server, and the first stroed proc I run contains a
>> reference to a user defined data type then the query
fails
>> because the data type is not recognised. (In this case
I'm
>> creating a temporary table in the stored proc, where one
>> column in the table uses the user defined data type)
>> A workaround is to run another stored proc first, and
then
>> everything is fine.
>> Has anyone got a more elegant solution or reason why
this
>> would happen?
>> Thanks,
>> Neil
>
>.
>|||This is a scoping problem. Since the UDT is created in dynamic SQL, the datatype doesn't exist when
SQL Server parses the code in the procedure. Create the datatype in model instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
> It seems I spoke too soon! The following code reproduces
> the problem:
> CREATE PROCEDURE MyUDTTest AS
> BEGIN
> declare @.stmt as nvarchar(255)
> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
> sp_addtype ''udtBuySell'' , ''char(1)'''
> exec sp_executesql @.stmt
> CREATE TABLE #MyTable (MyColumn udtBuySell)
> END
> trying to run this SP after a stop/start gives the
> following error:
> Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
> Line 10
> Column or parameter #1: Cannot find data type udtBuySell.
> in tempdb create the udtBuySell data type. Then drop it.
> Now the sp will run.
> Any thoughts?
> Neil
>
> >--Original Message--
> >Seems to be some issue with a user defined datatype not
> existing in tempdb. Hard to ell without a
> >repro. Perhaps it is as easy as creating that dt in the
> model database so you know it will exist in
> >tempdb?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> I'm having a problem with SQL Server 7. If I restart the
> >> server, and the first stroed proc I run contains a
> >> reference to a user defined data type then the query
> fails
> >> because the data type is not recognised. (In this case
> I'm
> >> creating a temporary table in the stored proc, where one
> >> column in the table uses the user defined data type)
> >>
> >> A workaround is to run another stored proc first, and
> then
> >> everything is fine.
> >>
> >> Has anyone got a more elegant solution or reason why
> this
> >> would happen?
> >>
> >> Thanks,
> >>
> >> Neil
> >
> >
> >.
> >|||Tibor,
Thanks very much for your help. Creating the data type in
model has been ruled out from above (!) as it would mean
remembering to change it in two places (in the working db
and in model).
I'm curious as to why the procedure should work after the
datatype has been created then dropped in tempdb. I can
see what you are saying about scoping, but why would it be
OK after creating/dropping the type in tempdb? It still
doesn't exist in tempdb after all...
Our current plan is to run a job when SQLAgent starts to
copy the user types from our working db to tempdb (so now
we have to remember to restart the server after a change
to the user types - where will it all end!)
Thanks again for your interest in this problem,
Neil
>--Original Message--
>This is a scoping problem. Since the UDT is created in
dynamic SQL, the datatype doesn't exist when
>SQL Server parses the code in the procedure. Create the
datatype in model instead.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
>> It seems I spoke too soon! The following code reproduces
>> the problem:
>> CREATE PROCEDURE MyUDTTest AS
>> BEGIN
>> declare @.stmt as nvarchar(255)
>> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
>> sp_addtype ''udtBuySell'' , ''char(1)'''
>> exec sp_executesql @.stmt
>> CREATE TABLE #MyTable (MyColumn udtBuySell)
>> END
>> trying to run this SP after a stop/start gives the
>> following error:
>> Server: Msg 2715, Level 16, State 7, Procedure
MyUDTTest,
>> Line 10
>> Column or parameter #1: Cannot find data type
udtBuySell.
>> in tempdb create the udtBuySell data type. Then drop it.
>> Now the sp will run.
>> Any thoughts?
>> Neil
>>
>> >--Original Message--
>> >Seems to be some issue with a user defined datatype not
>> existing in tempdb. Hard to ell without a
>> >repro. Perhaps it is as easy as creating that dt in the
>> model database so you know it will exist in
>> >tempdb?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I'm having a problem with SQL Server 7. If I restart
the
>> >> server, and the first stroed proc I run contains a
>> >> reference to a user defined data type then the query
>> fails
>> >> because the data type is not recognised. (In this
case
>> I'm
>> >> creating a temporary table in the stored proc, where
one
>> >> column in the table uses the user defined data type)
>> >>
>> >> A workaround is to run another stored proc first, and
>> then
>> >> everything is fine.
>> >>
>> >> Has anyone got a more elegant solution or reason why
>> this
>> >> would happen?
>> >>
>> >> Thanks,
>> >>
>> >> Neil
>> >
>> >
>> >.
>> >
>
>.
>

First run of reports is SLOW

Hello all
I realize that the first time you run a report from RS it's slow because it
has to load everything up. Is there some way around this? Can I setup a
report to refresh itself every so often?
Anyway to turn this off? I hate the 30 second wait time to view the first
report...That is what I do, the official work around didn't work for me (IIS
configuration). If you have a report autorefresh every 5 minutes that will
keep everything ready to go.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:BE1E4369-E18A-4AE2-8931-F8BC56E8D609@.microsoft.com...
> Hello all
> I realize that the first time you run a report from RS it's slow because
> it
> has to load everything up. Is there some way around this? Can I setup a
> report to refresh itself every so often?
> Anyway to turn this off? I hate the 30 second wait time to view the first
> report...|||Link for official work around?
"Bruce L-C [MVP]" wrote:
> That is what I do, the official work around didn't work for me (IIS
> configuration). If you have a report autorefresh every 5 minutes that will
> keep everything ready to go.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BE1E4369-E18A-4AE2-8931-F8BC56E8D609@.microsoft.com...
> > Hello all
> >
> > I realize that the first time you run a report from RS it's slow because
> > it
> > has to load everything up. Is there some way around this? Can I setup a
> > report to refresh itself every so often?
> >
> > Anyway to turn this off? I hate the 30 second wait time to view the first
> > report...
>
>|||I guess it is not "official" it is just less of a hack. I.e. adjust certain
IIS settings etc. Below is the posting from someone else describing this,
but I just have a report on my box that refreshes every 5 minutes (set this
via report properties I believe).
>>>>>>>
If you are running Windows 2003 server for your IIS reportserver, then this
is a simple issue - I'll explain what happens:
The report service engine, once it is idle for more than the default 20
minutes, the worker process is shutdown.
This is controlled by IIS.
Open up the Internet Information Services (IIS) Manager
Expand the server node then the application pools.
On my IIS machine, I created an application pool dedicated to the
reportserver & reportmanager virtual webs.
But anyways, for the application pool that the reportserver is pointing to
if you left everything to their defaults will be the DefaultAppPool.
Right click the default app pool and select properties.
There are two things that are checked by default - On the recycling tab
there is a checkbox for recycling worker processes - it is currently set to
1740 minutes (29 hours). Leave it.
The other one is on the performance tab - which is the one you are
interested in changing...
See the "Idle Timeout" section and increase the number of minutes to be 8
hours a typical working day - 8*60 = 480 minutes.
Next, to be sure the "morning person" that runs the first report doesn't get
the delay, set up a schedule for either a dummy or adhoc report to fire off
like at 6am so that the report component worker processes get loaded.
I hope this helps you.
There is no need to have a report fire off every minute to keep things
alive - it is just that the report service was "unloaded" and needed to load
back up.
=-Chris
>>>>>>>
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John" <John@.discussions.microsoft.com> wrote in message
news:459E7222-BA43-436A-8980-96934C6FAC02@.microsoft.com...
> Link for official work around?
>
> "Bruce L-C [MVP]" wrote:
>> That is what I do, the official work around didn't work for me (IIS
>> configuration). If you have a report autorefresh every 5 minutes that
>> will
>> keep everything ready to go.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "John" <John@.discussions.microsoft.com> wrote in message
>> news:BE1E4369-E18A-4AE2-8931-F8BC56E8D609@.microsoft.com...
>> > Hello all
>> >
>> > I realize that the first time you run a report from RS it's slow
>> > because
>> > it
>> > has to load everything up. Is there some way around this? Can I setup
>> > a
>> > report to refresh itself every so often?
>> >
>> > Anyway to turn this off? I hate the 30 second wait time to view the
>> > first
>> > report...
>>|||Thanks Bruce. If the "hack" doesn't work I will just setup a report and have
it refresh every few minutes or so.
"Bruce L-C [MVP]" wrote:
> I guess it is not "official" it is just less of a hack. I.e. adjust certain
> IIS settings etc. Below is the posting from someone else describing this,
> but I just have a report on my box that refreshes every 5 minutes (set this
> via report properties I believe).
> >>>>>>>
> If you are running Windows 2003 server for your IIS reportserver, then this
> is a simple issue - I'll explain what happens:
> The report service engine, once it is idle for more than the default 20
> minutes, the worker process is shutdown.
> This is controlled by IIS.
> Open up the Internet Information Services (IIS) Manager
> Expand the server node then the application pools.
> On my IIS machine, I created an application pool dedicated to the
> reportserver & reportmanager virtual webs.
> But anyways, for the application pool that the reportserver is pointing to
> if you left everything to their defaults will be the DefaultAppPool.
> Right click the default app pool and select properties.
> There are two things that are checked by default - On the recycling tab
> there is a checkbox for recycling worker processes - it is currently set to
> 1740 minutes (29 hours). Leave it.
> The other one is on the performance tab - which is the one you are
> interested in changing...
> See the "Idle Timeout" section and increase the number of minutes to be 8
> hours a typical working day - 8*60 = 480 minutes.
> Next, to be sure the "morning person" that runs the first report doesn't get
> the delay, set up a schedule for either a dummy or adhoc report to fire off
> like at 6am so that the report component worker processes get loaded.
> I hope this helps you.
> There is no need to have a report fire off every minute to keep things
> alive - it is just that the report service was "unloaded" and needed to load
> back up.
> =-Chris
> >>>>>>>
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "John" <John@.discussions.microsoft.com> wrote in message
> news:459E7222-BA43-436A-8980-96934C6FAC02@.microsoft.com...
> > Link for official work around?
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> That is what I do, the official work around didn't work for me (IIS
> >> configuration). If you have a report autorefresh every 5 minutes that
> >> will
> >> keep everything ready to go.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "John" <John@.discussions.microsoft.com> wrote in message
> >> news:BE1E4369-E18A-4AE2-8931-F8BC56E8D609@.microsoft.com...
> >> > Hello all
> >> >
> >> > I realize that the first time you run a report from RS it's slow
> >> > because
> >> > it
> >> > has to load everything up. Is there some way around this? Can I setup
> >> > a
> >> > report to refresh itself every so often?
> >> >
> >> > Anyway to turn this off? I hate the 30 second wait time to view the
> >> > first
> >> > report...
> >>
> >>
> >>
>
>|||I wouldn't call adjusting the properties of the app pool a "hack", but
really, "configuration".|||The reason I called it less of a hack is because the configuration of itself
doesn't do it. The last step was creating a subscription that ran every day.
My solution is a total hack but it is easy to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"octavius@.gmail.com" <Octavius@.gmail.com> wrote in message
news:1126656232.440168.308260@.o13g2000cwo.googlegroups.com...
>I wouldn't call adjusting the properties of the app pool a "hack", but
> really, "configuration".
>

Fireshose mode

About 2 times a week our SQL Server 2000 system will go into "firehose
mode" and we aren't able to run certain stored procedures. It seems
the ones that are affected are any procedures accessing linked
servers. The only way we can solve the problem is by restarting the
sql service on the server. This is starting to become a drag on our
business because all data processing stops when restart SQL. Has
anybody else encountered this issue with your systems?
Thanks,
JK
PS - We are not using Enterprise Manager so that is not the cause of
the problem. We are suspicous of the SQL Object Browser in Query
Analyzer and wonder if that uses the same "firehose" cursor as EM.Hello JK,
These links will help you to work on your problem.
PRB: SQL Enterprise Manager Returns "Cannot Start
Transaction While in Firehose Mode" Error
http://www.support.microsoft.com/?id=237398
FIX: Cursor Overhead Higher on SQL Server 7.0 for Small
Result Sets
http://support.microsoft.com/support/kb/articles/Q197/8/00.
ASP
Good Luck!
-SQLVarad (MCDBA-1999,MCSE-1999)
>--Original Message--
>About 2 times a week our SQL Server 2000 system will go
into "firehose
>mode" and we aren't able to run certain stored
procedures. It seems
>the ones that are affected are any procedures accessing
linked
>servers. The only way we can solve the problem is by
restarting the
>sql service on the server. This is starting to become a
drag on our
>business because all data processing stops when restart
SQL. Has
>anybody else encountered this issue with your systems?
>Thanks,
>JK
>PS - We are not using Enterprise Manager so that is not
the cause of
>the problem. We are suspicous of the SQL Object Browser
in Query
>Analyzer and wonder if that uses the same "firehose"
cursor as EM.
>.
>|||One of those articles references Enterprise Manager, which we are not
using. The other one talks about SQL Server 7.0, which we are not
using.
Thanks for your help.
"SQLVarad" <SQLVarad@.hotmail.com> wrote in message news:<074101c3ad67$6182baa0$a101280a@.phx.gbl>...
> Hello JK,
> These links will help you to work on your problem.
> PRB: SQL Enterprise Manager Returns "Cannot Start
> Transaction While in Firehose Mode" Error
> http://www.support.microsoft.com/?id=237398
> FIX: Cursor Overhead Higher on SQL Server 7.0 for Small
> Result Sets
> http://support.microsoft.com/support/kb/articles/Q197/8/00.
> ASP
> Good Luck!
> -SQLVarad (MCDBA-1999,MCSE-1999)
> >--Original Message--
> >About 2 times a week our SQL Server 2000 system will go
> into "firehose
> >mode" and we aren't able to run certain stored
> procedures. It seems
> >the ones that are affected are any procedures accessing
> linked
> >servers. The only way we can solve the problem is by
> restarting the
> >sql service on the server. This is starting to become a
> drag on our
> >business because all data processing stops when restart
> SQL. Has
> >anybody else encountered this issue with your systems?
> >
> >Thanks,
> >
> >JK
> >
> >PS - We are not using Enterprise Manager so that is not
> the cause of
> >the problem. We are suspicous of the SQL Object Browser
> in Query
> >Analyzer and wonder if that uses the same "firehose"
> cursor as EM.
> >.
> >