Copy data from one MS SQL database to another

Hi
The old database layout is very slow on queries so I have created a new
layout and want to copy the data from the old database into the new layout.
How can this best be done?
Do I need an application do this or can it be done by the SQL Server running
a stored procedure located in one of the databases?
Can you give me an example of a script copying data from one table in one
database to a table in another database?
Most of the tables are the same as before but there is one table that have
been normalized. Text fields have been moved to their own tables and
referenced from the main table.
Thank You for your help.
Regards
Kjell Arne Johansenyou can simply use
Insert into database1.DBO.newtable
(Select * from database2.dbo.oldtable )
before this be sure that your Newtable is empty or does not contain
duplicated rows
hch
"Kjell Arne Johansen" wrote:

> Hi
> The old database layout is very slow on queries so I have created a new
> layout and want to copy the data from the old database into the new layout
.
> How can this best be done?
> Do I need an application do this or can it be done by the SQL Server runni
ng
> a stored procedure located in one of the databases?
> Can you give me an example of a script copying data from one table in one
> database to a table in another database?
> Most of the tables are the same as before but there is one table that have
> been normalized. Text fields have been moved to their own tables and
> referenced from the main table.
> Thank You for your help.
> Regards
> Kjell Arne Johansen
>
>|||Hello Kjell
To copy the data, probably the best thing to do is use Data Transformation
Services from within Enterprise Manager. Have you also considered adding
indexes to the old database instead of creating a new one. Also, is your
database setup to automatically update statistics? If the database has been
in use for a while, the statistics that the server uses for your database
may be out of date which may be causing the slow queries.
HTH
D.
"Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
news:SXihe.10020$SL4.226519@.news4.e.nsc.no...
> Hi
> The old database layout is very slow on queries so I have created a new
> layout and want to copy the data from the old database into the new
> layout.
> How can this best be done?
> Do I need an application do this or can it be done by the SQL Server
> running a stored procedure located in one of the databases?
> Can you give me an example of a script copying data from one table in one
> database to a table in another database?
> Most of the tables are the same as before but there is one table that have
> been normalized. Text fields have been moved to their own tables and
> referenced from the main table.
> Thank You for your help.
> Regards
> Kjell Arne Johansen
>|||Hi
My company deliver process control systems and a part of this system is
logging of alarms and events to an SQL alarms and events database.
We have just moved from Access to SQL Server and may have some tuning and
architecture problems, I think..
There is one application writing to the database and several applications
that will read from the database.
Existing records in the database will not be edited but new one will be
added as soon there is a not normal situation in the process control.
The old database layout is not fully normalized. Many of the text fields
can be moved to their own tables.
I am experimenting with tables, relations and indexes in different new
layout to make the database so fast as possible both for writinig and
reading.
It is very important that the writer application always have access to the
database. The main table must not be locked for a longer time be the
readers.
Also the readers must have fast access to the data in the database even when
the main table grow to houndred millions of records.
To make the testing as complite as possible the database must be filled with
relevant data. I have got a database from one of our customers where the
main table has several million records with relevant data from their
process. I want to copy these data into a now layout and test reading and
writing.
The requrement is that it shall be possible to write bursts of data into the
database, up to 8000 records a minute.
This will not be the normal situation. In a good tuned process control
there will be less than one record each ten minute.
-But during configuration and equipment tuning there will be a lot of
situations that will cause an alarm or event.
I will try to write 2000 - 8000 alarms and events each minute to the
database, at same time as I am reading records from the database filtering
on different fields.
The repsons from the SQL Server shall be fast and the load on the SQL Server
shall not be high.
"Fast" and "High" is not fully defined yet. What is a fast respons, what
is high load on the SQL Server?
Typically I can say that the user will not want to wait for a respons for
more than 10 seconds.
The load on the SQL Server should not be so high that the writing
application does not get access to it.
I think normailizing the the database by putting fields that will have much
duplicated data into their own tables should be done.
The clustered primary key in the main table will be a combination of an auto
number desc, time desc, milliseconds desc, sequence desc, station desc.
I do not know about database statistics setup. Is this important?
Regards
Kjell Arne Johansen
"DJP" <djp@.snotmail.com> skrev i melding
news:pNkhe.2481$E7.1770@.news-server.bigpond.net.au...
> Hello Kjell
> To copy the data, probably the best thing to do is use Data Transformation
> Services from within Enterprise Manager. Have you also considered adding
> indexes to the old database instead of creating a new one. Also, is your
> database setup to automatically update statistics? If the database has
> been in use for a while, the statistics that the server uses for your
> database may be out of date which may be causing the slow queries.
> HTH
> D.
> "Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
> news:SXihe.10020$SL4.226519@.news4.e.nsc.no...
>|||Sounds like you just need to hire a competent databasedesigner to work with
you for a few days to get the schema correct. What you are asking SQL
Server to do is quite reasonable and should not be a problem with proper
normalization and indexing. Your PK sounds a bit much. If you have an
autonumber then it should be unique on its own. And SQL Server only has a
Datetime datatype which includes values down to the millisecond so you don't
need several columns for this.
Andrew J. Kelly SQL MVP
"Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
news:lllhe.9690$ai7.234663@.news2.e.nsc.no...
> Hi
> My company deliver process control systems and a part of this system is
> logging of alarms and events to an SQL alarms and events database.
> We have just moved from Access to SQL Server and may have some tuning and
> architecture problems, I think..
> There is one application writing to the database and several applications
> that will read from the database.
> Existing records in the database will not be edited but new one will be
> added as soon there is a not normal situation in the process control.
> The old database layout is not fully normalized. Many of the text fields
> can be moved to their own tables.
> I am experimenting with tables, relations and indexes in different new
> layout to make the database so fast as possible both for writinig and
> reading.
> It is very important that the writer application always have access to the
> database. The main table must not be locked for a longer time be the
> readers.
> Also the readers must have fast access to the data in the database even
> when the main table grow to houndred millions of records.
> To make the testing as complite as possible the database must be filled
> with relevant data. I have got a database from one of our customers where
> the main table has several million records with relevant data from their
> process. I want to copy these data into a now layout and test reading and
> writing.
> The requrement is that it shall be possible to write bursts of data into
> the database, up to 8000 records a minute.
> This will not be the normal situation. In a good tuned process control
> there will be less than one record each ten minute.
> -But during configuration and equipment tuning there will be a lot of
> situations that will cause an alarm or event.
> I will try to write 2000 - 8000 alarms and events each minute to the
> database, at same time as I am reading records from the database filtering
> on different fields.
> The repsons from the SQL Server shall be fast and the load on the SQL
> Server shall not be high.
> "Fast" and "High" is not fully defined yet. What is a fast respons, what
> is high load on the SQL Server?
> Typically I can say that the user will not want to wait for a respons for
> more than 10 seconds.
> The load on the SQL Server should not be so high that the writing
> application does not get access to it.
> I think normailizing the the database by putting fields that will have
> much duplicated data into their own tables should be done.
> The clustered primary key in the main table will be a combination of an
> auto number desc, time desc, milliseconds desc, sequence desc, station
> desc.
> I do not know about database statistics setup. Is this important?
> Regards
> Kjell Arne Johansen
>
> "DJP" <djp@.snotmail.com> skrev i melding
> news:pNkhe.2481$E7.1770@.news-server.bigpond.net.au...
>|||Hi
Yes, I know that the autonumber is unique. The millisecond field is also
microseconds from our process stations.
I need an index with the combination of time, and two other fields. An
Oracle database man in our company says I should make them clustered.
It looks like You can have only one clustered index in the table. Should
the PK be the clustered index or can I decide to use the combination of
fields as a clustered index?
I am concidering hiring a database expert haveing a look at our existing
layout.
Thank You.
Regards
Kjell Arne Johansen
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> skrev i melding
news:ODz6QoIWFHA.3840@.tk2msftngp13.phx.gbl...
> Sounds like you just need to hire a competent databasedesigner to work
> with you for a few days to get the schema correct. What you are asking
> SQL Server to do is quite reasonable and should not be a problem with
> proper normalization and indexing. Your PK sounds a bit much. If you
> have an autonumber then it should be unique on its own. And SQL Server
> only has a Datetime datatype which includes values down to the millisecond
> so you don't need several columns for this.
> --
> Andrew J. Kelly SQL MVP
>
> "Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
> news:lllhe.9690$ai7.234663@.news2.e.nsc.no...
>|||Yes hiring someone who really knows what they are doing can save you a LOT
of time and aggravation down the road. As for which is the best to cluster
you really have to know how your data is used overall to properly determine
that.
Andrew J. Kelly SQL MVP
"Kjell Arne Johansen" <kjellarj@.online.no> wrote in message
news:A6phe.9699$ai7.233873@.news2.e.nsc.no...
> Hi
> Yes, I know that the autonumber is unique. The millisecond field is also
> microseconds from our process stations.
> I need an index with the combination of time, and two other fields. An
> Oracle database man in our company says I should make them clustered.
> It looks like You can have only one clustered index in the table. Should
> the PK be the clustered index or can I decide to use the combination of
> fields as a clustered index?
> I am concidering hiring a database expert haveing a look at our existing
> layout.
> Thank You.
>
> Regards
> Kjell Arne Johansen
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> skrev i melding
> news:ODz6QoIWFHA.3840@.tk2msftngp13.phx.gbl...
>|||Try www.sqlscripter.com to copy your data.
Thomas
"Kjell Arne Johansen" wrote:

