Comparing order of fields in two copies of sql server database

I have a need to look at 2 copies of the same database structure and
check certain tables to make sure the fields are in the same order.
IE, if someone adds a field to table A in Database 1 in position 5,
and then adds the same field to Table A in Database 2 in position 10,
the two databases have all the same fields, but not in the same order.
Is there a way to check for this and change the order of the fields
in one database so it is the same as the other?
Thanks in advance for your help.
Carol
carol.cooper@.comcast.netthis is just an idea. If the 2 tables are from 2 DBs then you might need to
get the actual object ID of the table in that DB to replace object_id
select name from DB1..syscolumns where id = object_id('table A') order by
colid
join
select name from DB2..syscolumns where id = object_id('table B') order by
colid
"Carol Cooper" <carol.cooper@.comcast.net> wrote in message
news:9b2ed4c6.0307291339.30f3b6b2@.posting.google.com...
> I have a need to look at 2 copies of the same database structure and
> check certain tables to make sure the fields are in the same order.
> IE, if someone adds a field to table A in Database 1 in position 5,
> and then adds the same field to Table A in Database 2 in position 10,
> the two databases have all the same fields, but not in the same order.
> Is there a way to check for this and change the order of the fields
> in one database so it is the same as the other?
> Thanks in advance for your help.
> Carol
> carol.cooper@.comcast.net|||Carol,
You can compare the two tables by querying the information schema views on
each database, assuming they are on the same server. You could use a full
outer join and filter out matches. Here's an example:
SELECT
c1.table_name
, c1.column_name
, c1.ordinal_position
, c2.table_name
, c2.column_name
, c2.ordinal_position
FROM Database1.INFORMATION_SCHEMA.COLUMNS c1
FULL JOIN Database2.INFORMATION_SCHEMA.COLUMNS c2
ON c1.TABLE_NAME = c2.TABLE_NAME
AND c1.COLUMN_NAME = c2.COLUMN_NAME
AND c1.ORDINAL_POSITION = c2.ORDINAL_POSITION
WHERE
(
c1.TABLE_NAME IS NULL
OR
c2.TABLE_NAME IS NULL
OR
c1.COLUMN_NAME IS NULL
OR
c2.COLUMN_NAME IS NULL
OR
c1.ORDINAL_POSITION IS NULL
OR
c2.ORDINAL_POSITION IS NULL
)
When you find a difference, you'll need to fix the table using ALTER TABLE
commands. If you decide to use the scripts from Enterprise Manager's Design
Table dialog, make sure you read them carefully first. No matter what you
do, back up youir data first and figure out a rollback plan just in case.
Ron
--
Ron Talmage
SQL Server MVP
"Carol Cooper" <carol.cooper@.comcast.net> wrote in message
news:9b2ed4c6.0307291339.30f3b6b2@.posting.google.com...
> I have a need to look at 2 copies of the same database structure and
> check certain tables to make sure the fields are in the same order.
> IE, if someone adds a field to table A in Database 1 in position 5,
> and then adds the same field to Table A in Database 2 in position 10,
> the two databases have all the same fields, but not in the same order.
> Is there a way to check for this and change the order of the fields
> in one database so it is the same as the other?
> Thanks in advance for your help.
> Carol
> carol.cooper@.comcast.net

Compare 2 DB

Hi,
I need to compare 2 DB (Tables, Colums etc) in order two calculate/determine
the difference between both, does somebody knows an aplication which is able
to do this? It would be great if this aplication would also be able to
create a script, after comparison, which aplies to one of the DB what the
other has differente/more. I hope I made understand myself!! :-) If the
aplication is able to compare two Tables would be enough.
I apreciate any help, thanks.
Marcelo
SQLCompare from www.red-gate.com is the tool I use for that.
Jacco Schalkwijk
SQL Server MVP
"Marcelo Moreira" <marcelo.moreira@.vpconsulting.pt> wrote in message
news:eoh%234BEYFHA.252@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need to compare 2 DB (Tables, Colums etc) in order two
> calculate/determine the difference between both, does somebody knows an
> aplication which is able to do this? It would be great if this aplication
> would also be able to create a script, after comparison, which aplies to
> one of the DB what the other has differente/more. I hope I made understand
> myself!! :-) If the aplication is able to compare two Tables would be
> enough.
> I apreciate any help, thanks.
> Marcelo
>

Compare 2 DB

