Problem with linked server

Here's the situation: My production environment has two SQL Servers 2000,
call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
On S1, I have created a linked server for S2. Thus, on S1, I can write:
SELECT * FROM S2.myDB.dbo.myTable
and everything is happy.
My development environment is slightly different. I have a single machine
running a single instance of SQL Server 2000 (acually, MSDE). I have made
local copies of the tables from both S1 and S2 in my local database. In
order to be able to copy, without modification, the stored procedures I
write on my development machine, I need to be able to access the tables in
my database as either:
myDB.dbo.myTable
OR
S2.myDB.dbo.myTable
To this end, I created a linked database on my development machine, but
instead of selecting type SQL Server (which forces the linked server name
and the hostname to be the same), I selected Microsoft OLE DB provider for
SQL Server (which allows specifying different hostname and data source
names). I specified my hostname as the data source and named the linked
server S2. SQL server dutifully made the linked server. Note that I have
confirmed the creation of the linked server by running sp_helpserver, which
does show both the local server (identified by the machine name) and the
linked server (identified by the assigned name) [but see more info below].
Despite this seeming success, I have been unable to do anything with this
linked server. Under the security tab, I selected 'Be made using this
security context', and entered 'sa' and its password. I then ran the query:
SELECT * FROM S2.someDB.dbo.myTable
and received: SQL Server does not exist or access denied.
Some additional information:
1- On the development machine, sqlservr runs under the SYSTEM context
2- Looking in the table master.dbo.sysservers, I saw the following
srvname datasource srvnetname
S1 S1 S1
S2 S2 S2
when running the query, it takes a while to respond, thus I am thinking that
it is searching the net for a server named S2 instead of using itself as the
datasource. If this is so, what can I do differently to resolve this
problem?
Gerry Roston
Pair of Docs Consulting
gerry@.pairofdocs.netGerald,

quote:

---

>what can I do differently to resolve this problem?

---

Isn't a second instance for the dev box the most logical, desirable
solution?
"Gerald Roston" <groston@.annarbormachine.com> wrote in message
news:Ovk9A606DHA.3420@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Here's the situation: My production environment has two SQL Servers 2000,
> call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
> On S1, I have created a linked server for S2. Thus, on S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
> and everything is happy.
> My development environment is slightly different. I have a single machine
> running a single instance of SQL Server 2000 (acually, MSDE). I have made
> local copies of the tables from both S1 and S2 in my local database. In
> order to be able to copy, without modification, the stored procedures I
> write on my development machine, I need to be able to access the tables in
> my database as either:
> myDB.dbo.myTable
> OR
> S2.myDB.dbo.myTable
> To this end, I created a linked database on my development machine, but
> instead of selecting type SQL Server (which forces the linked server name
> and the hostname to be the same), I selected Microsoft OLE DB provider for
> SQL Server (which allows specifying different hostname and data source
> names). I specified my hostname as the data source and named the linked
> server S2. SQL server dutifully made the linked server. Note that I have
> confirmed the creation of the linked server by running sp_helpserver,

---

which

quote:

---

> does show both the local server (identified by the machine name) and the
> linked server (identified by the assigned name) [but see more info below].
> Despite this seeming success, I have been unable to do anything with this
> linked server. Under the security tab, I selected 'Be made using this
> security context', and entered 'sa' and its password. I then ran the

---

query:

quote:

---

> SELECT * FROM S2.someDB.dbo.myTable
> and received: SQL Server does not exist or access denied.
> Some additional information:
> 1- On the development machine, sqlservr runs under the SYSTEM context
> 2- Looking in the table master.dbo.sysservers, I saw the following
> srvname datasource srvnetname
> S1 S1 S1
> S2 S2 S2
> when running the query, it takes a while to respond, thus I am thinking

---

that

quote:

---

> it is searching the net for a server named S2 instead of using itself as

---

the

quote:

---

> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>

---

