How to insert primary keys without using identity

I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?

thanks for your time,

AndreIt is possible with cursors, but would be awkward and I expect subject to problems if the table was having lots of records added by lots of users at the same time...

Why do you not want to use Autoincrement?

How to Insert Null Values into a DataBase Field

I have a function that updates a table in sql server

If a field is left blank I want the database field set to Null. I use:
sqlCmd.Parameter.Add("@.somefield, someintvalue) to pass all my values to the database.

if someintvalue is null I want to set @.somefield to dbnull

How can I do this? I get errors if I submit a null integer when there is a foreign key constraint. What is the best way to handle this? should I just make all parameters objects? so that I can set them to DBNull.Value?

Also on a side note, when you are populating fields from a datatable, is there a better way to set the values (i.e. of a textbox) than cheking for DBNull before assigning?

check this out|||

FOREIGN KEY can allow NULL values if it is a UNIQUE constraint. Run a search in the BOL(books online) enable NULL on FOREIGN KEY. The following is from the BOL. Hope this helps.

"A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table. A FOREIGN KEY constraint can containnull values; however, if any column of a composite FOREIGN KEY constraint containsnull values, then verification of the FOREIGN KEY constraint will be skipped."

sql

How to INSERT INTO [Table] ([Field]) VALUES(I Have a in value)

Hi, I want to INSERT INTO [Table] ([Field]) VALUES('I Have a ' in value')

please teach me how to

xxxINSERT INTO [Table] ([Field]) VALUES('I Have a '' in value')

instead of one single quote, u need to add one more.|||Use Parameterized Queries...you woudnt have to worry about escape characters besides saving your db from sqlinjection attacks.

hth

How to Insert date in sql server database

How to insert date to the sql server database.

I am getting input from the HTML form and store it to database using
ASP.

how to store date field, what datatype needed and what conversion
needed.

Thanx & Regards,
SSGSSG (ssg14j@.gmail.com) writes:
> How to insert date to the sql server database.
> I am getting input from the HTML form and store it to database using
> ASP.
> how to store date field, what datatype needed and what conversion
> needed.

The data type to use datetime or smalldatetime. These always include
the time portion, but set it to midnight for dates only.

Conversion should occur in the client, by using parameterised statements.

Here is an example that I have canned of a parameterised statement in ADO
(it's VB6 and not ASP, but I believe that they are not too different):

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnn

cmd.CommandType = adCmdText
cmd.CommandText = " SELECT OrderID, OrderDate, CustomerID, ShipName " & _
" FROM dbo.Orders WHERE 1 = 1 "
If custid <> "" Then
cmd.CommandText = cmd.CommandText & " AND CustomerID LIKE ? "
cmd.Parameters.Append
cmd.CreateParameter("@.custid", adWChar, adParamInput, 5, custid)
End If

If shipname <> "" Then
cmd.CommandText = cmd.CommandText & " AND ShipName LIKE ? "
cmd.Parameters.Append cmd.CreateParameter("@.shipname", _
adVarWChar, adParamInput, 40, shipname)
End If

Set rs = cmd.Execute

--
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

how to insert characters in existing field

Hi,
I have a sql table with a field name model. In the model column, i have
model number that start with FX%.
I want to add WM before FX%. After FX can be anything, characters or
numbers. The model data type is varchar.
How can i do it. I tried using the syntax below but not successful.
update test2 set model = 'WM%' where model = 'FX%'
Can anyone help?
Tiffany,
It sounds like what you want is
update test2 set
model = 'WM' + model
where model = 'FX%'
This will paste WM on the beginning of all the FX... model names.
Steve Kass
Drew University
Tiffany wrote:

>Hi,
>I have a sql table with a field name model. In the model column, i have
>model number that start with FX%.
>I want to add WM before FX%. After FX can be anything, characters or
>numbers. The model data type is varchar.
>How can i do it. I tried using the syntax below but not successful.
>update test2 set model = 'WM%' where model = 'FX%'
>Can anyone help?
>
|||Steve!
I have a similar problem where I want to replace the domain part in a column
with e-mail addresses. Like aaa@.xxx.se with aaa@.yyy.se. I tried using the
tip you gave Tiffany but nothing happens.
Any clues?
Regards,
Bosse
"Steve Kass" wrote:

> Tiffany,
> It sounds like what you want is
> update test2 set
> model = 'WM' + model
> where model = 'FX%'
> This will paste WM on the beginning of all the FX... model names.
> Steve Kass
> Drew University
> Tiffany wrote:
>
|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:8241
On Wed, 16 Feb 2005 07:09:06 -0800, Bosse wrote:

>Steve!
>I have a similar problem where I want to replace the domain part in a column
>with e-mail addresses. Like aaa@.xxx.se with aaa@.yyy.se. I tried using the
>tip you gave Tiffany but nothing happens.
>Any clues?
>Regards,
>Bosse
Hi Bosse,
Steve's message to Tiffany was about how to put some extra characters in
front of existing string data. To replace a part of existing string data,
you use the REPLACE function instead.
UPDATE MyTable
SET Email = REPLACE (Email, '@.xxx.se', '@.yyy.se')
WHERE Email LIKE '%@.xxx.se'
Note the extra @. and .se inserted in the replace, to make sure that an
address like aaxxxt@.xxx.se is not accidentaly changed to aayyyt@.yyy.se.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql

How to insert a zero length string into a field using SqlDataSource?

I have a few columns in table with default value defined as zero length string (''). I want to insert record from DetailsView which uses SqlDataSource as DataSource. In the ItemInserting event, if the data is not valid, I want to use zero length string for the column. But I always get Null instead of zero length string. The code in ItemInserting event looks like this:

If objddl.SelectedIndex > 0 Then
e.Values("myFld") = objddl.SelectedItem.Value
Else
e.Values("myFld") = ""
End If

The line: e.Values("myFld") = "" put Null in the column.

How can I set a column as zero length string using the SqlDataSource?

Any help is appreciated.

Thanks.

Try:

e.Values("myFld") =string.Empty

|||Check the advanced properties of the parameter in your upate statement. You probably have the property (Sorry, the exact name eludes me) "ChangeEmptyStringToNull" set to true (true is the default).|||Thank you very much. "ConvertEmptyStringToNull" worked perfectly.

How to insert a parenthesis into a field

I'd like to know how to insert a parenthesis into a field:
Example:
insert into MyTable(mydescription) values ('4.ó%?)&.?')
I tried SET QUOTED_IDENTIFIER ON without success. The above is a scrambled
password. It must go into the database exactly as it appears.
Regards,
Jamie
Your code actually worked for me. The parenthesis shouldn't cause a problem
but some non-printable, control characters might. I suggest you insert data
like this as VARBINARY rather than strings so that you can safely insert any
byte values you may require.
Passwords? Don't store them in the database. Store a secure hash of the
password in the database instead. Maybe you meant that this was a password
hash but your use of the word "scrambled" implied to me that this is an
*encrypted* password. Storing encrypted passwords is not really a good idea
from a security point-of-view.
David Portas
SQL Server MVP
|||Please ignore this post. I was having a problem with syntax. It is solved.
Regards,
Jamie
"thejamie" wrote:

> I'd like to know how to insert a parenthesis into a field:
> Example:
> insert into MyTable(mydescription) values ('4.ó%?)&.?')
> I tried SET QUOTED_IDENTIFIER ON without success. The above is a scrambled
> password. It must go into the database exactly as it appears.
> --
> Regards,
> Jamie
|||Thanks David,
Ah... you're dead right and as it is now, I'm storing both. I figure that
scrambling the password is adequate to keep people from knowing that they
are passwords stored in a database provided I don't name the field something
conspicuous like 'password'. I'm not doing rocket science here, just
creating a record to read. Each scrambled password is also hashed. If the
scrambled password is altered, the hash won't work. I have enough checks
and balances to satisfy management and that satisfies me. I hash dates,
cpuids, networklogins, userid's, aliases... anything I can think of that
someone might play with. Probably slows the database down a bit, but since
it all gets done at startup, I can live with that too. I probably overdo
the hash thing and one of these days, I'll trim it down. For now, too much
is probably enough. Something like that.
Thanks for the advice though. Never thought of using the varbinary to
store the string. I do use it for the hash. The special characters should
store in the varchar though, shoudn't they?
Giac
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0tqdndBnn_mcJPjcRVn-jg@.giganews.com...
> Your code actually worked for me. The parenthesis shouldn't cause a
> problem but some non-printable, control characters might. I suggest you
> insert data like this as VARBINARY rather than strings so that you can
> safely insert any byte values you may require.
> Passwords? Don't store them in the database. Store a secure hash of the
> password in the database instead. Maybe you meant that this was a password
> hash but your use of the word "scrambled" implied to me that this is an
> *encrypted* password. Storing encrypted passwords is not really a good
> idea from a security point-of-view.
> --
> David Portas
> SQL Server MVP
> --
>
sql

How to insert a parenthesis into a field

I'd like to know how to insert a parenthesis into a field:
Example:
insert into MyTable(mydescription) values ('4.ó%ø)&.ö')
I tried SET QUOTED_IDENTIFIER ON without success. The above is a scrambled
password. It must go into the database exactly as it appears.
--
Regards,
JamieYour code actually worked for me. The parenthesis shouldn't cause a problem
but some non-printable, control characters might. I suggest you insert data
like this as VARBINARY rather than strings so that you can safely insert any
byte values you may require.
Passwords? Don't store them in the database. Store a secure hash of the
password in the database instead. Maybe you meant that this was a password
hash but your use of the word "scrambled" implied to me that this is an
*encrypted* password. Storing encrypted passwords is not really a good idea
from a security point-of-view.
--
David Portas
SQL Server MVP
--|||Please ignore this post. I was having a problem with syntax. It is solved.
Regards,
Jamie
"thejamie" wrote:
> I'd like to know how to insert a parenthesis into a field:
> Example:
> insert into MyTable(mydescription) values ('4.ó%ø)&.ö')
> I tried SET QUOTED_IDENTIFIER ON without success. The above is a scrambled
> password. It must go into the database exactly as it appears.
> --
> Regards,
> Jamie|||Thanks David,
Ah... you're dead right and as it is now, I'm storing both. I figure that
scrambling the password is adequate to keep people from knowing that they
are passwords stored in a database provided I don't name the field something
conspicuous like 'password'. I'm not doing rocket science here, just
creating a record to read. Each scrambled password is also hashed. If the
scrambled password is altered, the hash won't work. I have enough checks
and balances to satisfy management and that satisfies me. I hash dates,
cpuids, networklogins, userid's, aliases... anything I can think of that
someone might play with. Probably slows the database down a bit, but since
it all gets done at startup, I can live with that too. I probably overdo
the hash thing and one of these days, I'll trim it down. For now, too much
is probably enough. Something like that.
Thanks for the advice though. Never thought of using the varbinary to
store the string. I do use it for the hash. The special characters should
store in the varchar though, shoudn't they?
Giac
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:0tqdndBnn_mcJPjcRVn-jg@.giganews.com...
> Your code actually worked for me. The parenthesis shouldn't cause a
> problem but some non-printable, control characters might. I suggest you
> insert data like this as VARBINARY rather than strings so that you can
> safely insert any byte values you may require.
> Passwords? Don't store them in the database. Store a secure hash of the
> password in the database instead. Maybe you meant that this was a password
> hash but your use of the word "scrambled" implied to me that this is an
> *encrypted* password. Storing encrypted passwords is not really a good
> idea from a security point-of-view.
> --
> David Portas
> SQL Server MVP
> --
>

how to insert a NULL in a table

hello,
in a table i have a field of type bit which allows NULL.
if a new record is created and the field is not set a NULL value is created.
Now my question: if the field was set to the value 0 or 1 how can i reset
that field manualy to NULL. Can i use some key STRG + ...?
thanksUsing the Enterprise manager you can use STRG + 0 to insert a NULL,
using an update statement it would be:
UPDATE Sometable SET SomeColumn = NULL Where is = SomeIdValue
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--|||thanks for your help
"Jens" wrote:

> Using the Enterprise manager you can use STRG + 0 to insert a NULL,
> using an update statement it would be:
> UPDATE Sometable SET SomeColumn = NULL Where is = SomeIdValue
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Through the GUI its usually CTRL 0 together.
With command line its
UPDATE <table>
SET yourcol = NULL
WHERE ...
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:B7AB5BA4-8AAB-425C-9668-1C435E96A613@.microsoft.com...
> hello,
> in a table i have a field of type bit which allows NULL.
> if a new record is created and the field is not set a NULL value is
> created.
> Now my question: if the field was set to the value 0 or 1 how can i reset
> that field manualy to NULL. Can i use some key STRG + ...?
> thanks
>|||thanks Toni
"Tony Rogerson" wrote:

> Through the GUI its usually CTRL 0 together.
> With command line its
> UPDATE <table>
> SET yourcol = NULL
> WHERE ...
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:B7AB5BA4-8AAB-425C-9668-1C435E96A613@.microsoft.com...
>
>

How to insert a german ole objet (bmg-Image) to a RS Report

Hi all,
how can i get an image from a db (blob) field that was inseret into the DB
with Access (OLE-Object) into a Report?!
On Internet (Microsoft KB) i found the following Source
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!VolvoProductExtLogo.Value),105))
But on my side this source does not work. I have researched that there are
different length on the ole header. How do i get the ole header length for an
german ole bmp object?!
Thanks
Danieli have found a solution
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),97))
"Daniel Doerfel" wrote:
> Hi all,
> how can i get an image from a db (blob) field that was inseret into the DB
> with Access (OLE-Object) into a Report?!
> On Internet (Microsoft KB) i found the following Source
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!VolvoProductExtLogo.Value),105))
> But on my side this source does not work. I have researched that there are
> different length on the ole header. How do i get the ole header length for an
> german ole bmp object?!
> Thanks
> Daniel
>sql

How to inquire on column headings?

Hello!
I'd like to be able to find out, programmatically, what field names are there in a particular table. I use JScript and an MS Access database.
Also, if possible, if there is a particular table in the database.
And to do it so that no error message is generated.
Is there a way in SQL? I spent considerable time looking for an SQL syntax for that query, without any success.There is a MS Access forum on this site. The below should help you out:

http://www.dbforums.com/showthread.php?threadid=755288&highlight=column+names

Originally posted by masha
Hello!
I'd like to be able to find out, programmatically, what field names are there in a particular table. I use JScript and an MS Access database.
Also, if possible, if there is a particular table in the database.
And to do it so that no error message is generated.
Is there a way in SQL? I spent considerable time looking for an SQL syntax for that query, without any success.|||Originally posted by dmmac
There is a MS Access forum on this site. The below should help you out:

http://www.dbforums.com/showthread.php?threadid=755288&highlight=column+names

Thank you very much! That solves it indeed.
Sorry for posting in this forum. I did it because I hoped that it were possible using pure SQL.
Thanks again!sql

how to index large varchar field

Here's the situation: we have a character based field of up to 2048
characters in length that we want to store in a single column in a
sqlserver table. need to be able to search on this field and guarantee
uniqueness. it would be nice if this field has an index with a unique
constraint. the problem is that sqlserver can't index anything over
900 characters in length.
any suggestions appreciated.
we have a couple thoughts so far:
1 - compress this field in the data access layer before saving in sql -
that should keep the length of the field under 900 chars and let us
apply a unique index in sql. however, we would still have the
uncompressed, unindexed version of the field in another column
searching.
2 - split the field into multiple columns each under 900 characters.
apply an index to each column. however, we'd lose the uniqueness
constraint and this would complicate searches.
3 - combination of 1 and 2.
by the way - we're using SQL Server 2005.
In an effort to maintain uniqueness you could investigate creating three
additional columns (varchar (683)) then popultate the data from the field in
question evenly across the new columns. To ensure uniqueness you could
created a concatenated key with the three new columns. Just a thought....
Thomas
"steve.c.thompson@.gmail.com" wrote:

> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.
>
|||I was thinking the same thing Thomas. I created the three additional
columns and the tried to create the composity key, but sql won't create
an index across the three fields since the sum of the field lengths is
greater then 900.
steve.
|||steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and
> guarantee uniqueness. it would be nice if this field has an index
> with a unique constraint. the problem is that sqlserver can't index
> anything over 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql
> - that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
Compression means you're dealing with binary data and you're not going
to get a lot of compression on a max of 2K worth of data.

> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
Adds additional management.
I would recommend that you hash the data and generate a 32-bit hash
value. You can do this from the client using .Net, for example, or using
an extended stored procedure. SQL Server has the T-SQL BINARY_CHECKSUM()
function, but it has some limitations generating the same checksum for a
few different values. However, the T-SQL function would be quite easy to
implement using an INSERT/UPDATE trigger on the table.
select BINARY_CHECKSUM('ABC123'), BINARY_CHECKSUM('aBC123')
|||Steve,
I think you must get the data size down to 900 characters or less,
otherwise you will not be able to guarantee uniqueness.
As for the searching: if you have to search in the texts (for example
LIKE '%some text%'), then an index is only useful if the data column is
narrow in comparison to average row size. That is probably not the case
here, which means a table scan (or clustered index scan) is probably
most efficient.
If it is relatively small in comparison to the average row size, then
you could create a separate table with only a key column and the
varchar(2048) column, with a one-to-one relation to the original table.
HTH,
Gert-Jan
steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.
|||Thanks for the replies guys.
We were originally using a hash for the key as David suggested, but
thinking back to my days at university when we had to program hash
algorithms, they dont gaurantee unquiness. In which case the insert
would cause a sql exception and we wouldn't be able to store certain
data strings with non-unique hashes. That would be bad.
In the end, I think we're just going to have to reduce the field to 900
characters as Gert-Jan has suggested. I don't think there's any other
way around it.
steve
|||why didnt u use the text datatype..any reason
|||i need to be able to put a unique index on this field, text fields do
not allow that.

how to index large varchar field

Here's the situation: we have a character based field of up to 2048
characters in length that we want to store in a single column in a
sqlserver table. need to be able to search on this field and guarantee
uniqueness. it would be nice if this field has an index with a unique
constraint. the problem is that sqlserver can't index anything over
900 characters in length.
any suggestions appreciated.
we have a couple thoughts so far:
1 - compress this field in the data access layer before saving in sql -
that should keep the length of the field under 900 chars and let us
apply a unique index in sql. however, we would still have the
uncompressed, unindexed version of the field in another column
searching.
2 - split the field into multiple columns each under 900 characters.
apply an index to each column. however, we'd lose the uniqueness
constraint and this would complicate searches.
3 - combination of 1 and 2.
by the way - we're using SQL Server 2005.In an effort to maintain uniqueness you could investigate creating three
additional columns (varchar (683)) then popultate the data from the field in
question evenly across the new columns. To ensure uniqueness you could
created a concatenated key with the three new columns. Just a thought....
--
Thomas
"steve.c.thompson@.gmail.com" wrote:

> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.
>|||I was thinking the same thing Thomas. I created the three additional
columns and the tried to create the composity key, but sql won't create
an index across the three fields since the sum of the field lengths is
greater then 900.
steve.|||steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and
> guarantee uniqueness. it would be nice if this field has an index
> with a unique constraint. the problem is that sqlserver can't index
> anything over 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql
> - that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
Compression means you're dealing with binary data and you're not going
to get a lot of compression on a max of 2K worth of data.

> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
Adds additional management.
I would recommend that you hash the data and generate a 32-bit hash
value. You can do this from the client using .Net, for example, or using
an extended stored procedure. SQL Server has the T-SQL BINARY_CHECKSUM()
function, but it has some limitations generating the same checksum for a
few different values. However, the T-SQL function would be quite easy to
implement using an INSERT/UPDATE trigger on the table.
select BINARY_CHECKSUM('ABC123'), BINARY_CHECKSUM('aBC123')|||Steve,
I think you must get the data size down to 900 characters or less,
otherwise you will not be able to guarantee uniqueness.
As for the searching: if you have to search in the texts (for example
LIKE '%some text%'), then an index is only useful if the data column is
narrow in comparison to average row size. That is probably not the case
here, which means a table scan (or clustered index scan) is probably
most efficient.
If it is relatively small in comparison to the average row size, then
you could create a separate table with only a key column and the
varchar(2048) column, with a one-to-one relation to the original table.
HTH,
Gert-Jan
steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.|||Thanks for the replies guys.
We were originally using a hash for the key as David suggested, but
thinking back to my days at university when we had to program hash
algorithms, they dont gaurantee unquiness. In which case the insert
would cause a sql exception and we wouldn't be able to store certain
data strings with non-unique hashes. That would be bad.
In the end, I think we're just going to have to reduce the field to 900
characters as Gert-Jan has suggested. I don't think there's any other
way around it.
steve|||why didnt u use the text datatype..any reason|||i need to be able to put a unique index on this field, text fields do
not allow that.sql

how to index large varchar field

Here's the situation: we have a character based field of up to 2048
characters in length that we want to store in a single column in a
sqlserver table. need to be able to search on this field and guarantee
uniqueness. it would be nice if this field has an index with a unique
constraint. the problem is that sqlserver can't index anything over
900 characters in length.
any suggestions appreciated.
we have a couple thoughts so far:
1 - compress this field in the data access layer before saving in sql -
that should keep the length of the field under 900 chars and let us
apply a unique index in sql. however, we would still have the
uncompressed, unindexed version of the field in another column
searching.
2 - split the field into multiple columns each under 900 characters.
apply an index to each column. however, we'd lose the uniqueness
constraint and this would complicate searches.
3 - combination of 1 and 2.
by the way - we're using SQL Server 2005.In an effort to maintain uniqueness you could investigate creating three
additional columns (varchar (683)) then popultate the data from the field in
question evenly across the new columns. To ensure uniqueness you could
created a concatenated key with the three new columns. Just a thought....
--
Thomas
"steve.c.thompson@.gmail.com" wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.
>|||I was thinking the same thing Thomas. I created the three additional
columns and the tried to create the composity key, but sql won't create
an index across the three fields since the sum of the field lengths is
greater then 900.
steve.|||steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and
> guarantee uniqueness. it would be nice if this field has an index
> with a unique constraint. the problem is that sqlserver can't index
> anything over 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql
> - that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
Compression means you're dealing with binary data and you're not going
to get a lot of compression on a max of 2K worth of data.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
Adds additional management.
I would recommend that you hash the data and generate a 32-bit hash
value. You can do this from the client using .Net, for example, or using
an extended stored procedure. SQL Server has the T-SQL BINARY_CHECKSUM()
function, but it has some limitations generating the same checksum for a
few different values. However, the T-SQL function would be quite easy to
implement using an INSERT/UPDATE trigger on the table.
select BINARY_CHECKSUM('ABC123'), BINARY_CHECKSUM('aBC123')|||Steve,
I think you must get the data size down to 900 characters or less,
otherwise you will not be able to guarantee uniqueness.
As for the searching: if you have to search in the texts (for example
LIKE '%some text%'), then an index is only useful if the data column is
narrow in comparison to average row size. That is probably not the case
here, which means a table scan (or clustered index scan) is probably
most efficient.
If it is relatively small in comparison to the average row size, then
you could create a separate table with only a key column and the
varchar(2048) column, with a one-to-one relation to the original table.
HTH,
Gert-Jan
steve.c.thompson@.gmail.com wrote:
> Here's the situation: we have a character based field of up to 2048
> characters in length that we want to store in a single column in a
> sqlserver table. need to be able to search on this field and guarantee
> uniqueness. it would be nice if this field has an index with a unique
> constraint. the problem is that sqlserver can't index anything over
> 900 characters in length.
> any suggestions appreciated.
> we have a couple thoughts so far:
> 1 - compress this field in the data access layer before saving in sql -
> that should keep the length of the field under 900 chars and let us
> apply a unique index in sql. however, we would still have the
> uncompressed, unindexed version of the field in another column
> searching.
> 2 - split the field into multiple columns each under 900 characters.
> apply an index to each column. however, we'd lose the uniqueness
> constraint and this would complicate searches.
> 3 - combination of 1 and 2.
> by the way - we're using SQL Server 2005.|||Thanks for the replies guys.
We were originally using a hash for the key as David suggested, but
thinking back to my days at university when we had to program hash
algorithms, they dont gaurantee unquiness. In which case the insert
would cause a sql exception and we wouldn't be able to store certain
data strings with non-unique hashes. That would be bad.
In the end, I think we're just going to have to reduce the field to 900
characters as Gert-Jan has suggested. I don't think there's any other
way around it.
steve|||why didnt u use the text datatype..any reason|||i need to be able to put a unique index on this field, text fields do
not allow that.

How to increment field after selecting it

I have FeaturedClassifiedsCount field, which I would like to update each time record is selected. How do I do it in stored procedure on SQL 2005?

This is my existing code:

alterPROCEDURE dbo.SP_FeaturedClassifieds

@.PageIndexINT,

@.NumRowsINT,

@.FeaturedClassifiedsCountINTOUTPUT

AS

BEGIN

select @.FeaturedClassifiedsCount=(SelectCount(*)From classifieds_AdsWhere AdStatus=100And Adlevel=50)

Declare @.startRowIndexINT;

Set @.startRowIndex=(@.PageIndex* @.NumRows)+ 1;

With FeaturedClassifiedsas(

SelectROW_NUMBER()OVER(OrderBy FeaturedDisplayedCount*(1-(Weight-1)/100)ASC)as

Row, Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

classifieds_Ads

Where

AdStatus=100And AdLevel=50

)

Select

Id, PreviewImageId, Title, DateCreated, FeaturedDisplayedCount

From

FeaturedClassifieds

Where

Rowbetween

@.startRowIndexAnd @.startRowIndex+@.NumRows-1

END

Hello rfurdzik,

Am I correct that you want to update the counter in the table Classified_Ads? Try to add an update statement before the last select statement :

UPDATE Classified_Ads SET FeaturedDisplayedCount = FeaturedDisplayedCount + 1

FROM FeaturedClassifieds

WHERE FeaturedClassifieds.Id = Classified_Ads.Id

AND Row between (@.startRowIndex AND @.startRowIndex + @.NumRows - 1)

How to increment a field for a Line Number?

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

HI ,
Can you tell me clearly.please send how you tried it|||What is the relationship between the two columns ?

I don't think SQL Server 2000 will allow you to use the result of a Stored/Procedure / User Defined Function as the default value for a column.

SQL Server 2005 might.|||Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END|||

Quote:

Originally Posted by TrentC

Thanks. Here is what I have tried but my SQL is very rusty and I havent really dealt with triggers before. There is no relationship between the columns except that they are both in same table.

Alter trigger UpdateLineNumber
ON [F47012-IntegrationTable]
AFTER INSERT
AS
DECLARE@.LineNum INT

BEGIN

SELECT @.LineNum = COUNT(*) + 1
FROM [F47012-IntegrationTable]
WHERE Document_Number = (SELECT Document_Number FROM Inserted)

UPDATE [F47012-IntegrationTable]
SET Line_Number = @.LineNum
WHERE Document_Number = (SELECT Document_Number FROM Inserted)
AND Line_Number IS NULL

END

It looks like you are trying to replicate the functionality of an identity column.

Unless the value in your line_number column is directly related to the information in that record just make your line_number column an int column with identity turned on and seed at +1.|||

Quote:

Originally Posted by TrentC

Hi I am trying to work out how to automatically create a line number based up values in another column. For example:

DocNum LineNum
1 1
2 1
2 2
3 1
4 1
4 2
4 3

Any ideas?

I am tring to do is in MS SQL Server

Hi,

I recently had to do the same thing, if your table has an identity column (i.e. primary key that is incremented automatically when you insert a new row), which all tables should, then you can use the following method:

First of all insert all your DocNum records into the table so that the identity column (ID say) is updated automatically. Then update the LineNum field as follows:

UPDATE
[TABLE_NAME]
SET
LineNum = (SELECT
COUNT(*)
FROM
[TABLE_NAME] t1
WHERE
t1.ID <= [TABLE_NAME].ID
AND
t1.DocNum = [TABLE_NAME].DocNum
)

How to incorporate a table field into the email message body nto as an attachmen

Hello everyone,

Please i need your help...

I dont know how to place the field 'strTitle and datBorrowed " in my email? Not as an attachment though...Just write it in the mail as part of message body...

I use this SQL select statement to retrieve the strTitle and datBorrowed fields

strSQL += @."Select replace(strtitle,'[Original Book] - ',''), datBorrowed from tblBooks where convert(varchar(10),datBorrowed,101) = convert(varchar(10),(getdate() - 1),101) ORDER BY strTitle asc";

Now, I have the following code to write the email

static void SendTest()
{

int iEmailLanguage = 0;
MailMessage objMail;
objMail = new MailMessage();
objMail.From = MAIL_FROM;
objMail.To =MAIL_TO;
objMail.Subject = "Books Borrowed Yesterday";
objMail.Body = Dict.GetVal(iEmailLanguage, "EMAIL_MESSAGE");
objMail.Attachments.Add(new MailAttachment(strAttachment));
SmtpMail.SmtpServer = SSMTP_SERVER;
SmtpMail.Send(objMail);
}

And the body of the email is this.....

Dict.AddVal(0, "EMAIL_MESSAGE", "*** This e-mail is automatically generated. ***\n" +
"*** PLEASE DO NOT REPLY TO THIS E-MAIL. ***\n" +
"\n" +
"Books Borrowed Yesterday are:\n" +

"\n" +
"\n" +
"Thank you,\n" +
"\n" +
"eLibrarian\n" +
"\n" +
"================================================== ===============\n" +
"\n" +
"This e-mail is automatically generated by the Library system.\n" +
"Please do not reply.");

i need to put or wedge the data i got from the SQL Statement into this or after the line "Books Borrowed Yesterday are:\n" +

So how should i do this?you want to execute the strSQL. Obtain the result and append it to your body string before calling the Dict.add/GetVal().|||you want to execute the strSQL. Obtain the result and append it to your body string before calling the Dict.add/GetVal().

Im sorry sir...but I'm just a newbie in programming...How should i do it? Could you give me an example? :o|||Please take a look at the link below and download the sample codes:
http://msdn.microsoft.com/vcsharp/downloads/samples/default.aspx

They should help/guide you through your learning/development.sql

How to include a dataset field value in the header

Since I can not directly use a field value of a dataset in the header and
footer section of a report, I'm trying to store this field value in a
variable in the custom code.
Is there a way to do this?
Thanks...I would suggest using parameter with default value from query.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"eralper" <eralper@.discussions.microsoft.com> wrote in message
news:5F028ED8-E8BC-4795-A79E-3481CECC9EA6@.microsoft.com...
> Since I can not directly use a field value of a dataset in the header and
> footer section of a report, I'm trying to store this field value in a
> variable in the custom code.
> Is there a way to do this?
> Thanks...sql

how to implement unique key on multiple field

hello guys,

I have one table which is using to keep 10 difference type of serial number (that mean i got 10 column in the table). Is there any way to do unique key checking (individually, not combine) on these 10 serial number without sacrify the performance?

thank you.

Sure, i you wnt to check them only per column you can put a UNIQUE Constraint on those columns, that will check like the primary key for uniqueness.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
MacYou can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out[vbcol=seagreen]
>|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out[vbcol=seagreen]
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMSShipped') = 0
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
have[vbcol=seagreen]
> defined
of[vbcol=seagreen]
have[vbcol=seagreen]
not[vbcol=seagreen]
> out
>|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> system
ObjectProperty[vbcol=seagreen]
> have
> of
> have
> not
this[vbcol=seagreen]
procs[vbcol=seagreen]
>