anyone use a 3rd party product to load balance SQL Server?

I'm working on a project needing a high-end large mission critical database
solution. The data is in 1 database. So SQL 2000 doesn't support load
balancing, and neither will 2005. That sucks. Oracle supposedly does load
balancing and I can't understand why even SQL Server 2005 won't do it. So
either I go with an 8+ CPU SQL Server or consider a 3rd party load balancer
that will somehow (?) make it work so that multiple SQL brains can balance
the load of 1 single database that is presumably shared on 1 set of disks.
I'm finding very little information on load balancing SQL Server when
searching the net. Is there a product like Legato, for example, (I haven't
tried it) that will somehow give me load balancing with SQL Server?HK wrote:
> I'm working on a project needing a high-end large mission critical
> database solution. The data is in 1 database. So SQL 2000 doesn't
> support load balancing, and neither will 2005. That sucks. Oracle
> supposedly does load balancing and I can't understand why even SQL
> Server 2005 won't do it. So either I go with an 8+ CPU SQL Server or
> consider a 3rd party load balancer that will somehow (?) make it work
> so that multiple SQL brains can balance the load of 1 single database
> that is presumably shared on 1 set of disks.
> I'm finding very little information on load balancing SQL Server when
> searching the net. Is there a product like Legato, for example, (I
> haven't tried it) that will somehow give me load balancing with SQL
> Server?
You may want to take a look at Federated Database Servers on SQL Server
2000/2005 if that architecture will work for you.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:uzLN8u5wFHA.3180@.TK2MSFTNGP14.phx.gbl...
> HK wrote:
> > I'm working on a project needing a high-end large mission critical
> > database solution. The data is in 1 database. So SQL 2000 doesn't
> > support load balancing, and neither will 2005. That sucks. Oracle
> > supposedly does load balancing and I can't understand why even SQL
> > Server 2005 won't do it. So either I go with an 8+ CPU SQL Server or
> > consider a 3rd party load balancer that will somehow (?) make it work
> > so that multiple SQL brains can balance the load of 1 single database
> > that is presumably shared on 1 set of disks.
> >
> > I'm finding very little information on load balancing SQL Server when
> > searching the net. Is there a product like Legato, for example, (I
> > haven't tried it) that will somehow give me load balancing with SQL
> > Server?
> You may want to take a look at Federated Database Servers on SQL Server
> 2000/2005 if that architecture will work for you.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>
Creating the partitioned views sounds very challenging for an already-in-use
DB that needs to be cut over in the middle of the night. Plus ongoing
maintenance. Maybe I'm missing the boat, but that's why I'd want to stay
away from federated databases.

Anyone recently installed SQL Server 2000 Eval version

Hello,

I am having trouble installing Evaluation copy of SQL Server on my
notebook, which has XP, P4 processor,1G Ram, and 14 G harddisk space.

After I run the Setup.exe, I get the files files extracted in the
SQLEVAL directory.

As per the instruction, I need to run Autorun.exe file. For some
reason, when I try to run this file, a new windows pops up, which is
blank black screen with cursor flying around in the window. The title
of the window is
C:\windows\system32\wowexec.exe

This is how far I have reached while trying to install the database. I
would really appreciate if someone could provide some suggestions.

VS"TinTin" <lalalulu24@.yahoo.com> wrote in message
news:2d5425d1.0404021102.1ac7269f@.posting.google.c om...
> Hello,
> I am having trouble installing Evaluation copy of SQL Server on my
> notebook, which has XP, P4 processor,1G Ram, and 14 G harddisk space.
> After I run the Setup.exe, I get the files files extracted in the
> SQLEVAL directory.
> As per the instruction, I need to run Autorun.exe file. For some
> reason, when I try to run this file, a new windows pops up, which is
> blank black screen with cursor flying around in the window. The title
> of the window is
> C:\windows\system32\wowexec.exe
> This is how far I have reached while trying to install the database. I
> would really appreciate if someone could provide some suggestions.
> VS

I have no idea what the problem is, but perhaps you could try running the
setup program directly - I think it's called setupsql.exe, in the \setup
folder (from memory).

Simon|||> I
> would really appreciate if someone could provide some suggestions.

http://otn.oracle.com/software/prod...e10g/index.html :-)

Anyone producing a large number of reports per day?

I'm evaluting Reporting Services for my client. They need to produce 5000+
reports per day, mainly output in PDF format. The PDF's are 6-8 pages long.
Does anyone have any experience of getting this kind of performance out of
SQL-RS?
They have an 8-processor 2.8Ghz server running SQL Server that will be the
Reporting Services server. It has 4gig of memory. Will it be up to the job?
Thanks,
Andy.I am able to render about 70reports (PDF format) in less than 10mins. Each
report has about 200 pages.
"Andy Smith" wrote:
> I'm evaluting Reporting Services for my client. They need to produce 5000+
> reports per day, mainly output in PDF format. The PDF's are 6-8 pages long.
> Does anyone have any experience of getting this kind of performance out of
> SQL-RS?
> They have an 8-processor 2.8Ghz server running SQL Server that will be the
> Reporting Services server. It has 4gig of memory. Will it be up to the job?
> Thanks,
> Andy.|||Don't forget to set boot.ini to use /3GB switch in order to take advantage
of the memory.
To verify RS can handle this load, consider using a test tool like
Application Center Test (ACT), part of Visual Studio, to run a Proof Of
Concept. Below is sample ACT script you might use to run such a test:
' --
CONSTANTS --
const ENABLE_DELAYS = True
const REQUESTBUFFERSIZE = 15000
const REPORTSERVERNAME = "localhost"
' --
' -- Think time variables - examples shows between 2-3 seconds
' --
const MIN_SLEEP_MSEC = 2000
const MAX_SLEEP_MSEC = 3000
' --
' -- Think time logic if desired
' --
Function RandomSleep()
if (NOT ENABLE_DELAYS) then
RandomSleep = 0
return
end if
Dim lMinSleep, lMaxSleep, lSleep
lMaxSleep = MAX_SLEEP_MSEC
lMinSleep = MIN_SLEEP_MSEC
' create a random int within our range
Call Randomize()
lSleep = Int((lMaxSleep - lMinSleep + 1) * Rnd(1) + lMinSleep)
' Test.Trace "Sleeping: " + Cstr(lSleep)
Call Test.Sleep(lSleep)
' return the delay time
RandomSleep = lSleep
End Function
Function SendGetRequest(reportServerUrl)
Dim oConnection, oRequest, oResponse, oHeaders, statusCode
Set oConnection = Test.CreateConnection(REPORTSERVERNAME, 80, false)
If (oConnection is Nothing) Then
Test.Trace "Error: Unable to create connection to server"
Else
' TODO: uncomment this after debugging
' Test.Trace(reportServerUrl)
Set oRequest = Test.CreateRequest
oRequest.ResponseBufferSize = REQUESTBUFFERSIZE
oRequest.Path = reportServerUrl
oRequest.Verb = "GET"
oRequest.HTTPVersion = "HTTP/1.1"
set oHeaders = oRequest.Headers
oHeaders.RemoveAll
oHeaders.Add "Accept", "image/gif, image/x-xbitmap, image/jpeg,
image/pjpeg, application/vnd.ms-excel, application/vnd.ms-powerpoint,
application/msword, */*"
oHeaders.Add "Accept-Language", "en-us"
oHeaders.Add "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0;
Windows NT 5.1; .NET CLR 1.0.3512; .NET CLR 1.1.4322)"
oHeaders.Add "Host", "(automatic)"
oHeaders.Add "Cookie", "(automatic)"
Set oResponse = oConnection.Send(oRequest)
If (oResponse is Nothing) Then
Test.Trace "Error: Failed to receive response for URL to " +
reportServerUrl
Else
statusCode = oResponse.ResultCode
'DEBUG
Test.Trace "Received: " + CStr(statusCode) + " for: " + REPORTSERVERNAME
+ reportServerUrl
' delay in case of errors - to avoid the snowball effect
' Test.Trace "Response code of: " + CStr(statusCode) + "
recieved for " + reportServerUrl
if (statusCode = 503) Then
Test.Trace "Server too busy error: " + CStr(statusCode) + "
recieved for " + reportServerUrl + " - Process Put To Sleep"
End If
End If
oConnection.Close
End If
End Function
Sub Main()
Dim Url
'--
'-- URL of report to call for test. This one calls report named "Simple"
located in dir /Benchmark
'-- passing in a parm of &RowNumber '--
Url ="/ReportServer?/Benchmark/Simple&RowNumber=20&rs:Command=Render&rc:Toolbar=false&rs:Format=PDF"
SendGetRequest(Url)
RandomSleep()
End Sub
Main
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Raj Chandra" <RajChandra@.discussions.microsoft.com> wrote in message
news:C23DE604-F4E0-4F3E-BD7B-1C4516C1987C@.microsoft.com...
>I am able to render about 70reports (PDF format) in less than 10mins. Each
> report has about 200 pages.
> "Andy Smith" wrote:
>> I'm evaluting Reporting Services for my client. They need to produce
>> 5000+
>> reports per day, mainly output in PDF format. The PDF's are 6-8 pages
>> long.
>> Does anyone have any experience of getting this kind of performance out
>> of
>> SQL-RS?
>> They have an 8-processor 2.8Ghz server running SQL Server that will be
>> the
>> Reporting Services server. It has 4gig of memory. Will it be up to the
>> job?
>> Thanks,
>> Andy.sql

ANYONE PLEASE - Local Synchronization Between SQL Mobile and Local SQL Database

Hi Everyone

I am at the stage of architecting my solution

My goal is to develop the system on a windows application and pda

There is a central server which will create a publication called inventory

The laptops which host the windows application will be subscribers to the central server using merge replication

The client now wants the PDA using SQL Mobile to synchronize with the local subscirber database on the laptop using active sync. They dont want to do it via WIFI to the IIS Server at the central server

I have been reading for days and I am still unsure whether this is possible to do.

I know Appforge provide a conduit for palm to access synchronization but not local sql databases

I would appreciate your help immensley

Can anyone please help me

|||

If you build your mobile application using VS2005 and SQL Mobile, you can use merge replication between your server-side publication and SQL Mobile. The SQL Mobile Books OnLine are the first place to start - read the section on merge replication.

-Darren Shaffer

|||

Hi Darren

Yes I got my sql mobile to synchronize successfully with the local desktop sql database called "Inventory"

However I have a new problem

I would like to have the desktop replicating with the server for database "Inventory"

I also got this to work seperately

I would now want the SQL mobile database to replicate with the local desktop database "Inventory" which inturn will replicate with the server copy called "Inventory"

I would like to have both replication scenarios joined together. I know that most people will say get the SQL mobile to talk directly to the server but this is the most expensive scenario I believe

Is this possible?

Thankyou for your earlier post

Touraj

|||

Touraj,

Sorry for not reading your post more closely - I see what you are trying to do now. You want to have a 3 tier merge replication going on. Yes, this is possible, but the database on the laptop is going to have to be SQL Server 2005, not SQL Express. The way it would have to work is this:

The laptop would have to have IIS installed and the SQL Mobile server tools. As far as the mobile app is concerned, the laptop is the publisher. On the laptop, you have a subscription to the central server in a replication relationship. You would turn around and create a publication on the laptop with the same articles that are in the subscription to the central server. You would need to be extremely careful with uniqueness in this situation - I would avoid identity columns and use GUIDs for everything in this app domain.

Is there any chance that both the laptops and the PDAs can be subscribers to the central server? That would be a much easier implementation, but it is possible to do what you suggested with the extra tier.

-Darren

|||

Hi Darren

Thankyou again for your kind assistance

Yes you are right. It would be easier and 'cheaper' to get the PDA to synch with the central server via the servers IIS process

I am tossing up between a i-Mate and iPaq 4700. I would be interested to know if you have a preference over the two

Thanks from sunny Australia.

Touraj

|||

I think both i-Mate and HP make great devices - I have not had any trouble with SQL Mobile on my JasJar or on a variety of iPaqs. My WM5 JasJar seems a little sluggish overall compared to a Dell Axim X50v (WM5), which others have noted as well. Not slow, just not instant to respond to input. I have not had specific experience with the 4700. There are some public newsgroups (microsoft.public.pocketpc.developer) where people tend to talk more about specific devices and you could use Google Advanced Groups search to get previous opinions on the 4700.

-Darren

anyone linked mysql?

has anyone been able to link mysql to sqlserver 2k?
i've gotten it to link and can see the tables, but all linked server
queries give errors because the mysql odbc apparently doesn't return the
right info needed for the 4 part name. after an extensive google
search, all i've found are people reporting the exact same problem i'm
having, but nobody has posted a solution.
i'm being forced to update mysql data and would much rather use my
stored procedures and scheduled jobs instead of scheduled dts packages.
We've seen the same thing, and never got it working. The ODBC driver for
mysql doesn't behave properly.
Sorry!
"ch" <ch@.dontemailme.com> wrote in message
news:40966A00.67574C8C@.dontemailme.com...
> has anyone been able to link mysql to sqlserver 2k?
> i've gotten it to link and can see the tables, but all linked server
> queries give errors because the mysql odbc apparently doesn't return the
> right info needed for the 4 part name. after an extensive google
> search, all i've found are people reporting the exact same problem i'm
> having, but nobody has posted a solution.
> i'm being forced to update mysql data and would much rather use my
> stored procedures and scheduled jobs instead of scheduled dts packages.
>

anyone linked mysql?

has anyone been able to link mysql to sqlserver 2k?
i've gotten it to link and can see the tables, but all linked server
queries give errors because the mysql odbc apparently doesn't return the
right info needed for the 4 part name. after an extensive google
search, all i've found are people reporting the exact same problem i'm
having, but nobody has posted a solution.
i'm being forced to update mysql data and would much rather use my
stored procedures and scheduled jobs instead of scheduled dts packages.We've seen the same thing, and never got it working. The ODBC driver for
mysql doesn't behave properly.
Sorry!
"ch" <ch@.dontemailme.com> wrote in message
news:40966A00.67574C8C@.dontemailme.com...
> has anyone been able to link mysql to sqlserver 2k?
> i've gotten it to link and can see the tables, but all linked server
> queries give errors because the mysql odbc apparently doesn't return the
> right info needed for the 4 part name. after an extensive google
> search, all i've found are people reporting the exact same problem i'm
> having, but nobody has posted a solution.
> i'm being forced to update mysql data and would much rather use my
> stored procedures and scheduled jobs instead of scheduled dts packages.
>

anyone linked mysql?

has anyone been able to link mysql to sqlserver 2k?
i've gotten it to link and can see the tables, but all linked server
queries give errors because the mysql odbc apparently doesn't return the
right info needed for the 4 part name. after an extensive google
search, all i've found are people reporting the exact same problem i'm
having, but nobody has posted a solution.
i'm being forced to update mysql data and would much rather use my
stored procedures and scheduled jobs instead of scheduled dts packages.We've seen the same thing, and never got it working. The ODBC driver for
mysql doesn't behave properly.
Sorry!
"ch" <ch@.dontemailme.com> wrote in message
news:40966A00.67574C8C@.dontemailme.com...
> has anyone been able to link mysql to sqlserver 2k?
> i've gotten it to link and can see the tables, but all linked server
> queries give errors because the mysql odbc apparently doesn't return the
> right info needed for the 4 part name. after an extensive google
> search, all i've found are people reporting the exact same problem i'm
> having, but nobody has posted a solution.
> i'm being forced to update mysql data and would much rather use my
> stored procedures and scheduled jobs instead of scheduled dts packages.
>

Anyone lend a hand in .NET?

This is just one of many SQL based questions that go unanswered in the .NET forum. If anyone has some spare time, it would be great if you could take a look at the post mentioned above and also see if there are any other database questions that would benefit from your expertise!!

Thanks!It doesn't look like I can click on "This".
I check SQL questions sometimes on VB forum but don't have time for more... :(

I am working full time and try to manage some questions whenever I can...
I don't see anyone to answer questions on this forum but me for a while.

You can move some questions to SQL forum I guess.
I have some knowledge of .Net

Irina.|||http://www.msdner.com/forum/thread633912.html is what "This" is.|||I responded to the best of my abilities.

You can refer people to this article:
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson05.aspx

Thank you.
Good Luck.|||

Quote:

Originally Posted by iburyak

I responded to the best of my abilities.

You can refer people to this article:
http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson05.aspx

Thank you.
Good Luck.

Good, hopefully this will put them in the right direction!

Thanks!sql

anyone knows what happen?

I am currently using SQL server 2000 with SP3a
However, my SQL server still attach others' 1433 port
any one know what happen it is and how to solve it?
thxYour question doesn's make any sense to me. What does "still attach others
1433 port" mean?

> I am currently using SQL server 2000 with SP3a
> However, my SQL server still attach others' 1433 port
> any one know what happen it is and how to solve it?
> thx
>
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||sorry...let me explain to you
I have installed SP3a for my SQL server 2000
why I install SP3, since my server is attacks others server's 1433 port
so, I thought my server was infected by slammer, so I install the SP3a
however, after the installation of SP3a, my server still attacked other
server's 1433 port.
So..any solution'
thanks
"Neil Pike" <neilpike@.compuserve.com> wrote in message
news:VA.000061e6.0ddcb352@.compuserve.com...
> Your question doesn's make any sense to me. What does "still attach
others
> 1433 port" mean?
>
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq
>|||Did you update ALL instances of SQL Server on that machine?
Andrew J. Kelly SQL MVP
"Utada P.W. SIU" <wing0508@.hotmail.com> wrote in message
news:eQS$B6yKEHA.3076@.TK2MSFTNGP10.phx.gbl...
> sorry...let me explain to you
> I have installed SP3a for my SQL server 2000
> why I install SP3, since my server is attacks others server's 1433 port
> so, I thought my server was infected by slammer, so I install the SP3a
> however, after the installation of SP3a, my server still attacked other
> server's 1433 port.
> So..any solution'
> thanks
> "Neil Pike" <neilpike@.compuserve.com> wrote in message
> news:VA.000061e6.0ddcb352@.compuserve.com...
> others
>|||Utada - did you definitely INSTALL SP3a, or did you just "unpack" it. When
you run it after download that just unpacks it onto the hard-drive. You the
n
need to run the actual setup routine that it creates...

> sorry...let me explain to you
> I have installed SP3a for my SQL server 2000
> why I install SP3, since my server is attacks others server's 1433 port
> so, I thought my server was infected by slammer, so I install the SP3a
> however, after the installation of SP3a, my server still attacked other
> server's 1433 port.
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Nah Utada's definately on to something. I just noticed this morning my sql
server is doing the same thing.
I woke up to find my network going crazy, a simple "netstat -an" shows a
whole heap of 1433 connections. Enterprise manager indicates SP3 is
installed and I downloaded and ran the SQL Server 2000 security tools
(http://www.microsoft.com/downloads/...en&FamilyID=955
2d43b-04eb-4af9-9e24-6cde4d933600#filelist) and it reports that nothing
needs patched. No other sql server instances or MSDE installed.
Futher more, if you run sqlscan:
C:\SQLCritUpdPkg\SQLScan>sqlscan -m 127.0.0.1
No need to run the SQL Critical Update utility for this instance at this
time.
server=127.0.0.1 instance=MSSQLSERVER version=SP3 language=1033
MSDEProd
uctCode=N/A MSDEPackageName=N/A platform=NT os=5.2
There's definately something similar to slammer or some variant going
around. Either that or I've missed a patch somewhere along the line?
- Simon
"Neil Pike" <neilpike@.compuserve.com> wrote in message
news:VA.000061e9.01dc39ef@.compuserve.com...
> Utada - did you definitely INSTALL SP3a, or did you just "unpack" it.
When
> you run it after download that just unpacks it onto the hard-drive. You
then
> need to run the actual setup routine that it creates...
>
> Neil Pike MVP/MCSE. Protech Computing Ltd
> Reply here - no email
> SQL FAQ (484 entries) see
> http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
> (faqxxx.zip in lib 7)
> or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> or www.sqlserverfaq.com
> or www.mssqlserver.com/faq
>|||I reviewed someone SQL Server yesterday and since they had it exposed with
a public IP on the internet, and because 1433 is one of the highest scanned
ports, someone had repeated tried guessing their 'sa' password.
My advice to you:
1. Firewall. Get one if you don't have one.
2. If you don't need external machines connecting to your MSDE/SQL, have it
listen only on Shared Memory.
3. Use SQL Integrated Security. This eliminates hackers from attempting to
guess you 'sa' password.
4. Read our Best Practices.
http://www.microsoft.com/technet/pr...n/sp3sec00.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Get a network trace of it and call it in to MS PSS. I'd never directly
connect a SQL Server box to the internet myself though anyway...

> There's definately something similar to slammer or some variant going
> around. Either that or I've missed a patch somewhere along the line?
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq|||Thanks Guys. I ended up just changing from the default port as the server
does need remote access and SQL authentication enabled. The server is behind
a router with firewall and port forwarding for just the SQL port. I suspect
that changing the port is a fairly good catch-all solution to
vulnerabilities in the future also. The activity may well have been users
trying to guess the sa password, which might explain why each unique IP had
about 8 or so connections open.
- Si
"Kevin McDonnell [MSFT]" <kevmc@.online.microsoft.com> wrote in message
news:DhULSwENEHA.1368@.cpmsftngxa10.phx.gbl...
> I reviewed someone SQL Server yesterday and since they had it exposed with
> a public IP on the internet, and because 1433 is one of the highest
scanned
> ports, someone had repeated tried guessing their 'sa' password.
> My advice to you:
> 1. Firewall. Get one if you don't have one.
> 2. If you don't need external machines connecting to your MSDE/SQL, have
it
> listen only on Shared Memory.
> 3. Use SQL Integrated Security. This eliminates hackers from attempting
to
> guess you 'sa' password.
> 4. Read our Best Practices.
>
http://www.microsoft.com/technet/pr...n/sp3sec00.mspx
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>

anyone knows the anatomy to the transaction logs

how can i retrieve information from that very consuming tlogs.
Its consuming a lot of space maybe we can make relevant info from it
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787Lumigent Log Explorer can help you on that.
HTH, Jens Suessmeyer.|||thanks but thats not what i need.
do you dig deep into to the logs
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Jens" wrote:

> Lumigent Log Explorer can help you on that.
> HTH, Jens Suessmeyer.
>|||What do you want to do ? Look at the feature list of lumigent, perhaps
it=B4ll fit your needs.
http://www.lumigent.com/products/le_sql.html
HTH, Jens Suessmeyer.|||i want to generate reports from it without using
the log explorer
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Jens" wrote:

> What do you want to do ? Look at the feature list of lumigent, perhaps
> it′ll fit your needs.
> http://www.lumigent.com/products/le_sql.html
> HTH, Jens Suessmeyer.
>|||SQL Server doesn't provide any features for reporting from the logs.
You'd have to use third party tools for that. Logs are generally
unsuitable for business reporting requirements because any useful data
they contain is lost each time you do a log backup (which you should be
doing regularly if you are in Full Recovery mode). If you need to log
historical data then the transaction logs are not the way to do it.
David Portas
SQL Server MVP
--|||Hi Jose,
The details of the log are not released by Microsoft. The companies that
have products which read the log have done so by cracking the logs structure
themselves. Therefore you would be very (VERY) lucky if any of them are
willing to let you know what it is, as this is big money for them.
David right though even if you could read it reporting from it would not be
a good idea
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:B42E3821-F9FC-4A1B-B87C-AD2698EBAB4D@.microsoft.com...
> how can i retrieve information from that very consuming tlogs.
> Its consuming a lot of space maybe we can make relevant info from it
>
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||thanks for the input guys
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Jose G. de Jesus Jr MCP, MCDBA" wrote:

> how can i retrieve information from that very consuming tlogs.
> Its consuming a lot of space maybe we can make relevant info from it
>
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787|||You can manage (but not entirely limit) the amount of space consumed by the
transaction logs by configuring the database recovery model and scheduling
transaction log backups / truncates.
"Jose G. de Jesus Jr MCP, MCDBA" <Email me> wrote in message
news:B42E3821-F9FC-4A1B-B87C-AD2698EBAB4D@.microsoft.com...
> how can i retrieve information from that very consuming tlogs.
> Its consuming a lot of space maybe we can make relevant info from it
>
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787

Anyone knows a software that compares 2 dbs and synchronize even if replication exist

Anyone knows a software that compares 2 dbs and synchronize even if replication existNadim
I found that on internet,check it out
----
--
-- sp_CompareDB
--
-- The SP compares structures and data in 2 databases.
-- 1. Compares if all tables in one database have analog (by name) in second
database
-- Tables not existing in one of databases won't be used for data comparing
-- 2. Compares if structures for tables with the same names are the same.
Shows structural
-- differences like:
-- authors
-- Column Phone: in db1 - char(12), in DB2 - char(14)
-- sales
-- Column Location not in db2
-- Tables, having different structures, won't be used for data comparing.
However if the tables
-- contain columns of the same type and different length (like Phone in the
example above) or
-- tables have compatible data types (have the same type in syscolumns -
char and nchar,
-- varchar and nvarchar etc) they will be allowed for data comparing.
-- 3. Data comparison itself.
-- 3.1 Get information about unique keys in the tables. If there are unique
keys then one of them
-- (PK is a highest priority candidate for this role) will be used to
specify rows with
-- different data.
-- 3.2 Get information about all data columns in the table and form
predicates that will be
-- used to compare data.
-- 3.3 Compare data with the criteria:
-- a. if some unique keys from the table from first database do not exist in
second db (only
-- for tables with a unique key)
-- b. if some unique keys from the table from second database do not exist
in first db (only
-- for tables with a unique key)
-- c. if there are rows with the same values of unique keys and different
data in other
-- columns (only for tables with a unique key)
-- d. if there are rows in the table from first database that don't have a
twin in the
-- table from second db
-- e. if there are rows in the table from second database that don't have a
twin in the
-- table from first db
----
--
-- Parameters:
-- 1. @.db1 - name of first database to compare
-- 2. @.db2 - name of second database to compare
-- 3. @.TabList - list of tables to compare. if empty - all tables in the
databases should be
-- compared
-- 4. @.NumbToShow - number of rows with differences to show. Default - 10.
-- 5. @.OnlyStructure - flag, if set to 1, allows to avoid data comparing.
Only structures should
-- be compared. Default - 0
-- 6. @.NoTimestamp - flag, if set to 1, allows to avoid comparing of columns
of timestamp
-- data type. Default - 0
-- 7. @.VerboseLevel - if set to 1 allows to print querues used for data
comparison
----
--
-- Created by Viktor Gorodnichenko (c)
-- Created on: July 5, 2001
----
--
CREATE PROC sp_CompareDB
@.db1 varchar(128),
@.db2 varchar(128),
@.OnlyStructure bit = 0,
@.TabList varchar(8000) = '',
@.NumbToShow int = 10,
@.NoTimestamp bit = 0,
@.VerboseLevel tinyint = 0
AS
if @.OnlyStructure <> 0
set @.OnlyStructure = 1
if @.NoTimestamp <> 0
set @.NoTimestamp = 1
if @.VerboseLevel <> 0
set @.VerboseLevel = 1
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET ANSI_NULLS ON
declare @.sqlStr varchar(8000)
set nocount on
-- Checking if there are specified databases
declare @.SrvName sysname
declare @.DBName sysname
set @.db1 = RTRIM(LTRIM(@.db1))
set @.db2 = RTRIM(LTRIM(@.db2))
set @.SrvName = @.@.SERVERNAME
if CHARINDEX('.',@.db1) > 0
begin
set @.SrvName = LEFT(@.db1,CHARINDEX('.',@.db1)-1)
if not exists (select * from master.dbo.sysservers where srvname = @.SrvName)
begin
print 'There is no linked server named '+@.SrvName+'. End of work.'
return
end
set @.DBName = RIGHT(@.db1,LEN(@.db1)-CHARINDEX('.',@.db1))
end
else
set @.DBName = @.db1
exec ('declare @.Name sysname select @.Name=name from
['+@.SrvName+'].master.dbo.sysdatabases where name = '''+@.DBName+'''')if
@.@.rowcount = 0
begin
print 'There is no database named '+@.db1+'. End of work.'
return
end
set @.SrvName = @.@.SERVERNAME
if CHARINDEX('.',@.db2) > 0
begin
set @.SrvName = LEFT(@.db2,CHARINDEX('.',@.db2)-1)
if not exists (select * from master.dbo.sysservers where srvname = @.SrvName)
begin
print 'There is no linked server named '+@.SrvName+'. End of work.'
return
end
set @.DBName = RIGHT(@.db2,LEN(@.db2)-CHARINDEX('.',@.db2))
end
else
set @.DBName = @.db2
exec ('declare @.Name sysname select @.Name=name from
['+@.SrvName+'].master.dbo.sysdatabases where name = '''+@.DBName+'''')
if @.@.rowcount = 0
begin
print 'There is no database named '+@.db2+'. End of work.'
return
end
print Replicate('-',LEN(@.db1)+LEN(@.db2)+25)
print 'Comparing databases '+@.db1+' and '+@.db2
print Replicate('-',LEN(@.db1)+LEN(@.db2)+25)
print 'Options specified:'
print ' Compare only structures: '+CASE WHEN @.OnlyStructure = 0 THEN 'No'
ELSE 'Yes' END
print ' List of tables to compare: '+CASE WHEN LEN(@.TabList) = 0 THEN ' All
tables' ELSE @.TabList END
print ' Max number of different rows in each table to show:
'+LTRIM(STR(@.NumbToShow))
print ' Compare timestamp columns: '+CASE WHEN @.NoTimestamp = 0 THEN 'No'
ELSE 'Yes' END
print ' Verbose level: '+CASE WHEN @.VerboseLevel = 0 THEN 'Low' ELSE 'High'
END
----
--
-- Comparing structures
----
--
print CHAR(10)+Replicate('-',36)
print 'Comparing structure of the databases'
print Replicate('-',36)
if exists (select * from tempdb.dbo.sysobjects where name like
'#TabToCheck%')
drop table #TabToCheck
create table #TabToCheck (name sysname)
declare @.NextCommaPos int
if len(@.TabList) > 0
begin
while 1=1
begin
set @.NextCommaPos = CHARINDEX(',',@.TabList)
if @.NextCommaPos = 0
begin
set @.sqlstr = 'insert into #TabToCheck values('''+@.TabList+''')'
exec (@.sqlstr)
break
end
set @.sqlstr = 'insert into #TabToCheck
values('''+LEFT(@.TabList,@.NextCommaPos-1)+''')'
exec (@.sqlstr)
set @.TabList = RIGHT(@.TabList,LEN(@.TabList)-@.NextCommaPos)
end
end
else -- then will check all tables
begin
exec ('insert into #TabToCheck select name from '+@.db1+'.dbo.sysobjects
where type = ''U''')
exec ('insert into #TabToCheck select name from '+@.db2+'.dbo.sysobjects
where type = ''U''')
end
-- First check if at least one table specified in @.TabList exists in db1
exec ('declare @.Name sysname select @.Name=name from '+@.db1+'.dbo.sysobjects
where name in (select * from #TabToCheck)')
if @.@.rowcount = 0
begin
print 'No tables in '+@.db1+' to check. End of work.'
return
end
-- Check if tables existing in db1 are in DB2 (all tables or specified in
@.TabList)
if exists (select * from tempdb.dbo.sysobjects where name like
'#TabNotInDB2%')
drop table #TabNotInDB2
create table #TabNotInDB2 (name sysname)
insert into #TabNotInDB2
exec ('select name from '+@.db1+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@.db2+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name)')
if @.@.rowcount > 0
begin
print CHAR(10)+'The table(s) exist in '+@.db1+', but do not exist in
'+@.db2+':'
select * from #TabNotInDB2
end
delete from #TabToCheck where name in (select * from #TabNotInDB2)
drop table #TabNotInDB2
if exists (select * from tempdb.dbo.sysobjects where name like
'#TabNotInDB1%')
drop table #TabNotInDB1
create table #TabNotInDB1 (name sysname)
insert into #TabNotInDB1
exec ('select name from '+@.db2+'.dbo.sysobjects d1o '+
'where name in (select * from #TabToCheck) and '+
' d1o.type = ''U'' and not exists '+
'(select * from '+@.db1+'.dbo.sysobjects d2o'+
' where d2o.type = ''U'' and d2o.name = d1o.name)')
if @.@.rowcount > 0
begin
print CHAR(10)+'The table(s) exist in '+@.db2+', but do not exist in
'+@.db1+':'
select * from #TabNotInDB1
end
delete from #TabToCheck where name in (select * from #TabNotInDB1)
drop table #TabNotInDB1
-- Comparing structures of tables existing in both dbs
print CHAR(10)+'Checking if there are tables existing in both databases
having structural differences ...'+CHAR(10)
if exists (select * from tempdb.dbo.sysobjects where name like
'#DiffStructure%')
drop table #DiffStructure
create table #DiffStructure (name sysname)
set @.sqlStr='
declare @.TName1 sysname, @.TName2 sysname, @.CName1 sysname, @.CName2 sysname,
@.TypeName1 sysname, @.TypeName2 sysname,
@.CLen1 smallint, @.CLen2 smallint, @.Type1 sysname, @.Type2 sysname, @.PrevTName
sysname
declare @.DiffStructure bit
declare Diff cursor fast_forward for
select d1o.name, d2o.name, d1c.name, d2c.name, d1t.name, d2t.name,
d1c.length, d2c.length, d1c.type, d2c.type
from ('+@.db1+'.dbo.sysobjects d1o
JOIN '+@.db2+'.dbo.sysobjects d2o2 ON d1o.name = d2o2.name and d1o.type =
''U'' --only tables in both dbs
and d1o.name in (select * from #TabToCheck)
JOIN '+@.db1+'.dbo.syscolumns d1c ON d1o.id = d1c.id
JOIN '+@.db1+'.dbo.systypes d1t ON d1c.xusertype = d1t.xusertype)
FULL JOIN ('+@.db2+'.dbo.sysobjects d2o
JOIN '+@.db1+'.dbo.sysobjects d1o2 ON d1o2.name = d2o.name and d2o.type =
''U'' --only tables in both dbs
and d2o.name in (select * from #TabToCheck)
JOIN '+@.db2+'.dbo.syscolumns d2c ON d2c.id = d2o.id
JOIN '+@.db2+'.dbo.systypes d2t ON d2c.xusertype = d2t.xusertype)
ON d1o.name = d2o.name and d1c.name = d2c.name
WHERE (not exists
(select * from '+@.db2+'.dbo.sysobjects d2o2
JOIN '+@.db2+'.dbo.syscolumns d2c2 ON d2o2.id = d2c2.id
JOIN '+@.db2+'.dbo.systypes d2t2 ON d2c2.xusertype = d2t2.xusertype
where d2o2.type = ''U''
and d2o2.name = d1o.name
and d2c2.name = d1c.name
and d2t2.name = d1t.name
and d2c2.length = d1c.length)
OR not exists
(select * from '+@.db1+'.dbo.sysobjects d1o2
JOIN '+@.db1+'.dbo.syscolumns d1c2 ON d1o2.id = d1c2.id
JOIN '+@.db1+'.dbo.systypes d1t2 ON d1c2.xusertype = d1t2.xusertype
where d1o2.type = ''U''
and d1o2.name = d2o.name
and d1c2.name = d2c.name
and d1t2.name = d2t.name
and d1c2.length = d2c.length))
order by coalesce(d1o.name,d2o.name), d1c.name
open Diff
fetch next from Diff into @.TName1, @.TName2, @.CName1, @.CName2, @.TypeName1,
@.TypeName2,
@.CLen1, @.CLen2, @.Type1, @.Type2
set @.PrevTName = ''''
set @.DiffStructure = 0
while @.@.fetch_status = 0
begin
if Coalesce(@.TName1,@.TName2) <> @.PrevTName
begin
if @.PrevTName <> '''' and @.DiffStructure = 1
begin
insert into #DiffStructure values (@.PrevTName)
set @.DiffStructure = 0
end
set @.PrevTName = Coalesce(@.TName1,@.TName2)
print @.PrevTName
end
if @.CName2 is null
print '' Colimn ''+RTRIM(@.CName1)+'' not in '+@.db2+'''
else
if @.CName1 is null
print '' Colimn ''+RTRIM(@.CName2)+'' not in '+@.db1+'''
else
if @.TypeName1 <> @.TypeName2
print '' Colimn ''+RTRIM(@.CName1)+'': in '+@.db1+' - ''+RTRIM(@.TypeName1)+'',
in '+@.db2+' - ''+RTRIM(@.TypeName2)
else --the columns are not null(are in both dbs) and types are equal,then
length are diff
print '' Colimn ''+RTRIM(@.CName1)+'': in '+@.db1+' -
''+RTRIM(@.TypeName1)+''(''+
LTRIM(STR(CASE when @.TypeName1=''nChar'' or @.TypeName1 = ''nVarChar'' then
@.CLen1/2 else @.CLen1 end))+
''), in '+@.db2+' - ''+RTRIM(@.TypeName2)+''(''+
LTRIM(STR(CASE when @.TypeName1=''nChar'' or @.TypeName1 = ''nVarChar'' then
@.CLen2/2 else @.CLen2 end))+'')''
if @.Type1 = @.Type2
set @.DiffStructure=@.DiffStructure -- Do nothing. Cannot invert predicate
else
set @.DiffStructure = 1
fetch next from Diff into @.TName1, @.TName2, @.CName1, @.CName2, @.TypeName1,
@.TypeName2,
@.CLen1, @.CLen2, @.Type1, @.Type2
end
deallocate Diff
if @.DiffStructure = 1
insert into #DiffStructure values (@.PrevTName)
'
exec (@.sqlStr)
if (select count(*) from #DiffStructure) > 0
begin
print CHAR(10)+'The table(s) have the same name and different structure in
the databases:'
select distinct * from #DiffStructure
delete from #TabToCheck where name in (select * from #DiffStructure)
end
else
print CHAR(10)+'There are no tables with the same name and structural
differences in the databases'+CHAR(10)+CHAR(10)
if @.OnlyStructure = 1
begin
print 'The option ''Only compare structures'' was specified. End of work.'
return
end
exec ('declare @.Name sysname select @.Name=d1o.name
from '+@.db1+'.dbo.sysobjects d1o, '+@.db2+'.dbo.sysobjects d2o
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)')
if @.@.rowcount = 0
begin
print 'There are no tables with the same name and structure in the databases
to compare. End of work.'
return
end
----
--
-- Comparing data
----
--
-- ##CompareStr - will be used to pass comparing strings into dynamic script
-- to execute the string
if exists (select * from tempdb.dbo.sysobjects where name like
'##CompareStr%')
drop table ##CompareStr
create table ##CompareStr (Ind int, CompareStr varchar(8000))
if exists (select * from tempdb.dbo.sysobjects where name like
'#DiffTables%')
drop table #DiffTables
create table #DiffTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like
'#IdenticalTables%')
drop table #IdenticalTables
create table #IdenticalTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like
'#EmptyTables%')
drop table #EmptyTables
create table #EmptyTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like
'#NoPKTables%')
drop table #NoPKTables
create table #NoPKTables (Name sysname)
if exists (select * from tempdb.dbo.sysobjects where name like '#IndList1%')
truncate table #IndList1
else
create table #IndList1 (IndId int, IndStatus int,
KeyAndStr varchar(7000), KeyCommaStr varchar(1000))
if exists (select * from tempdb.dbo.sysobjects where name like '#IndList2%')
truncate table #IndList2
else
create table #IndList2 (IndId smallint, IndStatus int,
KeyAndStr varchar(7000), KeyCommaStr varchar(1000))
print Replicate('-',51)
print 'Comparing data in tables with indentical structure:'
print Replicate('-',51)
----
--
-- Cursor for all tables in dbs (or for all specified tables if parameter
@.TabList is passed)
----
--
declare @.SqlStrGetListOfKeys1 varchar(8000)
declare @.SqlStrGetListOfKeys2 varchar(8000)
declare @.SqlStrGetListOfColumns varchar(8000)
declare @.SqlStrCompareUKeyTables varchar(8000)
declare @.SqlStrCompareNonUKeyTables varchar(8000)
set @.SqlStrGetListOfKeys1 = '
declare @.sqlStr varchar(8000)
declare @.ExecSqlStr varchar(8000)
declare @.PrintSqlStr varchar(8000)
declare @.Tab varchar(128)
declare @.d1User varchar(128)
declare @.d2User varchar(128)
declare @.KeyAndStr varchar(8000)
declare @.KeyCommaStr varchar(8000)
declare @.AndStr varchar(8000)
declare @.Eq varchar(8000)
declare @.IndId int
declare @.IndStatus int
declare @.CurrIndId smallint
declare @.CurrStatus int
declare @.UKey sysname
declare @.Col varchar(128)
declare @.LastUsedCol varchar(128)
declare @.xType int
declare @.Len int
declare @.SelectStr varchar(8000)
declare @.ExecSql nvarchar(1000)
declare @.NotInDB1 bit
declare @.NotInDB2 bit
declare @.NotEq bit
declare @.Numb int
declare @.Cnt1 int
declare @.Cnt2 int
set @.Numb = 0
declare @.StrInd int
declare @.i int
declare @.PrintStr varchar(8000)
declare @.ExecStr varchar(8000)
declare TabCur cursor for
select d1o.name, d1u.name, d2u.name from '+@.db1+'.dbo.sysobjects d1o,
'+@.db2+'.dbo.sysobjects d2o,
'+@.db1+'.dbo.sysusers d1u, '+@.db2+'.dbo.sysusers d2u
where d1o.name = d2o.name and d1o.type = ''U'' and d2o.type = ''U''
and d1o.uid = d1u.uid and d2o.uid = d2u.uid
and d1o.name not in (''dtproperties'')
and d1o.name in (select * from #TabToCheck)
order by 1
open TabCur
fetch next from TabCur into @.Tab, @.d1User, @.d2User
while @.@.fetch_status = 0
begin
set @.Numb = @.Numb + 1
print Char(13)+Char(10)+LTRIM(STR(@.Numb))+''. TABLE: [''+@.Tab+''] ''
set @.ExecSql = ''SELECT @.Cnt = count(*) FROM
'+@.db1+'.[''+@.d1User+''].[''+@.Tab+'']''
exec sp_executesql @.ExecSql, N''@.Cnt int output'', @.Cnt = @.Cnt1 output
print CHAR(10)+STR(@.Cnt1)+'' rows in '+@.db1+'''
set @.ExecSql = ''SELECT @.Cnt = count(*) FROM
'+@.db2+'.[''+@.d2User+''].[''+@.Tab+'']''
exec sp_executesql @.ExecSql, N''@.Cnt int output'', @.Cnt = @.Cnt2 output
print STR(@.Cnt2)+'' rows in '+@.db2+'''
if @.Cnt1 = 0 and @.Cnt2 = 0
begin
exec ('' insert into #EmptyTables values(''''[''+@.Tab+'']'''')'')
goto NextTab
end
set @.KeyAndStr = ''''
set @.KeyCommaStr = ''''
set @.NotInDB1 = 0
set @.NotInDB2 = 0
set @.NotEq = 0
set @.KeyAndStr = ''''
set @.KeyCommaStr = ''''
truncate table #IndList1
declare UKeys cursor fast_forward for
select i.indid, i.status, c.name, c.xType from '+@.db1+'.dbo.sysobjects o,
'+@.db1+'.dbo.sysindexes i, '+@.db1+'.dbo.sysindexkeys k,
'+@.db1+'.dbo.syscolumns c
where i.id = o.id and o.name = @.Tab
and (i.status & 2)<>0
and k.id = o.id and k.indid = i.indid
and c.id = o.id and c.colid = k.colid
order by i.indid, c.name
open UKeys
fetch next from UKeys into @.IndId, @.IndStatus, @.UKey, @.xType
set @.CurrIndId = @.IndId
set @.CurrStatus = @.IndStatus
while @.@.fetch_status = 0
begin
if @.KeyAndStr <> ''''
begin
set @.KeyAndStr = @.KeyAndStr + '' and '' + CHAR(10)
set @.KeyCommaStr = @.KeyCommaStr + '', ''
end
if @.xType = 175 or @.xType = 167 or @.xType = 239 or @.xType = 231 -- char,
varchar, nchar, nvarchar
begin
set @.KeyAndStr = @.KeyAndStr + ''
ISNULL(d1. [''+@.UKey+''],''''!#null$'''')=ISNULL(d2
.[''+@.UKey+''],''''!#null$
'''') ''
end
if @.xType = 173 or @.xType = 165 -- binary, varbinary
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN
0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4
ELSE d2.[''+@.UKey+''] END ''
end
else if @.xType = 56 or @.xType = 127 or @.xType = 60 or @.xType = 122 -- int,
127 - bigint,60 - money, 122 - smallmoney
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 971428763405345098745 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 971428763405345098745 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 106 or @.xType = 108 -- int, decimal, numeric
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 71428763405345098745098.8723 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 71428763405345098745098.8723 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 62 or @.xType = 59 -- 62 - float, 59 - real
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 8764589764.22708E237 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 8764589764.22708E237 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 52 or @.xType = 48 or @.xType = 104 -- smallint, tinyint, bit
begin
set @.KeyAndStr = @.KeyAndStr + '' CASE WHEN d1.[''+@.UKey+''] is null THEN
99999 ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 99999 ELSE d2.[''+@.UKey+''] END ''
end
else if @.xType = 36 -- 36 - id
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@.UKey+''] END''
end
else if @.xType = 61 or @.xType = 58 -- datetime, smalldatetime
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d1.[''+@.UKey+''],109) END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d2.[''+@.UKey+''],109) END ''
end
else if @.xType = 189 -- timestamp (189)
begin
set @.KeyAndStr = @.KeyAndStr + '' d1.[''+@.UKey+'']=d2.[''+@.UKey+''] ''
end
else if @.xType = 98 -- SQL_variant
begin
set @.KeyAndStr = @.KeyAndStr + ''
ISNULL(d1. [''+@.UKey+''],''''!#null$'''')=ISNULL(d2
.[''+@.UKey+''],''''!#null$
'''') ''
end
set @.KeyCommaStr = @.KeyCommaStr + '' d1.''+@.UKey
fetch next from UKeys into @.IndId, @.IndStatus, @.UKey, @.xType
if @.IndId <> @.CurrIndId
begin
insert into #IndList1 values (@.CurrIndId, @.CurrStatus, @.KeyAndStr,
@.KeyCommaStr)
set @.CurrIndId = @.IndId
set @.CurrStatus = @.IndStatus
set @.KeyAndStr = ''''
set @.KeyCommaStr = ''''
end
end
deallocate UKeys
insert into #IndList1 values (@.CurrIndId, @.CurrStatus, @.KeyAndStr,
@.KeyCommaStr)'
set @.SqlStrGetListOfKeys2 = '
set @.KeyAndStr = ''''
set @.KeyCommaStr = ''''
truncate table #IndList2
declare UKeys cursor fast_forward for
select i.indid, i.status, c.name, c.xType from '+@.db2+'.dbo.sysobjects o,
'+@.db2+'.dbo.sysindexes i, '+@.db2+'.dbo.sysindexkeys k,
'+@.db2+'.dbo.syscolumns c
where i.id = o.id and o.name = @.Tab
and (i.status & 2)<>0
and k.id = o.id and k.indid = i.indid
and c.id = o.id and c.colid = k.colid
order by i.indid, c.name
open UKeys
fetch next from UKeys into @.IndId, @.IndStatus, @.UKey, @.xType
set @.CurrIndId = @.IndId
set @.CurrStatus = @.IndStatus
while @.@.fetch_status = 0
begin
if @.KeyAndStr <> ''''
begin
set @.KeyAndStr = @.KeyAndStr + '' and '' + CHAR(10)
set @.KeyCommaStr = @.KeyCommaStr + '', ''
end
if @.xType = 175 or @.xType = 167 or @.xType = 239 or @.xType = 231 -- char,
varchar, nchar, nvarchar
begin
set @.KeyAndStr = @.KeyAndStr + ''
ISNULL(d1. [''+@.UKey+''],''''!#null$'''')=ISNULL(d2
.[''+@.UKey+''],''''!#null$
'''') ''
end
if @.xType = 173 or @.xType = 165 -- binary, varbinary
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN
0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4
ELSE d2.[''+@.UKey+''] END ''
end
else if @.xType = 56 or @.xType = 127 or @.xType = 60 or @.xType = 122 -- int,
127 - bigint,60 - money, 122 - smallmoney
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 971428763405345098745 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 971428763405345098745 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 106 or @.xType = 108 -- int, decimal, numeric
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 71428763405345098745098.8723 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 71428763405345098745098.8723 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 62 or @.xType = 59 -- 62 - float, 59 - real
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN 8764589764.22708E237 ELSE
d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 8764589764.22708E237 ELSE
d2.[''+@.UKey+''] END ''
end
else if @.xType = 52 or @.xType = 48 or @.xType = 104 -- smallint, tinyint, bit
begin
set @.KeyAndStr = @.KeyAndStr + '' CASE WHEN d1.[''+@.UKey+''] is null THEN
99999 ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN 99999 ELSE d2.[''+@.UKey+''] END ''
end
else if @.xType = 36 -- 36 - id
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@.UKey+''] END=''+
''CASE WHEN d2.[''+@.UKey+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@.UKey+''] END''
end
else if @.xType = 61 or @.xType = 58 -- datetime, smalldatetime
begin
set @.KeyAndStr = @.KeyAndStr +
'' CASE WHEN d1.[''+@.UKey+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d1.[''+@.UKey+''],109) END=''+
''CASE WHEN d2.[''+@.UKey+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d2.[''+@.UKey+''],109) END ''
end
else if @.xType = 189 -- timestamp (189)
begin
set @.KeyAndStr = @.KeyAndStr + '' d1.[''+@.UKey+'']=d2.[''+@.UKey+''] ''
end
else if @.xType = 98 -- SQL_variant
begin
set @.KeyAndStr = @.KeyAndStr + ''
ISNULL(d1. [''+@.UKey+''],''''!#null$'''')=ISNULL(d2
.[''+@.UKey+''],''''!#null$
'''') ''
end
set @.KeyCommaStr = @.KeyCommaStr + '' d1.''+@.UKey
fetch next from UKeys into @.IndId, @.IndStatus, @.UKey, @.xType
if @.IndId <> @.CurrIndId
begin
insert into #IndList2 values (@.CurrIndId, @.CurrStatus, @.KeyAndStr,
@.KeyCommaStr)
set @.CurrIndId = @.IndId
set @.CurrStatus = @.IndStatus
set @.KeyAndStr = ''''
set @.KeyCommaStr = ''''
end
end
deallocate UKeys
insert into #IndList2 values (@.CurrIndId, @.CurrStatus, @.KeyAndStr,
@.KeyCommaStr)
set @.KeyCommaStr = null
select @.KeyCommaStr=i1.KeyCommaStr from #IndList1 i1
join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr
where (i1.IndStatus & 2048)<> 0 and (i2.IndStatus & 2048)<>0
if @.KeyCommaStr is null
set @.KeyCommaStr = (select top 1 i1.KeyCommaStr from #IndList1 i1
join #IndList2 i2 on i1.KeyCommaStr = i2.KeyCommaStr)
set @.KeyAndStr = (select TOP 1 KeyAndStr from #IndList1 where KeyCommaStr =
@.KeyCommaStr)
if @.KeyCommaStr is null
set @.KeyCommaStr = ''''
if @.KeyAndStr is null
set @.KeyAndStr = ''
set @.SqlStrGetListOfColumns = '
set @.AndStr = ''''
set @.StrInd = 1
declare Cols cursor local fast_forward for
select c.name, c.xtype, c.length from '+@.db1+'.dbo.sysobjects o,
'+@.db1+'.dbo.syscolumns c
where o.id = c.id and o.name = @.Tab
and CHARINDEX(c.name, @.KeyCommaStr) = 0
open Cols
fetch next from Cols into @.Col, @.xType, @.len
while @.@.fetch_status = 0
begin
if @.xType = 175 or @.xType = 167 or @.xType = 239 or @.xType = 231 -- char,
varchar, nchar, nvarchar
begin
set @.Eq =
''ISNULL(d1.[''+@.Col+''],''''!#null$'''')=ISNULL(d2.[''+@.Col+''],''''!#null$
'''') ''
end
if @.xType = 173 or @.xType = 165 -- binary, varbinary
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null THEN
0x4D4FFB23A49411D5BDDB00A0C906B7B4 ELSE d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN 0x4D4FFB23A49411D5BDDB00A0C906B7B4
ELSE d2.[''+@.Col+''] END ''
end
else if @.xType = 56 or @.xType = 127 or @.xType = 60 or @.xType = 122 -- int,
127 - bigint,60 - money, 122 - smallmoney
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null THEN 971428763405345098745
ELSE d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN 971428763405345098745 ELSE
d2.[''+@.Col+''] END ''
end
else if @.xType = 106 or @.xType = 108 -- int, decimal, numeric
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null THEN
71428763405345098745098.8723 ELSE d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN 71428763405345098745098.8723 ELSE
d2.[''+@.Col+''] END ''
end
else if @.xType = 62 or @.xType = 59 -- 62 - float, 59 - real
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null THEN 8764589764.22708E237 ELSE
d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN 8764589764.22708E237 ELSE
d2.[''+@.Col+''] END ''
end
else if @.xType = 52 or @.xType = 48 or @.xType = 104 -- smallint, tinyint, bit
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null THEN 99999 ELSE
d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN 99999 ELSE d2.[''+@.Col+''] END ''
end
else if @.xType = 36 -- 36 - id
begin
set @.Eq = ''CASE WHEN d1.[''+@.Col+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d1.[''+@.Col+''] END=''+
''CASE WHEN d2.[''+@.Col+''] is null''+
'' THEN
CONVERT(uniqueidentifier,''''1CD827A0-744A-4866-8401-B9902CF2D4FB'''')''+
'' ELSE d2.[''+@.Col+''] END''
end
else if @.xType = 61 or @.xType = 58 -- datetime, smalldatetime
begin
set @.Eq =
''CASE WHEN d1.[''+@.Col+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d1.[''+@.Col+''],109) END=''+
''CASE WHEN d2.[''+@.Col+''] is null THEN ''''!#null$'''' ELSE
CONVERT(varchar(40),d2.[''+@.Col+''],109) END ''
end
else if @.xType = 34
begin
set @.Eq =
''ISNULL(DATALENGTH(d1.[''+@.Col+'']),0)=ISNULL(DATALENGTH(d2.[''+@.Col+'']),0
) ''
end
else if @.xType = 35 or @.xType = 99 -- text (35),ntext (99)
begin
set @.Eq = ''ISNULL(SUBSTRING(d1.[''+@.Col+''],1,DATALENGTH(d1.[''+@.Col+
''])),''''!#null$'''')=ISNULL(SUBSTRING(
d2.[''+@.Col+''],1,DATALENGTH(d2.[''+
@.Col+''])),''''!#null$'''') ''
end
else if @.xType = 189
begin
if '+STR(@.NoTimestamp)+' = 0
set @.Eq = ''d1.[''+@.Col+'']=d2.[''+@.Col+''] ''
else
set @.Eq = ''1=1''
end
else if @.xType = 98 -- SQL_variant
begin
set @.Eq =
''ISNULL(d1.[''+@.Col+''],''''!#null$'''')=ISNULL(d2.[''+@.Col+''],''''!#null$
'''') ''
end
if @.AndStr = ''''
set @.AndStr = @.AndStr + CHAR(10) + '' '' + @.Eq
else
if len(@.AndStr) + len('' and '' + @.Eq)<8000
set @.AndStr = @.AndStr + '' and '' + CHAR(10) + '' '' + @.Eq
else
begin
set @.StrInd = @.StrInd + 1
Insert into ##CompareStr values(@.StrInd,@.AndStr)
set @.AndStr = '' and '' + @.Eq
end
fetch next from Cols into @.Col, @.xType, @.len
end
deallocate Cols '
set @.SqlStrCompareUKeyTables = '
if @.KeyAndStr <> ''''
begin
set @.SelectStr = ''SELECT ''+ @.KeyCommaStr+'' INTO ##NotInDb2 FROM
'+@.db1+'.[''+@.d1User+''].[''+@.Tab+''] d1 ''+
'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM
'+@.db2+'.[''+@.d2User+''].[''+@.Tab+''] d2 ''+
'' WHERE ''+CHAR(10)+@.KeyAndStr+'')''
if '+STR(@.VerboseLevel)+' = 1
print CHAR(10)+''To find rows that are in '+@.db1+', but are not in
db2:''+CHAR(10)+
REPLACE (@.SelectStr, ''into ##NotInDB2'','''')
exec (@.SelectStr)
if @.@.rowcount > 0
set @.NotInDB2 = 1
set @.SelectStr = ''SELECT ''+@.KeyCommaStr+'' INTO ##NotInDB1 FROM
'+@.db2+'.[''+@.d2User+''].[''+@.Tab+''] d1 ''+
'' WHERE not exists''+CHAR(10)+'' (SELECT * FROM
'+@.db1+'.[''+@.d1User+''].[''+@.Tab+''] d2 ''+
'' WHERE ''+CHAR(10)+@.KeyAndStr+'')''
if '+STR(@.VerboseLevel)+' = 1
print CHAR(10)+''To find rows that are in '+@.db2+', but are not in
'+@.db1+':''+CHAR(10)+
REPLACE (@.SelectStr, ''into ##NotInDB1'','''')
exec (@.SelectStr)
if @.@.rowcount > 0
set @.NotInDB1 = 1
-- if there are non-key columns
if @.AndStr <> ''''
begin
set @.PrintStr = '' Print ''
set @.ExecStr = '' exec (''
set @.SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+ @.KeyCommaStr+'' INTO ##NotEq FROM
'+@.db2+'.[''+@.d2User+''].[''+@.Tab+''] d1 ''+
'' INNER JOIN '+@.db1+'.[''+@.d1User+''].[''+@.Tab+''] d2 ON
''+CHAR(10)+@.KeyAndStr+CHAR(10)+''WHERE not('')
-- Adding last string in temp table containing a comparing string to execute
set @.StrInd = @.StrInd + 1
Insert into ##CompareStr values(@.StrInd,@.AndStr+'')'')
set @.i = 1
while @.i <= @.StrInd
begin
set @.SqlStr = @.SqlStr + '' declare @.Str''+LTRIM(STR(@.i))+'' varchar(8000)
''+
''select @.Str''+LTRIM(STR(@.i))+''=CompareStr FROM ##CompareStr WHERE ind =
''+STR(@.i)
if @.ExecStr <> '' exec (''
set @.ExecStr = @.ExecStr + ''+''
if @.PrintStr <> '' Print ''
set @.PrintStr = @.PrintStr + ''+''
set @.ExecStr = @.ExecStr + ''@.Str''+LTRIM(STR(@.i))
set @.PrintStr = @.PrintStr + '' REPLACE(@.Str''+LTRIM(STR(@.i))+'','''' into
##NotEq'''',''') ''
set @.i = @.i + 1
end
set @.ExecStr = @.ExecStr + '') ''
set @.ExecSqlStr = @.SqlStr + @.ExecStr
set @.PrintSqlStr = @.SqlStr +
'' Print CHAR(10)+''''To find rows that are different in non-key
columns:'''' ''+
@.PrintStr
if '+STR(@.VerboseLevel)+' = 1
exec (@.PrintSqlStr)
exec (@.ExecSqlStr)
if @.@.rowcount > 0
set @.NotEq = 1
end
else
if '+STR(@.VerboseLevel)+' = 1
print CHAR(10)+''There are no non-key columns in the table''
truncate table ##CompareStr
if @.NotInDB1 = 1 or @.NotInDB2 = 1 or @.NotEq = 1
begin
print CHAR(10)+''Data are different''
if @.NotInDB2 = 1 and '+STR(@.NumbToShow)+' > 0
begin
print ''These key values exist in '+@.db1+', but do not exist in '+@.db2+': ''
set @.SelectStr = ''select top ''+STR('+STR(@.NumbToShow)+')+'' * from
##NotInDB2''
exec (@.SelectStr)
end
if @.NotInDB1 = 1 and '+STR(@.NumbToShow)+' > 0
begin
print ''These key values exist in '+@.db2+', but do not exist in '+@.db1+': ''
set @.SelectStr = ''select top ''+STR('+STR(@.NumbToShow)+')+'' * from
##NotInDB1''
exec (@.SelectStr)
end
if @.NotEq = 1 and '+STR(@.NumbToShow)+' > 0
begin
print ''Row(s) with these key values contain differences in non-key columns:
''
set @.SelectStr = ''select top ''+STR('+STR(@.NumbToShow)+')+'' * from
##NotEq''
exec (@.SelectStr)
end
exec (''insert into #DiffTables values(''''[''+@.Tab+'']'''')'')
end
else
begin
print CHAR(10)+''Data are identical''
exec ('' insert into #IdenticalTables values(''''[''+@.Tab+'']'''')'')
end
if exists (select * from tempdb.dbo.sysobjects where name like ''##NotEq%'')
drop table ##NotEq
end
else '
set @.SqlStrCompareNonUKeyTables = '
begin
exec (''insert into #NoPKTables values(''''[''+@.Tab+'']'''')'')
set @.PrintStr = '' Print ''
set @.ExecStr = '' exec (''
set @.SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+
'' * INTO ##NotInDB2 FROM '+@.db1+'.[''+@.d1User+''].[''+@.Tab+''] d1 WHERE not
exists ''+CHAR(10)+
'' (SELECT * FROM '+@.db2+'.[''+@.d2User+''].[''+@.Tab+''] d2 WHERE '')
set @.StrInd = @.StrInd + 1
Insert into ##CompareStr values(@.StrInd,@.AndStr+'')'')
set @.i = 1
while @.i <= @.StrInd
begin
set @.SqlStr = @.SqlStr + '' declare @.Str''+LTRIM(STR(@.i))+'' varchar(8000)
''+
''select @.Str''+LTRIM(STR(@.i))+''=CompareStr FROM ##CompareStr WHERE ind =
''+STR(@.i)
if @.ExecStr <> '' exec (''
set @.ExecStr = @.ExecStr + ''+''
if @.PrintStr <> '' Print ''
set @.PrintStr = @.PrintStr + ''+''
set @.ExecStr = @.ExecStr + ''@.Str''+LTRIM(STR(@.i))
set @.PrintStr = @.PrintStr + '' REPLACE(@.Str''+LTRIM(STR(@.i))+'','''' into
##NotInDB2'''',''') ''
set @.i = @.i + 1
end
set @.ExecStr = @.ExecStr + '') ''
set @.ExecSqlStr = @.SqlStr + @.ExecStr
set @.PrintSqlStr = @.SqlStr +
'' Print CHAR(10)+''''To find rows that are in '+@.db1+', but are not in
'+@.db2+':'''' ''+
@.PrintStr
if '+STR(@.VerboseLevel)+' = 1
exec (@.PrintSqlStr)
exec (@.ExecSqlStr)
if @.@.rowcount > 0
set @.NotInDB2 = 1
delete from ##CompareStr where ind = 1
set @.PrintStr = '' Print ''
set @.ExecStr = '' exec (''
set @.SqlStr = ''''
Insert into ##CompareStr values(1,
''SELECT ''+
'' * INTO ##NotInDB1 FROM '+@.db2+'.[''+@.d2User+''].[''+@.Tab+''] d1 WHERE not
exists ''+CHAR(10)+
'' (SELECT * FROM '+@.db1+'.[''+@.d1User+''].[''+@.Tab+''] d2 WHERE '')
set @.i = 1
while @.i <= @.StrInd
begin
set @.SqlStr = @.SqlStr + '' declare @.Str''+LTRIM(STR(@.i))+'' varchar(8000)
''+
''select @.Str''+LTRIM(STR(@.i))+''=CompareStr FROM ##CompareStr WHERE ind =
''+STR(@.i)
if @.ExecStr <> '' exec (''
set @.ExecStr = @.ExecStr + ''+''
if @.PrintStr <> '' Print ''
set @.PrintStr = @.PrintStr + ''+''
set @.ExecStr = @.ExecStr + ''@.Str''+LTRIM(STR(@.i))
set @.PrintStr = @.PrintStr + '' REPLACE(@.Str''+LTRIM(STR(@.i))+'','''' into
##NotInDB1'''',''') ''
set @.i = @.i + 1
end
set @.ExecStr = @.ExecStr + '') ''
set @.ExecSqlStr = @.SqlStr + @.ExecStr
set @.PrintSqlStr = @.SqlStr +
'' Print CHAR(10)+''''To find rows that are in '+@.db2+', but are not in
'+@.db1+':'''' ''+
@.PrintStr
if '+STR(@.VerboseLevel)+' = 1
exec (@.PrintSqlStr)
exec (@.ExecSqlStr)
if @.@.rowcount > 0
set @.NotInDB1 = 1
truncate table ##CompareStr
if @.NotInDB1 = 1 or @.NotInDB2 = 1
begin
print CHAR(10)+''Data are different''
if @.NotInDB2 = 1 and '+STR(@.NumbToShow)+' > 0
begin
print ''The row(s) exist in '+@.db1+', but do not exist in '+@.db2+': ''
set @.SelectStr = ''select top ''+STR('+STR(@.NumbToShow)+')+'' * from
##NotInDB2''
exec (@.SelectStr)
end
if @.NotInDB1 = 1 and '+STR(@.NumbToShow)+' > 0
begin
print ''The row(s) exist in '+@.db2+', but do not exist in '+@.db1+': ''
set @.SelectStr = ''select top ''+STR('+STR(@.NumbToShow)+')+'' * from
##NotInDB1''
exec (@.SelectStr)
end
exec (''insert into #DiffTables values(''''[''+@.Tab+'']'''')'')
end
else
begin
print CHAR(10)+''Data are identical''
exec ('' insert into #IdenticalTables values(''''[''+@.Tab+'']'''')'')
end
end
if exists (select * from tempdb.dbo.sysobjects where name like
''##NotInDB1%'')
drop table ##NotInDB1
if exists (select * from tempdb.dbo.sysobjects where name like
''##NotInDB2%'')
drop table ##NotInDB2
NextTab:
fetch next from TabCur into @.Tab, @.d1User, @.d2User
end
deallocate TabCur
'
exec (@.SqlStrGetListOfKeys1+@.SqlStrGetListOfK
eys2+@.SqlStrGetListOfColumns+
@.SqlStrCompareUKeyTables+@.SqlStrCompareN
onUKeyTables)
print ' '
SET NOCOUNT OFF
if (select count(*) from #NoPKTables) > 0
begin
select name as 'Table(s) without Unique key:' from #NoPKTables
end
if (select count(*) from #DiffTables) > 0
begin
select name as 'Table(s) with the same name & structure, but different
data:' from #DiffTables
end
else
print CHAR(10)+'No tables with the same name & structure, but different
data'+CHAR(10)
if (select count(*) from #IdenticalTables) > 0
begin
select name as 'Table(s) with the same name & structure and identical data:'
from #IdenticalTables
end
if (select count(*) from #EmptyTables) > 0
begin
select name as 'Table(s) with the same name & structure and empty in the
both databases:' from #EmptyTables
end
drop table #TabToCheck
drop table ##CompareStr
drop table #DiffTables
drop table #IdenticalTables
drop table #EmptyTables
drop table #NoPKTables
drop table #IndList1
drop table #IndList2
return
GO
"Nadim Wakim" <nadimlb@.cyberia.net.lb> wrote in message
news:O$j55AkGFHA.2936@.TK2MSFTNGP15.phx.gbl...
>

Anyone know why this isnt working...?

I am using the following C#... SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class loader = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class(); loader.ConnectionString = "PROVIDER=SQLOLEDB; Server=(local); database=TestDB; user id=test; password=password"; loader.ErrorLogFile = "C:\log.txt"; loader.SchemaGen = true; loader.SGDropTables = true; loader.CheckConstraints = true; loader.Execute( "C:\xsd.xsd"/* this file below */, "C:\xml.xml" );To generate tables in the database using the following xml schema...(file: xsd.xsd...) My problems is that it doesn't generate the tables and I am following the SQLXML2.0 documentation as closely as possible.Does anyone know what I have to do to get it to generate the tables? Running it doesn't even produce an error.Cheers,Matt.

I forgot to include to include the XML yesterday...

I am using the following C#...

SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class loader = new SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class();
loader.ConnectionString = "PROVIDER=SQLOLEDB; Server=(local); database=TestDB; user id=test; password=password";
loader.ErrorLogFile = "C:\log.txt";
loader.SchemaGen = true;
loader.SGDropTables = true;
loader.CheckConstraints = true;
loader.Execute( "C:\xsd.xsd"/* this file below */, "C:\xml.xml" );

To generate tables in the database using the following xml schema... (file: xsd.xsd...)

<?xml version="1.0" standalone="yes"?>
<xs:schema id="PartnerData" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="PartnerData">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="MessageHeader">
<xs:complexType>
<xs:sequence>
<xs:element name="SystemName" type="xs:string" minOccurs="0" />
<xs:element name="CreationDate" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="Year" type="xs:string" minOccurs="0" />
<xs:element name="Month" type="xs:string" minOccurs="0" />
<xs:element name="Day" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="MessageBody">
<xs:complexType>
<xs:sequence>
<xs:element name="Product" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="Manufacturer" type="xs:string" minOccurs="0" />
<xs:element name="Brand" type="xs:string" minOccurs="0" />
<xs:element name="LongDescription" type="xs:string" minOccurs="0" />
<xs:element name="ShortDescription" type="xs:string" minOccurs="0" />
<xs:element name="Category" type="xs:string" minOccurs="0" />
<xs:element name="Family" type="xs:string" minOccurs="0" />
<xs:element name="ProductCode" type="xs:string" minOccurs="0" />
<xs:element name="InformationsUrl" type="xs:string" minOccurs="0" />
<xs:element name="PictureUrl" type="xs:string" minOccurs="0" />
<xs:element name="PublicationDate" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="Year" type="xs:string" minOccurs="0" />
<xs:element name="Month" type="xs:string" minOccurs="0" />
<xs:element name="Day" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="RecommendedRetailPrice" nillable="true" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:simpleContent>
<xs:extension base="xs:string">
<xs:attribute name="currency" type="xs:string" />
<xs:attribute name="numberOfDecimal" type="xs:string" />
</xs:extension>
</xs:simpleContent>
</xs:complexType>
</xs:element>
<xs:element name="Characteristics" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="Characteristic" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="Label" type="xs:string" minOccurs="0" />
<xs:element name="Value" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Arguments" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="Argument" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="codeEan" type="xs:string" />
<xs:attribute name="localisation" type="xs:string" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema
My problems is that it doesn't generate the tables and I am following the SQLXML2.0 documentation as closely as possible.

Does anyone know what I have to do to get it to generate the tables? Running it doesn't even produce an error.

Cheers,
Matt.

anyone know the best way to do this

Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could do
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
--
Paul G
Software engineer.More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>sql

anyone know the best way to do this

Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could d
o
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
--
Paul G
Software engineer.More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>