Coonection to SQL Server 2005 Express

What is the proper connection string for SQL Server 2005 Express on XP?
I want to connect to the master database by VBScript as the following:
Dim computer : computer = CreateObject("WScript.Network").ComputerName
Dim connectionString:connectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Master;Data
Source=" + computer
Dim connection
Set connection= CreateObject("ADODB.Connection")
With connection
.Provider = "SQLOLEDB"
.ConnectionString = connectionString
.Open
End With
It works on MSDE. But I got a "[DBNETLIB][ConnectionOpen (Connect()).]SQL
Server does not exist or access denied. " error for SQL Express. I have the
TCP/IP enabled for SQL Server Express too.
Thanks.
take help from KBA
http://support.microsoft.com/default...;EN-US;Q328306 and
http://www.connectionstrings.com/ for all types of connection strings.
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"Roy" wrote:

> What is the proper connection string for SQL Server 2005 Express on XP?
> I want to connect to the master database by VBScript as the following:
> Dim computer : computer = CreateObject("WScript.Network").ComputerName
> Dim connectionString:connectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=Master;Data
> Source=" + computer
> Dim connection
> Set connection= CreateObject("ADODB.Connection")
> With connection
> .Provider = "SQLOLEDB"
> .ConnectionString = connectionString
> .Open
> End With
> It works on MSDE. But I got a "[DBNETLIB][ConnectionOpen (Connect()).]SQL
> Server does not exist or access denied. " error for SQL Express. I have the
> TCP/IP enabled for SQL Server Express too.
> Thanks.

Coonection to SQL Server 2005 Express

What is the proper connection string for SQL Server 2005 Express on XP?
I want to connect to the master database by VBScript as the following:
Dim computer : computer = CreateObject("WScript.Network").ComputerName
Dim connectionString:connectionString = "Provider=SQLOLEDB.1;Integrated
Security=SSPI;Persist Security Info=False;Initial Catalog=Master;Data
Source=" + computer
Dim connection
Set connection= CreateObject("ADODB.Connection")
With connection
.Provider = "SQLOLEDB"
.ConnectionString = connectionString
.Open
End With
It works on MSDE. But I got a "[DBNETLIB][ConnectionOpen (Connect())
.]SQL
Server does not exist or access denied. " error for SQL Express. I have the
TCP/IP enabled for SQL Server Express too.
Thanks.take help from KBA
http://support.microsoft.com/defaul...b;EN-US;Q328306 and
http://www.connectionstrings.com/ for all types of connection strings.
--
--
Satya SKJ
Visit http://www.sql-server-performance.com for tips and articles on
Performance topic.
"Roy" wrote:

> What is the proper connection string for SQL Server 2005 Express on XP?
> I want to connect to the master database by VBScript as the following:
> Dim computer : computer = CreateObject("WScript.Network").ComputerName
> Dim connectionString:connectionString = "Provider=SQLOLEDB.1;Integrated
> Security=SSPI;Persist Security Info=False;Initial Catalog=Master;Data
> Source=" + computer
> Dim connection
> Set connection= CreateObject("ADODB.Connection")
> With connection
> .Provider = "SQLOLEDB"
> .ConnectionString = connectionString
> .Open
> End With
> It works on MSDE. But I got a "[DBNETLIB][ConnectionOpen (Connect(
)).]SQL
> Server does not exist or access denied. " error for SQL Express. I have th
e
> TCP/IP enabled for SQL Server Express too.
> Thanks.

Converts the 5 character string duration (ie hh:nn) to minutes

Dear All,

I wanted to convert 5 characters string duration (ie hh:mm - 10:30) to minutes and convert back the resulting minutes to 5 character string duration using a scalar UDFs in Sqlserver 2000. How do i write the script to obtain the desired output.

I badly need some from someone.

Give few set of example. do you want result 10 *60 + 30 for 10:30..|||

For example, if duration is 10 hrs. 30 mins. written in 10:30 format should be convert to minutes, means convert 10 hours into minutes and add 30 mins to it will be the resulting output = 630 minutes this should be convert back into the previous (10:30) format.

Thanks

|||

here you go...

Code Snippet

Create Table #times (

[Time] Varchar(100)

);

Insert Into #times Values('10:30');

Insert Into #times Values('12:34');

Insert Into #times Values('15:45');

Select

Datediff(Mi,Cast('00:00' as datetime) ,Cast([Time] as Datetime))

From

#times

--or

Select

Substring([Time],1,Charindex(':',[Time])-1) * 60

+ Substring([Time],Charindex(':',[Time]) + 1,10)

From

#Times

|||

Thanks alot for the help. And how about the reverse of that, ie, Converts the resulting minutes to 5 character string duration

|||

Yes.. here it is...

Code Snippet

Create Table #mindata (

[Mint] int

);

Insert Into #mindata Values('630');

Insert Into #mindata Values('754');

Insert Into #mindata Values('945');

Select

Cast(Mint/60 as Varchar) + ':' + Cast(Mint%60 as Varchar)as [Time]

From

#mindata

|||

Dear Manivannan.D.Sekaran,

Thank you very very very much.

Converting Timestamp to varchar or concatenating it with a string

Hello,

I apologise if this question has been asked before but I have searched forums and the web and have not found a solution. I am current creating a script that has a cursor that builds a sql statement to be executed e.g.

--code within cursor

SELECT'

DECLARE @.Result INT

EXEC @.Result = DELETE_DOCUMENT

@.DocumentID = ' + STR(DocumentID) + ',

@.TimeStamp =' + CAST([Timestamp] as varchar) + ',

-- CHECK RESULT AND STATUS

-- IF OK LOG IN META_BATCH ELSE LOG ERROR' AS SQL

FROMDocument

The problem I am having is trying to join the timestamp column into the sql string. I have tried to cast the time stamp to a varchar but I end up with the following output for the timestamp column values

T

T?

T-

xnT

T!

T"

T#

T$

T%

T&

T'

T(

T)

T*

T+

T,

instead of

0x0000000013540F1C

0x0000000013540F1E

0x0000000013540F1F

0x0000000013786EDE

0x0000000013540F21

0x0000000013540F22

0x0000000013540F23

0x0000000013540F24

0x0000000013540F25

0x0000000013540F26

0x0000000013540F27

0x0000000013540F28

0x0000000013540F29

0x0000000013540F2A

0x0000000013540F2B

0x0000000013540F2C

which would not allow my delete script to work correctly. So I would really appreciate some advice to a pointer to where I might find out how to convert the timestamp.

Thanks

Sam

use tempdb

go

create table dbo.t1 (

c1 timestamp

)

go

insert into dbo.t1 default values

go

select

c1,

master.sys.fn_varbintohexstr(cast(c1 as varbinary(8)))

from

dbo.t1

go

drop table dbo.t1

go

AMB

|||

hi

Thanks for that I have been looking for that kind of function every where

Regards

Sam

Converting the string value to data format

Hello Everyone,

Please guide me in converting the value to date format.

from source i'm getting the value (which is acchally a data value) '20070730'.

I need this value to be in date format '2007/07/30'

Please help me in getting it done.

thank you

Hi

Code Snippet

select cast('20070730' as datetime)

Note : beware of collation used in your SQL instance or your database.

--

Jean-Pierre

|||

THANK YOU FOR YOUR REPLY.

bUT MY SOURCE IS FALAT FILE

|||

HI, you van use a derived column with the following expression:

(DT_DBTIMESTAMP)(SUBSTRING(Column,1,4) + "-" + SUBSTRING(Column,5,2) + "-" + SUBSTRING(Column,7,2))

HTH,

Ccote

Converting Text to Proper Text in SQL

Given a string it should convert it to a proper text.
Example: if you passed a string 'Cat in the hat', I want 'Cat In The
Hat'

Curious about few things, Does sql have Instr OR Split(like VB)
functionality

Anybody can help??(m.ramana@.gmail.com) writes:
> Given a string it should convert it to a proper text.
> Example: if you passed a string 'Cat in the hat', I want 'Cat In The
> Hat'

I though "Cat in the Hat" was the proper title text in English,
and "Cat In The Hat" is what you get when you use a computer?

SQL Server is not strong on text maninpulation. You would have to loop
over the string, either one by one, or possibly piece by piece with
charindex(). There is no built-in for this.

> Curious about few things, Does sql have Instr OR Split(like VB)
> functionality

I would encourage you to look at Functions->String Functions in the
T-SQL References in Books Online. There you can learn about all functions
to manipulate strings in T-SQL.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||
m.ramana@.gmail.com wrote:
> Given a string it should convert it to a proper text.
> Example: if you passed a string 'Cat in the hat', I want 'Cat In The
> Hat'
> Curious about few things, Does sql have Instr OR Split(like VB)
> functionality
> Anybody can help??

CREATE function TitleCase
(
@.my_str as varchar(8000)
)
Returns varchar (8000)
AS
Begin
Declare @.this_str as varchar(8000)
Declare @.word_str as varchar(5000)
Declare @.spc int
Declare @.patindx1 as varchar(100)
Declare @.patindx2 as varchar(100)
Declare @.patindx3 as varchar(100)
Declare @.patindx4 as varchar(100)
select @.patindx1 = '%[ -"/().]%'
select @.patindx2 = '%[''][^s]%'
select @.patindx4 = '%[''][s][a-z]%'
select @.patindx3 = '%[0123456789][^snrt][^tdh]%'
Select @.this_str = ''

Select
@.my_str = LTrim(RTrim(@.my_str))

While Len(@.my_str) > 0
Begin
if (PatIndex(@.patindx1, @.my_str) + PatIndex(@.patindx2, @.my_str) +
PatIndex(@.patindx3, @.my_str) + PatIndex(@.patindx4, @.my_str) = 0)
Begin
Select
@.spc = Len(@.my_str)
End
Else
Begin
Select @.spc = PatIndex(@.patindx1, @.my_str)
If @.spc = 0 Or (PatIndex(@.patindx2, @.my_str) < @.spc
And PatIndex(@.patindx2, @.my_str) > 0)
Select @.spc = PatIndex(@.patindx2, @.my_str)
If @.spc = 0 Or (PatIndex(@.patindx3, @.my_str) < @.spc
And PatIndex(@.patindx3, @.my_str) > 0)
Select @.spc = PatIndex(@.patindx3, @.my_str)
If @.spc = 0 Or (PatIndex(@.patindx4, @.my_str) < @.spc
And PatIndex(@.patindx4, @.my_str) > 0)
Select @.spc = PatIndex(@.patindx4, @.my_str)

End

Select
@.word_str = Left(@.my_str, @.spc)

Select
@.this_str = @.this_str + Upper(Left(@.word_str,1)) +
Lower(SubString(@.word_str,2,@.spc))
select @.my_str = Right(@.my_str, (Len(@.my_str)-(@.spc)))
End
Return (@.this_str)
End

