Comparing two databases

Is there a way to compare the strored procedure,views and UDF's between two
databases to see if there are any differences. I use one database for
developement and the other is online. I would like to be able to run a
structural comparison between the two to make sure i didn't forget to script
a function or stored procedure after making modifications. I normally just
script all objects from developement to online after mods but I would like t
o
know for certain they are both the same sometimes. Also I would like to read
up on best practices for tracking developement so if you know of any good
reading that would help me. I use MS Access project as a front end and SQL
2000 as the Be. Thanks> Is there a way to compare the strored procedure,views and UDF's between
> two
> databases to see if there are any differences. I use one database for
> developement and the other is online. I would like to be able to run a
> structural comparison between the two to make sure i didn't forget to
> script
> a function or stored procedure after making modifications.
SQL Compare 4.0
http://www.red-gate.com/|||"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:3A5AF648-8D41-441A-BA91-0EF14B5C09A0@.microsoft.com...
> Is there a way to compare the strored procedure,views and UDF's between tw
o
> databases to see if there are any differences. I use one database for
We use SQL Delta. Does wonders for our spare time! (maintaining/updating app
rox. 25 DB
installations)
http://www.sqldelta.com|||You should be checking your development and production scripts into some
type of source version control system. For example, Visual Source Safe has
an option to compare two projects and list files that are different, and it
has a feature for comparing two versions of a script side by side with
differences highlighted.
Also, you can script the databases to seperate folders and use a tool like
WinMerge to perform the comparisons:
http://groups.google.com/group/micr...br />
46abfa76
Rather than scripting all objects from development to production in bulk,
you need to identify specific objects that have changed and deploy them
individually. There are a number of reasons, but for one, you run the risk
of accidentally running a script that drops / recreates a table thus
resulting in data loss.
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:3A5AF648-8D41-441A-BA91-0EF14B5C09A0@.microsoft.com...
> Is there a way to compare the strored procedure,views and UDF's between
> two
> databases to see if there are any differences. I use one database for
> developement and the other is online. I would like to be able to run a
> structural comparison between the two to make sure i didn't forget to
> script
> a function or stored procedure after making modifications. I normally just
> script all objects from developement to online after mods but I would like
> to
> know for certain they are both the same sometimes. Also I would like to
> read
> up on best practices for tracking developement so if you know of any good
> reading that would help me. I use MS Access project as a front end and SQL
> 2000 as the Be. Thanks

Comparing text in WHERE

Hi everyone,

I am compare to text fields like this:

CREATE PROCEDURE dbo.PCAttByVal
(
@.Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value=@.Value

END

But the environment (I use VS2005) says that the data type is incompatible with the equal operator. Then I tried:

CREATE PROCEDURE dbo.PCAttByVal
(
@.Value text
)
AS
BEGIN

SET NOCOUNT ON
SELECT ID FROM PCAtt WHERE Value IN '('+@.Value+')'

END

But nada (nothing). Any ideas? I can't change the data type since this field will hold values of different sizes.

Thank you for your input in advance!

hi,

text/ntext datatype does not support this kind of operation/comparison... you can find the "available" methods against these datatypes in http://msdn2.microsoft.com/en-us/library/ms187993.aspx..

these datatype are not good candidates for "filtering" operations as well.. consider that text can hold up to 2gb of data, and it's not worth the problem to support such heavy features..

so you can end up with "workarounds" if they fit your needs, similar to

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, Value text NULL ); GO INSERT INTO dbo.TestTB VALUES ( 1 , 'some text' ); INSERT INTO dbo.TestTB VALUES ( 2 , 'some other text' ); GO DECLARE @.key varchar(10); SET @.key = 'some text'; SELECT * FROM dbo.TestTB WHERE SUBSTRING( Value, 1, DATALENGTH(@.key)) = @.key; GO DROP TABLE dbo.TestTB; --<- Id Value -- 1 some text

but consider the other methods as well..

if you are using SQL Server 2005, consider moving the text/ntext datatypes to varchar(MAX)/nvarchar(MAX), as text datatype has been deprecated and, more usefull, varchar(MAX) supports all the traditional "string" operations...

regards

sqlsql

Comparing Records

Is there a Stored Procedure that compares 2 records and give you the
diffrences between them? Or do I have to manually compare each record?You will have to write one depending on what you want to compare and how you
want to retrieve the differences. For direct data comparison there are
certain 3rd party tools from companies like RedGate which you can buy off
the shelf.
Anith|||I wanted to do this from a trigger. ie see what changes there are from
the old record to the new record.|||You'd have to use the inserted and deleted tables within the trigger to do
this. Check out the topic CREATE TRIGGER in SQL Server Books Online; there
are certain examples which details how you can use them.
If you still have difficulty in coming up with a required solution, pl.
refer to www.aspfaq.com/5006 and post required information for others to
repro your problem.
Anith|||Thanks, I know how to do triggers. But you answered my question so
thanks.

Comparing Keys

Hi! I Have a Table with composed Primary Key and need to validate in a
stored procedure that new rows from a temporary table that would be inserted does not exist already.
I use to have a code similar to this for many tables, even with composed keys

Delete Det_Order

Where Ltrim(Rtrim(Convert(Char(20),id))) +
Ltrim(Rtrim(Convert(Char(20),renglon)))

