Profile value + Sql Value, a login problem

Hi all. Quick question. I'm using VS2005, C#, aspx page.

I'm creating a Profile to store login and password. That part is working... I can call the values (and display them) using this code <%= Profile.login %> and <%=Profile.password %
Now I want to create a Grid View that will connect to the SQL db, see if the login and password value stored in the Profile match that of ones in the SQL db.

So if the profile is login: bob password: dog, the grid view will output all application ID numbers associated with the bob and dog. Here is the SQL code...trying to use the <%=Profile.login %> as a filter on the login and password doesn't seem to work...

Can anyone tell me what I'm doing wrong? How can I reference a value in the Profile within an SQL statement?

SELECT ApplicationStatus.Description, Customer.CustomerName, Application.ApplicationDate
FROM Application INNER JOIN
ApplicationStatus ON Application.ApplicationStatusID = ApplicationStatus.ApplicationStatusID INNER JOIN
Customer ON Application.ApplicationID = Customer.ApplicationID INNER JOIN
[User] ON Application.DealerId = [User].UserId
WHERE ([User].LoginId = '<%= Profile.login %>') AND ([User].LoginPwd = '<%= Profile.password %>') AND (ApplicationStatus.Description = 'Pending')

Try getting the value into a variable and use the variable in the SQL.

SELECT ApplicationStatus.Description, Customer.CustomerName, Application.ApplicationDate
FROM Application INNER JOIN
ApplicationStatus ON Application.ApplicationStatusID = ApplicationStatus.ApplicationStatusID INNER JOIN
Customer ON Application.ApplicationID = Customer.ApplicationID INNER JOIN
[User] ON Application.DealerId = [User].UserId
WHERE ([User].LoginId = @.login) AND ([User].LoginPwd = @.pwd) AND (ApplicationStatus.Description = 'Pending')

Then add the parameters to the command object and set their values appropriately.

|||I agree with ndinakar.

Processing cubes in Analysis Services 2000

I'm working with Analysis Services 2000 (SP4) on a 64 bit Windows Server 2003 where I have installed the framework 2.0.
I need to write an application to process Analysis Services 2000 cubes but I can't use DSO because there isn't a 64 bit version available of this library.
Can I use AMO (Analysis Management Objects) for administering Analysis Services 2000? Is there another solution?
Thanks in advance
Daniele

Unfortunatlely you cannot.
Your only option is to find a 32-bit workstation and run your DSO application from there. This is a restriction of AS2K. Sorry.

_-_-_ Dave

Processing cubes in Analysis Services 2000

I'm working with Analysis Services 2000 (SP4) on a 64 bit Windows Server 2003 where I have installed the framework 2.0.
I need to write an application to process Analysis Services 2000 cubes but I can't use DSO because there isn't a 64 bit version available of this library.
Can I use AMO (Analysis Management Objects) for administering Analysis Services 2000? Is there another solution?
Thanks in advance
DanieleUnfortunately you cannot use AMO against Analysis Services 2000.
You can use DSO to remotely process Analysis Services object from another 32bit machine.
By the way you can use separate Redistributable component to install DSO
http://www.microsoft.com/downloads/details.aspx?familyid=d09c1d60-a13c-4479-9b91-9e8b9d835cdc&displaylang=en
Look for
"Microsoft SQL Server 2005 Backward Compatibility Components "

Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Process could note deliver updates (s) at the publisher

I have created a publisher with row filtering using merge replication .I configured 5-6 subscribers for it.It was working fine for 2 months.But now for two subscribers its showing an error Process could not deliver update (s) at the publisher

Ive checked the job history..its showing

Merge process encountered an unexpected network error. The connection to tublisher 'Publishername' is no longer available

can any one help me?

thanks and regards

Dhanya

Sounds like a connection issue. Does it happen intermittently or persistently? If persistently, you can try to use osql.exe to connect to server "Publishername" from the machine that merge process is launched.

Thanks,

Peng

|||

Ive increased the query time out both at the publisher and subscriber...

now i am getting another message "The process is running and is wainting for a response from one of the backend connections"

Process could note deliver updates (s) at the publisher

I have created a publisher with row filtering using merge replication .I configured 5-6 subscribers for it.It was working fine for 2 months.But now for two subscribers its showing an error Process could not deliver update (s) at the publisher

Ive checked the job history..its showing

Merge process encountered an unexpected network error. The connection to tublisher 'Publishername' is no longer available

can any one help me?

thanks and regards

Dhanya

Sounds like a connection issue. Does it happen intermittently or persistently? If persistently, you can try to use osql.exe to connect to server "Publishername" from the machine that merge process is launched.

