Applying the snapshot for Merge rep fails everytime because Connection is reset?

Hi guys, Im desperately hoping someone here can help me. Ive been trying to set up merge replication between 2 servers all weekend and it keeps failing. Im setting this up as follows
The Publisher/Distributor is a SQL Server 2005 machine, the database being replicated is 4gb, and the only subscriber is a SQL Server 2000 SP4 machine. I set up my publication normally, and set up the push subscription, adn set it to initialize immediately. It weill then start bulk copying and go on for about 3-4 hours before it fails and gives me the errors:

Error messages:
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
The process could not bulk copy into table '"dbo"."CONTENT"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
One or more BLOB columns could not be sent to the server, attempt to recover from the problem failed. (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Communication link failure (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Unspecified error (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Failed to send batch after max errors (Source: MSSQLServer, Error number: 0)

This is driving me crazy as I havent slept in ages trying to get this fixed. Anyone have any idea whats going on? Its always one of 2 tables so far that keep failing, ContactLog and Content, what can I do?

For anyone who finds an error similar to this one, its the line "forcibly closed by the remote host" that was the most interesting. Well, found the solution here, in section 4.1.2, http://support.microsoft.com/kb/910228
Basically, SP1 for Win 2K3 includes a lovely feature to prevent DOS attacks, and rather cleverly it thinks SQL Server applying a snapshot is a DOS attack so closes the connection after a set amount of Connect/Disconnects. Jeebus wept. 1 simple registry key cost me my weekend :-|

Hopefully this will save someone the same heartache!

Keep up the good work guys
|||Hello Shane,

Did you find the solution to your problem if yes then please let me know also because i am also facing the same problem from last two weeks and i am not able to solve it and its causing a headache for me please tell me if u have the solution.
my email id is dr_hunaindurrani@.hotmail.com.

Thanks in advance.

Applying the snapshot for Merge rep fails everytime because Connection is reset?

Hi guys, Im desperately hoping someone here can help me. Ive been trying to set up merge replication between 2 servers all weekend and it keeps failing. Im setting this up as follows
The Publisher/Distributor is a SQL Server 2005 machine, the database being replicated is 4gb, and the only subscriber is a SQL Server 2000 SP4 machine. I set up my publication normally, and set up the push subscription, adn set it to initialize immediately. It weill then start bulk copying and go on for about 3-4 hours before it fails and gives me the errors:

Error messages:
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
The process could not bulk copy into table '"dbo"."CONTENT"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
Get help: http://help/MSSQL_REPL20037
An existing connection was forcibly closed by the remote host. (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
One or more BLOB columns could not be sent to the server, attempt to recover from the problem failed. (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Communication link failure (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Unspecified error (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
Failed to send batch after max errors (Source: MSSQLServer, Error number: 0)

This is driving me crazy as I havent slept in ages trying to get this fixed. Anyone have any idea whats going on? Its always one of 2 tables so far that keep failing, ContactLog and Content, what can I do?

For anyone who finds an error similar to this one, its the line "forcibly closed by the remote host" that was the most interesting. Well, found the solution here, in section 4.1.2, http://support.microsoft.com/kb/910228
Basically, SP1 for Win 2K3 includes a lovely feature to prevent DOS attacks, and rather cleverly it thinks SQL Server applying a snapshot is a DOS attack so closes the connection after a set amount of Connect/Disconnects. Jeebus wept. 1 simple registry key cost me my weekend :-|

Hopefully this will save someone the same heartache!

Keep up the good work guys
|||Hello Shane,

Did you find the solution to your problem if yes then please let me know also because i am also facing the same problem from last two weeks and i am not able to solve it and its causing a headache for me please tell me if u have the solution.
my email id is dr_hunaindurrani@.hotmail.com.

Thanks in advance.

Application Server to Point to Mirror Server

Hi All,
I have tested database mirroring for a few days. The application server
is using a connection string in the registry to point to SQL server.
When failover happens, how can I tell the app server to failover and
run against the mirrored server and mirrored database?
Please advice. Thanks.
-GaryYou must use SQL Native Client driver. This gives you the option to specify
the fail-over mirror server name. Then you must also use a witness server
for the fail-over to be automatic.
See books online;
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61fc9caae7c.htm
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Gary Lou" wrote:
> Hi All,
> I have tested database mirroring for a few days. The application server
> is using a connection string in the registry to point to SQL server.
> When failover happens, how can I tell the app server to failover and
> run against the mirrored server and mirrored database?
> Please advice. Thanks.
> -Gary
>|||The other thing to keep in mind is that when the primary fails over the
connection closes with an error and the application server will have to
reopen the connection to get to the new primary. This may require some code
changes unless your application already handles this.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...
> You must use SQL Native Client driver. This gives you the option to
> specify the fail-over mirror server name. Then you must also use a witness
> server for the fail-over to be automatic.
> See books online;
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61fc9caae7c.htm
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Gary Lou" wrote:
>> Hi All,
>> I have tested database mirroring for a few days. The application server
>> is using a connection string in the registry to point to SQL server.
>> When failover happens, how can I tell the app server to failover and
>> run against the mirrored server and mirrored database?
>> Please advice. Thanks.
>> -Gary
>|||Thank you for both of your response.
My application server is running on Windows 2000 sp4. I have SQL Native
Client enable in the SQL 2005 server.
Question:
1) I got a "Login timeout expired" error when tried to set up the
connection under ODBC. Do I need to install sqlncli.msi in the app
server to make it work?
2) The connection string in the registry is as below. What needs to be
changed to make the failover to work?
Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
Library=DBMSSOCN
Many thanks.
Roger Wolter[MSFT] wrote:
> The other thing to keep in mind is that when the primary fails over the
> connection closes with an error and the application server will have to
> reopen the connection to get to the new primary. This may require some code
> changes unless your application already handles this.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
> news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...
> > You must use SQL Native Client driver. This gives you the option to
> > specify the fail-over mirror server name. Then you must also use a witness
> > server for the fail-over to be automatic.
> >
> > See books online;
> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61fc9caae7c.htm
> >
> >
> > --
> >
> > Regards,
> >
> > Dave Patrick ...Please no email replies - reply in newsgroup.
> > Microsoft Certified Professional
> > Microsoft MVP [Windows]
> > http://www.microsoft.com/protect
> >
> > "Gary Lou" wrote:
> >> Hi All,
> >>
> >> I have tested database mirroring for a few days. The application server
> >> is using a connection string in the registry to point to SQL server.
> >> When failover happens, how can I tell the app server to failover and
> >> run against the mirrored server and mirrored database?
> >>
> >> Please advice. Thanks.
> >>
> >> -Gary
> >>
> >|||You need either SQLNCLI or the .Net 2.0 SQL Client to use the failover
feature so you will have to install the appropriate software on your clients
I'm not a connection string expert. Obviously you will need to change the
provider to SQLNCLI and specify the secondary server. There's a good
explanation here:
http://msdn2.microsoft.com/de-de/library/ms366348.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gary Lou" <garylou@.gmail.com> wrote in message
news:1165301726.681691.33270@.l12g2000cwl.googlegroups.com...
> Thank you for both of your response.
> My application server is running on Windows 2000 sp4. I have SQL Native
> Client enable in the SQL 2005 server.
> Question:
> 1) I got a "Login timeout expired" error when tried to set up the
> connection under ODBC. Do I need to install sqlncli.msi in the app
> server to make it work?
> 2) The connection string in the registry is as below. What needs to be
> changed to make the failover to work?
> Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
> ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
> Library=DBMSSOCN
>
> Many thanks.
>
> Roger Wolter[MSFT] wrote:
>> The other thing to keep in mind is that when the primary fails over the
>> connection closes with an error and the application server will have to
>> reopen the connection to get to the new primary. This may require some
>> code
>> changes unless your application already handles this.
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> Use of included script samples are subject to the terms specified at
>> http://www.microsoft.com/info/cpyright.htm
>> "Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
>> news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...
>> > You must use SQL Native Client driver. This gives you the option to
>> > specify the fail-over mirror server name. Then you must also use a
>> > witness
>> > server for the fail-over to be automatic.
>> >
>> > See books online;
>> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61fc9caae7c.htm
>> >
>> >
>> > --
>> >
>> > Regards,
>> >
>> > Dave Patrick ...Please no email replies - reply in newsgroup.
>> > Microsoft Certified Professional
>> > Microsoft MVP [Windows]
>> > http://www.microsoft.com/protect
>> >
>> > "Gary Lou" wrote:
>> >> Hi All,
>> >>
>> >> I have tested database mirroring for a few days. The application
>> >> server
>> >> is using a connection string in the registry to point to SQL server.
>> >> When failover happens, how can I tell the app server to failover and
>> >> run against the mirrored server and mirrored database?
>> >>
>> >> Please advice. Thanks.
>> >>
>> >> -Gary
>> >>
>> >
>|||Thank you Roger and Dave. It's working all together.
Roger Wolter[MSFT] wrote:
> You need either SQLNCLI or the .Net 2.0 SQL Client to use the failover
> feature so you will have to install the appropriate software on your clients
> I'm not a connection string expert. Obviously you will need to change the
> provider to SQLNCLI and specify the secondary server. There's a good
> explanation here:
> http://msdn2.microsoft.com/de-de/library/ms366348.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gary Lou" <garylou@.gmail.com> wrote in message
> news:1165301726.681691.33270@.l12g2000cwl.googlegroups.com...
> > Thank you for both of your response.
> >
> > My application server is running on Windows 2000 sp4. I have SQL Native
> > Client enable in the SQL 2005 server.
> >
> > Question:
> > 1) I got a "Login timeout expired" error when tried to set up the
> > connection under ODBC. Do I need to install sqlncli.msi in the app
> > server to make it work?
> >
> > 2) The connection string in the registry is as below. What needs to be
> > changed to make the failover to work?
> > Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
> > ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
> > Library=DBMSSOCN
> >
> >
> > Many thanks.
> >
> >
> >
> > Roger Wolter[MSFT] wrote:
> >> The other thing to keep in mind is that when the primary fails over the
> >> connection closes with an error and the application server will have to
> >> reopen the connection to get to the new primary. This may require some
> >> code
> >> changes unless your application already handles this.
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> Use of included script samples are subject to the terms specified at
> >> http://www.microsoft.com/info/cpyright.htm
> >>
> >> "Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
> >> news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...
> >> > You must use SQL Native Client driver. This gives you the option to
> >> > specify the fail-over mirror server name. Then you must also use a
> >> > witness
> >> > server for the fail-over to be automatic.
> >> >
> >> > See books online;
> >> > ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61fc9caae7c.htm
> >> >
> >> >
> >> > --
> >> >
> >> > Regards,
> >> >
> >> > Dave Patrick ...Please no email replies - reply in newsgroup.
> >> > Microsoft Certified Professional
> >> > Microsoft MVP [Windows]
> >> > http://www.microsoft.com/protect
> >> >
> >> > "Gary Lou" wrote:
> >> >> Hi All,
> >> >>
> >> >> I have tested database mirroring for a few days. The application
> >> >> server
> >> >> is using a connection string in the registry to point to SQL server.
> >> >> When failover happens, how can I tell the app server to failover and
> >> >> run against the mirrored server and mirrored database?
> >> >>
> >> >> Please advice. Thanks.
> >> >>
> >> >> -Gary
> >> >>
> >> >
> >

Application Server to Point to Mirror Server

Hi All,
I have tested database mirroring for a few days. The application server
is using a connection string in the registry to point to SQL server.
When failover happens, how can I tell the app server to failover and
run against the mirrored server and mirrored database?
Please advice. Thanks.
-GaryYou must use SQL Native Client driver. This gives you the option to specify
the fail-over mirror server name. Then you must also use a witness server
for the fail-over to be automatic.
See books online;
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a61f
c9caae7c.htm
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Gary Lou" wrote:
> Hi All,
> I have tested database mirroring for a few days. The application server
> is using a connection string in the registry to point to SQL server.
> When failover happens, how can I tell the app server to failover and
> run against the mirrored server and mirrored database?
> Please advice. Thanks.
> -Gary
>|||The other thing to keep in mind is that when the primary fails over the
connection closes with an error and the application server will have to
reopen the connection to get to the new primary. This may require some code
changes unless your application already handles this.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...
> You must use SQL Native Client driver. This gives you the option to
> specify the fail-over mirror server name. Then you must also use a witness
> server for the fail-over to be automatic.
> See books online;
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/a62d9dd7-3667-4751-a294-a6
1fc9caae7c.htm
>
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Gary Lou" wrote:
>|||Thank you for both of your response.
My application server is running on Windows 2000 sp4. I have SQL Native
Client enable in the SQL 2005 server.
Question:
1) I got a "Login timeout expired" error when tried to set up the
connection under ODBC. Do I need to install sqlncli.msi in the app
server to make it work?
2) The connection string in the registry is as below. What needs to be
changed to make the failover to work?
Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
Library=DBMSSOCN
Many thanks.
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> The other thing to keep in mind is that when the primary fails over the
> connection closes with an error and the application server will have to
> reopen the connection to get to the new primary. This may require some co
de
> changes unless your application already handles this.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Dave Patrick" <DSPatrick@.nospam.gmail.com> wrote in message
> news:382B1DEB-E7EB-4A6F-8AA8-C865A9215D05@.microsoft.com...|||You need either SQLNCLI or the .Net 2.0 SQL Client to use the failover
feature so you will have to install the appropriate software on your clients
I'm not a connection string expert. Obviously you will need to change the
provider to SQLNCLI and specify the secondary server. There's a good
explanation here:
http://msdn2.microsoft.com/de-de/library/ms366348.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Gary Lou" <garylou@.gmail.com> wrote in message
news:1165301726.681691.33270@.l12g2000cwl.googlegroups.com...
> Thank you for both of your response.
> My application server is running on Windows 2000 sp4. I have SQL Native
> Client enable in the SQL 2005 server.
> Question:
> 1) I got a "Login timeout expired" error when tried to set up the
> connection under ODBC. Do I need to install sqlncli.msi in the app
> server to make it work?
> 2) The connection string in the registry is as below. What needs to be
> changed to make the failover to work?
> Provider=SQLOLEDB.1;Password=p8ssw0rd;Persist Security Info=True;User
> ID=DB_USER1;Initial Catalog=MagicDB;Data Source=MagicSQL2000;Network
> Library=DBMSSOCN
>
> Many thanks.
>
> Roger Wolter[MSFT] wrote:
>|||Thank you Roger and Dave. It's working all together.
Roger Wolter[MSFT] wrote:[vbcol=seagreen]
> You need either SQLNCLI or the .Net 2.0 SQL Client to use the failover
> feature so you will have to install the appropriate software on your clien
ts
> I'm not a connection string expert. Obviously you will need to change the
> provider to SQLNCLI and specify the secondary server. There's a good
> explanation here:
> http://msdn2.microsoft.com/de-de/library/ms366348.aspx
>
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Gary Lou" <garylou@.gmail.com> wrote in message
> news:1165301726.681691.33270@.l12g2000cwl.googlegroups.com...

