Extract number from a string

Hi All,
I have a table in which has a Notes field.
Each of these notes field has a phone number - eg. "Please provide
number 1234 to user XYZ."
I need to extract this number from each of the Notes field.
Can anybody tell me how to extract a number from a string?...
TIA!!!
Hello, snigs
If you want to extract the first number from a string and the number
does not contain any punctuation in it, you can try something like
this:
SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
ISNULL(NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(Notes,
PATINDEX('%[0-9]%',Notes) ,8000)),0)-1,8000)) FROM YourTable
If you want to extract all the numbers from a string, including any
punctuation found inside the number, you can try something like this:
SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
LEN(Notes)-NULLIF(PATINDEX('%[0-9]%', REVERSE(RTRIM(Notes))),0)
-NULLIF(PATINDEX('%[0-9]%',Notes),0)+2) FROM YourTable
Razvan
PS. With this occasion, I would like to submit my two entries for the
"most unreadable query of the month" contest ;)
|||On 6 Dec 2005 12:07:18 -0800, Razvan Socol wrote:

>SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
>ISNULL(NULLIF(PATINDEX('%[^0-9]%', SUBSTRING(Notes,
>PATINDEX('%[0-9]%',Notes) ,8000)),0)-1,8000)) FROM YourTable

>SELECT SUBSTRING(Notes, NULLIF(PATINDEX('%[0-9]%',Notes),0),
>LEN(Notes)-NULLIF(PATINDEX('%[0-9]%', REVERSE(RTRIM(Notes))),0)
>-NULLIF(PATINDEX('%[0-9]%',Notes),0)+2) FROM YourTable

>PS. With this occasion, I would like to submit my two entries for the
>"most unreadable query of the month" contest ;)
Hi Razvann,
Month, year, century - you win them all! ;-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

extract non-unique records from a table

Can anyone pls help me with any SQL syntax / logic of extracting only the
non-unique records from an SQL table ?
Thanks
ShekharIf i understand correctly you're after a query that returns the duplicates.
If so give this a try :-
SELECT Col001, COUNT(*) FROM tablename
GROUP BY Col001
HAVING COUNT(*) > 1
This will return any Col001 that are duplicate
HTH. Ryan
"Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
> Can anyone pls help me with any SQL syntax / logic of extracting only the
> non-unique records from an SQL table ?
> Thanks
> Shekhar|||gr8, Thanks Ryan, this worked
shekhar
"Ryan" wrote:

> If i understand correctly you're after a query that returns the duplicates
.
> If so give this a try :-
> SELECT Col001, COUNT(*) FROM tablename
> GROUP BY Col001
> HAVING COUNT(*) > 1
> This will return any Col001 that are duplicate
> --
> HTH. Ryan
>
> "Shekhar Gupta" <ShekharGupta@.discussions.microsoft.com> wrote in message
> news:38DC7E2D-051B-4573-9C9A-5B6164F99E78@.microsoft.com...
>
>

Extract MonthName

Is there a way to get the 3 digit month name like Dec from GETDATE() or any
date?SELECT Left(DateName(m, <DateColumn> ),3) FROM <TableName>
"Scott" <sbailey@.mileslumber.com> wrote in message
news:echrOus$FHA.3104@.TK2MSFTNGP15.phx.gbl...
> Is there a way to get the 3 digit month name like Dec from GETDATE() or
> any date?
>|||Scott,
Just to cover all bases, note that the short month is not
necessarily three characters long, if one allows the possibility
of any language setting. If you need the "short month", regardless
of its length, you can parse it out of
select shortmonths from master..syslanguages where langid = @.@.langid
or you can get it indirectly this way:
select
left(
convert(nvarchar(30),getdate(),9),
charindex(space(1),convert(varchar,getda
te(),9))-1) as ShortMonth
Steve Kass
Drew University
Scott wrote:

>Is there a way to get the 3 digit month name like Dec from GETDATE() or any
>date?
>
>|||SELECT CONVERT(CHAR(3), DATENAME(MONTH, GETDATE()))
"Scott" <sbailey@.mileslumber.com> wrote in message
news:echrOus$FHA.3104@.TK2MSFTNGP15.phx.gbl...
> Is there a way to get the 3 digit month name like Dec from GETDATE() or
> any date?
>

