Need to sum columns: view

Showing posts with label view. Show all posts

Wednesday, March 28, 2012

Network Configuration...

I am not able to view the Network Configuration window.
What special permission(s) do I need in order to view the
information? I need to find out what port my instance is
on.Typically, the admin on the machine would be viewing/chaning the ports SQL
is listening on.
You need to be able to read the following registry key;
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketNet
Lib\Tcp
After SQL Server starts, you can determine what port it is using by
reviewing the SQL Errorlogs and the NT Application Eventlogs.
Also, running netstat -an will give you the port information as well.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thank you very much! I found my port information. I do
have another question. Do you know if there is a way to
change the port to the default port 1433? Do you see any
problems with doing this?

quote:

>--Original Message--
>Typically, the admin on the machine would be

viewing/chaning the ports SQL

quote:

>is listening on.
>You need to be able to read the following registry key;
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLSe

rver\SuperSocketNet

quote:

>Lib\Tcp
>After SQL Server starts, you can determine what port it

is using by

quote:

>reviewing the SQL Errorlogs and the NT Application

Eventlogs.

quote:

>Also, running netstat -an will give you the port

information as well.

quote:

>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and

confers no rights.

quote:

>
>.
>

|||Question:
Do you know if there is a way to
change the port to the default port 1433? Do you see any
problems with doing this?
Answer:
You can use the Server Network Utility to change the port. The only
problem with doing this is that then you also would need to modify each
client to connect to the new port. All clients will by default send
traffic to 1433. If you change this, then each client would need an alias
created to reflect the change, or you would need to modify the connection
string. Ex.
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=myuser;Initial Catalog=Northwind;Data
Source=MySQLServerName,4500;Network Library=DBMSSOCN
(Where 4500 is the new port)
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thank you!

quote:

>--Original Message--
>Question:
>Do you know if there is a way to
>change the port to the default port 1433? Do you see

any

quote:

>problems with doing this?
>Answer:
>You can use the Server Network Utility to change the

port. The only

quote:

>problem with doing this is that then you also would need

to modify each

quote:

>client to connect to the new port. All clients will by

default send

quote:

>traffic to 1433. If you change this, then each client

would need an alias

quote:

>created to reflect the change, or you would need to

modify the connection

quote:

>string. Ex.
>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist

Security

quote:

>Info=False;User ID=myuser;Initial Catalog=Northwind;Data
>Source=MySQLServerName,4500;Network Library=DBMSSOCN
> (Where 4500 is the new port)
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and

confers no rights.

quote:

>
>.
>

|||You're welcome.
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Friday, March 23, 2012

Nesting views?

OK say a user creates a view "A"...
Now another user comes along and creates a view "B" which joins view "A"
plus other tables.
Now yet another user creates a view "C" which uses "B"...
Supported yes but is this really wise? It really begins to be convoluded
quickly. Seems to me a view is to abstract data to the user not to use as a
crutch when writing procs. Is this just me being dumb or narrow minded? Or
is this not considered a good practice?Views can provide a well-defined client application interface as well as
horizontal and vertical partitioning functionality. Views can also be used
for query encapsulation, but you don't want to go overboard with nesting.
I once worked with a application (upsized from Access) that had views nested
up to 8 levels deep. Debugging functionality and performance was a real
challenge.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>
I try to structure a SQL Server application like this
---
| Business Processes |
|---
| Business Logic | The Application Tier
|---
| Database Transactions |
| *******************************
| * Stored Procedures |
************-- |
| Views and Table-Valued UDF's | | The Data Tier
---
| Tables |
---
So the tables are accessed via views, UDF's and Stored Procedures. Together
the views, UDF's and Stored Procedures provide the interface to the data
tier. The application tier defines transactions by composing elemetnts
exposed by the data tier.
The Database Transactions and even the Business Logic might be implemented
in TSQL, although they are more likely in .NET. The choice of language
there depends more on factors like the complexity of the applicaiton,
performance requirements, team skillset, the need for production debugging
and tuning, etc.
There's no real reason, however, to wrap tables in views and stored
procedures that don't do anything. Using views that pass through single
tables, and stored procedures that insert single rows acomplishes little.
If you're tempted there, just remember that there are worse things in life
than having a sub-optimal application architecture. Like failing to deliver
on time, or not delighting your users, or performing poorly, or spending all
your time learning technology rather than delivering business value.
David

Nesting views?

