Locked out again?

I have SQL Server 2005 Developer edition installed. I use Windows
Authentication and the user is an admin on this box. For whatever
reason, I was recently locked out of SQL Server. So I uninstalled and
reinstalled then I could get back in. Then once again, I became locked
out. Same procedure and I can get back in. I downloaded and installed
SP1 for SQL Server 2005 last night. Now I'm locked out again. "sa"
never works with a blank or any other password.
Any ideas why this keeps happening and how I can gain access?
Thanks,
BrettI'm running this on Vista. I always turn off account access control.
After installing SP1 for SQL Server and rebooting, access control was
reenabled. This prevented me from logging into SQL Server. I've once
again turned access control. Now I can log in.
Brett|||Make sure that you have configured it for mixed authentication if you want
to use sa. Also, make sure that if you are using Windows authentication
with SSMS that you have that option selected when you login.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"brett" <account@.cygen.com> wrote in message
news:1167499761.263497.179960@.h40g2000cwb.googlegroups.com...
I have SQL Server 2005 Developer edition installed. I use Windows
Authentication and the user is an admin on this box. For whatever
reason, I was recently locked out of SQL Server. So I uninstalled and
reinstalled then I could get back in. Then once again, I became locked
out. Same procedure and I can get back in. I downloaded and installed
SP1 for SQL Server 2005 last night. Now I'm locked out again. "sa"
never works with a blank or any other password.
Any ideas why this keeps happening and how I can gain access?
Thanks,
Brett|||Well did you give the sa acoount a password?
You sure that youre windows account is a member of the local administrator
group?
Greetz,
HDD
"brett" wrote:

> I have SQL Server 2005 Developer edition installed. I use Windows
> Authentication and the user is an admin on this box. For whatever
> reason, I was recently locked out of SQL Server. So I uninstalled and
> reinstalled then I could get back in. Then once again, I became locked
> out. Same procedure and I can get back in. I downloaded and installed
> SP1 for SQL Server 2005 last night. Now I'm locked out again. "sa"
> never works with a blank or any other password.
> Any ideas why this keeps happening and how I can gain access?
> Thanks,
> Brett
>|||Hate_orphaned_users wrote:
> Well did you give the sa acoount a password?
This was never an option during set.

> You sure that youre windows account is a member of the local administrato
r
> group?
Yep|||Tom Moreau wrote:
> Make sure that you have configured it for mixed authentication if you want
> to use sa.
Can it be set to mixed after installation?
Thanks,
Brett|||In Vista by default you are not an admin and as such not part of the
sysadmins group. You need to make yourself an admin, create an account for
yourself in SQL Server and then you can log in without being an admin. This
wasn't an issue with XP because you were generally always an admin on the
box when you logged in. Vista changed this default because running as an
admin made viruses much more nasty. I think that SP2 is supposed to create
this user for you automatically but I don't know if that part of it works in
the current CTP.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"brett" <account@.cygen.com> wrote in message
news:1167501829.564730.89250@.n51g2000cwc.googlegroups.com...
> I'm running this on Vista. I always turn off account access control.
> After installing SP1 for SQL Server and rebooting, access control was
> reenabled. This prevented me from logging into SQL Server. I've once
> again turned access control. Now I can log in.
> Brett
>|||Yes.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"brett" <account@.cygen.com> wrote in message
news:1167505428.307381.19420@.i12g2000cwa.googlegroups.com...
Tom Moreau wrote:
> Make sure that you have configured it for mixed authentication if you want
> to use sa.
Can it be set to mixed after installation?
Thanks,
Brett

Lock problem when inserting and deleting records

Hi all.

I have an application that is using a SQL compact edition database to save/process information.

I run a test that is creating two threads:

1. one is inserting data in Table1

2. the other one is deleting records from Table1

When I run the application, I get some exceptions on both threads saying that the insert/delete could not aquire a lock on the table.

After a while, when I try to connect to the database I get an exception saying that the database file might be corrupted.

Any thoughts?

Thanks.

Hello,

Can you send me the database, and if possible, tell me (or give me) the app, so that, I can see what the issue is? Send it to goteti.udaya.bhanu@.gmail.com. And also, let me know which version of SQL CE are you using (3.1 or 3.0 etc...). Are you using ADO.NET or a native app to do this. Can you send me the code.

Thanks

Udaya.

|||

The db has a very simple structure:

2 tables with strings and datetimes as columns data types.

I am using SQLCE 3.1 version and ADO.NET. So, there is no native call from my code.

Thanks.

Mircea

|||

Hello,

I have tried to repro the issue, with SQLCE 3.1, but, could not. I need one more detail for that, I think. Which version of .NET are you using? Are you using .NET CF or .NET (on a device or on the desktop)?

Thanks

Udaya.

|||

If your both threads are doing DML operations on the same thread at the same time, there is a good chance that you experience locking issues. It could be that both operations need a lock on the same index page and one of them is denied lock for a long time (2000 msec or some thing like that). This looks okay to me. We are trying to improve the experience.

How to solve it? You might solve the above problem by either handling the error or serializing your actions. In the first approach you might want to just catch the error and retry the operation.

But why file corruption? This should not happen. Unless we look into your code, it's difficult to explain. Can you please try using different connection objects in these two threads?

Thanks

Raja [MSFT]

P.S: If this solves your problem, please mark it as answered.

Lock problem when inserting and deleting records

Hi all.

I have an application that is using a SQL compact edition database to save/process information.

I run a test that is creating two threads:

1. one is inserting data in Table1

2. the other one is deleting records from Table1

When I run the application, I get some exceptions on both threads saying that the insert/delete could not aquire a lock on the table.

After a while, when I try to connect to the database I get an exception saying that the database file might be corrupted.

Any thoughts?

Thanks.

Hello,

Can you send me the database, and if possible, tell me (or give me) the app, so that, I can see what the issue is? Send it to goteti.udaya.bhanu@.gmail.com. And also, let me know which version of SQL CE are you using (3.1 or 3.0 etc...). Are you using ADO.NET or a native app to do this. Can you send me the code.

Thanks

Udaya.

|||

The db has a very simple structure:

2 tables with strings and datetimes as columns data types.

I am using SQLCE 3.1 version and ADO.NET. So, there is no native call from my code.

Thanks.

Mircea

|||

Hello,

I have tried to repro the issue, with SQLCE 3.1, but, could not. I need one more detail for that, I think. Which version of .NET are you using? Are you using .NET CF or .NET (on a device or on the desktop)?

Thanks

Udaya.

|||

If your both threads are doing DML operations on the same thread at the same time, there is a good chance that you experience locking issues. It could be that both operations need a lock on the same index page and one of them is denied lock for a long time (2000 msec or some thing like that). This looks okay to me. We are trying to improve the experience.

How to solve it? You might solve the above problem by either handling the error or serializing your actions. In the first approach you might want to just catch the error and retry the operation.

But why file corruption? This should not happen. Unless we look into your code, it's difficult to explain. Can you please try using different connection objects in these two threads?

Thanks

Raja [MSFT]

P.S: If this solves your problem, please mark it as answered.

Lock pages in memory right on IA64 edition

I'm planing to install SQL2000 IA64 on Itanium server with win2003-IA64.
Not sure if I need to add "Lock pages in memory" right to SQL service
account or not. I know it must be used on x86 and is also recommended on x64
systems because they use AWE but on IA64 systems AWE is not used so I would
guess it's not necessary. Am I right?
Tom"Tom" <mcseman@.hotmail.com> wrote in message
news:uFRSdkGOGHA.2036@.TK2MSFTNGP14.phx.gbl...
> I'm planing to install SQL2000 IA64 on Itanium server with win2003-IA64.
> Not sure if I need to add "Lock pages in memory" right to SQL service
> account or not. I know it must be used on x86 and is also recommended on
> x64 systems because they use AWE but on IA64 systems AWE is not used so I
> would guess it's not necessary. Am I right?
>
No. My understanding is that "Lock pages in memory" is recommended on
64-bit SQL because it prevents the OS from paging out SQL Server memory
under pressure. Instead SQL Server will reduce its own memory usage when it
notices the system is under memory pressure.
David

Lock pages in memory right on IA64 edition

I'm planing to install SQL2000 IA64 on Itanium server with win2003-IA64.
Not sure if I need to add "Lock pages in memory" right to SQL service
account or not. I know it must be used on x86 and is also recommended on x64
systems because they use AWE but on IA64 systems AWE is not used so I would
guess it's not necessary. Am I right?
Tom"Tom" <mcseman@.hotmail.com> wrote in message
news:uFRSdkGOGHA.2036@.TK2MSFTNGP14.phx.gbl...
> I'm planing to install SQL2000 IA64 on Itanium server with win2003-IA64.
> Not sure if I need to add "Lock pages in memory" right to SQL service
> account or not. I know it must be used on x86 and is also recommended on
> x64 systems because they use AWE but on IA64 systems AWE is not used so I
> would guess it's not necessary. Am I right?
>
No. My understanding is that "Lock pages in memory" is recommended on
64-bit SQL because it prevents the OS from paging out SQL Server memory
under pressure. Instead SQL Server will reduce its own memory usage when it
notices the system is under memory pressure.
David

Lock pages in memory right on IA64 edition

I'm planing to install SQL2000 IA64 on Itanium server with win2003-IA64.
Not sure if I need to add "Lock pages in memory" right to SQL service
account or not. I know it must be used on x86 and is also recommended on x64
systems because they use AWE but on IA64 systems AWE is not used so I would
guess it's not necessary. Am I right?
Tom
"Tom" <mcseman@.hotmail.com> wrote in message
news:uFRSdkGOGHA.2036@.TK2MSFTNGP14.phx.gbl...
> I'm planing to install SQL2000 IA64 on Itanium server with win2003-IA64.
> Not sure if I need to add "Lock pages in memory" right to SQL service
> account or not. I know it must be used on x86 and is also recommended on
> x64 systems because they use AWE but on IA64 systems AWE is not used so I
> would guess it's not necessary. Am I right?
>
No. My understanding is that "Lock pages in memory" is recommended on
64-bit SQL because it prevents the OS from paging out SQL Server memory
under pressure. Instead SQL Server will reduce its own memory usage when it
notices the system is under memory pressure.
David
sql

lock pages in memory in standard edition

If the lock pages in memory cannot be used in standard edition, what exactly
do I lose ?
Using SQL 2005 x64 standard edition on windows 2003 standard editionAllocated memory if Windows decides to take it. "Trimming the working set"
has caused pain for several of my customers using the same versions as you
reference below
--
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Hassan" <hassan@.test.com> wrote in message
news:%23FJscm8TIHA.4532@.TK2MSFTNGP02.phx.gbl...
> If the lock pages in memory cannot be used in standard edition, what
> exactly do I lose ?
> Using SQL 2005 x64 standard edition on windows 2003 standard edition

lock pages in memory in standard edition

If the lock pages in memory cannot be used in standard edition, what exactly
do I lose ?
Using SQL 2005 x64 standard edition on windows 2003 standard edition
Allocated memory if Windows decides to take it. "Trimming the working set"
has caused pain for several of my customers using the same versions as you
reference below
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Hassan" <hassan@.test.com> wrote in message
news:%23FJscm8TIHA.4532@.TK2MSFTNGP02.phx.gbl...
> If the lock pages in memory cannot be used in standard edition, what
> exactly do I lose ?
> Using SQL 2005 x64 standard edition on windows 2003 standard edition

Lock Pages in Memory

My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
Lock Pages in Memory setting is ignored.
We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
configuration). The only other application sharing SQL Server resources is
CLR.
With the Lock Pages in Memory setting ignored, does this mean that the 14GB
dedicated to SQL Server is fair game, to be taken as desired by other
processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1Still hoping for an answer.
We had a server running 32bit with 2 GB RAM, converted it to 64bit with 16GB
RAM (14GB Max Server Memory) and in less than a week we are getting the
following error in our error log:
"A significant part of sql server process memory has been paged out. This may
result in a performance degradation. Duration: 0 seconds. Working set (KB):
117484, committed (KB): 11856700, memory utilization: 0%."
We have not made any modifications to MemToLeave, so it is at the default
(256MB?). But from what I understand, 64bit gives you a huge extended memory.
So we have not done anything to MemToLeave.
Why do we have memory issues with 16GB RAM, 64bit, but did not have memory
issues on 2GB with 32 bit?
cbrichards wrote:
>My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
>Lock Pages in Memory setting is ignored.
>We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
>configuration). The only other application sharing SQL Server resources is
>CLR.
>With the Lock Pages in Memory setting ignored, does this mean that the 14GB
>dedicated to SQL Server is fair game, to be taken as desired by other
>processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||Hello there!
Have you read the following document?
http://support.microsoft.com/default.aspx/kb/918483
Ekrem Ã?nsoy
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7925eb5963cda@.uwe...
> My understanding is that running 64 bit SQL Server 2005 Standard Edition,
> the
> Lock Pages in Memory setting is ignored.
> We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server
> Memory
> configuration). The only other application sharing SQL Server resources is
> CLR.
> With the Lock Pages in Memory setting ignored, does this mean that the
> 14GB
> dedicated to SQL Server is fair game, to be taken as desired by other
> processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||I highly recommend you read the KB that was posted by Ekrem. I don't
believe Std Edition supports Lock Pages in Memory but you have to understand
there is a huge difference in how memory is utilized between 32 and 64 bit
and even SQL2000 and 2005. These messages are not related to the MemToLeave
area. That memory is pre-allocated and will not be paged out. But unlike in
32 bit the 64 bit SQL Server can utilize all of the memory for things
normally confined to the 2 or 3GB area in a 32 env and can use considerably
more memory than before. For instance the procedure cache would have been
limited to less than 2GB on your old server. Now it can grow at times to as
much as 75% ( exact amount changes with service packs and current conditions
and this is peak, usually won't be more than 50%) of total available memory.
This memory is all dynamic by default and if windows needs more it can call
for memory from SQL Server which will give you the messages you are seeing.
My guess is that you have a lot of adhoc SQL and the proc cache is huge due
to all the non-reusable plans. And or you have operations other than SQL
Server itself running on the server such as SSIS, CLR, Term Services,
Winzip, Notepad etc.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7929227ce194c@.uwe...
> Still hoping for an answer.
> We had a server running 32bit with 2 GB RAM, converted it to 64bit with
> 16GB
> RAM (14GB Max Server Memory) and in less than a week we are getting the
> following error in our error log:
> "A significant part of sql server process memory has been paged out. This
> may
> result in a performance degradation. Duration: 0 seconds. Working set
> (KB):
> 117484, committed (KB): 11856700, memory utilization: 0%."
> We have not made any modifications to MemToLeave, so it is at the default
> (256MB?). But from what I understand, 64bit gives you a huge extended
> memory.
> So we have not done anything to MemToLeave.
> Why do we have memory issues with 16GB RAM, 64bit, but did not have memory
> issues on 2GB with 32 bit?
> cbrichards wrote:
>>My understanding is that running 64 bit SQL Server 2005 Standard Edition,
>>the
>>Lock Pages in Memory setting is ignored.
>>We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server
>>Memory
>>configuration). The only other application sharing SQL Server resources is
>>CLR.
>>With the Lock Pages in Memory setting ignored, does this mean that the
>>14GB
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via http://www.sqlmonster.com
>|||Well, Hello Ekrem!
I have read that document, and that applies to SQL Server 2005 64 bit
Enterprise Edition. In SQL Server 2005 64 bit Standard Edition (which I noted
in my post, is what we run), that Lock Pages in Memory is ignored.
So, I am still, seeking for answers...
Ekrem Ã?nsoy wrote:
>Hello there!
>Have you read the following document?
>http://support.microsoft.com/default.aspx/kb/918483
>> My understanding is that running 64 bit SQL Server 2005 Standard Edition,
>> the
>[quoted text clipped - 9 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||So now the question is, if I cannot lock pages in memory, then is my only
choice to upgrade to Enterprise edition?
cbrichards wrote:
>Well, Hello Ekrem!
>I have read that document, and that applies to SQL Server 2005 64 bit
>Enterprise Edition. In SQL Server 2005 64 bit Standard Edition (which I noted
>in my post, is what we run), that Lock Pages in Memory is ignored.
>So, I am still, seeking for answers...
>>Hello there!
>[quoted text clipped - 6 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||Thanks Andrew for the information.
From what you wrote, I believe our server ran out of memory due to an export
of data from a 250,000,000 row table. This table holds 90 days worth of
archive data and the "adhoc" query was selecting 1 day to export.
I am still perplexed, because this same archive table was (a week ago) on a
32 bit server with 2 GB RAM, and nothing like this ever happened (running out
of memory). The volume of data, number of stored procedures, user connections,
etc., etc. are all the same as when it was on a 32 bit server. One month ago
I performed this same export when it was 32 bit, and while the CPU escalated,
the server did not crater.
I am having difficulty wrapping my arms around the fact that the server did
not crater under 32 bit and 2 GB RAM, but the same operation cratered 64 bit
with 16 GB RAM.
The following came from running DBCC MemoryStatus:
Procedure Cache Value
-- --
TotalProcs 88
TotalPages 2086
InUsePages 70
Any further insights would be appreciated. We are nervous to move forward
with 64 bit Standard Edition with all these unknowns.
Andrew J. Kelly wrote:
>I highly recommend you read the KB that was posted by Ekrem. I don't
>believe Std Edition supports Lock Pages in Memory but you have to understand
>there is a huge difference in how memory is utilized between 32 and 64 bit
>and even SQL2000 and 2005. These messages are not related to the MemToLeave
>area. That memory is pre-allocated and will not be paged out. But unlike in
>32 bit the 64 bit SQL Server can utilize all of the memory for things
>normally confined to the 2 or 3GB area in a 32 env and can use considerably
>more memory than before. For instance the procedure cache would have been
>limited to less than 2GB on your old server. Now it can grow at times to as
>much as 75% ( exact amount changes with service packs and current conditions
>and this is peak, usually won't be more than 50%) of total available memory.
>This memory is all dynamic by default and if windows needs more it can call
>for memory from SQL Server which will give you the messages you are seeing.
>My guess is that you have a lot of adhoc SQL and the proc cache is huge due
>to all the non-reusable plans. And or you have operations other than SQL
>Server itself running on the server such as SSIS, CLR, Term Services,
>Winzip, Notepad etc.
>> Still hoping for an answer.
>[quoted text clipped - 30 lines]
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via http://www.sqlmonster.com|||OK now we are getting somewhere. You keep feeding bits and pieces to us but
it would be great to have a more detailed description of the issue and
circumstances. Exactly how are you doing this data export? Is it SSIS? If
so that explains a lot. SSIS is a totally separate process from SQL Server
and it will use it's own memory space just like another app on the server.
On a 32 bit machine it can only use up to 2GB max but probably a lot less.
On the 64 bit it can use all it wants. Since the memory is dynamic it will
compete with SQL Server for sure if run on the same machine. SSIS like to do
the work totally in memory if at all possible and can use a lot more than
you might expect. If you provide more details we can give a more directed or
intelligent answer. Also if you do something like this routinely it sounds
like you may want to partition the data to suite these needs.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79322e221281d@.uwe...
> Thanks Andrew for the information.
> From what you wrote, I believe our server ran out of memory due to an
> export
> of data from a 250,000,000 row table. This table holds 90 days worth of
> archive data and the "adhoc" query was selecting 1 day to export.
> I am still perplexed, because this same archive table was (a week ago) on
> a
> 32 bit server with 2 GB RAM, and nothing like this ever happened (running
> out
> of memory). The volume of data, number of stored procedures, user
> connections,
> etc., etc. are all the same as when it was on a 32 bit server. One month
> ago
> I performed this same export when it was 32 bit, and while the CPU
> escalated,
> the server did not crater.
> I am having difficulty wrapping my arms around the fact that the server
> did
> not crater under 32 bit and 2 GB RAM, but the same operation cratered 64
> bit
> with 16 GB RAM.
> The following came from running DBCC MemoryStatus:
> Procedure Cache Value
> -- --
> TotalProcs 88
> TotalPages 2086
> InUsePages 70
> Any further insights would be appreciated. We are nervous to move forward
> with 64 bit Standard Edition with all these unknowns.
> Andrew J. Kelly wrote:
>>I highly recommend you read the KB that was posted by Ekrem. I don't
>>believe Std Edition supports Lock Pages in Memory but you have to
>>understand
>>there is a huge difference in how memory is utilized between 32 and 64 bit
>>and even SQL2000 and 2005. These messages are not related to the
>>MemToLeave
>>area. That memory is pre-allocated and will not be paged out. But unlike
>>in
>>32 bit the 64 bit SQL Server can utilize all of the memory for things
>>normally confined to the 2 or 3GB area in a 32 env and can use
>>considerably
>>more memory than before. For instance the procedure cache would have been
>>limited to less than 2GB on your old server. Now it can grow at times to
>>as
>>much as 75% ( exact amount changes with service packs and current
>>conditions
>>and this is peak, usually won't be more than 50%) of total available
>>memory.
>>This memory is all dynamic by default and if windows needs more it can
>>call
>>for memory from SQL Server which will give you the messages you are
>>seeing.
>>My guess is that you have a lot of adhoc SQL and the proc cache is huge
>>due
>>to all the non-reusable plans. And or you have operations other than SQL
>>Server itself running on the server such as SSIS, CLR, Term Services,
>>Winzip, Notepad etc.
>> Still hoping for an answer.
>>[quoted text clipped - 30 lines]
>>dedicated to SQL Server is fair game, to be taken as desired by other
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
> --
> Message posted via http://www.sqlmonster.com
>|||> Is Max Server Memory even needed in this case?
Personally, I'd always set Max Server Memory and Min Server Memory on any
serious instance. Why let the SQL Server process engage in expensive back and
forth memory trading with OS?
Linchi
"cbrichards via SQLMonster.com" wrote:
> My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
> Lock Pages in Memory setting is ignored.
> We have 16GB RAM with 14GB dedicated to SQL Server (as per Max Server Memory
> configuration). The only other application sharing SQL Server resources is
> CLR.
> With the Lock Pages in Memory setting ignored, does this mean that the 14GB
> dedicated to SQL Server is fair game, to be taken as desired by other
> processes whenever needed? Is Max Server Memory even needed in this case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Interesting...
I am using SSIS to perform the export.
This adds more questions:
1. You stated, "On a 32 bit machine it can only use up to 2GB max but
probably a lot less." Why only 2GB and probably a lot less?
2. You stated, "On the 64 bit it can use all it wants. Since the memory is
dynamic..." Is this true even when using Enterprise edition when you can lock
pages in memory?
Andrew J. Kelly wrote:
>OK now we are getting somewhere. You keep feeding bits and pieces to us but
>it would be great to have a more detailed description of the issue and
>circumstances. Exactly how are you doing this data export? Is it SSIS? If
>so that explains a lot. SSIS is a totally separate process from SQL Server
>and it will use it's own memory space just like another app on the server.
>On a 32 bit machine it can only use up to 2GB max but probably a lot less.
>On the 64 bit it can use all it wants. Since the memory is dynamic it will
>compete with SQL Server for sure if run on the same machine. SSIS like to do
>the work totally in memory if at all possible and can use a lot more than
>you might expect. If you provide more details we can give a more directed or
>intelligent answer. Also if you do something like this routinely it sounds
>like you may want to partition the data to suite these needs.
>> Thanks Andrew for the information.
>[quoted text clipped - 65 lines]
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||> This adds more questions:
> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
> probably a lot less." Why only 2GB and probably a lot less?
Well if the system only had 2GB you need memory for other things as well.
But in 32 bit OS by default any app can only use up to 2GB of directly
addressable memory. If you had more than 2GB and the OS was capable of using
PAE and the app was AWE aware it may be able to use more than 2GB. But I
don't think SSIS was AWE aware so on 32 bit I believe it was only able to
use 2GB or 3GB if /3gb was set max. I could be wrong there but I am pretty
sure that is correct.
> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
> dynamic..." Is this true even when using Enterprise edition when you can
> lock
> pages in memory?
No. The purpose of Lock Pages is to prevent something else from stealing or
borrowing those pages once they are allocated. You can achieve a similar
functionality as Linchi mentioned by setting the MIN and MAX to the same
value. Then as long as you start SQL Server, use that much memory and the
memory is available it will keep it once it has used it. But now you have
another potentially serious issue and that is with the memory that is left.
If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the OS
and anything else you run. If you attempt to run that SSIS package again in
this mode you will surely starve the OS of memory and the machine and
everything on it will not be happy. This is one of the main reasons why it
is recommended you run larger SSIS packages on a separate machine.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:79367cc8cda07@.uwe...
> Interesting...
> I am using SSIS to perform the export.
> This adds more questions:
> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
> probably a lot less." Why only 2GB and probably a lot less?
> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
> dynamic..." Is this true even when using Enterprise edition when you can
> lock
> pages in memory?
> Andrew J. Kelly wrote:
>>OK now we are getting somewhere. You keep feeding bits and pieces to us
>>but
>>it would be great to have a more detailed description of the issue and
>>circumstances. Exactly how are you doing this data export? Is it SSIS?
>>If
>>so that explains a lot. SSIS is a totally separate process from SQL Server
>>and it will use it's own memory space just like another app on the server.
>>On a 32 bit machine it can only use up to 2GB max but probably a lot less.
>>On the 64 bit it can use all it wants. Since the memory is dynamic it will
>>compete with SQL Server for sure if run on the same machine. SSIS like to
>>do
>>the work totally in memory if at all possible and can use a lot more than
>>you might expect. If you provide more details we can give a more directed
>>or
>>intelligent answer. Also if you do something like this routinely it
>>sounds
>>like you may want to partition the data to suite these needs.
>> Thanks Andrew for the information.
>>[quoted text clipped - 65 lines]
>>processes whenever needed? Is Max Server Memory even needed in this
>>case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||> 1. So, in my case, if SSIS were to be run on a separate machine, would it
> not
> equally starve the Memory on the separate machine?
If those are the only two processes on the machine they would live much
better together than with SQL Server and lock pages.
> 2. When running this SSIS export on the same machine, (loosely speaking)
> does
> SSIS first consume the 14GB, then go after the 2GB of the OS?
I don't know the allocations work at that level but I would say there are
factors that would make it a DEPENDS type of answer.
> 3. At what point does the 8TB of virtual address space get used.
Virtual address space is always used but if you mean when does it start
swapping to disk? Then generally when all the physical memory is depleted.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:793df7e614c62@.uwe...
> This raises a few more questions:
> 1. So, in my case, if SSIS were to be run on a separate machine, would it
> not
> equally starve the Memory on the separate machine?
> 2. When running this SSIS export on the same machine, (loosely speaking)
> does
> SSIS first consume the 14GB, then go after the 2GB of the OS?
> 3. At what point does the 8TB of virtual address space get used.
> Andrew J. Kelly wrote:
>> This adds more questions:
>> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
>> probably a lot less." Why only 2GB and probably a lot less?
>>Well if the system only had 2GB you need memory for other things as well.
>>But in 32 bit OS by default any app can only use up to 2GB of directly
>>addressable memory. If you had more than 2GB and the OS was capable of
>>using
>>PAE and the app was AWE aware it may be able to use more than 2GB. But I
>>don't think SSIS was AWE aware so on 32 bit I believe it was only able to
>>use 2GB or 3GB if /3gb was set max. I could be wrong there but I am
>>pretty
>>sure that is correct.
>> 2. You stated, "On the 64 bit it can use all it wants. Since the memory
>> is
>> dynamic..." Is this true even when using Enterprise edition when you can
>> lock
>> pages in memory?
>>No. The purpose of Lock Pages is to prevent something else from stealing
>>or
>>borrowing those pages once they are allocated. You can achieve a similar
>>functionality as Linchi mentioned by setting the MIN and MAX to the same
>>value. Then as long as you start SQL Server, use that much memory and the
>>memory is available it will keep it once it has used it. But now you have
>>another potentially serious issue and that is with the memory that is
>>left.
>>If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the
>>OS
>>and anything else you run. If you attempt to run that SSIS package again
>>in
>>this mode you will surely starve the OS of memory and the machine and
>>everything on it will not be happy. This is one of the main reasons why
>>it
>>is recommended you run larger SSIS packages on a separate machine.
>> Interesting...
>>[quoted text clipped - 32 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
> --
> Message posted via http://www.sqlmonster.com
>|||This raises a few more questions:
1. So, in my case, if SSIS were to be run on a separate machine, would it not
equally starve the Memory on the separate machine?
2. When running this SSIS export on the same machine, (loosely speaking) does
SSIS first consume the 14GB, then go after the 2GB of the OS?
3. At what point does the 8TB of virtual address space get used.
Andrew J. Kelly wrote:
>> This adds more questions:
>> 1. You stated, "On a 32 bit machine it can only use up to 2GB max but
>> probably a lot less." Why only 2GB and probably a lot less?
>Well if the system only had 2GB you need memory for other things as well.
>But in 32 bit OS by default any app can only use up to 2GB of directly
>addressable memory. If you had more than 2GB and the OS was capable of using
>PAE and the app was AWE aware it may be able to use more than 2GB. But I
>don't think SSIS was AWE aware so on 32 bit I believe it was only able to
>use 2GB or 3GB if /3gb was set max. I could be wrong there but I am pretty
>sure that is correct.
>> 2. You stated, "On the 64 bit it can use all it wants. Since the memory is
>> dynamic..." Is this true even when using Enterprise edition when you can
>> lock
>> pages in memory?
>No. The purpose of Lock Pages is to prevent something else from stealing or
>borrowing those pages once they are allocated. You can achieve a similar
>functionality as Linchi mentioned by setting the MIN and MAX to the same
>value. Then as long as you start SQL Server, use that much memory and the
>memory is available it will keep it once it has used it. But now you have
>another potentially serious issue and that is with the memory that is left.
>If you have 16GB and you allocate 14GB to SQL that leave you 2GB for the OS
>and anything else you run. If you attempt to run that SSIS package again in
>this mode you will surely starve the OS of memory and the machine and
>everything on it will not be happy. This is one of the main reasons why it
>is recommended you run larger SSIS packages on a separate machine.
>> Interesting...
>[quoted text clipped - 32 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
--
Message posted via http://www.sqlmonster.com|||Thanks for all the info, Andrew.
Hopefully, (for your sake) this is my last set of questions. If I set my
Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS, then,
if I execute an expensive adhoc query directly on the server from Management
Studio, and that query consumes the 14GB, is that query able to consume any
of the OS memory?
On the flip side, if my Max Server Memory is set to 14GB and Min Server
Memory is left at its default (0), then with this configuration, the same
adhoc query is executed above, does that mean the query could consume both
the 14GB as well as the 2GB OS?
Andrew J. Kelly wrote:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would it
>> not
>> equally starve the Memory on the separate machine?
>If those are the only two processes on the machine they would live much
>better together than with SQL Server and lock pages.
>> 2. When running this SSIS export on the same machine, (loosely speaking)
>> does
>> SSIS first consume the 14GB, then go after the 2GB of the OS?
>I don't know the allocations work at that level but I would say there are
>factors that would make it a DEPENDS type of answer.
>> 3. At what point does the 8TB of virtual address space get used.
>Virtual address space is always used but if you mean when does it start
>swapping to disk? Then generally when all the physical memory is depleted.
>> This raises a few more questions:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would it
>[quoted text clipped - 49 lines]
>>>processes whenever needed? Is Max Server Memory even needed in this
>>>case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1|||> Hopefully, (for your sake) this is my last set of questions. If I set my
> Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS,
> then,
> if I execute an expensive adhoc query directly on the server from
> Management
> Studio, and that query consumes the 14GB, is that query able to consume
> any
> of the OS memory?
Well the portion of memory that used to be called MemTo Leave still comes
into play as a section of memory pre-reserved at startup and is not part of
the buffer pool. In 2000 that defaulted to about 384MB (128M for the worker
threads). In 2005 the allocations are a little more complicated but lets
just say 384MB comes right off the top. The MAX Memory is for the buffer
pool only. So you can theoriticallyuse 14GB for bufferpool and 384MB for
Contiguous memory. That will leave about 1.7GB for the OS. Having said that
none of the 1.7GB will be used for a query in SQL Server under the
conditions you outlined.
> On the flip side, if my Max Server Memory is set to 14GB and Min Server
> Memory is left at its default (0), then with this configuration, the same
> adhoc query is executed above, does that mean the query could consume both
> the 14GB as well as the 2GB OS?
No, this still follows the exact same rules. The MIN just states that after
SQL Server grabs that memory it will not give it back to the OS.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:793ea42168c08@.uwe...
> Thanks for all the info, Andrew.
> Hopefully, (for your sake) this is my last set of questions. If I set my
> Min/Max Server Memory settings the same (14GB) and leave 2GB for the OS,
> then,
> if I execute an expensive adhoc query directly on the server from
> Management
> Studio, and that query consumes the 14GB, is that query able to consume
> any
> of the OS memory?
> On the flip side, if my Max Server Memory is set to 14GB and Min Server
> Memory is left at its default (0), then with this configuration, the same
> adhoc query is executed above, does that mean the query could consume both
> the 14GB as well as the 2GB OS?
> Andrew J. Kelly wrote:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would
>> it
>> not
>> equally starve the Memory on the separate machine?
>>If those are the only two processes on the machine they would live much
>>better together than with SQL Server and lock pages.
>> 2. When running this SSIS export on the same machine, (loosely speaking)
>> does
>> SSIS first consume the 14GB, then go after the 2GB of the OS?
>>I don't know the allocations work at that level but I would say there are
>>factors that would make it a DEPENDS type of answer.
>> 3. At what point does the 8TB of virtual address space get used.
>>Virtual address space is always used but if you mean when does it start
>>swapping to disk? Then generally when all the physical memory is
>>depleted.
>> This raises a few more questions:
>> 1. So, in my case, if SSIS were to be run on a separate machine, would
>> it
>>[quoted text clipped - 49 lines]
>>>>processes whenever needed? Is Max Server Memory even needed in
>>>>this
>>>>case?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1
>|||Thanks for the advice Linchi.
I am trying to understand the difference between the ability to lock pages in
memory on 64 bit Enterprise Edition and setting the Min/Max Server Memory
setting the same on a 64 bit Standard Edition.
We are running 64 bit Standard Edition, with 16 GB RAM, and have the Min
Server Memory set to zero and Max Server Memory set to 14336.
We are still experiencing a shortage of memory and I believe it is either a
separate application, such as Reporting Services or SSIS that is depleting
our memory.
So, it this were an Enterprise addition box (instead of Standard Edition),
and I had the ability to Lock Pages in Memory, how would this change my
situation?
Or, with my current Standard Edition situation, how would setting the Min and
Max Server Memory to the same amount change my situation?
Linchi Shea wrote:
>> Is Max Server Memory even needed in this case?
>Personally, I'd always set Max Server Memory and Min Server Memory on any
>serious instance. Why let the SQL Server process engage in expensive back and
>forth memory trading with OS?
>Linchi
>> My understanding is that running 64 bit SQL Server 2005 Standard Edition, the
>> Lock Pages in Memory setting is ignored.
>[quoted text clipped - 6 lines]
>> dedicated to SQL Server is fair game, to be taken as desired by other
>> processes whenever needed? Is Max Server Memory even needed in this case?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200710/1sql

Location of transaction log on another machine

Hi,
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
Steve
Basically, SQL Server doesn't support this. Doing it would be a big performance hit for
modifications. For the full story, check out
http://support.microsoft.com/default...b;en-us;304261
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.c om...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com

Location of transaction log on another machine

Hi,
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
SteveBasically, SQL Server doesn't support this. Doing it would be a big performa
nce hit for
modifications. For the full story, check out
http://support.microsoft.com/defaul...kb;en-us;304261
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.com...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com

Location of transaction log on another machine

Hi,
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
SteveBasically, SQL Server doesn't support this. Doing it would be a big performance hit for
modifications. For the full story, check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.com...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com

location of ODBC drivers for SQL 2k5 x64 in Win Server 2003 x64 ST

Hi,
I've got Win Server 2003 Standard 64bit edition trying to work with SQL 2005
standard 64bit edition however there is a problem.
I need to locate the ODBC driver in Win Ser.
I know where they are in the 32bit edition but not in the 64bit version.
can anyone help?
Thanks
Olgun.Ucurel@.ccsmedia.comIs there a 64 bit ODBC Driver? I didnt think that there was. You might have
to compile your application in x86 or if you are using SSIS or DTS exec you
can use this post...
http://forums.microsoft.com/MSDN/Sh...603814&SiteID=1
Can you use the OLE DB Driver?
C:\WINDOWS\SysWOW64\odbcad32.exe
uses...
odbc32.dll
and
C:\WINDOWS\System32\odbcad32.exe
uses
odbc32.dll
I am sure others will have some thoughts as well.
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Olgun" <Olgun@.discussions.microsoft.com> wrote in message
news:9BA59FAC-D013-4BF4-9700-A97D71D65545@.microsoft.com...
> Hi,
> I've got Win Server 2003 Standard 64bit edition trying to work with SQL
> 2005
> standard 64bit edition however there is a problem.
> I need to locate the ODBC driver in Win Ser.
> I know where they are in the 32bit edition but not in the 64bit version.
> can anyone help?
> Thanks
> Olgun.Ucurel@.ccsmedia.com|||Olgun wrote:
> Hi,
> I've got Win Server 2003 Standard 64bit edition trying to work with SQL 20
05
> standard 64bit edition however there is a problem.
> I need to locate the ODBC driver in Win Ser.
> I know where they are in the 32bit edition but not in the 64bit version.
> can anyone help?
> Thanks
> Olgun.Ucurel@.ccsmedia.com
As far as I know the ODBC driver is not working on 64 bits Windows. You
could use OLEDB instead, which is working fine.
Regards,
lucm

location of ODBC drivers for SQL 2k5 x64 in Win Server 2003 x64 ST

Hi,
I've got Win Server 2003 Standard 64bit edition trying to work with SQL 2005
standard 64bit edition however there is a problem.
I need to locate the ODBC driver in Win Ser.
I know where they are in the 32bit edition but not in the 64bit version.
can anyone help?
Thanks
Olgun.Ucurel@.ccsmedia.com
Is there a 64 bit ODBC Driver? I didnt think that there was. You might have
to compile your application in x86 or if you are using SSIS or DTS exec you
can use this post...
http://forums.microsoft.com/MSDN/Sho...03814&SiteID=1
Can you use the OLE DB Driver?
C:\WINDOWS\SysWOW64\odbcad32.exe
uses...
odbc32.dll
and
C:\WINDOWS\System32\odbcad32.exe
uses
odbc32.dll
I am sure others will have some thoughts as well.
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Olgun" <Olgun@.discussions.microsoft.com> wrote in message
news:9BA59FAC-D013-4BF4-9700-A97D71D65545@.microsoft.com...
> Hi,
> I've got Win Server 2003 Standard 64bit edition trying to work with SQL
> 2005
> standard 64bit edition however there is a problem.
> I need to locate the ODBC driver in Win Ser.
> I know where they are in the 32bit edition but not in the 64bit version.
> can anyone help?
> Thanks
> Olgun.Ucurel@.ccsmedia.com
|||Olgun wrote:
> Hi,
> I've got Win Server 2003 Standard 64bit edition trying to work with SQL 2005
> standard 64bit edition however there is a problem.
> I need to locate the ODBC driver in Win Ser.
> I know where they are in the 32bit edition but not in the 64bit version.
> can anyone help?
> Thanks
> Olgun.Ucurel@.ccsmedia.com
As far as I know the ODBC driver is not working on 64 bits Windows. You
could use OLEDB instead, which is working fine.
Regards,
lucm

Local connection to SQL Server 2005 Express fails

I just installed SQL Server 2005 Express edition, and created an app in
Visual Studio using C#. I get the following error when trying to connect:
"An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server)"
I enabled Named Pipes but left TCP/IP disabled because this is a localhost
connection on the same computer.
Here is the portion of the code I am using to connect:
string dbCString = "Persist Security Info=False;Integrated
Security=true;Trusted_Connection=true;Initial
Catalog=MxData;server=(local)";
SqlConnection ThisConnection = new SqlConnection(dbCString);
ThisConnection.Open();
Thanks in advance for any help.
Perhaps these will help:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:GsCdnUMH0OMdb_DYnZ2dnUVZ_qOdnZ2d@.giganews.com ...
>I just installed SQL Server 2005 Express edition, and created an app in
>Visual Studio using C#. I get the following error when trying to connect:
> "An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the fact
> that under the default settings SQL Server does not allow remote
> connections. (provider: Named Pipes Provider, error: 40 - Could not open a
> connection to SQL Server)"
> I enabled Named Pipes but left TCP/IP disabled because this is a localhost
> connection on the same computer.
> Here is the portion of the code I am using to connect:
> string dbCString = "Persist Security Info=False;Integrated
> Security=true;Trusted_Connection=true;Initial
> Catalog=MxData;server=(local)";
> SqlConnection ThisConnection = new SqlConnection(dbCString);
> ThisConnection.Open();
>
> Thanks in advance for any help.
>
>
>
>
>
|||Thanks for the info, but I did everything suggested in those two articles
and I still get the same connection error. I also tried various alterations
of my connection string with no success. Further research indicates that
this is a common problem, so I wonder why Microsoft ships the Express
edition along with Visual Studio 2005, but makes it so difficult to connect.
If you or anyone else has any other ideas, I would be most grateful.
Thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OOy2vm$EHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Perhaps these will help:
> Configuration -Configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
> Configuration -Connect to SQL Express from "downlevel clients"
> http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "TAC" <tac@.noemail.com> wrote in message
> news:GsCdnUMH0OMdb_DYnZ2dnUVZ_qOdnZ2d@.giganews.com ...
>
|||This is a WAG:
You only need one of these two settings, perhaps having both is causing the
connection string to cause failure.
Integrated Security=true;Trusted_Connection=true;
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:ONCdne2ocahst_LYnZ2dnUVZ_u6dnZ2d@.giganews.com ...
> Thanks for the info, but I did everything suggested in those two articles
> and I still get the same connection error. I also tried various
> alterations of my connection string with no success. Further research
> indicates that this is a common problem, so I wonder why Microsoft ships
> the Express edition along with Visual Studio 2005, but makes it so
> difficult to connect.
> If you or anyone else has any other ideas, I would be most grateful.
> Thanks.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OOy2vm$EHHA.1784@.TK2MSFTNGP06.phx.gbl...
>
|||Thanks for the suggestion, but using only one or the other didn't work,
either.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OD744%23LFHHA.1912@.TK2MSFTNGP03.phx.gbl...
> This is a WAG:
> You only need one of these two settings, perhaps having both is causing
> the connection string to cause failure.
> Integrated Security=true;Trusted_Connection=true;
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "TAC" <tac@.noemail.com> wrote in message
> news:ONCdne2ocahst_LYnZ2dnUVZ_u6dnZ2d@.giganews.com ...
>
|||After a lot of research, here's the answer. The connection string should
read as follows:
"Server=.\\SQLEXPRESS;Initial Catalog=MxData;Integrated Security=SSPI";
Note that you need a dot followed by TWO left slashes in your server name.
BTW, Initial Catalog is whatever database you want to use.
"TAC" <tac@.noemail.com> wrote in message
news:bpadnbRK_s7Mz_LYnZ2dnUVZ_oadnZ2d@.giganews.com ...
> Thanks for the suggestion, but using only one or the other didn't work,
> either.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OD744%23LFHHA.1912@.TK2MSFTNGP03.phx.gbl...
>
|||Thanks for following up your own post. It prevents others from wasting their
time trying to help you after you have solved the problem, and it helps
others when you share your solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:BKidnUwKtpML-PLYnZ2dnUVZ_qmdnZ2d@.giganews.com...
> After a lot of research, here's the answer. The connection string should
> read as follows:
> "Server=.\\SQLEXPRESS;Initial Catalog=MxData;Integrated Security=SSPI";
> Note that you need a dot followed by TWO left slashes in your server name.
> BTW, Initial Catalog is whatever database you want to use.
>
> "TAC" <tac@.noemail.com> wrote in message
> news:bpadnbRK_s7Mz_LYnZ2dnUVZ_oadnZ2d@.giganews.com ...
>

Local connection to SQL Server 2005 Express fails

I just installed SQL Server 2005 Express edition, and created an app in
Visual Studio using C#. I get the following error when trying to connect:
"An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to
SQL Server)"
I enabled Named Pipes but left TCP/IP disabled because this is a localhost
connection on the same computer.
Here is the portion of the code I am using to connect:
string dbCString = "Persist Security Info=False;Integrated
Security=true;Trusted_Connection=true;In
itial
Catalog=MxData;server=(local)";
SqlConnection ThisConnection = new SqlConnection(dbCString);
ThisConnection.Open();
Thanks in advance for any help.Perhaps these will help:
Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/defaul...kb;EN-US;914277
Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/ar.../23/192044.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:GsCdnUMH0OMdb_DYnZ2dnUVZ_qOdnZ2d@.gi
ganews.com...
>I just installed SQL Server 2005 Express edition, and created an app in
>Visual Studio using C#. I get the following error when trying to connect:
> "An error has occurred while establishing a connection to the server.
> When connecting to SQL Server 2005, this failure may be caused by the fact
> that under the default settings SQL Server does not allow remote
> connections. (provider: Named Pipes Provider, error: 40 - Could not open a
> connection to SQL Server)"
> I enabled Named Pipes but left TCP/IP disabled because this is a localhost
> connection on the same computer.
> Here is the portion of the code I am using to connect:
> string dbCString = "Persist Security Info=False;Integrated
> Security=true;Trusted_Connection=true;In
itial
> Catalog=MxData;server=(local)";
> SqlConnection ThisConnection = new SqlConnection(dbCString);
> ThisConnection.Open();
>
> Thanks in advance for any help.
>
>
>
>
>|||Thanks for the info, but I did everything suggested in those two articles
and I still get the same connection error. I also tried various alterations
of my connection string with no success. Further research indicates that
this is a common problem, so I wonder why Microsoft ships the Express
edition along with Visual Studio 2005, but makes it so difficult to connect.
If you or anyone else has any other ideas, I would be most grateful.
Thanks.
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:OOy2vm$EHHA.1784@.TK2MSFTNGP06.phx.gbl...
> Perhaps these will help:
> Configuration -Configure SQL Server 2005 to allow remote connections
> http://support.microsoft.com/defaul...kb;EN-US;914277
> Configuration -Connect to SQL Express from "downlevel clients"
> http://blogs.msdn.com/sqlexpress/ar.../23/192044.aspx
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to
> the top yourself.
> - H. Norman Schwarzkopf
>
> "TAC" <tac@.noemail.com> wrote in message
> news:GsCdnUMH0OMdb_DYnZ2dnUVZ_qOdnZ2d@.gi
ganews.com...
>|||This is a WAG:
You only need one of these two settings, perhaps having both is causing the
connection string to cause failure.
Integrated Security=true;Trusted_Connection=true;
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:ONCdne2ocahst_LYnZ2dnUVZ_u6dnZ2d@.gi
ganews.com...
> Thanks for the info, but I did everything suggested in those two articles
> and I still get the same connection error. I also tried various
> alterations of my connection string with no success. Further research
> indicates that this is a common problem, so I wonder why Microsoft ships
> the Express edition along with Visual Studio 2005, but makes it so
> difficult to connect.
> If you or anyone else has any other ideas, I would be most grateful.
> Thanks.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OOy2vm$EHHA.1784@.TK2MSFTNGP06.phx.gbl...
>|||After a lot of research, here's the answer. The connection string should
read as follows:
"Server=.\\SQLEXPRESS;Initial Catalog=MxData;Integrated Security=SSPI";
Note that you need a dot followed by TWO left slashes in your server name.
BTW, Initial Catalog is whatever database you want to use.
"TAC" <tac@.noemail.com> wrote in message
news:bpadnbRK_s7Mz_LYnZ2dnUVZ_oadnZ2d@.gi
ganews.com...
> Thanks for the suggestion, but using only one or the other didn't work,
> either.
>
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:OD744%23LFHHA.1912@.TK2MSFTNGP03.phx.gbl...
>|||Thanks for following up your own post. It prevents others from wasting their
time trying to help you after you have solved the problem, and it helps
others when you share your solution.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"TAC" <tac@.noemail.com> wrote in message
news:BKidnUwKtpML-PLYnZ2dnUVZ_qmdnZ2d@.giganews.com...
> After a lot of research, here's the answer. The connection string should
> read as follows:
> "Server=.\\SQLEXPRESS;Initial Catalog=MxData;Integrated Security=SSPI";
> Note that you need a dot followed by TWO left slashes in your server name.
> BTW, Initial Catalog is whatever database you want to use.
>
> "TAC" <tac@.noemail.com> wrote in message
> news:bpadnbRK_s7Mz_LYnZ2dnUVZ_oadnZ2d@.gi
ganews.com...
>