One large database or many small....

Hi Experts

We are debating what is best:

1. To combine all the company's data in one large database, and use schemas and file groups to create logical and physical distribution on drives and namespaces

or

2. Distribute the data into smaller databases with related data - eg. products and product description in one db, Customers in another and orders and orderlines in a third db.

Just what are the pros and cons?

regards

Jens Chr

In almost any case I would suggest one database. The ability to use foreign key constraints is enough right there. The only reason I would suggest many databases is if you might need to seperate them into different servers, but that is very seldom.

It is just so much easier to work with data in the same database versus multiple databases. You get one log, which is easier to manage (unless you have tons of disk drive channels to have multiple log channels, it will be faster to have the one log). If you seperate stuff into filegroups instead of databases, you can still backup and restore them independently if you need to, as well as position the tables in a filegroup on different drives as needed.

|||I echo Louis' sentiments - listen to him. He's a wise and experienced SQL person :)

Check out my SQL Server 2005 Video Tutorials: http://www.learnsqlserver.com/

One instance or two instances

Hi,

If I have two applications which have several databases for each application, do you create one instance for two applications or you create one instance for each application? Which way is better on pricing, performance, maintanence etc?

Thanks for your input

It does not matter as far as pricing goes. You can install multiple instances on the same server once you have a single license.

If you have multiple instances on the same server, you need to think about how much memory you allocate to each instance (by setting the max memory option). In most cases you would be better off (especially from a performance and maint perspective) to only have one instance on the machine, rather than one instance per application.

Multiple instances can be more secure (from each other), and they can be at different SP levels.

|||

thanks for your help.

Where I can change the parameter for the instance on memory limitation etc?

Thanks

|||

In SQL Server 2005, you can use SQL Server Management Studio (SSMS). In Object Explorer, select the instance, right-click and select Properties. Then select Memory, and set the value for the Max server memory. You want to leave some memory for the OS.

Please mark this question as answered if this was helpful. Thanks!

One Database or More

A bit of advice needed, as I'm having trouble figuring out whether I need to create multiple SQL Server databases or just one...or if it is a matter of choice.

Say I'm creating Website A that does one thing.

And Website B that does another thing.

But each one has common core underlying tables (customers, cargos, ports, etc) that they both use.

Am I best just creating one database or several? Then am I best creating two or three (A,B and core tables)?

Not that used to SQL server at the moment, and this will be a complete backend for the companies main core business, crm, quality, etc - all distinct apps so to speak, but the data has common underlying tables and they will want to cross-reference data.::Am I best just creating one database or several?

This absolutly and 100% depends on whether you want them or not.

In this case:

::Not that used to SQL server at the moment, and this will be a complete backend for the
::companies main core business, crm, quality, etc - all distinct apps so to speak,

No, these are NOT distinct apps. They are all parts of one suite running the company. In this case it is absolutly best to run all this:

* From one database
* with ONE SET OF BUSINESS OBJECTS.|||Yes - part of one suite. Thanks.

One database item per page Reporting Services

I'm creating Reporting Services reports in Visual Studio. I create a new
report, and see the Data, Layout, and Preview tabs.
In the data tab, I say: "select name from mytable". Four names are returned.
I'd like to display one name per page.
I posted this question a few days ago. The folks that responded (thanks)
suggested that in the Layout tab, I could place a table or list on the form,
then drag the "name" from DataSet1 onto the table or list. The problem with
this approach is that all 4 names are grouped on the first page. I'l like to
see one name per page.
In reality, mytable contains dozens of fields which will go on the report
form. I want Reporting Services to print out a one page report for Mr. Able,
Mr. Baker. Mr. Charles, and so on.
--
RandyI think the best way to handle this is with the List control.
In the layout tab drag the list from the toolbox.
You can then drag the name (and any other field) into the list control.
You can then right click on the List control and select properties.
In the General tab, click "Edit details group..."
In the top line of the expression list, choose the name field from the drop
down.
Check the "Page break at end" checkbox.
Each instance of the list will now show up on it's own page. You can drag
whatever fields you want into the list box.
Hope this helps.
"randy1200" wrote:
> I'm creating Reporting Services reports in Visual Studio. I create a new
> report, and see the Data, Layout, and Preview tabs.
> In the data tab, I say: "select name from mytable". Four names are returned.
> I'd like to display one name per page.
> I posted this question a few days ago. The folks that responded (thanks)
> suggested that in the Layout tab, I could place a table or list on the form,
> then drag the "name" from DataSet1 onto the table or list. The problem with
> this approach is that all 4 names are grouped on the first page. I'l like to
> see one name per page.
> In reality, mytable contains dozens of fields which will go on the report
> form. I want Reporting Services to print out a one page report for Mr. Able,
> Mr. Baker. Mr. Charles, and so on.
> --
> Randy|||Hi,
Hey Its simple. Suppose "select name from mytable" is your query.
After dragging table and placing fields as per your wish. Create a group
with "name" field, so whenever name changes it goes to the next page (dont
forget to click on "page break at end".
Amarnath
"randy1200" wrote:
> I'm creating Reporting Services reports in Visual Studio. I create a new
> report, and see the Data, Layout, and Preview tabs.
> In the data tab, I say: "select name from mytable". Four names are returned.
> I'd like to display one name per page.
> I posted this question a few days ago. The folks that responded (thanks)
> suggested that in the Layout tab, I could place a table or list on the form,
> then drag the "name" from DataSet1 onto the table or list. The problem with
> this approach is that all 4 names are grouped on the first page. I'l like to
> see one name per page.
> In reality, mytable contains dozens of fields which will go on the report
> form. I want Reporting Services to print out a one page report for Mr. Able,
> Mr. Baker. Mr. Charles, and so on.
> --
> Randy|||Got it! Many thanks...
--
Randy
"Amarnath" wrote:
> Hi,
> Hey Its simple. Suppose "select name from mytable" is your query.
> After dragging table and placing fields as per your wish. Create a group
> with "name" field, so whenever name changes it goes to the next page (dont
> forget to click on "page break at end".
> Amarnath
> "randy1200" wrote:
> > I'm creating Reporting Services reports in Visual Studio. I create a new
> > report, and see the Data, Layout, and Preview tabs.
> >
> > In the data tab, I say: "select name from mytable". Four names are returned.
> > I'd like to display one name per page.
> >
> > I posted this question a few days ago. The folks that responded (thanks)
> > suggested that in the Layout tab, I could place a table or list on the form,
> > then drag the "name" from DataSet1 onto the table or list. The problem with
> > this approach is that all 4 names are grouped on the first page. I'l like to
> > see one name per page.
> >
> > In reality, mytable contains dozens of fields which will go on the report
> > form. I want Reporting Services to print out a one page report for Mr. Able,
> > Mr. Baker. Mr. Charles, and so on.
> >
> > --
> > Randy|||That's exactly what I needed. Many thanks...
--
Randy
"Limey" wrote:
> I think the best way to handle this is with the List control.
> In the layout tab drag the list from the toolbox.
> You can then drag the name (and any other field) into the list control.
> You can then right click on the List control and select properties.
> In the General tab, click "Edit details group..."
> In the top line of the expression list, choose the name field from the drop
> down.
> Check the "Page break at end" checkbox.
> Each instance of the list will now show up on it's own page. You can drag
> whatever fields you want into the list box.
> Hope this helps.
>
> "randy1200" wrote:
> > I'm creating Reporting Services reports in Visual Studio. I create a new
> > report, and see the Data, Layout, and Preview tabs.
> >
> > In the data tab, I say: "select name from mytable". Four names are returned.
> > I'd like to display one name per page.
> >
> > I posted this question a few days ago. The folks that responded (thanks)
> > suggested that in the Layout tab, I could place a table or list on the form,
> > then drag the "name" from DataSet1 onto the table or list. The problem with
> > this approach is that all 4 names are grouped on the first page. I'l like to
> > see one name per page.
> >
> > In reality, mytable contains dozens of fields which will go on the report
> > form. I want Reporting Services to print out a one page report for Mr. Able,
> > Mr. Baker. Mr. Charles, and so on.
> >
> > --
> > Randy

One data source view and multiple data source

Hi,

In my datawarehouse we have different database one for dimensions and one for fact tables.

can we create a cube to pull dimensions from one data soure and fact from other databsource?

I recommend you to have the fact tables and the dimensions in the same database.

Your long term quick-fix is to use views between the databases.

Your short scenario description looks like you are building a cube directly from a source system.

If you need to connect another source system you will have to create a data wareouse to consolidate each source.

If not, you wille be creating information silos above each source system that you cannot connect to a second system.

HTH

Thomas Ivarsson

|||both the source are on the same SQL Server but different databases, I was planning to use View but was just considering the performance impact that will cause.|||

Actually Analysis Services allows for having dimensions and parittions to come from different datasources.

The caveat here is not to use different datasources to define your dimension. In such case Analysis Services might decide to use OPENROWSET clause as part of the query it sends during processing of dimension. This would slow you down considerably. But having partitions to come from different datasource should be perfectly fine.

Run Profier to capture SQL queries Aanlysis Server sends during processing and verify you dont get OPENROWSET is these queries.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

one configure file for all my package.

Hi,

How can i create one configure file that all my packages will inherit the connection to other databases from him?

thanks.

what is better to save my connection string into a table in my DWH or save them to XML file,

so when i want to change my SSIS to work with test enviroment it take the connection string from the db and when i want to switch to prod enviroment i'll use the connection of prod that store in the db.

i think to create a package will switch the connection string value whenever i want?

is it the right way ?

any ideas?

thanks alot!

|||Use of the database or XML is mostly a matter of personal preference. An additional step that is required with a database configuration is managing connections to the configuration database itself. You usually need a seperate XML configuration (or a variable that you set via the commandline) to handle the connection for the configuration database.

On error i want to send error description as mail

I have create a SSIS package, for data export import process, but if my task get failed then i have to send a mail with proper error description as the SSIS generate in output window.

Can any one sugget me to, how can i store that error desciption in my variable.?

Thanks in advance.

You can create a table with the following fields packageid,packagename,taskname,errormessage.

And later write a stored procedure which will insert the data into this table from the package variables. On event handler tab call this stored procedure and pass the system variables "system::executioninstanceguid","system::packagename","system::sourcename","system::errordescription".

Once this is done you can read values from this particular table and assign it to a variable and later on call it in the send mail task.Hope this will help you.

|||

Use OnError Event in Event Handler and system variables such as [System::ErrorCode] and [System::ErrorDescription]

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

|||

As you define above i'm doing such a way

dts.variable("User::vName").value = dts.variable("System::ErrorDescription")

but it is going me error in my script task.

:-(

please provide more specific detail or information.

|||

Hi..

1. In OnError Event Handler, Add a Script Task..

2. Define ReadWriteVariables value in ScriptTask Properties like this

ReadWriteVariables | User::vName,System::ErrorDescription

3. In VSA, edit your script like this..

Dts.Variables("vName").Value = Dts.Variables("ErrorDescription").Value.ToString

HTH

ADConsulting / SQLLeader.com / Daeseong Han

|||

Thanks for you needful help.

:-)

sql

On demand Subreports

Is it possible to create a subreport, that will show after i clicked a certain value in the main report.
for insance:
Customer sales
Microsoft 200k
Oracle 100k
Clicking on the customer microsoft should start the subreport (on demand for proformance) where all sales details are in.
Please let me know, thanks in advanceYou might want to look into drillthrough reports.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_interactive_v1_38tn.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6wa3.asp
Also check out the samples - the Territory Sales report uses drillthrough:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSAMPLES/htm/rss_samplereports_v1_5u7n.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"M.a.t" <M.a.t@.discussions.microsoft.com> wrote in message
news:6D59E6F9-9EA7-4C7A-9318-B7A30838E3D9@.microsoft.com...
> Is it possible to create a subreport, that will show after i clicked a
certain value in the main report.
> for insance:
> Customer sales
> Microsoft 200k
> Oracle 100k
> Clicking on the customer microsoft should start the subreport (on demand
for proformance) where all sales details are in.
> Please let me know, thanks in advance

On delete trigger for a view

Hi!
I have a view defined in MANAGE database as:
CREATE VIEW dbo.sysdatabasesview AS
SELECT *
FROM master.dbo.sysdatabases WITH (nolock)
GO
Trying to place a trigger on it:
CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview]
FOR DELETE
AS
Declare @.user_name sysname, @.msg varchar(3000)
select @.user_name = name
from deleted
set @.msg = 'Delete database ' + @.user_name + ' on server ' +
@.@.servername + ' from host ' + host_name()
insert into MANAGE..MAIL (recipient, subject, message, occur)
values ('myemail@.domain.local', 'Delete datadase', @.msg, getdate())
Get an error:
Error 208: Invalid object name 'dbo.sysdatabasesview'
What is wrong?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You cannot create a FOR trigger (now known as an AFTER trigger) on a =view. You can create an INSTEAD OF trigger on a view, however.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roust_m" <roustam@.hotbox.ru> wrote in message =news:a388fd78.0309300241.4462ca59@.posting.google.com...
Hi!
I have a view defined in MANAGE database as:
CREATE VIEW dbo.sysdatabasesview AS
SELECT *
FROM master.dbo.sysdatabases WITH (nolock)
GO
Trying to place a trigger on it:
CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview] FOR DELETE AS Declare @.user_name sysname, @.msg varchar(3000) select @.user_name =3D name from deleted
set @.msg =3D 'Delete database ' + @.user_name + ' on server ' +
@.@.servername + ' from host ' + host_name()
insert into MANAGE..MAIL (recipient, subject, message, occur) values ('myemail@.domain.local', 'Delete datadase', @.msg, getdate())
Get an error:
Error 208: Invalid object name 'dbo.sysdatabasesview'
What is wrong?
Thanks.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You cannot create a FOR trigger (now =known as an AFTER trigger) on a view. You can create an INSTEAD OF trigger on =a view, however.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roust_m" wrote in message news:a388fd=78.0309300241.4462ca59@.posting.google.com...Hi!I have a view defined in MANAGE database as:CREATE VIEW dbo.sysdatabasesview ASSELECT *FROM master.dbo.sysdatabases WITH (nolock)GOTrying to place a =trigger on it:CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview] FOR DELETE AS Declare @.user_name =sysname, @.msg varchar(3000) select @.user_name =3D name from deleted =set @.msg =3D 'Delete database ' + @.user_name + ' on server ' =+@.@.servername + ' from host ' + host_name()insert into MANAGE..MAIL (recipient, =subject, message, occur) values ('myemail@.domain.local', ='Delete datadase', @.msg, getdate()) Get an error:Error =208: Invalid object name 'dbo.sysdatabasesview'What is wrong?Thanks.

---

--=_NextPart_000_0012_01C3872D.EA0155E0--sql

on delete cascade on the same table - is it possible?

Hi !
as u should know this code doesn't work ..
CREATE TABLE tempo (
cid int,
parent_id int,
PRIMARY KEY (cid),
FOREIGN KEY (parent_id) REFERENCES tempo ON DELETE CASCADE
)
any suggestion to achieve this ?
thx!Create a trigger to delete all the records with the same criteria. When a record is deleted in that Table.

This Code may help

create trigger delcascadetrig
on tempo
for delete
as
delete parent_id
from tempo, deleted
where tempo.cid = deleted.cid

OLEDB for DB2 how to ...

Hello,
I've installed the Host Integration server client SP1 on my development
machine running SQL server. I'm tryiyng to create a udl file to connect to
the DB server but I allways get an error about wrong parameters ... can
anyone help me out?
The idea is to be able to run a dts task against the DB2 thx!OK the connection test worksm
now I struggling with the meaning and significance of collection and schema
options ...
Tips are welcome
"John Koswalski" <john.koswalski@.shell.nl> wrote in message
news:umv9fhe2DHA.2856@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Hello,
> I've installed the Host Integration server client SP1 on my development
> machine running SQL server. I'm tryiyng to create a udl file to connect

---

to

quote:

---

> the DB server but I allways get an error about wrong parameters ... can
> anyone help me out?
> The idea is to be able to run a dts task against the DB2 thx!
>

---

OLEDB Cursor Questions

Howdy folks! I have some questions on cursors.

I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:

Forward-Only:

Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.

This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?

MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?

If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?

Scrollable:

"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."

Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?

Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.

Thanks in advance!

The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.

The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.

|||

Thanks Joao.

So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?

Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.

|||Now, there's a question I would like to see answered by someone from Microsoft.|||I have yet another tough question:

Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).

Both rowsets use forward-only cursors.

Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?

UPDATE:

I've since learned that a command can only have one rowset open at a time...thus the above is a moot point

OLEDB Cursor Questions

Howdy folks! I have some questions on cursors.

I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:

Forward-Only:

Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.

This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?

MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?

If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?

Scrollable:

"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."

Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?

Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.

Thanks in advance!

The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.

The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.

|||

Thanks Joao.

So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?

Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.

|||Now, there's a question I would like to see answered by someone from Microsoft.|||I have yet another tough question:

Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).

Both rowsets use forward-only cursors.

Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?

UPDATE:

I've since learned that a command can only have one rowset open at a time...thus the above is a moot point

OLEDB Cursor Questions

Howdy folks! I have some questions on cursors.

