I am in the process of migrating to a new SQL 2005 server. I have a number of DTS packages on my SQL2000 server, approximately 200, that are used on a daily basis. I used the migration wizard to migrate the packages from the 2000 server to the new 2005 server however there are issues with the way some were brought over. I would like to have all of the packages moved from the 2000 to the 2005 server and appear under Legacy DTS so that I can run them as 2000 DTS packages unitl I have a chance to correct the issues.
Here is where my question lies. The migration wizard migrates upgrades all of the packages. How do I move them from the one server to the other and perserve their 2000 DTS format? The servers are on 2 separate boxes with different instance names. Everything I've read tells you how to run the legacy packages but nothing seems to explain on to move them.
My packages are migrated over our new 2005 server. How do I schedule/run them? BOL seems to suggest that we replace the dtsrun commands with the dtexec one. Am I on the right track?
Hi All, One of my user was able create DTS package using DTS Wizard, working from his workstation and saved this DTS in Legacy(in Data Transaformation services) on different SQL 2005 EE SP 2(9.0.3042) production server.. At same time he has no access to msdb on this SQL 2005 server(he also not sysadmin for this server).. How this could happen..??
We have a SQL server with many legacy DTS packages. sa and Admins can open them and change them then save them but we need to allow the DTS people (Developers) the rights to save the package after they have opened it and modified it.
Hi, I have imported 3 DTS from SQL 2000 to the SQL 2005 server. The wizard went fine, everything is ok. When I close the wizard window, I cannot see any of them. If I reimport it, it asks confirmation to override it. Where are the 3 DTS in the management Studio if they are not under Legacy DTS? Thanks
How can I scheudule a legacy DTS 2000 package if it stored in SQL Server itself?
I assume the package 'lives' in the msdb database.
For what bizarre reason is there no option to schedule legacy packages anyway, why provide the DTS legacy/designer components if the ability to schedule them isn't possible?
Is this microsoft's subtle way of telling me that I should convert them to SSIS packages ...I just don't have the time to do that...help
I am trying to migrate DTS packages from sql2000 To SQL2005 server. I am running the migration wizard from Data Transformation services under the ManagementLegacy Node on SQL 2005 server. I get to choose the packages to be migrated, but each of the selected pkg ends with a progress of "STOPPED" in the wizard and the outcome of the wizard shows as Successfull with a chaek mark on Top Left corner.
But No packageg appear under the location reff. above. I like to know if anoyone has a solution for this issue.
I have changed a legacy dts 2000 package. It resides on a sql 2005 server. How do I schedule it? That option doesn't look like it is available for legacy 2000 dts packages.
I have a legacy DTS package on my test SQL Server 2005 in the ManagementLegacyData Transformation Services folder. I can run the package, but how can I schedule it?? this doesn't appear to be an option anymore like it was in 2000.
I am converting several DTS pkgs to SSIS. Several of the pkgs contain complicated "Active X script" transformations on text files. That is, it would take me a long time to have to re-write this!
In the meanwhile, do you think it's just best to use the EXECUTE DTS 2000 task until I have a better grip on SSIS??
Also, what is the equivalent of "Active X script" validation in SSIS?
For example, I have an Active X script that checks the values of a particular column in a text file. If the column contains a datefield, then load into the database, if not, then discard... what task in SSIS would replace this logic? (not now, but for later reference)
I am trying to import a legacy dBase III file (.dbf format) into SQL server. The file contains timestamp fields which, as implemented in the dBase data file format, are actually eight-byte character strings. I am using this command:
SELECT * INTO LegacyData FROM OPENROWSET('MSDASQL','Driver={Microsoft dBase Driver (*.dbf)};DBQ=D:Files','SELECT * FROM data.dbf')
The command fails with this error:
Msg 8114, Level 16, State 8, Line 1 Error converting data type DBTYPE_DBTIMESTAMP to datetime.
This is happening because some of the datetime fields contain strings that can't be parsed by SQL as valid dates and times. The legacy application which created the data file apparently indicated a missing timestamp by storing "- - " as the character string.
If I change the select statement to say "select top 2 *" to only import the first two records (neither of which happen to have any invalid datetime values), the records are imported successfully. What I would like to do is to import all records and either skip those records that have a bad datetime value or, better yet, import all records converting invalid dates to null values.
I tried changing the select statement to include various types of casts but it seems that because the .dbf file indicates that the data field is of time timestamp, SQL will always try to read it as a datetime field regardless of how the select statement is written. I don't currently have any way of modifying the dBase III file or I would attempt to search for and remove the offending records.
Does anyone know of a workaround for such a situation? Is there a way I can import the data using SQL server or will I need to find a dabasebase conversion utility that can handle unparseable date strings?
My manager wants me to produce a legacy dts-style display of an executing package in an asp.net grid view. It would be color-coded the same way: red, green, black showing the status of each step with start and finish time. Any ideas on how to do this?
if we see provider=sqloledb in the connection string of a legacy sql server 2000/2005 system we're trying to understand better, can we conclude that ODBC is definitely not in the client picture, that possibly OLEDB is and that SQL Native isnt? Can we conclude for sure what middleware must be in use?
My limited understanding of this "middleware" space is that ODBC is old and restrictive, OLEDB is a lot more state of the art and general, and that SQL Native is more proprietary than OLEDB.
Even if we can conclude what middleware must be in use, is it generally as simple as changing a connection string and ensuring installation of the preferred middleware in upgrading to better performing middleware?
Hi, I am not able to delete the Legacy maintenance plans created: for example I created a maintenance plan: "MyPlan" with: master.dbo.xp_sqlmaint and the plan created at: ManagementLegacyDatabase Maintenance PlansMyPlan
After this I tryed to delete this using SQL Server Management Studio, it is deleted (right click on the plan and select delete menu item) But when I refresh the server, the "MyPlan" is back again.
There r plenty of duplicate rows in the legacy system(Unix /Cobol) from which the data has to be migrated to SQL Server 7.0 Using DTS.Could u please help me in finding out all the repeating rows so that my people could go through it manually and make it unique rows. I expect a query that can be executed to select the repeating rows in a staging/temp table that contain all the rows from legacy system. If u could sujest any other alternative u r most welcome...
I am unable to acces Oracle from legacy DTS designer, here is what I have done,
1. I have kept my old DTS packages in SQL2K5 legacy DTS container 2. Now I am unable to access oracle; it gives provider error 3. I am able to connect oracle from SQLPLUS
How can I troubleshoot this problem?
------------------------ I think, therefore I am - Rene Descartes
the SQL Management Studio keeps crashing out on me everytime i try to open a Lookup in one of my DTS packages i am using in the Legacy section.
I am copying the DTS package across and need to change the server connections (which i do), but then i was getting a permissions based error when the package ran when it tries to access the Lookup.
I tried to open the Lookup and the SQL Management Studio hanged when it tried to display the details for the lookup. It's done this many times and i have tried different files incase one was corrupted to no avail.
We have a large table which is very old and not much ppl take care about, recently there is a performance problem from the report need to query to this table. Eventally we find that this table have primary key missing and there is duplicate data which make "alter table add primary key" don't work
Besides the data size of this table require unacceptable time to execute something like "insert into new_table_with_pk from select distinct * from old table"
Do you have any recommendation of fixing this? As the application run on oracle , sybase and sql server, is that cross database approace will work?
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
When I installed VS 2005, it installed the default version of SQL Server 2005 Express that ships with Visual Studio 2005 installer media.
How can apply SQL Server 2005 Express SP1 to update this existing instance?
Currently, if I run this query:
SELECT @@version
I get the following:
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
After applying SP1, I should get 9.00.2047.00.
Should I just go to this link and download & install the SQL Server 2005 Express Edition SP1:
First of all I am planning to install sql 2005 standard and sql standard sp2 on vista ultimate professional using vs 2008 pro I have some questions about installing sql 2005 what is the best way to install sql 2005 on vista My article I got this from ms 403393 (upgrading sql standard 2005 to sql standard 2005sp2) 1 sql standard .(shortcut right click from cd/dvd not possible) I have heard command prompt start menu, click cmd type run D: cd/dvd exe From a wizard (sql sp2)
2. sql standard sp2 a. upgrade using a wizard b. open a command prompt (not sure if right click is best option) install dvd media example dqlserver2005sp2-kb921896-exe SKUUPGRADE=1(is run as administrator option on media) do I type run or what do I type ? c. go to system configuration page(where is this) you should see a single warnining you need to install sp2, the edition should say success if it does not please reapply d. on the registration page please enter your registeration number e. on the comments to install please enter database services, reporting services workstation components, books online and development tools( I wish to install all components)
f. on the instance page click default instance(default install) g. finish rest of wizard h. apply sql sp2
I also have of seperate question of upgrading sql from vista using a Configuring local admin account account (configuring administration from a wizard-will this notify me by default) I do not want my administrative priviledges elevated. I will be using default configurations for my installations......... I often wish there would a nice picture examples of doing this Please help
I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....
I am trying to import Access reports using Visual Studio 2005 and Access 2007. SQL Server 2005 with Reporting Services is also installed. I select Reports in the Solution Explorer and then Import Reports... Microsoft Access and browse to my Access database. After I select the database I see it open briefly as if it is trying to import the reports but then Access quickly closes and a error pops up: "You already have the database open." I have checked and double checked and the database is not open. I have even tried several different databases and the same errror occurs. Any help would be greatly appreciated.
We have a large number of clients attempting to replicate two publications on 2005 Express databases (2 publications subscribed to the one subscriber database) with our 2005 Server (9.00.3042.00 SP2 Standard Edition) and experiencing two significant problems:
1) Users experience the following message:
The Merge Agent failed after detecting that retention-based metadata cleanup has deleted metadata at the Subscriber for changes not yet sent to the Publisher. You must reinitialize the subscription (without upload).
This problem should not apparently occur with SQL Server 2005 (or 2005 Express) instances with SP2 applied. All clients experiencing this problem have SP2 installed as does our Server and the retention period is 30 days. The subscribers have been replicating well under that.
2) Replications never succeed after appearing to replicate/loop around for hours
This issue is the most critical as we have clients who have been installed and re-installed with new instances of SQL Server 2005 Express, new empty databases (on subscriber before snapshot extraction), and using fresh snapshots (less than an few hours old) which cannot successfully replicate.
Interestingly there is at least 1 instance where several computers are subscribed and successfully replicating the same database as another where replication refuses to succeed.
To test we have taken a republished database from another 2005 Server which is working fine and restored it to the same server as the one holding the database with which we are experiencing problems and subscribed to it. This test worked fine and replication of both publications went through fast and repeatedly without showing any signs of problem.
This indicates that the problem is perhaps data related as it appears localised to that database.
Below are two screenshots which may assist.
Screenshot 1 Shows that on the server side the replication attempts look like they are succeeding despite the fact that the subscriber end does not indicate success. Also the history indicates the the subscription has spent all it's time initialising and not merging any changes.
Screenshot 2 Shows a rogue process which has appears on many of the problem child subscribers. It shows a process running with no end time even though the job indicates failure in the message and even though other replication attempts appear to have succeeded after it. This process stays in the history showing that it is running even when I can find no corresponding process for it.
Can anyone suggest a further course of action/further testing/further information required which may assist?
This is extremely urgent and any assistance would be greatly appreciated!
Is there any way to measure bandwith usage during merge replication between sql server 2005 and sql server mobile 2005 running on a cradled wm5 mobile device.
Attaching the windows performance monitor to the network connection established over usb would work although I was wondering if there was something specific for this case integrated into Sql server 2005 / sql server mobile 2005 / Sql server management studio / third party tools that i could use ?
I have created a database using VB Express 2005 express edition and I have created number of tables and SP's etc.
I am trying to use the database diagram tool but I get an error stating that the database doesn't have a valid dbo. I say yes to the prompt about making myself a valid dbo but I then get a message stating that I do not have permissions to imprersonate the dbo.
I have a problem when i start sincronyzing with the emulator of MSVS2005 to SQL2005 in Windows Vista. I have the same program in the emulator, but sincronyzing with windows XP Pro and no problem...
"Failure to connect to SQLServer with provided connection information. SQL Server does not exist, access is denied because the IIS user is not a valid user on the SQL Server, or the password is incorrect"
A first chance exception of type 'System.MissingMemberException' occurred in Microsoft.VisualBasic.dll Public member 'Variables' on type 'IDTSPackage90' not found.
I am update/inserting records threw a web form in vb.net. I need to insert 'NULL' into my microsoft sql server database. I am not talking about the below line of code where website is the name of my paramater. If i do that it will just place a blank into that field in the database. If i dont enter anything into that textbox I want it to to say NULL in that field. So if I go into the actual table in the SQL Server Management Studio and look at the website field of the recored I just added or updated and did not type anything into the web site textbox it needs to say NULL. I also tried the second line of code but that places a single quote in front and behind NULL. So the field will have the value 'NULL'. website.value = txtwebsite.text.tostirng if txtwebsite.text ="" then website.value ="NULL" end if The reason why I need the NULL there is because I bind the website filed to a hyperlink template in a gridview. The actual text of the hyperlink is bound to PAYER which is a name of a insurance payer but the navigateto is bound to the website field. If i do not enter any data into the website field and it stays as NULL, then when my gridview is loaded, payers that dont have a website will not be underlined and user wont have the option to click on them since there is no value for the navigateto. But if i use my form to update/add a payer and leave the website textbox blank in puts a blank into that field in my database and when it loads that new row into the gridview the PAYER is underlined and u can click on it but it will just take u to the web site is unavailable page. So is there anyway to actaull have NULL placed into a filed and not just blank space
