Showing posts with label across. Show all posts
Showing posts with label across. Show all posts

Wednesday, March 21, 2012

Replace Apostrophies?

Hi All,

I have trigger that sends data from one table to another. I need to make sure that if the data that needs to be sent across has an apostrophe, that it is replaced by two apostrophies so it adheres to the SQL syntax and that it doesn't error.

Is it possible to use the REPLACE function for this or is there some other way to get around this??
Can I write REPLACE(@.VARIABLE, ''', '''') for this or won't this work??

If someone can give me anysight into this and any code on how to do this would be great.

Thanks
AnthonyREPLACE(@.VARIABLE, '''', ''')

Tuesday, March 20, 2012

Replace a string across multiple stored procedures

actually 2 questions.
a. Need to replace the string "server name" available on multiple stored
procedures p1,p2...p19
with ""?
can I do this with an update to system tables like syscomments?
b. Can I create a linked server to itself?> can I do this with an update to system tables like syscomments?
NO! Updates to system tables are not supported and are strongly
discouraged.
Script out the stored procedures as ALTER or DROP/CREATE (you can do this
using Enterprise Manager, Query Analyzer, or a host of other tools). Do the
replace operation in the output file, then apply that script to the server.
Have you heard of source control?|||Not to mention that changing the text in syscomments wouldn't actually
change the procedure, right?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OTgoNCFlFHA.3568@.TK2MSFTNGP10.phx.gbl...
> NO! Updates to system tables are not supported and are strongly
> discouraged.
> Script out the stored procedures as ALTER or DROP/CREATE (you can do this
> using Enterprise Manager, Query Analyzer, or a host of other tools). Do
> the replace operation in the output file, then apply that script to the
> server.
> Have you heard of source control?
>|||> Not to mention that changing the text in syscomments wouldn't actually
> change the procedure, right?
That's correct, but I was nipping any other potential "what if I update
this, or what if I update that..." before they come up. :-)|||Yeah, no doubt :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eCverKFlFHA.2852@.TK2MSFTNGP15.phx.gbl...
> That's correct, but I was nipping any other potential "what if I update
> this, or what if I update that..." before they come up. :-)
>

Monday, March 12, 2012

Repeating a Matrix With Different Values

I have a basic matrix with a title in the top lef corner, a list of locations
down the left hand side and one column per day of reporting across the top.
My problem is that the title is actually data from another column of my
recordset. I am repeating the matrix several times and filtering on this
value to obtain the relevant records for that matrix.
What I want is to automatically repeat the matrix however many times is
necessary for the recordset to be fully output. I thought adding the matrix
to the detail row of a table would acheive this but ssrs won't allow me to do
this.
I'm relatively new to ssrs - is there any way of doing this?If i Understand your concern.
You want to show different matrices for each distinct value in the
Column of a Record set
why can't you have a list containing Matix and Group the List on column
name you want to
Group .Hope this will help you
if you want more detail
please reply
regards
Raj Deep.A
rocket salad wrote:
> I have a basic matrix with a title in the top lef corner, a list of locations
> down the left hand side and one column per day of reporting across the top.
> My problem is that the title is actually data from another column of my
> recordset. I am repeating the matrix several times and filtering on this
> value to obtain the relevant records for that matrix.
> What I want is to automatically repeat the matrix however many times is
> necessary for the recordset to be fully output. I thought adding the matrix
> to the detail row of a table would acheive this but ssrs won't allow me to do
> this.
> I'm relatively new to ssrs - is there any way of doing this?|||I've managed to get a list object to repeat the matrix. But I still can't set
the filter for the matrix to reference the list's filter's current value.
Help is much appreciated.
"rocket salad" wrote:
> I have a basic matrix with a title in the top lef corner, a list of locations
> down the left hand side and one column per day of reporting across the top.
> My problem is that the title is actually data from another column of my
> recordset. I am repeating the matrix several times and filtering on this
> value to obtain the relevant records for that matrix.
> What I want is to automatically repeat the matrix however many times is
> necessary for the recordset to be fully output. I thought adding the matrix
> to the detail row of a table would acheive this but ssrs won't allow me to do
> this.
> I'm relatively new to ssrs - is there any way of doing this?|||My test data only included one value, so the matrix didn't repeat. I had
assumed that when it did, it wouldn't automatically reference the list. As it
happens, since I've altered my source data it works fine.
Thanks.
"RajDeep" wrote:
> If i Understand your concern.
> You want to show different matrices for each distinct value in the
> Column of a Record set
> why can't you have a list containing Matix and Group the List on column
> name you want to
> Group .Hope this will help you
> if you want more detail
> please reply
> regards
> Raj Deep.A
>
> rocket salad wrote:
> > I have a basic matrix with a title in the top lef corner, a list of locations
> > down the left hand side and one column per day of reporting across the top.
> >
> > My problem is that the title is actually data from another column of my
> > recordset. I am repeating the matrix several times and filtering on this
> > value to obtain the relevant records for that matrix.
> >
> > What I want is to automatically repeat the matrix however many times is
> > necessary for the recordset to be fully output. I thought adding the matrix
> > to the detail row of a table would acheive this but ssrs won't allow me to do
> > this.
> >
> > I'm relatively new to ssrs - is there any way of doing this?
>|||So you are trying to catch something which is not in the scope.matrix
can be in Scope of list.But list cannot be in Scope of matrix
Try filter in the dataset tab.
Frankly to speak i didn't understand what exactly you are trying to do.
Filter on the Dataset will filter the records which is returned by the
datasource.
Regards
Raj Deep.A
rocket salad wrote:
> I've managed to get a list object to repeat the matrix. But I still can't set
> the filter for the matrix to reference the list's filter's current value.
> Help is much appreciated.
> "rocket salad" wrote:
> > I have a basic matrix with a title in the top lef corner, a list of locations
> > down the left hand side and one column per day of reporting across the top.
> >
> > My problem is that the title is actually data from another column of my
> > recordset. I am repeating the matrix several times and filtering on this
> > value to obtain the relevant records for that matrix.
> >
> > What I want is to automatically repeat the matrix however many times is
> > necessary for the recordset to be fully output. I thought adding the matrix
> > to the detail row of a table would acheive this but ssrs won't allow me to do
> > this.
> >
> > I'm relatively new to ssrs - is there any way of doing this?

Friday, March 9, 2012

Repeat List across page, not just down?

I want to use a List (or similar), but I want the fields from each row of the source datatable to repeat across the page before wrapping to the next line. For example:

row1-FieldA row1-FieldB row2-FieldA row2-FieldB
row3-FieldA row3-FieldB row4... etc

Is this possible?

Quick and dirthy is to write the expression

select the required report item(table in ur list)

choose expression for property(value)

==Iif(RowNumber("table") mod 2,FieldA,"")

Saturday, February 25, 2012

reorganize data files

Just added a new datafile to my database. How do I distribute all the
data evenly across the two files now?
I tried dbcc dbreindex on all tables, this results in 75%/25%
I also tried creating 2 new files and emptying the main file dbcc
shrinkdb. I could not empty the main file completely though so had to
empty the 3rd file again and drop it.. this gave me a 40%/60%
Anyway to reorganize the datafiles and have 50/50 distribution?The cleanest way to ensure even distribution between all files is to export
the data, truncate the tables and import it back in again. The reason none
of the techniques worked is that SQL Server adds data to individual files
within the same file group by the percentage of free space in the file. So
if you start with 4 files of the same size and empty all the existing data
you have 4 files with equal amounts of free space. Then when you insert the
data it will use the proportional fill algorithm to fill them in in an even
manor. But when some of the files already have data in them the
distribution will be uneven due to the uneven amount of free space.
Eventually the amount of free space will even out. So if you run DBCC
DBREINDEX on all the tables many times it will eventually even out. The key
is to ensure all the files are the same size with PLENTY of free space in
each file. That way Autogrow does not kick in and mess with the even sizes.
Andrew J. Kelly SQL MVP
"Gordon Cowie" <gordy@.dynamicsdirect.com> wrote in message
news:uz%23WtZzBGHA.676@.TK2MSFTNGP10.phx.gbl...
> Just added a new datafile to my database. How do I distribute all the data
> evenly across the two files now?
> I tried dbcc dbreindex on all tables, this results in 75%/25%
> I also tried creating 2 new files and emptying the main file dbcc
> shrinkdb. I could not empty the main file completely though so had to
> empty the 3rd file again and drop it.. this gave me a 40%/60%
> Anyway to reorganize the datafiles and have 50/50 distribution?|||running dbreindex twice evened it out nicely, thanks!
Andrew J. Kelly wrote:
> The cleanest way to ensure even distribution between all files is to expor
t
> the data, truncate the tables and import it back in again. The reason none
> of the techniques worked is that SQL Server adds data to individual files
> within the same file group by the percentage of free space in the file. S
o
> if you start with 4 files of the same size and empty all the existing data
> you have 4 files with equal amounts of free space. Then when you insert th
e
> data it will use the proportional fill algorithm to fill them in in an eve
n
> manor. But when some of the files already have data in them the
> distribution will be uneven due to the uneven amount of free space.
> Eventually the amount of free space will even out. So if you run DBCC
> DBREINDEX on all the tables many times it will eventually even out. The k
ey
> is to ensure all the files are the same size with PLENTY of free space in
> each file. That way Autogrow does not kick in and mess with the even size
s.
>