multiple connects to database

How does reporting services handle connection strings. There is a single report using a shared datasource. Testing this report seems to generate multiple connections to the database for a single user. The report being tested has 4 datasets (3 to obtain parameter value lists) using the same shared datasource but the number of connections for the single user were 20 in one instance. When does reporting services make the connection and when does it release it? It didnt seem to let these go when the report was done executing. Using RS2005 sp1 with DB2The Report Server processes the dataset queries in parallel whenever possible. This is why you see three connections open when you run a report with multiple datasets. Not sure why the number of connections jump to 20. Does the DB2 provider support connection pooling?

Multiple connection type query

Hi everyone,
Is it possible to perform a SELECT/INSERT statement with two different connection types? I want to do the "SELECT" statement with data from SQL Server and "INSERT" it into an Access database all in one query.
Sanctosuse linked tables to sql server in Access and create your insert query in access.

multiple connection to the same data base file: is it possible?

Hi,

Please tell me if my query is suitable for this group or should I post this to the appropriate one. I am trying to find out if it is possible to create a new connection to an already opened database. I am using vb to create the user interface for my application.

I have programmed in a connection to a database file, which will display the file in the main window of the app. Through another form, the user can key in certain criteria which will be used generate a new qeury result of the already opened database file and display the query result in a new window. For this purpose, I have tested this by creating a new connection for the diplay form but I receive this error message :

'C:\MITalong\Project\test'. It is already opened exclusively by another user, or you need permission to view its data."

What is the correct way of doing this, ie. doing queries on an opened database file to generate result on another window. I am utilising the oledb as I wish for my app to work with microsoft access files.

Thank you.

Hi,

are you sure you are in the right group ? That sounds like a Access database file ratehr than a SQL Server file ? if you are using a Access database file you will have to tweak in the settings page, the lock status of an access database file which shouldn′t be then exclusivly locked if someone opens it.

Or are you using SQl Server Express userInbstance functionality ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Oops,

Thanks for the feed back. I final realised I have posted this to the wrong group.

Once again thank you.

|||

Hi again,

I have rechecked all the groups desription in this forum, and this is where queries on oledb connections can be directed.

Yes, I am using access for my project using the oledb thing. I forgot to mention I am quite new to vb.net way of things so some terms used might be lost to me. Regarding my starter thread, does anybody have any ideas? btw, I am working on a window app, so there is no web usage.

Thanks.

multiple connection sql server express

HI
I have a win app (always running 24x7) (.net 2 c#) that uses sql server
express 2005.
I use the following connection string to attach it in the app.config
<connectionStrings>
<add name="Browser.Properties.Settings.DataConnectionSt ring"
connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data. mdf;Integrated
Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Every day a win service, using the same MDB updates/imports the data from
another source. This all works as expected. when the windows service has
completed its update, the win app is notified to refresh its data and show
the updates.
My issue, is that though the data has been refreshed and the win app
reconnects to the database to collect the updates (new datasets) it does not
reflect the updates, simply the older version. I think it is the way I have
attached the database (the win app starts first on sys reboot) is there a
better way of 2 apps using the same database without having to attach it.
Thanks
Richard
It doesn't work that way. You are either both using the same mdf or you are
not. If you are using the same one then any committed changes one user makes
are immediately available to the other user. Your app may require refreshing
if it caches the data but SQL Server only has 1 copy of the committed data.
The exception is if you are using one of the snapshot isolation levels. In
that case depending on the level and what you are doing you may see the
original versions. But you have had to explicitly turn this on and I don't
even think Express edition has this capability available in the first place.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Steele" <RichardSteele@.discussions.microsoft.com> wrote in message
news:48E10FFE-E1A2-4318-BFE0-F1E07E077D17@.microsoft.com...
> HI
> I have a win app (always running 24x7) (.net 2 c#) that uses sql server
> express 2005.
> I use the following connection string to attach it in the app.config
> <connectionStrings>
> <add name="Browser.Properties.Settings.DataConnectionSt ring"
> connectionString="Data
> Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data. mdf;Integrated
> Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
> providerName="System.Data.SqlClient" />
> </connectionStrings>
> Every day a win service, using the same MDB updates/imports the data from
> another source. This all works as expected. when the windows service has
> completed its update, the win app is notified to refresh its data and show
> the updates.
> My issue, is that though the data has been refreshed and the win app
> reconnects to the database to collect the updates (new datasets) it does
> not
> reflect the updates, simply the older version. I think it is the way I
> have
> attached the database (the win app starts first on sys reboot) is there a
> better way of 2 apps using the same database without having to attach it.
> --
> Thanks
> Richard

multiple connection sql server express

HI
I have a win app (always running 24x7) (.net 2 c#) that uses sql server
express 2005.
I use the following connection string to attach it in the app.config
<connectionStrings>
<add name="Browser.Properties.Settings.DataConnectionString"
connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data.mdf;Integrated
Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>
Every day a win service, using the same MDB updates/imports the data from
another source. This all works as expected. when the windows service has
completed its update, the win app is notified to refresh its data and show
the updates.
My issue, is that though the data has been refreshed and the win app
reconnects to the database to collect the updates (new datasets) it does not
reflect the updates, simply the older version. I think it is the way I have
attached the database (the win app starts first on sys reboot) is there a
better way of 2 apps using the same database without having to attach it.
--
Thanks
RichardIt doesn't work that way. You are either both using the same mdf or you are
not. If you are using the same one then any committed changes one user makes
are immediately available to the other user. Your app may require refreshing
if it caches the data but SQL Server only has 1 copy of the committed data.
The exception is if you are using one of the snapshot isolation levels. In
that case depending on the level and what you are doing you may see the
original versions. But you have had to explicitly turn this on and I don't
even think Express edition has this capability available in the first place.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Richard Steele" <RichardSteele@.discussions.microsoft.com> wrote in message
news:48E10FFE-E1A2-4318-BFE0-F1E07E077D17@.microsoft.com...
> HI
> I have a win app (always running 24x7) (.net 2 c#) that uses sql server
> express 2005.
> I use the following connection string to attach it in the app.config
> <connectionStrings>
> <add name="Browser.Properties.Settings.DataConnectionString"
> connectionString="Data
> Source=.\SQLEXPRESS;AttachDbFilename=C:\data\Data.mdf;Integrated
> Security=True;Connect Timeout=60;Database=papdata;User Instance=False;"
> providerName="System.Data.SqlClient" />
> </connectionStrings>
> Every day a win service, using the same MDB updates/imports the data from
> another source. This all works as expected. when the windows service has
> completed its update, the win app is notified to refresh its data and show
> the updates.
> My issue, is that though the data has been refreshed and the win app
> reconnects to the database to collect the updates (new datasets) it does
> not
> reflect the updates, simply the older version. I think it is the way I
> have
> attached the database (the win app starts first on sys reboot) is there a
> better way of 2 apps using the same database without having to attach it.
> --
> Thanks
> Richard

Multiple Browser Connection Problem

I'm having problems working on a DNN Site. I'm doing all the dev work local
on my machine. I've got SQL 2005 Developer Edition. I'm running a DNN site on
the local IIS. I've enabled both tcp and named pipe connections and I've
started the browser service. The web config is pointed to Server=(local).
Simply put I get the message
The page cannot be displayed
There are too many people accessing the Web site at this time.
I realize they don't want people running big-ol' web sites on this but come
one. I've simply got 3 browser windows open. I've got 2 on the local machine
and then I use remote desktop to view the page from another machine and
logged into the site as a test user while on my local machine I'm logged in
as a superuser making changes. I don't know what the heck is going on. I
don't know if changing it to TCP only or even specifying localhost instead of
(local) in the connection string. If anyone has a fix for this please let me
know. Honestly this is pretty silly on Microsofts part. If this is just
something you can't get around, one of those By design failures, Does anyone
know if there is an upgrade I can get from dev edition to standard (or
whatever marketing is calling it this year).
hi,
NetFodder wrote:
> I'm having problems working on a DNN Site. I'm doing all the dev work
> local on my machine. I've got SQL 2005 Developer Edition. I'm running
> a DNN site on the local IIS. I've enabled both tcp and named pipe
> connections and I've started the browser service. The web config is
> pointed to Server=(local). Simply put I get the message
> The page cannot be displayed
> There are too many people accessing the Web site at this time.
as the instance you are referencing is a default instance, there's no need
of the the SQL Browser service..

> I realize they don't want people running big-ol' web sites on this
> but come one. I've simply got 3 browser windows open. I've got 2 on
> the local machine and then I use remote desktop to view the page from
> another machine and logged into the site as a test user while on my
> local machine I'm logged in as a superuser making changes. I don't
> know what the heck is going on. I don't know if changing it to TCP
> only or even specifying localhost instead of (local) in the
> connection string. If anyone has a fix for this please let me know.
> Honestly this is pretty silly on Microsofts part. If this is just
> something you can't get around, one of those By design failures, Does
> anyone know if there is an upgrade I can get from dev edition to
> standard (or whatever marketing is calling it this year).
actually I do not think the problem is SQLExpress related, as SQLExpress
does not limit this way the max number of connections to the SQL Server
instance..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz http://italy.mvps.org
DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
-- remove DMO to reply
|||Actually I did a bunch of research and found a couple of solutions for it.
First the developer edition also does not have a limit (programatically) on
the number of connections. However, for some reason my system did. After
fixing that by uninstalling/reboot/re-installing, I ran into an IIS
limitation. Most likely because I'm working on some modules that actually use
specified user accounts for running. They work kind of the same as SQL, or
exchange where the services need a service account to run. These need a web
account to run so think of them as DNN Service Accounts. Anywho, 2 pieces
together fixed it. First I used MetaEdit to change the IIS limit from 10 to
40. Then I unchecked the box on the web site properties page for KeepAlives.
If anyone runs into this, I hope this helps.
"Andrea Montanari" wrote:

> hi,
> NetFodder wrote:
> as the instance you are referencing is a default instance, there's no need
> of the the SQL Browser service..
>
> actually I do not think the problem is SQLExpress related, as SQLExpress
> does not limit this way the max number of connections to the SQL Server
> instance..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz http://italy.mvps.org
> DbaMgr2k ver 0.21.0 - DbaMgr ver 0.65.0 and further SQL Tools
> -- remove DMO to reply
>
>

Multiple ADOQuery on same connection, performances go down because of serverside cursors...

Hi everyone,

I am coding under Delphi a software which will be using a SQL Server 2000. I am using ADO components and have the latest MDAC: 2.8 SP1.

The software is using a lot of TADOQuery objects which are connected to the same TADOConnection.
The TADOQuery having for options :
- cursorlocation : useclient
- cursortype : static
- locktype : optimistic

On the first use of a TADOQuery in order to execute any type of request, the processing is fast.
If I am using this same object for others requests, everything is fine and as fast as the first request.
But as soon as I am using another TADOQuery, performances are going down.

In order to give some numbers, some requests can take as much as 17 seconds to be executed. The same request would take at most 100 milliseconds if it were executed with the first TADOQuery.

It is not a problem concerning indexes; if I try to use the request analyzer, all my requests are processed quickly.

I used the SQL Profiler to see the details and saw some interesting things :
the first uses of TADOQuery are of type SQL:BatchCompleted
but switching to another TADOquery make it use another type : RPC:Completed.
Contents of this RPC can be a declaration or an execution of cursor.

More precisely, if I am using only one ADOQuery, I see one line in the Profiler for every block of code opening/reading contents/closing.
But using multiples ADOQuery, I see one declaration of cursor for the opening and then every reading produces one cursorfetch.
So, this declaration of cursor may take a long of time and every fetch too and if there is many lines to send to the client, the network is used for each line to send.

Better, using an TADOQuery to make a request and then simply initializing another one by changing the SQL property and then executing a second time the first TADOQuery makes this last execution used as a cursor serverside.
The problem should then on the side of the client but I can not figure where.

I hope having been as clear as needed !
My wishes is to not have recode the whole database side of our software because it is huge and badly designed so it will be hard to test everything fine.
So before recoding I wish to continue the analysis.

So does anyone knows why is happening this serverside thing ?
And is there a simple way to make it not happen ?

Thanks to everyone for any future help !

Is this issue still alive? I have several years experience with Delphi and only a little with sql server, but I can never get ADO to perform quickly especially since our database holds pictures. That's why we opted to connect to sql server through the bordland db engine, since queries are always quick this way. Is this an option for you?

Multiple ADOQuery on same connection, performances go down because of serverside cursors...

Hi everyone,

I am coding under Delphi a software which will be using a SQL Server 2000. I am using ADO components and have the latest MDAC: 2.8 SP1.

The software is using a lot of TADOQuery objects which are connected to the same TADOConnection.
The TADOQuery having for options :
- cursorlocation : useclient
- cursortype : static
- locktype : optimistic

On the first use of a TADOQuery in order to execute any type of request, the processing is fast.
If I am using this same object for others requests, everything is fine and as fast as the first request.
But as soon as I am using another TADOQuery, performances are going down.

In order to give some numbers, some requests can take as much as 17 seconds to be executed. The same request would take at most 100 milliseconds if it were executed with the first TADOQuery.

It is not a problem concerning indexes; if I try to use the request analyzer, all my requests are processed quickly.

I used the SQL Profiler to see the details and saw some interesting things :
the first uses of TADOQuery are of type SQL:BatchCompleted
but switching to another TADOquery make it use another type : RPC:Completed.
Contents of this RPC can be a declaration or an execution of cursor.

More precisely, if I am using only one ADOQuery, I see one line in the Profiler for every block of code opening/reading contents/closing.
But using multiples ADOQuery, I see one declaration of cursor for the opening and then every reading produces one cursorfetch.
So, this declaration of cursor may take a long of time and every fetch too and if there is many lines to send to the client, the network is used for each line to send.

Better, using an TADOQuery to make a request and then simply initializing another one by changing the SQL property and then executing a second time the first TADOQuery makes this last execution used as a cursor serverside.
The problem should then on the side of the client but I can not figure where.

I hope having been as clear as needed !
My wishes is to not have recode the whole database side of our software because it is huge and badly designed so it will be hard to test everything fine.
So before recoding I wish to continue the analysis.

So does anyone knows why is happening this serverside thing ?
And is there a simple way to make it not happen ?

Thanks to everyone for any future help !

Is this issue still alive? I have several years experience with Delphi and only a little with sql server, but I can never get ADO to perform quickly especially since our database holds pictures. That's why we opted to connect to sql server through the bordland db engine, since queries are always quick this way. Is this an option for you?

Multi-parameters issue in data flow task SSIS

Hi all,

I met a problem when trying to pass values to a SQL statement through parameters. It's a data flow task. I used the OLE DB connection. My statement is like the statement below(the real statement is little complex):

Select * from myTable where mydate>? and mydate<?

I used the "set query parameter" dialogbox to bulid two parameters varStartTime and varEndTime, the values for the two parameters were set to "1/1/2005" and "12/30/2006" respectively. But when I click the "Parse query" button, I got errors

Parameter Information cannot be derived from SQL statements. Set parameter information before preparing command.

I have referred to the posts, but the problem still exists. Any help will be highly appreciated. Thanks a lot!

The "Parse Query" never works when you have ? in your SQL command. Just ignore the error.