How to make UDF deterministic

I have a user defined function in SQL Server 2000 that splits a Name field into First, Last, Middle names. Originally I utilized the CHARINDEX function to find the spaces in the string. Since I then found out that function is by definition Non-deterministic that didn't work for me, so I re-wrote the function a bit differently.

I am using the function as a field's formula in my table structure. I need the ability to create an index on that field so need the function to be deterministic. Apparently there is still something about how I designed the function that causes it to be non-deterministic. The function code is below; if anyone can help point me in the right direction it'd be greatly appreciated.

ALTER function fSplitName_test
(
@.name varchar(100),
@.fname int)
returns varchar(55)
as

begin
declare @.Fname_position int, @.Lname_position int, @.Mname_position int, @.SplitName varchar(55), @.iint int, @.iLen int,
@.sChar char(1), @.sName varchar(55)

set @.name = ltrim(replace(@.name, ' JR', ''))
set @.sName = @.name
set @.ilen = len(rtrim(@.name))
set @.Fname_position = 0
set @.Lname_position = 0
set @.iint = 0

while @.iint <> @.ilen
begin
set @.schar = left(@.sName, 1)
begin
if @.schar = ' '
begin
if @.Fname_position = 0
set @.Fname_position = @.iint
else
set @.Lname_position = @.iint
end
set @.iint = @.iint + 1
set @.sName = right(@.sName, @.ilen - @.iint)
end
end
if @.fname = 1
begin
if @.fname_position <> 0
set @.SplitName = left(@.name, @.Fname_position)
else
set @.splitname = 'unkn'
end
else
begin
if @.fname = 2
begin
if @.lname_position <> 0
set @.SplitName = right(@.name, @.ilen - @.Lname_position-1)
else
set @.SplitName = right(@.name, @.ilen - @.fname_position)
end
else
begin
if @.fname_position + @.lname_position <> @.ilen and @.lname_position <> 0
begin
set @.SplitName = left(@.name, @.lname_position)
set @.splitname = right(@.splitname,len(@.splitname) - @.fname_position - 1)
end
else
set @.splitname = 'unkn'
end
end
return @.SplitName
endYou might get more response if you post this under the "Microsoft SQL Server" forum. This one's for generic SQL questions - I for one know nothing about SQL Server.|||what if a person has a lastname like this "DELA CRUZ"
can you post an example of the name parameter ex: "Michael Angelo Rodriguez" or "Michael Angelo Dela Cruz" coz as you can see on the 2 ex. it is hard to detemine the first name on the first example and last name on the latter the name should have a middle initial or comma perhaps.|||This is probably completely wrong, but I wouldn't put it past SQL Server to think that you were trying to modify the parameters. That would, of course, make the function non-deterministic. Try copying them to local variables.

Otherwise, the way to debug a problem like this is to break your function into small functions and verify that the small functions are all deterministic.sql

how to make password field case sensitive in sql server 2005

Hi,

SELECT UserID, UserName, Password, PublisherID, Currency
FROM [User]
WHERE (Password = 'Anitha') I am using the above mentioned it is working but int the password field i had given it as anitha. Now the querry is retriving the record for anitha, it shouldnot happen. The querry should retrive the record of anitha only for where condition anitha and not for Anitha or ANITHA etc..

Thanks

Vishwanath

Convert the password field and password value to varbinary and compare if they are equal.

Here is the code:

select

*from userswhere UserName='Nilesh'Andcast(passwordasvarbinary(20))=cast(N'password'asvarbinary(20))

The usename is not case sensitive but password is.

|||

hi Vishwanath,

its good practise to store passwords in encoded/encrypted format. what i do is use a custom encryption function which uses our own logic to encrypt each character and store it.

to add what Nilesh has mentioned read this linkhttp://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

Hope it helps.

regards,

Satish.

|||

Hi satish,

now its working fine, but when i choose the collation property from that password field and changed it to bin format then at that it gave some warning message saying that some of the data will be lost, but when i choose yes i couldnt experience any such loss of data. Everything is working fine..

Thnaks

Vishwanath

|||

ohk sounds cool.

please mark the post which helped you as answered, it will give credit to answerer and post will be resolved.

thanks,

satish.

How to make or add a table with a field with default value

I am doing a shopping basket type demo.

I have difficulties going from table with fields like (Name,ProductCode) to table(Name, ProductCode, NumberOfItems).

One way could be adding just Name and ProductCode fields and let NumberOfItems come automatically. It could have some common value like 1.

How should I do this with VB.

Do you mean that if you insert a record but don't supply values for all the fields that you want the database to default the value? This is easy to do in SQL Server, here's an example of setting a default value of 1

create table junk ( SomeIntColumn smallint NOT NULL DEFAULT 1)

You can also create your own named defaults, then bind them, eg:

create default blank as ' '
go
create default zero as 0
go
create default today as getdate()
go

create table junk(SomeDateColumn datetime not null)

go

exec sp_bindefault today, 'junk.SomeDatecolumnt

go

This binds a default value named "today' to SoemDateColumn in table junk

How to make elements of a field into hyperlinks

Hi,
I've been looking high and low for a way to turn the elements of a
field into hyperlinks and so far I've found nothing. One of the fields
in my report holds all the document hyperlinks pertaining to that
particular record. For example, if the record is about employee A, I
have a field that holds all the document links to employee A like his
security clearance form, annual review documents, etc. All of these
forms were scanned and saved to the HR server. I could extract the
document path "http://companyServer/hr/employeeA/annual review1.pdf;
http://companyServer/hr/employeeA/security clearance form.pdf" and
display them to a field on the report but Reporting Services does not
recognize it as a hyperlink. Is there a way to make each one into a
hyperlink so that the user can click on it and it would open the
respective document in pdf? Please help.
Thank you all,
JonHi Jon,
Have you tried different Naviagton option of Text box property.
You can r you can "jump to a UR"L or jump to a report .
you can use a formula to jump to the pdf file which will be downloaded to
the IE and the user can see it.
Cheers
Bava
"Newbie" wrote:
> Hi,
> I've been looking high and low for a way to turn the elements of a
> field into hyperlinks and so far I've found nothing. One of the fields
> in my report holds all the document hyperlinks pertaining to that
> particular record. For example, if the record is about employee A, I
> have a field that holds all the document links to employee A like his
> security clearance form, annual review documents, etc. All of these
> forms were scanned and saved to the HR server. I could extract the
> document path "http://companyServer/hr/employeeA/annual review1.pdf;
> http://companyServer/hr/employeeA/security clearance form.pdf" and
> display them to a field on the report but Reporting Services does not
> recognize it as a hyperlink. Is there a way to make each one into a
> hyperlink so that the user can click on it and it would open the
> respective document in pdf? Please help.
>
> Thank you all,
> Jon
>|||Correct. He needs to use Jump To Url action on a field. I make the field
underline and blue so users know to click on it. The URL is based on an
expression and hence is very flexible in how you assemble the URL. Plus, you
can cause it to open in another window or in the same window.
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
If you don't want to have it appear in a new window then do this in jump to
URL:
=Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
I am using parameters but you could use the value of the field as well. I do
that quite often.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Bava Mani" <BavaMani@.discussions.microsoft.com> wrote in message
news:F6268370-6FB3-468B-9AD8-6B41CA33EFEE@.microsoft.com...
> Hi Jon,
> Have you tried different Naviagton option of Text box property.
> You can r you can "jump to a UR"L or jump to a report .
> you can use a formula to jump to the pdf file which will be downloaded to
> the IE and the user can see it.
> Cheers
> Bava
> "Newbie" wrote:
>> Hi,
>> I've been looking high and low for a way to turn the elements of a
>> field into hyperlinks and so far I've found nothing. One of the fields
>> in my report holds all the document hyperlinks pertaining to that
>> particular record. For example, if the record is about employee A, I
>> have a field that holds all the document links to employee A like his
>> security clearance form, annual review documents, etc. All of these
>> forms were scanned and saved to the HR server. I could extract the
>> document path "http://companyServer/hr/employeeA/annual review1.pdf;
>> http://companyServer/hr/employeeA/security clearance form.pdf" and
>> display them to a field on the report but Reporting Services does not
>> recognize it as a hyperlink. Is there a way to make each one into a
>> hyperlink so that the user can click on it and it would open the
>> respective document in pdf? Please help.
>>
>> Thank you all,
>> Jon
>>|||Hi Bruce,
I have a related question on this.
you have explained how to proivde hyperlinks in the reports, but can we
provide
a hyperlink in the parameter section of the report page to provide a simple
link that when the user click the hyperlink it will open a window where they
will be prompted
to enter value for the parameter or enter multi select parameter.
I know microsoft providing Multiselect option in 2005 but still there are
not providing option for boolean type parameter where the user can enter
complex search condition with boolan condition in the parameter value. Is
there a way to provide a hyper link for parameter selection so that data for
the parameter is populated from another page. Please let me know if you know
any knowledge on this. Is there any 3rd party product do this customization.
Thanks in advance
Bava
"Bruce L-C [MVP]" wrote:
> Correct. He needs to use Jump To Url action on a field. I make the field
> underline and blue so users know to click on it. The URL is based on an
> expression and hence is very flexible in how you assemble the URL. Plus, you
> can cause it to open in another window or in the same window.
> Here is an example of a Jump to URL link I use. This causes Excel to come up
> with the data in a separate window:
>
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> If you don't want to have it appear in a new window then do this in jump to
> URL:
> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
> I am using parameters but you could use the value of the field as well. I do
> that quite often.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Bava Mani" <BavaMani@.discussions.microsoft.com> wrote in message
> news:F6268370-6FB3-468B-9AD8-6B41CA33EFEE@.microsoft.com...
> > Hi Jon,
> > Have you tried different Naviagton option of Text box property.
> > You can r you can "jump to a UR"L or jump to a report .
> > you can use a formula to jump to the pdf file which will be downloaded to
> > the IE and the user can see it.
> >
> > Cheers
> > Bava
> >
> > "Newbie" wrote:
> >
> >> Hi,
> >>
> >> I've been looking high and low for a way to turn the elements of a
> >> field into hyperlinks and so far I've found nothing. One of the fields
> >>
> >> in my report holds all the document hyperlinks pertaining to that
> >> particular record. For example, if the record is about employee A, I
> >> have a field that holds all the document links to employee A like his
> >> security clearance form, annual review documents, etc. All of these
> >> forms were scanned and saved to the HR server. I could extract the
> >> document path "http://companyServer/hr/employeeA/annual review1.pdf;
> >> http://companyServer/hr/employeeA/security clearance form.pdf" and
> >> display them to a field on the report but Reporting Services does not
> >> recognize it as a hyperlink. Is there a way to make each one into a
> >> hyperlink so that the user can click on it and it would open the
> >> respective document in pdf? Please help.
> >>
> >>
> >> Thank you all,
> >> Jon
> >>
> >>
>
>|||Hi gracious helpers,
I've been busy with other aspects of my project and I totally forgot
about following up on my question. I'll give your suggestion a try.
I've also posted another question on whether or not the "Show Details"
button can be disabled or hidden. Can that be done? Thank you so much
for your generosity.
--Jon
Bruce L-C [MVP] wrote:
> Correct. He needs to use Jump To Url action on a field. I make the field
> underline and blue so users know to click on it. The URL is based on an
> expression and hence is very flexible in how you assemble the URL. Plus, you
> can cause it to open in another window or in the same window.
> Here is an example of a Jump to URL link I use. This causes Excel to come up
> with the data in a separate window:
>
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> If you don't want to have it appear in a new window then do this in jump to
> URL:
> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
> I am using parameters but you could use the value of the field as well. I do
> that quite often.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Bava Mani" <BavaMani@.discussions.microsoft.com> wrote in message
> news:F6268370-6FB3-468B-9AD8-6B41CA33EFEE@.microsoft.com...
> > Hi Jon,
> > Have you tried different Naviagton option of Text box property.
> > You can r you can "jump to a UR"L or jump to a report .
> > you can use a formula to jump to the pdf file which will be downloaded to
> > the IE and the user can see it.
> >
> > Cheers
> > Bava
> >
> > "Newbie" wrote:
> >
> >> Hi,
> >>
> >> I've been looking high and low for a way to turn the elements of a
> >> field into hyperlinks and so far I've found nothing. One of the fields
> >>
> >> in my report holds all the document hyperlinks pertaining to that
> >> particular record. For example, if the record is about employee A, I
> >> have a field that holds all the document links to employee A like his
> >> security clearance form, annual review documents, etc. All of these
> >> forms were scanned and saved to the HR server. I could extract the
> >> document path "http://companyServer/hr/employeeA/annual review1.pdf;
> >> http://companyServer/hr/employeeA/security clearance form.pdf" and
> >> display them to a field on the report but Reporting Services does not
> >> recognize it as a hyperlink. Is there a way to make each one into a
> >> hyperlink so that the user can click on it and it would open the
> >> respective document in pdf? Please help.
> >>
> >>
> >> Thank you all,
> >> Jon
> >>
> >>|||Hi Bruce,
Thank you so very much for your help. I've been busy on other aspects
of my project and totally forgot about this until today when I
revisited it. I'll give you suggestion a try. Please help me again
should I stumble. I've also posted another question on how to hide or
disable the "Show Detail" button. Can that be done?
Thanks a million,
Jon
Bruce L-C [MVP] wrote:
> Correct. He needs to use Jump To Url action on a field. I make the field
> underline and blue so users know to click on it. The URL is based on an
> expression and hence is very flexible in how you assemble the URL. Plus, you
> can cause it to open in another window or in the same window.
> Here is an example of a Jump to URL link I use. This causes Excel to come up
> with the data in a separate window:
>
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> If you don't want to have it appear in a new window then do this in jump to
> URL:
> =Globals!ReportServerUrl & "?/SomeFolder/SomeReport&ParamName=" &
> Parameters!ParamName.Value & "&rs:Format=CSV&rc:Encoding=ASCII"
> I am using parameters but you could use the value of the field as well. I do
> that quite often.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Bava Mani" <BavaMani@.discussions.microsoft.com> wrote in message
> news:F6268370-6FB3-468B-9AD8-6B41CA33EFEE@.microsoft.com...
> > Hi Jon,
> > Have you tried different Naviagton option of Text box property.
> > You can r you can "jump to a UR"L or jump to a report .
> > you can use a formula to jump to the pdf file which will be downloaded to
> > the IE and the user can see it.
> >
> > Cheers
> > Bava
> >
> > "Newbie" wrote:
> >
> >> Hi,
> >>
> >> I've been looking high and low for a way to turn the elements of a
> >> field into hyperlinks and so far I've found nothing. One of the fields
> >>
> >> in my report holds all the document hyperlinks pertaining to that
> >> particular record. For example, if the record is about employee A, I
> >> have a field that holds all the document links to employee A like his
> >> security clearance form, annual review documents, etc. All of these
> >> forms were scanned and saved to the HR server. I could extract the
> >> document path "http://companyServer/hr/employeeA/annual review1.pdf;
> >> http://companyServer/hr/employeeA/security clearance form.pdf" and
> >> display them to a field on the report but Reporting Services does not
> >> recognize it as a hyperlink. Is there a way to make each one into a
> >> hyperlink so that the user can click on it and it would open the
> >> respective document in pdf? Please help.
> >>
> >>
> >> Thank you all,
> >> Jon
> >>
> >>sql

