Showing posts with label values. Show all posts
Showing posts with label values. Show all posts

Friday, March 30, 2012

Problem with SQL Update when a field is NULL

I am having a problem with an update statement. I am using compareallvalues with a SQL data source. When one of the old values is null the update does not go through.

i.e. This does not work if something is NULL

UPDATE myTable SET something = @.something
WHERE ID = @.o_ID AND something = @.o_something

Thank You,
Jason

The reason is ANSI SQL NULL is an unknown so comparing NULL to NULL is not NULL but an unknown. Try the link below for work around. Hope this helps.

http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls_printversion.asp

|||Will there be a huge performance impact if I use Coalesce() 144 times in a statement?|||

No I think you should try SET ANSI_NULLS OFF instead. Try the link below for details from Microsoft. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_9rec.asp

|||It still does not work when I use set ANSI_NULLS OFF|||

I have two option there is a new tool that gives you intelisense in SQL Server Query Tools called SQL Prompt see if it can give you some idea of how to get the info. The other is a link with long code using COALESCE and ISNULL. Hope this helps.

http://www.red-gate.com/products/SQL_Prompt/index.htm?utm_source=sscentral&utm_medium=banner&utm_campaign=sqlprompt

http://weblogs.sqlteam.com/mladenp/articles/2937.aspx

Problem with SQL transacts

I have an issue with duplicates in a table but the whole rows are not
duplicated just certain values. Could anyone point me in the right
direction for creating a script to fix the problem as described
below. I have a basic understanding of tsql and have used the count
function but this is a bit beyond me at the moment:
Table1
col1 col2 col3 col4 col5
101 abc xyz A other
101 abc uvw A other
101 def xyz A other
102 def uvw A other
103 abc xyz A other
104 def uvw A other
col 2 and col 3 should only ever have the same values in a single row
but as you can see rows 5 and 6 contain duplicates I'd like to be able
to runa script that would find these duplicates and then check col1
and update col 4 from A to D thats should rectify my issues.
Any help much appreciated
RobFirst, once you have corrected the current data problem add a UNIQUE
constraint . The basis of database integrity is to prevent problems
from happening in the first place.
The basic query to find duplicates:
SELECT col2, col3, count(*) as DupCount
FROM Sometable
GROUP BY col2, col3
HAVING count(*) > 1
I think this does what you want:
UPDATE Whatever
SET col4 = 'D'
WHERE EXISTS
(SELECT * FROM Whatever as B
WHERE Whatever.col2 = D.col2
AND Whatever.col3 = D.col3
AND Whatever.col1 < D.col1)
AND col4 <> 'D'
Roy Harvey
Beacon Falls, CT
On Sat, 12 Jan 2008 11:00:34 -0800 (PST), rob.stevenson@.isoftplc.com
wrote:
>I have an issue with duplicates in a table but the whole rows are not
>duplicated just certain values. Could anyone point me in the right
>direction for creating a script to fix the problem as described
>below. I have a basic understanding of tsql and have used the count
>function but this is a bit beyond me at the moment:
>Table1
>col1 col2 col3 col4 col5
>101 abc xyz A other
>101 abc uvw A other
>101 def xyz A other
>102 def uvw A other
>103 abc xyz A other
>104 def uvw A other
>col 2 and col 3 should only ever have the same values in a single row
>but as you can see rows 5 and 6 contain duplicates I'd like to be able
>to runa script that would find these duplicates and then check col1
>and update col 4 from A to D thats should rectify my issues.
>Any help much appreciated
>Rob|||Hi Rob
I don't understand your description of how you want to 'fix' the problem.
Can you give more details, including what this data should like after your
fix?
This query will find your duplicates:
SELECT col2, col3
FROM Table1
GROUP BY col2, col3
HAVING count(*) > 1
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
<rob.stevenson@.isoftplc.com> wrote in message
news:364750da-441c-41f0-82f9-a7285fc23ba3@.l32g2000hse.googlegroups.com...
>I have an issue with duplicates in a table but the whole rows are not
> duplicated just certain values. Could anyone point me in the right
> direction for creating a script to fix the problem as described
> below. I have a basic understanding of tsql and have used the count
> function but this is a bit beyond me at the moment:
> Table1
> col1 col2 col3 col4 col5
> 101 abc xyz A other
> 101 abc uvw A other
> 101 def xyz A other
> 102 def uvw A other
> 103 abc xyz A other
> 104 def uvw A other
> col 2 and col 3 should only ever have the same values in a single row
> but as you can see rows 5 and 6 contain duplicates I'd like to be able
> to runa script that would find these duplicates and then check col1
> and update col 4 from A to D thats should rectify my issues.
> Any help much appreciated
> Rob|||On 12 Jan, 19:18, "Roy Harvey (SQL Server MVP)" <roy_har...@.snet.net>
wrote:
> First, once you have corrected the current data problem add a UNIQUE
> constraint . =A0The basis of database integrity is to prevent problems
> from happening in the first place.
> The basic query to find duplicates:
> SELECT col2, col3, count(*) as DupCount
> =A0 FROM Sometable
> =A0GROUP BY col2, col3
> HAVING count(*) > 1
> I think this does what you want:
> UPDATE Whatever
> =A0 =A0SET col4 =3D 'D'
> =A0WHERE EXISTS
> =A0 =A0 =A0 =A0(SELECT * FROM Whatever as B
> =A0 =A0 =A0 =A0 =A0WHERE Whatever.col2 =3D D.col2
> =A0 =A0 =A0 =A0 =A0 =A0AND Whatever.col3 =3D D.col3
> =A0 =A0 =A0 =A0 =A0 =A0AND Whatever.col1 < D.col1)
> =A0 =A0AND col4 <> 'D'
> Roy Harvey
> Beacon Falls, CT
> On Sat, 12 Jan 2008 11:00:34 -0800 (PST), rob.steven...@.isoftplc.com
> wrote:
>
> >I have an issue with duplicates in a table but the whole rows are not
> >duplicated just certain values. =A0Could anyone point me in the right
> >direction for creating a script to fix the problem as described
> >below. =A0I have a basic understanding of tsql and have used the count
> >function but this is a bit beyond me at the moment:
> >Table1
> >col1 col2 col3 col4 col5
> >101 =A0abc =A0xyz =A0A =A0other
> >101 =A0abc =A0uvw =A0A =A0other
> >101 =A0def =A0 xyz =A0A =A0other
> >102 =A0def =A0 uvw =A0A =A0other
> >103 =A0abc =A0xyz =A0A =A0other
> >104 =A0def =A0 uvw =A0A =A0other
> >col 2 and col 3 should only ever have the same values in a single row
> >but as you can see rows 5 and 6 contain duplicates I'd like to be able
> >to runa script that would find these duplicates and then check col1
> >and update col 4 from A to D thats should rectify my issues.
> >Any help much appreciated
> >Rob- Hide quoted text -
> - Show quoted text -
Hi Roy,
Thanks for that the second script was spot on with some minor tweaking
very much appreciated.
Thanks, Robsql