--
David Rowland
NEW DBMonitor Out Now! New Email Alert Feature!
http://dbmonitor.tripod.com|||Great, I was about to create one, appreciate your time.

Converting string to unicode string in T-SQL

Hi,
We have stored proc name proc_test(str nvarchar(30)). So far this proc
has been invoked from a .NET application assuming that only English
character strings will be passed to it. The calls are like
proc_test('XYZ')
We now have a requirement for passing Chinese strings as well. Rather
than changing the calls throughout the application, we would like to
handle it in the stored procedure so that it treats the string as a
unicode string. Can we apply some function to the parameter to convert
it to unicode so that we don't have to call with an N prefixed to the
string?
Thanks,
YashHi
> unicode string. Can we apply some function to the parameter to convert
> it to unicode so that we don't have to call with an N prefixed to the
> string?
Do you mean to get an INTEGER of the string , then you have UNICODE function
, see in the BOL.
What's wrong with calling with an N prefixed to the string?
<yashgt@.gmail.com> wrote in message
news:1174896401.921135.170240@.b75g2000hsg.googlegroups.com...
> Hi,
> We have stored proc name proc_test(str nvarchar(30)). So far this proc
> has been invoked from a .NET application assuming that only English
> character strings will be passed to it. The calls are like
> proc_test('XYZ')
> We now have a requirement for passing Chinese strings as well. Rather
> than changing the calls throughout the application, we would like to
> handle it in the stored procedure so that it treats the string as a
> unicode string. Can we apply some function to the parameter to convert
> it to unicode so that we don't have to call with an N prefixed to the
> string?
> Thanks,
> Yash
>|||On Mar 26, 11:06 am, yas...@.gmail.com wrote:
> [...]
> We now have a requirement for passing Chinese strings as well. [...]
> Can we apply some function to the parameter to convert it to unicode
> so that we don't have to call with an N prefixed to the string?
No, you can't. If you don't prefix it with N, then the chinese
characters are lost in the implicit conversion to varchar, so you
cannot get them back (unless the varchar has a DBCS collation, which
would be if you have the default server collation on a Chinese_*
collation, but I don't think that would be a good idea).
Razvan|||<yashgt@.gmail.com> wrote in message
news:1174896401.921135.170240@.b75g2000hsg.googlegroups.com...
> Hi,
> We have stored proc name proc_test(str nvarchar(30)). So far this proc
> has been invoked from a .NET application assuming that only English
> character strings will be passed to it. The calls are like
> proc_test('XYZ')
You should go back and change your code to properly parameterize your
queries instead of concatenating the parameter values into strings,
VB5-style. Then you wouldn't have to worry about the N prefix. Or SQL
Injection.|||Are you building query strings in your .Net code by concatenating character
values? If so - why? Use parameters, or better yet use stored procedures.
ML
--
http://milambda.blogspot.com/

Converting string to int

Hi,

This probably is a basic question but I can't figure it out...

Because SQL Server's ISNUMERIC function allows some strange values to count as numeric (such as '\'), I want to create my own function that will only return an integer if the value is able to be converted (otherwise it will return 0). I have created the following function to do this:

CREATE FUNCTION [dbo].[fnConvertToInt]
(
@.str nvarchar(30)
)
RETURNS int
AS
BEGIN
DECLARE @.s int

BEGIN TRY
SET @.s = convert(int, @.str)
END TRY
BEGIN CATCH
SET @.s = 0
END CATCH
-- Return the result of the function
RETURN @.s
END

When I run this however, I get errors from having the TRY CATCH in a function:

Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function.

How can I convert a string to an integer without getting an error? I am using SQL Server 2005.

Hi,

the isnumeric function is hazle, but you can implement some isreallynumeric, like aaron did this on his website:

http://www.aspfaq.com/show.asp?id=2390

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Converting string to datetime

Hi,

I'm in a bit of a tricky situation. I'm upgrading and existing application (VB.NET 05 and sql server 2000). One of the tables has field having datatype varchar(20) but actually storing dates. From different parts of the application the datetime values are saved basically in three formats.

1. 2004/11/26 00:00:00

2. Nov 25 2004 12:00AM
3. 24/11/2004

The problem is I need to run a datediff to calculate a date difference. I can't get my head around to convert all the three types to one data type using a sql.

Really appritiate if some one can help me out on this.

Regards,

Vije

If your data is exactly the three formats you provided, then this approach should work for you:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
MyDate nvarchar(30),
MyNewDate datetime
)

INSERT INTO @.MyTable ( MyDate ) VALUES ( '2004/11/26 00:00:00' )
INSERT INTO @.MyTable ( MyDate ) VALUES ( 'Nov 25 2004 12:00AM' )
INSERT INTO @.MyTable ( MyDate ) VALUES ( '24/11/2004' )

UPDATE @.MyTable
SET MyNewDate = CASE isdate( MyDate )
WHEN 1 THEN cast( MyDate AS datetime )
ELSE convert( datetime, MyDate, 103 )
END
SELECT *
FROM @.MyTable

RowID MyDate MyNewDate
-- - -
1 2004/11/26 00:00:00 2004-11-26 00:00:00.000
2 Nov 25 2004 12:00AM 2004-11-25 00:00:00.000
3 24/11/2004 2004-11-24 00:00:00.000

|||

Vije:

You might be able to get by with something like this:

Code Snippet

select theDt,
case when charindex(':', theDt) <> 0
and isDate(theDt) = 1
then convert(datetime, theDt)
when charindex(':', theDt) = 0
and isDate(theDt) = 0
and isDate
( parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
) = 1
then parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
end as convertedDT
from ( select '2004/11/26 00:00:00' as theDT union all
select 'Nov 25 2004 12:00AM' union all
select '24/11/2004' union all
select 'invalid'
) a
/*
where isDate(theDt) = 1
or ( charindex(':', theDt) = 0
and isDate(theDt) = 0
and isDate
( parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
) = 1
)
*/

/*
theDt convertedDT
-
2004/11/26 00:00:00 2004-11-26 00:00:00.000
Nov 25 2004 12:00AM 2004-11-25 00:00:00.000
24/11/2004 2004-11-24 00:00:00.000
invalid NULL
*/

If you don't want the invalid data to appear just uncomment the WHERE clause (that is shown in red).

As an aside, you ought to consider converting this column to a DATETIME data type. These kinds of problems will only grow until you resolve the real problem here -- which is a design problem.

|||

In one single update statement you can't do it..

But the following batch may help you.

Code Snippet

Create Table #datedata (

[Dates] Varchar(20)

);

Insert Into #datedata Values('2004/11/26 00:00:00');

Insert Into #datedata Values('Nov 25 2004 12:00AM');

Insert Into #datedata Values('24/11/2004');

Set DateFormat DMY

Update

#datedata

Set

Dates = Convert(varchar,cast(Dates as datetime) , 120) -- Finaly converted as ANSI Format

Where

Isdate(Dates) = 1

Set DateFormat YMD

Update

#datedata

Set

Dates = Convert(varchar,cast(Dates as datetime) ,120) -- Finaly converted as ANSI Format

Where

Isdate(Dates) = 1

Select * From #dateData

|||

Woooh .. We all 3 given unique and different solution.... . I like Arnie's solution, Kent you are really hard worker..

Converting string to datetime

Hi

What's wrong about

SELECT SUM(CASE WHEN [PO Date] BETWEEN CONVERT(Datetime, @.FY + '/04/01')
AND CONVERT(Datetime, @.FY -1 + '/03/31') THEN Quantity ELSE 0 END) AS Expr1,
[Item No_]
FROM table A

It looks like you are trying to use the aggregate function sum() and the [item no_] column is not in an aggregate function or group by clause

|||

here it is,

Code Snippet

SELECT

SUM(CASE WHEN [PO Date] BETWEEN CONVERT(Datetime, @.FY + '/04/01')

AND CONVERT(Datetime, @.FY -1 + '/03/31') THEN Quantity ELSE 0 END) AS Expr1,

[Item No_]

FROM table A

Group By

[Item No_]

|||

Still can't! It show error massage" wrong parameter".

|||

Please provide the entire procedure code and the error message in its entirity.|||

Hi

I use this query on reporting service. After perview will show "Conversion failed when converting to varchar value"/04/01" to date type int.

|||

You are attempting to concatenate the varchar value '/04/01' to the integer value @.FY.

You need to first cast the variable @.FY as a char()/varchar().

However, this could be avoided IF you used an actual datetime value for the fiscal year instead an integer value. This may be an excellent opportunity to finally explore using the 'Calendar' table you've most likely heard about before. I recommend reviewing this article to see if there is utility in the concept for you.

(From this example, I suspect that there are other places in your code where you are attempting to handle 'date' issues in 'creative' and non-functional methods.)

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

|||

Hai,

As Arnie said, you need to first convert @.FY value to varchar type.

And, also, in your query, first date in Between is > second date which always returns the Expr1 to 0.

you can try this, sample, query:

DECLARE @.FY int

DECLARE @.TableA Table([PODate] datetime, [ItemNo_] int, Quantity int)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/01',1, 200)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/03',1, 100)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/05',1, 50)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/10',2, 30)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/22',2, 40)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/04/01',3, 60)

SET @.FY = 2007

SELECT

SUM(CASE WHEN [PODate] BETWEEN CONVERT(Datetime, CONVERT(VarChar(4),(@.FY-1)) + '/03/31') AND

CONVERT(Datetime, CONVERT(VarChar(4),@.FY) + '/04/01')

THEN Quantity ELSE 0

END) AS Expr1,

[ItemNo_]

FROM @.TableA

GROUP BY [ItemNo_]

Hope this will work, Please clarify If'm wrong.

Regards,

Y.Kiran Kumar.

Converting String to Date in Report Expressions

Hello,

I am running a report in RS 2005 and as an expression I have something like that:

=DateDiff("YYYY", NOW(), CDATE("2001"))

Unfortunately, I am getting message:

Conversion from string "2001" to type 'Date' is not valid.

Is there a way to fix it?

Thanks!

The CDATE function wants a valid date format passed to it and not just the year. It would work if you specified something like "1/1/2001" or "Jan 1, 2006". However, it appears all you're trying to do is find the difference between the current year and some specified year. If that's the case, I would suggest using the following expression instead:

=Year(Now()) - 2001

or if the specified year is a string value:

=Year(Now()) - CInt("2001")

Sincerely,

Micheal

|||

Greate advice!

Thanks for your help!

converting string to date

Hello,

I try to convert a pseudo datetime string into a date. In Oracle I can do
to_date( MyDate, 'yyyymmddhh24miss' ); how I can do this with MS SQL ?

thanks and regards
MarkSee CONVERT() in Books Online - it supports a number of different
formats, although not the exact one you've mentioned above. You might
need to look at modifying the string before using CONVERT() - see
"String Functions" in Books Online, or you could also consider doing
that in your client application.