Extract lower case characters

I need to split a string in two if there are lowercase characters at
the end of it.

For example:
'AAPLpr' becomes 'AAPL' and 'pr'
'Ta' becomes 'T' and 'a'
'MSFT' becomes 'MSFT' and ''
'TAPA' becomes 'TAPA' and ''

I am using SQL 2000. I read about "collate Latin1_General_CS_AS" but
not sure if I can use that outside of a select statement.

Thank you in advance for any help.You can specify a case-sensitive collation either on the column definition:

CREATE TABLE SomeTable (x VARCHAR(6) COLLATE Latin1_General_CS_AS PRIMARY
KEY)

INSERT INTO SomeTable VALUES ('AAPLpr')
INSERT INTO SomeTable VALUES ('Ta')
INSERT INTO SomeTable VALUES ('MSFT')
INSERT INTO SomeTable VALUES ('TAPA')
INSERT INTO SomeTable VALUES ('AbCdE')

SELECT
LEFT(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a')-1),
SUBSTRING(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a'),LEN(x))
FROM SomeTable

Or, as part of a string expression:

SELECT
LEFT(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
COLLATE Latin1_General_CS_AS,x+'a')-1),
SUBSTRING(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
COLLATE Latin1_General_CS_AS,x+'a'),LEN(x))
FROM SomeTable

Result:

-- --
AAPL pr
A bCdE
MSFT
T a
TAPA

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xfKdneMR5PHz97zdRVn-hw@.giganews.com>...
> You can specify a case-sensitive collation either on the column definition:
> CREATE TABLE SomeTable (x VARCHAR(6) COLLATE Latin1_General_CS_AS PRIMARY
> KEY)
> INSERT INTO SomeTable VALUES ('AAPLpr')
> INSERT INTO SomeTable VALUES ('Ta')
> INSERT INTO SomeTable VALUES ('MSFT')
> INSERT INTO SomeTable VALUES ('TAPA')
> INSERT INTO SomeTable VALUES ('AbCdE')
> SELECT
> LEFT(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a')-1),
> SUBSTRING(x,PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%',x+'a'),LEN(x))
> FROM SomeTable
> Or, as part of a string expression:
> SELECT
> LEFT(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
> COLLATE Latin1_General_CS_AS,x+'a')-1),
> SUBSTRING(x, PATINDEX('%[abcdefghijklmnopqrstuvwxyz]%'
> COLLATE Latin1_General_CS_AS,x+'a'),LEN(x))
> FROM SomeTable
> Result:
> -- --
> AAPL pr
> A bCdE
> MSFT
> T a
> TAPA

Wonderful. Thank you for your help.sql

extract just the date from a datetime field using T-SQL

I am using a calendar control to pass a date to a stored procedure. The field in the table is a datetime field. Is it possible to extract just the date from the datetime field, or do I have to use multiple Datepart?

WHERE (datepart(mm,sampletimestamp) = month(@.selcteddate) and
datepart(dd,sampletimestamp) = day(@.selcteddate) and
datepart(yyyy,sampletimestamp) = year(@.selcteddate)
)

This works, but I thought there must be an easier way.

There are many ways. Easiest is to do below:

convert(varchar, sampletimestamp, 112) = @.selcteddate

|||

Something like this:

select DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE())),
DATEADD(DAY, 1, DATEDIFF(DAY, 0, GETDATE()))

-- --
2007-01-10 00:00:00.000 2007-01-11 00:00:00.000

And best to use a form like this for your where:

WHERE sampletimestamp >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, @.selcteddate))
and sampletimestamp < DATEADD(DAY, 1, DATEDIFF(DAY, 0, @.selcteddate))

So you can increase the likelihood of using an index for the search, since you don't have to execute a function on the column (which makes it unusable as a search argument for an index lookup.)

extract information from tables

Hello Guys,

my collegue left the job and I have been chosen to work on his assignments.

He posted this question before and got the answer but I couldn't find a way to implement it in the program.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1726638&SiteID=1

I changed the database a bit as I couldn't understand his logic of putting both PayID and TransNo as this would be a linked table and should have many to many relationship

Code Snippet

TransactionID int Unchecked
TransNo varchar(50) Checked
RefNo nvarchar(50) Checked
FundCodeID smallint Checked
TransAmount decimal(18, 2) Checked
Description varchar(100) Checked
TransPayID (from TransPayLnk Table as foreign key)

Tbl_TransPayLnk

Code Snippet

TransPayID int Unchecked
TransNo varchar(50) Checked
TransDate datetime Checked
Operator char(5) Checked
TerminalID tinyint Checked
Flagged char(1) Unchecked
Remarks text Checked
RemarksDate datetime Checked
TPayment decimal(18, 2) Checked

Tbl_Payment

Code Snippet

PaymentID int Unchecked
PayType varchar(50) Checked
Amount decimal(18, 2) Checked
TransPayID (Foreign Key from Tbl_TransPayLnk)

My requirement would stay the same, I would need to put the data from these 3 tables into a single line without duplicating the records and I would need to display them in a DataGridView.

P.S: Would it be possible to have Mixed when a TransPayID is matched in Tbl_payment with more then one record rather then brining cash and cheque.

Thank you all for your help.

Bye

Gemma

The answer is already available on the given post itself. What exactly you are looking for?|||

Yes the answer is there but I changed the table structure and though I appreciate the effort of DaleJ but I can't seem to display it like this in a DataGridView Row.
Why don't you check the new table structure and please reply on this.

Thanks for your help

Gemma

Extract IBM UniData (Pick database) data into SQL Server

Good afternoon SQL dudes

Does anyone have any experience of extracting data from IBM's UniData (http://en.wikipedia.org/wiki/UniData) (or any post-relational\ Pick\ nested relational\ multi-valued relational database) into a SQL Server?
More info here (http://www.rpbourret.com/xml/ProdsXMLEnabled.htm), here (http://www.pick-ware.co.uk/) and here (http://en.wikipedia.org/wiki/Pick_operating_system)

I don't (which is why I am asking) but I could imagine it being a right bugger. No need for detailed or technical info - I have no more info at this stage - just wondered if anyone has any similar experience.

Super duper, thank you SQL troopers :)Probably gonna show my age now :/ its been a while since i have done anything in Pick (over 10 years)

If tools dont already exist to do the export the easies way would be to write a small program in PickBasic that just displayed the data to screen, and a small app that runs on the PC that initates the Pick Program, and then just captures to output to a file (from what i remember alot of pc based terminal emulation programs already do that)

Just remember to calculate a checksum for the data, then re-calculate it from the captured files.

If its UniData then there are ODBC drivers available that will let you connect to the data directly.

Sorry cant be more helpfull, its been a while since i have used that technology, although i must admit, i loved working in that environment xD|||Thanks Mark - appreciated.

Checksum is a good idea.

Extract hh AM/PM from getdate()

Hi,
I am looking for a query to extract hour and AM or PM value from a date on sql2000.

ex/-
Input : 2001-12-28 22:18:07.810 (from getdate())
Output : 10 PM

select convert(varchar, (datepart(hh, convert(varchar, getdate(), 8)) % 12)) + ' ' +
substring (convert(varchar, convert(datetime, getdate(),20), 100),
DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100)) - 1,
DATALENGTH(convert(varchar, convert(datetime, getdate(),20), 100 )))

The above works but is there a better way to do this?This is a little shorter:

SELECT CONVERT(VARCHAR,DATEPART(hh,GETDATE())%12) +
CASE WHEN (DATEPART(hh,GETDATE())%12) > 0 THEN ' PM' ELSE ' AM' END|||thanks for your reply.
but i figured that 12 AM or 12 PM was displayed as 0 AM and 0 PM.
Hence to reduce my troubles, i will stick with the good ol' substring.

SELECT (substring(CONVERT(VARCHAR,getdate(),22),10,2) + ' ' +
substring(CONVERT(VARCHAR,getdate(),22), 19,2))

Extract filename

Quick question (with hopefully a quick answer :)
Does there exist a function that turns this:
C:\projecten\33994\400134506.doc
into
400134506.doc ?
Kind regards,
Stijn Verrept.I might suggest you are better to do this splitting at the presentation
tier. Or, if only the filename is important, store only the filename.
declare @.foo varchar(64)
set @.foo = 'C:\projecten\33994\400134506.doc'
select right(@.foo, charindex('', reverse(@.foo))-1)
http://www.aspfaq.com/
(Reverse address to reply.)
"Stijn Verrept" <stijn@.no_s.p.a.n.entrysoft.com> wrote in message
news:eBK$rMqIFHA.276@.tk2msftngp13.phx.gbl...
> Quick question (with hopefully a quick answer :)
> Does there exist a function that turns this:
> C:\projecten\33994\400134506.doc
> into
> 400134506.doc ?
> --
> Kind regards,
> Stijn Verrept.|||Aaron [SQL Server MVP] wrote:

> select right(@.foo, charindex('', reverse(@.foo))-1)
Thanks, this one worked perfect :)
Kind regards,
Stijn Verrept.sql

Extract Exchange 2003 data

I have searched everywhere for an answer and seems I am either blind or there is no simple solution. Basically I have a need to report on data stored in an Exchange 2003 data store. There is mention of a OLE DB for Exchange 2000 but nothing for Exchange 2003. For example, A company report on time spent on various categories in a staff's calendar, etc. I would like to use SQl 2005 SSIS to extract and reporting services to report. Why is this so difficult? I come from a Lotus Notes / Domino background and there are numerous articles and ways to do this? Please can some MS guru please give me some direction on where to look?

Cheers

Damien

Damien,

Have a look at this Microsoft support page: http://support.microsoft.com/?kbid=813349

It lists the various methods to access Exchange programmically and status of support. I used to use CDO for this type of access...

Frank

|||

Frank,

Once again this artile is date 2005 and yet still only mentions Exchange 2000 and 5.5? I am confused does microsoft not know that 2003 exists at this date?

Sureley there must be a relevant article for 2003? Anywhere?

Cheers

Damien

|||

Damien,

Try this link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/e2k3/e2k3/_exchserv2k_provider_extensions.asp

This is from the Exchange 2003 SDK. It lists relevant API's for accessing the Exchange 2003 store.

Frank

extract distinct information and order the results

Hi,

MSSQL 2000 T-SQL

I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.

I've attached a sample of DB data below.

tran_nr ret_ref_no msg_type description

5111 12345 420 reversal
5112 12345 200 auths
5113 15236 200 auths
5114 46587 200 auths
5115 46587 420 reversal

Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.

I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.

SELECT * FROM SAMPLE

WHERE ret_ref_no in
(
SELECT ret_ref_no FROM SAMPLE
GROUP BY ret_ref_no HAVING COUNT(*) > 1
)

Results of query

5111 12345 420 reversal
5112 12345 200 auths
5114 46587 200 auths
5115 46587 420 reversal

If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it.

Regards

DeceptiveThe requirement "msg_type 420 happens before the 200" can be interpreted in more than one way. Did you mean that both 420 and 200 must exist and 420 must have a lower tran_nr. Or did you mean that if 200 exists for ret_ref_no , then it must be rpecede by 420?|||

Hi AKuz,

Your first thought was correct. Thats exactly what i require.

The requirement "msg_type 420 happens before the 200" can be interpreted in more than one way. Did you mean that

both 420 and 200 must exist and 420 must have a lower tran_nr.

Deceptive

|||

The possible query,

Assumption : The tran_nr are inserted in sequence. Suggestion, instead of using the id use the timestamp.

Code Snippet

Create Table #data (

[tran_nr] int ,

[ret_ref_no] int ,

[msg_type] int ,

[description] Varchar(100)

);

Insert Into #data Values('5111','12345','420','reversal');

Insert Into #data Values('5112','12345','200','auths');

Insert Into #data Values('5113','15236','200','auths');

Insert Into #data Values('5114','46587','200','auths');

Insert Into #data Values('5115','46587','420','reversal');

select * from #data main

where exists

(

select l.ret_ref_no from #data l

inner join #data r

on l.ret_ref_no = r.ret_ref_no

and l.tran_nr < r.tran_nr

where

l.msg_type='420'

and r.msg_type = '200'

and main.ret_ref_no = l.ret_ref_no

)

|||

Here are two more possible solutions. Thanks to Manivannan for the DDL and sample data.

Code Snippet

select

*

from

#data as a

where

exists (

select

*

from

#data as b

where

b.[ret_ref_no] = a.[ret_ref_no]

and b.[tran_nr] < a.[tran_nr]

and b.[msg_type] = 420 and a.[msg_type] = 200

)

or exists (

select

*

from

#data as b

where

b.[ret_ref_no] = a.[ret_ref_no]

and b.[tran_nr] > a.[tran_nr]

and b.[msg_type] = 200 and a.[msg_type] = 420

)

select

*

from

#data

where

[ret_ref_no] in (

select

a.[ret_ref_no]

from

#data as a

where

a.[msg_type] in (200, 420)

group by

a.[ret_ref_no]

having

min(case when a.[msg_type] = 200 then a.[tran_nr] end) > min(case when a.[msg_type] = 420 then a.[tran_nr] end)

)

AMB

|||Thanks Manivannan.D.Sekaran for your assistance.|||Thank you too hunchback |||

hi Manivannan

Looks like i have another issue that i need help with. I've i add a coloumn rsp_code to the database. This coloumn will contain two degit response codes consisting of values like 00, 25,91,01,05 etc. Each transaction will have its own rsp_code value.

Question:-

Together with the SQL already provided is it posible to only retrive the records where the rsp_code = '25' for the 420 and the rsp_code = '00' for the 200 transactions?

Thanks

Deceptive

Extract Database from Master

I've a database (Citrix Data Store) that's stored in the master database (but shouldn't be). I need to extract the "Data Store" from the master
Citrix have some documentation on this:
http://support.citrix.com/article/CTX104452&searchID=10596990
The article states:
"Remove all the master database references from the new database (Please consult SQL documentation)"
My question: how do I do the above? Pointers welcome.
- Seán
Belatedly moving this thread to the database engine forum...

Extract Database from Master

I've a database (Citrix Data Store) that's stored in the master database (but shouldn't be). I need to extract the "Data Store" from the master
Citrix have some documentation on this:
http://support.citrix.com/article/CTX104452&searchID=10596990
The article states:
"Remove all the master database references from the new database (Please consult SQL documentation)"
My question: how do I do the above? Pointers welcome.
- Seán
Belatedly moving this thread to the database engine forum...

extract database content

My server had the following main tool before the harddiak gone dead
MS Windows server 2003
MS Sharepoint Portal Server 2003
MS SQL Server 2000 Enterprise
I already have a new harddisk and the above key software installed. Now
is to get the data back in place. The main data i had backup separetely
and wanted to restore is a .SPB file created using Sharepoint Backup
tool. I have since manage to use SQL Enterprise Manager to restore the
content of the .SPB file into a new database.
I have been trying to restore the data using Sharepoint Restore tool
but have yet to see any result. I am exploring other alternatives. One
of the alternative in my mind is to extract the content of this
database using any tools suitable. Most of the data in this database
are MS office document, PDF file and images. I am not familiar with SQL
database and hence not sure if there are tools like that readily
available.
Thank you very much in advance for any advice to get the file extracted
such that I can rebuild my system.
Best Regards
ChanHi,
just to close the loop,
I found a tool that works very well so far in extracting the files in
my databases. It is capable of viewing all the version history but not
able to retrieved them, anyway who cares this since I can extract the
latest files :)
http://www.sharepoint-france.com/Pr...ery/Release.rar
Best Regards
Chan

Extract data in "Insert Into..." statement format

Is there a way in SQL Server 2000 to extract data from a table, such that
the result is a text file in the format of "Insert Into..." statements, i.e.
if the table has 5 rows, the result would be 5 lines of :

insert into Table ([field1], [field2], ... VALUES a,b,c)
insert into Table ([field1], [field2], ... VALUES d, e, f)
insert into Table ([field1], [field2], ... VALUES g, h, i)
insert into Table ([field1], [field2], ... VALUES j, k, l)
insert into Table ([field1], [field2], ... VALUES m, n, o)

Thanks in advanceVyas has just what you need:
http://vyaskn.tripod.com/code.htm#inserts

--
David Portas
SQL Server MVP
--|||INSERT INTO TABLE1(FIELD1,FIELD2)
SELECT ABC, XYZ from TABLE2 where bla bla

or

INSERT INTO TABLE1(A,B,C)
SELECT X,Y, 'some static text' FROM TABLE2

the number of columns must tally

Chad Richardson wrote:
> Is there a way in SQL Server 2000 to extract data from a table, such
that
> the result is a text file in the format of "Insert Into..."
statements, i.e.
> if the table has 5 rows, the result would be 5 lines of :
> insert into Table ([field1], [field2], ... VALUES a,b,c)
> insert into Table ([field1], [field2], ... VALUES d, e, f)
> insert into Table ([field1], [field2], ... VALUES g, h, i)
> insert into Table ([field1], [field2], ... VALUES j, k, l)
> insert into Table ([field1], [field2], ... VALUES m, n, o)
> Thanks in advance|||You can by creating a calculated column that does the insert format:

select 'insert into table (id, name, phone) values (' +
cast(id as varchar(10)) + ',' +
quotename(name,'''') + ',' +
quotename(phone,'''') + ')'
from namelist

results:
insert into table (id, name, phone) values (1, 'James', 'Smith')
insert into table (id, name, phone) values (2, 'John', 'O''Kieth')

--
David Rowland
For a good User and Performance monitor, try DBMonitor
http://dbmonitor.tripod.com|||You can by creating a calculated column that does the insert format:

select 'insert into table (id, name, phone) values (' +
cast(id as varchar(10)) + ',' +
quotename(name,'''') + ',' +
quotename(phone,'''') + ')'
from namelist

results:
insert into table (id, name, phone) values (1, 'James', 'Smith')
insert into table (id, name, phone) values (2, 'John', 'O''Kieth')

--
David Rowland
For a good User and Performance monitor, try DBMonitor
http://dbmonitor.tripod.com|||Thanks all for the responses!

"Chad Richardson" <chad@.NIXSPAM_chadrichardson.com> wrote in message
news:1102ir7m0udi5cc@.corp.supernews.com...
> Is there a way in SQL Server 2000 to extract data from a table, such that
> the result is a text file in the format of "Insert Into..." statements,
> i.e. if the table has 5 rows, the result would be 5 lines of :
> insert into Table ([field1], [field2], ... VALUES a,b,c)
> insert into Table ([field1], [field2], ... VALUES d, e, f)
> insert into Table ([field1], [field2], ... VALUES g, h, i)
> insert into Table ([field1], [field2], ... VALUES j, k, l)
> insert into Table ([field1], [field2], ... VALUES m, n, o)
> Thanks in advance

extract data from SSAS and feed to a formated file / another application

Hello,

I'm looking for a tool which is able to connect to SSAS Cube, extract data and transfer it to a file (as interface to another application). With reporting services I can extract Cube data but can't transform to the right file format and with SSIS I can't extract the data from Cube.

Do you have any ideas, tools, links, etc. ?

Thanks in advanced!

-flyall-

Why would you say: "with SSIS I can't extract the data from Cube" - what is the issue? Others in the forum are apparently using SSIS for this purpose, like:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203503&SiteID=1

>>

...

I am populating flatfiles from MDX queries run i SSIS against AS databases.

...

>>

|||

Hi,

I tried a MDX query in "OLE DB Source" -> "SQL Command" but the tool can't identify the output coumns.

Is this a typical problem?

cu

-flyall-

|||

You can do it if you use a Data Reader source, but you should also be aware that all of your columns will be Unicode Text (ie the DT_NText type); this is because the AS provider isn't strongly typed because there are quite valid scenarios where a measure could return values of different types, such as a numeric value or a string. It's perfectly possible to do the conversion downstream if you need to though.

HTH,

Chris

sql

extract data from SSAS and feed to a formated file / another application

Hello,

I'm looking for a tool which is able to connect to SSAS Cube, extract data and transfer it to a file (as interface to another application). With reporting services I can extract Cube data but can't transform to the right file format and with SSIS I can't extract the data from Cube.

Do you have any ideas, tools, links, etc. ?

Thanks in advanced!

-flyall-

Why would you say: "with SSIS I can't extract the data from Cube" - what is the issue? Others in the forum are apparently using SSIS for this purpose, like:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203503&SiteID=1

>>

...

I am populating flatfiles from MDX queries run i SSIS against AS databases.

...

>>

|||

Hi,

I tried a MDX query in "OLE DB Source" -> "SQL Command" but the tool can't identify the output coumns.

Is this a typical problem?

cu

-flyall-

|||

You can do it if you use a Data Reader source, but you should also be aware that all of your columns will be Unicode Text (ie the DT_NText type); this is because the AS provider isn't strongly typed because there are quite valid scenarios where a measure could return values of different types, such as a numeric value or a string. It's perfectly possible to do the conversion downstream if you need to though.

HTH,

Chris

extract data from sqlserver

hi all
how can I go about extracting data from a sql database and piping to a file,
lets say .csv format.... any ideas appreciated
thnx
tmWhile this is possible with Reporting Services (using the file share
delivery extension), you might look at using a tool like DTS.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"tmmm" <a@.a.com> wrote in message
news:eSzJTPFVEHA.2564@.TK2MSFTNGP11.phx.gbl...
> hi all
> how can I go about extracting data from a sql database and piping to a
file,
> lets say .csv format.... any ideas appreciated
> thnx
> tm
>

Extract data from SQL Server 2005 by SMO or DMO

I am running an old script generator using SQL-DMO. Even on SQL Server 2005 it is working fine, but the new features like xml data type are not supported. So I switched to SMO. At the first view it looks pretty cool and easy. I changed the properties in the following source a thousand times but it doesn’t script any data to the file. Is it a bug or a stupid misunderstanding?

Transfer t = new Transfer(db);

t.CopyAllObjects = false;

t.CopyAllTables = true;

t.CopyData = true;

//t.Options.WithDependencies = true;

t.Options.ContinueScriptingOnError = true;

t.DestinationServer = "PC-E221\\SQLEXPRESS";

t.DestinationDatabase = "TestAgent";

t.DestinationLoginSecure = true;

t.CreateTargetDatabase = true;

t.Options.AllowSystemObjects = false;

t.Options.FileName = "testFile.sql";

t.Options.IncludeDatabaseContext = true;

t.Options.ToFileOnly = true;

Best regards

Wolfgang

Smo is a tool for generating scripts / manitaining the database not scripting the data out.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

thanks for your response.

1. If it is so, what does ".CopyData= true" mean, if it doesn't copy data?

2. How can I copy data, if DMO doesn't work either? As I said before, DMO doesn't copy xml data types.

br

Wolfgang

|||1. That is related to the TransferData method which will use DTS behind the scenes to transfer the data (read this somewhere sometime).

2. You could use a scripting utility like this here: http://vyaskn.tripod.com/code.htm to do the job. i don′t know if this is capable of using XMlL txypes, but its worth a try, because it can be really quick tested.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Extract data from SQL 6.5

I need to get some data from an enormous, creaky old SQL 6.5 database.
I know nothing about either the data schema (though I believe some sort
of documentation exists), nor 6.5 for that matter, having come to SQL
Server at 7.0.

My clients need the data in comma delimited format.

Please, can anyone suggest any possibilities? One thing that occurred
to me might be to create an Access application, use an ODBC link to the
SQL DB, and then leverage Access' not inconsiderable functionality to
get the data out.

Does anyone foresee any problems with this, or any better ways?

Forever in your debt.

Edward
--
The reading group's reading group:
http://www.bookgroup.org.ukI also came to SQL Server well after version 6.5 but I believe that 6.5
had a version of Enterprise Manager (EM) and that EM supported access
to the 6.5 equilivent of DTS. If so you should be able to use the tool
to do what you need without being 'knowledgable'.

Between the GUI and a manual I have managed to stumble through the
first time I have had to perform certain tasks.

HTH -- Mark D Powell --|||(teddysnips@.hotmail.com) writes:
> I need to get some data from an enormous, creaky old SQL 6.5 database.
> I know nothing about either the data schema (though I believe some sort
> of documentation exists), nor 6.5 for that matter, having come to SQL
> Server at 7.0.
> My clients need the data in comma delimited format.
> Please, can anyone suggest any possibilities? One thing that occurred
> to me might be to create an Access application, use an ODBC link to the
> SQL DB, and then leverage Access' not inconsiderable functionality to
> get the data out.
> Does anyone foresee any problems with this, or any better ways?

If you know Access and is comfortable with that, I guess it will work
fine.

You cold also use BCP, and you could use the BCP that comes with SQL
Server 2000. (I do seem recall that there were some problems when accessing
SQL 6.5 if the database+table names were too long.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp