@@IDENTITY and thread safety

I am currently using @.@.Identity to retreive the Identity value for the PK field in a table that I am inserting data into. Essentially, my code looks like this:
SqlCommand cmd=new SqlCommand("Insert into table(... ;Select @.@.Identity from table",conn);
string identity=cmd.ExecuteScalar();

Testing this myself, it works fine, but I am worried as to how thread safe this is in a real-world environment? (i.e. with multiple users clicking at it). Is there a guaranteed way to make this thread safe- wrap it in a transaction maybe?
I think you should use scope_identity()|||I would search the online books on @.@.Identity. I'm sure the subject istouched upon. I seem to recall something about @.@.Identity applying tothe current connection or context but I forgot the details. In anycase, @.@.Identity is so widely used that if it wasn't thread safe we'dall be in serious trouble.
|||scope_identity()
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp
@.@.Identity
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_globals_50u1.asp
@.@.Identity is thread safe, but scope_identity() is the way to go.|||

Everyone,

Thanks for the help. JHouse, those articles are great. Just for the benefit of others reading this thread, the key difference between the @.@.IDENTITY and Scope_Identity() is the @.@.IDENTITY returns the last identity value of any table your batch or procedure inserts into - implicit or explicit - while Scope_Identity() is explicit only. So if you insert into tableA that has a trigger that inserts into tableB. @.@.Identity will return the identity from tableB, and Scope_Identity will return the identity from tableA.

@@ identity insert

Is it possible to create a trigger that inserts the @.@.identity (primary key) from Table1 into a field in Table2?
If so, how? Thank you.
-D-yes it's possible. Create a trigger on Table1 for insert and execute an insert-statement using @.@.identity. Are you stuck?

on Check Constraint expressions

I am creating a check constraint on a field (GRID_NBR) for values between 1 & 99. I am a little confused on creating the expression for it (Books online is vague).

Can I use the following expression: GRID_NBR BETWEEN 1 AND 99

Or do I have to use: GRID_NBR > 0 AND GRID_NBR < 100

Thanks!I am creating a check constraint on a field (GRID_NBR) for values between 1 & 99. I am a little confused on creating the expression for it (Books online is vague).

Can I use the following expression: GRID_NBR BETWEEN 1 AND 99

Or do I have to use: GRID_NBR > 0 AND GRID_NBR < 100

Thanks!

I found my own answer (in Books Online)...here's the example it gave on the Constraint page...

CREATE TABLE cust_sample
(
cust_id int PRIMARY KEY,
cust_name char(50),
cust_address char(50),
cust_credit_limit money,
CONSTRAINT chk_id CHECK (cust_id BETWEEN 0 and 10000 )
)

datetime Parameter issue

I'm using a sproc to insert the time (Now()) into a datetime field. Somehow the time "seconds" are not making it into the field. For example, the variable grabs the value of Now() - "9/16/2005 01:58:15 AM" to insert into the field. But when I view the records, the datetime value is "2005-09-16 13:58:00.000".
What am I doing wrong?
Thanks for your help!
Lynnette
Here is a snippet of the code and the sproc:

Dim recDateAs DateTime = Now()
cmd.Parameters.Add(New SqlParameter("@.DELastChg", SqlDbType.DateTime)).Value = recDate

ALTER PROC usp_AddTmpFLSAfromPT
@.dtWdate datetime,
@.whereString as varchar(255),
@.DEuser as varchar(30),
@.DELastChg datetime

AS

DECLARE @.strSQL as varchar(2000)

SET @.strSQL =
'
INSERT INTO tmpFLSAEmpInfo
( Emp_Number,
PT_ID,
Emp_Division,
Emp_Dept,
--Emp_DeptInfo,
Emp_Supervisor,
Emp_Location,
Emp_Union,
Emp_SG,
Emp_Shift,
DEusername,
DELastChg,
WrkDate
)
SELECT
Employee2.Emp_Number,
Employee2.[ID],
Employee2.Division,
Employee2.Job_Dept_Code,
Employee2.Job_Supervisor,
Employee2.Loc_Name,
Employee2.[Union],
Employee2.Sched_Group,
Employee2.Shift,
''' + @.DEUser + ''',
''' + CONVERT(varchar, @.DELastChg) + ''',
''' + CONVERT(varchar, @.dtWdate) + '''

FROM Employee2
WHERE '
+ @.whereString

EXEC(@.strSQL)

You are prbly losing the seconds because of the conversion to varchar in your code above. Put in the size also. Perhaps something like this :CONVERT(varchar(20), @.dtWdate) might help.|||Thanks for the response! I tried your suggestion, but it had no effect - still losing the seconds. I tried using CONVERT(datetime, @.DELastChg, 131) but then I get an error that I cannot convert string to DATETIME. I am hopelessly stuck.
Any other suggestions?
Thanks!
Lynnette|||Changed it toCONVERT(varchar, @.DELastChg, 113) and it works!

.NET regular expression to replace #$# special ASCII characters

Help pleaseâ?¦ - I have a report with textbox field called â'Bulletsâ' â' using
#$# special ASCII characters (pound-dollar-pound) as bullet delimiter from
legacy print publishing system.
Questions:
1. What is the correct .NET regular expression to replace #$# special ASCII
characters within textbox field to display Bullet textâ?¦?
2. Can textbox field display Bullet textâ?¦?
Please advise MSDN or Books Online references for .NET regular expression
Replace syntax.
Thank you.
MichaelHere is an example
Input Text: tester#$#bullet_1#$#bullet_2#$#bullet_3#$#bullet_4
Regular Expression: (ASCII char Octal) \043\044\043
Replace Expression: (ASCII char Octal) \015\011\052\040\040\040
Question: Is there a Code Customization example for parsing a textbox field
and replacing regular expression characters with HTML Bullets - like
<BR><FONT family=Wingding>something_bullet_symbol</FONT><TAB>
Please advise.
"Michael" wrote:
> Help pleaseâ?¦ - I have a report with textbox field called â'Bulletsâ' â' using
> #$# special ASCII characters (pound-dollar-pound) as bullet delimiter from
> legacy print publishing system.
> Questions:
> 1. What is the correct .NET regular expression to replace #$# special ASCII
> characters within textbox field to display Bullet textâ?¦?
> 2. Can textbox field display Bullet textâ?¦?
> Please advise MSDN or Books Online references for .NET regular expression
> Replace syntax.
> Thank you.
> Michael
>|||I tried this expression
=IIF(Fields!product_Bullets.Value=#$#, Fields!product_Bullets.Value=*)
Got the following error
The value expression for the textbox â'Product_Bulletsâ' contains an error:
[BC30201] Expression expected.
Please advise the correct expression syntax.
"Michael" wrote:
> Here is an example
> Input Text: tester#$#bullet_1#$#bullet_2#$#bullet_3#$#bullet_4
> Regular Expression: (ASCII char Octal) \043\044\043
> Replace Expression: (ASCII char Octal) \015\011\052\040\040\040
> Question: Is there a Code Customization example for parsing a textbox field
> and replacing regular expression characters with HTML Bullets - like
> <BR><FONT family=Wingding>something_bullet_symbol</FONT><TAB>
> Please advise.
> "Michael" wrote:
> > Help pleaseâ?¦ - I have a report with textbox field called â'Bulletsâ' â' using
> > #$# special ASCII characters (pound-dollar-pound) as bullet delimiter from
> > legacy print publishing system.
> >
> > Questions:
> >
> > 1. What is the correct .NET regular expression to replace #$# special ASCII
> > characters within textbox field to display Bullet textâ?¦?
> > 2. Can textbox field display Bullet textâ?¦?
> >
> > Please advise MSDN or Books Online references for .NET regular expression
> > Replace syntax.
> >
> > Thank you.
> > Michael
> >|||I tried this expression - OK
=IIF(Fields!product_Bullets.Value="chr(35) + chr(36) + chr(35)", "",chr(13)
+ chr(42) + chr(9))
"Michael" wrote:
> I tried this expression
> =IIF(Fields!product_Bullets.Value=#$#, Fields!product_Bullets.Value=*)
> Got the following error
> The value expression for the textbox â'Product_Bulletsâ' contains an error:
> [BC30201] Expression expected.
> Please advise the correct expression syntax.
>
> "Michael" wrote:
> > Here is an example
> >
> > Input Text: tester#$#bullet_1#$#bullet_2#$#bullet_3#$#bullet_4
> >
> > Regular Expression: (ASCII char Octal) \043\044\043
> >
> > Replace Expression: (ASCII char Octal) \015\011\052\040\040\040
> >
> > Question: Is there a Code Customization example for parsing a textbox field
> > and replacing regular expression characters with HTML Bullets - like
> > <BR><FONT family=Wingding>something_bullet_symbol</FONT><TAB>
> >
> > Please advise.
> >
> > "Michael" wrote:
> >
> > > Help pleaseâ?¦ - I have a report with textbox field called â'Bulletsâ' â' using
> > > #$# special ASCII characters (pound-dollar-pound) as bullet delimiter from
> > > legacy print publishing system.
> > >
> > > Questions:
> > >
> > > 1. What is the correct .NET regular expression to replace #$# special ASCII
> > > characters within textbox field to display Bullet textâ?¦?
> > > 2. Can textbox field display Bullet textâ?¦?
> > >
> > > Please advise MSDN or Books Online references for .NET regular expression
> > > Replace syntax.
> > >
> > > Thank you.
> > > Michael
> > >|||Please help with regular expression syntax.
I have tried both these two expressions, using ascii hex - but I am still
getting lost on the on whether or not Reporting Services will support the
Replace function including a ascii hex Carriage Return or an ascii hex
Horizontal Tab replace string...?
=Replace(Fields!product_Bullets.Value, "[chr(35) + chr(36) + chr(35)]",
"chr(13) + chr(42) + chr(9)", "1", "-1", "1")
=Replace(Fields!product_Bullets.Value, "([\x23][\x24][\x23])",
"([\xD][\x2A][\x9])", "1", "-1", "1")
Please advise any suggestions for correct regular expression syntax.
"Michael" wrote:
> I tried this expression - OK
> =IIF(Fields!product_Bullets.Value="chr(35) + chr(36) + chr(35)", "",chr(13)
> + chr(42) + chr(9))
> "Michael" wrote:
> > I tried this expression
> >
> > =IIF(Fields!product_Bullets.Value=#$#, Fields!product_Bullets.Value=*)
> >
> > Got the following error
> >
> > The value expression for the textbox â'Product_Bulletsâ' contains an error:
> > [BC30201] Expression expected.
> >
> > Please advise the correct expression syntax.
> >
> >
> > "Michael" wrote:
> >
> > > Here is an example
> > >
> > > Input Text: tester#$#bullet_1#$#bullet_2#$#bullet_3#$#bullet_4
> > >
> > > Regular Expression: (ASCII char Octal) \043\044\043
> > >
> > > Replace Expression: (ASCII char Octal) \015\011\052\040\040\040
> > >
> > > Question: Is there a Code Customization example for parsing a textbox field
> > > and replacing regular expression characters with HTML Bullets - like
> > > <BR><FONT family=Wingding>something_bullet_symbol</FONT><TAB>
> > >
> > > Please advise.
> > >
> > > "Michael" wrote:
> > >
> > > > Help pleaseâ?¦ - I have a report with textbox field called â'Bulletsâ' â' using
> > > > #$# special ASCII characters (pound-dollar-pound) as bullet delimiter from
> > > > legacy print publishing system.
> > > >
> > > > Questions:
> > > >
> > > > 1. What is the correct .NET regular expression to replace #$# special ASCII
> > > > characters within textbox field to display Bullet textâ?¦?
> > > > 2. Can textbox field display Bullet textâ?¦?
> > > >
> > > > Please advise MSDN or Books Online references for .NET regular expression
> > > > Replace syntax.
> > > >
> > > > Thank you.
> > > > Michael
> > > >

.Net 2.0 and datetime

Hi,

Here's my problem.

So using a stored proc and some parameters I want to update a datetime field

So I define @.theDate as datetime in my stored proc

in my vb.net app I have

dbcmd.Parameters.AddWithValue("@.theDate", SelectedDate)

where SelectedDate is

Dim SelectedDate as Nullable(of Datetime)

Now if SelectDate is a value all is good with the world.

However if SelectDate is Null I get an error.

How do I pass a null date to the stored proc ?

You need to add code like this:

dbcmd.Parameters.AddWithValue("@.theDate", (SelectedDate==null) ? DbNull.Value : SelectedDate)

DbNull.Value is a null for the database.

.doc iFilter not working for Mobile docs

Hi,
I have implemented full text searching over files stored in a varbinary(max)
field and this works great
The only issue i am now having is that users creating office documents
created on a Windows Mobile device such as word docs are not being searched.
The extension is .doc but the files look cut down in size.
Does anyone know of a filter or filter update that will also search office
type files created on a Windows Mobile device?
Thanks,
Jeff
The iFilters are specific for document type. The PocketWord application does
not save it in a format that the Office iFilter can understand. You would be
better off to convert them to Word.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:87EE86C3-0B73-4F23-B1C2-FCB85A15DF5B@.microsoft.com...
> Hi,
> I have implemented full text searching over files stored in a
> varbinary(max)
> field and this works great
> The only issue i am now having is that users creating office documents
> created on a Windows Mobile device such as word docs are not being
> searched.
> The extension is .doc but the files look cut down in size.
> Does anyone know of a filter or filter update that will also search office
> type files created on a Windows Mobile device?
> Thanks,
> Jeff
|||Hi Hilary,
Thanks for your answer.
What I find strange is that the device saves the document with the extension
..doc, but is not a true word document format. So when I check the file
extension with the supported SQL iFilter extensions I get a match and save it
in the db for indexing and searching.
How would I go about programaticaly converting the uploaded docs from the
mobile devices to the full Office document standard supported by the Office
iFilter?
Thanks for your help,
Jeff
"Hilary Cotter" wrote:

> The iFilters are specific for document type. The PocketWord application does
> not save it in a format that the Office iFilter can understand. You would be
> better off to convert them to Word.
> "Jeff" <Jeff@.discussions.microsoft.com> wrote in message
> news:87EE86C3-0B73-4F23-B1C2-FCB85A15DF5B@.microsoft.com...
>
>
|||You would have to use Word to convert the files to the Office Word document
format as opposed to the Pocket Word format.
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:20A3F7E8-C0C6-490A-B555-EAD7931C62E0@.microsoft.com...[vbcol=seagreen]
> Hi Hilary,
> Thanks for your answer.
> What I find strange is that the device saves the document with the
> extension
> .doc, but is not a true word document format. So when I check the file
> extension with the supported SQL iFilter extensions I get a match and save
> it
> in the db for indexing and searching.
> How would I go about programaticaly converting the uploaded docs from the
> mobile devices to the full Office document standard supported by the
> Office
> iFilter?
> Thanks for your help,
> Jeff
> "Hilary Cotter" wrote:

.dbf file import (duplicate Field Names)

I am importing a file creating by an application which exports the file into .dbf format. Very unfortunately, this .dbf file can have fields with IDENTICAL column_names. Utilizing ActiveX, I create an ado connection to the .dbf file using a visual foxpro drver. However, and not unexpectantly, I can not do the 'select *' from the file if there are duplicate names.

Can anyone make recommendations here that might help?

Oh, this is SQL200 in case that impacts what you might advise!!!!

Anyone out there from MSFT want to make some suggestions?|||

Hi Ellen,

I find it surprising that the DBF file can have duplicate field names. What application created it? Is it a FoxPro DBF or a DbaseIV or Paradox DBF?

I assume you are able to read the table structure with something other than SQL Server, is that correct?

Are you using ODBC or OLE DB? The latest FoxPro and Visual FoxPro OLE DB data provider is downloadable from msdn.microsoft.com/vfoxpro/downloads/updates .

(Urgent) How Get a Text Field and put the result into a text Var

Hi All

Iam trying to Get a text field value i wrote this code

DECLARE @.ptrval varbinary(16)
DECLARE @.length bigint
SELECT @.ptrval = TEXTPTR(Template), @.length = LEN(Template)
FROM #TEMPLATE
READTEXT Template.#TEMPLATE @.ptrval 0 @.length

but i need to put the result into a text var
is that possible or not and if it possible any one could help me with thatYou could return a text column as part of a result set. Are you trying to get the text back to ASP.NET?