I'm trying to create a RecordSet type object in native oledb which only stores 1 row at a time. The main reason for this is that I very well may be selecting thousands of records and I don't want to run into memory issues storing all those rows. Thus I need to use a cursor to move between records in a Rowset. Unfortunately, I'm required to support SQL query processing, so unless there's an easy workaround, I'm limited to the forward-only and scrollable cursors. Both cursors would work from the functionality standpoint, but I have some concerns with both of them:

Forward-Only:

Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.

This cursor does not allow quick restarts...in fact, it's mentioned in an msdn article that when this cursor is restarted, it may rerun the query. I would have to restart a forward only cursor to allow for MoveToPrev() and MoveToFirst() functionality. Can anyone confirm that the query is rerun on a restartposition call?

MSDN states that this cursor does not support updates, does this mean that if I execute an UPDATE query on a row within the rowset, then move to that row via GetNextRows the data will be old?

If the above is true, that makes me think the cursor is caching the data. MSDN doesn't state this, but wouldn't that be accurate?

Scrollable:

"The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed."

Is the cache the same as just storing all selected rows in memory? If so, why would I bother using this cursor at all instead of storing the rows in my own data structure?

Again if anyone knows a simple way to run SQL queries and still use a base table cursor let me know; otherwise I've got to decide between the slower two.

Thanks in advance!

The forward-only does not cache the data so if you want to move back, you would have to requery forcing the cursor to the first row. When MSDN says the cursor is not updatable it means that you cannot change the data you read and like in a base table cursor. If you update the data on a different query you should see the changes.

The scrollable cursor caches data for SELECT queries which makes it expensive to run (and that is why I don't use it). Cachind the data yourself is a good option.

|||

Thanks Joao.

So does the scrollable cursor cache the data as it reads each row, or cache the entire selected rowset at the beginning?

Regardless I agree that it's impractical. I guess I'll just tell my team that if they want efficiency, they'll need to sort the data properly in the query beforehand.

|||Now, there's a question I would like to see answered by someone from Microsoft.|||I have yet another tough question:

Say I set an ICommandText object (e.g. m_pICmdText) to "SELECT * FROM Table1" and execute it, placing the data in pIRowset1.
Now say I come along and execute "SELECT * FROM Table2" from the same ICommandText object, but placing the data into a different IRowset (say pIRowset2).

Both rowsets use forward-only cursors.

Now what will happen if I restart the position of pIRowset1? Will the original sql statement be executed, or will the last text to be placed into m_pICmdText be executed?

UPDATE:

I've since learned that a command can only have one rowset open at a time...thus the above is a moot point

OLE DB error - Please Help

OLE DB error
I have the following sp running one a night via an agent: (See error below)
CREATE PROCEDURE [dbo].[IDR_Get_IDR] AS
Declare @.LastRefresh DateTime,
@.ProcessID Int,
@.MyError int
Begin Tran Process_Up
-- Find the last refreshed process
Select @.LastRefresh = P_EndTime
From dbo.IDR_Process
Where P_Status ='S'
-- Record the start of the refresh process
Select @.ProcessID = Max(P_Id)+1
From dbo.IDR_Process
Insert Into dbo.IDR_Process(
P_Id,P_ProcessID,P_StartTime,P_Status)
Select @.ProcessID ,57,GetDate(),'S'
Set @.MyError = @.@.Error
IF @.MyError <> 0
Rollback Tran Process_Up
ELSE
BEGIN
commit Tran Process_Up
Set @.MyError =0
End
Begin Tran Pop_Idr WITH MARK
Truncate Table dbo.IDR_IncomeDist
Insert Into dbo.IDR_IncomeDist(
id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertySta
te,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmou
nt,
ApplicationCommitmentFeeAmount,DiscountP
ointsAmount,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, CommissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified)
SELECT id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertySta
te,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmou
nt,
ApplicationCommitmentFeeAmount,DiscountP
ointsAmount,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, commissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified
FROM OPENQUERY(Digex, 'SELECT * from FileArchive.dbo.IncomeDist')
Where Created >@.LastRefresh-1 or Modified >@.LastRefresh-1
Set @.MyError = @.@.Error
IF @.MyError <> 0
Begin
Rollback Tran Pop_Idr
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'F'
End
ELSE
Begin
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'S'
commit Tran Pop_Idr
End
GO
When pull out the individual pieces the query and run them in sequence they
al run fine but when I run the sp as a whole I get the following error:
Server: Msg 7391, Level 16, State 1, Procedure IDR_Get_IDR, Line 35
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Gerald Blackwell
gblackwell@.alliedhomenet.comI cannot give you the exact reason for the error but here are sme things to
check:
Since there is a linked server and a BEGIN TRAN statement a distributed
transaction is used. This requires that MSDTC be running on both machine
and SQL Server has to have access to it. Check the SQL Server errorlog for
a message related to MSDTC or the transaction coordinator. Also if these
servers are on Windows 3003 verify that you have Network MSDTc installed.
It is a Windows component so you can check in Control Panel - Add/Remove
Programs.
Rand
This posting is provided "as is" with no warranties and confers no rights.

OLE DB error - Please Help

OLE DB error
I have the following sp running one a night via an agent: (See error below)
CREATE PROCEDURE [dbo].[IDR_Get_IDR] AS
Declare @.LastRefresh DateTime,
@.ProcessID Int,
@.MyError int
Begin Tran Process_Up
-- Find the last refreshed process
Select @.LastRefresh = P_EndTime
From dbo.IDR_Process
Where P_Status ='S'
-- Record the start of the refresh process
Select @.ProcessID = Max(P_Id)+1
From dbo.IDR_Process
Insert Into dbo.IDR_Process(
P_Id,P_ProcessID,P_StartTime,P_Status)
Select @.ProcessID ,57,GetDate(),'S'
Set @.MyError = @.@.Error
IF @.MyError <> 0
Rollback Tran Process_Up
ELSE
BEGIN
commit Tran Process_Up
Set @.MyError =0
End
Begin Tran Pop_Idr WITH MARK
Truncate Table dbo.IDR_IncomeDist
Insert Into dbo.IDR_IncomeDist(
id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmoun t,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, CommissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified)
SELECT id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmoun t,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, commissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified
FROM OPENQUERY(Digex, 'SELECT * from FileArchive.dbo.IncomeDist')
Where Created >@.LastRefresh-1 or Modified >@.LastRefresh-1
Set @.MyError = @.@.Error
IF @.MyError <> 0
Begin
Rollback Tran Pop_Idr
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'F'
End
ELSE
Begin
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'S'
commit Tran Pop_Idr
End
GO
When pull out the individual pieces the query and run them in sequence they
al run fine but when I run the sp as a whole I get the following error:
Server: Msg 7391, Level 16, State 1, Procedure IDR_Get_IDR, Line 35
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Gerald Blackwell
gblackwell@.alliedhomenet.com
I cannot give you the exact reason for the error but here are sme things to
check:
Since there is a linked server and a BEGIN TRAN statement a distributed
transaction is used. This requires that MSDTC be running on both machine
and SQL Server has to have access to it. Check the SQL Server errorlog for
a message related to MSDTC or the transaction coordinator. Also if these
servers are on Windows 3003 verify that you have Network MSDTc installed.
It is a Windows component so you can check in Control Panel - Add/Remove
Programs.
Rand
This posting is provided "as is" with no warranties and confers no rights.

OLE DB error - Please Help

OLE DB error
I have the following sp running one a night via an agent: (See error below)
CREATE PROCEDURE [dbo].[IDR_Get_IDR] AS
Declare @.LastRefresh DateTime,
@.ProcessID Int,
@.MyError int
Begin Tran Process_Up
-- Find the last refreshed process
Select @.LastRefresh = P_EndTime
From dbo.IDR_Process
Where P_Status ='S'
-- Record the start of the refresh process
Select @.ProcessID = Max(P_Id)+1
From dbo.IDR_Process
Insert Into dbo.IDR_Process(
P_Id,P_ProcessID,P_StartTime,P_Status)
Select @.ProcessID ,57,GetDate(),'S'
Set @.MyError = @.@.Error
IF @.MyError <> 0
Rollback Tran Process_Up
ELSE
BEGIN
commit Tran Process_Up
Set @.MyError =0
End
Begin Tran Pop_Idr WITH MARK
Truncate Table dbo.IDR_IncomeDist
Insert Into dbo.IDR_IncomeDist(
id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmount,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, CommissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified)
SELECT id, DateSubmitted, DateSent,SubmittedBy ,
Branch,TransferToBranch,TransferAmount,
AssignedToBranch, BranchEmail,BranchPhone,
BranchFax, LoanNumber, BlockF,LoanAmount,
DateClosed, Lender, Investor, Appraiser ,
AppraiserLicense, LoanPurpose, LoanProgram ,
LoanType, ARMType, OtherProgramType, ExperionScore,
TransUnionScore, EquifaxScore,BrwFirstName,
BrwLastName, BrwSS,BrwAddress,BrwCity,BrwState,
BrwZIP, PropertyAddress,PropertyCity,PropertyState,
PropertyZip,TitleOrAttorney, TitleOfficer,
TitleCompany, TitleCompanyPhone, TitleCompanyAddress,
TitleCompanyCity, TitleCompanyState, TitleCompanyZIP,
OriginationFeeAmount,UnderwritingFeeAmount,
ApplicationCommitmentFeeAmount,DiscountPointsAmount,
YieldPremiumAmount, ProcessingFeeAmount, CourierAmount,
AppraisalFeeAmount, CreditReportAmount, InspectionFeeAmount,
FloodCertAmount, AutomatedUWAmount, LenderPaidFeeAmount,
AssignedToBranchAmount, NRCFeeAmount, InterestPaidAmount,
GrandTotal, ReserveForMarketing, commissionsPaid,
CommissionTotal, CommissionRecipient01, CommissionRecipient02,
CommissionRecipient03, CommissionRecipient04, CommissionRecipient05,
CommissionRecipient06, CommissionRecipient07, CommissionRecipient08,
CommissionRecipient09, CommissionRecipient10, Commission01Amount,
Commission02Amount, Commission03Amount, Commission04Amount,
Commission05Amount,Commission06Amount, Commission07Amount,
Commission08Amount, Commission09Amount, Commission10Amount,
Check01Number, Check02Number, Check03Number,
Check04Number, Check05Number, Check06Number,
Check07Number, Check08Number, Check09Number,
Check10Number, Check01Amount, Check02Amount,
Check03Amount, Check04Amount, Check05Amount,
Check06Amount, Check07Amount, Check08Amount,
Check09Amount, Check10Amount, ChecksTotal ,
Created,Modified
FROM OPENQUERY(Digex, 'SELECT * from FileArchive.dbo.IncomeDist')
Where Created >@.LastRefresh-1 or Modified >@.LastRefresh-1
Set @.MyError = @.@.Error
IF @.MyError <> 0
Begin
Rollback Tran Pop_Idr
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'F'
End
ELSE
Begin
Update dbo.IDR_Process
Set P_EndTime = GetDate(),
P_Status = 'S'
commit Tran Pop_Idr
End
GO
When pull out the individual pieces the query and run them in sequence they
al run fine but when I run the sp as a whole I get the following error:
Server: Msg 7391, Level 16, State 1, Procedure IDR_Get_IDR, Line 35
The operation could not be performed because the OLE DB provider 'SQLOLEDB'
was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the
specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Gerald Blackwell
gblackwell@.alliedhomenet.comI cannot give you the exact reason for the error but here are sme things to
check:
Since there is a linked server and a BEGIN TRAN statement a distributed
transaction is used. This requires that MSDTC be running on both machine
and SQL Server has to have access to it. Check the SQL Server errorlog for
a message related to MSDTC or the transaction coordinator. Also if these
servers are on Windows 3003 verify that you have Network MSDTc installed.
It is a Windows component so you can check in Control Panel - Add/Remove
Programs.
Rand
This posting is provided "as is" with no warranties and confers no rights.

OLE DB connections

I have .net 2.0 framework and SQL V 8.0 on my web server.

I am trying to create a DSN and I am not given the OLEDB provider for SQL as an option for my list of providers. Is there a download that will add this ? What do I do to get it?

Hi,

If you use Start > Control Panel > Administrative Tools > Data Sources (ODBC) Choose "File DSN" and Choose "Add" and scroll all the way to the bottom, do you not see "Sql Server" ?

|||Alternatively you could check out all these connection strings; http://www.connectionstrings.com/?carrier=sqlserver