Change Lincense Key

We currently have a SQL cluster which was built on pirated license keys.
This was a managerial mistake let me point out.~
Can we change the license key to legal copy without re-installing SQL? I
see that they license key is stored in the registry, but it appears that the
Product ID is generated randomly.
Any help is appreciated.Hi
You may want to try the rebuild registry option in the setup program or see
http://tinyurl.com/ad484
John
"Andrew" wrote:
> We currently have a SQL cluster which was built on pirated license keys.
> This was a managerial mistake let me point out.~
> Can we change the license key to legal copy without re-installing SQL? I
> see that they license key is stored in the registry, but it appears that the
> Product ID is generated randomly.
> Any help is appreciated.
>

Change Lincense Key

We currently have a SQL cluster which was built on pirated license keys.
This was a managerial mistake let me point out.~
Can we change the license key to legal copy without re-installing SQL? I
see that they license key is stored in the registry, but it appears that the
Product ID is generated randomly.
Any help is appreciated.
Hi
You may want to try the rebuild registry option in the setup program or see
http://tinyurl.com/ad484
John
"Andrew" wrote:

> We currently have a SQL cluster which was built on pirated license keys.
> This was a managerial mistake let me point out.~
> Can we change the license key to legal copy without re-installing SQL? I
> see that they license key is stored in the registry, but it appears that the
> Product ID is generated randomly.
> Any help is appreciated.
>

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.

> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/default...b;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.
|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:

> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFiles
> or F:\DBLogFiles). These don't need to match the default SQL Server folder
> structure.
>
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/default...b;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
>
>

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:
> > 1) Shall I just point out the partition, or do I have to create a folder
> > structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFiles
> or F:\DBLogFiles). These don't need to match the default SQL Server folder
> structure.
> > 2) How do I go about to move one existing database from, for instance, C:
> > to
> > E:? (Strangely, I can't find any documentation on this.)
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> > There's a setting in properties for SQL server in Enterprise Manager where
> > you can point out new default location for databases and logs. I want to
> > do
> > just that, but have two questions:
> >
> > 1) Shall I just point out the partition, or do I have to create a folder
> > structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> >
> > 2) How do I go about to move one existing database from, for instance, C:
> > to
> > E:? (Strangely, I can't find any documentation on this.)
> >
> > [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> >
> > Looking forward to any help.
>
>

Change default database/log location

There's a setting in properties for SQL server in Enterprise Manager where
you can point out new default location for databases and logs. I want to do
just that, but have two questions:
1) Shall I just point out the partition, or do I have to create a folder
structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
2) How do I go about to move one existing database from, for instance, C: to
E:? (Strangely, I can't find any documentation on this.)
[Windows Server 2003 (SP1); SQL 2000 (SP4).]
Looking forward to any help.> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
The only requirement is that the paths you specify must exist in order for
databases files to be created in the specified location (e.g. E:\DBDataFiles
or F:\DBLogFiles). These don't need to match the default SQL Server folder
structure.

> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
There are 2 methods commonly used to do this. One is to detach the
databases, move the files and then attach the files from the new location.
The other is backup and then restore WITH MOVE. See
http://support.microsoft.com/defaul...kb;en-us;314546 for more
information. Since the target is the same SQL instance, you don't need to
be concerned with different collations or orphaned users.
Hope this helps.
Dan Guzman
SQL Server MVP
"JSL" <JSL@.discussions.microsoft.com> wrote in message
news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
> There's a setting in properties for SQL server in Enterprise Manager where
> you can point out new default location for databases and logs. I want to
> do
> just that, but have two questions:
> 1) Shall I just point out the partition, or do I have to create a folder
> structure similar to the one SQL creates itself? (sqldata\MSSQL\ etc).
> 2) How do I go about to move one existing database from, for instance, C:
> to
> E:? (Strangely, I can't find any documentation on this.)
> [Windows Server 2003 (SP1); SQL 2000 (SP4).]
> Looking forward to any help.|||Thank you very much, Dan, for your quick and detailed answer.
/JSL
"Dan Guzman" wrote:

> The only requirement is that the paths you specify must exist in order for
> databases files to be created in the specified location (e.g. E:\DBDataFil
es
> or F:\DBLogFiles). These don't need to match the default SQL Server folde
r
> structure.
>
> There are 2 methods commonly used to do this. One is to detach the
> databases, move the files and then attach the files from the new location.
> The other is backup and then restore WITH MOVE. See
> http://support.microsoft.com/defaul...kb;en-us;314546 for more
> information. Since the target is the same SQL instance, you don't need to
> be concerned with different collations or orphaned users.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JSL" <JSL@.discussions.microsoft.com> wrote in message
> news:0020C5C3-A2C4-4DD3-B5F9-8DA9492CDA88@.microsoft.com...
>
>

Change decimal point

How can I change decimal point using SQL command ?
hi Ricardo,
Ricardo Luiz wrote:
> How can I change decimal point using SQL command ?
if you mean you want to alter a column in order to add additional
"precision" to a decimal type column, you can perform an ALTER TABLE ALTER
COLUMN statement like
SET NOCOUNT ON;
USE tempdb;
GO
CREATE TABLE dbo.test (
ID int NOT NULL PRIMARY KEY ,
d decimal (18,2) NOT NULL DEFAULT 0
);
INSERT INTO dbo.test VALUES ( 1 , 1.12 );
GO
PRINT 'fails inserting all decimals'
INSERT INTO dbo.test VALUES ( 2 , 1.123 );
SELECT * FROM dbo.test;
GO
ALTER TABLE dbo.test
ALTER COLUMN d decimal (18,4);
INSERT INTO dbo.test VALUES ( 3 , 1.123 );
SELECT * FROM dbo.test;
GO
DROP TABLE dbo.test;
if you like to modify the decimal separator, than you can not, as this
setting is by design and not dependent to the language associated to the
current login as for date formatting..
you can however cast the value to a varchar(n) and replace the separator
with your own costant, but this way you no longer have a decimal type result
but a varchar..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.18.0 - DbaMgr ver 0.62.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Change Column Order

How does one go about changing the column order of the data moving thru an SSIS package? It'd be nice to able to do this at any point in the package for readability, but where I really need it is in the Flat File Output. I need to deliver the columns in a specific order, and I can't figure out how to do that.

This seems to be such a basic question, but I can't find it in Search anywhere. My apologies if it's already been answered.The column order of a flat file is defined within the connection. Changing column order within the pipeline itself would be a bad ideas as this require moving data around, which costs. A way to do this as a view for readability may well be nice, but may end up being confusing as well.

The default behaviour of the Flat File Destination means you create a new connection within the UI, and that connection will happily define the columns according to the buffer, which is just perfect 99% of the time. For your case you have two workarounds-

1 - Add your Flat File Connection, set file and delimiters. Select the Advanced tab, and add your columns as you want in the correct order. This connection can then be selected, and you map the columns. You could do this within the Flat File Destination, as this will create all columns to start with, but cannot change order, which is a shame, so you will have to remove and insert columns to get the correct order overall.

2 - A simple method is to create a flat file that represents what you want to produce from SSIS. Then you can add a new Flat File Connection, and select the sample file, and allow the columns to be generated from this file. You may still wish to fine tune this through the Advanced tab, but it should do the bulk of the work.

Why not log some Feedback on MSDN for the ability to change column order within the Flat File connection as that is really what you want I belive.|||I had the exact same question...and I too couldn't believe that it wasn't simple to re-order the outputs being written to a flat file.

I am pulling data from a database using a SQL Query then adding columns with the Derived Column transform. I want the derived columns to go at the beginning of the output file, not the end.

I like your idea of reverse engineering a sample file...I will give that a shot.

I am currently evaluating ETL tools for converting data from various database types and structures to complex (multiple record types) flat files. Overall, I am pretty impressed with SSIS although I have noticed that some tasks are not as straightforward as they are in Sagent and Informatica.

|||If you are not already aware there are a couple of whitepapers that may help your evaluation-

Microsoft SQL Server: Forrester Report: Microsoft Addresses Enterprise ETL
(http://www.microsoft.com/sql/technologies/integration/foresterreport.mspx)
Well that's one. The other was a review of SSIS compared to Informatica, by Conchango. I can't find it now in the wake of the site updates for RTM. Hopefully it will reappear or someone will post a link. Searching microsoft.com only gives a broken link.

Don't forget you get a free RDBMS, OLAP and Reporting System when you buy SSIS :)|||

Very funny...you never know when that RDBMS may come in handy ; )

Thanks for the Forrester link.

I found the Conchango review....thanks for the lead.
http://download.microsoft.com/download/1/0/3/103fd39e-3ca4-4db7-a087-1263dc6ed0b1/CompIntTools.pdf

Our enterprise product is built pretty much from front to back on Microsoft technology so SSIS will be hard to beat if it can get the job done efficiently.

Change an instance of SQL Server 2005

Hi All,

I have two drive, C and D. When first time I installed SQL Server 2005, I think that I point to C drive which is having only 10 GB, instead of drive D which has bigger space. Now, I have a problem of restoring my database due to lack of space on my C drive. Can anyone guide me to change the instance of database from drive C to drive D.

TIA

I am afraid you want to move data from C: to D: .

Stop sqlserver and move your data file from C: to D: .Default data file locates in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA

Start sql server and delete old database and right Databases ->choose attach ->choose add and select data file on D: ->click ok to finish.

Hope it helps.

|||Or simply sepecify a new location on D drive when restoring the database, just make sure the "Overwrite the existing database" option is checked.|||Thank you guys. It works and I don't have not enough space problem anymore.

Chain Multiplication on a column

Can someone point me out a function that return the multiplication of all row contents from a column?

It should work like the sum() function, but instead of the summary.. it will return the chain multiplication result. Here is what I need it to do.

x
---
4
2
3
7

I need a function "multiply" so that when I issued "Select multiply(x) ...group by..." It would return 168 which is 4*2*3*7

I need to do this in DB2, I checked the DB2 UDF, but looks like there is no simple way to create a customized column function like this.

I'll be really appreciated if someone can help.

Quote:

Originally Posted by janet04

Can someone point me out a function that return the multiplication of all row contents from a column?

It should work like the sum() function, but instead of the summary.. it will return the chain multiplication result. Here is what I need it to do.

x
---
4
2
3
7

I need a function "multiply" so that when I issued "Select multiply(x) ...group by..." It would return 168 which is 4*2*3*7

I need to do this in DB2, I checked the DB2 UDF, but looks like there is no simple way to create a customized column function like this.

I'll be really appreciated if someone can help.

--------------
Reply : select exp(sum(ln(val_num))) from test_multiply;|||select exp(sum(ln(val_num))) from test_multiply;

It will solve your purpose

Certification

Can anyone guide me on how to prepare for exam 229. There is just ton of
information out there. What is a good begining point? I'll appreciate if
someone can send me a link for good mock exams.Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen Delaney.
Read (and understand) it cover-to-cover and you'll be all set.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
> Can anyone guide me on how to prepare for exam 229. There is just ton of
> information out there. What is a good begining point? I'll appreciate if
> someone can send me a link for good mock exams.
>|||ACK
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> schrieb im
Newsbeitrag news:eHnn$tccFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen
> Delaney.
> Read (and understand) it cover-to-cover and you'll be all set.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
>|||agreed.
also try to get the Transcender Exams.
Greg Jackson
PDX, Oregon

Certification

Can anyone guide me on how to prepare for exam 229. There is just ton of
information out there. What is a good begining point? I'll appreciate if
someone can send me a link for good mock exams.
Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen Delaney.
Read (and understand) it cover-to-cover and you'll be all set.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"docsql" <docsql@.noemail.nospam> wrote in message
news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
> Can anyone guide me on how to prepare for exam 229. There is just ton of
> information out there. What is a good begining point? I'll appreciate if
> someone can send me a link for good mock exams.
>
|||ACK
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> schrieb im
Newsbeitrag news:eHnn$tccFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen
> Delaney.
> Read (and understand) it cover-to-cover and you'll be all set.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
>
|||agreed.
also try to get the Transcender Exams.
Greg Jackson
PDX, Oregon

Certification

Can anyone guide me on how to prepare for exam 229. There is just ton of
information out there. What is a good begining point? I'll appreciate if
someone can send me a link for good mock exams.Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen Delaney.
Read (and understand) it cover-to-cover and you'll be all set.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"docsql" <docsql@.noemail.nospam> wrote in message
news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
> Can anyone guide me on how to prepare for exam 229. There is just ton of
> information out there. What is a good begining point? I'll appreciate if
> someone can send me a link for good mock exams.
>|||ACK
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> schrieb im
Newsbeitrag news:eHnn$tccFHA.4028@.TK2MSFTNGP10.phx.gbl...
> Personally, I would get a copy of _Inside SQL Server 2000_ by Kalen
> Delaney.
> Read (and understand) it cover-to-cover and you'll be all set.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "docsql" <docsql@.noemail.nospam> wrote in message
> news:eUBHBdccFHA.3244@.TK2MSFTNGP10.phx.gbl...
>> Can anyone guide me on how to prepare for exam 229. There is just ton of
>> information out there. What is a good begining point? I'll appreciate
>> if
>> someone can send me a link for good mock exams.
>>
>|||agreed.
also try to get the Transcender Exams.
Greg Jackson
PDX, Oregon

CDC Retention time

According to BOL the default CDC retention time is 3 days and it was mentioned that it configurable.

Can some of point me on how to change the default value.

Thanks in advance.

You can use sp_cdc_add_job or sp_cdc_change_job to set the retention for the cleanup job.

Thanks