|||Problem resolved by using sp_addlinkedserver instead of Enterprise Manager.
I suspect that the problem is due to a bug in the documentation and a bug in
Enterprise Manager. Reviewing the document shows that the string 'SQL
Server' can be used as the product_name along with SQLOLEDB as the
provider_name. Trying this with sp_addlinkedserver yields the error message:
You cannot specify a provider or any properties for product 'SQL Server'.
but doing the same from Enterprise Manager results in success, even though
the entry in master.dbo.sysservers is incorrect.
"Gerald Roston" <groston@.annarbormachine.com> wrote in message
news:Ovk9A606DHA.3420@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Here's the situation: My production environment has two SQL Servers 2000,
> call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
> On S1, I have created a linked server for S2. Thus, on S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
> and everything is happy.
> My development environment is slightly different. I have a single machine
> running a single instance of SQL Server 2000 (acually, MSDE). I have made
> local copies of the tables from both S1 and S2 in my local database. In
> order to be able to copy, without modification, the stored procedures I
> write on my development machine, I need to be able to access the tables in
> my database as either:
> myDB.dbo.myTable
> OR
> S2.myDB.dbo.myTable
> To this end, I created a linked database on my development machine, but
> instead of selecting type SQL Server (which forces the linked server name
> and the hostname to be the same), I selected Microsoft OLE DB provider for
> SQL Server (which allows specifying different hostname and data source
> names). I specified my hostname as the data source and named the linked
> server S2. SQL server dutifully made the linked server. Note that I have
> confirmed the creation of the linked server by running sp_helpserver,

---

which

quote:

---

> does show both the local server (identified by the machine name) and the
> linked server (identified by the assigned name) [but see more info below].
> Despite this seeming success, I have been unable to do anything with this
> linked server. Under the security tab, I selected 'Be made using this
> security context', and entered 'sa' and its password. I then ran the

---

query:

quote:

---

> SELECT * FROM S2.someDB.dbo.myTable
> and received: SQL Server does not exist or access denied.
> Some additional information:
> 1- On the development machine, sqlservr runs under the SYSTEM context
> 2- Looking in the table master.dbo.sysservers, I saw the following
> srvname datasource srvnetname
> S1 S1 S1
> S2 S2 S2
> when running the query, it takes a while to respond, thus I am thinking

---

that

quote:

---

> it is searching the net for a server named S2 instead of using itself as

---

the

quote:

---

> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>

---

Problem with linked server

Here's the situation: My production environment has two SQL Servers 2000,
call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
On S1, I have created a linked server for S2. Thus, on S1, I can write:
SELECT * FROM S2.myDB.dbo.myTable
and everything is happy.
My development environment is slightly different. I have a single machine
running a single instance of SQL Server 2000 (acually, MSDE). I have made
local copies of the tables from both S1 and S2 in my local database. In
order to be able to copy, without modification, the stored procedures I
write on my development machine, I need to be able to access the tables in
my database as either:
myDB.dbo.myTable
OR
S2.myDB.dbo.myTable
To this end, I created a linked database on my development machine, but
instead of selecting type SQL Server (which forces the linked server name
and the hostname to be the same), I selected Microsoft OLE DB provider for
SQL Server (which allows specifying different hostname and data source
names). I specified my hostname as the data source and named the linked
server S2. SQL server dutifully made the linked server. Note that I have
confirmed the creation of the linked server by running sp_helpserver, which
does show both the local server (identified by the machine name) and the
linked server (identified by the assigned name) [but see more info below].
Despite this seeming success, I have been unable to do anything with this
linked server. Under the security tab, I selected 'Be made using this
security context', and entered 'sa' and its password. I then ran the query:
SELECT * FROM S2.someDB.dbo.myTable
and received: SQL Server does not exist or access denied.
Some additional information:
1- On the development machine, sqlservr runs under the SYSTEM context
2- Looking in the table master.dbo.sysservers, I saw the following
srvname datasource srvnetname
S1 S1 S1
S2 S2 S2
when running the query, it takes a while to respond, thus I am thinking that
it is searching the net for a server named S2 instead of using itself as the
datasource. If this is so, what can I do differently to resolve this
problem?
--
Gerry Roston
Pair of Docs Consulting
gerry@.pairofdocs.netYou could try adding a host entry for the linked server
with the same ip address as the development server using a
different name.
i.e.
192.168.1.1 development
192.168.1.1 Dev_Link
This will allow you to add the linked server as another
sql server.
Shane
>--Original Message--
>Here's the situation: My production environment has two
SQL Servers 2000,
>call them S1 and S2. S1 also hosts IIS and my ASP.NET
application.
>On S1, I have created a linked server for S2. Thus, on
S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
>and everything is happy.
>My development environment is slightly different. I have
a single machine
>running a single instance of SQL Server 2000 (acually,
MSDE). I have made
>local copies of the tables from both S1 and S2 in my
local database. In
>order to be able to copy, without modification, the
stored procedures I
>write on my development machine, I need to be able to
access the tables in
>my database as either:
>myDB.dbo.myTable
>OR
>S2.myDB.dbo.myTable
>To this end, I created a linked database on my
development machine, but
>instead of selecting type SQL Server (which forces the
linked server name
>and the hostname to be the same), I selected Microsoft
OLE DB provider for
>SQL Server (which allows specifying different hostname
and data source
>names). I specified my hostname as the data source and
named the linked
>server S2. SQL server dutifully made the linked server.
Note that I have
>confirmed the creation of the linked server by running
sp_helpserver, which
>does show both the local server (identified by the
machine name) and the
>linked server (identified by the assigned name) [but see
more info below].
>Despite this seeming success, I have been unable to do
anything with this
>linked server. Under the security tab, I selected 'Be
made using this
>security context', and entered 'sa' and its password. I
then ran the query:
>SELECT * FROM S2.someDB.dbo.myTable
>and received: SQL Server does not exist or access denied.
>Some additional information:
>1- On the development machine, sqlservr runs under the
SYSTEM context
>2- Looking in the table master.dbo.sysservers, I saw the
following
>srvname datasource srvnetname
>S1 S1 S1
>S2 S2 S2
>when running the query, it takes a while to respond, thus
I am thinking that
>it is searching the net for a server named S2 instead of
using itself as the
>datasource. If this is so, what can I do differently to
resolve this
>problem?
>--
>Gerry Roston
>Pair of Docs Consulting
>gerry@.pairofdocs.net
>
>.
>|||Gerald,
>what can I do differently to resolve this problem?
Isn't a second instance for the dev box the most logical, desirable
solution?
"Gerald Roston" <groston@.annarbormachine.com> wrote in message
news:Ovk9A606DHA.3420@.TK2MSFTNGP11.phx.gbl...
> Here's the situation: My production environment has two SQL Servers 2000,
> call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
> On S1, I have created a linked server for S2. Thus, on S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
> and everything is happy.
> My development environment is slightly different. I have a single machine
> running a single instance of SQL Server 2000 (acually, MSDE). I have made
> local copies of the tables from both S1 and S2 in my local database. In
> order to be able to copy, without modification, the stored procedures I
> write on my development machine, I need to be able to access the tables in
> my database as either:
> myDB.dbo.myTable
> OR
> S2.myDB.dbo.myTable
> To this end, I created a linked database on my development machine, but
> instead of selecting type SQL Server (which forces the linked server name
> and the hostname to be the same), I selected Microsoft OLE DB provider for
> SQL Server (which allows specifying different hostname and data source
> names). I specified my hostname as the data source and named the linked
> server S2. SQL server dutifully made the linked server. Note that I have
> confirmed the creation of the linked server by running sp_helpserver,
which
> does show both the local server (identified by the machine name) and the
> linked server (identified by the assigned name) [but see more info below].
> Despite this seeming success, I have been unable to do anything with this
> linked server. Under the security tab, I selected 'Be made using this
> security context', and entered 'sa' and its password. I then ran the
query:
> SELECT * FROM S2.someDB.dbo.myTable
> and received: SQL Server does not exist or access denied.
> Some additional information:
> 1- On the development machine, sqlservr runs under the SYSTEM context
> 2- Looking in the table master.dbo.sysservers, I saw the following
> srvname datasource srvnetname
> S1 S1 S1
> S2 S2 S2
> when running the query, it takes a while to respond, thus I am thinking
that
> it is searching the net for a server named S2 instead of using itself as
the
> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>|||Problem resolved by using sp_addlinkedserver instead of Enterprise Manager.
I suspect that the problem is due to a bug in the documentation and a bug in
Enterprise Manager. Reviewing the document shows that the string 'SQL
Server' can be used as the product_name along with SQLOLEDB as the
provider_name. Trying this with sp_addlinkedserver yields the error message:
You cannot specify a provider or any properties for product 'SQL Server'.
but doing the same from Enterprise Manager results in success, even though
the entry in master.dbo.sysservers is incorrect.
"Gerald Roston" <groston@.annarbormachine.com> wrote in message
news:Ovk9A606DHA.3420@.TK2MSFTNGP11.phx.gbl...
> Here's the situation: My production environment has two SQL Servers 2000,
> call them S1 and S2. S1 also hosts IIS and my ASP.NET application.
> On S1, I have created a linked server for S2. Thus, on S1, I can write:
> SELECT * FROM S2.myDB.dbo.myTable
> and everything is happy.
> My development environment is slightly different. I have a single machine
> running a single instance of SQL Server 2000 (acually, MSDE). I have made
> local copies of the tables from both S1 and S2 in my local database. In
> order to be able to copy, without modification, the stored procedures I
> write on my development machine, I need to be able to access the tables in
> my database as either:
> myDB.dbo.myTable
> OR
> S2.myDB.dbo.myTable
> To this end, I created a linked database on my development machine, but
> instead of selecting type SQL Server (which forces the linked server name
> and the hostname to be the same), I selected Microsoft OLE DB provider for
> SQL Server (which allows specifying different hostname and data source
> names). I specified my hostname as the data source and named the linked
> server S2. SQL server dutifully made the linked server. Note that I have
> confirmed the creation of the linked server by running sp_helpserver,
which
> does show both the local server (identified by the machine name) and the
> linked server (identified by the assigned name) [but see more info below].
> Despite this seeming success, I have been unable to do anything with this
> linked server. Under the security tab, I selected 'Be made using this
> security context', and entered 'sa' and its password. I then ran the
query:
> SELECT * FROM S2.someDB.dbo.myTable
> and received: SQL Server does not exist or access denied.
> Some additional information:
> 1- On the development machine, sqlservr runs under the SYSTEM context
> 2- Looking in the table master.dbo.sysservers, I saw the following
> srvname datasource srvnetname
> S1 S1 S1
> S2 S2 S2
> when running the query, it takes a while to respond, thus I am thinking
that
> it is searching the net for a server named S2 instead of using itself as
the
> datasource. If this is so, what can I do differently to resolve this
> problem?
> --
> Gerry Roston
> Pair of Docs Consulting
> gerry@.pairofdocs.net
>

