Hi I'm just looking for a little bit of advice to point me in the right direction before I start to put this together.
The task I have is too automate the clear down of the transaction logs. Presently I am manually going in and using this method:
BACKUP LOG {database name} WITH TRUNCATE_ONLY
DBCC SHRINKFILE ( {log file name},2 )
However now we have decided this would be best to be automated and I'm faily new to SQL server so I'm not sure the best way to go about this. So I was hoping someone could point me in the right direction that I go about doing it so I don't go off on a complete tangent. Ow and Can anyone think of any problem automating this job could cause.
My eternal graitude, EdWhy are you backing up with TRUNCATE ONLY? This does not give you any recoverability for transactions occuring after the last backup. If you really don't need point-in-time recoverability then you could just put the database in simple recovery mode.
blindman|||Ahh I see
I was trying to come up with a way of reducing the size transaction Log without, stopping the database and restarting and I came across this advice, I used the first statment because otherwise the the transactionlog won't shrink much. I didn't really consider the point that (I said I was new too this) this basically mean that the it is no longer actuly producing a functional backup.
Ouch
Well in that case I need to come up with a solution, Do you have any Idea what the best time to clear down the transaction logs would be? erm could I run this immediately before the back up. but then if the backup is faulty we would lose a whole days work and if I run it just after the backup then the next days would be useless, unless I run it immediately and make sure their is no transaction inbetween the backup and the transaction shrinking!
Do you know how to restore the transaction log to it orginal functioning glory!
Thanks Again Ed
Showing posts with label together. Show all posts
Showing posts with label together. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
Advanced Join?
I have two tables X and Y which need to join together. Table X has an ID field which connects to an ID field in Table Y. My problem is the ID field in Table X can contain multiple ID's EX:
Table X
ID
-
2,1,4,
2,5,
1,
3,1,2,4,
ect...
whereas the ID field in Table Y contains one ID in each row EX:
Table Y
ID Color
- -
1 Green
2 Blue
3 Red
4 Yellow
5 Orange
ect...
Is there a way to join these two tables together? I need the output to be...
ID Color
-
2,1,4, Blue,Green,Yellow
2,5, Blue, Orange
1, Green
3,1,2,4, Red, Green, Blue, Yellow
ect...
Yes, but it will be messy and inefficient. And most likely, it will ignore any indexing.
You have violated a cardinal rule with ID columns. In order to efficiently link tables, they must be individual values -NOT simulated arrays of values. This is really is a bad design that will continue to haunt you throughout the project lifecycle.
SELECT
x.ID,
y.Color
FROM Table_X x
JOIN Table_Y x
ON ( ',' + x.ID ) LIKE ( ',' + y.ID + ',' )
There are some other methods as well, one of which would create a #Temp table, split apart the separate ID's in Table_X, and then JOIN that #Temp table with these two tables. Here is a link to that approach. And another.
You really 'shouldn't do that kind of 'trickery' with key values. The continual hassle with the data just isn't worth the 'buzz' gained from being so clever...
It's NOT an advanced JOIN, it is a very, very silly table design!
|||If you think this is "silly" you should see the rest of the database! The sad part about this whole thing is that our company purchased this application from a leading medical management software company for 1,$$$,$$$. Your example doesn't give me the output I was looking for but I will try the example from the link you provided on Monday when I get back to work. Thanks for your help.|||
I see that I left out an important piece of the code. My apologies.
SELECT
x.ID,
y.Color
FROM Table_X x
JOIN Table_Y x
ON ( ',' + x.ID ) LIKE ( '%,' + y.ID + ',%' )
That 'should' work a bit better.
I understand. Sometimes I just don't get how seemingly intelligent folks spend so much money for such poorly designed software. But it happens every day. And then we have to support it.
|||Your query gives me....
Table_X.ID Table_Y.Color
2,1,4, Blue
2,1,4, Green
2,1,4, Yellow
2,5, Blue
2,5, Orange
1, Green
3,1,2,4, Red
3,1,2,4, Green
3,1,2,4, Blue
3,1,2,4, Yellow
How do you get it to.?.?.?.
Table_X.ID Table_Y.Color
-
2,1,4, Blue,Green,Yellow
2,5, Blue, Orange
1, Green
3,1,2,4, Red, Green, Blue, Yellow
What you are now requesting is a function for the display tool. The client application 'should' be creating a string array to put those values together. Folks often forget, SQL Server excels at storing and retrieving data, not at creating displays. But so many application are being created by folks that really aren't very good programmers at all, so things like this get stuffed into SQL Server.
You can use one of the methods listed in these resources.
Lists -Field Concatenation, One Field to Itself for string
SQL 2000 http://omnibuzz-sql.blogspot.com/2006/06/concatenate-values-in-column-in-sql.html
SQL 2005 http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/06/871.aspx
http://www.projectdmx.com/tsql/rowconcatenate.aspx
|||
Thanks for all your help!!!
Advanced Execution Plan Question
I have a scenario where I have 3 tables joined together. The first is the
"main" table that will define the result set. The second is a lookup table;
the main table has a foreign key to it. The third is a detail table; it has
a foreign key to the main table. Both the second and third tables are on
the right side on a LEFT OUTER JOIN. In this particular query, I only need
the detail data for a particular row if the lookup row exists. The reason
for this is that if the lookup row exists, it will be used to further filter
the result set based upon a value in the detail row. But if the lookup row
doesn't exist, there's no need to lookup the detail row. In order to
optimize the query I attempted to add an additional condition to the ON
clause joining the main and detail table saying only join if the lookup key
is not null:
SELECT
...
FROM
tbFilterTest F
LEFT JOIN tbFilterTestLookup L ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail D ON L.LookupID IS NOT NULL
AND
F.FilterID
= D.FilterID
My hope was that in the join in the execution plan that there would be an
argument "L.LookupID <> null". What I'm ending up with in the actual
execution plan is the "L.LookupID <> null" is not in the join but its
showing up as an argument in the index s
for the inner input, meaning
(I'm thinking) that the s is still getting executed and then the
"L.LookupID <> null" is being applied afterward, which means the s was
wasted if "L.LookupID = null".
Now I put together the 3 tables listed above to test this more closely.
Interestingly enough, the "L.LookupID <> null" argument WAS in the join and
not in the inner input, meaning the index s was not being performed on
the detail table when the lookup was null. HOWEVER, I noticed the inner
input (detail table) was having an index scan because I didn't have an index
on FilterID. Once I put the index on FilterID on the detail table, the
"L.LookupID <> null" moved back to the inner input (detail) index s!
I'm trying to understand the logic of this and also wonder if there is a way
to make the "L.LookupID <> null" always occur in the join. The actual
query I'm doing this for is very vital and regularly executed.
Below is DDL, sample data, and the query. Also is the index that "makes the
difference". If you execute the query without the index and then with the
index, you should see the "L.LookupID <> null" move from the join to the
inner input index s. Note that the sample query simply demonstrates "the
moving WHERE clause" in the execution plan. It does not demonstrate the
condition in my real query that moved me to try and accomplish this.
Thanks,
Mike
----
SET NOCOUNT ON
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTestDetail')
DROP TABLE tbFilterTestDetail
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTest')
DROP TABLE tbFilterTest
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTestLookup')
DROP TABLE tbFilterTestLookup
GO
CREATE TABLE tbFilterTestLookup
(
LookupID int NOT NULL CONSTRAINT PK_FilterTestLookup PRIMARY KEY
)
GO
INSERT tbFilterTestLookup (LookupID) VALUES (1000)
INSERT tbFilterTestLookup (LookupID) VALUES (2000)
INSERT tbFilterTestLookup (LookupID) VALUES (3000)
INSERT tbFilterTestLookup (LookupID) VALUES (4000)
INSERT tbFilterTestLookup (LookupID) VALUES (5000)
INSERT tbFilterTestLookup (LookupID) VALUES (5001)
INSERT tbFilterTestLookup (LookupID) VALUES (5002)
INSERT tbFilterTestLookup (LookupID) VALUES (5003)
INSERT tbFilterTestLookup (LookupID) VALUES (5004)
INSERT tbFilterTestLookup (LookupID) VALUES (5005)
INSERT tbFilterTestLookup (LookupID) VALUES (5006)
INSERT tbFilterTestLookup (LookupID) VALUES (5007)
INSERT tbFilterTestLookup (LookupID) VALUES (5008)
INSERT tbFilterTestLookup (LookupID) VALUES (5009)
INSERT tbFilterTestLookup (LookupID) VALUES (5010)
INSERT tbFilterTestLookup (LookupID) VALUES (5011)
INSERT tbFilterTestLookup (LookupID) VALUES (5012)
INSERT tbFilterTestLookup (LookupID) VALUES (5013)
INSERT tbFilterTestLookup (LookupID) VALUES (5014)
GO
CREATE TABLE tbFilterTest
(
FilterID int NOT NULL CONSTRAINT PK_FilterTest PRIMARY KEY,
LookupID int NOT NULL
)
GO
INSERT tbFilterTest (FilterID, LookupID) VALUES (100, 10) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (200, 11) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (300, 12) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (400, 1000) -- Match
INSERT tbFilterTest (FilterID, LookupID) VALUES (500, 13) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (600, 14) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (700, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (800, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (900, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (620, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (730, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (840, 20) -- No match
GO
CREATE TABLE tbFilterTestDetail
(
DetailID int IDENTITY (10000, 1) NOT NULL CONSTRAINT PK_tbFilterTestDetail
PRIMARY KEY,
FilterID int NOT NULL CONSTRAINT FK_tbFilterTestDetail_tbFilterTest
REFERENCES tbFilterTest (FilterID),
Descrip varchar(128) NOT NULL
)
GO
CREATE INDEX IX_tbFilterTestDetail_FilterID ON tbFilterTestDetail (FilterID)
GO
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (100, '100-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (100, '100-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (200, '200-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (200, '200-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (300, '200-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (300, '200-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (400, '400-1-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (400, '400-2-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (500, '500-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (500, '500-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (600, '600-1-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (600, '600-2-match')
GO
-- Sample Query
SELECT
F.FilterID,
L.LookupID,
D.Descrip
FROM
tbFilterTest F
LEFT JOIN tbFilterTestLookup L ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail D ON L.LookupID IS NOT NULL -- this is
the condition that moves in the execution plan
AND
F.FilterID =
D.FilterID
-- Magic index: try sample query w/ and w/o
CREATE INDEX IX_tbFilterTestDetail_FilterID ON tbFilterTestDetail (FilterID)
GOOn Thu, 18 Aug 2005 13:53:36 -0400, Mike Jansen wrote:
(snip)
>Both the second and third tables are on
>the right side on a LEFT OUTER JOIN. In this particular query, I only need
>the detail data for a particular row if the lookup row exists.
Hi Mike,
Then why use a LEFT OUTER JOIN' Outer joins should only be used if
you must retain rows from one table even though there is no match in the
other table. The standard join type (INNER JOIN) will retain only rows
that match (as per your request) and will probably perform faster as
well.
SELECT ...
FROM tbFilterTest AS F
INNER JOIN tbFilterTestLookup AS L
ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail AS D
ON F.FilterID = D.FilterID
If you are sure that there will always be at least one matching row in
the FilterTestDetail table, change the second join to an INNER JOIN as
well. It won't change the results, but it might speed up the
performance.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
"main" table that will define the result set. The second is a lookup table;
the main table has a foreign key to it. The third is a detail table; it has
a foreign key to the main table. Both the second and third tables are on
the right side on a LEFT OUTER JOIN. In this particular query, I only need
the detail data for a particular row if the lookup row exists. The reason
for this is that if the lookup row exists, it will be used to further filter
the result set based upon a value in the detail row. But if the lookup row
doesn't exist, there's no need to lookup the detail row. In order to
optimize the query I attempted to add an additional condition to the ON
clause joining the main and detail table saying only join if the lookup key
is not null:
SELECT
...
FROM
tbFilterTest F
LEFT JOIN tbFilterTestLookup L ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail D ON L.LookupID IS NOT NULL
AND
F.FilterID
= D.FilterID
My hope was that in the join in the execution plan that there would be an
argument "L.LookupID <> null". What I'm ending up with in the actual
execution plan is the "L.LookupID <> null" is not in the join but its
showing up as an argument in the index s
for the inner input, meaning(I'm thinking) that the s
is still getting executed and then the"L.LookupID <> null" is being applied afterward, which means the s
waswasted if "L.LookupID = null".
Now I put together the 3 tables listed above to test this more closely.
Interestingly enough, the "L.LookupID <> null" argument WAS in the join and
not in the inner input, meaning the index s
was not being performed onthe detail table when the lookup was null. HOWEVER, I noticed the inner
input (detail table) was having an index scan because I didn't have an index
on FilterID. Once I put the index on FilterID on the detail table, the
"L.LookupID <> null" moved back to the inner input (detail) index s
!I'm trying to understand the logic of this and also wonder if there is a way
to make the "L.LookupID <> null" always occur in the join. The actual
query I'm doing this for is very vital and regularly executed.
Below is DDL, sample data, and the query. Also is the index that "makes the
difference". If you execute the query without the index and then with the
index, you should see the "L.LookupID <> null" move from the join to the
inner input index s
. Note that the sample query simply demonstrates "themoving WHERE clause" in the execution plan. It does not demonstrate the
condition in my real query that moved me to try and accomplish this.
Thanks,
Mike
----
SET NOCOUNT ON
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTestDetail')
DROP TABLE tbFilterTestDetail
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTest')
DROP TABLE tbFilterTest
GO
IF EXISTS (SELECT * FROM sysobjects where name = 'tbFilterTestLookup')
DROP TABLE tbFilterTestLookup
GO
CREATE TABLE tbFilterTestLookup
(
LookupID int NOT NULL CONSTRAINT PK_FilterTestLookup PRIMARY KEY
)
GO
INSERT tbFilterTestLookup (LookupID) VALUES (1000)
INSERT tbFilterTestLookup (LookupID) VALUES (2000)
INSERT tbFilterTestLookup (LookupID) VALUES (3000)
INSERT tbFilterTestLookup (LookupID) VALUES (4000)
INSERT tbFilterTestLookup (LookupID) VALUES (5000)
INSERT tbFilterTestLookup (LookupID) VALUES (5001)
INSERT tbFilterTestLookup (LookupID) VALUES (5002)
INSERT tbFilterTestLookup (LookupID) VALUES (5003)
INSERT tbFilterTestLookup (LookupID) VALUES (5004)
INSERT tbFilterTestLookup (LookupID) VALUES (5005)
INSERT tbFilterTestLookup (LookupID) VALUES (5006)
INSERT tbFilterTestLookup (LookupID) VALUES (5007)
INSERT tbFilterTestLookup (LookupID) VALUES (5008)
INSERT tbFilterTestLookup (LookupID) VALUES (5009)
INSERT tbFilterTestLookup (LookupID) VALUES (5010)
INSERT tbFilterTestLookup (LookupID) VALUES (5011)
INSERT tbFilterTestLookup (LookupID) VALUES (5012)
INSERT tbFilterTestLookup (LookupID) VALUES (5013)
INSERT tbFilterTestLookup (LookupID) VALUES (5014)
GO
CREATE TABLE tbFilterTest
(
FilterID int NOT NULL CONSTRAINT PK_FilterTest PRIMARY KEY,
LookupID int NOT NULL
)
GO
INSERT tbFilterTest (FilterID, LookupID) VALUES (100, 10) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (200, 11) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (300, 12) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (400, 1000) -- Match
INSERT tbFilterTest (FilterID, LookupID) VALUES (500, 13) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (600, 14) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (700, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (800, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (900, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (620, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (730, 20) -- No match
INSERT tbFilterTest (FilterID, LookupID) VALUES (840, 20) -- No match
GO
CREATE TABLE tbFilterTestDetail
(
DetailID int IDENTITY (10000, 1) NOT NULL CONSTRAINT PK_tbFilterTestDetail
PRIMARY KEY,
FilterID int NOT NULL CONSTRAINT FK_tbFilterTestDetail_tbFilterTest
REFERENCES tbFilterTest (FilterID),
Descrip varchar(128) NOT NULL
)
GO
CREATE INDEX IX_tbFilterTestDetail_FilterID ON tbFilterTestDetail (FilterID)
GO
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (100, '100-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (100, '100-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (200, '200-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (200, '200-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (300, '200-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (300, '200-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (400, '400-1-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (400, '400-2-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (500, '500-1-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (500, '500-2-no match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (600, '600-1-match')
INSERT tbFilterTestDetail (FilterID, Descrip) VALUES (600, '600-2-match')
GO
-- Sample Query
SELECT
F.FilterID,
L.LookupID,
D.Descrip
FROM
tbFilterTest F
LEFT JOIN tbFilterTestLookup L ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail D ON L.LookupID IS NOT NULL -- this is
the condition that moves in the execution plan
AND
F.FilterID =
D.FilterID
-- Magic index: try sample query w/ and w/o
CREATE INDEX IX_tbFilterTestDetail_FilterID ON tbFilterTestDetail (FilterID)
GOOn Thu, 18 Aug 2005 13:53:36 -0400, Mike Jansen wrote:
(snip)
>Both the second and third tables are on
>the right side on a LEFT OUTER JOIN. In this particular query, I only need
>the detail data for a particular row if the lookup row exists.
Hi Mike,
Then why use a LEFT OUTER JOIN' Outer joins should only be used if
you must retain rows from one table even though there is no match in the
other table. The standard join type (INNER JOIN) will retain only rows
that match (as per your request) and will probably perform faster as
well.
SELECT ...
FROM tbFilterTest AS F
INNER JOIN tbFilterTestLookup AS L
ON F.LookupID = L.LookupID
LEFT JOIN tbFilterTestDetail AS D
ON F.FilterID = D.FilterID
If you are sure that there will always be at least one matching row in
the FilterTestDetail table, change the second join to an INNER JOIN as
well. It won't change the results, but it might speed up the
performance.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, February 24, 2012
ADO.NET connection to SQL fails
Hello,
I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do an insert to can't validate my login. I've verified that the account does have permissions to access the SQL DB and that everything is granted as far as the INSERT, UPDATE
, etc...
I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and Windows).
I've tried all the suggestions from the "connectionstrings.com" website and I'm still getting errors stating that the login failed.
Here is a copy of the error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
The above error is happening when I use the connection string of:
"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
I get the same thing when I use "Integrated Security=SSPI" as well.
Here is the error I get when I try to use a user id:
Login failed for user '{user name}'.
The funny thing about it is that the user name specified above is the DBO and it is still rejected.
One last question; have you ever wanted to give up with computers and go back to pen and paper?
Any futher suggestions would be really great! I really appreciate it...
Dale
DBO is not a login, it is a user.
Try connecting with sa (and the sa password). I am guessing that you =
will have better results.
NOTE: You should probably create a login that you will use to connect to =
the database from your web app. If you are planning on using stored =
procedures (a good idea, by the way) they will not need any permissions =
in the database (other than "execute" on the stored procedures used by =
your app).
--=20
Keith
"Dale" <anonymous@.discussions.microsoft.com> wrote in message =
news:1025EFC0-546F-4F76-96CD-75B12AA30E03@.microsoft.com...
> Hello,=20
>=20
> I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm =
trying to do an insert to can't validate my login. I've verified that =
the account does have permissions to access the SQL DB and that =
everything is granted as far as the INSERT, UPDATE, etc...=20
>=20
> I have my SQL Server (SQL2K) set up for mixed authentication (both SQL =
and Windows).=20
>=20
> I've tried all the suggestions from the "connectionstrings.com" =
website and I'm still getting errors stating that the login failed.=20
>=20
> Here is a copy of the error:=20
> Login failed for user '(null)'. Reason: Not associated with a trusted =
SQL Server connection.=20
>=20
> The above error is happening when I use the connection string of:=20
> "server=3DMyServer;Initial Catalog=3DMyDatabase;Integrated =
Security=3DTRUE"=20
>=20
> I get the same thing when I use "Integrated Security=3DSSPI" as well.=20
>=20
> Here is the error I get when I try to use a user id:=20
> Login failed for user '{user name}'.=20
>=20
> The funny thing about it is that the user name specified above is the =
DBO and it is still rejected.=20
>=20
> One last question; have you ever wanted to give up with computers and =
go back to pen and paper?=20
>=20
> Any futher suggestions would be really great! I really appreciate =
it...=20
>=20
> Dale
|||The login failed for user NULL indicates that the account that is
attempting to make the connection is unknown to SQL Server. Look at the IIS
configuration to verify that the user that IIS is using has a login at the
SQL Server. If you are using anonymous access and the IIS machine is on a
separate machine the NT authentication will generate the Login failed for
user NULL.
I would look at articles:
PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"
http://support.microsoft.com/?id=307002
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/?id=247931
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||The problem you are having may be related to how you've configured
your asp.net application. The app may in fact be attempting to connect
using the aspnet process model account unless you've configured IIS to
use impersonation. There's a pretty good "asp.net security best
practices" whitepaper that bears reading:
http://www.microsoft.com/downloads/r...eleaseID=44047
Your machine.config file has the settings you are using in the
<processModel> section -- the default is to have userName="machine",
which means that the aspnet windows account is being used.
In the meantime, try enabling the aspnet windows login in SQLS and
give it the necessary database access. This should work with the
integrated security=sspi setting in your connection string.
--Mary
On Fri, 16 Apr 2004 10:06:03 -0700, "Dale"
<anonymous@.discussions.microsoft.com> wrote:
>Hello,
>I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do an insert to can't validate my login. I've verified that the account does have permissions to access the SQL DB and that everything is granted as far as the INSERT, UPDAT
E, etc...
>I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and Windows).
>I've tried all the suggestions from the "connectionstrings.com" website and I'm still getting errors stating that the login failed.
>Here is a copy of the error:
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
>The above error is happening when I use the connection string of:
>"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
>I get the same thing when I use "Integrated Security=SSPI" as well.
>Here is the error I get when I try to use a user id:
>Login failed for user '{user name}'.
>The funny thing about it is that the user name specified above is the DBO and it is still rejected.
>One last question; have you ever wanted to give up with computers and go back to pen and paper?
>Any futher suggestions would be really great! I really appreciate it...
>Dale
I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do an insert to can't validate my login. I've verified that the account does have permissions to access the SQL DB and that everything is granted as far as the INSERT, UPDATE
, etc...
I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and Windows).
I've tried all the suggestions from the "connectionstrings.com" website and I'm still getting errors stating that the login failed.
Here is a copy of the error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
The above error is happening when I use the connection string of:
"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
I get the same thing when I use "Integrated Security=SSPI" as well.
Here is the error I get when I try to use a user id:
Login failed for user '{user name}'.
The funny thing about it is that the user name specified above is the DBO and it is still rejected.
One last question; have you ever wanted to give up with computers and go back to pen and paper?
Any futher suggestions would be really great! I really appreciate it...
Dale
DBO is not a login, it is a user.
Try connecting with sa (and the sa password). I am guessing that you =
will have better results.
NOTE: You should probably create a login that you will use to connect to =
the database from your web app. If you are planning on using stored =
procedures (a good idea, by the way) they will not need any permissions =
in the database (other than "execute" on the stored procedures used by =
your app).
--=20
Keith
"Dale" <anonymous@.discussions.microsoft.com> wrote in message =
news:1025EFC0-546F-4F76-96CD-75B12AA30E03@.microsoft.com...
> Hello,=20
>=20
> I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm =
trying to do an insert to can't validate my login. I've verified that =
the account does have permissions to access the SQL DB and that =
everything is granted as far as the INSERT, UPDATE, etc...=20
>=20
> I have my SQL Server (SQL2K) set up for mixed authentication (both SQL =
and Windows).=20
>=20
> I've tried all the suggestions from the "connectionstrings.com" =
website and I'm still getting errors stating that the login failed.=20
>=20
> Here is a copy of the error:=20
> Login failed for user '(null)'. Reason: Not associated with a trusted =
SQL Server connection.=20
>=20
> The above error is happening when I use the connection string of:=20
> "server=3DMyServer;Initial Catalog=3DMyDatabase;Integrated =
Security=3DTRUE"=20
>=20
> I get the same thing when I use "Integrated Security=3DSSPI" as well.=20
>=20
> Here is the error I get when I try to use a user id:=20
> Login failed for user '{user name}'.=20
>=20
> The funny thing about it is that the user name specified above is the =
DBO and it is still rejected.=20
>=20
> One last question; have you ever wanted to give up with computers and =
go back to pen and paper?
=20>=20
> Any futher suggestions would be really great! I really appreciate =
it...=20
>=20
> Dale
|||The login failed for user NULL indicates that the account that is
attempting to make the connection is unknown to SQL Server. Look at the IIS
configuration to verify that the user that IIS is using has a login at the
SQL Server. If you are using anonymous access and the IIS machine is on a
separate machine the NT authentication will generate the Login failed for
user NULL.
I would look at articles:
PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"
http://support.microsoft.com/?id=307002
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/?id=247931
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||The problem you are having may be related to how you've configured
your asp.net application. The app may in fact be attempting to connect
using the aspnet process model account unless you've configured IIS to
use impersonation. There's a pretty good "asp.net security best
practices" whitepaper that bears reading:
http://www.microsoft.com/downloads/r...eleaseID=44047
Your machine.config file has the settings you are using in the
<processModel> section -- the default is to have userName="machine",
which means that the aspnet windows account is being used.
In the meantime, try enabling the aspnet windows login in SQLS and
give it the necessary database access. This should work with the
integrated security=sspi setting in your connection string.
--Mary
On Fri, 16 Apr 2004 10:06:03 -0700, "Dale"
<anonymous@.discussions.microsoft.com> wrote:
>Hello,
>I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do an insert to can't validate my login. I've verified that the account does have permissions to access the SQL DB and that everything is granted as far as the INSERT, UPDAT
E, etc...
>I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and Windows).
>I've tried all the suggestions from the "connectionstrings.com" website and I'm still getting errors stating that the login failed.
>Here is a copy of the error:
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
>The above error is happening when I use the connection string of:
>"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
>I get the same thing when I use "Integrated Security=SSPI" as well.
>Here is the error I get when I try to use a user id:
>Login failed for user '{user name}'.
>The funny thing about it is that the user name specified above is the DBO and it is still rejected.
>One last question; have you ever wanted to give up with computers and go back to pen and paper?
>Any futher suggestions would be really great! I really appreciate it...
>Dale
ADO.NET connection to SQL fails
Hello,
I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm tryin
g to do an insert to can't validate my login. I've verified that the account
does have permissions to access the SQL DB and that everything is granted a
s far as the INSERT, UPDATE
, etc...
I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and W
indows).
I've tried all the suggestions from the "connectionstrings.com" website and
I'm still getting errors stating that the login failed.
Here is a copy of the error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Se
rver connection.
The above error is happening when I use the connection string of:
"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
I get the same thing when I use "Integrated Security=SSPI" as well.
Here is the error I get when I try to use a user id:
Login failed for user '{user name}'.
The funny thing about it is that the user name specified above is the DBO an
d it is still rejected.
One last question; have you ever wanted to give up with computers and go bac
k to pen and paper?
Any futher suggestions would be really great! I really appreciate it...
DaleDBO is not a login, it is a user.
Try connecting with sa (and the sa password). I am guessing that you =
will have better results.
NOTE: You should probably create a login that you will use to connect to =
the database from your web app. If you are planning on using stored =
procedures (a good idea, by the way) they will not need any permissions =
in the database (other than "execute" on the stored procedures used by =
your app).
--=20
Keith
"Dale" <anonymous@.discussions.microsoft.com> wrote in message =
news:1025EFC0-546F-4F76-96CD-75B12AA30E03@.microsoft.com...
> Hello,=20
>=20
> I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm =
trying to do an insert to can't validate my login. I've verified that =
the account does have permissions to access the SQL DB and that =
everything is granted as far as the INSERT, UPDATE, etc...=20
>=20
> I have my SQL Server (SQL2K) set up for mixed authentication (both SQL =
and Windows).=20
>=20
> I've tried all the suggestions from the "connectionstrings.com" =
website and I'm still getting errors stating that the login failed.=20
>=20
> Here is a copy of the error:=20
> Login failed for user '(null)'. Reason: Not associated with a trusted =
SQL Server connection.=20
>=20
> The above error is happening when I use the connection string of:=20
> "server=3DMyServer;Initial Catalog=3DMyDatabase;Integrated =
Security=3DTRUE"=20
>=20
> I get the same thing when I use "Integrated Security=3DSSPI" as well.=20
>=20
> Here is the error I get when I try to use a user id:=20
> Login failed for user '{user name}'.=20
>=20
> The funny thing about it is that the user name specified above is the =
DBO and it is still rejected.=20
>=20
> One last question; have you ever wanted to give up with computers and =
go back to pen and paper?=20
>=20
> Any futher suggestions would be really great! I really appreciate =
it...=20
>=20
> Dale|||The login failed for user NULL indicates that the account that is
attempting to make the connection is unknown to SQL Server. Look at the IIS
configuration to verify that the user that IIS is using has a login at the
SQL Server. If you are using anonymous access and the IIS machine is on a
separate machine the NT authentication will generate the Login failed for
user NULL.
I would look at articles:
PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"
http://support.microsoft.com/?id=307002
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/?id=247931
Rand
This posting is provided "as is" with no warranties and confers no rights.|||The problem you are having may be related to how you've configured
your asp.net application. The app may in fact be attempting to connect
using the aspnet process model account unless you've configured IIS to
use impersonation. There's a pretty good "asp.net security best
practices" whitepaper that bears reading:
http://www.microsoft.com/downloads/...ReleaseID=44047
Your machine.config file has the settings you are using in the
<processModel> section -- the default is to have userName="machine",
which means that the aspnet windows account is being used.
In the meantime, try enabling the aspnet windows login in SQLS and
give it the necessary database access. This should work with the
integrated security=sspi setting in your connection string.
--Mary
On Fri, 16 Apr 2004 10:06:03 -0700, "Dale"
<anonymous@.discussions.microsoft.com> wrote:
>Hello,
>I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do
an insert to can't validate my login. I've verified that the account does have perm
issions to access the SQL DB and that everything is granted as far as the INSERT, UP
DAT
E, etc...
>I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and
Windows).
>I've tried all the suggestions from the "connectionstrings.com" website and
I'm still getting errors stating that the login failed.
>Here is a copy of the error:
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL S
erver connection.
>The above error is happening when I use the connection string of:
>"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
>I get the same thing when I use "Integrated Security=SSPI" as well.
>Here is the error I get when I try to use a user id:
>Login failed for user '{user name}'.
>The funny thing about it is that the user name specified above is the DBO a
nd it is still rejected.
>One last question; have you ever wanted to give up with computers and go ba
ck to pen and paper?
>Any futher suggestions would be really great! I really appreciate it...
>Dale
I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm tryin
g to do an insert to can't validate my login. I've verified that the account
does have permissions to access the SQL DB and that everything is granted a
s far as the INSERT, UPDATE
, etc...
I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and W
indows).
I've tried all the suggestions from the "connectionstrings.com" website and
I'm still getting errors stating that the login failed.
Here is a copy of the error:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Se
rver connection.
The above error is happening when I use the connection string of:
"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
I get the same thing when I use "Integrated Security=SSPI" as well.
Here is the error I get when I try to use a user id:
Login failed for user '{user name}'.
The funny thing about it is that the user name specified above is the DBO an
d it is still rejected.
One last question; have you ever wanted to give up with computers and go bac
k to pen and paper?
Any futher suggestions would be really great! I really appreciate it...
DaleDBO is not a login, it is a user.
Try connecting with sa (and the sa password). I am guessing that you =
will have better results.
NOTE: You should probably create a login that you will use to connect to =
the database from your web app. If you are planning on using stored =
procedures (a good idea, by the way) they will not need any permissions =
in the database (other than "execute" on the stored procedures used by =
your app).
--=20
Keith
"Dale" <anonymous@.discussions.microsoft.com> wrote in message =
news:1025EFC0-546F-4F76-96CD-75B12AA30E03@.microsoft.com...
> Hello,=20
>=20
> I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm =
trying to do an insert to can't validate my login. I've verified that =
the account does have permissions to access the SQL DB and that =
everything is granted as far as the INSERT, UPDATE, etc...=20
>=20
> I have my SQL Server (SQL2K) set up for mixed authentication (both SQL =
and Windows).=20
>=20
> I've tried all the suggestions from the "connectionstrings.com" =
website and I'm still getting errors stating that the login failed.=20
>=20
> Here is a copy of the error:=20
> Login failed for user '(null)'. Reason: Not associated with a trusted =
SQL Server connection.=20
>=20
> The above error is happening when I use the connection string of:=20
> "server=3DMyServer;Initial Catalog=3DMyDatabase;Integrated =
Security=3DTRUE"=20
>=20
> I get the same thing when I use "Integrated Security=3DSSPI" as well.=20
>=20
> Here is the error I get when I try to use a user id:=20
> Login failed for user '{user name}'.=20
>=20
> The funny thing about it is that the user name specified above is the =
DBO and it is still rejected.=20
>=20
> One last question; have you ever wanted to give up with computers and =
go back to pen and paper?
=20>=20
> Any futher suggestions would be really great! I really appreciate =
it...=20
>=20
> Dale|||The login failed for user NULL indicates that the account that is
attempting to make the connection is unknown to SQL Server. Look at the IIS
configuration to verify that the user that IIS is using has a login at the
SQL Server. If you are using anonymous access and the IIS machine is on a
separate machine the NT authentication will generate the Login failed for
user NULL.
I would look at articles:
PRB: ASP/ODBC/SQL Server Error 0x80040E4D "Login Failed for User '(Null)'"
http://support.microsoft.com/?id=307002
INF: Authentication Methods for Connections to SQL Server in Active Server
Pages
http://support.microsoft.com/?id=247931
Rand
This posting is provided "as is" with no warranties and confers no rights.|||The problem you are having may be related to how you've configured
your asp.net application. The app may in fact be attempting to connect
using the aspnet process model account unless you've configured IIS to
use impersonation. There's a pretty good "asp.net security best
practices" whitepaper that bears reading:
http://www.microsoft.com/downloads/...ReleaseID=44047
Your machine.config file has the settings you are using in the
<processModel> section -- the default is to have userName="machine",
which means that the aspnet windows account is being used.
In the meantime, try enabling the aspnet windows login in SQLS and
give it the necessary database access. This should work with the
integrated security=sspi setting in your connection string.
--Mary
On Fri, 16 Apr 2004 10:06:03 -0700, "Dale"
<anonymous@.discussions.microsoft.com> wrote:
>Hello,
>I'm putting together an ASP.NET web form (using VB.NET) and the DB I'm trying to do
an insert to can't validate my login. I've verified that the account does have perm
issions to access the SQL DB and that everything is granted as far as the INSERT, UP
DAT
E, etc...
>I have my SQL Server (SQL2K) set up for mixed authentication (both SQL and
Windows).
>I've tried all the suggestions from the "connectionstrings.com" website and
I'm still getting errors stating that the login failed.
>Here is a copy of the error:
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL S
erver connection.
>The above error is happening when I use the connection string of:
>"server=MyServer;Initial Catalog=MyDatabase;Integrated Security=TRUE"
>I get the same thing when I use "Integrated Security=SSPI" as well.
>Here is the error I get when I try to use a user id:
>Login failed for user '{user name}'.
>The funny thing about it is that the user name specified above is the DBO a
nd it is still rejected.
>One last question; have you ever wanted to give up with computers and go ba
ck to pen and paper?
>Any futher suggestions would be really great! I really appreciate it...
>Dale
Subscribe to:
Posts (Atom)