Hi, does anybody know a nice tool to automatically compare and show
only the structural differences between two databases, this is, the
differences in the columns of a table, constrains, data type, etc.
Thanks
J.A.
I have a Development and Production database and they are a little out of sink. Does any one know of a way to compare two databases (tables, fields, sp) and report the differences? Thanks in advance.
View 3 Replies View RelatedHey all,
Trying to compare unique fields from two databases.
Basically what i'm trying to do is get all of the id's out of one field that don't exsist in the 2nd database.
so I have this query
Code:
select Distinct Id
from table1
where ID!= (select Distinct ID from table2)
I'm getting this error
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Now I don't understand why because then I query
select id from tables 2
I only get 1 value for each row.
Now you should know, the two columns in either table are different, in one of them the first value is "1" in the other the first value is "00000001" although I don't think that should effect anything.
Any idea's?
Thanks
Hi all,
Sql server 7
i have two databases best and books.Most of the tables present in best are in books except some.
i have been given a task to list out the tables and columns of which are present in best database and not in books database.
Pls suggest me the simplest and quickest way to do this.
this is very urgent.
waiting for reply.
TIA
Adil
Hi,I would like to compare two sql server databases.I need to find the difference in the two database(i.e table, colums,data type)Is there any free utility available to compare sql server databaseI'm using sql server 2000.Help me out to compare databasesThanks & Regards,Mani
View 2 Replies View RelatedHello,I would like to make a comparison between two databases in SQL server,to find differences between tables, procedures and so on.I have searched the web and found several softwares able to perform thetask, but none able to compare a database with a backup otained from aSQL Server database, or between two backups.Is this possible ? Is there a software able to perform such comparison?Thank you for your help.--L'amour, c'est un sport. Surtout s'il y en a un des deux qui veut pas.[Jean Yanne]
View 4 Replies View Related
Hey guys,
Im busy working on converting one DB to look like another one and I am using a data comparison tool to generate a script that I can hopefully use to upgrade an existing DB.
The problem is occuring with only one table and as soon as the script is run I receive the following error msg in SQL server 2005;
______________________________________________________________________________________________________
'Patient' table
- Unable to modify table.
Cannot insert the value NULL into column 'ClinicCode', table 'DCS.dbo.Tmp_Patient'; column does not allow nulls. INSERT fails.
The statement has been terminated.
______________________________________________________________________________________________________
Now the problem I am facing and I am hoping you could help with is where I can find the .Tmp table. This is obviously a temporary table created, but I have no clue as to how to alter the values of that temporary table in order to facilitate the changing of the table to coincide with the updated table.
Some help would be appreciated in this regard.
p.s. Hopefully I explained the problem sufficiently for you guys to help out!
Thanks in advance
Hi, I would like to know if it is possible to compare data in two different databases (both SQL7.0),
with same exact schema. If possible, how could we do it?
Thanks
- Sharma
Hi
Is there a way of comparing the data contained in two separate databases (on the same server)?
I have two databases: DevDB and DB and all I want to verify is that the data in both are *exactly* the same.
Is there a nifty function to do this in SQL Server?
Thanks
Sara
I want to compare the records in a table on my live database server with the same table that is my test database server. How can I do this?
Note that both databases are mirrors of each other but contain slightly different data and are on different servers.
Hi All,
I wan't to compare two SQL Server databases. i.e., I wan't to check both databases structure and add if necessary.
In my project I am using SQL Express database and now we update some fields in the database. So, if we wan't to update the new versions for those customers who are using old database we need to update the database also. It is not recommanded that to manually add the fields in the database and is time taking too. So, I wan't to compare two databases(old and new) and update the fields in the old database.
Any confusion in my question plz let me know so that I can be more clear.
Thanks for any reply,
Narayana Ayyagari.
Hi
can anybody tell how to compare two databases on sql server
thank u
vizai
Hello,I am starting to examine using SQLServer instead of Oracle. I wentthrough the SQLServer import utility to copy tables from Oracle intoSQLServer. I then checked the size of the database.I then started to specify indices for the tables. I did not see thedatabase file size grow all that much. I kept on using the shrinkcommand to make sure that wasted space was removed. I even made surethat minimal free space was allowed.I was rather expecting (from experience with Oracle) that there wouldbe a large growth in the database when addind indices. So what did Imiss? Are the indices stored elsewhere and I missed them? DoesSQLServer handle indices differently so they dont' bloat the databasesize?The size of our data in SQLServer is a key factor in whether we movefrom Oracle, so I need to under how indices effect storage size.Thanks for any help.Bart TorbertJoin Bytes!
View 7 Replies View RelatedHi All,
Is there a way in SSIS to change the database connection to sqlserver or oracle or db2 without choosing the connection manager in the source.
For e.g. I ran a package which is reading data from sqlserver and later i want to connect to oracle to read the data but without changing the connection manager in the source.
Basically, i want to find out from you gurus is about a way to switch between databases without changing the connection managers. OR the only way is to make different sets of packages for oracle / db2 and sqlserver sources ?
Thanks,
Vipul
We have a static class that makes an HTTPWebRequest to get XML data from one of our vendors. We use this as input to a stored proc in SQLServer2005. When I compile this class and call it from a console application in visual studio it executes in milliseconds, everytime. When I compile it, create the assembly and clr function and execute it in SQLServer, it takes around 14 seconds to execute the first time, then on subsequent requests it is again really fast, until I wait for 10 seconds and re-execute, once again it is slow the first time and then fast on subsequent requests. We do not see this behavior when executing outside SQLServer. Makes me think that some sort of authentication is perhaps taking place the first time the function is run in SQLServer? I have no idea how to debug this further. Anyone seen this before or have any ideas?
Here is the class:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Net;
using System.IO;
namespace Predict.Services
{
public static class Foo
{
public static string GetIntradayQuote(string symbol)
{
string returnQuote = "";
HttpWebRequest request = (HttpWebRequest)(WebRequest.Create("http://data.predict.com/predictws/detailed_quote.html?syms=" + symbol + "&fields=1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,30"));
request.Timeout = 1000;
HttpWebResponse response = (HttpWebResponse)(request.GetResponse());
StreamReader streamReader = new StreamReader(response.GetResponseStream());
returnQuote = streamReader.ReadToEnd();
streamReader.Close();
response.Close();
return returnQuote;
}
}
}
When I run call it from a console app it is fine.
I compile it into a dll and then create the assembly and function as follows:
Code Snippet
drop function fnTestGetIntradayQuoteXML_SJS
go
drop assembly TestGetIntradayQuoteXML_SJS
go
create ASSEMBLY TestGetIntradayQuoteXML_SJS from 'c:DataBackupsCLRLibrariesTestGetIntradayQuote_SJS.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
go
CREATE FUNCTION fnTestGetIntradayQuoteXML_SJS(@SymbolList nvarchar(max)) RETURNS nvarchar(max) AS EXTERNAL NAME TestGetIntradayQuoteXML_SJS.[Predict.Services.Foo].GetIntraDayQuote
go
declare @testing nvarchar(max)
set @testing = dbo.fnTestGetIntradayQuoteXML_SJS('goog')
print @testing
When I execute the function as above, again, really slow the first time, then fast on subsequent calls. Could there be something wrong with the code, or some headers that need to be set differently to operate from the CLR in SQLServer?
Regards,
Skipper.
Hi experts;
I have a problem with unicode character 0x2300
I created this table
create table testunicode (Bez nchar(128))
Insert Data
insert into testunicode (Bez)values('Œ€„¢')
with 2 Unicode characters
Œ€ = 0x2300
„¢ = 0x2122
Selecting the data
select Bez from testunicode
I see
"?„¢"
„¢ = 0x2122 is ok but instead of 0x2300 there is 0x3f
When I modify the insert statement like that ( 8960 = 0x2300 )
insert into testunicode (Bez)values(NCHAR(8960)+'„¢')
and select again voila i see
"Œ€„¢"
Does anyone have an idea?
Thanks
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
Thanks!!
I'm chasing after a documetn that was available on one of the Microsoftwebsites that was titled somethign like "MS SQL Server Best Practices"and detailed a nyumber of best practices about securing the server.Included in this was revoking public access to the system tableobjects.Can someone post the URL where I can pick this up, or drop me a note oncontacting them for a copy of the document?
View 2 Replies View RelatedI have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
Hello,
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
Thanks in advance,
TassosTS
Hello people.
I am in the process of planning a server upgrade to sql2005 x64.
I created 2 linked servers: one to a SQL2000 sp4 server and one to a SQL7.0 SP3.
I have the following error when I query the linked servers.
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "IVDM2K" returned message "The stored procedure required to complete this operation could not be found on the server. Please contact your system administrator.".
Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES_INFO" for OLE DB provider "SQLNCLI" for linked server "IVDM2K". The provider supports the interface, but returns a failure code when it is used.
I am aware of KB 906954.
http://support.microsoft.com/default.aspx?scid=kb;en-us;906954
I applied the instcat.sql on the SQL2000SP4 server and my linked server issues for that one are gone.
However, I ran the instcat.sql script on the SQL7.0 sp3 server and the linked server is still giving me an issue.
Can someone help me find a solution to this?
Regarding KB935356, is there a "post" service pack 2 update for SQLServer Express?
Thanks.
Hello Expert!
I have 2 Database €“ Access & SQLServer(ver 7)
I need to Import Data TblShift from Access to SQLServer €“ using DTS I€™ve done this successfully!
Now I want to use parameter so I only importing record within range (e.g. ShiftDate BETWEEN 05-24-2006 AND 06-23-2006)
In SQLServer, I have created table to store the date range as following:
TblParameter
DateFrom: 04/24/2006
DateTo: 05/23/2006
How do I use the date range from TblParameter(SQLServer) to import record from TblShift(Access) using DTS?
Is this possible or any better solution for this?
TIA
Regards,
Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
Hi Friends,
Can some please let me know the differences between sqlserver 2000 and sqlserver 7.0
Hi,
I am new to SQL Server 2005. I tried connecting to my local machine by using my machine name as Server name and then tried running the following query:
SELECT * FROM SYS.Objects. It gives me following error:
Invalid object name 'sys.objects'.
Whereas, if I connect to my local machine using mahcinenameSQLEXPRESS, then the above mentioned query runs fine.
Why is this difference? What is the difference when I login in these 2 different ways.
Any help would be appreciated.
Thanks in advance.
Any help
Is there a way to transfer data from a SqlServer db to a SqlServer Express db. I tried to use the backup file of SqlServer, but this file is not valid for SqlServer Express. Or there any alternatives?
thanks,
Henk
I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
Hello,
I am trying to retrieve the data that are not more than 3 months. How do I do this? The closest thing that I can do is...
(dbo.CLASSIFIEDADS.PostDate > CONVERT(DATETIME, '2006-03-26 00:00:00', 102))
I want to be able to put 3 months in there somehow...
Thanks in Advance!
Hi,
I have a table with 3 columns inside
- record_id (int)
- user_id (varchar)
- login_date (date)
it is a many-to-many relationship table that record login date of users
Now, I want if I want to COUNT the users who login before 31 May 2008, I would use
SELECT COUNT(*)
FROM table1
WHERE login_date < '2008-31-05'
That's works
But the problem is I want to split the result to
- How many people visit the site only once?
- How many people visit the site only twice?
- How many people visit the site three time?
How do I do that?
please help
Thank you
Hi,
I have a field in database money. When I enter value for it the amount entered is for example 20.000. How can I compare this value with noraml vaules i.e. like 20 in my search engine. Will I need to convert it to varchar and then compare it or is there some other way. Also if I need to convert it to varchar, how can I do it?
Thanks in advance,
Uday
hi ... please look at it :
<< The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. >>
ok ... it was my error mess, but i just wrote this code :
***********************************************************
Dim conPubs As SqlConnection
Dim cmdSelect As SqlCommand
Dim strSelect As String
conPubs = New SqlConnection ("Server=localhost;uid=ggggg;pwd=uuuu;database=eeee")
strSelect = "Select username From userpass Where username = '" & usernametxt.Text & "' And password = '" & passtxt.Text & "'"
cmdSelect = New SqlCommand( strSelect, conPubs )
conPubs.Open()
if Not(cmdSelect.ExecuteScalar() IS Nothing) then 'findeduser.text
response.redirect("welcome.aspx")
conPubs.Close()
else
msg.text="ID or Pass is incorrect!!"
conPubs.Close()
end if
***********************************************************
what is wrong?
can you help me?
me : mhamini@gmail.com
I am wondering how I would create a SELECT that will select the most recent date from one of two tables. For example, table1 has a field called LastUpdate and table 2 has a field called LastUpdate. I need to grab only the most recent date. I tried this using an inner join...but that didn't work because it only picks the lastupdate form one table only. talbe1 and table2 are tied by table2.table1id.
Can anyone help?