Problem with keeptogether

In a report I have a table with 3 detail lines. The property 'keeptogether'
is set to true. However, if I call the report in IE and then export it to
pdf, the detail region can be splitted: The first detail line is at the end
of a page and the next 2 lines (of the same record) are on the next page. I
want that the 3 lines come together on the same page (of course with the
exception that the 3 lines together are longer than one page). How must this
be solved?On Sep 27, 3:20 am, "Lorenz Ingold" <l.ing...@.winvs.ch> wrote:
> In a report I have a table with 3 detail lines. The property 'keeptogether'
> is set to true. However, if I call the report in IE and then export it to
> pdf, the detail region can be splitted: The first detail line is at the end
> of a page and the next 2 lines (of the same record) are on the next page. I
> want that the 3 lines come together on the same page (of course with the
> exception that the 3 lines together are longer than one page). How must this
> be solved?
You might need to select 'Fit table on one page if possible' as part
of the table's Properties (if you haven't already). Also, you might
try putting the table control into a rectangle. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultantsql

problem with huge amount of data

Hi,

I've an application, lets call it simply "A", which creates in a Microsoft Sql Database two huge tables.
Lets call them "table1" and "table2"
It safes really much data into this tables.

After application "A" has finished another application is executed which deletes this two tables.

Then application "A" is started again and it will create this two tables again, but the amount of data becomes bigger.
It can only proceed if the tables were deleted completely before and the database is empty.