> Hi
> The old database layout is very slow on queries so I have created a new
> layout and want to copy the data from the old database into the new layout
.
> How can this best be done?
> Do I need an application do this or can it be done by the SQL Server runni
ng
> a stored procedure located in one of the databases?
> Can you give me an example of a script copying data from one table in one
> database to a table in another database?
> Most of the tables are the same as before but there is one table that have
> been normalized. Text fields have been moved to their own tables and
> referenced from the main table.
> Thank You for your help.
> Regards
> Kjell Arne Johansen
>
>

Converting Stored Procedures back to MS Access Queries

I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.

We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?

Any advice would be greatly appreciated.

Many thanks
SmillaYou can't convert any but the simplest sprocs into Access queries. Sprocs can be mult-step, while Access queries are simple statements like SQL Views. You would have to write VB code to simulate the sprocs, and there is no utility I know of that will do this for you.

converting sql 6.5 to access

Hey,
I have a SQL 6.5 db that I need to make accessible to a user for occasional
queries. The SQL server itself is being retired. Can I convert it from SQL
6.5 to Access? How would I do this?
tia,
jjHi,
Access can not hold the data if the SQL 6.5 database is more than 1 GB or so
( Not so sure). I recommend you to keep SQL 6.5 or
upgrade it to SQL 2000.
Steps to convert:-
To easily convert all of your SQL tables on a regular basis to Access
tables, you could:
1. Link the SQL tables you will need
2. Build Make Table queries for each table you wish to port to Access
3. Create a code that deletes the records in the local tables and runs the
make table queries to bring in the latest records from
SQL Server.
You can also use SQL 6.5 to replicate tables to move data to Access.
Thanks
Hari
SQL Server MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:%23BEPPS2PFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hey,
> I have a SQL 6.5 db that I need to make accessible to a user for
> occasional queries. The SQL server itself is being retired. Can I convert
> it from SQL 6.5 to Access? How would I do this?
> tia,
> jj
>

converting some t-sql queries to work with MS ACCESS

how do i convert this statement to work in MS ACCESS. These are some
statements I use in SQL server now they are required for a database in
access. They do not work. Anyone knows if it is possible to write them
in SQL which would work in access
sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)
sum(case when p.PaymentDate is null and datediff(day, DueDate,
getdate()) >30 then 1 else 0 end) [OVER 30]
sum(case when completionDate > Duedate then 1 else 0 end)
case when location = 'NYC' then avg(datediff(day, orderdate, shipdate))
else NULL end [Avg TIME TO SHIP in NYC]
sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]
Replace the case statments with IIF:
SELECT Sum(IIf([table1].[field1]='Hello',1,0)) AS Expr1
FROM Table1;
The avg statement may be:
SELECT Avg(field2) AS Expr1
FROM Table1
WHERE (((1) In (SELECT IIF(field1 = 'Hello', 1, 0)
FROM Table1
)));
ashley.sql@.gmail.com wrote:
> how do i convert this statement to work in MS ACCESS. These are some
> statements I use in SQL server now they are required for a database in
> access. They do not work. Anyone knows if it is possible to write them
> in SQL which would work in access
> sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)
> sum(case when p.PaymentDate is null and datediff(day, DueDate,
> getdate()) >30 then 1 else 0 end) [OVER 30]
> sum(case when completionDate > Duedate then 1 else 0 end)
> case when location = 'NYC' then avg(datediff(day, orderdate, shipdate))
> else NULL end [Avg TIME TO SHIP in NYC]
>
> sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]

converting some t-sql queries to work with MS ACCESS

how do i convert this statement to work in MS ACCESS. These are some
statements I use in SQL server now they are required for a database in
access. They do not work. Anyone knows if it is possible to write them
in SQL which would work in access
sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)
sum(case when p.PaymentDate is null and datediff(day, DueDate,
getdate()) >30 then 1 else 0 end) [OVER 30]
sum(case when completionDate > Duedate then 1 else 0 end)
case when location = 'NYC' then avg(datediff(day, orderdate, shipdate))
else NULL end [Avg TIME TO SHIP in NYC]
sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]Replace the case statments with IIF:
SELECT Sum(IIf([table1].[field1]='Hello',1,0)) AS Expr1
FROM Table1;
The avg statement may be:
SELECT Avg(field2) AS Expr1
FROM Table1
WHERE (((1) In (SELECT IIF(field1 = 'Hello', 1, 0)
FROM Table1
)));
ashley.sql@.gmail.com wrote:
> how do i convert this statement to work in MS ACCESS. These are some
> statements I use in SQL server now they are required for a database in
> access. They do not work. Anyone knows if it is possible to write them
> in SQL which would work in access
> sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)
> sum(case when p.PaymentDate is null and datediff(day, DueDate,
> getdate()) >30 then 1 else 0 end) [OVER 30]
> sum(case when completionDate > Duedate then 1 else 0 end)
> case when location = 'NYC' then avg(datediff(day, orderdate, shipdate))
> else NULL end [Avg TIME TO SHIP in NYC]
>
> sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]

converting some t-sql queries to work with MS ACCESS

how do i convert this statement to work in MS ACCESS. These are some
statements I use in SQL server now they are required for a database in
access. They do not work. Anyone knows if it is possible to write them
in SQL which would work in access
sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)
sum(case when p.PaymentDate is null and datediff(day, DueDate,
getdate()) >30 then 1 else 0 end) [OVER 30]
sum(case when completionDate > Duedate then 1 else 0 end)
case when location = 'NYC' then avg(datediff(day, orderdate, shipdate))
else NULL end [Avg TIME TO SHIP in NYC]
sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]Replace the case statments with IIF:
SELECT Sum(IIf([table1].[field1]='Hello',1,0)) AS Expr1
FROM Table1;
The avg statement may be:
SELECT Avg(field2) AS Expr1
FROM Table1
WHERE (((1) In (SELECT IIF(field1 = 'Hello', 1, 0)
FROM Table1
)));
ashley.sql@.gmail.com wrote:
> how do i convert this statement to work in MS ACCESS. These are some
> statements I use in SQL server now they are required for a database in
> access. They do not work. Anyone knows if it is possible to write them
> in SQL which would work in access
> sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)
> sum(case when p.PaymentDate is null and datediff(day, DueDate,
> getdate()) >30 then 1 else 0 end) [OVER 30]
> sum(case when completionDate > Duedate then 1 else 0 end)
> case when location = 'NYC' then avg(datediff(day, orderdate, shipdate))
> else NULL end [Avg TIME TO SHIP in NYC]
>
> sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]

Converting Rows into Columns MS SQL 2K

I have a SP that returns the information I want but it returns it in 2
separate queries.

Example:
Query 1
Name, Number, Class
Row 1- Mike Phillips, 154AA, AA
and
Query 2
Time, Manual
Row 1 -12:45:22,0
Row 2 -13:04:56,0

What I want it to look like is:
Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2
Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0

Here is the query I'm using:
DECLARE Class cursor

FOR
--here we get a list of distinct classes to pass to the Class cursor
select Distinct(class_ID) from kt_member_lap
where Race_ID = 83
order by Class_ID;

OPEN Class;

DECLARE @.RaceID int
DECLARE@.RacerCount int
DECLARE @.ClassID char(50)
DECLARE @.classcount
DECLARE @.Racer char(50)
DECLARE @.i int

SET @.RaceID = 83
--this is where we loop through the classes
FETCH NEXT FROM Class INTO @.ClassID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN

IF (@.@.FETCH_STATUS <> -2)

DECLARE Lap cursor

FOR
Select DISTINCT(Member_ID) from KT_MEMBER_LAP
Where class_ID = @.classID and race_id = @.RaceID

OPEN Lap;

--this is to begin counting from the first lap
SET @.i = 1;

FETCH NEXT FROM Lap INTO @.Racer
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN

IF (@.@.FETCH_STATUS <> -2)
SELECT KT_MEMBER.MEMBER_FNAME + ' ' +
KT_MEMBER.MEMBER_LNAME As MemberName,
CONVERT(nvarchar(3),
KT_MEMBER_CLASS.MEMBER_CLASS_BIKE_NUM) + KT_CLASS.CLASS_LETTER As
BikeNumber,
KT_CLASS.CLASS_DESC
FROM KT_CLASS INNER JOIN
KT_MEMBER_CLASS ON KT_CLASS.CLASS_ID =
KT_MEMBER_CLASS.CLASS_ID INNER JOIN
KT_MEMBER ON KT_MEMBER_CLASS.MEMBER_ID =
KT_MEMBER.MEMBER_ID
WHERE KT_MEMBER.MEMBER_ID = @.Racer and KT_CLASS.CLASS_ID =
@.ClassID

--SELECT @.Racer, @.ClassID

Select MEMBER_LAP_TIME_REAL, member_lap_manual from KT_MEMBER_LAP
Where Member_ID = @.Racer and class_ID = @.classID and race_id =
@.RaceID
ORDER BY MEMBER_LAP_TIME_REAL

--here I count up for the next lap
SET @.i = @.i + 1;

FETCH NEXT FROM Lap INTO @.Racer
END

CLOSE Lap;
DEALLOCATE Lap;

FETCH NEXT FROM Class INTO @.ClassID
END

CLOSE Class;
DEALLOCATE Class;

Any help would be appreciated.[posted and mailed, please reply in news]

dare197 (daniel.white@.perceptivetech.com) writes:
> I have a SP that returns the information I want but it returns it in 2
> separate queries.
> Example:
> Query 1
> Name, Number, Class
> Row 1- Mike Phillips, 154AA, AA
> and
> Query 2
> Time, Manual
> Row 1 -12:45:22,0
> Row 2 -13:04:56,0
> What I want it to look like is:
> Name, Number, Class, Time 1, Manual 1, Time 2, Manual 2
> Row 1- Mike Phillips, 154AA, AA, 12:45:22, 0, 13:04:56, 0

Could there be any number of Time, Manual rows or is there never more
than two? I will assume that you always have two. Then you can try
this query:

SELECT m.MEMBER_FNAME + ' ' + m.MEMBER_LNAME As MemberName,
CONVERT(nvarchar(3), mc.MEMBER_CLASS_BIKE_NUM) +
c.CLASS_LETTER As BikeNumber,
c.CLASS_DESC,
ml1.MEMBER_LAP_TIME_REAL AS "Time 1",
ml1.member_lap_manual AS "Manual 1",
ml2.MEMBER_LAP_TIME_REAL AS "Time 2",
ml2.member_lap_manual AS "Manual 2",
FROM KT_CLASS c
JOIN KT_MEMBER_CLASS mc ON c.CLASS_ID = mc.CLASS_ID
JOIN KT_MEMBER m ON mc.MEMBER_ID = m.MEMBER_ID
JOIN KT_MEMBER_LAP ml1 ON ml1.MEMBER_ID = m.MEMBER_ID
AND mll.CLASS_ID = mc.CLASS_ID
JOIN KT_MEMBER_LAP ml2 ON ml2.MEMBER_ID = m.MEMBER_ID
AND ml2.CLASS_ID = mc.CLASS_ID
AND ml2.member_lap_manual > ml1.member_lap_manual
WHERE m.MEMBER_ID = @.Racer
AND c.CLASS_ID = @.ClassID
ORDER BY c.CLASS_ID, m.MEMBER_ID

Here I have collapsed everything into one query, without any cursor, as
I could see no need for a cursor. Cursors can be a magnitude slower than
set-based statements, so there all reasons to avoid them.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Converting report input dates to UTC

I have created a report in reporting services and it takes a number of
DateTime values as input parameters. These values will be passed on to
queries in my database; the problem is that my database stores DateTime
values as UTC.
Is there a way I can convert them to UTC before the values are sent to the
query?Hello,
You could try this:
DATEADD(Hour, DATEDIFF(Hour, GETUTCDATE(), GETDATE()), @.LocalDate)
http://geekswithblogs.net/ewright/archive/2004/09/14/11180.aspx
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
>Thread-Topic: Converting report input dates to UTC
>thread-index: AcYtdvuTa+Zu+2dZQC6M3qdl1xmC3Q==>X-WBNR-Posting-Host: 195.139.24.170
>From: "=?Utf-8?B?Q2hyaXN0b3BoZXIgS2ltYmVsbA==?="
<c_kimbell@.newsgroup.nospam>
>Subject: Converting report input dates to UTC
>Date: Thu, 9 Feb 2006 04:47:27 -0800
>Lines: 7
>Message-ID: <920BBD92-A5C6-4A43-B965-8D1AE0151687@.microsoft.com>
>MIME-Version: 1.0
>Content-Type: text/plain;
> charset="Utf-8"
>Content-Transfer-Encoding: 7bit
>X-Newsreader: Microsoft CDO for Windows 2000
>Content-Class: urn:content-classes:message
>Importance: normal
>Priority: normal
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:68388
>X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
>I have created a report in reporting services and it takes a number of
>DateTime values as input parameters. These values will be passed on to
>queries in my database; the problem is that my database stores DateTime
>values as UTC.
>Is there a way I can convert them to UTC before the values are sent to the
>query?
>

converting queries from Access2k to MsSql2k

Hi,

I'm converting an Access 2000 database to Sql Server and must be
missing something obvious.

Using the Import utility in Sql Server, the Access queries seem to get
executed and the resultant data imported as tables. Oops!

Using the Upsize lizard in Access 2003, the queries aren't even in the
selection list of "tables" to upsize. It looks like the Upsize wizard
isn't supposed to do queries.

How does one automate the migration of standard conforming queries to
Sql Server?

Of course, I expect to spend some time addressing the stickier
non-compliant queries, but there must be a way to do the easy ones.
What am I missing?

-Davedschl wrote:
> Hi,
> I'm converting an Access 2000 database to Sql Server and must be
> missing something obvious.
> Using the Import utility in Sql Server, the Access queries seem to get
> executed and the resultant data imported as tables. Oops!
> Using the Upsize lizard in Access 2003, the queries aren't even in the
> selection list of "tables" to upsize. It looks like the Upsize wizard
> isn't supposed to do queries.
> How does one automate the migration of standard conforming queries to
> Sql Server?
> Of course, I expect to spend some time addressing the stickier
> non-compliant queries, but there must be a way to do the easy ones.
> What am I missing?
> -Dave

I don't know that you can automagically convert queries from Access to
SQL. After all, the "easy" ones should already run in SQL.

All of Joe's ranting about writting standard SQL for easier migration
isn't sounding so silly after all... :D

Zach|||Hi Dave,

The Import util. in SQL will give you the ability to import data from
tables as well as data from queries as if they were Views and not the
schema of the query. A dirty way of upsizing is to again use the Upsize
wizard in Access and when prompted accept the question to create an
Access project. This option whether you decide to keep the Access
Project front-end or not will upsize the schema for the tables and
queries. Through this process Query code that is T-SQL compliant and
not MS Access specific will be created on the SQL Server as a view,
stored proc. or function. A couple of words of advice on the upsize:

1 - Compile, compact and repair the db before upsize.
2 - Ensure that the code behind the queries are clean such as no double
quotes, no Access specific functions, etc...
3 - If large tables, upsize table schema in wizard and when complete
use DTS to import data, this will save some time.
4 - Verify through report at end of upsize, proper table and column
properties.

Hope that helps, Unfortunatly I have been through far to many of these.

RS

dschl wrote:
> Hi,
> I'm converting an Access 2000 database to Sql Server and must be
> missing something obvious.
> Using the Import utility in Sql Server, the Access queries seem to
get
> executed and the resultant data imported as tables. Oops!
> Using the Upsize lizard in Access 2003, the queries aren't even in
the
> selection list of "tables" to upsize. It looks like the Upsize wizard
> isn't supposed to do queries.
> How does one automate the migration of standard conforming queries to
> Sql Server?
> Of course, I expect to spend some time addressing the stickier
> non-compliant queries, but there must be a way to do the easy ones.
> What am I missing?
> -Dave|||how many queries you worried about?
i can probably rewrite 20 access queries per hour into SQL Server|||I don't know if this would be of any help, but I do this with some
frequency and set up some guidelines for being more efficient with the
conversions.

See

http://thecodegallery.org/modules.p...order=0&thold=0