Getting a final version of a person into a DW

I have about 8 databases to integrate. All of the databases have ssno, address city...ect. I need to create a DW table with one unique record for each actual person. In other words,

Joe Smith,123 Main St, Anytown, State,....+ssno

goes into the DW table and is the same person as Joseph S. Smith,123 Main Street... and any other versions.

Could someone point me to a reference or give me an outline of how to do this in and SSIS package?

Is fuzzy logic used here?

Do I need to deduplicate the feeder systems first?

It needs to handle a situation in, for example, the Bronx New York where there could be an apartment buiding with 7 people named Jose Sanchez .

I hope I've been clear, I'm a newbie at this DW stuff, but it's fascinating. Any help would be appreciated. Thanks

Fuzzy could be of help here. The fuzzy grouping can be used for deduplicating, the fuzzy lookup to check whether you already have a record that resembles your new one.

You will need to spend some time (by testing) to figure out a similarity value that suits your situation. In a DW environment, I think this should be a business decision.

From what I understand, ssno probably needs to be involved. In the lookup (haven't used the grouping yet) you can set similarity values for ssno addressno and name. For example, ssno (or birthdate?) needs a similarity of 1, and the name needs to have a similarity > 0.6

Regards,

Pipo

GetDate() in SQL Server

Hi,
I have a Stored Proc that creates an Unique ID for me.
I pass in an ID and append on other values as below.
select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_' +
convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi, getdate
()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms, getdate())
, 2))
In some cases my Left(datepart(ms, getdate()), 2)) returns the same value (T
his happens approx 1 in 5000 ID's that I create.)
Does anyone know why this is the case? Is there some kind of buffering happe
ning?
Thanks,
C.Time in SQL Server is only accurate to 1/300th of a second, so if you have
two calls to your stored procedure within that timeframe, you will get the
same ID. Downside is that your code doesn't work as expected, upside is that
your server is performing reasonably well ;-)
If you want a truly unique number, you can use a GUID, which you can
generate with NEWID().
Jacco Schalkwijk
SQL Server MVP
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
> Thanks,
> C.|||Using time, even as part of a uniqueID, is a flawed approach. You know that
two events can happen at the same time, especially given SQL Server's loose
accuracy, right? Why do you need such a complex and manual uniqueID anyway?
SQL Server has multiple built-in facilities for this, such as IDENTITY,
GUID...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
> + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
> getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
> getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
> (This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
> happening?
> Thanks,
> C.|||"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
the range of ms is 0-999 and repeats every second ...

GetDate() in SQL Server

Hi,
I have a Stored Proc that creates an Unique ID for me.
I pass in an ID and append on other values as below.
select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_' + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi, getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms, getdate()), 2))
In some cases my Left(datepart(ms, getdate()), 2)) returns the same value (This happens approx 1 in 5000 ID's that I create.)
Does anyone know why this is the case? Is there some kind of buffering happening?
Thanks,
C.
Time in SQL Server is only accurate to 1/300th of a second, so if you have
two calls to your stored procedure within that timeframe, you will get the
same ID. Downside is that your code doesn't work as expected, upside is that
your server is performing reasonably well ;-)
If you want a truly unique number, you can use a GUID, which you can
generate with NEWID().
Jacco Schalkwijk
SQL Server MVP
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
> Thanks,
> C.
|||Using time, even as part of a uniqueID, is a flawed approach. You know that
two events can happen at the same time, especially given SQL Server's loose
accuracy, right? Why do you need such a complex and manual uniqueID anyway?
SQL Server has multiple built-in facilities for this, such as IDENTITY,
GUID...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
> + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
> getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
> getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
> (This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
> happening?
> Thanks,
> C.
|||"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
the range of ms is 0-999 and repeats every second ...

Get Unique Values in a group statement

Hi,
Suppose a table [Inventory]:

Item Color Quantity
------- ------- --------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50

I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:

Table Blue,Red,Yellow 60
Chair Blue,Red 90

Does anyone has an idea how this can be achieved?

Regards,
Manolis PerrakisDoes anyone has an idea how this can be achieved?Ooh ooh ooh - me - pick me!

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
:)|||Hi,
thanks for the answer.
I don't think is a good idea to run a query for each record. This will consume a lot of resources, and in my case when having to do with large tables and a lot of resulted records this is not an option.
I was hoping there was an internal function, of if it can be defined such a funtion. For example if AVG is used the MSSQL access all the records keep their values and at the end calculates the result. Instead of adding these values I was hoping to create a string which can be compared internally without having to execute another query.
Reagrds,
Manolis Perrakis|||Search the text for "Yep, me too... until I tuned it and then it did it all in 5 seconds flat on a million rows for 50,000 CustID's. " and see if the suggested optimisations are appropriate for you.

Ultimately you are taking relational data and trying to put it into a context that violates first normal form so it is not surprising that SQL does not provide a built in function to do this.

The other alternative is that you can write (if you are using SQL Server 2005) your own CLR aggregate functions. I don't really know anything about these (including how they perform) but you could try researching to see if it is applicable to your needs.|||Yes you are right. There is the article:
http://msdn2.microsoft.com/en-us/library/ms131056.aspx
that does exactly this.
However I use MSSQL2000.|||However I use MSSQL2000.I suspect you are probably stuffed then.

I like the article I linked to as it demonstrates, and compares, two methods of skinning this particular cat. In particular it focuses on performance and, as I pointed out, later on one contributer offers a few refinements that get a decent performance for a medium sized table (1 million rows).

I doubt you will find any alternative technique that will substantially outperform the solution in the article but maybe one of the posters here will surprise me :)

BTW - what sort of performance did you get?|||Hi,
I want to use this operation in some aggregate complex queries that are already slow. Apart from this in order to get the correct data when calculating these value I must run these queries with other criteria also, such as date range which must be used in order to get the correct records. Therefore it's quite complicated. However I will try it the next days.
Regards,
Manolis|||I don't suppose there is a finite, known and ideally small number of possible "color" values?|||No, the "color" values is large, about 25000.|||you can get these things in 25000 different colours? Henry Ford would turn in his grave...|||I don't think is a good idea to run a query for each record. Just thought - I don't know how SQL Server optimises the query for this but presumably does as you suggest - runs the function for each record and then groups on the results. So you could improve things (I imagine) with something like:

SELECT Item, dbo.MyConcatFunction(Item) AS CSV_Colors, TotalQuantity
FROM (
SELECT Item, SUM(Quantity) AS TotalQuantity
FROM [Inventory]
GROUP BY Item
) AS Distinct_Items
Call the function once per item rather than once per row.

Get unique sequential number- best practice

I need a number generator. (e.g. for Receipt number, or transaction number,
etc.) in a multiuser high volume envoironment. What is the best way to get
one from SQLserver2005? (no duplicates allowed)
1) I've seen a StoredProc that will get value, value++, then save back,
enclosed in a Transaction. This will work, but locks the table. A little
concerned about the blocking here.
2) Should I do the same without the Transaction and check for changed value
(optimistic lock?)
3) better way ?
Thanks!Look up identity columns. That should satisfy most of your requirements.
Anith|||Assuming you're not happy with the identity column property and for your own
reasons need this to be implemented with a stored procedure...
The locking part of the technique you are talking about is essential if you
need to guarantee no gaps in the sequence. You queue requests for a new
sequence value by locking it for the duration of the transaction. Here's an
example for an implementation of a blocking sequence:
-- Sequence Table
USE tempdb;
GO
IF OBJECT_ID('dbo.SyncSeq') IS NOT NULL
DROP TABLE dbo.SyncSeq;
GO
CREATE TABLE dbo.SyncSeq(val INT);
INSERT INTO dbo.SyncSeq VALUES(0);
GO
-- Sequence Proc
IF OBJECT_ID('dbo.usp_SyncSeq') IS NOT NULL
DROP PROC dbo.usp_SyncSeq;
GO
CREATE PROC dbo.usp_SyncSeq
@.val AS INT OUTPUT
AS
UPDATE dbo.SyncSeq
SET @.val = val = val + 1;
GO
-- Get Next Sequence
DECLARE @.key AS INT;
EXEC dbo.usp_SyncSeq @.val = @.key OUTPUT;
SELECT @.key;
The UPDATE statement in the stored procedure locks the sequence exclusively
and maintains the lock for the duration of the transaction. If running in
the context of an explicit transaction, the lock is maintained until the
explicit transaction finishes.
As an example, suppose connection 1 requests a new sequence value in an
explicit transaction:
BEGIN TRAN
DECLARE @.key AS INT;
EXEC dbo.usp_SyncSeq @.val = @.key OUTPUT;
SELECT @.key;
And gets the sequence value 1
Connection 2 requests a new sequence value and is blocked:
DECLARE @.key AS INT;
EXEC dbo.usp_SyncSeq @.val = @.key OUTPUT;
SELECT @.key;
Connection 1 issues a rollback:
ROLLBACK
Connection 2 gets the sequence value 1 because it was ultimately not used by
connection 1.
You see, if you want to guarantee that there won't be any gaps, you must
queue requests for new sequence values by locking the sequence for the
duration of the transaction.
If you don't care about gaps, rather only want to guarantee uniqueness of
sequence values, you can use a different sequencing logic, based on
identity. You can rely on the fact if a transaction is rolled back, identity
increment is not rolled back as it's not considered part of an explicit
transaction. Here's how you can implement the sequencing mechanism:
-- Sequence Table
USE tempdb;
GO
IF OBJECT_ID('dbo.AsyncSeq') IS NOT NULL
DROP TABLE dbo.AsyncSeq;
GO
CREATE TABLE dbo.AsyncSeq(val INT IDENTITY);
GO
-- Sequence Proc
IF OBJECT_ID('dbo.usp_AsyncSeq') IS NOT NULL
DROP PROC dbo.usp_AsyncSeq;
GO
CREATE PROC dbo.usp_AsyncSeq
@.val AS INT OUTPUT
AS
BEGIN TRAN
SAVE TRAN S1;
INSERT INTO dbo.AsyncSeq DEFAULT VALUES;
SET @.val = SCOPE_IDENTITY();
ROLLBACK TRAN S1;
COMMIT TRAN
GO
-- Get Next Sequence
DECLARE @.key AS INT;
EXEC dbo.usp_AsyncSeq @.val = @.key OUTPUT;
SELECT @.key;
The purpose of the transaction in the stored procedure is to allow defining
a savepoint and rolling back to it without effecting an external
transaction.
The rollback's purpose is to undo the insertion to the sequence table,
preventing the need to clear it from time to time for maintenance. Remember
that the identity increment is not effected by the rollback.
Back to the original example, suppose connection 1 requests a new sequence
value in an explicit transaction:
BEGIN TRAN
DECLARE @.key AS INT;
EXEC dbo.usp_AsyncSeq @.val = @.key OUTPUT;
SELECT @.key;
And gets the sequence value 1
Connection 2 requests a new sequence value and is not blocked, rather gets
the value 2:
DECLARE @.key AS INT;
EXEC dbo.usp_AsyncSeq @.val = @.key OUTPUT;
SELECT @.key;
Connection 1 issues a rollback:
ROLLBACK
At this point you have a gap in your sequence values since the value 1 was
ultimately not used, while 2 was. If you don't care about gaps, this
mechanism provides better concurrency.
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Ronj" <Ronj@.discussions.microsoft.com> wrote in message
news:C1D283D7-2849-4E9A-8F93-6C1F7048128F@.microsoft.com...
>I need a number generator. (e.g. for Receipt number, or transaction number,
> etc.) in a multiuser high volume envoironment. What is the best way to get
> one from SQLserver2005? (no duplicates allowed)
> 1) I've seen a StoredProc that will get value, value++, then save back,
> enclosed in a Transaction. This will work, but locks the table. A little
> concerned about the blocking here.
> 2) Should I do the same without the Transaction and check for changed
> value
> (optimistic lock?)
> 3) better way ?
> Thanks!|||>> I need a number generator. (e.g. for Receipt number, or transaction numbe
r, etc.) in a multiuser high volume envoironment. <<
What kidn of check digit and validatoin are you using? Is this number
exposed in such a way that your need a SOX audit trail? People think
this can be done on one machine with IDENTITY and it really is not that
esy, if you give a damn about doing it right. What IDENTITY says is
that you are planning on never being a large company with many stores
on purpose! The gps will not matter because nobody will ever invest in
the company so there is no need for good auditing and SOX compliance!
Not a great business plan.
Not a problem, really. You can issue blocks of invoice numbers to
stores/salesmen or you can have a generator rule that adds the store,
cash register, timestamp and a sequence number to the sales ticket
(works for Home Depot, et al).
(optimistic lock?) <<
With a computed key like the Home Depot (they are on my mind today --
I just bought some keys), optimistic concurrency control (it is not
really locking) works great. But SQL Server is a pessimistic system by
nature. What to use Firebird or Innerbase instead?
Look up additive congruence generators if you need a random number that
will not repeat. There are some games you can play with those that are
fun.
Again, there is no "Magic, Universal one-size-fits-all" answer. Ever
wonder why each industry has different standards? Different problems!

Get the SQL Server Unique ID of the Server

Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
EdwardIf you mean you want to find the name of the Windows machine that the
SQL instance is running on then you could use "SELECT
SERVERPROPERTY('MachineName')" or if you're after the name of the SQL
instance itself then you could use "SELECT
SERVERPROPERTY('ServerName')", which will return the Windows machine
name and the SQL instance name.
*mike hodgson*
http://sqlnerd.blogspot.com
Edward Low wrote:

>Hi All,
>Is there any ID / Key which identify the server of the SQL Server
>installation uniquely? I need this ID to make sure the database installed i
n
>a SQL server can't be transferred to other SQL Server, for licencing issues
.
>Or, is there other ways to achieve this?
>
>Thanks & Regards,
>Edward
>
>|||Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward|||For SQL Server 2005 you could write an Unsafe CLR procedure which can get th
e Hard drive serial number or any other metric tha you want.
However!? What happens if said hard drive breaks and the server has to be
re-built with a different hard drive and a database backup restored?
Regards
Colin Dawson
www.cjdawson.com
"Edward Low" <wc_low@.hotmail.com> wrote in message news:ORW6yDwfGHA.5088@.TK2
MSFTNGP02.phx.gbl...
Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward|||If you could get the Windows domain name (there's no real kosher way
that I know of to do this purely with SQL (you'd have to shell out to
DOS with xp_cmdshell (yuk), like
exec xp_cmdshell 'echo %USERDOMAIN%'
)) then the domain/machine/instance has to be unique (although you could
argue that different unrelated companies, or individuals, could create
domains with the same name).
Alternately I think the ProductID (stored in the registry under
HKLM\SOFTWARE\Microsoft\Windows\CurrentV
ersion\ProductId) is supposed to
be unique for a Windows installation, so that, combined with the
instance name, ought to be a unique SQL instance. However, once again,
there's no real kosher way to read registry keys. In SQL 2000 you can
use the proc xp_regread, like this:
declare @.ProductId varchar(100)
exec xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows\CurrentVersi
on',
'ProductId',
@.ProductId OUTPUT
select @.ProductId
but this is an undocumented proc and in SQL 2005 Microsoft have changed
its behaviour (the permissions around what keys it can and cannot read)
and so it probably won't work in SQL 2005 (although I just tried it on
both a Dev Edition & Ent Edition of SQL 2005 SP1 (x64) and it worked on
both but the trusted login I used was a local admin so that might have
skewed the result a little). But, in any case, it's an undocumented
proc so you probably shouldn't use it (if you want it to continue
working in future versions of SQL Server) and also, if the SQL instance
is installed on a cluster you'd get a different ProductId value
depending on which node the SQL instance happened to be running on at
the time. There's a couple other reg keys you might be able to read to
get a unique ID (like the 180 byte binary SQL setup checksum) but they
all still have the "read from the registry" problem (and on a cluster
reading from the SQL branches of the registry is very messy because
you've got to look up the installed instances, get the one you want,
look up which reg key that instance has its values stored in, go to that
reg key and get the value you're after).
There's no real nice way I can think of to tie a DB to a specific SQL
instance, not with T-SQL code from within a SQL Server session anyway
(you could store a hash, generated outside of a SQL session like in VB
or C# code for example, of some unique attributes of the SQL
installation, like domainname/servername/instancename or
IPaddress/TCPportnumber but those attributes may, validly, change over
time...).
*mike hodgson*
http://sqlnerd.blogspot.com
Edward Low wrote:
> Hi mike,
> thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a
> unique, mean someone can have the same servername on other machine. I
> need something unique like the hard drive serial no which is unique in
> each machine. can we get this information from sql server?
> Best regards,
> Edward
>
> "Mike Hodgson" <e1minst3r@.gmail.com <mailto:e1minst3r@.gmail.com>>
> wrote in message news:e7vcOyvfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> If you mean you want to find the name of the Windows machine that
> the SQL instance is running on then you could use "SELECT
> SERVERPROPERTY('MachineName')" or if you're after the name of the
> SQL instance itself then you could use "SELECT
> SERVERPROPERTY('ServerName')", which will return the Windows
> machine name and the SQL instance name.
> --
> *mike hodgson*
> http://sqlnerd.blogspot.com
>
> Edward Low wrote:
>|||Hi Mike,
Thanks for your explanation.
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:uOGY$d6fGHA.5092@.
TK2MSFTNGP04.phx.gbl...
If you could get the Windows domain name (there's no real kosher way that I
know of to do this purely with SQL (you'd have to shell out to DOS with xp_c
mdshell (yuk), like
exec xp_cmdshell 'echo %USERDOMAIN%'
)) then the domain/machine/instance has to be unique (although you could arg
ue that different unrelated companies, or individuals, could create domains
with the same name).
Alternately I think the ProductID (stored in the registry under HKLM\SOFTWAR
E\Microsoft\Windows\CurrentVersion\Produ
ctId) is supposed to be unique for a
Windows installation, so that, combined with the instance name, ought to be
a unique SQL instance. However, once again, there's no real kosher way to
read registry keys. In SQL 2000 you can use the proc xp_regread, like this:
declare @.ProductId varchar(100)
exec xp_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Windows\CurrentVersi
on',
'ProductId',
@.ProductId OUTPUT
select @.ProductId
but this is an undocumented proc and in SQL 2005 Microsoft have changed its
behaviour (the permissions around what keys it can and cannot read) and so i
t probably won't work in SQL 2005 (although I just tried it on both a Dev Ed
ition & Ent Edition of SQL 2005 SP1 (x64) and it worked on both but the trus
ted login I used was a local admin so that might have skewed the result a li
ttle). But, in any case, it's an undocumented proc so you probably shouldn'
t use it (if you want it to continue working in future versions of SQL Serve
r) and also, if the SQL instance is installed on a cluster you'd get a diffe
rent ProductId value depending on which node the SQL instance happened to be
running on at the time. There's a couple other reg keys you might be able
to read to get a unique ID (like the 180 byte binary SQL setup checksum) but
they all still have the "read from the registry" problem (and on a cluster
reading from the SQL branches of the registry is very messy because you've g
ot to look up the installed instances, get the one you want, look up which r
eg key that instance has its values stored in, go to that reg key and get th
e value you're after).
There's no real nice way I can think of to tie a DB to a specific SQL instan
ce, not with T-SQL code from within a SQL Server session anyway (you could s
tore a hash, generated outside of a SQL session like in VB or C# code for ex
ample, of some unique attributes of the SQL installation, like domainname/se
rvername/instancename or IPaddress/TCPportnumber but those attributes may, v
alidly, change over time...).
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi mike,
thanks for your reply. But, "SERVERPROPERTY('ServerName')" still not a uniqu
e, mean someone can have the same servername on other machine. I need someth
ing unique like the hard drive serial no which is unique in each machine. ca
n we get this information from sql server?
Best regards,
Edward
"Mike Hodgson" <e1minst3r@.gmail.com> wrote in message news:e7vcOyvfGHA.4304@.
TK2MSFTNGP05.phx.gbl...
If you mean you want to find the name of the Windows machine that the SQL in
stance is running on then you could use "SELECT SERVERPROPERTY('MachineName'
)" or if you're after the name of the SQL instance itself then you could use
"SELECT SERVERPROPERTY('ServerName')", which will return the Windows machin
e name and the SQL instance name.
mike hodgson
http://sqlnerd.blogspot.com
Edward Low wrote:
Hi All,
Is there any ID / Key which identify the server of the SQL Server
installation uniquely? I need this ID to make sure the database installed in
a SQL server can't be transferred to other SQL Server, for licencing issues.
Or, is there other ways to achieve this?
Thanks & Regards,
Edward

Get next unique ID from a table before insert @@identity / Sequence

How do I get the next int value for a column before I do an insert in
MY SQL Server 2000? I'm currently using Oracle sequence and doing
something like:

select seq.nextval from dual;

Then I do my insert into 3 different table all using the same uniqueID.

I can't use the @.@.identity function because my application uses a
connection pool and it's not garanteed that a connection won't be used
by another request so under a lot of load there could be major problems
and this doens't work:

insert into <table>;
select @.@.identity;

This doesn't work because the select @.@.identity might give me the value
of an insert from someone else's request.

Thanks,

BrentOn 16 Mar 2005 14:58:25 -0800, brent.ryan@.gmail.com wrote:

>How do I get the next int value for a column before I do an insert in
>MY SQL Server 2000? I'm currently using Oracle sequence and doing
>something like:
>select seq.nextval from dual;
>Then I do my insert into 3 different table all using the same uniqueID.
>I can't use the @.@.identity function because my application uses a
>connection pool and it's not garanteed that a connection won't be used
>by another request so under a lot of load there could be major problems
>and this doens't work:
>insert into <table>;
>select @.@.identity;
>This doesn't work because the select @.@.identity might give me the value
>of an insert from someone else's request.
>Thanks,
>Brent

Hi Brent,

Create a stored procedure that starts a transaction, inserts into the
first table, retrieves the identity value used (with SCOPE_IDENTITY, the
recommended method in SQL Server 2000), uses it to insert data into the
other two table, then commits the transaction (or rolls it back if
anything went wrong).