Thanks,

Peng

|||

Ive increased the query time out both at the publisher and subscriber...

now i am getting another message "The process is running and is wainting for a response from one of the backend connections"

Procedure/Cursor question about returning results

I'm working on a procedure that needs to cycle through the records of some raw data and combine the the current record with the datetime field of the prior record. I have been able to write a script to do that with cursors and variables but my problem is it returns each record separately. How do I go about getting the procedure to return all the records as one set of data?

To see what I mean, the following script for the Pubs DB returns each pass through the data as a seperate query. Since I can't do a select *, what approach should I take? If you want the actual script, I would be happy to provide it.

DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor

WHILE @.@.FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end

Close Authors_cursor
deallocate authors_cursor

Thanks in advance

Tony Murunion

If you only need the previous value, the best thing would be to store the previous data in a local variable. I think that is a common approach.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for the reply

The actual script I was working with used local variables to get the data I wanted. I was just stuck with getting the results back in one set instead of one for each record.

I was able to resolve my problem by dumping the data into a table in a function (decided to try that instead of a proc)

Tony

Procedure/cursor question about returning results

I'm working on a procedure that needs to cycle through the records of some
raw data and combine the the current record with the datetime field of the
prior record. I have been able to write a script to do that with cursors
and variables but my problem is it returns each record separately. How do I
go about getting the procedure to return all the records as one set of data?
To see what I mean, the following script for the Pubs DB returns each pass
through the data as a seperate query. Since I can't do a select *, what
approach should I take? If you want the actual script I have so far, I
would be happy to provide it.
DECLARE authors_cursor CURSOR
FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
WHILE @.@.FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end
Close Authors_cursor
deallocate authors_cursor
Thanks in advance
Tony MurunionTony Murnion (remove) wrote:
> I'm working on a procedure that needs to cycle through the records of some
> raw data and combine the the current record with the datetime field of the
> prior record. I have been able to write a script to do that with cursors
> and variables but my problem is it returns each record separately. How do
I
> go about getting the procedure to return all the records as one set of dat
a?
> To see what I mean, the following script for the Pubs DB returns each pass
> through the data as a seperate query. Since I can't do a select *, what
> approach should I take? If you want the actual script I have so far, I
> would be happy to provide it.
> DECLARE authors_cursor CURSOR
> FOR SELECT * FROM authors
> OPEN authors_cursor
> FETCH NEXT FROM authors_cursor
> WHILE @.@.FETCH_STATUS = 0
> begin
> FETCH NEXT FROM authors_cursor
> end
> Close Authors_cursor
> deallocate authors_cursor
> Thanks in advance
> Tony Murunion
Your cursor wouldn't give predictable results anyway because you
haven't specified ORDER BY.
Cursors are rarely a good way to get results out of data. In this case
you can possibly use a query. To take another example from Pubs:
SELECT T1.title_id, T1.title,
T1.pubdate AS current_pubdate,
MAX(T2.pubdate) AS previous_pubdate
FROM titles AS T1
LEFT JOIN titles AS T2
ON T1.pubdate > T2.pubdate
GROUP BY T1.title, T1.title_id, T1.pubdate
ORDER BY current_pubdate, previous_pubdate ;
To do that with a cursor you could insert each row to a table variable
and then SELECT from the variable. Don't forget ORDER BY though!
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the reply. I just started looking into the tables option. I'm
fairly new to advanced script writing. I've done a lot of select and
updates over the years but most of my data manipulation\retrieval has been
through Crystal. In this current project, I need to do the manipulaton
before the ending result.
With my cursor testing, I do have the order by clause for just the reasons
you stated. The results I'm getting are valid - I just need them to come
over as one record set. The tables option may do that for me. I was also
just looking at the user definde functions. Since this database I am reading
will be generating a few thousand records a day, what do you think will
ultimately give me the best performance? The join example you gave seems
like it would bog down with larger volumes of data.
I did not mention before but this is on sql 2000
Thanks again.
Tony
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1149798283.293874.324730@.j55g2000cwa.googlegroups.com...
> Tony Murnion (remove) wrote:
> Your cursor wouldn't give predictable results anyway because you
> haven't specified ORDER BY.
> Cursors are rarely a good way to get results out of data. In this case
> you can possibly use a query. To take another example from Pubs:
> SELECT T1.title_id, T1.title,
> T1.pubdate AS current_pubdate,
> MAX(T2.pubdate) AS previous_pubdate
> FROM titles AS T1
> LEFT JOIN titles AS T2
> ON T1.pubdate > T2.pubdate
> GROUP BY T1.title, T1.title_id, T1.pubdate
> ORDER BY current_pubdate, previous_pubdate ;
> To do that with a cursor you could insert each row to a table variable
> and then SELECT from the variable. Don't forget ORDER BY though!
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

procedure, report parameters

(sql/rs v2K, VS .Net 2003)
I have a problem with several multi-dataset reports. I've got several
single-dataset reports working just fine, but these multi-datasets are
problematic. For example, I have one procedure which works w/out parameters,
just returns a statistics report. I have modified the proc to accept a
parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the 2nd
chunk, etc., all the way thru to @.ch=5. All of them together comprise the
whole report. The proc works just fine in query analyzer, like this:
exec procname 1
exec procname 2
exec procname 3
exec procname 4
exec procname 5
now, in RS, i have created 5 datasets. each with the fields returned by
each procedure invocation. mind you, these aren't actual fields, it's select
this as value, select this as count, select this as volume, etc.
the datasets each are Text command types, the query strings are simply
'procname 1', 'procname 2', etc.
all of them work just fine in the Data tab in VS.
in the layout tab, i've created several tables and dragged/dropped the
fields into each accordingly
in the preview tab, i've got data returned in the top two chunks. WOW! cuz
i have been trying this for a very long time, yesterday i had no data at all,
only column headings, i don't know what i changed, but, now i have data in
two chunks, none in the rest. only column headings.
please, can anybody provide some direction on the right way to wrap the
report(s) around procedures like this? remember, the procedures all all just
statisitcal analysis data...no user input is required. i just need to
deliver nicely via email on a scheduled basis.
-- LynnAnd, when I refresh the report (in Preview tab, VS), sometimes all 5 chunks
are there, as desired...but then, I refresh again, and two are blank. (just
the column headings, no data) then, refresh again, and a different two are
blank.
(by 'chunks', i just mean different sections of the report. this particular
report has five different sections...or 'chunks'. each chunk has a
corresponding dataset)
please advise. this is terribly urgent.
-- Lynn
"Lynn" wrote:
> (sql/rs v2K, VS .Net 2003)
> I have a problem with several multi-dataset reports. I've got several
> single-dataset reports working just fine, but these multi-datasets are
> problematic. For example, I have one procedure which works w/out parameters,
> just returns a statistics report. I have modified the proc to accept a
> parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the 2nd
> chunk, etc., all the way thru to @.ch=5. All of them together comprise the
> whole report. The proc works just fine in query analyzer, like this:
> exec procname 1
> exec procname 2
> exec procname 3
> exec procname 4
> exec procname 5
> now, in RS, i have created 5 datasets. each with the fields returned by
> each procedure invocation. mind you, these aren't actual fields, it's select
> this as value, select this as count, select this as volume, etc.
> the datasets each are Text command types, the query strings are simply
> 'procname 1', 'procname 2', etc.
> all of them work just fine in the Data tab in VS.
> in the layout tab, i've created several tables and dragged/dropped the
> fields into each accordingly
> in the preview tab, i've got data returned in the top two chunks. WOW! cuz
> i have been trying this for a very long time, yesterday i had no data at all,
> only column headings, i don't know what i changed, but, now i have data in
> two chunks, none in the rest. only column headings.
> please, can anybody provide some direction on the right way to wrap the
> report(s) around procedures like this? remember, the procedures all all just
> statisitcal analysis data...no user input is required. i just need to
> deliver nicely via email on a scheduled basis.
> -- Lynn|||You might just be seeing an artifact of the development environment. Report
designer caches data. Look where your .rdl file is and you will see a
.rdl.data file. Delete it and you will be guaranteed that it will query your
database.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:96659649-7A0D-488A-B6AD-C6BDD9A2651F@.microsoft.com...
> And, when I refresh the report (in Preview tab, VS), sometimes all 5
> chunks
> are there, as desired...but then, I refresh again, and two are blank.
> (just
> the column headings, no data) then, refresh again, and a different two
> are
> blank.
> (by 'chunks', i just mean different sections of the report. this
> particular
> report has five different sections...or 'chunks'. each chunk has a
> corresponding dataset)
> please advise. this is terribly urgent.
> -- Lynn
>
> "Lynn" wrote:
>> (sql/rs v2K, VS .Net 2003)
>> I have a problem with several multi-dataset reports. I've got several
>> single-dataset reports working just fine, but these multi-datasets are
>> problematic. For example, I have one procedure which works w/out
>> parameters,
>> just returns a statistics report. I have modified the proc to accept a
>> parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the
>> 2nd
>> chunk, etc., all the way thru to @.ch=5. All of them together comprise
>> the
>> whole report. The proc works just fine in query analyzer, like this:
>> exec procname 1
>> exec procname 2
>> exec procname 3
>> exec procname 4
>> exec procname 5
>> now, in RS, i have created 5 datasets. each with the fields returned by
>> each procedure invocation. mind you, these aren't actual fields, it's
>> select
>> this as value, select this as count, select this as volume, etc.
>> the datasets each are Text command types, the query strings are simply
>> 'procname 1', 'procname 2', etc.
>> all of them work just fine in the Data tab in VS.
>> in the layout tab, i've created several tables and dragged/dropped the
>> fields into each accordingly
>> in the preview tab, i've got data returned in the top two chunks. WOW!
>> cuz
>> i have been trying this for a very long time, yesterday i had no data at
>> all,
>> only column headings, i don't know what i changed, but, now i have data
>> in
>> two chunks, none in the rest. only column headings.
>> please, can anybody provide some direction on the right way to wrap the
>> report(s) around procedures like this? remember, the procedures all all
>> just
>> statisitcal analysis data...no user input is required. i just need to
>> deliver nicely via email on a scheduled basis.
>> -- Lynn|||Hi Bruce. There are no data files there at all. ( I wasn't able to get back
to you sooner, as I had a bit of a family emergency. ) I'm not sure how to
proceed with this. I am looking at the rpt right now through
http://server/reports, it is doing the same thing. First invocation, it had
all chunks of the rpt except for chunk 3. After a refresh, that chunk was
there, too. Two more refreshes, entire resultset is there fine, but then
after another refresh, chunk 4 is now missing/blank.
Another much less urgent question would be subscription scheduling. Can
those things vary at all? Meaning, how can i run it on all production
weekdays, instead of just all weekdays? you know...how do I correctly
account for the holidays?
Please advise.
-- Lynn
"Bruce L-C [MVP]" wrote:
> You might just be seeing an artifact of the development environment. Report
> designer caches data. Look where your .rdl file is and you will see a
> ..rdl.data file. Delete it and you will be guaranteed that it will query your
> database.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:96659649-7A0D-488A-B6AD-C6BDD9A2651F@.microsoft.com...
> > And, when I refresh the report (in Preview tab, VS), sometimes all 5
> > chunks
> > are there, as desired...but then, I refresh again, and two are blank.
> > (just
> > the column headings, no data) then, refresh again, and a different two
> > are
> > blank.
> >
> > (by 'chunks', i just mean different sections of the report. this
> > particular
> > report has five different sections...or 'chunks'. each chunk has a
> > corresponding dataset)
> >
> > please advise. this is terribly urgent.
> > -- Lynn
> >
> >
> > "Lynn" wrote:
> >
> >> (sql/rs v2K, VS .Net 2003)
> >> I have a problem with several multi-dataset reports. I've got several
> >> single-dataset reports working just fine, but these multi-datasets are
> >> problematic. For example, I have one procedure which works w/out
> >> parameters,
> >> just returns a statistics report. I have modified the proc to accept a
> >> parameter. If @.ch =1 , give me the first chunk, if @.ch = 2, give me the
> >> 2nd
> >> chunk, etc., all the way thru to @.ch=5. All of them together comprise
> >> the
> >> whole report. The proc works just fine in query analyzer, like this:
> >>
> >> exec procname 1
> >> exec procname 2
> >> exec procname 3
> >> exec procname 4
> >> exec procname 5
> >>
> >> now, in RS, i have created 5 datasets. each with the fields returned by
> >> each procedure invocation. mind you, these aren't actual fields, it's
> >> select
> >> this as value, select this as count, select this as volume, etc.
> >>
> >> the datasets each are Text command types, the query strings are simply
> >> 'procname 1', 'procname 2', etc.
> >>
> >> all of them work just fine in the Data tab in VS.
> >> in the layout tab, i've created several tables and dragged/dropped the
> >> fields into each accordingly
> >>
> >> in the preview tab, i've got data returned in the top two chunks. WOW!
> >> cuz
> >> i have been trying this for a very long time, yesterday i had no data at
> >> all,
> >> only column headings, i don't know what i changed, but, now i have data
> >> in
> >> two chunks, none in the rest. only column headings.
> >>
> >> please, can anybody provide some direction on the right way to wrap the
> >> report(s) around procedures like this? remember, the procedures all all
> >> just
> >> statisitcal analysis data...no user input is required. i just need to
> >> deliver nicely via email on a scheduled basis.
> >>
> >> -- Lynn
>
>