sql reports: log

Showing posts with label log. Show all posts

Tuesday, March 27, 2012

Can we do Transaction log backup and log shipping on same server.

Hi,
I know that Log Shipping and Transaction log backup cannot be
done on same primary server side by side because i tried it, but i want
possible reason for that.
Next i want to know is that can we Detach and re Atach Log
Shipping Database on secondary server in a Standby mode (as you know
this database is itself in standby mode before detaching).
thanks and regards,
Sajid.What's version are you using?
<csajid@.gmail.com> wrote in message
news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> Hi,
> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
> thanks and regards,
> Sajid.
>|||> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
know, log backups has to be performed in sequence. So if the first log backup is shipped to the
other server, and the second is done by you and not shipped to the server, then the third log backup
done my log shipping won't restore as the second haven't been restored. It is possible that the
built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
thing to do.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
No.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> Hi,
> I know that Log Shipping and Transaction log backup cannot be
> done on same primary server side by side because i tried it, but i want
> possible reason for that.
> Next i want to know is that can we Detach and re Atach Log
> Shipping Database on secondary server in a Standby mode (as you know
> this database is itself in standby mode before detaching).
> thanks and regards,
> Sajid.
>|||Hi All,
Thanks for this reply.
It clears my all doubt.
thanks again.
Regards,
Sajid.
Tibor Karaszi wrote:
> > I know that Log Shipping and Transaction log backup cannot be
> > done on same primary server side by side because i tried it, but i want
> > possible reason for that.
> You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
> know, log backups has to be performed in sequence. So if the first log backup is shipped to the
> other server, and the second is done by you and not shipped to the server, then the third log backup
> done my log shipping won't restore as the second haven't been restored. It is possible that the
> built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
> thing to do.
>
> > Next i want to know is that can we Detach and re Atach Log
> > Shipping Database on secondary server in a Standby mode (as you know
> > this database is itself in standby mode before detaching).
> No.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> > Hi,
> >
> > I know that Log Shipping and Transaction log backup cannot be
> > done on same primary server side by side because i tried it, but i want
> > possible reason for that.
> >
> > Next i want to know is that can we Detach and re Atach Log
> > Shipping Database on secondary server in a Standby mode (as you know
> > this database is itself in standby mode before detaching).
> >
> > thanks and regards,
> > Sajid.
> >|||Hi All,
I want to schedule a job in ms sql server 2000 which will
copy the backup file from one server whose drive is mapped to
destination server where the backup file should get copied.
After copying the file i want to restore it on a same
destination server.
I can restore it with the help of Restore Database with move
option command, but the problem is the backup file is in format like
'sample_db_200605122100.bak' where sample is database name and numbers
indicate the date and time when backup was taken.
Now, i want to make a batch file which will delete the
previous same backup file, copy the backup file and rename it to
standard filename say 'sample_db_backup.bak'.
This batch file i can shedule to run through ms sql server
jobs by using 'xp_cmdshell'.
Please revert me on this ASAP.
Thanks and Regards,
Sajid.
csajid@.gmail.com wrote:
> Hi All,
> Thanks for this reply.
> It clears my all doubt.
> thanks again.
> Regards,
> Sajid.
> Tibor Karaszi wrote:
> > > I know that Log Shipping and Transaction log backup cannot be
> > > done on same primary server side by side because i tried it, but i want
> > > possible reason for that.
> >
> > You can do it, but you don't want to. Log shipping is based on transaction log backups. And as you
> > know, log backups has to be performed in sequence. So if the first log backup is shipped to the
> > other server, and the second is done by you and not shipped to the server, then the third log backup
> > done my log shipping won't restore as the second haven't been restored. It is possible that the
> > built-in log shipping tries somehow to restrict you from such a scenario, which would be a smart
> > thing to do.
> >
> >
> > > Next i want to know is that can we Detach and re Atach Log
> > > Shipping Database on secondary server in a Standby mode (as you know
> > > this database is itself in standby mode before detaching).
> >
> > No.
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > <csajid@.gmail.com> wrote in message news:1147952596.990826.130310@.i39g2000cwa.googlegroups.com...
> > > Hi,
> > >
> > > I know that Log Shipping and Transaction log backup cannot be
> > > done on same primary server side by side because i tried it, but i want
> > > possible reason for that.
> > >
> > > Next i want to know is that can we Detach and re Atach Log
> > > Shipping Database on secondary server in a Standby mode (as you know
> > > this database is itself in standby mode before detaching).
> > >
> > > thanks and regards,
> > > Sajid.
> > >sql

can we change the file size that c2 creates a new trc file at ?

hello there
I want to know if I can have sql server C2 login log to a file till it
reaches 10 megs and switch to another one.
the reason is that I want to ship the logs to a log aggregation software
every hour or so ?
any ideas ?
Hi,
As far as I know we cant change the trace file size after enabling C2 audit.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eSKK8R3dFHA.688@.TK2MSFTNGP14.phx.gbl...
> hello there
> I want to know if I can have sql server C2 login log to a file till it
> reaches 10 megs and switch to another one.
> the reason is that I want to ship the logs to a log aggregation software
> every hour or so ?
> any ideas ?
>

can we change the file size that c2 creates a new trc file at ?

hello there
I want to know if I can have sql server C2 login log to a file till it
reaches 10 megs and switch to another one.
the reason is that I want to ship the logs to a log aggregation software
every hour or so ?
any ideas ?Hi,
As far as I know we cant change the trace file size after enabling C2 audit.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eSKK8R3dFHA.688@.TK2MSFTNGP14.phx.gbl...
> hello there
> I want to know if I can have sql server C2 login log to a file till it
> reaches 10 megs and switch to another one.
> the reason is that I want to ship the logs to a log aggregation software
> every hour or so ?
> any ideas ?
>

Sunday, March 25, 2012

can we change the file size that c2 creates a new trc file at ?

hello there
I want to know if I can have sql server C2 login log to a file till it
reaches 10 megs and switch to another one.
the reason is that I want to ship the logs to a log aggregation software
every hour or so ?
any ideas ?Hi,
As far as I know we cant change the trace file size after enabling C2 audit.
Thanks
Hari
SQL Server MVP
"Simo Sentissi" <msentissi@.rightnow.com> wrote in message
news:eSKK8R3dFHA.688@.TK2MSFTNGP14.phx.gbl...
> hello there
> I want to know if I can have sql server C2 login log to a file till it
> reaches 10 megs and switch to another one.
> the reason is that I want to ship the logs to a log aggregation software
> every hour or so ?
> any ideas ?
>sql

Thursday, March 22, 2012

Can user view objects they only have select permission on?

I have a user that belongs to a role. This role only has select permissions
on 10 views. When I log in as this user via Management Studio I cannot see
the views however I can execute queries against them.
On another server that I did not setup, that I'm supposed to be mimicking
the same security, this same user can see the views.
Any ideas what the difference is?
Thanks!Here's the commands that I'm executing in order:
CREATE ROLE [Customers_ROLE] Authorization dbo
CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] Deny
View
Definition to [Customers_Role]
exec sp_adduser 'phenson', 'phenson', [Customers_Role]
GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
When I log in as phenson I do not see PT_View but I can query on it. I need
to be able to see it.
"SpankyATL" wrote:

> I have a user that belongs to a role. This role only has select permissio
ns
> on 10 views. When I log in as this user via Management Studio I cannot se
e
> the views however I can execute queries against them.
> On another server that I did not setup, that I'm supposed to be mimicking
> the same security, this same user can see the views.
> Any ideas what the difference is?
> Thanks!|||I thought I'd answer my own question for those of you who come across this
some day. I need to remove the "Deny View Definition" portion and that took
care of it. The user was able to see that view and execute it but could not
see the script.
"SpankyATL" wrote:
[vbcol=seagreen]
> Here's the commands that I'm executing in order:
> CREATE ROLE [Customers_ROLE] Authorization dbo
> CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] De
ny View
> Definition to [Customers_Role]
> exec sp_adduser 'phenson', 'phenson', [Customers_Role]
> GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
>
> When I log in as phenson I do not see PT_View but I can query on it. I ne
ed
> to be able to see it.
> "SpankyATL" wrote:
>|||SpankyATL (SpankyATL@.discussions.microsoft.com) writes:
> Here's the commands that I'm executing in order:
> CREATE ROLE [Customers_ROLE] Authorization dbo
> CREATE SCHEMA [Customers_Schema] AUTHORIZATION [Customers_Role] De
ny View
> Definition to [Customers_Role]
> exec sp_adduser 'phenson', 'phenson', [Customers_Role]
> GRANT SELECT ON [dbo].[PT_VIEW] TO [Customers_Role]
>
> When I log in as phenson I do not see PT_View but I can query on it. I
> need to be able to see it.
Why then did you do DENY VIEW DEFINITION to a role that you made phenson a
member of?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I was working from a script that was given to me. The person who developed
it mistakenly thought deny view would only deny the user from viewing the
source code.
"Erland Sommarskog" wrote:

> SpankyATL (SpankyATL@.discussions.microsoft.com) writes:
> Why then did you do DENY VIEW DEFINITION to a role that you made phenson a
> member of?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Tuesday, March 20, 2012

Can t-log size handle large number of deletions?

Hi All,
Im just wondering if there is a way i could find out if the size of my
transaction log will be able to record the number of deletions i will be
performing? Basically i have to delete 325 million rows from a table and my
t-log is, say 20GB. is there a way i can calculate if the t-log is big
enough or will it need to expand?
thx in advance.Well I certainly would not advocate deleting them all in one batch. If you
delete them in smaller batches (say 10K or 100K at a time) it will not only
be faster but you would have the option to backup or truncate the log as you
go along. How many rows in the table do you want to keep. It may be
easier to BCP out the ones to keep, truncate the table and bcp them back in.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> Im just wondering if there is a way i could find out if the size of my
> transaction log will be able to record the number of deletions i will be
> performing? Basically i have to delete 325 million rows from a table and
my
> t-log is, say 20GB. is there a way i can calculate if the t-log is big
> enough or will it need to expand?
> thx in advance.
>|||You are correct in that I dont delete them all in one batch. I delete in
batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
planning to do is just set the model to SIMPLE during the deletion and have
the COMMIT TRAN force the log to be flushed during the automatic checkpoint
(when log is 70% full).
Just thought there would be a way to guesstimate how big a t-log youd need
for certain operations..
thx.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> Well I certainly would not advocate deleting them all in one batch. If
you
> delete them in smaller batches (say 10K or 100K at a time) it will not
only
> be faster but you would have the option to backup or truncate the log as
you
> go along. How many rows in the table do you want to keep. It may be
> easier to BCP out the ones to keep, truncate the table and bcp them back
in.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "M Sandico" <msandico@.muchomail.com> wrote in message
> news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > Im just wondering if there is a way i could find out if the size of my
> > transaction log will be able to record the number of deletions i will be
> > performing? Basically i have to delete 325 million rows from a table and
> my
> > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > enough or will it need to expand?
> >
> > thx in advance.
> >
> >
>|||I don't know of a formula off hand. You would have to account for at least
the amount of data that you are deleting and then some percentage for
overhead etc. What that percentage is I don't really know.
--
Andrew J. Kelly
SQL Server MVP
"M Sandico" <msandico@.muchomail.com> wrote in message
news:ugg192JpDHA.2188@.TK2MSFTNGP11.phx.gbl...
> You are correct in that I dont delete them all in one batch. I delete in
> batches of 1000 with a BEGIN TRAN and COMMIT TRAN as well. What I am
> planning to do is just set the model to SIMPLE during the deletion and
have
> the COMMIT TRAN force the log to be flushed during the automatic
checkpoint
> (when log is 70% full).
> Just thought there would be a way to guesstimate how big a t-log youd need
> for certain operations..
> thx.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e9rVaHJpDHA.360@.TK2MSFTNGP12.phx.gbl...
> > Well I certainly would not advocate deleting them all in one batch. If
> you
> > delete them in smaller batches (say 10K or 100K at a time) it will not
> only
> > be faster but you would have the option to backup or truncate the log as
> you
> > go along. How many rows in the table do you want to keep. It may be
> > easier to BCP out the ones to keep, truncate the table and bcp them back
> in.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "M Sandico" <msandico@.muchomail.com> wrote in message
> > news:%23ZDvAlIpDHA.3688@.TK2MSFTNGP11.phx.gbl...
> > > Hi All,
> > >
> > > Im just wondering if there is a way i could find out if the size of my
> > > transaction log will be able to record the number of deletions i will
be
> > > performing? Basically i have to delete 325 million rows from a table
and
> > my
> > > t-log is, say 20GB. is there a way i can calculate if the t-log is big
> > > enough or will it need to expand?
> > >
> > > thx in advance.
> > >
> > >
> >
> >
>

Monday, March 19, 2012

Can the use of Shrinkfile break the Transaction Log chain?

My Log Shipping process has been up and running fine for about 6 months now.
Then as part of the preparation for upgrading the front end application for
the log shipped database, my partner DBA thought he would help the backup
speed by running a SHRINKFILE over the database files.
It was on or about this time that log shipping stopped working and reported
errors just like the ones you get when the transaction log has been
truncated. Basically it thinks the T Log chain has been broken.
Can SHRINKFILE break the chain? I thought it only compacted the unused file
space?
In my tests log shipping was not sensitive to shrinkfile statements. Perhaps
something else has gone wrong.
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
"GRP" <GRP@.discussions.microsoft.com> wrote in message
news:BD8BC1B6-FD32-43C2-B34E-7CA0397CAC35@.microsoft.com...
> My Log Shipping process has been up and running fine for about 6 months
now.
> Then as part of the preparation for upgrading the front end application
for
> the log shipped database, my partner DBA thought he would help the backup
> speed by running a SHRINKFILE over the database files.
> It was on or about this time that log shipping stopped working and
reported
> errors just like the ones you get when the transaction log has been
> truncated. Basically it thinks the T Log chain has been broken.
> Can SHRINKFILE break the chain? I thought it only compacted the unused
file
> space?
|||Hilary, thanks for taking the time to test this out. The only other
explanation I can think of is that the other DBA truncated the log prior to
the shrinkfile.
"Hilary Cotter" wrote:

> In my tests log shipping was not sensitive to shrinkfile statements. Perhaps
> something else has gone wrong.
> --
> 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
> "GRP" <GRP@.discussions.microsoft.com> wrote in message
> news:BD8BC1B6-FD32-43C2-B34E-7CA0397CAC35@.microsoft.com...
> now.
> for
> reported
> file
>
>

Can the transaction log be turned off?

I have a couple of databases that see a lot of updates (WEB user session state stuff) and I don't care at all if I lose any or all of the data. They don't get backed up and fresh copies can be created in seconds. Is there a way I can turn off the transaction logging to cut down on overhead?

ThanksNo

Why what the problem?

Waht's your recovery model set to?|||Recovery model is 'Simple'. Just thought I'd be able to cut down overhead. I've been getting timeouts and am grasping at straws.|||Originally posted by grahamt
Recovery model is 'Simple'. Just thought I'd be able to cut down overhead. I've been getting timeouts and am grasping at straws.

When you say timeouts, what do you mean by that?

From QA, from the application layer?

Are you blocking yourself (sp_lock)

How many connections are open (sp_who2)

How big is the database?

How often do you take backups?|||Setting recovery model is nothing to do with blocks or timeouts on the database. Ensure there are no issues with network, client connections and query conditions involved which may help causing timeouts.

Whatever the recovery model ensure to maintain & schedule full backup of the database.|||The SQL Server timeouts are reported by the ASP.
A bit of config info.

Hardware

SQL Server 2000
Dual Xeon 2.4GHz CPU
Ultra 320 SCSI controller
Two Ultra 320 18GB, 15000 RPM Hard Disks (C: and D:)
Two GB RAM (1.5GB assigned to SQL Server)
Windows 2000 Server

WEB Server connected to SQL server by Gigabit Ethernet.

Two databases involved. One has a single table of perhaps 2000 records (Session state info for WEB users) and is heavily used (Insert, Delete, and Select). It resides on drive C:.

Second DB residing on drive D: has one Master table and 26 others (A-Z). Master table has about 16000 6K records, Primary key on a varchar(50) and a char(2) column. Other tables run from 1000 to 50000 records 70 bytes/record (PK on a varchar(50) field). No updates are ever done (well, every few months maybe) and the Selects are done using stored procedures, one for the Master table (2 parameters) and 26 for the other tables (1 parameter).

Every few minutes (time varies as does the DB with the DB on drive D: getting the most) the WEB app logs an SQL Server timeout. With this horsepower driving these small DBs (and nothing else running) I wouldn't expect any timeouts at all.

Since I don't care about the data in one table and the data in the other never changes, I don't bother with backups.

Any thoughts?|||What are the timeout settings on SQL & ASP script?
IT may be worth if you enable DBCC DBREINDEX and other maint.plan checks on database which will addup performance.

And also consider network settings and take help of netadmin.|||http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm - a useful guide to troubleshoot timeouts when using SQL & ASP.|||I've been monitoring network traffic with 3COMs Network Monitor and everything seems fine. The WEB server showed high FTP traffic so I've shut that service down but there's no perceptable load on SQL server. Performance monitor shows Disk Idle Time averaging 98% or better on both drives and the CPU load never exceeds 3-4%. I've run the SPs through QA and because they are so basic the execution cost is almost nil. The two SPs are

CREATE PROCEDURE [DBO].[prGetOrigins_A]
@.SomeVar Varchar(50)
AS

SET NOCOUNT ON

SELECT Code, Master FROM A Where SomeVar = @.Somevar
GO

and

CREATE PROCEDURE [DBO].[prGetMasterRec]
@.master varChar(20),
@.code varCHar(3)
AS
SET NOCOUNT ON

SELECT id, code,SomeData,
FROM Masters WHERE Master = @.master AND Code = @.Code
GO

and still I get random timeouts.

Personally I am not concerned. A timeout every 5 minutes or so with 250 users on the WEB server probably shouldn't happen but I can live with it. It's the boss who panics and worries that he may have lost a $5.00 sale. 8-)

Wednesday, March 7, 2012

Can SQL 2005 be changed to accept case-INSENSITIVE passwords???