Calling the server three times for three inserts is not only incurring
the overhead of more roundtrips then necessary, you also run the risk of
getting corrupted data: if one insert fails and the others succeed,
you'll have incomplete data in your database. Always include related
modifications in a transaction. And if each call to the database can use
a different connection, then the complete operation, from start to end
of transaction, needs to be done in one call, as transactions are tied
to the connection.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||(brent.ryan@.gmail.com) writes:
> insert into <table>;
> select @.@.identity;
> This doesn't work because the select @.@.identity might give me the value
> of an insert from someone else's request.

No, @.@.identity is local to the connection, so it cannot be someone
else's value. Well, if you submit to queries and close your connection
in between, it won't work, but that would be poor practice anyway.

Hugo's suggestion of using a stored procedure is an excellent idea.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Thu, 17 Mar 2005 22:57:45 +0000 (UTC), Erland Sommarskog
<esquel@.sommarskog.se> wrote:

> (brent.ryan@.gmail.com) writes:
>> insert into <table>;
>> select @.@.identity;
>>
>> This doesn't work because the select @.@.identity might give me the value
>> of an insert from someone else's request.
>No, @.@.identity is local to the connection, so it cannot be someone
>else's value. Well, if you submit to queries and close your connection
>in between, it won't work, but that would be poor practice anyway.
>Hugo's suggestion of using a stored procedure is an excellent idea.

Excuse me for butting in here, Erland, but there is one 'little'
problem that I have found with @.@.IDENTITY that I can't see referred to
anywhere, and that anyone relying on it should know about, and that is
that @.@.IDENTITY can return unexpected values in certain circumstances.

In the supplied example:

insert into <table>
select @.@.identity

BEAWRE!
If there is a trigger fired during the insert on <table>, and the
trigger performs an insert itself, then @.@.IDENTITY will return the ID
from the Trigger's insert, not the <table> insert.

This caused me many to lose much more hair than I can afford!

It behaves this way in SQL Server 7, and 2000.

Here is a script to create a test data base:
(Make a new blank database, I called it "Test")

=============================
/****** Object: Table [dbo].[MainTable] Script Date: 18/03/2005
3:10:38 PM ******/
CREATE TABLE [dbo].[MainTable] (
[MainTableId] [int] IDENTITY (1, 1) NOT NULL ,
[LongName] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[TriggerTable] Script Date: 18/03/2005
3:10:39 PM ******/
CREATE TABLE [dbo].[TriggerTable] (
[TriggerTableId] [int] IDENTITY (666, 1) NOT NULL ,
[TriggerRowLongName] [nvarchar] (255) NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TriggerTable] WITH NOCHECK ADD
CONSTRAINT [PK_TriggerTable] PRIMARY KEY CLUSTERED
(
[TriggerTableId]
) ON [PRIMARY]
GO
/****** Object: Stored Procedure dbo.Test_sp Script Date:
18/03/2005 3:10:39 PM ******/
CREATE PROCEDURE dbo.Test_sp
AS
INSERT INTO MainTable (LongName) VALUES ('TestLongName')
SELECT @.@.IDENTITY
GO
/****** Object: Trigger dbo.MainTable_Trigger1 Script Date:
18/03/2005 3:10:39 PM ******/
CREATE TRIGGER MainTable_Trigger1
ON dbo.MainTable
FOR INSERT,UPDATE,DELETE
AS
INSERT INTO TriggerTable (TriggerRowLongName) VALUES ('Stuff')
GO
=============================

Then, if one executes [Test_sp] in Query Analyser,

EXEC Test_sp

the returned @.@.IDENTITY is not 1, as you would expect, (this is ID of
the new MainTable row), but 666, which is the ID of the row inserted
via the trigger!
(I seeded this table's identity to begin at 666, in order to show up
clearly)

I would be interested if you were aware of this tiny problemette.|||Michael Gray (fleetg@.newsguy.spam.com) writes:
> Excuse me for butting in here, Erland, but there is one 'little'
> problem that I have found with @.@.IDENTITY that I can't see referred to
> anywhere, and that anyone relying on it should know about, and that is
> that @.@.IDENTITY can return unexpected values in certain circumstances.
> In the supplied example:
> insert into <table>
> select @.@.identity
> BEAWRE!
> If there is a trigger fired during the insert on <table>, and the
> trigger performs an insert itself, then @.@.IDENTITY will return the ID
> from the Trigger's insert, not the <table> insert.

Yes, this is a correct observation. For this reason, you should use
scope_identity() instead. This function was introduced in SQL 2000.

scope_identity() returns the most recently generated IDENTITY in the
current scope, that is a trigger, stored procedure, block of dynamic
SQL etc.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp