Migrating from MDF File to SQL Server 2005?

I created a web site using the "personal web site" starter kit with Visual Studio 2005. It created an ASPNET MDF file as well as a Personal.MDF in my App_Data directory.

I want to migrate this site to a production server that does not have SQL Express, but does have a full version of SQL Server 2005.

How do I migrate both the database format (tables and SPs) as well as the data to the SQL Server?

I can't find anything that looks like Enterprise Manager or Query Analyzer on my system, and installing the "native client tools" doesn't do anything except install configuration utilities (no clients).

Inside of Visual Studio 2005, I can bring up both databases (one from the MDF file(s) and one using a connection string to the server), but there doesn't appear to be any way to copy from one to the other.

There must be an easy way to do this, right?

Many thanks in advance!

Hello cambler,

You can simply do a straight-forward Attach Database function in SQL Server 2005 Management Studio. You can simply copy the MDF and LDF files from your App_Data folder from your PC to your production server running SQL Server 2005. Then, attach the database pointing to the copy of the MDF file. You need to detach the database files first on your development PC before you can copy the MDF and LDF files as the service is making use of these files.

For more information, visit http://support.microsoft.com/default.aspx?scid=kb;en-us;224071

|||

I am trying to utilize the SQL Server Management Studio to open an MDF file, so I can perform tasks such as importing CSV files. I guess that I need to attach to a an MDF file, but I don't see the that option available.

If there is a nother approach, please advise.

Thanks,

|||From SQL Server Management Studio, you can simply right-click on the Databases folder and select Attach. Then point to the location of your MDF file. That should do the trick.sql

Migrating data into dbf file

I have to migrate datat from my table to my accounting system.

The table fields of the accounting system are different from my sql table and the Acc sys accepts only .DBF files.

I would have to migrate the data daily.
So my question is: whats the best approach for doing this work.
Should I create a nother table in Sql Server with the field names as the dbf file and write a SP to get all the data from another tabel into this one and then using export wizard to export data into a dbf file.
Or is there any better appraoch
regards

Hi,
Perhaps the better approach is a Data Trasformation Service scheduled daily.
Best regards

migrating (cloning?) DTS mapping

I have a DTS package on SQL Server 2000 that loads a table from a text file daily. The text file has 148 distinct fields that are mapped to a corresponding 148 columns on the table. I need to recreate the package to SQL Server 2005. Is there any shortcut to replicating the mapping from DTS to SSIS or do I need to redo from the beginning?

Dennis Cronin wrote:

I have a DTS package on SQL Server 2000 that loads a table from a text file daily. The text file has 148 distinct fields that are mapped to a corresponding 148 columns on the table. I need to recreate the package to SQL Server 2005. Is there any shortcut to replicating the mapping from DTS to SSIS or do I need to redo from the beginning?

If there are 148 columns in both source and destination then you can probably use the Import Wizard to build the package for you and then adapt it as you wish. very easy and very quick.

-Jamie

Migrated NT users to AD - Permissions (Cross Posted)

(This message is cross posted to SQL and AD groups).
I'm in the process of migrating NT users to Active Directory. For file share
access this is fine because I migrate with SID history so everything just
works.
However I've done some test migrations for users with SQL permissions and
they can't access any databases. (As the permissions says their old name).
Short of me having to go through every single server for ever user is there
a way to update or a way for this to work?
Thanks
Rob.
Thanks.
Do you know what scripting i would be looking at or what third party tools?
I've spent a good while searching on this topic and am unable to find
anything so would appreciated any advice.
Thanks.
Rob.
"Jorge de Almeida Pinto [MVP - DS]"
<SubstituteThisWithMyFullNameSeparatedByDots@.gmail .com> wrote in message
news:%23ZQC3GxaIHA.1212@.TK2MSFTNGP05.phx.gbl...
> ADMT does not update SQL DBs. You might do it yourself (scripting) or buy
> third party software
> --
> Cheers,
> (HOPEFULLY THIS INFORMATION HELPS YOU!)
> # Jorge de Almeida Pinto # MVP Windows Server - Directory Services
> BLOG (WEB-BASED)--> http://blogs.dirteam.com/blogs/jorge/default.aspx
> BLOG (RSS-FEEDS)--> http://blogs.dirteam.com/blogs/jorge/rss.aspx
> ----
> * How to ask a question --> http://support.microsoft.com/?id=555375
> ----
> * This posting is provided "AS IS" with no warranties and confers no
> rights!
> * Always test before implementing!
> ----
> #################################################
> #################################################
> ----
> "Robert Nafty" <rob_nafty_spam@.hotmail.co.uk> wrote in message
> news:ODqqU8waIHA.6024@.TK2MSFTNGP06.phx.gbl...
>
|||Hi
If you need to change the domain then look at
http://support.microsoft.com/kb/298897
This may also be used:
http://support.microsoft.com/kb/246133/
You may have orphaned users in which case see
http://support.microsoft.com/kb/274188/
Also check out http://support.microsoft.com/kb/240872/
John
"Robert Nafty" wrote:

