Exporting SQL DB files: March 2012

Thursday, March 29, 2012

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

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

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?

-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,

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?

-flyall-

|||

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.

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

Extract data from multiple oracle database

I have to extract data from 5 different oracle databases with same schema.This will be scheduled job.Can someone guide me.

Use a For Each loop to iterate through the database connections. Use data flows inside the For Each loop to copy the data.

You might want to start with the SSIS tutorials in Books Online, if this is your first time using SSIS.

sql

Extract data from MS SQL Server 6.5 database to ascii file

I am trying to extract the data from our MS SQL Server 6.5
database to an ascii file. I need to have the data in the
columns enclosed in double quotes ( " ) and terminated by
a comma ( , ). Does anyone have any usefull information
on doing this. I am looking for a tool or a script. I
don't want to write multiple bcp out statements and I am
unsure how to enclose the data from the columns in double
quotes. Any information will be greatly appreciated."Susie" <anonymous@.discussions.microsoft.com> wrote in message
news:138b401c412b4$39088750$a401280a@.phx
.gbl...
> I am trying to extract the data from our MS SQL Server 6.5
> database to an ascii file. I need to have the data in the
> columns enclosed in double quotes ( " ) and terminated by
> a comma ( , ). Does anyone have any usefull information
> on doing this. I am looking for a tool or a script. I
> don't want to write multiple bcp out statements and I am
> unsure how to enclose the data from the columns in double
> quotes. Any information will be greatly appreciated.
You could try looking at DTS. Create a package with the Import/Export
Wizard, then tweak it in the DTS Designer
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.614 / Virus Database: 393 - Release Date: 05/03/2004

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
HariIf you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
--
David Portas
SQL Server MVP
--|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
HariIf you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
David Portas
SQL Server MVP
--|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx
.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract data from MS Access

Hi,
What are the various methods by which I can extract data
from MS Access databases using scripts (TSQL/ActiveX). I
know I can use MS DTS. But I'm interested to know about
other options.
TIA,
Hari
If you want to bring the Access data into SQLS Server you can use OPENROWSET
or create a Linked Server and query the Access database directly. See
OPENROWSET or sp_addlinkedserver in Books Online for details and examples.
David Portas
SQL Server MVP
|||Hari
You can use OPENDATASOURCE command
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="d:\northwind.mdb";
User ID=Admin;Password='
)...Customers
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:2de1701c46a4d$a3b57220$a301280a@.phx.gbl...
> Hi,
> What are the various methods by which I can extract data
> from MS Access databases using scripts (TSQL/ActiveX). I
> know I can use MS DTS. But I'm interested to know about
> other options.
> TIA,
> Hari

Extract Data from Excel 2007 on Vista

Hello all,

I am in the process up testing an upgrade from XP to Vista, and the only thing that I am running into is that my linked servers for Excel that I defined no longer work.

The spreadsheet that I am trying to open is in the 97-2003 format (not the 2007 format), yet I keep getting the same error message "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "TEST1"

Has anyone successfully created a linked server to an Excel spreadsheet on Vista, and if so, please can you provide some insight into what I am doing wrong.

I tried creating a linked server on an XP box running MS Office 2007, and it worked without any issues.

All comments welcome.

thanks

Steve

Where is the excel located in ? If it is in a UAC controlled folder (which would need special permissions / elevation) you willprobably not be able to access the file unless you disbaled UAC and restartet the computer. If this is not the case, you should check if the the file is accessible to you (if you use windows authentication and have the linked server confgured for using the Windows Authentication of the logged in user) or if the SQL Server service account has permissions to access the file (in case that you are either using SQL Server authentication or you configured the linked server to use the SQL Server service account credentials)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

Thanks for the response. I changed the log on properties of the SQL Server service to use a name user instead of the built in Network Service user and that seemed to fix the problem.

Steve

sql

Extract Data from Excel 2007 on Vista

Hello all,

I am in the process up testing an upgrade from XP to Vista, and the only thing that I am running into is that my linked servers for Excel that I defined no longer work.

Has anyone successfully created a linked server to an Excel spreadsheet on Vista, and if so, please can you provide some insight into what I am doing wrong.

I tried creating a linked server on an XP box running MS Office 2007, and it worked without any issues.

All comments welcome.

thanks

Steve

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

Thanks for the response. I changed the log on properties of the SQL Server service to use a name user instead of the built in Network Service user and that seemed to fix the problem.

Steve

Extract data from ACCESS database with pwd via ODBC

Hi,

My task is simple, I want to use the execute sql task editor to grab a value from a database in Access and put it in a variable. The connection is via ODBC and the access database is protected by a password.

I've done all the preliminary stuff such as running profiler to make sure that the package is getting the call to the database, setting up the ResultSet to be "single row" in the general tab, mapped the Result Set correctly, but nothing works. I get the same error every time.

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

Have you properly mapped the column value that you want out of the resultset by naming the resultset FingerPrint or 0 (zero), as described in the "Populating a Variable with a Result Set" section of the Execute SQL Task topic? http://msdn2.microsoft.com/en-us/library/ms141003.aspx

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Extract data from ACCESS database with pwd via ODBC

Hi,

This is my sql command:

selectcount(FingerPrintID) as FingerPrint

from Employee

Result Set is set up like this:

Result Name: FingerPrint ; Variable Name: User:: varDataset

Here is the error I get:

Error: 0xC002F309 at Execute SQL Task, Execute SQL Task: An error occurred while assigning a value to variable "varDataset": "Value does not fall within the expected range.".

My variable is set up as a Int16.

Please help!!!!!

If you could provide step by step example's that would really make my day.

Thanks,

Lawrence

What connection type are you using? ADO.NET?|||

Have you tried a 32-bit integer? That would explain the error message.

-Doug

|||

Hi,

I create a connection via odbc, using Microsoft Access Driver.

And i'm using this connection to access the data in Access DataBase.

|||

Hi,

I already try all kind of variable types that exist in SSIS, but all them retrieve the same error.

Any more ideia? I don't knew what's to do.

Thanx very much

|||

-Doug

|||

Hi,

First i created an odbc, in ODBC Data Source.

Then i have been trying to connect via odbc, using Execute SqlTask. But it doesn't work

Can you give me some help on this?

Thanx

|||

Hi,

Yes, i already did this. But also didn' work.

Any more ideias?

Thanx

Extract Data From A Table & Save As An XML File

Hello Everyone,
I am trying to extract data from from my Hit list and save as a file in XML
format.
I create a stored proc called it as stProblemClients
I wrote: Select * from clients where status = 'Not Dead'
I set a schedule stProblemClients to run daily so I canmonitor my contracted
jobs
I need the output to be saved in an XML format. Are there any small utility
out there? or any way pieces of code I can mimic?
Let me know if you need to resolve a non technical problem.
Grascia.
Vito CorleoneAre you using SQL Server 2005? Did you look at exposing the stored proc as a
webservice?
Or use FOR XML in the database and use Management Studio to take the
generated XML and save it into a file?
Best regards
Michael
"Vito Corleone" <VitoCorleone@.discussions.microsoft.com> wrote in message
news:982EE5DF-F39B-4582-8FB0-BC8FC324EB56@.microsoft.com...
> Hello Everyone,
> I am trying to extract data from from my Hit list and save as a file in
> XML
> format.
> I create a stored proc called it as stProblemClients
> I wrote: Select * from clients where status = 'Not Dead'
> I set a schedule stProblemClients to run daily so I canmonitor my
> contracted
> jobs
> I need the output to be saved in an XML format. Are there any small
> utility
> out there? or any way pieces of code I can mimic?
> Let me know if you need to resolve a non technical problem.
> Grascia.
> Vito Corleone

Extract Data From A Table & Save As An XML File

Hello Everyone,
I am trying to extract data from from my Hit list and save as a file in XML
format.
I create a stored proc called it as stProblemClients
I wrote: Select * from clients where status = 'Not Dead'
I set a schedule stProblemClients to run daily so I canmonitor my contracted
jobs
I need the output to be saved in an XML format. Are there any small utility
out there? or any way pieces of code I can mimic?
Let me know if you need to resolve a non technical problem.
Grascia.
Vito Corleone
Are you using SQL Server 2005? Did you look at exposing the stored proc as a
webservice?
Or use FOR XML in the database and use Management Studio to take the
generated XML and save it into a file?
Best regards
Michael
"Vito Corleone" <VitoCorleone@.discussions.microsoft.com> wrote in message
news:982EE5DF-F39B-4582-8FB0-BC8FC324EB56@.microsoft.com...
> Hello Everyone,
> I am trying to extract data from from my Hit list and save as a file in
> XML
> format.
> I create a stored proc called it as stProblemClients
> I wrote: Select * from clients where status = 'Not Dead'
> I set a schedule stProblemClients to run daily so I canmonitor my
> contracted
> jobs
> I need the output to be saved in an XML format. Are there any small
> utility
> out there? or any way pieces of code I can mimic?
> Let me know if you need to resolve a non technical problem.
> Grascia.
> Vito Corleone
sql

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

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 data from 350 seperate Excel Files

We have used a template for 350 excel files and now we are trying to
extract certain information from these files to either one excel file
or to an access database. The problem is that in this template the rows
are not necessarily the same in each file. (E.G. If a company started
in 1995 the corresponding rows and columns for the 2000 data will be
different than a company that started in 1999.) I also would like to
change the column headings to rows and the rows into heading columns. I
know its a big task and I am not so sure how to begin. Any thoughts
would be appreciated.<acaseutk@.gmail.com> wrote in message
news:1142284202.900306.197310@.i40g2000cwc.googlegroups.com...
> We have used a template for 350 excel files and now we are trying to
> extract certain information from these files to either one excel file
> or to an access database. The problem is that in this template the rows
> are not necessarily the same in each file. (E.G. If a company started
> in 1995 the corresponding rows and columns for the 2000 data will be
> different than a company that started in 1999.) I also would like to
> change the column headings to rows and the rows into heading columns. I
> know its a big task and I am not so sure how to begin. Any thoughts
> would be appreciated.
I sympathise with you. I seem to have spent much of my career trying to
educate accountants that a spreasheet is a totally lousy way to store data.
My suggestion is that you use Excel macros or cut and paste to get the data
as straight as you can first. Then try saving the files in delimited form
(again you can automate with macros) and import from the intermediate format
to some staging tables. Then you have LOTS of validation and transformation
to do.
You can try DTS or Integration Services straight from the Excel sheets but
in my experience this rarely works in your situation. Each file will have
different formatting, column widths, heading, etc and DTS will choke again
and again unless you are lucky. I can't say I've tried it with IS though -
maybe some things have improved.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||How did you know I was an accountant...Haha. Thanks for the suggestion
I will look into it.

EXtract data from 10 identical oracle database into one

Please guide me urgently how to extract data in SSIS from 10 identical oracle database into 1 sql server database.

There is a table which list all the 10 databases.

You could do it all in one data flow task or many it's up to you. Basically, you'll need to create a connection manager for oracle and one for sql server. For the source define the table or query to extract the needed data from orcale and then connect it to the destination. Then just map the columns from source to target.

|||

JIGJAN wrote:

Please guide me urgently how to extract data in SSIS from 10 identical oracle database into 1 sql server database.
There is a table which list all the 10 databases.

You can use a For Each loop to drive this process. Put an Execute SQL to retrieve the list of databases, and connect it to a For Each loop set to enumerate a rowset. In the For Each loop map a variable to the database name. Put an expression on the connection manager for the Oracle database, to create the connect string dynamically. Put a data flow (or multiple flows, depending on the number of tables) inside it, configured to copy the tables.

Extract color formatted text from code editor?

Is it possible to get the text from the code editor into another app like MS
Word while retaining the font color coding?
txThis is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing th
is
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>sql

Extract color formatted text from code editor?

Is it possible to get the text from the code editor into another app like MS
Word while retaining the font color coding?
tx
This is what happens when you select code from Management Studio's query
editor and paste into Word. I don't think there is any magic for doing this
from Query Analyzer (the color coding is not part of what is transfered to
the clipboard).
"Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
> Is it possible to get the text from the code editor into another app like
> MS
> Word while retaining the font color coding?
> tx
|||Thanks, Aaron. It's kind of brute force. I have been using ADO and catalog
views to extract meta data and then try to mimic the default color coding in
MS Word but it is a challenge of a different kind.
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is what happens when you select code from Management Studio's query
> editor and paste into Word. I don't think there is any magic for doing this
> from Query Analyzer (the color coding is not part of what is transfered to
> the clipboard).
>
>
>
> "Tadwick" <Tadwick@.discussions.microsoft.com> wrote in message
> news:A8A6DC13-2258-4995-A493-F49F151F88D9@.microsoft.com...
>
>

Extract and compare hour

Hi all:
As I can extract the hour values and minute of a field of type datetime to compare it with the values of a field of type smalldatetime of another table
Thanks.:confused:Yes. Look at DatePart in BOL.

Extract a string in a Stored Procedure

Is there anyway to extract part of a string in a stored procedure
using a parameter as the starting point?
For example, my string might read: x234y01zx567y07zx541y04z
My Parameter is an nvarchar and the value is: "x567y"
What I want to extract is the two charachters after the parameter, in
this case "07".
Can anyone shed some light on this problem?
Thanks,
lqLauren Quantrell (laurenquantrell@.hotmail.com) writes:
> Is there anyway to extract part of a string in a stored procedure
> using a parameter as the starting point?
> For example, my string might read: x234y01zx567y07zx541y04z
> My Parameter is an nvarchar and the value is: "x567y"
> What I want to extract is the two charachters after the parameter, in
> this case "07".
> Can anyone shed some light on this problem?

Looks like a combination of substring and charindex (or possibly
patindex) is what you need. I recommend that you use the SQL Server
Books Online to study all the string functions that SQL Server
offers. They are not that many, and not that extremely powerful, but
it's very useful to know them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks.
I'm on a crash project using MDSE and don't have immediate access to
Books Online though...
lq

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns946FEC5AC5081Yazorman@.127.0.0.1>...
> Lauren Quantrell (laurenquantrell@.hotmail.com) writes:
> > Is there anyway to extract part of a string in a stored procedure
> > using a parameter as the starting point?
> > For example, my string might read: x234y01zx567y07zx541y04z
> > My Parameter is an nvarchar and the value is: "x567y"
> > What I want to extract is the two charachters after the parameter, in
> > this case "07".
> > Can anyone shed some light on this problem?
> Looks like a combination of substring and charindex (or possibly
> patindex) is what you need. I recommend that you use the SQL Server
> Books Online to study all the string functions that SQL Server
> offers. They are not that many, and not that extremely powerful, but
> it's very useful to know them.|||I figured out how to do this:

substring(mystring,charindex(@.parameter,myString)+ len(@.parameter),2)

where @.parameter = 'x' + [myUserID] + 'y'

Thanks for pointing me in the right direction.

You have. Check my signature.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Extract a program from SQL 2000?

Hello,
We have a web based program that is using SQL 2000 as the db. Sql 2000 is running on a Windows 2003 server.

Is it possible to extract the SQL 2000 program(web based) with a clean db onto a blanket CD so another person can install it?

Any help would greatly be appreciated.

Mike

If you mean, is it possible to create an Installation disk for SQL Server, the answer is -No.

The 'another person' will have to purchase, or otherwise, legitimately obtain and license SQL Server.

Very, very Silly Question.

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

Extract a character string from a text field

I need to extract a character string from a text field. The string I'm
looking for will always start with the first four characters of "ABC-" and
then will end with three numbers (0-9) in varying combinations, ex.
"ABC-508". The problem is that the position of the string in the text field
is different in each record and the last three characters of the string will
vary as described above. Any help is greatly appreciated
--
Finn GirlOne method:
SELECT
CASE
WHEN PATINDEX('%ABC-[0-9][0-9][0-9]%', MyTextCol) = 0 THEN
NULL
ELSE
SUBSTRING(MyTextCol, PATINDEX('%ABC-[0-9][0-9][0-9]%',
MyTextCol), 7)
END AS ExtractedValue
FROM dbo.MyTable
You can encapsulate the code in a proc or function for reusability.
Hope this helps.
Dan Guzman
SQL Server MVP
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:576F175D-855C-424F-A8D7-80E4A5B110CE@.microsoft.com...
>I need to extract a character string from a text field. The string I'm
> looking for will always start with the first four characters of "ABC-" and
> then will end with three numbers (0-9) in varying combinations, ex.
> "ABC-508". The problem is that the position of the string in the text
> field
> is different in each record and the last three characters of the string
> will
> vary as described above. Any help is greatly appreciated
> --
> Finn Girl|||create table #foo (SomeColumn varchar(50))
insert into #foo values ('test ABC-508')
insert into #foo values ('testing ABC-509')
insert into #foo values ('ABC-123')
insert into #foo values ('FinnGirlABC-524')
insert into #foo values ('abcABC-456')
select * from #foo
--this shows you the charindex
SELECT CHARINDEX('ABC-', SomeColumn) FROM #foo
--this gets the desired information:
SELECT SUBSTRING(SomeColumn, (CHARINDEX('ABC-', SomeColumn)), 7) FROM #foo
drop table #foo
Keith Kratochvil
"FinnGirl" <FinnGirl@.discussions.microsoft.com> wrote in message
news:576F175D-855C-424F-A8D7-80E4A5B110CE@.microsoft.com...
>I need to extract a character string from a text field. The string I'm
> looking for will always start with the first four characters of "ABC-" and
> then will end with three numbers (0-9) in varying combinations, ex.
> "ABC-508". The problem is that the position of the string in the text
> field
> is different in each record and the last three characters of the string
> will
> vary as described above. Any help is greatly appreciated
> --
> Finn Girl

Extract "GroupName" from "sp_helpuser"

Hi all,

I want to create a stored procedure which will extract the "GroupName"
from the record returned by "sp_helpuser". In order to do this I need
to execute "sp_helpuser" which returns the entire record. I want to
just extract the "GroupName" from the record and return it to my
application. How do I go about this?

Thanks in advance,

AlvinAlvin Sebastian (asebastian@.cmri.usyd.edu.au) writes:
> I want to create a stored procedure which will extract the "GroupName"
> from the record returned by "sp_helpuser". In order to do this I need
> to execute "sp_helpuser" which returns the entire record. I want to
> just extract the "GroupName" from the record and return it to my
> application. How do I go about this?

Either you access sysusers directly, you can use the INSERT EXEC construct:

INSERT #temp (...)
EXEC sp_helpuser

You need to create #temp so that it agrees with the output from sp_helpuser.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

By the way, I forgot to mention in the first post that I'm only
interested in the "GroupName" of the currently logged-on user so the
stored procedure will be returning a single string value only and not
a table. How should the stored procedure return this single value from
the record returned by "sp_helpuser"?

Alvin

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93BF646E19EF7Yazorman@.127.0.0.1>...
> Alvin Sebastian (asebastian@.cmri.usyd.edu.au) writes:
> > I want to create a stored procedure which will extract the "GroupName"
> > from the record returned by "sp_helpuser". In order to do this I need
> > to execute "sp_helpuser" which returns the entire record. I want to
> > just extract the "GroupName" from the record and return it to my
> > application. How do I go about this?
> Either you access sysusers directly, you can use the INSERT EXEC construct:
> INSERT #temp (...)
> EXEC sp_helpuser
> You need to create #temp so that it agrees with the output from sp_helpuser.|||Alvin Sebastian (asebastian@.cmri.usyd.edu.au) writes:
> By the way, I forgot to mention in the first post that I'm only
> interested in the "GroupName" of the currently logged-on user so the
> stored procedure will be returning a single string value only and not
> a table. How should the stored procedure return this single value from
> the record returned by "sp_helpuser"?

A one-row result set is still a table.

There is the OPENQUERY method as well.

See http://www.algonet.se/~sommar/share_data.html where I discuss both
methods.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland, I got it working now!

Extracing a House Number

I am trying to Extract the House Number from a address field

i want to start on the left and grab everything till i find the first space.

any help is greatly appreciated

select LEFT(address, (CHARINDEX(' ', address)-1)) as HouseNum

from Table

|||

Could you post some sample address data with house/unit/apt #? Note that this will be a tough problem to solve depending on your address data. It is easy to write specific SQL/TSQL expressions to extract various parts of the address but the possibilities are numerous depending on the data. There are many variables like following:

Does the addresses belong only to the USA?

Do you have different line numbers for the address that can contain the unit#?

What about the format of the addresses?

Are the addresses normalized in any manner?

So this can be a non-trivial approach and depending on your requirements it will be tough to solve in TSQL. It is best to do the address cleansing outside of the database. There are lot of commercial packages / products available to do this type of data cleaning. They can easily correct address data and normalize them to various fixed formats depending on the type of address. You can then get individual fields like zip, city, state, unit#, street# etc and use it in your application.

|||Agree. My SQL statement is only used to extract information from a string. Don should base on his situation to decide which is the best solution.|||

if the objective is only to get the house number only then

he should grab the first consecutive non alpha characters instead

save the results and have an encoder to the dirty job

|||thanks all the suggestion helped alot.

Tuesday, March 27, 2012

Extra xml node

This is in sql 2005
I have a query that is returning a set of rows each with one xml field
row1 -<apple></apple>
row3 -<orange></orange>
row2-<grape></grape>
I want the output to look like this
<fruits>
<apple></apple>
<orange></orange>
<grape></grape>
</fruits>
However when I use FOR XML AUTO, root(''Fruits'')
<fruits>
<fruit><apple></apple></fruit>
<fruit><orange></orange></fruit>
<fruit> <grape></grape></fruit>
</fruits>
How do I remove the extra fruit element?
ENDHello Hyper,
Try using a FOR XML PATH query instead, ala for xml path (''),root('fruits')
,type
Thanks!
Kent

> This is in sql 2005
> I have a query that is returning a set of rows each with one xml field
> row1 -<apple></apple>
> row3 -<orange></orange>
> row2-<grape></grape>
> I want the output to look like this
> <fruits>
> <apple></apple>
> <orange></orange>
> <grape></grape>
> </fruits>
> However when I use FOR XML AUTO, root(''Fruits'')
> <fruits>
> <fruit><apple></apple></fruit>
> <fruit><orange></orange></fruit>
> <fruit> <grape></grape></fruit>
> </fruits>
> How do I remove the extra fruit element?
> END
>
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Extra white space in table

I've got a table with a header, group, and detail row. The detail row
is collaspable however when it's collasped there is several lines of
white space between the parent groups. How do I get rid of this white
space?
Thanks in advance for the helpIs it possible that the white space is really the detail row, and it is
not shrinking as expected? (I've had this same problem before...) If
you are supressing just a column or two, but not hiding the entire
detail row (such as setting visibility --> hidden property based upon
conditional expression) then you will get the spacing issue as
described.|||Thanks Matt
That was the problem. I was setting the visibility at the cell level
when i should have been setting it on the over all row. thanks for the
help
Mathiassql

Extra White Space

Hi All,
I've got a report that is set up with the Report Properties layout as
follows: page width = 8.5in; page height = 11in; left/right margins = .25in;
top/bottom margins = .50in.
When the report displays in IE7, there is a large white area (blank) to the
right of the report and it's full length. When I export as a PDF document,
this area seems to cause extra pages to be created in the PDF output.
Since I'm not sure "why" this extra white space is appearing, I thought I'd
post this here.
Thanks in advance!
Kind regards - FredHi Fred,
I've found, that meters of extra white space on the right side, before all
when viewed in HTML, are created when there are overlapping visible textboxes.
When textboxes overlap where one is invisible, there are also minor layout
differences, but never so dramatically.
Yours, Henry

Extra Trailing Whitespace Doesn't Violate Foreign Key Constrai

To check whether it was off, try this run this and see if the value is 1. If
so, then the trailing blank spaces are lost
select ColumnProperty(object_id('ProductTable')
, 'Product One',
'UsesAnsiTrim')On Tue, 30 May 2006 07:43:03 -0700, Omnibuzz wrote:

> To check whether it was off, try this run this and see if the value is 1.
If
> so, then the trailing blank spaces are lost
> select ColumnProperty(object_id('ProductTable')
, 'Product One',
> 'UsesAnsiTrim')
Yes, the value of this is 1, so ANSI_PADDING is on. As far as I can tell,
ANSI_PADDING is ON per the default and I have never done anything to
change this.
Thanks,
Josh|||It would have been set at the time of the table creation.
Its set at runtime.
"Josh T" wrote:

> On Tue, 30 May 2006 07:43:03 -0700, Omnibuzz wrote:
>
>
> Yes, the value of this is 1, so ANSI_PADDING is on. As far as I can tell,
> ANSI_PADDING is ON per the default and I have never done anything to
> change this.
> Thanks,
> Josh
>

Extra Space....

Hello!

I have written a report that is being displayed in a table. For some reason all the way to the right of the table has a HUGE amount of space......I checked to make sure there was no layers over it and I looked in the properties and saw no padding....ANY IDEAS? I am so frusterated because this defies all logic!!! Has anybody experienced this?

TIA!

That happened to me and I checked the width of the object that contained the table. In my case, the width of the rectangle that contained my table was way bigger than it should have been.|||

Oh Thank you for responding!! Are you talking about the width of the table itself....or the width of the page?

Thanks Again!

|||

Hi,

Check what the parent of the table is. If it says "body", then that is what you would adjust the width of.

|||Oh great! thanks!

Extra space at bottom of page

I keep getting extra space at the bottom of each page on my report when it is deployed to the web server but I don't get this extra space on the report server. Does anyone know if this is in the report layout or if the problem is in the web page itself?

Thanks,

messed around with report printer properties and re-deployed.sql

extra space at bottom

Hi;
i want to add extra separation between lines when a field has certain
value... right now, i tried bottom padding and Lineheight but none seems to
work...
this is the expression i had on my table detail's line bottom pading :
=iif(fields!UTIL.value="T",10,2)
Any advice?found the problem...
i has to be: =iif(fields!UTIL.value="T","10pt","2pt")
"Willo" <willoberto@.yahoo.com.mx> wrote in message
news:%235Wc$9FgHHA.668@.TK2MSFTNGP05.phx.gbl...
> Hi;
> i want to add extra separation between lines when a field has certain
> value... right now, i tried bottom padding and Lineheight but none seems
> to work...
> this is the expression i had on my table detail's line bottom pading :
> =iif(fields!UTIL.value="T",10,2)
> Any advice?
>
>

Extra Row Needed

Here is the basic sql I am trying to implement:

select classid, count(*) as [COUNT], dtmready from unit
where rmpropid = '123' and classid = 'A1'
group by rmpropid, classid, dtmready
order by dtmready;

Here is my result set:

A1 3 2006-07-01 00:00:00.000
A1 10 2006-08-15 00:00:00.000
A1 11 2006-09-15 00:00:00.000
A1 10 2006-10-15 00:00:00.000
A1 10 2006-11-01 00:00:00.000
A1 10 2006-11-30 00:00:00.000

If you notice, the earliest dtmready is 7/1/2006. What I need is to return an additional row when the earliest dtmready is after today. The desired row would be:

A1 0 (today's date)

Background: I am running SQL Server 2000 SP4 and the results of the query are returned to a java program at a level where I do not have the ability to create a new row. So, it would be ideal if I could create the sql that returns a row with a dtmready of today with a count of 0.Try this:

select classid, count(*) as [COUNT], dtmready
into #tempunit
from unit
where rmpropid = '123' and classid = 'A1'
group by rmpropid, classid, dtmready
order by dtmready;

if ((select min(dtmready) from #tempunit)>getdate())
insert into #tempunit values ('a1','0',getdate())

select * from #tempunit

This should work?|||Thanks for the response.

I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem. Do you have any other ideas?

Thanks, Mike|||I don't think I will be allowed to create a temp table (production database bureaucracy etc.) to solve this problem.No temp tables? That is bogus. I can see them not wanting you to create permanent tables "temporarily", but there should be nothing wrong with creating true "temp" tables.
Regardless, here is another method:select classid,
count(*) as [COUNT],
dtmready
from unit
where rmpropid = '123'
and classid = 'A1'
group by rmpropid,
classid,
dtmready
UNION
select classid,
0 as [COUNT],
getdate() as dtmready
from unit
group by classid
having min(dtmready) > getdate()
order by dtmready;|||That did it! Thanks so much for your help!

Mike

Extra parameters being added onto SP call with ADO?

We are running into problems on our development environment with extra
parameters being added on to a stored procedure call. Instead of the
expected stored procedure call, the trace shows something like this:

declare @.P1 int
set @.P1=NULL
<<expected stored procedure call>> , @.P1 output, <<repeat of first
three sp parms>>
select @.P1

The developer has checked the code, and I have checked the SP - both
seem to match what is in production (which works fine). The databases
are on the same server, and the apps are running on seperate web
servers.

I'm guessing this may be some sort of configuration issue with ADO,
SQL, or something else - has anyone run into something similar to
this? Thanks!

DaveSo, is the sp running at all when called with that additional parameter? It
should fail if that parameter (the one you think is additional) is not
declared in the sp.

What happens if you paste the output from Profiler into Query Analyzer and
run it?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm

"Dokter Z" <dzahn@.execpc.com> wrote in message
news:7e6a5a6.0402250644.4ef950eb@.posting.google.co m...
We are running into problems on our development environment with extra
parameters being added on to a stored procedure call. Instead of the
expected stored procedure call, the trace shows something like this:

declare @.P1 int
set @.P1=NULL
<<expected stored procedure call>> , @.P1 output, <<repeat of first
three sp parms>>
select @.P1

I'm guessing this may be some sort of configuration issue with ADO,
SQL, or something else - has anyone run into something similar to
this? Thanks!

Dave|||Vyas -

Thanks for the response. Actually, we have discovered that the initial
problem was caused by

1) Some "sub-optimal" Paramaters.Refresh code
2) A stored procedure call issued by user aaaaa ended up calling
bbbbb.stored_proc_name instead of dbo.stored_proc_name. I'm currently
researching that issue...

Dave

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Extra Pages with Landscape

I have a report that is landscape (11w x 8.5h). The left margin is .2 and the
right margin is .2 and the body is 10.149w. The total (10.549) is under the
11 inches of the width but when I go to print preview, the report is 15 pages
with only 10 containing data. The last five pages are blank.
I then go to print it and the printer still sees the report a portrait
document instead of landscape so I have to manually adjust for this each
time.
Any ideas or suggestions?
I thought that this would be a good challenge for Peter Blackburn...In Visual Studio report designer make sure that the white canvas page is at
minimum size.
If the canvas (The white page you start with when designing a report) is
greater than the width of your paper then the print out will strech more
pagers...
"Kipp C" wrote:
> I have a report that is landscape (11w x 8.5h). The left margin is .2 and the
> right margin is .2 and the body is 10.149w. The total (10.549) is under the
> 11 inches of the width but when I go to print preview, the report is 15 pages
> with only 10 containing data. The last five pages are blank.
> I then go to print it and the printer still sees the report a portrait
> document instead of landscape so I have to manually adjust for this each
> time.
> Any ideas or suggestions?
> I thought that this would be a good challenge for Peter Blackburn...

Extra page in print preview and .pdf

I have tried and tried to get my reports to quit printing an extra blank page but have been unsuccessful in my efforts.

I've set the margins in my report properties to (all in inches):

.75 - top

.5 - left, right, bottom

My page is landscape so the width is 11 and the height is 8.5

I've made sure my table is not wider than 9.5 - I've even made it as small as 8" wide to see if that was the problem . (I've done this for the header/footer as well.)

My report prints all on one page but I still get a 2nd page with header/footer information and a blank body.

I read a post that said to try making the right margin 0 - that didn't work either.

Additional information: I just saw a post suggesting borders around everything to see what might be bleeding onto a 2nd page.

One page 1, the full borders are there, so nothing should be bleeding onto page 2. However, when I go to page 2, there are no borders around the header, but the left edge shows on the right side of the page. Also, the footer is missing it's left edge, but the right shows. I've tried making both the header and footer smaller, as well as moving them further right - still get the exact issue.

I'm using the reporting services for Visual Studio .NET 2003.

Any help would be greatly appreciated! Thanks!

Well, turns out it was the layout. Although my report elements were the right size, the layout was too big, forcing the margins over.|||

It is COOL that your posted your results!

|||

Hello - just a general comment!

I have eliminated the capability to export to PDF within RS in that PDF NEVER renders any ledgeable characters in my part of the world which includes:

Thailand, Cambodia, Laos, Mayamar (Burma), Vietnam, Pakistan and Tibet and this also includes any dialect in Mandarin!

We just stick to a TIF and everything is always perfect -

|||

When you say your layout was too big, what did you set it too?

Just curious, since I was having an issue similar to yours although I might not have worded it as clearly Wink [;)]

http://forums.asp.net/1139722/ShowPost.aspx

Thanks

sql

Thursday, March 29, 2012

Tuesday, March 27, 2012

Blog Archive

About Me