@@identity

Consider Table A having an Identity column.
If when I want insert a record to the table, I want to have the autogenerated value to be inserted into another column within the same table and same row, how am I going to do it?
rignhomCheck @.@.Identity from BOOKS ONLINE to get last-inserted identity value.|||Originally posted by Satya
Check @.@.Identity from BOOKS ONLINE to get last-inserted identity value.

Actually, to be safer, I have found that SCOPE_IDENTITY() is better than @.@.IDENTITY. Both do the same thing, but if you happen to have 2 identities being created within different execution scopes, SCOPE_IDENTITY always seems to return the correct one.|||Hi, what I want is
consider TableA with (Column1 Identity, Column2, Column3).

When I insert into this table
I want to issue sort of

INSERT INTO jobs (Column2,Colum3)
VALUES ('Col2Value', @.@.IDENTITY)

I want Column3 to have the same value as the newly autogenerated value for Column1.

Thanks
rignhom|||CREATE TRIGGER TRG_NAME ON TableA
FOR INSERT
AS
BEGIN
DECLARE @.ID BIGINT
SELECT @.ID = Column1 FROM INSERTED

UPDATE TableA
SET Column3 = @.ID
WHERE Column1 = @.ID
END|||if Column3 is a does not allow Null, would this trigger be useful?|||I don't know of another way of retrieving the id value, because the values for either @.@.identity and scope_identity() are only know after the insert in the table. To solve your problem you can define a default value of let's say 0 (zero), this value will then be overwritten by the trigger.|||hmm.. that's a way.. but would the cost be too high, since there's two write. And it may hit concurrency as well..|||Try this, use a computed column over a trigger

create table blah (ikey int identity(1,1), column2 varchar(20),column3 as ikey)

insert into blah select 'joe'

select * from blah

HTH|||Try this, use a computed column over a trigger

create table blah (ikey int identity(1,1), column2 varchar(20),column3 as ikey)

insert into blah select 'joe'

select * from blah

Can you explain what the intention is? Does your insert consists of two selects? How do you know the value for column3 before the actual insert?|||If you run this after running my prior SQL

select * from syscolumns where name in ('ikey','column2','column3')

You will see that ikey and column 3 are almost identical (except for xoffset which I am not sure what it's for, BOL says internal use only)

That makes me think that in one insert it is seeing the insert of those two columns as the same, that's why you are able to specify the insert with only one value even though you have three columns. Not sure if that is answering your question and I'm not sure exactly how it works but I know it's faster and preferred than a Trigger.|||nice ...sql

@@identity

I have a problem hopefully someone can help with...
I have a stored procedure which ...
Writes to table A (table A has an identity column)
Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
Updates a table; setting column X to the IDENTITY value just retrieved
Table A has a couple of triggers on it, one of which writes to a different
table - and that table has an identity column.
Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
identiy column for the table I wrote to (TABLE A), it returns the identity
value of the insert inside the triggers.
Any suggestions on how I get the correct value after writing to table A?
Hope this makes some sense!
Thanks> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
There is a pretty good explanation in the BOL about how it works
If you insert a value into TableA by using @.@.scope_identity() function you
will get the last values of the identity column of the TableA
"..." <...@.nowhere.com> wrote in message
news:emnME2LAGHA.3496@.TK2MSFTNGP11.phx.gbl...
>I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
>
>|||.... wrote:

> I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
If you reference SCOPE_IDENTITY() in the proc then it will return the
last IDENTITY value inserted in the current scope (not in the trigger).
If you reference @.@.IDENTITY it will return the IDENTITY value from ANY
scope (including the trigger).
To reference the TableA IDENTITY value in the trigger itself don't use
either of those functions. Refer to the INSERTED virtual table instead.
Your trigger code should accommodate the fact that there may be more
than one row inserted.
There is no such function as @.@.SCOPE_IDENTITY.
David Portas
SQL Server MVP
--|||SCOPE_IDENTITY should work for you
If you want the latest identity regardless of scope and session then
take a look at IDENT_CURRENT
IDENT_CURRENT returns the last identity value generated for a specific
table in any session and any scope.
You would use it like this: SELECT IDENT_CURRENT('TableA') --TableA
would be your table name
http://sqlservercode.blogspot.com/|||not sure what @.@.scope_identity is -- SCOPE_IDENTITY() is the correct
function.
@.@.IDENTITY should have the last inserted identity value (the one
inserted into from the trigger)
SCOPE_IDENTITY() has last inserted identity from your session, so when
you check it, it should be as soon after the related insert as possible.
If this still doesn't help, could you post the proc and trigger code?
This will help posters give better answers.
... wrote:
> I have a problem hopefully someone can help with...
> I have a stored procedure which ...
> Writes to table A (table A has an identity column)
> Gets the identity column using @.@.IDENTITY (OR @.@.SCOPE_IDENTITY)
> Updates a table; setting column X to the IDENTITY value just retrieved
> Table A has a couple of triggers on it, one of which writes to a different
> table - and that table has an identity column.
> Using either @.@.IDENTITY or @.@.SCOPE_IDENTITY I cannot get it to return the
> identiy column for the table I wrote to (TABLE A), it returns the identity
> value of the insert inside the triggers.
> Any suggestions on how I get the correct value after writing to table A?
> Hope this makes some sense!
> Thanks
>
>

@@DBTS correct usage

Hi,

Suppose my Test table has a timestamp (rowversion) column, and I have the following code:

INSERT INTO Test ...

SELECT @.@.DBTS as 'TS'

Can I be sure the TS return column value contains the value of the timestamp column of the Test table? I suspect no in general (although it will work "most of the time"), but I would like a confirmation.

If I am correct, and there may be a mismatch, is there any clever way of getting the exact value, except from doing a SELECT WHERE ...?

Thanks.

Can I be sure the TS return column value contains the value of the timestamp column of the Test table?

If the BOL said "@.@.DBTS Returns the value of the current timestamp data type for the current database", then you couldn't.

Anyway, there are some other ways to find the records have been inserted.

|||

INSERT INTO Test ...

SELECT @.@.DBTS as 'TS'

You are correct, this will not work in a lot of cases since the timestamp (better to start using the rowversion synonym too) value is shared amongst all tables. It would require a database lock to prevent this from possibly failing

Doing a SELECT is the best way, and will almost always be really fast since:

1. You will likely have the primary key value
2. The row will be in cache since you just created it.

@@DBTS

Hi

There are tables in our database with timestamp column. As and when rows are updated/inserted in these tables, the timestamp column is getting populated which is getting reflected in the @.@.DBTS system variable.

However when we take the FULL database backup and restore the database, the @.@.BSTS is not showing the same timestamp value as it was when full DB backup was done (the behavior is unpredictable - once i did manage to get the correct @.@.DBTS value).

Does backup and restore process have any effects on the @.@.DBTS system variable.

Any guidance on parameter settings for Backup and restore will be appreciated to resolve this issue.

Cheers

Nishant Hate

I am assuming that is because Timestamp is a binary datatype used for row versioning, if you want the timeback you need to use datetime datatype. If you go to the Timestamp section of the link below Microsoft explains it in detail. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms191240.aspx

|||

Hi

Thanks for your prompt response.

Let me give you background on the issue

There is a source system which is already developed from which i have to take incremental data every day and load into my warehouse.

There are no datetime fields in the source system for me to use and there is no scope for us to get the source system modified,

however each table is having the timestamp column which should give me daily new/updated records in the source system.

We can acheive this by storing the last loaded Timestamp in a control table and getting the latest timestamp using @.@.DBTS

Using this range we can get the incremental data from the source system.

This works fine.

However assuming that there is some issue with the source system and the database has to be restored from the backup.

Ideally i would have liked the @.@.DBTS to have the same value that was there when the backup was taken.

But the @.@.DBTS value is not coming the same infact it moves forward.

Logically @.@.DBTS moving forward should also be fine as I am interested in the records within the range and all the records will be included in the range.

So the question now is should I rely on @.@.DBTS even though it is different from the one which was there at the time of backup.

or am i better off to aviod this because of its unpredictable nature and build something to simulate the @.@.DBTS functionality i.e. Take max(timestamp) across all tables.

Please do let me know your views.

Cheers

Nishant hate

on parameters

Hi,
I have 3 parameters-
columnname -takes the column name like name, course, marks
operator - takes the operator - <.>.<= value - takes the value like 30,computers , english etc
i want to pass these parameter to a table and filter the value out.
how can i make the operator as a parameter ?
in the filter expression -
i can give in the column name - column parameter
value - value parameter
but how do i give the operator ?
Thanks
RPIlJQIiA8UlBAZGlzY3Vzc2lvbnMubWljcm9zb2Z0LmNvbT4gd3JvdGUgDQo+IEkgaGF2ZSAzIHBh
cmFtZXRlcnMtDQo+IGNvbHVtbm5hbWUgICAtdGFrZXMgdGhlIGNvbHVtbiBuYW1lIGxpa2UgbmFt
ZSwgY291cnNlLCBtYXJrcw0KPiBvcGVyYXRvciAtIHRha2VzIHRoZSBvcGVyYXRvciAtIDwuPi48
PQ0KPiB2YWx1ZSAtIHRha2VzIHRoZSB2YWx1ZSBsaWtlIDMwLGNvbXB1dGVycyAsIGVuZ2xpc2gg
ZXRjDQo+IA0KPiBpIHdhbnQgdG8gcGFzcyB0aGVzZSBwYXJhbWV0ZXIgdG8gYSB0YWJsZSBhbmQg
ZmlsdGVyIHRoZSB2YWx1ZSBvdXQuDQo+IGhvdyBjYW4gaSBtYWtlIHRoZSBvcGVyYXRvciBhcyBh
IHBhcmFtZXRlciA/DQo+IGluIHRoZSBmaWx0ZXIgZXhwcmVzc2lvbiAtDQo+IGkgY2FuIGdpdmUg
aW4gdGhlIGNvbHVtbiBuYW1lIC0gY29sdW1uIHBhcmFtZXRlcg0KPiB2YWx1ZSAtIHZhbHVlIHBh
cmFtZXRlcg0KPiBidXQgaG93IGRvIGkgZ2l2ZSB0aGUgb3BlcmF0b3IgPw0KDQp0cnkgdG8gd3Jp
dGUgeW91IHF1ZXJ5IGFzIHN0cmluZy4NCkluc3RlYWQgb2YgDQpTRUxFQ1QgKiBGUk9NIGR1bW15
IFdIRVJFIC4uLi4NCndyaXRlDQo9IlNFTEVDVCAqIEZST00gZHVtbXkgZCBXSEVSRSAiICtQYXJh
bWV0ZXJzIXBBZ2VudENvZGUuY29sdW1ubmFtZSAgKyBQYXJhbWV0ZXJzIXBBZ2VudENvZGUub3Bl
cmF0b3IgKyBQYXJhbWV0ZXJzIXBBZ2VudENvZGUudmFsdWUgDQo=|||b29wcw0KcXVlcnkgbXVzdCBsb29rIGxpa2UgdGhpczoNCg0KID0iU0VMRUNUICogRlJPTSBkdW1t
eSBkIFdIRVJFICIgK1BhcmFtZXRlcnMhY29sdW1ubmFtZS52YWx1ZSArIFBhcmFtZXRlcnMhb3Bl
cmF0b3IudmFsdWUgKyBQYXJhbWV0ZXJzIXZhbHVlLnZhbHVlIA0K

::FN_DBLOG() - Problem in retreiving [row data]

Subj: ::FN_DBLOG() - Problem in retreiving [row data]
Hello
While attempting to retrieve [row data] column using ::FN_DBLOG
an empty '0x' value is passed back:
select [Current Lsn], [row data] from ::fn_dblog(null,null)
The retrived record does contain row data. It can be seen when using
DBCC LOG.
However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
a standard SQL query. In addition - DBCC seems to be a more resource
consuming
piece.
Please do not propse a 3rd party software solution.
I need the [row data] 'as is' in its very basic binary raw format and
::FN_DBLOG() fits exactly to my needs.
Any help will be appreciated.
Regards
Hillel.Both fn_dblog() and dbcc log are undocumented and therefore unsupported
features of SQL Server, so in all cases you are on you own.
If you can send me a repro I will look at this. What are you trying to do
with that information anyhow?
GertD@.SQLDev.Net
"Hillel" <hillel.eilat@.gmail.com> wrote in message
news:1144768876.281792.244330@.e56g2000cwe.googlegroups.com...
> Subj: ::FN_DBLOG() - Problem in retreiving [row data]
> Hello
> While attempting to retrieve [row data] column using ::FN_DBLOG
> an empty '0x' value is passed back:
> select [Current Lsn], [row data] from ::fn_dblog(null,null)
> The retrived record does contain row data. It can be seen when using
> DBCC LOG.
> However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
> a standard SQL query. In addition - DBCC seems to be a more resource
> consuming
> piece.
> Please do not propse a 3rd party software solution.
> I need the [row data] 'as is' in its very basic binary raw format and
> ::FN_DBLOG() fits exactly to my needs.
> Any help will be appreciated.
> Regards
> Hillel.
>sql

::FN_DBLOG - Retreiving [row data]

Subj: ::FN_DBLOG - Retreiving [row data]
Hello
While attempting to retrieve [row data] column using ::FN_DBLOG
an empty '0x' value is passed back.
The retrived record does contain row data. It can be seen when using DBCC LO
G.
However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
a standard SQL query. In addition - DBCC seems to be a more resource
consuming
piece.
Please do not propse a 3rd party software solution.
I need the [row data] 'as is' in its very basic binary raw format and ::
FN_DBLOG() fits exactly to my needs.
Any help will be appreciated.
Regards
Hillel.Hi hillel,
I don't know exactly what you are looking for but launching this you can
recover the log info:
SELECT * FROM ::fn_dblog(null,null)
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"hillel" wrote:

> Subj: ::FN_DBLOG - Retreiving [row data]
> Hello
> While attempting to retrieve [row data] column using ::FN_DBLOG
> an empty '0x' value is passed back.
> The retrived record does contain row data. It can be seen when using DBCC
LOG.
> However - DBCC LOG is not as versatile as ::FN_DBLOG(): which supports
> a standard SQL query. In addition - DBCC seems to be a more resource
> consuming
> piece.
> Please do not propse a 3rd party software solution.
> I need the [row data] 'as is' in its very basic binary raw format and ::
> FN_DBLOG() fits exactly to my needs.
> Any help will be appreciated.
> Regards
> Hillel.
>|||Thank you Enric.
My question is very specific.
In SQL 2000 - there are 2 interfaces by which I can access the transaction
LOG:
DBCC LOG is not a very convinient API in many aspects.
::FN_DBLOG() is much more tempting but - surprisingly - it does not display
the [row data] columns - as seen below:
SELECT [Current LSN],[Transaction ID],[Object Name],[row data]
FROM ::fn_dblog(null,null) where operation='LOP_INSERT_ROWS'
0000000b:0000018b:0002 0000:000005bf dbo
.jobs (1977058079) 0x
0000000b:00000190:0002 0000:000005c1 dbo
.jobs (1977058079) 0x
0000000b:00000190:0003 0000:000005c1 dbo
.jobs (1977058079) 0x
There is data associated with these records - as seen below:
dbcc log(DB5,3,logrecs,'LOP_INSERT_ROWS')
0000000b:0000018b:0002 ...0000:000005bf... dbo.jobs (1977058079)...
0x3000080025004749040000010030004261636B
75705F313931393139313931393139313931
3931393139313931393139
Did I miss something in the ::FN_DBLOG() invocation - or- what...?
Thanks
Hillel.
"Enric" wrote:
> Hi hillel,
> I don't know exactly what you are looking for but launching this you can
> recover the log info:
> SELECT * FROM ::fn_dblog(null,null)
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)
>
> "hillel" wrote:
>

.NET class to hold single disconnected record? Nothing smaller than DataSet?

What is the most efficient standalone .NET class that can hold a single disconnected record? The class must also retain column names, but other schema is not relevant (.NET data type is sufficient).

If I understand System.Data.Common.DbDataRecord, it provides an interface on a DbDataReader, and has no storage of its own.

I'm familiar with DataSet, is that the only .NET-standard class to do this?

Thank you,

Shannon

Why not create a normal class with properties and fill it from a SqlDataReader, or use a OR-Mapper..?

|||

I second N Frederick 's suggestiong, nothing will be faster (or easier) than a datareader populating a regular class

|||

Thanks for your reply. If I understand correctly, you are suggesting that in most application-specific design scenarios, a strongly-typed class representing my scenario data object is proscribed? "normal class" assumes that I would benefit from get/set properties for myself to code against. And O/R mapper suggestion to accomplish the same.

I'm building a generic server control (an in-place editor with HTML client-side functionality). I just want something to do table-based forms against for now.

My comment that I need to store data type and column name result from the fact that I won't know at design-time (of the server control) what they are.

p.s. If you are aware of a free .NET library to do this already, I'm all ears.

|||

TwasBrillig:

you are suggesting that in most application-specific design scenarios, a strongly-typed class representing my scenario data object is proscribed? "normal class" assumes that I would benefit from get/set properties for myself to code against. And O/R mapper suggestion to accomplish the same

Speaking for myself, I wouldn't overanalyze. The requirement you gave originally is very simple, so solve it with something very simple. OTOH....

TwasBrillig:

comment that I need to store data type and column name result from the fact that I won't know at design-time (of the server control) what they

that's a different story, if you don't know at design time what you're getting back then you can't put it in a predefined class. I suspect that your requirements are much more complex than yo've get on

|||

dbland07666:

TwasBrillig:

you are suggesting that in most application-specific design scenarios, a strongly-typed class representing my scenario data object is proscribed? "normal class" assumes that I would benefit from get/set properties for myself to code against. And O/R mapper suggestion to accomplish the same

Speaking for myself, I wouldn't overanalyze. The requirement you gave originally is very simple, so solve it with something very simple. OTOH...

That's the second cryptic reply that implies in an offhand way that the answer is simple, but doesn't answer. I hear you, the answer is 42. But what does it mean? Sure I'd love to do something very simple; What is very simple to store one record, for goodness sake? A DataSet? That was my question.

dbland07666:

TwasBrillig:

comment that I need to store data type and column name result from the fact that I won't know at design-time (of the server control) what they

that's a different story, if you don't know at design time what you're getting back then you can't put it in a predefined class. I suspect that your requirements are much more complex than yo've get on

Not knowing the type is different from not knowing the structure. A record. One table record. Sure I can build a class myself to do it, I can iterate through columns and store Column-name:Object pairs in a dictionary list, and inspect later what type is in the box. No, my requirements aren't more complex. But my question is: Is there a class already in the .NET library to hold one record, one I can copy one record into with a single method call? When I'm done I have one record in storage, and can disconnect my DbDataReader and think about my one record, its columns' values, its columns' data types, and their names for as long as I like?

|||

Here are the types in ADO.Net that you can use:

DataSet = Table, rows and columns, a disconnected resultset that more or less reflects a databas but in memory.

DataReader = Need a connection and will read a row on command, so it will only stay at a specific position and move to the next recoed when you ask it to do it. See it as a Row, Column based object.

You can also use DataSet with XML etc.

Then you can create your own classes (custom business objects "entities"), such as a Customer, Product etc.

In your case where you specify that you can't use a custom class, I think you should simply use a DataSet.

|||

Thank you Fredrik, that absolutely answered my question. I appreciate you taking your time answering something so basic.

I felt like there must be another class out there that I had overlooked in the library, or some other magic to DbDataRecord that I wasn't aware of.

If I'm not mistaken, I could reliably use DataReader to access the current row after disconnecting it if I only ever asked it for the values, and not any schema information (such as column name or data type).

Thanks again!

.:: MS-SQL, JTurbo and autoincrement columns ::.

Hi all!
Does anyone knows how to get if a column is autoincrement or not, using the DatabaseMetaData in java, or using de JTurbo driver for it?
Is there any other way to figure this out?
:confused:
Thank you very much in advance for any replies.select columnproperty(object_id('table_name'), 'column_name', 'isidentity')

... or changing system table

if ALTER myTable ALTER COLUMN id ...
to remove IDENTITY from a column is not possible (what i fear), can i
change this in a system table?
HelmutHelmut
Why ? It is strongly not recommended to alter system tables.
Why don't you want to move the data to the new column and then drop the old
one?
"Helmut Woess" <user22@.inode.at> wrote in message
news:sdwgal2frk9n$.gvo98pu5gu99$.dlg@.40tude.net...
> if ALTER myTable ALTER COLUMN id ...
> to remove IDENTITY from a column is not possible (what i fear), can i
> change this in a system table?
> Helmut|||Am Sun, 30 Apr 2006 14:50:06 +0300 schrieb Uri Dimant:

> Why ? It is strongly not recommended to alter system tables.
> Why don't you want to move the data to the new column and then drop the o
ld
> one?
Okay, here the whole story: i have a special stored proc called by the
trigger, and in this stored proc i need the data from temp tables deleted
and inserted.
For this i did a "insert into #ins select * from inserted" (and the same
for deleted) and used the data in #ins/#del in the stored proc. This is
working, but it needs too much time and ressource, to create and drop this
temp tables in every call of the trigger.
So i thougt, i create a global ##ins/##del at first call of the trigger:
if object_id('tempdb..##table1_ins') is Null -- create table
select * into ##table1_ins from inserted where 1 = 2
...
and then there is no need to create and drop the temp tables in each
execution of the trigger.
But if i do this, i cannot longer do a "insert into #ins select * from
inserted" i must do a "select * into ##table1_ins from inserted".
And my problem is now, that every record has as first field a field named
id and this is an identity field. So a "select * into ##table1_ins from
inserted" is not working because of the identity field.
But if i can change this and remove the identity property it should work.
It doesn't work if i drop this column and add it again, because it is added
at the end of the record and not at the beginning, so a "select * into..."
would not longer be possible.
And i don't want to set identity_insert ON and OFF and use a field list for
the select statement, because i have more than 120 tables where i need this
solution and some of the records have nearly 200 fields, so i need a
universal solution to bring the data from temp tables inserted and deleted
into the stored proc. In this misery i would even change system tables if
this helps.
Maybe you have a better idea how to do this?
thanks, Helmut|||Helmut
As I see you are using SQL Server 2000, am I right? In SQL Server 2005 you
have a new functino ROW_NUMBER() which provides you a sequential number of a
row within a partition of a result set.
Well, I don't know your business requirements , however I'd redesign an
approach of calling stored procedures within a trigger and morepver creating
a temporaring tables.
Now, can you create a surrogate key in your tables that you will see in
'deleted' and 'inserted' virtual tables , so there is no need to add am
identity property at all, or if it does not help please post the code and
additional info about what are you trying to achive.
"Helmut Woess" <user22@.inode.at> wrote in message
news:ket7d7xavej9$.1niowodavl99.dlg@.40tude.net...
> Am Sun, 30 Apr 2006 14:50:06 +0300 schrieb Uri Dimant:
>
> Okay, here the whole story: i have a special stored proc called by the
> trigger, and in this stored proc i need the data from temp tables deleted
> and inserted.
> For this i did a "insert into #ins select * from inserted" (and the same
> for deleted) and used the data in #ins/#del in the stored proc. This is
> working, but it needs too much time and ressource, to create and drop this
> temp tables in every call of the trigger.
> So i thougt, i create a global ##ins/##del at first call of the trigger:
> if object_id('tempdb..##table1_ins') is Null -- create table
> select * into ##table1_ins from inserted where 1 = 2
> ...
> and then there is no need to create and drop the temp tables in each
> execution of the trigger.
> But if i do this, i cannot longer do a "insert into #ins select * from
> inserted" i must do a "select * into ##table1_ins from inserted".
> And my problem is now, that every record has as first field a field named
> id and this is an identity field. So a "select * into ##table1_ins from
> inserted" is not working because of the identity field.
> But if i can change this and remove the identity property it should work.
> It doesn't work if i drop this column and add it again, because it is
> added
> at the end of the record and not at the beginning, so a "select * into..."
> would not longer be possible.
> And i don't want to set identity_insert ON and OFF and use a field list
> for
> the select statement, because i have more than 120 tables where i need
> this
> solution and some of the records have nearly 200 fields, so i need a
> universal solution to bring the data from temp tables inserted and deleted
> into the stored proc. In this misery i would even change system tables if
> this helps.
> Maybe you have a better idea how to do this?
> thanks, Helmut|||Am Sun, 30 Apr 2006 16:08:39 +0300 schrieb Uri Dimant:

> Helmut
> As I see you are using SQL Server 2000, am I right? In SQL Server 2005 you
> have a new functino ROW_NUMBER() which provides you a sequential number of
a
> row within a partition of a result set.
> Well, I don't know your business requirements , however I'd redesign an
> approach of calling stored procedures within a trigger and morepver creati
ng
> a temporaring tables.
> Now, can you create a surrogate key in your tables that you will see in
> 'deleted' and 'inserted' virtual tables , so there is no need to add am
> identity property at all, or if it does not help please post the code and
> additional info about what are you trying to achive.
>
Uri,
you are right, it is SQL2000 (but we want to change to 2005 the next
ws).
What i have to solve: log all inserts/changes/deletes
Because i have to do this for a lot of tables i need an universal solution.
For this i use a trigger, the trigger only puts data from inserted/deleted
into temp tables and calls a stored proc which makes the rest.
This is because if something changes, i have only the stored proc to change
and not hundred of triggers.
The business is now, how can i bring the data from inserted/deleted as fast
as possible in an universal way into this stored proc.
And i am figthing with the fact that that every of this tables has this
identity field as first field. I don't want to add identity attribute, i
want to remove it!
Because of automation, temp table ##tbl is created as
"select * into ##tmp from inserted where 1 = 2"
which creates an empty copy of inserted, unfortunately with identity
attribute too.
Then i want to copy data from inserted into #tbl with
"insert into ##tbl select * from inserted"
which is not possible as long as ##tbl has this identity attribute too.
If you tell me that it is not possible in a simple way in SQL2000, then i
could stop this work for SQL2000 and search for a solution in SQL2005.
What do you think, will it be easier in SQL2005 (maybe using CLR)?
sorry, i cannot post complete code (company ownership, you know)
thanks, Helmut|||Helmut Woess (user22@.inode.at) writes:
> Okay, here the whole story: i have a special stored proc called by the
> trigger, and in this stored proc i need the data from temp tables deleted
> and inserted.
> For this i did a "insert into #ins select * from inserted" (and the same
> for deleted) and used the data in #ins/#del in the stored proc. This is
> working, but it needs too much time and ressource, to create and drop this
> temp tables in every call of the trigger.
> So i thougt, i create a global ##ins/##del at first call of the trigger:
> if object_id('tempdb..##table1_ins') is Null -- create table
> select * into ##table1_ins from inserted where 1 = 2
> ...
Use a permanent table, keyed by spid instead.
Read more about it here:
http://www.sommarskog.se/share_data.html#prockeyed
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

*unsigned* 32-bit column type?

I need to stored lots of unsigned 32-bit values. (I'm actually storing IPv4 addresses). These values will frequently exceed 2^31 but never exceed 2^32

My options are:

Use bigint. This is undesirable since this wastes 32-bits of space for every value.
Use int and use the negative value range to get full 32-bits worth of data. In C, it is easy and fast to cast a signed -1 to an unsigned 2^32-1. In SQL, it will be more expensive: Must cast to 64-bit and if val < 0 then val = 2^32 + val.

Is there a better alternative?

Is there a fast (binary) way to cast a value > 2^31 to a negative signed value?

DECLARE @.value BIGINT
SET @.value = 3000000000

SELECT CONVERT(INT, @.value) -- causes error
SELECT CAST(@.value AS INT) -- causes errorIf it's IP addresses, why don't you break them into parts and store each part in TINYINT? For any sub-mask value you would take only 4 bytes.|||The quick and dirty answer is to do a C union to make both the UINT 32 and INT 32 values easily accessible.

-PatP|||If it's IP addresses, why don't you break them into parts and store each part in TINYINT? For any sub-mask value you would take only 4 bytes.

The quick and dirty answer is to do a C union to make both the UINT 32 and INT 32 values easily accessible.

Thanks guys. I'll stick with the signed 32-bit column and some ugly code to convert between unsigned 32-bit values.

Using 4 columns makes for uglier joins and queries. And Pat, I don't quite follow your approach.

CREATE FUNCTION parseIPv4(@.ipString VARCHAR(30))
RETURNS INT AS
BEGIN
DECLARE @.dotIndex1 INT
SET @.dotIndex1 = CHARINDEX('.', @.ipString)
DECLARE @.dotIndex2 INT
SET @.dotIndex2 = CHARINDEX('.', @.ipString, @.dotIndex1 + 1)
DECLARE @.dotIndex3 INT
SET @.dotIndex3 = CHARINDEX('.', @.ipString, @.dotIndex2 + 1)

IF @.dotIndex3 < 1 RETURN 0

DECLARE @.section1 BIGINT
SET @.section1 = CAST(SUBSTRING(@.ipString, 1, @.dotIndex1 - 1) AS BIGINT)
DECLARE @.section2 BIGINT
SET @.section2 = CAST(SUBSTRING(@.ipString, @.dotIndex1 + 1, @.dotIndex2 - @.dotIndex1 - 1) AS BIGINT)
DECLARE @.section3 BIGINT
SET @.section3 = CAST(SUBSTRING(@.ipString, @.dotIndex2 + 1, @.dotIndex3 - @.dotIndex2 - 1) AS BIGINT)
DECLARE @.section4 BIGINT
SET @.section4 = CAST(RIGHT(@.ipString, LEN(@.ipString) - @.dotIndex3) AS BIGINT)

DECLARE @.result BIGINT
SET @.result = (@.section1 * 0x1000000) | (@.section2 * 0x10000) | (@.section3 * 0x100) | (@.section4)

DECLARE @.t INT

-- If the value can fit in a signed 32-bit value
IF @.result < 2147483648 RETURN @.result

-- The value can't fit in a signed 32-bit value. Use negative space.
-- This is what would happen if the value was binary cast from unsigned to signed.
RETURN CAST(@.result - 4294967296 AS INT)
END|||What I meant was:// ptp 200408021738 demo for dbforums.com

#include <stdio.h>

void main()

{
union bar {
unsigned int u;
int s;
} foo;

foo.u = 4294967293;
printf("%d\n", foo.s); // outputs "-3"
}
-PatP|||What I meant was:

Ah, I see what you are saying. In C you can also just do a binary cast:

unsigned int value = 0xFFFFFFFF;
int signedValue = (int) value; // will be -1

In a T-SQL stored proc, I don't see an equivalent. I'm stuck using if statements and subtraction:

DECLARE @.value BIGINT
DECLARE @.signedValue INT

SET @.value = 4294967295

IF @.value < 2147483648 SET @.signedValue = @.value
ELSE SET @.signedValue = @.value - 4294967296

SELECT @.signedValue -- Will be -1

This works but I had a nagging feeling that there was a cleaner solution... Maybe not.|||Uglier joins and queries? Have you tried it?

begin tran
go
create function fn_RogerWilco(
@.section1 bigint ,
@.section2 bigint ,
@.section3 bigint ,
@.section4 bigint ) returns bigint
as begin
declare @.result bigint
set @.result = (@.section1 * 0x1000000) | (@.section2 * 0x10000) | (@.section3 * 0x100) | (@.section4)
return @.result
end
go
create table RogerWilco (
section1 tinyint not null,
section2 tinyint not null,
section3 tinyint not null,
section4 tinyint not null,
IPAddress as dbo.fn_RogerWilco(section1, section2, section3, section4) )
insert RogerWilco (section1, section2, section3, section4) values (192,168,1,1)
select * from RogerWilco
rollback tran|||In C you can also just do a binary cast:

unsigned int value = 0xFFFFFFFF;
int signedValue = (int) value; // will be -1

I think that you are making this orders of magnitude more difficult than it needs to be. Any flavor of four-byte int reaches the SQL Server as a signed int... It is the only way that they come. If you send any four-byte int down the wire, you'll get back the same bit pattern regardless of how you reference it within your client code. If you use either %d or %u to format it, you'll format the strings as signed or unsigned, regardless of how the int values are declared. The only reason that signed/unsigned makes any difference at all is if you want to do math on the server and have it match math done on the client, if you don't care about that, then nothing else matters!

-PatP|||i prefer the rdjabarov solution myself
i worked on a ip storage question where they had stored it as varchar...(yuck, first timers)
you can however write a pretty complex little function to break apart an ip address using substring and case.
either way i would be very interested to see the storage in a single column with a simple way to use the decimal storage inplace and in the context of an ip address not a decimal.

"The suspense is terrible. I hope it will last." Willy Wonka|||I'm confused. You see value in the database engine being able to address the octets of an IP address? So would a BINARY(4) be what you want, or four separate TINYINT columns, or something different?

I guess I'd never given real thought to allowing the database engine to manipulate the IP addresses directly. I know that it can be done, I'm just not sure that I can see the value in doing it.

-PatP|||I'm confused...I'm just not sure that I can see the value in doing it.Me neither. But maybe he knows something we don't? Do IP addresses actually mean something other than the fact that they belong to class A, B, or C and are unique for their sub-nets? Does converting them to signed/unsigned actually add value?|||Answering the question as given, you could use numeric (11, 0). In the database, it would not look like an IP address as such. Is your application going to decode the data?|||free at last, free at last...thank god almighty...

well you get the idea...

ahem...

anyone want to suggest that an ip address is NOT a number....|||Me neither. But maybe he knows something we don't? Do IP addresses actually mean something other than the fact that they belong to class A, B, or C and are unique for their sub-nets? Does converting them to signed/unsigned actually add value?There are ways you can manipulate IP addresses to obtain useful information if you know the network topology, such as knowing for any address A.B.C.D in your network, its router will be A.B.C.1 or similar operations. You might also know that for a device of class X, there will be a device of class Y at the IP address of X + 2 (for example in a cluster configuration).

I was thinking that RogerWilco wanted to ensure that the addresses computed would match between the client and the server. For that usage, since the server must use signed ints, it would help to be able to have signed ints available on the client too, but that is the only reason I can imagine.

Oh well, maybe RogerWilco will weigh in with some details that my feeble brain can't find at the moment!

-PatP|||anyone want to suggest that an ip address is NOT a number....What else is an IP address but a number? Over the years, folks have put some kind of funky spins on how it is used, but if you go back to Metcalfe's original writings you'll see that it really truly is just a number!

-PatP|||What else is an IP address but a number? Over the years, folks have put some kind of funky spins on how it is used, but if you go back to Metcalfe's original writings you'll see that it really truly is just a number!

-PatP

Do you do math on it?|||How else do you figure out which node is which in a cluster without doing math using the IP address? There might be a way to do it using some really arcane bit mapping dreck, but I'll stick with simple math myself.

-PatP|||What's the formula for it, Pat?|||In our configurations (which aren't standard), node N is the cluster IP + N. Granted that it isn't pretty, but it works.