> Thanks.
> Do you know what scripting i would be looking at or what third party tools?
> I've spent a good while searching on this topic and am unable to find
> anything so would appreciated any advice.
>
> Thanks.
> Rob.
>
> "Jorge de Almeida Pinto [MVP - DS]"
> <SubstituteThisWithMyFullNameSeparatedByDots@.gmail .com> wrote in message
> news:%23ZQC3GxaIHA.1212@.TK2MSFTNGP05.phx.gbl...
>
>

Migrated NT users to AD - Permissions (Cross Posted)

(This message is cross posted to SQL and AD groups).
I'm in the process of migrating NT users to Active Directory. For file share
access this is fine because I migrate with SID history so everything just
works.
However I've done some test migrations for users with SQL permissions and
they can't access any databases. (As the permissions says their old name).
Short of me having to go through every single server for ever user is there
a way to update or a way for this to work'
Thanks
Rob.ADMT does not update SQL DBs. You might do it yourself (scripting) or buy
third party software
--
Cheers,
(HOPEFULLY THIS INFORMATION HELPS YOU!)
# Jorge de Almeida Pinto # MVP Windows Server - Directory Services
BLOG (WEB-BASED)--> http://blogs.dirteam.com/blogs/jorge/default.aspx
BLOG (RSS-FEEDS)--> http://blogs.dirteam.com/blogs/jorge/rss.aspx
----
* How to ask a question --> http://support.microsoft.com/?id=555375
----
* This posting is provided "AS IS" with no warranties and confers no rights!
* Always test before implementing!
----
#################################################
#################################################
----
"Robert Nafty" <rob_nafty_spam@.hotmail.co.uk> wrote in message
news:ODqqU8waIHA.6024@.TK2MSFTNGP06.phx.gbl...
> (This message is cross posted to SQL and AD groups).
> I'm in the process of migrating NT users to Active Directory. For file
> share access this is fine because I migrate with SID history so everything
> just works.
> However I've done some test migrations for users with SQL permissions and
> they can't access any databases. (As the permissions says their old name).
> Short of me having to go through every single server for ever user is
> there a way to update or a way for this to work'
> Thanks
> Rob.
>|||Thanks.
Do you know what scripting i would be looking at or what third party tools?
I've spent a good while searching on this topic and am unable to find
anything so would appreciated any advice.
Thanks.
Rob.
"Jorge de Almeida Pinto [MVP - DS]"
<SubstituteThisWithMyFullNameSeparatedByDots@.gmail.com> wrote in message
news:%23ZQC3GxaIHA.1212@.TK2MSFTNGP05.phx.gbl...
> ADMT does not update SQL DBs. You might do it yourself (scripting) or buy
> third party software
> --
> Cheers,
> (HOPEFULLY THIS INFORMATION HELPS YOU!)
> # Jorge de Almeida Pinto # MVP Windows Server - Directory Services
> BLOG (WEB-BASED)--> http://blogs.dirteam.com/blogs/jorge/default.aspx
> BLOG (RSS-FEEDS)--> http://blogs.dirteam.com/blogs/jorge/rss.aspx
> ----
> * How to ask a question --> http://support.microsoft.com/?id=555375
> ----
> * This posting is provided "AS IS" with no warranties and confers no
> rights!
> * Always test before implementing!
> ----
> #################################################
> #################################################
> ----
> "Robert Nafty" <rob_nafty_spam@.hotmail.co.uk> wrote in message
> news:ODqqU8waIHA.6024@.TK2MSFTNGP06.phx.gbl...
>> (This message is cross posted to SQL and AD groups).
>> I'm in the process of migrating NT users to Active Directory. For file
>> share access this is fine because I migrate with SID history so
>> everything just works.
>> However I've done some test migrations for users with SQL permissions and
>> they can't access any databases. (As the permissions says their old
>> name).
>> Short of me having to go through every single server for ever user is
>> there a way to update or a way for this to work'
>> Thanks
>> Rob.
>>
>|||maybe SQL scripts, vbs scripts
third party tooling, try Quest
--
Cheers,
(HOPEFULLY THIS INFORMATION HELPS YOU!)
# Jorge de Almeida Pinto # MVP Windows Server - Directory Services
BLOG (WEB-BASED)--> http://blogs.dirteam.com/blogs/jorge/default.aspx
BLOG (RSS-FEEDS)--> http://blogs.dirteam.com/blogs/jorge/rss.aspx
----
* How to ask a question --> http://support.microsoft.com/?id=555375
----
* This posting is provided "AS IS" with no warranties and confers no rights!
* Always test before implementing!
----
#################################################
#################################################
----
"Robert Nafty" <rob_nafty_spam@.hotmail.co.uk> wrote in message
news:u6y7cDyaIHA.356@.TK2MSFTNGP03.phx.gbl...
> Thanks.
> Do you know what scripting i would be looking at or what third party
> tools?
> I've spent a good while searching on this topic and am unable to find
> anything so would appreciated any advice.
>
> Thanks.
> Rob.
>
> "Jorge de Almeida Pinto [MVP - DS]"
> <SubstituteThisWithMyFullNameSeparatedByDots@.gmail.com> wrote in message
> news:%23ZQC3GxaIHA.1212@.TK2MSFTNGP05.phx.gbl...
>> ADMT does not update SQL DBs. You might do it yourself (scripting) or buy
>> third party software
>> --
>> Cheers,
>> (HOPEFULLY THIS INFORMATION HELPS YOU!)
>> # Jorge de Almeida Pinto # MVP Windows Server - Directory Services
>> BLOG (WEB-BASED)--> http://blogs.dirteam.com/blogs/jorge/default.aspx
>> BLOG (RSS-FEEDS)--> http://blogs.dirteam.com/blogs/jorge/rss.aspx
>> ----
>> * How to ask a question --> http://support.microsoft.com/?id=555375
>> ----
>> * This posting is provided "AS IS" with no warranties and confers no
>> rights!
>> * Always test before implementing!
>> ----
>> #################################################
>> #################################################
>> ----
>> "Robert Nafty" <rob_nafty_spam@.hotmail.co.uk> wrote in message
>> news:ODqqU8waIHA.6024@.TK2MSFTNGP06.phx.gbl...
>> (This message is cross posted to SQL and AD groups).
>> I'm in the process of migrating NT users to Active Directory. For file
>> share access this is fine because I migrate with SID history so
>> everything just works.
>> However I've done some test migrations for users with SQL permissions
>> and they can't access any databases. (As the permissions says their old
>> name).
>> Short of me having to go through every single server for ever user is
>> there a way to update or a way for this to work'
>> Thanks
>> Rob.
>>
>|||Hi
If you need to change the domain then look at
http://support.microsoft.com/kb/298897
This may also be used:
http://support.microsoft.com/kb/246133/
You may have orphaned users in which case see
http://support.microsoft.com/kb/274188/
Also check out http://support.microsoft.com/kb/240872/
John
"Robert Nafty" wrote:
> Thanks.
> Do you know what scripting i would be looking at or what third party tools?
> I've spent a good while searching on this topic and am unable to find
> anything so would appreciated any advice.
>
> Thanks.
> Rob.
>
> "Jorge de Almeida Pinto [MVP - DS]"
> <SubstituteThisWithMyFullNameSeparatedByDots@.gmail.com> wrote in message
> news:%23ZQC3GxaIHA.1212@.TK2MSFTNGP05.phx.gbl...
> > ADMT does not update SQL DBs. You might do it yourself (scripting) or buy
> > third party software
> >
> > --
> >
> > Cheers,
> > (HOPEFULLY THIS INFORMATION HELPS YOU!)
> >
> > # Jorge de Almeida Pinto # MVP Windows Server - Directory Services
> >
> > BLOG (WEB-BASED)--> http://blogs.dirteam.com/blogs/jorge/default.aspx
> > BLOG (RSS-FEEDS)--> http://blogs.dirteam.com/blogs/jorge/rss.aspx
> > ----
> > * How to ask a question --> http://support.microsoft.com/?id=555375
> > ----
> > * This posting is provided "AS IS" with no warranties and confers no
> > rights!
> > * Always test before implementing!
> > ----
> > #################################################
> > #################################################
> > ----
> > "Robert Nafty" <rob_nafty_spam@.hotmail.co.uk> wrote in message
> > news:ODqqU8waIHA.6024@.TK2MSFTNGP06.phx.gbl...
> >> (This message is cross posted to SQL and AD groups).
> >>
> >> I'm in the process of migrating NT users to Active Directory. For file
> >> share access this is fine because I migrate with SID history so
> >> everything just works.
> >>
> >> However I've done some test migrations for users with SQL permissions and
> >> they can't access any databases. (As the permissions says their old
> >> name).
> >>
> >> Short of me having to go through every single server for ever user is
> >> there a way to update or a way for this to work'
> >>
> >> Thanks
> >> Rob.
> >>
> >>
> >
>
>

