Showing posts with label clarification. Show all posts
Showing posts with label clarification. Show all posts

Thursday, March 22, 2012

Clarification requested for 'Estimating the Size of a Table' (Estimating the Size of a Heap)

I am currently engaged in determining the estimated size of a table given a potentially high growth rate. I am following the instructions found in the SQL2000 BOL article entitled "Estimating the Size of a Table". I need clarification regarding the instructions specified for item #4, determining the Variable_Data_Size variable. Basically, the table I am using has 25 TEXT fields in it (please don't ask, I didn't design it), and I'm not certain how these play into the Variable_Data_Size calculation. None of these columns will EVER store anywhere near the potential size for the data type. This table has the 'text in row' option enabled, and is set to store up to 256 characters for each text field 'in row'. (On a side note, I think the original designer thought doing this actually set a limit on the size of the text field to 256 chars, but I can't be certain). If it helps you to visualize, here is the table schema:

CREATE TABLE [picaweb].[temp_response] (
[ResponseID] [varchar] (30) NOT NULL ,
[Term] [varchar] (5) NOT NULL ,
[Subject] [varchar] (4) NOT NULL ,
[Course] [varchar] (4) NOT NULL ,
[Sect] [varchar] (3) NOT NULL ,
[MidEndFlag] [varchar] (3) NOT NULL ,
[SID] [varchar] (9) NOT NULL ,
[TemplateID] [varchar] (30) NOT NULL ,
[LastModified] [datetime] NULL ,
[College] [varchar] (30) NULL ,
[Classification] [varchar] (10) NULL ,
[CourseRequired] [varchar] (3) NULL ,
[ExpectedGrade] [varchar] (10) NULL ,
[Sex] [varchar] (6) NULL ,
[ItemAnswer1] [varchar] (1) NULL ,
[ItemComments1] [text] NULL ,
[ItemAnswer2] [varchar] (1) NULL ,
[ItemComments2] [text] NULL ,
[ItemAnswer3] [varchar] (1) NULL ,
[ItemComments3] [text] NULL ,
[ItemAnswer4] [varchar] (1) NULL ,
[ItemComments4] [text] NULL ,
[ItemAnswer5] [varchar] (1) NULL ,
[ItemComments5] [text] NULL ,
[ItemAnswer6] [varchar] (1) NULL ,
[ItemComments6] [text] NULL ,
[ItemAnswer7] [varchar] (1) NULL ,
[ItemComments7] [text] NULL ,
[ItemAnswer8] [varchar] (1) NULL ,
[ItemComments8] [text] NULL ,
[ItemAnswer9] [varchar] (1) NULL ,
[ItemComments9] [text] NULL ,
[ItemAnswer10] [varchar] (1) NULL ,
[ItemComments10] [text] NULL ,
[ItemAnswer11] [varchar] (1) NULL ,
[ItemComments11] [text] NULL ,
[ItemAnswer12] [varchar] (1) NULL ,
[ItemComments12] [text] NULL ,
[ItemAnswer13] [varchar] (1) NULL ,
[ItemComments13] [text] NULL ,
[ItemAnswer14] [varchar] (1) NULL ,
[ItemComments14] [text] NULL ,
[ItemAnswer15] [varchar] (1) NULL ,
[ItemComments15] [text] NULL ,
[ItemAnswer16] [varchar] (1) NULL ,
[ItemComments16] [text] NULL ,
[ItemAnswer17] [varchar] (1) NULL ,
[ItemComments17] [text] NULL ,
[ItemAnswer18] [varchar] (1) NULL ,
[ItemComments18] [text] NULL ,
[ItemAnswer19] [varchar] (1) NULL ,
[ItemComments19] [text] NULL ,
[ItemAnswer20] [varchar] (1) NULL ,
[ItemComments20] [text] NULL ,
[EssayQuestionAnswer1] [text] NULL ,
[EssayQuestionAnswer2] [text] NULL ,
[EssayQuestionAnswer3] [text] NULL ,
[EssayQuestionAnswer4] [text] NULL ,
[EssayQuestionAnswer5] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I would appreciate any insight anyone might be able to provide. My calculations were wrong somewhere, and I believe it was on the Variable_Data_Size variable determination.

Best,
B.

Have you checked in the 2K5 version of Books Online? I'm not sure if it was updated...

http://msdn2.microsoft.com/en-us/library/ms187445.aspx

|||Yes, it's pretty much the same as for 2000. Now that I've really thought about it, I suppose the size of the data to be stored "in row" should be treated like a fixed-length field, especially since it will reduce row density per page. I think that's what I'll do.

Question answered.

Best,
B.

Clarification on the ROWCOUNT statement

Hi All,
I just wanted to get some clarification on the ROWCOUNT statement in T-SQL.
I'm presently using it to limit the number of rows returned in SELECT based
SPs.
However I'm just curious as the effect of this keyword on the global scope
of SQL operations.
Does setting ROWCOUNT to 25 in one stored procedure effect other SPs running
?
Or is it only specific to the SP which set it?
Also...if it does only effect the code in the specific SP which set the
ROWCOUNT why is it recommended/required to set it back to zero when you are
done?
I have tested this and is "seems" to have no effect on other code running,
but I just want to make sure before I create considerable headaches in my
application.
Thanks for any feedback.
John RossitterJohn,
You can just use the TOP clause with an ORDER BY clause to ommit the need of
setting ROWCOUNT.
From SQL BOL:
It is recommended that DELETE, INSERT, and UPDATE statements currently using
SET ROWCOUNT be rewritten to use the TOP syntax. For more information, see
DELETE, INSERT, or UPDATE.
The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and
DELETE statements against remote tables and local and remote partitioned
views.
HTH
Jerry
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
> Hi All,
> I just wanted to get some clarification on the ROWCOUNT statement in
> T-SQL.
> I'm presently using it to limit the number of rows returned in SELECT
> based
> SPs.
> However I'm just curious as the effect of this keyword on the global scope
> of SQL operations.
> Does setting ROWCOUNT to 25 in one stored procedure effect other SPs
> running?
> Or is it only specific to the SP which set it?
> Also...if it does only effect the code in the specific SP which set the
> ROWCOUNT why is it recommended/required to set it back to zero when you
> are
> done?
> I have tested this and is "seems" to have no effect on other code running,
> but I just want to make sure before I create considerable headaches in my
> application.
> Thanks for any feedback.
> John Rossitter
>|||The ROWCOUNT setting does not bleed to other connections and it is also loca
l to inside the
procedure. I still always type a comment to reset it to 0 the very same mome
nt as I type the setting
to non-zero. Just in case someone else add some code to the procedure later.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
> Hi All,
> I just wanted to get some clarification on the ROWCOUNT statement in T-SQL
.
> I'm presently using it to limit the number of rows returned in SELECT base
d
> SPs.
> However I'm just curious as the effect of this keyword on the global scope
> of SQL operations.
> Does setting ROWCOUNT to 25 in one stored procedure effect other SPs runni
ng?
> Or is it only specific to the SP which set it?
> Also...if it does only effect the code in the specific SP which set the
> ROWCOUNT why is it recommended/required to set it back to zero when you ar
e
> done?
> I have tested this and is "seems" to have no effect on other code running,
> but I just want to make sure before I create considerable headaches in my
> application.
> Thanks for any feedback.
> John Rossitter
>|||Hi Jerry,
I can't use TOP because I need a dynamic number of rows selected.
Which is why I'm using ROWCOUNT directive instead.
To clarify what I have done, is in one of my tables set a MaxRows threshold.
Now when any of my SELECT based SPs execute the code looks something like
this:
====================================
DECLARE @.TOP int;
SET @.TOP = 1000
SELECT @.TOP = Max_Record_Count FROM Control_Table
SET ROWCOUNT @.TOP
SELECT ....
SET ROWCOUNT 0
=====================================
This way I can control the overall performance of all of my queries.
I just want to make sure that the ROWCOUNT setting does not have an impact
on any other code running in SQL.
For example lets say that the above SP was running, and then another started
in the middle of it's execution. Should I expect to see the ROWCOUNT clause
effect the 2nd query, or does it only effect the code which it's currently
executing.
I hope that helps clarify my question.
Thanks,
John Rossitter
"Jerry Spivey" wrote:

> John,
> You can just use the TOP clause with an ORDER BY clause to ommit the need
of
> setting ROWCOUNT.
> From SQL BOL:
> It is recommended that DELETE, INSERT, and UPDATE statements currently usi
ng
> SET ROWCOUNT be rewritten to use the TOP syntax. For more information, see
> DELETE, INSERT, or UPDATE.
> The setting of the SET ROWCOUNT option is ignored for INSERT, UPDATE, and
> DELETE statements against remote tables and local and remote partitioned
> views.
> HTH
> Jerry
> "John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in messag
e
> news:2095A9F1-B48A-40A3-8D40-A19A904B9BDD@.microsoft.com...
>
>|||Ok. I'm not aware of any issues. My testing confirms the same results - no
side affects. However that being said, I would recommend appending a SET
ROWCOUNT 0 to your code for completeness.
HTH
Jerry
"John Rossitter" <JohnRossitter@.discussions.microsoft.com> wrote in message
news:9596F2C7-649D-4EDD-98B3-A7C321FD4038@.microsoft.com...
> Hi Jerry,
> I can't use TOP because I need a dynamic number of rows selected.
> Which is why I'm using ROWCOUNT directive instead.
> To clarify what I have done, is in one of my tables set a MaxRows
> threshold.
> Now when any of my SELECT based SPs execute the code looks something like
> this:
> ====================================
> DECLARE @.TOP int;
> SET @.TOP = 1000
> SELECT @.TOP = Max_Record_Count FROM Control_Table
> SET ROWCOUNT @.TOP
> SELECT ....
> SET ROWCOUNT 0
> =====================================
> This way I can control the overall performance of all of my queries.
> I just want to make sure that the ROWCOUNT setting does not have an impact
> on any other code running in SQL.
> For example lets say that the above SP was running, and then another
> started
> in the middle of it's execution. Should I expect to see the ROWCOUNT
> clause
> effect the 2nd query, or does it only effect the code which it's currently
> executing.
> I hope that helps clarify my question.
> Thanks,
> John Rossitter
>
> "Jerry Spivey" wrote:
>|||Hi
You can use top in dynamic queries like this
DECLARE @.TOP int;
DECLARE @.VAR varchar(200)
SET @.TOP = 1000
SELECT @.TOP = Max_Record_Count FROM Control_Table
select @.VAR = 'SELECT TOP' + CAST(@.TOP as varchar(10) + ' from your table
name...'
exec(@.VAR)
--
Regards
R.D
--Knowledge gets doubled when shared
"John Rossitter" wrote:
> Hi Jerry,
> I can't use TOP because I need a dynamic number of rows selected.
> Which is why I'm using ROWCOUNT directive instead.
> To clarify what I have done, is in one of my tables set a MaxRows threshol
d.
> Now when any of my SELECT based SPs execute the code looks something like
> this:
> ====================================
> DECLARE @.TOP int;
> SET @.TOP = 1000
> SELECT @.TOP = Max_Record_Count FROM Control_Table
> SET ROWCOUNT @.TOP
> SELECT ....
> SET ROWCOUNT 0
> =====================================
> This way I can control the overall performance of all of my queries.
> I just want to make sure that the ROWCOUNT setting does not have an impact
> on any other code running in SQL.
> For example lets say that the above SP was running, and then another start
ed
> in the middle of it's execution. Should I expect to see the ROWCOUNT claus
e
> effect the 2nd query, or does it only effect the code which it's currently
> executing.
> I hope that helps clarify my question.
> Thanks,
> John Rossitter
>
> "Jerry Spivey" wrote:
>

Clarification on SQL Server Reporting Services 2005

Are all of the Reporting Services service packs handled in the SQL Server
service packs?
So, no more separate services packs for reporting services like in SQL
Server Reporting Services 2000.
Thanks
ReevesSo far it is. I haven't heard anything about that changing.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Reeves Smith" <ReevesSmith@.discussions.microsoft.com> wrote in message
news:B1BFE079-0E58-4FB0-BC06-932C4B072C19@.microsoft.com...
> Are all of the Reporting Services service packs handled in the SQL Server
> service packs?
> So, no more separate services packs for reporting services like in SQL
> Server Reporting Services 2000.
> Thanks
> Reeves
>

clarification on Reporting services

hi folks,

I want to display a message "no records retrieved" in the preview tab of the report in RS when no records are retrieved for a query specified in the data tab of the report in RS.Is there any custom code for this?How to accomplish this?Can anyone help me find a solution for this?Pls send me the procedure for this .

You could:

1. Set the message in the NoRows property of the data region bound to the dataset.

2. Display the message in a textbox which is hidden if the dataset has rows or visible otherwise.

|||

Dear sir,

Thank you for your reply..I was successful with the first point.Can u tell me how to proceed with the second point?

How to display the message in the textbox?should i type something in the textbox value property?if so how?

Regards

Balaji

|||Drop a textbox in the report body with the desired text, e.g. "No data". Set the textbox Visibility Hidden expression to =IsNothing(First(Fields!<some field name>.Value, "<your dataset name>")) = Falsesqlsql

clarification on Reporting services

hi folks,

I want to display a message "no records retrieved" in the preview tab of the report in RS when no records are retrieved for a query specified in the data tab of the report in RS.Is there any custom code for this?How to accomplish this?Can anyone help me find a solution for this?Pls send me the procedure for this .

You could:

1. Set the message in the NoRows property of the data region bound to the dataset.

2. Display the message in a textbox which is hidden if the dataset has rows or visible otherwise.

|||

Dear sir,

Thank you for your reply..I was successful with the first point.Can u tell me how to proceed with the second point?

How to display the message in the textbox?should i type something in the textbox value property?if so how?

Regards

Balaji

|||Drop a textbox in the report body with the desired text, e.g. "No data". Set the textbox Visibility Hidden expression to =IsNothing(First(Fields!<some field name>.Value, "<your dataset name>")) = False

Tuesday, March 20, 2012

Clarification on product specification

http://www.microsoft.com/sql/editions/compact/sscecomparison.mspx

The above link says the pros and cons of two SQL Server 2005 editions.

Document says Compact Edition is not good for ‘When you need a multi-user database server

’.

What is the meaning of multi-user database?

Does this means the database strictly will not support two database connections at a time?

-Thank you,

Gish

Multi-user means supporting several autonomous clients, like several web servers running against the same database, as SQL Compact is an in-process database engine, this will not work. Several connections are supported, but from the same application on the same machine.

Clarification on NULL values in the records.

Hi All
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>
>.
>|||Answered in .programming.
Please don't multi-post.
David Portas
SQL Server MVP
--

Clarification on NULL values in the records.

Hi All
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>> Hi All
>> Please write me the reason for
>> SQL:
>> --
>> select * from table1
>> where Required <> 'Y'
>> the result set is empty.
>> Table Schema is:
>> --
>> CREATE TABLE [dbo].[Table1] (
>> [Names] [nvarchar] (50) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Required] [nvarchar] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>> Insert Statement is is:
>> --
>> insert into table1 ([Names],Required)
>> values('Test1',null)
>> insert into table1 ([Names],Required)
>> values('Test2','Y')
>> insert into table1 ([Names],Required)
>> values('Test3',null)
>> insert into table1 ([Names],Required)
>> values('Test4','Y')
>> insert into table1 ([Names],Required)
>> values('Test5',null)
>> Is there any option I need to get the appropriate result
>> set?
>> B'coz my result set expected is
>> Test1 NULL
>> Test3 NULL
>> Test5 NULL
>
>.
>|||Answered in .programming.
Please don't multi-post.
--
David Portas
SQL Server MVP
--sqlsql

Clarification on Joins

Dear friends,

Can any one clearly explain about all type of joins or any tutorials for that. please help me b'se i failed it to explain in an interview clearly

samy

Quote:

Originally Posted by samycbe

Dear friends,

Can any one clearly explain about all type of joins or any tutorials for that. please help me b'se i failed it to explain in an interview clearly

samy


These are SQL server specification.

Inner joins (the typical join operation, which uses some comparison operator like = or <>). These include equi-joins and natural joins.
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. For example, retrieving all rows where the student identification number is the same in both the students and courses tables.

Outer joins. Outer joins can be a left, a right, or full outer join.
Outer joins are specified with one of the following sets of keywords when they are specified in the FROM clause:

LEFT JOIN or LEFT OUTER JOIN
The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.

RIGHT JOIN or RIGHT OUTER JOIN.
A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.

FULL JOIN or FULL OUTER JOIN.
A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.

Cross joins.
Cross joins return all rows from the left table, each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.|||Thanks and Its really helpful

Clarification on DataReader

I want to create a DataList that shows products, which will be onmultiple pages. I have my stored proc to show paged results, whichcontains a return value for more records.
I have found examples of coding the DataReader, defining all theparameters etc, but what about the drag and drop SqlDataSource?? Youcan select the DataSource Mode to be "DataReader". I can put selectparameters in, with input and my return value. I don't know how to thenaccess the return value, or output value if needed, from this? MyDataList references the SqlDataSource, but I don't know how to get thereturn/output value out? This is very frustrating, cause I can't findany info about it anywhere. Always input parameters, but no output.
This is my current SqlDataSource...

<asp:SqlDataSource ID="SqlDataSource1" runat="server"DataSourceMode="DataReader" ConnectionString="<%$ConnectionStrings:Personal %>" SelectCommand="sp_PagedItems"SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="Page" QueryStringField="page" />
<asp:Parameter Name="RecsPerPage" DefaultValue="10" />
<asp:QueryStringParameter Name="CategoryID" QueryStringField="cat"/>
<asp:Parameter Name="RETURN_VALUE" Direction="ReturnValue" Size="1"/>
</SelectParameters>
</asp:SqlDataSource
If I take out the RETURN_VALUE Parameter, my results display in my datalist, but that's useless if I can't access the return value todetermine the remaining number of pages etc. Is my RETURN_VALUEparameter wrong? How do I access that? My stored proc is shown below...

CREATE PROCEDURE sp_PagedItems
(
@.Page int,
@.RecsPerPage int,
@.CategoryID int
)
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON

--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
No varchar(100),
Name varchar(100),
SDescription varchar(500),
Size varchar(10),
ImageURL varchar(100)
)

-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (No, Name, SDescription, Size, ImageURL)
SELECT No, Name, SDescription, Size, ImageURL FROM Products WHERE CategoryID=@.CategoryID

-- Find out the first and last record we want
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.Page - 1) * @.RecsPerPage
SELECT @.LastRec = (@.Page * @.RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @.LastRec
)
FROM #TempItems
WHERE ID > @.FirstRec AND ID < @.LastRec

-- Turn NOCOUNT back OFF
SET NOCOUNT OFF

Hi Micky,

I have a sample that you might want to check out on my blog here:http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx

It shows how to build a paged datalist control that provides a product category listing in a very efficient way.

Hope this helps,

Scott

|||Scott,
Thanks for the reply, and I did find your blog, and it works perfect, however...
I don't have SQL 2005, i have SQL Server 2000. That's why I'm trying tofind a solution, or a blog or tutorial that would deal with that.
If i have to code the parameters in code-behind, then that's what I'lldo, but I'm trying to figure out why the SqlDataSource has the featureof DataReader, and putting in parameters, if I don't know how to usethem. No literature so far has shown me how it works. Everyone showsexamples of hand coding the parameters, but like I said, why would thedatasource have then available?|||

Hi Micky,

You can use the Selecting and Selected events on the SQlDataSource to write code that fires before and immediately after the Select method is called on the SqlDataSource. You can then use the eventArgs parameter to these events to either put parameters into the SqlDataSource (before the database is called), or to retrieve the output parameters (after it is called).

Hope this helps,

Scott

Clarification on ALT Snapshot Folder Location

I have 2 sql boxes setup in a VM environment, with neither of them knowing each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder location (Where I copied the Snapshot files to) c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file 'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch' due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription replication....
Made changes in the mean time...
I needed to Share the folders & give everyone rights (What is the minimum security I need to give everyone when I try & do this exact same setup when using the Internet?)
Publisher Alt Snapshot Folder = \\SQL2000B\C$\REPLDATA
Subscriber = C:\REPLDATA
I copied the folder from Publisher to Subscriber, changed subscription Alt Snapshot folder as described above & VIOLA! It finally works...
Man, I really, really hope this works when I try & set this up the same way from the DMZ (Internet) to the domain.
Any suggestions on that folder permission? Not being a network person, but recalling giving Everyone Full Control may be a bad thing (But it's just this replication folder, so in reality how bad can that really be?)
Thanx for putting up with my gazillion posts!!!!
Jude
"JLS" <jlshoop@.hotmail.com> wrote in message news:uYigLPO$FHA.3804@.TK2MSFTNGP14.phx.gbl...
I have 2 sql boxes setup in a VM environment, with neither of them knowing each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder location (Where I copied the Snapshot files to) c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file 'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch' due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription replication....
|||Its read and list files and folders.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ub7HLgO$FHA.1288@.TK2MSFTNGP09.phx.gbl...
Made changes in the mean time...
I needed to Share the folders & give everyone rights (What is the minimum
security I need to give everyone when I try & do this exact same setup when
using the Internet?)
Publisher Alt Snapshot Folder = \\SQL2000B\C$\REPLDATA
Subscriber = C:\REPLDATA
I copied the folder from Publisher to Subscriber, changed subscription Alt
Snapshot folder as described above & VIOLA! It finally works...
Man, I really, really hope this works when I try & set this up the same way
from the DMZ (Internet) to the domain.
Any suggestions on that folder permission? Not being a network person, but
recalling giving Everyone Full Control may be a bad thing (But it's just
this replication folder, so in reality how bad can that really be?)
Thanx for putting up with my gazillion posts!!!!
Jude
"JLS" <jlshoop@.hotmail.com> wrote in message
news:uYigLPO$FHA.3804@.TK2MSFTNGP14.phx.gbl...
I have 2 sql boxes setup in a VM environment, with neither of them knowing
each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as
follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot
location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder
location (Where I copied the Snapshot files to)
c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file
'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch'
due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription
replication....
|||THANX!
Jude
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:uGIyPwO$FHA.1288@.TK2MSFTNGP09.phx.gbl...
Its read and list files and folders.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"JLS" <jlshoop@.hotmail.com> wrote in message
news:ub7HLgO$FHA.1288@.TK2MSFTNGP09.phx.gbl...
Made changes in the mean time...
I needed to Share the folders & give everyone rights (What is the minimum
security I need to give everyone when I try & do this exact same setup when
using the Internet?)
Publisher Alt Snapshot Folder = \\SQL2000B\C$\REPLDATA
Subscriber = C:\REPLDATA
I copied the folder from Publisher to Subscriber, changed subscription Alt
Snapshot folder as described above & VIOLA! It finally works...
Man, I really, really hope this works when I try & set this up the same way
from the DMZ (Internet) to the domain.
Any suggestions on that folder permission? Not being a network person, but
recalling giving Everyone Full Control may be a bad thing (But it's just
this replication folder, so in reality how bad can that really be?)
Thanx for putting up with my gazillion posts!!!!
Jude
"JLS" <jlshoop@.hotmail.com> wrote in message
news:uYigLPO$FHA.3804@.TK2MSFTNGP14.phx.gbl...
I have 2 sql boxes setup in a VM environment, with neither of them knowing
each other (No Trusted Domains)
Here's what I have accomplished:
I was able to put the entry in the Hosts file for each
Put an Alias for each box in Client Network Utility
Setup the Publication - allowing anonymous pull subscribers
Setup the anonymous pull subscription
NOTE: I set the publisher snapshot location as
follows;\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B _test_test\unc\SQL2000B_test_test\20051208181044
Then I copied this folder to the Subscriber & setup the alt. snapshot
location with the same path
\\Sql2000b\c$\Inetpub\wwwroot\unc\SQL2000B_test_te st\unc\SQL2000B_test_test\20051208181044
I even tried to put the Subscriber path to point to the local folder
location (Where I copied the Snapshot files to)
c:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL2 000B_test_test\20051208181044
But I continue to get an error message stating:
The process could not read file
'C:\Inetpub\wwwroot\unc\SQL2000B_test_test\unc\SQL 2000B_test_test\20051208181044\testtbl_1.sch'
due to OS error 3. The step failed.
I am so missing something when trying to setup anonymous pull subscription
replication....

Clarification of suitability

We ship an application for use in hotels. Some are big and will us
SQL Server 2005, some aren't so big and will use SQL Server Express
For the bigger sites it is fine for the users to select reports fro
the server
But for all sites we would like to present a list of reports an
descriptions from our application for the user to select and ru
(generally printing direct to a printer, but sometimes to a previe
or export). Also, we want the user to be able to add his own report
to this list without us having to write any database access code
etc
Also, some of these reports need to be run directly by our applicatio
(nearly always direct to the printer)
My question is whether all this can be achieved with RS and th
versions of SQL Server outlined above
Apologies if this has been covered elsewhere but I haven't found it s
farLast time I looked, RS was not available with SQL Server Express or SQL
Workgroup, only Enterprise and Standard
--
Mary Bray [SQL Server MVP]
Please reply only to newsgroups
"SimonP" <simon@.mentat.co-dot-uk.no-spam.invalid> wrote in message
news:pr2dnf298v9c4BDfRVn_vg@.giganews.com...
> We ship an application for use in hotels. Some are big and will use
> SQL Server 2005, some aren't so big and will use SQL Server Express.
> For the bigger sites it is fine for the users to select reports from
> the server.
> But for all sites we would like to present a list of reports and
> descriptions from our application for the user to select and run
> (generally printing direct to a printer, but sometimes to a preview
> or export). Also, we want the user to be able to add his own reports
> to this list without us having to write any database access code,
> etc.
> Also, some of these reports need to be run directly by our application
> (nearly always direct to the printer).
> My question is whether all this can be achieved with RS and the
> versions of SQL Server outlined above.
> Apologies if this has been covered elsewhere but I haven't found it so
> far!
>

Clarification of execution in SQL2000 SP3A

Hi,
I need some clarification of a situation that we now have when moving to
SP3A. If we try and execute a stored procedure from another database it runs
on the database the stored procedure belongs to.
e.g. USE DatabaseA
Execute DatabaseB.dbo.upRunsp
The sp upRunsp will run on DatabaseB not DatabaseA
Is this an effect of cross DB ownership chaining being set off?
Thanks
Chris Wood
Alberta Department of Energy
CANADA
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I need some clarification of a situation that we now have when moving to
> SP3A. If we try and execute a stored procedure from another database it
runs
> on the database the stored procedure belongs to.
> e.g. USE DatabaseA
> Execute DatabaseB.dbo.upRunsp
> The sp upRunsp will run on DatabaseB not DatabaseA
> Is this an effect of cross DB ownership chaining being set off?
>
No. This has nothing to do with cross DB ownership chaining. When you
EXECUTE a procedure using a database-qualified name, the object names inside
the procedure are resolved of wrt the procedure's owner. When you run it
without qualifation, names are resolved wrt your current context.
compare the output of
EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
and
EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
And the only way to run a procedure in another database without a
database-qualified name is to put it in Master and prefix it with sp_. So
the only way to have one procedure run against different databases is to put
it in Master and prefix it with sp_.
David
|||David,
Did the behaviour change between Gold and SP3A?
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%238Lp6gmTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> runs
> No. This has nothing to do with cross DB ownership chaining. When you
> EXECUTE a procedure using a database-qualified name, the object names
inside
> the procedure are resolved of wrt the procedure's owner. When you run it
> without qualifation, names are resolved wrt your current context.
> compare the output of
> EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
> and
> EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
> And the only way to run a procedure in another database without a
> database-qualified name is to put it in Master and prefix it with sp_. So
> the only way to have one procedure run against different databases is to
put
> it in Master and prefix it with sp_.
> David
>
>
|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> David,
> Did the behaviour change between Gold and SP3A?
>
This behavior has been like that since at least 6.5.
David
|||David,
Just debugged this one better. Under SP2 if the default database is the one
you are on you can run xp_sendmail from master, while you are on your
default database, and look at a table on your default database without
needing to fully qualify it.
e.g. on DatabaseA
exec master..xp_sendmail @.recipients = '...',
@.query = 'SELECT FROM tablea where
This will work.
Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT to
DatabaseA.dbo.tablea
We are using build 818 for testing.
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> This behavior has been like that since at least 6.5.
> David
>
|||Also. This only works this way if the database is your default database. It
does not matter about the permissions of the user just its default database
setting.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e2yXy9xTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> David,
> Just debugged this one better. Under SP2 if the default database is the
one
> you are on you can run xp_sendmail from master, while you are on your
> default database, and look at a table on your default database without
> needing to fully qualify it.
> e.g. on DatabaseA
> exec master..xp_sendmail @.recipients = '...',
> @.query = 'SELECT FROM tablea where
> This will work.
> Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT
to
> DatabaseA.dbo.tablea
> We are using build 818 for testing.
> Chris
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
>
|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:O8LPRQyTEHA.808@.tk2msftngp13.phx.gbl...
> Also. This only works this way if the database is your default database.
It
> does not matter about the permissions of the user just its default
database
> setting.
>
Ahh. xp_sendmail executes that query over a different connection created
from within the extended stored procedure, but bound into the same
transaction. Normal rules of name resolution don't apply here, as it
depends entirely on how xp_sendmail creates the second connection and what
database it connects to. Perhaps there was a change to the implementation
of xp_sendmail in SP3.
David
|||Are all of the objects needed in the same database on the same server? We
had "cross database ownership" introduced in sp3. Is this checked?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Vikram,
I thought that this may have been the problem but it is not. We have a
stored procedure in database A that included a xp_sendmail with a query that
ran against a table in database A. Prior to SP3 we did not have to fully
qualify the table in database A. Fully qualifying works for both SP2 and SP3
and that is what we had to do.
Thanks
Chris
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:O%23j3ZkNXEHA.328@.cpmsftngxa10.phx.gbl...
> Are all of the objects needed in the same database on the same server? We
> had "cross database ownership" introduced in sp3. Is this checked?
>
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
|||Vikram,
I thought that this may have been the problem but it is not. We have a
stored procedure in database A that included a xp_sendmail with a query that
ran against a table in database A. Prior to SP3 we did not have to fully
qualify the table in database A. Fully qualifying works for both SP2 and SP3
and that is what we had to do.
Thanks
Chris
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:O%23j3ZkNXEHA.328@.cpmsftngxa10.phx.gbl...
> Are all of the objects needed in the same database on the same server? We
> had "cross database ownership" introduced in sp3. Is this checked?
>
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>
sqlsql

Clarification of execution in SQL2000 SP3A

Hi,
I need some clarification of a situation that we now have when moving to
SP3A. If we try and execute a stored procedure from another database it runs
on the database the stored procedure belongs to.
e.g. USE DatabaseA
Execute DatabaseB.dbo.upRunsp
The sp upRunsp will run on DatabaseB not DatabaseA
Is this an effect of cross DB ownership chaining being set off?
Thanks
Chris Wood
Alberta Department of Energy
CANADA"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I need some clarification of a situation that we now have when moving to
> SP3A. If we try and execute a stored procedure from another database it
runs
> on the database the stored procedure belongs to.
> e.g. USE DatabaseA
> Execute DatabaseB.dbo.upRunsp
> The sp upRunsp will run on DatabaseB not DatabaseA
> Is this an effect of cross DB ownership chaining being set off?
>
No. This has nothing to do with cross DB ownership chaining. When you
EXECUTE a procedure using a database-qualified name, the object names inside
the procedure are resolved of wrt the procedure's owner. When you run it
without qualifation, names are resolved wrt your current context.
compare the output of
EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
and
EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
And the only way to run a procedure in another database without a
database-qualified name is to put it in Master and prefix it with sp_. So
the only way to have one procedure run against different databases is to put
it in Master and prefix it with sp_.
David|||David,
Did the behaviour change between Gold and SP3A?
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%238Lp6gmTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> runs
> No. This has nothing to do with cross DB ownership chaining. When you
> EXECUTE a procedure using a database-qualified name, the object names
inside
> the procedure are resolved of wrt the procedure's owner. When you run it
> without qualifation, names are resolved wrt your current context.
> compare the output of
> EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
> and
> EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
> And the only way to run a procedure in another database without a
> database-qualified name is to put it in Master and prefix it with sp_. So
> the only way to have one procedure run against different databases is to
put
> it in Master and prefix it with sp_.
> David
>
>|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> David,
> Did the behaviour change between Gold and SP3A?
>
This behavior has been like that since at least 6.5.
David|||David,
Just debugged this one better. Under SP2 if the default database is the one
you are on you can run xp_sendmail from master, while you are on your
default database, and look at a table on your default database without
needing to fully qualify it.
e.g. on DatabaseA
exec master..xp_sendmail @.recipients = '...',
@.query = 'SELECT FROM tablea where
This will work.
Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT to
DatabaseA.dbo.tablea
We are using build 818 for testing.
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> This behavior has been like that since at least 6.5.
> David
>|||Also. This only works this way if the database is your default database. It
does not matter about the permissions of the user just its default database
setting.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e2yXy9xTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> David,
> Just debugged this one better. Under SP2 if the default database is the
one
> you are on you can run xp_sendmail from master, while you are on your
> default database, and look at a table on your default database without
> needing to fully qualify it.
> e.g. on DatabaseA
> exec master..xp_sendmail @.recipients = '...',
> @.query = 'SELECT FROM tablea where
> This will work.
> Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT
to
> DatabaseA.dbo.tablea
> We are using build 818 for testing.
> Chris
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
>|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:O8LPRQyTEHA.808@.tk2msftngp13.phx.gbl...
> Also. This only works this way if the database is your default database.
It
> does not matter about the permissions of the user just its default
database
> setting.
>
Ahh. xp_sendmail executes that query over a different connection created
from within the extended stored procedure, but bound into the same
transaction. Normal rules of name resolution don't apply here, as it
depends entirely on how xp_sendmail creates the second connection and what
database it connects to. Perhaps there was a change to the implementation
of xp_sendmail in SP3.
David|||Are all of the objects needed in the same database on the same server? We
had "cross database ownership" introduced in sp3. Is this checked?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Vikram,
I thought that this may have been the problem but it is not. We have a
stored procedure in database A that included a xp_sendmail with a query that
ran against a table in database A. Prior to SP3 we did not have to fully
qualify the table in database A. Fully qualifying works for both SP2 and SP3
and that is what we had to do.
Thanks
Chris
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:O%23j3ZkNXEHA.328@.cpmsftngxa10.phx.gbl...
> Are all of the objects needed in the same database on the same server? We
> had "cross database ownership" introduced in sp3. Is this checked?
>
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

Clarification of execution in SQL2000 SP3A

Hi,
I need some clarification of a situation that we now have when moving to
SP3A. If we try and execute a stored procedure from another database it runs
on the database the stored procedure belongs to.
e.g. USE DatabaseA
Execute DatabaseB.dbo.upRunsp
The sp upRunsp will run on DatabaseB not DatabaseA
Is this an effect of cross DB ownership chaining being set off?
Thanks
Chris Wood
Alberta Department of Energy
CANADA"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I need some clarification of a situation that we now have when moving to
> SP3A. If we try and execute a stored procedure from another database it
runs
> on the database the stored procedure belongs to.
> e.g. USE DatabaseA
> Execute DatabaseB.dbo.upRunsp
> The sp upRunsp will run on DatabaseB not DatabaseA
> Is this an effect of cross DB ownership chaining being set off?
>
No. This has nothing to do with cross DB ownership chaining. When you
EXECUTE a procedure using a database-qualified name, the object names inside
the procedure are resolved of wrt the procedure's owner. When you run it
without qualifation, names are resolved wrt your current context.
compare the output of
EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
and
EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
And the only way to run a procedure in another database without a
database-qualified name is to put it in Master and prefix it with sp_. So
the only way to have one procedure run against different databases is to put
it in Master and prefix it with sp_.
David|||David,
Did the behaviour change between Gold and SP3A?
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%238Lp6gmTEHA.2128@.TK2MSFTNGP11.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:e7MpPTmTEHA.1168@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I need some clarification of a situation that we now have when moving to
> > SP3A. If we try and execute a stored procedure from another database it
> runs
> > on the database the stored procedure belongs to.
> >
> > e.g. USE DatabaseA
> > Execute DatabaseB.dbo.upRunsp
> >
> > The sp upRunsp will run on DatabaseB not DatabaseA
> >
> > Is this an effect of cross DB ownership chaining being set off?
> >
> No. This has nothing to do with cross DB ownership chaining. When you
> EXECUTE a procedure using a database-qualified name, the object names
inside
> the procedure are resolved of wrt the procedure's owner. When you run it
> without qualifation, names are resolved wrt your current context.
> compare the output of
> EXECUTE sp_help --displays info about the currentdb.dbo.sysobjects
> and
> EXECUTE master.dbo.sp_help --displays info about master.dbo.sysobjects
> And the only way to run a procedure in another database without a
> database-qualified name is to put it in Master and prefix it with sp_. So
> the only way to have one procedure run against different databases is to
put
> it in Master and prefix it with sp_.
> David
>
>|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> David,
> Did the behaviour change between Gold and SP3A?
>
This behavior has been like that since at least 6.5.
David|||David,
Just debugged this one better. Under SP2 if the default database is the one
you are on you can run xp_sendmail from master, while you are on your
default database, and look at a table on your default database without
needing to fully qualify it.
e.g. on DatabaseA
exec master..xp_sendmail @.recipients = '...',
@.query = 'SELECT FROM tablea where
This will work.
Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT to
DatabaseA.dbo.tablea
We are using build 818 for testing.
Chris
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> > David,
> >
> > Did the behaviour change between Gold and SP3A?
> >
> This behavior has been like that since at least 6.5.
> David
>|||Also. This only works this way if the database is your default database. It
does not matter about the permissions of the user just its default database
setting.
Chris
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:e2yXy9xTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> David,
> Just debugged this one better. Under SP2 if the default database is the
one
> you are on you can run xp_sendmail from master, while you are on your
> default database, and look at a table on your default database without
> needing to fully qualify it.
> e.g. on DatabaseA
> exec master..xp_sendmail @.recipients = '...',
> @.query = 'SELECT FROM tablea where
> This will work.
> Under SP3A it gives an ODBC error 208 (42S02) unless I change the SELECT
to
> DatabaseA.dbo.tablea
> We are using build 818 for testing.
> Chris
> "David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
> message news:eGGxE0mTEHA.760@.TK2MSFTNGP12.phx.gbl...
> >
> > "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> > news:eTVRjlmTEHA.1472@.TK2MSFTNGP12.phx.gbl...
> > > David,
> > >
> > > Did the behaviour change between Gold and SP3A?
> > >
> >
> > This behavior has been like that since at least 6.5.
> >
> > David
> >
> >
>|||"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:O8LPRQyTEHA.808@.tk2msftngp13.phx.gbl...
> Also. This only works this way if the database is your default database.
It
> does not matter about the permissions of the user just its default
database
> setting.
>
Ahh. xp_sendmail executes that query over a different connection created
from within the extended stored procedure, but bound into the same
transaction. Normal rules of name resolution don't apply here, as it
depends entirely on how xp_sendmail creates the second connection and what
database it connects to. Perhaps there was a change to the implementation
of xp_sendmail in SP3.
David|||Are all of the objects needed in the same database on the same server? We
had "cross database ownership" introduced in sp3. Is this checked?
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Vikram,
I thought that this may have been the problem but it is not. We have a
stored procedure in database A that included a xp_sendmail with a query that
ran against a table in database A. Prior to SP3 we did not have to fully
qualify the table in database A. Fully qualifying works for both SP2 and SP3
and that is what we had to do.
Thanks
Chris
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:O%23j3ZkNXEHA.328@.cpmsftngxa10.phx.gbl...
> Are all of the objects needed in the same database on the same server? We
> had "cross database ownership" introduced in sp3. Is this checked?
>
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>

clarification needed on sqlxml installation

I have 2 servers. One is an IIS server and the other is
the SQL Server 2000 SP3 server. I am confused on where i
am supposed to install SQLXML 3.0 SP2. Should i install
the software on the SQL Server machine or the IIS machine?
thanks
In most cases you just need to install it on the IIS Server.
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Giovanni" <anonymous@.discussions.microsoft.com> wrote in message
news:6ae201c4cb1d$2d894f00$a301280a@.phx.gbl...
I have 2 servers. One is an IIS server and the other is
the SQL Server 2000 SP3 server. I am confused on where i
am supposed to install SQLXML 3.0 SP2. Should i install
the software on the SQL Server machine or the IIS machine?
thanks
|||Now you are making me confused.
Are you sure that you are supposed to install it on the IIS and not the SQL?
What data should the msxisapi.dll file present if not the data from the SQL
server? I have installed the XML service on the SQL server and not the IIS.
Can you explain why you install it on the WEB server and how you configure
it to retrieve data from the SQL server...
Reg
Jrgen Persson
"Graeme Malcolm" <graemem_cm@.hotmail.com> skrev i meddelandet
news:eaeKy97yEHA.336@.TK2MSFTNGP10.phx.gbl...
> In most cases you just need to install it on the IIS Server.
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Giovanni" <anonymous@.discussions.microsoft.com> wrote in message
> news:6ae201c4cb1d$2d894f00$a301280a@.phx.gbl...
> I have 2 servers. One is an IIS server and the other is
> the SQL Server 2000 SP3 server. I am confused on where i
> am supposed to install SQLXML 3.0 SP2. Should i install
> the software on the SQL Server machine or the IIS machine?
> thanks
>
|||It depends what you are doing, but SQLXML is primarily a client-side
technology. Install it on the IIS server and use the "Configure IIS Support"
tool to create a VDir that connects to your SQL Server to retrieve the data
you need to expose through IIS (either via a template, a schema, a SOAP
vName).
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Jrgen" <jorgen_persson78@.hotmail.com> wrote in message
news:%23pNo3H8yEHA.3376@.TK2MSFTNGP12.phx.gbl...
Now you are making me confused.
Are you sure that you are supposed to install it on the IIS and not the SQL?
What data should the msxisapi.dll file present if not the data from the SQL
server? I have installed the XML service on the SQL server and not the IIS.
Can you explain why you install it on the WEB server and how you configure
it to retrieve data from the SQL server...
Reg
Jrgen Persson
"Graeme Malcolm" <graemem_cm@.hotmail.com> skrev i meddelandet
news:eaeKy97yEHA.336@.TK2MSFTNGP10.phx.gbl...
> In most cases you just need to install it on the IIS Server.
> --
> --
> Graeme Malcolm
> Principal Technologist
> Content Master Ltd.
> www.contentmaster.com
>
> "Giovanni" <anonymous@.discussions.microsoft.com> wrote in message
> news:6ae201c4cb1d$2d894f00$a301280a@.phx.gbl...
> I have 2 servers. One is an IIS server and the other is
> the SQL Server 2000 SP3 server. I am confused on where i
> am supposed to install SQLXML 3.0 SP2. Should i install
> the software on the SQL Server machine or the IIS machine?
> thanks
>