Migrating from Access to SQL Server

I recently migrated some user tables from Access to SQL Server while leaving
the forms, reports, and queries in Access with Linked tables to the SQL
instance. Some of the users queries/forms are not working correctly? It
seems that Access Queries return a single empty row when no matches are foun
d
while SQL Server returns an empty result set. In Access, the users use a
query to search for a customer number, if no matching number is found the
customer form is displayed with one record containing no values and the user
s
can then input new customer information. When the query/form is pointed to
SQL Server, when no matching customer is found a blank page is displayed. I
s
there a way to force SQL Server to return an empty row if none are found?Hi,
probably the attached script would help to you (I'm not sure how to
implement it within Access enviroment):
create table t (col1 int null, col2 varchar(10) null)
--without variables
select col1,col2 from t
union all
select null, null
--with variables
declare @.r as int
select * from t
set @.r=@.@.rowcount
if @.r>0
return
else
select null as col1, null as col2
drop table t
Denis
"DT" wrote:

> I recently migrated some user tables from Access to SQL Server while leavi
ng
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are fo
und
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the us
ers
> can then input new customer information. When the query/form is pointed t
o
> SQL Server, when no matching customer is found a blank page is displayed.
Is
> there a way to force SQL Server to return an empty row if none are found?
>|||Although your solution does cause the form to be displayed (progress!) I can
not enter data on the form? Am I missing something?
"Denis" wrote:
> Hi,
> probably the attached script would help to you (I'm not sure how to
> implement it within Access enviroment):
> create table t (col1 int null, col2 varchar(10) null)
> --without variables
> select col1,col2 from t
> union all
> select null, null
> --with variables
> declare @.r as int
> select * from t
> set @.r=@.@.rowcount
> if @.r>0
> return
> else
> select null as col1, null as col2
>
> drop table t
> Denis
> "DT" wrote:
>|||There is no such thing as an "empty" row in SQL Server. The functionality
you are wanting would probably be a property of the recordset or data grid.
"DT" <DT@.discussions.microsoft.com> wrote in message
news:0D425E66-49D7-4C46-97E2-7813DB70A6D0@.microsoft.com...
>I recently migrated some user tables from Access to SQL Server while
>leaving
> the forms, reports, and queries in Access with Linked tables to the SQL
> instance. Some of the users queries/forms are not working correctly? It
> seems that Access Queries return a single empty row when no matches are
> found
> while SQL Server returns an empty result set. In Access, the users use a
> query to search for a customer number, if no matching number is found the
> customer form is displayed with one record containing no values and the
> users
> can then input new customer information. When the query/form is pointed
> to
> SQL Server, when no matching customer is found a blank page is displayed.
> Is
> there a way to force SQL Server to return an empty row if none are found?
>

Migrating database

Can anyone tell me how I can migrate database from one server to another
I need to migrate all the tables, stored procedures, functions and so on

thank youYou can use the Copy Database Wizard to achieve this. See BOL for more info.

As you are moving the database to a new server, you will have to manually delete the data and log files on the old server once the copy process has completed.|||Can anyone tell me how I can migrate database from one server to another
I need to migrate all the tables, stored procedures, functions and so on

thank you

As noted earlier, you can use the Copy Database Objects wizard. Or:

1. You can load a backup of your database onto the new database (you will have to manually add in SQL Logins and resynch those logins with the users in the new database).

2. You can detach the old database, copy over the .mdf and .ldf files and then attach it to a new server (again, you will have to manually handle SQL Logins and synch them to the new db).

Regards,

hmscott|||I dont want to detach the old database. That needs to stay the way it is. Can I just copy over all the tables, sp, views and fns along with the logins. Would it work that way. I know its going to be a lot of work but it seems like the simplest way to go without making changes to the old database

Migrating Data from MySQL

Hello

I am looking for a way to migrate my data from a MySQL database to a SQL2005 Express data base. All of my MySQL Schemas only have 1 or 2 tables in them so I would imagine that it isn't that hard to do. I treid just exporting it to a CSV file with the intention of just using import/export in MSSQL but in the express edition, it ain't there. Like I stated all of my schemas are only 1 or two tables none are linked in any way they are independant table and the most records any one of the tables contain is around 250. Any suggestions?

hi,

as you already found, SQLExpress does not provide import/export tools, but you can rely on the underlying fundametals of the engine it self to perform such a kind of operation..

you can, for instance, perform a bulk insert as indicated in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=723808&SiteID=1, or directly using BCP

additional "modes" are indicated in http://blogs.msdn.com/euanga/archive/2006/07/20/672272.aspx

regards

|||I don't mean to sound like a total dummy but I sort of understand what is being said and there is some really good round about information but, it just isn't getting me there. I have a table right now for example that I exported in CSV format from MySQL that has 6 columns and 203 rows. If I create this same table in SQL2005 Express, how do I bring that data into my SQL 2005 database. Admittedly my experience level with SQL and Scripting is not quite beginner but I wouldn't consider myself intermediate either. Could you dumb it down for me a little?|||Hi,

I was in the process of trying to load data into a SQL Express DB with bcp see
Unable to open BCP host data-file
when I ran across this post. I read the above linked articles. The DTSwizard seems to only want to add a new table where bcp will load data into an existing table using a format file.
I have used bcp to load data in SQL Server 2000 but am having trouble with SQL Server 2005 Express. We are planning to purchase the standard version of SQL Server 2005, but I was wanting to get some feel for 2005 it until the $$$ actually come through. Isn't that what Express version is for?

Any help?|||hi

a way to migrate is you import into Access and afzer them you build in SQL-Express the Table structure and import via ODBC the dates into the new table.

I have this do somtimes from MySQL to acces and then to SQLExpress 2005 and vice versa

sincerly
Helmi|||hi

a way to migrate is you import into Access and afzer them you build in

SQL-Express the Table structure and import via ODBC the dates into the

new table.

I have this do somtimes from MySQL to acces and then to SQLExpress 2005 and vice versa

sincerly

Helmi|||This sounds like it would be a good option. I was exploring the possibility of doing that, I even went so far as to import my tables in to Access however, I don't see a way to bring the data over into SQL express. Someone had mentioned using the DTS wizard but unless I am missing something, it isn't included in SQL 2005 express.|||The links above reference a utility
"C:\Program Files\Microsoft SQL Server\90\DTS\Binn\DTSWizard.exe"

It doesn't work for me because it seems to create the table definitions from the data file and I already have that part done.|||Nevermind I figure out how the Access option works. That was the ticket. Thank you.

migrating 2000 partitionned view to 2005 partitionned table?

Hi,
I have an SQL 2000 datawarehouse where my 2 biggest tables are partitionned
by year. (Fact_Table_2003, Fact_Table_2004,...)
I want to test to migrate to 2005 and I want to use the new partitionned
table options.
how to do this easely?
thanks.
Jerome.
I have not had to do this yet, so this is all theory from my
perspective.
But from what I have seen, you should be able to backup/restore your
database "as is" and then I from what I have seen, you just need to do
an ALTER TABLE...SWITCH to move the table data around. BOL has a topic
on "moving partitioned data" which lists one of the uses of SWITCH as
"Assigning a table as a partition to an already existing partitioned
table."
Assigning partitions to different tables is just a meta data operation
so it happens very quickly, no data is copied, pointers to the data are
just updated.
In a nutshell I think the process would go something like this:
1. migrate the database onto SQL2k5
2. join all the tables together using "ALTER TABLE..SWITCH"
3. drop the view
4. rename the table from step 2. with the same name as the view
5. re-create any Referential Integrity constraints
I would suggest doing a test on small sample database first (a copy of
one of the sample databases maybe)
HTH
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <ePn#4Tk7FHA.3876@.TK2MSFTNGP09.phx.gbl>,
willgart@.BBBhotmailAAA.com says...
> Hi,
> I have an SQL 2000 datawarehouse where my 2 biggest tables are partitionned
> by year. (Fact_Table_2003, Fact_Table_2004,...)
> I want to test to migrate to 2005 and I want to use the new partitionned
> table options.
> how to do this easely?
> thanks.
> Jerome.
>
>

migrating 2000 partitionned view to 2005 partitionned table?

Hi,
I have an SQL 2000 datawarehouse where my 2 biggest tables are partitionned
by year. (Fact_Table_2003, Fact_Table_2004,...)
I want to test to migrate to 2005 and I want to use the new partitionned
table options.
how to do this easely?
thanks.
Jerome.I have not had to do this yet, so this is all theory from my
perspective.
But from what I have seen, you should be able to backup/restore your
database "as is" and then I from what I have seen, you just need to do
an ALTER TABLE...SWITCH to move the table data around. BOL has a topic
on "moving partitioned data" which lists one of the uses of SWITCH as
"Assigning a table as a partition to an already existing partitioned
table."
Assigning partitions to different tables is just a meta data operation
so it happens very quickly, no data is copied, pointers to the data are
just updated.
In a nutshell I think the process would go something like this:
1. migrate the database onto SQL2k5
2. join all the tables together using "ALTER TABLE..SWITCH"
3. drop the view
4. rename the table from step 2. with the same name as the view
5. re-create any Referential Integrity constraints
I would suggest doing a test on small sample database first (a copy of
one of the sample databases maybe)
HTH
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <ePn#4Tk7FHA.3876@.TK2MSFTNGP09.phx.gbl>,
willgart@.BBBhotmailAAA.com says...
> Hi,
> I have an SQL 2000 datawarehouse where my 2 biggest tables are partitionned
> by year. (Fact_Table_2003, Fact_Table_2004,...)
> I want to test to migrate to 2005 and I want to use the new partitionned
> table options.
> how to do this easely?
> thanks.
> Jerome.
>
>

migrating 2000 partitionned view to 2005 partitionned table?

Hi,
I have an SQL 2000 datawarehouse where my 2 biggest tables are partitionned
by year. (Fact_Table_2003, Fact_Table_2004,...)
I want to test to migrate to 2005 and I want to use the new partitionned
table options.
how to do this easely?
thanks.
Jerome.
I have not had to do this yet, so this is all theory from my
perspective.
But from what I have seen, you should be able to backup/restore your
database "as is" and then I from what I have seen, you just need to do
an ALTER TABLE...SWITCH to move the table data around. BOL has a topic
on "moving partitioned data" which lists one of the uses of SWITCH as
"Assigning a table as a partition to an already existing partitioned
table."
Assigning partitions to different tables is just a meta data operation
so it happens very quickly, no data is copied, pointers to the data are
just updated.
In a nutshell I think the process would go something like this:
1. migrate the database onto SQL2k5
2. join all the tables together using "ALTER TABLE..SWITCH"
3. drop the view
4. rename the table from step 2. with the same name as the view
5. re-create any Referential Integrity constraints
I would suggest doing a test on small sample database first (a copy of
one of the sample databases maybe)
HTH
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <ePn#4Tk7FHA.3876@.TK2MSFTNGP09.phx.gbl>,
willgart@.BBBhotmailAAA.com says...
> Hi,
> I have an SQL 2000 datawarehouse where my 2 biggest tables are partitionned
> by year. (Fact_Table_2003, Fact_Table_2004,...)
> I want to test to migrate to 2005 and I want to use the new partitionned
> table options.
> how to do this easely?
> thanks.
> Jerome.
>
>

migrating 2000 partitionned view to 2005 partitionned table?

Hi,
I have an SQL 2000 datawarehouse where my 2 biggest tables are partitionned
by year. (Fact_Table_2003, Fact_Table_2004,...)
I want to test to migrate to 2005 and I want to use the new partitionned
table options.
how to do this easely?
thanks.
Jerome.I have not had to do this yet, so this is all theory from my
perspective.
But from what I have seen, you should be able to backup/restore your
database "as is" and then I from what I have seen, you just need to do
an ALTER TABLE...SWITCH to move the table data around. BOL has a topic
on "moving partitioned data" which lists one of the uses of SWITCH as
"Assigning a table as a partition to an already existing partitioned
table."
Assigning partitions to different tables is just a meta data operation
so it happens very quickly, no data is copied, pointers to the data are
just updated.
In a nutshell I think the process would go something like this:
1. migrate the database onto SQL2k5
2. join all the tables together using "ALTER TABLE..SWITCH"
3. drop the view
4. rename the table from step 2. with the same name as the view
5. re-create any Referential Integrity constraints
I would suggest doing a test on small sample database first (a copy of
one of the sample databases maybe)
HTH
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <ePn#4Tk7FHA.3876@.TK2MSFTNGP09.phx.gbl>,
willgart@.BBBhotmailAAA.com says...
> Hi,
> I have an SQL 2000 datawarehouse where my 2 biggest tables are partitionne
d
> by year. (Fact_Table_2003, Fact_Table_2004,...)
> I want to test to migrate to 2005 and I want to use the new partitionned
> table options.
> how to do this easely?
> thanks.
> Jerome.
>
>sql

Migrate VIEW from Access to SQLserver ??

I migrated a DB from Access 2000 to SQLserver 2000.

The tables are OK, but I see the old Access views... as TABLE in
SQLserver!!

Any suggestions?

--Access isn't "smart" enough to upsize its queries into MS SQL Server views
so instead it just runs the query and creates a table from the result.
You'll have to port all of the queries manually (although I'm sure there are
tools or at least guides to help you with this) since Access's SQL differs
from MS SQL Server's SQL. Some of the most common issues I've run in are
that MS SQL Server doesn't have all those handy VB functions and IsNull()
does not work the same way in both.

"YURYSSG" <yuryssg@.yahoo.it> wrote in message
news:2i85qbFjsi78U1@.uni-berlin.de...
> I migrated a DB from Access 2000 to SQLserver 2000.
> The tables are OK, but I see the old Access views... as TABLE in
> SQLserver!!
> Any suggestions?
> --|||"Jonathan Amend" <cephas_is@.hotmail.com> /
: news:40bf8fc4$1_3@.aeinews...
> Access isn't "smart" enough to upsize its queries into MS SQL Server views
> so instead it just runs the query and creates a table from the result.
> You'll have to port all of the queries manually (although I'm sure there
are
> tools or at least guides to help you with this) since Access's SQL differs
> from MS SQL Server's SQL. Some of the most common issues I've run in are
> that MS SQL Server doesn't have all those handy VB functions and IsNull()
> does not work the same way in both.
> "YURYSSG" <yuryssg@.yahoo.it> wrote in message
> news:2i85qbFjsi78U1@.uni-berlin.de...
> > I migrated a DB from Access 2000 to SQLserver 2000.
> > The tables are OK, but I see the old Access views... as TABLE in
> > SQLserver!!
> > Any suggestions?
> > --|||"Jonathan Amend" <cephas_is@.hotmail.com> wrote:

>Access isn't "smart" enough to upsize its queries into MS SQL Server views
>so instead it just runs the query and creates a table from the result.
>You'll have to port all of the queries manually (although I'm sure there are
>tools or at least guides to help you with this) since Access's SQL differs
>from MS SQL Server's SQL. Some of the most common issues I've run in are
>that MS SQL Server doesn't have all those handy VB functions and IsNull()
>does not work the same way in both.

FWIW I was able to programmatically make views out of many of my
Access queries.

I had much more code than this but the basics were as follows.

strNewSQL = adhReplace(Q.SQL, vbCrLf, " ")
strNewSQL = Left(strNewSQL, InStr(strNewSQL, ";") - 1)
strNewSQL = ConvertTrueFalseTo10(strNewSQL)

tagRetryAfterCleanup:
Set myquerydef = dbsPermanent.CreateQueryDef("")
'Q.Name & " DAO Test")
myquerydef.ReturnsRecords = False
myquerydef.Connect = strConnect
myquerydef.SQL = "CREATE VIEW [" & strQueryName & "]
AS " & strNewSQL
myquerydef.Execute
myquerydef.Close

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

migrate objects between SQL2005

I got 2 development servers, I m triyng to move some data from 2 tables to other tables from other database located in other server.

I was using Sql Server 2005 Business Intelligence Development Studio to acommplish this, but when i debug it is shows me error.

someone can tell me the right steps to do this?

If you are using a windows authenticated account to access both servers, just right-click the table in the source database, go to Script Table as --> CREATE to --> New query editor window.

Once the script is generated, right-click in your script and go to Connection -->Change Connection, and connect to the other SQL Server. Once connected, select the appropriate database, and run the create script to add the table.

Then you can run a simple insert statement to post records from the source table to the new table using fully qulified naming. For example:

Insert into TargetTableName
Select * from SourceServerName.SourceDatabase.dbo.SourceTableName

sql

Migrate from SQL Server 2005 to MS Access 2000

I need to migrate some tables from a SQL Server 2005 full version (trial) database to a MS Access 2000 database. The situation is that a search program is available online (with SQL Server database) and also as a CD based desktop version ( that uses MS Access 2000). I have made corrections to the text contents of the online SQL Server DB and would like to make a MS Access 2000 database version for desktop application, rather than have to manually make the hundreds of corrections again in the MS Access DB. Is this type of migration possible? If so, where do I begin. I tried the export function in SQL Server but there is no option for the type of migration I need. Thanks for any help you can provide.I suggest using SQlExpress or SQl Everywhere on the client rather than this old version of Acccess however, Integration Services should be able to do it, is there an error?|||

Euan: Thanks for your help. I researched Integration Services and did the migration successfully by exporting from SQL Server Management Studio. I am dealing with a legacy desktop search program that only uses MS Access and needed to update some data from a new SQL online database version.

Migrate from Access to SQl Server 2005

I am trying to convert an Access database to SQL Server 2005. I have gotten the tables imported into SS2005 but how can I get all my queries imported in? Or is it not possible? Thanks.

John

You coudl try this CTP of a new tool

http://www.microsoft.com/downloads/details.aspx?familyid=D842F8B4-C914-4AC7-B2F3-D25FFF4E24FB&displaylang=en

Migrate from Access to SQL 2005

Dear friends,

I'm trying to migrate my database in MS Access to SQL 2005. With the tables I dont have any problem using the Microsft tool for that. But I dont know how can I migrate StoredProdures/Views created in Access...

I have about 300 SP and views in database access.

thanks

Someone help me?|||stored procedure in sql server is completely different from access. you may have to recode your sp's

Migrate database objects to new server

How to migrate all SQL 2000 objects (tables, views, SPs)
and users from current SQL server to a new SQL 2000
server in different domain? DO I need to modify DTS
packages?
Thanks in advance.
-DonHi
You could restore a backup or detach/attach the database. This should be
alot quicker.
See
http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b224071
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q314546
For DTS packages you can either follow the instructions in the second link
or use DTS backup utility from
http://www.sqldts.com/default.aspx?242
John
"Don Walter" <anonymous@.discussions.microsoft.com> wrote in message
news:8e2c01c3b5cf$38e49cb0$a601280a@.phx.gbl...
> How to migrate all SQL 2000 objects (tables, views, SPs)
> and users from current SQL server to a new SQL 2000
> server in different domain? DO I need to modify DTS
> packages?
> Thanks in advance.
> -Don|||Thank you John for your advice.
-Don
>--Original Message--
>Hi
>You could restore a backup or detach/attach the
database. This should be
>alot quicker.
>See
>http://support.microsoft.com/default.aspx?scid=kb%3ben-
us%3b224071
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;Q314546
>For DTS packages you can either follow the instructions
in the second link
>or use DTS backup utility from
>http://www.sqldts.com/default.aspx?242
>John
>"Don Walter" <anonymous@.discussions.microsoft.com> wrote
in message
>news:8e2c01c3b5cf$38e49cb0$a601280a@.phx.gbl...
>> How to migrate all SQL 2000 objects (tables, views,
SPs)
>> and users from current SQL server to a new SQL 2000
>> server in different domain? DO I need to modify DTS
>> packages?
>> Thanks in advance.
>> -Don
>
>.
>

Microsoft.SqlServer.Management

I am looking for a way to script out all tables within a SQL Server 2005 database using VB.NET. I am writing a backup utility and have been able to do stored procedures, the data within the tables, but am now stuck on scripting out the tables with their primary keys, indexes, etc. Sure, I could hard-code some stuff like "CREATE TABLE dbo" & strTable, but I am pretty sure there is a way to do it within VB.NET using SMO. So, any help that someone could offer is greatly appreciated.

Thanks

I just googled it and found the following:

http://www.yukonxml.com/articles/smo/

Not sure if the above will be any help for you

Microsoft Visual Studio

I have a database with 2 seperate tables. how can I get those two tables
onto the same report?On Jun 29, 2:32 pm, SQL Server express edition 2005
<SQLServerexpressedition2...@.discussions.microsoft.com> wrote:
> I have a database with 2 seperate tables. how can I get those two tables
> onto the same report?
Depending on your needs, you can either create a dataset that is based
on a query/stored procedure that accesses both tables -or- create 2
separate datasets, each going to a different table and have them
populate different report controls (i.e., table/matrix controls). Hope
this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Microsoft Visual Studio

I am using Microsoft Visual studio and have a question. I need to put
multiple tables on the same report how can I do that? I got this reply to a
previous message but dont understand it
"Depending on your needs, you can either create a dataset that is based
on a query/stored procedure that accesses both tables -or- create 2
separate datasets, each going to a different table and have them
populate different report controls (i.e., table/matrix controls). Hope
this helps."
Can someone please take me through this step by step in detail?On Jul 1, 12:12 am, SQL Server express edition 2005
<SQLServerexpressedition2...@.discussions.microsoft.com> wrote:
> I am using Microsoft Visual studio and have a question. I need to put
> multiple tables on the same report how can I do that? I got this reply to a
> previous message but dont understand it
> "Depending on your needs, you can either create a dataset that is based
> on a query/stored procedure that accesses both tables -or- create 2
> separate datasets, each going to a different table and have them
> populate different report controls (i.e., table/matrix controls). Hope
> this helps."
> Can someone please take me through this step by step in detail?
If you are wanting to combine multiple database tables in a report,
you can write a stored procedure/query including something like this:
select a.*, b.*
from table_a as a
inner join table_b as b on a.x = b.x
Then in the Report in the data view, to the right of Dataset: select
<New Dataset...> and then enter the stored procedure name as the query
string and select Stored Procedure as the command type. Then in the
Layout view, drag-and-drop a table control from the toolbox (Ctrl+Alt
+X). Right click the table control and select Properties. Then on the
General tab, select the dataset you just created below Dataset name:.
Select your header and footer rows via right-clicking the Header/
Footer row and selecting Properties. In the General tab select the
Value (Field from the dataset) you would like to display. If you just
want to display text, instead of right-clicking and selecting
Properties, right-click and select 'Expression...' and enter in
something like: ="Some text here." Do the same for the detail row.
If you want 2 table controls from different database tables, do the
same as above but create your dataset based on a single table or query
of your choice and then repeat for the number of different database
tables or queries you want to use. Just drag-and-dro the number of
different table controls you want to use in the report and repeat the
above steps. I hope this clarifies it a little better for you.
Regards,
Enrique Martinez
Sr. Software Consultant

Microsoft table design best practices

I remember viewing a Microsoft article (MSDN, i think) that gave a detailed
list of Microsoft's best practices for designing database tables. It
mentioned stuff like naming conventions, etc. but I'll be damned if I can
find it again.
Has anyone seen this article?bshewan wrote:
> I remember viewing a Microsoft article (MSDN, i think) that gave a
> detailed list of Microsoft's best practices for designing database
> tables. It mentioned stuff like naming conventions, etc. but I'll be
> damned if I can find it again.
> Has anyone seen this article?
Not from MS, but may give you some ideas:
http://vyaskn.tripod.com/object_naming.htm
David Gugick
Quest Software
www.imceda.com
www.quest.com