Monday, March 26, 2012

problem with split function to select multiple values

When running the split function from my data pane within ssrs, or when
running it from sql server directly everything works fine.
But..when running the report in preview I receive dbo.split too many
arguments specified. I assume reporting services in some way passes it
like a string value once running the report. Otherwise it's also
noticeable the join function works fine within
code i'm using is like this within my dataset
Select myfield
from table
where field in
(
select item
from dbo.split(@.par,',')
)
The parameter passed to the split function has an 'integer value'
specified for the 'parameter value' . The split value is the well
known generic split function.If @.Par is a multivalued parameter, then try joining the items together in a
string:
Select myfield
from table
where field in
(
select item
from dbo.split(join(@.par,','),',')
)
"koopmans.johan@.hccnet.nl" wrote:
> When running the split function from my data pane within ssrs, or when
> running it from sql server directly everything works fine.
> But..when running the report in preview I receive dbo.split too many
> arguments specified. I assume reporting services in some way passes it
> like a string value once running the report. Otherwise it's also
> noticeable the join function works fine within
> code i'm using is like this within my dataset
> Select myfield
> from table
> where field in
> (
> select item
> from dbo.split(@.par,',')
> )
> The parameter passed to the split function has an 'integer value'
> specified for the 'parameter value' . The split value is the well
> known generic split function.
>|||Update to my previous post, the parameter to the split function must be a
varchar parameter as this will be sent through as a single comma separated
string list to the function.
"koopmans.johan@.hccnet.nl" wrote:
> When running the split function from my data pane within ssrs, or when
> running it from sql server directly everything works fine.
> But..when running the report in preview I receive dbo.split too many
> arguments specified. I assume reporting services in some way passes it
> like a string value once running the report. Otherwise it's also
> noticeable the join function works fine within
> code i'm using is like this within my dataset
> Select myfield
> from table
> where field in
> (
> select item
> from dbo.split(@.par,',')
> )
> The parameter passed to the split function has an 'integer value'
> specified for the 'parameter value' . The split value is the well
> known generic split function.
>|||Branden, sorry for taking your time...I've been using a direct query
within SSRS so there was no need for the split (this explains the
error). I tend to use SP's or cubes regularly and confused the way
they handle parameters with a direct query i'm using now. So this is
the simple solution
Select myfield
from table
where field in
(
@.par
)

Wednesday, March 21, 2012

Problem with setting variable values in a loop

In a stored procedure that I'm fixing, there is a problem with assigning variable values inside a loop. The proc is using dynamic SQL and if statements to build all these statements, but I'm having to add a new variable value to it that is throwing it out of whack.

This is the current structure:

SET @.MktNbr = 10

WHILE @.MktNbr < 90

BEGIN

DECLARE @.sqlstmt varchar(1000)

SET @.Market = '0' + CONVERT(char(2),@.MktNbr)

SET @.sqlstmt = ' SELECT (columns)
INTO dbo.table' + @.Market + '
FROM #table
WHERE marketcode = ''' + @.Market + '''
IF @.MktNbr = 50
BEGIN
SET @.MktNbr = 51
END
ELSE
IF @.MktNbr = 51
BEGIN
SET @.MktNbr = 52
END
ELSE
IF @.MktNbr = 52
BEGIN
SET @.MktNbr = 55
END
ELSE
IF @.MktNbr = 55
BEGIN
SET @.MktNbr = 60
END
ELSE
BEGIN
SET @.MktNbr = @.MktNbr + 10
END
EXEC (@.sqlstmt)

END

I'm probably having a blonde moment, but I'm trying to replace the if statements with this:

SET @.MktNbr =
CASE
WHEN @.MktNbr = 10 THEN 20
WHEN @.MktNbr = 20 THEN 30
WHEN @.MktNbr = 30 THEN 40
WHEN @.MktNbr = 40 THEN 50
WHEN @.MktNbr = 50 THEN 51
WHEN @.MktNbr = 51 THEN 52
WHEN @.MktNbr = 52 THEN 55
WHEN @.MktNbr = 55 THEN 60
WHEN @.MktNbr = 60 THEN 70
WHEN @.MktNbr = 70 THEN 80
WHEN @.MktNbr = 80 THEN 81
ELSE @.MktNbr END

Clearly it's wrong because the proc bombs every time with a duplicate table error.

It has been suggested to me that I should hold these market values in an external table. This sounds reasonable but I'm ashamed to admit that I don't know how I'd implement that. Can someone maybe give me a nudge in the right direction?That works fine for me:

DECLARE @.MktNbr int

SET @.MktNbr = 30

SET @.MktNbr =
CASE
WHEN @.MktNbr = 10 THEN 20
WHEN @.MktNbr = 20 THEN 30
WHEN @.MktNbr = 30 THEN 40
WHEN @.MktNbr = 40 THEN 50
WHEN @.MktNbr = 50 THEN 51
WHEN @.MktNbr = 51 THEN 52
WHEN @.MktNbr = 52 THEN 55
WHEN @.MktNbr = 55 THEN 60
WHEN @.MktNbr = 60 THEN 70
WHEN @.MktNbr = 70 THEN 80
WHEN @.MktNbr = 80 THEN 81
ELSE @.MktNbr END

PRINT @.MktNbr|||First...dynamic sql...ugh

Second, why are you setting @.market BEFORE you set @.mrktnmbr?

third, non logged creation of a table will fail the second time you need to do the insert

Can you explain, in business terms, what you are trying to accomplish, or what's been asked of you?|||Clearly it's wrong because the proc bombs every time with a duplicate table error.

Clearly

You can only execute it once per table creation.

Also, again, the assignmnet is out of whack

You will always be trying to create the same table, over and over, because the tablename is not being included in your "logic"|||Clearly it's wrong because the proc bombs every time with a duplicate table error.
My guess is that it fails on dbo.table081, right?