How to make computed field out of conditional

Short Form of Question.
How to do IF statement in computed field. Wanting to concat on to
resulting field based on simple conditional.
made up syntax of what I want:
STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
Long Form:
I want to make a computer field like the following. I'm going to make
up the syntax which of course doesn't work. Wondering if someone
could suggest some syntax that does.
Thanks
Database Columns:
ActiveEmail bit,
users_id Int
id INT
STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
that is, say users_id=10
id=99
ActiveEmail=1
I want for my computer column 10A
users_id=10
if id=99
activeemail=0
I get 1099
THanks,
Peter Kellner
http://peterkellner.net
Peter Kellner
http://peterkellner.netHi Peter
Try something like:
SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
ELSE CAST(id AS VARCHAR(10)) END AS Result
FROM MyTable
John
"Peter Kellner" <pkellnernews@.73rdstreet.com> wrote in message
news:uu084259i3cclbbc61uhhju9c834f7c2mg@.
4ax.com...
> Short Form of Question.
> How to do IF statement in computed field. Wanting to concat on to
> resulting field based on simple conditional.
> made up syntax of what I want:
> STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
> --
> Long Form:
> I want to make a computer field like the following. I'm going to make
> up the syntax which of course doesn't work. Wondering if someone
> could suggest some syntax that does.
> Thanks
> Database Columns:
> ActiveEmail bit,
> users_id Int
> id INT
> STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
> that is, say users_id=10
> id=99
> ActiveEmail=1
> I want for my computer column 10A
> users_id=10
> if id=99
> activeemail=0
> I get 1099
> THanks,
> Peter Kellner
> http://peterkellner.net
> Peter Kellner
> http://peterkellner.net|||On Mon, 17 Apr 2006 22:33:32 +0100, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:

>Hi Peter
>Try something like:
>SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
>ELSE CAST(id AS VARCHAR(10)) END AS Result
>FROM MyTable
>John
>
Perfect! Thank you John.
Peter Kellner
http://peterkellner.net|||On Mon, 17 Apr 2006 22:33:32 +0100, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:

>Hi Peter
>Try something like:
>SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
>ELSE CAST(id AS VARCHAR(10)) END AS Result
>FROM MyTable
>John
>
Perfect! Thank you John.
Peter Kellner
http://peterkellner.net

How to make computed field out of conditional

Short Form of Question.
How to do IF statement in computed field. Wanting to concat on to
resulting field based on simple conditional.
made up syntax of what I want:
STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
--
Long Form:
I want to make a computer field like the following. I'm going to make
up the syntax which of course doesn't work. Wondering if someone
could suggest some syntax that does.
Thanks
Database Columns:
ActiveEmail bit,
users_id Int
id INT
STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
that is, say users_id=10
id=99
ActiveEmail=1
I want for my computer column 10A
users_id=10
if id=99
activeemail=0
I get 1099
THanks,
Peter Kellner
http://peterkellner.net
Peter Kellner
http://peterkellner.netHi Peter
Try something like:
SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
ELSE CAST(id AS VARCHAR(10)) END AS Result
FROM MyTable
John
"Peter Kellner" <pkellnernews@.73rdstreet.com> wrote in message
news:uu084259i3cclbbc61uhhju9c834f7c2mg@.4ax.com...
> Short Form of Question.
> How to do IF statement in computed field. Wanting to concat on to
> resulting field based on simple conditional.
> made up syntax of what I want:
> STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
> --
> Long Form:
> I want to make a computer field like the following. I'm going to make
> up the syntax which of course doesn't work. Wondering if someone
> could suggest some syntax that does.
> Thanks
> Database Columns:
> ActiveEmail bit,
> users_id Int
> id INT
> STR(users_id) + IF(ActiveEmail==1,'A',STR(id))
> that is, say users_id=10
> id=99
> ActiveEmail=1
> I want for my computer column 10A
> users_id=10
> if id=99
> activeemail=0
> I get 1099
> THanks,
> Peter Kellner
> http://peterkellner.net
> Peter Kellner
> http://peterkellner.net|||On Mon, 17 Apr 2006 22:33:32 +0100, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:
>Hi Peter
>Try something like:
>SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
>ELSE CAST(id AS VARCHAR(10)) END AS Result
>FROM MyTable
>John
>
Perfect! Thank you John.
Peter Kellner
http://peterkellner.net|||On Mon, 17 Apr 2006 22:33:32 +0100, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:
>Hi Peter
>Try something like:
>SELECT CAST (users_id AS VARCHAR(10)) + CASE WHEN ActiveEmail = 1 THEN 'A'
>ELSE CAST(id AS VARCHAR(10)) END AS Result
>FROM MyTable
>John
>
Perfect! Thank you John.
Peter Kellner
http://peterkellner.netsql

How to make a trigger refer to the current record

I have a table full of items that have a "date_updated" field. I'd like this field to be set to GETDATE() whenever a record is updated. I've got this trigger:

CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
update cp_shiptos set date_updated = GETDATE()

Problem is, of course, there's no WHERE clause..yet. I don't know how to refer to the record that was updated... for example:

CREATE trigger tr_cp_shiptos_u on dbo.cp_shiptos for update as
update cp_shiptos set date_updated = GETDATE()
where shipto_id = @.THIS_ID

I imagine there's some kind of builtin variable or something like that. How is this done?

Thanks in advance.CREATE TRIGGER tr_cp_shiptos_u ON dbo.cp_shiptos
FOR UPDATE
AS
BEGIN
UPDATE c
SET date_updated = GETDATE()
FROM cp_shiptos c
JOIN inserted i
ON i.key = c.key
END
GO

How to make a SELECT with a field name inside a variable?

I have a table (for example PERSONS) with several fields (NIF NAME AGE).
I have a cursors than read the differents fiels tat have this table inside a trigger fron another table that contain all the fields of the tables.
This cursor save the name of the fiels inside the variable @.FIELD.
I need to read the value of the several records of the table like:

@.FIELD='NIF' <--(CURSOR)

SELECT @.FIELS FEOM INSERTED --> select nif from inserted(persons)

This sentence doesn't works. How can I do?

Thanks, Otto Martinez.Hi,
you maye use a variable to generate the whole statement then use the exec command
Declare @.sql NVarchar(200)
set @.sql= "Select " + @.Fiels +" from inserted"
exec @.sql

How to make (int) Identity field showed like (0001) ?

Hi,

How to make (int) Identity auto increment field showed like

0001

0002

0003

0004

...

Lew:

To perform the formatting you ask about will require a little code and will require converting your IDENTITY column to VARCHAR or NVARCHAR -- something like:

right('000'+convert(varchar(4),anIdentity),4)

For example:

declare @.anIdentityTable table (anIdentity integer identity)
insert into @.anIdentityTable default values

select anIdentity,
right('000'+convert(varchar(4),anIdentity),4) formattedIdentity
from @.anIdentityTable

-- anIdentity formattedIdentity
-- -- --
-- 1 0001

How to load XML from filename and path

Hi,

I have inherited a database that has a table called "Resources" that contains a field called "FilePath". FilePath is a VarChar(100) and contains the server path and filename of an XML file on the server hard disk.

I need to join the data in the XML with rows in the "Resources" table (and other tables).

Is it possible to get a Stored Procedure to load the XML file into a temporary table?

Is loading it into a table the best way to do it - or can I join directly to the XML file somehow?

What is the best approach? (I know nothing about XML in SQL Server yet)

Thanks in advance,

Chiz.

Try the link below to use OPENXML in SQL Server, I am not sure about Temp tables I would try it to see if it is possible, just remember to drop the temp table explictly. Hope this helps.
http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx|||Thanks Caddre,
At the moment the .NET code calls a Stored Procedure which JOINs the Resources table with some other tables and returns the result which goes into a DataSet.
In order to build a DataSet that can be displayed in a DataGrid the DataSet has to be looked at (one row at a time) and the FilePath 'field' used to load the XML file from disk into an XmlDocument. Then the right data in the right node has to be found and then finally when that is found it can be added to the DataSet.
Anyway, to cut a long story short, I reckon that if I can somehow get T-SQL to load an XML file from disk into a temporary table that it will be neater and more efficient.
Any other help appreciated,
Chiz.|||

The text below is from the BOL(books online) sp_xml_preparedocument System stored Procedure can be used, run a search for sp_xml_preparedocument in the BOL (books online). Hope this helps

(sp_xml_preparedocument
Reads the Extensible Markup Language (XML) text provided as input, then parses the text using the MSXML parser (Msxml2.dll), and provides the parsed document in a state ready for consumption. This parsed document is a tree representation of the various nodes (elements, attributes, text, comments, and so on) in the XML document.)

how to let sql server generate a random id of bigint field from 10000 to the MAX BIGINT?

all generated IDs must be unique.how?
any help would be appreciated.
See the old posts:-
http://groups.google.co.in/group/com...cbf13c18513f64
If you just need the unique number then define a identitiy property.
CREATE TABLE test(i bigint identity(10000,1), k varchar(1)
insert into test (k) values('h')
This will start inserting the value from 10001
Thanks
Hari
SQL Server MVP
"wen" <wenming_hu2002@.hotmail.com> wrote in message
news:%23YYMC%23WaFHA.2756@.tk2msftngp13.phx.gbl...
> all generated IDs must be unique.how?
> any help would be appreciated.
>
|||There are a couple of ways you could do this but they depend on the reason
why you need to do it. In short, you haven't supplied sufficient
information for us to assist you.
For instance, why not use an IDENTITY? Or, why not use a UUID?
How long do they need to be unique? How frequently will you need them? All
at once or only one at a time?
Sincerely,
Anthony Thomas

"wen" <wenming_hu2002@.hotmail.com> wrote in message
news:%23YYMC%23WaFHA.2756@.tk2msftngp13.phx.gbl...
all generated IDs must be unique.how?
any help would be appreciated.
|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ezY3GWZaFHA.2076@.TK2MSFTNGP15.phx.gbl...
> There are a couple of ways you could do this but they depend on the reason
> why you need to do it. In short, you haven't supplied sufficient
> information for us to assist you.
> For instance, why not use an IDENTITY? Or, why not use a UUID?
i will use this id(bigint) to identify users, and users will use this uid to
login system. so uuid is not proper as user's id for logining the system.
it's too long!

> How long do they need to be unique? How frequently will you need them?
All
> at once or only one at a time?
>
i need it in this case:
once user fill his/her information and submit the information into the
server-side, the server will write his/her information into a table(called
"u_account", assuming it has field uid(bigint, that must be unique), name,
email,etc) of db, and at the same time, the record of the table u_account
will get a userid(bigint) as users' id(naturally, it must be unique)
it would be great if db will generate a random number(bigint, from 10,000 to
10,000,000) and unique(not the same as the existing number existing in the
table u_account).
how?
thank you.

> Sincerely,
>
> Anthony Thomas
>
|||On Sun, 5 Jun 2005 15:35:29 +0800, wen wrote:
(snip)
>it would be great if db will generate a random number(bigint, from 10,000 to
>10,000,000) and unique(not the same as the existing number existing in the
>table u_account).
Hi wen,
Unless you expect to get so many users that the range of available
numbers will fill up for more than 10%, I'd say:
1. Use RAND() to get a random number in the desired range;
2. Use an IF EXISTS() query to check if that number is already in use;
3. Go back to step 1 if it is.
Not pretty, but it works, and it will be fast enough (provided some
index can be used for step 2), since more than 10 iterations will be
extremely rare.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

how to let sql server generate a random id of bigint field from 10000 to the MAX BIGINT?

all generated IDs must be unique.how?
any help would be appreciated.See the old posts:-
http://groups.google.co.in/group/comp.databases.ms-sqlserver/browse_thread/thread/d8daab96ffd68fce/dccbf13c18513f64?q=unique+random+number+in+sql+server&rnum=2&hl=en#dccbf13c18513f64
If you just need the unique number then define a identitiy property.
CREATE TABLE test(i bigint identity(10000,1), k varchar(1)
insert into test (k) values('h')
This will start inserting the value from 10001
Thanks
Hari
SQL Server MVP
"wen" <wenming_hu2002@.hotmail.com> wrote in message
news:%23YYMC%23WaFHA.2756@.tk2msftngp13.phx.gbl...
> all generated IDs must be unique.how?
> any help would be appreciated.
>|||There are a couple of ways you could do this but they depend on the reason
why you need to do it. In short, you haven't supplied sufficient
information for us to assist you.
For instance, why not use an IDENTITY? Or, why not use a UUID?
How long do they need to be unique? How frequently will you need them? All
at once or only one at a time?
Sincerely,
Anthony Thomas
"wen" <wenming_hu2002@.hotmail.com> wrote in message
news:%23YYMC%23WaFHA.2756@.tk2msftngp13.phx.gbl...
all generated IDs must be unique.how?
any help would be appreciated.|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ezY3GWZaFHA.2076@.TK2MSFTNGP15.phx.gbl...
> There are a couple of ways you could do this but they depend on the reason
> why you need to do it. In short, you haven't supplied sufficient
> information for us to assist you.
> For instance, why not use an IDENTITY? Or, why not use a UUID?
i will use this id(bigint) to identify users, and users will use this uid to
login system. so uuid is not proper as user's id for logining the system.
it's too long!
> How long do they need to be unique? How frequently will you need them?
All
> at once or only one at a time?
>
i need it in this case:
once user fill his/her information and submit the information into the
server-side, the server will write his/her information into a table(called
"u_account", assuming it has field uid(bigint, that must be unique), name,
email,etc) of db, and at the same time, the record of the table u_account
will get a userid(bigint) as users' id(naturally, it must be unique)
it would be great if db will generate a random number(bigint, from 10,000 to
10,000,000) and unique(not the same as the existing number existing in the
table u_account).
how?
thank you.
> Sincerely,
>
> Anthony Thomas
>|||On Sun, 5 Jun 2005 15:35:29 +0800, wen wrote:
(snip)
>it would be great if db will generate a random number(bigint, from 10,000 to
>10,000,000) and unique(not the same as the existing number existing in the
>table u_account).
Hi wen,
Unless you expect to get so many users that the range of available
numbers will fill up for more than 10%, I'd say:
1. Use RAND() to get a random number in the desired range;
2. Use an IF EXISTS() query to check if that number is already in use;
3. Go back to step 1 if it is.
Not pretty, but it works, and it will be fast enough (provided some
index can be used for step 2), since more than 10 iterations will be
extremely rare.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

how to let sql server generate a random id of bigint field from 10000 to the MAX BIGIN

all generated IDs must be unique.how?
any help would be appreciated.See the old posts:-
d8daab96ffd68fce/dccbf13c18513f64?q=unique+random+number+in+sql+server&rnum=2&hl=e
n#dccbf13c18513f64" target="_blank">http://groups.google.co.in/group/co...ccbf13c18513f64
If you just need the unique number then define a identitiy property.
CREATE TABLE test(i bigint identity(10000,1), k varchar(1)
insert into test (k) values('h')
This will start inserting the value from 10001
Thanks
Hari
SQL Server MVP
"wen" <wenming_hu2002@.hotmail.com> wrote in message
news:%23YYMC%23WaFHA.2756@.tk2msftngp13.phx.gbl...
> all generated IDs must be unique.how?
> any help would be appreciated.
>|||There are a couple of ways you could do this but they depend on the reason
why you need to do it. In short, you haven't supplied sufficient
information for us to assist you.
For instance, why not use an IDENTITY? Or, why not use a UUID?
How long do they need to be unique? How frequently will you need them? All
at once or only one at a time?
Sincerely,
Anthony Thomas
"wen" <wenming_hu2002@.hotmail.com> wrote in message
news:%23YYMC%23WaFHA.2756@.tk2msftngp13.phx.gbl...
all generated IDs must be unique.how?
any help would be appreciated.|||"Anthony Thomas" <ALThomas@.kc.rr.com> wrote in message
news:ezY3GWZaFHA.2076@.TK2MSFTNGP15.phx.gbl...
> There are a couple of ways you could do this but they depend on the reason
> why you need to do it. In short, you haven't supplied sufficient
> information for us to assist you.
> For instance, why not use an IDENTITY? Or, why not use a UUID?
i will use this id(bigint) to identify users, and users will use this uid to
login system. so uuid is not proper as user's id for logining the system.
it's too long!

> How long do they need to be unique? How frequently will you need them?
All
> at once or only one at a time?
>
i need it in this case:
once user fill his/her information and submit the information into the
server-side, the server will write his/her information into a table(called
"u_account", assuming it has field uid(bigint, that must be unique), name,
email,etc) of db, and at the same time, the record of the table u_account
will get a userid(bigint) as users' id(naturally, it must be unique)
it would be great if db will generate a random number(bigint, from 10,000 to
10,000,000) and unique(not the same as the existing number existing in the
table u_account).
how?
thank you.

> Sincerely,
>
> Anthony Thomas
>|||On Sun, 5 Jun 2005 15:35:29 +0800, wen wrote:
(snip)
>it would be great if db will generate a random number(bigint, from 10,000 t
o
>10,000,000) and unique(not the same as the existing number existing in the
>table u_account).
Hi wen,
Unless you expect to get so many users that the range of available
numbers will fill up for more than 10%, I'd say:
1. Use RAND() to get a random number in the desired range;
2. Use an IF EXISTS() query to check if that number is already in use;
3. Go back to step 1 if it is.
Not pretty, but it works, and it will be fast enough (provided some
index can be used for step 2), since more than 10 iterations will be
extremely rare.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to know the rows count of a Field

I use a matrix which datasource is come from a olap
How can I know the rows count of a Field?Hi,
you can use the CountRows function. You can find more information about this
function at BOL.
Bye!
"ad" wrote:
> I use a matrix which datasource is come from a olap
> How can I know the rows count of a Field?
>
>|||Thanks,
I have tried, but I found that CountRows can only count f group members
which contain itself,
But if I want to know the count of group members of another(Say I want to
know the rows count of a column group in a row group),
how can I do that?
"Javier Catala" <JavierCatala@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:74E94833-7F46-4D5C-882D-02754D9715FA@.microsoft.com...
> Hi,
> you can use the CountRows function. You can find more information about
this
> function at BOL.
> Bye!
> "ad" wrote:
> > I use a matrix which datasource is come from a olap
> > How can I know the rows count of a Field?
> >
> >
> >

How to know the Primary Key of a Table?

Hi,
I need a quey that gives me the Field(s) that form the Primary Key of the
Table. I can't find anything about it on google, does anybody knwo how to
achieve this?
Thanks a lot in advance,
Pieter
This exact question was posted here or in .programming yesterday.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need a quey that gives me the Field(s) that form the Primary Key of the
> Table. I can't find anything about it on google, does anybody knwo how to
> achieve this?
> Thanks a lot in advance,
> Pieter
>
|||Thanks, I found it!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> This exact question was posted here or in .programming yesterday.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
to
>
|||Justo nel ittle remark: When i entered a new row directly in the Enterprise
Manager: I got this as 'query': "sp_executesql;1"
is this normal? does it only occur in that situation?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> This exact question was posted here or in .programming yesterday.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
to
>
|||I never use EM do modify data in the tables (Aaron has a FAQ on this at www.aspfaq.com), but it is
possible that EM uses prepared SQL or similar to do modifications...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
> Justo nel ittle remark: When i entered a new row directly in the Enterprise
> Manager: I got this as 'query': "sp_executesql;1"
> is this normal? does it only occur in that situation?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> the
> to
>
|||Hi Tibor,
i'm testing your really promising solution, unfortunately, when I'm doing
updates or isnert with the SqlDataAdapter of .NET, I alsogot the
"sp_executesql;1" as the used query isntead of the sql string.
You don't have any idea how to get the original Sql-suery from that
sp_executesql?
Thanks a lot in advance,
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%232tNWf0RFHA.2448@.TK2MSFTNGP10.phx.gbl...
> I never use EM do modify data in the tables (Aaron has a FAQ on this at
www.aspfaq.com), but it is
> possible that EM uses prepared SQL or similar to do modifications...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
Enterprise[vbcol=seagreen]
in[vbcol=seagreen]
how
>
|||Hmm, the command has to be send at one point in time. Did you trace the whole session? I'm afraid
that I can't be of any more help, I'm more of an engine person and not a client tool person...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:uswywDYSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> Hi Tibor,
> i'm testing your really promising solution, unfortunately, when I'm doing
> updates or isnert with the SqlDataAdapter of .NET, I alsogot the
> "sp_executesql;1" as the used query isntead of the sql string.
> You don't have any idea how to get the original Sql-suery from that
> sp_executesql?
> Thanks a lot in advance,
> Pieter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%232tNWf0RFHA.2448@.TK2MSFTNGP10.phx.gbl...
> www.aspfaq.com), but it is
> news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
> Enterprise
> in
> how
>
|||And what about the fn_get_sql function? I found soem info about it, and I
guess it must be possible to do like the samething? but maybe this will give
me the exact SQL-string? If i put it in a trigger now, I get the code of the
Trigger, not the code of the SQL statement that fired thetrigger :-(
But apparently I should somehow be able to do it with the @.stmt_end and
@.stmt_start variables? Or am I wrong?
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O6mxNLbSFHA.3424@.TK2MSFTNGP10.phx.gbl...
> Hmm, the command has to be send at one point in time. Did you trace the
whole session? I'm afraid
> that I can't be of any more help, I'm more of an engine person and not a
client tool person...[vbcol=seagreen]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:uswywDYSFHA.1236@.TK2MSFTNGP14.phx.gbl...
doing[vbcol=seagreen]
in[vbcol=seagreen]
wrote[vbcol=seagreen]
of
>

How to know the Primary Key of a Table?

Hi,
I need a quey that gives me the Field(s) that form the Primary Key of the
Table. I can't find anything about it on google, does anybody knwo how to
achieve this?
Thanks a lot in advance,
PieterThis exact question was posted here or in .programming yesterday.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need a quey that gives me the Field(s) that form the Primary Key of the
> Table. I can't find anything about it on google, does anybody knwo how to
> achieve this?
> Thanks a lot in advance,
> Pieter
>|||Thanks, I found it!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> This exact question was posted here or in .programming yesterday.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
to[vbcol=seagreen]
>|||Justo nel ittle remark: When i entered a new row directly in the Enterprise
Manager: I got this as 'query': "sp_executesql;1"
is this normal? does it only occur in that situation?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> This exact question was posted here or in .programming yesterday.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
the[vbcol=seagreen]
to[vbcol=seagreen]
>|||I never use EM do modify data in the tables (Aaron has a FAQ on this at .com" target="_blank">www.aspfaq
.com), but it is
possible that EM uses prepared SQL or similar to do modifications...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.ph
x.gbl...
> Justo nel ittle remark: When i entered a new row directly in the Enterpris
e
> Manager: I got this as 'query': "sp_executesql;1"
> is this normal? does it only occur in that situation?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> the
> to
>|||Hi Tibor,
i'm testing your really promising solution, unfortunately, when I'm doing
updates or isnert with the SqlDataAdapter of .NET, I alsogot the
"sp_executesql;1" as the used query isntead of the sql string.
You don't have any idea how to get the original Sql-suery from that
sp_executesql?
Thanks a lot in advance,
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%232tNWf0RFHA.2448@.TK2MSFTNGP10.phx.gbl...
> I never use EM do modify data in the tables (Aaron has a FAQ on this at
www.aspfaq.com), but it is
> possible that EM uses prepared SQL or similar to do modifications...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
Enterprise[vbcol=seagreen]
in[vbcol=seagreen]
how[vbcol=seagreen]
>|||Hmm, the command has to be send at one point in time. Did you trace the whol
e session? I'm afraid
that I can't be of any more help, I'm more of an engine person and not a cli
ent tool person...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:uswywDYSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> Hi Tibor,
> i'm testing your really promising solution, unfortunately, when I'm doing
> updates or isnert with the SqlDataAdapter of .NET, I alsogot the
> "sp_executesql;1" as the used query isntead of the sql string.
> You don't have any idea how to get the original Sql-suery from that
> sp_executesql?
> Thanks a lot in advance,
> Pieter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:%232tNWf0RFHA.2448@.TK2MSFTNGP10.phx.gbl...
> www.aspfaq.com), but it is
> news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
> Enterprise
> in
> how
>|||And what about the fn_get_sql function? I found soem info about it, and I
guess it must be possible to do like the samething? but maybe this will give
me the exact SQL-string? If i put it in a trigger now, I get the code of the
Trigger, not the code of the SQL statement that fired thetrigger :-(
But apparently I should somehow be able to do it with the @.stmt_end and
@.stmt_start variables? Or am I wrong?
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O6mxNLbSFHA.3424@.TK2MSFTNGP10.phx.gbl...
> Hmm, the command has to be send at one point in time. Did you trace the
whole session? I'm afraid
> that I can't be of any more help, I'm more of an engine person and not a
client tool person...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:uswywDYSFHA.1236@.TK2MSFTNGP14.phx.gbl...
doing[vbcol=seagreen]
in[vbcol=seagreen]
wrote[vbcol=seagreen]
of[vbcol=seagreen]
>

How to know the Primary Key of a Table?

Hi,
I need a quey that gives me the Field(s) that form the Primary Key of the
Table. I can't find anything about it on google, does anybody knwo how to
achieve this?
Thanks a lot in advance,
PieterThis exact question was posted here or in .programming yesterday.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I need a quey that gives me the Field(s) that form the Primary Key of the
> Table. I can't find anything about it on google, does anybody knwo how to
> achieve this?
> Thanks a lot in advance,
> Pieter
>|||Thanks, I found it!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> This exact question was posted here or in .programming yesterday.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I need a quey that gives me the Field(s) that form the Primary Key of
the
> > Table. I can't find anything about it on google, does anybody knwo how
to
> > achieve this?
> >
> > Thanks a lot in advance,
> >
> > Pieter
> >
> >
>|||Justo nel ittle remark: When i entered a new row directly in the Enterprise
Manager: I got this as 'query': "sp_executesql;1"
is this normal? does it only occur in that situation?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> This exact question was posted here or in .programming yesterday.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I need a quey that gives me the Field(s) that form the Primary Key of
the
> > Table. I can't find anything about it on google, does anybody knwo how
to
> > achieve this?
> >
> > Thanks a lot in advance,
> >
> > Pieter
> >
> >
>|||I never use EM do modify data in the tables (Aaron has a FAQ on this at www.aspfaq.com), but it is
possible that EM uses prepared SQL or similar to do modifications...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
> Justo nel ittle remark: When i entered a new row directly in the Enterprise
> Manager: I got this as 'query': "sp_executesql;1"
> is this normal? does it only occur in that situation?
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
>> This exact question was posted here or in .programming yesterday.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> > Hi,
>> >
>> > I need a quey that gives me the Field(s) that form the Primary Key of
> the
>> > Table. I can't find anything about it on google, does anybody knwo how
> to
>> > achieve this?
>> >
>> > Thanks a lot in advance,
>> >
>> > Pieter
>> >
>> >
>>
>|||Hi Tibor,
i'm testing your really promising solution, unfortunately, when I'm doing
updates or isnert with the SqlDataAdapter of .NET, I alsogot the
"sp_executesql;1" as the used query isntead of the sql string.
You don't have any idea how to get the original Sql-suery from that
sp_executesql?
Thanks a lot in advance,
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%232tNWf0RFHA.2448@.TK2MSFTNGP10.phx.gbl...
> I never use EM do modify data in the tables (Aaron has a FAQ on this at
www.aspfaq.com), but it is
> possible that EM uses prepared SQL or similar to do modifications...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
> > Justo nel ittle remark: When i entered a new row directly in the
Enterprise
> > Manager: I got this as 'query': "sp_executesql;1"
> > is this normal? does it only occur in that situation?
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> >> This exact question was posted here or in .programming yesterday.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> >> > Hi,
> >> >
> >> > I need a quey that gives me the Field(s) that form the Primary Key of
> > the
> >> > Table. I can't find anything about it on google, does anybody knwo
how
> > to
> >> > achieve this?
> >> >
> >> > Thanks a lot in advance,
> >> >
> >> > Pieter
> >> >
> >> >
> >>
> >>
> >
> >
>|||Hmm, the command has to be send at one point in time. Did you trace the whole session? I'm afraid
that I can't be of any more help, I'm more of an engine person and not a client tool person...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:uswywDYSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> Hi Tibor,
> i'm testing your really promising solution, unfortunately, when I'm doing
> updates or isnert with the SqlDataAdapter of .NET, I alsogot the
> "sp_executesql;1" as the used query isntead of the sql string.
> You don't have any idea how to get the original Sql-suery from that
> sp_executesql?
> Thanks a lot in advance,
> Pieter
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%232tNWf0RFHA.2448@.TK2MSFTNGP10.phx.gbl...
>> I never use EM do modify data in the tables (Aaron has a FAQ on this at
> www.aspfaq.com), but it is
>> possible that EM uses prepared SQL or similar to do modifications...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
>> > Justo nel ittle remark: When i entered a new row directly in the
> Enterprise
>> > Manager: I got this as 'query': "sp_executesql;1"
>> > is this normal? does it only occur in that situation?
>> >
>> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>> > message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
>> >> This exact question was posted here or in .programming yesterday.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >>
>> >>
>> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
>> >> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> >> > Hi,
>> >> >
>> >> > I need a quey that gives me the Field(s) that form the Primary Key of
>> > the
>> >> > Table. I can't find anything about it on google, does anybody knwo
> how
>> > to
>> >> > achieve this?
>> >> >
>> >> > Thanks a lot in advance,
>> >> >
>> >> > Pieter
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>|||And what about the fn_get_sql function? I found soem info about it, and I
guess it must be possible to do like the samething? but maybe this will give
me the exact SQL-string? If i put it in a trigger now, I get the code of the
Trigger, not the code of the SQL statement that fired thetrigger :-(
But apparently I should somehow be able to do it with the @.stmt_end and
@.stmt_start variables? Or am I wrong?
Pieter
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O6mxNLbSFHA.3424@.TK2MSFTNGP10.phx.gbl...
> Hmm, the command has to be send at one point in time. Did you trace the
whole session? I'm afraid
> that I can't be of any more help, I'm more of an engine person and not a
client tool person...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:uswywDYSFHA.1236@.TK2MSFTNGP14.phx.gbl...
> > Hi Tibor,
> >
> > i'm testing your really promising solution, unfortunately, when I'm
doing
> > updates or isnert with the SqlDataAdapter of .NET, I alsogot the
> > "sp_executesql;1" as the used query isntead of the sql string.
> >
> > You don't have any idea how to get the original Sql-suery from that
> > sp_executesql?
> >
> > Thanks a lot in advance,
> >
> > Pieter
> >
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:%232tNWf0RFHA.2448@.TK2MSFTNGP10.phx.gbl...
> >> I never use EM do modify data in the tables (Aaron has a FAQ on this at
> > www.aspfaq.com), but it is
> >> possible that EM uses prepared SQL or similar to do modifications...
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> > news:u2N$AQ0RFHA.356@.TK2MSFTNGP14.phx.gbl...
> >> > Justo nel ittle remark: When i entered a new row directly in the
> > Enterprise
> >> > Manager: I got this as 'query': "sp_executesql;1"
> >> > is this normal? does it only occur in that situation?
> >> >
> >> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
wrote
> > in
> >> > message news:ekpn%231wRFHA.904@.tk2msftngp13.phx.gbl...
> >> >> This exact question was posted here or in .programming yesterday.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >>
> >> >>
> >> >> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> >> >> news:O6YXAqwRFHA.1392@.TK2MSFTNGP10.phx.gbl...
> >> >> > Hi,
> >> >> >
> >> >> > I need a quey that gives me the Field(s) that form the Primary Key
of
> >> > the
> >> >> > Table. I can't find anything about it on google, does anybody knwo
> > how
> >> > to
> >> >> > achieve this?
> >> >> >
> >> >> > Thanks a lot in advance,
> >> >> >
> >> >> > Pieter
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

How to know if my varchar is only alphanumeric

Hi all
I would like to know in a select if my varchar field contains only
alphabetic caracter '
Is there a Function that can tell me this '
Thank
NicWell..you can use the IsNumeric() function like so
Select F1...Fn
From TableName
Where IsNumeric(F1) = 0
However, IsNumeric can sometimes return funky results. For example,
IsNumeric('.') returns 1.
Another way would be:
Select F1...Fn
From TableName
Where F1 Like '[^0-9]'
This expression finds results that do not contain the characters 0...9. The
downside to this approach is that a value of '02A' for example would also be
excluded even though it is not a number per se.
HTH
Thomas|||You could write one...
Create FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
RETURNS TinyInt
AS
BEGIN
Declare @.HasNonAlpha TinyInt
Declare @.A TinyInt
While Len(@.Value) > 0 Begin
Set @.A = Ascii(@.Value)
Set @.Value = Substring(@.Value, 2, Len(@.Value))
If @.A Not Between Ascii('a') And Ascii('z')
And @.A Not Between Ascii('A') And Ascii('Z')
And @.A <> Ascii(' ') Return 0
End
Return 1
RETURN @.HasNonAlpha
END
"Nicolas Veilleux" wrote:

> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
>|||Refactored...
CREATE FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
RETURNS TinyInt
AS
BEGIN
Declare @.A TinyInt
While Len(@.Value) > 0 Begin
Set @.A = Ascii(@.Value)
Set @.Value = Substring(@.Value, 2, Len(@.Value))
If @.A Not Between Ascii('a') And Ascii('z')
And @.A Not Between Ascii('A') And Ascii('Z')
And @.A <> Ascii(' ') Return 0
End
Return 1
End
"CBretana" wrote:
> You could write one...
> Create FUNCTION dbo.IsAlpha (@.Value VarChar(7000) )
> RETURNS TinyInt
> AS
> BEGIN
> Declare @.HasNonAlpha TinyInt
> Declare @.A TinyInt
> While Len(@.Value) > 0 Begin
> Set @.A = Ascii(@.Value)
> Set @.Value = Substring(@.Value, 2, Len(@.Value))
> If @.A Not Between Ascii('a') And Ascii('z')
> And @.A Not Between Ascii('A') And Ascii('Z')
> And @.A <> Ascii(' ') Return 0
> End
> Return 1
>
> RETURN @.HasNonAlpha
> END
> "Nicolas Veilleux" wrote:
>|||"Nicolas Veilleux" <nveilleux@.nbautomation.com> wrote in message
news:u9$bVx3RFHA.3120@.TK2MSFTNGP10.phx.gbl...
> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
SELECT
CASE PATINDEX('%[^a-z ]%','This string contains the number 1')
WHEN 0 THEN 'Alphabetic'
ELSE 'Non-alphabetic'
END|||How about :
SELECT PATINDEX('%[^A-Z]%', 'ab1c'), PATINDEX('%[^A-Z]%', 'abc')
If PATINDEX returns 0 then the string contains a non-alpha character.
Might need to be modified for case-sensitive collations.
- KH
"Nicolas Veilleux" wrote:

> Hi all
> I would like to know in a select if my varchar field contains only
> alphabetic caracter '
> Is there a Function that can tell me this '
> Thank
> Nic
>
>|||Sorry - if PATINDEX returns *NON-ZERO* then the string contains a non-alpha
character.
"KH" wrote:
> How about :
> SELECT PATINDEX('%[^A-Z]%', 'ab1c'), PATINDEX('%[^A-Z]%', 'abc')
> If PATINDEX returns 0 then the string contains a non-alpha character.
> Might need to be modified for case-sensitive collations.
> - KH
>
> "Nicolas Veilleux" wrote:
>|||First of all, columns are not fields; learn to think correctly.
CHECK
( LEN
(REPLACE (
REPLACE (
. REPLACE (UPPER(foobar), 'A', '')
.
'Z', '') = LEN(foobar))
Basically, uppercase the string, use nested REPLACE() functions to turn
the alphas into striings and check the length. Do not create a
function with a WHILE loop or other procedural stuff in SQL. That
misses the whole point of a declarative language.
SQL Server has a PATINDEX() which can also be used; I am not sure which
is faster. The REPLACE () is a scan in main storage, but PATINDEX()
has to build a finite state machine.