Concat ?

Hi

I'm not sure about the right 'term' of this question. I'm using MS SQL server 2000 standard edition in a Windows 2003 Server Enterprise edition.

I have two tables. One of this has several columns (Year,Month,Day,User,Message). The other one, have just one column called Messages.

I count the today's messages in the first table using the following syntax

SELECT COUNT(*) FROM Tab1 WHERE YY = (datepart(year, getdate())) AND MM = (datepart(month, getdate())) AND DD = (datepart(day, getdate()))

Now, I have to update the column Messages in Table2, but I must to add a text before the count result. I.E.

'Messages today = 25'

Hoy may I add the text before the SELECT COUNT query result ?? Is it possible ??

Thank you.Try this syntax

select emp_id, 'Messages today = ' + convert(varchar(10),count(1)) from table group by empid

Prasad

computed columns or UDFs

Hi,

What is the difference between a computed column and a UDF?
Is a computed column the same as the "Formula" field under Design Table in Enterprise Manager?
Also, what is the proper syntax for the Formula field? Can I use regular SQL on it or is there more to it?

thanks,
Frankjust look up books online they have a better xplanation than anyone here can give you in 1-2 lines.

hth

Compression

Hello,

I have been wanting to compress my database. I am not really sure how this is done. I was looking on Enterprise Mangr. and if you right click on the db and go to all tasks, there is an option to shrink database. Is this the way you would compress your database, or are there other ways of doing this?

Thanks for all the help.DUMP

DBCC SHRINKFILE|||Originally posted by Brett Kaiser
DUMP

DBCC SHRINKFILE

How would you do a shrink file?|||Do you have access to Books Online?

Go to the U=Index and look it up

Examples
This example shrinks the size of a file named DataFil1 in the UserDB user database to 7 MB.

USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO

Look up DBBC SHRINKDATABASE as well|||Originally posted by Brett Kaiser
Do you have access to Books Online?

Go to the U=Index and look it up

Examples
This example shrinks the size of a file named DataFil1 in the UserDB user database to 7 MB.

USE UserDB
GO
DBCC SHRINKFILE (DataFil1, 7)
GO

Look up DBBC SHRINKDATABASE as well

Thanks again for all your help.|||Shrinkdatabase May not work All the time . Try using Shrinkfiles too.

Also, it is not a good practice to leave Shrink_db option checked ..

Compress text column on SQL2000

I am trying to compress text/image column on a table on MSSQL200 Enterprise Ed.
When will compress when write to the table and uncompress when user retrieve
the data. Anyone can please suggest me how to or any tool to do this.
You need to do the compression and uncompression in the client app or middle
tier and not the database server.
Andrew J. Kelly SQL MVP
"Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
>I am trying to compress text/image column on a table on MSSQL200 Enterprise
>Ed.
> When will compress when write to the table and uncompress when user
> retrieve
> the data. Anyone can please suggest me how to or any tool to do this.
>
>
|||To add to Andrew's response, I recommend that you test very heavily before
implementing this in a production environment; I've done fairly extensive
testing of various on-the-fly .NET compression libraries for the purpose of
compressing LOB data on the way in and out of the database, and found that
instead of improving performance as I expected (due to lowering disk IOs and
network bandwidth required to retrieve the data), there was instead a
moderate degredation due to the extra processor strain on the middle tier.
If you do find a way to improve performance using compression, I would be
very interested in hearing about your techniques and results -- of course,
if you're doing this only for disk space savings, you can disregard my
rantings...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
> I am trying to compress text/image column on a table on MSSQL200
Enterprise Ed.
> When will compress when write to the table and uncompress when user
retrieve
> the data. Anyone can please suggest me how to or any tool to do this.
>
>
|||Andrew J. Kelly wrote:
> You need to do the compression and uncompression in the client app or
> middle tier and not the database server.
>
The OP could place the TEXTIMAGE on a filegroup that is located on a
compressed folder on the server. That's supported by SQL Server if I'm
not mistaken and won't require any additional libraries to manage.
Andrew, any thoughts?
David Gugick
Imceda Software
www.imceda.com
|||Actually I know using compressed volumes is not recommended and I think it
may even be unsupported for Sql Server.
Andrew J. Kelly SQL MVP
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23S5%23N4uGFHA.2732@.TK2MSFTNGP15.phx.gbl...
> Andrew J. Kelly wrote:
> The OP could place the TEXTIMAGE on a filegroup that is located on a
> compressed folder on the server. That's supported by SQL Server if I'm not
> mistaken and won't require any additional libraries to manage.
> Andrew, any thoughts?
> --
> David Gugick
> Imceda Software
> www.imceda.com
|||Andrew J. Kelly wrote:
> Actually I know using compressed volumes is not recommended and I
> think it may even be unsupported for Sql Server.
>
You are correct:
http://support.microsoft.com/kb/231347/EN-US/
David Gugick
Imceda Software
www.imceda.com
|||What about upgrade to use Yukon, use CLR written in c# to write unpresss text
and store on sqlserver? Anyone has done this sort of thing?
"Adam Machanic" wrote:

> To add to Andrew's response, I recommend that you test very heavily before
> implementing this in a production environment; I've done fairly extensive
> testing of various on-the-fly .NET compression libraries for the purpose of
> compressing LOB data on the way in and out of the database, and found that
> instead of improving performance as I expected (due to lowering disk IOs and
> network bandwidth required to retrieve the data), there was instead a
> moderate degredation due to the extra processor strain on the middle tier.
> If you do find a way to improve performance using compression, I would be
> very interested in hearing about your techniques and results -- of course,
> if you're doing this only for disk space savings, you can disregard my
> rantings...
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Vitamin E" <VitaminE@.discussions.microsoft.com> wrote in message
> news:4C471127-F072-4D0B-823D-468532E868BA@.microsoft.com...
> Enterprise Ed.
> retrieve
>
>
|||"SQLwonder" <SQLwonder@.discussions.microsoft.com> wrote in message
news:EBBE4D21-0CFE-493D-9802-68BC9B81D2A0@.microsoft.com...
> What about upgrade to use Yukon, use CLR written in c# to write unpresss
text
> and store on sqlserver? Anyone has done this sort of thing?
I haven't tried yet -- but I plan to when the next beta is released and
performance is improved a bit. MS hadn't started performance tuning the
last releases much so it wasn't worth testing, IMO.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||Please let us know later the.
"Adam Machanic" wrote:

> "SQLwonder" <SQLwonder@.discussions.microsoft.com> wrote in message
> news:EBBE4D21-0CFE-493D-9802-68BC9B81D2A0@.microsoft.com...
> text
> I haven't tried yet -- but I plan to when the next beta is released and
> performance is improved a bit. MS hadn't started performance tuning the
> last releases much so it wasn't worth testing, IMO.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
>

Complicated Licensing Question

We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
CPU box. Along with this, we are hoping to upgrade to SQL Server 2005 within
a year after the hardware upgrade.
The only reason we are currently running SQL Server 2000 Enterprise is
because our server has over 2 GB of memory. In 2005, the 2 GB memory
limitation has been removed for the Standard Edition so we are moving over to
the Standard Edition when we upgrade in a year.
So currently, we are looking to add two more SQL Server 2000 Ent licenses
for the addtional CPUs. Can I buy two SQL Server Standard licenses and use
the downgrade rights to temporarily run 2000 Enterprise?
"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
The best thing you can do is contact a Microsoft licensing specialist:
http://www.microsoft.com/licensing/index/worldwide.mspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
I never got an answer to taht when I asked a similar question, directly to
Microsoft.
BUT, they are the only ones who can really answer that.
Any advice given here, unless by an official MS person is worth what you're
paying for it.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per proc,
retail). Sounds unlikely.
David
|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ej3Tc2RXHHA.1432@.TK2MSFTNGP02.phx.gbl...
>
> "licensing_confusion" <licensing_confusion@.discussions.microsoft.com>
> wrote in message
> news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
> retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per
> proc, retail). Sounds unlikely.
> David
I think that's his question, which is similar to the case we had. 2
node-cluster with 8 gig of ram with 2 CPUs each.
Wanted to upgrade to 4 CPUs.
We're running SQL 2000, but plan on updating to SQL 2005.
Now, SQL 2005 Standard works on that config, but 2000 requires Enterprise.
So kind of puts some folks in a bind. :-/

Complicated Licensing Question

We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
CPU box. Along with this, we are hoping to upgrade to SQL Server 2005 withi
n
a year after the hardware upgrade.
The only reason we are currently running SQL Server 2000 Enterprise is
because our server has over 2 GB of memory. In 2005, the 2 GB memory
limitation has been removed for the Standard Edition so we are moving over t
o
the Standard Edition when we upgrade in a year.
So currently, we are looking to add two more SQL Server 2000 Ent licenses
for the addtional CPUs. Can I buy two SQL Server Standard licenses and use
the downgrade rights to temporarily run 2000 Enterprise?"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
The best thing you can do is contact a Microsoft licensing specialist:
http://www.microsoft.com/licensing/index/worldwide.mspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
I never got an answer to taht when I asked a similar question, directly to
Microsoft.
BUT, they are the only ones who can really answer that.
Any advice given here, unless by an official MS person is worth what you're
paying for it.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per proc,
retail). Sounds unlikely.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ej3Tc2RXHHA.1432@.TK2MSFTNGP02.phx.gbl...
>
> "licensing_confusion" <licensing_confusion@.discussions.microsoft.com>
> wrote in message
> news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
> retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per
> proc, retail). Sounds unlikely.
> David
I think that's his question, which is similar to the case we had. 2
node-cluster with 8 gig of ram with 2 CPUs each.
Wanted to upgrade to 4 CPUs.
We're running SQL 2000, but plan on updating to SQL 2005.
Now, SQL 2005 Standard works on that config, but 2000 requires Enterprise.
So kind of puts some folks in a bind. :-/

Complicated Licensing Question

We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
CPU box. Along with this, we are hoping to upgrade to SQL Server 2005 within
a year after the hardware upgrade.
The only reason we are currently running SQL Server 2000 Enterprise is
because our server has over 2 GB of memory. In 2005, the 2 GB memory
limitation has been removed for the Standard Edition so we are moving over to
the Standard Edition when we upgrade in a year.
So currently, we are looking to add two more SQL Server 2000 Ent licenses
for the addtional CPUs. Can I buy two SQL Server Standard licenses and use
the downgrade rights to temporarily run 2000 Enterprise?"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
The best thing you can do is contact a Microsoft licensing specialist:
http://www.microsoft.com/licensing/index/worldwide.mspx
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
I never got an answer to taht when I asked a similar question, directly to
Microsoft.
BUT, they are the only ones who can really answer that.
Any advice given here, unless by an official MS person is worth what you're
paying for it.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||"licensing_confusion" <licensing_confusion@.discussions.microsoft.com> wrote
in message news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a 4
> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
> within
> a year after the hardware upgrade.
> The only reason we are currently running SQL Server 2000 Enterprise is
> because our server has over 2 GB of memory. In 2005, the 2 GB memory
> limitation has been removed for the Standard Edition so we are moving over
> to
> the Standard Edition when we upgrade in a year.
> So currently, we are looking to add two more SQL Server 2000 Ent licenses
> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
> use
> the downgrade rights to temporarily run 2000 Enterprise?
So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per proc,
retail). Sounds unlikely.
David|||"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:ej3Tc2RXHHA.1432@.TK2MSFTNGP02.phx.gbl...
>
> "licensing_confusion" <licensing_confusion@.discussions.microsoft.com>
> wrote in message
> news:0D3492D5-F10D-435D-8FC0-293BCB09C94F@.microsoft.com...
>> We are currently upgrading our 2 CPU SQL Server 2000 Enterprise box to a
>> 4
>> CPU box. Along with this, we are hoping to upgrade to SQL Server 2005
>> within
>> a year after the hardware upgrade.
>> The only reason we are currently running SQL Server 2000 Enterprise is
>> because our server has over 2 GB of memory. In 2005, the 2 GB memory
>> limitation has been removed for the Standard Edition so we are moving
>> over to
>> the Standard Edition when we upgrade in a year.
>> So currently, we are looking to add two more SQL Server 2000 Ent licenses
>> for the addtional CPUs. Can I buy two SQL Server Standard licenses and
>> use
>> the downgrade rights to temporarily run 2000 Enterprise?
> So your question is can buy a SQL 2005 Standard License ($5,999 per proc,
> retail) to and use it to run SQL 2000 Enterprise Edition ($25,000 per
> proc, retail). Sounds unlikely.
> David
I think that's his question, which is similar to the case we had. 2
node-cluster with 8 gig of ram with 2 CPUs each.
Wanted to upgrade to 4 CPUs.
We're running SQL 2000, but plan on updating to SQL 2005.
Now, SQL 2005 Standard works on that config, but 2000 requires Enterprise.
So kind of puts some folks in a bind. :-/