Does anyone know if there is a utility available in SQL Server 2000 equivalent to the Tablediff utility in 2005? While reinitializing a Subscriber, I got the error "The row was not found at the Subscriber when applying the replicated command." I would like to identify the location of the data to fix at the Subscriber. In the interim, I changed the Distribution agent from the default to "Continue replication on data consistency errors," which successfully replicated changes, except for the errors. Is the stored procedure sp_publication_validation the only option for SQL Server 2000? This ran successfully after the replicated changes were processed.
In my transactional push replication I had differences, which were reported correctly by the utility tbalediff.
I created with the tablediff utility all necessary statements to correct them.
Afterwards I ran tablediff again to check if everything is ok.
It wasn't, I got a lot of mismatches.
The reason for those mismatches are columns from type "float". It looks like that float values are not generated correctly in insert statements. We had differences in the values and from that point on we have mismatches.
Does anyone has an idea how to get rid of those reported mismatches?!?!
I want to use Tablediff utility. It has not been installed into my system. Any idea, from where I can download it? I have both sql 2000 and sql 2005. May I use tablediff in both sql 2000 and 2005?
I am presently using 'tablediff' utility of SQLServer 2005 to compare tables located on two different servers. My real intension is to backup data from Serv1 to Serv2, except that I wish to update the tables on server2 with only newly inserted rows and updated rows and NOT deleted rows.
In short, I want the final .sql file generated by the tablediff utility to contain only 'Insert' and 'Update' statements and NOT 'Delete' statements.
I have created a linked server that point to a firebird database, everything is fine, I can query the database with something like
Select * from MYLINKEDSERVER...MYTABLE
Now , can i use tablediff utility with this linked server. If yes, any example ..
I would say also that performance is not very important to me. this would be used at the end of the day to make some sort of replication by generating a sql script with datadiff, and run it on a local sql server database and a web based SQL database. The replication is not an option since i am using a shared web hosting and replication is disabled on it .
Windows 2003 backup utility uses the shadow copy option that allows it to copy open files. Therefore, can I use this utility to backup the .mdf and .ldf files for my SQL 2000 database? I can then attach the .mdf files if I need to restore the database to another server. Can anyone tell me if this is safe? I've tried it and it worked but I'm worried there maybe some lurking danger in using this approach.
I recognized a strange behaviour when using tablediff. Because I don't want to replicate all columns, I create two views on selected attributes and compare them.
Have two tables: create table t1 (timestamp timestamp, b int PRIMARY KEY, c varchar(30))create table t2 (b int PRIMARY KEY, c varchar(30))
and two views: create view vt1 as select b, c from t1 create view vt2 as select b, c from t2
Compare the views with tablediff and get a failure: ... vt1 and vt2 ... have different schemas and cannot be compared.
Now I just find out that it works when table t2 also has a timestamp column.
drop table t2create table t2 (b int PRIMARY KEY, c varchar(30), ts timestamp)
Compare the views again with tablediff and get: ... vt1 and vt2 ... are identical.
What dou you think, do I always need the same datatypes in source- and target-table I want to compare, even if a create a subselection via views?
Using MSSQL Server 2012 Standard on a Windows 2008R2 Standard server.
For the tablediff utility MSDN says:
The Transact-SQL script generated to bring the destination table into convergence does not include the following data types: varchar(max), nvarchar(max), timestamp...etc
We have a small replication job running and one of the tables contains a timestamp field. After running tablediff the generated script contains a note:
-- Column(s) AccessTS are not included in this script because they are of type(s) text, ntext, varchar(max), nvarchar(max), varbinary(max), image, timestamp, or xml. Columns of these types cannot be updated by tablediff utility scripts; .....
The updated record is in the script file, but the timestamp field is omitted.
All good so far.
However, another table contains nvarchar(max) fields. The scripts that are generated for these tables do not contain the above message and instead contain these nvarchar(max) fields in both the update and insert into statements!
So why does it say it will ignore these types of fields, but then actually include the nvarchar(max) ones in the generated scripts?
TableDiff Stop Functioning on 2million records with 1.8milion changes on a particular row. Only happen if "-f" is specified. Anyway to work around this?
I'm trying to put together an automated system that uses sp_publication_validation & the tablediff utility to automatically sync up our subscribers to the publisher. What I'm noticing is that the sp_publication_validation sys proc gets run on the publisher. It does a rowcount and binary_checksum of each article in a specified publication. When I do a binary_checksum myself I've verified these results. For some reason a column could be a smalldatetime on the publisher and all the subscribers, however, the binary checksum will be different IF RUN from the publisher for that column/row/table. If I run the binary_checksum from the subscriber (instead of accross ODBC using a linked server command) I'm seeing matching checksums.
The result of this is I'm seeing a bunch of records that are actually identical coming through the tablediff command as different. I have a few questions. Has anyone ever run accross this issue before? I believe it has something to do w/ ODBC. I'm willing to work/share a pretty nice SSIS package that I am developing if somebody could assist me in figuring a way around this issue.
I am trying to do BCP from a comma separator .txt file to upload it into two tables. That is I want the first row (4 columns of header details ) into one table and from the second row (21 columns each of detail rows) to be uploaded into another table. I have written a batch file to upload the header details by giving the first and last column as 1.It is working fine. But when I try to upload the detail rows into another table by giving the first row as 2, the bcp is not able to read from the second row. It skips the second row and reads from the third row. This happens for all separators like pipeline separators also.So to avoid this I have to give blank 21 separators(i.e. , or |) in the second row to avoid the loss of data. Please provide a solution for this, as I cannot insist my customer to provide me a text file with blank comma in the second row. Thanks and Regards, S.Raghuraman
Hello,I have SQL Server 2000 Developer Edition. Where is SQL Server NetworkUtility?In Start menu SQL server group I have only Client Network Utility :|Is it a command line tool or sth? How to install it?tnx--STEFAN
I have just installed sql 2005 with sp2 and now I am trying to connect with other databases but when I try to register, it gives me an error stating something about not enabling "remote connection"..but checked the boxs and they are set to connect remotely...any ideas?
When i open the server network utility window, it doesn't show any protocols in Enabled protocols and Disabled protocols.:shocked: I have sql server 200 with sp4. pls can anyone solve my issue. thanx and regards kiran
When I try to start the SQL Server Express Utility (SSEUtil.exe), I get the following crash output message below ...
I have checked all of my instances. The all work with windows authentication and sql server authentication.
I have used the surface area configration manager to allow 'everything' and rebooted my machine.
I have tested each through osql. I have turned my firewall on an off. The problem stilll persists.
Could this have something to do with my SQL Services running under the local account?
Y:All_DatabaseMSsSQLServerSQLServerExpressUtilityJan2006Extracted>SSEUtil.exe -consolewnd -server HOME -user sa -password mypass -timeout 60
Unhandled Exception: System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Versi at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Console.SetOut(TextWriter newOut) at SSEUtil.ConsoleHandler.EnsureRedirectOutputStream() at SSEUtil.Program.Main(String[] args) The action that failed was: Demand The type of the first permission that failed was: System.Security.Permissions.SecurityPermission The first permission that failed was: <IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" version="1" Flags="UnmanagedCode"/>
The demand was for: <IPermission class="System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" version="1" Flags="UnmanagedCode"/>
The assembly or AppDomain that failed was: SSEUtil, Version=1.0.2130.35318, Culture=neutral, PublicKeyToken=null The method that caused the failure was: Void EnsureRedirectOutputStream() The Zone of the assembly that failed was: Internet The Url of the assembly that failed was: file:///Y:/All_DatabaseMSs/SQLServer/SQLServerExpressUtilityJan2006Extracted/SSEUtil.exe
I'm trying to learn how to use the bcp utility... but I keep getting an error message that says: SQLState = 08001, NativeError = 2Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].SQLState = HYT00, NativeError = 0Error = [Microsoft][SQL Native Client]Login timeout expiredSQLState = 08001, NativeError = 2Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. The command i'm running is: bcp territore.dbo.import in territorydata.csv -T -c territore is my database name... and dbo i think is the owner name. and the table is called Import I don't know how to check the default settings to see if remote connections are enabled. Any help would be appreciated.
I am looking for the SQL Server 2000 Licensing Setup Utility Programand cannot find it. Everything I read says, goto ControlPanel->Administrative Tools->SQL Server License Utility. When I gothere, I do not see... Any help would be Greatly appreciated!Setup:Win 2000 ServerSQL Server 2000 Standard Edition Svc Pk 3.Thanks.
I have stumbled on a problem with running a large number of SSIS packages in parallel, using the €œdtexec€? command from inside an SQL Server job.
I€™ve described the environment, the goal and the problem below. Sorry if it€™s a bit too long, but I tried to be as clear as possible.
The environment: Windows Server 2003 Enterprise x64 Edition, SQL Server 2005 32bit Enterprise Edition SP2.
The goal: We have a large number of text files that we€™re loading into a staging area of a data warehouse (based on SQL Server 2k5, as said above).
We have one €œmain€? SSIS package that takes a list of files to load from an XML file, loops through that list and for each file in the list starts an SSIS package by using €œdtexec€? command. The command is started asynchronously by using system.diagnostics.process.start() method. This means that a large number of SSIS packages are started in parallel. These packages perform the actual loading (with BULK insert).
I have successfully run the loading process from the command prompt (using the dtexec command to start the main package) a number of times.
In order to move the loading to a production environment and schedule it, we have set up an SQL Server Agent job. We€™ve created a proxy user with the necessary rights (the same user that runs the job from command prompt), created an the SQL Agent job (there is one step of type €œcmdexec€? that runs the €œmain€? SSIS package with the €œdtexec€? command).
If the input XML file for the main package contains a small number of files (for example 10), the SQL Server Agent job works fine €“ the SSIS packages are started in parallel and they finish work successfully.
The problem: When the number of the concurrently started SSIS packages gets too big, the packages start to fail. When a large number of SSIS package executions are already taking place, the new dtexec commands fail after 0 seconds of work with an empty error message.
Please bear in mind that the same loading still works perfectly from command prompt on the same server with the same user. It only fails when run from the SQL Agent Job.
I€™ve tried to understand the limit, when do the packages start to fail, and I believe that the threshold is 80 parallel executions (I understand that it might not be desirable to start so many SSIS packages at once, but I€™d like to do it despite this).
Additional information:
The dtexec utility provides an error message where the package variables are shown and the fact that the package ran 0 seconds, but the €œMessage€? is empty (€œMessage: €œ). Turning the logging on in all the packages does not provide an error message either, just a lot of run-time information. The try-catch block around the process.start() script in the main package€™s script task also does not reveal any errors. I€™ve increased the €œmax worker threads€? number for the cmdexec subsystem in the msdb.dbo.syssubsystems table to a safely high number and restarted the SQL Server, but this had no effect either.
The request:
Can anyone give ideas what could be the cause of the problem? If you have any ideas about how to further debug the problem, they are also very welcome. Thanks in advance!
I downloaded the SSEUtilSetup.EXE and extracted the SSEUtil.exe to a folder of C:drive of my PC that is Windows XP Pro. I plan to learn the CLR programming via user-instance of SQL Server Express. I need to have SQL Server Express Utility installed. Please help and tell me how I can install SSEUtil.exe in my PC and how I use it to interact with SQL Server Express.
Hello, i have a question that the sql server 2000 is install in window 2000 server. If i want to update to window 2003. Is that any problem in sql server 2000. I am worry about whether we will have problem after update. What i need to do? Many thanks.
I want to know how can i can use the bcp coomand to import a text file into a SQL database table . The text file contains info. like servername , date , time , logininfo and description .
The problem is i cannot import that file into the table through data import wizard . The wizard is not able to separate the fields properly and the import takes place with mixed up data in different columns . The separator in the text file is colon . I even tried fixed length with manual column separation . The text in the text file are not aligned properly as these are log files generated by the system which i am trying to import in a table .
So how can use the bcp command to specify the column length properly and seperate the fields uniformly so that the data gets imported neatly .
Hi, I used bcp utility to import and export data between databases. These exported data is it read only? If so how can I change the parameter so that we could edit as well. Any suggestions will be helpful. Thank you!
I want to use the BCP utility to import data from a .dat file into my database. The .dat file contains a table called xv_Appointments containing the following fields:
In the process of exporting data from SQL data file to text file through BCP utility I am not getting the Column names.How can I get the column names through BCP utility? I used this script exec master..xp_cmdShell 'bcp "select * from regulator.dbo.TEMPTBLBRANCHNOTUPLOAD" QueryOUT \indiadbftprootCLIENT_BRANCH_UPLOADranchnotup loaded.csv -S indiadb -U sa -P sasocrates -k -r -c -t "," -q'