In (Select Ltrim(Rtrim(Convert(Char(20),id))) +
Ltrim(Rtrim(Convert(Char(20),renglon)))
From ##TmpDet_Order )

But I just dont now what happen with this case that the process just hold and never ends. Both fields that I have in as keys are integer. Would you have any idea of what happens? or maybe you can tell me another way to make the same. Please ...

I have also tried this..
Delete from Det_Order
Where [id] + renglon

In (Select [id] + renglon
From ##Det_Order )

Rather than use the IN condition, try the TSQL DELETE extension; something like:

Delete Det_Order
from ##TmpDet_Order a
join Det_Order b
on a.[id] = b.[id]
and a.renglon = b.renglon

In general, I try to use the TSQL extensions sparingly, but this seems like a good spot for use.

|||I have also tried Kent, but have the same problem |||

Try running this query and let us know record count returned; have you tried limiting the number of rows being deleted in a single batch?

select count(*)
from ##TmpDet_Order (nolock) a
join Det_Order (nolock) b
on a.[id] = b.[id]
and a.renglon = b.renglon

|||

ok, my rowcount is 4677

The purpose of this process is to run every day and getting data from a day before.

|||

Well, that isn't nominally enough to grieve anything. My knee-jerk reaction is to run profiler to see what is going on; you also might be able to get at this by running SP_LOCK while your delete query is hung because it sounds like you might be blocked. If you will perform a search on this site you will find posts that discuss diagnosis of performance problems.

More importantly, I'd like to recruit opinions from others that are more experienced.

Kent

|||I could be a resource contention issue. I would also check the indexing on the key.|||

How long does it take for a similar SELECT query to execute?

select

b.*

from ##TmpDet_Order a
join Det_Order b
on a.[id] = b.[id]
and a.renglon = b.renglon

I have occasionally encountered problems with DELETE statements that are deleting from large tables.

How many rows are in Det_Order?

How many indexes are associated with Det_Order?

Are any of the indexes on Det_Order CLUSTERED indexes? Maintaining clustered indexes has a performance penalty -- but I would not have expected it to be substantial when deleting only 4700 rows.

|||

Ok, I have a clusterd index on the field last_date_change and have anotherone with id + renglon.

Could it afect the order?

|||

If your table is large, my understanding is that considerable effort must be expended to adjust the data to conform to the clustered index. Such maintenance is required merely as a result of deleting records -- it does not depend on the columns you used to determine which records to delete.

But you cannot have TWO clustered indexes on a single table.

You never told us how large your table is (rows, as well as megabytes), only that you were deleting 4700 rows, or so.

Dan

|||Oh yes, my table has 5,917,688 rows.. and it has a cluster index by the field of date.|||

That sounds like a lot of data to move about, to maintain the clustered index.

In any case, I would like to suggest that you make a copy of your table and change the CLUSTERED index to a NON-CLUSTERED index. Test your query against this copy of the table. (Unfortunately this change will increase the size of your indexes (by approx. 50 MB, if a DATETIME column is the only column of the CLUSTERED index), but I am expecting that it will improve the performance of any INSERT, UPDATE, and DELETE actions you perform on this table.)

If you frequently perform millions of INSERT, UPDATE, and DELETE actions on this table, you may want to consider using FILLFACTOR = 50, or maybe 80, depending on the quantity of such actions. (For some of my processing, where I have only 100,000 rows in a table when I create the index, but at later stages of processing I add another 200,000 rows that will be intermingled with these others, I set the FILLFACTOR = 30 to leave room in the index for the new rows. Some of my INSERT actions that formerly took 7-10 minutes then took only a few seconds.)

|||Thanks everyobody : )

comparing datime(SQL ) Year, Month, Day,Time

hi

i want to comparetow dates in my procedure, comparing all(year, month, day, time).

can anyone help me. thanhs.

Take a look following link

http://www.databasejournal.com/features/mssql/article.php/2209321

Hope this help!!!

Comparing Date to SQL Server Date

Hey everyone...I need to create a job which occurs once monthly that is based on a stored procedure that contains as a rough example:

CREATE PROCEDURE CompareDate
@.CurrentDate datetime

AS

SELECT * FROM Contracts WHERE EndDate LIKE (CURRENT SERVER DATE)
GO

How do I declare a parameter to automatically get the current server date in the stored procedure? Also, once it is declared how can I add Months to it? For example this job is executed monthly and is supposed to select contracts that expire 2 months from the current month. So once I get the current server date, how do I add 2 months to the date in the stored procedure? Thanks very much in advance!!!Use getdate() function to get the current server date.

compare two string in SQL Server

Hi,
I am writing a Store Procedure for Login, as following:
@.p_sUsername, @.p_sPassword are parameters
...
SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
username = @.p_sUsername AND Passwort = @.p_sPassword
...
but the SELECT Statement can not differ Uppercase and Lowercase, that means,
"Martin" = "martin"
Then I check explicitly:
if @.BName != @.p_sUsername
Login = 0
but this comparing works the same.
What can I do?
Thanks
Martinif you SQL Server database is not case sensitive you have to convert to to a
comparable format ,e.g. varbinary or specifiy a CASE Sensitive (CS) Collatio
n
for it:
Select 1 where 'test' = 'TEST'
GO
Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST' COLLATE
SQL_Latin1_General_CP1_CS_AS
GO
Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
Select 1 where 'test' = 'TEST'
GO
Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST' COLLATE
SQL_Latin1_General_CP1_CS_AS
GO
Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Martin" wrote:

> Hi,
> I am writing a Store Procedure for Login, as following:
> @.p_sUsername, @.p_sPassword are parameters
> ...
> SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
> username = @.p_sUsername AND Passwort = @.p_sPassword
> ...
> but the SELECT Statement can not differ Uppercase and Lowercase, that mean
s,
> "Martin" = "martin"
> Then I check explicitly:
> if @.BName != @.p_sUsername
> Login = 0
> but this comparing works the same.
> What can I do?
> Thanks
> Martin
>
>|||Case-sensitivity is determined by the column collation. So choose a
case-sensitive collation. For example:
ALTER TABLE benutzer
ALTER COLUMN username VARCHAR(128) COLLATE Latin1_General_CS_AS ;
David Portas
SQL Server MVP
--|||Thanks
Martin
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1125571037.540712.199690@.g43g2000cwa.googlegroups.com...
> Case-sensitivity is determined by the column collation. So choose a
> case-sensitive collation. For example:
> ALTER TABLE benutzer
> ALTER COLUMN username VARCHAR(128) COLLATE Latin1_General_CS_AS ;
> --
> David Portas
> SQL Server MVP
> --
>|||thanks
Martin
"Jens Smeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
schrieb im Newsbeitrag
news:877CE479-F0DF-4CDA-8720-A14908BFB207@.microsoft.com...
> if you SQL Server database is not case sensitive you have to convert to to
a
> comparable format ,e.g. varbinary or specifiy a CASE Sensitive (CS)
Collation
> for it:
> Select 1 where 'test' = 'TEST'
> GO
>
> Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST'
COLLATE
> SQL_Latin1_General_CP1_CS_AS
> GO
> Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
> Select 1 where 'test' = 'TEST'
> GO
>
> Select 1 where 'Test' COLLATE SQL_Latin1_General_CP1_CS_AS = 'TEST'
COLLATE
> SQL_Latin1_General_CP1_CS_AS
> GO
> Select 1 Where CAST('Test' as varbinary) = CAST('TEST' as varbinary)
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Martin" wrote:
>
means,|||To add to the other responses, if you force a case-sensitive compare in your
SQL statement, consider also including the normal compare in your WHERE
clause. This will allow SQL Server to efficiently use indexes on those
columns and thereby improve performance.
SELECT
@.BName = B.username,
@.BPW = Password
FROM BENUTZER as B
WHERE
username COLLATE SQL_Latin1_General_CP1_CS_AS = @.p_sUsername AND
username = @.p_sUsername AND
password COLLATE SQL_Latin1_General_CP1_CS_AS = @.p_sPassword AND
Password = @.p_sPassword
Hope this helps.
Dan Guzman
SQL Server MVP
"Martin" <martinsm@.freenet.de> wrote in message
news:Okeml6trFHA.304@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I am writing a Store Procedure for Login, as following:
> @.p_sUsername, @.p_sPassword are parameters
> ...
> SELECT @.BName = B.username, @.BPW = Password FROM BENUTZER as B WHERE
> username = @.p_sUsername AND Passwort = @.p_sPassword
> ...
> but the SELECT Statement can not differ Uppercase and Lowercase, that
> means,
> "Martin" = "martin"
> Then I check explicitly:
> if @.BName != @.p_sUsername
> Login = 0
> but this comparing works the same.
> What can I do?
> Thanks
> Martin
>

Compare timestamps then delete

What I am trying to do is create a stored procedure that compares a the current datetime to a datetime field already added to a table. I also want it to compare the two and if the old data that is collected in the table is over 6 months old I want it deleted.

Someone please help

Quote:

Originally Posted by JReneau35

What I am trying to do is create a stored procedure that compares a the current datetime to a datetime field already added to a table. I also want it to compare the two and if the old data that is collected in the table is over 6 months old I want it deleted.

Someone please help

delete ... where datediff(mm,datefield,getdate()) > 6|||

Quote:

Originally Posted by ck9663

delete ... where datediff(mm,datefield,getdate()) > 6

Will this function automatically change with each month. So I don't have to plug in the month everytime it changes.|||

Quote:

Originally Posted by JReneau35

Will this function automatically change with each month. So I don't have to plug in the month everytime it changes.

datediff() gets the difference between the start data and end date. the "mm" signifies you're trying to get the difference expressed in number of months. getdate() is a function that returns the system date.

essentially, you're deleting the record if the difference between the datefield (content of your field) and the system date is more then 6 months ... if you need to include 6 months and older, do a "=>" instead

Compare Stored Procedures

Is there a way to compare Stored procedure definitions
in different databases?
Eg. I have database A with Stored procedure Sp1
and database B with Stored procedure Sp1
how can I compare the definitions of Sp1 in two databases?
Thank you in advance-
-Asok
Check out SQL Compare by Red-Gate at www.red-gate.com
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Asok" <anonymous@.discussions.microsoft.com> wrote in message
news:fca701c43e95$7d34d480$a101280a@.phx.gbl...
> Is there a way to compare Stored procedure definitions
> in different databases?
> Eg. I have database A with Stored procedure Sp1
> and database B with Stored procedure Sp1
> how can I compare the definitions of Sp1 in two databases?
> Thank you in advance-
> -Asok
|||Several tools listed here:
http://www.aspfaq.com/2209
In addition, you could script your stored procedures to a file and use
WinDiff (which ships with Visual Studio).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Asok" <anonymous@.discussions.microsoft.com> wrote in message
news:fca701c43e95$7d34d480$a101280a@.phx.gbl...
> Is there a way to compare Stored procedure definitions
> in different databases?
> Eg. I have database A with Stored procedure Sp1
> and database B with Stored procedure Sp1
> how can I compare the definitions of Sp1 in two databases?
> Thank you in advance-
> -Asok
|||Hi,
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data.
You can find it here:
http://www.extreme.co.il

Compare Stored Procedures

Is there a way to compare Stored procedure definitions
in different databases?
Eg. I have database A with Stored procedure Sp1
and database B with Stored procedure Sp1
how can I compare the definitions of Sp1 in two databases?
Thank you in advance-
-Asokcheck out www.dbghost.com
>--Original Message--
>Is there a way to compare Stored procedure definitions
>in different databases?
>Eg. I have database A with Stored procedure Sp1
>and database B with Stored procedure Sp1
>how can I compare the definitions of Sp1 in two
databases?
>Thank you in advance-
>-Asok
>.
>|||Check out SQL Compare by Red-Gate at www.red-gate.com
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Asok" <anonymous@.discussions.microsoft.com> wrote in message
news:fca701c43e95$7d34d480$a101280a@.phx.gbl...
> Is there a way to compare Stored procedure definitions
> in different databases?
> Eg. I have database A with Stored procedure Sp1
> and database B with Stored procedure Sp1
> how can I compare the definitions of Sp1 in two databases?
> Thank you in advance-
> -Asok|||Several tools listed here:
http://www.aspfaq.com/2209
In addition, you could script your stored procedures to a file and use
WinDiff (which ships with Visual Studio).
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Asok" <anonymous@.discussions.microsoft.com> wrote in message
news:fca701c43e95$7d34d480$a101280a@.phx.gbl...
> Is there a way to compare Stored procedure definitions
> in different databases?
> Eg. I have database A with Stored procedure Sp1
> and database B with Stored procedure Sp1
> how can I compare the definitions of Sp1 in two databases?
> Thank you in advance-
> -Asok|||Hi
Try dbMaestro. It's a product that allows comparison, migration and archiving of database schema and data
You can find it here
http://www.extreme.co.i

Compare Stored Procedures

Is there a way to compare Stored procedure definitions
in different databases?
Eg. I have database A with Stored procedure Sp1
and database B with Stored procedure Sp1
how can I compare the definitions of Sp1 in two databases?
Thank you in advance-
-AsokCheck out SQL Compare by Red-Gate at www.red-gate.com
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Asok" <anonymous@.discussions.microsoft.com> wrote in message
news:fca701c43e95$7d34d480$a101280a@.phx.gbl...
> Is there a way to compare Stored procedure definitions
> in different databases?
> Eg. I have database A with Stored procedure Sp1
> and database B with Stored procedure Sp1
> how can I compare the definitions of Sp1 in two databases?
> Thank you in advance-
> -Asok|||Several tools listed here:
http://www.aspfaq.com/2209
In addition, you could script your stored procedures to a file and use
WinDiff (which ships with Visual Studio).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Asok" <anonymous@.discussions.microsoft.com> wrote in message
news:fca701c43e95$7d34d480$a101280a@.phx.gbl...
> Is there a way to compare Stored procedure definitions
> in different databases?
> Eg. I have database A with Stored procedure Sp1
> and database B with Stored procedure Sp1
> how can I compare the definitions of Sp1 in two databases?
> Thank you in advance-
> -Asok|||Hi,
Try dbMaestro. It's a product that allows comparison, migration and archivin
g of database schema and data.
You can find it here:
http://www.extreme.co.il

Compare SQL objects

Is there a stored procedure to allow me to compare two
SQL objects? In my case for example I want to compare
two stored procedures on two different databases?

If there is no SP that does comparison, would there be
any code in SQL DMO that does this?

How does someone learn SQL DMO? Does SQL Server
2000 have by default DMO learning material or I have to
search for books?

Thank youHi

You can try scripting them using DMO and then doing a file compare on the
two files.

Other options are Redgate compare:
http://www.red-gate.com/sql/summary.htm

OR QALite:
http://www.rac4sql.net/qalite_main.asp

John

"serge" <sergea@.nospam.ehmail.com> wrote in message
news:AtHje.74139$JU3.1442129@.wagner.videotron.net. ..
> Is there a stored procedure to allow me to compare two
> SQL objects? In my case for example I want to compare
> two stored procedures on two different databases?
> If there is no SP that does comparison, would there be
> any code in SQL DMO that does this?
> How does someone learn SQL DMO? Does SQL Server
> 2000 have by default DMO learning material or I have to
> search for books?
>
> Thank you|||"serge" <sergea@.nospam.ehmail.com> wrote in message
news:AtHje.74139$JU3.1442129@.wagner.videotron.net. ..
> Is there a stored procedure to allow me to compare two
> SQL objects? In my case for example I want to compare
> two stored procedures on two different databases?
> If there is no SP that does comparison, would there be
> any code in SQL DMO that does this?
> How does someone learn SQL DMO? Does SQL Server
> 2000 have by default DMO learning material or I have to
> search for books?
>
> Thank you

As John suggested, you can use the DMO Script method and compare the output.
This should be fine for procs, but it can be a problem for tables, because
you may have constraints which have been assigned a name by the system - the
constraint definition is identical, but it will show up as a difference in
your diff. You may or may not regard this as a problem, but tools such as
the Red Gate one allow you to configure your comparison to take this into
account, as well as whether or not to compare indexes, if a comparison
should be case-sensitive etc.

As for learning SQL-DMO - Books Online and practice. If you're already
familiar with COM programming, that would be useful, but it's far from
essential. Unfortunately the Books Online documentation doesn't provide many
examples - it tends to describe the steps to follow for a certain task, but
doesn't also give an actual code sample. Even if you want to use SQL-DMO
from a compiled language like VB or C#, I suggest you consider also using a
language such as Perl or Python - it's usually much faster for quick tests
and ad hoc experiments.

Simon|||There is another software tool that compares and synchronises databases
called DB Ghost (www.dbghost.com)
and it does data as well as schema. It is also the foundation for a
SQL Server change management process that works in harmony with any
source control/configuration management system to provide a completely
scalable solution for any size development team that has to work on the
same schema at the same time.

I highly recommend you check it out.

Compare DataBases......

Hi

I want Tom Compare two databases to see if there are stroed procedure \ table that exist in one database

but not exist in the other.....

how do i do it?

Thanks

hi,

Please find the solution below:

Code Snippet

--SQL 2005

select [name] from <database name>..sysobjects

where type = 'P'

except

select [name] from <database name>..sysobjects

where type = 'P'

--SQL 2000

select [name] from <database name>..sysobjects

where type = 'P'

and [name] not in (

select [name] from <database name>..sysobjects

where type = 'P')

|||

There are several good third party products that will compare databases, and provide you with scripts to make them the same (as well as reports). These products will not only compare the object names, but will also compare the object definitions to see if they are the same.

I suggest that you explore Red Gate's SQL Compare, or ApexSQL's SQL Diff products. Visual Studio for Database Professionals also has that capability. (Most have a 14-30 day fully featured evaluation version so that you can try them out.)

Comparison Tools
Object Comparison:
AdeptSQL Diff
AlfaAlfa Software - SQL Server Comparison Tool
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
e-Dule - DB SynchroComp
PrimeLogics - DataVision 2007
Quest – SchemaCompare
RAC4SQL's QALite (Free)
Red Gate – SQL Compare
SQL Effects Clarity
TASC - SQL Delta
Teratrax Database Compare
TulsaSoft - SQL Examiner
Voltex Data Systems - SQLDBcontrol
XpressApps - sqlXpress Diff
xSQL Software - xSQL Object
Data Comparison
ApexSQL – SQL Diff
Best SoftTool – SQL DBCompare
Quest - DataCompare
Red Gate – Data Compare
TASC - SQL Delta
TulsaSoft - SQL Data Examiner
xSQL Software - xSQL DataCompare
DTS Comparison
Red Gate – DTS Package Compare
Server Comparison
Quest - ServerCompare
Free Tools
RAC4SQL's QALite (Free)
SQL Effects Clarity CE Edition

|||

Lots of ways to do this as mentioned above. Also, the Visual Studio Team Edition for Database Professionals has this built in. you can also code your own:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=108&rl=1

Compact Database

Is there a stored procedure that allow the compact database programmatically
in SQL Server?"Claudio Di Flumeri" <claudioNOSPAM@.mtgc.net> wrote in message
news:c0conk$15i74v$1@.ID-198343.news.uni-berlin.de...
> Is there a stored procedure that allow the compact database
programmatically
> in SQL Server?

Check documentation for
DBCC SHRINKDATABASE|||"Claudio Di Flumeri" <claudioNOSPAM@.mtgc.net> wrote in message news:<c0conk$15i74v$1@.ID-198343.news.uni-berlin.de>...
> Is there a stored procedure that allow the compact database programmatically
> in SQL Server?

I believe compacting is an Access concept - it doesn't exist in MSSQL.
If you want to check the integrity of a database, then you can look at
DBCC CHECKDB; if you want to physically reduce the size of a database,
then DBCC SHRINKDATABASE and DBCC SHRINKFILE would help.

Simon

Common Table Expressions!

Hi all
I've checked the CTE's in SQL server 2005 beta 2 it is really a very useful feature but I want to use it with the output of a stored procedure. Is this available?

Thanks in advanceHi,

Can you please be more specific about what you want?

I understood you to say that you wanted to use the result set of a stored procedure as the operand to a relational JOIN operator inside a CTE. AFAIK, that is not allowed. AFAIK, the only place where you can use a stored proceure in a relational (table-valued) expression is the INSERT INTO table.. EXEC proc syntax.

However - I believe you should be able to use a table-valued function.

Regards,
Clifford Dibble

Committing a Stored Procedure

hi there,

I am connecting to a sql server express 2005 database which is located on another developers machine in the company. he has given me the username and password and i can read and write to this db using vs2005 no problems.

i can also added new tables and everything through the management studio, but when i add stored procedures, it does not seem to commit to the database, when i click save it asks for a local destination but i want it to save onto the sql server.

i can add stored procedures through vs 2005 no problems! this does not make sense?

Hi,

yes it sure does. You don′t have to save the procs you have to execute the DDL rather than saving the code. That should work.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Can you please explain how to execute the DDL step by step, because I have pressed EXECUTE and it just runs the query and displays the results|||OK, due to he fact that I don′t have your code right here and you didn′t pasted it in your previous post I assume that you don′t have any DDL code right now.

DDL code (especially for procs) begin with

CREATE PROCEDURE (...)

If you don′t have such DDL code, you just have a query. What code are you executing ?|||

Hi Jens,

I sorted it out...I clicked Stored Procedures>New stored procedure and from the Query Menu>Specify values for template> and then fill that form out and then it commits it to the database NO PROBLEMS!

Commit Transaction Gets Deleted - Unable to save SP

I've re-written a stored procedure and when I post the following code
into the existing SP in EM, is saves OK. However, when I re-edit the
SP, the last line 'Commit Transaction' has been removed.

I cannot save the remainder of the SP as it throws error 208 (Invalid
Object name #Max) about two of the temp tables I use when I post the
entire script. It shows in a message box with the header : 'Microsoft
SQL-DMO(ODBC SQLState:42S02)

I haven't posted the full SP nor the structure as it's quite large
(2000 lines), so hopefully I have given enough detail, but my questions
are :

Why does it now have problems with (temp) #Tables ? The use of these
has not changed. All I have done is wrap the script into various
transactions as this helps a lot for performance and tweaked a few
parts later in the SP again for performance.

Also, why does the line get removed once I save the SP ?

If I run this in QA, I get the same errors, so I suspect it's my
script, but don't know where I'm going wrong.

SQL2000 (Need to upgrade the service pack as recently installed on my
PC, so this may help)

Thanks in advance

Ryan

CREATE PROCEDURE [dbo].[JAG_Extract] (@.ExtractYear INTEGER,
@.ExtractMonth INTEGER) AS

BEGIN TRANSACTION

SELECT 0 AS MaxYear, 0 AS MaxMonth INTO #Max

UPDATE #Max SET MaxYear = @.ExtractYear
UPDATE #Max SET MaxMonth = @.ExtractMonth

PRINT 'Stage 1 - ' + Convert(VarChar, GetDate())
CREATE TABLE #Extract (
[DEALER_SOURCE_DATA_ID] INT,
[DSD_YEAR] INT NULL,
[DSD_MONTH] INT NULL,
[DEALER_CODE] VarChar(20),
[FranDealerCode] VarChar(20) NULL,
[Line_No] VarChar(75),
[Current] [numeric](15, 5) NULL,
[YTD] [numeric](15, 5) NULL,
[12Months] [numeric](15, 5) NULL,
[24Months] [numeric](15, 5) NULL,
[Average_YTD] [numeric](15, 5) NULL,
[Average12Months] [numeric](15, 5) NULL,
[Average24Months] [numeric](15, 5) NULL,
[Last_YTD] [numeric](15, 5) NULL,
[Current_STATUS] INT,
[PD1] [numeric](15, 5) NULL,
[PD2] [numeric](15, 5) NULL,
[PD3] [numeric](15, 5) NULL,
[PD4] [numeric](15, 5) NULL,
[PD5] [numeric](15, 5) NULL,
[PD6] [numeric](15, 5) NULL,
[PD7] [numeric](15, 5) NULL,
[PD8] [numeric](15, 5) NULL,
[PD9] [numeric](15, 5) NULL,
[PD10] [numeric](15, 5) NULL,
[PD11] [numeric](15, 5) NULL,
[PD12] [numeric](15, 5) NULL,
[PD13] [numeric](15, 5) NULL,
[PD14] [numeric](15, 5) NULL,
[PD15] [numeric](15, 5) NULL,
[PD16] [numeric](15, 5) NULL,
[PD17] [numeric](15, 5) NULL,
[PD18] [numeric](15, 5) NULL,
[PD19] [numeric](15, 5) NULL,
[PD20] [numeric](15, 5) NULL,
[PD21] [numeric](15, 5) NULL,
[PD22] [numeric](15, 5) NULL,
[PD23] [numeric](15, 5) NULL,
[PD24] [numeric](15, 5) NULL,
[PD25] [numeric](15, 5) NULL,
[PD26] [numeric](15, 5) NULL,
[PD27] [numeric](15, 5) NULL,
[PD28] [numeric](15, 5) NULL,
[PD29] [numeric](15, 5) NULL,
[PD30] [numeric](15, 5) NULL,
[PD31] [numeric](15, 5) NULL,
[PD32] [numeric](15, 5) NULL,
[PD33] [numeric](15, 5) NULL,
[PD34] [numeric](15, 5) NULL,
[PD35] [numeric](15, 5) NULL,
[PD36] [numeric](15, 5) NULL)

INSERT INTO #Extract

SELECT DISTINCT
SD.DEALER_SOURCE_DATA_ID,
SD.DSD_YEAR,
SD.DSD_MONTH,
DN.DEALER_CODE,
DN.FRAN_DEALER_CODE,
DV.FIELD_CODE,
0,
0,
0,
0,
0,
0,
0,
0,
SD.STATUS,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0

FROM
DEALER_NAW DN WITH (NOLOCK)
INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)
ON DN.DEALER_CODE = SD.DEALER_CODE
INNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)
ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_ID
AND SD.STATUS < 4096
INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)
ON DN.DEALER_CODE = GR.DEALER_CODE
AND GR.FIXED_GROUP_ID IN
(11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,4 9,50,
51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,1 12,
113,114,115,130,131,132,133,134,135,136,137)
GO
COMMIT TRANSACTIONYou need to remove 'GO' keyword.
Its a batch separator so you basically have two independent parts. Second
part is : COMMIT TRANSACTION.