When @.MktNbr reaches 81, your case statement assigns it the new value of 81. The loop will try to make table081 again and fails.
You should set it to 90, so the loop will end.|||The answer is:
@.MktNbr never exceeds 81.|||the biggest wtf here is why are there so many market tables? why not just one?|||But as Brett (and now Rudy... Man I'm slow resonding to this thread) as highlighted above - the code is not good!
Even if you have a fix this is not the way for you to be doing this - explain what you're trying to achieve and hopefully we can prod you towards a better solution :)|||First...dynamic sql...ugh

Second, why are you setting @.market BEFORE you set @.mrktnmbr?

third, non logged creation of a table will fail the second time you need to do the insert

Can you explain, in business terms, what you are trying to accomplish, or what's been asked of you?

Fair points...allow me to address them in turn.

First: yes, dynamic SQL can be yucky but this is not something I developed, I am only making a modification to it. ;)

Second: See first point...I didn't write that, somebody else did. Somebody who no longer works here. :angel:

Third: I've had some ideas of things I'm going to try there so I'll get back to you on that. :)|||Second: See first point...I didn't write that, somebody else did. Somebody who no longer works here. :angel:

There's a reason for that|||Let me ask, do the tables get dropped before you hit this code?

How much data are we talking about?

Why not just hard code the 10 statements and not use dynamic sql?

Or, why not use 1 table and add a column for market code?

Really, all of this makes very little sense

So where did the person go? Burger King?|||There's a reason for that

Yep, there is. Thing is, the bossman doesn't want me to re-write the proc since it works...it's just slow. Right now the priority is just to make that amendment.

If you think that's good, there's some other ones that'd probably turn your hair white.|||Let me ask, do the tables get dropped before you hit this code?

How much data are we talking about?

Why not just hard code the 10 statements and not use dynamic sql?

Or, why not use 1 table and add a column for market code?

Really, all of this makes very little sense

So where did the person go? Burger King?

Don't fret, the person who suggested that I needed to set the variable to 90 was right; it works now. :cool:

Some of the procs do have hard-coded statements. Some of the developers here prefer the dynamic sql because they feel it's easier to maintain. I'm new here so I'm not in a position to tell them their code sucks, particularly since I'm the least experienced of the group. And yes, the tables get dropped; that's the first thing that happens in the proc. Right now we're not doing any design changes.|||that'd probably turn your hair white.

too late, the margarita's took care of that

And btw, what's "Too slow"

Instead of moving the data, why not just create views that are the name of the tables you are creating?

Oh, and if the smucks think your a jr. dba/developer, just keep coming here.

We'll smoke'em|||too late, the margarita's took care of that

And btw, what's "Too slow"

Instead of moving the data, why not just create views that are the name of the tables you are creating?

Oh, and if the smucks think your a jr. dba/developer, just keep coming here.

We'll smoke'em

This particular proc takes over an hour to execute.

Right now I'm testing one that has been executing for over four hours. It's obscene. :Ssql

Monday, March 12, 2012

Problem with Saving data in SQL Server

Hi,

I have a problem. The thing is that when I enter the values in a text boxes like evendate.text = "02/11/07" and desc.text="hello how r u" then I click on Save Button and its save in the SQL DATABASE but after this if I click on Refresh Button every time from the internet Explorer its automatically again save the data in the database although I have empty the textboxes.

The code is below:

<script runat="server">

sub SaveEvent(sender as object, e as EventArgs)

dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")

dim cmd as new SQLCommand("insert into events values('" & desc.text & "','" & DateTime.Parse(eventdate.text) & "')",con)

con.open()

cmd.executenonquery()

con.close()
clearform()
display()

end sub

sub display()

dim con as new SQLConnection("server=london-home; Database=tony; uid=rashid2; pwd=test; ")
dim cmd as new SQLCommand("select * from events",con)

con.open()

dim SDR as SQLDataReader
SDR = cmd.ExecuteReader()


if SDR.HasRows = true then
ViewEvents.DataSource = SDR
ViewEvents.DataBind()

end if

con.close()


end sub

sub clearform()
eventdate.text=""
desc.text=""

end sub

sub Reset(sender as object, e as EventArgs)
clearform()

end sub
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>


<style type="text/css">

#display_prod{float:left;}

#prod{margin-top:0;}

</style>

</head>
<body>

<table border="1" align="center" cellpadding="5" cellspacing="0">
<tr>
<td valign="top">

<form id="prod" runat="server" enctype="multipart/form-data">
<table id="display_prod" width="554" border="1" cellspacing="0" cellpadding="0" align="left" height="400">
<tr valign="top" height="20">
<td> </td>
<td>
<table border="1" align="center" cellpadding="10" cellspacing="0">
<tr>
<td>PRODUCTS DESCRIPTION <asp:Label runat="server" ID="lbl"></asp:Label></td>
</tr>
</table>
</td>
</tr>

<tr valign="top">
<td>
<table align="left" id="menu" width="150" border="1" cellpadding="0" cellspacing="0">
<tr align="center">

<td><a href="http://links.10026.com/?link=home.aspx">Main</a></td>
</tr>
<tr align="center">
<td><a href="http://links.10026.com/?link=Events.aspx">Change Password</a></td>
</tr>
<tr align="center">
<td><a href="http://links.10026.com/?link=events.aspx">Events</a></td>
</tr>
</table>
</td>

<td align="left">
<table width="390" border="1" cellpadding="0" cellspacing="0">
<tr>
<td width="100" >Enter Date :</td>
<td width="243" ><asp:TextBox runat="server" ID="eventdate" BackColor="#FFFFFF" Width="100"></asp:TextBox> mm/dd/yy</td>
</tr>
<tr>
<td>Description :</td>
<td><asp:TextBox runat="server" ID="desc" TextMode="MultiLine" Columns="20" Rows="3" BackColor="#FFFFFF" MaxLength="200"></asp:TextBox> max 200</td>
</tr>
<tr>
<td> </td>
<td>
<asp:Button runat="server" ID="btnsubmit" Text="Save" OnClick="SaveEvent"></asp:Button>
<asp:Button runat="server" ID="btnreset" Text="Reset"></asp:Button>
</td>
</tr>
</table>

<table width="390" border="1" align="left" cellpadding="0" cellspacing="0">
<tr>
<td>
<asp:DataGrid runat="server" ID="ViewEvents" AutoGenerateColumns="false">
<columns>
<asp:TemplateColumn>
<itemtemplate>
<table id="tt" border="1" cellpadding="5" cellspacing="0">
<tr>
<td><asp:Label runat="server" ID="eventname" Text='<%#container.DataItem("event_name")%>' ></asp:Label></td>

<td><asp:Label runat="server" ID="pdesc" Text='<%# trim(DataBinder.Eval(Container.DataItem,"event_date","{0:MM/dd/yyyy}")) %>' ></asp:Label></td>
</tr>
</table>
</itemtemplate>
</asp:TemplateColumn>
</columns>
</asp:DataGrid>
</td>
</tr>
</table>
</td>
</tr>
</table>

</form>


</td>
</tr>
</table>
</body>
</html>

it will happend.. on think you can do after successfully save the data you can redirect the page with same URL.

|||

Thanks its solved now!

Here is a structure of my table

create table events
(
event_id integer primary key identity(1,1),
event_name varchar(200),
event_date datetime,
)

Note: The event_id is a automacally field and we dont need to insert record in this.

for example

i have inserted 10 records in a table

then i deleted all records manually from sql server

but when again I save the record the event_id field shows started from 11 even the table was empty, it should be start from 1?

Thanks for the co-operation

Monday, February 20, 2012

Problem with parenthesis in the default value of a column

I am having a problem with the format of default values in SQL Server 2005.
I
am using a legacy application that is not expecting the double parenthesis
around the default value that SQL Server adds. For instance, if
I set 0 (zero) as the default value for a column of type int as follows
ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
Class)
then SQL Server will set the default value as ((0)). The problem is that the
legacy application is validating the default values and expecting just 0. I
own the database but have no control over the application. Therefore, I
cannot change the application to remove the parenthesis after reading the
value.
Is there any way to force SQL Server to store the default value without the
parenthesis or to return it without the parenthesis (note that the
application is reading the database tables directly).
Regards,
ArturHi
I don't think you can change the way it is stored but you could use the
REPLACE function to strip out the braces when you return it.
John
"artur" wrote:

> I am having a problem with the format of default values in SQL Server 2005
. I
> am using a legacy application that is not expecting the double parenthesis
> around the default value that SQL Server adds. For instance, if
> I set 0 (zero) as the default value for a column of type int as follows
> ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
> Class)
> then SQL Server will set the default value as ((0)). The problem is that t
he
> legacy application is validating the default values and expecting just 0.
I
> own the database but have no control over the application. Therefore, I
> cannot change the application to remove the parenthesis after reading the
> value.
> Is there any way to force SQL Server to store the default value without th
e
> parenthesis or to return it without the parenthesis (note that the
> application is reading the database tables directly).
> Regards,
> Artur

Problem with parenthesis in the default value of a column

I am having a problem with the format of default values in SQL Server 2005. I
am using a legacy application that is not expecting the double parenthesis
around the default value that SQL Server adds. For instance, if
I set 0 (zero) as the default value for a column of type int as follows
ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
Class)
then SQL Server will set the default value as ((0)). The problem is that the
legacy application is validating the default values and expecting just 0. I
own the database but have no control over the application. Therefore, I
cannot change the application to remove the parenthesis after reading the
value.
Is there any way to force SQL Server to store the default value without the
parenthesis or to return it without the parenthesis (note that the
application is reading the database tables directly).
Regards,
ArturHi
I don't think you can change the way it is stored but you could use the
REPLACE function to strip out the braces when you return it.
John
"artur" wrote:
> I am having a problem with the format of default values in SQL Server 2005. I
> am using a legacy application that is not expecting the double parenthesis
> around the default value that SQL Server adds. For instance, if
> I set 0 (zero) as the default value for a column of type int as follows
> ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
> Class)
> then SQL Server will set the default value as ((0)). The problem is that the
> legacy application is validating the default values and expecting just 0. I
> own the database but have no control over the application. Therefore, I
> cannot change the application to remove the parenthesis after reading the
> value.
> Is there any way to force SQL Server to store the default value without the
> parenthesis or to return it without the parenthesis (note that the
> application is reading the database tables directly).
> Regards,
> Artur

Problem with Parameters

I am writing a Data Processing Extension for Reporting Services and am
having a problem getting the values of parameters I pass in the query string
in ReportDesigner.
I have implemented the IDbCommandAnalysis GetParameters() method to parse
the query string in ReportDesigner. When I enter
"&CompanyId=1000&EmployeeId=2000" in the "Query string" field,
GetParameters() returns a local collection with my two parameters and their
values.
Upon command execution, the IDataParameterCollection IDbCommand.Parameters
property getter is called twice before calling ExecuteReader() and return my
member parameter collection. When ExecuteReader() is called, my member
parameter collection contains the two parameters of the correct name but
with values of null.
Because the parameters with correct names are added to the collection, it
seems that ReportDesigner saved the parameter collection I returned from
GetParameters() but what happened to the parameter values?
Since the values are null, ReportDesigner pops a window for the values to be
input. I don't want this to happen after the report is deployed but cannot
find a way to simulate the parameter name-value pairs being submitted on the
URL string.
What am I missing?
Thanks,
TomHi Tom,
Welcome to use MSDN Managd Newsgroup!
I am looking into this issue and will update you as soon as possible. I
have also noticed you have another two duplicated posts. To ensure the
integrity of newsgroup, I will answer your questions and follow up issue in
this thread.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
Thanks for your reply. I wanted to pass along some additional information
about the parameter problem I'm having.
I published my report and fount the parameters are passed to the report when
added to the URL. That's good, but it points out a big disparity between
parameter handling in ReportDesigner and in published reports.
I look forward to learning what you discover about the correct way to handle
parameters.
Thanks,
Tom
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:WJlr33TeFHA.4856@.TK2MSFTNGXA01.phx.gbl...
> Hi Tom,
> Welcome to use MSDN Managd Newsgroup!
> I am looking into this issue and will update you as soon as possible. I
> have also noticed you have another two duplicated posts. To ensure the
> integrity of newsgroup, I will answer your questions and follow up issue
> in
> this thread.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Tom,
Sorry for the delay, would you please provide me a sample project? I
understand the information may be sensitive to you, my direct email address
is v-mingqc@.online.microsoft.com (please REMOVE "online" before you click
SEND as it's ONLY for SPAM), you may send the file to me directly and I
will keep secure.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Michael,
I am having the same problem. Did you ever figure this out for Tom?
I am trying to run a report by passing in the parameter through the url.
The report comes up but prompts for the parameter instead of taking it
through url. If I enter it at the prompt, the report runs fine. My url looks
like:
http://server-1/Reports/Pages/Report.aspx?ItemPath=%2fMarkListingbyStudent%2fMarkListingbyStudent&rs:Command=Render&calendarID=155
It's internal though.
Thank you for your help!
Sharlyn
"Michael Cheng [MSFT]" wrote:
> Hi Tom,
> Sorry for the delay, would you please provide me a sample project? I
> understand the information may be sensitive to you, my direct email address
> is v-mingqc@.online.microsoft.com (please REMOVE "online" before you click
> SEND as it's ONLY for SPAM), you may send the file to me directly and I
> will keep secure.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>

Problem with Parameterized reports - changing default values

Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
1. When I run CRM3 parameterized reports using the RS Report Manager
interface i get error messages like " The 'CRM_Locale' parameter is missing
a value " or "The 'CRM_Locale' parameter is missing a value", however when
these reports are run from the CRM application or ReportServer URL interface
the reports work fine.
2. I inadvertantly changed one of the report parameter default values in
one of the reports to blank, how can i change it back to its original setting
"Override Default". The report now prints with a blank Prepared By: User Name
value.
Any suggestions would be greatly appreciated
GsBring the report up in the web front end- report manager. Go to the
properties and you can change the default parameters.
Doing so from the Development IDE will NOT change the parameters once the
report has been deployed.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Graham Smith" wrote:
> Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> 1. When I run CRM3 parameterized reports using the RS Report Manager
> interface i get error messages like " The 'CRM_Locale' parameter is missing
> a value " or "The 'CRM_Locale' parameter is missing a value", however when
> these reports are run from the CRM application or ReportServer URL interface
> the reports work fine.
> 2. I inadvertantly changed one of the report parameter default values in
> one of the reports to blank, how can i change it back to its original setting
> "Override Default". The report now prints with a blank Prepared By: User Name
> value.
> Any suggestions would be greatly appreciated
> Gs|||Hi Wayne
> Bring the report up in the web front end- report manager. Go to the
> properties and you can change the default parameters.
>
Tried that, No Joy!
> > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > these reports are run from the CRM application or ReportServer URL interface
> > the reports work fine.
The reports do not render at all. It appears that these parameter values are
not being populated are missing when reports are run using the Report Manager
Interface. Reports without parameters run OK.
> 2. I inadvertantly changed one of the report parameter default values in
> one of the reports to blank, how can i change it back to its original setting
> "Override Default". The report now prints with a blank Prepared By: User Name
> value.
>
I cannot change this back to its original state. The report is CRM 3.0
standard Report "User Summary" the parameter is "CRM_FullName" It originally
had a
"Override Default" command button in the default value for this parameter.
When the reports are run from the CRM 3.0 web interface ot the ReportServer
URL web interface the report renders ok but without the value for the User
Name.
regards
GS
"Wayne Snyder" wrote:
> Bring the report up in the web front end- report manager. Go to the
> properties and you can change the default parameters.
> Doing so from the Development IDE will NOT change the parameters once the
> report has been deployed.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Graham Smith" wrote:
> > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> >
> > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > these reports are run from the CRM application or ReportServer URL interface
> > the reports work fine.
> >
> > 2. I inadvertantly changed one of the report parameter default values in
> > one of the reports to blank, how can i change it back to its original setting
> > "Override Default". The report now prints with a blank Prepared By: User Name
> > value.
> >
> > Any suggestions would be greatly appreciated
> > Gs|||What means "No Joy" exact.
Any error message after clicking "Apply" in the properties page?
You need to go to properties after open the report and don't click on execute.
"Graham Smith" wrote:
> Hi Wayne
> > Bring the report up in the web front end- report manager. Go to the
> > properties and you can change the default parameters.
> >
> Tried that, No Joy!
> > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > these reports are run from the CRM application or ReportServer URL interface
> > > the reports work fine.
> The reports do not render at all. It appears that these parameter values are
> not being populated are missing when reports are run using the Report Manager
> Interface. Reports without parameters run OK.
> >
> > 2. I inadvertantly changed one of the report parameter default values in
> > one of the reports to blank, how can i change it back to its original setting
> > "Override Default". The report now prints with a blank Prepared By: User Name
> > value.
> >
> I cannot change this back to its original state. The report is CRM 3.0
> standard Report "User Summary" the parameter is "CRM_FullName" It originally
> had a
> "Override Default" command button in the default value for this parameter.
> When the reports are run from the CRM 3.0 web interface ot the ReportServer
> URL web interface the report renders ok but without the value for the User
> Name.
> regards
> GS
>
> "Wayne Snyder" wrote:
> > Bring the report up in the web front end- report manager. Go to the
> > properties and you can change the default parameters.
> >
> > Doing so from the Development IDE will NOT change the parameters once the
> > report has been deployed.
> > --
> > Wayne Snyder MCDBA, SQL Server MVP
> > Mariner, Charlotte, NC
> >
> > I support the Professional Association for SQL Server ( PASS) and it''s
> > community of SQL Professionals.
> >
> >
> > "Graham Smith" wrote:
> >
> > > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> > >
> > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > these reports are run from the CRM application or ReportServer URL interface
> > > the reports work fine.
> > >
> > > 2. I inadvertantly changed one of the report parameter default values in
> > > one of the reports to blank, how can i change it back to its original setting
> > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > value.
> > >
> > > Any suggestions would be greatly appreciated
> > > Gs|||Hi
No Joy means it did not work!
There were no error messages after hitting the "Apply" button in the
Properties.
I cant set the Default Value back to its original setting, so the reports
being rendered by the CRM 3.0 Reports function and Reports Web browser URL
Interface are incorrect.
I can send screen shots of the problem if you can send an email alias to
reply to.
Thanks
GS
"Dev Main" wrote:
> What means "No Joy" exact.
> Any error message after clicking "Apply" in the properties page?
> You need to go to properties after open the report and don't click on execute.
> "Graham Smith" wrote:
> > Hi Wayne
> >
> > > Bring the report up in the web front end- report manager. Go to the
> > > properties and you can change the default parameters.
> > >
> >
> > Tried that, No Joy!
> >
> > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > these reports are run from the CRM application or ReportServer URL interface
> > > > the reports work fine.
> >
> > The reports do not render at all. It appears that these parameter values are
> > not being populated are missing when reports are run using the Report Manager
> > Interface. Reports without parameters run OK.
> >
> > >
> > > 2. I inadvertantly changed one of the report parameter default values in
> > > one of the reports to blank, how can i change it back to its original setting
> > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > value.
> > >
> >
> > I cannot change this back to its original state. The report is CRM 3.0
> > standard Report "User Summary" the parameter is "CRM_FullName" It originally
> > had a
> > "Override Default" command button in the default value for this parameter.
> >
> > When the reports are run from the CRM 3.0 web interface ot the ReportServer
> > URL web interface the report renders ok but without the value for the User
> > Name.
> >
> > regards
> > GS
> >
> >
> >
> > "Wayne Snyder" wrote:
> >
> > > Bring the report up in the web front end- report manager. Go to the
> > > properties and you can change the default parameters.
> > >
> > > Doing so from the Development IDE will NOT change the parameters once the
> > > report has been deployed.
> > > --
> > > Wayne Snyder MCDBA, SQL Server MVP
> > > Mariner, Charlotte, NC
> > >
> > > I support the Professional Association for SQL Server ( PASS) and it''s
> > > community of SQL Professionals.
> > >
> > >
> > > "Graham Smith" wrote:
> > >
> > > > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> > > >
> > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > these reports are run from the CRM application or ReportServer URL interface
> > > > the reports work fine.
> > > >
> > > > 2. I inadvertantly changed one of the report parameter default values in
> > > > one of the reports to blank, how can i change it back to its original setting
> > > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > > value.
> > > >
> > > > Any suggestions would be greatly appreciated
> > > > Gs|||Sorry, I don't have the CRM Application.
But I had similare experience with parameters. If I changed parameter
settings on deployed report, the changes where not deployed.
Since this I allways need to change the parameter in the Report Manager
manuell after redeploy those reports. After manuell change of the parameters
I can see the changes in the parameter section of the report. And the URL
Access works again.
Whats a bit difficult is the way "allow null" settings are displayed in the
report manager. If I like to allow null I need to activate "have dafault" and
the value field must be empty. In the designer it is required to set Default
values to non and activate "Allow Null" and "Allow blank".
I assume if you apply the correct settings in the report manager for the
parameter, the url access must work.
"Graham Smith" wrote:
> Hi
> No Joy means it did not work!
> There were no error messages after hitting the "Apply" button in the
> Properties.
> I cant set the Default Value back to its original setting, so the reports
> being rendered by the CRM 3.0 Reports function and Reports Web browser URL
> Interface are incorrect.
> I can send screen shots of the problem if you can send an email alias to
> reply to.
> Thanks
> GS
>
>
> "Dev Main" wrote:
> > What means "No Joy" exact.
> >
> > Any error message after clicking "Apply" in the properties page?
> >
> > You need to go to properties after open the report and don't click on execute.
> >
> > "Graham Smith" wrote:
> >
> > > Hi Wayne
> > >
> > > > Bring the report up in the web front end- report manager. Go to the
> > > > properties and you can change the default parameters.
> > > >
> > >
> > > Tried that, No Joy!
> > >
> > > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > > these reports are run from the CRM application or ReportServer URL interface
> > > > > the reports work fine.
> > >
> > > The reports do not render at all. It appears that these parameter values are
> > > not being populated are missing when reports are run using the Report Manager
> > > Interface. Reports without parameters run OK.
> > >
> > > >
> > > > 2. I inadvertantly changed one of the report parameter default values in
> > > > one of the reports to blank, how can i change it back to its original setting
> > > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > > value.
> > > >
> > >
> > > I cannot change this back to its original state. The report is CRM 3.0
> > > standard Report "User Summary" the parameter is "CRM_FullName" It originally
> > > had a
> > > "Override Default" command button in the default value for this parameter.
> > >
> > > When the reports are run from the CRM 3.0 web interface ot the ReportServer
> > > URL web interface the report renders ok but without the value for the User
> > > Name.
> > >
> > > regards
> > > GS
> > >
> > >
> > >
> > > "Wayne Snyder" wrote:
> > >
> > > > Bring the report up in the web front end- report manager. Go to the
> > > > properties and you can change the default parameters.
> > > >
> > > > Doing so from the Development IDE will NOT change the parameters once the
> > > > report has been deployed.
> > > > --
> > > > Wayne Snyder MCDBA, SQL Server MVP
> > > > Mariner, Charlotte, NC
> > > >
> > > > I support the Professional Association for SQL Server ( PASS) and it''s
> > > > community of SQL Professionals.
> > > >
> > > >
> > > > "Graham Smith" wrote:
> > > >
> > > > > Hi I have two issues: I have recently migrated SSRS2000 to SSRS2005.
> > > > >
> > > > > 1. When I run CRM3 parameterized reports using the RS Report Manager
> > > > > interface i get error messages like " The 'CRM_Locale' parameter is missing
> > > > > a value " or "The 'CRM_Locale' parameter is missing a value", however when
> > > > > these reports are run from the CRM application or ReportServer URL interface
> > > > > the reports work fine.
> > > > >
> > > > > 2. I inadvertantly changed one of the report parameter default values in
> > > > > one of the reports to blank, how can i change it back to its original setting
> > > > > "Override Default". The report now prints with a blank Prepared By: User Name
> > > > > value.
> > > > >
> > > > > Any suggestions would be greatly appreciated
> > > > > Gs

Problem with page breaks

Hello All,

I face the following problem.

I have a report with a list and under that list i have a table and a chart that sums the values from the dataset.

In my list properties i have set that their should be a page break after the list (meaning that my table and chart

should start a new page).

In my listgroup properties is hava also set a page break after each group.

Now when i preview my report in visual studio, the groupdetails are displayed in one page and the summed table and chart are on a second page. when i do a print preview, i get 3 pages but my sum table is on the second page under the last groupdetail and the chart is 3rth page.

When i publish my report on the reportserver and i do the same through my browser, i get the same error.

Why doesn"t the page break after each group doesn't work ?

Why does the page break after the list doesn't work ? is this a bug ?

Is there a way to set a pagebreak manually on the report for example in a hidden textbox ?

Any suggestions, tricks, help .....

Vinnie

Hello again ...

It seems i always find the answer to my proper questions .. but always when i have posted an item on this forum.

So i solved my problem based on the following article :

http://www.eggheadcafe.com/articles/20040823.asp

I draw a rectangle under my list and but my table and chart in this rectangle.

Then i set the PageBreakAtStart property to True.

This did the trick .... Isn't it simple ... if you know how to .....

Greetings

Vinnie

|||Hi Vinnie,

Thanks for this, I was also facing the same problem and tried exactly the same but this worked only first time. In the subsequent iterations of this report the rectangles start overlapping each other and I can't figure out why.
Do you have any suggestion for this?

Thanks
Anshu
anshu.bathla@.gmail.com

problem with OUTPUT + returned recordset

I have written a stored procedure which contains a Select that returns a
recordset,
and returns a pair of OUTPUT values. The Recordset returned is correct, but
I cannot access the OUTPUT args until I close the recordset. I've seen
reference to this in SQL 7, but not for SQL 2000. I need the open recordset
and the return values at the same time. I've tried changing the Recordset
properties from adUseServer to adUseClient etc with no success.
Thanks for any help,
Jack
The following is both sp & vb code to execute.
CREATE PROCEDURE Select_LatestTimeSlice
(@.dataID [int],
@.TS_ID [int] OUTPUT,
@.RowCnt [int] OUTPUT)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.TSID int
DECLARE @.numcnt int
-- get TSID(s) for this dataID value; and recordcount
Select @.TSID=TS_ID, @.numcnt = COUNT(TS_ID) FROM TimeSlices
WHERE DATAID= @.dataID GROUP BY TS_ID
-- get recordset containing all rows where DATAID = this dataID
SELECT * FROM TimeSlices WHERE DATEID= @.dataID
SET @.RowCnt = @.numcnt
SET @.TS_ID = @.TSID
END
GO
=========================
Dim rsdata As ADODB.Recordset
Set rsdata = New ADODB.Recordset
rsdata.CursorLocation = ad_UseServer 'ad_UseClient
rsdata.CursorType = ad_OpenStatic 'ad_OpenDynamic
rsdata.LockType = adLockReadOnly 'adLockOptimistic
'
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cn
.CommandText = "Select_LatestTimeSlice"
.CommandType = adCmdStoredProc
'
.Parameters("@.dataID") = varDataID
'
' Pull the Trigger......
Set rsdata = .Execute() ' , , adExecuteNoRecords
'----
' when enabled these lines return NULL and I cannot get values
later
'Debug.Print Format(.Parameters("@.TS_ID"))
'Debug.Print Format(.Parameters("@.RowCnt"))
End With
'
With rsdata
' recordset data is correct
Debug.Print Format(.Fields("DATAID")) & " " &
Format(.Fields("TS_ID"))
rsdata.Close
Debug.Print Format(cmd.Parameters("@.TS_ID"))
Debug.Print Format(cmd.Parameters("@.RowCnt"))
End WithThat is the way sql server works. it sends output parameters and return valu
e
in the last packet it returns to the client. See "Parameters Markers" in BOL
.
You have to process or cancel all result sets returned by the stored
procedure before you have access to the return code and output parameter
values.
Instead using the execute method of the command, use the command as the
source of the recordset open method.
Example:
use northwind
go
create procedure dbo.usp_p1
@.sd datetime,
@.ed datetime,
@.rowcnt int output
as
set nocount on
declare @.error int
select
orderid,
customerid,
orderdate
from
dbo.orders
where
orderdate >= convert(varchar(8), coalesce(@.sd, getdate()), 112)
and orderdate < dateadd(day, 1, convert(varchar(8), coalesce(@.ed,
getdate()), 112))
select @.error = @.@.error, @.rowcnt = @.@.rowcount
return @.error
go
-- vb6
Private Sub Command1_Click()
Dim objConn As ADODB.Connection
Dim objCmd As ADODB.Command
Dim objRs As ADODB.Recordset
Set objConn = New ADODB.Connection
Set objCmd = New ADODB.Command
Set objRs = New ADODB.Recordset
With objConn
.ConnectionString =
"provider=sqloledb;server=weg-256;database=northwind;integrated security=SSP
I"
.Errors.Clear
.Open
End With
With objCmd
.CommandText = "dbo.usp_p1"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("@.return_value", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("@.sd", adVarChar, adParamInput,
8, "19970701")
.Parameters.Append .CreateParameter("@.ed", adVarChar, adParamInput,
8, "19970731")
.Parameters.Append .CreateParameter("@.rowcnt", adInteger,
adParamOutput)
.ActiveConnection = objConn
End With
With objRs
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
End With
objRs.Open objCmd
MsgBox objRs.Fields(0) & " - " & objRs.Fields(1) & " - " & objRs.Fields(2)
MsgBox objCmd.Parameters("@.rowcnt").Value
objRs.Close
objConn.Close
Set objConn = Nothing
Set objCmd = Nothing
Set objRs = Nothing
End Sub
AMB
"hushtech" wrote:

> I have written a stored procedure which contains a Select that returns a
> recordset,
> and returns a pair of OUTPUT values. The Recordset returned is correct, b
ut
> I cannot access the OUTPUT args until I close the recordset. I've seen
> reference to this in SQL 7, but not for SQL 2000. I need the open records
et
> and the return values at the same time. I've tried changing the Recordset
> properties from adUseServer to adUseClient etc with no success.
> Thanks for any help,
> Jack
> The following is both sp & vb code to execute.
> CREATE PROCEDURE Select_LatestTimeSlice
> (@.dataID [int],
> @.TS_ID [int] OUTPUT,
> @.RowCnt [int] OUTPUT)
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.TSID int
> DECLARE @.numcnt int
> -- get TSID(s) for this dataID value; and recordcount
> Select @.TSID=TS_ID, @.numcnt = COUNT(TS_ID) FROM TimeSlices
> WHERE DATAID= @.dataID GROUP BY TS_ID
> -- get recordset containing all rows where DATAID = this dataID
> SELECT * FROM TimeSlices WHERE DATEID= @.dataID
> SET @.RowCnt = @.numcnt
> SET @.TS_ID = @.TSID
> END
> GO
> =========================
> Dim rsdata As ADODB.Recordset
> Set rsdata = New ADODB.Recordset
> rsdata.CursorLocation = ad_UseServer 'ad_UseClient
> rsdata.CursorType = ad_OpenStatic 'ad_OpenDynamic
> rsdata.LockType = adLockReadOnly 'adLockOptimistic
> '
> Set cmd = New ADODB.Command
> With cmd
> .ActiveConnection = cn
> .CommandText = "Select_LatestTimeSlice"
> .CommandType = adCmdStoredProc
> '
> .Parameters("@.dataID") = varDataID
> '
> ' Pull the Trigger......
> Set rsdata = .Execute() ' , , adExecuteNoRecords
> '----
> ' when enabled these lines return NULL and I cannot get values
> later
> 'Debug.Print Format(.Parameters("@.TS_ID"))
> 'Debug.Print Format(.Parameters("@.RowCnt"))
> End With
> '
> With rsdata
> ' recordset data is correct
> Debug.Print Format(.Fields("DATAID")) & " " &
> Format(.Fields("TS_ID"))
> rsdata.Close
> Debug.Print Format(cmd.Parameters("@.TS_ID"))
> Debug.Print Format(cmd.Parameters("@.RowCnt"))
> End With
>|||You could "forget" about using Output parameters and return the values as a
Recordset.
Select TS_ID, COUNT(TS_ID) as rowCnt FROM TimeSlices
WHERE DATAID= @.dataID GROUP BY TS_ID
...and then use the nextRecordset method in your page.
Or keep the method you are using but use getRows to "transfer" your
recordset into an array.
Then close the recordset and access the Output parameters.
"hushtech" <hushtech@.discussions.microsoft.com> wrote in message
news:377AB4EE-033A-4A6A-A87C-5DD8AD355556@.microsoft.com...
>I have written a stored procedure which contains a Select that returns a
> recordset,
> and returns a pair of OUTPUT values. The Recordset returned is correct,
> but
> I cannot access the OUTPUT args until I close the recordset. I've seen
> reference to this in SQL 7, but not for SQL 2000. I need the open
> recordset
> and the return values at the same time. I've tried changing the Recordset
> properties from adUseServer to adUseClient etc with no success.
> Thanks for any help,
> Jack
> The following is both sp & vb code to execute.
> CREATE PROCEDURE Select_LatestTimeSlice
> (@.dataID [int],
> @.TS_ID [int] OUTPUT,
> @.RowCnt [int] OUTPUT)
> AS
> BEGIN
> SET NOCOUNT ON
> DECLARE @.TSID int
> DECLARE @.numcnt int
> -- get TSID(s) for this dataID value; and recordcount
> Select @.TSID=TS_ID, @.numcnt = COUNT(TS_ID) FROM TimeSlices
> WHERE DATAID= @.dataID GROUP BY TS_ID
> -- get recordset containing all rows where DATAID = this dataID
> SELECT * FROM TimeSlices WHERE DATEID= @.dataID
> SET @.RowCnt = @.numcnt
> SET @.TS_ID = @.TSID
> END
> GO
> =========================
> Dim rsdata As ADODB.Recordset
> Set rsdata = New ADODB.Recordset
> rsdata.CursorLocation = ad_UseServer 'ad_UseClient
> rsdata.CursorType = ad_OpenStatic 'ad_OpenDynamic
> rsdata.LockType = adLockReadOnly 'adLockOptimistic
> '
> Set cmd = New ADODB.Command
> With cmd
> .ActiveConnection = cn
> .CommandText = "Select_LatestTimeSlice"
> .CommandType = adCmdStoredProc
> '
> .Parameters("@.dataID") = varDataID
> '
> ' Pull the Trigger......
> Set rsdata = .Execute() ' , , adExecuteNoRecords
> '----
> ' when enabled these lines return NULL and I cannot get values
> later
> 'Debug.Print Format(.Parameters("@.TS_ID"))
> 'Debug.Print Format(.Parameters("@.RowCnt"))
> End With
> '
> With rsdata
> ' recordset data is correct
> Debug.Print Format(.Fields("DATAID")) & " " &
> Format(.Fields("TS_ID"))
> rsdata.Close
> Debug.Print Format(cmd.Parameters("@.TS_ID"))
> Debug.Print Format(cmd.Parameters("@.RowCnt"))
> End With
>|||Alejandro,
Thanks for the solution to my problem. I've implemented it successfully.
You referred me to "Parameters Markers" in BOL. I'm not familiar with what
BOL is and how to find it. Please give me a pointer if you can.
Thanks again for the help. I'm really happy with how quickly responses are
given on this forum - and how accurate and helpful they are.
-- jack
"Alejandro Mesa" wrote:
> That is the way sql server works. it sends output parameters and return va
lue
> in the last packet it returns to the client. See "Parameters Markers" in B
OL.
> You have to process or cancel all result sets returned by the stored
> procedure before you have access to the return code and output parameter
> values.
> Instead using the execute method of the command, use the command as the
> source of the recordset open method.
> Example:
> use northwind
> go
> create procedure dbo.usp_p1
> @.sd datetime,
> @.ed datetime,
> @.rowcnt int output
> as
> set nocount on
> declare @.error int
> select
> orderid,
> customerid,
> orderdate
> from
> dbo.orders
> where
> orderdate >= convert(varchar(8), coalesce(@.sd, getdate()), 112)
> and orderdate < dateadd(day, 1, convert(varchar(8), coalesce(@.ed,
> getdate()), 112))
> select @.error = @.@.error, @.rowcnt = @.@.rowcount
> return @.error
> go
> -- vb6
> Private Sub Command1_Click()
> Dim objConn As ADODB.Connection
> Dim objCmd As ADODB.Command
> Dim objRs As ADODB.Recordset
> Set objConn = New ADODB.Connection
> Set objCmd = New ADODB.Command
> Set objRs = New ADODB.Recordset
> With objConn
> .ConnectionString =
> "provider=sqloledb;server=weg-256;database=northwind;integrated security=S
SPI"
> .Errors.Clear
> .Open
> End With
> With objCmd
> .CommandText = "dbo.usp_p1"
> .CommandType = adCmdStoredProc
> .Parameters.Append .CreateParameter("@.return_value", adInteger,
> adParamReturnValue)
> .Parameters.Append .CreateParameter("@.sd", adVarChar, adParamInput
,
> 8, "19970701")
> .Parameters.Append .CreateParameter("@.ed", adVarChar, adParamInput
,
> 8, "19970731")
> .Parameters.Append .CreateParameter("@.rowcnt", adInteger,
> adParamOutput)
> .ActiveConnection = objConn
> End With
> With objRs
> .CursorLocation = adUseClient
> .CursorType = adOpenStatic
> .LockType = adLockOptimistic
> End With
> objRs.Open objCmd
> MsgBox objRs.Fields(0) & " - " & objRs.Fields(1) & " - " & objRs.Field
s(2)
> MsgBox objCmd.Parameters("@.rowcnt").Value
> objRs.Close
> objConn.Close
> Set objConn = Nothing
> Set objCmd = Nothing
> Set objRs = Nothing
> End Sub
>
> AMB
> "hushtech" wrote:
>