Need to sum columns: code

Showing posts with label code. Show all posts

Friday, March 30, 2012

NetWork Credentials. Please help

I need to see report using ReportViewer. Problem is when I want to see report
as customer it asks for Username and Password. So I need to code in page load
like
Imports Microsoft.Samples.ReportingServices.ReportViewer
Dim rs As ReportingService = New ReportingService
rs.Credentials = New NetworkCredential(username, password, domain)
Dim extensions() As Extension = rs.ListExtensions(ExtensionTypeEnum.Delivery)
Problem is it says "ReportingService" Type not defined. Dont know what I am
missing. Please any one help.Have you addded a Web Reference to the report server web service?
If not, use Project -> Add Web Reference and browse to
http://<server>/reportserver/reportservice.asmx.
Watch what namespace the web reference goes into, you'll need to use
Imports on the namespace or fully qualify the ReportingService type.
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Tue, 19 Oct 2004 16:35:03 -0700, "LordAli"
<LordAli@.discussions.microsoft.com> wrote:
>I need to see report using ReportViewer. Problem is when I want to see report
>as customer it asks for Username and Password. So I need to code in page load
>like
>Imports Microsoft.Samples.ReportingServices.ReportViewer
> Dim rs As ReportingService = New ReportingService
> rs.Credentials = New NetworkCredential(username, password, domain)
> Dim extensions() As Extension =>rs.ListExtensions(ExtensionTypeEnum.Delivery)
>Problem is it says "ReportingService" Type not defined. Dont know what I am
>missing. Please any one help.

Friday, March 23, 2012

Nesting a SP inside a Query

This does not work:
select * from (exec sp_lock) as ex
I want to process sp_lock in code as a table. How can I do this?
SeanSean
Create a table that will contain all columns from sp_lock stored procedure
and the perform
insert into #t exec sp_lock
select * from #t
"Sean Smith" <dremoorSPAMSUX@.msn.com> wrote in message
news:ePz9bgLUFHA.628@.tk2msftngp13.phx.gbl...
> This does not work:
> select * from (exec sp_lock) as ex
> I want to process sp_lock in code as a table. How can I do this?
> Sean
>|||Thanks but...
insert into #t exec sp_lock
gives...
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#t'.|||ignore than last message... I'm doing 10 things at once
"Sean Smith" <dremoorSPAMSUX@.msn.com> wrote in message
news:OD$E9WMUFHA.2540@.tk2msftngp13.phx.gbl...
> Thanks but...
> insert into #t exec sp_lock
> gives...
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name '#t'.
>|||You have to create the temp table first. Try this:
Create Table #Locks
(
spid int
, dbid int
, objId int
, indid int
, type nvarchar(10)
, resource ntext
, mode nvarchar(2)
, status nvarchar(25)
)
Insert #Locks
Exec sp_lock
Granted, I'm using the Force in determining the data types and sizes for the
various columns.
Thomas
"Sean Smith" <dremoorSPAMSUX@.msn.com> wrote in message
news:ePz9bgLUFHA.628@.tk2msftngp13.phx.gbl...
> This does not work:
> select * from (exec sp_lock) as ex
> I want to process sp_lock in code as a table. How can I do this?
> Sean
>sql

Wednesday, March 21, 2012

Nested Stored Proceudre Error

Hi Gurus;
I m calling a stroed procedure B inside the stored procedure A from VB. If
error occured in stored procedure B VB code does detect that error nor it
raise any error why? So I m unable to do error handling if Procedure B
failed.
Any suggestion to solve this problem
AnsariIs procedure A returning some resultset? If so, you have to process that,
and move on to the next recordset, using the Recordset.NextRecordset method,
to get the error. Also make sure you have SET NOCOUNT ON at the beginning of
your stored procedure code.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:e230QN2iEHA.140@.TK2MSFTNGP12.phx.gbl...
Hi Gurus;
I m calling a stroed procedure B inside the stored procedure A from VB. If
error occured in stored procedure B VB code does detect that error nor it
raise any error why? So I m unable to do error handling if Procedure B
failed.
Any suggestion to solve this problem
Ansari|||MM
There are some errors that just terminate the bacth , you are not be able to
captute them ( like Primary Key Violation)
CREATE PROC Parent
@.ord INT
AS
SELECT OrderId FROM Orders WHERE OrderId =@.ord
IF @.@.ERROR >0
RETURN 99 --Failure
ELSE
RETURN 0 --Success
GO
CREATE PROC Child
@.ord INT
AS
DECLARE @.err INT
EXEC @.err=Parent @.ord
IF @.err =99
RAISERROR ('We have a problem',16,1)
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:e230QN2iEHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi Gurus;
> I m calling a stroed procedure B inside the stored procedure A from VB. If
> error occured in stored procedure B VB code does detect that error nor it
> raise any error why? So I m unable to do error handling if Procedure B
> failed.
> Any suggestion to solve this problem
> Ansari
>
>sql

Nested Stored Proceudre Error

Hi Gurus;
I m calling a stroed procedure B inside the stored procedure A from VB. If
error occured in stored procedure B VB code does detect that error nor it
raise any error why? So I m unable to do error handling if Procedure B
failed.
Any suggestion to solve this problem
Ansari
Is procedure A returning some resultset? If so, you have to process that,
and move on to the next recordset, using the Recordset.NextRecordset method,
to get the error. Also make sure you have SET NOCOUNT ON at the beginning of
your stored procedure code.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:e230QN2iEHA.140@.TK2MSFTNGP12.phx.gbl...
Hi Gurus;
I m calling a stroed procedure B inside the stored procedure A from VB. If
error occured in stored procedure B VB code does detect that error nor it
raise any error why? So I m unable to do error handling if Procedure B
failed.
Any suggestion to solve this problem
Ansari
|||MM
There are some errors that just terminate the bacth , you are not be able to
captute them ( like Primary Key Violation)
CREATE PROC Parent
@.ord INT
AS
SELECT OrderId FROM Orders WHERE OrderId =@.ord
IF @.@.ERROR >0
RETURN 99 --Failure
ELSE
RETURN 0 --Success
GO
CREATE PROC Child
@.ord INT
AS
DECLARE @.err INT
EXEC @.err=Parent @.ord
IF @.err =99
RAISERROR ('We have a problem',16,1)
"M.M Ansari" <mudasar_ansari@.hotmail.com> wrote in message
news:e230QN2iEHA.140@.TK2MSFTNGP12.phx.gbl...
> Hi Gurus;
> I m calling a stroed procedure B inside the stored procedure A from VB. If
> error occured in stored procedure B VB code does detect that error nor it
> raise any error why? So I m unable to do error handling if Procedure B
> failed.
> Any suggestion to solve this problem
> Ansari
>
>

Nested set show leaves of parent

Hello,

I have the following code which will show all bottom level leaf nodes of the hierachy:

SELECT name
FROM tree
WHERE rgt = lft + 1;

I'd like to be able to filter results by a node. For example in a tree such as:

Products

ReleaseProduct

Release1

Release build 1

Release build 2

Release 2

Release 2 build 1

Release 2 build 2

Build Product

Build 1

Build 2

If Build 2 is chosen (any node with no children) I'd like to just show the Buuild 2, if ReleaseProduct is chosen Release build 1, Release build 2, Release 2 build 1 and Release 2 build 2 will be shown and if BuildProduct is chosen I'd like to display Build 1, Build 2.

I understand the prinicipals but my SQL is quite lacking anything further than the select, where statements. If anyone could please lend me a little advice on how to go about this I would be very grateful!

Thanks :)

Hello,

Can you post the schema of the table in question and what version of SQL Server you are using?

If 2005, a recursive CTE sounds like it may suit, otherwise a more "creative" solution may apply. let us know the specifics and I'm sure we can help out.

Cheers,

Rob

|||Thank's for the quick reply!

The schema is as follows:

CREATE TABLE site_category(
site_id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(20) NOT NULL,
lft INT NOT NULL,
rgt INT NOT NULL
);

So a site may be a root, parent or child depending on the left and right values of the nodes in the hierachy. I'm using 2005 Express.

Thanks for the help!|||

Hello,

I don't know what lft or rgt is, but I'm going to assume that they contain the site_id of the parent node. So, to simplify this, let's call it ParentSiteID:

with Sites(SiteName, site_id, ParentID, NestLevel)
AS
(
SELECT [name], site_id, parentSiteID, 0
FROM site_category
WHERE [name] = 'Site123'
UNION ALL
SELECT sc.[Name], sc.Site_ID, s.Site_ID,(NestLevel + 1)
FROM Sites s
JOIN site_category sc ON s.Site_ID = sc.ParentSiteID
)
SELECT *
FROM Sites

The above example will return "Site123" and all child nodes therein (including any nested relationships). The NestLevel column indicates how deep the nesting level is. You'll need to adjust this to cater for your lft/rgt columns...

Cheers,

Rob

|||

The lft and rgt fields store values used to determine the level in the hierachy. The example from the MySQL site I am using as a guide is:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Following this I have got to the heading 'Finding the Depth of the Nodes' which produces the results I am after.

Where I'm having trouble is the heading 'Find the Immediate Subordinates of a Node' which is exactly what I need and is explained with code but I just can't figure it out! I feel there may be some subtle differences in the SQL used in this MySQL example and the TSQL SQL Server is expecting. Not to mention my SQL knowledge isn't great at this point!

I havn't tried your example but feel this post may offer a better explanation as (I may be wrong) your example looks like it assumes I am using an Adjacency List Model.

I appreciate your time! :)

|||

Hello,

OK, I understand what you're trying to do:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT TOP 100 node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, depth, node.lft
HAVING depth <= 1
ORDER BY node.lft;

Does that do what you want?

Cheers,

Rob

|||

That works exactly how I want!

Is the TOP keyword and value an approximation of the rows to be returned to be returned, as the complete result set is not loaded into memory?

Thanks :)

|||

Actually, the only reason to use TOP in the sub query is because without it, you cannot use an order by. So you could actually remove it and the corresponding order by:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
nested_category AS parent,
nested_category AS sub_parent,
(
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.name = 'PORTABLE ELECTRONICS'
GROUP BY node.name, node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.name = sub_tree.name
GROUP BY node.name, depth, node.lft
HAVING depth <= 1
ORDER BY node.lft;

Cheers,

Rob

|||Oh I see, Thanks again!sql

Monday, March 19, 2012

Nested Loop Join - need help :)

i'm running the following code on Ms SQL Server 2000, Query Analyzer to analyze the result of Nested Loop Join.

SET STATISTICS PROFILE ON
GO
SELECT pdN.ProductID, pdN.ProductName,
spN.CompanyName, spN.ContactName
FROM dbo.ProductsNew pdN
INNER JOIN dbo.SuppliersNew spN
ON pdN.SupplierId = spN.SupplierId
GO

but the execution plan give me the following result :-

http://i31.photobucket.com/albums/c366/i3lu3fun/executionplan.jpg

instead of using nested, why does it using hash join? is there anything wrong with my code?Hi

Welcome to the forum :D

Nowt wrong with your query. I didn't enlarge your image however the optimiser will select the best plan it can (within certain provisos e.g. it selects the best plan within a time limit, it bases it's plan on available statistics etc.). As such - it is probable for this query that a hash join is better than a nested loop join. BOL illustrates such cases:

A nested loops join is particularly effective if the outer input is quite small and the inner input is preindexed and quite large. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are far superior to both merge joins and hash joins. In large queries, however, nested loops joins are often not the optimal choice.

Short of using hints (which is a bad idea unless you really know what you are doing and, arguably, not even then) all you can do is make sure that you write good, efficient SQL (as you have), ensure statistics are up to date and indexes are optimal. SQL Server does the rest.

HTH|||Thank you for replying :D

Is it possible for me to still get the result of using nested loop join because i need to make a comparison between the execution time of using nested loop, hash & merge join. Will i get the result that i want if i use FORCE option?|||You can force these using Join Hints - check the BOL entries for "FROM" and "Hints".|||BTW - out of curiosity - how come you want to compare the three rather than leave it up to the optimiser?|||hehe, thanks.
I'm doing my FYP, and i need this results to be included in the report. Plus, i need to come up with a better algorithm, to retrieve data in distributed database. Thanks again for ur help.|||where is it the BOL entries :p, sorry newbie here i couldn't find it.|||I'm doing my FYP, and i need this results to be included in the report.
Aw - an RFH.

Ah well - I noticed a little gotcha that I didn't know re hints (unsurprising as you can probably tell I don't tend to use them) - see if you can spot it. Having reread BOL I think that it is referred to but it is rather under stated...|||where is it the BOL entries :p, sorry newbie here i couldn't find it.Well - you are looking for Join Hints so I guess you need to search for... :rolleyes:

Monday, March 12, 2012

nested ifs in custom code

Is it not possible to have nested ifs in a custom code function? I keep getting an error message when I try it.Never mind. Sorry!

Nested Cursors

What is the best way to nest cursors?

This code does not seem to be returning me all of the data.

Code Snippet

DECLARE element_Cursor CURSOR FOR

SELECT ElementTypeRecNo

FROM dbo.tblTemplateElementType

where TemplateRecno = @.TemplateRecNo

OPEN element_cursor

FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno

--delete from tblElementCPO

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.Count = count (*)

from tblProjTypeSet

where ProjRecno = @.ProjRecNo

if @.Count > 0

begin

select @.ProjTypeRecno = ProjTypeRecno

from tblProjTypeSet

where ProjRecno = @.ProjRecNo

select @.Count = count (*)

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

and ProjTypeRecno = @.ProjTypeRecNo

if @.Count > 0

begin

DECLARE ElementTypeDep_Cursor CURSOR FOR

SELECT ElementTypeDepRecNo, PreElementTypeRecNo,

PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,

Description

FROM tblElementTypeDep

WHERE (TemplateRecNo = @.TemplateRecNo)

AND (ProjTypeRecNo = @.ProjTypeRecno)

AND (PreElementTypeRecNo = @.ElementTypeRecno)

OPEN ElementTypeDep_cursor

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementTypeRecNo,

@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,

@.Description

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.PreElementRecNo = ElementRecno

from tblElementCPO

where ProjRecNo = @.ProjRecNo

and IssueRecno = @.IssueRecNo

and ElementTypeRecno = @.PreElementTypeRecno

if @.PreElementRecno is not null

begin

select @.PostElementRecNo = ElementRecno

from tblElementCPO

where ProjRecNo = @.ProjRecNo

and IssueRecno = @.IssueRecNo

and ElementTypeRecno = @.PostElementTypeRecno

if @.PostElementRecno is not null

begin

select @.Count = count (*)

from tblElementDepCPO

where ElementTypeDepRecno = @.ElementTypeDepRecno

and PreElementRecNo = @.PreElementRecNo

and PostElementRecno = @.PostElementRecno

if @.Count = 0

begin

INSERT INTO tblElementDepCPO

(ElementTypeDepRecNo, PreElementRecNo,

PostElementRecNo, ElapsedTimeDueDates,

ElapsedTimePlanDates, Description,

ChangeDate, ChangePerson)

VALUES (@.ElementTypeDepRecno, @.PreElementRecNo,

@.PostElementRecno, @.ElapsedTimeDueDates,

@.ElapsedTimePlanDates, @.Description,

GETDATE(), CURRENT_USER)

end

select @.Count = count (*)

from tblElementAttemptCPO

where ElementRecNo = @.PostElementRecNo

if @.Count = 0

begin

select @.Count = count (*)

from tblElementAttemptCPO

where ElementRecNo = @.PostElementRecNo

if @.Count = 0

begin

select @.NextPlanDate = ProjectedCompletionDate,

@.NextDueDate = RequiredCompletionDate

from tblElementAttemptCPO

where ElementRecno = @.PreElementRecNo

end

else

begin

select @.NextPlanDate = @.StartDate

select @.NextDueDate = @.StartDate

end

select @.NextPlanDate =

dbo.fncAddBusinessDays (@.NextPlanDate, @.ElapsedTimePlanDates)

select @.NextDueDate =

dbo.fncAddBusinessDays (@.NextDueDate, @.ElapsedTimePlanDates)

insert into tblElementAttemptCPO (ElementRecno,

ProjectedCompletionDate, RequiredCompletionDate,

ProjectedStartDate, RequiredStartDate,

ActualStartDate, ActualCompletionDate, AttemptNum,

IsCompleted, IsStarted, ResponsibleRoleTypeRecno,

ChangeDate, ChangePerson)

values (@.PostElementRecno,

@.NextPlanDate, @.NextDueDate,

'1/11/1900', '1/11/1900',

'1/11/1900', '1/11/1900', 0,

0, 0, 0,

GETDATE(), CURRENT_USER)

end

FETCH NEXT

FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementTypeRecNo,

@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,

@.Description

END

CLOSE elementTypeDep_Cursor

DEALLOCATE elementTypeDep_Cursor

end

FETCH NEXT FROM element_Cursor into @.ElementTypeRecno

END

CLOSE element_Cursor

DEALLOCATE element_Cursor

end

There is a single insert statement hidden within the cursors. Since there is no select statement, there wouldn't be any data returned. Exactly what are you trying to return?

Also, I suggest you post DDL+sample data (i.e. insert statement)+expected output here. We might be able to help draft a non-cursor version.

|||As oj implied, cursors are extremely taxing to a SQL Server and generally should be avoided if possible. Is some cases, it's not possible. But if you'll post the info that oj requested, perhaps this is a case where they can be avoided.

Joe

Nested Cursors

What is the best way to nest cursors?

This code does not seem to be returning me all of the data.

Code Snippet

DECLARE element_Cursor CURSOR FOR

SELECT ElementTypeRecNo

FROM dbo.tblTemplateElementType

where TemplateRecno = @.TemplateRecNo

OPEN element_cursor

FETCH NEXT FROM Element_Cursor into @.ElementTypeRecno

--delete from tblElementCPO

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.Count = count (*)

from tblProjTypeSet

where ProjRecno = @.ProjRecNo

if @.Count > 0

begin

select @.ProjTypeRecno = ProjTypeRecno

from tblProjTypeSet

where ProjRecno = @.ProjRecNo

select @.Count = count (*)

FROM dbo.tblElementTypeDep

where TemplateRecno = @.TemplateRecNo

and ProjTypeRecno = @.ProjTypeRecNo

if @.Count > 0

begin

DECLARE ElementTypeDep_Cursor CURSOR FOR

SELECT ElementTypeDepRecNo, PreElementTypeRecNo,

PostElementTypeRecNo, ElapsedTimeDueDates, ElapsedTimePlanDates,

Description

FROM tblElementTypeDep

WHERE (TemplateRecNo = @.TemplateRecNo)

AND (ProjTypeRecNo = @.ProjTypeRecno)

AND (PreElementTypeRecNo = @.ElementTypeRecno)

OPEN ElementTypeDep_cursor

FETCH NEXT FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementTypeRecNo,

@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,

@.Description

WHILE @.@.FETCH_STATUS = 0

BEGIN

select @.PreElementRecNo = ElementRecno

from tblElementCPO

where ProjRecNo = @.ProjRecNo

