Failed to insert.

Hi!
I am trying to insert into a sql server 2000 enterprise edition database from a .net application. but I keep getting the error server doesnot exist or access denied. I can connect to the server from query analyzer and sql manager. The database is running on windows 2003. The only thing I don't see is the ASPNET sys account not on the login database. I don't know how to add that.

Here is the connection string that I have always used and worked before.

Network Library=DBMSSOCN; Data Source=DEVELOPERS,1433; Initial Catalog=CMLTODB;User ID=sa;Password=password

Thanks,

You are hardcoding a username and password thus the ASPNET user does not need to be in the log in database.
Your connection string looks good. Some things to consider:
- Can you ping DEVELOPERS from your ASP.NET box?
- Is your SQL server in mixed or Windows Auth mode? It needs to be in mixed?
Check this article for a complete list of possible issues that cause this error:
http://support.microsoft.com/default.aspx?scid=kb;en-us;328306
Jason
|||

Hi Jason!
I will check the link you provided, but I even put the application on the same server as the database. It still give me the same error. I changed my connection string to use the local server. It did not help either.

My database authentication is already in mixed mode.

Thanks,

Mostafa

|||

Try this url it has visual walk through, assuming your application is not handling money transactions. Hope this helps.http://duhnetnuke2.net/Default.aspx?tabid=64

Kind regards,

Gift Peddie

Failed to get distributed transaction startet

Hi
I'm trying to get distributed transactions working between 2 machines within
2 different windows domains.
I can't get around following error:
Server: Msg 7391, Level 16, State 1, Line 1
OLE DB provider "SQLNCLI" for linked server <our server> returned message
"No transaction is active.".
The operation could not be performed because OLE DB provider "SQLNCLI" for
linked server <our server> was unable to begin a distributed transaction.
That the environment:
- server A is Win2k3 + SP 1
- server B is Win2k3 + SP 1 (within other windows domain; connection through
VPN)
- both servers running SQL 2005 + SP1
- Distributed Transaction Coordinator service is enabled on both servers
(security set to: 'No authentication required')
- The services are running with 'Log On As' Network Service
- windows firewall is disabled on both servers
- all ports are opened (bidirectional) between both domains
- @.@.servername is set correctly on both servers
That's what I try from server A:
UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
but
begin tran
UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message abov
e
commit tran
When running this query from another ServerC within the same domain of
ServerB everything works fine. So, I guess there's some limitations across
the domains ?!
Do you have any further suggestions ?
Many thanks in advance
MichaelHi Michael
Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
John
"micpauls" wrote:

> Hi
> I'm trying to get distributed transactions working between 2 machines with
in
> 2 different windows domains.
> I can't get around following error:
>
> Server: Msg 7391, Level 16, State 1, Line 1
> OLE DB provider "SQLNCLI" for linked server <our server> returned message
> "No transaction is active.".
> The operation could not be performed because OLE DB provider "SQLNCLI" for
> linked server <our server> was unable to begin a distributed transaction.
> That the environment:
> - server A is Win2k3 + SP 1
> - server B is Win2k3 + SP 1 (within other windows domain; connection throu
gh
> VPN)
> - both servers running SQL 2005 + SP1
> - Distributed Transaction Coordinator service is enabled on both servers
> (security set to: 'No authentication required')
> - The services are running with 'Log On As' Network Service
> - windows firewall is disabled on both servers
> - all ports are opened (bidirectional) between both domains
> - @.@.servername is set correctly on both servers
>
> That's what I try from server A:
> UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- success
> but
> begin tran
> UPDATE ServerB.DB.dbo.T1 SET MyColumn = 1 -- failure, see error message ab
ove
> commit tran
> When running this query from another ServerC within the same domain of
> ServerB everything works fine. So, I guess there's some limitations across
> the domains ?!
> Do you have any further suggestions ?
>
> Many thanks in advance
> Michael|||Yes, I already tried, but without success. I still get the same error messag
e.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Michael
> Have you tried the BEGIN DISTRIBUTED TRANSACTION syntax?
> John
> "micpauls" wrote:
>|||Hi
Have you checked out http://support.microsoft.com/kb/839279? I would also
try changing the service to be a domain account.
John
"micpauls" wrote:
[vbcol=seagreen]
> Yes, I already tried, but without success. I still get the same error mess
age.
> "John Bell" wrote:
>|||Hi
windows firewall is completely disabled on both servers.
Security settings for MSDTC were already set apropriate to
"http://support.microsoft.com/kb/839279?" on both servers.
I added "NT AUTHORITY\NetworkService" to admin group on serverB, but I can't
find this account on serverA because this is a domain controller. How can I
do this ? Should I try changing the account for "Distributed Transaction
Coordinator" service to some other domain admin account ?
Michael
"John Bell" wrote:
[vbcol=seagreen]
> Hi
> Have you checked out http://support.microsoft.com/kb/839279? I would also
> try changing the service to be a domain account.
> John
> "micpauls" wrote:
>|||micpauls wrote:
> Hi
> windows firewall is completely disabled on both servers.
> Security settings for MSDTC were already set apropriate to
> "http://support.microsoft.com/kb/839279?" on both servers.
> I added "NT AUTHORITY\NetworkService" to admin group on serverB, but I can
't
> find this account on serverA because this is a domain controller. How can
I
> do this ? Should I try changing the account for "Distributed Transaction
> Coordinator" service to some other domain admin account ?
>
You shouldn't run SQL under the NetworkService context. Create a real
domain user, i.e. DOMAIN\SQLExecAcct, and configure the SQL services to
use that domain user.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thanks, for your help. I already run SQL services using a separate domain
(admin) account. Yesterday the problem has been resolved by adding
appropriate hostname/IP address entries to the 'hosts' file and the 'lmhosts
'
file, to enable network transport services to resolve the 2 server hostnames
accross the 2 different domains. Adding linked servers by IP address wasn't
sufficient. RPC in/out options within the linked servers properties must by
enabled.
"Tracy McKibben" wrote:

> micpauls wrote:
> You shouldn't run SQL under the NetworkService context. Create a real
> domain user, i.e. DOMAIN\SQLExecAcct, and configure the SQL services to
> use that domain user.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>sql

Failed insert query

Hi

I'm trying to port some data from one database table to another
database table on the same server.

This is the query I am using:

-->
INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents
WHERE Exists (SELECT ContactID FROM newdatabase.dbo.contacts)
<---

This is the error I'm getting:

-->
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'FK_ContactEvents_Contacts'. The conflict occurred in database
'newdatabase', table 'Contacts', column 'ContactID'.
The statement has been terminated.
<---

There is a relationship between the contacts table (Primary key
ContactID) and the contactsevent (foreign key ContactID) table. I guess
the error being flagged up here is that some contacts don't exist in
the new database, therefore referential intergretory won't allow it
being copied. I thought I could get around this using:
"WHERE Exists (SELECT ContactID FROM newdatabase.dbo.contacts)"
Note I've also tried:
"WHERE Exists (SELECT * FROM newdatabase.dbo.contacts)"

What am I doing wrong?

Many Thanks!

AlexYour subquery always evaluates to TRUE, so it's not filtering the data
- you need to link it to the outer table (see "Correlated Subqueries"
in Books Online):

...
from olddatabase.dbo.contactevents o
WHERE Exists (
SELECT *
FROM newdatabase.dbo.contacts n
where o.ContactID = n.ContactID
)

Simon|||Simon thanks...!

I checked books online, thanks for the reference.
Looking at the conditional statement you gave me:

WHERE Exists (
SELECT *
FROM newdatabase.dbo.contacts n
where o.ContactID = n.ContactID
)

... could you please clarify what 'o'' and 'n'' are?

I've tried now tried the below statement, which makes for sense to me
after your advice, unfortunately I still get the same error:

WHERE EXISTS
(SELECT ContactID FROM newdatabase.dbo.contacts
WHERE ContactID IN (SELECT ContactID FROM olddatabase.dbo.contacts))

I guess I still haven't got the hang of it!

Cheers!

Alex|||You query return all rows from olddatabase.dbo.contactevents, without
checking for the existance in newdatabase.dbo.contacts.

It aslo not advisible to use exists as it is ineffecient.

you can try this query:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005
00:00',ISNULL(Notes,'')
from olddatabase.dbo.contactevents
INNER JOIN newdatabase.dbo.contacts
ON newdatabase.dbo.contacts.ContactID =
olddatabase.dbo.contactevents.ContactID

please let me know if u have any questions

best Regards,
Chandra
http://www.SQLResource.com/
http://chanduas.blogspot.com/
------------

*** Sent via Developersdex http://www.developersdex.com ***|||o and n are table aliases - instead of typing out the full table name
every time, it's easier to use an alias, and it often makes the code
more readable (see "Using Table Aliases" in Books Online). As for your
query, try this:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents o
WHERE EXISTS (
SELECT *
FROM newdatabase.dbo.contacts n
WHERE o.ContactID = n.ContactID
)

Or you may find this clearer:

INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
ContactID, DateEntered, EventDate, Description)
select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005 00:00',
ISNULL(Notes,'')
from olddatabase.dbo.contactevents
WHERE ContactID IN (
SELECT ContactID
FROM newdatabase.dbo.contacts
)

I suspect that your query is mixing these two forms.

Simon|||Simon and Chandra

Thank you very much for your help!

Alex|||On Thu, 04 Aug 2005 12:17:11 GMT, Chandra wrote:

>You query return all rows from olddatabase.dbo.contactevents, without
>checking for the existance in newdatabase.dbo.contacts.
>It aslo not advisible to use exists as it is ineffecient.

Hi Chandra,

EXISTS inefficient? This is the first time that I hear that. In fact, I
always hear the opposite that it is very efficient since it'll stop
searching as soon as the first match is found, whereas other techniques
have to process all the data.

Can you post a repro script (or point me to one somewhere on the web)
that shows how EXISTS is less efficient than any of it's equivalents?

>you can try this query:
>INSERT into newdatabase.dbo.contactevents (EventTypeID, UserID,
>ContactID, DateEntered, EventDate, Description)
>select '20','1', ContactID, '1/1/2005 00:00', '1/1/2005
>00:00',ISNULL(Notes,'')
>from olddatabase.dbo.contactevents
>INNER JOIN newdatabase.dbo.contacts
>ON newdatabase.dbo.contacts.ContactID =
>olddatabase.dbo.contactevents.ContactID

It's highly probably that this query will work, but you can't be totally
sure. As the OP didn't post the DDL for the table, you can't be totally
sure that the join to newdatabase.dbo.contacts will never result in more
than one row. And if it ever does, then your query will either insert
duplicates in newdatabase.dbo.contactevents, or (if a key is properly
declared) result in a primary key violation.

I'd definitely use EXISTS in this case. And I'd change the dates to an
unambiguous format ('20050101' or '2005-01-01T00:00:00').

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Fact Table Many-to-Many Relationship

Hi

I've just started using SSAS2005 in a health application.

My main Fact table is a Patient Details table with Patient ID as primary key. The cube built around it uses age and gender as dimensions, with median age and Male-Female ratio as some of the measures.

Now in the same Data Source View I've added a Treatment Fact table with multiple treatment rows per Patient ID, each row containing treatment details and the name of the hospital which provided the treatment.

How do I create a dimension or link between these two tables so that in the same cube I can summarize, by hospital, both the number of treatments (and other treatment-related measures and dimensions) as well as the characteristics (age/gender) of patients treated in each hospital? Sounds like a many-to-many relationship but I don't know how to implement it in this case.

Thanks in advance.

You could set up a Patient measure group with a PatientCount measure and a Treatment measure group with a TreatmentCount measure. Age and Gender dimensions are directly related to the Patient measure group, and Hospital to the Treatment measure group. If you then create a PatientDetails fact dimension for the Patient measure group, which is also directly related to the Treatment measure group, the Hospital dimension can have a Many-to-Many relation with the Patient measure group, via the PatientDetails intermediate dimension. You can refer to the Many-to-Many relation of SalesReason to Internet Sales in Adventure Works, for a similar example.|||

Thanks Deepak, that worked. Much appreciated.

Extremely slow median measures

Hi

I'm having a problem with extremely slow median measures.

I've created a named set of all record IDs and wrote the measure as Median( [All Records], [Measures].[Age] ). When I drop a dimension into one of the axes, it takes a very long time to calculate the median even at the top level of the hierarchy, and I suspect it's computing the median for all the members of that dimension, even before I've drilled down into them.

Anyone know a better method for this?

Your formula computes Median for all records always, regardless of the selection in Records dimension.|||

Sorry Mosha, I don't quite follow. I need this to be a generic measure that will return the median of any cell in the client browser. Since median cannot be preaggregated, I thought the only way to do this was to take the median of the set of all records in the current cell. The other option I had explored went something like this:

Median (

{ ( Axis(0)(0)( Axis(0)(0).Count - 1 ).Dimension.CurrentMember.All, [All Records].[ ID ].[ ID ] ) },

[Measures].[Age]

)

But this didn't work at all.

|||Sorry, this time I don't quite follow. What exactly do you mean by the following: "I need this to be a generic measure that will return the median of any cell in the client browser". Median of what ? Perha[s you could illustrate with couple of examples.|||

My mistake... Should have said median of a measure (e.g. age) within any cell in the spreadsheet. For example, if I have a 2 x 2 table with Male and Female as columns and marital status Single and Married as rows, I'd be showing the median age in each of the 4 cells. Other times, users would be interested in the median age for other combinations of factors, say Gender and Cancer, or Cancer and Socioeconomic Status, but they shouldn't have to select a different median measure for each combination. Sort of like a percentage/proportion against any dimension selected on the row/column axis, as discussed in the following post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720160&SiteID=1

