Reply to: Resources for Learning elements of MS SQL server

[lilelvis2000]

No, don't use Access for the db, use the SQL Server db, but connect from Access using ODBC & bring down the minimal set of results to Access.

WHS.. I do this for my client. works brill. You'll need to use passthroughs to get performance if you've a lot of data.

[Grinder]

No, don't use Access for the db, use the SQL Server db, but connect from Access using ODBC & bring down the minimal set of results to Access.

[ThomasSoerensen]

Access? I mean how much are we talking about here and who are the end users? Just yourself or a department or business. Would they mind getting Access snapshot reports or do they want web front end?

I will be the only user - but I cannot use Access anymore as I run into the limit of 2gb db size.

I will be the only user. The db will generate a report I present.

[Grinder]

whs or Excel.

[lilelvis2000]

Not a daily task but an regular one. Performed after loading the tables with new data.

So every time I use the db I will: populate tables with data, execute all views in sequence, generate a report, store results for statistics in some tables.

Access? I mean how much are we talking about here and who are the end users? Just yourself or a department or business. Would they mind getting Access snapshot reports or do they want web front end?

[lilelvis2000]

You haven't said what you are trying to accomplish.

Is this just some kind of daily task that will manipulate data?

stick it all in a stored proc and schedule it using a Sql Server task.

You mentioned reports?

You can create a SSRS report that calls a stored proc, and displays the output.

This can be exposed on the internet/intranet pretty easily, or you can set it up so that the output report is emailed, or dropped to a file share

That's sounds over his head TBH.

Never used SSRS - does it have wizards?

[lilelvis2000]

I am not an experienced programmer.

Forget that link then...you need SQL for Dummies first....or hibernate.

[jmo21]

take a look at Sql Server Integration services as well.

it may be overkill for what you are wanting to however.

[ThomasSoerensen]

You haven't said what you are trying to accomplish.

Is this just some kind of daily task that will manipulate data?

stick it all in a stored proc and schedule it using a Sql Server task.

You mentioned reports?

You can create a SSRS report that calls a stored proc, and displays the output.

This can be exposed on the internet/intranet pretty easily, or you can set it up so that the output report is emailed, or dropped to a file share

Not a daily task but an regular one. Performed after loading the tables with new data.

So every time I use the db I will: populate tables with data, execute all views in sequence, generate a report, store results for statistics in some tables.

[jmo21]

You haven't said what you are trying to accomplish.

Is this just some kind of daily task that will manipulate data?

stick it all in a stored proc and schedule it using a Sql Server task.

You mentioned reports?

You can create a SSRS report that calls a stored proc, and displays the output.

This can be exposed on the internet/intranet pretty easily, or you can set it up so that the output report is emailed, or dropped to a file share

[ThomasSoerensen]

As I am going to be the only user, is there a need for a front end? Can't I just work in the SQL MANAGEMENT Center?

[jmo21]

Ok, will a query window will do for now before you write an application.

Sounds like you might want to use temporary tables, or table variables to store data as you go.

[ThomasSoerensen]

As for your app requirements, I'm not sure what you are meaning by view, as a view is a well defined "thing" in Sql Server.

I know, I meant view in the sense of the "well defined thing"

A query produces a result set (you are calling it a view).

A View in a Sql Server is like a saved query.

I agree

Ie. basic query select x, y, z from Table1
can be saved as a View called View1 for example.

then you can write a query in the form.
Select * from View1 which will give you exactly the same result as your original query.

There are many reasons for this that you can look up.

"how to trigger the execution of the views in a sequence"
Assuming you mean queries, you can call them one by one in your code, or one by one in a stored proc.

At the moment I do not have any front-end/gui code - I plan on using the db from SQL Management Center - I will be the only one on the db

"how to store the result of each view in a more permanent way than just in the output screen of the view - in a new table? in many new tables? what are the options"

they are already in tables, so the first question is why do you want to store them somewhere else? do you need? why can't you just run the query again?
But of course, there is a facility to do that, look up "Select Into" statements.

My queries, stored in view are quite complex and work on a lot of data so 1 query easily takes a few minutes to produce the result. That is why I want to store the result and run other queries against that result - I think if I nest queries/views too much I will perform the same query endless times where actually only 1 time is needed

"reporting capabilities"

look up Sql Server Reporting Services for a starter. There are other reporting packages that can hook up to Sql Server as well.

Thanks

[jmo21]

As for your app requirements, I'm not sure what you are meaning by view, as a view is a well defined "thing" in Sql Server.

A query produces a result set (you are calling it a view).

A View in a Sql Server is like a saved query.

Ie. basic query select x, y, z from Table1
can be saved as a View called View1 for example.

then you can write a query in the form.
Select * from View1 which will give you exactly the same result as your original query.

There are many reasons for this that you can look up.

"how to trigger the execution of the views in a sequence"
Assuming you mean queries, you can call them one by one in your code, or one by one in a stored proc.

"how to store the result of each view in a more permanent way than just in the output screen of the view - in a new table? in many new tables? what are the options"

they are already in tables, so the first question is why do you want to store them somewhere else? do you need? why can't you just run the query again?
But of course, there is a facility to do that, look up "Select Into" statements.

"reporting capabilities"

look up Sql Server Reporting Services for a starter. There are other reporting packages that can hook up to Sql Server as well.

[ThomasSoerensen]

I find that best way to learn is to take some real life project and work on it while using reference guides to find out how to do particular things on that particular system: if you are an experienced programmer then all you need is a good reference and test system.

I am not an experienced programmer.