BEFORE Triggers: application

Showing posts with label application. Show all posts

Thursday, March 29, 2012

best tutorial for sql reporting service 2005

i am asked to develop a web application where i have to display reports in a seperate window from a hyperlink .i have to display the report in grid format and show a pie chart within the report layout .

i need to give user credential like print,save the report in excel or doc format and email report.

can anybody give the links for the best tutorial to achieve my purpose.

thanks

sally

Hi there,

There are many resources available. Try books online.

Here is a great guide to building a report.

http://www.eggheadcafe.com/articles/20040823.asp

Here is a guide to building a report + a web interface.

http://www.15seconds.com/Issue/041013.htm

Try MS downloads for further samples.

cheers,

Andrew

|||

Reporting Services Tutorials >>> http://msdn2.microsoft.com/en-us/library/ms170246.aspx

Sunday, March 25, 2012

Best Query/Search Method

Hi,

I'm wondering about the following:

I have come across an InfoPath Forms application who's code is scripted in javascript and who's data seems to be in XML files.
An analyst at that company told me they suspect the data is ALSO in SQL Server... somewhere. They can't seem to find it though. I have
reviewed the .js code and some methods are called for which I can find no source. I believe those methods execute OK because they're found
inside some DLL.

I'm thinking I would enter a new record using the form in InfoPath using some datavalue that I can expect will be unique.. like a lastname who's first three chars is ZZZ or something like that. Subsequently, I'd search each column in each table in each DB on the server to see if I can locate it somewhere.

So, my question is what is the best approach for this? I have access to the db, table and column names. I know I can write a small vb.net piece of code to execute my search. But, is there some better way using some sql procedure (or using the full text catalog) instead or any other tool(s)?

Thanks in advance for your advise.

Stewart

Go into sql server management studio and open up a new query window, then set the query window to the the database in question.

Execute this query:

select 'union select ''[' + colu.table_schema + '].[' + colu.table_name + '].[' + colu.column_name + ']'' as "Schema.Table.Column" '
+ ',[' + colu.column_name + '] as "Value" '
+ ' from [' + colu.table_schema + '].[' + colu.table_name + '] '
+ ' where [' + colu.column_name + '] like ''ZZZ%'' '
from INFORMATION_SCHEMA.columns as colu
where colu.data_type in ('varchar','nvarchar','char','nchar','text','ntext')

Below the query will be the results, one sql statement per text-based column.

You can click, then right-click on the top-left button-looking box in the grid header and copy the text into the buffer.

Paste it into a new query window.

Delete the first "union" on the first line and execute the query.

It will return one row per column value per table that matches ZZZ%.

Depending upon the number of tables/columns in the database, and the number of rows in the table, you might need to split the results into multiple, smaller queries.

Enjoy!

|||

Hi David,

Thanks very much for the assistance. It worked perfectly!

Regards,
Stewart

|||

The views in the master database are very powerful. Try the information_schema views first, and switch to the sys... views if the information_schema views don't have what you need.

Glad it helped!

sql

Thursday, March 22, 2012

Best practices to design datawarehouse for a Fraud Detection/Transaction Monitoring Application

Hi All,

This thread relates to following thread of Architecure forum.

http://forums.microsoft.com/msdn/showpost.aspx?postid=1908857&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=2

Because after reaching at decision that application would require datawarehousing the members suggested that it would be nice if it is posted on datawarehouse forum.

I would appreciate if could go through the mentioned thread that would state application's business requirement.

Now i am continuing the from the thread where we were discussing about SCD.

In our Application customer address is defined in 6 fields (Street,city,State,Country,Pincode,ContactNo) so if we are to maintain 3 type of addresses like Office,Communication,Residence then we have to add 18 fields in our Customer Master dimension table in our dataware house.

Suppose after some time customer office address is changed so what should be done in Customer Master Table at time of synchronization, Should ETL process insert a new record into dimension table with updated address in office address fields or it should update the existing record fields without adding new one.

If for Changing dimensions we are adding a new record then we have to maintain a flag field where status of record would be kept like 'current record' or 'old record' , this would help us to identify the main record.Every time whenever a new customer record is added old record status is changed to 'Old record'.

Should we add new record for changing fields like address,designation,employer,annual income etc or update the existing record only.Because in our analysis these all fields would play major because after each profile updation a risk category is assigned to customer.So we can not afford to overwrite any field information because at any time old information can be asked by management/regulator.

If every time for each field updation a new record is updated then customer master datawarehouse table would become 5 times if each customer record is updated five times during the year.

Suggest us the efficient approach to handle changing dimensions.

Thanks & Regards,

Sameer Gautam

Hi,

You don't have to create a record each time a change happens, just depends if you want to capture the history for that field or not. For example you may want to capture history for change of address, so you would add a new record, but you may not want it for surname, so just update all records for that customer with the new surname.

SCD are a pain to say the least when data warehousing, the principle is nice but coding it is sometimes complicated especially if you try and use snowflakes. And space soon becomes an issue, if you data changes regually. And you are right that if you make a change to a customer 5 times in a year then you will get 5 new records. Although you may code it so it only captures the end of year snapshot, rather than every change.

I would have a look through some of kimballs articles:

http://kimballgroup.com/html/articlesArchitecture/articlesAdvancedDim.html

Good luck

Matt

sql

Best practices for SQL data access

Hi,

i am newbie in ASP.net world. i am using 3 tier application architechture for my web based application. data base is sql server 2000. i have looked at object and sql datasource objects but i think they are not suitable for my requirements. so i am planning to directly use ado.net to access data from database.( i.e. creating connection, then creating commands n executing them)

now what i am looking for is the best known practices for the above task. i have following solutions in my mind please let me know if i am missing some or which could be the best aproach.

careate one class which will handle all the database requests so that all the pages and business objects request that class to to do all the db related stuff. (creating connection, command n execution)

have a class which will return connection to your page or business object and then u can use that connection to do db related stuff.

some thing in between that you create a sqlcommand and pass it to a class which will take care of connections and execute you request.

what i am worried about more is the connections to database and the connection pooling n sharing stuff. i dont have any idea how they works.

please help me in this regard

thanks

Have you looked at the Data Access Application Block?

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

|||

thanks i had a look through it, n it sound goods.

any other thoughs or ideas???

|||

aakbar:
thanks i had a look through it, n it sound goods.
any other thoughs or ideas???

Yes, follow those suggestions there... Smile

|||

Outside of that, if was using ADO, I'd create a Business Logic class for each of the tables in my database using this class to generate all of the business logic which in turn would then call the ADO function within the DAL which would be your Data Application Blocks.

Tuesday, March 20, 2012

Best Practices for Design of Views

Hi, I have been using Views for a while now instead of constructing SQL
statements in the application but one the problems is that as the
applications grow the views need more and more columns, alisaes, and
both Lookup values and Foreign key ID for a range of uses.
At least when constructing SQL SELECT in the application each form or
function has a specific need for columns so you dont aim for any
re-usability.
How it is best to manage this when designing views? Showuld there be
one view only per logical entity that has a lot of columns making it
applicable for a whole range of uses, or individual views, one per use
with its own specific columns. If the latter is used I have a problem
naming and remembering names of all the different views.
An example may help illustrate this:
vwInvoices - This currently contains only the columns needed to diplay
a list of Invoices after doing a search, + any columns used in the
search. But when drilling into a single Invoice from the list it will
need many more columns. While the search tool only needs the foreign
key ID of the user who created it (The search tool users a dropdownn
list of users with IDs behind names) , the full detail screen needs to
show the actual user name from the users table.
Shold this view even be used for for selecting one record? Should a
stored procedure be used instead?
Are there any design principles or best practices anyone ccan share for
these issues.
Thanks.hals_left wrote:
> Hi, I have been using Views for a while now instead of constructing SQL
> statements in the application but one the problems is that as the
> applications grow the views need more and more columns, alisaes, and
> both Lookup values and Foreign key ID for a range of uses.
> At least when constructing SQL SELECT in the application each form or
> function has a specific need for columns so you dont aim for any
> re-usability.
> How it is best to manage this when designing views? Showuld there be
> one view only per logical entity that has a lot of columns making it
> applicable for a whole range of uses, or individual views, one per use
> with its own specific columns. If the latter is used I have a problem
> naming and remembering names of all the different views.
> An example may help illustrate this:
> vwInvoices - This currently contains only the columns needed to diplay
> a list of Invoices after doing a search, + any columns used in the
> search. But when drilling into a single Invoice from the list it will
> need many more columns. While the search tool only needs the foreign
> key ID of the user who created it (The search tool users a dropdownn
> list of users with IDs behind names) , the full detail screen needs to
> show the actual user name from the users table.
> Shold this view even be used for for selecting one record? Should a
> stored procedure be used instead?
> Are there any design principles or best practices anyone ccan share for
> these issues.
> Thanks.
Best practice in most environments is that the application should
access the database only through stored procedures, not views. Procs
are the best method to encapsulate logic, facilitate code reuse,
optimise performance and implement security. Views are useful if you
need to share the same query logic in several procs. For most business
process applications it isn't good practice to access views or tables
directly. See:
http://msdn.microsoft.com/library/d...y/en-us/opti...
http://www.sql-server-performance.c..._procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/arch...1/17/38095.aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Oops. One of the links got scrambled:
http://msdn.microsoft.com/library/d.../>
1a_6x45.asp
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David.
If I understand correctly, the best practice to display lines for a
single invoice would therefore be to have a stored procedure that
queries a view , rather than have the application construct a WHERE
clause and query the view directly ?
The procedure would be something like this ?
-- Retrieves the Invoice Lines for 1 invoice
CREATE PROCEDURE [dbo].[getSingleInvoiceLines]
@.InvoiceID Int
AS
SELECT [Description],Quantity,UnitCost, Department,Nominal
FROM dbo.vwInvoiceLines
WHERE Invoice=@.InvoiceID
GO
And used something like this in an application ?
set oRS = objConn.Execute ( "dbo.getSingleInvoiceLines " & intInvoice )
// Or with ADODB.Command / Explicit Parameters etc..
If Not oRS.EOF THen
while not oRS.EOF%>
...|||hals_left wrote:
> Thanks David.
> If I understand correctly, the best practice to display lines for a
> single invoice would therefore be to have a stored procedure that
> queries a view , rather than have the application construct a WHERE
> clause and query the view directly ?
> The procedure would be something like this ?
> -- Retrieves the Invoice Lines for 1 invoice
> CREATE PROCEDURE [dbo].[getSingleInvoiceLines]
> @.InvoiceID Int
> AS
> SELECT [Description],Quantity,UnitCost, Department,Nominal
> FROM dbo.vwInvoiceLines
> WHERE Invoice=@.InvoiceID
> GO
> And used something like this in an application ?
> set oRS = objConn.Execute ( "dbo.getSingleInvoiceLines " & intInvoice )
> // Or with ADODB.Command / Explicit Parameters etc..
> If Not oRS.EOF THen
> while not oRS.EOF%>
> ...
Use the ADO parameters collection rather than construct strings
dynamically. Usually you'll also want to put some error handling in
your procs - in fact that's one of the advantages of procs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Monday, March 19, 2012

Best Practice guidelines

Hi All,

We have an application requirement for a database supporting field service engineers, which calls for a central SQL Server databse, and laptops with the same database replicated onto SQL Express. I'm resposible for designing the database for this, physical and logical. I've designed and built many a database, but never had to use replication before.

I've read through BOL, and understand how the merge replication process works, and I have no problem designing the database assuming it were to run on a single server.

What I am trying to find are whitepapers, or equivalent, on "best design and implementation practice", and especialy common mistakes to avoid.

I know that the windows programmers responsible for the UI will not completely abstract the database from the code (no matter how desirable that is or how often I tell them!), and I really don't want to find I have to change the physical tables or replication logic after they've coded most of the UI .

Many thanks in advance

Richard R

I would say depending on the features you plan to use, Books Online s your best friend.

Let us know if that doesnt help you much and also let us know what specific feature areas you are looking at.

|||

Hi,

BOL is pretty good at describing the process, but doesn't list any caveats. It may of course be that there aren't any - but that would be unusual!

The main question I supppose is, can I just get on and design the system as if it were stand-alone, then build the replication parts afterwards?

I expect I will need some custom logic for reconcilliation, as there has to be a log for tracking part movements, and it is likely that users will synchronise their laptops in a different order to them physically moving the parts, thus generating gaps in the log that need to be filled, as well as there local copy of the data not reflecting the physical reality when they come to move parts.

Is there a best practice for doing this sort of thing without bothering the users?

Thanks for your help,

Richard

Best Practice for Windows Authentication?

Hi,
We are changing our classic ASP web application to use Windows
Authentication instead of SQL Server Authentication.
I would like to know the best practice for:
1. IIS and SQL Server are on the same machine and
2.When they are on different machines in the same domain.
I *think* the solution to 1. is to add the IUSR_MACHINENAME user to SQL
Server (this works but is it the best practice?)
For 2. I have read different opinions. Some say create a IUSR_IISMACHINENAME
account on the SQL Server and make sure they have the same password. Other
say create a user on the domain and use that in IIS as the anonymous user
(and give that user the relevant rights on SQL Server)
I would like to know what is considered the best practice for this sort of
authentication.
Thanks in advancewhat version of IIS you running ?, using ASP.NET ?
http://msdn2.microsoft.com/en-us/library/bsz5788z
"Hugh Mungo" <hugh_mungo@.hotmail.com> wrote in message
news:%23igmiopuFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Hi,
> We are changing our classic ASP web application to use Windows
> Authentication instead of SQL Server Authentication.
> I would like to know the best practice for:
> 1. IIS and SQL Server are on the same machine and
> 2.When they are on different machines in the same domain.
> I *think* the solution to 1. is to add the IUSR_MACHINENAME user to SQL
> Server (this works but is it the best practice?)
> For 2. I have read different opinions. Some say create a
> IUSR_IISMACHINENAME
> account on the SQL Server and make sure they have the same password. Other
> say create a user on the domain and use that in IIS as the anonymous user
> (and give that user the relevant rights on SQL Server)
> I would like to know what is considered the best practice for this sort of
> authentication.
> Thanks in advance
>|||The solution should work with IIS5 and above.
We are not using ASP.NET this is a classic ASP application.
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:OcQFYgruFHA.2076@.TK2MSFTNGP14.phx.gbl...
> what version of IIS you running ?, using ASP.NET ?
> http://msdn2.microsoft.com/en-us/library/bsz5788z
> "Hugh Mungo" <hugh_mungo@.hotmail.com> wrote in message
> news:%23igmiopuFHA.3500@.TK2MSFTNGP09.phx.gbl...
Other[vbcol=seagreen]
user[vbcol=seagreen]
of[vbcol=seagreen]
>|||What a coincidence. Same here. I would definitely be interested to know
how to do this best practice also as this is the exact same thing that I'm
currently working on. One slightly different thing here is that we require
individual accounts (so we can track user activity with our sql profiler)
and would believe we would create a windows account on our domain controller
which resides on a different machine than our web (.asp files) and sql
server (also on separate machine) and was wondering if this would be
possible and how to go about doing this. Would it be as straight forward in
changing the connection string in our .asp files specifying windows
authentication? I am not too familiar in how to do this but was thinking of
maybe removing the anonymous account in IIS so that it would force the user
to login with the windows authentication pop up (in the possible situation
if users share a public machine and/or if the machine's operating system is
not windows with a valid corresponding domain windows account on our domain
controller?...which makes me wonder how this would be incorporated into our
connection string in our .asp files? Thanks in advance

"Hugh Mungo" <hugh_mungo@.hotmail.com> wrote in message
news:eJLVIyruFHA.2312@.TK2MSFTNGP14.phx.gbl...
> The solution should work with IIS5 and above.
> We are not using ASP.NET this is a classic ASP application.
> "David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
> news:OcQFYgruFHA.2076@.TK2MSFTNGP14.phx.gbl...
> Other
> user
> of
>|||_-=?/today_-=?/354
"Hugh Mungo" wrote:

> Hi,
> We are changing our classic ASP web application to use Windows
> Authentication instead of SQL Server Authentication.
> I would like to know the best practice for:
> 1. IIS and SQL Server are on the same machine and
> 2.When they are on different machines in the same domain.
> I *think* the solution to 1. is to add the IUSR_MACHINENAME user to SQL
> Server (this works but is it the best practice?)
> For 2. I have read different opinions. Some say create a IUSR_IISMACHINENA
ME
> account on the SQL Server and make sure they have the same password. Other
> say create a user on the domain and use that in IIS as the anonymous user
> (and give that user the relevant rights on SQL Server)
> I would like to know what is considered the best practice for this sort of
> authentication.
> Thanks in advance
>
>

Sunday, March 11, 2012

Best Practice for Report Projects Related To Application Solutions

Hello Reporting Services Gurus!

I'm about to start on my first reporting services project, but before I mess it up, I'm looking for some guidance on how best to achieve my mission. Here's what I'm looking to achieve:

I have a datacentric application (SQL Server 2005 Express w/ Advanced Services backend) in which I want to build about 50 "canned" reports for the end users. I want to build the reports utilizing server mode so I can take advantage of some of Reporting Services advanced features. I'm not sure what the best practice would be to build the reporting services project. Is it better to include the report project as another project within the application solution? Or, should I build the report project independent of the application solution? What are the pros and cons of doing it either way? How does including the report project build if it's included in the application solution? How would a ClickOnce deployment deploy the report project to the report server?

My ultimate goal would be to have an "off-the-shelf" software solution that includes an installation package consisting of the application project and report project. Is it even possible due to the Reporting Services architecture to achieve an install in this manner with ClickOnce, Windows Installer, or Installshield? Or, is building the report project indepedent of the application project and deploying the reports to the report server "manually" (i.e. deploy within the report server project) the only solution?

Any help would be greatly appreciated!

Tony

I haven't received any feedback yet, so I thought I'd try bumping it back to the top of the thread.

Thanks for any help!

Best practice for relationship

Hi,

There is an idea that using relationship is not suitable in big Database (or Application).

I had checked some big Application : SharePoint Portal(2003 & 2007),

Biztalk Server and see that Microsoft dont use (even one) relatopnships in database.

Everybody, comments plz!

MA.

yes that's a behaviour that MS system tables have...i can comment on sql server system DB , rest of its product i guess must follow the same pattern....see relations (-explicit implementation) are basically constraints (or shud i say we put say a foreign key to enforce a relationship..and ask DB engine to check it (relationship) for us while we do insert/update)..so if ur sure that u wont enter wrong values in the related tables, u can save the effort of db engine testing it for u....the system SP and dll which update system views/tables(in 2000 sql server) take care that all tables data is kept in sync C of ACID properties .....

Thursday, March 8, 2012

Best practice configuring Visual Studio Solution for .SDF databases

Hello,

In our company we haven't tamed Visual Studio yet for the part of configuring

the application database. Therefore I was wondering if there is someone that

can give me hints or clues on what the best practice is for configuring Visual

Solution to handle an SDF database (SQLCE 3.0). I have search a bit on the

internet and this forum, but couldn't find a decent how-to or best practices

guide that copes with this specific problem.

a matter of facts, i am also curious for guidelines and tips on the best way to

configure and manage SQL Server databases under Visual Studio. I guess (read as

hope) that the configuration of a mobile and full blown

database will have some overlaps somehow.

I've found the Database Project which with to manage (Create/Alter) databases,

but is this also the best way to store versions of the database in an

source repository? Can such a project allow me to 'automagically' have a

correct database (so with tables and data) when i want to deploy or debug my

device application?

I am asking this because now our database designers and software engineers have

to do allot of manual actions to update the application with the most recent

database version. SDF databases are flying around all over the place, and in

order to test a specific version of the application, the related database has

to be copied manually on the device. We are not searching for replication

related solutions, nor adding the SDF itself to the source repository, but because

most of our applications are server-client based, it would be really super cool

if we could somehow couple both database definitions and generation together. My

feeling says me there ought to be some feature embedded somewhere deep into

Visual Studio that we missed that would (partially) simplify and automate this

whole process for us.

Another

related question is if it is possible to couple XSD generated datasets to an

SDF database. Is it possible to update the generated code (or the describing

XSD documents) from an SDF database? i again guess that this somehow should be

possible in oderder to keep both code and data in sync, and i do not like the

alternative to always update the XSD when the SDF architecture changes. Somehow

i cannot find out how to do this. i was initially searching for the other way

around: Use an XSD to generate both the DataSet codewrappers and the database

itself.

Any

information is welcome and many thanks in advance!

Peter

Vrenken

Too bad there is no one that can give more information

regarding these issues. Can I assume that allot of people/companies haven’t got

a decent SDF database (configuration) setup or thought about it?

I would really like to open a dialog about these issues. Anyone

wants to join me?

Greetings from the rainy Netherlands,

Peter Vrenken

|||Do all you developers out there have got a decent database setup or never thought about it yet? I am hoping that VTS will solve some of the riddles for us but until that time i would really like to know how other companies manage their SDF databases.

Is there not a single developer (maybe a MVP) that wants to shed some light on it and describe how he does it (or how it should be done)?

Thanks in advance,

Peter Vrenken|||I used to put an .mdb in vss. No reason why this couldn't be done with the .sdf|||Hello and thanks for your response!

I know that as of VS2K5 SP1 the management of .SDF files from within a solution has been greatly enhanced.
You say that you ‘used’ to put an .mdb in VSS. Is this because you found a better solution?

Peter Vrenken|||Peter, there are several questions here and I'll try to help where I can. As I understand your issues you're trying to build SDF databases in a way that can be better managed through developer tools like Visual Studio. At this point VS can help, but not as much as it could. The VS team is working on an updated version of the tools that can address some (but not nearly all) of your issues. The SQL Server Management Studio can also do more to help in this regard. As far as scripting, there is little to no support in any of the tools. I too felt your frustration so I wrote my first EBook to supplement my just completed Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition). This is available at WWW.Hitchhikerguides.net. In the book I walk through the process of creating a database using a script reader that I wrote (and provide with the book), with replication and using the APIs. I expect it will help answer many more of your questions.|||"Used to" only in that I no longer use .mdb files.

I moved to MSDE where I kept the create scripts in vss.

I have migrated these to SQL Express (with the scipts in vss) but am now working on a new application with CE. Unfortunately the scripting that was created from SQL Server Management Tool does not work with CE so I am planning on using vss.