flat file connection string

Hello,
i can't find the connection string (and the def of the options) to
access a flat file from SQL Server 2005.
I guess it is something like:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=MyFlatFile; Extended
Properties=... etc.etc
Can anyone help me?
Thx,
ChrisChris,
please have a look at this site - http://www.connectionstrings.com/
The flat file connection strings are there for ODBC and OLE DB.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

flat file connection string

Hello,
i can't find the connection string (and the def of the options) to
access a flat file from SQL Server 2005.
I guess it is something like:
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=MyFlatFile; Extended
Properties=... etc.etc
Can anyone help me?
Thx,
ChrisChris,
please have a look at this site - http://www.connectionstrings.com/
The flat file connection strings are there for ODBC and OLE DB.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Fixed Width Text Report

I'm trying to create a fixed width text report. I've created a function that
takes a string and a length and returns the string either truncated or padded
with spaces to the given length.
I then use url parameters to modify the CSV device information settings to
change the encoding to ascii, change the extension to txt and change the
FieldDelimiter to %1f (unicode symbol for some kind of field grouping or
something).
Things seems to properly but I don't like having to set the FieldDelimiter
to anything. I tried setting it to null by saying isnull=true but that
generates an error about referencing a null object. I read something that
said to make it an empty string but I can seem to be able to do that using
url parameters. I could try it programmatically but I would prefer using the
url.
Does anyone have any ideas?(The parent post is mine, I just changed my login)
I decided that setting the FieldDelimiter to %1f was not a good idea. I
did try to set the parameter to an empty string programmatically but it
just went to the default comma delimiter.
I've decided to just plug in the url encoded value of which stands
for a null ascii character. I don't know if this is the best solution
but I am going with it. Here is my final url:
http://localhost/ReportServer?/Devel/TestFile&rs:Format=CSV&rs:Command=Render&rc:Extension=txt&rc:NoHeader=true&rc:FieldDelimiter=&rc:Encoding=ascii
If anyone else comes up with any other ideas of how to use RS to create
fixed width text files I would be glad to hear them. I can't find
anything that explains a way of doing this. This would work perfect if
I could set the FieldDemlimiter parameter to nothing but it keeps going
to the default comma.
Thanks.
Gary

fixed point arithmetic for price calculations

I've got a price in euro as a string, which I can easily cast to a numeric SSIS data type e.g. R4, R8, DECIMAL, NUMERIC. And I've got the dollar/euro exchange rate stored in an SSIS variable of type DOUBLE, set to 1.28 for testing purposes. I want to multiply the two values and return the (dollar) result, rounded (not truncated) to 2 decimal places, as a string.

Here are some experiments I did in an SSIS expression editor:

(DT_WSTR, 10) (1.28 * 31.10) evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_R8) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 0) "31.10") evaluates to "39.68"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 1) "31.10") evaluates to "39.808"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 2) "31.10") evaluates to "39.8080"

(DT_WSTR, 10) (1.28 * (DT_DECIMAL, 3) "31.10") evaluates to "39.80800"

Of course, what I really want is "39.81", so I went on:

(DT_WSTR, 10) ((DT_DECIMAL, 0) (1.28 * (DT_R8) "31.10")) evaluates to "39"

