how to change query that runs report programmatically

I have a report that will display certain records based on the user that logs in. I need to be able to change the query the report uses programmatically. I havent had any luck trying to find how to do this online. If anyone can help me that would be great.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Please look into BOL for "Using Dynamic Queries"
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OtGrybSkEHA.2668@.TK2MSFTNGP10.phx.gbl...
>I have a report that will display certain records based on the user that
>logs in. I need to be able to change the query the report uses
>programmatically. I havent had any luck trying to find how to do this
>online. If anyone can help me that would be great.
>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.

How to Change AutoNumber Not Start From 1

I have a table with structure below :
Table_A
{
PGradeID int IDENTITY(1,1),
PName varchar(20)
}

If I delete all records on Table_A, and then fill some records,
PGradeID will no longer start from 1 anymore, everybody knows that. How
to change it so PGradeID will start from 1 after all records was
deleted?

Thanks b4

ResantLookup the DBCC CHECKIDENT command. Also, if you TRUNCATE rather than DELETE
then the IDENTITY will be reset to seed.

Why do you care what the IDENTITY value is? It's usually fatal to attach any
significance to the value assigned by IDENTITY. If you care about the value
then IDENTITY isn't the right solution.

--
David Portas
SQL Server MVP
--|||Reset identity value will not cause problem in my case, but I
appreciate your warning.
Thanks a lot, it's works!

How To Change All Records in a Single Field?

Hey guys,
I am in a bit of a pickle. I have to do a software training today,
and my training database is hosed. I have a production database, but
I do not want the users to actually see a customer=92s live data on the
screen. I want to make a global alternation to all records on a
single field such as change everyone=92s account number to a specific
value.
Is there a way to do this in SQL 2005?
Thanks!Create a view and show them the results of the view.
CREATE VIEW dbo.foo
AS
SELECT col1, col2, AccountNumber = 'foo', col3, ...
FROM dbo.Original_Table;
<alvinstraight38@.hotmail.com> wrote in message
news:d2e508c2-4c8e-4627-90dc-5b7e38dabb91@.e39g2000hsf.googlegroups.com...
Hey guys,
I am in a bit of a pickle. I have to do a software training today,
and my training database is hosed. I have a production database, but
I do not want the users to actually see a customer?s live data on the
screen. I want to make a global alternation to all records on a
single field such as change everyone?s account number to a specific
value.
Is there a way to do this in SQL 2005?
Thanks!|||UPDATE ThatTable
SET SomeColumn = 'NewValue'
Since there is no WHERE clause to limit the effect, every row in
ThatTable will be updated.
The problem with changing a customer's account number that way is that
it is likely to violate a UNIQUE constraint, as well as appearing in
many tables. It is likely to be used for JOINs.
Roy Harvey
Beacon Falls, CT
On Tue, 29 Apr 2008 06:18:42 -0700 (PDT),
"alvinstraight38@.hotmail.com" <alvinstraight38@.hotmail.com> wrote:
>Hey guys,
>I am in a bit of a pickle. I have to do a software training today,
>and my training database is hosed. I have a production database, but
>I do not want the users to actually see a customer?s live data on the
>screen. I want to make a global alternation to all records on a
>single field such as change everyone?s account number to a specific
>value.
>Is there a way to do this in SQL 2005?
>Thanks!

How to catch sql exceptions gracefully when deleting some records

I use the following function (in the BLL) to delete some records:

PublicFunction DeleteStep4Dashboards()AsBoolean

Try

adpDashboards.DeleteStep4Dashboards()

Catch exAs Exception

ReturnFalse

EndTry

ReturnTrue

EndFunction

How can I catch the sql database errors when deleting the records goes wrong.

You can add a Catch for a SqlException e.g.

Catch sqlExAs SqlException

|||

You can specify the type of the exception that you want to catch, e.g. Catch ex as SqlException (rather than as Exception).

However, catching exceptions and returning a value indicating success or failure is in general bad coding style. An exception means that something has gone wrong with what you are trying to do, and you either need to take some remedial action such as correcting parameters and retrying, or report it to the user and/or administrator.

Returning false or true puts the onus of detecting and handling errorconditions on all your method's callers, and there could be several ofthose scattered through your code, making it difficult to maintain. On the other hand, throwing an exception means that your callers only need to write code to handle the error condition if they are actually able to do something about it. Otherwise they need to be allowed to bubble up to the top layer so that they can be logged and/or the user can be notified that something went wrong.

|||

Another option is to put the delete into a stored procedure and handle it there -- in fact, you may be able to prevent any errors by checking the state of your data first to make sure the delete will work before actually trying it (eg, if a foreign key might prevent the delete)

|||

Is it really best practise to bubble up the error.

Can't I show an general error message to the user.

And handle the error (logging, e-mail to administrator) in the BLL?

|||

JohanNL:

Is it really best practise to bubble up the error.

Without meaning to answer for James, I would say that you be misundestanding what he is suggesting. As he wrote, the rule of thumb is to catch an exception only if you intend to do something about it, and that may mean logging the error and showing the user a general message. I would say that it's an application specific decision about where you want to log the errors, but if you think about it, it's not worth the effort to put try...catch everywhere if all you're going to do is log it and move on. OTOH, updates often cry out for special handling in the event of a sql error, especially since it's often helpful to catch an error right where it happens.

The best thing to do, of course, is to anticipate every possible error and code in such a way that you prevent them from happening. If you think you might get a divide by zero exception, for instance, do the check before attempting the division and give the user a message. I do this in stored procedures all the time. However, if you could think of everything you probably wouldn't need an exception log anyway<g>

How To Call A Procedure While Declaring A Cursor

HI,
WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-

DECLARE CUR_NAME CURSOR
FOR SELECT * FROM CLEANCUSTOMER

BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-

CREATE PROCEDURE MY_PROC
AS
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CLEANCUSTOMER A
INNER JOIN TRCUSTOMERPREFERENCE03JULY B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
ORDER BY B.INTPREFERENCEID

WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-

EXEC MY_PROC

BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-

I AM USING :-

DECLARE CHK_CUR CURSOR
FOR SELECT * FROM MY_PROC

WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."

AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR EXEC MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".

AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR CALL MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "

IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE?
HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR
PLS HELP.

I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW

THANKSDo a insert inot temp table and then use the temp table to declare the cursor|||/*
As far as i know, there is only one way, you must create a temporary table, for example:
*/

Create Proc Caller
As
Create Table #temp (INTCUSTOMERID [FieldType],
CHREMAIL [FieldType], INTPREFERENCEID [FieldType],
CHRPREFERENCEDESC [FieldType])
Declare CHK_CUR For Select a.INTCUSTOMERID From #temp a
Insert Into #temp (INTCUSTOMERID, CHREMAIL, INTPREFERENCEID,
CHRPREFERENCEDESC) Exec MY_PROC