and IssueRecno = @.IssueRecNo

and ElementTypeRecno = @.PreElementTypeRecno

if @.PreElementRecno is not null

begin

select @.PostElementRecNo = ElementRecno

from tblElementCPO

where ProjRecNo = @.ProjRecNo

and IssueRecno = @.IssueRecNo

and ElementTypeRecno = @.PostElementTypeRecno

if @.PostElementRecno is not null

begin

select @.Count = count (*)

from tblElementDepCPO

where ElementTypeDepRecno = @.ElementTypeDepRecno

and PreElementRecNo = @.PreElementRecNo

and PostElementRecno = @.PostElementRecno

if @.Count = 0

begin

INSERT INTO tblElementDepCPO

(ElementTypeDepRecNo, PreElementRecNo,

PostElementRecNo, ElapsedTimeDueDates,

ElapsedTimePlanDates, Description,

ChangeDate, ChangePerson)

VALUES (@.ElementTypeDepRecno, @.PreElementRecNo,

@.PostElementRecno, @.ElapsedTimeDueDates,

@.ElapsedTimePlanDates, @.Description,

GETDATE(), CURRENT_USER)

end

select @.Count = count (*)

from tblElementAttemptCPO

where ElementRecNo = @.PostElementRecNo

if @.Count = 0

begin

select @.Count = count (*)

from tblElementAttemptCPO

where ElementRecNo = @.PostElementRecNo

if @.Count = 0

begin

select @.NextPlanDate = ProjectedCompletionDate,

@.NextDueDate = RequiredCompletionDate

from tblElementAttemptCPO

where ElementRecno = @.PreElementRecNo

end

else

begin

select @.NextPlanDate = @.StartDate

select @.NextDueDate = @.StartDate

end

select @.NextPlanDate =

dbo.fncAddBusinessDays (@.NextPlanDate, @.ElapsedTimePlanDates)

select @.NextDueDate =

dbo.fncAddBusinessDays (@.NextDueDate, @.ElapsedTimePlanDates)

insert into tblElementAttemptCPO (ElementRecno,

ProjectedCompletionDate, RequiredCompletionDate,

ProjectedStartDate, RequiredStartDate,

ActualStartDate, ActualCompletionDate, AttemptNum,

IsCompleted, IsStarted, ResponsibleRoleTypeRecno,

ChangeDate, ChangePerson)

values (@.PostElementRecno,

@.NextPlanDate, @.NextDueDate,

'1/11/1900', '1/11/1900',

'1/11/1900', '1/11/1900', 0,

0, 0, 0,

GETDATE(), CURRENT_USER)

end

FETCH NEXT

FROM ElementTypeDep_Cursor

into @.ElementTypeDepRecno, @.PreElementTypeRecNo,

@.PostElementTypeRecno, @.ElapsedTimeDueDates, @.ElapsedTimePlanDates,

@.Description

END

CLOSE elementTypeDep_Cursor

DEALLOCATE elementTypeDep_Cursor

end

FETCH NEXT FROM element_Cursor into @.ElementTypeRecno

END

CLOSE element_Cursor

DEALLOCATE element_Cursor

end

There is a single insert statement hidden within the cursors. Since there is no select statement, there wouldn't be any data returned. Exactly what are you trying to return?

Also, I suggest you post DDL+sample data (i.e. insert statement)+expected output here. We might be able to help draft a non-cursor version.

Joe

Nested cursors

I have something kind of bizarre going on and I'm not sure how to
troubleshoot it, I've put print statements all over in my code to try an fin
d
out where it's breaking but it doesn't seem to be breaking at any specific
point, there is no error message, processing just stops about the time the
machine runs out of memery, I suspect it's something with the number of
cursors I have open at one time. I can remove some of the nested cursors and
the problem goes away so I know it's memory related... Anybody have some
design advice.? I'm just moving/transforming data from a legacy database to
a
new system.. I tried to post my code but it's too big, I know that should be
a clue right there but I'm trying to bail a buddy out on a project that is
way overdue so opting for quick and dirty.. I can email the script, shoot me
an email..
Thanks!!
Danhi,
Let me know all that stuff at vtam13@.terra.es
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Alien2_51" wrote:

> I have something kind of bizarre going on and I'm not sure how to
> troubleshoot it, I've put print statements all over in my code to try an f
ind
> out where it's breaking but it doesn't seem to be breaking at any specific
> point, there is no error message, processing just stops about the time the
> machine runs out of memery, I suspect it's something with the number of
> cursors I have open at one time. I can remove some of the nested cursors a
nd
> the problem goes away so I know it's memory related... Anybody have some
> design advice.? I'm just moving/transforming data from a legacy database t
o a
> new system.. I tried to post my code but it's too big, I know that should
be
> a clue right there but I'm trying to bail a buddy out on a project that is
> way overdue so opting for quick and dirty.. I can email the script, shoot
me
> an email..
> Thanks!!
> Dan
>|||It went away with FAST_FORWARD.. hmm.. Maybe someone can tell why the
READ_ONLY is so much more costly on resources...?
"Enric" wrote:
> hi,
> Let me know all that stuff at vtam13@.terra.es
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
>
> "Alien2_51" wrote:
>|||Fast_forward is a forward Only read only cursor. If you can move forward an
d
backward in a cursor SQL needs to cache the results... If you use
fast_forward, sql only needs to have in memory ( or in tempdb depending on
the other options you choose), the current row you are looking at... Once yo
u
move off of that row, sql can purge it from memory because you are not
allowed to move backward...
Forward-only, read only cursors are called firehose cursors, and are the
fastest of any cursor...
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Alien2_51" wrote:
> It went away with FAST_FORWARD.. hmm.. Maybe someone can tell why the
> READ_ONLY is so much more costly on resources...?
> "Enric" wrote:
>|||Thanks Wayne!!
Dan
"Wayne Snyder" wrote:
> Fast_forward is a forward Only read only cursor. If you can move forward
and
> backward in a cursor SQL needs to cache the results... If you use
> fast_forward, sql only needs to have in memory ( or in tempdb depending on
> the other options you choose), the current row you are looking at... Once
you
> move off of that row, sql can purge it from memory because you are not
> allowed to move backward...
> Forward-only, read only cursors are called firehose cursors, and are the
> fastest of any cursor...
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Alien2_51" wrote:
>|||On Tue, 28 Mar 2006 02:26:02 -0800, Alien2_51 wrote:

>I have something kind of bizarre going on and I'm not sure how to
>troubleshoot it, I've put print statements all over in my code to try an fi
nd
>out where it's breaking but it doesn't seem to be breaking at any specific
>point, there is no error message, processing just stops about the time the
>machine runs out of memery, I suspect it's something with the number of
>cursors I have open at one time. I can remove some of the nested cursors an
d
>the problem goes away so I know it's memory related... Anybody have some
>design advice.?
Hi Dan,
Triggers whould be used VERY sparingly in SQL Server. Nested triggers
are a big red waving flag screaming "bad design!! bad design!!"
You should consider rewriting your stored procedure to use set-based
logic only - or at least to reduce the number of triggers.
If you need help with that, you'll really have to post your existing
code. And not only that - we need table structure (as CREATE TABLE
statements), sample data (as INSERT statements) and expected results as
well!

> I tried to post my code but it's too big
Eh? I've posted some prettly lengthy posts here and I've never gotten
any errors on this. Just make sure NOT to post it as a file atachment
(they get filtered out by namy usenet providers); just paste the
complete CREATE PROCEDURE statement in your post.
Of course - the longer the code you post, the harder it is to comment;
we are after all all doing this in our spare time. But if you do post, I
will definitely look at it - that much I promise.
Hugo Kornelis, SQL Server MVP|||Thanks Hugo... I'm not using the cursors in a trigger.. I have a huge
procedural script that transforms data from one data schema to another.. I
have to nest my cursors to preserve all of my data relationships.. Some of
the relationships I'm trying to preserve are four and five levels deep..
~Dan
"Hugo Kornelis" wrote:

> On Tue, 28 Mar 2006 02:26:02 -0800, Alien2_51 wrote:
>
> Hi Dan,
> Triggers whould be used VERY sparingly in SQL Server. Nested triggers
> are a big red waving flag screaming "bad design!! bad design!!"
> You should consider rewriting your stored procedure to use set-based
> logic only - or at least to reduce the number of triggers.
> If you need help with that, you'll really have to post your existing
> code. And not only that - we need table structure (as CREATE TABLE
> statements), sample data (as INSERT statements) and expected results as
> well!
>
> Eh? I've posted some prettly lengthy posts here and I've never gotten
> any errors on this. Just make sure NOT to post it as a file atachment
> (they get filtered out by namy usenet providers); just paste the
> complete CREATE PROCEDURE statement in your post.
> Of course - the longer the code you post, the harder it is to comment;
> we are after all all doing this in our spare time. But if you do post, I
> will definitely look at it - that much I promise.
> --
> Hugo Kornelis, SQL Server MVP
>|||On Tue, 28 Mar 2006 12:12:02 -0800, Alien2_51 wrote:

>Thanks Hugo... I'm not using the cursors in a trigger.. I have a huge
>procedural script that transforms data from one data schema to another.. I
>have to nest my cursors to preserve all of my data relationships.. Some of
>the relationships I'm trying to preserve are four and five levels deep..
Hi Dan,
I made a stupid typo. Where I wrote
I actually intended to write
*Cursors* whould be used VERY sparingly in SQL Server. Nested *cursors*
are a big red waving flag screaming "bad design!! bad design!!"
In most cases you don't need cursors at all. Neither for transforming
data, nor for preserving relationships.
That being said - if this is a one-time conversion job and it works now,
leave it as is!
Hugo Kornelis, SQL Server MVP|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Yep! My rule of thumb is that any SQL statement over 10 lines is
suspect. But the real problem is that you used cursors at all! Nested
cursrors scream total incompedency, and old mag tape processing
algorithms.
No good deed goes unpunished! But if it is sooooo bad that you cannot
post to a Newsgroup, then he needs to pay someone to do his job for
him.
Based on 20+ years of fixing this stuff, I would start over with the
spec and do the job from scratch. What I am afraid of is that the
schema is also designed as if it were a mag tape file and thsu cannot
be saved, and probably has already destroyed data integrity.|||>> I have to nest my cursors to preserve all of my data relationships.. Some
of
the relationships I'm trying to preserve are four and five levels
deep.. <<
First, have you looked at ETL tools to do this job?
Again without DDL or code, we can only guess at what you are doing.
But when I have moved data from a "scrub table" to a normalized schema,
I never use cursors. I have found that something this is easier to
write:
BEGIN
..
INSERT INTO HighestReferencedLevel (...)
SELECT ..
FROM ScrubTable
WHERE ..;
INSERT INTO NextHighestReferencedLevel (...)
SELECT ..
FROM ScrubTable
WHERE ..;
etc.
END;
The real problem is what to do about the dirty data that cannot be
moved, like an order for an item that is not in Inventory.
I might have to hit the ScrubTable a few times in the transaction, but
it is still faster than cursors. You might also look at the MERGE
statement in SQL:2003 that other SQL products have.

Nested Cases - is it possible?

I have the following code :
select {?TroubleType}
case "All Types":
{tbl_tickets.CustomerAcctNumber} <> '0' and
{tbl_tickets.OpenDate} >= {?BeginDate} and
{tbl_tickets.OpenDate} <= {?EndDate} and
{tbl_tickets.CustomerAcctNumber} <> '184114' and
{tbl_tickets.TroubleType} <> ""
case {?TroubleType}:
{tbl_tickets.TroubleID}<> 0 and
{tbl_tickets.OpenDate}>= {?BeginDate} and
{tbl_tickets.OpenDate}<= {?EndDate} and
{tbl_tickets.CustomerAcctNumber}<> '184114' and
{tbl_tickets.TroubleType}= {?TroubleType}

and I need to duplicate it for 3 other parameter fields. Is there a way to nest the case statements - I continually get an error everytime I try add just one other case statement to a new parameter...Not clear. U want to put on case within another?|||Yes -

I need to do this same type of functionality for a total of 4 parameters - and have it interchangeably choose the option selected or all for each individual paramter:

example: Give me all the cities
and just troubletype of -email
with all the status

Or Just this city, with just this trouble with just open status...|||Still I'm not clear. But if you want to nest ur case statements, here is an example

//starts here

select {?state}
case "MN" :
(

select {?city}

case "City A" :
"here is some code for city A"

case "City B" :

"here is some code for city B"

)

case "CA" :
"Here is some code for CA"

case "FL" :
"Here is some code for FL"

Wednesday, March 7, 2012

Need VB.NET code to generate snapshot reports automatically

I need to generate hundreds of snapshot reports, which would be
refreshed every night. Each employee would view a snapshot report
pertaining to his employee number (which is the parameter in the
report). The employee is not allowed to look at anyone else's report,
and the company doesn't want employees to be refreshing reports all
day long.
So, here's what I need:
1. VB.NET code that calls the Reporting Services web service to
generate a linked snapshot report for each employee report and every
employee number (for the employee parameter) in my SQL database
2. Code to automatically schedule these snapshots for a nightly run
using a shared scheduled execution time
3. A way to name each linked snapshot report using some kind of naming
convention (e.g. "Employee Report - Employee 100", "Employee Report -
Employee 205", etc.)
Can anyone help? Does anyone have any sample VB.NET code to share?question. How will you set up security for filter out employee's to read
only the
report snaped from their ID?
dlr
"Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> I need to generate hundreds of snapshot reports, which would be
> refreshed every night. Each employee would view a snapshot report
> pertaining to his employee number (which is the parameter in the
> report). The employee is not allowed to look at anyone else's report,
> and the company doesn't want employees to be refreshing reports all
> day long.
> So, here's what I need:
> 1. VB.NET code that calls the Reporting Services web service to
> generate a linked snapshot report for each employee report and every
> employee number (for the employee parameter) in my SQL database
> 2. Code to automatically schedule these snapshots for a nightly run
> using a shared scheduled execution time
> 3. A way to name each linked snapshot report using some kind of naming
> convention (e.g. "Employee Report - Employee 100", "Employee Report -
> Employee 205", etc.)
> Can anyone help? Does anyone have any sample VB.NET code to share?|||Dennis, when the user logs in to the web application, a stored
procedure fires to retrieve the ID for the employee, where the
employee works, where the employee is in the management food chain,
and what reports the user is authorized to see.
So, when the user enters the reports page in the web application, the
user would see all the reports he/she is permitted to see that the
stored procedure brought back from that report table I mentioned.
Because the web application has the employee and workplace ID in
memory, it would call the respective snapshot by taking the report
name and concatenating the employee ID and workplace ID, which then
references the snapshot report name. Here's an example...
Let's assume that the user's employee ID is 205 and workplace ID is
5000. If the user clicks on a report called "Sales by Employee", the
web application would then construct the snapshot report name (e.g.
"Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
snapshots available in the Reporting Services database (i.e. one
snapshot combination for every employee ID and work place ID) and
display the correct snapshot.
Unfortunately, we don't know how to do the VB.NET code to
automatically build all the snapshots from our database table of
employee and workplace IDs. We need a means for automatically
generating and eliminating snapshots as employees come on board,
switch departments, or leave the organization.
"Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> question. How will you set up security for filter out employee's to read
> only the
> report snaped from their ID?
> dlr
> "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > I need to generate hundreds of snapshot reports, which would be
> > refreshed every night. Each employee would view a snapshot report
> > pertaining to his employee number (which is the parameter in the
> > report). The employee is not allowed to look at anyone else's report,
> > and the company doesn't want employees to be refreshing reports all
> > day long.
> >
> > So, here's what I need:
> >
> > 1. VB.NET code that calls the Reporting Services web service to
> > generate a linked snapshot report for each employee report and every
> > employee number (for the employee parameter) in my SQL database
> > 2. Code to automatically schedule these snapshots for a nightly run
> > using a shared scheduled execution time
> > 3. A way to name each linked snapshot report using some kind of naming
> > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > Employee 205", etc.)
> >
> > Can anyone help? Does anyone have any sample VB.NET code to share?|||ok Steve. I am a little more pluged in to your design.
The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
you to name your output snapshots anything different from the base name of
the report (see BOL on this function and the section of snapshots with
parameterized reports).
I think, based on what you are telling me is that you will want to
(0) identify the user and her report parameters
(1) use the Web Service "Render" method (which returns a stream of bytes) to
create the report stream
(2) write the bytes to a file share (and name it using your paramater
values) and then
(3) redirect the user to that file.
[you will want to skip (1) and (2) if a valid file on share exists when the
user jumps in]
does this sound correct?
dlr
"Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
news:437b6286.0409241745.6fa9b007@.posting.google.com...
> Dennis, when the user logs in to the web application, a stored
> procedure fires to retrieve the ID for the employee, where the
> employee works, where the employee is in the management food chain,
> and what reports the user is authorized to see.
> So, when the user enters the reports page in the web application, the
> user would see all the reports he/she is permitted to see that the
> stored procedure brought back from that report table I mentioned.
> Because the web application has the employee and workplace ID in
> memory, it would call the respective snapshot by taking the report
> name and concatenating the employee ID and workplace ID, which then
> references the snapshot report name. Here's an example...
> Let's assume that the user's employee ID is 205 and workplace ID is
> 5000. If the user clicks on a report called "Sales by Employee", the
> web application would then construct the snapshot report name (e.g.
> "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> snapshots available in the Reporting Services database (i.e. one
> snapshot combination for every employee ID and work place ID) and
> display the correct snapshot.
> Unfortunately, we don't know how to do the VB.NET code to
> automatically build all the snapshots from our database table of
> employee and workplace IDs. We need a means for automatically
> generating and eliminating snapshots as employees come on board,
> switch departments, or leave the organization.
> "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > question. How will you set up security for filter out employee's to
read
> > only the
> > report snaped from their ID?
> >
> > dlr
> > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > I need to generate hundreds of snapshot reports, which would be
> > > refreshed every night. Each employee would view a snapshot report
> > > pertaining to his employee number (which is the parameter in the
> > > report). The employee is not allowed to look at anyone else's report,
> > > and the company doesn't want employees to be refreshing reports all
> > > day long.
> > >
> > > So, here's what I need:
> > >
> > > 1. VB.NET code that calls the Reporting Services web service to
> > > generate a linked snapshot report for each employee report and every
> > > employee number (for the employee parameter) in my SQL database
> > > 2. Code to automatically schedule these snapshots for a nightly run
> > > using a shared scheduled execution time
> > > 3. A way to name each linked snapshot report using some kind of naming
> > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > Employee 205", etc.)
> > >
> > > Can anyone help? Does anyone have any sample VB.NET code to share?|||Dennis, I'll need to research more on the Web Service method you
referred to. It seems like the web service has everything I would
need to do generate snapshot reports, but I'd like to see some sample
VB.NET code to help me along.
As for your numbered items below, I would have to say that we already
have the logic to identify the user and get the right snapshot (e.g.
"Sales by Employee - 36", where "Sales by Employee" is the base report
name, "36" is the parameter value for the employee number, and "Sales
by Employee - 36" is the saved snapshot name).
I've successfully created some snapshots manually and retrieved the
right snapshot based on the employee ID of the user logged in...so,
rendering the snapshot report is no problem.
The problem is generating all the snapshots I need via an automated
process. I'm sure with the web service, there are available methods
to do this. I've already created a console application that
automatically hides parameters for all 50 of my reports.
So, the VB.NET code will need the following:
1. Retrieve a collection of reports
2. Set a default parameter for the Employee ID to each report
3. Create a linked report for each base report and respective Employee
ID value and concatenate the parameter value to the report name (e.g.
"Sales by Employee - 36")
4. Create a snapshot from the linked report
5. Set the snapshot to use the shared schedule for my nightly refresh
6. Remove existing snapshots for those employees who have left the
company
7. Remove the default value for the Employee ID from the base reports
so they can be refreshed separately from the snapshot reports
"Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<OcjTCRLpEHA.3552@.TK2MSFTNGP15.phx.gbl>...
> ok Steve. I am a little more pluged in to your design.
> The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
> you to name your output snapshots anything different from the base name of
> the report (see BOL on this function and the section of snapshots with
> parameterized reports).
> I think, based on what you are telling me is that you will want to
> (0) identify the user and her report parameters
> (1) use the Web Service "Render" method (which returns a stream of bytes) to
> create the report stream
> (2) write the bytes to a file share (and name it using your paramater
> values) and then
> (3) redirect the user to that file.
> [you will want to skip (1) and (2) if a valid file on share exists when the
> user jumps in]
> does this sound correct?
>
> dlr
> "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> news:437b6286.0409241745.6fa9b007@.posting.google.com...
> > Dennis, when the user logs in to the web application, a stored
> > procedure fires to retrieve the ID for the employee, where the
> > employee works, where the employee is in the management food chain,
> > and what reports the user is authorized to see.
> >
> > So, when the user enters the reports page in the web application, the
> > user would see all the reports he/she is permitted to see that the
> > stored procedure brought back from that report table I mentioned.
> > Because the web application has the employee and workplace ID in
> > memory, it would call the respective snapshot by taking the report
> > name and concatenating the employee ID and workplace ID, which then
> > references the snapshot report name. Here's an example...
> >
> > Let's assume that the user's employee ID is 205 and workplace ID is
> > 5000. If the user clicks on a report called "Sales by Employee", the
> > web application would then construct the snapshot report name (e.g.
> > "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> > snapshots available in the Reporting Services database (i.e. one
> > snapshot combination for every employee ID and work place ID) and
> > display the correct snapshot.
> >
> > Unfortunately, we don't know how to do the VB.NET code to
> > automatically build all the snapshots from our database table of
> > employee and workplace IDs. We need a means for automatically
> > generating and eliminating snapshots as employees come on board,
> > switch departments, or leave the organization.
> >
> > "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
> news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > > question. How will you set up security for filter out employee's to
> read
> > > only the
> > > report snaped from their ID?
> > >
> > > dlr
> > > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > > I need to generate hundreds of snapshot reports, which would be
> > > > refreshed every night. Each employee would view a snapshot report
> > > > pertaining to his employee number (which is the parameter in the
> > > > report). The employee is not allowed to look at anyone else's report,
> > > > and the company doesn't want employees to be refreshing reports all
> > > > day long.
> > > >
> > > > So, here's what I need:
> > > >
> > > > 1. VB.NET code that calls the Reporting Services web service to
> > > > generate a linked snapshot report for each employee report and every
> > > > employee number (for the employee parameter) in my SQL database
> > > > 2. Code to automatically schedule these snapshots for a nightly run
> > > > using a shared scheduled execution time
> > > > 3. A way to name each linked snapshot report using some kind of naming
> > > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > > Employee 205", etc.)
> > > >
> > > > Can anyone help? Does anyone have any sample VB.NET code to share?|||Are you using integrated security?
"Steve Pantazis" wrote:
> Dennis, I'll need to research more on the Web Service method you
> referred to. It seems like the web service has everything I would
> need to do generate snapshot reports, but I'd like to see some sample
> VB.NET code to help me along.
> As for your numbered items below, I would have to say that we already
> have the logic to identify the user and get the right snapshot (e.g.
> "Sales by Employee - 36", where "Sales by Employee" is the base report
> name, "36" is the parameter value for the employee number, and "Sales
> by Employee - 36" is the saved snapshot name).
> I've successfully created some snapshots manually and retrieved the
> right snapshot based on the employee ID of the user logged in...so,
> rendering the snapshot report is no problem.
> The problem is generating all the snapshots I need via an automated
> process. I'm sure with the web service, there are available methods
> to do this. I've already created a console application that
> automatically hides parameters for all 50 of my reports.
> So, the VB.NET code will need the following:
> 1. Retrieve a collection of reports
> 2. Set a default parameter for the Employee ID to each report
> 3. Create a linked report for each base report and respective Employee
> ID value and concatenate the parameter value to the report name (e.g.
> "Sales by Employee - 36")
> 4. Create a snapshot from the linked report
> 5. Set the snapshot to use the shared schedule for my nightly refresh
> 6. Remove existing snapshots for those employees who have left the
> company
> 7. Remove the default value for the Employee ID from the base reports
> so they can be refreshed separately from the snapshot reports
>
> "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message news:<OcjTCRLpEHA.3552@.TK2MSFTNGP15.phx.gbl>...
> > ok Steve. I am a little more pluged in to your design.
> >
> > The Web Service "UpdateReportExecutionSnapshot" method is not going to allow
> > you to name your output snapshots anything different from the base name of
> > the report (see BOL on this function and the section of snapshots with
> > parameterized reports).
> >
> > I think, based on what you are telling me is that you will want to
> > (0) identify the user and her report parameters
> > (1) use the Web Service "Render" method (which returns a stream of bytes) to
> > create the report stream
> > (2) write the bytes to a file share (and name it using your paramater
> > values) and then
> > (3) redirect the user to that file.
> >
> > [you will want to skip (1) and (2) if a valid file on share exists when the
> > user jumps in]
> >
> > does this sound correct?
> >
> >
> > dlr
> > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > news:437b6286.0409241745.6fa9b007@.posting.google.com...
> > > Dennis, when the user logs in to the web application, a stored
> > > procedure fires to retrieve the ID for the employee, where the
> > > employee works, where the employee is in the management food chain,
> > > and what reports the user is authorized to see.
> > >
> > > So, when the user enters the reports page in the web application, the
> > > user would see all the reports he/she is permitted to see that the
> > > stored procedure brought back from that report table I mentioned.
> > > Because the web application has the employee and workplace ID in
> > > memory, it would call the respective snapshot by taking the report
> > > name and concatenating the employee ID and workplace ID, which then
> > > references the snapshot report name. Here's an example...
> > >
> > > Let's assume that the user's employee ID is 205 and workplace ID is
> > > 5000. If the user clicks on a report called "Sales by Employee", the
> > > web application would then construct the snapshot report name (e.g.
> > > "Sales by Employee - EmpID 205 - OrgID 5000") out of say hundreds of
> > > snapshots available in the Reporting Services database (i.e. one
> > > snapshot combination for every employee ID and work place ID) and
> > > display the correct snapshot.
> > >
> > > Unfortunately, we don't know how to do the VB.NET code to
> > > automatically build all the snapshots from our database table of
> > > employee and workplace IDs. We need a means for automatically
> > > generating and eliminating snapshots as employees come on board,
> > > switch departments, or leave the organization.
> > >
> > > "Dennis Redfield" <dennis.redfield@.acadia-ins.com> wrote in message
> > news:<#IauM2joEHA.2140@.TK2MSFTNGP11.phx.gbl>...
> > > > question. How will you set up security for filter out employee's to
> > read
> > > > only the
> > > > report snaped from their ID?
> > > >
> > > > dlr
> > > > "Steve Pantazis" <steve.pantazis@.gmail.com> wrote in message
> > > > news:437b6286.0409231823.b5e6fbb@.posting.google.com...
> > > > > I need to generate hundreds of snapshot reports, which would be
> > > > > refreshed every night. Each employee would view a snapshot report
> > > > > pertaining to his employee number (which is the parameter in the
> > > > > report). The employee is not allowed to look at anyone else's report,
> > > > > and the company doesn't want employees to be refreshing reports all
> > > > > day long.
> > > > >
> > > > > So, here's what I need:
> > > > >
> > > > > 1. VB.NET code that calls the Reporting Services web service to
> > > > > generate a linked snapshot report for each employee report and every
> > > > > employee number (for the employee parameter) in my SQL database
> > > > > 2. Code to automatically schedule these snapshots for a nightly run
> > > > > using a shared scheduled execution time
> > > > > 3. A way to name each linked snapshot report using some kind of naming
> > > > > convention (e.g. "Employee Report - Employee 100", "Employee Report -
> > > > > Employee 205", etc.)
> > > > >
> > > > > Can anyone help? Does anyone have any sample VB.NET code to share?
>

Saturday, February 25, 2012

Need urgent help to sort this out!

I've made this example and it loads a picture into a database. (MsSql )
Take a look at the code, it works just fine however it leaves a process in sleeping mode "avaiting command" in Enterprise manager under "Management/current Activity/Process Info"
Is it supposed to be like this or is it supposed to be reemoved after .net is finished??
Code snip
_______________________________________________________

Dim connAsNew SqlConnection("Data Source = (local);Initial Catalog = " & "test;User ID = NAME; Password=PASSWORD;")

Dim cmdAsNew SqlCommand("Select * from tab_bild", cnn)

Try

conn.Open()

Dim myDatareaderAs SqlDataReader

myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

DoWhile (myDatareader.Read())

Response.ContentType = myDatareader.Item("PersonImageType")

Response.BinaryWrite(myDatareader.Item("PersonImage"))

Loop

conn.Close()

Response.Write("Picture info succesfully retrieved")

Catch SQLexcAs SqlException

Response.Write("Read failed, Reason: " & SQLexc.ToString())

EndTry

EndSub
________________________________________________________________

Please can someone explain this for me or sort this out for me.
All help is welcome even if its only points me too a direction.

Regards
Tombola

You should move yourconn.Close() into a Finally block.
|||Thanks for the reply Morton!
I suspect that you mean something like this.
----------------

Try

conn.Open()

Dim myDatareaderAs SqlDataReader

myDatareader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

DoWhile (myDatareader.Read())

Response.ContentType = myDatareader.Item("PersonImageType")

Response.BinaryWrite(myDatareader.Item("PersonImage"))

Loop

Response.Write("Bild info succesfully retrieved")

Catch SQLexcAs SqlException

Response.Write("Read failed, Reason: " & SQLexc.ToString())

Finally

conn.Close()

EndTry

----------------
However it still leaves a sleeping process, but it will eventually time out, and be killed, I suppose.
I dont think this would bee such a good solution tough, what if the server is short off memory and there is a lot of sleeping processes that just waits to be timed out. The system would eventually freeze I think. Both IIS and MsSql harvests memory if I remember right.
Anyway the question would still be, Should the .net SqlClient leave a sleeping process on the server when the SqlClient is finished with all its doings?
Regards
Tombola|||I believe what you are seeing is a phenomenon of connection pooling. This is a good thing.
You should likely add a cmd.Dispose() after the conn.Close(). AndI would also add a conn.Dispose() after the conn.Close() for goodmeasure. It's a good idea to Dispose any object which implementthe IDisposable interface once you are done with it.

Need Transact-SQL code

Is there any transact code for sql server that I can type out to view all of the relationship Definments of the current database or even an individual table.Moving thread to T-SQL forum.|||Please take a look at the INFORMATION_SCHEMA views in SQL Server 70/2000/2005 or the system catalog views in SQL Server 2005. There are also system stored procedures like sp_helpconstraint, sp_helpindex, sp_help that will give you similar information. Search in Books Online for these SPs or views.|||

Yes. (sorry about my poor english)

Making an "simplest" answer, point to the system tables sysindexes (index), sysreferences (fk) and syscomments (views, tr, sp).

Friday, March 30, 2012

NetWork Credentials. Please help

Friday, March 23, 2012

Nesting a SP inside a Query

Wednesday, March 21, 2012

Nested Stored Proceudre Error

Nested Stored Proceudre Error

Nested Stored Proceudre Error

Nested set show leaves of parent

Monday, March 19, 2012

Nested Loop Join - need help :)

Monday, March 12, 2012

nested ifs in custom code

Nested Cursors

Nested Cursors

Nested cursors

Nested Cases - is it possible?

Wednesday, March 7, 2012

Need VB.NET code to generate snapshot reports automatically

Need VB.NET code to generate snapshot reports automatically

Saturday, February 25, 2012

Need urgent help to sort this out!

Need Transact-SQL code

Need to sum columns

Blog Archive

About Me