Data transformation services: contains

Showing posts with label contains. Show all posts

Tuesday, March 27, 2012

Data Source file on Reporting Services server question

In the Reporting Services Manager, when the user selects the folder where the
reports reside there is a file that contains the connection string
information along with a user name and password (Data Source).
Is there a way to keep the file from being selected by the user ?
Thanks.There is a setting in the properties of the data source to "Hide in List
View". Unfortunately the users can just click on the "Show Details"
option and see the datasource. Depending on the permissions you gave
them they will only be able to view the information.
I sent an question out some time ago seeing if there was away to remove
the "Show Details" for some users but no one replied.
Mike wrote:
> In the Reporting Services Manager, when the user selects the folder where the
> reports reside there is a file that contains the connection string
> information along with a user name and password (Data Source).
> Is there a way to keep the file from being selected by the user ?
>
> Thanks.|||You can use a datasource in another location. It's a faff because you
have to change it manually for each deployed report affected. Also
everytime you deploy the report it will create the datasources in the
deployment folder if it can, so you have to remove it each time.
Having said that, this would essentially cure your problem.
Chris
Bon733 wrote:
> There is a setting in the properties of the data source to "Hide in
> List View". Unfortunately the users can just click on the "Show
> Details" option and see the datasource. Depending on the permissions
> you gave them they will only be able to view the information.
> I sent an question out some time ago seeing if there was away to
> remove the "Show Details" for some users but no one replied.
> Mike wrote:
> > In the Reporting Services Manager, when the user selects the folder
> > where the reports reside there is a file that contains the
> > connection string information along with a user name and password
> > (Data Source).
> >
> > Is there a way to keep the file from being selected by the user ?
> >
> >
> > Thanks.

Thursday, March 22, 2012

Data search

I use SQL server to connect to a HUGE odbc compliant db with 1000's of tables. The db contains tons of data. I am looking for specific data elements. Is there any way to search the entire db for a data value?

Thanks Marcie

I'm sorry, I'm not sure I understand the question. Do you mean besides using standard SQL?sql

Data Restore on Elm Street!

Hi all, a bit of a nasty one here!

Ive inherited a server that has just collapsed. It contains SQL Server
and has a fair amount of data on it.

The server would only boot into a BSOD Stop Error. I suspected that
one of the hives in the registry was corrupt so I used the Win2K
Recovery Console to restore the registry to its original settings (So
Windows can not see the SQL stuff now). There is no restore points
from which to take recent registry settings.

My Question is this...

We have a spare server (just lying about you know!!!) which we plan to
install Win2K and MS SQL Server onto... How can I transfer the data,
in a nice easy manner, from the old server to the new server?

Is it possible to move just the data files into the new install?

Remember I inherited this server! (I dont have Backups!!!)
Please... No email's about how important backups are... I know how
important backups are.

Any help/ideas would be appreciated.

Thanks, John T.You can certainly try

Exec sp_attach_db (details in BOL)

There are times when this will only work if you have explicitly used
sp_detach_db (details in BOL)

Failing that and no backups then you may want to give the boys and girls in
PSS a bell.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"John Tear" <johntear@.hotmail.com> wrote in message
news:92ae3244.0404150027.72b19847@.posting.google.c om...
> Hi all, a bit of a nasty one here!
> Ive inherited a server that has just collapsed. It contains SQL Server
> and has a fair amount of data on it.
> The server would only boot into a BSOD Stop Error. I suspected that
> one of the hives in the registry was corrupt so I used the Win2K
> Recovery Console to restore the registry to its original settings (So
> Windows can not see the SQL stuff now). There is no restore points
> from which to take recent registry settings.
> My Question is this...
> We have a spare server (just lying about you know!!!) which we plan to
> install Win2K and MS SQL Server onto... How can I transfer the data,
> in a nice easy manner, from the old server to the new server?
> Is it possible to move just the data files into the new install?
> Remember I inherited this server! (I dont have Backups!!!)
> Please... No email's about how important backups are... I know how
> important backups are.
> Any help/ideas would be appreciated.
> Thanks, John T.|||Hi Allan

Thanks for your reply. I got this sorted, basically I took the data
files and transfered them accross to the new server then I attached
them in the management console for MS SQL. Pretty lucky I think!

I have advised that a backup solution is the VERY next step!!!

John T.

"Allan Mitchell" <allan@.no-spam.sqldts.com> wrote in message news:<407e4e4e$0$31686$fa0fcedb@.lovejoy.zen.co.uk>...
> You can certainly try
> Exec sp_attach_db (details in BOL)
> There are times when this will only work if you have explicitly used
> sp_detach_db (details in BOL)
>
> Failing that and no backups then you may want to give the boys and girls in
> PSS a bell.
> --
> --
> Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
> www.SQLDTS.com - The site for all your DTS needs.
> I support PASS - the definitive, global community
> for SQL Server professionals - http://www.sqlpass.org
>
> "John Tear" <johntear@.hotmail.com> wrote in message
> news:92ae3244.0404150027.72b19847@.posting.google.c om...
> > Hi all, a bit of a nasty one here!
> > Ive inherited a server that has just collapsed. It contains SQL Server
> > and has a fair amount of data on it.
> > The server would only boot into a BSOD Stop Error. I suspected that
> > one of the hives in the registry was corrupt so I used the Win2K
> > Recovery Console to restore the registry to its original settings (So
> > Windows can not see the SQL stuff now). There is no restore points
> > from which to take recent registry settings.
> > My Question is this...
> > We have a spare server (just lying about you know!!!) which we plan to
> > install Win2K and MS SQL Server onto... How can I transfer the data,
> > in a nice easy manner, from the old server to the new server?
> > Is it possible to move just the data files into the new install?
> > Remember I inherited this server! (I dont have Backups!!!)
> > Please... No email's about how important backups are... I know how
> > important backups are.
> > Any help/ideas would be appreciated.
> > Thanks, John T.|||Hi Allan

I have advised that a backup solution is the VERY next step!!!

John T.

Data Restore on Elm Street!

Hi all, a bit of a nasty one here!

Ive inherited a server that has just collapsed. It contains SQL Server
and has a fair amount of data on it.

My Question is this...

Is it possible to move just the data files into the new install?

Remember I inherited this server! (I dont have Backups!!!)
Please... No email's about how important backups are... I know how
important backups are.

Any help/ideas would be appreciated.

Thanks, John T.You can certainly try

Exec sp_attach_db (details in BOL)

There are times when this will only work if you have explicitly used
sp_detach_db (details in BOL)

Failing that and no backups then you may want to give the boys and girls in
PSS a bell.

--
--

Data Report Problem

I have a transaction table which contains 2 columns, Amount and Cat_id (category)

eg my table is like

Amount | Cat_id
500 | 1
300 | 1
800 | 2
400 | 2

So now ive 2 queries
select amount from transaction where category = 1;

select amount from transaction where category = 2;

I want to display the result of these 2 queries in my report.

If i try to add these queries in two seperate commands in the data environment and then add it to the report then it shows an error.

So I created 2 views cat1 and cat 2 with those queries, created a new query which takes the value from those two views and placed it on the report.

But now wen i see the report i get this

Cat_1 | Cat_2
500 | 800
300 | 800
500 | 400
300 | 400

While i want my report like this.

Cat_1 | Cat_2
500 | 800
300 | 400

Please help me guys, have to build a small program.i am not sure i am reading this correctly, but i am going to take a stab at helping just in case...
have you tried grouping your results by category?

hope this helps...|||What about rewriting your query to:

select amount from transaction where category = 1 OR category = 2
Order By category

That will get your results in the form of:

Amount | Cat_id
500 | 1
300 | 1
800 | 2
400 | 2

... unless category 1 and category 2 come from 2 different tables. If they do, you could use a JOIN.

Tuesday, March 20, 2012

Data Recovery Book

Hi!
I am glad to introduce World's First Book on Professional Data
Recovery Programming. Book also Includes a FREE CD, which contains
complete Source Codes of all the software/Programs described within
the book and may interesting tools
You can see the contents of the book and order Online on following
link:
http://www.DataDoctor.biz/author.htm
The Details of the Book are as follows:
Title of the book: "Data Recovery with & without Programming"
Author of the book: Tarun Tyagi
Publishers of the book: BPB Publications, New Delhi, India
Number of Pages: 540
Size: (9.5 x 7.5 x 1.0) Inches
ISBN: 81-7656-922-4
Price: $ 69.00 (Including Shipping Charge, Cost of
Book and Other expenses)
Enjoy Reading!!
Regards,
Tarun Tyagi
J-110, Patel Nagar - 1,
Ghaziabad (U.P.), India -201001
Cell: (+91)9868337762
http://www.DataDoctor.BizYour page doesn't render at all in Firefox. You might want to check that
out.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Author Tarun Tyagi" <tyagi_st@.yahoo.com> wrote in message
news:a921f5f5.0412010120.1b976a90@.posting.google.com...
> Hi!
> I am glad to introduce World's First Book on Professional Data
> Recovery Programming. Book also Includes a FREE CD, which contains
> complete Source Codes of all the software/Programs described within
> the book and may interesting tools
> You can see the contents of the book and order Online on following
> link:
> http://www.DataDoctor.biz/author.htm
> The Details of the Book are as follows:
> Title of the book: "Data Recovery with & without Programming"
> Author of the book: Tarun Tyagi
> Publishers of the book: BPB Publications, New Delhi, India
> Number of Pages: 540
> Size: (9.5 x 7.5 x 1.0) Inches
> ISBN: 81-7656-922-4
> Price: $ 69.00 (Including Shipping Charge, Cost of
> Book and Other expenses)
> Enjoy Reading!!
> Regards,
> Tarun Tyagi
> J-110, Patel Nagar - 1,
> Ghaziabad (U.P.), India -201001
> Cell: (+91)9868337762
> http://www.DataDoctor.Biz

Thursday, March 8, 2012

Data Missing from Report