MC

"Ryan" <ryanofford@.hotmail.com> wrote in message
news:1138113402.871088.270810@.g43g2000cwa.googlegr oups.com...
> I've re-written a stored procedure and when I post the following code
> into the existing SP in EM, is saves OK. However, when I re-edit the
> SP, the last line 'Commit Transaction' has been removed.
> I cannot save the remainder of the SP as it throws error 208 (Invalid
> Object name #Max) about two of the temp tables I use when I post the
> entire script. It shows in a message box with the header : 'Microsoft
> SQL-DMO(ODBC SQLState:42S02)
> I haven't posted the full SP nor the structure as it's quite large
> (2000 lines), so hopefully I have given enough detail, but my questions
> are :
> Why does it now have problems with (temp) #Tables ? The use of these
> has not changed. All I have done is wrap the script into various
> transactions as this helps a lot for performance and tweaked a few
> parts later in the SP again for performance.
> Also, why does the line get removed once I save the SP ?
> If I run this in QA, I get the same errors, so I suspect it's my
> script, but don't know where I'm going wrong.
> SQL2000 (Need to upgrade the service pack as recently installed on my
> PC, so this may help)
> Thanks in advance
>
> Ryan
> CREATE PROCEDURE [dbo].[JAG_Extract] (@.ExtractYear INTEGER,
> @.ExtractMonth INTEGER) AS
> BEGIN TRANSACTION
> SELECT 0 AS MaxYear, 0 AS MaxMonth INTO #Max
> UPDATE #Max SET MaxYear = @.ExtractYear
> UPDATE #Max SET MaxMonth = @.ExtractMonth
> PRINT 'Stage 1 - ' + Convert(VarChar, GetDate())
> CREATE TABLE #Extract (
> [DEALER_SOURCE_DATA_ID] INT,
> [DSD_YEAR] INT NULL,
> [DSD_MONTH] INT NULL,
> [DEALER_CODE] VarChar(20),
> [FranDealerCode] VarChar(20) NULL,
> [Line_No] VarChar(75),
> [Current] [numeric](15, 5) NULL,
> [YTD] [numeric](15, 5) NULL,
> [12Months] [numeric](15, 5) NULL,
> [24Months] [numeric](15, 5) NULL,
> [Average_YTD] [numeric](15, 5) NULL,
> [Average12Months] [numeric](15, 5) NULL,
> [Average24Months] [numeric](15, 5) NULL,
> [Last_YTD] [numeric](15, 5) NULL,
> [Current_STATUS] INT,
> [PD1] [numeric](15, 5) NULL,
> [PD2] [numeric](15, 5) NULL,
> [PD3] [numeric](15, 5) NULL,
> [PD4] [numeric](15, 5) NULL,
> [PD5] [numeric](15, 5) NULL,
> [PD6] [numeric](15, 5) NULL,
> [PD7] [numeric](15, 5) NULL,
> [PD8] [numeric](15, 5) NULL,
> [PD9] [numeric](15, 5) NULL,
> [PD10] [numeric](15, 5) NULL,
> [PD11] [numeric](15, 5) NULL,
> [PD12] [numeric](15, 5) NULL,
> [PD13] [numeric](15, 5) NULL,
> [PD14] [numeric](15, 5) NULL,
> [PD15] [numeric](15, 5) NULL,
> [PD16] [numeric](15, 5) NULL,
> [PD17] [numeric](15, 5) NULL,
> [PD18] [numeric](15, 5) NULL,
> [PD19] [numeric](15, 5) NULL,
> [PD20] [numeric](15, 5) NULL,
> [PD21] [numeric](15, 5) NULL,
> [PD22] [numeric](15, 5) NULL,
> [PD23] [numeric](15, 5) NULL,
> [PD24] [numeric](15, 5) NULL,
> [PD25] [numeric](15, 5) NULL,
> [PD26] [numeric](15, 5) NULL,
> [PD27] [numeric](15, 5) NULL,
> [PD28] [numeric](15, 5) NULL,
> [PD29] [numeric](15, 5) NULL,
> [PD30] [numeric](15, 5) NULL,
> [PD31] [numeric](15, 5) NULL,
> [PD32] [numeric](15, 5) NULL,
> [PD33] [numeric](15, 5) NULL,
> [PD34] [numeric](15, 5) NULL,
> [PD35] [numeric](15, 5) NULL,
> [PD36] [numeric](15, 5) NULL)
> INSERT INTO #Extract
> SELECT DISTINCT
> SD.DEALER_SOURCE_DATA_ID,
> SD.DSD_YEAR,
> SD.DSD_MONTH,
> DN.DEALER_CODE,
> DN.FRAN_DEALER_CODE,
> DV.FIELD_CODE,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> SD.STATUS,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0,
> 0
> FROM
> DEALER_NAW DN WITH (NOLOCK)
> INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK)
> ON DN.DEALER_CODE = SD.DEALER_CODE
> INNER JOIN DEALER_SOURCE_DATA_VALUES_Current DV WITH (NOLOCK)
> ON SD.DEALER_SOURCE_DATA_ID = DV.DEALER_SOURCE_DATA_ID
> AND SD.STATUS < 4096
> INNER JOIN DEALER_FIXED_GROUP_RELATION GR WITH (NOLOCK)
> ON DN.DEALER_CODE = GR.DEALER_CODE
> AND GR.FIXED_GROUP_ID IN
> (11,12,13,14,15,16,17,18,23,42,43,44,45,46,47,48,4 9,50,
> 51,52,53,54,55,56,57,58,59,60,61,106,109,110,111,1 12,
> 113,114,115,130,131,132,133,134,135,136,137)
> GO
> COMMIT TRANSACTION|||Sorted. Thanks for the pointer. I should have spotted that earlier.