/*Do what ever you want with your cursor|||Hi,
Thanks Enigma And Sneaky Pie For The Quick Replies ,but The Problem Still Lies And It Has Not Helped Me Much.

The Thing Is It Might Be Possible That I Have Not Written It Down
Properly Or You Might Not Have Understood What I Have Wanted To Ask From You.

First Of All I Don't Want To Declare A Cursor Inside A Procedure For Some Reasons Out Here.

Second,i Want To Declare A Cursor Outside The Procedure And Then Call The Procedure From The Cursor.

Third,as You Said That I Should Use A Temp Table,which Is Not Possible Cause The Temp Table Is Not Present Outside The Procedure In Which It Is Created,i Might Be Wrong ,but Pls Tell Me So Then.

Fourth, I Can Go For A Permanent Table ,but It Consumes Too Much
Space,which Is A Constraint In Our Case.

Pls ,look Into The Above Scenario And Help Me Out With It.i Personally Think That It Should Be A Nobrainer,but Somehow It Has Become A Nightmare For Me.

Pls Look Into It As Soon As Possible As I Am Really Feeling The Heat On This Now.

Thanks.|||But the way with the temporary table is the only solution with procs.
Create the #table and after that you are able to create a cursor fetching this #table. If you need a result after some changes or calculations with the cursor data, you need a second #table. At the end return the data of the second #table.

There is, maybe, an other way, but it works with funtions, not with procs. You can create a function that returns a cursor.|||I think Enigma was getting at something like:CREATE TABLE #procResults (
column1 -- whatever
column2 -- etc
)

DECLARE scum CURSOR FOR SELECT
column1, column2
FROM #procResultsDoes that make more sense?

-PatP|||HI,
tHANKS SNEAKY PIE ,BUT I THINK PAT GOT IT RIGHT ABOUT WHAT I WANT : - BUT THE PROBLEM STILL PERSISTS.

I AM GETTING :-

IF,
I AM USING :-

DECLARE CHK_CUR CURSOR
FOR SELECT * FROM MY_PROC

WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."

AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR EXEC MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".

AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR CALL MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "

IS THERE ANY WAY TO CALL THE PROCEDURE FROM A CURSOR AND SAVE THE RESULTS OF THE PROCEDURE IN A VARICABLE OR SOMETHING.
BUT REMEMBER ONLY AFTER THE PROCEDURE IS CALLED FROM THE CURSOR.

THANKS.|||Oops! My bad, I forgot an important step (sleep deprivation does funny things to me!). Let's try:CREATE TABLE #procResults (
column1 -- whatever
column2 -- etc
)

INSERT INTO #procResults -- The "missing link"
EXECUTE myProc

DECLARE scum CURSOR FOR SELECT
column1, column2
FROM #procResults-PatP|||Hi,

Fantastic !!! Right On !! Bull's Eye !!!!!

Got It ... Thanks To You... I Was Scratching My Skull Whole Of Last Week.

But,

Now Though, Things Are Running Fine ... It Is Giving :-

System Low On Resources
Affecting The Whole System .

Is There Any Way Or Any Settings That Could Be Changed. ?

So That I Can Allocate More Resources While The Cursor Is Running.

Thanks|||What is giving the messages? Are they in the Transact-SQL output, the SQL Server log, an NT log, or somewhere that I haven't thought of yet?

-PatP|||HI,

THE MESSAGES ARE FROM THE T-SQL OUTPUT :-

"SYSTEM LOW ON RESOURCES SOME OF THE RESULTS WOULD BE DROPPED."

THIS IS AFFECTING MY WHOLE SYSTEM... AFTER I RUN THE CURSORS

Is There Any Way Or Any Settings That Could Be Changed. ?

So That I Can Allocate More Resources While The Cursor Is Running.

Thanks|||Your choices are quite limited in that situation. You can upgrade the hardware (most likely RAM memory) to make more resources available, or stop other (non-essential) processes running on the same machine to free up the resources that they are using. Those are the only choices that I think might help.

-PatP|||OK...anyone want to ask WHY there's a CURSOR in the mix?

How many levels of nested cursors do you have?|||Hi,

I Don't Think My Ram Must Be A Problem Has It Is 256mb Ram And I Have Seen Stopping The Remaining Processes And Running Only This Processes Still It Gives A Problem.

Also, There Is Only One Level Of Cursors.

And The Data Contained In The Table Is Just Around 10,000 .so Shouldn't Be Problem.

So If Something About This Could Be Done Then It Would Be Fantastic.

Thanks.|||Don't use a Procedure use an inline table function:

CREATE FUNCTION dbo.fn_MY_PROC()
RETURNS TABLE
AS
RETURN (

SELECT TOP 100 PERCENT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CLEANCUSTOMER A
INNER JOIN TRCUSTOMERPREFERENCE03JULY B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
ORDER BY B.INTPREFERENCEID

)

declare cursor cur as fast_forward local for
select * /*or whatever*/ from dbo.fn_MY_PROC()|||Hi,

Thanks,hanif For The Suggestion, But Yes I Will Have To Think On It On A Long -term Basis . About How To Convert All The Procs Into Functions .but Right Now Will Have To Stick On The Existing System To See How It Works.

Thanks.