Extract Data From A Column

Hi ,
I have to extract City,State and Zip Code from the below
column and insert it separately in 3 columns. how can i
write my select statements so i can get
City=North Bergen
State=NJ
Zip =07057
Site
--
NORTH BERGEN, NJ, 07057
Springfield, IL, 62704
MANASQUAN, NJ, 08736
BLOOMINGTON, MN, 55425As long as you have a uniform delimiter, you can use the following proc to
parse the tokens and load them appopriately.
You tweak the stored proc to handle the tokens as you wnat.
--
HTH
Satish Balusa
Corillian Corp.
Create Procedure sp_ParseArrayAndLoadTable ( @.Array varchar(1000),
@.Separator char(1) ,
@.LoadTableName sysname OUT
)
AS
BEGIN
SET NOCOUNT ON
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
DECLARE @.separator_position int -- This is used to locate each separator
character
DECLARE @.array_value varchar(1000) -- this holds each array value as it is
returned
-- For my loop to work I need an extra separator at the end. I always look
to the
-- left of the separator character for each array value
SET @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @.separator + '%' , @.array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @.separator_position = PATINDEX('%' + @.separator + '%' , @.array)
SELECT @.array_value = LEFT(@.array, @.separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @.array_value holds the value of this element of the array
-- Do the job whatever you wanted to do
SELECT Array_Value = @.array_value
-- This replaces what we just processed with and empty string
SELECT @.array = STUFF(@.array, 1, @.separator_position, '')
END
SET NOCOUNT OFF
END
GO
"Mohamadi.Slatewala@.wellsfargo.com" <anonymous@.discussions.microsoft.com>
wrote in message news:22f301c3e12f$9d1dbc30$a301280a@.phx.gbl...
> Hi ,
> I have to extract City,State and Zip Code from the below
> column and insert it separately in 3 columns. how can i
> write my select statements so i can get
> City=North Bergen
> State=NJ
> Zip =07057
> Site
> --
> NORTH BERGEN, NJ, 07057
> Springfield, IL, 62704
> MANASQUAN, NJ, 08736
> BLOOMINGTON, MN, 55425
>

Extract Data From A Column

Hi ,
I have to extract City,State and Zip Code from the below
column and insert it separately in 3 columns. how can i
write my select statements so i can get
City=North Bergen
State=NJ
Zip =07057
Site
--
NORTH BERGEN, NJ, 07057
Springfield, IL, 62704
MANASQUAN, NJ, 08736
BLOOMINGTON, MN, 55425As long as you have a uniform delimiter, you can use the following proc to
parse the tokens and load them appopriately.
You tweak the stored proc to handle the tokens as you wnat.
--
HTH
Satish Balusa
Corillian Corp.
Create Procedure sp_ParseArrayAndLoadTable ( @.Array varchar(1000),
@.Separator char(1) ,
@.LoadTableName sysname OUT
)
AS
BEGIN
SET NOCOUNT ON
-- @.Array is the array we wish to parse
-- @.Separator is the separator charactor such as a comma
DECLARE @.separator_position int -- This is used to locate each separator
character
DECLARE @.array_value varchar(1000) -- this holds each array value as it is
returned
-- For my loop to work I need an extra separator at the end. I always look
to the
-- left of the separator character for each array value
SET @.array = @.array + @.separator
-- Loop through the string searching for separtor characters
WHILE PATINDEX('%' + @.separator + '%' , @.array) <> 0
BEGIN
-- patindex matches the a pattern against a string
SELECT @.separator_position = PATINDEX('%' + @.separator + '%' , @.array)
SELECT @.array_value = LEFT(@.array, @.separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @.array_value holds the value of this element of the array
-- Do the job whatever you wanted to do
SELECT Array_Value = @.array_value
-- This replaces what we just processed with and empty string
SELECT @.array = STUFF(@.array, 1, @.separator_position, '')
END
SET NOCOUNT OFF
END
GO
"Mohamadi.Slatewala@.wellsfargo.com" <anonymous@.discussions.microsoft.com>
wrote in message news:22f301c3e12f$9d1dbc30$a301280a@.phx.gbl...

quote:

---

> Hi ,
> I have to extract City,State and Zip Code from the below
> column and insert it separately in 3 columns. how can i
> write my select statements so i can get
> City=North Bergen
> State=NJ
> Zip =07057
> Site
> --
> NORTH BERGEN, NJ, 07057
> Springfield, IL, 62704
> MANASQUAN, NJ, 08736
> BLOOMINGTON, MN, 55425
>

---

Extract a complete XML section and sub sections

Ive been using select statments to get information from each 'section'.
example below shows only for the Header section.
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
the following section of the file has sub sections contained within the
Header section, Is there a simple way to return all the data values in one
SQL ?
- <Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>
You can specify relative XPaths for the columns in the subelements, as shown
below. Is that what you mean?
DECLARE @.TESTXML nvarchar(2000)
DECLARE @.hDoc integer
SET @.TESTXML =
'<Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>'
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header', 1)
with
(reportType varchar(10),
reportNumber VarChar(6),
batchNumber varchar(6),
reportSequenceNumber varchar(6),
userNumber varchar(6),
ProducedOnTime nvarchar(10) 'ProducedOn/@.time',
ProducedOnDate nvarchar(20) 'ProducedOn/@.date',
ProcessingDate nvarchar(20) 'ProcessingDate/@.date' )
EXEC sp_xml_removedocument @.hDoc
Cheers,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:4CD137BC-09E8-42A8-BC86-6C6991EA979C@.microsoft.com...
Ive been using select statments to get information from each 'section'.
example below shows only for the Header section.
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.TESTXML
Select * from OpenXML(@.hDoc, '//Header') with
(reportType varchar(10), reportNumber VarChar(6), batchNumber
varchar(6),
reportSequenceNumber varchar(6), userNumber varchar(6) )
EXEC sp_xml_removedocument @.hDoc
the following section of the file has sub sections contained within the
Header section, Is there a simple way to return all the data values in one
SQL ?
- <Header reportType="REFT2013" reportNumber="14685" batchNumber="023"
reportSequenceNumber="000760" userNumber="948053">
<ProducedOn time="17:31:38" date="2004-09-27" />
<ProcessingDate date="2004-09-28" />
</Header>
sql

Exterpise Manager Select Export (ASCII,Excel,Access)?

Hi All. A client needs to send me some sample data. He has insisted he can query the table in Enterprise Manager via a simple select... "Select * from Table1"...

Now I need to get the data in some simple form (ASCII, Excel, Access, etc.) sent to me.

Can someone please provide me the info so I can pass it on for him to query a table from Enterprise Manager and "export it" to a simple file so I can receive it.

ANY THOUGHTS would be helpfull and GREATLY Appreciated!

Thanks.

BillIf this is a one timer I would go for "Tools -> Data Transformation Services -> Export Data".

// Pati|||Hey Pati... It may just be a one timer... but if the data looks good, it may be more frequent. it turns out some other process is taking records from this table, and may be removing them... Part of the reason we are trying to get some snapshots of the data.

I'm trying not to write an app until I know if we need this data.

Is it really simple to use to do the menu picks? Doesn't seem like the end user is very experienced, nor am I on Sql Server.

Thanks for the thoughts.

Bill|||If you think that you will need the same procedure again then you can save the DTS package for further use and then schedule it to run as desired.
...or then for another approach you could automate everything with scripts/scheduled jobs.

But as you're saying that you don't have much experience in SQL Server and that this might just be a one off solution then I would stick to DTS.

// Patisql

Extended Stored Procedure

I have written extend stored procedure using dblib.
declare @.p1 int
xp_myProc 'select * from table', @.p1 output.
This extended procedure exeute the query and generate the
xml. Using xp_prepardocument it generate the handle.
The output handle i want to use it another procedure to
read the value.
<anonymous@.discussions.microsoft.com> wrote in message
news:717501c47625$ef3aeb60$a401280a@.phx.gbl...
>I have written extend stored procedure using dblib.
> declare @.p1 int
> xp_myProc 'select * from table', @.p1 output.
> This extended procedure exeute the query and generate the
> xml. Using xp_prepardocument it generate the handle.
> The output handle i want to use it another procedure to
> read the value.
Are you having problems with the handle no longer being valid?
Bryant
|||Note that the handle is valid for the duration of a session. If your xp runs
in a different session context, the handle cannot be reused...
Best regards
Michael
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:%23mDquOsdEHA.712@.TK2MSFTNGP09.phx.gbl...
> <anonymous@.discussions.microsoft.com> wrote in message
> news:717501c47625$ef3aeb60$a401280a@.phx.gbl...
> Are you having problems with the handle no longer being valid?
> --
> Bryant
>

extended character search

Is there an easy way to perform a SELECT where you have

blah LIKE 'asdf'

but instead of just returning all the asdf's, it also looks for sdf,
sdf, sdf, etc?Right now, this is what I'm doing: replacing each accent letter (a, e,
i, etc) with a string of possible accents. For example

A search for 'GONCALTRONICA' actually searches with:

'G[o][n][c][a]LTR[o][n][i][c][a]'

and returns the correct row with 'GONALTRNICA' in the field. I'm
thinking there has to be a better way to have a case insensitive
search. Maybe an option somewhere?

Thanks|||"PepperellMA" <andy@.pepperell.net> wrote in message
news:1106163716.326409.20730@.c13g2000cwb.googlegro ups.com...
> Right now, this is what I'm doing: replacing each accent letter (a, e,
> i, etc) with a string of possible accents. For example
> A search for 'GONCALTRONICA' actually searches with:
> 'G[o][n][c][a]LTR[o][n][i][c][a]'
> and returns the correct row with 'GONALTRNICA' in the field. I'm
> thinking there has to be a better way to have a case insensitive
> search. Maybe an option somewhere?
> Thanks

You can specify an accent-insensitive collation in your queries:

create table #pep (col1 nvarchar(100))
insert into #pep select 'GONALTRNICA'

-- Returns 0 rows
select * from #pep
where col1 = 'GONCALTRONICA'
-- Returns 1 row
select * from #pep
where col1 = 'GONCALTRONICA' collate SQL_Latin1_General_CP850_CI_AI

Simon|||I am getting the error "Line 7: Incorrect syntax near 'collate'." Maybe
I am using an out of date version of sql server that doesn't support
COLLATE (Microsoft SQL Server 7.00 - 7.00.623) ?

> You can specify an accent-insensitive collation in your queries:
> create table #pep (col1 nvarchar(100))
> insert into #pep select 'GONALTRNICA'
> -- Returns 0 rows
> select * from #pep
> where col1 = 'GONCALTRONICA'
> -- Returns 1 row
> select * from #pep
> where col1 = 'GONCALTRONICA' collate SQL_Latin1_General_CP850_CI_AI
>
> Simon|||"PepperellMA" <andy@.pepperell.net> wrote in message
news:1106165927.837051.321340@.f14g2000cwb.googlegr oups.com...
> I am getting the error "Line 7: Incorrect syntax near 'collate'." Maybe
> I am using an out of date version of sql server that doesn't support
> COLLATE (Microsoft SQL Server 7.00 - 7.00.623) ?

COLLATE is only available in SQL 2000 - please always mention which version
you have (and to be fair, I shouldn't have assumed you had 2000). In SQL 7,
the sort order is fixed at install time, so you would have to rebuild the
master database to change it.

If it's important enough to you, it might be worth rebuilding (but of course
you run the risk of breaking other code), setting up an additional MSSQL
installation for insensitive searches, or upgrading to 2000, otherwise
you're probably stuck with writing code as you've already done. Fulltext
searching is always case and accent sensitive, so unfortunately that's not
an option either.

By the way, your build version indicates you haven't installed any
servicepacks (SP4 is the latest one for SQL 7).

Simon

expression syntax

How would I do a select from a container using the previous container's starttime as a condition in the variable?

select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + @.[System::ContainerStartTime] + "

Thanks,

Phil

Tackett,

I dont know the requirements of your project, but try to run the SQL statment inside a OLEDB command and define @.[System::ContainerStartTime] as parameter.

For example, you can create your sql statment as stored procedure in database and inside OLEDB Command write in SQL command :

EXEC SP_NAMESTOREDPROCEDURE ?

And in the second tab link the parameter to your system variable.

If you want i can show you an example.

Regards,

Pedro

|||

Isn't there a way to refer to the containerstarttime in a execute sql task via expression syntax? That's what I'm trying to do.

select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= ' + (DT_STR,50)@.[System::ContainerStartTime] + '

|||

Sorry Tacket, I was "sleeping"...

Try this post to help your problem...

Tomorrow morning I will think better about this.

Regards,

Pedro

|||

NP. Actually I need to do a BETWEEN where time between 'previous container' and 'current container'. I'm sure that involves system variables and namespaces .

Thanks,

Phil

|||

Check this:

http://blogs.conchango.com/jamiethomson/archive/2005/06/11/1593.aspx

|||

Ok, so I need to create a variable and then evaluate it as an expression and put the code in there, correct? NP, accept, I can't get it to evaluate in the "evaluate as expression" part. Here's what I have so far in the expression builder and it's not working..

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50)@.[System::ContainerStartTime] + "

Any help?

Thanks,

Phil

|||

One thing I noticed is that your DT_STR type conversion is missing the thrid parameter (Code Page)

(DT_STR,50) should be (DT_STR,50,1252) -- Assuming you are using the standard 1252 code page.

|||

TITLE: Expression Builder

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

ADDITIONAL INFORMATION:

Attempt to parse the expression ""select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50,1252)@.[System::ContainerStartTime] + "" failed. The token """ at line number "5", character number "68" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

(Microsoft.DataTransformationServices.Controls)

BUTTONS:

OK

Here is my code in the variable's expression builder:

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] >= " + (DT_STR,50,1252)@.[System::ContainerStartTime] + "

|||Well I took off the + " and now it's working |||

Working syntax for those who care:

"select publisher,publisher_db,subscriber,subscriber_db,article
from msdb.dbo.sysreplicationalerts
where error_id <> 0
and alert_error_code = 20574
and [time] between '" + (DT_STR,50,1252)@.[System::ContainerStartTime] + "' and '" + (DT_STR,50,1252)GETDATE() + "'"

Express SP1 No version #

I downloaded the SQL Server 2005 Express SP1 product today. If I right click SQLEXPR.EXE, select Properties, and select the Version tab, the File Version value is 0.0.0.0. The version listed for the GA product was 9.0.1399.6.

Is this a problem to be concerned about?

Hi,

that shouldn′t bother you, smae for me here. Look at the digital signature date, if it is sometime in April 2006 signed (as mine is) this is the right one for you. Starting the File will also come up with a EULA shwoing the Service pack Level.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

SQLEXPR.EXE is a compressed archive, you'll find version numbers on the actual files that are installed.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

Express SP1 No version #

I downloaded the SQL Server 2005 Express SP1 product today. If I right click SQLEXPR.EXE, select Properties, and select the Version tab, the File Version value is 0.0.0.0. The version listed for the GA product was 9.0.1399.6.

Is this a problem to be concerned about?

Hi,

that shouldn′t bother you, smae for me here. Look at the digital signature date, if it is sometime in April 2006 signed (as mine is) this is the right one for you. Starting the File will also come up with a EULA shwoing the Service pack Level.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

SQLEXPR.EXE is a compressed archive, you'll find version numbers on the actual files that are installed.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

exporting to excel "no dlls found"

From terminal services i am trying to export a crystal report 8.5 to an excel file. When i click on export and then select excel an error pops up saying "no dll's found" and that is it. The funny thing is, I have crystal reports 8.5 loaded to my workstation and it works just fine. I am not using terminal services though, (I have crystal loaded locally) but even when i try it from the server that is running terminal services, i get the same error msg. I am almost positive that crystal reports 8.5 was loaded the exact same way to my machine as it was to the server. Does anyone know of a way that i can fix this problem.Make sure the Export dlls are installed in: C:\WINNT\Crystal (or similar if you're using a different OS)

Also, keep in mind that Crystal requires 2 dlls for export, one for the Export Type (pdf, Excel, etc) and one for the Destination (app, disk, etc). Look for the runtime.hlp file that came with Crystal. It will tell you which files you need to have installed for the type of Exporting you want to do.|||You wouldn't happen to know the name of the 2 dll files, I checked crystal reports support site and it says to Copy the export DLLs to the Windows\system directory. The export DLLs are the u2*.dll files. When i do a search on the install disk i find several u2*.dll files here is a list of them.
I'm guesing it is u2fxls.dll, u2fxml.dll but i want to be sure.

u252000.dll
u25dts.dll
u2dapp.dll
u2ddisk.dll
u2dmapi.dll
u2dnotes.dll
u2dpost.dll
u2dvim.dll
u2fcr.dll
u2fdif.dll
u2fhtml.dll
u2fodbc.dll
u2frdef.dll
u2frec.dll
u2fsepv.dll
u2ftext.dll
u2fwks.dll
u2fwordw.dll
u2fxls.dll
u2fxml.dll
u22l2000.dll
u2lcom.dll
u2ldts.dll
u2lexch.dll
u2lfinra.dll
u2lsamp.dll|||Accroding to runtime.hlp for Crystal Reports 8.5:

Destination Files:

FILE LOCATION DESCRIPTION
---------------------------
U2DAPP.DLL \WINDOWS\CRYSTAL Application destination
U2DDISK.DLL \WINDOWS\CRYSTAL Disk file destination
U2DMAPI.DLL \WINDOWS\CRYSTAL MAPI format (Microsoft Mail, Microsoft Exchange)
U2DNOTES.DLL \WINDOWS\CRYSTAL Lotus Domino destination
U2DPOST.DLL \WINDOWS\CRYSTAL Microsoft Exchange Public Folders
U2DVIM.DLL \WINDOWS\CRYSTAL VIM destination

Export Formats:

FILE LOCATION DESCRIPTION
--------------------------
U2FCR.DLL \WINDOWS\CRYSTAL Crystal Reports format
U2FDIF.DLL \WINDOWS\CRYSTAL DIF format
U2FHTML.DLL \WINDOWS\CRYSTAL HTML format See HTML under Additional Components for additional runtime information.
U2FODBC.DLL \WINDOWS\CRYSTAL ODBC data source
CRXF_PDF.DLL \WINDOWS\CRYSTAL PDF format Replaces U2FPDF.DLL from version 8.See Page Ranged Export under Additional Components for additional runtime information.
U2FRDEF.DLL \WINDOWS\CRYSTAL Report Definition format
U2FREC.DLL \WINDOWS\CRYSTAL Record format
CRXF_RTF.DLL \WINDOWS\CRYSTAL Rich Text FormatReplaces U2FRTF.DLL from version 8 and earlier.See Page Ranged Export under Additional Components for additional runtime information.
U2FSEPV.DLL \WINDOWS\CRYSTAL Comma Separated Values format
U2FTEXT.DLL \WINDOWS\CRYSTAL Text format
U2FWKS.DLL \WINDOWS\CRYSTAL Lotus 1-2-3 format
U2FWORDW.DLL \WINDOWS\CRYSTAL Microsoft Word for Windows format
U2FXML.DLL \WINDOWS\CRYSTAL XML format
U2FXLS.DLL \WINDOWS\CRYSTAL Microsoft Excel format|||On my machine the export button works, so i copied all the u2*.dll files from my machine and pasted them in the winnt\crystal folder on the server and the export button still doesn't work, it gives the same error msg. "no dll's found"
Any other ideas would be greatly appreicated. Thank you.|||Try looking up the exact error message and/or error number on Google (or another search engine) or Crystal's Website (http://support.businessobjects.com/search/default.asp).|||hi guys!

just want to ask if u can give an idea how to fix exporting report in vb app...
im using an vb app that used crystal report 8.5 and installed in several client pc... but if im exporting the files it doesnt work... nothing was happen... do i need to install also a crystal report software in every pc to install the required dll's?

thank you very much!|||benjz -

Did you read this post? You have to have the correct export dlls installed on the client machine in order for Export to work. (To install the dll, just copy and paste it into the directory listed above. Also, make sure you include those dlls in your setup file as they may have dependencies that I don't about)

Also, if you post your own question (instead of adding it to an existing post), you'll get a better response. (Click the 'New Thread' button on the main page of this forum.)

Exporting SQL Server 2005 data into formatted XML file

Hello,

I currently have a stored procedure in my SQL Server 2005 database that contains a simple SELECT FOR XML statement. I would like to call this stored procedure in C# and use C# to write an XML file such that when I open it in Visual Studio for editing it looks properly nested (rather than all on one line). Is there a quick way to do this?

I would like the file to look something like the following when opened:

Code Snippet

<Element1>

<Element2>

<Element3>Text</Element3>

<Element4>Text</Element4>

</Element2>

</Element1>

Thanks.

Create a DataSet put your table in it and call the DataSet.ReadXML method, if you want formatting you can use a Repeater for Webform. Try the link below to get started.

http://msdn2.microsoft.com/en-us/library/360dye2a.aspx

|||Thanks for your reply! I will look into that.