Ryan

commit and/or rollback transaction error

Hi,
I have a stored procedure that uses the commit and rollback transaction
functionalities.
However, it keeps on throwing me the error:
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 3, current count = 4.
Can someone explain to me what does it mean by "Previous count" please?
ThanksHi
Posting the code will help, you may have a path though it where the BEGIN
TRANSACTION is not paired correctly to a COMMIT/ROLLBACK. This may be in a
stored procedure called by the main procedure.
Check out
http://support.microsoft.com/defaul...kb;en-us;158325
http://tinyurl.com/7rmo6
You may want to also read:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
There are also many posts on Google regarding this:
http://tinyurl.com/bjmw2
John
"Tina" wrote:

> Hi,
> I have a stored procedure that uses the commit and rollback transaction
> functionalities.
> However, it keeps on throwing me the error:
> Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
> TRANSACTION statement is missing. Previous count = 3, current count = 4.
> Can someone explain to me what does it mean by "Previous count" please?
> Thanks
>
>

commit and rollback problem

Hi,

I still haven't got a decent book on relational databases :-)

My stored procedure insert_wire inserts values into two tables (wire and
cablewire). The wire_ref (primary key) will be the same for both inserts.
However, if for any reason the first insert fails then I would like a
rollback system to take place. I have tried testing for an error (@.@.error
<> 0) after the 1st transaction but I just get a syntax error. Am I going
down the right lines here? Any tips appreciated.

