Correct Memory Configuration for 4GB/W2K3 Enterprise

Hello all,
I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of system
memory. SQL never uses more than 2GB of system memory. What is the correct
configuration of the 3GB and PAE switches in the boot.ini file?
Roy Love
SupportOnline.BIZ
Roy,
From the SQL BOL:
To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
support a 3-GB virtual address space, you must add the /3gb parameter to the
boot.ini file. This allows user applications to address 3 GB of virtual
memory and reserves 1 GB of virtual memory for the operating system.
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
> Hello all,
> I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
> system
> memory. SQL never uses more than 2GB of system memory. What is the
> correct
> configuration of the 3GB and PAE switches in the boot.ini file?
> --
> Roy Love
> SupportOnline.BIZ
|||I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
setting AWE to the amount of memory in the server. You mentioned virtural
memory, I am talking about physical memory.
thanks,
Roy Love
SupportOnline.BIZ
"Jerry Spivey" wrote:

> Roy,
> From the SQL BOL:
> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
> support a 3-GB virtual address space, you must add the /3gb parameter to the
> boot.ini file. This allows user applications to address 3 GB of virtual
> memory and reserves 1 GB of virtual memory for the operating system.
> HTH
> Jerry
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>
>
|||Roy,
Might take a look at:
http://support.microsoft.com/default...274750&sd=tech
and
http://support.microsoft.com/kb/283037/
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...[vbcol=seagreen]
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
|||If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memory. I think that 2Gb is
for the buffer pool (data cache, cached plans etc), and MemToLeave area is outside of that. So,
*possibly* you could tweak out a bit more using PAE and AWE:
Today (roughly):
BP: 1.7 GB
MemToLeave: 0.3 GB
With AWE and PAE:
BP: 2 GB
MemToLeave 0.3 GB
Here's a good place to learn more about how SQL Server uses memory, AWE etc.:
http://blogs.msdn.com/slavao/
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...[vbcol=seagreen]
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
|||Is Windows seeing the full 4GB?
Some raid cards (hot swap pci etc) can "steal" some memory unless you use
/PAE.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23BPfWr70FHA.3376@.TK2MSFTNGP14.phx.gbl...
> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB
> memory. I think that 2Gb is for the buffer pool (data cache, cached plans
> etc), and MemToLeave area is outside of that. So, *possibly* you could
> tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE
> etc.: http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>
|||Thanks Tibor,
I am running SQL2000 Enterprise Edition, not SE.
Regards,
Roy Love
SupportOnline.BIZ
"Tibor Karaszi" wrote:

> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memory. I think that 2Gb is
> for the buffer pool (data cache, cached plans etc), and MemToLeave area is outside of that. So,
> *possibly* you could tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE etc.:
> http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>
|||Then you need either the /3GB switch or PAE and AWE. Aside, all memory in Windows is virtual memory,
An application doesn't address memory directly, it goes by the OS memory manager. Here's a start:
http://support.microsoft.com/default...b;en-us;274750
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:76402F72-A1B6-466F-802F-234A1033E0AE@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor,
> I am running SQL2000 Enterprise Edition, not SE.
> Regards,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Tibor Karaszi" wrote:

Correct Memory Configuration for 4GB/W2K3 Enterprise

Hello all,
I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of system
memory. SQL never uses more than 2GB of system memory. What is the correct
configuration of the 3GB and PAE switches in the boot.ini file?
--
Roy Love
SupportOnline.BIZRoy,
From the SQL BOL:
To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
support a 3-GB virtual address space, you must add the /3gb parameter to the
boot.ini file. This allows user applications to address 3 GB of virtual
memory and reserves 1 GB of virtual memory for the operating system.
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
> Hello all,
> I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
> system
> memory. SQL never uses more than 2GB of system memory. What is the
> correct
> configuration of the 3GB and PAE switches in the boot.ini file?
> --
> Roy Love
> SupportOnline.BIZ|||I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
setting AWE to the amount of memory in the server. You mentioned virtural
memory, I am talking about physical memory.
thanks,
--
Roy Love
SupportOnline.BIZ
"Jerry Spivey" wrote:
> Roy,
> From the SQL BOL:
> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
> support a 3-GB virtual address space, you must add the /3gb parameter to the
> boot.ini file. This allows user applications to address 3 GB of virtual
> memory and reserves 1 GB of virtual memory for the operating system.
> HTH
> Jerry
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
> > Hello all,
> >
> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
> > system
> > memory. SQL never uses more than 2GB of system memory. What is the
> > correct
> > configuration of the 3GB and PAE switches in the boot.ini file?
> > --
> > Roy Love
> > SupportOnline.BIZ
>
>|||Roy,
Might take a look at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750&sd=tech
and
http://support.microsoft.com/kb/283037/
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
>> Roy,
>> From the SQL BOL:
>> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server
>> to
>> support a 3-GB virtual address space, you must add the /3gb parameter to
>> the
>> boot.ini file. This allows user applications to address 3 GB of virtual
>> memory and reserves 1 GB of virtual memory for the operating system.
>> HTH
>> Jerry
>> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
>> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>> > Hello all,
>> >
>> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
>> > system
>> > memory. SQL never uses more than 2GB of system memory. What is the
>> > correct
>> > configuration of the 3GB and PAE switches in the boot.ini file?
>> > --
>> > Roy Love
>> > SupportOnline.BIZ
>>|||If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memory. I think that 2Gb is
for the buffer pool (data cache, cached plans etc), and MemToLeave area is outside of that. So,
*possibly* you could tweak out a bit more using PAE and AWE:
Today (roughly):
BP: 1.7 GB
MemToLeave: 0.3 GB
With AWE and PAE:
BP: 2 GB
MemToLeave 0.3 GB
Here's a good place to learn more about how SQL Server uses memory, AWE etc.:
http://blogs.msdn.com/slavao/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
>> Roy,
>> From the SQL BOL:
>> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
>> support a 3-GB virtual address space, you must add the /3gb parameter to the
>> boot.ini file. This allows user applications to address 3 GB of virtual
>> memory and reserves 1 GB of virtual memory for the operating system.
>> HTH
>> Jerry
>> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
>> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>> > Hello all,
>> >
>> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
>> > system
>> > memory. SQL never uses more than 2GB of system memory. What is the
>> > correct
>> > configuration of the 3GB and PAE switches in the boot.ini file?
>> > --
>> > Roy Love
>> > SupportOnline.BIZ
>>|||Is Windows seeing the full 4GB?
Some raid cards (hot swap pci etc) can "steal" some memory unless you use
/PAE.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23BPfWr70FHA.3376@.TK2MSFTNGP14.phx.gbl...
> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB
> memory. I think that 2Gb is for the buffer pool (data cache, cached plans
> etc), and MemToLeave area is outside of that. So, *possibly* you could
> tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE
> etc.: http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
>> setting AWE to the amount of memory in the server. You mentioned
>> virtural
>> memory, I am talking about physical memory.
>> thanks,
>> --
>> Roy Love
>> SupportOnline.BIZ
>>
>> "Jerry Spivey" wrote:
>> Roy,
>> From the SQL BOL:
>> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter
>> Server to
>> support a 3-GB virtual address space, you must add the /3gb parameter to
>> the
>> boot.ini file. This allows user applications to address 3 GB of virtual
>> memory and reserves 1 GB of virtual memory for the operating system.
>> HTH
>> Jerry
>> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
>> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>> > Hello all,
>> >
>> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
>> > system
>> > memory. SQL never uses more than 2GB of system memory. What is the
>> > correct
>> > configuration of the 3GB and PAE switches in the boot.ini file?
>> > --
>> > Roy Love
>> > SupportOnline.BIZ
>>
>|||Thanks Tibor,
I am running SQL2000 Enterprise Edition, not SE.
Regards,
--
Roy Love
SupportOnline.BIZ
"Tibor Karaszi" wrote:
> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memory. I think that 2Gb is
> for the buffer pool (data cache, cached plans etc), and MemToLeave area is outside of that. So,
> *possibly* you could tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE etc.:
> http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
> >I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> > setting AWE to the amount of memory in the server. You mentioned virtural
> > memory, I am talking about physical memory.
> >
> > thanks,
> > --
> > Roy Love
> > SupportOnline.BIZ
> >
> >
> > "Jerry Spivey" wrote:
> >
> >> Roy,
> >>
> >> From the SQL BOL:
> >>
> >> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
> >> support a 3-GB virtual address space, you must add the /3gb parameter to the
> >> boot.ini file. This allows user applications to address 3 GB of virtual
> >> memory and reserves 1 GB of virtual memory for the operating system.
> >>
> >> HTH
> >>
> >> Jerry
> >> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> >> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
> >> > Hello all,
> >> >
> >> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
> >> > system
> >> > memory. SQL never uses more than 2GB of system memory. What is the
> >> > correct
> >> > configuration of the 3GB and PAE switches in the boot.ini file?
> >> > --
> >> > Roy Love
> >> > SupportOnline.BIZ
> >>
> >>
> >>
>|||Then you need either the /3GB switch or PAE and AWE. Aside, all memory in Windows is virtual memory,
An application doesn't address memory directly, it goes by the OS memory manager. Here's a start:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274750
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:76402F72-A1B6-466F-802F-234A1033E0AE@.microsoft.com...
> Thanks Tibor,
> I am running SQL2000 Enterprise Edition, not SE.
> Regards,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Tibor Karaszi" wrote:
>> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memory. I think that 2Gb
>> is
>> for the buffer pool (data cache, cached plans etc), and MemToLeave area is outside of that. So,
>> *possibly* you could tweak out a bit more using PAE and AWE:
>> Today (roughly):
>> BP: 1.7 GB
>> MemToLeave: 0.3 GB
>> With AWE and PAE:
>> BP: 2 GB
>> MemToLeave 0.3 GB
>> Here's a good place to learn more about how SQL Server uses memory, AWE etc.:
>> http://blogs.msdn.com/slavao/
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
>> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>> >I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
>> > setting AWE to the amount of memory in the server. You mentioned virtural
>> > memory, I am talking about physical memory.
>> >
>> > thanks,
>> > --
>> > Roy Love
>> > SupportOnline.BIZ
>> >
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Roy,
>> >>
>> >> From the SQL BOL:
>> >>
>> >> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
>> >> support a 3-GB virtual address space, you must add the /3gb parameter to the
>> >> boot.ini file. This allows user applications to address 3 GB of virtual
>> >> memory and reserves 1 GB of virtual memory for the operating system.
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
>> >> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>> >> > Hello all,
>> >> >
>> >> > I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
>> >> > system
>> >> > memory. SQL never uses more than 2GB of system memory. What is the
>> >> > correct
>> >> > configuration of the 3GB and PAE switches in the boot.ini file?
>> >> > --
>> >> > Roy Love
>> >> > SupportOnline.BIZ
>> >>
>> >>
>> >>
>>

Correct Memory Configuration for 4GB/W2K3 Enterprise

Hello all,
I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of system
memory. SQL never uses more than 2GB of system memory. What is the correct
configuration of the 3GB and PAE switches in the boot.ini file?
--
Roy Love
SupportOnline.BIZRoy,
From the SQL BOL:
To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server to
support a 3-GB virtual address space, you must add the /3gb parameter to the
boot.ini file. This allows user applications to address 3 GB of virtual
memory and reserves 1 GB of virtual memory for the operating system.
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
> Hello all,
> I have a SQL2000 installation with Windows2003 Enterprise and 4 GB of
> system
> memory. SQL never uses more than 2GB of system memory. What is the
> correct
> configuration of the 3GB and PAE switches in the boot.ini file?
> --
> Roy Love
> SupportOnline.BIZ|||I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
setting AWE to the amount of memory in the server. You mentioned virtural
memory, I am talking about physical memory.
thanks,
--
Roy Love
SupportOnline.BIZ
"Jerry Spivey" wrote:

> Roy,
> From the SQL BOL:
> To enable Windows 2000 Advanced Server and Windows 2000 Datacenter Server
to
> support a 3-GB virtual address space, you must add the /3gb parameter to t
he
> boot.ini file. This allows user applications to address 3 GB of virtual
> memory and reserves 1 GB of virtual memory for the operating system.
> HTH
> Jerry
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:0F55F3B8-39E0-4C82-8C51-189DC271D915@.microsoft.com...
>
>|||Roy,
Might take a look at:
http://support.microsoft.com/defaul...;274750&sd=tech
and
http://support.microsoft.com/kb/283037/
HTH
Jerry
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...[vbcol=seagreen]
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
>|||If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB memo
ry. I think that 2Gb is
for the buffer pool (data cache, cached plans etc), and MemToLeave area is o
utside of that. So,
*possibly* you could tweak out a bit more using PAE and AWE:
Today (roughly):
BP: 1.7 GB
MemToLeave: 0.3 GB
With AWE and PAE:
BP: 2 GB
MemToLeave 0.3 GB
Here's a good place to learn more about how SQL Server uses memory, AWE etc.
:
http://blogs.msdn.com/slavao/
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...[vbcol=seagreen]
>I am running Windows2003 Server Enterprise, not 2K. SQL also mentions
> setting AWE to the amount of memory in the server. You mentioned virtural
> memory, I am talking about physical memory.
> thanks,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Jerry Spivey" wrote:
>|||Is Windows seeing the full 4GB?
Some raid cards (hot swap pci etc) can "steal" some memory unless you use
/PAE.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23BPfWr70FHA.3376@.TK2MSFTNGP14.phx.gbl...
> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB
> memory. I think that 2Gb is for the buffer pool (data cache, cached plans
> etc), and MemToLeave area is outside of that. So, *possibly* you could
> tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE
> etc.: http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>|||Thanks Tibor,
I am running SQL2000 Enterprise Edition, not SE.
Regards,
--
Roy Love
SupportOnline.BIZ
"Tibor Karaszi" wrote:

> If you are using SQL Server 2000 SE, then SQL Server will not use > 2GB me
mory. I think that 2Gb is
> for the buffer pool (data cache, cached plans etc), and MemToLeave area is
outside of that. So,
> *possibly* you could tweak out a bit more using PAE and AWE:
> Today (roughly):
> BP: 1.7 GB
> MemToLeave: 0.3 GB
> With AWE and PAE:
> BP: 2 GB
> MemToLeave 0.3 GB
> Here's a good place to learn more about how SQL Server uses memory, AWE et
c.:
> http://blogs.msdn.com/slavao/
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
> news:98937CC7-FA07-4952-951A-132FD41E23F6@.microsoft.com...
>|||Then you need either the /3GB switch or PAE and AWE. Aside, all memory in Wi
ndows is virtual memory,
An application doesn't address memory directly, it goes by the OS memory man
ager. Here's a start:
http://support.microsoft.com/defaul...kb;en-us;274750
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RDL1912" <RDL1912@.discussions.microsoft.com> wrote in message
news:76402F72-A1B6-466F-802F-234A1033E0AE@.microsoft.com...[vbcol=seagreen]
> Thanks Tibor,
> I am running SQL2000 Enterprise Edition, not SE.
> Regards,
> --
> Roy Love
> SupportOnline.BIZ
>
> "Tibor Karaszi" wrote:
>

Copying tables

I have a dual boot system with SQL Server 2000 on each operating system. I
would like to copy tables from one system to the other.
The problem is that when one os is running the other is not available. I
could create scripts to recreate all the tables, export data to a text file,
and then import the data into the newly created tables.
Is there an easier way?
TIA
DavidAre you wanting to copy all tables? How about a backup and restore?
Ray Higdon MCSE, MCDBA, CCNA
--
"David Rose" <drose2929@.yahoo.com> wrote in message
news:equf4qAnDHA.2364@.TK2MSFTNGP11.phx.gbl...
> I have a dual boot system with SQL Server 2000 on each operating system.
I
> would like to copy tables from one system to the other.
> The problem is that when one os is running the other is not available. I
> could create scripts to recreate all the tables, export data to a text
file,
> and then import the data into the newly created tables.
> Is there an easier way?
> TIA
> David
>|||Hi
You should be able to attach the databases from SQL Server on second OS
instance to the SQL Server instance on the first, they will always be up to
date.
John
"David Rose" <drose2929@.yahoo.com> wrote in message
news:equf4qAnDHA.2364@.TK2MSFTNGP11.phx.gbl...
> I have a dual boot system with SQL Server 2000 on each operating system.
I
> would like to copy tables from one system to the other.
> The problem is that when one os is running the other is not available. I
> could create scripts to recreate all the tables, export data to a text
file,
> and then import the data into the newly created tables.
> Is there an easier way?
> TIA
> David
>|||Ray,
No, not all the tables. However, I could do that and take what I need after
the restore.
Is there something like writing the tables to a structured storage file?
That would be ideal.
Thanks.
David
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:OAQwZ8AnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Are you wanting to copy all tables? How about a backup and restore?
>
> --
> Ray Higdon MCSE, MCDBA, CCNA
> --
> "David Rose" <drose2929@.yahoo.com> wrote in message
> news:equf4qAnDHA.2364@.TK2MSFTNGP11.phx.gbl...
> > I have a dual boot system with SQL Server 2000 on each operating system.
> I
> > would like to copy tables from one system to the other.
> >
> > The problem is that when one os is running the other is not available.
I
> > could create scripts to recreate all the tables, export data to a text
> file,
> > and then import the data into the newly created tables.
> >
> > Is there an easier way?
> >
> > TIA
> >
> > David
> >
> >
>|||> Is there something like writing the tables to a structured storage file?
> That would be ideal.
Nope. To get at the table level, you would need to generate script to get the table structure and
then BCP or similar for the data. Or use some of the tools that generate INSERT statements from an
existing table:
http://vyaskn.tripod.com/code.htm#inserts
http://rac4sql.net/objectscriptr_main.asp
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"David Rose" <drose2929@.yahoo.com> wrote in message news:uT%23EJSBnDHA.964@.TK2MSFTNGP10.phx.gbl...
> Ray,
> No, not all the tables. However, I could do that and take what I need after
> the restore.
> Is there something like writing the tables to a structured storage file?
> That would be ideal.
> Thanks.
> David
>
> "Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
> news:OAQwZ8AnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Are you wanting to copy all tables? How about a backup and restore?
> >
> >
> >
> > --
> > Ray Higdon MCSE, MCDBA, CCNA
> > --
> > "David Rose" <drose2929@.yahoo.com> wrote in message
> > news:equf4qAnDHA.2364@.TK2MSFTNGP11.phx.gbl...
> > > I have a dual boot system with SQL Server 2000 on each operating system.
> > I
> > > would like to copy tables from one system to the other.
> > >
> > > The problem is that when one os is running the other is not available.
> I
> > > could create scripts to recreate all the tables, export data to a text
> > file,
> > > and then import the data into the newly created tables.
> > >
> > > Is there an easier way?
> > >
> > > TIA
> > >
> > > David
> > >
> > >
> >
> >
>|||Hi
If the database is available on the second OS then a DTS job can be
scheduled to transfer the data/tables required.
John
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:bnhjj7$4bf$1@.titan.btinternet.com...
> Hi
> You should be able to attach the databases from SQL Server on second OS
> instance to the SQL Server instance on the first, they will always be up
to
> date.
> John
> "David Rose" <drose2929@.yahoo.com> wrote in message
> news:equf4qAnDHA.2364@.TK2MSFTNGP11.phx.gbl...
> > I have a dual boot system with SQL Server 2000 on each operating system.
> I
> > would like to copy tables from one system to the other.
> >
> > The problem is that when one os is running the other is not available.
I
> > could create scripts to recreate all the tables, export data to a text
> file,
> > and then import the data into the newly created tables.
> >
> > Is there an easier way?
> >
> > TIA
> >
> > David
> >
> >
>

Copying SPs from one DB to another

I'm working on a system that will create a new DB that copies the SPs from
an older DB.
The purpose of this project is to create smaller, yearly DBs that will
require fewer table scans (since most searches are done on only the current
year). I would like to mimic the old DB year by year, copying all table
structure, a few tables worth of data (so far so good on those queries), but
I'm unsure on how to copy SPs from one DB to another.
Any thoughts?
Chad Edge
Seattle, WAYou can create the procedures in model, so when you create the new DB, they
get created automatically.
You can also try SQL Compare from www.red-gate.com, which will help you
generate scripts to synchronize two databases.
"Chad Edge" <xcasex@.hotmail.com> wrote in message
news:D2746C57-08D3-4A58-B0C0-6D7725D27C73@.microsoft.com...
> I'm working on a system that will create a new DB that copies the SPs from
> an older DB.
> The purpose of this project is to create smaller, yearly DBs that will
> require fewer table scans (since most searches are done on only the
> current year). I would like to mimic the old DB year by year, copying all
> table structure, a few tables worth of data (so far so good on those
> queries), but I'm unsure on how to copy SPs from one DB to another.
> Any thoughts?
> Chad Edge
> Seattle, WA
>|||Aaron,
Thanks for your help on that. I'll look up the link and work on the scripts.
I'm actually wondering now if we even need to copy the SP's. I guess it will
depend on what amount of weight we want to put on the interface-level.
What we're trying to do is make smaller seasonal db's without changing much
of the interfaces access (there's an internal edit tool, and an external
read-only view tool). If we keep our current, 'master' database in-place w/
the SP's, then we could adjust those SP's to choose which smaller, seasonal
db to get data from. If we do things that way, then we won't need to manage
all the SP's on multiple db's.
Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eHxOBWAlGHA.1936@.TK2MSFTNGP04.phx.gbl...
> You can create the procedures in model, so when you create the new DB,
> they get created automatically.
> You can also try SQL Compare from www.red-gate.com, which will help you
> generate scripts to synchronize two databases.
>
>
> "Chad Edge" <xcasex@.hotmail.com> wrote in message
> news:D2746C57-08D3-4A58-B0C0-6D7725D27C73@.microsoft.com...
>|||> What we're trying to do is make smaller seasonal db's without changing
> much of the interfaces access (there's an internal edit tool, and an
> external read-only view tool). If we keep our current, 'master' database
> in-place w/ the SP's, then we could adjust those SP's to choose which
> smaller, seasonal db to get data from. If we do things that way, then we
> won't need to manage all the SP's on multiple db's.
I recommend having the schema spread across the dbs, which will allow for
the app to simply change its connection string, instead of your main control
database needing to generate a dynamic SQL string EXEC('EXEC ' + @.dbname + '
.dbo.ProcedureName');|||Aaron,
My worry about doing that would be having to manage the SP's from season to
season.
So just to clarify, you're saying this is not recommended:
1. Master db, currently the only db, is called MAINEVENTS1. This db has all
SP's, and stores about 10 years worth of information (events)
2. We'd like to create new, seasonal db's called 'EVENTS_YYxYY' (eg:
EVENTS_06x07)
I'm considering this approach since it would shrink the amount of table
data that would need to be scanned, since most event results are polled for
the current 'season'
3. When an administrator logs in using (currently CFM) our admin interface
(on our intranet), they would have a new dropdown "Select Season:" which
would set their session for a specific season
4. When visitors to our Web site (again, using CFM), they would by default
get the current season set as their session, but would have text-links to
previous seasons.
5. Passing the session variable of which season is selected to the query
string would fire the correct SP from MAINEVENTS1, which would then retrieve
the database information from the correct DB.
In our first year, that would mean that there'd be two DB's :
MAINEVENTS1 with the last 10 years worth of data, and EVENTS_06x07.
6. This fall, we'd be adding new event types (concerts, speaking
engagements, etc) that would only be added to the newer DB's in the seasons
that they happened (no need to add Public Speaking to the MAINEVENTS1 since
it never happened).
7. The next round of updates would allow us to split MAINEVENTS1 into
EVENTS_01x02, 03_04, etc. further shrinking our query source
8. The MAINEVENTS1 would then become a very small DB, just a gateway of SPs
that query other dbs and format the data.
The reason I've been approaching the project this way is so the Online
interface only needs minor changes, the SP's only need minor changes, and we
don't have to manage SP's from season to season (say we change the way we'd
like data formatted; we'd have to track the changes down and verify control
over each database - bad form in my opinion).
The main reasons for approaching the project this way:
1. Shrink the databases since there's thousands of rows in our ten-year db.
2. Lock past events so they don't get overwritten (we've had issues where a
venue will change it's name; if we update our database the way it's
organized now, all events in the past are then related to the new venue
name - this makes it a nightmare to try and track in the past where an event
occurred because the venue in the past will no longer exist in our db).
I know #2 should be addressed, but we're not able to make such drastic
changes to the data at this time. Perhaps a round 3 we can restructure. For
now, we're getting about 2 million hits a month for our events and I'd like
to keep the impact small.
Hopefully that's not boring you to tears, I just needed to dump my brain
onto the screen to make sure we're speaking the same language.
Thanks again for your suggestions. I'll look at the structure and the SP's a
little more and see what work is ahead. I've got to make a decision by the
end of this w so I can roll out the replication by EOM June.
Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u3DsmsIlGHA.2304@.TK2MSFTNGP02.phx.gbl...
> I recommend having the schema spread across the dbs, which will allow for
> the app to simply change its connection string, instead of your main
> control database needing to generate a dynamic SQL string EXEC('EXEC ' +
> @.dbname + ' .dbo.ProcedureName');
>|||> My worry about doing that would be having to manage the SP's from season
> to season.
What is there to manage? If you store the SPs in model, then every new
database you create will have the SPs automatically.
When you have a changed SP to deploy, you run a loop to create a series of
CREATE or ALTER procedure statements, to generate a script you can run,
deploying to all databases at once.
We have two production systems that use this model, one has over 3000
identical databases spread across three machines, and the other has over 750
identical databases across three different environments (dev, qa,
production).
I use red-gate's SQL compare to synchronize model between environments.
And by naming the databases with a common naming scheme, I was able to write
a stored procedure in master that gets all the databases from sys.databases
matching that naming scheme.
I store the create and/or alter scripts in a file called change.sql, then
run a C# command line program that calls the stored procedure, and creates
an output file for each database.
Deploying changes really couldn't be simpler (except when there are
exceptions between environments). Everything configurable (such as network
share paths, SMTP servers, ftp passwords, etc.) are stored in properties
tables, nothing is hard-coded, and it makes it really easy to take the one
customer who explodes in activity from one server to another. Just backup
the database, restore on another server, and update a flag in the control
database that tells you what server the app can find that database.
A|||> I store the create and/or alter scripts in a file called change.sql, then
> run a C# command line program that calls the stored procedure, and creates
> an output file for each database.
Sorry, let me explain that sentence a little better.
Let's say I have a fix to a single stored procedure that needs to be
broadcast to all databases in an environment.
I store a single ALTER PROCEDURE script in change.sql.
My C# app grabs the stored procedure from master, steps through the 750+
databases, and for each one, reads change.sql, adds a USE [dbname] in front
of it, and appends it to a file called deploy.sql. Now I can run a single
.sql script to deploy my changes to every single database.
Sure, there are pros and cons, and yes that many databases sounds like a lot
to manage, and it is. But "a lot" does not necessarily equate to "hard"...
there are many ways to make the multi-db scenario much easier to manage.
One of these days I'm going to write an article on it...
A|||Aaron,
I'm liking where you're going (excluding the fact that it's all new ground
to me; I've stayed as far from MS coding as I have been able in my career).
One thing I might not have mentioned: We're going to get into situations
where we'll need a *controller* sp that takes variables and chooses which
database to retrieve the data from. For example:
Right now (with MAINEVENTS1) we have a table of Artists (musicians, let's
say). If you want to retrieve information about a specific Artist (say,
their instrument, the places they've played, tickets sold, etc) you run a
query using their ArtistID (just an example). That works in the MAINEVENTS1
db.
Now lets say we create EVENTS_06x07 that adds additional columns of data
that weren't available in the past (such as, height and weight, which
changes as the year goes on - just to make it dynamic). We won't have that
information for anything in the past, so we'd like to not make any changes
to MAINEVENTS1. However, we would now need to make the query (SP) understand
where to get it's data, and how much detail (columns) to return, based on
the year passed (pre-06 or post-06).
I picture (in my ideal candy-land imagination) a controller SP that would
take two variables (the ArtistID, and the Year), determine which database to
run the SP (or better yet, add the correct formatting to the SP itself), run
the SP and return the proper column data.
This is where replicating SPs gets tricky: one DB may have changes that a
previous years DB won't.
Also, leaving SP's in the Model would not automatically roll-out the changes
until a new DB was created, or when we create an entirely separate
application (such as something in C#).
My example is very elementary; there's trickier situations (statistics about
artists, about venues, about groups, etc) that will change from year to
year. That's where centrally managing SP's and having the MAINEVENTS1 manage
the connections and returns makes so much sense to me.
Again, thanks for your insight. You're making a lot of sense and I've
already started working with the Red-Gate software today (the dependency
tracker is flipping me out).
Chad
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23ESReGJlGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Sorry, let me explain that sentence a little better.
> Let's say I have a fix to a single stored procedure that needs to be
> broadcast to all databases in an environment.
> I store a single ALTER PROCEDURE script in change.sql.
> My C# app grabs the stored procedure from master, steps through the 750+
> databases, and for each one, reads change.sql, adds a USE [dbname] in
> front of it, and appends it to a file called deploy.sql. Now I can run a
> single .sql script to deploy my changes to every single database.
> Sure, there are pros and cons, and yes that many databases sounds like a
> lot to manage, and it is. But "a lot" does not necessarily equate to
> "hard"... there are many ways to make the multi-db scenario much easier to
> manage. One of these days I'm going to write an article on it...
> A
>|||> Now lets say we create EVENTS_06x07 that adds additional columns of data
> that weren't available in the past (such as, height and weight, which
> changes as the year goes on - just to make it dynamic).
So, in every db, add weight and height to the table(s) or view(s), adjust
procedure(s) that exist there, and only update those customers that have
that data (otherwise it is NULL).
Displaying these columns is something the app can decide based on the
settings in the control db, not based on the data itself.
Adding columns to a stored procedure should not change the behavior of the
app... but the app *can* use those new columns if it deems it should.
A

Copying entire databases to remote server w/o Enterprise Manager

Is there any possible way to copy an entire MSDE database from my local system to a remote server using a program like 'osql'?

Or, is there any other GUI available for MSDE?

Thanks in advance.

GrierSure. There are a few ways. A couple that come to mind are to use the sp_detach_db system stored procedure in osql, copy the database files to the server, and use sp_attach_db to reattach them. Another is to run dtsrun.exe to run a DTS package that copies it over. You could also write ADO.NET code to do it, but it would be a lot of work to get all the objects copied.

There are several tools you can use to administer MSDE:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

ASP.NET WebMatrix (which includes a database management tool) from this web site (click on the Web Matrix tab at the top of this page).

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also access MSDE using Access. I'm not sure if this will do what you want, though.

Any of these work for you?
Don

copying Databases

I backed a MSSQL2000 database into a BAK file. Now when I create a
MSSQL2005 system and I goto RESTORE - after creating a database with the
same name- I get
"ERROR the backup set holds a database other than the existing "Toxinet'
database. RESTORE DATABASE is terminating abnormally"
Any ideas? This process has always worked in MSSQL97 & 2000
Thanks,
Raul Rego
NJPIES
rrego@.njpies.org
Hi Raul
From your limited information, it sounds like the name in the backup is not
what you think it is.
You don't have to create a database before doing a restore. Why don't you
just try restoring and letting the restore process create the database.
Perhaps there is a spelling issue or you have a case sensitive collation.
BTW, there was no MSSQL97. The version before SQL Server 2000 was SQL Server
7.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Raul" <rrego@.njpies.org> wrote in message
news:BCE20BE9-3480-43A4-9B44-C6A0F628B38A@.microsoft.com...
>I backed a MSSQL2000 database into a BAK file. Now when I create a
>MSSQL2005 system and I goto RESTORE - after creating a database with the
>same name- I get
> "ERROR the backup set holds a database other than the existing "Toxinet'
> database. RESTORE DATABASE is terminating abnormally"
> Any ideas? This process has always worked in MSSQL97 & 2000
> Thanks,
> Raul Rego
> NJPIES
> rrego@.njpies.org
>
|||Use the "with replace" clause.
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Raul" <rrego@.njpies.org> wrote in message
news:BCE20BE9-3480-43A4-9B44-C6A0F628B38A@.microsoft.com...
>I backed a MSSQL2000 database into a BAK file. Now when I create a
>MSSQL2005 system and I goto RESTORE - after creating a database with the
>same name- I get
> "ERROR the backup set holds a database other than the existing "Toxinet'
> database. RESTORE DATABASE is terminating abnormally"
> Any ideas? This process has always worked in MSSQL97 & 2000
> Thanks,
> Raul Rego
> NJPIES
> rrego@.njpies.org
>

copying Databases

I backed a MSSQL2000 database into a BAK file. Now when I create a
MSSQL2005 system and I goto RESTORE - after creating a database with the
same name- I get
"ERROR the backup set holds a database other than the existing "Toxinet'
database. RESTORE DATABASE is terminating abnormally"
Any ideas? This process has always worked in MSSQL97 & 2000
Thanks,
Raul Rego
NJPIES
rrego@.njpies.orgHi Raul
From your limited information, it sounds like the name in the backup is not
what you think it is.
You don't have to create a database before doing a restore. Why don't you
just try restoring and letting the restore process create the database.
Perhaps there is a spelling issue or you have a case sensitive collation.
BTW, there was no MSSQL97. The version before SQL Server 2000 was SQL Server
7.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Raul" <rrego@.njpies.org> wrote in message
news:BCE20BE9-3480-43A4-9B44-C6A0F628B38A@.microsoft.com...
>I backed a MSSQL2000 database into a BAK file. Now when I create a
>MSSQL2005 system and I goto RESTORE - after creating a database with the
>same name- I get
> "ERROR the backup set holds a database other than the existing "Toxinet'
> database. RESTORE DATABASE is terminating abnormally"
> Any ideas? This process has always worked in MSSQL97 & 2000
> Thanks,
> Raul Rego
> NJPIES
> rrego@.njpies.org
>|||Use the "with replace" clause.
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"Raul" <rrego@.njpies.org> wrote in message
news:BCE20BE9-3480-43A4-9B44-C6A0F628B38A@.microsoft.com...
>I backed a MSSQL2000 database into a BAK file. Now when I create a
>MSSQL2005 system and I goto RESTORE - after creating a database with the
>same name- I get
> "ERROR the backup set holds a database other than the existing "Toxinet'
> database. RESTORE DATABASE is terminating abnormally"
> Any ideas? This process has always worked in MSSQL97 & 2000
> Thanks,
> Raul Rego
> NJPIES
> rrego@.njpies.org
>

Copying Data to a File

Hi Guys,
I am trying to fix the following problem:
I need to create a .txt for input into a unix based system (sybase).
This has been done using BCP, and not DTS. Once BCP'ing data into the
.txt file, there are <CR><LF> data at the end of every row. Once the
data is imported into the unix system, these are showing up in the
database.
I've tried BCP with the:
-r \n
-r \r
-r \n \r
With no success (the characters still appear in sybase).
Is there any way to do this with BCP, and not have the characters
appear?
thanks,
Justinjustin.drennan@.gmail.com wrote:
> Hi Guys,
> I am trying to fix the following problem:
> I need to create a .txt for input into a unix based system (sybase).
> This has been done using BCP, and not DTS. Once BCP'ing data into the
> .txt file, there are <CR><LF> data at the end of every row. Once the
> data is imported into the unix system, these are showing up in the
> database.
> I've tried BCP with the:
> -r \n
> -r \r
> -r \n \r
> With no success (the characters still appear in sybase).
> Is there any way to do this with BCP, and not have the characters
> appear?
You might want to try the tr command in *nix:
in_file | tr -d \r > out_file
Delete the carriage return character from the in_file and output to the
out_file.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Copying data between databases with different collations

I have written a system which I now want to intergrate to some degree
with with Syspro accounting software, both run off MS SQL databases on
the same server.
I am writing a trigger on the syspro database,
I need to join across the 2 DBs, each have different collations
When I try:
Insert INTO DB1.dbo.Table1 (F1,F2.....)
Select (View1.Field1, View1.Field2.....)
>From DB2.dbo.View1 where [condition]
I get the following error "Cannot resolve collation conflict for
replace operation."
is there some way to convert the values from the view to allow them to
be inserted?
Syspro database is using : SQL_Latin1_General_CP437_BIN
And mine is using : SQL_Latin1_General_CP1_CI_ASHave a look at Collate database_default in SQL Books Online
Jump To URL :- tsqlref.chm::/ts_ca-co_5z55.htm
HTH. Ryan
<cameron.waldron@.gmail.com> wrote in message
news:1139489197.088750.109310@.g44g2000cwa.googlegroups.com...
>I have written a system which I now want to intergrate to some degree
> with with Syspro accounting software, both run off MS SQL databases on
> the same server.
> I am writing a trigger on the syspro database,
> I need to join across the 2 DBs, each have different collations
> When I try:
> Insert INTO DB1.dbo.Table1 (F1,F2.....)
> Select (View1.Field1, View1.Field2.....)
> I get the following error "Cannot resolve collation conflict for
> replace operation."
> is there some way to convert the values from the view to allow them to
> be inserted?
> Syspro database is using : SQL_Latin1_General_CP437_BIN
> And mine is using : SQL_Latin1_General_CP1_CI_AS
>|||Thanks, the problem was that I was in the view I was querying on my
database (it had a replace statement in it which was the problem, hence
the "Cannot resolve collation conflict for replace operation." Error) I
brought the view from my db to the syspro db and collated fields in the
view as I needed with collate database_default, problem fixed :-)

Copying a database

Hi all,
I want to copy a database from my SQL Server system and install it on another. Can anyone suggest me how I can copy the same along with log info, login info, permissions, etc. and install it on another system. Thanks!

Vik!Hi,

Basically, you can't. Not in one fell swoop. What you can do, however, is to detach the database from one server, copy it--along with the log file--to the new server, and attach it. But you'll need to recreate the logins, because the internal SIDs--security IDs--on the new server won't match those on the old.

If permissions are assigned to database roles, those will be intact.

Don|||If you have Enterprise Manager, you can just create a sql script, and script that database, and it's users.|||What donkiely says in true. However, I'd say the best techniques are...
1. Backup database on server 1, restore it on server 2, re-wire logins
2. Use SQLs Transformation Wizards from Enterprise Server...ok for small databases but network intensive. Best to sort out the security issue first for this one, that way the transform will do the bulk (ahem) of the work for you.
3. As previous posters says, script all the meta data, then copy the data. Basically the same issues as (2) but at least you've got some nice scripts to reapply if your db gets into trouble.|||KraGiE,

Just keep in mind that scripting the database won't copy the data. You'd then have to use DTS or some other means to copy it.

Don

Copy tables to a new database

Hi All,
If I put tables in 'Model' database and creating new database will automatic
ally copy the system files along with the additional files that I created.
I want to do the same thing, but getting all the tables from a database call
ed 'Template'. How could I
achieve this by writing a stored procedure? Any help or suggestions is appr
eciated.Steve,
Create a script of everything in Template (you could use RedGate, Enterprise
Manager,etc.) then run that script in the newly created database.
Alternatively, you create your Template database and put whatever you want
into it. Then back it up. Create your new database and use a RESTORE ...
REPLACE of your Template backup to load your template into the new database.
Russell Fields
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:9771EEDD-FF40-4509-9EDE-7A0E04233A61@.microsoft.com...

quote:

---

> Hi All,
> If I put tables in 'Model' database and creating new database will

---

automatically copy the system files along with the additional files that I
created. I want to do the same thing, but getting all the tables from a
database called 'Template'. How could I achieve this by writing a stored
procedure? Any help or suggestions is appreciated.

Copy tables to a new database

Hi All,
If I put tables in 'Model' database and creating new database will automatically copy the system files along with the additional files that I created. I want to do the same thing, but getting all the tables from a database called 'Template'. How could I achieve this by writing a stored procedure? Any help or suggestions is appreciated.Steve,
Create a script of everything in Template (you could use RedGate, Enterprise
Manager,etc.) then run that script in the newly created database.
Alternatively, you create your Template database and put whatever you want
into it. Then back it up. Create your new database and use a RESTORE ...
REPLACE of your Template backup to load your template into the new database.
Russell Fields
"Steve" <anonymous@.discussions.microsoft.com> wrote in message
news:9771EEDD-FF40-4509-9EDE-7A0E04233A61@.microsoft.com...
> Hi All,
> If I put tables in 'Model' database and creating new database will
automatically copy the system files along with the additional files that I
created. I want to do the same thing, but getting all the tables from a
database called 'Template'. How could I achieve this by writing a stored
procedure? Any help or suggestions is appreciated.

Copy SQL Server Database to another Server

I have developed a SQL Server 2000 Database on my development system. I need to copy the Database to my client's Server. Unfortunately they are not linked.

What is the best way to copy the Database? Can I detach it on my Server, make a copy of the file. Copy the file to my Client's Server, and then Attach it?

OR IS THERE A BETTER WAY?

Thanks in advance for your helpDetach, copy, reattach is probably the fastest way to copy a database to another box, although there are plenty of other choices. Using BACKUP / RESTORE is probably the safest, and it doesn't take much longer than detach/copy/reattach does.

-PatP|||the best way (In My Opinion)
is to perform a transfer database task in dts
it copies everything without having to require a detach then copy then attach.
backing up and restoring is comprehensive but takes a while to perform
and the objects are in whatever state they were in when you backed em up

transfering the db is fast and you get brand new objects so for example indexes are created new so they are not fragged..

[Books Online] DTS tasks, transferring database objects

Copy rows from multiple tables into one table (in another database)

My boss has been using linked tables (read only) from Access to copy data
from our accounting system to his pc. Instead of using Access I would like
to copy rows from different tables into a new table in a different database.
I want to use a different database for security reasons because the new
database will be accessed by the web server.
What is the best way to simulate Access' linked tables and can the
resolution be realtime or would I need to run a query once a day to have the
most current info in my new table?You can either use DTS or Replication depends on how your boss want to see
the data as exactly current or could be sometime delay.
Perayu
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> My boss has been using linked tables (read only) from Access to copy data
> from our accounting system to his pc. Instead of using Access I would
> like to copy rows from different tables into a new table in a different
> database. I want to use a different database for security reasons because
> the new database will be accessed by the web server.
> What is the best way to simulate Access' linked tables and can the
> resolution be realtime or would I need to run a query once a day to have
> the most current info in my new table?
>
>|||It looks like I should use SQL triggers to update information in new table
when changes have been made in the accounting database. What is the best
way to export data rows from different tables to one new table and does
anyone have an example of how to create a trigger that watches many tables,
but only inserts into one table?
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> My boss has been using linked tables (read only) from Access to copy data
> from our accounting system to his pc. Instead of using Access I would
> like to copy rows from different tables into a new table in a different
> database. I want to use a different database for security reasons because
> the new database will be accessed by the web server.
> What is the best way to simulate Access' linked tables and can the
> resolution be realtime or would I need to run a query once a day to have
> the most current info in my new table?
>
>|||Sometime delayed is fine with us. I'll research those two topics, but which
is less complicated seeing as I'm not a SQL guru.
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:O%23lvOifvFHA.3100@.TK2MSFTNGP12.phx.gbl...
> It looks like I should use SQL triggers to update information in new table
> when changes have been made in the accounting database. What is the best
> way to export data rows from different tables to one new table and does
> anyone have an example of how to create a trigger that watches many
> tables, but only inserts into one table?
> "JohnS" <JohnSPAM@.hotmail.com> wrote in message
> news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||In my opinion, DTS is a easier one. Sometime, Replication is hard to
maintain whenever you try to change the related data structure.
Perayu
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:Oi4bWnfvFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Sometime delayed is fine with us. I'll research those two topics, but
> which is less complicated seeing as I'm not a SQL guru.
> "JohnS" <JohnSPAM@.hotmail.com> wrote in message
> news:O%23lvOifvFHA.3100@.TK2MSFTNGP12.phx.gbl...
>|||The functional equivalent to Access's linked tables is to set up a Linked
Server in SQL Server. A linked server can be any OLEDB data source. Any
tables in the remote system would be available through this technique.
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> My boss has been using linked tables (read only) from Access to copy data
> from our accounting system to his pc. Instead of using Access I would
> like to copy rows from different tables into a new table in a different
> database. I want to use a different database for security reasons because
> the new database will be accessed by the web server.
> What is the best way to simulate Access' linked tables and can the
> resolution be realtime or would I need to run a query once a day to have
> the most current info in my new table?
>
>|||I tried DTS but didn't find anyway to choose which rows in a table I want.
It seemed to be the whole table or nothing at all. The other problem was
data from the accounting system would wipe out the target destination data
and I want them to merge.
Can I choose certain rows from different tables and merge them into one
table with Linked Server?
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:uFybfFhvFHA.2504@.TK2MSFTNGP15.phx.gbl...
> The functional equivalent to Access's linked tables is to set up a Linked
> Server in SQL Server. A linked server can be any OLEDB data source. Any
> tables in the remote system would be available through this technique.
> "JohnS" <JohnSPAM@.hotmail.com> wrote in message
> news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||Well, DTS and linked servers are kind of "apples and oranges" topics.
In DTS, you can use regular SQL statements to define which columns and rows
you want to retrieve from your source connection, and you can define where
that data gets placed in your destination connection. DTS is a fairly large
topic with many sub-topics. I would encourage you to thouroughly review the
topic in SQL Server Books Online (BOL).
As for linked servers, all I was pointing out was that you can set up a
linked server in SQL Server in a somewhat similar fashion to the way you set
up linked tables in MS Access. Again, this is a moderately involved topic.
For more information, see the topic "Accessing External Data" in BOL.
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:eXBFQPivFHA.904@.tk2msftngp13.phx.gbl...
>I tried DTS but didn't find anyway to choose which rows in a table I want.
>It seemed to be the whole table or nothing at all. The other problem was
>data from the accounting system would wipe out the target destination data
>and I want them to merge.
> Can I choose certain rows from different tables and merge them into one
> table with Linked Server?
>
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:uFybfFhvFHA.2504@.TK2MSFTNGP15.phx.gbl...
>