(DT_WSTR, 10) ((DT_DECIMAL, 1) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

This looks promising! But:

(DT_WSTR, 10) ((DT_DECIMAL, 2) (1.28 * (DT_R8) "31.10")) evaluates to "39.8"

(DT_WSTR, 10) ((DT_DECIMAL, 3) (1.28 * (DT_R8) "31.10")) evaluates to "39.808"

Argh... How does one get a floating point value rounded to 2 decimal places?

(DT_NUMERIC, 6,2)(1.28 * 31.10) ?|||

Phil Brammer wrote:

(DT_NUMERIC, 6,2)(1.28 * 31.10) ?

Hmm... That didn't seem to work either.|||Use the ROUND() function. That will work for you.

ROUND(1.28 * 31.1 ,2)|||

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

I didn't think to try ROUND(numeric_expression, length) because the help text in the Expression Builder says that it returns an integer (regardless of the length parameter).

Thanks!

|||

Kevin Rodgers wrote:

(DT_WSTR, 10) ROUND(1.28 * 31.10, 2) indeed evaluates to "39.8100", which can simply be truncated.

Hmmm. I had to increase the string length from 10 to avoid a truncation error, so I changed it to 4000 which I think is the maximum for Unicode strings -- no worries. But more testing reveals that (DT_WSTR, 4000) ROUND(numeric_expression, 2) sometimes returns a value with fewer than 2 digits after the decimal point e.g. "16" instead of "16.00" or "133.5" insead of "133.50"'.

Here's what I'm using to ensure that there is a decimal point followed by 2 digits in the result:

FINDSTRING(usd_price, ".", 1) > 0 ? SUBSTRING(usd_price + "00", 1, FINDSTRING(usd_price, ".", 1) + 2) : usd_price + ".00"

Fix Replace function plz!

Hello!
I need to replace a string which starts from "DBX:" and ends to "Addr1:"
with a word "APPLE". The cloumn is Name in #temp table. I am captuting
it correctly but not using the replace function the right way. It is
replacing eveywhere which I dont want.
Thanks for your help.
create table #temp
(ID int, Name varchar(80))
insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
Addr1: 1234')
insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
I am getting this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: APPLE DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
And I want this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
*** Sent via Developersdex http://www.examnotes.net ***Test,
Try:
SELECT SUBSTRING([NAME],7,DATALENGTH([NAME])) AS 'NAME'
FROM #TEMP
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I need to replace a string which starts from "DBX:" and ends to "Addr1:"
> with a word "APPLE". The cloumn is Name in #temp table. I am captuting
> it correctly but not using the replace function the right way. It is
> replacing eveywhere which I dont want.
> Thanks for your help.
> create table #temp
> (ID int, Name varchar(80))
> insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
> Addr1: 1234')
> insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
> insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
> select
> --ID,
> --Captured = substring(Name, charindex('DBX:', Name) + 4,
> --charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
> Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
> charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
> from #temp
> I am getting this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: APPLE DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> And I want this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Nothing wrong with the replace function, basically what you asked for was
this:
REPLACE('Name: John Doe DBX: John Doe Addr1: 9999', 'John Doe', 'APPLE')
This is what you're looking for I think:
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
--Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE '),
Correct = Left(Name, charindex('DBX:', Name) + 3) +
replace(substring(Name, charindex('DBX:', Name) + 4, LEN(Name)),
substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
By the way, wouldn't it be much a bit cleaner to have separate columns for
Name, DBX and Addr1?
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
> Hello!
> I need to replace a string which starts from "DBX:" and ends to "Addr1:"
> with a word "APPLE". The cloumn is Name in #temp table. I am captuting
> it correctly but not using the replace function the right way. It is
> replacing eveywhere which I dont want.
> Thanks for your help.
> create table #temp
> (ID int, Name varchar(80))
> insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
> Addr1: 1234')
> insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
> insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
> select
> --ID,
> --Captured = substring(Name, charindex('DBX:', Name) + 4,
> --charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
> Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
> charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
> from #temp
> I am getting this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: APPLE DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> And I want this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks but this is not what I am looking for. I need this:
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
The APPLE has been updated in between the DBX: and Addr1: in the Name
column. My SQL is not working in John Doe case but John Doe is in two
places and I want to see the replacement in one place only (which is
from DBX: to Addr1:).
Hope this information helps.
*** Sent via Developersdex http://www.examnotes.net ***|||I don't know what to tell you, but my query returned me this (which is what
you say you're looking for) :
Name: Smith Black Jones DBX: APPLE Addr1: 1234
Name: John Doe DBX: APPLE Addr1: 9999
Name: Batman DBX: APPLE Addr1: 1234
Copy and paste this (just to verify that there wasn't a copy / paste mistake
when you ran it last time) :
___
create table #temp
(ID int, Name varchar(80))
insert into #temp values(23, 'Name: Smith Black Jones DBX: Smith Jones
Addr1: 1234')
insert into #temp values(27, 'Name: John Doe DBX: John Doe Addr1: 9999')
insert into #temp values(25, 'Name: Batman DBX: Robin Addr1: 1234')
select
--ID,
--Captured = substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4),
--Final = replace(Name, substring(Name, charindex('DBX:', Name) + 4,
--charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE '),
Correct = Left(Name, charindex('DBX:', Name) + 3) +
replace(substring(Name, charindex('DBX:', Name) + 4, LEN(Name)),
substring(Name, charindex('DBX:', Name) + 4,
charindex('Addr1:', Name) - charindex('DBX:', Name)-4), ' APPLE ')
from #temp
___
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23YMaCDa1FHA.3816@.TK2MSFTNGP14.phx.gbl...
> Thanks but this is not what I am looking for. I need this:
> Name: Smith Black Jones DBX: APPLE Addr1: 1234
> Name: John Doe DBX: APPLE Addr1: 9999
> Name: Batman DBX: APPLE Addr1: 1234
> The APPLE has been updated in between the DBX: and Addr1: in the Name
> column. My SQL is not working in John Doe case but John Doe is in two
> places and I want to see the replacement in one place only (which is
> from DBX: to Addr1:).
> Hope this information helps.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Sorry test...misread the data requirements prior to posting this query.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:u6EPH3Z1FHA.2792@.tk2msftngp13.phx.gbl...
> Test,
> Try:
> SELECT SUBSTRING([NAME],7,DATALENGTH([NAME])) AS 'NAME'
> FROM #TEMP
>
> HTH
> Jerry
> "Test Test" <farooqhs_2000@.yahoo.com> wrote in message
> news:OwySQyZ1FHA.3864@.TK2MSFTNGP12.phx.gbl...
>|||Thanks ESPNSTI! It works fine! Sorry I missed the solution in your
initial posting. Thanks!
*** Sent via Developersdex http://www.examnotes.net ***|||ESPN! I have one more condiction to take care of which is to replace
anything after "Name:" and before "DBX:" with a word ' XXXX '.
so I tried it but again my SQL is not working for John Doe bc it is in
two place.
Now,
select replace(Name, substring(Name, charindex(':', Name)+2,
charindex('DBX:', Name)- charindex(':', Name)-2), ' XXXXX ')
from #temp
I am getting this:
Name: XXXXX DBX: Smith Jones Addr1: 1234
Name: XXXXX DBX: XXXXX Addr1: 9999
Name: XXXXX DBX: Robin Addr1: 1234
An I want this:
Name: XXXXX DBX: Smith Jones Addr1: 1234
Name: XXXXX DBX: John Doe Addr1: 9999
Name: XXXXX DBX: Robin Addr1: 1234
I would appreciate your help! Thanks.
*** Sent via Developersdex http://www.examnotes.net ***

First time writing, string manipulation?

I'm hoping someone can help! Im using sql2000, and I am attempting to capitalize every 1st letter of a word in a column.

For Example:
"GOLF IS FUN,BOWLING IS GREAT"

What Id like to get as my results:

"Golf is fun, Bowling is great"

Trying to figure out the syntax to get the character after the comma to have a space then capital "B" Thought I could use a charindex but just cant seem to get it.Hi

Welcome to the forum :D

By coincidence I was tootling around Vyas's site earlier today and noticed his proper case function.
http://vyaskn.tripod.com/code.htm#propercase

I am confess I have not used it in anger. I would not be surprised if an extended sproc might be a little faster. There is one optimisation I would do in the first place - one single SELECT @.var = 'this', @.other_var = 'that' rather than multiple SETs.

Anyhoo - see how you get on.

HTH|||Oops - just noticed you are not after propercase. Check out PATINDEX for your requirement e.g. PATINDEX('%, %', MyCol)|||CREATE Proc sp_Parsing( @.String Varchar(255))
AS
Declare @.Recepient Varchar(100)
Declare @.Comma Int
DEclare @.OutPut varchar(8000)
create Table #Test(Names Varchar(55))

set nocount on

While @.String is not null
Begin
Set @.Comma = Patindex('%,%',@.String)
if @.Comma <> 0
Begin
Select @.recepient = Ltrim(Rtrim(Substring(@.string,1,(@.Comma - 1))))
Select @.String = Ltrim(Rtrim(Substring(@.String,(@.Comma + 1),255)))

Insert Into #Test
Values(@.Recepient)
Continue
End
Else
Begin
Select @.recepient = @.string
Select @.String = null
Insert Into #Test
Values(@.Recepient)
Break
End
End
set @.OutPut = ''
Select @.OutPut = @.OutPut + Upper(substring(Names,1,1)) + Lower(Substring(Names,2, Len(Names))) + ',' from #test
Select substring(@.OutPut,1, len( @.OutPut) -1)
Drop Table #Test
GO

Exec sp_Parsing 'GOLF IS FUN,BOWLING IS GREAT'|||string manipulation is pretty much always better done in compiled code, not sql, as pootle suggests.

what sql is good at is set based operations.

First 4 symbols of a string truncated when inserting typed XML

Dear SQL experts, is it a bug or I'm understanding something wrong?

Please look at the script below. Is it a bug? If yes, when can we expect it to be fixed and released? Are there any workarounds apart from just not using xsd:string type?

IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[TestSchema]')

DROP XML SCHEMA COLLECTION [dbo].[TestSchema]

CREATE XML SCHEMA COLLECTION [dbo].[TestSchema] AS N'

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="TEST" elementFormDefault="qualified">

<xsd:element name="Root" type="xsd:string"/>

</xsd:schema>'

GO

DECLARE @.doc xml (TestSchema)

SET @.doc = ''

-- Inserting string of 64 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234</Root>

into (/)')

-- Inserting string of 70 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234567890</Root>

into (/)')

-- Inserting string of 10 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a</Root>

into (/)')

SELECT @.doc

Here is the result:

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234</Root>

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234567890</Root>

<Root xmlns="TEST">123456789a</Root>

You can see that first two strings are truncated of 4 characters at the start.

The problem occurs in case if:

1. The variable or column is typed XML.

2. Type of the string is xsd:string or derived.

3. The string is longer than 63 characters (no matter how much longer, what is truncated are alwasy first 4 characters).

Thanks a lot in advance!

This is known issue. We have fixed this bug in Yukon SP2.

Jinghao Liu

|||

Hello Jinghao,

Thanks for the prompt answer!

And do you know approximate term - when the SP2 is going to be released to public?

Best regards,

Yuriy

|||

You can ask your SQL support contact about the schedule of Yukon Service Pack 2. They usually know better than us about the date. If you are important customer, you might get the early version of it.

|||Thanks, we'll try to.

First 4 symbols of a string truncated when inserting typed XML

Dear SQL experts, is it a bug or I'm understanding something wrong?

Please look at the script below. Is it a bug? If yes, when can we expect it to be fixed and released? Are there any workarounds apart from just not using xsd:string type?

IF EXISTS (SELECT * FROM sys.xml_schema_collections c, sys.schemas s WHERE c.schema_id = s.schema_id AND (quotename(s.name) + '.' + quotename(c.name)) = N'[dbo].[TestSchema]')

DROP XML SCHEMA COLLECTION [dbo].[TestSchema]

CREATE XML SCHEMA COLLECTION [dbo].[TestSchema] AS N'

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="TEST" elementFormDefault="qualified">

<xsd:element name="Root" type="xsd:string"/>

</xsd:schema>'

GO

DECLARE @.doc xml (TestSchema)

SET @.doc = ''

-- Inserting string of 64 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234</Root>

into (/)')

-- Inserting string of 70 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a123456789b123456789c123456789d123456789e123456789f1234567890</Root>

into (/)')

-- Inserting string of 10 characters

SET @.doc.modify('declare default element namespace "TEST";

insert <Root xmlns="TEST">123456789a</Root>

into (/)')

SELECT @.doc

Here is the result:

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234</Root>

<Root xmlns="TEST">56789a123456789b123456789c123456789d123456789e123456789f12341234567890</Root>

<Root xmlns="TEST">123456789a</Root>

You can see that first two strings are truncated of 4 characters at the start.

The problem occurs in case if:

1. The variable or column is typed XML.

2. Type of the string is xsd:string or derived.

3. The string is longer than 63 characters (no matter how much longer, what is truncated are alwasy first 4 characters).

Thanks a lot in advance!

This is known issue. We have fixed this bug in Yukon SP2.

Jinghao Liu

|||

Hello Jinghao,

Thanks for the prompt answer!

And do you know approximate term - when the SP2 is going to be released to public?

Best regards,

Yuriy

|||

You can ask your SQL support contact about the schedule of Yukon Service Pack 2. They usually know better than us about the date. If you are important customer, you might get the early version of it.

|||Thanks, we'll try to.