I have created a RS report that contains 2 datasets and 2 lists. This is a
consolidated corporate report grouped to return data for 53 companies (one
page per company). Both datasets return data for all 53 companies when
viewing from the data pane. However, when the report is viewed in the
preview pane (or rendered), the 1st dataset returns data for all 53
companies, but there are 8 of the companies missing data from the 2nd dataset.
The problem varies in severity depending on the Begin and End date
parameters used to generate the report. What is strange is that a narrower
date range (several days) leaves off more data than a broader date range
(full month).
Anyone have any ideas?Has anyone out there ever experienced data missing from a RS generated report
that is returned when the same query is run in Query Analyzer?
"Lynn" wrote:
> I have created a RS report that contains 2 datasets and 2 lists. This is a
> consolidated corporate report grouped to return data for 53 companies (one
> page per company). Both datasets return data for all 53 companies when
> viewing from the data pane. However, when the report is viewed in the
> preview pane (or rendered), the 1st dataset returns data for all 53
> companies, but there are 8 of the companies missing data from the 2nd dataset.
> The problem varies in severity depending on the Begin and End date
> parameters used to generate the report. What is strange is that a narrower
> date range (several days) leaves off more data than a broader date range
> (full month).
> Anyone have any ideas?
>
>

Saturday, February 25, 2012

Data manipulation

I have a table which contains the following data:

1877: Morell, Prince Edward Island 1878: Morin-Heights, Quebec 1879: Morinville, Alberta 1880: Morley, Alberta

etc.

I would like to extract just the name that is found between (: ) and (,)
Is there a way i could do that?

Thanks in advance

Code Snippet

selectsubstring(data,charindex(':', data)+1,len(data)-charindex(',',reverse(rtrim(data)))-charindex(':', data))

from(

select'1877: Morell, Prince Edward Island 'as data

unionallselect'1878: Morin-Heights, Quebec'

unionallselect'1879: Morinville, Alberta '

unionallselect'1880: Morley, Alberta'

)as x

|||

This could work for you:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyData varchar(50)
)

INSERT INTO @.MyTable VALUES ( '1877: Morell, Prince Edward Island ' )
INSERT INTO @.MyTable VALUES ( '1878: Morin-Heights, Quebec' )
INSERT INTO @.MyTable VALUES ( '1879: Morinville, Alberta' )
INSERT INTO @.MyTable VALUES ( '1880: Morley, Alberta' )

SELECT substring( MyData, ( charindex( ': ', MyData ) + 2 ), ( charindex( ', ', MyData ) - charindex( ': ', MyData ) - 2 ))
FROM @.MyTable

--
Morell
Morin-Heights
Morinville
Morley

|||Thank you

Sunday, February 19, 2012

Data in one column contains column name of other table

I have one table in one of its column it stored column name of another
table. i want select statement to select that column name. i will explain
this with example
Table1 has columns with name ID,col1 and having data in that column as
(1,tb_col1),(2,tb_col2),(3,tb_col3) etc
and another table has this columndata of table one as column name means
table2 has column (tab_id2,tb_col1,tb_col2,tb_col3)
inthis if i select ID=1 from table1 then select statement should show me
data in column tb_col1 of table2..
if i select ID=2 from table1 then select statement should show me data in
column tb_col2 of table2..
here data in column col1 of table1 may change any time so i want it
dynamically
Message posted via http://www.webservertalk.comYou need to use dynamic sql.
Example:
create procedure dbo.proc1
@.id int
as
set nocount on
declare @.sql nvarchar(4000)
if exists(select * from table1 where [id] = @.id)
begin
set @.sql = N'select [' + (select top 1 col1 from table1 where [id] =
@.id) + N'] from table2'
exec sp_executesql @.sql
end
return @.@.error
go
exec dbo.proc1 1
exec dbo.proc1 2
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Malkesh Sheth via webservertalk.com" wrote:

> I have one table in one of its column it stored column name of another
> table. i want select statement to select that column name. i will explain
> this with example
> Table1 has columns with name ID,col1 and having data in that column as
> (1,tb_col1),(2,tb_col2),(3,tb_col3) etc
> and another table has this columndata of table one as column name means
> table2 has column (tab_id2,tb_col1,tb_col2,tb_col3)
> inthis if i select ID=1 from table1 then select statement should show me
> data in column tb_col1 of table2..
> if i select ID=2 from table1 then select statement should show me data in
> column tb_col2 of table2..
> here data in column col1 of table1 may change any time so i want it
> dynamically
> --
> Message posted via http://www.webservertalk.com
>|||>> I have one table in one of its column it stored column name of
another table. <<
This is called mixing data and metadata. Good SQL programmers do not
write code like this! Bad SQL programmers use Dynamic SQL and lose
their data integrity.

Tuesday, March 27, 2012

Data Source file on Reporting Services server question

Thursday, March 22, 2012

Data search

Data Restore on Elm Street!

Data Restore on Elm Street!

Data Report Problem

Tuesday, March 20, 2012

Data Recovery Book

Thursday, March 8, 2012

Data Missing from Report

Saturday, February 25, 2012

Data manipulation

Sunday, February 19, 2012

Data in one column contains column name of other table

Data transformation services

Blog Archive

About Me