-PatP|||Well, then why making it so hard when all you need to do is to increment a specific part? Or I missed your answer and it means to add to the signed representation of the IP address?|||Nah, you missed a bunch of stuff riddled throughout this thread. Brett was just freaking because I see an IP address as a UINT32 instead of an abstract data type. He couldn't imagine that I might ever do math on an IP address. Not only do I do simple arithmetic, but I do bitmasks and other fancy tricks too!

-PatP|||For what purpose?

How dynamic does this stuff have to be?|||I still don't see the formula...What do you add N to? UINT of the IP or a specific part of IP?|||If you think of the IP address as a 32 bit unsigned integer, that is what you manipulate. In nearly every case, that means that you'll only manipulate the low order octet. Very few clusters have more than four machines, and they almost never cross a subnet boundary (although in larger "server farms" they certainly could).

-PatP|||OK, I got it. So in regards to what I posted way up there, RogerWilco will have to have a translation back from signed to a 4-part number, huh?|||Most of the C routines that use IP addresses store them as an int or an unsigned int. The bit pattern itself is all that really matters, and ADO does the translation for me.

I suspect that we've made a mountain out of a mole hill. Unless RogerWilco has problems, I think we ought to let this thread die gracefully.

-PatP|||Yes, an IP address is just a number. But:

- There is frequently a need to convert the value in the database to/from the dotted notation that humans are accustomed to. When I do a query, I want to see the IP results in dotted notation format or I want to specify an IP in the WHERE clause in dotted notation. Obviously, a client app can do this kind of thing easily but I frequently need to write one-off SQL statements and run directly in Query Analyzer.
- I'd like to avoid strings since they are an inefficient use of space and are subject to various formatting errors.
- Frequently I want to join two tables together by IP, ORDER BY IP, or GROUP by IP. This is definitely possible but bulkier when the IP value is split across multiple columns: WHERE A.IP1 = B.IP1 AND A.IP2 = B.IP2 AND ...

I really never do anything with the numeric value of an IP other than equality tests. It would be nice to use the numeric value for "range" purposes, as in from 192.168.1.0 to 192.168.1.255 but that doesn't work quite right with signed values.

Thanks for all the answers, everyone! I don't think I've ever gotten this much of a response!

Here are the two TSQL functions I'm using to convert between dotted notation and 32-bit numeric:

CREATE FUNCTION parseIPv4(@.ipString VARCHAR(30))
RETURNS INT AS
BEGIN
DECLARE @.dotIndex1 INT
SET @.dotIndex1 = CHARINDEX('.', @.ipString)
DECLARE @.dotIndex2 INT
SET @.dotIndex2 = CHARINDEX('.', @.ipString, @.dotIndex1 + 1)
DECLARE @.dotIndex3 INT
SET @.dotIndex3 = CHARINDEX('.', @.ipString, @.dotIndex2 + 1)

IF @.dotIndex3 < 1 RETURN 0

DECLARE @.section1 BIGINT
SET @.section1 = CAST(SUBSTRING(@.ipString, 1, @.dotIndex1 - 1) AS BIGINT)
DECLARE @.section2 BIGINT
SET @.section2 = CAST(SUBSTRING(@.ipString, @.dotIndex1 + 1, @.dotIndex2 - @.dotIndex1 - 1) AS BIGINT)
DECLARE @.section3 BIGINT
SET @.section3 = CAST(SUBSTRING(@.ipString, @.dotIndex2 + 1, @.dotIndex3 - @.dotIndex2 - 1) AS BIGINT)
DECLARE @.section4 BIGINT
SET @.section4 = CAST(RIGHT(@.ipString, LEN(@.ipString) - @.dotIndex3) AS BIGINT)

DECLARE @.result BIGINT
SET @.result = (@.section1 * 0x1000000) | (@.section2 * 0x10000) | (@.section3 * 0x100) | (@.section4)

DECLARE @.t INT

IF @.result < 2147483648 RETURN @.result

RETURN CAST(@.result - 4294967296 AS INT)
END

CREATE FUNCTION formatIPv4(@.ipValue INT)
RETURNS VARCHAR(30) AS
BEGIN
DECLARE @.unsignedValue BIGINT
SET @.unsignedValue = @.ipValue
IF @.unsignedValue < 0 SET @.unsignedValue = @.unsignedValue + 4294967296

RETURN
CAST((@.unsignedValue / 0x1000000 & 0xFF) AS VARCHAR(4)) + '.'
+ CAST((@.unsignedValue / 0x0010000 & 0xFF) AS VARCHAR(4)) + '.'
+ CAST((@.unsignedValue / 0x0000100 & 0xFF) AS VARCHAR(4)) + '.'
+ CAST((@.unsignedValue / 0x0000001 & 0xFF) AS VARCHAR(4))
END|||I'd probably fall back to using BINARY(4) then, just because it is more efficient to convert and allows range comparisons. You could use something like:DROP FUNCTION dbo.f_b2s
GO
CREATE FUNCTION dbo.f_b2s(@.ipColumn BINARY(4)) RETURNS VARCHAR(15) AS
BEGIN
RETURN Convert(VARCHAR(3), Ascii(@.ipColumn)) + '.'
+ Convert(VARCHAR(3), Ascii(SubString(@.ipColumn, 2, 1))) + '.'
+ Convert(VARCHAR(3), Ascii(SubString(@.ipColumn, 3, 1))) + '.'
+ Convert(VARCHAR(3), Ascii(SubString(@.ipColumn, 4, 1)))
END
GO
DROP FUNCTION dbo.f_s2b
GO
CREATE FUNCTION dbo.f_s2b(@.pcDotted VARCHAR(20)) RETURNS BINARY(4) AS
BEGIN
DECLARE @.bWork VARBINARY(4)
, @.i1 INT
, @.i2 INT
, @.i3 INT

SET @.i1 = CharIndex('.', @.pcDotted, 1)
SET @.i2 = CharIndex('.', @.pcDotted, 1 + @.i1)
SET @.i3 = CharIndex('.', @.pcDotted, 1 + @.i2)

RETURN
CASE
WHEN @.pcDotted LIKE '%[^.0-9]%' THEN NULL
WHEN @.i3 < @.i2 OR @.i2 < @.i1 OR 0 = @.i1 THEN NULL
ELSE
CAST(Char(Convert(int, SubString(@.pcDotted, 1
, @.i1 - 1))) AS BINARY(1))
+ CAST(Char(Convert(int, SubString(@.pcDotted, @.i1 + 1
, @.i2 - @.i1 - 1))) AS BINARY(1))
+ CAST(Char(Convert(int, SubString(@.pcDotted, @.i2 + 1
, @.i3 - @.i2 - 1))) AS BINARY(1))
+ CAST(Char(Convert(int, SubString(@.pcDotted, @.i3 + 1
, 20))) AS BINARY(1))

END
END
GO-PatP|||free at last, free at last...thank god almighty...

well you get the idea...

ahem...

anyone want to suggest that an ip address is NOT a number....

OK i will byte.
I will suggest that an ip address is not a number..

it is a collection of 4 8bit sections (octets) that make up a network address. so each octet is a "number". because the ip address is not valuable without the subnet mask. 1921681100 as a number means something specific.
192.168.1.100 could be the 192 network or the 192.168 network or the 192.168.1 network etc ...
another problem is that with using numeric (11,0) each octet can contain a decimal equivalent of 1 through 255 so the periods (.) are necessary to decoding the address.

NOW technically if you broke each octet up into it's binary equivalent then each section would be an exact 8 character number consisting of ones and zeros.

rdjabarov,, becuase of cidr and vlsm the old classfull based system of ip addresses is a bit out of date. Classless addressing(Supernetting and subnetting) kind of make the classfull system a bit old fashioned and not the most effective.

My solution in this problem (considering all of the hassles) has always been to have 4 tinyint cols and display accordingly. not the most elegant solution, but it has always been the simplest and i am always one for bringing the app logic to the db

there , i think that about covers it.|||Ah brings tears to a data modelers heart

very nice...

And le the denormalization begin

And, which one are you?

http://www.techwise.com/techwise/index.cfm?ID=F66B888A-1D5D-4FAB-BE0BB5361EDC93BA|||Ah brings tears to a data modelers heart

very nice...

And le the denormalization begin

And, which one are you?

http://www.techwise.com/techwise/index.cfm?ID=F66B888A-1D5D-4FAB-BE0BB5361EDC93BA

front row third from the right.

i'm not in that picture.

as far as normalization goes, they are not rules but guidelines.
i am very dedicated to making my dbs as "normal" as possible but sometimes denormalizing can solve some certain problems but at the same time cause more**.

i realize that breaking up the ip address into 4 individual columns violates the entity rules but dammit it's so much easier than all the other work that has to be performed to treat the ip correctly.

**little known fact is that i invented the 2nd normal form. it was 1965 and i was bored with 1nf (i mean how many tables can you build??) so i tried to spice it up. i thought "Hey lets make more tables, it'l be cool" but shortly after i came out with it [i had ordered all the t shirts and the beer coozies and those arent cheap] codd and date come out with 3nf and steal all of my thunder. i had to eat it on the promotional 2nf pocket protectors and pens i even had my catchy saying and everything

"repeating groups, repeating groups, repeating groups, repeating groups"
catchy huh
"i'll admit it's not as good as the key the whole key and blah blah blah" but dammit those bastards ruined me... :D|||Hey, I'll buy some of your pocket protectors! They'll make great Christmas presents for all of my dba friends and co-workers!

-PatP

*Help* Function to convert a column to a scalar value - how?

Hi,
I'm trying to consolidate a column from the 'many' side of a relationship
into a single NVARCHAR scalar value so that it can be displayed in a client
application (no editing is required).
I figured that I would be best using a user function called from within the
originating stored procedure, passing the record ID value to the function
which is then used as the foreign key within a SELECT statement on the
'many' table.
My confusion is around the looping structure within the function and
persisting a current value to append to the NVARCHAR value, which can then
be passed out to the calling stored proc.
The function currently looks like this (but doesn't work):
ALTER FUNCTION dbo.fnCofAsStringForContactID
(
@.ContactID AS INTEGER
)
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE @.TempString AS NVARCHAR(500)
DECLARE @.FullLocation AS NVARCHAR(100)
SELECT @.FullLocation = CompanyLocations.LocationName
FROM ContactCofAJoin
INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
CompanyLocations.LocationID
WHERE (ContactCofAJoin.fContactID = @.ContactID)
WHILE @.FullLocation IS NOT NULL
BEGIN
SET @.TempString = @.TempString + @.FullLocation + ', '
END
RETURN @.TempString
END
Am I correct in thinking that the WHILE loop will iterate through the SELECT
records and extract the data that I need?
Cheers,
Andyhttp://www.aspfaq.com/show.asp?id=2529
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>|||You don't actually need a loop. Try this:
http://milambda.blogspot.com/2005/0...s-as-array.html
Adjust to your specific needs.
ML|||Many thanks for the tips guys!
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>

**HAVE NULL AND NOT DUBLICATED VALUES IN COL**

Hi
I'm using SQL 2000,how is it possible to control a value of column to get
the ollowing result?
Emp (EmpId PK,EmpName,EmpCode)
EmpId EmpName EmpCode
-- -- --
100 John 3
101 Anne 5
102 Robert null
103 Jennifer null
104 Peter 78
I want to have some NULLs or a unique number in EmpCode column as above,
I tried to define Uniqe index or constraint but it wasn't successfull
because of null,
now how can I prevent of inserting a record of (105,'Tom',3) and not prevent
of inserting a record like (105,'Tom',null)
Any help would be greatly thankful.Here are three possible methods (in each case, Colx is the nullable,
unique column):
1. Use an indexed view:
CREATE VIEW SomeTable_Unique_Non_NULL
WITH SCHEMABINDING
AS
SELECT colx
FROM dbo.SomeTable
WHERE colx IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX uclcolx ON SomeTable_Unique_Non_NULL
(colx)
2. Use an index with a calculated column:
CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL,
foo
AS (CASE WHEN colx IS NULL THEN keycol END), UNIQUE (colx,foo))
3. Use a trigger:
CREATE TRIGGER trg_SomeTable_Unique_Non_NULL
ON SomeTable FOR UPDATE, INSERT
AS
IF EXISTS
(SELECT *
FROM Inserted AS I
JOIN SomeTable AS S
ON I.colx = S.colx AND I.keycol<>S.keycol)
BEGIN
ROLLBACK TRAN
RAISERROR('Duplicate values not permitted',16,1)
END
David Portas
SQL Server MVP
--|||David
I think violation of UNIQUE KEY constraint will terminate the batch , i mean
we will not see the error generated by the trigger.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108554393.210913.172600@.z14g2000cwz.googlegroups.com...
> Here are three possible methods (in each case, Colx is the nullable,
> unique column):
> 1. Use an indexed view:
> CREATE VIEW SomeTable_Unique_Non_NULL
> WITH SCHEMABINDING
> AS
> SELECT colx
> FROM dbo.SomeTable
> WHERE colx IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX uclcolx ON SomeTable_Unique_Non_NULL
> (colx)
> 2. Use an index with a calculated column:
> CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL,
> foo
> AS (CASE WHEN colx IS NULL THEN keycol END), UNIQUE (colx,foo))
> 3. Use a trigger:
> CREATE TRIGGER trg_SomeTable_Unique_Non_NULL
> ON SomeTable FOR UPDATE, INSERT
> AS
> IF EXISTS
> (SELECT *
> FROM Inserted AS I
> JOIN SomeTable AS S
> ON I.colx = S.colx AND I.keycol<>S.keycol)
> BEGIN
> ROLLBACK TRAN
> RAISERROR('Duplicate values not permitted',16,1)
> END
> --
> David Portas
> SQL Server MVP
> --
>|||I tried sth like below in the past ,but I think it can be dangrous in
sometimes
for example if we have a begin transaction in our statement and the this
trigger
fires and duplicated value was inserted then it will rollback to the poit
which made the other activities which are done before this trigger to be
rollbacked too!!!!!!!
what's your openion?
CREATE TRIGGER trg_SomeTable_Unique_Non_NULLred">
> ON SomeTable FOR UPDATE, INSERT
> AS
> IF EXISTS
> (SELECT *
> FROM Inserted AS I
> JOIN SomeTable AS S
> ON I.colx = S.colx AND I.keycol<>S.keycol)
> BEGIN
> ROLLBACK TRAN
> RAISERROR('Duplicate values not permitted',16,1)
> END
>
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108554393.210913.172600@.z14g2000cwz.googlegroups.com...
> Here are three possible methods (in each case, Colx is the nullable,
> unique column):
> 1. Use an indexed view:
> CREATE VIEW SomeTable_Unique_Non_NULL
> WITH SCHEMABINDING
> AS
> SELECT colx
> FROM dbo.SomeTable
> WHERE colx IS NOT NULL
> GO
> CREATE UNIQUE CLUSTERED INDEX uclcolx ON SomeTable_Unique_Non_NULL
> (colx)
> 2. Use an index with a calculated column:
> CREATE TABLE SomeTable (keycol INTEGER PRIMARY KEY, colx INTEGER NULL,
> foo
> AS (CASE WHEN colx IS NULL THEN keycol END), UNIQUE (colx,foo))
> 3. Use a trigger:
> CREATE TRIGGER trg_SomeTable_Unique_Non_NULL
> ON SomeTable FOR UPDATE, INSERT
> AS
> IF EXISTS
> (SELECT *
> FROM Inserted AS I
> JOIN SomeTable AS S
> ON I.colx = S.colx AND I.keycol<>S.keycol)
> BEGIN
> ROLLBACK TRAN
> RAISERROR('Duplicate values not permitted',16,1)
> END
> --
> David Portas
> SQL Server MVP
> --
>|||On Thu, 17 Feb 2005 08:47:56 +0330, maryam rezvani wrote:

>I tried sth like below in the past ,but I think it can be dangrous in
>sometimes
>for example if we have a begin transaction in our statement and the this
>trigger
>fires and duplicated value was inserted then it will rollback to the poit
>which made the other activities which are done before this trigger to be
>rollbacked too!!!!!!!
>what's your openion?
Hi Maryam,
Transactions are used to encapsulate a number of statements that should
either be executed as a whole, or none at all.
So if I have several statements and one of them is an insert that fails as
a result of duplicate data, I would expect and want SQL Server to undo all
changes made since the BEGIN TRANSACTION.
If you want to rollback only the insert but none of the preceding
activities, then simply commit the transaction for these other activities
first, before executing the insert.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks,now imagine we don't define any begin trans before and we fire this
trigger and it will fail because of duplicated rows and rollback happen
too,but to which poit ?just to prevet inserting duplicated rows or......?
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:13o811tiqus3gpgt2glj5tn74sp16utraa@.
4ax.com...
> On Thu, 17 Feb 2005 08:47:56 +0330, maryam rezvani wrote:
>
> Hi Maryam,
> Transactions are used to encapsulate a number of statements that should
> either be executed as a whole, or none at all.
> So if I have several statements and one of them is an insert that fails as
> a result of duplicate data, I would expect and want SQL Server to undo all
> changes made since the BEGIN TRANSACTION.
> If you want to rollback only the insert but none of the preceding
> activities, then simply commit the transaction for these other activities
> first, before executing the insert.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||In that case only the INSERT operation itself will be rolled back.
--
David Portas
SQL Server MVP
--|||Yes, if you define colx as UNIQUE then the trigger will not fire. The
trigger is something you can use instead of a unique constraint.
David Portas
SQL Server MVP
--

**Current Activity(Enterprise Manager)**

Hi
I'm using SQL server 2000 and when I select the current activity in
enterprise manager and want to sort the CPU column ascending, it seels by
clicking on the title of this column it will be sort alphabetically not in
numeric mode,why?
and the second problem is,by looking through this page how can I
understand which users or application are using the resources on server
more? and is there anyway to find if an object locked or busy for a long
time with a special program on specific station?
Fore example: I've a specific procedure that most of time work probably(2
sec) but sometimes it takes a long time to fetch the result (5 min)!! and
by renaming it ,it works well again,can this page help me to find which
station is running this query during that special "5 minute" time?
Any help would be thankfulThe CPU data is probably converted to a character behind the scenes. Take a
look at sp_who2 and sp_lock sp's to get a better picture of what is
happening. These are more flexible than EM and you can look at the code for
them in the master db to see what they are doing. You may also want to run
a trace to capture more information about who is doing what and how long
each takes. Lookup trace or profiler in BooksOnLine.
Andrew J. Kelly SQL MVP
<R> wrote in message news:opssw5yamdmw7tkz@.system109.parskhazar.net...
> Hi
> I'm using SQL server 2000 and when I select the current activity in
> enterprise manager and want to sort the CPU column ascending, it seels by
> clicking on the title of this column it will be sort alphabetically not in
> numeric mode,why?
> and the second problem is,by looking through this page how can I
> understand which users or application are using the resources on server
> more? and is there anyway to find if an object locked or busy for a long
> time with a special program on specific station?
> Fore example: I've a specific procedure that most of time work probably(2
> sec) but sometimes it takes a long time to fetch the result (5 min)!! and
> by renaming it ,it works well again,can this page help me to find which
> station is running this query during that special "5 minute" time?
> Any help would be thankful|||Thanks, I tried to save activities by creating a trace saving result to
table.
Although I filtered it just to save some activities for some logins ,it
needs a lot space to store all details in a table for some days!!!
I want to find a way to record most activities in details to refer to them
later to find who and when sth happened.
can you direct me more?
Thanks a lot|||You should never send a trace directly to a table, especially if it is long
term. You should send the results directly to a file on a local disk drive.
You can then load them into a table later with fn_trace_gettable. Don't use
profiler for this but rather sp_trace_create and it's associated sp's. See
here for more details:
http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm
--
Andrew J. Kelly SQL MVP
<R> wrote in message news:opss0idcllmw7tkz@.system109.parskhazar.net...
> Thanks, I tried to save activities by creating a trace saving result to
> table.
> Although I filtered it just to save some activities for some logins ,it
> needs a lot space to store all details in a table for some days!!!
> I want to find a way to record most activities in details to refer to them
> later to find who and when sth happened.
> can you direct me more?
> Thanks a lot

**Current Activity(Enterprise Manager)**

Hi
I'm using SQL server 2000 and when I select the current activity in
enterprise manager and want to sort the CPU column ascending, it seels by
clicking on the title of this column it will be sort alphabetically not in
numeric mode,why?
and the second problem is,by looking through this page how can I
understand which users or application are using the resources on server
more? and is there anyway to find if an object locked or busy for a long
time with a special program on specific station?
Fore example: I've a specific procedure that most of time work probably(2
sec) but sometimes it takes a long time to fetch the result (5 min)!! and
by renaming it ,it works well again,can this page help me to find which
station is running this query during that special "5 minute" time?
Any help would be thankfulThe CPU data is probably converted to a character behind the scenes. Take a
look at sp_who2 and sp_lock sp's to get a better picture of what is
happening. These are more flexible than EM and you can look at the code for
them in the master db to see what they are doing. You may also want to run
a trace to capture more information about who is doing what and how long
each takes. Lookup trace or profiler in BooksOnLine.
Andrew J. Kelly SQL MVP
<R> wrote in message news:opssw5yamdmw7tkz@.system109.parskhazar.net...
> Hi
> I'm using SQL server 2000 and when I select the current activity in
> enterprise manager and want to sort the CPU column ascending, it seels by
> clicking on the title of this column it will be sort alphabetically not in
> numeric mode,why?
> and the second problem is,by looking through this page how can I
> understand which users or application are using the resources on server
> more? and is there anyway to find if an object locked or busy for a long
> time with a special program on specific station?
> Fore example: I've a specific procedure that most of time work probably(2
> sec) but sometimes it takes a long time to fetch the result (5 min)!! and
> by renaming it ,it works well again,can this page help me to find which
> station is running this query during that special "5 minute" time?
> Any help would be thankful|||Thanks, I tried to save activities by creating a trace saving result to
table.
Although I filtered it just to save some activities for some logins ,it
needs a lot space to store all details in a table for some days!!!
I want to find a way to record most activities in details to refer to them
later to find who and when sth happened.
can you direct me more?
Thanks a lot|||You should never send a trace directly to a table, especially if it is long
term. You should send the results directly to a file on a local disk drive.
You can then load them into a table later with fn_trace_gettable. Don't use
profiler for this but rather sp_trace_create and it's associated sp's. See
here for more details:
http://vyaskn.tripod.com/server_sid..._sql_server.htm
Andrew J. Kelly SQL MVP
<R> wrote in message news:opss0idcllmw7tkz@.system109.parskhazar.net...
> Thanks, I tried to save activities by creating a trace saving result to
> table.
> Although I filtered it just to save some activities for some logins ,it
> needs a lot space to store all details in a table for some days!!!
> I want to find a way to record most activities in details to refer to them
> later to find who and when sth happened.
> can you direct me more?
> Thanks a lot

**Current Activity(Enterprise Manager)**

Hi
I'm using SQL server 2000 and when I select the current activity in
enterprise manager and want to sort the CPU column ascending, it seels by
clicking on the title of this column it will be sort alphabetically not in
numeric mode,why?
and the second problem is,by looking through this page how can I
understand which users or application are using the resources on server
more? and is there anyway to find if an object locked or busy for a long
time with a special program on specific station?
Fore example: I've a specific procedure that most of time work probably(2
sec) but sometimes it takes a long time to fetch the result (5 min)!! and
by renaming it ,it works well again,can this page help me to find which
station is running this query during that special "5 minute" time?
Any help would be thankful
The CPU data is probably converted to a character behind the scenes. Take a
look at sp_who2 and sp_lock sp's to get a better picture of what is
happening. These are more flexible than EM and you can look at the code for
them in the master db to see what they are doing. You may also want to run
a trace to capture more information about who is doing what and how long
each takes. Lookup trace or profiler in BooksOnLine.
Andrew J. Kelly SQL MVP
<R> wrote in message news:opssw5yamdmw7tkz@.system109.parskhazar.net...
> Hi
> I'm using SQL server 2000 and when I select the current activity in
> enterprise manager and want to sort the CPU column ascending, it seels by
> clicking on the title of this column it will be sort alphabetically not in
> numeric mode,why?
> and the second problem is,by looking through this page how can I
> understand which users or application are using the resources on server
> more? and is there anyway to find if an object locked or busy for a long
> time with a special program on specific station?
> Fore example: I've a specific procedure that most of time work probably(2
> sec) but sometimes it takes a long time to fetch the result (5 min)!! and
> by renaming it ,it works well again,can this page help me to find which
> station is running this query during that special "5 minute" time?
> Any help would be thankful
|||Thanks, I tried to save activities by creating a trace saving result to
table.
Although I filtered it just to save some activities for some logins ,it
needs a lot space to store all details in a table for some days!!!
I want to find a way to record most activities in details to refer to them
later to find who and when sth happened.
can you direct me more?
Thanks a lot
|||You should never send a trace directly to a table, especially if it is long
term. You should send the results directly to a file on a local disk drive.
You can then load them into a table later with fn_trace_gettable. Don't use
profiler for this but rather sp_trace_create and it's associated sp's. See
here for more details:
http://vyaskn.tripod.com/server_side...sql_server.htm
Andrew J. Kelly SQL MVP
<R> wrote in message news:opss0idcllmw7tkz@.system109.parskhazar.net...
> Thanks, I tried to save activities by creating a trace saving result to
> table.
> Although I filtered it just to save some activities for some logins ,it
> needs a lot space to store all details in a table for some days!!!
> I want to find a way to record most activities in details to refer to them
> later to find who and when sth happened.
> can you direct me more?
> Thanks a lot

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly

(Urgent)How can I specify the excel File''s column to import data as varchar instead of Float

Hi,

I have a excel file which i want to import the data to sql server... The sql server Data type for that particular column is

varchar and it has a contraint too like the data should be in this fashion 00000-0000 or 00000...

but when i try to import the data from the excel to sql server... 08545 just becomes 8545 (cause excel is treating it as a float) and so my insert fails...

what can i do to rectify the problem...

regards

Karen

Hi Karenros,

You need to put a data conversion task and make the datatype as varchar instead of float.

You need to place this task inbetween the excel task and destination sql server.

Thanks,

SVGP.

|||

SVGP,

thanks for your answer... can u please elaborate as to how can i create the data conversion task...

Regards

Karen

|||

In the dataflow tab ,on the left side you will find dataflow source,dataflow transformation and dataflow destinations.

Drag and drop the Data Conversion task from the dataflow transformation, inbetween your excel and oledb Destination.

edit the data conversion task,you need to select the column you want to convert and it will appear down automatically.

then you need to change the datatype(the drop down will lead you for that).

Remember to map the copy of the column you selected in the oledb destination.

Thanks,

SVGP

|||is the dataflow tab in Excel?|||

No Karen,iam mentioning about the Data flow in Business Intelligence development studio(BIDS)

You have Three tabs,control,dataflow and event handlers,from that u select dataflow and do as i said before.

Good Luck.

SVGP

|||but i am importing the data from an excel file to the database directly|||

What tool you are using to migrate the data?

|||going to sql server and importing the data directly