We have a SQL 2000 server that has been migrated to a SQL 2005 server.
Now when the users log on to the SQL app a lot of them can't logon.
I believe this is due to the SQL passwords being case-insensitve in SQL 2000
and being case-sensitive in 2005. Is there a way to make SQL 2005 passwords
case-insensitive?
Thanks!How did you determine that it is case sensitivity that is causing these
login issues? Are the user names and passwords stored in a table in
the database? Or are the logins defined in SQL Server to use
windows/mixed authentication?
If the user names and passwords are stored in a table in the DB, try
running : "sp_helpsort" in the old and new databases. The result will
tell you if SQL Server 2005 has been set up to use a case sensitive
collation. If it is - you might want to look up how to change
collation so that it is the same as whatever was being used in SQL
Server 2000 - if you want the same behavior in SQL Server 2005.
If defined in SQL Server, run "EXEC sp_change_users_login 'Report'" to
see if any of those logins are out of sync. If they are out of sync,
use this same sp (look it up in BOL) to sync them.
winsysadmin wrote:
> We have a SQL 2000 server that has been migrated to a SQL 2005 server.
> Now when the users log on to the SQL app a lot of them can't logon.
> I believe this is due to the SQL passwords being case-insensitve in SQL 2000
> and being case-sensitive in 2005. Is there a way to make SQL 2005 passwords
> case-insensitive?
> Thanks!|||> Is there a way to make SQL 2005 passwords case-insensitive?
Sorry, but SQL 2005 requires that SQL login passwords be case-sensitive for
security reasons. In SQL 2000, password case sensitivity was determined by
the instance collation. This behavior change is documented in the SQL 2005
Books Online:
<Excerpt
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/65eaafa1-9e06-4264-b547-cbee8013c995.htm">
A password entered by a user must match the password stored in the server.
If a password does not match the password stored in SQL Server, the login
fails. If the precise case of the password characters is forgotten, the
password must be reset.
</Excerpt>
--
Hope this helps.
Dan Guzman
SQL Server MVP
"winsysadmin" <winsysadmin@.discussions.microsoft.com> wrote in message
news:369B36BD-BEDC-4EE9-BFFE-F41502EC2D84@.microsoft.com...
> We have a SQL 2000 server that has been migrated to a SQL 2005 server.
> Now when the users log on to the SQL app a lot of them can't logon.
> I believe this is due to the SQL passwords being case-insensitve in SQL
> 2000
> and being case-sensitive in 2005. Is there a way to make SQL 2005
> passwords
> case-insensitive?
> Thanks!|||Thanks for confirming this for me.
"Dan Guzman" wrote:
> > Is there a way to make SQL 2005 passwords case-insensitive?
> Sorry, but SQL 2005 requires that SQL login passwords be case-sensitive for
> security reasons. In SQL 2000, password case sensitivity was determined by
> the instance collation. This behavior change is documented in the SQL 2005
> Books Online:
> <Excerpt
> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/65eaafa1-9e06-4264-b547-cbee8013c995.htm">
> A password entered by a user must match the password stored in the server.
> If a password does not match the password stored in SQL Server, the login
> fails. If the precise case of the password characters is forgotten, the
> password must be reset.
> </Excerpt>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "winsysadmin" <winsysadmin@.discussions.microsoft.com> wrote in message
> news:369B36BD-BEDC-4EE9-BFFE-F41502EC2D84@.microsoft.com...
> > We have a SQL 2000 server that has been migrated to a SQL 2005 server.
> > Now when the users log on to the SQL app a lot of them can't logon.
> > I believe this is due to the SQL passwords being case-insensitve in SQL
> > 2000
> > and being case-sensitive in 2005. Is there a way to make SQL 2005
> > passwords
> > case-insensitive?
> > Thanks!
>
>|||Thanks, I'll try those commands you mention.
"sqlQuest@.gmail.com" wrote:
> How did you determine that it is case sensitivity that is causing these
> login issues? Are the user names and passwords stored in a table in
> the database? Or are the logins defined in SQL Server to use
> windows/mixed authentication?
> If the user names and passwords are stored in a table in the DB, try
> running : "sp_helpsort" in the old and new databases. The result will
> tell you if SQL Server 2005 has been set up to use a case sensitive
> collation. If it is - you might want to look up how to change
> collation so that it is the same as whatever was being used in SQL
> Server 2000 - if you want the same behavior in SQL Server 2005.
> If defined in SQL Server, run "EXEC sp_change_users_login 'Report'" to
> see if any of those logins are out of sync. If they are out of sync,
> use this same sp (look it up in BOL) to sync them.
>
> winsysadmin wrote:
> > We have a SQL 2000 server that has been migrated to a SQL 2005 server.
> > Now when the users log on to the SQL app a lot of them can't logon.
> > I believe this is due to the SQL passwords being case-insensitve in SQL 2000
> > and being case-sensitive in 2005. Is there a way to make SQL 2005 passwords
> > case-insensitive?
> > Thanks!
>

Can SQL 2005 be changed to accept case-INSENSITIVE passwords???

Sunday, February 19, 2012

Can Select ever be in Xact log?

Can select statements ever go into the transaction log?Selects do not get logged in the tran log. If you want to see what
statements are being executed you should use trace or profiler for that.
Andrew J. Kelly SQL MVP
"Jim Weiler" <lisajimbo@.rcn.com> wrote in message
news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> Can select statements ever go into the transaction log?
>|||Jim
A select into will write to the transaction log, but as Andrew says regular
selects will not.
I once got asked this question at an interview, after answering the
question, the interviewer told me he was sorry but I was wrong and all
selects were written to the transaction log. It’s a bit hard to argue too
strongly with someone interviewing you. I told him I thought he was incorrec
t
and left it at that. He got a couple more questions wrong later in the
interview and I did not get the job because my technical knowledge was not
good enough.
Regards
John
"Andrew J. Kelly" wrote:

> Selects do not get logged in the tran log. If you want to see what
> statements are being executed you should use trace or profiler for that.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
>
>|||Hi John !

> A select into will write to the transaction log
I admit that I haven't used a log reader to investigate the log records for
SELECT INTO (or INSERT
... SELECT for that matter), but I will venture a guess that what is record
ed would be each inserted
row and not the SELECT statement. One could argue that the inserted rows wil
l show you what the
SELECT returned, of course. Splitting hairs, I guess... :-)

> I once got asked this question at an interview, <snip>
I hate it when this happens. My experiences are from MCT tests. I score far
higher on products I
don't know that well, where I only score OK on SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in messag
e
news:11C530BB-73B2-416F-9FED-E0D904B63C2C@.microsoft.com...[vbcol=seagreen]
> Jim
> A select into will write to the transaction log, but as Andrew says regula
r
> selects will not.
> I once got asked this question at an interview, after answering the
> question, the interviewer told me he was sorry but I was wrong and all
> selects were written to the transaction log. It’s a bit hard to argue to
o
> strongly with someone interviewing you. I told him I thought he was incorr
ect
> and left it at that. He got a couple more questions wrong later in the
> interview and I did not get the job because my technical knowledge was not
> good enough.
> Regards
> John
>
> "Andrew J. Kelly" wrote:
>

Can Select ever be in Xact log?

Can select statements ever go into the transaction log?
Selects do not get logged in the tran log. If you want to see what
statements are being executed you should use trace or profiler for that.
Andrew J. Kelly SQL MVP
"Jim Weiler" <lisajimbo@.rcn.com> wrote in message
news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> Can select statements ever go into the transaction log?
>
|||Jim
A select into will write to the transaction log, but as Andrew says regular
selects will not.
I once got asked this question at an interview, after answering the
question, the interviewer told me he was sorry but I was wrong and all
selects were written to the transaction log. It’s a bit hard to argue too
strongly with someone interviewing you. I told him I thought he was incorrect
and left it at that. He got a couple more questions wrong later in the
interview and I did not get the job because my technical knowledge was not
good enough.
Regards
John
"Andrew J. Kelly" wrote:

> Selects do not get logged in the tran log. If you want to see what
> statements are being executed you should use trace or profiler for that.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
>
>
|||Hi John !

> A select into will write to the transaction log
I admit that I haven't used a log reader to investigate the log records for SELECT INTO (or INSERT
... SELECT for that matter), but I will venture a guess that what is recorded would be each inserted
row and not the SELECT statement. One could argue that the inserted rows will show you what the
SELECT returned, of course. Splitting hairs, I guess... :-)

> I once got asked this question at an interview, <snip>
I hate it when this happens. My experiences are from MCT tests. I score far higher on products I
don't know that well, where I only score OK on SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in message
news:11C530BB-73B2-416F-9FED-E0D904B63C2C@.microsoft.com...[vbcol=seagreen]
> Jim
> A select into will write to the transaction log, but as Andrew says regular
> selects will not.
> I once got asked this question at an interview, after answering the
> question, the interviewer told me he was sorry but I was wrong and all
> selects were written to the transaction log. It’s a bit hard to argue too
> strongly with someone interviewing you. I told him I thought he was incorrect
> and left it at that. He got a couple more questions wrong later in the
> interview and I did not get the job because my technical knowledge was not
> good enough.
> Regards
> John
>
> "Andrew J. Kelly" wrote:

Can Select ever be in Xact log?

Can select statements ever go into the transaction log?Selects do not get logged in the tran log. If you want to see what
statements are being executed you should use trace or profiler for that.
--
Andrew J. Kelly SQL MVP
"Jim Weiler" <lisajimbo@.rcn.com> wrote in message
news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> Can select statements ever go into the transaction log?
>|||Jim
A select into will write to the transaction log, but as Andrew says regular
selects will not.
I once got asked this question at an interview, after answering the
question, the interviewer told me he was sorry but I was wrong and all
selects were written to the transaction log. Itâ's a bit hard to argue too
strongly with someone interviewing you. I told him I thought he was incorrect
and left it at that. He got a couple more questions wrong later in the
interview and I did not get the job because my technical knowledge was not
good enough.
Regards
John
"Andrew J. Kelly" wrote:
> Selects do not get logged in the tran log. If you want to see what
> statements are being executed you should use trace or profiler for that.
> --
> Andrew J. Kelly SQL MVP
>
> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
> > Can select statements ever go into the transaction log?
> >
>
>|||Hi John !
> A select into will write to the transaction log
I admit that I haven't used a log reader to investigate the log records for SELECT INTO (or INSERT
... SELECT for that matter), but I will venture a guess that what is recorded would be each inserted
row and not the SELECT statement. One could argue that the inserted rows will show you what the
SELECT returned, of course. Splitting hairs, I guess... :-)
> I once got asked this question at an interview, <snip>
I hate it when this happens. My experiences are from MCT tests. I score far higher on products I
don't know that well, where I only score OK on SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in message
news:11C530BB-73B2-416F-9FED-E0D904B63C2C@.microsoft.com...
> Jim
> A select into will write to the transaction log, but as Andrew says regular
> selects will not.
> I once got asked this question at an interview, after answering the
> question, the interviewer told me he was sorry but I was wrong and all
> selects were written to the transaction log. Itâ's a bit hard to argue too
> strongly with someone interviewing you. I told him I thought he was incorrect
> and left it at that. He got a couple more questions wrong later in the
> interview and I did not get the job because my technical knowledge was not
> good enough.
> Regards
> John
>
> "Andrew J. Kelly" wrote:
>> Selects do not get logged in the tran log. If you want to see what
>> statements are being executed you should use trace or profiler for that.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jim Weiler" <lisajimbo@.rcn.com> wrote in message
>> news:dJSdnfm07fFFuq3eRVn-3A@.rcn.net...
>> > Can select statements ever go into the transaction log?
>> >
>>

Tuesday, February 14, 2012

Can only log into a MSDE ODBC Connection as Administrator

When trying to log into a ODBC connection as a power user on a network we
receive the following error:
Connection failed:
SQLState:'01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNET]ConnectionOpen (/cibbect ()),
Connection failed:
SQLState:'08001'
SQL server Error:178
[microsoft][odbc sql server driver][dbnetlib]sql server does not exist or
access denied.
Change the user to Administrator and they are able to login.
Did you add logins for other users who aren't members of the
local admin group on the box where MSDE is installed? It
sounds like they may not have logins setup in MSDE.
By default, members of the local admins group will have
access. For other users, you need to add logins.
-Sue
On Thu, 27 Oct 2005 19:25:02 -0700, "bw"
<bw@.discussions.microsoft.com> wrote:

>When trying to log into a ODBC connection as a power user on a network we
>receive the following error:
>Connection failed:
>SQLState:'01000'
>SQL Server Error: 53
>[Microsoft][ODBC SQL Server Driver][DBNET]ConnectionOpen (/cibbect ()),
>Connection failed:
>SQLState:'08001'
>SQL server Error:178
>[microsoft][odbc sql server driver][dbnetlib]sql server does not exist or
>access denied.
>Change the user to Administrator and they are able to login.
|||Sue,
Yes I did add them to the system as a user. Then created an MSDE Group.
The Group has the following rights:
GROUP EVERYONE - Read to Program Files
MSDE GROUP DOES NOT HAVE FULL CONTROL, NO DEL FOLDERS, TAKE OWNERSHIP,
CREATE FOLDER OR CHANGE PERMISSION TO THE MSDE/SYSTEM FOLDER OR MSDE/SERVER
FOLDER
USING A NOTEBOOK WITH ENTERPRISE MANAGER WE HAD GIVE THE MSDE GROUP -
PUBLIC, DB_WRITER, DB_READER AND DB_BACKUP OPERATOR
APPLIED SERVICE PACK 2 TO THE SYSTEM THAT IS RUNNING THE MSDE SERVICE AND
NOW THE SYSTEM RUNNING ENTERPRISE MANAGER GETS THE FOLLOWING ERROR WHEN
ATTEMPTING TO CONNECT TO THE SYSTEM MAP DRIVE THAT HAS THE MSDE INSTALLED WE
GET THE FOLLOWING ERROR:
AN ERROR OCCURRED WHILE RECONNECTING TO A LOCAL CONNECTION . MICROSOFT
WINDOWN NETWORK. THE LOCAL DEVICE NAME IS ALREADY IN USE.
CANNOT CONNECT THE ENTERPRISE MANAGER TO THE MSDE DATABASE NOW.
I APPRECIATE YOUR HELP.
"Sue Hoegemeier" wrote:

> Did you add logins for other users who aren't members of the
> local admin group on the box where MSDE is installed? It
> sounds like they may not have logins setup in MSDE.
> By default, members of the local admins group will have
> access. For other users, you need to add logins.
> -Sue
> On Thu, 27 Oct 2005 19:25:02 -0700, "bw"
> <bw@.discussions.microsoft.com> wrote:
>
>
|||No need to yell. Using caps is considered yelling in
newsgroups.
By adding the users as logins in SQL Server, that doesn't
have anything to do with adding them to groups on the box
where MSDE is running.
How exactly are you trying to connect? I think something is
missing here as mapping drives is not necessary, giving
users full control on MSDE folders, etc is generally not
necessary either.
-Sue
On Fri, 28 Oct 2005 15:13:03 -0700, "bw"
<bw@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Yes I did add them to the system as a user. Then created an MSDE Group.
>The Group has the following rights:
> GROUP EVERYONE - Read to Program Files
> MSDE GROUP DOES NOT HAVE FULL CONTROL, NO DEL FOLDERS, TAKE OWNERSHIP,
>CREATE FOLDER OR CHANGE PERMISSION TO THE MSDE/SYSTEM FOLDER OR MSDE/SERVER
>FOLDER
>USING A NOTEBOOK WITH ENTERPRISE MANAGER WE HAD GIVE THE MSDE GROUP -
>PUBLIC, DB_WRITER, DB_READER AND DB_BACKUP OPERATOR
>APPLIED SERVICE PACK 2 TO THE SYSTEM THAT IS RUNNING THE MSDE SERVICE AND
>NOW THE SYSTEM RUNNING ENTERPRISE MANAGER GETS THE FOLLOWING ERROR WHEN
>ATTEMPTING TO CONNECT TO THE SYSTEM MAP DRIVE THAT HAS THE MSDE INSTALLED WE
>GET THE FOLLOWING ERROR:
>AN ERROR OCCURRED WHILE RECONNECTING TO A LOCAL CONNECTION . MICROSOFT
>WINDOWN NETWORK. THE LOCAL DEVICE NAME IS ALREADY IN USE.
>CANNOT CONNECT THE ENTERPRISE MANAGER TO THE MSDE DATABASE NOW.
>I APPRECIATE YOUR HELP.
>
>"Sue Hoegemeier" wrote:
|||I apologize I did not know that caps were yelling, I am happy for the help.
Since we are testing this on our network, I am logging into our domain where
both systems reside. The MSDE is running on an XP workstation and we have
other workstations attempting to log into it. When we go into the ODBC setup
we can see the MSDE instance but cannot log on to it.
Bonnie
"Sue Hoegemeier" wrote:

> No need to yell. Using caps is considered yelling in
> newsgroups.
> By adding the users as logins in SQL Server, that doesn't
> have anything to do with adding them to groups on the box
> where MSDE is running.
> How exactly are you trying to connect? I think something is
> missing here as mapping drives is not necessary, giving
> users full control on MSDE folders, etc is generally not
> necessary either.
> -Sue
> On Fri, 28 Oct 2005 15:13:03 -0700, "bw"
> <bw@.discussions.microsoft.com> wrote:
>
>
|||No problem...figured you just didn't know about the caps
thing. Anyway, check the event logs on the XP workstation
where MSDE is running to see if there are any security
errors. Make sure the MSDE instance is listening on TCP/IP
and that the clients are configure to connect with TCP/IP.
You would probably want to start by running through the
following article - it's long but connectivity issues aren't
real easy to resolve as it can be so many different things.
Potential causes of the "SQL Server does not exist or access
denied" error message
http://support.microsoft.com/?id=328306
-Sue
On Mon, 31 Oct 2005 07:01:07 -0800, "bw"
<bw@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I apologize I did not know that caps were yelling, I am happy for the help.
>Since we are testing this on our network, I am logging into our domain where
>both systems reside. The MSDE is running on an XP workstation and we have
>other workstations attempting to log into it. When we go into the ODBC setup
>we can see the MSDE instance but cannot log on to it.
>Bonnie
>
>"Sue Hoegemeier" wrote:
|||Sue,
We found out we were using the wrong port for MSDE. MSDE was listening on
Port 1143. We were using the SQL default of 1443 Thank you for your help. I
have another question now that this one is resolved. I'll start a new
thread. Thank you again for your help.
"Sue Hoegemeier" wrote:

> No problem...figured you just didn't know about the caps
> thing. Anyway, check the event logs on the XP workstation
> where MSDE is running to see if there are any security
> errors. Make sure the MSDE instance is listening on TCP/IP
> and that the clients are configure to connect with TCP/IP.
> You would probably want to start by running through the
> following article - it's long but connectivity issues aren't
> real easy to resolve as it can be so many different things.
> Potential causes of the "SQL Server does not exist or access
> denied" error message
> http://support.microsoft.com/?id=328306
> -Sue
> On Mon, 31 Oct 2005 07:01:07 -0800, "bw"
> <bw@.discussions.microsoft.com> wrote:
>
>

Can only log into a MSDE ODBC Connection as Administrator

When trying to log into a ODBC connection as a power user on a network we
receive the following error:
Connection failed:
SQLState:'01000'
SQL Server Error: 53
[Microsoft][ODBC SQL Server Driver][DBNET]ConnectionOpen (/cibbe
ct ()),
Connection failed:
SQLState:'08001'
SQL server Error:178
[microsoft][odbc sql server driver][dbnetlib]sql server does not
exist or
access denied.
Change the user to Administrator and they are able to login.Did you add logins for other users who aren't members of the
local admin group on the box where MSDE is installed? It
sounds like they may not have logins setup in MSDE.
By default, members of the local admins group will have
access. For other users, you need to add logins.
-Sue
On Thu, 27 Oct 2005 19:25:02 -0700, "bw"
<bw@.discussions.microsoft.com> wrote:

>When trying to log into a ODBC connection as a power user on a network we
>receive the following error:
>Connection failed:
>SQLState:'01000'
>SQL Server Error: 53
>[Microsoft][ODBC SQL Server Driver][DBNET]ConnectionOpen (/cibb
ect ()),
>Connection failed:
>SQLState:'08001'
>SQL server Error:178
>[microsoft][odbc sql server driver][dbnetlib]sql server does no
t exist or
>access denied.
>Change the user to Administrator and they are able to login.|||Sue,
Yes I did add them to the system as a user. Then created an MSDE Group.
The Group has the following rights:
GROUP EVERYONE - Read to Program Files
MSDE GROUP DOES NOT HAVE FULL CONTROL, NO DEL FOLDERS, TAKE OWNERSHIP,
CREATE FOLDER OR CHANGE PERMISSION TO THE MSDE/SYSTEM FOLDER OR MSDE/SERVER
FOLDER
USING A NOTEBOOK WITH ENTERPRISE MANAGER WE HAD GIVE THE MSDE GROUP -
PUBLIC, DB_WRITER, DB_READER AND DB_BACKUP OPERATOR
APPLIED SERVICE PACK 2 TO THE SYSTEM THAT IS RUNNING THE MSDE SERVICE AND
NOW THE SYSTEM RUNNING ENTERPRISE MANAGER GETS THE FOLLOWING ERROR WHEN
ATTEMPTING TO CONNECT TO THE SYSTEM MAP DRIVE THAT HAS THE MSDE INSTALLED WE
GET THE FOLLOWING ERROR:
AN ERROR OCCURRED WHILE RECONNECTING TO A LOCAL CONNECTION . MICROSOFT
WINDOWN NETWORK. THE LOCAL DEVICE NAME IS ALREADY IN USE.
CANNOT CONNECT THE ENTERPRISE MANAGER TO THE MSDE DATABASE NOW.
I APPRECIATE YOUR HELP.
"Sue Hoegemeier" wrote:

> Did you add logins for other users who aren't members of the
> local admin group on the box where MSDE is installed? It
> sounds like they may not have logins setup in MSDE.
> By default, members of the local admins group will have
> access. For other users, you need to add logins.
> -Sue
> On Thu, 27 Oct 2005 19:25:02 -0700, "bw"
> <bw@.discussions.microsoft.com> wrote:
>
>|||No need to yell. Using caps is considered yelling in
newsgroups.
By adding the users as logins in SQL Server, that doesn't
have anything to do with adding them to groups on the box
where MSDE is running.
How exactly are you trying to connect? I think something is
missing here as mapping drives is not necessary, giving
users full control on MSDE folders, etc is generally not
necessary either.
-Sue
On Fri, 28 Oct 2005 15:13:03 -0700, "bw"
<bw@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Sue,
>Yes I did add them to the system as a user. Then created an MSDE Group.
>The Group has the following rights:
> GROUP EVERYONE - Read to Program Files
> MSDE GROUP DOES NOT HAVE FULL CONTROL, NO DEL FOLDERS, TAKE OWNERSHIP,
>CREATE FOLDER OR CHANGE PERMISSION TO THE MSDE/SYSTEM FOLDER OR MSDE/SERVER
>FOLDER
>USING A NOTEBOOK WITH ENTERPRISE MANAGER WE HAD GIVE THE MSDE GROUP -
>PUBLIC, DB_WRITER, DB_READER AND DB_BACKUP OPERATOR
>APPLIED SERVICE PACK 2 TO THE SYSTEM THAT IS RUNNING THE MSDE SERVICE AND
>NOW THE SYSTEM RUNNING ENTERPRISE MANAGER GETS THE FOLLOWING ERROR WHEN
>ATTEMPTING TO CONNECT TO THE SYSTEM MAP DRIVE THAT HAS THE MSDE INSTALLED W
E
>GET THE FOLLOWING ERROR:
>AN ERROR OCCURRED WHILE RECONNECTING TO A LOCAL CONNECTION . MICROSOFT
>WINDOWN NETWORK. THE LOCAL DEVICE NAME IS ALREADY IN USE.
>CANNOT CONNECT THE ENTERPRISE MANAGER TO THE MSDE DATABASE NOW.
>I APPRECIATE YOUR HELP.
>
>"Sue Hoegemeier" wrote:
>|||I apologize I did not know that caps were yelling, I am happy for the help.
Since we are testing this on our network, I am logging into our domain where
both systems reside. The MSDE is running on an XP workstation and we have
other workstations attempting to log into it. When we go into the ODBC setu
p
we can see the MSDE instance but cannot log on to it.
Bonnie
"Sue Hoegemeier" wrote:

> No need to yell. Using caps is considered yelling in
> newsgroups.
> By adding the users as logins in SQL Server, that doesn't
> have anything to do with adding them to groups on the box
> where MSDE is running.
> How exactly are you trying to connect? I think something is
> missing here as mapping drives is not necessary, giving
> users full control on MSDE folders, etc is generally not
> necessary either.
> -Sue
> On Fri, 28 Oct 2005 15:13:03 -0700, "bw"
> <bw@.discussions.microsoft.com> wrote:
>
>|||No problem...figured you just didn't know about the caps
thing. Anyway, check the event logs on the XP workstation
where MSDE is running to see if there are any security
errors. Make sure the MSDE instance is listening on TCP/IP
and that the clients are configure to connect with TCP/IP.
You would probably want to start by running through the
following article - it's long but connectivity issues aren't
real easy to resolve as it can be so many different things.
Potential causes of the "SQL Server does not exist or access
denied" error message
http://support.microsoft.com/?id=328306
-Sue
On Mon, 31 Oct 2005 07:01:07 -0800, "bw"
<bw@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>I apologize I did not know that caps were yelling, I am happy for the help.
>Since we are testing this on our network, I am logging into our domain wher
e
>both systems reside. The MSDE is running on an XP workstation and we have
>other workstations attempting to log into it. When we go into the ODBC set
up
>we can see the MSDE instance but cannot log on to it.
>Bonnie
>
>"Sue Hoegemeier" wrote:
>|||Sue,
We found out we were using the wrong port for MSDE. MSDE was listening on
Port 1143. We were using the SQL default of 1443 Thank you for your help.
I
have another question now that this one is resolved. I'll start a new
thread. Thank you again for your help.
"Sue Hoegemeier" wrote:

> No problem...figured you just didn't know about the caps
> thing. Anyway, check the event logs on the XP workstation
> where MSDE is running to see if there are any security
> errors. Make sure the MSDE instance is listening on TCP/IP
> and that the clients are configure to connect with TCP/IP.
> You would probably want to start by running through the
> following article - it's long but connectivity issues aren't
> real easy to resolve as it can be so many different things.
> Potential causes of the "SQL Server does not exist or access
> denied" error message
> http://support.microsoft.com/?id=328306
> -Sue
> On Mon, 31 Oct 2005 07:01:07 -0800, "bw"
> <bw@.discussions.microsoft.com> wrote:
>
>

can ommiting log file speed up me and how

Dear friends,
I have a database with simple stucture and tasks but big data sizes (23M
rec, 35G data file).
Most of my data are static and I dn't need transaction facilities. so I set
it to simple mode.
The log file is created and has good size. How can I get ride of it to have
more simplified and
more speed operations.
Thanks
You cannot do as you ask. Every SQL Server database has a transaction log
and DML statements get written there FIRST before being written to the
actual data file and you CANNOT disable this functionality.
If you have a large amount of read-only data, consider putting said data on
read-only filegroups (or make the entire database read only as a database
option). This will definitely help performance, especially the
database-level setting.
TheSQLGuru
President
Indicium Resources, Inc.
"Tarvirdi" <mail@.tarvirdi.com> wrote in message
news:eXabaVnfHHA.5044@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I have a database with simple stucture and tasks but big data sizes (23M
> rec, 35G data file).
> Most of my data are static and I dn't need transaction facilities. so I
> set it to simple mode.
> The log file is created and has good size. How can I get ride of it to
> have more simplified and
> more speed operations.
> Thanks
>
|||Tarvirdi,
Set the database logging to simple and shrink it. I would consider 35GB to
be a medium size database. 500GB drives are in the $100 range according to
pricewatch.com.
Luke
"Tarvirdi" <mail@.tarvirdi.com> wrote in message
news:eXabaVnfHHA.5044@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I have a database with simple stucture and tasks but big data sizes (23M
> rec, 35G data file).
> Most of my data are static and I dn't need transaction facilities. so I
> set it to simple mode.
> The log file is created and has good size. How can I get ride of it to
> have more simplified and
> more speed operations.
> Thanks
>

can ommiting log file speed up me and how

Dear friends,
I have a database with simple stucture and tasks but big data sizes (23M
rec, 35G data file).
Most of my data are static and I dn't need transaction facilities. so I set
it to simple mode.
The log file is created and has good size. How can I get ride of it to have
more simplified and
more speed operations.
ThanksYou cannot do as you ask. Every SQL Server database has a transaction log
and DML statements get written there FIRST before being written to the
actual data file and you CANNOT disable this functionality.
If you have a large amount of read-only data, consider putting said data on
read-only filegroups (or make the entire database read only as a database
option). This will definitely help performance, especially the
database-level setting.
TheSQLGuru
President
Indicium Resources, Inc.
"Tarvirdi" <mail@.tarvirdi.com> wrote in message
news:eXabaVnfHHA.5044@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I have a database with simple stucture and tasks but big data sizes (23M
> rec, 35G data file).
> Most of my data are static and I dn't need transaction facilities. so I
> set it to simple mode.
> The log file is created and has good size. How can I get ride of it to
> have more simplified and
> more speed operations.
> Thanks
>|||Tarvirdi,
Set the database logging to simple and shrink it. I would consider 35GB to
be a medium size database. 500GB drives are in the $100 range according to
pricewatch.com.
Luke
"Tarvirdi" <mail@.tarvirdi.com> wrote in message
news:eXabaVnfHHA.5044@.TK2MSFTNGP05.phx.gbl...
> Dear friends,
> I have a database with simple stucture and tasks but big data sizes (23M
> rec, 35G data file).
> Most of my data are static and I dn't need transaction facilities. so I
> set it to simple mode.
> The log file is created and has good size. How can I get ride of it to
> have more simplified and
> more speed operations.
> Thanks
>

Sunday, February 12, 2012

Can not shrink a Database!

Hi,
I am running SQL 2K
I have a DB that is 6GB.
out of that, 5.5GB is the Trans Log.
I did a backup of the Data and the log.
Now it shows 5.3GB unused, but I cant get that free space to go away!
When I creaded the DB it was 10MB? so I know that is not the problem
any ideas?
thanks George.George,
A log backup wont shrink the files, it would just empty it.To shrink the
files, use DBCC SHRINKFILE.More details here:
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/support/kb/Articles/q272/3/18.asp
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"george d lake" <georgel@.valleyyellowpages.com> wrote in message
news:uf8dznzaDHA.2672@.tk2msftngp13.phx.gbl...
> Hi,
> I am running SQL 2K
> I have a DB that is 6GB.
> out of that, 5.5GB is the Trans Log.
> I did a backup of the Data and the log.
> Now it shows 5.3GB unused, but I cant get that free space to go away!
> When I creaded the DB it was 10MB? so I know that is not the problem
>
> any ideas?
> thanks George.
>|||Ok,
I ran this:
DBCC SHRINKFILE(SystemMon_Log, 140)
And got this:
Cannot shrink log file 2 (SystemMon_Log) because all logical log files are
in use.
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:OOAParzaDHA.2344@.TK2MSFTNGP09.phx.gbl...
> George,
> A log backup wont shrink the files, it would just empty it.To shrink the
> files, use DBCC SHRINKFILE.More details here:
> INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
> http://support.microsoft.com/support/kb/Articles/q272/3/18.asp
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "george d lake" <georgel@.valleyyellowpages.com> wrote in message
> news:uf8dznzaDHA.2672@.tk2msftngp13.phx.gbl...
> > Hi,
> > I am running SQL 2K
> > I have a DB that is 6GB.
> > out of that, 5.5GB is the Trans Log.
> > I did a backup of the Data and the log.
> > Now it shows 5.3GB unused, but I cant get that free space to go
away!
> > When I creaded the DB it was 10MB? so I know that is not the problem
> >
> >
> > any ideas?
> >
> > thanks George.
> >
> >
>|||Ok.
It worked!
"Dinesh.T.K" <tkdinesh@.nospam.mail.tkdinesh.com> wrote in message
news:OOAParzaDHA.2344@.TK2MSFTNGP09.phx.gbl...
> George,
> A log backup wont shrink the files, it would just empty it.To shrink the
> files, use DBCC SHRINKFILE.More details here:
> INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
> http://support.microsoft.com/support/kb/Articles/q272/3/18.asp
> --
> Dinesh.
> SQL Server FAQ at
> http://www.tkdinesh.com
> "george d lake" <georgel@.valleyyellowpages.com> wrote in message
> news:uf8dznzaDHA.2672@.tk2msftngp13.phx.gbl...
> > Hi,
> > I am running SQL 2K
> > I have a DB that is 6GB.
> > out of that, 5.5GB is the Trans Log.
> > I did a backup of the Data and the log.
> > Now it shows 5.3GB unused, but I cant get that free space to go
away!
> > When I creaded the DB it was 10MB? so I know that is not the problem
> >
> >
> > any ideas?
> >
> > thanks George.
> >
> >
>

Friday, February 10, 2012

Can not Log in with windows user after installing active directory

After installing active directory I failed to connect SQL server 2000 with
eather with Active directory user accont and sa.
It's showing a messege unable to connect sql server Mainserver\SQL2000DB
server msg 17, level16 state 1
server does not exist or access denied.
Please helpasif (jossy881@.nospam.com) writes:
> After installing active directory I failed to connect SQL server 2000 with
> eather with Active directory user accont and sa.
> It's showing a messege unable to connect sql server Mainserver\SQL2000DB
> server msg 17, level16 state 1
> server does not exist or access denied.
That message does not anything to do with Active Directory, I would
guess. The message means that you never get in contact with SQL Server.
It could be because it's not running, or because some firewall that is
in the way.
See this KB article for a troubleshooter:
http://support.microsoft.com/defaul...B;EN-US;q328306
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Tuesday, March 27, 2012

Can we do Transaction log backup and log shipping on same server.

Can we do Transaction log backup and log shipping on same server.

can we change the file size that c2 creates a new trc file at ?

can we change the file size that c2 creates a new trc file at ?

Sunday, March 25, 2012

can we change the file size that c2 creates a new trc file at ?

Thursday, March 22, 2012

Can user view objects they only have select permission on?

Tuesday, March 20, 2012

Can t-log size handle large number of deletions?

Monday, March 19, 2012

Can the use of Shrinkfile break the Transaction Log chain?

Can the transaction log be turned off?

Wednesday, March 7, 2012

Can SQL 2005 be changed to accept case-INSENSITIVE passwords???

Can SQL 2005 be changed to accept case-INSENSITIVE passwords???

Sunday, February 19, 2012

Can Select ever be in Xact log?

Can Select ever be in Xact log?

Can Select ever be in Xact log?

Tuesday, February 14, 2012

Can only log into a MSDE ODBC Connection as Administrator

Can only log into a MSDE ODBC Connection as Administrator

can ommiting log file speed up me and how

can ommiting log file speed up me and how

Sunday, February 12, 2012

Can not shrink a Database!

Friday, February 10, 2012

Can not Log in with windows user after installing active directory

sql reports

Blog Archive

About Me