Can Users choose Rows & columns for a matrix?

Hi,
Is it possible to create a report which allows users to select the fields
for the matrix in a report. To explain in detail, can we allow the users to
select the X and Y axis for a matrix in a report?
For ex., there is a report containing a matrix which shows the total sales(
data cell) by month (X axis) and by Company( Y axis). Can we have some option
so that if the users select Year as the X axis and SalesPerson as the Y axis
then they can view the same report but by SalesPerson and Year instead od
Month and Company?
Any help is highly appreciated.
Thanks
--
pmudYes, this is possible by using dynamic field references for the grouping
expression and the matrix group header textbox expression. E.g.
=Fields(Parameters!RowGroup.Value).Value
Note: the actual field name in the expression above will be determined by
the parameter's value at runtime. You can use this for the row and the
column grouping of the matrix.
A RS 2005 sample report (which also includes InteractiveSort on the dynamic
matrix row groups) is attached. Note: you cannot load this sample in the RS
2000 report designer.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:B91AE70B-5CEF-402E-B9A5-30BD193CBCE0@.microsoft.com...
> Hi,
> Is it possible to create a report which allows users to select the fields
> for the matrix in a report. To explain in detail, can we allow the users
> to
> select the X and Y axis for a matrix in a report?
> For ex., there is a report containing a matrix which shows the total
> sales(
> data cell) by month (X axis) and by Company( Y axis). Can we have some
> option
> so that if the users select Year as the X axis and SalesPerson as the Y
> axis
> then they can view the same report but by SalesPerson and Year instead od
> Month and Company?
> Any help is highly appreciated.
> Thanks
> --
> pmud
=====================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="northwind">
<DataSourceReference>northwind</DataSourceReference>
<rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="RowGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ProductName</Value>
</Values>
</DefaultValue>
<Prompt>RowGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ProductName</Value>
<Label>By Product Name</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
<ParameterValue>
<Value>CategoryID</Value>
<Label>By Category ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="ColumnGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ReorderLevel</Value>
</Values>
</DefaultValue>
<Prompt>ColumnGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ReorderLevel</Value>
<Label>By Reorder Level</Label>
</ParameterValue>
<ParameterValue>
<Value>UnitsInStock</Value>
<Label>By Stock</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox3">
<Left>0.125in</Left>
<Top>0.375in</Top>
<ZIndex>2</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix columns " & Parameters!ColumnGroup.Label</Value>
</Textbox>
<Textbox Name="textbox1">
<Left>0.125in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix rows " & Parameters!RowGroup.Label</Value>
</Textbox>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>2.125in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!RowGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ColumnGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.875in</Top>
<Width>3.125in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
<SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
</UserSort>
<Value>Sort rows</Value>
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products</CommandText>
<DataSourceName>northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>3.375in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||Hi Robert,
I dont have RS 2005. So the way you told ( by having
=Fields(Parameters!RowGroup.Value)) will work for RS 2000 too?
Thanks
--
pmud
"Robert Bruckner [MSFT]" wrote:
> Yes, this is possible by using dynamic field references for the grouping
> expression and the matrix group header textbox expression. E.g.
> =Fields(Parameters!RowGroup.Value).Value
> Note: the actual field name in the expression above will be determined by
> the parameter's value at runtime. You can use this for the row and the
> column grouping of the matrix.
> A RS 2005 sample report (which also includes InteractiveSort on the dynamic
> matrix row groups) is attached. Note: you cannot load this sample in the RS
> 2000 report designer.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:B91AE70B-5CEF-402E-B9A5-30BD193CBCE0@.microsoft.com...
> > Hi,
> >
> > Is it possible to create a report which allows users to select the fields
> > for the matrix in a report. To explain in detail, can we allow the users
> > to
> > select the X and Y axis for a matrix in a report?
> >
> > For ex., there is a report containing a matrix which shows the total
> > sales(
> > data cell) by month (X axis) and by Company( Y axis). Can we have some
> > option
> > so that if the users select Year as the X axis and SalesPerson as the Y
> > axis
> > then they can view the same report but by SalesPerson and Year instead od
> > Month and Company?
> >
> > Any help is highly appreciated.
> >
> > Thanks
> > --
> > pmud
>
> =====================================================> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="northwind">
> <DataSourceReference>northwind</DataSourceReference>
> <rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>1in</BottomMargin>
> <RightMargin>1in</RightMargin>
> <ReportParameters>
> <ReportParameter Name="RowGroup">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>ProductName</Value>
> </Values>
> </DefaultValue>
> <Prompt>RowGroup</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>ProductName</Value>
> <Label>By Product Name</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>SupplierID</Value>
> <Label>By Supplier ID</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>CategoryID</Value>
> <Label>By Category ID</Label>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> <ReportParameter Name="ColumnGroup">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>ReorderLevel</Value>
> </Values>
> </DefaultValue>
> <Prompt>ColumnGroup</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>ReorderLevel</Value>
> <Label>By Reorder Level</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>UnitsInStock</Value>
> <Label>By Stock</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>SupplierID</Value>
> <Label>By Supplier ID</Label>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>8.5in</InteractiveWidth>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <ReportItems>
> <Textbox Name="textbox3">
> <Left>0.125in</Left>
> <Top>0.375in</Top>
> <ZIndex>2</ZIndex>
> <Width>3in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>="Matrix columns " & Parameters!ColumnGroup.Label</Value>
> </Textbox>
> <Textbox Name="textbox1">
> <Left>0.125in</Left>
> <Top>0.125in</Top>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Width>3in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>="Matrix rows " & Parameters!RowGroup.Label</Value>
> </Textbox>
> <Matrix Name="matrix1">
> <MatrixColumns>
> <MatrixColumn>
> <Width>1in</Width>
> </MatrixColumn>
> </MatrixColumns>
> <Left>0.125in</Left>
> <RowGroupings>
> <RowGrouping>
> <Width>2.125in</Width>
> <DynamicRows>
> <ReportItems>
> <Textbox Name="CategoryID">
> <rd:DefaultName>CategoryID</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields(Parameters!RowGroup.Value).Value</Value>
> </Textbox>
> </ReportItems>
> <Grouping Name="matrix1_RowGroup">
> <GroupExpressions>
> <GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </DynamicRows>
> </RowGrouping>
> </RowGroupings>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <ReportItems>
> <Textbox Name="ReorderLevel">
> <rd:DefaultName>ReorderLevel</rd:DefaultName>
> <ZIndex>2</ZIndex>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>
> </Textbox>
> </ReportItems>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Grouping Name="matrix1_ColumnGroup">
> <GroupExpressions>
> <GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </DynamicColumns>
> <Height>0.25in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <DataSetName>DataSet1</DataSetName>
> <Top>0.875in</Top>
> <Width>3.125in</Width>
> <Corner>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>3</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <UserSort>
> <SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
> <SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
> </UserSort>
> <Value>Sort rows</Value>
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>0.5in</Height>
> <MatrixRows>
> <MatrixRow>
> <Height>0.25in</Height>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="ProductID">
> <rd:DefaultName>ProductID</rd:DefaultName>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Count(Fields!ProductID.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> </MatrixRow>
> </MatrixRows>
> </Matrix>
> </ReportItems>
> <Height>2in</Height>
> </Body>
> <rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="DataSet1">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>select * from products</CommandText>
> <DataSourceName>northwind</DataSourceName>
> </Query>
> <Fields>
> <Field Name="ProductID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>ProductID</DataField>
> </Field>
> <Field Name="ProductName">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>ProductName</DataField>
> </Field>
> <Field Name="SupplierID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>SupplierID</DataField>
> </Field>
> <Field Name="CategoryID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>CategoryID</DataField>
> </Field>
> <Field Name="QuantityPerUnit">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>QuantityPerUnit</DataField>
> </Field>
> <Field Name="UnitPrice">
> <rd:TypeName>System.Decimal</rd:TypeName>
> <DataField>UnitPrice</DataField>
> </Field>
> <Field Name="UnitsInStock">
> <rd:TypeName>System.Int16</rd:TypeName>
> <DataField>UnitsInStock</DataField>
> </Field>
> <Field Name="UnitsOnOrder">
> <rd:TypeName>System.Int16</rd:TypeName>|||Robert -
I have been trying something very similar to this except that it takes
it one step further. My matrix has multiple RowGroups and multiple
ColumnGroups. I'd like the user to be able to turn some off, but I
cannot keep the RowGroup fields from always appearing in the matrix.
I use expressions to turn the RowGrouping on/off. When the RowGroup is
not to appear, the expression evaluates to "".
I also use expressions to set the RowGroup visibility to false.
Is there any way to do this? Maybe I am doing something wrong?
Thanks.

Can SQL server encrypt a single column within table?

Hello:
We have a SecUsers table that contains "Password" column, can SQL server
encrypt that column within that table? Please explain. If not, do we have
to buy a software that will encrypt single column in DB?
Thank you for your help.
Chai W.Is it possible for you not to store the password in the first place?
Try storing a hash (MD5 or SHA1 etc) of the password instead.
It has the benefit also, that the password is never transmitted over
the network (it must be hashed by the client application).
"Chai" <chai@.trs.state.il.us> wrote in message
news:OzJJtR7RDHA.3880@.tk2msftngp13.phx.gbl...
> Hello:
> We have a SecUsers table that contains "Password" column, can SQL server
> encrypt that column within that table? Please explain. If not, do we have
> to buy a software that will encrypt single column in DB?
> Thank you for your help.
> Chai W.
>

Can someone, MS?, please explain the Reporting Server security?

I just installed the evaluation version and am comparing it to Crystal
Reports. I can't seem to figure out the user level security.
My set-up:
Web server Win2003: Has IIS6 and Report server
DB SQL Server Win 2003: has Sql Server 2k
These boxes are not on a domain, they are test machines.
I open the report server up and get the home page, I can add folder, files,
etc but I no user level security. I went into site settings and all I can
find is roles, but no logins, etc.' I can go to the site and do everything
with no login?
Help.you are correct. Where you've gone awry is the difference between
authentication and authorization.
authentication is the process of determining who you are, most commonly done
via a username and password.
authorization is the process of determining what you can do now that we know
who you are.
In the Reporting Services world, RS relies on external authentication. In
other words, somebody other than RS has to authenticate the user. In most
cases that will be windows. So all user accounts are created in windows.
Users are managed there as per normal.
In the security settings you then create roles. Those roles have specific
permissions on report items etc. You then assign windows users and groups
these roles.
So if we take Joe User... His user account is maintained in windows (userID,
Password etc) either at the domain or the server level. Joe's account is
then assigned to roles in RS which determine what he can do with RS.
Make sense?
Regards,
Rob Labbé, MCP, MCAD, MCSD, MCT
Lead Architect/Trainer
Fidelis
Blog: http://spaces.msn.com/members/roblabbe
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:B6C6072D-63BC-422F-9DF9-A4400B300EF8@.microsoft.com...
>I just installed the evaluation version and am comparing it to Crystal
> Reports. I can't seem to figure out the user level security.
> My set-up:
> Web server Win2003: Has IIS6 and Report server
> DB SQL Server Win 2003: has Sql Server 2k
> These boxes are not on a domain, they are test machines.
> I open the report server up and get the home page, I can add folder,
> files,
> etc but I no user level security. I went into site settings and all I can
> find is roles, but no logins, etc.' I can go to the site and do
> everything
> with no login?
> Help.|||then why is there several tables that house roles, users, and their assigned
roles then is Sql Server?
Also, Crystal take sit to the lowest level via a "Universe" to a record
level. Does MS Report Serv. not do this either?
Are you saying that I have to add everyone to the windows box? That doesn't
make sense for web reporting, the IUSR account can access everything?
thanx.
"Rob Labbe (Lowney)" wrote:
> you are correct. Where you've gone awry is the difference between
> authentication and authorization.
> authentication is the process of determining who you are, most commonly done
> via a username and password.
> authorization is the process of determining what you can do now that we know
> who you are.
> In the Reporting Services world, RS relies on external authentication. In
> other words, somebody other than RS has to authenticate the user. In most
> cases that will be windows. So all user accounts are created in windows.
> Users are managed there as per normal.
> In the security settings you then create roles. Those roles have specific
> permissions on report items etc. You then assign windows users and groups
> these roles.
> So if we take Joe User... His user account is maintained in windows (userID,
> Password etc) either at the domain or the server level. Joe's account is
> then assigned to roles in RS which determine what he can do with RS.
> Make sense?
> Regards,
>
> --
> Rob Labbé, MCP, MCAD, MCSD, MCT
> Lead Architect/Trainer
> Fidelis
> Blog: http://spaces.msn.com/members/roblabbe
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:B6C6072D-63BC-422F-9DF9-A4400B300EF8@.microsoft.com...
> >I just installed the evaluation version and am comparing it to Crystal
> > Reports. I can't seem to figure out the user level security.
> > My set-up:
> > Web server Win2003: Has IIS6 and Report server
> > DB SQL Server Win 2003: has Sql Server 2k
> >
> > These boxes are not on a domain, they are test machines.
> >
> > I open the report server up and get the home page, I can add folder,
> > files,
> > etc but I no user level security. I went into site settings and all I can
> > find is roles, but no logins, etc.' I can go to the site and do
> > everything
> > with no login?
> >
> > Help.
>
>|||As you've discovered, the security model is vastly different in RS as
compared to Crystal. You're going to drive yourself nuts if you try to do a
one-to-one mapping of security features between the two.
You're best bet is to look at the end result you're after, not "how" it was
implemented in Crystal. Then look at RS security and set it up to get the
same end result. If you describe your scenario, and what the result you're
after is, we may be able to help you out here.
Regards,
Rob Labbé, MCP, MCAD, MCSD, MCT
Lead Architect/Trainer
Fidelis
Blog: http://spaces.msn.com/members/roblabbe
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:65196924-819E-4F4E-AD10-78D60F4A729C@.microsoft.com...
> then why is there several tables that house roles, users, and their
> assigned
> roles then is Sql Server?
> Also, Crystal take sit to the lowest level via a "Universe" to a record
> level. Does MS Report Serv. not do this either?
> Are you saying that I have to add everyone to the windows box? That
> doesn't
> make sense for web reporting, the IUSR account can access everything?
> thanx.
> "Rob Labbe (Lowney)" wrote:
>> you are correct. Where you've gone awry is the difference between
>> authentication and authorization.
>> authentication is the process of determining who you are, most commonly
>> done
>> via a username and password.
>> authorization is the process of determining what you can do now that we
>> know
>> who you are.
>> In the Reporting Services world, RS relies on external authentication.
>> In
>> other words, somebody other than RS has to authenticate the user. In
>> most
>> cases that will be windows. So all user accounts are created in windows.
>> Users are managed there as per normal.
>> In the security settings you then create roles. Those roles have
>> specific
>> permissions on report items etc. You then assign windows users and
>> groups
>> these roles.
>> So if we take Joe User... His user account is maintained in windows
>> (userID,
>> Password etc) either at the domain or the server level. Joe's account is
>> then assigned to roles in RS which determine what he can do with RS.
>> Make sense?
>> Regards,
>>
>> --
>> Rob Labbé, MCP, MCAD, MCSD, MCT
>> Lead Architect/Trainer
>> Fidelis
>> Blog: http://spaces.msn.com/members/roblabbe
>> "Chris" <Chris@.discussions.microsoft.com> wrote in message
>> news:B6C6072D-63BC-422F-9DF9-A4400B300EF8@.microsoft.com...
>> >I just installed the evaluation version and am comparing it to Crystal
>> > Reports. I can't seem to figure out the user level security.
>> > My set-up:
>> > Web server Win2003: Has IIS6 and Report server
>> > DB SQL Server Win 2003: has Sql Server 2k
>> >
>> > These boxes are not on a domain, they are test machines.
>> >
>> > I open the report server up and get the home page, I can add folder,
>> > files,
>> > etc but I no user level security. I went into site settings and all I
>> > can
>> > find is roles, but no logins, etc.' I can go to the site and do
>> > everything
>> > with no login?
>> >
>> > Help.
>>|||if there is no central login then how do you restrict it as a web app in the
manager? The roles don't appear to be machine or AD roles?
Are we saying that all users get "everything".
I'm still not seeing this. I thought maybe the eval version doesn't have
login dilaogs or something?
thanx.
"Rob Labbe (Lowney)" wrote:
> As you've discovered, the security model is vastly different in RS as
> compared to Crystal. You're going to drive yourself nuts if you try to do a
> one-to-one mapping of security features between the two.
> You're best bet is to look at the end result you're after, not "how" it was
> implemented in Crystal. Then look at RS security and set it up to get the
> same end result. If you describe your scenario, and what the result you're
> after is, we may be able to help you out here.
> Regards,
>
> --
> Rob Labbé, MCP, MCAD, MCSD, MCT
> Lead Architect/Trainer
> Fidelis
> Blog: http://spaces.msn.com/members/roblabbe
> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> news:65196924-819E-4F4E-AD10-78D60F4A729C@.microsoft.com...
> > then why is there several tables that house roles, users, and their
> > assigned
> > roles then is Sql Server?
> > Also, Crystal take sit to the lowest level via a "Universe" to a record
> > level. Does MS Report Serv. not do this either?
> >
> > Are you saying that I have to add everyone to the windows box? That
> > doesn't
> > make sense for web reporting, the IUSR account can access everything?
> >
> > thanx.
> >
> > "Rob Labbe (Lowney)" wrote:
> >
> >> you are correct. Where you've gone awry is the difference between
> >> authentication and authorization.
> >>
> >> authentication is the process of determining who you are, most commonly
> >> done
> >> via a username and password.
> >>
> >> authorization is the process of determining what you can do now that we
> >> know
> >> who you are.
> >>
> >> In the Reporting Services world, RS relies on external authentication.
> >> In
> >> other words, somebody other than RS has to authenticate the user. In
> >> most
> >> cases that will be windows. So all user accounts are created in windows.
> >> Users are managed there as per normal.
> >>
> >> In the security settings you then create roles. Those roles have
> >> specific
> >> permissions on report items etc. You then assign windows users and
> >> groups
> >> these roles.
> >>
> >> So if we take Joe User... His user account is maintained in windows
> >> (userID,
> >> Password etc) either at the domain or the server level. Joe's account is
> >> then assigned to roles in RS which determine what he can do with RS.
> >>
> >> Make sense?
> >>
> >> Regards,
> >>
> >>
> >> --
> >> Rob Labbé, MCP, MCAD, MCSD, MCT
> >> Lead Architect/Trainer
> >> Fidelis
> >>
> >> Blog: http://spaces.msn.com/members/roblabbe
> >>
> >> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> >> news:B6C6072D-63BC-422F-9DF9-A4400B300EF8@.microsoft.com...
> >> >I just installed the evaluation version and am comparing it to Crystal
> >> > Reports. I can't seem to figure out the user level security.
> >> > My set-up:
> >> > Web server Win2003: Has IIS6 and Report server
> >> > DB SQL Server Win 2003: has Sql Server 2k
> >> >
> >> > These boxes are not on a domain, they are test machines.
> >> >
> >> > I open the report server up and get the home page, I can add folder,
> >> > files,
> >> > etc but I no user level security. I went into site settings and all I
> >> > can
> >> > find is roles, but no logins, etc.' I can go to the site and do
> >> > everything
> >> > with no login?
> >> >
> >> > Help.
> >>
> >>
> >>
>
>|||Role and groups are two different things. I do the following. I create a
local group called Reports. For that group I add either individual users or
I add domain groups. Then in the home page of Report Manager, Properties,
New Role Assignment. For the user/group I put in Reports (the local group I
created previously) and I give it Browse rights.
RS is totally integrated with Windows security. If you are not going to be
using that (for instance over the internet) then you need to create your own
authentication that you integrate in with Reporting Service. Search the
books on line on authentication. Lots of good info. Just remember,
authentication and roles are two different thing. Once someone is
authenticated to be in a particular group (or authenticated to be a
particular user) then RS allows them rights based on the role assignment for
that group/user.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:7DABBC90-778B-4991-8177-1E7495A87788@.microsoft.com...
> if there is no central login then how do you restrict it as a web app in
the
> manager? The roles don't appear to be machine or AD roles?
> Are we saying that all users get "everything".
> I'm still not seeing this. I thought maybe the eval version doesn't have
> login dilaogs or something?
> thanx.
> "Rob Labbe (Lowney)" wrote:
> > As you've discovered, the security model is vastly different in RS as
> > compared to Crystal. You're going to drive yourself nuts if you try to
do a
> > one-to-one mapping of security features between the two.
> >
> > You're best bet is to look at the end result you're after, not "how" it
was
> > implemented in Crystal. Then look at RS security and set it up to get
the
> > same end result. If you describe your scenario, and what the result
you're
> > after is, we may be able to help you out here.
> >
> > Regards,
> >
> >
> > --
> > Rob Labbé, MCP, MCAD, MCSD, MCT
> > Lead Architect/Trainer
> > Fidelis
> >
> > Blog: http://spaces.msn.com/members/roblabbe
> >
> > "Chris" <Chris@.discussions.microsoft.com> wrote in message
> > news:65196924-819E-4F4E-AD10-78D60F4A729C@.microsoft.com...
> > > then why is there several tables that house roles, users, and their
> > > assigned
> > > roles then is Sql Server?
> > > Also, Crystal take sit to the lowest level via a "Universe" to a
record
> > > level. Does MS Report Serv. not do this either?
> > >
> > > Are you saying that I have to add everyone to the windows box? That
> > > doesn't
> > > make sense for web reporting, the IUSR account can access everything?
> > >
> > > thanx.
> > >
> > > "Rob Labbe (Lowney)" wrote:
> > >
> > >> you are correct. Where you've gone awry is the difference between
> > >> authentication and authorization.
> > >>
> > >> authentication is the process of determining who you are, most
commonly
> > >> done
> > >> via a username and password.
> > >>
> > >> authorization is the process of determining what you can do now that
we
> > >> know
> > >> who you are.
> > >>
> > >> In the Reporting Services world, RS relies on external
authentication.
> > >> In
> > >> other words, somebody other than RS has to authenticate the user.
In
> > >> most
> > >> cases that will be windows. So all user accounts are created in
windows.
> > >> Users are managed there as per normal.
> > >>
> > >> In the security settings you then create roles. Those roles have
> > >> specific
> > >> permissions on report items etc. You then assign windows users and
> > >> groups
> > >> these roles.
> > >>
> > >> So if we take Joe User... His user account is maintained in windows
> > >> (userID,
> > >> Password etc) either at the domain or the server level. Joe's
account is
> > >> then assigned to roles in RS which determine what he can do with RS.
> > >>
> > >> Make sense?
> > >>
> > >> Regards,
> > >>
> > >>
> > >> --
> > >> Rob Labbé, MCP, MCAD, MCSD, MCT
> > >> Lead Architect/Trainer
> > >> Fidelis
> > >>
> > >> Blog: http://spaces.msn.com/members/roblabbe
> > >>
> > >> "Chris" <Chris@.discussions.microsoft.com> wrote in message
> > >> news:B6C6072D-63BC-422F-9DF9-A4400B300EF8@.microsoft.com...
> > >> >I just installed the evaluation version and am comparing it to
Crystal
> > >> > Reports. I can't seem to figure out the user level security.
> > >> > My set-up:
> > >> > Web server Win2003: Has IIS6 and Report server
> > >> > DB SQL Server Win 2003: has Sql Server 2k
> > >> >
> > >> > These boxes are not on a domain, they are test machines.
> > >> >
> > >> > I open the report server up and get the home page, I can add
folder,
> > >> > files,
> > >> > etc but I no user level security. I went into site settings and all
I
> > >> > can
> > >> > find is roles, but no logins, etc.' I can go to the site and do
> > >> > everything
> > >> > with no login?
> > >> >
> > >> > Help.
> > >>
> > >>
> > >>
> >
> >
> >|||There is a Reporting Services 2005 book screaming to be written, here -
I know I'd buy multiple copies for work if someone could clearly
present this topic.
As is, it would make Oppenheimer stagger across the room for a
Tylenol...

Can someone please explain this to me...

why do the following return the same datasets?

select * from myTable where myData = ''

select * from myTable where myData = ' '

in the first I'm specifically searching empty strings, in the second a sequence of five spaces. Yet both return any and all white character matches? Is this a "feature" of SQL...

P.S. I'm using T-SQL

This should work. Check it out

select * from myTable where myData = '';

select * from myTable where myData = SPACE(5);

|||

Yes, trailing spaces are not significant (nor stored) in varchar fields.

Can someone help explain?

Hi,

I am working with several tables, but for now I just mention 4 : one is fact table (named Usage), and 3 dimensional tables Periods, Products, and Regions. The fact table contains references to the dimensional tables. Table Periods contain two other columns month and year.

I created a cube containing columns from those 4 tables. Deployment was successful. Trouble comes when I want to create a mining structure using Time Series containing these columns :

- Period
- Amount (of money)
- Product name
- Region name

When I choose to use cube (instead of table) as source for mining structure, I'm forced to choose only one dimension (among the Periods, Products, and Regions). Whatever dimension I choose I end up being unable to use the column period as the Time-Key column. Effectively I cannot use Time Series method since I cannot use the column period.

(1) Why is this so [why Visual Studio forced us to use only one dimension from the cube] ?
(2) Why Visual Studio eliminates the column period, column that has relationship with the
time dimension?
(3) What is the use of Cube anyway to the mining? Is there still any use for it?
(4) What is the solution to that kind of problem I face?

Thank you,

Bernaridho
Dear all,

I have not received any answer why can I use only one dimension when using a cube to mine data. Can someone help explain please? The details are ini my previous posting.

Thank you,

Bernaridho
|||

#1. The current design on OLAP-based mining structures requires that case-level columns be member properties of the key - this is a strict way of making sure that the attributes refer to the same entity (the case) and the requirement is met if all the case columns come from the same dimension.

#2. The data mining engine does not have time intelligence in terms of recognizing the special status of the time dimension so unless there is explicit relationship through inclusion of the case dimension and time dimension in a measuregroup, the dimension cannot be included (as a nested table).

#3. Building mining models on top of cubes is useful when you already have the data in that form (and you don't want to or don't have access to the source data) and also for applying the results of data mining back to the cube to slice/dice the fact data with the hierarchies discovered by the model. For instance, you could build a cluster model and slice your facts by the clusters.

#4. In your case, it might be better to build your time series model directly on the source (relational) data since you will have much more flexibility in that case.

|||

You can also use DMX to create the models. DMX allows the form

INSERT INTO <mining model>(<columns>)

<MDX statement>

In this form it uses the flattening semantics of MDX to populate the mining model. This way you can create any arbitrary dataset as input into the mining model.

|||Are there any more details to this question?

Can someone explain why this stored proc does not work?

Sorry if this angers anyone. I'm posting here and to the .NET group. I
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
@.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).Value
How about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegr oups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
|||Hi
"jbonifacejr" wrote:

> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John

Can someone explain why this stored proc does not work?

Sorry if this angers anyone. I'm posting here and to the .NET group. I
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName = @.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).ValueHow about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
--
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegroups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName => @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>|||Hi
"jbonifacejr" wrote:
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName => @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John

Can someone explain why this stored proc does not work?

Sorry if this angers anyone. I'm posting here and to the .NET group. I
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
@.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).ValueHow about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegroups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>|||Hi
"jbonifacejr" wrote:

> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John

Can someone explain why this is not an error?

I accidentally did something similar to this, and I can't figure out why it
doesn't give an error.
Use Pubs
Select * From Authors
Where Au_LName In (Select Au_LName From Publishers)
Of course, "Select Au_LName From Publishers" by itself gives an error
message since Publishers doesn't have a field called AULName.
What's up? An error message would have helped here.
For a slightly different case, if the two tables have a field name in
common, and you use thet field in the inner Select, but don't qualify the
field name in the inner Select, what happens?
Thanks.
David WalkerDavid,
See the following thread:
http://groups.google.com/group/micr...6c9a254304c7629
HTH
Jerry
"DWalker" <none@.none.com> wrote in message
news:%239LoUZSxFHA.3720@.TK2MSFTNGP11.phx.gbl...
>I accidentally did something similar to this, and I can't figure out why it
> doesn't give an error.
> Use Pubs
> Select * From Authors
> Where Au_LName In (Select Au_LName From Publishers)
> Of course, "Select Au_LName From Publishers" by itself gives an error
> message since Publishers doesn't have a field called AULName.
> What's up? An error message would have helped here.
> For a slightly different case, if the two tables have a field name in
> common, and you use thet field in the inner Select, but don't qualify the
> field name in the inner Select, what happens?
> Thanks.
> David Walker|||I think I know what happened here (someone more knowledgeable can correct me
if I am wrong)...
Since there is no Au_LName column in the Publishers table, SQL Server goes
looking for any other place in your statement that it can find a table with
a column named Au_LName. Since the Authors column is 'in scope' within the
subquery, the statement you posted is functionally equivalent to this:
Select * From Authors
Where Au_LName In (Select Authors.Au_LName From Publishers)
This in turn is functionally equivalent to:
--note the cartesian product in the subquery
Select * From Authors
Where Au_LName In (Select Authors.Au_LName From Authors, Publishers)
If the Authors table was not 'in scope' in the subquery, then you could not
write correlated subqueries, since the outer table(s) would not be
accessible from inside the subquery. So everything worked as it should, but
it ends up looking really strange if you are not expecting this to happen.
"DWalker" <none@.none.com> wrote in message
news:%239LoUZSxFHA.3720@.TK2MSFTNGP11.phx.gbl...
>I accidentally did something similar to this, and I can't figure out why it
> doesn't give an error.
> Use Pubs
> Select * From Authors
> Where Au_LName In (Select Au_LName From Publishers)
> Of course, "Select Au_LName From Publishers" by itself gives an error
> message since Publishers doesn't have a field called AULName.
> What's up? An error message would have helped here.
> For a slightly different case, if the two tables have a field name in
> common, and you use thet field in the inner Select, but don't qualify the
> field name in the inner Select, what happens?
> Thanks.
> David Walker|||Correction: that should be "Authors table", not "Authors column".
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:uL64ZQTxFHA.2924@.TK2MSFTNGP15.phx.gbl...
>I think I know what happened here (someone more knowledgeable can correct
>me if I am wrong)...
> Since there is no Au_LName column in the Publishers table, SQL Server goes
> looking for any other place in your statement that it can find a table
> with a column named Au_LName. Since the Authors column is 'in scope'
> within the subquery, the statement you posted is functionally equivalent
> to this:
> Select * From Authors
> Where Au_LName In (Select Authors.Au_LName From Publishers)
> This in turn is functionally equivalent to:
> --note the cartesian product in the subquery
> Select * From Authors
> Where Au_LName In (Select Authors.Au_LName From Authors, Publishers)
> If the Authors table was not 'in scope' in the subquery, then you could
> not write correlated subqueries, since the outer table(s) would not be
> accessible from inside the subquery. So everything worked as it should,
> but it ends up looking really strange if you are not expecting this to
> happen.
> "DWalker" <none@.none.com> wrote in message
> news:%239LoUZSxFHA.3720@.TK2MSFTNGP11.phx.gbl...
>|||OK, I double checked the execution plans, and I can plainly see that my
second statement is *not* equivalent - just ignore that. However, I appear
to have gotten the first part right, so I am batting .500!
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:%23$%233fVTxFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Correction: that should be "Authors table", not "Authors column".
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:uL64ZQTxFHA.2924@.TK2MSFTNGP15.phx.gbl...
>|||Jeremy,
Batting .500...nice...you're leading both the National and the America
leagues. Would you mind batting for the Red Sox this wend against the
Yankees? ;-)
Your theory makes sense. What really looks weird is when you replace the
Publisher's table with one of the system tables.
HTH
Jerry
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:OSkXksTxFHA.2212@.TK2MSFTNGP15.phx.gbl...
> OK, I double checked the execution plans, and I can plainly see that my
> second statement is *not* equivalent - just ignore that. However, I appear
> to have gotten the first part right, so I am batting .500!
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:%23$%233fVTxFHA.3556@.TK2MSFTNGP12.phx.gbl...
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in
news:e1hEnxTxFHA.3900@.TK2MSFTNGP10.phx.gbl:

> Jeremy,
> Batting .500...nice...you're leading both the National and the America
> leagues. Would you mind batting for the Red Sox this wend against
> the Yankees? ;-)
> Your theory makes sense. What really looks weird is when you replace
> the Publisher's table with one of the system tables.
> HTH
> Jerry
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:OSkXksTxFHA.2212@.TK2MSFTNGP15.phx.gbl...
>
Baseball aside (go Braves!), yes it perhaps looks like a correlated
subquery, but it's really strange if you're not expecting it. But
correlated subqueries at least require you to say which field(s) you're
joining the two tables on.
In that older thread (which I started although it was nine months ago
and I made the same mistake again), Andrew Kelly said "If the column
name in the subselect is not part of that table it looks to the parent
select and if it finds it there it thinks all is well". But if it's
joining the inner select table with the table in the parent select,
you'd have to tell it which field to join on, I would think, unless it
assumes that any field that happened to be named the same is the join
field (or it's a Cartesian product). If you're not joining, then you're
just ignoring the table in the inner select?
While this might be per ANSI standards, I don't see it mentioned in BOL
anywhere.
This example from that thread:
SELECT *
FROM TblA
WHERE TblA.Col =
(SELECT TblA.ColB - TblB.ColC
FROM TblB)
is weird. How can you do the subtraction without knowing an ID column?
Or is it also a Cartesian product?
David|||On Fri, 30 Sep 2005 07:25:23 -0700, DWalker wrote:

>Baseball aside (go Braves!), yes it perhaps looks like a correlated
>subquery, but it's really strange if you're not expecting it. But
>correlated subqueries at least require you to say which field(s) you're
>joining the two tables on.
Hi David,
They usually do, but it's not a syntactical requirement. To understand
how SQL Server finds which column to use if you don't explicitly qualify
it, check out the following example:
SELECT ...
FROM Tab1
INNER JOIN Tab2
ON ...
WHERE ... = (SELECT ...
FROM Tab3
INNER JOIN Tab4
ON ...
WHERE ...)
Let's say that an unqualified column (TheCol) is found in the subquery.
These steps are taken:
1. Look in both Tab3 and Tab4 for a column with the name TheCol.
2. If a column with that name is found in both tables: raise error.
3. If a column with that name is found in one of the tables: use it.
4. If a column with that name is found in neither table, continue to
check Tab1 and Tab2 for a column named TheCol.
5. If a column with that name is found in both tables: raise error.
6. If a column with that name is found in one of the tables: use it.
7. If a column with that name is found in neither table: raise error.
On the other hand, if a reference to TheCol is found outside of the
subquery, only Tab1 and Tab2 are checked, since it's outside of the
scope where Tab3 and Tab4 are valid.
Of course, it's much better to get into the habit of qualifying all
column names whenever you use more than one table in your query!

>This example from that thread:
>SELECT *
>FROM TblA
>WHERE TblA.Col =
> (SELECT TblA.ColB - TblB.ColC
> FROM TblB)
>is weird. How can you do the subtraction without knowing an ID column?
It will only work if TblB has one or zero rows. Otherwise, you'll get an
error ("subquery returned more than one row").
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Can someone explain this strange value of @@ROWCOUNT?

I have noticed the following strange situation, but cannot find any explanation. The @.rows variable is 1 , even though the @.@.ROWCOUNT value just after the select statement is 0. (I have checked this by stepping through the SP).

update Products set ParentId = @.parent where ParentId = @.lastParentId
set @.err = @.@.ERROR
set @.rows = @.@.ROWCOUNT

However, if I set @.rows before @.err then the @.rows is correctly set to 0. (as in script below).

update Products set ParentId = @.parent where ParentId = @.lastParentId
set @.rows = @.@.ROWCOUNT
set @.err = @.@.ERROR

Can someone please explain why this is happenning?

In the first instance, @.@.ROWCOUNT is reporting on the statement immediately above it, which is the @.@.ERROR check. Please realize, however, that in your second instance, your @.@.ERROR check is now happening against your @.@.ROWCOUNT check.

To get around this issue, it's better practice to use a statement like this,making sure to reference @.@.ERROR and @.@.ROWCOUNT in this specific order:

SELECT @.err = @.@.ERROR, @.rows = @.@.ROWCOUNT

|||

Thanks for the explanation.

In your answer, why should the order of @.@.ERROR and @.@.ROWCOUNT matter, since both are now in 'one' statement, and therefore, both are reporting on what occurred in a previous statement? 'select @.err=@.@.ERROR, @.rows = @.@.ROWCOUNT' is after all a single statement.

|||

sun21170:

In your answer, why should the order of @.@.ERROR and @.@.ROWCOUNT matter, since both are now in 'one' statement, and therefore, both are reporting on what occurred in a previous statement? 'select @.err=@.@.ERROR, @.rows = @.@.ROWCOUNT' is after all a single statement.

Thank you, you are correct, the order does not matter!

Can someone explain this message?

Server: Msg 1204, Level 19, State 1, Line 30 The SQL Server cannot obtain a
LOCK resource at this time. Rerun your statement when there are fewer active
users or ask the system administrator to check the SQL Server lock and
memory configuration.
We can't find it in BOL.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conquerer.
Kill them all and you're a god." -- Dave MustaneMike,
Sounds like a lock issue or possibly a memory issue. What does the
following reveal?
exec sp_configure 'locks'
exec sp_configure 'min server memory'
exec sp_configure 'max server memory'
How much memory is available on the system? How much is SQL Server
currently using?
HTH
Jerry
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:uVtXrEgvFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Server: Msg 1204, Level 19, State 1, Line 30 The SQL Server cannot obtain
> a LOCK resource at this time. Rerun your statement when there are fewer
> active users or ask the system administrator to check the SQL Server lock
> and memory configuration.
>
> We can't find it in BOL.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conquerer.
> Kill them all and you're a god." -- Dave Mustane
>|||Maybe here?
http://support.microsoft.com/search...SE&ast=2&mode=a
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:uVtXrEgvFHA.3556@.TK2MSFTNGP12.phx.gbl...
> Server: Msg 1204, Level 19, State 1, Line 30 The SQL Server cannot obtain
> a LOCK resource at this time. Rerun your statement when there are fewer
> active users or ask the system administrator to check the SQL Server lock
> and memory configuration.
>
> We can't find it in BOL.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conquerer.
> Kill them all and you're a god." -- Dave Mustane
>|||> http://support.microsoft.com/search...SE&ast=2&mode=a
I read the article, printed it and showed it to her. Yep, that's exactly
what she's doing.
Loop over SampleSourceArchive table, processing child records. We're
currently in an email-war about "WHY NOT JUST DO A JOIN YOU SILLY USER!!"
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conquerer.
Kill them all and you're a god." -- Dave Mustane|||> Sounds like a lock issue or possibly a memory issue. What does the
> following reveal?
> exec sp_configure 'locks'
> exec sp_configure 'min server memory'
> exec sp_configure 'max server memory'
> How much memory is available on the system? How much is SQL Server
> currently using?
Min Locks = 5000
Max Locks = 2 Billion (and some change)
Min Server Memory = 0 -> 2GB
Max Server Memory = 4MB -> 2GB
The server has 3 GB of hard RAM (Can't tell the swap file size from here)
but it looks like SQL Server is set to use anywhere between 0 and 2GB of
memory, if I have interpreted this correctly.
I think she is just applying her "expert" MS Access skills to SQL Server.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane

Can someone explain this behaviour?

Hello All,
The following script is reproducing the problem assuming you have
Northwind database on the server.
Please note it gives you the error message on line 12.
USE tempdb
GO
sp_addlinkedserver 'Test17'
GO
sp_setnetname 'Test17', @.@.SERVERNAME
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_works]
GO
CREATE PROCEDURE This_works
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
go
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_does_not]
GO
CREATE PROCEDURE This_does_not
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
GO
PRINT 'This_works'
EXECUTE This_works 0
PRINT ' '
PRINT 'This_does_not'
EXECUTE This_does_not 0
Thanks for any help or hint,
Igor Raytsin
Igor Raytsin (igorray@.sympatico.ca) writes:
> The following script is reproducing the problem assuming you have
> Northwind database on the server.
> Please note it gives you the error message on line 12.
I think I understand what's going on. Since you drop and recreate the
table, the next reference to the table after its recreation will cause
a recompilation of the procedure. If that SELECT is in a transaction, you
have a problem, because SQL Server then wants to talk with the linked server
to verify the table. (Deferred name resolution does not apply to linked
tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Igor
In addition
Inside the transaction spesify name of the database and it will work
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
COMMIT
Perhaps SQL Server verified the new table (SELECT * INTO) by SELECT which
is was remarted in the second example
(Deferred name resolution does not apply to linked
> tables.)
Erlan, I think it has nothing to do with a linked servers it does a creation
on the local server and not a linked one.
Or if I did not understand you , can you please elaborate the explanation?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96295474102Yazorman@.127.0.0.1...
> Igor Raytsin (igorray@.sympatico.ca) writes:
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked
server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||Uri Dimant (urid@.iscar.co.il) writes:
> Erlan, I think it has nothing to do with a linked servers it does a
> creation on the local server and not a linked one. Or if I did not
> understand you , can you please elaborate the explanation?
It's correct that the actual execution path does not touch the linked
server. However, the procedure is recompiled as a whole, and the procedure
includes a reference to linked table. And when the recompilation occurs
in a transaction, that transaction becomes a distributed transaction,
but this is not handled well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||Erland
Ok, I got it, but how do you explain that by adding a name of the database
within a tranasction (which is becamed distributed ) it began to work?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96298185A423CYazorman@.127.0.0.1...
> Uri Dimant (urid@.iscar.co.il) writes:
> It's correct that the actual execution path does not touch the linked
> server. However, the procedure is recompiled as a whole, and the procedure
> includes a reference to linked table. And when the recompilation occurs
> in a transaction, that transaction becomes a distributed transaction,
> but this is not handled well.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp
|||Uri Dimant (urid@.iscar.co.il) writes:
> Ok, I got it, but how do you explain that by adding a name of the
> database within a tranasction (which is becamed distributed ) it began
> to work?
I got the same error message when I made your replacement. I suspect
that you had inadvertently created an Orders_TMP in the Northwind
database. But Igor's script runs from tempdb. Under this scenario
there is no need for recompilation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Can someone explain this behaviour?

Hello All,
The following script is reproducing the problem assuming you have
Northwind database on the server.
Please note it gives you the error message on line 12.
USE tempdb
GO
sp_addlinkedserver 'Test17'
GO
sp_setnetname 'Test17', @.@.SERVERNAME
GO
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_works]
GO
CREATE PROCEDURE This_works
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
go
IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_does_not]
GO
CREATE PROCEDURE This_does_not
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON
IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id = object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP
SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders
-- SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
GO
PRINT 'This_works'
EXECUTE This_works 0
PRINT ' '
PRINT 'This_does_not'
EXECUTE This_does_not 0
Thanks for any help or hint,
Igor RaytsinIgor Raytsin (igorray@.sympatico.ca) writes:
> The following script is reproducing the problem assuming you have
> Northwind database on the server.
> Please note it gives you the error message on line 12.
I think I understand what's going on. Since you drop and recreate the
table, the next reference to the table after its recreation will cause
a recompilation of the procedure. If that SELECT is in a transaction, you
have a problem, because SQL Server then wants to talk with the linked server
to verify the table. (Deferred name resolution does not apply to linked
tables.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Igor
In addition
Inside the transaction spesify name of the database and it will work
BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
COMMIT
Perhaps SQL Server verified the new table (SELECT * INTO) by SELECT which
is was remarted in the second example
(Deferred name resolution does not apply to linked
> tables.)
Erlan, I think it has nothing to do with a linked servers it does a creation
on the local server and not a linked one.
Or if I did not understand you , can you please elaborate the explanation?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96295474102Yazorman@.127.0.0.1...
> Igor Raytsin (igorray@.sympatico.ca) writes:
> > The following script is reproducing the problem assuming you have
> > Northwind database on the server.
> > Please note it gives you the error message on line 12.
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked
server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Uri Dimant (urid@.iscar.co.il) writes:
> Erlan, I think it has nothing to do with a linked servers it does a
> creation on the local server and not a linked one. Or if I did not
> understand you , can you please elaborate the explanation?
It's correct that the actual execution path does not touch the linked
server. However, the procedure is recompiled as a whole, and the procedure
includes a reference to linked table. And when the recompilation occurs
in a transaction, that transaction becomes a distributed transaction,
but this is not handled well.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Erland
Ok, I got it, but how do you explain that by adding a name of the database
within a tranasction (which is becamed distributed ) it began to work?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96298185A423CYazorman@.127.0.0.1...
> Uri Dimant (urid@.iscar.co.il) writes:
> > Erlan, I think it has nothing to do with a linked servers it does a
> > creation on the local server and not a linked one. Or if I did not
> > understand you , can you please elaborate the explanation?
> It's correct that the actual execution path does not touch the linked
> server. However, the procedure is recompiled as a whole, and the procedure
> includes a reference to linked table. And when the recompilation occurs
> in a transaction, that transaction becomes a distributed transaction,
> but this is not handled well.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Uri Dimant (urid@.iscar.co.il) writes:
> Ok, I got it, but how do you explain that by adding a name of the
> database within a tranasction (which is becamed distributed ) it began
> to work?
I got the same error message when I made your replacement. I suspect
that you had inadvertently created an Orders_TMP in the Northwind
database. But Igor's script runs from tempdb. Under this scenario
there is no need for recompilation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Can someone explain this behaviour?

Hello All,

The following script is reproducing the problem assuming you have
Northwind database on the server.
Please note it gives you the error message on line 12.

USE tempdb
GO
sp_addlinkedserver 'Test17'
GO
sp_setnetname 'Test17', @.@.SERVERNAME
GO

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_works]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_works]
GO
CREATE PROCEDURE This_works
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON

IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP

SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP

SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders

SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END

BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
go

IF EXISTS (SELECT 1 FROM dbo.sysobjects WHERE id =
object_id(N'[dbo].[This_does_not]') and OBJECTPROPERTY(id,
N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[This_does_not]
GO
CREATE PROCEDURE This_does_not
@.UseLinkedServer bit = 0
-- WITH RECOMPILE -- Does not help
AS
SET NOCOUNT ON

IF @.UseLinkedServer = 1 -- Linked Server
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP

SELECT * INTO dbo.Orders_TMP FROM Test17.Northwind.dbo.Orders
END
ELSE -- Local
BEGIN
IF EXISTS (SELECT 1 FROM dbo.sysobjects where id =
object_id(N'[dbo].[Orders_TMP]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
DROP TABLE dbo.Orders_TMP

SELECT * INTO dbo.Orders_TMP FROM Northwind.dbo.Orders

--SELECT 1 FROM dbo.Orders_TMP WHERE 1 = 2 -- Why do I need this line?
END

BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM dbo.Orders_TMP
COMMIT
GO

PRINT 'This_works'
EXECUTE This_works 0

PRINT ' '
PRINT 'This_does_not'
EXECUTE This_does_not 0

Thanks for any help or hint,
Igor RaytsinIgor Raytsin (igorray@.sympatico.ca) writes:
> The following script is reproducing the problem assuming you have
> Northwind database on the server.
> Please note it gives you the error message on line 12.

I think I understand what's going on. Since you drop and recreate the
table, the next reference to the table after its recreation will cause
a recompilation of the procedure. If that SELECT is in a transaction, you
have a problem, because SQL Server then wants to talk with the linked server
to verify the table. (Deferred name resolution does not apply to linked
tables.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog wrote:
> Igor Raytsin (igorray@.sympatico.ca) writes:
>>The following script is reproducing the problem assuming you have
>>Northwind database on the server.
>>Please note it gives you the error message on line 12.
>
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)

Thank you for your reply Erland,

In other words - those procedures are never got compiled without a error (or warning),
but SQL Server ignores them if it is not in a transaction. Right?

Thanks,
Igor|||Igor
In addition
Inside the transaction spesify name of the database and it will work

BEGIN TRANSACTION
Select 'Line 25'
SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
COMMIT

Perhaps SQL Server verified the new table (SELECT * INTO) by SELECT which
is was remarted in the second example

(Deferred name resolution does not apply to linked
> tables.)

Erlan, I think it has nothing to do with a linked servers it does a creation
on the local server and not a linked one.
Or if I did not understand you , can you please elaborate the explanation?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96295474102Yazorman@.127.0.0.1...
> Igor Raytsin (igorray@.sympatico.ca) writes:
> > The following script is reproducing the problem assuming you have
> > Northwind database on the server.
> > Please note it gives you the error message on line 12.
> I think I understand what's going on. Since you drop and recreate the
> table, the next reference to the table after its recreation will cause
> a recompilation of the procedure. If that SELECT is in a transaction, you
> have a problem, because SQL Server then wants to talk with the linked
server
> to verify the table. (Deferred name resolution does not apply to linked
> tables.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Uri Dimant (urid@.iscar.co.il) writes:
> Erlan, I think it has nothing to do with a linked servers it does a
> creation on the local server and not a linked one. Or if I did not
> understand you , can you please elaborate the explanation?

It's correct that the actual execution path does not touch the linked
server. However, the procedure is recompiled as a whole, and the procedure
includes a reference to linked table. And when the recompilation occurs
in a transaction, that transaction becomes a distributed transaction,
but this is not handled well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Igor Raytsin (igorray@.sympatico.ca) writes:
> In other words - those procedures are never got compiled without a error
> (or warning), but SQL Server ignores them if it is not in a transaction.
> Right?

This particular error only occurs when you are in a transaction. I can't
really say why, but apparently the fact that the single-machine transaction
suddenly makes a linked reference is not handled well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland
Ok, I got it, but how do you explain that by adding a name of the database
within a tranasction (which is becamed distributed ) it began to work?

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96298185A423CYazorman@.127.0.0.1...
> Uri Dimant (urid@.iscar.co.il) writes:
> > Erlan, I think it has nothing to do with a linked servers it does a
> > creation on the local server and not a linked one. Or if I did not
> > understand you , can you please elaborate the explanation?
> It's correct that the actual execution path does not touch the linked
> server. However, the procedure is recompiled as a whole, and the procedure
> includes a reference to linked table. And when the recompilation occurs
> in a transaction, that transaction becomes a distributed transaction,
> but this is not handled well.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Uri Dimant wrote:
Uri,

I tried to add the database name -

SELECT COUNT(*) FROM tempdb.dbo.Orders_TMP

But it did not work on my server :(

Igor

> Igor
> In addition
> Inside the transaction spesify name of the database and it will work
> BEGIN TRANSACTION
> Select 'Line 25'
> SELECT COUNT(*) FROM Northwind.dbo.Orders_TMP
> COMMIT
> Perhaps SQL Server verified the new table (SELECT * INTO) by SELECT which
> is was remarted in the second example
> (Deferred name resolution does not apply to linked
>>tables.)
>
> Erlan, I think it has nothing to do with a linked servers it does a creation
> on the local server and not a linked one.
> Or if I did not understand you , can you please elaborate the explanation?
>
>
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns96295474102Yazorman@.127.0.0.1...
>>Igor Raytsin (igorray@.sympatico.ca) writes:
>>
>>>The following script is reproducing the problem assuming you have
>>>Northwind database on the server.
>>>Please note it gives you the error message on line 12.
>>
>>I think I understand what's going on. Since you drop and recreate the
>>table, the next reference to the table after its recreation will cause
>>a recompilation of the procedure. If that SELECT is in a transaction, you
>>have a problem, because SQL Server then wants to talk with the linked
> server
>>to verify the table. (Deferred name resolution does not apply to linked
>>tables.)
>>
>>
>>
>>--
>>Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>>
>>Books Online for SQL Server SP3 at
>>http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Uri Dimant (urid@.iscar.co.il) writes:
> Ok, I got it, but how do you explain that by adding a name of the
> database within a tranasction (which is becamed distributed ) it began
> to work?

I got the same error message when I made your replacement. I suspect
that you had inadvertently created an Orders_TMP in the Northwind
database. But Igor's script runs from tempdb. Under this scenario
there is no need for recompilation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp