PATINDEX to Retrieve data from text field

I am trying to retrieve the data from a table that has text datatype. I just
need to
pull 5 digit numeric value from there which can later be matched with
another table
that has this 5 digit key. Let us assume the table name is Tab1. There are
two
columns col1 containing RecordId and col2 containing text data. I have
created some dummy data to explain my needs:
Col1 Col2
1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC INC.@.SOMETHING
1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF INC.@.SOMETHING
1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
1007 @.:92A:CUST8@.ANYTHING
If I use the following Query which needs to be tuned up to get the right
resultset:
SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
from tab1
where PATINDEX('%@.:97_:%', col2) > 0
I get the following results: The top two results are correct but others are
not.
col1 col2
-- --
1001 11221
1002 11229
1003 1233@.
1004 333@.C
1005 IENT
1006 AME I
I need the following resultset from the above data:
Col1 Col2
-- --
1001 11221
1002 11229
1003 11233
1004 11333
1005 22333
Col1 Id 1006 does not have the 5 digit numeric value so it is not required
in the
resultset. Id 1007 does not have :97_C: so this is also not required in the
resultset
too. I will appreciate your help. Thanks in advance. Fraz
Fraz
Look at this example helps you
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
drop table sometable
drop function dbo.CleanChars
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> I am trying to retrieve the data from a table that has text datatype. I
just
> need to
> pull 5 digit numeric value from there which can later be matched with
> another table
> that has this 5 digit key. Let us assume the table name is Tab1. There are
> two
> columns col1 containing RecordId and col2 containing text data. I have
> created some dummy data to explain my needs:
> Col1 Col2
> 1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING
> 1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING
> 1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
> 1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
> 1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
> 1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
> 1007 @.:92A:CUST8@.ANYTHING
> If I use the following Query which needs to be tuned up to get the right
> resultset:
> SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
> from tab1
> where PATINDEX('%@.:97_:%', col2) > 0
> I get the following results: The top two results are correct but others
are
> not.
> col1 col2
> -- --
> 1001 11221
> 1002 11229
> 1003 1233@.
> 1004 333@.C
> 1005 IENT
> 1006 AME I
> I need the following resultset from the above data:
> Col1 Col2
> -- --
> 1001 11221
> 1002 11229
> 1003 11233
> 1004 11333
> 1005 22333
> Col1 Id 1006 does not have the 5 digit numeric value so it is not required
> in the
> resultset. Id 1007 does not have :97_C: so this is also not required in
the
> resultset
> too. I will appreciate your help. Thanks in advance. Fraz
|||Uri: Thanks for your valuable input. This is a nice function which I am
trying to see if it can fit in my needs. If you could help little more by
showing how I can check to see the 5 digit numbers (11233) between this data
@.:97B:/XX022511233@.CLIENT. The position is always not the same. So by getting
@.:97_: we can get first position and by next "@." we can get second position.
Now I know that my data is in between first and second position and by using
RIGHT function I can get the 5 right digits. Thanks again...Fraz
"Uri Dimant" wrote:

> Fraz
> Look at this example helps you
> CREATE FUNCTION dbo.CleanChars
> (@.str VARCHAR(8000), @.validchars VARCHAR(8000))
> RETURNS VARCHAR(8000)
> BEGIN
> WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
> SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
> ']%',@.str), 1) ,'')
> RETURN @.str
> END
> GO
> CREATE TABLE sometable
> (namestr VARCHAR(20) PRIMARY KEY)
> INSERT INTO sometable VALUES ('AB-C123')
> INSERT INTO sometable VALUES ('A,B,C')
> SELECT namestr,
> dbo.CleanChars(namestr,'A-Z 0-9')
> FROM sometable
>
> drop table sometable
> drop function dbo.CleanChars
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> just
> INC.@.SOMETHING
> INC.@.SOMETHING
> are
> the
>
>
|||Hi Fraz,
On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
(snip)
>I need the following resultset from the above data:
>Col1 Col2
>-- --
>1001 11221
>1002 11229
>1003 11233
>1004 11333
>1005 22333
(snip)
Try the following (note: I added a test case to check that I return the
five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
first five digits followed by "@.").
-- Set up test table and fill it with some rows
create table tab1 (col1 int not null primary key, col2 varchar(200))
go
insert into tab1
select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING'
union all
select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING'
union all
select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
union all
select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
INC.@.ANYTHING'
union all
select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
union all
select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
union all
select 1007, '@.:92A:CUST8@.ANYTHING'
union all
select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
go
-- Here's the code:
SELECT col1,
SUBSTRING(col2,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
SUBSTRING(col2,
PATINDEX('%@.:97_:%', col2),
LEN(col2)))
+ PATINDEX('%@.:97_:%', col2)
- 1,
5)
FROM tab1
WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
go
-- Done. Now cleanup.
drop table tab1
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hello Hugo,
Your code has worked excellently. Most of the 5 digit numbers were correct
except for a few records that were very long and numbers were not correct. I
have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
"Hugo Kornelis" wrote:

> Hi Fraz,
> On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
> (snip)
> (snip)
> Try the following (note: I added a test case to check that I return the
> five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
> first five digits followed by "@.").
> -- Set up test table and fill it with some rows
> create table tab1 (col1 int not null primary key, col2 varchar(200))
> go
> insert into tab1
> select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
> INC.@.SOMETHING'
> union all
> select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
> INC.@.SOMETHING'
> union all
> select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> union all
> select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
> INC.@.ANYTHING'
> union all
> select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
> union all
> select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
> union all
> select 1007, '@.:92A:CUST8@.ANYTHING'
> union all
> select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> go
> -- Here's the code:
> SELECT col1,
> SUBSTRING(col2,
> PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
> SUBSTRING(col2,
> PATINDEX('%@.:97_:%', col2),
> LEN(col2)))
> + PATINDEX('%@.:97_:%', col2)
> - 1,
> 5)
> FROM tab1
> WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
> go
>
> -- Done. Now cleanup.
> drop table tab1
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Fri, 22 Apr 2005 14:21:03 -0700, Fraz wrote:

>Hello Hugo,
>Your code has worked excellently. Most of the 5 digit numbers were correct
>except for a few records that were very long and numbers were not correct. I
>have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
Hi Fraz,
Good to hear that it worked for you. Thanks for reporting back!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

PATINDEX to Retrieve data from text field

I am trying to retrieve the data from a table that has text datatype. I just
need to
pull 5 digit numeric value from there which can later be matched with
another table
that has this 5 digit key. Let us assume the table name is Tab1. There are
two
columns col1 containing RecordId and col2 containing text data. I have
created some dummy data to explain my needs:
Col1 Col2
1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC INC.@.SOMETHI
NG
1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF INC.@.SOMETHING
1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
1007 @.:92A:CUST8@.ANYTHING
If I use the following Query which needs to be tuned up to get the right
resultset:
SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
from tab1
where PATINDEX('%@.:97_:%', col2) > 0
I get the following results: The top two results are correct but others are
not.
col1 col2
-- --
1001 11221
1002 11229
1003 1233@.
1004 333@.C
1005 IENT
1006 AME I
I need the following resultset from the above data:
Col1 Col2
-- --
1001 11221
1002 11229
1003 11233
1004 11333
1005 22333
Col1 Id 1006 does not have the 5 digit numeric value so it is not required
in the
resultset. Id 1007 does not have :97_C: so this is also not required in the
resultset
too. I will appreciate your help. Thanks in advance. FrazFraz
Look at this example helps you
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
drop table sometable
drop function dbo.CleanChars
"Fraz" <Fraz@.discussions.microsoft.com> wrote in message
news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> I am trying to retrieve the data from a table that has text datatype. I
just
> need to
> pull 5 digit numeric value from there which can later be matched with
> another table
> that has this 5 digit key. Let us assume the table name is Tab1. There are
> two
> columns col1 containing RecordId and col2 containing text data. I have
> created some dummy data to explain my needs:
> Col1 Col2
> 1001 @.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING
> 1002 @.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING
> 1003 @.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI INC.@.ANYTHING
> 1004 @.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM INC.@.ANYTHING
> 1005 @.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING
> 1006 @.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING
> 1007 @.:92A:CUST8@.ANYTHING
> If I use the following Query which needs to be tuned up to get the right
> resultset:
> SELECT SUBSTRING(col2, PATINDEX('%@.:97_:%', col2)+14, 5)
> from tab1
> where PATINDEX('%@.:97_:%', col2) > 0
> I get the following results: The top two results are correct but others
are
> not.
> col1 col2
> -- --
> 1001 11221
> 1002 11229
> 1003 1233@.
> 1004 333@.C
> 1005 IENT
> 1006 AME I
> I need the following resultset from the above data:
> Col1 Col2
> -- --
> 1001 11221
> 1002 11229
> 1003 11233
> 1004 11333
> 1005 22333
> Col1 Id 1006 does not have the 5 digit numeric value so it is not required
> in the
> resultset. Id 1007 does not have :97_C: so this is also not required in
the
> resultset
> too. I will appreciate your help. Thanks in advance. Fraz|||Uri: Thanks for your valuable input. This is a nice function which I am
trying to see if it can fit in my needs. If you could help little more by
showing how I can check to see the 5 digit numbers (11233) between this data
@.:97B:/XX022511233@.CLIENT. The position is always not the same. So by gettin
g
@.:97_: we can get first position and by next "@." we can get second position.
Now I know that my data is in between first and second position and by using
RIGHT function I can get the 5 right digits. Thanks again...Fraz
"Uri Dimant" wrote:

> Fraz
> Look at this example helps you
> CREATE FUNCTION dbo.CleanChars
> (@.str VARCHAR(8000), @.validchars VARCHAR(8000))
> RETURNS VARCHAR(8000)
> BEGIN
> WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
> SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars
+
> ']%',@.str), 1) ,'')
> RETURN @.str
> END
> GO
> CREATE TABLE sometable
> (namestr VARCHAR(20) PRIMARY KEY)
> INSERT INTO sometable VALUES ('AB-C123')
> INSERT INTO sometable VALUES ('A,B,C')
> SELECT namestr,
> dbo.CleanChars(namestr,'A-Z 0-9')
> FROM sometable
>
> drop table sometable
> drop function dbo.CleanChars
> "Fraz" <Fraz@.discussions.microsoft.com> wrote in message
> news:B964C72E-D1A4-4906-A105-E1D87A2F29D6@.microsoft.com...
> just
> INC.@.SOMETHING
> INC.@.SOMETHING
> are
> the
>
>|||Hi Fraz,
On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
(snip)
>I need the following resultset from the above data:
>Col1 Col2
>-- --
>1001 11221
>1002 11229
>1003 11233
>1004 11333
>1005 22333
(snip)
Try the following (note: I added a test case to check that I return the
five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
first five digits followed by "@.").
-- Set up test table and fill it with some rows
create table tab1 (col1 int not null primary key, col2 varchar(200))
go
insert into tab1
select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
INC.@.SOMETHING'
union all
select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
INC.@.SOMETHING'
union all
select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
union all
select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
INC.@.ANYTHING'
union all
select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
union all
select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
union all
select 1007, '@.:92A:CUST8@.ANYTHING'
union all
select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
INC.@.ANYTHING'
go
-- Here's the code:
SELECT col1,
SUBSTRING(col2,
PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.%',
SUBSTRING(col2,
PATINDEX('%@.:97_:%', col2),
LEN(col2)))
+ PATINDEX('%@.:97_:%', col2)
- 1,
5)
FROM tab1
WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
go
-- Done. Now cleanup.
drop table tab1
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello Hugo,
Your code has worked excellently. Most of the 5 digit numbers were correct
except for a few records that were very long and numbers were not correct. I
have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
"Hugo Kornelis" wrote:

> Hi Fraz,
> On Thu, 14 Apr 2005 06:30:05 -0700, Fraz wrote:
> (snip)
> (snip)
> Try the following (note: I added a test case to check that I return the
> five digits preceding "@." AFTER the "@.:97_:" marker, not simply the
> first five digits followed by "@.").
> -- Set up test table and fill it with some rows
> create table tab1 (col1 int not null primary key, col2 varchar(200))
> go
> insert into tab1
> select 1001, '@.:92A:CUSTOMER1@.:97D://XX022211221@.CUSTOMER NAME IS ABC
> INC.@.SOMETHING'
> union all
> select 1002, '@.:92A:CUSTOMER@.:97A://XX022311229@.CLIENT NAME IS DEF
> INC.@.SOMETHING'
> union all
> select 1003, '@.:92A:CUST4@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> union all
> select 1004, '@.:92A:CUST8@.:97C:XX022311333@.CLIENT NAME IS LKM
> INC.@.ANYTHING'
> union all
> select 1005, '@.:92A:CUST8@.:97D:22333@.CLIENT NAME IS NOP INC.@.SOMETHING'
> union all
> select 1006, '@.:92A:CUST8@.:97C:CLIENT NAME IS QRS INC.@.ANYTHING'
> union all
> select 1007, '@.:92A:CUST8@.ANYTHING'
> union all
> select 1008, '@.:92A:CUST44444@.:97B:/XX022511233@.CLIENT NAME IS GHI
> INC.@.ANYTHING'
> go
> -- Here's the code:
> SELECT col1,
> SUBSTRING(col2,
> PATINDEX('%[0-9][0-9][0-9][0-9][0-9]@.
%',
> SUBSTRING(col2,
> PATINDEX('%@.:97_:%', col2),
> LEN(col2)))
> + PATINDEX('%@.:97_:%', col2)
> - 1,
> 5)
> FROM tab1
> WHERE col2 LIKE '%@.:97_:%[0-9][0-9][0-9][0-9][0-9]@.%'
> go
>
> -- Done. Now cleanup.
> drop table tab1
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Fri, 22 Apr 2005 14:21:03 -0700, Fraz wrote:

>Hello Hugo,
>Your code has worked excellently. Most of the 5 digit numbers were correct
>except for a few records that were very long and numbers were not correct.
I
>have dealt with it separately. Thanks a lot for your help. Cheers... Fraz
Hi Fraz,
Good to hear that it worked for you. Thanks for reporting back!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Passwords for the logins

Hi All,
Sql server 7
There are set of logins in sql server some are NT authentication and some are sql server authentication. How can i retrieve the passwords for both these logins.
TIA
AdilYou must be kind of foggy on the concept. They are passwords. They are supposed to be secret, and SQL Server keeps them that way.

If you have sufficient rights you can reset the passwords to whatever you want.

Passwords for the logins

Hi All,
Sql server 7
There are set of logins in sql server some are NT
authentication and some are sql server
authentication. How can i retrieve the passwords for both
these logins.
TIA
AdilHaseeb,
the nearest you can do is to extract the password in encrypted format for
SQL logins (only):
SELECT CONVERT(VARBINARY(32), password)
FROM syslogins
WHERE name = 'Margaret'
This can be useful to transfer logins from one server to another.
HTH,
Paul Ibison|||Hi,
You can not retrive the passwords of users in clear text. Only way is to
change the password.
For OS level. You have to change in OS level users password.
For SQL Srever, use enterprise manager or sp_password command from query
analyzer to change the password.
Thanks
Hari
MCDBA
"haseeb" <anonymous@.discussions.microsoft.com> wrote in message
news:16a3b01c447f9$9a5e0be0$a101280a@.phx
.gbl...
> Hi All,
> Sql server 7
> There are set of logins in sql server some are NT
> authentication and some are sql server
> authentication. How can i retrieve the passwords for both
> these logins.
>
> TIA
> Adil|||You can't.
The NT logins are the domain user accounts.
The SQL logins are ones that were created by an sql admin. Probably used by
an specific application. You could back track to the app and get the
password.
"haseeb" <anonymous@.discussions.microsoft.com> wrote in message
news:16a3b01c447f9$9a5e0be0$a101280a@.phx
.gbl...
> Hi All,
> Sql server 7
> There are set of logins in sql server some are NT
> authentication and some are sql server
> authentication. How can i retrieve the passwords for both
> these logins.
>
> TIA
> Adil

password

I have lost my password for my mssqlserver can someone help me retrieve it or
should I delete this server and download another.
On Wed, 26 Oct 2005 12:20:02 -0700, RonP wrote:

>I have lost my password for my mssqlserver can someone help me retrieve it or
>should I delete this server and download another.
Hi Ron,
If you are not the DBA, then ask the DBA to reset your password.
If you are the DBA, then log on to the computer with the Administrator
account, then connect to SQL Server using windows authentication (i.e.
you don't specify a SQL Server userid and password; SQL Server relies on
Wondows' authentication that you are indeed "Administrator"). You can
now use sp_password to change the password for your regular account.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

passing tablename as parameter to function and to use it dynamically

Hi,

How do I run dynamic sql statements in side a UDF?
Is there any work around to retrieve data that way?

Example:
-- Table
create table dataTbl
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

create table dataTbl2
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

--Populate data
insert into dataTbl values ('x','y','z')
insert into dataTbl values ('a','1','2')
insert into dataTbl values ('e','3','4')
insert into dataTbl values ('h','6','7')

insert into dataTbl2 values ('x','m','n')
insert into dataTbl2 values ('a','k','l')
insert into dataTbl2 values ('e','u','o')
insert into dataTbl2 values ('h','t','y')

-- function

Create function testFun(@.colname varchar(10),@.tblName varchar(10))
returns varchar(10)
as
Begin
declare @.x varchar(10)
select @.x=col2 from dataTbl where col1='a'
return @.x
end

-- calling the function
select dbo.testFun('x','dataTbl')
select dbo.testFun('x','dataTbl2')

How can I achive this objective?

Quote:

Originally Posted by satish@.entech.us

Hi,

How do I run dynamic sql statements in side a UDF?
Is there any work around to retrieve data that way?

Example:
-- Table
create table dataTbl
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

create table dataTbl2
(col1 varchar(5),col2 varchar(5),col3 varchar(5))

--Populate data
insert into dataTbl values ('x','y','z')
insert into dataTbl values ('a','1','2')
insert into dataTbl values ('e','3','4')
insert into dataTbl values ('h','6','7')

insert into dataTbl2 values ('x','m','n')
insert into dataTbl2 values ('a','k','l')
insert into dataTbl2 values ('e','u','o')
insert into dataTbl2 values ('h','t','y')

-- function

Create function testFun(@.colname varchar(10),@.tblName varchar(10))
returns varchar(10)
as
Begin
declare @.x varchar(10)
select @.x=col2 from dataTbl where col1='a'
return @.x
end

-- calling the function
select dbo.testFun('x','dataTbl')
select dbo.testFun('x','dataTbl2')

How can I achive this objective?

I don't think you can do this because DynamicSQL has it's own scope. Which means it won't return anything to the function. It will just run. So you can't do

SET @.Return = EXEC @.Command

As they are two different scopes.

What you could do is store the results of the output to a temp table and interrogate that when the function completes.

Cheers
C