Help! Users can't connect to database

Hello -
I am new to Sql Server 2005 and most probably screwed something up. I am
getting an error "The user is not associated with a trusted Sql Server
connection" when accessing my app using a regular user login.
Is NT Authority\System supposed to be both under Security/Users in MyDB and
also under the Server Security\Logins? i.e.
MyServer\DatabasesMyDatabase\Security\Us
ers\NT AUTHORITY\SYSTEM
and
MyServer\Security\Logins\NT AUTHORITY\SYSTEM
Also, is there a quick tutorial somewhere on EXACTLY how to set up users and
their rights?
Any help will be greatly appreciated! I am supposed to have this out
tomorrow --
YIKES!!
--
SandyThis normally when sql server is not able to authentic the connection to a
domain account. This can happen when the user connecting to sql server has
not logged there machine onto the domain prior to connecting to the domain
where SQL Server lives. Or you are trying to connect to SQL Server via an
application that lives on a different machine then the user, and the databas
e
server, so you your command is taking a double hoop to get to sql server.
Have you tried to connect via SQL Server authentication? "Sandy" wrote:

> Hello -
> I am new to Sql Server 2005 and most probably screwed something up. I am
> getting an error "The user is not associated with a trusted Sql Server
> connection" when accessing my app using a regular user login.
> Is NT Authority\System supposed to be both under Security/Users in MyDB an
d
> also under the Server Security\Logins? i.e.
> MyServer\DatabasesMyDatabase\Security\Us
ers\NT AUTHORITY\SYSTEM
> and
> MyServer\Security\Logins\NT AUTHORITY\SYSTEM
> Also, is there a quick tutorial somewhere on EXACTLY how to set up users a
nd
> their rights?
> Any help will be greatly appreciated! I am supposed to have this out
> tomorrow --
> YIKES!!
> --
> Sandy|||Hi Greg -
Thanks so much for your response!
I am at home now and don't have access to the database or Sql Server at work
presently, but from my recollection, I have the NT Authority\System as the
login for both the Server and the database. I think that's wrong and I
should probably delete the NT Authority\System from the database part.
At any rate, the application resides on a server on the K drive. All users
are members of AAA\CustomerService in the Active Directory. Sql Server
resides on Machine DAO401235. All users obviously have their own machines.
I thought this would be simple -- just Windows authentication, then set the
role so that Windows user account can have access to all the stored
procedures and are denied access to the tables. I also thought it would be
something that could be easily done through the GUI and not code.
I have purchased several books since this post (yes, just a couple of hours
ago -- that's how frantic I am) and all of them are oriented towards setting
Sql Server logins, instead of using Windows Authority. I would suspect they
don't even cover the Windows stuff because it's too simplistic. I can't,
however figure it out myself and apparently need it "spoon fed."
Can you give me clues on how to set this up?
Thanks again for your reply and please stay with me on this, if you can!
--
Sandy
"Greg Larsen" wrote:
> This normally when sql server is not able to authentic the connection to a
> domain account. This can happen when the user connecting to sql server h
as
> not logged there machine onto the domain prior to connecting to the domain
> where SQL Server lives. Or you are trying to connect to SQL Server via an
> application that lives on a different machine then the user, and the datab
ase
> server, so you your command is taking a double hoop to get to sql server.
> Have you tried to connect via SQL Server authentication? "Sandy" wrote:
>|||Sandy,
After the user is added to SQL add the user to the database in which
they need access. Check to make sure that the default database for the user
is set to the database for the application. Once given rights to log into
SQL the user will then go to their default database or the database within
the connection string. Authentication fails if the user does not have right
s
within the specified database.
"Sandy" wrote:
> Hi Greg -
> Thanks so much for your response!
> I am at home now and don't have access to the database or Sql Server at wo
rk
> presently, but from my recollection, I have the NT Authority\System as the
> login for both the Server and the database. I think that's wrong and I
> should probably delete the NT Authority\System from the database part.
> At any rate, the application resides on a server on the K drive. All user
s
> are members of AAA\CustomerService in the Active Directory. Sql Server
> resides on Machine DAO401235. All users obviously have their own machines
.
> I thought this would be simple -- just Windows authentication, then set th
e
> role so that Windows user account can have access to all the stored
> procedures and are denied access to the tables. I also thought it would b
e
> something that could be easily done through the GUI and not code.
> I have purchased several books since this post (yes, just a couple of hour
s
> ago -- that's how frantic I am) and all of them are oriented towards setti
ng
> Sql Server logins, instead of using Windows Authority. I would suspect th
ey
> don't even cover the Windows stuff because it's too simplistic. I can't,
> however figure it out myself and apparently need it "spoon fed."
> Can you give me clues on how to set this up?
> Thanks again for your reply and please stay with me on this, if you can!
> --
> Sandy
>
> "Greg Larsen" wrote:
>|||What exactly do you mean by when you say "I have the NT Authority\System as
the login for both the Server and the database"? The "NT Authority\System"
account known as the "Local System" account, or the Local "Administrator"
account on a computer. This account can only be used to access resource
locally on the machine, and not network resources. So basically this is not
an account any user might connect to your database with. The "NT
Authority\System" account might only be used if you where to login to your
sql server machine locally as "Administrator".
In order for your users to connect using windows authentication, you would
need to add the users domain\user account to sql server. Or grant a windows
group in which the users reside to sql server. The users would then also
need to be given permissions to the database for which you want to give them
access.
"Sandy" wrote:
> Hi Greg -
> Thanks so much for your response!
> I am at home now and don't have access to the database or Sql Server at wo
rk
> presently, but from my recollection, I have the NT Authority\System as the
> login for both the Server and the database. I think that's wrong and I
> should probably delete the NT Authority\System from the database part.
> At any rate, the application resides on a server on the K drive. All user
s
> are members of AAA\CustomerService in the Active Directory. Sql Server
> resides on Machine DAO401235. All users obviously have their own machines
.
> I thought this would be simple -- just Windows authentication, then set th
e
> role so that Windows user account can have access to all the stored
> procedures and are denied access to the tables. I also thought it would b
e
> something that could be easily done through the GUI and not code.
> I have purchased several books since this post (yes, just a couple of hour
s
> ago -- that's how frantic I am) and all of them are oriented towards setti
ng
> Sql Server logins, instead of using Windows Authority. I would suspect th
ey
> don't even cover the Windows stuff because it's too simplistic. I can't,
> however figure it out myself and apparently need it "spoon fed."
> Can you give me clues on how to set this up?
> Thanks again for your reply and please stay with me on this, if you can!
> --
> Sandy
>
> "Greg Larsen" wrote:
>|||Thanks for your response, Derekman!

> After the user is added to SQL add the user to the database in which
> they need access.
"After the user is added to SQL" - Do you mean something like:
Create Login [AAA\jcpracti] from Windows;
"add the user to the database in which they need access" - Do you mean:
Use MyDatabase
Create User [AAA\jcpracti]

> Check to make sure that the default database for the user
> is set to the database for the application.
How do I do this?
Also, I am puzzled by the fact that NT Authority\System keeps being put unde
r
MyDatabase\Security\Users\NT AUTHORITY\SYSTEM when I create a new user. I
didn't think it belonged here, but Sql Server keeps throwing it in.
It's already under the Security folder for the Sql Server Instance.
Any help will be greatly appreciated!
Sandy
"Derekman" wrote:
> Sandy,
> After the user is added to SQL add the user to the database in which
> they need access. Check to make sure that the default database for the us
er
> is set to the database for the application. Once given rights to log into
> SQL the user will then go to their default database or the database within
> the connection string. Authentication fails if the user does not have rig
hts
> within the specified database.
> "Sandy" wrote:
>|||Hi -
I misspoke - I meant to refer to the folders on the left in Management
Studio --
Under the Server\Security\Logins is an icon that says NT AUTHORITY\SYSTEM an
d
also under MyDatabase\Security\Users is another icon that says NT
AUTHORITY\SYSTEM. Should both of these be there?

>In order for your users to connect using windows authentication, you would
>need to add the users domain\user account to sql server. Or grant a window
s
>group in which the users reside to sql server.
Where and how would I do this? Also, what do you mean by "...add....TO SQL
SERVER" and "...grant...TO SQL SERVER? That brings up connotations to me of
using Sql Server authentication.
I am sooooo completely with the language used in this security thin
g!
Sandy
"Greg Larsen" wrote:
> What exactly do you mean by when you say "I have the NT Authority\System a
s
> the login for both the Server and the database"? The "NT Authority\System
"
> account known as the "Local System" account, or the Local "Administrator"
> account on a computer. This account can only be used to access resource
> locally on the machine, and not network resources. So basically this is n
ot
> an account any user might connect to your database with. The "NT
> Authority\System" account might only be used if you where to login to your
> sql server machine locally as "Administrator".
> In order for your users to connect using windows authentication, you would
> need to add the users domain\user account to sql server. Or grant a windo
ws
> group in which the users reside to sql server. The users would then also
> need to be given permissions to the database for which you want to give th
em
> access.
> "Sandy" wrote:
>|||You can add user in Management Studio under the "Security\Login
folder. To do this right click on the Logins folder and select "New
Login..." in the "New Login" page enter the <domain>\<user> in the "Login
Name" field with the "Windows Authentication" button set. Then under "User
Mappings" give them access to the correct database and role. Hope this make
sense.
For SQL Server examples check out my website at:
http://www.geocities.com/sqlserverexamples
"Sandy" wrote:
> Hi -
> I misspoke - I meant to refer to the folders on the left in Management
> Studio --
> Under the Server\Security\Logins is an icon that says NT AUTHORITY\SYSTEM
and
> also under MyDatabase\Security\Users is another icon that says NT
> AUTHORITY\SYSTEM. Should both of these be there?
>
> Where and how would I do this? Also, what do you mean by "...add....TO S
QL
> SERVER" and "...grant...TO SQL SERVER? That brings up connotations to me
of
> using Sql Server authentication.
> I am sooooo completely with the language used in this security th
ing!
> --
> Sandy
>
> "Greg Larsen" wrote:
>|||Thanks, Greg.
I tried it and I'm still getting the message
Login failed for user ". The user is not associated with
a trusted SQL Server connection.
BTW, isn't the user name normally in the error message?
At any rate, I'm at my wits end and probably at the end of this job (as in
fired). Any other ideas about what could be causing this?
Thanks again for your patience!
Sandy
"Greg Larsen" wrote:
> You can add user in Management Studio under the "Security\Login
> folder. To do this right click on the Logins folder and select "New
> Login..." in the "New Login" page enter the <domain>\<user> in the "Login
> Name" field with the "Windows Authentication" button set. Then under "Us
er
> Mappings" give them access to the correct database and role. Hope this ma
ke
> sense.
> --
> For SQL Server examples check out my website at:
> http://www.geocities.com/sqlserverexamples
> "Sandy" wrote:
>|||Sandy,
Follow Greg's instructions and that should fix it. You can also use
T-SQL to do the same task, but this way is definitely easier.
You had also asked about training. the below link takes you to on
demand web casts. Kimberly Tripp recently completed an 11 part web series,
TechNet Webcast: SQL Server 2005 for the IT Professional, that would
definitely help along with numerous other webcasts that are available.
You can also go to http://www.microsoft.com/sql/eval/e-learn.mspx which will
provide more free courses from Microsofton SQL 2005.
Good Luck
"Greg Larsen" wrote:
> You can add user in Management Studio under the "Security\Login
> folder. To do this right click on the Logins folder and select "New
> Login..." in the "New Login" page enter the <domain>\<user> in the "Login
> Name" field with the "Windows Authentication" button set. Then under "Us
er
> Mappings" give them access to the correct database and role. Hope this ma
ke
> sense.
> --
> For SQL Server examples check out my website at:
> http://www.geocities.com/sqlserverexamples
> "Sandy" wrote:
>sql

Help! Trans log is growing and Log Reader can't connect

I have a SQL 2000 box with latest service pack as a publisher and
distributor. The log reader agent is not able to connect and as a result the
transaction log is growing.
On the other hand, there is a connection to the distrubtion database and it
appears to be an orphan connection from the log reader agent. I have killed
the orphan connection several times but that has not resolved the issue.
Following is the error I am getting:
SPID 30: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I believe if I can kill the connection, I will be able to start the log
reader agent successfully. I had the exact same issue yesterday and rebooting
the server did the trick. It is a production box so I can't afford to reboot
everyday like this.
What happens when you do a sp_repltrans in publisher database? You need to
set up a verboselevel to 2 and see what kind of output you get in output
file. That would be the start.
"Adam" wrote:

> I have a SQL 2000 box with latest service pack as a publisher and
> distributor. The log reader agent is not able to connect and as a result the
> transaction log is growing.
> On the other hand, there is a connection to the distrubtion database and it
> appears to be an orphan connection from the log reader agent. I have killed
> the orphan connection several times but that has not resolved the issue.
> Following is the error I am getting:
> SPID 30: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I believe if I can kill the connection, I will be able to start the log
> reader agent successfully. I had the exact same issue yesterday and rebooting
> the server did the trick. It is a production box so I can't afford to reboot
> everyday like this.
>
|||it probably is a phantom. See if you can start up the log reader agent
again.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:C47F8C71-4D3A-4823-84AE-1948DAFAF850@.microsoft.com...
>I have a SQL 2000 box with latest service pack as a publisher and
> distributor. The log reader agent is not able to connect and as a result
> the
> transaction log is growing.
> On the other hand, there is a connection to the distrubtion database and
> it
> appears to be an orphan connection from the log reader agent. I have
> killed
> the orphan connection several times but that has not resolved the issue.
> Following is the error I am getting:
> SPID 30: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I believe if I can kill the connection, I will be able to start the log
> reader agent successfully. I had the exact same issue yesterday and
> rebooting
> the server did the trick. It is a production box so I can't afford to
> reboot
> everyday like this.
>

help! SQL server trial expired

My SQL Server trial has expired and now I can't seem to access my databases,
is there anyway to import them into MSDE so I can still access them?
You can simply attach user databases to MSDE using sp_attach_db. See BOL for
syntax. This assumes they don't break the file size limits of MSDE which I
believe is 2 GB. An example below
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Data\pubs.mdf',
@.filename2 = N'c:\Data\pubs_log.ldf'
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Skylar Challand" <skylar.challand@.blatant.ca> wrote in message
news:OnrhQ9jIEHA.3444@.TK2MSFTNGP11.phx.gbl...
> My SQL Server trial has expired and now I can't seem to access my
databases,
> is there anyway to import them into MSDE so I can still access them?
>
sql

Help! Oracle linked server not working

we upgraded servers and now i can't link to an oracle server
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?
Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:

> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>

Help! I cant write this SQL Server query ... can you?

Take the following table

ID Shipment ETA Updated
01 123 3/1/04 2/12/04
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
05 456 3/1/04 2/17/04
06 456 3/1/04 2/16/04
07 456 3/1/04 2/15/04

I need a query that will return the 2 most recently updated rows for
each shipment. So the results would look like the following:

ID Shipment ETA Updated
02 123 3/2/04 2/13/04
03 123 3/1/04 2/14/04
04 154 3/2/04 2/12/04
06 456 3/1/04 2/16/04
05 456 3/1/04 2/17/04

Thanks,

TeknariLets say the name of your table is Shipment_Status. Here goes

select Shipment,Id from shipment_status a where
id=(select max(id) from shipment_status b where shipment=a.shipment )
or
id=(select max(id) from shipment_status c where shipment=a.shipment
and id <>(select max(id) from shipment_status d where
shipment=c.shipment) )

Try it
Prashant

junk2@.bgtl.com (Teknari) wrote in message news:<b1e69426.0402160742.25896bac@.posting.google.com>...
> Take the following table
> ID Shipment ETA Updated
> 01 123 3/1/04 2/12/04
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 05 456 3/1/04 2/17/04
> 06 456 3/1/04 2/16/04
> 07 456 3/1/04 2/15/04
>
> I need a query that will return the 2 most recently updated rows for
> each shipment. So the results would look like the following:
> ID Shipment ETA Updated
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 06 456 3/1/04 2/16/04
> 05 456 3/1/04 2/17/04
> Thanks,
> Teknari|||You can write this in a couple of ways:

--#1 (using TOP clause)
SELECT *
FROM tbl
WHERE Updated IN ( SELECT TOP 2 t1.Updated
FROM tbl t1
WHERE t1.Shipment = tbl.Shipment
ORDER BY t1.Updated DESC );

--#2 (using an aggregate)
SELECT *
FROM tbl
WHERE( SELECT COUNT(*)
FROM tbl t1
WHERE t1.Shipment = tbl.Shipment
AND t1.Updated >= tbl.Updated) <= 2 ;

--
- Anith
( Please reply to newsgroups only )|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. You might want to use ISO-8601 temporal formats,
just in case you have to exchange data with someone, either human or
machine.

I also hope that you have not written your own audit logs in SQL and
that "updated" refers to the ETA and not the PHYSICAL row. There are
tools for that kind of function, which is totally outside the scope of
an application database. Likewise, I will assume that you know better
than to use any kind of auto-increment number for a key, so I assume
that the real DDL looks like this:

CREATE TABLE ShipmentHistory
(shipment_nbr INTEGER NOT NULL,
eta DATETIME NOT NULL,
revised_eta DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
PRIMARY KEY (shipment_nbr, eta, revised_eta));

>> I need a query that will return the two most recently updated rows
for each shipment. <<

SELECT H1.shipment_nbr, H1.eta, MAX(H1.revised_eta),
(SELECT MAX(H2.revised_eta)
FROM ShipmentHistory AS H2
WHERE H1.shipment_nbr = H2.shipment_nbr
AND H2.revised_eta < H1.revised_eta)
FROM ShipmentHistory AS H1
GROUP BY H1.shipment_nbr, H1.eta

This will give you a NULL if the ETA changed only once.|||Celko wrote...

<snip>
Likewise, I will assume that you know better
> than to use any kind of auto-increment number for a key
<snip
Why not use an auto increment number as a key?|||You could try this

SELECT s.*
FROM shipment_status s
INNER JOIN (SELECT shipment, MAX(Updated) max_updated
FROM shipment_status
GROUP BY shipment

UNION ALL

SELECT s1.shipment, MAX(s1.Updated)
FROM shipment_status s1
INNER JOIN (SELECT shipment, MAX(Updated) max_updated
FROM shipment_status
GROUP BY shipment) s2 ON s1.shipment = s2.shipment AND
s1.Updated < s2.max_updated
GROUP BY s1.shipment) as m_all
ON s.shipment = m_all.shipment AND s.Updated = m_all.max_updated
ORDER BY s.shipment, s.ID

"Teknari" <junk2@.bgtl.com> wrote in message
news:b1e69426.0402160742.25896bac@.posting.google.c om...
> Take the following table
> ID Shipment ETA Updated
> 01 123 3/1/04 2/12/04
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 05 456 3/1/04 2/17/04
> 06 456 3/1/04 2/16/04
> 07 456 3/1/04 2/15/04
>
> I need a query that will return the 2 most recently updated rows for
> each shipment. So the results would look like the following:
> ID Shipment ETA Updated
> 02 123 3/2/04 2/13/04
> 03 123 3/1/04 2/14/04
> 04 154 3/2/04 2/12/04
> 06 456 3/1/04 2/16/04
> 05 456 3/1/04 2/17/04
> Thanks,
> Teknari|||>> Why not use an auto increment number as a key? <<

I have longer rants, but the Cliff Notes version is:

1) It is PHYSICAL and not LOGICAL

2) Non-relational

3) Proprietary

4) Meaningless in the data model

5) Unverifiable in the reality of the data model

6) Dangerously redundant, assuming that the table is actually a properly
designed table.

Newbies throw this on a table to fake a key because they don't know what
a key is, they are too lazy to research their industry for standards and
it looks like a sequential file's record number. They only know files
and still think in those terms.

As an aside, if this is an audit log, then you might want to look at
third party tools which are built for audits:

http://www.sswug.org/searchresults...ofind2=lumigent

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Thanks Anith,

You solution seemed the most elegant (at least as far as length of
query) so I went with it.

I do not have any experience using the table t1 in your query below.
It appears to me that it is a temporary table that is identical to the
table tbl which it is placed next to in the FROM statement, but I
really don't understand this.

Can somebody explain to me how it is used below or point me to a good
tutorial on the web?

Thanks,
Teknari

"Anith Sen" <anith@.bizdatasolutions.com> wrote in message news:<g7aYb.7095$hm4.6316@.newsread3.news.atl.earthlink.n et>...
> You can write this in a couple of ways:
> --#1 (using TOP clause)
> SELECT *
> FROM tbl
> WHERE Updated IN ( SELECT TOP 2 t1.Updated
> FROM tbl t1
> WHERE t1.Shipment = tbl.Shipment
> ORDER BY t1.Updated DESC );
> --#2 (using an aggregate)
> SELECT *
> FROM tbl
> WHERE( SELECT COUNT(*)
> FROM tbl t1
> WHERE t1.Shipment = tbl.Shipment
> AND t1.Updated >= tbl.Updated) <= 2 ;|||>> I do not have any experience using the table t1 in your query below.
It appears to me that it is a temporary table that is identical to the
table tbl which it is placed next to in the FROM statement, but I really
don't understand this.<<

This is a correlation name, or alias. It lets youuse the same table in
sevreal places. This is one of the reasons that SQL used to mean
"Structured Query Language"; it is that fundamental to the language. It
acts as if the engine has made a copy of the base table under the new
name that exists for the duration o the statement.

>> ... point me to a good tutorial on the web? <<

Go to the FirstSQL website or use Google to find one.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Help! can't run Update in SQL 2005

I have a table name : sale there have some item about (SN(int,PK),...Invoice(nvarchar)....)

try run a update query in SQL 2005 :

update sale set Invoive = '99999' where SN in('1','2','3')

Get the error msg:

Msg 512, Level 16, State 1, Procedure Update_Cost, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

but run in SQL 2000+SP4 is OK!

what's wrong in SQl2005?

this is working for me,

or

just send us the full test data(Table structure + Test data), check is there some trigger there for this table? go for the same statment for other table, i mean update same fileds, or so.

|||

From your description, SN is integer. The single quotes around the integers should be removed.

UPDATE sale SET Invoice = '99999' where SN in (1, 2, 3)

Martin Poon [MVP - SQL Server]

|||

Thanks a lot!

Find the root cause! after check the DB find there have trigger with updat invoice!

thanks again!

Help! Can't put double lines in the Table Region.

Table region will not activate the "Double Lines" property. Does anyone know
how to fix this problem?I've noticed this as well. Must be a bug...
--
Adrian M.
MCP
"OriginalStealth" <OriginalStealth@.discussions.microsoft.com> wrote in
message news:B06E97EB-9DEC-4485-BDAE-E2876A794809@.microsoft.com...
> Table region will not activate the "Double Lines" property. Does anyone
> know
> how to fix this problem?|||Take your report output and export to a pdf. When you magnify the output you
will probably see that it is indeed a double line. This is a problem and I
hope that it is fixed in RS 2005. This wasn't any help to you, other than to
know other people are having the same problem.
"Adrian M." wrote:
> I've noticed this as well. Must be a bug...
> --
> Adrian M.
> MCP
> "OriginalStealth" <OriginalStealth@.discussions.microsoft.com> wrote in
> message news:B06E97EB-9DEC-4485-BDAE-E2876A794809@.microsoft.com...
> > Table region will not activate the "Double Lines" property. Does anyone
> > know
> > how to fix this problem?
>
>

Help! Can't pass names with apostrophe from ASP to SQL7 proc

I have a proc that does wildcard searches on names which works fine
expect with names that contain an apostrophe like O'Neil.
When O'Neil is sent to the proc, I get "Incorrect syntax near 'Neil'."
This tells me I've got a delimiter issue.
I've tried replacing the single apostrophe with a double or even using
quotes as the delimiter and still get an error. What should I try; can
anyone help?
BTW, here's an example of the proc.
========================================
=============
CREATE proc getName
@.CUSTNAME varchar(15)
as
declare @.SQL varchar(4000)
set @.SQL = 'select PARENT_NAME, CITY, STATE, ZIP from
[CS-170].CUST.dbo.CUST_ADR
where
PARENT_NAME like ''%' + @.CUSTNAME + '%'''
exec (@.SQL)
========================================
==============
I've passed the name as below and still get errors.
getName O'Neil
getName 'O'Neil'
getName ''O'Neil'' - All Single quotes chr (39)
getName "O'Neil" - Quotes on the outside chr(34)
getName 'O''Neil' - All Single quotes chr (39)
getName "O''Neil" - Quotes on the outside chr(34), and double
singles in the name chr(39)
Nothing works due to the syntex. What can I do, please HELP!!
Thank you,
DaeI don't understand why you are using dynamic sql for this..
I guess you simplified the actual query for the post :)
But anyways.. try this.
getName 'O''''Neil' -- 4 single quotes|||this would work
getname 'o''''niel'
Best Regards
Vadivel
http://vadivel.blogspot.com
"Omnibuzz" wrote:

> I don't understand why you are using dynamic sql for this..
> I guess you simplified the actual query for the post :)
> But anyways.. try this.
> getName 'O''''Neil' -- 4 single quotes|||Or this should work:
SET QUOTED_IDENTIFIER OFF
EXEC getName "O'Anders"
"Vadivel" wrote:
> this would work
> getname 'o''''niel'
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
>
> "Omnibuzz" wrote:
>

HELP! cant figure this one out!

I'm in desperate need of help. I'm setting up an intranet portal using DNN. I added an event calendar module, but whenever I try to add events to it, the system rejects it with a nasty Sql exception saying the conversion from char to datetime produced an out of bounds result.

The string the table uses to convert to datetime is (I have not modified it, the module is exactly as it came when i downloaded)

(convert(varchar,getdate(),101))

The whole stack trace for the error is:

Stack Trace:

[SqlException: La conversin del tipo de datos char a datetime produjo un valor datetime fuera de intervalo.]
System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +642
System.Data.SqlClient.SqlCommand.ExecuteReader() +11
DotNetNuke.AVCalendarDB.Save() +1067
DotNetNuke.AVCalendarEdit.updateButton_Click(Objec t sender, EventArgs e) +3367
System.Web.UI.WebControls.LinkButton.OnClick(Event Args e) +108
System.Web.UI.WebControls.LinkButton.System.Web.UI .IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +138
System.Web.UI.Page.ProcessRequestMain() +1263

At first I thought it could be a language issue (DNN and the module are in english and my system runs XP Pro in Spanish) but I discarded it since it didn't work when I installed XP Pro in english

Any ideas?? I would really appreciate your help
Best regards from Chile
Javier L.What is this?

[SqlException: La conversin del tipo de datos char a datetime produjo un valor datetime fuera de intervalo.]|||Originally posted by gyuan
What is this?

[SqlException: La conversin del tipo de datos char a datetime produjo un valor datetime fuera de intervalo.]

The conversion from char to datetime produced a value outside the range of a datetime.

This is caused when you have a string value that cannot possibly be date ( 31 Feb 2004) or a time 66:00:00. You need to check the data is coherent.|||Put VARCHAR(10) instead of just VARCHAR. But I don't think this is the line that it's barking at...|||Did you try this on your Query Analyzer?

select convert(varchar(20), getdate(), 101)|||Based on the error message you got

The conversion from char to datetime produced a value outside the range of a datetime.

the problem is not in the code

convert(varchar(20),getdate(),101)

HELP! Can't display calculated member in Excel PivotTable

I have created a few calculated members under one dimension (meaning the par
ent dimension is not Measures, but other dimensions). It can be showed in th
e Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS Offic
e 2k, xp, even 2003). Is th
ere any solution to display the calculated members (as with the dimension) i
n Excel PivotTable? vba code needed? service pack needed?Certain cube viewer (like Excel for example) don't allow you to view
calculated members and member properies ... you can't even do a
drill-through from there ... it's just a limitation on the client side ...
the client must be "programmed" to make use of those properties before it
will work ....
"xhuey" <anonymous@.discussions.microsoft.com> wrote in message
news:1D9E50BB-2936-48ED-9625-329596764B28@.microsoft.com...
> I have created a few calculated members under one dimension (meaning the
parent dimension is not Measures, but other dimensions). It can be showed in
the Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS
Office 2k, xp, even 2003). Is there any solution to display the calculated
members (as with the dimension) in Excel PivotTable? vba code needed?
service pack needed?|||I had a similar problem using ADO MD from Visual Basic. I solved it just
using MSOLAP.2 instead of MSOLAP in the connection string.
Hope it can help you
Andrea
"xhuey" <anonymous@.discussions.microsoft.com> wrote in message
news:1D9E50BB-2936-48ED-9625-329596764B28@.microsoft.com...
> I have created a few calculated members under one dimension (meaning the
parent dimension is not Measures, but other dimensions). It can be showed in
the Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS
Office 2k, xp, even 2003). Is there any solution to display the calculated
members (as with the dimension) in Excel PivotTable? vba code needed?
service pack needed?|||You may find that it's being exlcuded in Excel as Excel tends to make use of
the NONEMPTYCROSSJOIN function, which doesn't include (read as work for)
calculated members.
"xhuey" <anonymous@.discussions.microsoft.com> wrote in message
news:1D9E50BB-2936-48ED-9625-329596764B28@.microsoft.com...
> I have created a few calculated members under one dimension (meaning the
parent dimension is not Measures, but other dimensions). It can be showed in
the Analysis Manager, but cannot be displayed in MS Excel PivotTable (MS
Office 2k, xp, even 2003). Is there any solution to display the calculated
members (as with the dimension) in Excel PivotTable? vba code needed?
service pack needed?

HELP! Can't delete merge replication subscribers in 2005

My client is using SQL 2005 Workgroup Edition. About 18-20 users will be
merge replication subscribers over the Internet with web synchronization.
During testing, several subscriptions were created and they will not drop
with the normal drop stored procedures.
This is an urgent problem because with my bogus, testing subscriptions
there, we have reached the 25 subscription limit and we cannot get their
final users subscribed.
I've seen others having this problem but haven't seen the resolution. Any
help would be greatly appreciated.
Thanks!
I take it you have tried to drop them using sp_dropmergesubscription and
sp_dropmergepullsubscription correct?
If so you should be able to drop them by deleting the contents of the
following tables on your subscriber db.
sysmergesubscriptions
MSmerge_supportability_settings
MSmerge_log_files
MSmerge_replinfo
MSmerge_supportability_settings
MSmerge_log_files
MSrepl_errors
MSmerge_history
MSmerge_articlehistory
MSmerge_sessions
MSmerge_replinfo
sysmergearticles
sysmergepublications
sysmergepartitioninfo
sysmergepartitioninfoview
sysmergeschemaarticles
sysmergeextendedarticlesview
sysmergeschemachange
sysmergesubsetfilters
sysmergesubscriptions
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Shannon Willison" <Shannon Willison@.discussions.microsoft.com> wrote in
message news:2ABD7D82-C738-4547-A9F6-DF29202DF39F@.microsoft.com...
> My client is using SQL 2005 Workgroup Edition. About 18-20 users will be
> merge replication subscribers over the Internet with web synchronization.
> During testing, several subscriptions were created and they will not drop
> with the normal drop stored procedures.
> This is an urgent problem because with my bogus, testing subscriptions
> there, we have reached the 25 subscription limit and we cannot get their
> final users subscribed.
> I've seen others having this problem but haven't seen the resolution. Any
> help would be greatly appreciated.
> Thanks!

HELP! Can't connect to MSDE using Enterprise manger

I installed an instance (only one) of MSDE200 sp3a on one of the desktop machine (XP pro sp1), I would like to manage it by using the enterprise manager installed on another machine on the same network. I was getting the "SQL server does not exist or acce
ss denied" error. I am sure I have enter the proper login info and the MSDE is running.
Is there any configuration need to be done on the MSDE? any expert here can help me on this?
Thanks
Henry
hi Henry,
"Henry" <henryli1222@.msn.com> ha scritto nel messaggio
news:6AB45DD1-DA80-440E-B338-EA8F5467358F@.microsoft.com...
> I installed an instance (only one) of MSDE200 sp3a on one of the desktop
machine (XP pro sp1), I would like to manage it by using the enterprise
manager installed on another machine on the same network. I was getting the
"SQL server does not exist or access denied" error. I am sure I have enter
the proper login info and the MSDE is running.
> Is there any configuration need to be done on the MSDE? any expert here
can help me on this?
>
probably you installed the remote MSDE instance with the default parameter
which disable network protocols for it, not allowing remote connections to
that server...
please run svrnetcn.exe (Server Net Utility) on that server to verify the
desired network protocols are enabled...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks, it works. It's possible to enable the network protocals for the MSDE 2000 installation?
|||Hi Henry,
You can do this by executing svrnetcn.exe.
HTH,
Greg Low (MVP)
MSDE Manager SQL Tools
www.whitebearconsulting.com
"Henry" <anonymous@.discussions.microsoft.com> wrote in message
news:773EC08B-D846-4AB5-AA82-35034BD9375F@.microsoft.com...
> Thanks, it works. It's possible to enable the network protocals for the
MSDE 2000 installation?

Help! Cant Connect - db offline

OK, so I took one of my db's offline for maintenance, then realized that I wanted to be logged in as another user, so I logged out.

Now SS won't let me log in, because every time I try to connect I get:

A connection could not be established...
Reason: Cannot open user default database. Login failed..

The db I took offline is the default db for every single privileged account on the server. Why do I feel like I just locked my keys in the car?

Any help would be greatly appreciated! TIA!Never mind, I figured it out. Logged in via ISQL, and issued:

exec sp_defaultdb '<my sa account>', '<a db known to be online>'

ISQL let me log in, and simply used 'master' instead of the default db that was offline. What reason in the world could there be for EM behaving differently?|||In the future you should set master to be the default database for all users.|||Why? I only do this for the admin group.|||In the future you should set master to be the default database for all users.
Why in the world would you want to do that for application users ?|||Enterprise manager is a good tool to start learning about SQL Server with, but as you get to more and more advanced things, it helps more to use Query Analyzer, or if you are super-hard-core isql or osql. However, in this case, I am not certain how you did get around this. This may take a bit of experimentation for me to figure out. I had thought that any login is sent to the default database, even if just fr a moment, when you have the -d switch set in isql, or a database name set in your connection string. I could very well be wrong.

But essentially, yes. If you drop the database that all of the admin users have as their default database, then you have effectively "locked the keys in the server".|||In the future you should set master to be the default database for all users.

He's just drunk...

and I'm jealous...

Help! Bad insert to a table which I can't delete now.

I made a mistake in my insert statement, the insert to destination table is
the same as the Source from table. Oops! Now I can't truncate, delete this
table at all. Any way to get rid of it and start again?
Thanks, Alpha
insert into
[TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAutoAccident,RelatedOtherAccident,RelatedNotAccident,
ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
--We default to not releasing patient info
select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
exttidOops, sorry. I was able to delete the table after all.
"Alpha" wrote:
> I made a mistake in my insert statement, the insert to destination table is
> the same as the Source from table. Oops! Now I can't truncate, delete this
> table at all. Any way to get rid of it and start again?
> Thanks, Alpha
> insert into
> [TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAutoAccident,RelatedOtherAccident,RelatedNotAccident,
> ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
> --We default to not releasing patient info
> select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
> exttid
>

Help! Bad insert to a table which I can't delete now.

I made a mistake in my insert statement, the insert to destination table is
the same as the Source from table. Oops! Now I can't truncate, delete this
table at all. Any way to get rid of it and start again?
Thanks, Alpha
insert into
[TisSuite].[dbo]. tblSource_info(extTID,RelatedEmployment,
RelatedAuto
Accident,RelatedOtherAccident,RelatedNot
Accident,
ReleasePatientInfo,PlaceOfService,TypeOf
Service, EMG,COB)
--We default to not releasing patient info
select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS orde
r by
exttidOops, sorry. I was able to delete the table after all.
"Alpha" wrote:

> I made a mistake in my insert statement, the insert to destination table i
s
> the same as the Source from table. Oops! Now I can't truncate, delete th
is
> table at all. Any way to get rid of it and start again?
> Thanks, Alpha
> insert into
> [TisSuite].[dbo]. tblSource_info(extTID,RelatedEmployment,
RelatedAu
toAccident,RelatedOtherAccident,RelatedN
otAccident,
> ReleasePatientInfo,PlaceOfService,TypeO
fService, EMG,COB)
> --We default to not releasing patient info
> select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS o
rder by
> exttid
>

Help! Bad insert to a table which I can't delete now.

I made a mistake in my insert statement, the insert to destination table is
the same as the Source from table. Oops! Now I can't truncate, delete this
table at all. Any way to get rid of it and start again?
Thanks, Alpha
insert into
[TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAu toAccident,RelatedOtherAccident,RelatedNotAccident ,
ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
--We default to not releasing patient info
select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
exttid
Oops, sorry. I was able to delete the table after all.
"Alpha" wrote:

> I made a mistake in my insert statement, the insert to destination table is
> the same as the Source from table. Oops! Now I can't truncate, delete this
> table at all. Any way to get rid of it and start again?
> Thanks, Alpha
> insert into
> [TisSuite].[dbo].tblSource_info(extTID,RelatedEmployment,RelatedAu toAccident,RelatedOtherAccident,RelatedNotAccident ,
> ReleasePatientInfo,PlaceOfService,TypeOfService, EMG,COB)
> --We default to not releasing patient info
> select exttid,0,0,0,1,0,1,41,'','' from [TisSuite].[dbo].tblPCS order by
> exttid
>

help! backing up sql server 2005 using osql.exe

Hi

I am trying to make a backup of my SQL server 2005, used with Web Developer 2005 Express, in osql.exe (can't find any other way of doing it). I get the error that "under the default settings SQL server does not allow remote connections".

I connect to it fine from the Web Developer 2005 and my application . I followed another post's instructions where I enabled the TCP/IP and named pipes for "Protocols for SQLEXPRESS", but didn't work. In fact when I changed TCP/IP properties/ IP Addresses/TCP Dynamic port to nothing , and TCP Port to 1357, the server wouldn't start again !! (not doin THAT again)

Regards

Amanda

Godditt !!!!

I needed to enable the SQL Server Browser service, found in article:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Thanks...

help! all of a sudden can't connect to my db

hello,
i've been running fine with the same msft sql server set up for over a year
now. each night i run some vba code which connects with the db and does some
data updates. all of a sudden i'm having trouble communicating with the db.
when i attempt to connect to the db via query analyzer, i get the message:
Server: Msg 924, Level 14, State 1, Line 1
Database 'artorius' is already open and can only have one user at a time.
what the dillyo? maybe some setting got changed? how can i change it back?
hopefully this is not a symptom of a larger problem. anyway, would
appreciate any suggestions to get this working again.
appreciated,
matthew
nevermind, i switched the setting back. sorry for the panic. i have no idea
how the setting got switched in the first place though.
"matthew c. harad" wrote:

> hello,
> i've been running fine with the same msft sql server set up for over a year
> now. each night i run some vba code which connects with the db and does some
> data updates. all of a sudden i'm having trouble communicating with the db.
> when i attempt to connect to the db via query analyzer, i get the message:
> Server: Msg 924, Level 14, State 1, Line 1
> Database 'artorius' is already open and can only have one user at a time.
> what the dillyo? maybe some setting got changed? how can i change it back?
> hopefully this is not a symptom of a larger problem. anyway, would
> appreciate any suggestions to get this working again.
> appreciated,
> matthew

help! all of a sudden can't connect to my db

hello,
i've been running fine with the same msft sql server set up for over a year
now. each night i run some vba code which connects with the db and does som
e
data updates. all of a sudden i'm having trouble communicating with the db.
when i attempt to connect to the db via query analyzer, i get the message:
Server: Msg 924, Level 14, State 1, Line 1
Database 'artorius' is already open and can only have one user at a time.
what the dillyo? maybe some setting got changed? how can i change it back?
hopefully this is not a symptom of a larger problem. anyway, would
appreciate any suggestions to get this working again.
appreciated,
matthewnevermind, i switched the setting back. sorry for the panic. i have no ide
a
how the setting got switched in the first place though.
"matthew c. harad" wrote:

> hello,
> i've been running fine with the same msft sql server set up for over a yea
r
> now. each night i run some vba code which connects with the db and does s
ome
> data updates. all of a sudden i'm having trouble communicating with the d
b.
> when i attempt to connect to the db via query analyzer, i get the message:
> Server: Msg 924, Level 14, State 1, Line 1
> Database 'artorius' is already open and can only have one user at a time.
> what the dillyo? maybe some setting got changed? how can i change it bac
k?
> hopefully this is not a symptom of a larger problem. anyway, would
> appreciate any suggestions to get this working again.
> appreciated,
> matthew

HELP! - cant send mail

xp_sendmail has died on our SQL Server, I get error "xp_sendmail:
failed with mail error 0x80004005" if I try using it.

I believe that the reason that this has happened is because the SQL
Server service account is unable to send mail, as follows:

If I log on to Outlook Web Access as the SQL Server service account
(SVC_SqlServer) I am able to receive mail. However, any mail sent from
this account simply dissapears.

It appears in the Sent items folder (in OWA for the SVC_SqlServer
account) but the recipient (me, in this case) never receives it!

Does anyone know why this account is unable to send mail?

Help!

eddiec :-)chalk@.netspace.net.au wrote in message news:<79744f05.0402112113.3a3a7640@.posting.google.com>...
> xp_sendmail has died on our SQL Server, I get error "xp_sendmail:
> failed with mail error 0x80004005" if I try using it.
> I believe that the reason that this has happened is because the SQL
> Server service account is unable to send mail, as follows:
> If I log on to Outlook Web Access as the SQL Server service account
> (SVC_SqlServer) I am able to receive mail. However, any mail sent from
> this account simply dissapears.
> It appears in the Sent items folder (in OWA for the SVC_SqlServer
> account) but the recipient (me, in this case) never receives it!
> Does anyone know why this account is unable to send mail?
> Help!
> eddiec :-)

This KB article might be helpful:

http://support.microsoft.com/defaul...B;EN-US;q293422

It's not entirely clear from your post if xp_sendmail used to work and
now does not work; if SQL Mail has never worked, then you might also
want to look at this article:

http://support.microsoft.com/defaul...6&Product=sql2k

Simon