migrate mdf file to sql server 2005

hello

i am working on a mdf file on sql server express 2005 and i would like to migrate the content to sql server 2005.

How can i do it ?the reason why i want to do this is because i want to work on the same database from different project|||I havent used Express but you could try copying the mdf file into the data folder of SQL Server and "attaching" the database.|||Yup, it's that simple. Copy, and attach.

Migrate Login Control to SQL

Hi,

I was able to create an MSSQL database with the ASPNETDB.MDF file. But how do I get the Login Control to point to the MSSQL rather than the \App_Data\ASPNETDB.MDF file?

Hi

you need to see This

Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005

|||

check out below solved thread..

Login control And DB - ASP.NET Forums

you might need to include the tables and stored procedure which requires for login control into your DB...

hope it helps./.

Migrate Backup DAT file from MS SQL to MSDE 2000

Hi,
Anyone know how to migrate migrate backup file DAT from MS
SQL 7.0 to MSDE 2000?
Thanks
Michael
If this is a backup file created with SQL Servers BACKUP command then you
can simply restore the database to the MSDE 2000 instance. The RESTORE
command will convert the database for you.
Jim
"Michael" <anonymous@.discussions.microsoft.com> wrote in message
news:0a9401c503f4$51839a90$a401280a@.phx.gbl...
> Hi,
> Anyone know how to migrate migrate backup file DAT from MS
> SQL 7.0 to MSDE 2000?
> Thanks
> Michael

Migrate / Read Access data file(s) in SQL Server

Dear Experts,
I have some Access .mde files from a custom application.
The runtime version of Access is 2002. I don't have
a developer's version of Access. I also don't have the
schema design of the app.
I'd like to import or migrate the data file into SQL Server
schemas (databases) somehow. Or, another database
like Oracle.
Is it possible to do this from SQL Server?
What is the buzzword that I can search on?
Is there a link you can forward?
Thanks a lot!
Migrating Access databases to SQL Server can be done different ways from
both sides. Also, depending on the version of SQL Server that you target you
can use different tools too.
Here are a few links that will give you a good start:
SQL Server Migration Assistant for Access (for migrating databases from
Microsoft Access versions 97 through 2003 to Microsoft SQL Server 2005):
http://www.microsoft.com/sql/solutions/migration/access/default.mspx
Moving Your Access 2002 Database to SQL Server 2000 (look at the Data
Transformation Services section for importing Access data to SQL Server):
http://msdn2.microsoft.com/en-us/library/aa902657(SQL.80).aspx
How to use the Upsizing wizard in Access 2002:
http://support.microsoft.com/default.aspx/kb/330468
Access 2002 Upsizing Tools white paper:
http://support.microsoft.com/default.aspx/kb/294407
In the past I have used many times the Data Transformation Services in SQL
Server 2000 to import data from Access databases and it has been working
very well. Recently I used the SQL Server Import and Export Wizard in SQL
Server 2005 to import a few tables from .mde files and no problems there
too.
Regards,
Plamen Ratchev
http://www.SQLStudio.com
|||I tried the simplest method I could. In SQL 2000, using enterprise manager,
I imported the mde. In source, I chose the Access type. I then chose the
file (after changing the file filter from *.mdb to all files). After
accepting the SQL Server data source, I checked all of the tables in the mde.
The import wizard created my tables and imported the data.
Russel Loski, MCSD.Net
"dba_222@.yahoo.com" wrote:

> Dear Experts,
> I have some Access .mde files from a custom application.
> The runtime version of Access is 2002. I don't have
> a developer's version of Access. I also don't have the
> schema design of the app.
> I'd like to import or migrate the data file into SQL Server
> schemas (databases) somehow. Or, another database
> like Oracle.
> Is it possible to do this from SQL Server?
> What is the buzzword that I can search on?
> Is there a link you can forward?
> Thanks a lot!
>
|||<dba_222@.yahoo.com> wrote in message
news:1169516295.667810.162270@.51g2000cwl.googlegro ups.com...
> Dear Experts,
> I have some Access .mde files from a custom application.
> The runtime version of Access is 2002. I don't have
> a developer's version of Access. I also don't have the
> schema design of the app.
> I'd like to import or migrate the data file into SQL Server
> schemas (databases) somehow. Or, another database
> like Oracle.
> Is it possible to do this from SQL Server?
> What is the buzzword that I can search on?
> Is there a link you can forward?
> Thanks a lot!
>
|||you should use DTS or something similiar in order to import all of your
Access Databases into SQL Server
"la playa mas bella" <thepadrino_1@.hotmail.com> wrote in message
news:eSt2kaXgHHA.588@.TK2MSFTNGP06.phx.gbl...
> <dba_222@.yahoo.com> wrote in message
> news:1169516295.667810.162270@.51g2000cwl.googlegro ups.com...
>

Migrate / Read Access data file(s) in SQL Server

Dear Experts,
I have some Access .mde files from a custom application.
The runtime version of Access is 2002. I don't have
a developer's version of Access. I also don't have the
schema design of the app.
I'd like to import or migrate the data file into SQL Server
schemas (databases) somehow. Or, another database
like Oracle.
Is it possible to do this from SQL Server?
What is the buzzword that I can search on?
Is there a link you can forward?
Thanks a lot!Migrating Access databases to SQL Server can be done different ways from
both sides. Also, depending on the version of SQL Server that you target you
can use different tools too.
Here are a few links that will give you a good start:
SQL Server Migration Assistant for Access (for migrating databases from
Microsoft Access versions 97 through 2003 to Microsoft SQL Server 2005):
http://www.microsoft.com/sql/soluti...ss/default.mspx
Moving Your Access 2002 Database to SQL Server 2000 (look at the Data
Transformation Services section for importing Access data to SQL Server):
http://msdn2.microsoft.com/en-us/library/aa902657(SQL.80).aspx
How to use the Upsizing wizard in Access 2002:
http://support.microsoft.com/default.aspx/kb/330468
Access 2002 Upsizing Tools white paper:
http://support.microsoft.com/default.aspx/kb/294407
In the past I have used many times the Data Transformation Services in SQL
Server 2000 to import data from Access databases and it has been working
very well. Recently I used the SQL Server Import and Export Wizard in SQL
Server 2005 to import a few tables from .mde files and no problems there
too.
Regards,
Plamen Ratchev
http://www.SQLStudio.com|||I tried the simplest method I could. In SQL 2000, using enterprise manager,
I imported the mde. In source, I chose the Access type. I then chose the
file (after changing the file filter from *.mdb to all files). After
accepting the SQL Server data source, I checked all of the tables in the mde
.
The import wizard created my tables and imported the data.
--
Russel Loski, MCSD.Net
"dba_222@.yahoo.com" wrote:

> Dear Experts,
> I have some Access .mde files from a custom application.
> The runtime version of Access is 2002. I don't have
> a developer's version of Access. I also don't have the
> schema design of the app.
> I'd like to import or migrate the data file into SQL Server
> schemas (databases) somehow. Or, another database
> like Oracle.
> Is it possible to do this from SQL Server?
> What is the buzzword that I can search on?
> Is there a link you can forward?
> Thanks a lot!
>|||<dba_222@.yahoo.com> wrote in message
news:1169516295.667810.162270@.51g2000cwl.googlegroups.com...
> Dear Experts,
> I have some Access .mde files from a custom application.
> The runtime version of Access is 2002. I don't have
> a developer's version of Access. I also don't have the
> schema design of the app.
> I'd like to import or migrate the data file into SQL Server
> schemas (databases) somehow. Or, another database
> like Oracle.
> Is it possible to do this from SQL Server?
> What is the buzzword that I can search on?
> Is there a link you can forward?
> Thanks a lot!
>|||you should use DTS or something similiar in order to import all of your
Access Databases into SQL Server
"la playa mas bella" <thepadrino_1@.hotmail.com> wrote in message
news:eSt2kaXgHHA.588@.TK2MSFTNGP06.phx.gbl...
> <dba_222@.yahoo.com> wrote in message
> news:1169516295.667810.162270@.51g2000cwl.googlegroups.com...
>

Migrate / Read Access data file(s) in SQL Server

Dear Experts,
I have some Access .mde files from a custom application.
The runtime version of Access is 2002. I don't have
a developer's version of Access. I also don't have the
schema design of the app.
I'd like to import or migrate the data file into SQL Server
schemas (databases) somehow. Or, another database
like Oracle.
Is it possible to do this from SQL Server?
What is the buzzword that I can search on?
Is there a link you can forward?
Thanks a lot!Migrating Access databases to SQL Server can be done different ways from
both sides. Also, depending on the version of SQL Server that you target you
can use different tools too.
Here are a few links that will give you a good start:
SQL Server Migration Assistant for Access (for migrating databases from
Microsoft Access versions 97 through 2003 to Microsoft SQL Server 2005):
http://www.microsoft.com/sql/solutions/migration/access/default.mspx
Moving Your Access 2002 Database to SQL Server 2000 (look at the Data
Transformation Services section for importing Access data to SQL Server):
http://msdn2.microsoft.com/en-us/library/aa902657(SQL.80).aspx
How to use the Upsizing wizard in Access 2002:
http://support.microsoft.com/default.aspx/kb/330468
Access 2002 Upsizing Tools white paper:
http://support.microsoft.com/default.aspx/kb/294407
In the past I have used many times the Data Transformation Services in SQL
Server 2000 to import data from Access databases and it has been working
very well. Recently I used the SQL Server Import and Export Wizard in SQL
Server 2005 to import a few tables from .mde files and no problems there
too.
Regards,
Plamen Ratchev
http://www.SQLStudio.com|||<dba_222@.yahoo.com> wrote in message
news:1169516295.667810.162270@.51g2000cwl.googlegroups.com...
> Dear Experts,
> I have some Access .mde files from a custom application.
> The runtime version of Access is 2002. I don't have
> a developer's version of Access. I also don't have the
> schema design of the app.
> I'd like to import or migrate the data file into SQL Server
> schemas (databases) somehow. Or, another database
> like Oracle.
> Is it possible to do this from SQL Server?
> What is the buzzword that I can search on?
> Is there a link you can forward?
> Thanks a lot!
>|||you should use DTS or something similiar in order to import all of your
Access Databases into SQL Server
"la playa mas bella" <thepadrino_1@.hotmail.com> wrote in message
news:eSt2kaXgHHA.588@.TK2MSFTNGP06.phx.gbl...
> <dba_222@.yahoo.com> wrote in message
> news:1169516295.667810.162270@.51g2000cwl.googlegroups.com...
> > Dear Experts,
> >
> > I have some Access .mde files from a custom application.
> >
> > The runtime version of Access is 2002. I don't have
> > a developer's version of Access. I also don't have the
> > schema design of the app.
> >
> > I'd like to import or migrate the data file into SQL Server
> > schemas (databases) somehow. Or, another database
> > like Oracle.
> >
> > Is it possible to do this from SQL Server?
> > What is the buzzword that I can search on?
> > Is there a link you can forward?
> >
> > Thanks a lot!
> >
>

Mid Data Point

I have a database table with 464473 records in it. The file is an opt-in
list. Each record has a unique id number which are not sequential. I need
to split the file in half or find the id number of the mid-point in the
file. At some point I might need to split the file 3 or 5 ways.
HELP. How do i find the mid-point...
I thought that "select top 232237 order by id desc" would show me the id
number. It does not. Any other techniques.William,
If the ID is not sequential and there are possibly gaps in the sequencing,
finding the mid-way point is virtually useless if you are truly trying to
access 50% of the 464,473 row table.
Consider using SET ROWCOUNT:
set rowcount 232236
insert into new_table select * from current_table
delete from current_table
set rowcount 0
"William" wrote:

> I have a database table with 464473 records in it. The file is an opt-in
> list. Each record has a unique id number which are not sequential. I nee
d
> to split the file in half or find the id number of the mid-point in the
> file. At some point I might need to split the file 3 or 5 ways.
> HELP. How do i find the mid-point...
> I thought that "select top 232237 order by id desc" would show me the id
> number. It does not. Any other techniques.
>
>|||I think some clarfications would be useful.
First of all a table isn't a file.
Secondly, what do you mean by "split the file [table?] in half"? That seems
like something quite different from "find the id number of the mid-point".
It would help if you would state your actual goal rather than hinting at
different ideas. If we know what you are trying to achieve we may be able to
suggest better alternatives.
To find the mid-point ID:
SELECT MIN(id) AS mid_point
FROM
(SELECT TOP 232237 id
FROM YourTable
ORDER BY id) AS T
However, I suspect you may be looking for something slightly different, such
as paging or sampling, to which there are likely to be different and maybe
better solutions.
David Portas
SQL Server MVP
--

Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer column ordinal from name?

Hi,

I need to access columns from a data flow by ordinal position in a script transformation (I'm parsing an excel file which has several rowsets across the page). The first problem I encountered is the generated BufferWrapper does not expose the columns collection (i.e. Input0Buffer(0) does not work) but I got around that by implementing my own ProcessInputs(InputId, Buffer) method instead of using the wrapper.

My problem now is that the column ordinals are in some random order (i.e. Column "F1" is ordinal 1 but Column "F2" is 243). Where in the object model can I map between the name and the ordinal - it's not jumping out at me?

Dave

PS Why is the script editor modal, it's frustrating having to switch between the Visual Studio environment and the VSA one.

To read and write buffer columns by ordinal position in a script transform, load a name => index dictionary in the PreExecute function. The dictionary key would be the column name, and the dictionary value a structure containing column metadata, including the buffer column index you're referring to, and whatever else you'd like.

In PreExecute() function, load up the dictionary. It will then be available for use when you require direct column access by ordinal position. Here's an example which uses the GetColumnIndexes in lieu of BufferManager.FindColumnByLineageID().

Imports System

Imports System.Data

Imports System.Math

Imports System.Collections.Generic

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Public Class ScriptMain

Inherits UserComponent

Private inputBuffer As PipelineBuffer

Private cols As Dictionary(Of String, ColumnInfo) = New Dictionary(Of String, ColumnInfo)

Private currentColumnInfo As ColumnInfo = New ColumnInfo

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If cols.TryGetValue("GeneratedStr_1", currentColumnInfo) Then

' retrieve column metatdata by column name

inputBuffer.SetString(currentColumnInfo.colIndex, Guid.NewGuid().ToString())

End If

End Sub

Public Overrides Sub ProcessInput(ByVal InputID As Integer, ByVal Buffer As Microsoft.SqlServer.Dts.Pipeline.PipelineBuffer)

' Get the Pipeline Buffer for subsequent ordinal column access

inputBuffer = Buffer

MyBase.ProcessInput(InputID, Buffer)

End Sub

Public Overrides Sub PreExecute()

BuildColumnDictionary()

End Sub

Private Sub BuildColumnDictionary()

Dim indexes() As Integer

Dim input As IDTSInput90

Dim col As IDTSInputColumn90

Dim offset As Integer = 0

input = Me.ComponentMetaData.InputCollection(0)

'presumes GetColumnIndexes order matches iterator order

'as BufferManager is not available to my knowledge in ScriptComponent

indexes = Me.GetColumnIndexes(input.ID)

For Each col In input.InputColumnCollection

Dim columnStructure As New ColumnInfo

With columnStructure

.colName = col.Name

.colLength = col.Length

.colIndex = indexes(offset)

'Normally, BufferManager would be used, but its not exposed in Script Component

.colPrecision = col.Precision

.colScale = col.Scale

.colType = col.DataType

End With

Me.Log(String.Format("Name {0} Buffer Index {1} offset {2} ", col.Name, indexes(offset), offset), 0, Nothing)

cols.Add(col.Name, columnStructure)

offset += 1

Next

End Sub

Public Structure ColumnInfo

Dim colName As String

Dim colType As DataType

Dim colIndex As Int32

Dim colLength As Int32

Dim colPrecision As Int32

Dim colScale As Int32

End Structure

End Class|||

Hi Jaegd

Thanks for the code, capturing the PipelineBuffer in ProcessInputs for later use in _ProcessInputRow is a good idea. I'd also not noticed that PipelineBuffer has Get{Type} \ Set{Type} methods.

Your assumption that the 'GetColumnIndexes order matches iterator order' does not appear to hold though (at least for the Excel Data Source) but I found an alternative that appears to be ok

With columnStructure
.colName = col.Name
.colLength = col.Length
.colIndex = input.InputColumnCollection.GetObjectIndexByID(col.ID)

.colPrecision = col.Precision
.colScale = col.Scale
.colType = col.DataType
End With

Dave

Microsoft.Data.SqlXml not found

I keep getting the error that: File or assembly name Microsoft.Data.SqlXml, or one of its dependencies, was not found from a c# application.
I have VS.net 2003 and the .Net framework 1.1. Are there any other dependencies.
Thanks.
You need to install SQLXML 3.0
(http://www.microsoft.com/downloads/d...33a9-cf10-4e22
-8004-477098a407ac&DisplayLang=en)
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master Ltd.
"Asim" <anonymous@.discussions.microsoft.com> wrote in message
news:29F448E9-8387-4D04-8E37-87E43CA66619@.microsoft.com...
> I keep getting the error that: File or assembly name
Microsoft.Data.SqlXml, or one of its dependencies, was not found from a c#
application.
> I have VS.net 2003 and the .Net framework 1.1. Are there any other
dependencies.
> Thanks.

Microsoft SQL-DMO

I got an error as following while I am trying to restore a database:
Microsoft SQL-DMO (ODBC SQLState:42000)
Device activation error: The physical file name ... may be incorrect.
Please help how to solve this problem. Thanks.
Allen,
Check your spelling of the file name you are trying to restore from.
They do not match.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
"Allen Iverson" wrote:
> I got an error as following while I am trying to restore a database:
> Microsoft SQL-DMO (ODBC SQLState:42000)
> Device activation error: The physical file name ... may be incorrect.
>
> Please help how to solve this problem. Thanks.
>
|||Mark,
But I use the browser to open the file. So the spelling should
be OK.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:%23VqEGHKpEHA.1308@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> Allen,
> Check your spelling of the file name you are trying to restore from. They
> do not match.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
>
> "Allen Iverson" wrote:
|||Note that the restore is performed on the server. Are you sitting on the server when you use
explorer to get the file name?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:ejiY39OpEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Mark,
> But I use the browser to open the file. So the spelling should
> be OK.
> "Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
> news:%23VqEGHKpEHA.1308@.TK2MSFTNGP14.phx.gbl...
>
|||No, I am working on a client PC. But I did this before and no problem.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:erRDI7SpEHA.4004@.TK2MSFTNGP10.phx.gbl...
> Note that the restore is performed on the server. Are you sitting on the
> server when you use
> explorer to get the file name?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> ""Allen Iverson"" <no_spam@.bk.com> wrote in message
> news:ejiY39OpEHA.3324@.TK2MSFTNGP15.phx.gbl...
>
|||The filename specified is seen from SQL Server's perspective. If the file isn't on the SQL Server
machine you have to specify an UNC path for the file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
""Allen Iverson"" <no_spam@.bk.com> wrote in message news:OceVUrbqEHA.2764@.TK2MSFTNGP11.phx.gbl...
> No, I am working on a client PC. But I did this before and no problem.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:erRDI7SpEHA.4004@.TK2MSFTNGP10.phx.gbl...
>

Microsoft SQL Server Migration Assistant for Access Fails to Migrate Data

I attempted to use SSMA to migrate a 2000 file format .mdb into SQL Server Express (and, as I've just purchased VS2005 Pro, into SQL Server Developer Edition).

The file is actually a backend, so nothing to migrate other than tables.

After several runs during which I received (and fixed up) some errors, the process runs smoothly with no errors or warnings, until it comes to the last step; migrating the data itself.

Data migration into 'parent' tables works fine. However, wherever I have data in a table with a foreign key relationship to any of the aforementioned 'parent' tables, it refuses to migrate it. The text of the error message will be very smiliar to the following (from the log):

[Datamigrator: Error] [464/7] [2007-09-23 14:18:54]: Exception: The INSERT statement conflicted with the FOREIGN KEY constraint "Branch$CompanyBranch". The conflict occurred in database "DPMTest", table "dbo.Company", column 'CompanyID'.

There is nothing wrong, so far as I can determine, with the relationships involved.

I can insert data into the tables using any of the following methods:

1. Directly in Access, in the source backend.

2. Using the original Access frontend application, attached to the source backend.

3. Using VB.Net forms I am developing, in the 'upsized' database.

4. Directly in the Management Studio, in the 'upsized' database.

None of these four methods complains in the least about the relationships which SSMA balks at.

I would be grateful for any ideas....thanks.

The error is self explanatory...

You are inserting companyID that are not there in the master table. Just run a select some thing like this

Select *from ThisTableName where CompanyID not In (select companyID from ParentComapnyTableName)

You will get few rows. this needs to be deleted otherwise you have to correct the data.

Madhu

|||

As I said, I can insert data via all other methods. I am not inserting CompanyID where it does not exist in the master table. There is only one CompanyID at this point, whose ID is '1'. If I add a Branch with CompanyID of '1' in the Access source database which, again, doesn't complain about the relationship - because there is no problem with the relationship, or the data I'm inserting - the Migration Assistant throws the abovementioned error.

I appreciate your response, but I feel you didn't comprehend my original post. Thanks, anyway.

|||I have "successfully" migrated MDB files only to find out that SQL and Visual Studio balks at them. Problems with constraints, views, and other stuff.

Microsoft SQL Server Migration Assistant for Access Fails to Migrate Data

I attempted to use SSMA to migrate a 2000 file format .mdb into SQL Server Express (and, as I've just purchased VS2005 Pro, into SQL Server Developer Edition).

The file is actually a backend, so nothing to migrate other than tables.

After several runs during which I received (and fixed up) some errors, the process runs smoothly with no errors or warnings, until it comes to the last step; migrating the data itself.

Data migration into 'parent' tables works fine. However, wherever I have data in a table with a foreign key relationship to any of the aforementioned 'parent' tables, it refuses to migrate it. The text of the error message will be very smiliar to the following (from the log):

[Datamigrator: Error] [464/7] [2007-09-23 14:18:54]: Exception: The INSERT statement conflicted with the FOREIGN KEY constraint "Branch$CompanyBranch". The conflict occurred in database "DPMTest", table "dbo.Company", column 'CompanyID'.

There is nothing wrong, so far as I can determine, with the relationships involved.

I can insert data into the tables using any of the following methods:

1. Directly in Access, in the source backend.

2. Using the original Access frontend application, attached to the source backend.

3. Using VB.Net forms I am developing, in the 'upsized' database.

4. Directly in the Management Studio, in the 'upsized' database.

None of these four methods complains in the least about the relationships which SSMA balks at.

I would be grateful for any ideas....thanks.

The error is self explanatory...

You are inserting companyID that are not there in the master table. Just run a select some thing like this

Select *from ThisTableName where CompanyID not In (select companyID from ParentComapnyTableName)

You will get few rows. this needs to be deleted otherwise you have to correct the data.

Madhu

|||

As I said, I can insert data via all other methods. I am not inserting CompanyID where it does not exist in the master table. There is only one CompanyID at this point, whose ID is '1'. If I add a Branch with CompanyID of '1' in the Access source database which, again, doesn't complain about the relationship - because there is no problem with the relationship, or the data I'm inserting - the Migration Assistant throws the abovementioned error.

I appreciate your response, but I feel you didn't comprehend my original post. Thanks, anyway.

|||I have "successfully" migrated MDB files only to find out that SQL and Visual Studio balks at them. Problems with constraints, views, and other stuff.