Application Role with VB6

In VB6 I've got the following code when opening a connection to sql server:
With cn
.ConnectionString = "MyConnectionString"
.ConnectionTimeout = 10
.Properties("OLE DB SERVICES") = -2
.Open
'Had to allow execute for sps against the windows group until we can
suss app role
.Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N 'MyPassword'},'ODBC'"
End With
In sql server I've added a windows group to the database to allow the users
of the application to open the connection. I gave the windows group no
permissions of any kind. I added an application role and gave it select,
insert etc. permissions on the tables and execute permissions on all stored
procedures
When the application runs under a windows user (who is in the windows group
I added to the databse), the application can select data from the database
but gets 'execute permission denied' on any stored procedure that it tries t
o
run.
If I examine the permissions of one of these stored procedures, there is a
grant on it for the application role (there are no denies on it at all). If
I
then explicitly grant permission on one of these stored procedures to the
windows group containing the windows user, they are able to access it.
Ok, I think, it looks like it is ignoring the application role, so I deleted
the application role from the database and amended the VB code, the user is
then unable to access any data.
My conclusion then becomes the application role was giving the user access
to table data, but not to stored procedures, and when I granted permission t
o
the windows group this somehow overrode the application role. This is clearl
y
rubbish, because as I understand it, once an application role takes over, no
other permissions matter.
Anybody see what I'm doing wrong?
Thanks
PaulPlease ignore - accidentally posted twice
"Paul Whittaker" wrote:

> In VB6 I've got the following code when opening a connection to sql server
:
> With cn
> .ConnectionString = "MyConnectionString"
> .ConnectionTimeout = 10
> .Properties("OLE DB SERVICES") = -2
> .Open
> 'Had to allow execute for sps against the windows group until we c
an
> suss app role
> .Execute "EXEC sp_setapprole 'MyApp',{ENCRYPT N 'MyPassword'}
,'ODBC'"
> End With
> In sql server I've added a windows group to the database to allow the user
s
> of the application to open the connection. I gave the windows group no
> permissions of any kind. I added an application role and gave it select,
> insert etc. permissions on the tables and execute permissions on all store
d
> procedures
> When the application runs under a windows user (who is in the windows grou
p
> I added to the databse), the application can select data from the database
> but gets 'execute permission denied' on any stored procedure that it tries
to
> run.
> If I examine the permissions of one of these stored procedures, there is a
> grant on it for the application role (there are no denies on it at all). I
f I
> then explicitly grant permission on one of these stored procedures to the
> windows group containing the windows user, they are able to access it.
> Ok, I think, it looks like it is ignoring the application role, so I delet
ed
> the application role from the database and amended the VB code, the user i
s
> then unable to access any data.
> My conclusion then becomes the application role was giving the user access
> to table data, but not to stored procedures, and when I granted permission
to
> the windows group this somehow overrode the application role. This is clea
rly
> rubbish, because as I understand it, once an application role takes over,
no
> other permissions matter.
> Anybody see what I'm doing wrong?
> Thanks
> Paul

Application Role status

Is there a way to determine if an existing connection has invoked a specific
application role? I need to know so that I don't re-invoke it and get an
error.Hi
If you are doing this within your application then why don't you set a
boolean (class attribute) ?
If you try to set the application role a second time you will get an error
return code and Msg 2762
John
"Stefano Nicolini" wrote:

> Is there a way to determine if an existing connection has invoked a specif
ic
> application role? I need to know so that I don't re-invoke it and get an
> error.
>
>|||Yes, you can check this using the USER_NAME() function. The following
T-SQL activates the approle on the connection if it is not already
activated:
IF (SELECT USER_NAME()) <> 'ApproleName'
EXEC sp_setapprole 'ApproleName', 'password'
--Mary
On Mon, 18 Apr 2005 15:12:27 -0400, "Stefano Nicolini"
<StefanoN@.infotronics.com> wrote:

>Is there a way to determine if an existing connection has invoked a specifi
c
>application role? I need to know so that I don't re-invoke it and get an
>error.
>

Application Role and SQL Express (2005)

Hello,

Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)?

Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005

Thanks

John

Hi,

could you please show of these link in google which point out that pooling should be disabled ? You can enable this for sure in SQL Server.

HTH, Jens K. Suessmeyer.

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

Hi,

Here's one

http://solidqualitylearning.com/Blogs/dejan/archive/2006/11/10/3487.aspx

I did say, "still required", but perhaps I should have made it clear that most google searches get to SQL Server 2000.

I am having problems with pooling on...

If pooling is off, I execute sp_setapprole after every time I do an Open.

I assume with pooling on, I should execute it just once, the first time I open for the same connection string.

Thanks

John

|||

http://msdn2.microsoft.com/en-us/8xx3tyca(VS.80).aspx & http://databases.aspfaq.com/database/how-do-i-enable-or-disable-connection-pooling.html

FYI.

|||

If you want to use pooling with application roles, then you should use sp_unsetapprole to unset the approle before returning the connection to the pool. If you cannot enforce this, then you should disable pooling when using application roles. This holds for both SQL Server and SQL Server Express.

Thanks
Laurentiu

Application Role and SQL Express (2005)

Hello,

Can I confirm whether pooling=false in the connection string is still required for SQL Server 2005 (Express Edition)?

Various google searches say pooling has to be turned off for SQL Server 2000, but I was just wondering whether it is still a limitation for SQL Server 2005

Thanks

John

Hi,

could you please show of these link in google which point out that pooling should be disabled ? You can enable this for sure in SQL Server.

HTH, Jens K. Suessmeyer.

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

Hi,

Here's one

http://solidqualitylearning.com/Blogs/dejan/archive/2006/11/10/3487.aspx

I did say, "still required", but perhaps I should have made it clear that most google searches get to SQL Server 2000.

I am having problems with pooling on...

If pooling is off, I execute sp_setapprole after every time I do an Open.

I assume with pooling on, I should execute it just once, the first time I open for the same connection string.

Thanks

John

|||

http://msdn2.microsoft.com/en-us/8xx3tyca(VS.80).aspx & http://databases.aspfaq.com/database/how-do-i-enable-or-disable-connection-pooling.html

FYI.

|||

If you want to use pooling with application roles, then you should use sp_unsetapprole to unset the approle before returning the connection to the pool. If you cannot enforce this, then you should disable pooling when using application roles. This holds for both SQL Server and SQL Server Express.

Thanks
Laurentiu

Application Role and Pooled Connection Limitation

Any idea on how to overcome the limitation of not being
able to use pooled connection after application role is
enabled on a client connection to SQL Server.
Disabling Pooled connection would have a big impact in
performance, and disabling application role allows any non-
authorized application to connect to the back-end.
Of course, I could have my own code to control all this,
but it would require a big re-factoring effort. I would
appreciate if anybody has an ingenious solution to this
limitation on SQL Server.
Thanks,
Toledo>Hi Toledo,
>Since according to KB article 229564, we will encounter
problems when using
>application role with OLE DB resource pooling, if we want
to use OLE DB
>resource pooling, we cannot use application role.
>How about the following workaround?
>1. Create a SQL Server login and store the password in a
table.
>2. Only grant application role the Read right to that
table.
>3. Set application role and retrieve password.
>4. Connect using the login.
>This requires the login password to be secure so only
authroized
>application can read the password.
>
>Bill Cheng
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>--Original Message--
>Any idea on how to overcome the limitation of not being
>able to use pooled connection after application role is
>enabled on a client connection to SQL Server.
>Disabling Pooled connection would have a big impact in
>performance, and disabling application role allows any
non-
>authorized application to connect to the back-end.
>Of course, I could have my own code to control all this,
>but it would require a big re-factoring effort. I would
>appreciate if anybody has an ingenious solution to this
>limitation on SQL Server.
>Thanks,
>Toledo
>.
>

Application On Start And Database connection

Hello,
I was wondering if someone could help me answer this question.

If I had a site like Yahoo, that constantly had users accessing it. Would it be a good or bad idea to automatically open a database connection to SQL Server on Application Start? What would you recommend? Would opening and closing the DB connection on every page save alot of overhead and bottleneck? Thank You.> Would it be a good or bad idea to automatically open a database connection to SQL Server on Application Start?

BAD idea. horrible in fact.

open a database connection immediately before you need it and close it immediately after.

remember, connection pooling is handled for you.. so it's hardly a huge overhead.|||Thank You for your help.

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
Ivan
On Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:

> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:

> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Use
r
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL serve
r
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
Rudi Bruchez
MCDBA

Anything wrong with the connection?

Dear all,
I have a program (written in VB6) running in a Win2000 server with SQL
server 2000 in it. However, the program runs very slow or even "Time Out
Expired" many times. My connection string is:
strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
Id=mySQLUser;Password=myPassword;"
On the other hand I tried to run the same program in my own PC (Win2000
professional) with SQL desktop version. The program runs very fast. Can
anyone advise what is the problem when the program is running in SQL server
2000?
Thanks a million!!
IvanOn Mon, 7 Nov 2005 01:26:50 -0800, Ivan wrote:
> Dear all,
> I have a program (written in VB6) running in a Win2000 server with SQL
> server 2000 in it. However, the program runs very slow or even "Time Out
> Expired" many times. My connection string is:
> strConn = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;User
> Id=mySQLUser;Password=myPassword;"
> On the other hand I tried to run the same program in my own PC (Win2000
> professional) with SQL desktop version. The program runs very fast. Can
> anyone advise what is the problem when the program is running in SQL server
> 2000?
> Thanks a million!!
> Ivan
Hello,
I'm afraid that you need to investigate more in depth. Probably nothing to
do with your connection. Try to use the SQL Server profiler to trace
queries sent to the server, and watch things like reads, CPU, duration on
this trace. Then isolate the queires that could cause problems and watch
their query plan. Search for indexes that could lack.
Look in Enterprise Manager (or doing a sp_who2, or sp_lock) the number of
connections, and if there are blocking locks. Have also a look at some
performance counters.
You'll need to search for hints. Info you gave is not sufficient to give a
clue of what could be the problem.
Good luck
--
Rudi Bruchez
MCDBA