Showing posts with label view. Show all posts
Showing posts with label view. Show all posts

Friday, March 30, 2012

IF funcionality in SQL server views

Hi,
I am working with several tables and views. My goal is to create a view
with critical reporting data from these tables and views. I have
managed to get the majority of data but am having difficultly with the
final step.
For a record in the master dataview, add additional record information
by appending data from another view based on the first 2 letters of the
document number and the document number i.e
Document number
SC11111
DN22222
SI33333
For SC11111 look up data in Sales Credit table/view for doument SC11111
and append to that line
For DN22222 look up data in Delivery Note table/view for document
DN22222and append to that line
For SI33333 look up data in Sales Invoice table/view for document
SI33333 and append to that line
Any help appreciated.
Thank you in advance,
RajCan I just clarify what you're trying to do...
as I understand it, you want to select from different tables/views
depending on what the data is.
if so, could you left join on all 3 tables, then using join filters and
using isnull() in your select you could just get the data from the
relevant table.
Otherwise, could you explain a bit further what the problem is.
Cheers
Will|||If you want to conditional view, you can make a view using Multi-statement
Function.
"the_raj"ë'ì?´ ì'ì?±í' ë?´ì?©:
> Hi,
> I am working with several tables and views. My goal is to create a view
> with critical reporting data from these tables and views. I have
> managed to get the majority of data but am having difficultly with the
> final step.
> For a record in the master dataview, add additional record information
> by appending data from another view based on the first 2 letters of the
> document number and the document number i.e
> Document number
> SC11111
> DN22222
> SI33333
> For SC11111 look up data in Sales Credit table/view for doument SC11111
> and append to that line
> For DN22222 look up data in Delivery Note table/view for document
> DN22222and append to that line
> For SI33333 look up data in Sales Invoice table/view for document
> SI33333 and append to that line
> Any help appreciated.
> Thank you in advance,
> Raj
>|||Since you haven't provided the ddls I will try to explain it with a snippet.
CREATE VIEW MASTER_VIEW
AS
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_CREDIT B
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SC'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, C.*
FROM MASTER_DATA A, DELIVERY_NOTE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'DN'
AND <JOIN CODITIONS>
UNION ALL
SELECT A.*, B.*
FROM MASTER_DATA A, SALES_INVOICE C
WHERE SUBSTRING(A. DOCUMENT_NUMBER,1,2) = 'SI'
AND <JOIN CODITIONS>
--Note: do not use select *
-- and I assume the 3 selects that are unioned will be having the same schema

Wednesday, March 28, 2012

if exists DROP PROCEDURE/VIEW

how can I drop a propcedure or a view without error for MS SQL 2000/2500

for a table

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[myTABLE]

and for a procedure

IF EXISTS ?
DROP PROCEDURE [dbo].[SP_myTABLE_Count]

thank you for helpingPlease excuse me, but I find the number of people who can't use google abolutely unbelievable. A search for sys.objects gives the answer, and the type is P for TSQL stored procedures.

However, keep in mind that SQL Server 2005 hides objects on which you have no access, so even if you query sys.objects first you may get an error, and you should handle that situation.|||please excuse me, but a forum is not google|||please excuse me, but a forum is not google
I am perfectly aware of that. What I tried to point out is that very simple questions, like this one, can very easily be solved by searching google. And it's even way faster. Even better, by eliminating a few of the simpler questions, the users of the forum can use their time trying to resolve a bit tougher issues, for which there is NOT an obvious solution when searching google. I do think you get my point.

By all means, do not think of my post as a personal critism, rather a good guideline for the future. My approach is alway as this: Try to solve, search with google, post on forum.|||actually u need not think that hard to get the "if exists" script. sql server can do it for u. right click on any SP from EM and select "generate SQL Script" or from QA Object-Browser select "create drop script" by right clicking on a SP. check the code generated.....

Monday, March 26, 2012

IE Script ERROR with EM

I get an IE Script Error when trying to view a database in TASKPAD view in
EM. I ONLY get this error if I go directly to the TASKPAD view or this was
the last view I used for the given database. If I go to any other view, and
then TASKPAD, it works fine. Any ideas ?This bug has been around forever, the workaround as you have found is to
change to a different view and then back to taskpad. It seems unlikely it
will be addressed at this stage in SQL2000's life but you never know :-)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jeff Block" <jblock@.REMOVETHECAPSANDINVALIDfirstlook.biz.invalid> wrote in
message news:uK$uQS5BEHA.3472@.TK2MSFTNGP09.phx.gbl...
> I get an IE Script Error when trying to view a database in TASKPAD view in
> EM. I ONLY get this error if I go directly to the TASKPAD view or this was
> the last view I used for the given database. If I go to any other view,
and
> then TASKPAD, it works fine. Any ideas ?
>

Friday, March 23, 2012

IE 7 B3 Not Compatible With RS2005

Lon posted a message on the RS UseNet newsgroup about IE 7 B3 being unresponsive and using 100% of the cpu when trying to view RS reports.

I tried it on a test system, and indeed the problem showed up for me as well. Both the IE 7 B3 32 bit and 64 bit version where stuck in a loop when attempting to view RS 2005 reports. I tried both parameter and non-parameterized reports with the same results. The only way to stop it is via Task Manager.

A warning to all, do not install IE 7 B3 if you want to run RS reports.

Hey MS, you may want to pass this on to IE 7 development team. This
isn't good when MS products aren't compatible with each other.

I have IE Beta 3 and work fine with RS 2005.

May be a special report.

|||I experienced the same problem, the screen starts flickering and IE won′t come back. If you open up a BUG on connect.microsoft.com I will vote on it.

HTH, Jens Suessmeyer.

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

Yes, please open a bug so we can take a look. Do you also see the problem using URL Access? ie:

http://<server>/reportserver?/Folder/Report&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false

Thanks, Donovan.

|||

So I just tried that, if called directly via the Webservice and the path string, the problem is not occuring. Unfortunaly if you use this method you would have to pass the parameters via URL string, so I opened up a bug targeting this problem. Thats even not a problem of complexity, I just used a blank report with one textbox and deployed that to the server, which will also cause IE to hang.

I filed the BUG under https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=157905

Please vote on it, if you also experienced the problem.

Danovan, could you share this feedback along with the IE 7 group, I don′t know who is responsible for that, I filed the bug under SQL Server not IE.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Filing it under SQL is the right place to put this issue. We have a lot of script that executes when viewing the report through the report manager that doesn't execute when using URL Access. We'll take a look at what's going on and let the IE team know what we find, if necessary.

I just upgraded my laptop to IE7 B3 and see the same behavior before a report is even present (ie, just blank parameters are showing). Thanks for pointing this out.

Thanks, Donovan.

|||

We've identified the problem and are discussing the fix with the IE team. Thanks for reporting this.

Donovan

|||

If there is a hotfix etc available please post. I am flipping between two machines to view my reports.

Thanks

|||

A fix has gone into IE7 so B2 and RTM should work fine. Unfortunately, in the meantime, there is no work-around for B3.

Thanks, Donovan.

|||http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/17/Default.aspx

FYI

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

IE 7 B3 Not Compatible With RS2005

Lon posted a message on the RS UseNet newsgroup about IE 7 B3 being unresponsive and using 100% of the cpu when trying to view RS reports.

I tried it on a test system, and indeed the problem showed up for me as well. Both the IE 7 B3 32 bit and 64 bit version where stuck in a loop when attempting to view RS 2005 reports. I tried both parameter and non-parameterized reports with the same results. The only way to stop it is via Task Manager.

A warning to all, do not install IE 7 B3 if you want to run RS reports.

Hey MS, you may want to pass this on to IE 7 development team. This
isn't good when MS products aren't compatible with each other.

I have IE Beta 3 and work fine with RS 2005.

May be a special report.

|||I experienced the same problem, the screen starts flickering and IE won′t come back. If you open up a BUG on connect.microsoft.com I will vote on it.

HTH, Jens Suessmeyer.

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

Yes, please open a bug so we can take a look. Do you also see the problem using URL Access? ie:

http://<server>/reportserver?/Folder/Report&rs:Command=Render&rs:Format=HTML4.0&rc:Toolbar=false

Thanks, Donovan.

|||

So I just tried that, if called directly via the Webservice and the path string, the problem is not occuring. Unfortunaly if you use this method you would have to pass the parameters via URL string, so I opened up a bug targeting this problem. Thats even not a problem of complexity, I just used a blank report with one textbox and deployed that to the server, which will also cause IE to hang.

I filed the BUG under https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=157905

Please vote on it, if you also experienced the problem.

Danovan, could you share this feedback along with the IE 7 group, I don′t know who is responsible for that, I filed the bug under SQL Server not IE.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Filing it under SQL is the right place to put this issue. We have a lot of script that executes when viewing the report through the report manager that doesn't execute when using URL Access. We'll take a look at what's going on and let the IE team know what we find, if necessary.

I just upgraded my laptop to IE7 B3 and see the same behavior before a report is even present (ie, just blank parameters are showing). Thanks for pointing this out.

Thanks, Donovan.

|||

We've identified the problem and are discussing the fix with the IE team. Thanks for reporting this.

Donovan

|||

If there is a hotfix etc available please post. I am flipping between two machines to view my reports.

Thanks

|||

A fix has gone into IE7 so B2 and RTM should work fine. Unfortunately, in the meantime, there is no work-around for B3.

Thanks, Donovan.

|||http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/17/Default.aspx

FYI

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Wednesday, March 21, 2012

Identyfying which tables have gone into a view

I am trying to find the list of tables that make up any particular view in SQL server 2000. The information schema - VIEW_TABLE_USAGE would be the best tool, if only the sysdepends table worked! When I use the schema I get some but not all of my views.

Has anyone got a solution, prefably one without cursors, that can identitfy the source tables for a view?

I have used the following SQL, but unfortunately it gives too many results:

SELECT VIEWS.name AS VIEW_NAME,
TABLES.name AS TABLE_NAME,
VIEW_SQL.text
FROM sysobjects VIEWS
INNER JOIN
syscomments VIEW_SQL
ON VIEWS.id = VIEW_SQL.id
INNER JOIN
sysobjects TABLES
ON VIEW_SQL.text LIKE '%.' + TABLES.name + '%'
WHERE (VIEWS.xtype = 'V')
AND (TABLES.xtype = 'U')
ORDER BY VIEWS.name, TABLES.name

JustinLook for SQL Dependency Viewer from Red-Gate software. It's a free download (though I think it's still beta).

http://www.red-gate.com/products/sql_dependency_viewer/

Regards,

hmscott|||Look for SQL Dependency Viewer from Red-Gate software. It's a free download (though I think it's still beta).

http://www.red-gate.com/products/sql_dependency_viewer/

Regards,

hmscott
Thanks, I have tried this tool and it should be good when its complete, however I actually want the names of the tables rather then a graph view. I am using the table names to provide some meta data for a database I am developing.

Regards
Justin