Hi,
I need to compare 2 DB (Tables, Colums etc) in order two calculate/determine
the difference between both, does somebody knows an aplication which is able
to do this? It would be great if this aplication would also be able to
create a script, after comparison, which aplies to one of the DB what the
other has differente/more. I hope I made understand myself!! :-) If the
aplication is able to compare two Tables would be enough.
I apreciate any help, thanks.
MarceloSQLCompare from www.red-gate.com is the tool I use for that.
Jacco Schalkwijk
SQL Server MVP
"Marcelo Moreira" <marcelo.moreira@.vpconsulting.pt> wrote in message
news:eoh%234BEYFHA.252@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need to compare 2 DB (Tables, Colums etc) in order two
> calculate/determine the difference between both, does somebody knows an
> aplication which is able to do this? It would be great if this aplication
> would also be able to create a script, after comparison, which aplies to
> one of the DB what the other has differente/more. I hope I made understand
> myself!! :-) If the aplication is able to compare two Tables would be
> enough.
> I apreciate any help, thanks.
> Marcelo
>

Communications between Datasets

Is there any way to put together data from multiple datasets into one in
order to generate a single report?
ThanksHi,
You can define multiple datasets into the same report definition in order to
put their data in the same report.
In the other hand, if you want to put data from different datasets in the
same table or list, you need to use subreports (See post tittled "Fields
from Multiple Datasets" into this news group.
Hope this helps,
Mónica
"markgoldin" <markgoldin_2000@.yahoo.com> escribió en el mensaje
news:364A7754-9D67-4F8A-8528-CEEE5A924C46@.microsoft.com...
> Is there any way to put together data from multiple datasets into one in
> order to generate a single report?
> Thanks|||On Dec 12, 6:23 am, "markgoldin" <markgoldin_2...@.yahoo.com> wrote:
> Is there any way to put together data from multiple datasets into one in
> order to generate a single report?
> Thanks
You may put all your data into text boxes from several datasets. You
can also use tables, but one dataset per table. So if you don't mind
having separate datasets on separate tables then go for it.

Comment thread for this forums "Hint" sticky

My God! What happens if I miss a step, or put it in a different order? I know I can specify ORDER BY StepID, but what about a missing step? I do have "missing ranges" script, but posting questions filtered through the script process may become a full-time job in itself...What to do, what to do...now now trotsky. that'll do pig. that'll do.

I hate to say it but i have to agree with Brett. Partially because he's right but mostly because it will upset you.

it would help if some people were more clear with their questions and descriptions of issues. but many a time that is due to:

A lack of understaning of the concepts required to ask the questioni
A lack of understanding of english
but so what?

it just makes it less likely that they will get their question answered.

I'll leave you with this.
when and if i ever have a question for this site, i will try to be as clear and forthcoming with information as possible as my post languishes in the unanswered pile. :D|||Never mind

EDIT: Note to self...don't post when a). It's near midnight, and b). You've been "entertaining"|||now now trotsky. that'll do pig. that'll do.

I love that movie

I hate to say it but i have to agree with Brett. Partially because he's right

A slap and a compliment...ahh, just like when I was dating...I wouldn't have it any other way.....

ummm...what do you mean partially? I usually completely satisfy my dates...

No need for debates...the whole goal here is to provide solutions...no?|||how about we try this.
anytime you dont like the way someone asks a question, answer the question as yoda

"correlated subquery. you should not use".|||Wouldn't that be...

"Use, you should not"

But what's wrong with a coorelated sub query?|||isnt that correlated?
see you are already on your way to helping people find the way.

There is nothing "Wrong" with CSubs per se but you will have to acknowledge that they come with performance issues because of back and forth negotiation between the inner and outer queries. and Microsoft suggests in the KB that there are a few issues with them (having pushed out a few hotfixes just for CSubs).
while they may be neccessary at times I have always tried to rewrite them if at all possible.|||guys, do you realize that if your objective is to get newbies to read the guidelines for how to post a question, that the more bumph you add to the thread, the less likely they are to read it

sticky threads should (unless they are "resource" threads, i.e. lists of links, etc.) be limited to one brief post, the briefer the better|||Ok, I know that in the long lost Corral I wrote a post that said I had a real problem with editing threads, but I'll make an exception in this case because this is a sticky thread. Threads like this are intended to convey specific, highly focused information, particularly for new users that won't take time to wade through interesting but off-topic commentary that really belongs in the Corral.

