I am trying to create a process that will take data source that has been output from a proprietary ISAM database to import into a SQL database. This particular ISAM system cannot be accessed via OleDB or ODBC. The thing is I want the process to be able to create the required tables based on data structure information that has been somehow encoded into the data source.
Currently the solution I am going with is to spit out a CSV file that has a header with table and data format information followed by rows of actual data that gets parsed by a SQL script however I am sure that Microsoft must have some kind of preferred solution to this kind of problem but I have not been able to find it. I have looked at the the tools that are available when creating a SQL Server DTS package as well as what seems to be available using the new Integration Services but nothing seems to be any better than the solution I just mentioned.
Anyone have any ideas, I am willing to bet there is a much better way of doing this.
Yes/No - i think bitE-Mail/MSNURLImages - should i use links to the image or can i store the image directly(like in Access)?Password - is there encryption? like in MySQL? Whats the Data Type and the length i should use. i don't need entering unicode chars so VARCHARs should be good for most?
I have sql server 2005 developer edition and vs2005 team system. I want's to use data cleansing feature.i.e. Integration Services Advanced Transforms Includes data mining, text mining, and data cleansing
Can please some body points me to any good webcast about using this feature
I have a table that among other holds volume data. I need to calculate something called Intelligent Volume based on set of rules. After all rules were followed and I still find data that does not belong to any of the rules this data is bad and needs to be reported on (can not be discarded) I wanted to do this is sql task (insert intel. volume) and I am using Cursor to loop through all the data and all rules. How do I redirect this data to a file so we can report on those records?
Hi All: Not sure what's causing this problem... I have a win2k webserver running FPSE and .NET 2.0 on a private subnet (192.168.x.x). The webserver connects to a separate SQL 2005 server running win2003 on the same subnet. I have an alias on the webserver pointing to the dB server, and in web.config I'd have a connection string like: <connectionStrings> <add name="webConnectionString" connectionString="Data Source=sql2005serveralias;Initial Catalog=webdB;Persist Security Info=True;User ID=webUser;Password=webPWD;Network Library=dbmssocn"providerName="System.Data.SqlClient" /> </connectionStrings> That connection string, as is, works...pages on the website connect to the server fine. If, however, I open the website in VWD2005 from a remote location (using FPSE over HTTP), and try to create a new page with a sql data connector, using the existing connection string in the web.config, I can't connect to the database. I get a "TCP Provider, error: 0 ...database is not accessible" type of error. Is this because I'm outside the subnet, and my local copy of VWD is trying to connect to the server on "192.168.x.x" and of course can't? If so, is the only way to work off the server is to VPN into the firewall and thereby get a local IP address assigned to my computer?
Im doing a project using VS2k5 and sql server2k5. Its a mobile project and this is my first time working with SQL and Visual Basic. I need to know how to add data to my database from the windows form.I have however been able to use the tableadapters and some sql statements to read data from the database in my forms. Can anyone here help me?
I am currently in the process of migrating data from Sybase to Sql server and would like to know how to test the data migrated.
As of now, we took one table data from both source and destination and compared it in Excel to check if the data migrated looks good (note, we used SSIS to migrate data). However, I would like to check if there are any other best & easy ways to apprach data validation post migration.
Where interid in ('comp1', 'comp2', 'comp4', 'comp5')
what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?
I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?
I have been asked to design a solution for a client of mine who basically requires the daily analysis and reconciliation of the differences between 2 extremely large text files.
The files are not in an identical format but are both in some form of delimited format (one is CSV, the other is a little more complex). For the sake of this question, let's assume that I can effectively import each file into an MS SQL table.
Each file will have in excess of 100,000 rows each day (new data for each day).
Whilst I know that MS SQL does easily have the capacity to store the data, is there a recommended way to tackle the potential problems (I imagine that performance is important... they will be running the report every day)
Or is building the solution as simple as importing the data into 2 tables, and then querying the differences and outputting as a report using Crystal?
i need to design a database table which will store supplier's demand information. 1 supplier will probably have 10000 records and there are posibility that there are 10,000 suppliers. So, in total, the number of records will be 10000 * 10000 = XXXXA LOT XXXX which will be very large number of record to be inserted into a table. So, how can i design an table and structure to cater this scenario? Thanks.
Hi All,Further to my previous long-winded question about a situation in whichwe appear to be mysteriously losing data from our mssql2k server.We discovered an update statement, in the stored procedure we believeis at fault, after which no error check was being performed.Under certain conditions, this update is fired against the same recordin the same table as the immediately preceding update statement withinthe transaction. We are now suspecting that under some circumstances,these two updates get into a locking conflict that is eventuallyforcing the transaction to be rolled back.However, I'm still left with three questions.1) Where an update in a transaction gets locked, and an error isn'ttested immediately afterwards (ie no 'IF @@Error<>0' test is made),would the transaction proceed as normal?2) Most critically, would statements in the stored procedure thatappear after the COMMIT TRAN statement also be executed, even if anunresolved lock existed within the transaction?3) Assuming that (2) does happen, would a SELECT made on anotherconnection with a 'WITH(NOLOCK)' locking hint be able to see thechanges made in the locked transaction even if the server is set toREAD COMMITTED, and the SELECT takes place some time after the COMMITTRAN is issued? More to the point, given (2), how long would thelocked transaction survive before being rolled back after the COMMITTRAN has been issued? Is it possible that the COMMIT TRAN takes place,the transaction is flagged for potential rollback while a lockresolution is attempted, the stored procedure exists as thougheverything was fine, a subsequent SELECT (ie performed as one of thenext operations in the same application) using WITH(NOLOCK) 'sees' thechanges made by the transaction, reinforcing the impression that thetransaction succeeded, and then at some point thereafter the lock isdetermined to be unresolvable and the transaction is rolled back,making it seem as though the data disappeared, even though it had beenSELECTable via a different connection to the server?Thanks, by the way, to Simon and Erland for your advice on my previousquestions about this problem.Much warmth,M Wells
I am trying to migrate our Portals database from SQL2000 to SQL2005, but I received "SQL Type Variant Data" error during the data migration with some database. Can anyone help me with this?
Here is the solution to this error message if anyone gets one in the future...It took me about two days to figure out.
This error occurs if you save your stored procedure before execute it. Always execute first before you save. If you save before executing, the server will store its own defaults, usually integers.
This error aslo occurs if your datatypes do not match when passing values from code into the variables of a stored procedure.
It also occurs if the datatypes in your sql file do not match those of the original stored procedure.
You can check to see if the file saved in the "Projects" folder matches with the original by doing the following:
Expand Database, Expand Programmability, Expand Stored Procedures, Expand (Your Stored Procedure), Expand Parameters. Read the datatypes that are revealed in the tree.
Then go to File>Open>Projects>(save sql file). Click open.
View the datatypes in the file. If the datatypes in the file do not match the datatypes in the tree, what you must do to correct, as one solution, is delete both the file and stored procedure.
Then create a new stored procedure by right clicking the stored procedure node. Rewrite the store procedure, execute and then save. Everything should be okay.
I have an application that automatically reads a lot of data from a third-party application into my database, via XML. For example, I might read a couple thousand rows-worth of XML data, one row at a time in a foreach loop. To reduce the load on their server and database, I thought about putting a 2 second delay in between each of my automatic requests. Would this really help much, or is there enough overhead (setting up/tearing down connections, etc) with each request that it wouldn't reduce server load much anyway? Is 2 seconds enough? Too little or too much?
I have a problem at pulling data on PDA using Sql Mobile 5 from Sql Server 2000.
It work s for most tables, except for two . Every table of those two has about 1000 records and every record about 150 bytes, so all data is about 150 kbytes.
When I connect to the Sql Server 2000 through LAN, no problem. But when I connect through GPRS - VPN, the error occurs. But, the PDA- phone connection is via Bluetooth and at ping no TimeOut appears, the replies are about 600-700 ms. When connecting with a desktop computer through GPRS no such problem occurs.
So , I don't know what could be the cause: the connection or the Sql Mobile which doesn't retry enough in case of error to transfer all data .
A specialist from the GPRS provider told me to use 3G phone , but , because the GPRS phones have already been bought, this would be a too big investment. So, I don't think this is a good idea in this moment .
I found similar problem on the net and a solution was to use Pooling = False. But , in Sql Mobile there is no pulling parameter available when making the connection string .
Hi,I want to know the optimal solution, to find if all the data was entered. Lets say, Table A (date field) and for a given month, i need that all the days in the given month are present in the Table A. Right now i have different solutions, 1) a stored procedure which loops through all the days in the given month against a select statement on Table A2) a stored procedure, create a temp table which contains all the dates in the given month, and a single select statement using where condition (select * from.... where datefield not in (select * from...))I want to know what is the best solution of these two or any other solution.Thanks
Dear All, I have a page on the web site that will upload a CVS fiile. Then, it will read the file, and insert the data to the sql database. (around 14,000 records) I'm not sure why, it seems like whenever we do the upload/insertion for the first time, it will always return this timeout error. System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.Data.OleDb.OleDbException: Timeout expired After I got this error, and do the upload again. Then the upload will work fine. So I wonder is it when I got the timeout error, will the SQL server clean up its used resources or something, so my function will work the next time? Or any recommendation on this issue? Thank you very much.
I want to download data from an AS/400 to a SQL 2005 Database.
Within Sql server 2000 it was possible to do more than 1 transformation with just 1 source connenction and 1 destination.
In SSIS I have created:
- new solution
- data source
- Data source viewer ( I made named queries to format the data as i want it to be in Sql)
Although it is possible to view the named query data it does not seem to be possible to import the data in the data viewer into my sql 2005 database. (For each dataflow I have to define each query again)
I have added the data source in the connection manager.
Also I am searching to import more than 1 file in 1 package !
I can use however a Dts-2000 package but why then use SQL server 2005
I have data in tables with the constraints turn on. I would like to remove all the data from the tables and add new data, and also reset the identity back to 1, without dropping the constraints.
Any suggestions??
If I used truncate table, I would have to remove the primary key. If I remove the primary key I would loss my constraints.
I need to migrate data from Table 1 to Table 2 based on Type.
Type can be 1 or 2.
Example of Source.
Table 1 has
Id Type
1 1
2 1
3 1
3 2
Table 2 should have 2 records for each Id and based on the Type have different Part Numbers. Part numbers will be the same if in the Source table I had just one record with type 1 and if I had 2 records with type 1 and 2 - Part number will be different.
Example of Destination.
Id Type PartNumber
1 1 10
1 2 10
2 1 11
2 2 11
3 1 12
3 2 13
================================
As you see above
1. I need to duplicate records which had just one type in the Source table.
1 1 10
1 2 10
I will have 2 (1 and 2) types now, but everything else the same for the same id.
In my case I will have Part Numbers as guid. I would create them on my own. They will be the same here.
2. I need to have 2 records as it was before in Source table for id's with 2 types, but with different Part Numbers.
3 1 12
3 2 13
In my case I will have Part Numbers as guid. I would create them on my own.They will be different here.
How do I create 2 records for each Id and certain rules for them? How do I find if there is one or there are two records in Source and how do I apply my rules in looping through records? What control in SSIS tool can I use?
VB.NET 2005 Express and SQL Server 2005 Express - NOT saving updates to DB - SOLUTION!
-----------------------------------
The following article is bogus and confusing:
How to: Manage Local Data Files - Setting 'Copy to Output Directory' to 'Do not copy' http://msdn2.microsoft.com/en-us/library/ms246989.aspx
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
Do not copy
The file is never copied or overwritten by the project system. Because your application creates a dynamic connection string that points to the database file in the output directory, this setting only works for local database files when you manually copy the file yourself.
You must manually copy the database file to the output directory AFTER setting 'Copy to Output Directory' to 'Do not copy'.
-----------------------------------
The above article is bogus and confusing.
This is rediculous!
This is the most vague and convoluted bunch of nonsince I've ever come accross!
Getting caught out on this issue for the 10th time! And not being able to find an exact step-by-step solution.
--------------------------
I've tried it and it doesn't work for me.
Please don't try what the article eludes to as I'm still sorting out exactly what is supposed to be happening.
If you have a step-by-step procedure that can be reproduced this properly please PM me.
I would like to test its validity then update this exact post as a solution rather than just another dicussion thread.
Many thanks.
This is the exact procedure I have come up with:
NOTE 1: DO NOT allow VB.net to copy the database into its folders/directorys.
NOTE 2: DO NOT hand copy the database to a folder/directory in your project.
Yes, I know its hard not to do it because you want your project nice and tidy. I just simply could NOT get it to work. You should NOT have myData.mdf listed in the Solution Explorer. Ever.
Create a folder for your data following NOTE 2.
Copy your data to that folder. * mine was C:mydatamyData.mdf
Create a NEW project.
Remove any Data Connections. ( no matter what)
Save it.
Data | View Data Sources
Add New Data Source
select NEW CONNECTION ( No Matter what, do it!
Select the database. * again mine was C:mydatamyData.mdf
Answer NO to the question: Would you like to copy the file to your project and modify the connection? - NO ( no matter what - ANSWER NO ! - Absolutely NO ) Then select the tables you want in the DataSet. and Finish.
To Test ----------
From the Solution Explorer | click the table name drop down arrow | select details Now Drag the table name onto the form.
The form is then populated with a Navigation control and matching Labels with corresponding Textboxes for each field in the table.
Save it.
1) Run the app.
Add one database record to the database by pressing the Add(+) icon
Just add some quick junk data that you don't mind getting lost if it doesn't save.
YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Now exit the application.
2) Run the app again.
And verify there is one record already there.
Now add a second database record to the database by pressing the Add (+) icon.
NOW add some quick junk data that you WILL intentionally loose.
*** DO NOT *** press the save icon.
Just Exit the app.
3) Again, Run the app.
Verify that the first record is still there.
Verify that the Second record is NOT there. Its NOT there because you didn't save the data before exiting the app.
Proving that YOU MUST CLICK THE SAVE ICON to save the data you just entered.
Also proving you must add your own code to catch the changes and ask the user to save the data before exitiing or moving to another record.
As a side note, since vb.net uses detached datasets, (a copy/snapshot of the dataset in memory and NOT directly linked to the database) the dataset will reflect all changes made when moving around the detached datasets. YOU MUT REMEMBER TO SUBMIT YOUR CHANGES TO THE DATABASE TO SAVE THEM. Otherwise, they will simply be discarded without notice.
Whewh!
I hope this saves me some time the next time I want to start a new database project.
Oh, and uh, for anyone else reading this post.
Thanks, Barry G. Sumpter
Currently working with: Visual Basic 2005 Express SQL Server 2005 Express
Developing Windows Forms with 101 Samples for Visual Basic 2005 using the DataGridView thru code and every development wizard I can find within vb.net unless otherwise individually stated within a thread.
I have Data Flow task that contains 50 components.
My computer configuration: 1 GB RAM Microsoft Windows Server 2003
Periodicaly when i try to save package after making some changes Out of memory ... exceptions message box appears , and soon after this Not fatal error occurs ... message box shows . If i close solution and open it again all my 50 components disappears --instead I see clear list, and all my work losen.
Such "Not fatal errors" making hell out of job -- every time I need to change package i must add package to archive!!!
I need a sql server 7 script that will migrate data from my DEV dbase ON THE ALPHA SERVER to my WEB dbase ON THE BETA SERVER in a WINDOWS 2000 network. The data desired is for the field rig_id for rig number 0018. The shell of both dbases are similar. The table is called rig. An snapshot is below:
The rig table also exists on the WEB dbase. However, the Kaila data for rig_id number 0018 needs to be added to the WEB dbase's rig table. The Williams, Borsha, and Yoida data already exists on both tables.
I need to perform this migration w/ a TRANSACT-SQL stored procedure.
I have to migrate a database from 6.5 to 7.0 server. We don't have an upgrade wizard to i will have to do a turn around like :
> Scripting the objects > Running the scripts in 7.0 > BCP out the data into 7.0.
But the problem lies here ! how do i BCP the whole database table data in one shot ? bcos i have a lot of tables in the database and i cannot afford to do a table at a time. If anyone has any script which creates pl do pass it on to me.
Does anyone have experience in migrating data (not structure) from an Oracle 7.x database to a new schema built on SQL 6.5? Especially using third party migration tools, for example Data Junction 6.5?
I would be interested in seeing any materials you can provide or advise as I start down this path.
I'm working on the data migration section of my project at the moment. The customer I'm working for has an existing system of storing client information in an Excel file. Now, I want to move all this information into SQL Server so my new webapp can access the information. The problem is, for the client details, I used an ID system. For example, each piece of client information can be selected from a list. In the client table, this is stored as an ID. Then I have a ReferenceItem table where the IDs are mapped to descriptions. So my Client table has numbers in it, that map to descriptions in a different table.
Now, the existing Excel files have the descriptions, not IDs. What I need to do is to write some kind of script that will automatically go through the table created after I imported the Excel file, check each description and find it's ID and replace that description with the ID.
Now, I don't even know where to start on this one so if anyone has any pointers, could they please let me know?
Please find the script for migration. Here I have attached two supporting documents. One is database diagram and the Excel sheet itself
USE [COONEW] GO /****** Object: StoredProcedure [dbo].[GetScheduledData] Script Date: 14/02/2014 10:35:29 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetScheduledData]
I just moved to SQL 2005 and am facing a difficulty trying to build a DTS Package using SSIS Projects. I need to migrate data from an old Database design to a new Database design in my local pc.
Can anyone provide me some guidelines or tutorials on which components to use and a flow of the entire process which I should follow? I am confused.
Thanks alot in advance, hope to hear from you guys soon.
Hi Can Any one help me out of this in my project I have to migrate only datastructure or schema and not the data inside the table using SSIS. How can I do that ?
