Package Validation Error in Custom PipelineComponent

Hi

I'm developing an PipelineComponent (ComponentType.Transform). When I try to execute this component, the following error is thrown:

Code Snippet

===================================

Package Validation Error (Package Validation Error)

===================================

Error at Data Flow Task [DTS.Pipeline]: Buffer Type 1 had a size of 0 bytes.

Error at Data Flow Task [DTS.Pipeline]: The buffer manager failed to create a new buffer type.

Error at Data Flow Task [DTS.Pipeline]: The Data Flow task cannot register a buffer type. The type had 50 columns and was for execution tree 0.

Error at Data Flow Task [DTS.Pipeline]: The layout failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

Program Location:

at Microsoft.DataTransformationServices.Project.DataTransformationsPackageDebugger.ValidateAndRunDebugger(Int32 flags, DataWarehouseProjectManager manager, IOutputWindow outputWindow, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, ProjectItem startupProjItem, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchActivePackage(Int32 launchOptions)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.LaunchDtsPackage(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)
at Microsoft.DataTransformationServices.Project.DataTransformationsProjectDebugger.Launch(Int32 launchOptions, DataTransformationsProjectConfigurationOptions options)

Can anyone help me, please? The "Integration Services Error and Message Reference" didn't help me much.

Thank you

Manuel Bauer

The error does not give me any clues other than it happens during a validation stage, so what is in your Validate method?

Have your tried debugging this, i.e. Visual Studio attached to the execution host?

|||

Hi

Thank you for your answer. My pipeline component's validate method runs without any problems, I debugged this in design time and in run time (before the exception occured). In my opinion, the problem is the early validation (http://sqljunkies.com/WebLog/knight_reign/archive/2005/04/20/12365.aspx) of the data flow task. As I expect, the data flow task does any validation itself.

I can't imagine what "The Data Flow task cannot register a buffer type. The type had 50 columns and was for execution tree 0." means, so I really don't know where the problem could be.

The exception is thrown immediately after running the package (also after running the validate method of my pipeline component).

|||

Does the package work if you take your component out of it?

The early validation vs late validation is the same Validate method calls, and since it generally works, I'd say it is clear that your transform has a problem, regardless of if it is the early or late call. Early and Late means normally Validate gets called twice, there is no difference between them. The Task will have a validate method, and a Data Flow will then call Validate for each child component.

How can the exception be thrown "after" running a package, Execute will never get called if a task fails validation. What is the return of your Validate method when you debug it?

Did you debug against dtsdebughost or just Visual Studio (designer)? For simplicity when trying to resolve run-time issues I set the code project debug option to use dtexec directly, just using /F and a package file. It is faster than attaching to a designer instance of VS.

|||

I found a solution for my problem. I was thinking wrong about this validation thing, I thought that the data flow task does some independant, general validation itself.

In my component, I "forgot" to call base.Validate(), which would no be a problem if the own validate method is implemented as id should. In my case, it was not.

Thank you for your competent analysis of my problem.

Manuel Bauer

Package Validation Error + Code 0xC004801C

Hi,

I am learning to use SSIS for creating packages. I am getting the following error when I try to execute my package...

Package Validation Error

Error at Data Flow Task[DTS. Pipeline]:The "runtime connection "FlatFileConnection "(122)" in the connection manager collection, Connections, of component "Flat File Destination ("120") does not have a value for the ID property. Verify that the ConnectionManagerID property of the runtime connection object has been set for the component.

Error at Data Flow Task [DTS.Pipeline]: component "Flat File Destination" (120) failed validation and returned error code 0xC004801C.

Error at Data Flow Task[DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

I'd appreciate if someone can help me with this. Do I need to change some settings? Also, what does the number in brackets indicate, say in FlatFileConnection(122)? Is it valuable information while debugging?

Just to give an idea, my package is like this -

source FlatFile -> Derived Column -> Lookup1 ->Lookup2 ->OLE DB destination(SQL server DB table)

Lookup1 and Lookup2 have error redirected to one flat file destination each.

Thanks in advance!

Geez....the error was just because I hadn't assigned a Connection Manager to that Flat File destination!! Oops!!!

Package hanging on OLE DB Command with large result sets

I have an SSIS package (SQL 2005 SP2 and Visual Studio SP1) that does the following:

OLE DB Source --> Conditional Split --> OLE DB Command #1 --> OLE DB Command #2

The source reads from database A. Each row is variable-width and up to several KB wide, including two ntext columns.

Command #1 executes a stored proc in db A, using a bunch of inputs and two output parameters.

Cmd #2 executes an update in db B, using the two output params from cmd #1 as inputs.

When the rowset size is small, around 500, everything works fine.

However, when the rowset size is larger, around 5000, SSIS hangs when trying to execute cmd #2. The profiler shows that none of the cmd #2 updates are ever executed. No error messages are produced, and the connection never times out -- it just hangs forever.

If I replace the cmd #2 updates with a simple select, everything works fine. If I replace it with a stored proc that does an update, it hangs.

The work-around I came up with was to create a new table in db B, and do inserts into the table, but unless I'm missing something, this still seems like a bug...

Do you have the RetainSameConnection property set to true on the OLE DB connection manager you are using? Try that if not.

Package Configuration Wizard Error

I am attempting to create an XML Configuration file and I am recieving the following error once I click finish in the wizard:

Could not complete wizard actions.

Additional Information:
Could not generate the configuration file. (Microsoft.Data.TransformationServices.Wizard)

Technical Details:
===================================

Could not complete wizard actions. (Microsoft Visual Studio)

===================================

Could not generate the configuration file. (Microsoft.DataTransformationServices.Wizards)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Wizards.ConfigurationWizardPages.ConfigurationWizardSR&EvtID=CouldNotGenerateConfigurationFile&LinkId=20476

Program Location:

at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.GenerateXmlConfigurationFile(DesignTimeConfiguration designTimeConfiguration)
at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.OnFinish(CancelEventArgs e)

===================================

Failure exporting configuration file.
(eFreedomAS)

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Package.ExportConfigurationFile(String str)
at Microsoft.DataTransformationServices.Wizards.ConfigurationWizardForm.GenerateXmlConfigurationFile(DesignTimeConfiguration designTimeConfiguration)

Name:
eFreedomAS2

Type:
Configuration File

New configuration file will be created.

File name:
C:\abc.xml

Properties:
\Package.Variables[User::SourcePath].Properties[Value]
\Package.Variables[User::SourcePath].Properties[Name]
\Package.Variables[User::Packages].Properties[Value]
\Package.Variables[User::Packages].Properties[Name]
\Package.Variables[User::PackageDefNumber].Properties[Value]
\Package.Variables[User::PackageDefNumber].Properties[Name]
\Package.Variables[User::Directory].Properties[Value]
\Package.Variables[User::Directory].Properties[Name]
\Package.Variables[User::DestinationPath].Properties[Value]
\Package.Variables[User::DestinationPath].Properties[Name]
\Package.Connections[ViewHelpTopicDef].Properties[Name]
\Package.Connections[ViewHelpTopicDef].Properties[ConnectionString]
\Package.Connections[ViewDef].Properties[Name]
\Package.Connections[ViewDef].Properties[ConnectionString]
\Package.Connections[ValidationTestDef].Properties[Name]
\Package.Connections[ValidationTestDef].Properties[ConnectionString]
\Package.Connections[ValidationRulePackageDef].Properties[Name]
\Package.Connections[ValidationRulePackageDef].Properties[ConnectionString]
\Package.Connections[ValidationRuleFileDef].Properties[Name]
\Package.Connections[ValidationRuleFileDef].Properties[ConnectionString]
\Package.Connections[ValidationRuleDef].Properties[Name]
\Package.Connections[ValidationRuleDef].Properties[ConnectionString]
\Package.Connections[ValidationMessageFileDef].Properties[Name]
\Package.Connections[ValidationMessageFileDef].Properties[ConnectionString]
\Package.Connections[ValidationMessageDef].Properties[Name]
\Package.Connections[ValidationMessageDef].Properties[ConnectionString]
\Package.Connections[ValidationIdentifierDependency].Properties[Name]
\Package.Connections[ValidationIdentifierDependency].Properties[ConnectionString]
\Package.Connections[ValidationCalcGraphEdge].Properties[Name]
\Package.Connections[ValidationCalcGraphEdge].Properties[ConnectionString]
\Package.Connections[UserGroupFeatureDef].Properties[Name]
\Package.Connections[UserGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[UserGroupFeature].Properties[Name]
\Package.Connections[UserGroupFeature].Properties[ConnectionString]
\Package.Connections[UserGroupDef].Properties[Name]
\Package.Connections[UserGroupDef].Properties[ConnectionString]
\Package.Connections[UserGroup].Properties[Name]
\Package.Connections[UserGroup].Properties[ConnectionString]
\Package.Connections[TableDef].Properties[Name]
\Package.Connections[TableDef].Properties[ConnectionString]
\Package.Connections[Source].Properties[UserName]
\Package.Connections[Source].Properties[ServerName]
\Package.Connections[Source].Properties[Password]
\Package.Connections[Source].Properties[Name]
\Package.Connections[Source].Properties[InitialCatalog]
\Package.Connections[Source].Properties[ConnectionString]
\Package.Connections[SelectListItemDef].Properties[Name]
\Package.Connections[SelectListItemDef].Properties[ConnectionString]
\Package.Connections[SelectListDef].Properties[Name]
\Package.Connections[SelectListDef].Properties[ConnectionString]
\Package.Connections[ScriptAssemblyDef].Properties[Name]
\Package.Connections[ScriptAssemblyDef].Properties[ConnectionString]
\Package.Connections[PrintedPageFormulaScriptDef].Properties[Name]
\Package.Connections[PrintedPageFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[PageDef].Properties[Name]
\Package.Connections[PageDef].Properties[ConnectionString]
\Package.Connections[PageCalcPathSubPath].Properties[Name]
\Package.Connections[PageCalcPathSubPath].Properties[ConnectionString]
\Package.Connections[PageCalcPath].Properties[Name]
\Package.Connections[PageCalcPath].Properties[ConnectionString]
\Package.Connections[PackageLinkDef].Properties[Name]
\Package.Connections[PackageLinkDef].Properties[ConnectionString]
\Package.Connections[PackageImportTypeDef].Properties[Name]
\Package.Connections[PackageImportTypeDef].Properties[ConnectionString]
\Package.Connections[PackageIdentifierGroupDef].Properties[Name]
\Package.Connections[PackageIdentifierGroupDef].Properties[ConnectionString]
\Package.Connections[PackageIdentifierDef].Properties[Name]
\Package.Connections[PackageIdentifierDef].Properties[ConnectionString]
\Package.Connections[PackageExportTypeDef].Properties[Name]
\Package.Connections[PackageExportTypeDef].Properties[ConnectionString]
\Package.Connections[PackageDef].Properties[Name]
\Package.Connections[PackageDef].Properties[ConnectionString]
\Package.Connections[PackageDataItemTextDef].Properties[Name]
\Package.Connections[PackageDataItemTextDef].Properties[ConnectionString]
\Package.Connections[OnlinePagePrintedPageDef].Properties[Name]
\Package.Connections[OnlinePagePrintedPageDef].Properties[ConnectionString]
\Package.Connections[OnlinePageDefDependency].Properties[Name]
\Package.Connections[OnlinePageDefDependency].Properties[ConnectionString]
\Package.Connections[OnlinePageDef].Properties[Name]
\Package.Connections[OnlinePageDef].Properties[ConnectionString]
\Package.Connections[MaterialityToleranceDef].Properties[Name]
\Package.Connections[MaterialityToleranceDef].Properties[ConnectionString]
\Package.Connections[LineDef].Properties[Name]
\Package.Connections[LineDef].Properties[ConnectionString]
\Package.Connections[ImportTypeDef].Properties[Name]
\Package.Connections[ImportTypeDef].Properties[ConnectionString]
\Package.Connections[ImportDef].Properties[Name]
\Package.Connections[ImportDef].Properties[ConnectionString]
\Package.Connections[IdentifierStateDef].Properties[Name]
\Package.Connections[IdentifierStateDef].Properties[ConnectionString]
\Package.Connections[IdentifierRelatedDef].Properties[Name]
\Package.Connections[IdentifierRelatedDef].Properties[ConnectionString]
\Package.Connections[IdentifierPrintReferenceDef].Properties[Name]
\Package.Connections[IdentifierPrintReferenceDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupUserGroupFeatureDef].Properties[Name]
\Package.Connections[IdentifierGroupUserGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupUserGroupFeature].Properties[Name]
\Package.Connections[IdentifierGroupUserGroupFeature].Properties[ConnectionString]
\Package.Connections[IdentifierGroupIdentifierDef].Properties[Name]
\Package.Connections[IdentifierGroupIdentifierDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroupIdentifier].Properties[Name]
\Package.Connections[IdentifierGroupIdentifier].Properties[ConnectionString]
\Package.Connections[IdentifierGroupDef].Properties[Name]
\Package.Connections[IdentifierGroupDef].Properties[ConnectionString]
\Package.Connections[IdentifierGroup].Properties[Name]
\Package.Connections[IdentifierGroup].Properties[ConnectionString]
\Package.Connections[IdentifierDef].Properties[Name]
\Package.Connections[IdentifierDef].Properties[ConnectionString]
\Package.Connections[IdentifierDataItemTextDef].Properties[Name]
\Package.Connections[IdentifierDataItemTextDef].Properties[ConnectionString]
\Package.Connections[FormulaScriptDef].Properties[Name]
\Package.Connections[FormulaScriptDef].Properties[ConnectionString]
\Package.Connections[FormulaDef].Properties[Name]
\Package.Connections[FormulaDef].Properties[ConnectionString]
\Package.Connections[FeatureGroupFeatureDef].Properties[Name]
\Package.Connections[FeatureGroupFeatureDef].Properties[ConnectionString]
\Package.Connections[FeatureGroupDef].Properties[Name]
\Package.Connections[FeatureGroupDef].Properties[ConnectionString]
\Package.Connections[FeatureDef].Properties[Name]
\Package.Connections[FeatureDef].Properties[ConnectionString]
\Package.Connections[ExportTypeRelatedDef].Properties[Name]
\Package.Connections[ExportTypeRelatedDef].Properties[ConnectionString]
\Package.Connections[ExportTypeFormulaScriptDef].Properties[Name]
\Package.Connections[ExportTypeFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[ExportTypeExportDef].Properties[Name]
\Package.Connections[ExportTypeExportDef].Properties[ConnectionString]
\Package.Connections[ExportTypeDef].Properties[Name]
\Package.Connections[ExportTypeDef].Properties[ConnectionString]
\Package.Connections[ExportTypeBlobTypeDef].Properties[Name]
\Package.Connections[ExportTypeBlobTypeDef].Properties[ConnectionString]
\Package.Connections[ExportFormulaScriptDef].Properties[Name]
\Package.Connections[ExportFormulaScriptDef].Properties[ConnectionString]
\Package.Connections[ExportDef].Properties[Name]
\Package.Connections[ExportDef].Properties[ConnectionString]
\Package.Connections[ColumnHeaderDef].Properties[Name]
\Package.Connections[ColumnHeaderDef].Properties[ConnectionString]
\Package.Connections[ColumnDef].Properties[Name]
\Package.Connections[ColumnDef].Properties[ConnectionString]
\Package.Connections[CellTypeFileTypeDef].Properties[Name]
\Package.Connections[CellTypeFileTypeDef].Properties[ConnectionString]
\Package.Connections[CellTypeDef].Properties[Name]
\Package.Connections[CellTypeDef].Properties[ConnectionString]
\Package.Connections[CellRangeDef].Properties[Name]
\Package.Connections[CellRangeDef].Properties[ConnectionString]
\Package.Connections[CellRangeCellDef].Properties[Name]
\Package.Connections[CellRangeCellDef].Properties[ConnectionString]
\Package.Connections[CellOverrideDef].Properties[Name]
\Package.Connections[CellOverrideDef].Properties[ConnectionString]
\Package.Connections[CellDef].Properties[Name]
\Package.Connections[CellDef].Properties[ConnectionString]
\Package.Connections[CategoryDef].Properties[Name]
\Package.Connections[CategoryDef].Properties[ConnectionString]
\Package.Connections[CalcPathSubPath].Properties[Name]
\Package.Connections[CalcPathSubPath].Properties[ConnectionString]
\Package.Connections[CalcPath].Properties[Name]
\Package.Connections[CalcPath].Properties[ConnectionString]
\Package.Connections[CalcGraphEdge].Properties[Name]
\Package.Connections[CalcGraphEdge].Properties[ConnectionString]
\Package.Connections[AreaDef].Properties[Name]
\Package.Connections[AreaDef].Properties[ConnectionString]

Found the issue myself. One of the variables I had unintentionally set for the package configuration was of type Object. Rightfully so, you are unable to save a variable of type object to be saved in an xml configuration file.

Ownership of Stored Procedures/Functions By Role Other Than dbo

I would like to enforce the following security policy. This policy is used in our other db systems (Rdb and Oracle), and I'm thinking about how to implement this in SQL Server. (Yes, I'm a DBA.)

(1) Developers are not allowed to create/alter/delete tables owned
by dbo. To prevent this, no developers will be granted role db_owner.
Developers should only be creating/modifying stored procedures/functions.

(2) All tables will be owned by dbo. DBA's (who have role db_owner
and server privileges) will be creating/modifying table definitions. DBA's will also be granting individual table priv's to the developers (most likely through the role "dco" below).

(3) All stored procedures/functions will be owned by a new role "dco"
(database code owner). All developers will be granted role "dco". No tables should be created in "dco", so role "dco" will be DENY-ed the privilege CREATE TABLE. (I'm also thinking about restricting view creation to dbo by DENY-ing CREATE VIEW.) DBA's will implicitly get access to dco procs/funcs from server privileges.

Does anybody see any possible problems with this approach? Have you tried anything like this? I've read about "broken ownership chains", but as long as the DBA grants the object privs on the tables to the developers I don't see a problem.

Thanks in advance for any input.

JeffWho will be the owner of what the DCO's will create?|||Role "dco" will own the stored procedures/functions, ie:

Create Procedure mydb.dco.myproc ...
Create Function mydb.dco.myfunc ...

Jeff

Owner's Access to table

SQL SERVER 2000 Personal Edition
Win 2000
Connect using SqlServer Authentification
Jim **pw**
Run the following to create a new table:-
USE SolutionsNet
GO
CREATE TABLE
Companies
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50) NOT NULL,
IsaRestaurant BIT,
. . . . .
)
GO
Then the following:-
Use SolutionsNet
GO
SELECT * FROM Companies
REM OR select * from jim.Companies
GO
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet',
owner 'Jim'.
BUT
connect as sa **pw**
USE SolutionsNet
GO
SELECT * FROM Jim.Companies
GO
Displays the empty table Ok
[NB. just Companies without tyhe prefix Jim. does not display the empty
table
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Companies'. ]
?
Why can I not access the table connected as Jim - it's owner !!!
?
Jim Bunton
Ok - got the answer on sqlserver.tools group -
I'd 'clicked' all roles 'to make sure!!!!" one of them is 'denydatareader' -

> beginners beware!!!
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:gpO4f.135773$RW.48588@.fe2.news.blueyonder.co. uk...
> SQL SERVER 2000 Personal Edition
> Win 2000
> Connect using SqlServer Authentification
> Jim **pw**
> Run the following to create a new table:-
> --
> USE SolutionsNet
> GO
> CREATE TABLE
> Companies
> (CompanyId INT PRIMARY KEY IDENTITY(1,1),
> Companyname VarChar(50) NOT NULL,
> IsaRestaurant BIT,
> . . . . .
> )
> GO
> --
> Then the following:-
> --
> Use SolutionsNet
> GO
> SELECT * FROM Companies
> REM OR select * from jim.Companies
> GO
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database 'SolutionsNet',
> owner 'Jim'.
> BUT
> connect as sa **pw**
> --
> USE SolutionsNet
> GO
> SELECT * FROM Jim.Companies
> GO
> --
> Displays the empty table Ok
> [NB. just Companies without tyhe prefix Jim. does not display the empty
> table
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Companies'. ]
> ?
> Why can I not access the table connected as Jim - it's owner !!!
> ?
>
> Jim Bunton
>
|||He must be in some groups which denies seeing table data. Try to
determine this via sp_helpuser 'Username'
HTH, jens Suessmeyer.

Owner's Access to table

SQL SERVER 2000 Personal Edition
Win 2000
Connect using SqlServer Authentification
Jim **pw**
Run the following to create a new table:-
--
USE SolutionsNet
GO
CREATE TABLE
Companies
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50) NOT NULL,
IsaRestaurant BIT,
. . . . .
)
GO
--
Then the following:-
--
Use SolutionsNet
GO
SELECT * FROM Companies
REM OR select * from jim.Companies
GO
--
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet',
owner 'Jim'.
BUT
connect as sa **pw**
--
USE SolutionsNet
GO
SELECT * FROM Jim.Companies
GO
--
Displays the empty table Ok
[NB. just Companies without tyhe prefix Jim. does not display the empty
table
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'Companies'. ]
'
Why can I not access the table connected as Jim - it's owner !!!
?
Jim BuntonOk - got the answer on sqlserver.tools group -
I'd 'clicked' all roles 'to make sure!!!!" one of them is 'denydatareader' -

> beginners beware!!!
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message
news:gpO4f.135773$RW.48588@.fe2.news.blueyonder.co.uk...
> SQL SERVER 2000 Personal Edition
> Win 2000
> Connect using SqlServer Authentification
> Jim **pw**
> Run the following to create a new table:-
> --
> USE SolutionsNet
> GO
> CREATE TABLE
> Companies
> (CompanyId INT PRIMARY KEY IDENTITY(1,1),
> Companyname VarChar(50) NOT NULL,
> IsaRestaurant BIT,
> . . . . .
> )
> GO
> --
> Then the following:-
> --
> Use SolutionsNet
> GO
> SELECT * FROM Companies
> REM OR select * from jim.Companies
> GO
> --
> Server: Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'Companies', database 'SolutionsNet',
> owner 'Jim'.
> BUT
> connect as sa **pw**
> --
> USE SolutionsNet
> GO
> SELECT * FROM Jim.Companies
> GO
> --
> Displays the empty table Ok
> [NB. just Companies without tyhe prefix Jim. does not display the empt
y
> table
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'Companies'. ]
> '
> Why can I not access the table connected as Jim - it's owner !!!
> ?
>
> Jim Bunton
>|||He must be in some groups which denies seeing table data. Try to
determine this via sp_helpuser 'Username'
HTH, jens Suessmeyer.

Overlapping Sets

I have the following table structure

CREATE TABLE [dbo].[QDisc](
[Id] [int] NOT NULL,
[MinVal] [int] NOT NULL,
[MaxVal] [int] NOT NULL,
[PerVal] [int] NOT NULL,
CONSTRAINT [PK_QDisc] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I need to be able to select unique overlapping sets of data based on the minval and maxval.

Simple Example
minval maxval
5 15
16 25
10 20

query would produce
minval maxval
5 10
11 15
16 20
21 25

More Complex example
minval maxval
5 15
16 25
10 20
7 7
1 100

query would produce
minval maxval
1 5
6 6
7 7
8 10
11 15
16 20
21 25
26 100

Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.Extra points for doing your homework assignment for you?|||Extra points if anyone can do this without SP and cursor but I'd be satisfied if someone supplied it that way.Gosh - generous and gracious. How super.|||I'll give myself extra credit

ALTER PROCEDURE [dbo].[usp_Select_Disc]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select minval, maxval into #QtyRange from qdisc

declare @.minval int
declare @.maxval int
declare @.xminval int
declare @.xmaxval int

declare @.pmaxval int
declare @.pmaxva2 int

declare @.loopflg bit
set @.loopflg = 1
while @.loopflg = 1
begin
set @.loopflg = 0
DECLARE curSel CURSOR FOR select minval, maxval from #QtyRange order by minval
open curSel
fetch next from curSel into @.minval, @.maxval
WHILE (@.@.FETCH_STATUS <> -1)
begin

set @.xminval = null
set @.xmaxval = null

select top(1) @.xminval = minval, @.xmaxval = maxval from #QtyRange where minval > @.minval or maxval > @.maxval order by minval

if @.maxval > @.xminval
begin
set @.pmaxval = @.maxval
if @.minval = @.xminval
begin
set @.pmaxva2 = @.xmaxval
update #QtyRange set maxval = @.xminval where minval = @.minval and maxval = @.maxval
update #QtyRange set minval = @.xminval + 1, maxval = @.pmaxval where minval = @.xminval and maxval = @.xmaxval
insert into #QtyRange values (@.pmaxval + 1, @.pmaxva2)
set @.loopflg = 1
break
end
update #QtyRange set maxval = @.xminval - 1 where minval = @.minval and maxval = @.maxval

if @.xmaxval > @.pmaxval
begin
set @.pmaxva2 = @.xmaxval
update #QtyRange set maxval = @.pmaxval where minval = @.xminval and maxval = @.xmaxval
insert into #QtyRange values (@.pmaxval + 1, @.pmaxva2)
set @.loopflg = 1
break
end
if @.xmaxval < @.pmaxval
insert into #QtyRange values (@.xmaxval + 1, @.pmaxval)
set @.loopflg = 1
end

if @.maxval = @.xminval
begin
set @.pmaxval = @.maxval

if @.minval = @.maxval
begin
update #QtyRange set minval = @.pmaxval + 1 where minval = @.xminval and maxval = @.xmaxval
set @.loopflg = 1
break
end
else
begin
update #QtyRange set maxval = @.pmaxval - 1 where minval = @.minval and maxval = @.maxval
if @.xminval <> @.xmaxval
begin
insert into #QtyRange values (@.pmaxval, @.pmaxval)
update #QtyRange set minval = @.pmaxval + 1 where minval = @.xminval and maxval = @.xmaxval
set @.loopflg = 1
break
end
end
end
fetch next from curSel into @.minval, @.maxval
end
Close curSel
DEALLOCATE curSel
end

select distinct(a.minval), a.maxval, sum(qdisc.perval) from #QtyRange a
inner join qdisc on a.minval >= qdisc.minval and a.maxval <= qdisc.maxval
group by a.minval,a.maxval
order by minval
END|||I'll give myself extra credit

You shouldn't, you used a cursor.|||You shouldn't, you used a cursor.
I'm not sure it can be done without a cursor or some other form of looping. I see no way to do it with a simple SELECT statement.|||neither do I, but that was his self-professed requirement for extra points.|||No no no. That was the requirement for YOU to get extra points. He, of course, is free to give himself as much credit as he wants.|||how many points do i need to get a free ship?|||this worked for both the data sets provided by u. i have tried with a couple of other sets and appears to be working. interested to know if it worked at your end or not...

create table #tt1 (Id int identity (1,1), Val int )
insert into #tt1 (Val) select Val from (
select Minval as Val from QDisc
union all
select Maxval as Val from QDisc
) A order by 1

select B.Id,A.Val AVal, B.Val BVal, C.Val CVal into #tt2
from #tt1 A, #tt1 B, #tt1 C where A.Id+1 =* B.Id and C.Id-1 =* B.Id

update #tt2 set BVal=BVal-1 where AVal+1=BVal
update #tt2 set CVal=Null where BVal+1=CVal
update #tt2 set CVal=CVal-1 from #tt2 A where A.CVal= (select BVal from #tt2 where #tt2.id = A.id+1 and BVal=CVal)

select
BVal+ case when BVal=(select min(BVal) from #tt2) then 0 when BVal= CVal then 0 else 1 end as MinVal,
CVal as MaxVal
from #tt2
where CVal is not null

Overlapping Permissions

I would think the following scenario should work, but it does not:
I have a table, Products, for which all users, via an NT domain group (e.g.
Domain Users) have only select permissions.
There is another group, ProductManagers, who are also members of the above
group, who need update, delete, and insert permissions. To accomplish this,
I
created a database role ProductMgmt, and added the ProductManagers to it.
This role has select, insert, update and delete permissions on the table.
The members of this group, however, get an error when attempting to delete
from the table. These members belong to both the Domain User and
ProductManagers groups.
I've also given the ProductManager group full permissions on the table. I'm
confused as to why all of this is not working, obviously I'm missing
something.
Thanks for any assistance,
TomtDoes the NT group which has only select permissions have a
deny on delete? Do any users or groups have deny set on the
table?
Permissions are cumulative but deny will take precedence.
-Sue
On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
wrote:

>I would think the following scenario should work, but it does not:
>I have a table, Products, for which all users, via an NT domain group (e.g.
>Domain Users) have only select permissions.
>There is another group, ProductManagers, who are also members of the above
>group, who need update, delete, and insert permissions. To accomplish this,
I
>created a database role ProductMgmt, and added the ProductManagers to it.
>This role has select, insert, update and delete permissions on the table.
>The members of this group, however, get an error when attempting to delete
>from the table. These members belong to both the Domain User and
>ProductManagers groups.
>I've also given the ProductManager group full permissions on the table. I'm
>confused as to why all of this is not working, obviously I'm missing
>something.
>Thanks for any assistance,
>Tomt
>|||Sue,
Thanks for your reply. No, there are no deny's on delete. I did know that
one, but am missing something...
Thanks
Tom
"Sue Hoegemeier" wrote:

> Does the NT group which has only select permissions have a
> deny on delete? Do any users or groups have deny set on the
> table?
> Permissions are cumulative but deny will take precedence.
> -Sue
> On Tue, 9 Nov 2004 14:51:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||And there are no other Windows groups and no other roles in
that database? Just the two roles and the two NT groups?
And members of the ProductMgmt role can select, insert and
update but not delete?
-Sue
On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>Sue,
>Thanks for your reply. No, there are no deny's on delete. I did know that
>one, but am missing something...
>Thanks
>Tom
>"Sue Hoegemeier" wrote:
>|||There's the Domain Users and two other non-NT, SQL Server accounts for web
access to the table.
There are no other roles other than the built-in roles. That group has
select, insert, update and delete permissions.
I'm going to have them try it again tomorrow, I might have overlooked
checking the delete permission, which is just due to trying to do too many
things at once...
I'll post the results tomorrow. Thanks for your assistance with this.
Tom
"Sue Hoegemeier" wrote:

> And there are no other Windows groups and no other roles in
> that database? Just the two roles and the two NT groups?
> And members of the ProductMgmt role can select, insert and
> update but not delete?
> -Sue
> On Tue, 9 Nov 2004 15:44:04 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Tom
Grant them EXECUTE permission on SP that perform DELETE/INSERT/UPDATE on
this table.
"TomT" <tomt@.tomt.com> wrote in message
news:F6DB5A9A-6ADF-4F89-89E8-9656A8962BFF@.microsoft.com...[vbcol=seagreen]
> There's the Domain Users and two other non-NT, SQL Server accounts for web
> access to the table.
> There are no other roles other than the built-in roles. That group has
> select, insert, update and delete permissions.
> I'm going to have them try it again tomorrow, I might have overlooked
> checking the delete permission, which is just due to trying to do too many
> things at once...
> I'll post the results tomorrow. Thanks for your assistance with this.
> Tom
> "Sue Hoegemeier" wrote:
>
that[vbcol=seagreen]
group (e.g.[vbcol=seagreen]
above[vbcol=seagreen]
accomplish this, I[vbcol=seagreen]
to it.[vbcol=seagreen]
table.[vbcol=seagreen]
delete[vbcol=seagreen]
table. I'm[vbcol=seagreen]|||You really should check the other permissions as well as it
could make it easier for you to determine what has been
missed. Check the select, insert and update permissions as
well.
-Sue
On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>There's the Domain Users and two other non-NT, SQL Server accounts for web
>access to the table.
>There are no other roles other than the built-in roles. That group has
>select, insert, update and delete permissions.
>I'm going to have them try it again tomorrow, I might have overlooked
>checking the delete permission, which is just due to trying to do too many
>things at once...
>I'll post the results tomorrow. Thanks for your assistance with this.
>Tom
>"Sue Hoegemeier" wrote:
>|||I checked them all, for that particular group, and still no go. I have to
grant the permissions for the Domain Users group for insert, delete, etc.
otherwise the group I really need to have this access does not.
To summarize: Two groups (NT) Domain Users, to which all users belong,
member of the public role, and ProductManagers, member of public and
ProductMgmt roles.
A user, Rod, belongs to both Domain Users and ProductManagers groups.
ProductMangers have select, insert, delete and update permissions on table;
Domain Users have Select permission only, no other permissions granted or
denied.
Database role ProductMgmt has full permissions on the table.
With the scenario above, Rod cannot delete from the table. I have to grant
delete permissions to Domain Users in order for him to be able to delete row
s
from the table.
I gather from your replies that this should work, and I have set it up
correctly, is that right?
Thanks,
Tom
"Sue Hoegemeier" wrote:

> You really should check the other permissions as well as it
> could make it easier for you to determine what has been
> missed. Check the select, insert and update permissions as
> well.
> -Sue
> On Tue, 9 Nov 2004 21:31:03 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>|||Yes it will work so you are still missing something. I can't
reproduce the issue rebuilding with the same groups and
roles - it works fine on my end.
Try using xp_logininfo to determine the group membership and
dsiplay information on the Product Managers group at the
Windows level.
-Sue
On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
wrote:
[vbcol=seagreen]
>I checked them all, for that particular group, and still no go. I have to
>grant the permissions for the Domain Users group for insert, delete, etc.
>otherwise the group I really need to have this access does not.
>To summarize: Two groups (NT) Domain Users, to which all users belong,
>member of the public role, and ProductManagers, member of public and
>ProductMgmt roles.
>A user, Rod, belongs to both Domain Users and ProductManagers groups.
>ProductMangers have select, insert, delete and update permissions on table;
>Domain Users have Select permission only, no other permissions granted or
>denied.
>Database role ProductMgmt has full permissions on the table.
>With the scenario above, Rod cannot delete from the table. I have to grant
>delete permissions to Domain Users in order for him to be able to delete ro
ws
>from the table.
>I gather from your replies that this should work, and I have set it up
>correctly, is that right?
>Thanks,
>Tom
>"Sue Hoegemeier" wrote:
>|||Sue,
I found the problem, the person who set up the NT user group
ProductManagers, set it up as a distribution group, not a security group.
Once that was fixed, everything works correctly.
BTW, I assume this would still work without the database role, i.e., just
the ProductManager group having the delete, etc. permissions assigned,
correct?
thanks for your help and patience,
Tom
"Sue Hoegemeier" wrote:

> Yes it will work so you are still missing something. I can't
> reproduce the issue rebuilding with the same groups and
> roles - it works fine on my end.
> Try using xp_logininfo to determine the group membership and
> dsiplay information on the Product Managers group at the
> Windows level.
> -Sue
> On Wed, 10 Nov 2004 08:45:01 -0800, "TomT" <tomt@.tomt.com>
> wrote:
>
>

Overlapping Indexes

In examining one of our DBs there is a sales table with the following fields:

WDate: SMALLDATETIME PK
StoreID INT PK
ItemID: INT PK
UnitsSold: SMALLINT
TotalSales: REAL
SalesCode: CHAR(1)

There are also other attributes that do not pertain to the question.

The following Indexes are in the Sales table:

PK_Sales: (Clustered) Composit Primary Key
WDate, StoreID, ItemID

IX_Sales_SalesCode: (Non-Clustered)
SalesCode

IX_Sales_Cover: (Non-Clustered)
WDate, StoreID, ItemID, UnitsSold, TotalSales

Typical queries include:
Querying by the 3 field PK
Querying by the 3 field PK + Sales Code and summing UnitsSold and Total Sales
Querying by the 3 field PK and summing UnitsSold and Total Sales

We have several versions of this same DB with different Data in it for different Store Chains. Since we must keep 2 years of history in the DB, the sales table can be quite large. One of our versions of the DB has nearly 1 billion records and the indexing seems to run quite efficiently. In other words no huge amount of time spend querying (Keep in mind there are nearly a billion records.)

My question is:
Do these indexes look correct? Obviously the PK index is fine but are the other 2 indexes ever being hit? In other words, are the 2 indexes other that the PK index worth keeping or are they just taking up disk space and degradding performance?

Thanks in advance for any help on this.
mcatet

You have multiple ways to find out
(1) you can look at the query plan generated for each query and see what indexes are being used
(2) you can use DMV sys.dm_db_index_usage_stats to find out the usage statistics of each of the indexes since the SQL server started. Please refer to BOL for more details. There are other index related DMVs that you may find useful

Without knowing the text of your queries (we need to know what columns are being selected and what predicated are being used) and the selectivity of each predicate, it is hard to know what indexes will be used.

Some observations:
Query-1: If you are using range predicates and the select clause has columns not covered by indexx-3, then this is definitely useful.
Query-3: since it is covered by index-3 keys, it may be useful as it will avoid accessing the datapage.

|||Besides the advices from Sunil you can consider also using Index Tuning Wizard (in SQL Server 2000) or Database Tuning Advisor (in SQL Server 2005). The best is to provide representative sample of the workload to the tool.

Overlapping Indexes

In examining one of our DBs there is a sales table with the following fields:

WDate: SMALLDATETIME PK
StoreID INT PK
ItemID: INT PK
UnitsSold: SMALLINT
TotalSales: REAL
SalesCode: CHAR(1)

There are also other attributes that do not pertain to the question.

The following Indexes are in the Sales table:

PK_Sales: (Clustered) Composit Primary Key
WDate, StoreID, ItemID

IX_Sales_SalesCode: (Non-Clustered)
SalesCode

IX_Sales_Cover: (Non-Clustered)
WDate, StoreID, ItemID, UnitsSold, TotalSales

Typical queries include:
Querying by the 3 field PK
Querying by the 3 field PK + Sales Code and summing UnitsSold and Total Sales
Querying by the 3 field PK and summing UnitsSold and Total Sales

We have several versions of this same DB with different Data in it for different Store Chains. Since we must keep 2 years of history in the DB, the sales table can be quite large. One of our versions of the DB has nearly 1 billion records and the indexing seems to run quite efficiently. In other words no huge amount of time spend querying (Keep in mind there are nearly a billion records.)

My question is:
Do these indexes look correct? Obviously the PK index is fine but are the other 2 indexes ever being hit? In other words, are the 2 indexes other that the PK index worth keeping or are they just taking up disk space and degradding performance?

Thanks in advance for any help on this.
mcatet

You have multiple ways to find out
(1) you can look at the query plan generated for each query and see what indexes are being used
(2) you can use DMV sys.dm_db_index_usage_stats to find out the usage statistics of each of the indexes since the SQL server started. Please refer to BOL for more details. There are other index related DMVs that you may find useful

Without knowing the text of your queries (we need to know what columns are being selected and what predicated are being used) and the selectivity of each predicate, it is hard to know what indexes will be used.

Some observations:
Query-1: If you are using range predicates and the select clause has columns not covered by indexx-3, then this is definitely useful.
Query-3: since it is covered by index-3 keys, it may be useful as it will avoid accessing the datapage.

|||Besides the advices from Sunil you can consider also using Index Tuning Wizard (in SQL Server 2000) or Database Tuning Advisor (in SQL Server 2005). The best is to provide representative sample of the workload to the tool.

Overhead for transactions

Hi all,
I am looking at the following codes. I thought that
defining the transaction is redundant. Delete by itself
IS a transaction. But what I don't know is: how much
extra overhead is added by explicitly defining the
tranaction?
Thanks in advance, Anna
--
BEGIN TRAN Del_C_ActiveBenefits
DELETE dbo.C_ActiveBenefits
WHERE ClientCode = @.ClientCode
AND PlanCode = @.PlanCode
AND ValDate = @.ValDate
If (@.@.ERROR <> 0)
BEGIN
Rollback TRAN Del_C_ActiveBenefits
--initialize Error Num from global variable
SET @.Error_Num = @.@.ERROR
-- initialize the Error description
SET @.Error_Desc = 'Error Deleting C_ActiveBenefits
Data From Target Table'
GOTO Error_Handler -- trap & handle the Error
generated
END
Commit TRAN Del_C_ActiveBenefits
--There's no extra overhead. You might end up in carrying locks over longer time, so you can
experience higher risk of blocking of course. Also, grouping several DML commands in one transaction
can increase performance as it will reduce I/O (each commit, implicit or explicit result in an I/O
to the t-log file). However, you don't want to do too many DML's in one transaction, say in a back
job, perhaps limit to 10000 DML's in one transaction.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Anna Lin" <anonymous@.discussions.microsoft.com> wrote in message
news:063f01c3af0b$57505790$a401280a@.phx.gbl...
> Hi all,
> I am looking at the following codes. I thought that
> defining the transaction is redundant. Delete by itself
> IS a transaction. But what I don't know is: how much
> extra overhead is added by explicitly defining the
> tranaction?
> Thanks in advance, Anna
> --
> BEGIN TRAN Del_C_ActiveBenefits
> DELETE dbo.C_ActiveBenefits
> WHERE ClientCode = @.ClientCode
> AND PlanCode = @.PlanCode
> AND ValDate = @.ValDate
> If (@.@.ERROR <> 0)
> BEGIN
> Rollback TRAN Del_C_ActiveBenefits
> --initialize Error Num from global variable
> SET @.Error_Num = @.@.ERROR
> -- initialize the Error description
> SET @.Error_Desc = 'Error Deleting C_ActiveBenefits
> Data From Target Table'
> GOTO Error_Handler -- trap & handle the Error
> generated
> END
> Commit TRAN Del_C_ActiveBenefits
> --|||In addition to Tibor's comments may I suggest you review the error =handling code.
The line SET @.Error_Num =3D @.@.ERROR is after the rollback and hence =@.@.error will have been rest to zero by the rollback, so whatever is =using @.error_num is going to get zero. Safest way normally is imediately =after the statement you want to trap include:
Set @.errno =3D @.@.error,@.rcount =3D @.@.rowcount -- or similar names for =the variables
Then check @.rcount and/or @.errno for whatever you need.
Mike John
"Anna Lin" <anonymous@.discussions.microsoft.com> wrote in message =news:063f01c3af0b$57505790$a401280a@.phx.gbl...
> Hi all, > I am looking at the following codes. I thought that > defining the transaction is redundant. Delete by itself > IS a transaction. But what I don't know is: how much > extra overhead is added by explicitly defining the > tranaction?
> > Thanks in advance, Anna
> --
> BEGIN TRAN Del_C_ActiveBenefits
> DELETE dbo.C_ActiveBenefits
> WHERE ClientCode =3D @.ClientCode
> AND PlanCode =3D @.PlanCode
> AND ValDate =3D @.ValDate
> > If (@.@.ERROR <> 0)
> BEGIN > Rollback TRAN Del_C_ActiveBenefits
> --initialize Error Num from global variable
> SET @.Error_Num =3D @.@.ERROR > -- initialize the Error description > SET @.Error_Desc =3D 'Error Deleting C_ActiveBenefits > Data From Target Table'
> GOTO Error_Handler -- trap & handle the Error > generated > END > > Commit TRAN Del_C_ActiveBenefits
> --

Overflowed int column

I am trying to run a stored procedure but I get the
following error:
Server: Msg 248, Level 16, State 1, Procedure
sp_MarriageLookup, Line 34
The conversion of the varchar value '2820103430'
overflowed an int column. Maximum integer value exceeded.
Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
Return Code = -6
Output Parameter(s):
@.Barcode = 2820103430
The length of the @.Barcode variable is 10 characters and
it is a nchar type variable so I don't understand the 'int
column' overflow issue.
Also, the wierd thing is that I can run this same stored
procedure on another server (similar database, just
different data) and it works just fine. See below:
Stored Procedure: Unifirst.dbo.sp_MarriageLookup
Return Code = 0
Output Parameter(s):
@.Barcode = 4320000849
I don't see anything noticibly different between the two
databases.
What causes this problem and how do I correct?
Roger.Here is a copy of the SP:
/*
Purpose: Given a marriage tag code, return the original
barcode
Input params: Marriage tag code, type varchar(10)
Output params: Original bar code, type varchar(10)
Error params: Output of '9' indicates lookup failed
*/
CREATE PROCEDURE sp_MarriageLookup
@.Tag varchar(10),
@.Barcode varchar(10) output
AS
Declare @.eventid int
Declare cur_Marriage cursor for
Select CurrentTag from tblMarriage
Where MarriageTag = @.Tag
Open cur_Marriage
Fetch next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status <> 0)
Begin
/* Error log: Lookup failed */
Set @.Barcode = 9
Set @.eventid = 901
End
Else Begin
Fetch Next from cur_Marriage into @.Barcode
If (@.@.Fetch_Status = 0)
Begin
/* Error log: More than one entry for the marriage
code */
Set @.Barcode = 9
Set @.eventid = 902
End
End
Close cur_Marriage
Deallocate cur_Marriage
If (@.Barcode = 9)
Begin
Execute sp_LogError 1, @.eventid, 0, @.Tag
End
GO
>--Original Message--
>I am trying to run a stored procedure but I get the
>following error:
>Server: Msg 248, Level 16, State 1, Procedure
>sp_MarriageLookup, Line 34
>The conversion of the varchar value '2820103430'
>overflowed an int column. Maximum integer value exceeded.
>Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
>The length of the @.Barcode variable is 10 characters and
>it is a nchar type variable so I don't understand
the 'int
>column' overflow issue.
>Also, the wierd thing is that I can run this same stored
>procedure on another server (similar database, just
>different data) and it works just fine. See below:
>Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
>I don't see anything noticibly different between the two
>databases.
>What causes this problem and how do I correct?
>Roger.
>.
>|||Maximum value for int (SQL Server) data type is 2147483647.
So, if you convert varchar value '2820103430' to int,
overflow error is normal.
In the secend case, when this stored procedure
work correctly on another server, check data type
of the column to which this sp convert varchar parameter
ph
> I am trying to run a stored procedure but I get the
> following error:
> Server: Msg 248, Level 16, State 1, Procedure
> sp_MarriageLookup, Line 34
> The conversion of the varchar value '2820103430'
> overflowed an int column. Maximum integer value exceeded.
> Stored Procedure: Unifirst919.dbo.sp_MarriageLookup
> Return Code = -6
> Output Parameter(s):
> @.Barcode = 2820103430
> The length of the @.Barcode variable is 10 characters and
> it is a nchar type variable so I don't understand the 'int
> column' overflow issue.
> Also, the wierd thing is that I can run this same stored
> procedure on another server (similar database, just
> different data) and it works just fine. See below:
> Stored Procedure: Unifirst.dbo.sp_MarriageLookup
> Return Code = 0
> Output Parameter(s):
> @.Barcode = 4320000849
> I don't see anything noticibly different between the two
> databases.
> What causes this problem and how do I correct?
> Roger.

Overcoming the workload limitations of MSDE

Hi
The following topic deals with editing SQLBOOT.DLL by using a
hexadecimal editor
so we can disable workload Governor. Is it true?
Pls comment
http://groups.google.com/group/micro...qlserver.msde/
browse_thread/thread/d364f0edf9b0e6a0/2b1fa848ad052037?
q=limitations&rnum=4#2b1fa848ad052037
regards
Dil
hi,
Dil wrote:
> Hi
> The following topic deals with editing SQLBOOT.DLL by using a
> hexadecimal editor
> so we can disable workload Governor. Is it true?
> Pls comment
> http://groups.google.com/group/micro...qlserver.msde/
> browse_thread/thread/d364f0edf9b0e6a0/2b1fa848ad052037?
> q=limitations&rnum=4#2b1fa848ad052037
as you can immagine, this is not only not supported at all, but would break
your restricted EULA as you are personally patching/hacking a known software
limitation (not a bug) of a product to perform other then inteded...
personally, I'd not...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thnx once again Andrea,
bye
Dil