losing some results

i have created a report that fits the layout to achieve the fields that i
require, i then created an aspx page where my users can select any number of
fields and values to use in the where clause of the sql statement. My aspx
page then builds an sql statement based on these selections and passes this
sql statement to the report as a parameter. The report calls a stored
procedure that executes the sql statement passed in. This works great for
all but one situation that i have found. When a user enters '%bel%' to use
in the where clause for some reason when it gets to reporting services
report the sql statement is modified to 'l%'. Dropping the '%be'. Is
'%be' a reserved command.
example:
if my table had the following entries in a column name city Boston,
Belville,New York,Detroit,Los Angeles, Lakeville
my user wants to find all cities that have 'bel' in the name
the resulting sql would be select city from table where city like '%bel%'
i setup up my report to show the parameters when the aspx page redirects to
the report using the url of the report
the sql that shows up in the parameter field is select city from table where
city like 'l%'
Any help would be appreciated.
Thank youSolved my own problem. what i had to do was replace all my '%' to '%25' to
encode my url before i issued a response.redirect.
"Mike" <mike.no.spam.please@.no.spam.com> wrote in message
news:u2LsSw6tEHA.1596@.TK2MSFTNGP10.phx.gbl...
>i have created a report that fits the layout to achieve the fields that i
>require, i then created an aspx page where my users can select any number
>of fields and values to use in the where clause of the sql statement. My
>aspx page then builds an sql statement based on these selections and passes
>this sql statement to the report as a parameter. The report calls a stored
>procedure that executes the sql statement passed in. This works great for
>all but one situation that i have found. When a user enters '%bel%' to use
>in the where clause for some reason when it gets to reporting services
>report the sql statement is modified to 'l%'. Dropping the '%be'. Is
>'%be' a reserved command.
> example:
> if my table had the following entries in a column name city Boston,
> Belville,New York,Detroit,Los Angeles, Lakeville
> my user wants to find all cities that have 'bel' in the name
> the resulting sql would be select city from table where city like '%bel%'
>
> i setup up my report to show the parameters when the aspx page redirects
> to the report using the url of the report
> the sql that shows up in the parameter field is select city from table
> where city like 'l%'
> Any help would be appreciated.
> Thank you
>

Losing dbobtions on clustered server failover?

We have a clustered sql server 2000 installation on windows 2000. We
recently lost the select into/bulkcopy option from a database. The
most likely candidate appears to be a failover just before - after
this happened the setting disappeared. Any ideas?

Thanks
Tom"Thomas Richards" <tom.richards@.rocketmail.com> wrote in message
news:f118866.0311060256.7f998697@.posting.google.co m...
> We have a clustered sql server 2000 installation on windows 2000. We
> recently lost the select into/bulkcopy option from a database. The
> most likely candidate appears to be a failover just before - after
> this happened the setting disappeared. Any ideas?
> Thanks
> Tom

Can you clarify what you mean by "the setting disappeared"? Where did it
disappear from, and what happens if you use sp_dboption to set it directly?
Although MS now say that sp_dboption is for backwards compatibility only, so
perhaps you should look at using ALTER DATABASE instead, if that's possible.

If this issue only appears on clustered systems, you might want to post to
microsoft.public.sqlserver.clustering to see if anyone there has better
information.

Simonsql

loops oops

Hi,
How can I walk through a result of a select query? I have two tables.
TableA(id, name1, name2, notes), TableB(id, grade). I have to bulk insert
some data, (given: name1, name2, notes and grande too). The "bulk insert"
inserts datas in one step. So first I insert the values a temporary table
(TableTmp). Then I have to walk through this TableTmp and at each row I
have to do this:
1, insert name1, name2, notes into TableA
2, get back the actual @.@.identity
3, insert grade into TableB where is the last identity.
I would like to make it in sql query analizer...
I have problem with the loop. I have no idea how to make it.
Is it not overcomplicated? Is there any simple solution?
Thank you for help
chris
Message posted via http://www.webservertalk.comI don't think you'll need a loop. Usually it's best to avoid loops and row
by row processing.
Unfortunately you haven't given us any clues about keys or constraints or
shown us the table where this data comes from. So here is a wild guess:
INSERT INTO TableA (col1, col2, col3)
SELECT DISTINCT col1, col2, col3
FROM Unspecified
INSERT INTO TableB (id, col4, col5, col6)
SELECT DISTINCT
A.id /* the IDENTITY col from A */ ,
U.col4, U.col5, U.col6
FROM Unspecified AS U
JOIN TableA AS A
ON U.col1 = A.col1
AND U.col2 = A.col2
AND U.col3 = A.col3
David Portas
SQL Server MVP
--|||Mary,
To me it sounds like your database design is a bit odd.
You have the details of a student(?) in TableA, and the grade that the
student got in TableB. Presumably if you actually have:
TableA (students): id, name1, name2, notes...
TableC (subjects): id, code, description...
TableB (grades): studentid, subjectid, grade
then you could import your data into a temporary table... and then make sure
that all your subjects and students are listed:
insert into TableA (name1, ...)
select t.name1, ...
from TableTemp t
where not exists (select * from TableA a where a.name1 = t.name1 and a.name2
= t.name2 and ...)
(and similar for TableC)
And then insert the grades:
insert into TableC (studentid, subjectid, grade)
select stud.id, subj.id, t.grade
from TableTemp t JOIN TableA stud on stud.name1 = t.name1 and stud.name2...
JOIN TableC subj on subj.code = t.code ...
The '...' are the other fields and stuff that you're looking to identify
students by.
Of course, it's much nicer to have a separate file of "studentcode",
"subjectcode", "grade", if you can fetch the details like that.
Rob|||Thanx.
It seems working..Thank you very much

looping through XML with xquery

Hello,

for example:

declare @.xml xml
select @.xml = (select * from table for xml raw, elements)

... now i want to iterate through @.xml and get the values from field ID:

declare @.id int, @.x int, @.y int
select @.x = @.xml.value('data(count(/*))','int')
set @.y = 1
while @.y <= @.x begin
select @.id = @.xml.value('data(/row/ID)[' + cast(@.y as varchar) + ']','int')
set @.y * @.y + 1
end

... this is not working because for value() only string literals are allowed, so how can i do this?

thank you,
Helmut

You are on the right track, but you need to use the sql:variable function to do this. sql:variable gives you access to variables and parameters in scope.

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

so you should be able to write your query as:

select @.id = @.xml.value('data(/row/ID)[sql:variable(@.y)]','int')

This also has the added advantage of avoiding sql or xquery injection.

|||Superb, works perfect!

thank you very much,
Helmut
|||SELECT SearchCriteriaXML.value('data(//ColumnName)[sql:variable(@.i)]','varchar(100)')
FROM SearchColumn INNER JOIN SearchCriteria ON SearchColumn.SearchCriteriaId = SearchCriteria.Id
WHERE (SearchCriteria.Id = 1) AND (SearchColumn.Id = 1)

I'm Getting below error for above query

Msg 2225, Level 16, State 1, Line 20
XQuery [SearchCriteria.SearchCriteriaXML.value()]: A string literal was expected
sql

looping through recordset

hello,
i have a select query that returns multiple rows (within a cursor). How do i loop through the rows to process it (in a stored proc)? I donot want to use nested cursors. a code sample is requested.
thanks!Usually a cursor is created to process multiple rows (unless you developed a passion for cursors and would like to create one for 1 row).

Have you looked at FETCH NEXT?|||Well, the situation is somethin like this. I am using a cursor that returns multiple rows, within the while @.@.FETCH_STATUS = 0, I have another sql select that returns multiple rows for every row the cursor is processing. I would want to process every row returned in the inner select query. Do I have an option other than using nested cursors? Any help is appreciated.|||If you post the code maybe we can get rid of the cursor.

Looping through a list of values

Hi all
How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.
cheers
james :)In T-SQL? Even heart of a cursor?|||The only way that i could think of doing this is perhaps to use the 'IN' keyword within the 'WHERE' CLAUSE|||Originally posted by DoktorBlue
In T-SQL? Even heart of a cursor?

i'll have to look that up tonight. Never used cursors before.

thanx dok|||With a cursor, you can loop through a recordset, where the cursor references to one record at a time. The best way of understanding is to look for an example.

See DECLARE CURSOR (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_31yq.asp) for the syntax and soem examples in SQL Server 2000. Version 7 is similar.|||Originally posted by nano_electronix
Hi all

How do i loop through a list of values returned by a select statement using TSQL. maybe using a while statement.

cheers
james :)

Yep.. the only way to do a while loop is by using cursor.. If there is another much better way, I'd like to know too..|||Just save result of query to temp table with IDENTITY column and make loop by this field...

Good luck|||give us more details, maybe there is no need to do the loop at all.|||Hi all

I did it... woohoo, thanx for all your help guys.
This is probably the longest TSQL i've ever written, 200+ lines and most importantly it works.

Special thanx to dok for pointing out CURSOR as a looping solution, it works magics.

Cheers
James

PS: Here is the single store procedure implementing the application logic i intended. (just to show off) hahahahah :D

CREATE PROCEDURE GenerateTimesheets
(
@.JobID decimal
)
AS
BEGIN
-- Constants
DECLARE @.AllocatedTime decimal

-- Used for looping through recordset with CURSORs
DECLARE @.AccumulatedTime decimal -- Accumulated time spent on a particular job
DECLARE @.SpentTime decimal -- Time spent on each subtask of a job
DECLARE @.TimesheetDate datetime
DECLARE @.JobUpdateID decimal
DECLARE @.TimesheetID decimal
DECLARE @.TimesheetType int

-- Initialize variables
SELECT @.AllocatedTime = allocatedTime
FROM Job WHERE jobID = @.JobID
SET @.AccumulatedTime = 0
SET @.TimesheetType = 1

-- Define CURSOR for each distinct day
-- Because timesheets are generated on a daily basis.
DECLARE date_cursor CURSOR FOR
SELECT DISTINCT CAST( CONVERT(varchar(10), dateSubmitted, 120) as datetime)
FROM jobUpdate WHERE jobUpdate.jobID = @.JobID

OPEN date_cursor
FETCH NEXT FROM date_cursor
INTO @.TimesheetDate
-- Create timesheets for each day
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Create a new timesheet for each day
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
-- Return the new timesheetID for reference
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType

-- Create new cursor to loop through jobupdates for a particular day
DECLARE jobupdate_cursor CURSOR FOR
SELECT spentTime, jobUpdateID
FROM jobUpdate
WHERE @.TimesheetDate = CAST(CONVERT(varchar(10), dateSubmitted, 120) as datetime)
AND jobID = @.JobID

OPEN jobupdate_cursor
FETCH NEXT FROM jobupdate_cursor
INTO @.SpentTime, @.JobUpdateID

-- Loop through and process all jobupdates for the day
-- creating new timesheets for CT and TA if necessary
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.AccumulatedTime = @.AccumulatedTime + @.SpentTime

------------
-- if currently in standard time phase
------------
if @.TimesheetType = 1
BEGIN
IF @.AccumulatedTime >= @.AllocatedTime + 2
BEGIN
-- Create new timesheet approval timesheet
SET @.TimesheetType = 3
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id

END
ELSE IF @.AccumulatedTime >= @.AllocatedTime
BEGIN
-- Create new completion time timesheet
SET @.TimesheetType = 2
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType

-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
ELSE
BEGIN
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
END

-------------
-- if currently in completion time phase
-------------
ELSE IF @.TimesheetType = 2
BEGIN
IF @.AccumulatedTime >= @.AllocatedTime + 2
BEGIN
-- Create new timesheet approval timesheet
SET @.TimesheetType = 3
INSERT INTO Timesheet
(
timesheetDate,
jobID,
timesheetType
)
VALUES
(
@.TimesheetDate,
@.JobID,
@.TimesheetType
)
SELECT @.TimesheetID = timesheetID
FROM Timesheet
WHERE timesheetDate = @.TimesheetDate AND jobID = @.JobID AND timesheetType = @.TimesheetType
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
ELSE
BEGIN
-- Update the current job with timesheet reference id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END
END

--------------
-- if currently in timesheet approval phase
--------------
ELSE IF @.TimesheetType = 3
BEGIN
-- Update the current job with the timesheet refernce id
UPDATE JobUpdate SET timesheetID = @.TimesheetID
WHERE jobupdateID = @.JobUpdateID
END

FETCH NEXT FROM jobupdate_cursor
INTO @.SpentTime, @.JobUpdateID


END
CLOSE jobupdate_cursor
DEALLOCATE jobupdate_cursor

FETCH NEXT FROM date_cursor
INTO @.TimesheetDate

END
CLOSE date_cursor
DEALLOCATE date_cursor

END|||Hi James, very impressive! You are a fast learner.|||Originally posted by DoktorBlue
Hi James, very impressive! You are a fast learner.

Thanx dok !!!
I was wondering if you also know stuffs on Oracle as I will be working with oracle very soon, next monday actually. I guess I will asking questions in the Oracle forum, but it would be great if you are a Oracle expert too.

You have been very helpful, thanx again.

Cheers
James|||I think you have to be careful with cursors as they are not the most efficient methods available. In fact most problems can be solved without the need for cursors.

If you provide us with what you're trying to do, maybe someone will provide you with a set based solutions instead.|||I think James is quickly-learning poor programming habits. I can't believe this code does what he thinks it is doing, there are so many oportunities for errors. Why is TimeSheetType not reset to 1 when processing starts for a new day? I can't believe you need nested cursors to do whatever it is you are trying to do.

I can't figure out the logic from the code, and that at least is going to cause problems for whoever comes along and has to revise or debug it.

Add my vote to rdjabarov's and Crespo-n00b's that there ought to be a better solution.

blindman|||everyone learns from mistakes, won't take long ;-)|||Why so negative? James just took a step into the cursor world, and you should be glad with it. And everybody, which is claiming to know a better solution than that, what James showed us, is invited to do a proposal. But just saying, that there ought to be a better solution, is too cheap, because this is a TRUE statement in almost all cases.|||Taking a step into the cursor world is too often a step in the wrong direction. Cursors are too often a crutch used by procedural programmers. James needs to learn set-based processing, and their are a lot of individuals on this forum who would be happy to help him.

You may be impressed by volumes of code, but I'm impressed by short, elegant code that is simple, easily understood, well commented and readily debugged.

Inside every large program is a small program screaming to get out.

blindman|||Why don't you let it out, I'm curious to see whether you can add practise to you theory.|||Well, I've managed to shorten a lot of your code, and I and other members will help James if he asks.

I'll help you if you ask, too.

blindman|||You should go into politics. I challanged you to come with an elegant solution, but you didn't probably even understand James' problem. So, what's your point? Desperately referring to other threads? Hiding behind "other members"? The only one, who really helped James, was me.

About cursors: they are a usual mean to express functionality, and they are not bad at advance. It up to the user to use or abuse this functionality, like you can also easily write a "query from hell". The main point about cursors is, that the user takes responsibility of the execution plan from the Analyzer.

Blindman, if you want to respond, please do me a favour and focus on the issue of this thread, not other threads, not aiming at me, just show your short elegant solution, which would be a real contribution.|||Originally posted by DoktorBlue
Why so negative? James just took a step into the cursor world, and you should be glad with it. And everybody, which is claiming to know a better solution than that, what James showed us, is invited to do a proposal. But just saying, that there ought to be a better solution, is too cheap, because this is a TRUE statement in almost all cases.

What you say is true to a certain extent. There are view few cases where Cursors are needed and I believe that if people paid more attention to database concepts they would invariably find a set based solution to most of their SQL problems.

As the previous poster stated, it is hard to follow the logic of the cursor, and to make matters more complicated he has also used a nested cursor!

What we need is some CREATE TABLE statements and a few example records and I'm almost 100% sure that one of us here will come up with a much more efficient set based solution.

My 2 cents.|||I am also totally in agreement with blindman .
I would rather not use cursors ... let alone nested cursors , if there was an alternative way.

You should go into politics. I challanged you to come with an elegant solution, but you didn't probably even understand James' problem. So, what's your point? Desperately referring to other threads? Hiding behind "other members"? The only one, who really helped James, was me.

well , DoktorBlue , whats all the fighting for ... we are all here to share our knowledge .. dosent matter who is helping whom ... till the other guy gets the solution. Whats the problem in a little friendly discussion.|||I agree with DoktorBlue that you shouldn't continually jump onto threads and say that someone's solution is bad without offering a solution. The anti-cursor evangelists have done the same to me. I asked for a solution but they didn't provide one.

I'm actually a junior TSQL guy but even I can see that maybe the outer loop of this guy's code could be a WHILE loop looping through days (most timesheets don't skip days in the middle) but it would appear some folks are too scared to risk their lofty positions by actually suggesting some code. You'd think that this would be a simple enough of an example that they could put something out there....nope.

How do you get those stars under your name anyway...|||One more thing ...

If it is really a growing problem that cursors are often used inappropriately (because newbies are procedural rather than focused on how datasets work or for whatever reason), why not write a quick article and post it on the web (if it doesn't exist already). The article would show "practical" examples of common mistakes and how to avoid them.

That way, when you see samples of inappropriate cursor usage in these forums you can just tell the poster "You may benefit from this article" and give'em the URL. I could personally benefit from this article right now.

It's cool that folks exist out there like you guys who know the theories of what is good and bad but if nobody can explain in practical terms when they apply then...

Sorry for the rant.|||bill_dev,

I and other members have offered to provide more assistance with this problem, but we need more details about the requirements. We are not going to spend our time developing solutions (for free) without requirements. That is just chasing shadows.

Truth is, I have spent a lot of time looking over James' code, and as I stated quite a while back the first problem is that it is difficult to figure out what the code is supposed to be doing. Readability is important, especially if you have ever inherited a project, or even had to debug or modify something written six or twelve months before.

Cursors are crutches. They are slow, and awkward, and if you use them too much or too often they get painful. But like crutches, sometimes they are absolutely necessary and nothing else will do.

blindman|||I have to say I am in the anti-cursor faction. If you have a cursor on your system, try running it with task manager running, and watch the cpu graph. Now imagine that cursor being run by 5 or 6 other people at the same time. Truth is cursors are not scalable.
Another reason I am against cursors is that it promotes putting business logic directly on the database. If you know you are only going to have a very few records in these tables (under say 1000), then ok, the cursor can be used if there is no other way. But if you think, or even dream you will be going to hundreds of users, then the business logic has to come off the database. This is a job for application servers. Think about it this way. You can have many application servers, but you are stuck with one database.
To top it off, in many companies, you are not only stuck with one database server, but you have to share that server with all the other applications' databases. The performance on these servers is generally equal to the worst performing application.

As far as improvements in this particular procedure go, the only thing I have so far is to use select @.@.identity after the timesheet inserts, rather than select the new identity right off the table. If you have a million timesheets, you will see the performance degrade. If you have no index on the fields in the where clause, you guarantee that you have performance problems. Just things to watch for.

And, yes, I am still thinking about ways to eliminate the cursor entirely. So far, my best bet is to use a perl or VB script to do the actual logic.|||Originally posted by snail
Just save result of query to temp table with IDENTITY column and make loop by this field...

Good luck

snail's suggestion is good. Using a temp table is simpler than a cursor.

Here is a complete example:

-- Update area codes for people in the 415 area code
-- Set area code to 234
USE pubs
GO

-- Show values before update
SELECT au_id, phone
FROM authors
ORDER BY au_id

DECLARE @.error int
DECLARE @.authorCt int
DECLARE @.loopCt int

DECLARE @.authorsToUpdate TABLE (
EntryID integer IDENTITY(1,1),
au_id varchar(11),
phone char(12)
)

INSERT INTO @.authorsToUpdate (
au_id,
phone
)
SELECT
au_id,
REPLACE(phone, '415 ', '234 ')
FROM authors
WHERE phone LIKE '415 %'

SELECT
@.error = @.@.error,
@.authorCt = @.@.rowcount

-- Error check

IF @.authorCt > 0 BEGIN
SET @.loopCt = 0

BEGIN TRAN

WHILE @.loopCt < @.authorCt BEGIN
SET @.loopCt = @.loopCt + 1

UPDATE a
SET a.phone = u.phone
FROM authors AS a
JOIN @.authorsToUpdate AS u
ON a.au_id = u.au_id
WHERE u.EntryID = @.loopCt

-- Error check
END

-- Show values after update
SELECT au_id, phone
FROM authors
ORDER BY au_id

ROLLBACK TRAN
-- COMMIT TRAN
END
GO|||Cool. Very useful...Thanks|||Finally somebody with more than a story, but a proposal. Bravo!|||Thanx all for all your feedbacks.

I haven't looked this up for the last 2 days, so i missed all the discussions.

I think the suggestion of using temporary table is probably the way to get rid of cursor for certain cases. For the logic that i am trying to implement, using cursor is probably necessary and the most intuitive, but because the small amout of records that i have to process, there is no real performance issue for me.

The program logic that i tried to implement is to basically generate timesheets from jobupdate entries entered by developers. Basically how our company works is like follows.

1. Client submits a request
2. Project manager receives request and allocate Jobs to differnt developers. Each job has an allocated time.
3. Developers will update their job justifying what they did everytime they finish a task for that job, these jobupdates will include the time spent on this task

when ever a job is completed a number of timesheets would be generated from all the jobupdates submitted . for this particular company, they want to generate timesheets on a per job per day basis, what's more, all the jobupdates that exceeds the allocated time will be put into a differnt timesheet. so normally there would only be 1 timesheet per day when all the jobupdates submitted for a particular job are within allocated time. once allocated time is exceeded, there could be a maximum of 3 different timesheets generated for a particular day. Thus the reason that i have to process each entry individual and thus the need for cursor.

Because of this business logic, there is almost no way of avoiding cursor.

Cheers
James

PS: I love you guys, I have learnt a lot from dok and blindman and a number of other people since i joined. dbforums rocks because of you!! :)

Looping in Stored Procedures

can we write loops in MSSql stored procedures ?

my aim is to do something like

select * from table
while < recordset is not blank >
do something...
next record
end while

is this possible in stored procedure or should i do this from my client
application ?

pl explaintu can very well do this.
for eg:
create procedure looping
as
begin
declare @.l int
set @.l=10
while @.l>1
begin
print @.l
set @.l=@.l-1
end
end

or it can be a condition like
while exists (select * from table)
begin
some code...
end|||thanks for the information

but have one more qn

im SELECTing some rows from a table
and in the loop, i want to perform some operation on each selcted row
after performing the operations , will the loop moves to the next
record ?

pl comment|||no.
for that u need to use cursors.
but use of cursors is not the recomended way of doing things in sql.
so if u could post what u r trying to do, some one out here will surely be help u out.

Looping columns in instead of trigger

I have the following view (vProcurementPlan)

SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
dbo.tblRequisition.ROS AS ROS,
dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID AS
ProjectID
FROM dbo.tblProcurementPlan INNER JOIN
dbo.tblRequisition ON
dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID

If I try inserting a record from Access it complains about multiple base
tables, I'm happy to write an "instead of" trigger and handle the 5 columns
from tblRequisition but as it contains all columns from tblProcurementPlan I
don't want to have to list them separately in any insert or update
statement.

The idea is that a record will be inserted into both tables simultaneously
upon insert to the view.Trev@.Work (bouncer@.localhost) writes:
> I have the following view (vProcurementPlan)
> SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
> ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
> dbo.tblRequisition.ROS AS ROS,
> dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID
> AS ProjectID
> FROM dbo.tblProcurementPlan INNER JOIN
> dbo.tblRequisition ON
> dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID
> If I try inserting a record from Access it complains about multiple base
> tables, I'm happy to write an "instead of" trigger and handle the 5
> columns from tblRequisition but as it contains all columns from
> tblProcurementPlan I don't want to have to list them separately in any
> insert or update statement.

I am afraid you don't have much choice.

Besides, in my opinion SELECT * does not belong in production code.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Try not using the * and referencing all the column names explicitly.
It should insert without a problem.
eg
SELECT pp.RequisitionID ppreqid, pp.ReqNo ppReqNo, pp.Am AS Am, ppROS
AS ROS, pp.ActivityID ppAcID, pp.ProjectID ppProjID,rq.RequisitionID
rqReqId, rq.ReqNo rqReqNo, rq.Am Am, rqROS ROS, rq.ActivityID rqAcID,
rq.ProjectID rqProjID
FROM dbo.tblProcurementPlan pp INNER JOIN dbo.tblRequisition rq ON
pp.RequisitionID = rq.RequisitionID

Pachydermitis

"Trev@.Work" <bouncer@.localhost> wrote in message news:<3fddeed0$0$13894$afc38c87@.news.easynet.co.uk>...
> I have the following view (vProcurementPlan)
> SELECT dbo.tblProcurementPlan.*, dbo.tblRequisition.RequisitionID AS
> ReqReqID, dbo.tblRequisition.ReqNo AS ReqNo, dbo.tblRequisition.Am AS Am,
> dbo.tblRequisition.ROS AS ROS,
> dbo.tblRequisition.ActivityID AS ActivityID, dbo.tblRequisition.ProjectID AS
> ProjectID
> FROM dbo.tblProcurementPlan INNER JOIN
> dbo.tblRequisition ON
> dbo.tblProcurementPlan.RequisitionID = dbo.tblRequisition.RequisitionID
> If I try inserting a record from Access it complains about multiple base
> tables, I'm happy to write an "instead of" trigger and handle the 5 columns
> from tblRequisition but as it contains all columns from tblProcurementPlan I
> don't want to have to list them separately in any insert or update
> statement.
> The idea is that a record will be inserted into both tables simultaneously
> upon insert to the view.

Loop OR dataset in Store procedure

Hello ,
I want in a store procedure in SQL to have a loop.
I asctually want to SELECT something from a table and then
for each record of the results do something (another selection etc ) is
this posible in a store procedure ?
I know how to do this in vb.net but i want it to be really fast so i am
searching for a way to do this in a store procedure
example:
Select distinct(code) from Table1
for each code
select year from table2 where code=code (of the selection
for ...
for ...
next ...
next ...
The above is an example of what i want to do in the store procedure.
Are you sure you can't use a set statemet?
If not take a look at cursors in BOL
http://sqlservercode.blogspot.com/
"savvaschr@.nodalsoft.com.cy" wrote:

> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||You have to lookup CURSORS in the BOL
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegr oups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||If you using SQL Server 2005,
you could do it in SP with CLR code..
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegr oups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>
|||Perhaps. But there's nothing in the original post to indicate that that
would be necessary or desirable. The OP said "for each record ... do
something (another selection etc)". If "do something" means "select or
update some other data" then chances are the simplest and most
efficient solution is to do a join using set-based SQL code. CLR isn't
the natural place to do data retrieval and manipulation.
David Portas
SQL Server MVP
|||savvaschr@.nodalsoft.com.cy wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc )
> is this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i
> am searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
You can use a temp table and pull information from it one row at a time
in a loop.If you use a cursor, make sure it is read only, forward only,
and local.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql

Loop OR dataset in Store procedure

Hello ,
I want in a store procedure in SQL to have a loop.
I asctually want to SELECT something from a table and then
for each record of the results do something (another selection etc ) is
this posible in a store procedure ?
I know how to do this in vb.net but i want it to be really fast so i am
searching for a way to do this in a store procedure
example:
Select distinct(code) from Table1
for each code
select year from table2 where code=code (of the selection
for ...
for ...
next ...
next ...
The above is an example of what i want to do in the store procedure.Are you sure you can't use a set statemet?
If not take a look at cursors in BOL
http://sqlservercode.blogspot.com/
"savvaschr@.nodalsoft.com.cy" wrote:

> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||You have to lookup CURSORS in the BOL
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||If you using SQL Server 2005,
you could do it in SP with CLR code..
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||Perhaps. But there's nothing in the original post to indicate that that
would be necessary or desirable. The OP said "for each record ... do
something (another selection etc)". If "do something" means "select or
update some other data" then chances are the simplest and most
efficient solution is to do a join using set-based SQL code. CLR isn't
the natural place to do data retrieval and manipulation.
David Portas
SQL Server MVP
--|||savvaschr@.nodalsoft.com.cy wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc )
> is this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i
> am searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
You can use a temp table and pull information from it one row at a time
in a loop.If you use a cursor, make sure it is read only, forward only,
and local.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Loop OR dataset in Store procedure

Hello ,
I want in a store procedure in SQL to have a loop.
I asctually want to SELECT something from a table and then
for each record of the results do something (another selection etc ) is
this posible in a store procedure ?
I know how to do this in vb.net but i want it to be really fast so i am
searching for a way to do this in a store procedure
example:
Select distinct(code) from Table1
for each code
select year from table2 where code=code (of the selection
for ...
for ...
next ...
next ...
The above is an example of what i want to do in the store procedure.Are you sure you can't use a set statemet?
If not take a look at cursors in BOL
http://sqlservercode.blogspot.com/
"savvaschr@.nodalsoft.com.cy" wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||You have to lookup CURSORS in the BOL
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||If you using SQL Server 2005,
you could do it in SP with CLR code..
<savvaschr@.nodalsoft.com.cy> wrote in message
news:1129813884.938532.251580@.g44g2000cwa.googlegroups.com...
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc ) is
> this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i am
> searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
>|||Perhaps. But there's nothing in the original post to indicate that that
would be necessary or desirable. The OP said "for each record ... do
something (another selection etc)". If "do something" means "select or
update some other data" then chances are the simplest and most
efficient solution is to do a join using set-based SQL code. CLR isn't
the natural place to do data retrieval and manipulation.
--
David Portas
SQL Server MVP
--|||savvaschr@.nodalsoft.com.cy wrote:
> Hello ,
> I want in a store procedure in SQL to have a loop.
> I asctually want to SELECT something from a table and then
> for each record of the results do something (another selection etc )
> is this posible in a store procedure ?
> I know how to do this in vb.net but i want it to be really fast so i
> am searching for a way to do this in a store procedure
> example:
> Select distinct(code) from Table1
> for each code
> select year from table2 where code=code (of the selection
> for ...
> for ...
> next ...
> next ...
> The above is an example of what i want to do in the store procedure.
You can use a temp table and pull information from it one row at a time
in a loop.If you use a cursor, make sure it is read only, forward only,
and local.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com

Loop inside View

Hello,

is it possible to build a loop for the following statement?

CREATE VIEW vwObjects as (

Select 2001 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate >= '20010101' and o.sdate < '20010401'
union

Select 2001 as year, 2 as quarter, id as id
from dbo.objects o
where o.edate >= '20010301' and o.sdate < '20010701'
...
union

Select 2002 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate > '20020101' and o.sdate < '20020401'
...
)

I want a kind of calender for my olap cube, so I can get every active object in a special quarter resp year.

Thank you!Huh?

YEAR(edate), MONTH(edate)

What are you trying to do?

And what's with LOOP? I don't see no loop|||Oh,sorry. I have one Table for the objects. Every object as a startdate and an enddate. For my cube, I need kind of dimension, so the user can pick a quarter and he will get the sum of all active objects. I tried several ways to realize this.

My idea is to create of view, that looks like:

year quarter id
2001 1 1
2001 1 2
2001 1 3
2001 2 2
2001 2 4

From objects table:

id startdate enddate
1 2001/05/01 2001/13/02
2 2001/25/02 2001/03/04
3 2001/03/01 2001/5/01
4 2001/09/05 2001/22/05

I hope it's more more understandable now.|||Ok, forget that, I found another way.|||Ok, forget that, I found another way.

Can you elaborate? Your solution may help other users in the future.|||I couldn't solve this. Even if I could, this will be very slow for big tables. I will have to do a little work off on my design and then I will try this loop with a INSERT INTO, not a view. Greets, Silas

Loop a select statement?

Hi,
I have a select statement which brings back several fields and several
columns. Within one of these columns is an email address. I want to be
able to cycle through each record in the select statement and email the
details attached to their email address. My SP is below:-
SELECT dbo.tbl_surveillance.s_id as REG_NO,
dbo.tbl_surveillance_dates.sd_urn as URN,
dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
dbo.tbl_surveillance_dates.sd_renewal_date as
RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
FROM dbo.tbl_surveillance INNER JOIN
dbo.tbl_surveillance_dates ON
dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
JOIN
dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
dbo.tbl_email.e_id
WHERE getdate() > dateadd(day, -7, sd_renewal_date)
I know you can use the following to send emails:-
@.sbj varchar(200),
@.msg varchar(2000),
@.recipient varchar(50)
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
but I want to incorporate that xp_sendmail with the select statement.
If anyone has ever done this before and can give me some pointers it would
be greatly appreciated.
Thanks
DamonI think you will need to use a cursor.
Here's some help with your SELECT statement, by the way. It's still largely
unreadable because of the superfluous prefixes and mismatched column names,
but it should be a little easier to tackle. Note that I changed the WHERE
clause to apply transformation to the constant, instead ofto the column, and
kept with the tradition of object-operator-value instead of
value-operator-object. getdate()>column+x is very difficult to process, at
least for me. If the table is huge, you may find an advantage in declaring
a variable of smalldatetime up front and calculating GETDATE()+7 and storing
it in a constant. However, if there is no index on sd_renewal_date, it's
probably all moot.
SELECT
Reg_No = s.s_id,
URN = d.sd_urn,
OFFICER = d.sd_reviewing_officer,
RENEWAL_DATE = d.sd_renewal_date,
OFFICER_EMAIL = e.e_officer_email
FROM
dbo.tbl_surveillance s
INNER JOIN
dbo.tbl_surveillance_dates d
ON
s.s_id = d.sd_s_id
LEFT OUTER JOIN
dbo.tbl_email e
ON
d.sd_e_id = e.e_id
WHERE
d.sd_renewal_date < GETDATE()+7;
If the cursor's only purpose is to send e-mail, then you probably want an
inner join against tbl_email. What is the point of getting rows where there
isn't a valid recipient?
I'd write the cursor for you, but it is entirely unclear to me how you are
deriving @.sbj and @.msg based on Reg_No, URN, OFFICER, and RENEWAL_DATE.
Please see http://www.aspfaq.com/5006
"Damon" <nonsense@.nononsense.com> wrote in message
news:O6Xnf.19494$8v6.12132@.newsfe6-gui.ntli.net...
> Hi,
> I have a select statement which brings back several fields and several
> columns. Within one of these columns is an email address. I want to be
> able to cycle through each record in the select statement and email the
> details attached to their email address. My SP is below:-
> SELECT dbo.tbl_surveillance.s_id as REG_NO,
> dbo.tbl_surveillance_dates.sd_urn as URN,
> dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
> dbo.tbl_surveillance_dates.sd_renewal_date as
> RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
> FROM dbo.tbl_surveillance INNER JOIN
> dbo.tbl_surveillance_dates ON
> dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
> JOIN
> dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
> dbo.tbl_email.e_id
> WHERE getdate() > dateadd(day, -7, sd_renewal_date)
> I know you can use the following to send emails:-
> @.sbj varchar(200),
> @.msg varchar(2000),
> @.recipient varchar(50)
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> but I want to incorporate that xp_sendmail with the select statement.
> If anyone has ever done this before and can give me some pointers it would
> be greatly appreciated.
> Thanks
> Damon
>|||Thank you very much for your reply. I can see why you are a SQL Server MVP.
Unfortunately my works have not supplied me with SQL training so have had to
learn myself, thus the messy code.
The @.sbj would be the same everytime, something like "List of renewal
dates". @.msg would literally be a compilation of the other fields i.e.
Reg_no & ', ' & URN & ', ' & RENEWAL_DATE. This is just so the officer
being emailed can see the list of renewals that they have which are due up
within the next w.
I really appreciate your help. Need to get on to my works to send me on a
SQL course.
Thanks again
Damon.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrdL3fMAGHA.532@.TK2MSFTNGP15.phx.gbl...
>I think you will need to use a cursor.
> Here's some help with your SELECT statement, by the way. It's still
> largely unreadable because of the superfluous prefixes and mismatched
> column names, but it should be a little easier to tackle. Note that I
> changed the WHERE clause to apply transformation to the constant, instead
> ofto the column, and kept with the tradition of object-operator-value
> instead of value-operator-object. getdate()>column+x is very difficult to
> process, at least for me. If the table is huge, you may find an advantage
> in declaring a variable of smalldatetime up front and calculating
> GETDATE()+7 and storing it in a constant. However, if there is no index
> on sd_renewal_date, it's probably all moot.
> SELECT
> Reg_No = s.s_id,
> URN = d.sd_urn,
> OFFICER = d.sd_reviewing_officer,
> RENEWAL_DATE = d.sd_renewal_date,
> OFFICER_EMAIL = e.e_officer_email
> FROM
> dbo.tbl_surveillance s
> INNER JOIN
> dbo.tbl_surveillance_dates d
> ON
> s.s_id = d.sd_s_id
> LEFT OUTER JOIN
> dbo.tbl_email e
> ON
> d.sd_e_id = e.e_id
> WHERE
> d.sd_renewal_date < GETDATE()+7;
> If the cursor's only purpose is to send e-mail, then you probably want an
> inner join against tbl_email. What is the point of getting rows where
> there isn't a valid recipient?
> I'd write the cursor for you, but it is entirely unclear to me how you are
> deriving @.sbj and @.msg based on Reg_No, URN, OFFICER, and RENEWAL_DATE.
> Please see http://www.aspfaq.com/5006
>
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:O6Xnf.19494$8v6.12132@.newsfe6-gui.ntli.net...
>|||I've taken an example cursor from Books Online and adjusted it somewhat to
fit your situation. However, it's just a rough draft and you will need to
complete it. Basically, the cursor allows you to iternate through the query
result one row at a time, giving you the ability to populate variables and
exec the xp_sendmail call. Every column that you plan to reference will need
to be assigned a variable. I've also added the FAST_FORWARD option so it
should use less resources.
DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
SELECT dbo.tbl_surveillance.s_id as REG_NO,
dbo.tbl_surveillance_dates.sd_urn as URN,
dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
dbo.tbl_surveillance_dates.sd_renewal_date as
RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
FROM dbo.tbl_surveillance INNER JOIN
dbo.tbl_surveillance_dates ON
dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
JOIN
dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
dbo.tbl_email.e_id
WHERE getdate() > dateadd(day, -7, sd_renewal_date)
OPEN surveillance_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables should be in the same order as the columns in the
SELECT statement.
FETCH NEXT FROM surveillance_cursor
INTO @.recipient, @.Reg_no, @.RENEWAL_DATE, etc.
-- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sbj = ?
select @.msg = @.Reg_no + ', ' + @.URN + ', ' + @.RENEWAL_DATE + etc.
exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
@.message=@.msg
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM surveillance_cursor
INTO @.recipient, @.Reg_no, @.RENEWAL_DATE
END
CLOSE surveillance_cursor
DEALLOCATE surveillance_cursor
"Damon" <nonsense@.nononsense.com> wrote in message
news:cCXnf.28255$XZ6.26473@.newsfe1-gui.ntli.net...
> Thank you very much for your reply. I can see why you are a SQL Server
> MVP. Unfortunately my works have not supplied me with SQL training so have
> had to learn myself, thus the messy code.
> The @.sbj would be the same everytime, something like "List of renewal
> dates". @.msg would literally be a compilation of the other fields i.e.
> Reg_no & ', ' & URN & ', ' & RENEWAL_DATE. This is just so the officer
> being emailed can see the list of renewals that they have which are due up
> within the next w.
> I really appreciate your help. Need to get on to my works to send me on a
> SQL course.
> Thanks again
> Damon.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OrdL3fMAGHA.532@.TK2MSFTNGP15.phx.gbl...
>|||>> I have a select statement which brings back several fields and several co
lumns. <<
Interesting, since SQL does not have fields and columns are a totally
different concept.
More interesting, since SQL does not have records and rows are a
totally different concept. Tables are sets and not files;
sets by definition have no ordering, so cycles make no sense
whatsoever.
You need to use a cursor (explicit or hidden in a called procedure) to
convert the result table into a sequential structure that can have
loops. While you are catching up on the foundations of RM, you might
also want to learn ISO-11179 so that you stop using that silly "tbl-"
in your code, Standard SQL keywords, etc. You are writing SQL like a
procedural or OO programmer because you have not got the right mindset
yet.|||CELKO,
As I mentioned in my previous message, I have not had any official training
in SQL as my employer has not yet put me on a course, I have had to try and
learn this by myself so you will have to forgive my wording and code. I am
a VB programmer so most of the stuff I do I do in VB as I do not know SQL
very well and I find it easier to do in VB.
Thanks for your reply.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1134664262.456898.41440@.g14g2000cwa.googlegroups.com...
> Interesting, since SQL does not have fields and columns are a totally
> different concept.
>
> More interesting, since SQL does not have records and rows are a
> totally different concept. Tables are sets and not files;
> sets by definition have no ordering, so cycles make no sense
> whatsoever.
> You need to use a cursor (explicit or hidden in a called procedure) to
> convert the result table into a sequential structure that can have
> loops. While you are catching up on the foundations of RM, you might
> also want to learn ISO-11179 so that you stop using that silly "tbl-"
> in your code, Standard SQL keywords, etc. You are writing SQL like a
> procedural or OO programmer because you have not got the right mindset
> yet.
>|||JT,
Thank you very much for your reply. I really appreciate your time in
replying in such detail.
I will have a crack @. this today.
Thanks again.
"JT" <someone@.microsoft.com> wrote in message
news:eNhERNZAGHA.2560@.TK2MSFTNGP12.phx.gbl...
> I've taken an example cursor from Books Online and adjusted it somewhat to
> fit your situation. However, it's just a rough draft and you will need to
> complete it. Basically, the cursor allows you to iternate through the
> query result one row at a time, giving you the ability to populate
> variables and exec the xp_sendmail call. Every column that you plan to
> reference will need to be assigned a variable. I've also added the
> FAST_FORWARD option so it should use less resources.
> DECLARE surveillance_cursor CURSOR FAST_FORWARD FOR
> SELECT dbo.tbl_surveillance.s_id as REG_NO,
> dbo.tbl_surveillance_dates.sd_urn as URN,
> dbo.tbl_surveillance_dates.sd_reviewing_officer as OFFICER,
> dbo.tbl_surveillance_dates.sd_renewal_date as
> RENEWAL_DATE, dbo.tbl_email.e_officer_email AS OFFICER_EMAIL
> FROM dbo.tbl_surveillance INNER JOIN
> dbo.tbl_surveillance_dates ON
> dbo.tbl_surveillance.s_id = dbo.tbl_surveillance_dates.sd_s_id LEFT OUTER
> JOIN
> dbo.tbl_email ON dbo.tbl_surveillance_dates.sd_e_id =
> dbo.tbl_email.e_id
> WHERE getdate() > dateadd(day, -7, sd_renewal_date)
>
> OPEN surveillance_cursor
> -- Perform the first fetch and store the values in variables.
> -- Note: The variables should be in the same order as the columns in the
> SELECT statement.
> FETCH NEXT FROM surveillance_cursor
> INTO @.recipient, @.Reg_no, @.RENEWAL_DATE, etc.
> -- Check @.@.FETCH_STATUS to see if there are any more rows to fetch.
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.sbj = ?
> select @.msg = @.Reg_no + ', ' + @.URN + ', ' + @.RENEWAL_DATE + etc.
> exec master..xp_sendmail @.recipients= @.recipient, @.subject = @.sbj,
> @.message=@.msg
> -- This is executed as long as the previous fetch succeeds.
> FETCH NEXT FROM surveillance_cursor
> INTO @.recipient, @.Reg_no, @.RENEWAL_DATE
> END
> CLOSE surveillance_cursor
> DEALLOCATE surveillance_cursor
>
> "Damon" <nonsense@.nononsense.com> wrote in message
> news:cCXnf.28255$XZ6.26473@.newsfe1-gui.ntli.net...
>

Loop

Hi ,
I like to build a query to generate a virtual running number from the
table.
Something like "Select @.X , fieldA from TableA" . The result should look
like ,
@.x FieldA
-- --
1 AAA
2 BBB
3 CCC
Please help
Travis Tan
On Wed, 5 Oct 2005 00:04:04 -0700, Travis wrote:

>Hi ,
> I like to build a query to generate a virtual running number from the
>table.
>Something like "Select @.X , fieldA from TableA" . The result should look
>like ,
>@.x FieldA
>-- --
>1 AAA
>2 BBB
>3 CCC
>Please help
Hi Travis,
Maybe something like this?
SELECT COUNT(*) AS [@.x], a.FieldA
FROM TableA AS a
INNER JOIN TableA AS b
ON b.FieldA <= a.FieldA
GROUP BY a.FieldA
(untested - see www.aspfaq.com/5006 if you prefer a tested solution)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Loooping through SELECT values in SQL

Hi,

Environment - VB.NET, ASP.NET, SQL Server 2000.

In a SQL User-Defined Function, I am selecting a row which returns multiple values. I need to construct one single string out of those returned values. To do that, I am using CUROSR.

Now, CURSOR is expensive operation. If there are 1000 users at a time, it will consume lot of resources.

Is there a way, I can construct this String without using CURSORs??

Please advice. Thanks

PankajYou should be able to do something like this to concatenate the columns together:

DECLARE @.myResult VARCHAR(8000)
SET @.myResult = ''
SELECT
 @.myResult = @.myResult + myColumn1 + myColumn2 + myColumn3 + myColumn4
FROM
 myTable

Terri|||my mistake, the multiple values selected are from the same column (multiple rows)|||That's OK. you can still use the same method:

DECLARE @.myResult VARCHAR(8000)
SET @.myResult = ''
SELECT
 @.myResult = @.myResult + myColumn1
FROM
 myTable

Terri|||Even though the described SQL works this not a supported T-SQL approach. As per the documentation deom BOL on Select statement :
:: If the SELECT statement returns more than one value, the variable is assigned the last value returned.::

And such an approach is dangerous and can be removed in future builds or service packs. Donot rely on them.

Check out a thread posted by Umachander (MVP, SQL Server) on the same at the public.sqlserver.programmming newsgroup. http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=e7dcU%23jiAHA.2088%40tkmsftngp03

There are many other approaches to the same problem. Some of the solutions have been outlined at : http://www.extremeexperts.com/sql/articles/IterateTSQLResult.aspxsql

LookupCube: Unexpected Results with StrToSet

Hi.
SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
{StrToSet(CStr(LookupCube("Sales", "SetToStr({[Product].Member
s})")))} ON
ROWS
FROM [Sales]
Does anyone have any idea why the above query fails with an "Unknown
internal error"? When I replace the "[Product].Members" string with
"[Time].Members", it works fine.
The following query, which I think is quite similar, works fine:
SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
{StrToSet("[Product].Members")} ON ROWS
FROM [Sales]
Thanks.I believe this happens because the list of product members exceeds the
maximum string length that can be handled by "SetToStr", "LookupCube", or
both.
This is supported by testing your query replacing [Product].Members by
Head([Product].Members, n).
Up to n = 344 this works, then it breaks.
At this point , the string length should be over 32K, which is a likely limi
t.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html
"John" wrote:

> Hi.
> SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
> {StrToSet(CStr(LookupCube("Sales", "SetToStr({[Product].Memb
ers})")))} ON
> ROWS
> FROM [Sales]
> Does anyone have any idea why the above query fails with an "Unknown
> internal error"? When I replace the "[Product].Members" string with
> "[Time].Members", it works fine.
> The following query, which I think is quite similar, works fine:
> SELECT {[Customers].[All Customers].[Canada]} ON COLUMNS,
> {StrToSet("[Product].Members")} ON ROWS
> FROM [Sales]
> Thanks.
>
>

Lookup Transformation: How can I join tables in different databases

I want to join tables that reside in different databases (same instance). The Lookup object only lets me select from one data source. Is there anyway to lookup using more than one data source? I can write a SQL query to lookup across databases.

Is this a feature that is being added to future releases?

I appreciate your help

-Marcus
Are these SQL Server databases? If so you can create a view in one database that selects data from another - thus making it appear as though the data is all in the same DB.

Voila!

-Jamie|||lol... didn't think of that one...

Thanks :)|||Still onthe lookup subject, I have noticed that if I write a simpley query in the box "User results of an SQL query" say select * from DB1.dbo.tablea, DB2.dbo.tableb, I can then click the "Build Query" button and hey presto both tables are then available for me to work with even thought they are from different databases. I do notice that the top left corner of the table boxes have an arrow.

However if I go straight to "Build Query", add my first table by right clicking and selecting "add table" there is no arrow in the top left corner of the table box. I can then modify the sql statment manually to include the table from the other database. This table then appears in the top window with an arrow in the top left corner of it's box.

Is this a bug? should there actually be an optin to add a table from an alternative database and it's missing? As detailed above I can manually add the tables and the tool recognisines them.

Has anyone else seen this? Has this been fixed in later builds? I'm using Junes.

Thanks|||

What you have observed is not a bug.

We do supply an option to add tables. But we only list tables in the current database context. Tables in other databases have to be added manually. This is the behavior in June CTP and are not changed since then.

|||

Do you know if this will be changed in future CTP's?

Thanks

|||

I do not think so.

But please feel free to open a DCR via BetaPlace.

|||What about MS Access using DAO? In DAO you use something like this.

CDaoRecordset rset(&db);
rset.Open(dbOpenSnapshot, SQLquery, dbReadOnly);

This does not allow for binding to more than one database at the time. Therefore, how can you do a join query between two tables residing in different databases?

Thank you.

looking to collect distinct date part out of datetime field

from this, circdate being a datetime field:
SQLQuery = "select distinct circdate from circdata order by circdate"

I need the distinct date portion excluding the time part.

this has come about when I discovered
I am inserting and updating some datetime values with the same value,
but for some reason, the values are always off by a few seconds. I set
a variable called SetNow assigned to NOW and then set the datetime
fields to this SetNow variable. Then when I collect the distinct date
time I am assuming they will have the same values recorded in
circdate, but no, they are off by several seconds. Makes no sense to me
at all. I tried renaming the variable several times but it makes no
difference at all.
any help appreciated, thanks.SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"

I think I solved it
any one see a problem with this?
thanks
how does the '1' parameter affect the output as I know there are
several choices|||sdowney717@.msn.com (sdowney717@.msn.com) writes:
> SQLQuery = "SELECT distinct CONVERT(char,circdate,1) from circdata"
> I think I solved it
> any one see a problem with this?
> how does the '1' parameter affect the output as I know there are
> several choices

1 is a format parameter that controls how the datetime value is formatted.
You can read about these in the topic CAST and CONVERT in Books Online.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||http://www.aspfaq.com/show.asp?id=2464
shows all the outputs
Was wondering though if it wont use an index on a convert.|||sdowney717@.msn.com (sdowney717@.msn.com) writes:
> http://www.aspfaq.com/show.asp?id=2464
> shows all the outputs
> Was wondering though if it wont use an index on a convert.

For the query you gave,

SELECT distinct CONVERT(char,circdate,1) from circdata

this is not an issue. If there is an index on cricdate, SQL Server will
use that index in the most effective, that is to scan the index, because
that is what the query calls for, with or without the convert().

On the other hand

SELECT col1, col2, col3 FROM circdata
WHERE CONVERT(char, circdate, 1) = @.val

will probably not use the index, and in any case the query will not seek
the index, that is lookup the value through the B-tree. This is because
the index is sorted on the datetime value, not on a character value.

To list all rows for a given date you can do:

SELECT col1, col2, col3 FROM circdata
WHERE circdate >= @.val AND circdate < dateadd(DAY, @.val, 1)

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Also refer this to know how to query on dates
http://www.karaszi.com/SQLServer/info_datetime.asp

Madhivanan

looking for something like SHOW CREATE TABLE

After I do a
SELECT * INTO #temp_table FROM x WHERE y
I would like to see a CREATE TABLE statement for #temp_table somehow.Hi
"metaperl" wrote:
> After I do a
> SELECT * INTO #temp_table FROM x WHERE y
> I would like to see a CREATE TABLE statement for #temp_table somehow.
>
You can view the table in tempdb, but it will be called something like
#temp_table_____________________________12345678. The object browser (F8)in
Query Analyser should show it but the scripting options will not work!
The table structure would be the same as table x, so you can script that and
alter the script to remove contraints and indexes.
John|||On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> The table structure would be the same as table x, so you can script that and
> alter the script to remove contraints and indexes.
table x was hypothetical. in fact, this final temp table is the result
of many joins of many temp tables... so it's much easier to see the
schema of this final output temp table than to trace back through all
the joins and try to figure out its schema that way
> John
Terrence!|||On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "metaperl" wrote:
> > I would like to see a CREATE TABLE statement for #temp_table somehow.
> You can view the table in tempdb, but it will be called something like
> #temp_table_____________________________12345678. The object browser (F8)in
> Query Analyser should show it but the scripting options will not work!
What is tempdb? I am on MS-SQL 2000 still. I did not see such a table
in the Catalog (I use Synametrics WinSQL, we dont have anything else
installed for analyzing tables).|||Hi
"metaperl" wrote:
> On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > "metaperl" wrote:
> >
> > > I would like to see a CREATE TABLE statement for #temp_table somehow.
> >
> > You can view the table in tempdb, but it will be called something like
> > #temp_table_____________________________12345678. The object browser (F8)in
> > Query Analyser should show it but the scripting options will not work!
> What is tempdb? I am on MS-SQL 2000 still. I did not see such a table
> in the Catalog (I use Synametrics WinSQL, we dont have anything else
> installed for analyzing tables).
>
Tempdb is one of the system databases used for things such as temporary
tables and is sometimes used for other system actions such as rebuilding
indexes.
You should download books online and read the administration and
architecture sections. SQL 2005 books online can be downloaded from
http://go.microsoft.com/fwlink/?linkid=50478 SQL 2000 can be downloaded from
http://technet.microsoft.com/en-us/sqlserver/bb331733.aspx
You probably have MSDE or SQLExpress in which case download Microsoft SQL
Server Management Studio Express
http://www.microsoft.com/downloads/details.aspx?FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en if you are using SQL Express or
if you have SQL 2000 the SQL Server Web Data Administrato
http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&DisplayLang=en
If you don't want to do this, then you could write some DMO/SMO code to get
the table definitions (see Books Online). If you changed the statement to not
use a temporary table then you could get the definition of the table that you
have created rather than using the source tables. The T-SQL command sp_help
may give you enough information to create your own CREATE TABLE statement use
EXEC sp_help MyTable
John

Looking for help with an SQL query.

well, i want to select rows by date from a file. but I want
in particular one sum of values from the rows that fall WITHIN
a supplied date range, and a second sum of values from the
rows that have dates FOR ALL TIME UP TO the second date in the date range.

the former, by itself, might be:

SELECT id, value RangedValue
FROM myFile
WHERE date >= [lower date range value]
AND date <= [higher date range value]

and the latter, by itself, might be:

SELECT id, value AllTimeValue
FROM myFile
WHERE date <= [higher date range value]

but I need to grab the two separate sums (RangedValue and AllTimeValue)
using one SQL statement.

I'm thinking that the UNION might work, but my preliminary results are
taking a huge amount of time, and apparently smegging up the (rather
stupid, external) report generator to boot.
If you like the UNION idea, please give me an example.

I should mention that the report generator at very least can do the
(summing) part. I could do the summing at either the SQL level or the
report level. I should also mention that although I only talk about the
one file here (myFile), in fact I need to join to and pull values from
its "parent" file, although I don't think that that should change my
fundamental problem.

Any ideas?

Cheers in advance!

-GlennYou could try something like this:
Select Id
, Sum(Value) Alltimevalue
, Sum(Case
When Date >= [Lower Date Range Value] Then Value
Else 0 End) As Rangedvalue
From Myfile
Where Date <= [Higher Date Range Value];
;)