Simon|||On Fri, 12 Aug 2005 14:17:57 +0200, Mark wrote:

>Hello,
>I try to convert a pseudo datetime string into a date. In Oracle I can do
>to_date( MyDate, 'yyyymmddhh24miss' ); how I can do this with MS SQL ?
>thanks and regards
>Mark

Hi Mark,

The easiest way is to use string functions to convert your date to the
unambiguous ISO-standard yyyy-mm-ddThh:mm:ss format, then cast to
datetime:

DECLARE @.DateString char(14)
SET @.DateString = '20050812204332'
SELECT CAST(SUBSTRING(@.DateString, 1, 4) + '-'
+ SUBSTRING(@.DateString, 5, 2) + '-'
+ SUBSTRING(@.DateString, 7, 2) + 'T'
+ SUBSTRING(@.DateString, 9, 2) + ':'
+ SUBSTRING(@.DateString, 11, 2) + ':'
+ SUBSTRING(@.DateString, 13, 2) AS datetime)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Converting string hex pair into character

I need to parse a text string, stored as varchar(1000), like the one shown
below
RootFolder=%2fC14%2fLDI%20Documentation%
2fDocument%20Library
and covert the hexadecimal character codes into their corresponding
characters. For example, the above should come out to
RootFolder=/C14/LDI Documentation/Document Library
I cannot figure out a way to pass in a string value for the hex code to the
CHAR function. For example
SELECT CHAR('0x2f')
returns a datatype conversion error. Trying to manually CAST it produces the
same error.
The idea was to iterate through the string, finding all hex-pair represented
characters, and covert them, like so
WHILE (CHARINDEX('%', @.csuristem) <> 0 )
BEGIN
SET @.csuristem = LEFT(@.csuristem, CHARINDEX('%', @.csuristem) - 1) +
SOMEFUNCTION_TO_CONVERT_HEX_TO_CHAR(SUBS
TRING(@.csuristem, CHARINDEX('%',
@.csuristem) + 1,2) +
RIGHT(@.csuristem, LEN(@.csuristem) - CHARINDEX('%', @.csuristem))
END% is a reserved character in SQL (the wildcard character) and needs to be
escaped.
Try this:
(CHARINDEX('[%]', @.csuristem)
And of course:
CHAR(0x2f)
"Be it a number, no quotes shall be used. No where, no how, so there."
-- Luke, 5, 12
The tough part is actually converting the HEX codes to characters in T-SQL.
Perhaps this example can be of help:
http://milambda.blogspot.com/2005/0...a.blogspot.com/|||leave out the quotes: char(0x2f)
but another way is to add a character mapping table, e.g.
create table charmap (
asc_val tinyint not null unique,
htm_hex char(3) not null unique,
chr_val char(1) not null unique
)
insert into charmap (asc_val, htm_hex, chr_val)
values (32, '%20', ' ')
insert into charmap (asc_val, htm_hex, chr_val)
values (33, '%21', '!')
etc.
and then use the way mssql assignes variables to do it in one shot, e.g.
declare @.x varchar(1000)
set @.x = 'RootFolder=%2fC14%2fLDI%20Documentation
%2fDocument%20Library'
select @.x = replace(@.x, htm_hex, chr_val)
from charmap
select @.x
Mark Williams wrote:
> I need to parse a text string, stored as varchar(1000), like the one shown
> below
> RootFolder=%2fC14%2fLDI%20Documentation%
2fDocument%20Library
> and covert the hexadecimal character codes into their corresponding
> characters. For example, the above should come out to
> RootFolder=/C14/LDI Documentation/Document Library
> I cannot figure out a way to pass in a string value for the hex code to th
e
> CHAR function. For example
> SELECT CHAR('0x2f')
> returns a datatype conversion error. Trying to manually CAST it produces t
he
> same error.
> The idea was to iterate through the string, finding all hex-pair represent
ed
> characters, and covert them, like so
> WHILE (CHARINDEX('%', @.csuristem) <> 0 )
> BEGIN
> SET @.csuristem = LEFT(@.csuristem, CHARINDEX('%', @.csuristem) - 1) +
> SOMEFUNCTION_TO_CONVERT_HEX_TO_CHAR(SUBS
TRING(@.csuristem, CHARINDEX('%',
> @.csuristem) + 1,2) +
> RIGHT(@.csuristem, LEN(@.csuristem) - CHARINDEX('%', @.csuristem))
> END
>
>|||I know that CHAR(0x2f) will work, but that won't work as a solution to this
particular problem.
To put it another way, look at this
DECLARE @.string varchar(50)
SET @.string = 'mark%2fwilliams'
SELECT STUFF(@.string, CHARINDEX('%', @.string), 3, CHAR(SUBSTRING(@.string,
CHARINDEX('%', @.string) + 1, 2)))
I want to be able to programmatically convert hexadecimal specified
characters in a string.
I wrote my own udf to convert a hex string into an integer
CREATE FUNCTION udfHexcharToInt (@.hexstring varchar(512))
RETURNS int
AS
BEGIN
DECLARE @.halfbyte CHAR(1)
DECLARE @.halfbyteint int
DECLARE @.power int
DECLARE @.counter int
DECLARE @.outputint int
SET @.power = LEN(@.hexstring) - 1
SET @.counter = 0
SET @.outputint = 0
WHILE (@.counter <= @.power)
BEGIN
SET @.halfbyteint = CASE
WHEN ISNUMERIC(SUBSTRING(@.hexstring, @.counter + 1, 1)) = 1 THEN
CAST(SUBSTRING(@.hexstring, @.counter + 1, 1) AS int) * POWER(16, @.power -
@.counter)
WHEN ASCII(LOWER(SUBSTRING(@.hexstring, @.counter + 1, 1))) BETWEEN 97 AND
102 THEN (ASCII(LOWER(SUBSTRING(@.hexstring, @.counter + 1, 1))) - 87) *
POWER(16, @.power - @.counter)
ELSE -1
END
IF (@.halfbyteint < 0)
BEGIN
RETURN @.halfbyteint
END
SET @.outputint = @.outputint + @.halfbyteint
SET @.counter = @.counter + 1
END
RETURN @.outputint
END
I can then feed this into CHAR. The statement I started with now becomes
DECLARE @.string varchar(50)
SET @.string = 'mark%2fwilliams'
SELECT STUFF(@.string, CHARINDEX('%', @.string), 3,
CHAR(dbo.udfHexcharToInt(SUBSTRING(@.string, CHARINDEX('%', @.string) + 1, 2))
))
which works out. The charmap table sounds good too, and I will probably put
together another udf that will covert all of the hex pair characters in a
string at once.
If you couldn't tell, I'm doing this to clean up the content of a W3C
website log file.
"Trey Walpole" wrote:

> leave out the quotes: char(0x2f)
> but another way is to add a character mapping table, e.g.
> create table charmap (
> asc_val tinyint not null unique,
> htm_hex char(3) not null unique,
> chr_val char(1) not null unique
> )
> insert into charmap (asc_val, htm_hex, chr_val)
> values (32, '%20', ' ')
> insert into charmap (asc_val, htm_hex, chr_val)
> values (33, '%21', '!')
> etc.
> and then use the way mssql assignes variables to do it in one shot, e.g.
> declare @.x varchar(1000)
> set @.x = 'RootFolder=%2fC14%2fLDI%20Documentation
%2fDocument%20Library'
> select @.x = replace(@.x, htm_hex, chr_val)
> from charmap
> select @.x
> Mark Williams wrote:
>|||Here's another approach:
go
CREATE FUNCTION hex2char (
@. char(2)
) returns char as begin
set @. = upper(@.)
declare @.a tinyint
declare @.lookup char(15) set @.lookup = '123456789ABCDEF'
return (
select char(sum(wt*charindex(substring(@.,pos,1)
,@.lookup)))
from (select 1 as wt, 2 as pos union all select 16,1) as T
)
end
go
DECLARE @.string varchar(50)
SET @.string = 'RootFolder=%2fC14%2fLDI%20Documentation
%2fDocument%20Library'
DECLARE @.p int
SET @.p = patindex('%[%]%',@.string)
WHILE @.p > 0 BEGIN
SET @.string =
STUFF(@.string,@.p,3,dbo.hex2char(substring(@.string,@.p+1,2)))
SET @.p = patindex('%[%]%',@.string)
END
SELECT @.string
go
drop function hex2char
Steve Kass
Drew University
Mark Williams wrote:

>I need to parse a text string, stored as varchar(1000), like the one shown
>below
> RootFolder=%2fC14%2fLDI%20Documentation%
2fDocument%20Library
>and covert the hexadecimal character codes into their corresponding
>characters. For example, the above should come out to
>RootFolder=/C14/LDI Documentation/Document Library
>I cannot figure out a way to pass in a string value for the hex code to the
>CHAR function. For example
>SELECT CHAR('0x2f')
>returns a datatype conversion error. Trying to manually CAST it produces th
e
>same error.
>The idea was to iterate through the string, finding all hex-pair represente
d
>characters, and covert them, like so
>WHILE (CHARINDEX('%', @.csuristem) <> 0 )
>BEGIN
> SET @.csuristem = LEFT(@.csuristem, CHARINDEX('%', @.csuristem) - 1) +
> SOMEFUNCTION_TO_CONVERT_HEX_TO_CHAR(SUBS
TRING(@.csuristem, CHARINDEX('%',
>@.csuristem) + 1,2) +
> RIGHT(@.csuristem, LEN(@.csuristem) - CHARINDEX('%', @.csuristem))
>END
>
>
>|||Brilliant. Thanks!
--
"Steve Kass" wrote:

> Here's another approach:
>
> go
> CREATE FUNCTION hex2char (
> @. char(2)
> ) returns char as begin
> set @. = upper(@.)
> declare @.a tinyint
> declare @.lookup char(15) set @.lookup = '123456789ABCDEF'
> return (
> select char(sum(wt*charindex(substring(@.,pos,1)
,@.lookup)))
> from (select 1 as wt, 2 as pos union all select 16,1) as T
> )
> end
> go
> DECLARE @.string varchar(50)
> SET @.string = 'RootFolder=%2fC14%2fLDI%20Documentation
%2fDocument%20Librar
y'
> DECLARE @.p int
> SET @.p = patindex('%[%]%',@.string)
> WHILE @.p > 0 BEGIN
> SET @.string =
> STUFF(@.string,@.p,3,dbo.hex2char(substring(@.string,@.p+1,2)))
> SET @.p = patindex('%[%]%',@.string)
> END
> SELECT @.string
> go
> drop function hex2char
> Steve Kass
> Drew University
> Mark Williams wrote:
>
>

Converting String -> UniqueIdentifier

I get the following error when I do a CONVERT(UNIQUEIDENTIFIER, 'guid'), where 'guid' is a properly formatted GUID created in VS2005, inside my INSERT statement:

The data was truncated while converting from one data type to another. [ Name of function(if known) = ]

The column I am inserting into has a datatype of uniqueIdentifier.

This is the query I am running:

INSERT INTO [Table] ([guid], [value1], [value2])

VALUES (CONVERT(UNIQUEIDENTIFIER, 'F067FE20-EC76-44C8-9859-FEF222FBC96D'), 'Test, 'Test')

What am I doing wrong?

Matt

This works fine:

Code Snippet

INSERT INTO [Guidtest] ([Field1], [Field2])

VALUES (1, 'F067FE20-EC76-44C8-9859-FEF222FBC96D')

So no need to CONVERT!|||

Thks~

Converting String -> UniqueIdentifier

I get the following error when I do a CONVERT(UNIQUEIDENTIFIER, 'guid'), where 'guid' is a properly formatted GUID created in VS2005, inside my INSERT statement:

The data was truncated while converting from one data type to another. [ Name of function(if known) = ]

The column I am inserting into has a datatype of uniqueIdentifier.

This is the query I am running:

INSERT INTO [Table] ([guid], [value1], [value2])

VALUES (CONVERT(UNIQUEIDENTIFIER, 'F067FE20-EC76-44C8-9859-FEF222FBC96D'), 'Test, 'Test')

What am I doing wrong?

Matt

This works fine:

Code Snippet

INSERT INTO [Guidtest] ([Field1], [Field2])

VALUES (1, 'F067FE20-EC76-44C8-9859-FEF222FBC96D')

So no need to CONVERT!|||

Thks~

Converting Sql7 to 2000 version

Can anyone tell where i can find any guide to prevent big error!! My problems
are date and string. When using datetime fields, before i used [yyyy-mm-dd]
format, now its no longer available!!! How cai i do?!!?! Must i covert all
stored procedure or code? Wich problems can i have with the new string limit?
Thanx all!!
Marko
Hi
Have you looked at CAST and CONVERT. Formatting a string as you want is is
still there.
How are you trying to use the data?
Regards
Mike
"Marko" wrote:

> Can anyone tell where i can find any guide to prevent big error!! My problems
> are date and string. When using datetime fields, before i used [yyyy-mm-dd]
> format, now its no longer available!!! How cai i do?!!?! Must i covert all
> stored procedure or code? Wich problems can i have with the new string limit?
> Thanx all!!
> Marko
|||Thanx, but all my Stored Procedure i use CONVERT to obtain 120th format,
yyyy-mm-dd.
With 2000 version this is not good, and i must correct all the procedure
with yyyymmdd: is it correct?
This is a problem for me because i must to correct many procedures and then
i must tu correct all the procedure call from ado Connection; for example
cn.execute ("MyProc 'yyyy-mm-dd'") must become cn.execute ("MyProc
'yyyymmdd'")
Can you help me?
Again , Thanx !!
Mark
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Have you looked at CAST and CONVERT. Formatting a string as you want is is
> still there.
> How are you trying to use the data?
> Regards
> Mike
> "Marko" wrote:

Converting Sql7 to 2000 version

Can anyone tell where i can find any guide to prevent big error!! My problem
s
are date and string. When using datetime fields, before i used [yyyy-mm-
dd]
format, now its no longer available!!! How cai i do'!!?! Must i covert all
stored procedure or code? Wich problems can i have with the new string limit
?
Thanx all!!
MarkoHi
Have you looked at CAST and CONVERT. Formatting a string as you want is is
still there.
How are you trying to use the data?
Regards
Mike
"Marko" wrote:

> Can anyone tell where i can find any guide to prevent big error!! My probl
ems
> are date and string. When using datetime fields, before i used [yyyy-m
m-dd]
> format, now its no longer available!!! How cai i do'!!?! Must i covert a
ll
> stored procedure or code? Wich problems can i have with the new string lim
it?
> Thanx all!!
> Marko|||Thanx, but all my Stored Procedure i use CONVERT to obtain 120th format,
yyyy-mm-dd.
With 2000 version this is not good, and i must correct all the procedure
with yyyymmdd: is it correct'
This is a problem for me because i must to correct many procedures and then
i must tu correct all the procedure call from ado Connection; for example
cn.execute ("MyProc 'yyyy-mm-dd'") must become cn.execute ("MyProc
'yyyymmdd'")
Can you help me?
Again , Thanx !!
Mark
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> Have you looked at CAST and CONVERT. Formatting a string as you want is is
> still there.
> How are you trying to use the data?
> Regards
> Mike
> "Marko" wrote:
>

Converting Sql7 to 2000 version

Can anyone tell where i can find any guide to prevent big error!! My problems
are date and string. When using datetime fields, before i used [yyyy-mm-dd]
format, now its no longer available!!! How cai i do'!!?! Must i covert all
stored procedure or code? Wich problems can i have with the new string limit?
Thanx all!!
MarkoHi
Have you looked at CAST and CONVERT. Formatting a string as you want is is
still there.
How are you trying to use the data?
Regards
Mike
"Marko" wrote:
> Can anyone tell where i can find any guide to prevent big error!! My problems
> are date and string. When using datetime fields, before i used [yyyy-mm-dd]
> format, now its no longer available!!! How cai i do'!!?! Must i covert all
> stored procedure or code? Wich problems can i have with the new string limit?
> Thanx all!!
> Marko|||Thanx, but all my Stored Procedure i use CONVERT to obtain 120th format,
yyyy-mm-dd.
With 2000 version this is not good, and i must correct all the procedure
with yyyymmdd: is it correct'
This is a problem for me because i must to correct many procedures and then
i must tu correct all the procedure call from ado Connection; for example
cn.execute ("MyProc 'yyyy-mm-dd'") must become cn.execute ("MyProc
'yyyymmdd'")
Can you help me?
Again , Thanx !!
Mark
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Have you looked at CAST and CONVERT. Formatting a string as you want is is
> still there.
> How are you trying to use the data?
> Regards
> Mike
> "Marko" wrote:
> > Can anyone tell where i can find any guide to prevent big error!! My problems
> > are date and string. When using datetime fields, before i used [yyyy-mm-dd]
> > format, now its no longer available!!! How cai i do'!!?! Must i covert all
> > stored procedure or code? Wich problems can i have with the new string limit?
> > Thanx all!!
> > Marko

converting SELECT output to string

I'm looking for some good hints and tips for reprogrammin an old VB module I just found.

Basically what it does, is receive an input parameter (an int), does a select [name row] from Names where Name_id = [input parameter] and turns this into a string if multiplenames appear.

E.g. result set: John, Josh, Jock turns it into string "John Josh Jock".

So its piece of cake creating a stored procedure selecting data on the base of an input parameter. Select X from Y where Z = @.input... the trick is, I don't know how to do arrays in TSQL as in VB.

In the VB edition I create an array, load the names into it, I do a count on how many row the select returns and then a simple for... next adding the names to the string.

Any good examples on how to do this in a sql-server stored proc?

Thanks,

Trin

P.S. This is what I have pieced together this far:

CREATE PROCEDURE findnames

@.number int

AS

DECLARE @.instrument varchar(50)
DECLARE @.tempinstt varchar(10)

DECLARE medlemcursor CURSOR
FOR
SELECT [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

OPEN medlemcursor

FETCH NEXT FROM medlemcursor INTO @.tempinstt
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.instrument = @.instrument + @.tempinstt + '-'
FETCH NEXT FROM medlemcursor INTO @.tempinstt
END

CLOSE medlemcursor
DEALLOCATE medlemcursor

SELECT @.instrument
GO

Just doesn't seem to work, returns NULL, even though I've checked that the cursor SELECT statement actually returns data,--u have to intialize ur variable 'instrument' before appending other values.

CREATE PROCEDURE findnames

@.number int

AS

DECLARE @.instrument varchar(50)
DECLARE @.tempinstt varchar(10)
set instrument =''
DECLARE medlemcursor CURSOR
FOR
SELECT [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

OPEN medlemcursor

FETCH NEXT FROM medlemcursor INTO @.tempinstt
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.instrument = @.instrument + @.tempinstt + '-'
FETCH NEXT FROM medlemcursor INTO @.tempinstt
END

CLOSE medlemcursor
DEALLOCATE medlemcursor

SELECT @.instrument
GO|||Yeap, got it... and I just added a small substring addendum to cut off the trailing dash.

Mind boggling NULLs are..

Is it possible to call such a procedure within a select statement?

E.g. SELECT number, (EXEC findsnames number), city FROM names|||Just dump your cursor:CREATE PROCEDURE findnames(@.number int)
AS

declare @.instrument varchar(500) --varchar(50) seemed awfully short...

SELECT coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
GO|||u cannot call procedure from select statement,
instead of procedure , create a function,call that function from select statment.

blindman,
Poster wants to append record into a string,and ur query will not do that job.
select @.instrument returns NULL.|||Corrected code:CREATE PROCEDURE findnames(@.number int)
AS

declare @.instrument varchar(500) --varchar(50) seemed awfully short...

SELECT @.instrument = coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM DW.dbo.names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
GO

Copy/paste this to try it out:create table #Names([MCPS Kode] varchar(50), number int)
insert into #Names([MCPS Kode], number)
select 'Joe', 1
UNION
select 'James', 1
UNION
select 'Frank', 2
UNION
select 'Janis', 1
UNION
select 'Freda', 2
UNION
select 'Jeff', 1
UNION
select 'Fred', 2
UNION
select 'Foster', 2
UNION
select 'Jodi', 1

---------------------
declare @.instrument varchar(500) --varchar(50) seemed awfully short...
declare @.number int
set @.number = 1

SELECT @.instrument = coalesce(@.instrument + '-', '') + [MCPS Kode]
FROM #names(NOLOCK)
WHERE number = @.number

SELECT @.instrument
---------------------

drop table #Names

Output:James-Janis-Jeff-Jodi-Joe

Yes, it can be converted into a function if the user wishes.

Converting problem (Stored Procedure)

I need to link together string (NT-) and integer variable (1). The value of variable2 should be NT-1. I always got such an error message:

Conversion failed when converting the varchar value 'NT-1' to data type int.

DECLARE @.variable1 int;
SET @.variable1 = 1;

DECLARE @.variable2 varchar(10);
SET @.variable2 = CONVERT(varchar, 'NT-1')+ CONVERT(varchar, @.variable1);

i tried the code that you posted and do not encounter the conversion error.

However, you can simply the code and remove the convert for 'NT-1' as it is a string so you do not need to convert to varchar. And for the @.variable1, you might want to specify the varchar size or it will defaulted to a certain size. (cannot remember what is the default size)
SET @.variable2 = 'NT-1' + CONVERT(varchar(10), @.variable1)