How to format a string when pulling from a Dataitem

I seem to be having a problem in getting a string formatted correctly that I'm pulling from a SQL Datasource.

I'm trying to format a Datalist, and more specifically, a Paypal "Add to Cart" button that's supposed to pass several values to another page. The problem I have is with passing the price amount. All the data is in a SQL Express database and the price field is in the database as "money". However it outputs the dollar amount with too many trailing zeros, for example $6.00 is formatted as 6.0000. I'm trying to pass it into the form variable with this code:

<input type="hidden" name="amount" value="<%# DataBinder.eval(Container.dataitem, "Price", "{0}" %>"
Unfortunately, that tends to make include the trailing zeros and mess up the ability to pass the variable. Can anyone help me with how to format this variable so there's no trailing zeros, just so that $6.00 comes out as 6.00 and not 6.0000?

Thanks

Try this and let me know

<%# FormatCurrency(Container.dataitem("Price"), 2) %>

HTH

Regards

|||That looks like it works beautifully.

Thanks so much.

GZR

How to fix DT_Text and DT_NText Read Only in Script Component

I have a script component that I have written and works as long as the Output columns on the script are string types. When I change the output column type to text (since the size could be essentially unlimited) it gives an error in the script component that the property is read only.

Here is the code line that fails with Property Payments is read only.

Output0Buffer.Payments = fieldValues(i)

If I change the column payments to DT_Wstr it works without issue, but I want to use text incase the value is large.

Here is the error if you try to run the actual script even though I know it has an error.

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [Script Component [85]]: Error 30526: Property 'Payments' is 'ReadOnly'.
Line 86 Column 13 through 69
Error 30526: Property 'Ops' is 'ReadOnly'.
Line 155 Column 13 through 65

Error at Data Flow Task [Script Component [85]]: Error 30526: Property 'Payments' is 'ReadOnly'.
Line 86 Column 13 through 69
Error 30526: Property 'Ops' is 'ReadOnly'.
Line 155 Column 13 through 65

Error at Data Flow Task [DTS.Pipeline]: "component "Script Component" (85)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

Try explicitly calling SetString() on the column. Any better?

Thanks
Mark

|||First things first, make sure the input column usage type is set to Read/Write.

The reason it doesn't work is that Blob data types have a different interface in pipline script components.

Then, to set to value to a text field (DT_TEXT or DT_NTEXT) in a pipeline script component, use AddBlobData(), as in:

Imports System.Text
...

Output0Buffer.Payments.AddBlobData(Encoding.Unicode.GetBytes(SomeStringHere))|||I had just figured it out before this post.. but my code was way worse... yours works well and is clean. Thanks!

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName = (
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName = (
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
---
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName = (
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName = (
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
---
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?
JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>
|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =
null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

How to find strings in multiple tables

I have a database in SQL has 250 tables. I want to find out all the tables
which have certain string in it for example "computer1". Is there an easier
way to do this task rather than going into each table do a search?JL
This script has written by Narayana Vyas Kondreddi. Also I suggest to visit
his site (http://vyaskn.tripod.com )when you find a lot of examples and
useful scripts.
CREATE PROC SearchAllTables
(
@.SearchStr nvarchar(100)
)
AS
BEGIN
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue
nvarchar(3630))
SET NOCOUNT ON
DECLARE @.TableName nvarchar(256), @.ColumnName nvarchar(128), @.SearchStr2
nvarchar(110)
SET @.TableName = ''
SET @.SearchStr2 = QUOTENAME('%' + @.SearchStr + '%','''')
WHILE @.TableName IS NOT NULL
BEGIN
SET @.ColumnName = ''
SET @.TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @.TableName
AND OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@.TableName IS NOT NULL) AND (@.ColumnName IS NOT NULL)
BEGIN
SET @.ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@.TableName, 2)
AND TABLE_NAME = PARSENAME(@.TableName, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @.ColumnName
)
IF @.ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @.TableName + '.' + @.ColumnName + ''', LEFT(' +
@.ColumnName + ', 3630)
FROM ' + @.TableName + ' (NOLOCK) ' +
' WHERE ' + @.ColumnName + ' LIKE ' + @.SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>|||Hi,
Have a look into the below code, I got this procedure from previous post.
This code will generate the Select statement for all the
Char, varchar, nvarchar,nchar columns for all the tables searching for the
string you are inputting.
All you have to do is just cut and paste the result of this procedure to a
query window and execute.
---
drop procedure ColSearch
go
create procedure ColSearch @.instr varchar(255), @.tablename varchar(255) =
null
as
create table #colsearch (colid int identity (1,1),
colname varchar(255),
tablename varchar(255))
if @.tablename is not null
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and so.name = @.tablename
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
else
begin
insert #colsearch (colname, tablename)
select sc.name, so.name
from sysobjects so inner join syscolumns sc
on so.id = sc.id
where so.type = 'u'
and type_name(sc.xusertype) in ('varchar', 'char', 'nvarchar', 'nchar')
and datalength(@.instr) <= sc.length
order by so.name, sc.colid
end
select 'select '+rtrim(colname)+' from '+rtrim(tablename)+' where
'+colname+' like ''%'+@.instr+'%'''
from #colsearch
order by colid
Thanks
Hari
MCDBA
"JL" <ljmagzine@.hotmail.com> wrote in message
news:OuCRClDTEHA.3872@.TK2MSFTNGP10.phx.gbl...
> I have a database in SQL has 250 tables. I want to find out all the tables
> which have certain string in it for example "computer1". Is there an
easier
> way to do this task rather than going into each table do a search?
>

How to find record with xml data containing value...

Hello all,
SQL2005
I am newbe in XPath and XQuery
I have to find in table with xml field all records where value of attribute
contains string (e.g person name)
drop table dbo.bbb;
create table dbo.bbb(
p1 int not null identity,
p2 xml,
primary key(p1) );
insert into dbo.bbb values
('<root><item werte="d1"/><item werte="a2"/></root>');
insert into dbo.bbb values
('<root><item werte="b1"/><item werte="b2"/></root>');
select *
from dbo.bbb
where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
this query give me records with @.werte containing "a" but only if it is in
first item,
I have to find records with @.werte containing "a" regardless of item
position.
Can anybody help me write this query?
Regards
YaroOK, I know
select *
from dbo.bbb
where
p2.exist('/root/item/@.werte[contains(.,"a")]') = 1
Yaro
Uytkownik "Yaro" <yarok_delthisdes_@.op.pl> napisa w wiadomoci
news:e12vcm$nq4$1@.83.238.170.160...
> Hello all,
> SQL2005
> I am newbe in XPath and XQuery
> I have to find in table with xml field all records where value of
> attribute contains string (e.g person name)
> drop table dbo.bbb;
> create table dbo.bbb(
> p1 int not null identity,
> p2 xml,
> primary key(p1) );
> insert into dbo.bbb values
> ('<root><item werte="d1"/><item werte="a2"/></root>');
> insert into dbo.bbb values
> ('<root><item werte="b1"/><item werte="b2"/></root>');
> select *
> from dbo.bbb
> where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
> this query give me records with @.werte containing "a" but only if it is
> in first item,
> I have to find records with @.werte containing "a" regardless of item
> position.
> Can anybody help me write this query?
> Regards
> Yaro

How to find record with xml data containing value...

Hello all,
SQL2005
I am newbe in XPath and XQuery
I have to find in table with xml field all records where value of attribute
contains string (e.g person name)
drop table dbo.bbb;
create table dbo.bbb(
p1 int not null identity,
p2 xml,
primary key(p1) );
insert into dbo.bbb values
('<root><item werte="d1"/><item werte="a2"/></root>');
insert into dbo.bbb values
('<root><item werte="b1"/><item werte="b2"/></root>');
select *
from dbo.bbb
where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
this query give me records with @.werte containing "a" but only if it is in
first item,
I have to find records with @.werte containing "a" regardless of item
position.
Can anybody help me write this query?
Regards
Yaro
OK, I know
select *
from dbo.bbb
where
p2.exist('/root/item/@.werte[contains(.,"a")]') = 1
Yaro
Uytkownik "Yaro" <yarok_delthisdes_@.op.pl> napisa w wiadomoci
news:e12vcm$nq4$1@.83.238.170.160...
> Hello all,
> SQL2005
> I am newbe in XPath and XQuery
> I have to find in table with xml field all records where value of
> attribute contains string (e.g person name)
> drop table dbo.bbb;
> create table dbo.bbb(
> p1 int not null identity,
> p2 xml,
> primary key(p1) );
> insert into dbo.bbb values
> ('<root><item werte="d1"/><item werte="a2"/></root>');
> insert into dbo.bbb values
> ('<root><item werte="b1"/><item werte="b2"/></root>');
> select *
> from dbo.bbb
> where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
> this query give me records with @.werte containing "a" but only if it is
> in first item,
> I have to find records with @.werte containing "a" regardless of item
> position.
> Can anybody help me write this query?
> Regards
> Yaro

how to find out MSSQL connection string?

how to find out MSSQL connection string?
Thx
YuriWHi Yuri,
Can you give us some more information on what it is you want
to know? Are you wanting to know how to build a connection
string for an application, for ADO to connect to SQL Server?
Or do you want to know how to tell how an application or
user is connecting to SQL Server? Or something else?
-Sue
On Wed, 29 Oct 2003 21:27:21 GMT, "Yuri Weinstein"
<yuriw@.hotmail.com> wrote:
>how to find out MSSQL connection string?
>Thx
>YuriW
>|||What connection string you should use to connect to a SQL
Server instance depends on the driver you use.
For instance, if you use the Microsoft SQL Server ODBC
driver, the connection string is documented under the
SQLDriverConnect() function.
Linchi
>--Original Message--
>how to find out MSSQL connection string?
>Thx
>YuriW
>
>.
>|||Say I have a ODBC connection that tests OK.
I want to know what connection string it uses when making a connection...
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:leg0qvgb6hbone5eu8cg13df9mq1ddech2@.4ax.com...
> Hi Yuri,
> Can you give us some more information on what it is you want
> to know? Are you wanting to know how to build a connection
> string for an application, for ADO to connect to SQL Server?
> Or do you want to know how to tell how an application or
> user is connecting to SQL Server? Or something else?
> -Sue
> On Wed, 29 Oct 2003 21:27:21 GMT, "Yuri Weinstein"
> <yuriw@.hotmail.com> wrote:
> >how to find out MSSQL connection string?
> >
> >Thx
> >
> >YuriW
> >
>|||It depends on what you entered for the options but it would
look something like the SQL Server ODBC examples at this
site (watch for line wrap on the link):
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm#ODBCDriverForSQLServer
Most of the entries correspond to what you see in the ODBC
data source administrator applet except for dialog you get
for authentication. If you select Windows NT Authentication
Using Network Login ID, that will use a connection string
with Trusted_connection=Yes.
If you select SQL Server authentication, that will use a
connection string with UID=YourLogin and PWD=YourPassword.
-Sue
On Wed, 29 Oct 2003 23:30:38 GMT, "Yuri Weinstein"
<yuriw@.hotmail.com> wrote:
>Say I have a ODBC connection that tests OK.
>I want to know what connection string it uses when making a connection...
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:leg0qvgb6hbone5eu8cg13df9mq1ddech2@.4ax.com...
>> Hi Yuri,
>> Can you give us some more information on what it is you want
>> to know? Are you wanting to know how to build a connection
>> string for an application, for ADO to connect to SQL Server?
>> Or do you want to know how to tell how an application or
>> user is connecting to SQL Server? Or something else?
>> -Sue
>> On Wed, 29 Oct 2003 21:27:21 GMT, "Yuri Weinstein"
>> <yuriw@.hotmail.com> wrote:
>> >how to find out MSSQL connection string?
>> >
>> >Thx
>> >
>> >YuriW
>> >
>

How to find image name in URL?

If I have a string field with a URL, how can I pull out the image name?
Here are some examples and what I'd like returned:
http://somewebsite.com/myimage.gif
Return "image.gif"
http://somewebsite.com
Return "somewebsite.com"
http://somewebsite.com/89sdmksdfds9...kkkl.sldkfj.gif
Return "89sdmksdfds990s0s0s0kkkl.sldkfj.gif" (notice there are two dots
here)
http://somewebsite.com/89sdmksdfds990s0s0s0kkkl
Return "89sdmksdfds990s0s0s0kkkl"
Thanks,
BrettUse the RIGHT function to extract the right most portion of a string. Use
CHARINDEX function to find the first position of '/' in the string (reversed
in your case). Use REVERSE function to reverse a string.
Using all the above, you can do:
SELECT RIGHT( @.s, CHARINDEX('/', REVERSE( @.s ) ) - 1 )
Details & syntax of all the above mentioned functions can be found in SQL
Server Books Online.
Anith|||The OP might need to make minor adjustments for cases like:
http://www.foo.com/
http://www.foo.com/foo.gif/
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OM86YE#KFHA.904@.tk2msftngp13.phx.gbl...
> Use the RIGHT function to extract the right most portion of a string. Use
> CHARINDEX function to find the first position of '/' in the string
(reversed
> in your case). Use REVERSE function to reverse a string.
> Using all the above, you can do:
> SELECT RIGHT( @.s, CHARINDEX('/', REVERSE( @.s ) ) - 1 )
> Details & syntax of all the above mentioned functions can be found in SQL
> Server Books Online.
> --
> Anith
>

How to find Carriage Return CHAR(13)?

I've been looking for this online and on MSDN but no luck. I simply want to find all my CR in specific columns and later Replace them with a string (ie. --THIS-IS-A-CR--). The problem is I cannot even find/search CHAR(13) by using variations of the query below.

SELECT *
FROM Incident
WHERE (description LIKE '%CHAR(13)%') --I know this is incorrect

try this

SELECT *

FROM Incident

WHERE (description LIKE '%'+char(13)+'%')

|||Thanks Gopi, hope this post helps others as well|||

Gopi,

how will I replace with a string?

|||

This is what I am trying, it says that all rows have been updated, but when I do a find for the string I cannot find any changes to the table.

update Incident set
description = replace(cast(description AS varchar(8000)), '%'+char(13)+'%', '--THIS-WAS-CR--')
where description like '%'+char(13)+'%'

|||

try this

update Incident set

description = replace(cast(description AS varchar(8000)), char(13), '--THIS-WAS-CR--')

how to find a substring in a string

I guess my question is that in my sp how can I make sure wether a substring
exists ina string or not.I'm using this now,is there something better :
SELECT @.tempString = REPLACE(UPPER(mySpParamter),'MEMBER','wo
w!')
IF @.tempString <> mySpParamter --This means that the MEMBER keyword is
part of the word
BEGIN
SET @.ClientType='MB'
END
thanksHow about :
If myspParamter like '%MEMBER%'
Begin
....
"Ray5531" wrote:

> I guess my question is that in my sp how can I make sure wether a substrin
g
> exists ina string or not.I'm using this now,is there something better :
> SELECT @.tempString = REPLACE(UPPER(mySpParamter),'MEMBER','wo
w!')
> IF @.tempString <> mySpParamter --This means that the MEMBER keyword is
> part of the word
> BEGIN
> SET @.ClientType='MB'
> END
>
> thanks
>
>|||Yours better,
Thanks
"dtbascent" <dtbascent@.discussions.microsoft.com> wrote in message
news:8FC6472D-4705-4753-AB47-B5DD1C0A2AE6@.microsoft.com...
> How about :
> If myspParamter like '%MEMBER%'
> Begin
> .....
> "Ray5531" wrote:
>|||Hi
Looks like 'MEMBER' will never exist in @.tempString because its replaced
with 'wow!'
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"dtbascent" wrote:
> How about :
> If myspParamter like '%MEMBER%'
> Begin
> .....
> "Ray5531" wrote:
>|||yes,that's the key
then I check if it's different it mean it has been located(exists) and if it
dosen't exists replace returns the original string.
Thanks
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:BF8E3946-DB9D-410F-A6E2-40106B478976@.microsoft.com...
> Hi
> Looks like 'MEMBER' will never exist in @.tempString because its replaced
> with 'wow!'
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> ---
>
> "dtbascent" wrote:
>

How to find a string?

I have a table caled 'Test' with column caled 'Msgtext' on SQL 2000. The
column Msgtest have ca 800 000 rows.
One ex. on some rows on Msgtext column is:
-Oct 19 08:24:17 security[success] 538 SRV1 User Logoff: User Name:Rolf
Domain:
-Oct 20 10:56:17 security[success] 540 SRV2 User Name: Domain:
I need a query that can find and cut just string 'User Name:' but with the
actuell user name, f. ex. 'User Name:Rolf'. If the query found 'User Name:'
without actuell user name, jump over. The data in these rows is space
delimited.
Then the query should check if the ex. 'User Name:Rolf' is found more than 3
times
within last 24 hours, then create a table caled 'Result' and put in the
actuell user
name i the table 'Result' under column 'User'.
Thanks!Hi Mile
You can check PATINDEX function avaliable for Strings in SQL Server
http://msdn.microsoft.com/library/d...br />
28xk.asp
once you have the position u can use
http://msdn.microsoft.com/library/e...s.blogspot.com/
http://www.SQLResource.com/
---
"Mile" wrote:

> I have a table caled 'Test' with column caled 'Msgtext' on SQL 2000. The
> column Msgtest have ca 800 000 rows.
> One ex. on some rows on Msgtext column is:
> -Oct 19 08:24:17 security[success] 538 SRV1 User Logoff: User Name:Rolf
> Domain:
> -Oct 20 10:56:17 security[success] 540 SRV2 User Name: Domain:
> I need a query that can find and cut just string 'User Name:' but with the
> actuell user name, f. ex. 'User Name:Rolf'. If the query found 'User Name:
'
> without actuell user name, jump over. The data in these rows is space
> delimited.
> Then the query should check if the ex. 'User Name:Rolf' is found more than
3
> times
> within last 24 hours, then create a table caled 'Result' and put in the
> actuell user
> name i the table 'Result' under column 'User'.
> Thanks!|||Thanks Chandra,
I'm not so familiar with transact T-SQL code, I don't understand how to cut
after ex.
'User Name:Rolf' because the actuell user (in this case Rolf) can variety in
character length.
Can you please tray to write code for my example?
Thanks!
"Chandra" wrote:
> Hi Mile
> You can check PATINDEX function avaliable for Strings in SQL Server
> http://msdn.microsoft.com/library/d... />
z_28xk.asp
> once you have the position u can use
> http://msdn.microsoft.com/library/e...s.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Mile" wrote:
>|||Hi Mile
Just check this one. It might help you
SELECT
CASE WHEN PATINDEX(''User Name:%',user) > 1 THEN
SUBSTRING(user, PATINDEX(''User Name:%',user), LEN(user) - PATINDEX(''User
Name:%',user) )
ELSE user
END
[NAME]
FROM RESULT
Please not that it is just a sample and to give u an example. I didnt try
and execute it before sending.
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Mile" wrote:
> Thanks Chandra,
> I'm not so familiar with transact T-SQL code, I don't understand how to cu
t
> after ex.
> 'User Name:Rolf' because the actuell user (in this case Rolf) can variety
in
> character length.
> Can you please tray to write code for my example?
> Thanks!
> "Chandra" wrote:
>|||Thanks Chandra, I have tested this code and get the 'User Name:' (with
actuell user)
in a temporerly table caled 'Result':
select substring(msgtext,charindex('User Name:',msgtext),
charindex(' ',msgtext,charindex('User Name:',msgtext)+len('User
Name'))-charindex('User Name:',msgtext)) 'Result' from syslogd where msgtext
like '%User Name:%' and msgtext not like '%User Name: %'
Can you help me now to put all these 'User Name:' that is repeated more than
3 times
in a permanent table caled 'Finish'.
Thanks in advance!
"Chandra" skrev:
> Hi Mile
> Just check this one. It might help you
> SELECT
> CASE WHEN PATINDEX(''User Name:%',user) > 1 THEN
> SUBSTRING(user, PATINDEX(''User Name:%',user), LEN(user) - PATINDEX(''Us
er
> Name:%',user) )
> ELSE user
> END
> [NAME]
> FROM RESULT
>
> Please not that it is just a sample and to give u an example. I didnt try
> and execute it before sending.
> Please let me know if u have any questions
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Mile" wrote:
>|||On Tue, 25 Oct 2005 05:40:09 -0700, Mile wrote:

>I have a table caled 'Test' with column caled 'Msgtext' on SQL 2000. The
>column Msgtest have ca 800 000 rows.
>One ex. on some rows on Msgtext column is:
>-Oct 19 08:24:17 security[success] 538 SRV1 User Logoff: User Name:Rolf
>Domain:
>-Oct 20 10:56:17 security[success] 540 SRV2 User Name: Domain:
>I need a query that can find and cut just string 'User Name:' but with the
>actuell user name, f. ex. 'User Name:Rolf'. If the query found 'User Name:'
>without actuell user name, jump over. The data in these rows is space
>delimited.
Hi Mile,
For this first part, try if this suits your needs:
SELECT SUBSTRING(Msgtext,
PATINDEX('%User Name:%', Msgtext),
CHARINDEX(' ',
Msgtext,
PATINDEX('%User Name:%', Msgtext) + 6)
- PATINDEX('%User Name:%', Msgtext))
FROM Test
WHERE Msgtext LIKE '%User Name:[^ ]%'

>Then the query should check if the ex. 'User Name:Rolf' is found more than
3
>times
>within last 24 hours, then create a table caled 'Result' and put in the
>actuell user
>name i the table 'Result' under column 'User'.
>Thanks!
Assuming the above query works as expected, you can now change it to
SELECT SUBSTRING(Msgtext,
PATINDEX('%User Name:%', Msgtext),
CHARINDEX(' ',
Msgtext,
PATINDEX('%User Name:%', Msgtext) + 6)
- PATINDEX('%User Name:%', Msgtext))
FROM Test
WHERE Msgtext LIKE '%User Name:[^ ]%'
GROUP BY SUBSTRING(Msgtext,
PATINDEX('%User Name:%', Msgtext),
CHARINDEX(' ',
Msgtext,
PATINDEX('%User Name:%', Msgtext) + 6)
- PATINDEX('%User Name:%', Msgtext))
HAVING COUNT(*) > 3
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you Hugo, it works!!!
"Hugo Kornelis" wrote:

> On Tue, 25 Oct 2005 05:40:09 -0700, Mile wrote:
>
> Hi Mile,
> For this first part, try if this suits your needs:
> SELECT SUBSTRING(Msgtext,
> PATINDEX('%User Name:%', Msgtext),
> CHARINDEX(' ',
> Msgtext,
> PATINDEX('%User Name:%', Msgtext) + 6)
> - PATINDEX('%User Name:%', Msgtext))
> FROM Test
> WHERE Msgtext LIKE '%User Name:[^ ]%'
>
> Assuming the above query works as expected, you can now change it to
> SELECT SUBSTRING(Msgtext,
> PATINDEX('%User Name:%', Msgtext),
> CHARINDEX(' ',
> Msgtext,
> PATINDEX('%User Name:%', Msgtext) + 6)
> - PATINDEX('%User Name:%', Msgtext))
> FROM Test
> WHERE Msgtext LIKE '%User Name:[^ ]%'
> GROUP BY SUBSTRING(Msgtext,
> PATINDEX('%User Name:%', Msgtext),
> CHARINDEX(' ',
> Msgtext,
> PATINDEX('%User Name:%', Msgtext) + 6)
> - PATINDEX('%User Name:%', Msgtext))
> HAVING COUNT(*) > 3
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>