OK say a user creates a view "A"...
Now another user comes along and creates a view "B" which joins view "A"
plus other tables.
Now yet another user creates a view "C" which uses "B"...
Supported yes but is this really wise? It really begins to be convoluded
quickly. Seems to me a view is to abstract data to the user not to use as a
crutch when writing procs. Is this just me being dumb or narrow minded? Or
is this not considered a good practice?Views can provide a well-defined client application interface as well as
horizontal and vertical partitioning functionality. Views can also be used
for query encapsulation, but you don't want to go overboard with nesting.
I once worked with a application (upsized from Access) that had views nested
up to 8 levels deep. Debugging functionality and performance was a real
challenge.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>|||"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O4XU8%235aGHA.4612@.TK2MSFTNGP03.phx.gbl...
> OK say a user creates a view "A"...
> Now another user comes along and creates a view "B" which joins view "A"
> plus other tables.
> Now yet another user creates a view "C" which uses "B"...
> Supported yes but is this really wise? It really begins to be convoluded
> quickly. Seems to me a view is to abstract data to the user not to use as
> a crutch when writing procs. Is this just me being dumb or narrow minded?
> Or is this not considered a good practice?
>
I try to structure a SQL Server application like this
---
| Business Processes |
|---
| Business Logic | The Application Tier
|---
| Database Transactions |
| *******************************
| * Stored Procedures |
************-- |
| Views and Table-Valued UDF's | | The Data Tier
---
| Tables |
---
So the tables are accessed via views, UDF's and Stored Procedures. Together
the views, UDF's and Stored Procedures provide the interface to the data
tier. The application tier defines transactions by composing elemetnts
exposed by the data tier.
The Database Transactions and even the Business Logic might be implemented
in TSQL, although they are more likely in .NET. The choice of language
there depends more on factors like the complexity of the applicaiton,
performance requirements, team skillset, the need for production debugging
and tuning, etc.
There's no real reason, however, to wrap tables in views and stored
procedures that don't do anything. Using views that pass through single
tables, and stored procedures that insert single rows acomplishes little.
If you're tempted there, just remember that there are worse things in life
than having a sub-optimal application architecture. Like failing to deliver
on time, or not delighting your users, or performing poorly, or spending all
your time learning technology rather than delivering business value.
David

nested views lock management

Hi, I have a question about nested views. Is there a way for the view not to place any locks on the underlying tables? I tried to re-write my views and its nested views with "with (nolock)" but when I view the enterprise manager, I still see exclusive and share lock on the tables. Any help would be appreciated. Thanks.

I have wanted to do this before and have tried it; however, views do not work that way. If you want to code with the NOLOCK hint you will need to code it with the actual queries. One alternative, although not an efficient one, would be to create a multi-line TVF instead of a view in which the function consists of a query that contains the NOLOCK hint. I have never used this alternative and I guess I don't really recommend it.

|||

Hi , Thanks for the reply.

I was also wondering how about putting the select from view in a store procedure, and set the transaction level to read uncommited. Would that work? This is because I think I am getting a dead lock on a table. for some reason, the select is placing a share lock and an exclusive lock on the underlaying table which make no sense to me, I thought exclusive lock is only for insert, update and delete. Thanks for any response again.

|||

Which SQL Server version do you use?

If you use SQL Server 2005, you could use READ_COMMITTED_SNAPSHOT option for you database.

Using snapshot isolation could decrease number of deadlocks, because transaction isolation was implemented with usage row versions.

Nested Views are not getting executed.

hi,
I have peculiar but interesting problem.
I have a DTS Package which is transforming data from a view to a table.
The view that is used in the source is nested up to six levels.
It is similar to the below.
Assuming the 6 views as v1 , v1 ...v6
--View 1 definition --
select a , b , ..... from tbl a inner join v2 on...
--View 2 definition--
select a,b,..... from tbl a inner join v3 on ...
--view 3 definition--
select v3.a , v3.b .... v3.1 from v4 inner join v5 on
a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
a.v4 = a.v5 and a.v4 = a.v6
...
...
... and the join is until for v6 and atleast 3 columns for eash view.
Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
tables and also from table a used in v1 and v2.
The DTS package is scheduled as job.There are other steps too in the
package and this DTS runs at the third step.The job runs successfully
some times but hangs at the third step at times and there is no clue
why it hangs.
But when the job is cancelled and rerun after hanging , it runns
successfully the second time and there are no issues the second time.
A trace was run and there is no information of dead locks and time outs
on the trace , there is no information on the errorlog for dead locks.
But i presume the issue is with locks but have no proof for the same.
At the time when the job hangs there are number of context ids for the
spid that runs the job.
When i queried for locks , i found all the locks for the above
mentioned tables and views are Sch - S locks and with GRANT status
execpt for one lock which was Sch - M with a WAIT status
But I am not sure why there is Sch - M when there is no change in the
schema and the views are just doing a select.
I am not sure if UPDATE STATISTICS is running at the same time and
causing this problem.
And the peculiar thing is job is running successfully when it is run
the second time.
As i am using only views I am not able to insert into any table to
audit the process and check the place of issue.
Please provide any inputs on how to identify the issue.
Regards
Venkat
Have you run SQL Server Profiler? Take a look at execution plan ov the
views. Does the optimizer available to use indexes
Try to run these vews separatly, I mean no as one big job
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142755847.568403.179300@.e56g2000cwe.googlegr oups.com...
> hi,
> I have peculiar but interesting problem.
> I have a DTS Package which is transforming data from a view to a table.
> The view that is used in the source is nested up to six levels.
> It is similar to the below.
> Assuming the 6 views as v1 , v1 ...v6
> --View 1 definition --
> select a , b , ..... from tbl a inner join v2 on...
> --View 2 definition--
> select a,b,..... from tbl a inner join v3 on ...
> --view 3 definition--
> select v3.a , v3.b .... v3.1 from v4 inner join v5 on
> a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
> a.v4 = a.v5 and a.v4 = a.v6
> ...
> ...
> ... and the join is until for v6 and atleast 3 columns for eash view.
> Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
> tables and also from table a used in v1 and v2.
>
> The DTS package is scheduled as job.There are other steps too in the
> package and this DTS runs at the third step.The job runs successfully
> some times but hangs at the third step at times and there is no clue
> why it hangs.
> But when the job is cancelled and rerun after hanging , it runns
> successfully the second time and there are no issues the second time.
> A trace was run and there is no information of dead locks and time outs
> on the trace , there is no information on the errorlog for dead locks.
> But i presume the issue is with locks but have no proof for the same.
> At the time when the job hangs there are number of context ids for the
> spid that runs the job.
> When i queried for locks , i found all the locks for the above
> mentioned tables and views are Sch - S locks and with GRANT status
> execpt for one lock which was Sch - M with a WAIT status
> But I am not sure why there is Sch - M when there is no change in the
> schema and the views are just doing a select.
> I am not sure if UPDATE STATISTICS is running at the same time and
> causing this problem.
> And the peculiar thing is job is running successfully when it is run
> the second time.
> As i am using only views I am not able to insert into any table to
> audit the process and check the place of issue.
> Please provide any inputs on how to identify the issue.
> Regards
> Venkat
>
|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.
|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.
|||Venkat
It is really hard to suggest something without seeing the tables structure ,
how big are your tables, indexes? What does an optimizer show you? Perhaps
you try to create a stored procedure rather than view.
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142760843.063007.161570@.e56g2000cwe.googlegr oups.com...
>I tried running the views seperately and it works . As mentioned the
> job also does not hang always .. it hangs at times , but the trend is
> unpredictable.
>
|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegr oups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat
|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegr oups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat
|||Hi,
On an average we receive 350000 records daily. But as the table is a
daily refresh there is no big issue with this.
Regards
Venkat
sql

Nested Views are not getting executed.

hi,
I have peculiar but interesting problem.
I have a DTS Package which is transforming data from a view to a table.
The view that is used in the source is nested up to six levels.
It is similar to the below.
Assuming the 6 views as v1 , v1 ...v6
--View 1 definition --
select a , b , ..... from tbl a inner join v2 on...
--View 2 definition--
select a,b,..... from tbl a inner join v3 on ...
--view 3 definition--
select v3.a , v3.b .... v3.1 from v4 inner join v5 on
a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
a.v4 = a.v5 and a.v4 = a.v6
...
...
... and the join is until for v6 and atleast 3 columns for eash view.
Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
tables and also from table a used in v1 and v2.
The DTS package is scheduled as job.There are other steps too in the
package and this DTS runs at the third step.The job runs successfully
some times but hangs at the third step at times and there is no clue
why it hangs.
But when the job is cancelled and rerun after hanging , it runns
successfully the second time and there are no issues the second time.
A trace was run and there is no information of dead locks and time outs
on the trace , there is no information on the errorlog for dead locks.
But i presume the issue is with locks but have no proof for the same.
At the time when the job hangs there are number of context ids for the
spid that runs the job.
When i queried for locks , i found all the locks for the above
mentioned tables and views are Sch - S locks and with GRANT status
execpt for one lock which was Sch - M with a WAIT status
But I am not sure why there is Sch - M when there is no change in the
schema and the views are just doing a select.
I am not sure if UPDATE STATISTICS is running at the same time and
causing this problem.
And the peculiar thing is job is running successfully when it is run
the second time.
As i am using only views I am not able to insert into any table to
audit the process and check the place of issue.
Please provide any inputs on how to identify the issue.
Regards
VenkatHave you run SQL Server Profiler? Take a look at execution plan ov the
views. Does the optimizer available to use indexes
Try to run these vews separatly, I mean no as one big job
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142755847.568403.179300@.e56g2000cwe.googlegroups.com...
> hi,
> I have peculiar but interesting problem.
> I have a DTS Package which is transforming data from a view to a table.
> The view that is used in the source is nested up to six levels.
> It is similar to the below.
> Assuming the 6 views as v1 , v1 ...v6
> --View 1 definition --
> select a , b , ..... from tbl a inner join v2 on...
> --View 2 definition--
> select a,b,..... from tbl a inner join v3 on ...
> --view 3 definition--
> select v3.a , v3.b .... v3.1 from v4 inner join v5 on
> a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
> a.v4 = a.v5 and a.v4 = a.v6
> ...
> ...
> ... and the join is until for v6 and atleast 3 columns for eash view.
> Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
> tables and also from table a used in v1 and v2.
>
> The DTS package is scheduled as job.There are other steps too in the
> package and this DTS runs at the third step.The job runs successfully
> some times but hangs at the third step at times and there is no clue
> why it hangs.
> But when the job is cancelled and rerun after hanging , it runns
> successfully the second time and there are no issues the second time.
> A trace was run and there is no information of dead locks and time outs
> on the trace , there is no information on the errorlog for dead locks.
> But i presume the issue is with locks but have no proof for the same.
> At the time when the job hangs there are number of context ids for the
> spid that runs the job.
> When i queried for locks , i found all the locks for the above
> mentioned tables and views are Sch - S locks and with GRANT status
> execpt for one lock which was Sch - M with a WAIT status
> But I am not sure why there is Sch - M when there is no change in the
> schema and the views are just doing a select.
> I am not sure if UPDATE STATISTICS is running at the same time and
> causing this problem.
> And the peculiar thing is job is running successfully when it is run
> the second time.
> As i am using only views I am not able to insert into any table to
> audit the process and check the place of issue.
> Please provide any inputs on how to identify the issue.
> Regards
> Venkat
>|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||Venkat
It is really hard to suggest something without seeing the tables structure ,
how big are your tables, indexes? What does an optimizer show you? Perhaps
you try to create a stored procedure rather than view.
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
>I tried running the views seperately and it works . As mentioned the
> job also does not hang always .. it hangs at times , but the trend is
> unpredictable.
>|||Uri Dimant wrote:
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
> >I tried running the views seperately and it works . As mentioned the
> > job also does not hang always .. it hangs at times , but the trend is
> > unpredictable.
> >
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Uri Dimant wrote:
> Venkat
> It is really hard to suggest something without seeing the tables structure ,
> how big are your tables, indexes? What does an optimizer show you? Perhaps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
> >I tried running the views seperately and it works . As mentioned the
> > job also does not hang always .. it hangs at times , but the trend is
> > unpredictable.
> >
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Hi,
On an average we receive 350000 records daily. But as the table is a
daily refresh there is no big issue with this.
Regards
Venkat

Nested Views are not getting executed.

hi,
I have peculiar but interesting problem.
I have a DTS Package which is transforming data from a view to a table.
The view that is used in the source is nested up to six levels.
It is similar to the below.
Assuming the 6 views as v1 , v1 ...v6
--View 1 definition --
select a , b , ..... from tbl a inner join v2 on...
--View 2 definition--
select a,b,..... from tbl a inner join v3 on ...
--view 3 definition--
select v3.a , v3.b .... v3.1 from v4 inner join v5 on
a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
a.v4 = a.v5 and a.v4 = a.v6
...
...
... and the join is until for v6 and atleast 3 columns for eash view.
Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
tables and also from table a used in v1 and v2.
The DTS package is scheduled as job.There are other steps too in the
package and this DTS runs at the third step.The job runs successfully
some times but hangs at the third step at times and there is no clue
why it hangs.
But when the job is cancelled and rerun after hanging , it runns
successfully the second time and there are no issues the second time.
A trace was run and there is no information of dead locks and time outs
on the trace , there is no information on the errorlog for dead locks.
But i presume the issue is with locks but have no proof for the same.
At the time when the job hangs there are number of context ids for the
spid that runs the job.
When i queried for locks , i found all the locks for the above
mentioned tables and views are Sch - S locks and with GRANT status
execpt for one lock which was Sch - M with a WAIT status
But I am not sure why there is Sch - M when there is no change in the
schema and the views are just doing a select.
I am not sure if UPDATE STATISTICS is running at the same time and
causing this problem.
And the peculiar thing is job is running successfully when it is run
the second time.
As i am using only views I am not able to insert into any table to
audit the process and check the place of issue.
Please provide any inputs on how to identify the issue.
Regards
VenkatHave you run SQL Server Profiler? Take a look at execution plan ov the
views. Does the optimizer available to use indexes
Try to run these vews separatly, I mean no as one big job
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142755847.568403.179300@.e56g2000cwe.googlegroups.com...
> hi,
> I have peculiar but interesting problem.
> I have a DTS Package which is transforming data from a view to a table.
> The view that is used in the source is nested up to six levels.
> It is similar to the below.
> Assuming the 6 views as v1 , v1 ...v6
> --View 1 definition --
> select a , b , ..... from tbl a inner join v2 on...
> --View 2 definition--
> select a,b,..... from tbl a inner join v3 on ...
> --view 3 definition--
> select v3.a , v3.b .... v3.1 from v4 inner join v5 on
> a.v3 = a.v4 and a.v3 = a.v5 and a,v3 = a.v6 on
> a.v4 = a.v5 and a.v4 = a.v6
> ...
> ...
> ... and the join is until for v6 and atleast 3 columns for eash view.
> Views v4 , v5 , v6 are selecting atleast 6 coulmns from different
> tables and also from table a used in v1 and v2.
>
> The DTS package is scheduled as job.There are other steps too in the
> package and this DTS runs at the third step.The job runs successfully
> some times but hangs at the third step at times and there is no clue
> why it hangs.
> But when the job is cancelled and rerun after hanging , it runns
> successfully the second time and there are no issues the second time.
> A trace was run and there is no information of dead locks and time outs
> on the trace , there is no information on the errorlog for dead locks.
> But i presume the issue is with locks but have no proof for the same.
> At the time when the job hangs there are number of context ids for the
> spid that runs the job.
> When i queried for locks , i found all the locks for the above
> mentioned tables and views are Sch - S locks and with GRANT status
> execpt for one lock which was Sch - M with a WAIT status
> But I am not sure why there is Sch - M when there is no change in the
> schema and the views are just doing a select.
> I am not sure if UPDATE STATISTICS is running at the same time and
> causing this problem.
> And the peculiar thing is job is running successfully when it is run
> the second time.
> As i am using only views I am not able to insert into any table to
> audit the process and check the place of issue.
> Please provide any inputs on how to identify the issue.
> Regards
> Venkat
>|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||I tried running the views seperately and it works . As mentioned the
job also does not hang always .. it hangs at times , but the trend is
unpredictable.|||Venkat
It is really hard to suggest something without seeing the tables structure ,
how big are your tables, indexes? What does an optimizer show you? Perhaps
you try to create a stored procedure rather than view.
"Venkat" <sreepada123@.gmail.com> wrote in message
news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
>I tried running the views seperately and it works . As mentioned the
> job also does not hang always .. it hangs at times , but the trend is
> unpredictable.
>|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure
,
> how big are your tables, indexes? What does an optimizer show you? Perha
ps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL
,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90
ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Uri Dimant wrote:[vbcol=seagreen]
> Venkat
> It is really hard to suggest something without seeing the tables structure
,
> how big are your tables, indexes? What does an optimizer show you? Perha
ps
> you try to create a stored procedure rather than view.
>
> "Venkat" <sreepada123@.gmail.com> wrote in message
> news:1142760843.063007.161570@.e56g2000cwe.googlegroups.com...
Hi,
I have attached the script for the table. The data is refreshed daily
and the new data is loaded into it from where it is picked by the view
to push it to destination table using data pump in DTS.
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblImageGLBalances]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblImageGLBalances]
GO
CREATE TABLE [dbo].[tblImageGLBalances] (
[LdrEntityId] [char] (5) COLLATE Latin1_General_CI_AS NOT NULL ,
[GroupSubNat] [char] (7) COLLATE Latin1_General_CI_AS NOT NULL ,
[BusUnit] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[SellingChannel] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Function] [char] (4) COLLATE Latin1_General_CI_AS NOT NULL ,
[Project] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[StatAccount] [char] (6) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyCode] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[CurrencyType] [char] (3) COLLATE Latin1_General_CI_AS NOT NULL ,
[ProcessingYear] [smallint] NOT NULL ,
[AmountClassType] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL
,
[RequiredInd] [smallint] NOT NULL ,
[LdrAmount0] [money] NOT NULL ,
[LdrAmount1] [money] NOT NULL ,
[LdrAmount2] [money] NOT NULL ,
[LdrAmount3] [money] NOT NULL ,
[LdrAmount4] [money] NOT NULL ,
[LdrAmount5] [money] NOT NULL ,
[LdrAmount6] [money] NOT NULL ,
[LdrAmount7] [money] NOT NULL ,
[LdrAmount8] [money] NOT NULL ,
[LdrAmount9] [money] NOT NULL ,
[LdrAmount10] [money] NOT NULL ,
[LdrAmount11] [money] NOT NULL ,
[LdrAmount12] [money] NOT NULL ,
[LdrAmount13] [money] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [PK_tblImageGLBalances] PRIMARY KEY CLUSTERED
(
[LdrEntityId],
[GroupSubNat],
[BusUnit],
[SellingChannel],
[Function],
[Project],
[StatAccount],
[CurrencyCode],
[CurrencyType],
[ProcessingYear],
[AmountClassType]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[tblImageGLBalances] WITH NOCHECK ADD
CONSTRAINT [DF_tblImageGLBalances_RequiredInd] DEFAULT (0) FOR
[RequiredInd]
GO
CREATE INDEX [IX_tblImageGLBalances] ON
[dbo].[tblImageGLBalances]([GroupSubNat]) WITH FILLFACTOR = 90
ON
[PRIMARY]
GO
CREATE INDEX [IX_tblImageGLBalances_1] ON
[dbo].[tblImageGLBalances]([BusUnit]) WITH FILLFACTOR = 90 ON
[PRIMARY]
GO
I have checked the trace that was set up when the job hanged and there
is no time outs or any locks specified.
I want identify what the problem is , before I change them to SP.
Please let me know if I need to look for any thing else in the trace.
what is more surprising is , if i cancel the job when it hangs and
rerun it , it will run successfully in the first try.
please let me know your inputs.
Thanks for the help.
Regards
Venkat|||Hi,
On an average we receive 350000 records daily. But as the table is a
daily refresh there is no big issue with this.
Regards
Venkat

Friday, March 9, 2012

Negative values

Hello Everyone,
I am writing a view and I have some negative values coming out of some
fields in one of my tables. They should be there, but when i retrive data i
need them to be non negative. I want to know if there is a function or if
there is a way to get rid of negaive sign.
Thank you allIf all are negatives, multiply the value with -1. Otherwise, you can use the
ABS function. Refer to SQL Server Books Online for more details.
--
-- Anith
( Please reply to newsgroups only )

negate RESULT SET, all inactive client

I' d like to list all inactive clients.
Inactive client is a client who hasn't had invoice for 2 months.

I use INNER JOIN to join invoice view (vwDok4FSFZGrid) and clients
addresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_Nazwa !
=''). I select only invoices with date after the interesting date.

So now I can list all active clients but i can't negate this result
set to get all inactive clients:

SELECT dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
FROM vwDok4FSFZGrid
INNER JOIN adr__Ewid
ON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiek tu
WHERE
adr__Ewid.adr_Nazwa !=''
AND
(dok_DataWyst >= convert(datetime,'10/03/2007'))
GROUP BY dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst

Is there any way to negate this set?

1. Sorry for my English
2. I would appreciate any help
:)Mike (darthvadertojabuahahahaha@.gmail.com) writes:

Quote:

Originally Posted by

I' d like to list all inactive clients.
Inactive client is a client who hasn't had invoice for 2 months.
>
I use INNER JOIN to join invoice view (vwDok4FSFZGrid) and clients
addresses table (adr_Nazwa). I skip empty values (adr_Ewid.adr_Nazwa !
>=''). I select only invoices with date after the interesting date.
>
So now I can list all active clients but i can't negate this result
set to get all inactive clients:
>
SELECT dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
FROM vwDok4FSFZGrid
INNER JOIN adr__Ewid
ON vwDok4FSFZGrid.dok_PlatnikId=adr__Ewid.adr_IdObiek tu
WHERE
adr__Ewid.adr_Nazwa !=''
AND
(dok_DataWyst >= convert(datetime,'10/03/2007'))
GROUP BY dok_PlatnikId, adr_Nazwa,
adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP, dok_DataWyst
>
Is there any way to negate this set?

A complete guess:

SELECT adr_Nazwa, adr_NazwaPelna,adr_Adres,adr_Miejscowosc,adr_NIP
FROM adr__Ewid a
WHERE a.adr_Nazwa <''
AND NOT EXISTS (SELECT *
FROM vwDok4FSFZGrid v
WHERE v.dok_PlatnikId = a.adr_IdObiektu
AND v.dok_DataWyst >= '20070310')

--
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

Wednesday, March 7, 2012

Need View to return "NULL" values

I have a view that is joining multiple tables. How do I modify this view so
that it also returns when the value is "NULL" for some of the join table
fields?
Any help would be greatly appreciated!!!
Thank you,
-Valerie
SELECT TOP 100 PERCENT dbo.PATIENTMEDICATION.PatientMedicationID,
dbo.MEDICATION.MedicationDS, dbo.MEDICATION.MedicationID,
dbo.DOSAGEUNIT.DosageUnitDS,
dbo.PATIENTMEDICATION.DosageUnitID, dbo.PATIENTMEDICATION.Dosage,
dbo.DURATIONUNIT.DurationUnitDS,
dbo.PATIENTMEDICATION.DurationUnitID,
dbo.PATIENTMEDICATION.Duration, dbo.PATIENTMEDICATION.BegunDT,
dbo.PATIENTMEDICATION.DiscontinuedDT,
dbo.PATIENTMEDICATION.ActiveYN, dbo.PATIENTMEDICATION.VisitID,
dbo.PATIENTMEDICATION.PatientID
FROM dbo.PATIENTMEDICATION INNER JOIN
dbo.MEDICATION ON dbo.PATIENTMEDICATION.MedicationID =
dbo.MEDICATION.MedicationID INNER JOIN
dbo.DOSAGEUNIT ON dbo.PATIENTMEDICATION.DosageUnitID =
dbo.DOSAGEUNIT.DosageUnitID INNER JOIN
dbo.DURATIONUNIT ON
dbo.PATIENTMEDICATION.DurationUnitID = dbo.DURATIONUNIT.DurationUnitID
ORDER BY dbo.PATIENTMEDICATION.VisitID, dbo.PATIENTMEDICATION.ActiveYN DESC,
dbo.PATIENTMEDICATION.PatientMedicationIDTake a look at LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN
instructions. This is probably what you want.
The use of an UNION query can also be usefull for this kind of problem.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"kvrdev1" <kvrdev1@.discussions.microsoft.com> wrote in message
news:B11D4FE8-5F1F-462D-8BC6-DDD168C7A641@.microsoft.com...
>I have a view that is joining multiple tables. How do I modify this view
>so
> that it also returns when the value is "NULL" for some of the join table
> fields?
> Any help would be greatly appreciated!!!
> Thank you,
> -Valerie
> SELECT TOP 100 PERCENT dbo.PATIENTMEDICATION.PatientMedicationID,
> dbo.MEDICATION.MedicationDS, dbo.MEDICATION.MedicationID,
> dbo.DOSAGEUNIT.DosageUnitDS,
> dbo.PATIENTMEDICATION.DosageUnitID, dbo.PATIENTMEDICATION.Dosage,
> dbo.DURATIONUNIT.DurationUnitDS,
> dbo.PATIENTMEDICATION.DurationUnitID,
> dbo.PATIENTMEDICATION.Duration, dbo.PATIENTMEDICATION.BegunDT,
> dbo.PATIENTMEDICATION.DiscontinuedDT,
> dbo.PATIENTMEDICATION.ActiveYN, dbo.PATIENTMEDICATION.VisitID,
> dbo.PATIENTMEDICATION.PatientID
> FROM dbo.PATIENTMEDICATION INNER JOIN
> dbo.MEDICATION ON dbo.PATIENTMEDICATION.MedicationID
> =
> dbo.MEDICATION.MedicationID INNER JOIN
> dbo.DOSAGEUNIT ON dbo.PATIENTMEDICATION.DosageUnitID
> =
> dbo.DOSAGEUNIT.DosageUnitID INNER JOIN
> dbo.DURATIONUNIT ON
> dbo.PATIENTMEDICATION.DurationUnitID = dbo.DURATIONUNIT.DurationUnitID
> ORDER BY dbo.PATIENTMEDICATION.VisitID, dbo.PATIENTMEDICATION.ActiveYN
> DESC,
> dbo.PATIENTMEDICATION.PatientMedicationID|||>> I have a view that is joining multiple tables. How do I modify this vie=
w so that it also returns when the value is "NULL" for some of the join tab=
le fields [sic]? <<
Fileds and columns are different; one of the MANY differences is that a
column can have a NULL. You probably want an OUTER JOIN, but what you
posted looks wrong from a design viewpoint. If we had DDL we could
more.
1) What is a "medication_id" -- don't you have an industry standard
drug code? I seem to remember that such a thing exists.
2) What makes "medication_id" in medication a totally different thing
from a "patient_m=ADedication_id"? You never, never give the same
data elements different names in the same schema. And doing by
physical storage locations is really bad.
3) Why are units of measuresment modeled as entities and not
attributes? Do you see a gram walking around, independent of an
entity? Unless units of time and medication change independently and
frequently, they ought to be part of the dosage, not entities. You
might want to a add a CHECK() constrint to the DDL to assure this
attribute is correct.
4) Did you actually use an 'y/n' flag in SQL like we did with punch
cards? Remember the rule about storing computed data values? Do not
do it.
5) Can you explain the logical differences between a
"duration=AD_unit_id" and mere "duration=AD_unit" (ditto dosage)? If
you read any book on data modeling or ISO-11179 that first name is
absurd. An identifier gives you unique entity and unit is a scale for
an attribute; entities are not attributes.
6) Why do you have duration, start and discontinue times in the table?
You can compute duration, can't you?|||Thank you! The LEFT OUTER JOIN was exactly what I needed. You have made my
day. :-)
Thanks again,
Valerie
"Sylvain Lafontaine" wrote:

> Take a look at LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN
> instructions. This is probably what you want.
> The use of an UNION query can also be usefull for this kind of problem.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "kvrdev1" <kvrdev1@.discussions.microsoft.com> wrote in message
> news:B11D4FE8-5F1F-462D-8BC6-DDD168C7A641@.microsoft.com...
>
>|||Thank you for your input - you mentioned many good points/topics; most of
which are already taken into consideration with our current data model.
"--CELKO--" wrote:

> Fileds and columns are different; one of the MANY differences is that a
> column can have a NULL. You probably want an OUTER JOIN, but what you
> posted looks wrong from a design viewpoint. If we had DDL we could
> more.
> 1) What is a "medication_id" -- don't you have an industry standard
> drug code? I seem to remember that such a thing exists.
> 2) What makes "medication_id" in medication a totally different thing
> from a "patient_m_edication_id"? You never, never give the same
> data elements different names in the same schema. And doing by
> physical storage locations is really bad.
> 3) Why are units of measuresment modeled as entities and not
> attributes? Do you see a gram walking around, independent of an
> entity? Unless units of time and medication change independently and
> frequently, they ought to be part of the dosage, not entities. You
> might want to a add a CHECK() constrint to the DDL to assure this
> attribute is correct.
> 4) Did you actually use an 'y/n' flag in SQL like we did with punch
> cards? Remember the rule about storing computed data values? Do not
> do it.
> 5) Can you explain the logical differences between a
> "duration__unit_id" and mere "duration__unit" (ditto dosage)? If
> you read any book on data modeling or ISO-11179 that first name is
> absurd. An identifier gives you unique entity and unit is a scale for
> an attribute; entities are not attributes.
> 6) Why do you have duration, start and discontinue times in the table?
> You can compute duration, can't you?
>

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 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).

Monday, February 20, 2012

Need to View some type of LOG file

Hi
I need to view a log of all SQL scripts that were RUN in Sequel Server.
Is It possible to view some type of a log, which will show me the script
as well as when it was run.
Many Thanks
AQ
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Retroactively, no. Going forward, you can use SQL Server Profiler to catch
scripts as they run.
Look up SQL Profiler in Books Online for information about how to use the
tool.
"AQ Mahomed" <aq786@.shoecrazy.co.za> wrote in message
news:OHxDvpWTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need to view a log of all SQL scripts that were RUN in Sequel Server.
> Is It possible to view some type of a log, which will show me the script
> as well as when it was run.
> Many Thanks
> AQ
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
You can get the object creation date from sysobjects system table.
use dbname
go
select substring(name,1,35) as Object_name,type as Object_type,crdate from
sysobjects
Description for Object_type displayed in the above query
C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure
Thanks
Hari
MCDBA
"AQ Mahomed" <aq786@.shoecrazy.co.za> wrote in message
news:OHxDvpWTEHA.2464@.TK2MSFTNGP10.phx.gbl...
> Hi
> I need to view a log of all SQL scripts that were RUN in Sequel Server.
> Is It possible to view some type of a log, which will show me the script
> as well as when it was run.
> Many Thanks
> AQ
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Need to use MID function in SQL

When I try to use the MID statement in a SQL view, it reports 'function not recognized'. Is there some other way to execute the following?

CASE WHEN Mid(SearchID , 4 , 1) = '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END.

I have a column with two data set possibilities: aaa-bbbbb and aaaa-bbbbb. I only want the data to the left of the dash.

Thanks.

Ernie

You have to combine sql sever string function

like "left" and "right" to achive you requirements

I think the equivalent of vb mid function is the "substring" function

This example shows how to return only a portion of a character string. From the authors table, this query returns the last name in one column with only the first initial in the second column.

USE pubs SELECT au_lname, SUBSTRING(au_fname, 1, 1) FROM authors ORDER BY au_lname

|||

create table #test (SearchID varchar(49))
insert into #test values('aaa-bbbbb')
insert into #test values('aaaa-bbbbb')

one way using ParseName
Select ParseName(Replace(SearchID , '-', '.'), 2)
from #test

and another using left and charindex
select distinct LEFT(SearchID ,CHARINDEX('-',SearchID )-1 )
from #test

and a third using case substring and left
select CASE substring(SearchID , 4 , 1) when '-' THEN LEFT (SearchID , 3) ELSE LEFT (SearchID , 4) END
from #test

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||Thanks! I appreciate the help.