|||

If you are guaranteed to always have two axes, then something like that may work

Median(CrossJoin(Axis(0), Axis(1)), Measures.[Age])

|||

Thanks Mosha, but no luck with that one either. There's probably no other way around this; that is, other than using the Fact Table primary key (Record ID) to select the set of all individual age values.

|||

Then I again don't understand your requirements :( Based on what you wrote before:

> For example, if I have a 2 x 2 table with Male and Female as columns and marital status Single and Married as rows, I'd be showing the median age in each of the 4 cells.

The formula that I wrote computes then median of these 4 cells and places it into each one of these 4 cells. I have verified it with AdventureWorks which has Gender and Marital Status attributes...

Extremely Slow connection time

Hi
I have recently reinstalled XP Professional as well as the MSSQL client tool
s.
Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so f
or quite a while, no changes where done to the server while I reinstalled XP
on my PC.
Now my connection to the SQL server is extremely sluggish, e.g. connecting v
ia the Query Analyzer takes up to 2 minutes, and so do any other subsequent
actions, for example opening the list of databases, tables or stored procedu
res on the object browser.
All my queries take ages to get going even for the simplest queries.
Previously the response was very reasonable, as are the other developers PCs
in the company, with similar set up.
I have tried using both forms of security but have no joy either way. I have
used the IP address – no go.
If I ping the NetBIOS name of the server the response is instantaneous.
Please help!!Did you remember to install SQL Server 2000 SP3a after the reinstall?
Without it, you are vulnerable to the Slammer.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herbert Schroder" <herberts@.mweb.co.za> wrote in message
news:9FF54BCC-2F28-4F5A-9D6A-06A4D4B1CA41@.microsoft.com...
> Hi
> I have recently reinstalled XP Professional as well as the MSSQL client
tools.
> Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so
for quite a while, no changes where done to the server while I reinstalled
XP on my PC.
> Now my connection to the SQL server is extremely sluggish, e.g. connecting
via the Query Analyzer takes up to 2 minutes, and so do any other subsequent
actions, for example opening the list of databases, tables or stored
procedures on the object browser.
> All my queries take ages to get going even for the simplest queries.
> Previously the response was very reasonable, as are the other developers
PCs in the company, with similar set up.
> I have tried using both forms of security but have no joy either way. I
have used the IP address - no go.
> If I ping the NetBIOS name of the server the response is instantaneous.
>
> Please help!!
>

Extremely Slow connection time

Hi
I have recently reinstalled XP Professional as well as the MSSQL client tools.
Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so for quite a while, no changes where done to the server while I reinstalled XP on my PC.
Now my connection to the SQL server is extremely sluggish, e.g. connecting via the Query Analyzer takes up to 2 minutes, and so do any other subsequent actions, for example opening the list of databases, tables or stored procedures on the object browser.
All my queries take ages to get going even for the simplest queries.
Previously the response was very reasonable, as are the other developers PCs in the company, with similar set up.
I have tried using both forms of security but have no joy either way. I have used the IP address – no go.
If I ping the NetBIOS name of the server the response is instantaneous.
Please help!!
Did you remember to install SQL Server 2000 SP3a after the reinstall?
Without it, you are vulnerable to the Slammer.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herbert Schroder" <herberts@.mweb.co.za> wrote in message
news:9FF54BCC-2F28-4F5A-9D6A-06A4D4B1CA41@.microsoft.com...
> Hi
> I have recently reinstalled XP Professional as well as the MSSQL client
tools.
> Our SQL 2000 server is running Win2K with 512MB RAM, and has been doing so
for quite a while, no changes where done to the server while I reinstalled
XP on my PC.
> Now my connection to the SQL server is extremely sluggish, e.g. connecting
via the Query Analyzer takes up to 2 minutes, and so do any other subsequent
actions, for example opening the list of databases, tables or stored
procedures on the object browser.
> All my queries take ages to get going even for the simplest queries.
> Previously the response was very reasonable, as are the other developers
PCs in the company, with similar set up.
> I have tried using both forms of security but have no joy either way. I
have used the IP address - no go.
> If I ping the NetBIOS name of the server the response is instantaneous.
>
> Please help!!
>

extracting dates

hi
I want to select all the dates between 2 dates.ie if i'm passing 17th June and 19th june then it shud give 17,18 and 19.
Like wise if i'm giving 28th june and 3rd july it shud return 28,29,30,1,2,3.
Pls help me.
Thnx in advance
Nebubetween '20040101' and ''20040102