Thanks, Mary.

CREATE procedure insert_wire(in wire_ref VARCHAR(22), in standard
VARCHAR(16), in a_color VARCHAR(16), in material VARCHAR(22),
in metres INTEGER, in amps FLOAT(3), in volts FLOAT(3), in ni SMALLINT, in
some_comment VARCHAR(32))
BEGIN
insert into cablewire
values(wire_ref, standard, a_color, material, metres, some_comment);
insert into wire
values(wire_ref, amps, volts, ni);
commit;
END!Mary Walker (123@.123.com) writes:
> I still haven't got a decent book on relational databases :-)
> My stored procedure insert_wire inserts values into two tables (wire and
> cablewire). The wire_ref (primary key) will be the same for both inserts.
> However, if for any reason the first insert fails then I would like a
> rollback system to take place. I have tried testing for an error (@.@.error
><> 0) after the 1st transaction but I just get a syntax error. Am I going
> down the right lines here? Any tips appreciated.

Probably not. Judging from the syntax in your posts, you are using
some other DB engine than Microsoft SQL Server, which is the RDBMS
this group is about. @.@.error, on the other hand is a feature in
MS SQL Server, that I would expect not appear anywhere else, except
for Sybase.

So I think you should first out what product you are using, and then
a forum for that product.

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

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

Comments in SP

Hi,
I was wondering if the amount of comments inside a stored procedure could
hinder in any way the performance of the same sp. Does anyone know?
regards,AFAIK, Nope they have no impact on performance ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Emmanuel" <emmanuel@.email.com> wrote in message
news:u%23PZw1zEFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I was wondering if the amount of comments inside a stored procedure could
> hinder in any way the performance of the same sp. Does anyone know?
>
> regards,
>
>|||Apart from the time the compiler takes to parse the symbols in the procedure
source.
This is almost negligable and certainly discountable for the benefits of
having comments in your code. If it needs an essay the its probably better
in an external file with a reference note in the sproc header.
Mr Tea
"Vinod Kumar" <vinodk_sct@.NO_SPAM_hotmail.com> wrote in message
news:cusgo5$h7q$1@.news01.intel.com...
> AFAIK, Nope they have no impact on performance ...
> --
> HTH,
> Vinod Kumar
> MCSE, DBA, MCAD, MCSD
> http://www.extremeexperts.com
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
> "Emmanuel" <emmanuel@.email.com> wrote in message
> news:u%23PZw1zEFHA.732@.TK2MSFTNGP12.phx.gbl...
could
>|||While i haven't done any tests yet I suspect that lots of comments can
indeed affect performance on a system with lots of sp's. The reason being
is that the comments are also stored in the syscacheobjects when the plan is
created. This has several effects. One is that it takes up more memory for
the procedure cache. It also takes a little more effort to create the hash
code and do the searching in the cache once the hash bucket is identified.
Don't take me wrong here, I am not advocating removing all comments<g>.
But I have seen some large systems that had massive amounts of comments that
when added up, was well over 100MB's in coments. That is memory that could
be used by other processes. The bottom line is that most systems will never
notice any difference if they removed all their comments. But technically I
believe it can potentially affect performance.
Andrew J. Kelly SQL MVP
"Emmanuel" <emmanuel@.email.com> wrote in message
news:u%23PZw1zEFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi,
>
> I was wondering if the amount of comments inside a stored procedure could
> hinder in any way the performance of the same sp. Does anyone know?
>
> regards,
>
>