This is the procedure which I repeat very often, but everytime the amount of data becomes bigger (table1 and table2 becomes bigger).

A couple if times it works fine, but once it seems data becomes too big and application "A" fails.

Mostlikely because the data wasnt removed correctly / completely.

This is my code of deleting the two tables, maybee there is something I have to change:

</p><p> try
{
SqlConnectionStringBuilder builder =
new SqlConnectionStringBuilder("Server=mycomputer\dbname;Integrated Security=SSPI;" +
"Initial Catalog=testing");

builder["Server"] = "(local)\dbname";
builder["Connect Timeout"] = 10;
builder["Trusted_Connection"] = true;
builder["Initial Catalog"] = ((ComponentConfiguration)this.componentConfig).Persistency.DatabaseName;

SqlConnection sqlconnection = new SqlConnection();
sqlconnection.ConnectionString = builder.ConnectionString;
sqlconnection.Open();
SqlCommand cmd1 = new SqlCommand("DROP TABLE table1"); // TO Do delete all tables
SqlCommand cmd2 = new SqlCommand("DROP TABLE table2"); // TO Do delete all tables

cmd1.Connection = sqlconnection;
cmd2.Connection = sqlconnection;

cmd1.ExecuteNonQuery();
Thread.Sleep(7000);
cmd2.ExecuteNonQuery();
Thread.Sleep(7000);
sqlconnection.Close();
Thread.Sleep(3000);
}

catch { }</p><p> </p><p>

Thanks for help!

mulata

Rather than make 2 trips to delete the 2 tables, you could write a stored proc and delete the tables. I dont know what you mean by "huge" and "big" tables. It will be nice if you can provide some metrics as in the number of rows approximately - few hundred million rows? sample code:

CREATE PROC dbo.DeleteTablesAsBEGINSET NOCOUNT ON--Check if table existsIFEXISTS (SELECT *fromsysobjectsWHERE name ='table1'and xtype ='U')DROP TABLE table1--Check if table existsIFEXISTS (SELECT *fromsysobjectsWHERE name ='table2'and xtype ='U')DROP TABLE table2SET NOCOUNT OFFEND

|||

ok so what I have to do is:

clear a database completely, so that it is empty and recent.
as my programm only creates these two tables, table1 and table2 I can also
delete this two tables.

but its important that it is done fast and reliable.
and it must be done from a .NET program, because the name of the database and server is stored in the .NET application

table1 is small, only 1 row und 0,008 MB.
table2 has about 200000 - 300000 rows and a data space of about 33 GB

thanks,

mulata

|||

Then you would need to make 2 separate connections, each time open the connection, drop the table, close the connetion, in succession.

Would you have only 2 tables or is there possibility of more objects being created?

|||

well at the moment only this two tables.

what is the advantage of opening an own connection for each drop command?

how long takes the execution of a command which deletes a table with about 300000 rows and 33 GB data?

|||

Deleting 300,000 rows should not take long. Generally its not advised to have multiple connections open at the same time. If you have multiple users connecting at the same time your resource pool manager will be busy managing the connection pool. Also, if you have 2 connections, you need to create 2 connection objects. If you use single connection, you can use the same connection object which is less overhead on the framework.

|||

Instead of dropping and recreating the tables as was suggested, I would truncate the tables. This way your structures are left intact and since truncation is a non-logged operation it is lightning fast.

Problem with Extended Stored Procedure calling an WebService...

Hi all!
I have a problem with a Extended Stored Procedure that calls a
WebService...I'm using SOAP to call the WebService and I'm using an
TokenManager that I have written myself...I have WSE 2.0 installed...
It works fine when I'm calling it from my PC, but when I call it from the
SQL test server which is a Windows 2003 server with SQL Server 2000
installed.
The message we get when run the Extended Stored Procedure is.
ODBC: Msg 0, Level 16, State 1
Cannot load the DLL XpMQSQL.DLL, or one of the DLLs it references. Reason:
126(The specified module could not be found.).
Does anyone know what I'm missing? We tried to install WSE 2.0 runtime
version but that didn't help...
Regards,
Tommy Selggren
Mandator Sverige AB
http://www.mandator.comUse DEPENDS.EXE to figure out which file(s) is/are missing. Download
DEPENS.EXE from http://www.dependencywalker.com/ and point it at you DLL
which hosts the XP
GertD@.SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright SQLDev.Net 1991-2005 All rights reserved.
"Tommy Selggren" <tommy.selggren@.telia.com> wrote in message
news:e$pRK3kGFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Hi all!
> I have a problem with a Extended Stored Procedure that calls a
> WebService...I'm using SOAP to call the WebService and I'm using an
> TokenManager that I have written myself...I have WSE 2.0 installed...
> It works fine when I'm calling it from my PC, but when I call it from the
> SQL test server which is a Windows 2003 server with SQL Server 2000
> installed.
> The message we get when run the Extended Stored Procedure is.
> ODBC: Msg 0, Level 16, State 1
> Cannot load the DLL XpMQSQL.DLL, or one of the DLLs it references. Reason:
> 126(The specified module could not be found.).
> Does anyone know what I'm missing? We tried to install WSE 2.0 runtime
> version but that didn't help...
> Regards,
> Tommy Selggren
> Mandator Sverige AB
> http://www.mandator.com
>
>|||Many thanks!
"Gert E.R. Drapers" <GertD@.SQLDevNet> wrote in message
news:eT2QgflGFHA.3092@.tk2msftngp13.phx.gbl...
> Use DEPENDS.EXE to figure out which file(s) is/are missing. Download
> DEPENS.EXE from http://www.dependencywalker.com/ and point it at you DLL
> which hosts the XP
> GertD@.SQLDev.Net
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use.
> Copyright SQLDev.Net 1991-2005 All rights reserved.
> "Tommy Selggren" <tommy.selggren@.telia.com> wrote in message
> news:e$pRK3kGFHA.2136@.TK2MSFTNGP14.phx.gbl...
the
Reason:
>

problem with executing sp_addrolemember...

When I login localy (computer with sql server) I can call procedure sp_addrolemember but when I am loged in remotely (from client computer)and try to call the same procedure I get this message: 'User does not have permission to performe this action'.

hi

executing the procedure requires membership to db_owner or db_securityadmin database roles so that has nothing to do with workstation but with principals and thus logins...

verify the login you are connecting with from the remote client is mapped to a database user with enought permissions..

regards

Problem with dead-locks

We are experiencing some problems with deadlocks in our system. We call
Sql server 2k from a .Net application-layer.
The flow is
Create transaction object in .Net
FOR each table in a list (e.g. 11 tables in total)
DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
(done in stored procedure with no cursors)
// END FOR
FOR each table in a list (same list af before, but in opposite sequence)
BULK INSERT MyTable ..
(SQL-statement created in .Net and sent to Sql svr)
// END FOR
transactionsobject.Commit();
We receive data from an external system once every week. The external
system cannot give os delta-information, e.g. only new data, så they send
us their entire data. This is the reason for why we delete all data in
our system and insert them again.
The problem occurs at BULK INSERT, but odly not always. The database in
question is offline for the rest of the system, so nothing takes place on
the server apart from this job. The transaction object is sent to the
server using Microsoft Practices Enterprise Library's data-block to
enable us to roll-back the transaction if one of the statements fail.
We are kind of blank with regards to how to solve this - do you guys have
an idea to a solution?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=http://www.sony.com">evil</a>Please turn on TF-1204 and see what is getting deadlocked.
thanks
--
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns97107DFDEF6A4stocholmdk@.207.46.248.16...
> We are experiencing some problems with deadlocks in our system. We call
> Sql server 2k from a .Net application-layer.
> The flow is
> Create transaction object in .Net
> FOR each table in a list (e.g. 11 tables in total)
> DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key
> (done in stored procedure with no cursors)
> // END FOR
> FOR each table in a list (same list af before, but in opposite sequence)
> BULK INSERT MyTable ..
> (SQL-statement created in .Net and sent to Sql svr)
> // END FOR
> transactionsobject.Commit();
> We receive data from an external system once every week. The external
> system cannot give os delta-information, e.g. only new data, så they send
> us their entire data. This is the reason for why we delete all data in
> our system and insert them again.
> The problem occurs at BULK INSERT, but odly not always. The database in
> question is offline for the rest of the system, so nothing takes place on
> the server apart from this job. The transaction object is sent to the
> server using Microsoft Practices Enterprise Library's data-block to
> enable us to roll-back the transaction if one of the statements fail.
> We are kind of blank with regards to how to solve this - do you guys have
> an idea to a solution?
> Thanks,
> :o)
>
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in news:e2N
$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
It looks interesting - I didn't know of this.
I will look into it,
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
> Please turn on TF-1204 and see what is getting deadlocked.
We have turned Trace (3605, 1205, 1204, -1) on and the results of
this is:
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID (SPID) 86.
2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID) 86.
2005-11-17 20:26:31.03 spid3 --
2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
2005-11-17 20:26:31.03 spid3 Target Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ... verifying cycle
2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3
Deadlock encountered ... Printing deadlock information
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Wait-for graph
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 Node:1
2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1 Mode: IX Flags: 0x0
2005-11-17 20:26:31.03 spid3 Grant List 1::
2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK INSERT Line #: 1
2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT TAB_RENS_TILLADELSE FROM '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_0306_TAB_RENS_TILLADELSE.tab' WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1, KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
', FIELDTERMINATOR = ' ')
2005-11-17 20:26:31.03 spid3 Requested By:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
2005-11-17 20:26:31.03 spid3
2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was found.
2005-11-17 20:26:31.03 spid3 --
The content is also available on
http://dotnet.stocholm.dk/database/trace.zip
Can you help us decipher this?
Thanks,
:o)
--
Jesper Stocholm
http://stocholm.dk
<a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||Here is the feedback from dev. One suggestion is to upgrade to SP4, if not
already done. If the problem still shows up, please contact PSS
"It looks like a self-deadlock - i.e. we're using two incompatible
transactions in the same thread. The cycle looks like:
Spid86-Xact_T1 holds an IX lock.
Spid86-Xact_T2 requests an SCH_M lock which is incompatible with the IX.
Thus spid 86 is waiting for spid 86 which completes a deadlock cycle.
This is a bug, we shouldn't get into these self-deadlocks."
Sunil Agarwal (MSFT]
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jesper Stocholm" <j@.stocholm.invalid> wrote in message
news:Xns9711D30914A23stocholmdk@.207.46.248.16...
> "Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
> news:e2N$D8t6FHA.3760@.TK2MSFTNGP14.phx.gbl:
>> Please turn on TF-1204 and see what is getting deadlocked.
> We have turned Trace (3605, 1205, 1204, -1) on and the results of
> this is:
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 3605, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1204, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON 1205, server process ID
> (SPID) 86.
> 2005-11-17 20:26:29.11 spid86 DBCC TRACEON -1, server process ID (SPID)
> 86.
> 2005-11-17 20:26:31.03 spid3 --
> 2005-11-17 20:26:31.03 spid3 Starting deadlock search 62
> 2005-11-17 20:26:31.03 spid3 Target Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Deadlock cycle was encountered ...
> verifying cycle
> 2005-11-17 20:26:31.03 spid3 Node:1 ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3 Cycle: ResType:LockOwner Stype:'OR'
> Mode: Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0
> Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3
> Deadlock encountered ... Printing deadlock information
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Wait-for graph
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 Node:1
> 2005-11-17 20:26:31.03 spid3 TAB: 6:1410104064 [] CleanCnt:1
> Mode: IX Flags: 0x0
> 2005-11-17 20:26:31.03 spid3 Grant List 1::
> 2005-11-17 20:26:31.03 spid3 Owner:0x4b5eb380 Mode: IX
> Flg:0x0 Ref:1 Life:02000000 SPID:86 ECID:0
> 2005-11-17 20:26:31.03 spid3 SPID: 86 ECID: 0 Statement Type: BULK
> INSERT Line #: 1
> 2005-11-17 20:26:31.03 spid3 Input Buf: Language Event: BULK INSERT
> TAB_RENS_TILLADELSE FROM
> '\\MPDB03\Fileshares\MPReadyToUpload\0000023764_0306_TAB_RENS_TILLADELSE.tab'
> WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'widechar', FIRSTROW = 1,
> KEEPIDENTITY, KEEPNULLS, ROWTERMINATOR = '
> ', FIELDTERMINATOR = ' ')
> 2005-11-17 20:26:31.03 spid3 Requested By:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3 Victim Resource Owner:
> 2005-11-17 20:26:31.03 spid3 ResType:LockOwner Stype:'OR' Mode:
> Schema-Mod SPID:86 ECID:0 Ec:(0x2D8EB570) Value:0x4a2eafe0 Cost:(0/0)
> 2005-11-17 20:26:31.03 spid3
> 2005-11-17 20:26:31.03 spid3 End deadlock search 62 ... a deadlock was
> found.
> 2005-11-17 20:26:31.03 spid3 --
>
> The content is also available on
> http://dotnet.stocholm.dk/database/trace.zip
> Can you help us decipher this?
> Thanks,
> :o)
> --
> Jesper Stocholm
> http://stocholm.dk
> <a href="http://links.10026.com/?link=evil</a>">http://www.sony.com">evil</a>|||"Sunil Agarwal [MSFT]" <sunila@.onlin.microsoft.com> wrote in
news:#4himgJ7FHA.3276@.TK2MSFTNGP15.phx.gbl:
> Here is the feedback from dev. One suggestion is to upgrade to SP4, if
> not already done. If the problem still shows up, please contact PSS
We upgraded the server to SP4 and the problem has not ocurred since ... so
we are keeping our fingers crossed.
:o)
Thanks for your help.
--
Jesper Stocholm
http://stocholm.dk
Findes din kiosk på nettet? Se http://ekiosk.dk

Problem with db connection

My application works properly with development area (VS 2005) , When I created virtual directory and call using browser (IE 7.0) , error occurs exacly the same below

ERROR:

Cannot open user default database. Login failed.
Login failed for user 'CBOZLAGAN\ASPNET'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'CBOZLAGAN\ASPNET'.

Source Error:

Line 4932: this.Adapter.SelectCommand.Parameters[2].Value = ((int)(PROFIL));Line 4933: neuron.KULLANICI_TANIMLARIDataTable dataTable = new neuron.KULLANICI_TANIMLARIDataTable();Line 4934: this.Adapter.Fill(dataTable);Line 4935: return dataTable;Line 4936: }

Connection String is below

"Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\neuron2007_AJAX\\App_Data\\neuron_Data.MDF;Integrated Security=True;User Instance=True";

How can I solve this problem?

Thanks

Did you grant the ASPNET user account permission for the database?

Jeff

|||

Are you publishing to a remote server and are you publishing a copy of the local database that you are connecting to in that connection string?

Did you grant the correct permissions for Integrated Security?

|||

Yes BRCK231\ASPNET has rights below

- db_datareader

- db_datawriter

But connection is done with user BORINTERNAL\CBOZLAGAN for BRCK231\SQLEXPRESS sqlexpress 2005 server

|||

Is the website set up (via IIS) to accept anonymous connections? If not, please give that a try.

Mark

|||

Did you figure it out yet?