Rudy: I'd appreciate it if you'd actively prune all off topic (not directly related to post #1) postings from this thread. I understand that implies pruning this posting, since it doesn't directly contribute to what this thread is about. If possible, I'd also "lock" the thread to prevent off topic posts.

-PatP|||brett, this is your thread, any objections to pat's recommended pruning?|||but i digress...

Kill it!|||prune away, and lock it.

Thanks|||You Can Do Transplants?|||That's what "pruning" is... Cutting divergent posts to a different (possibly disposable) thread.

Now we can yammer all we like without diulting the value of the sticky post. If somebody comes up with something useful to add to the original post, Rudy can prune it from here back into the (now locked) thread for reference.

-PatP|||hey brett, in your sticky, you have instructions for how to script the table in EM

have you seen how to do it in QA?

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1129984,00.html

some people don't like EM, does they

CommandText on Report Server

When a report runs is it possible to retrieve the command text and modify it in order to append some additional "where" conditions, before the sql is processed to return data to the report?

You can write code like this

declare sSQL varchar(500)

declare sWhere varchar(200)

set sSQL = "Select * from mytable"

if lenght(@.Parameter1) > 0

begin

sWhere = " Where mycolume = '" + @.Parameter2 + "'"

end

sSQL = sSQL + sWhere

EXEC (sSQL)

|||

Thanks for the reply, but I need to be clearer with the question that I am asking.

I have written code to construct the piece of SQL that I need to append - it retrieves conditions from the database which define some additional filtering for security.

What I need to know is if it is possible at runtime to trap the SQL statement stored in the <CommandText> element of the RDL for the report that is running - append my additional segment of SQL just before it gets processed. Bear in mind that a Report designed may have several SQL statements from individual parts of the overall report - a data set tp provide values for parameters, a dataset for each section on a report, chart and matrix for example.

Should I be looking at doing it via a Data Processing extension, therefore trapping any report that that is run via SSRS?

comma separated list into stored procedure and order of records

I'm passing a comma separated list into a stored procedure
e.g. exec usp_returnProductsFromIdArray '5,4,1,99'
The comma list is being turned into a table (see usp_IntListToTable below)
Here's the proc that returns products that are in the list
CREATE procedure usp_returnProductsFromIdArray
@.prodIdList varchar(1000) = null
as
CREATE TABLE #prodIdTable (productId BIGINT)
if (@.prodIdList is not null)
EXEC usp_IntListToTable @.prodIdList,'#prodIdTable'
select productId, productTitle from products where productId in
(select productId from #prodIdTable)
drop table #prodIdTable
This works well, except I'd like the order of records returned to follow the
order of the IDs in the comma separated list, they aren't, they are in
ascending order (due to the key on the products table)
/*
How do I simulate an array inside a stored procedure?
http://www.aspfaq.com/show.asp?id=2248
*/
CREATE PROCEDURE dbo.usp_IntListToTable
@.cslist VARCHAR(8000),
@.tablename SYSNAME AS
BEGIN
DECLARE @.spot SMALLINT, @.str VARCHAR(8000), @.sql VARCHAR(8000)
WHILE @.cslist <> ''
BEGIN
SET @.spot = CHARINDEX(',', @.cslist)
IF @.spot>0
BEGIN
SET @.str = CAST(LEFT(@.cslist, @.spot-1) AS INT)
SET @.cslist = RIGHT(@.cslist, LEN(@.cslist)-@.spot)
END
ELSE
BEGIN
SET @.str = CAST(@.cslist AS INT)
SET @.cslist = ''
END
SET @.sql = 'INSERT INTO '+@.tablename+'
VALUES('+CONVERT(VARCHAR(100),@.str)+')'
EXEC(@.sql)
END
END
GOAdd a new column to the temp table to define the sequence. Increment an
integer value in the WHILE loop and insert that into the sequence
number column. Then do:
SELECT P.productid, P.producttitle
FROM products AS P
JOIN #prodIdTable AS T
ON P.productid = T.productid
ORDER BY T.sequence_no ;
David Portas
SQL Server MVP
--|||It works
Thanks very much!!!
www.xwords.co.uk
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129194249.222211.192330@.o13g2000cwo.googlegroups.com...
> Add a new column to the temp table to define the sequence. Increment an
> integer value in the WHILE loop and insert that into the sequence
> number column. Then do:
> SELECT P.productid, P.producttitle
> FROM products AS P
> JOIN #prodIdTable AS T
> ON P.productid = T.productid
> ORDER BY T.sequence_no ;
> --
> David Portas
> SQL Server MVP
> --
>