please help access/sql server conversion

I am new to the db world. I am trying to convert an Access 2000 database to SQL Server 2000. f/e has forms, reports, macros, modules, b/e has tables which the f/e file has linked to via link manager. The b/e is on a shared network drive. A dozen users, about 150 tables. The b/e file is appoaching 1Gb. The plan is to keep the Access f/e.

I have read the microsoft Q;s on Upsizing, DTS, Access Project vs Access database, Access sv SQL, etc. I am having diffculty understanding the best approach for my situation.

Looking for some expert guidance before I jump in.

Thanx

CFWhy do you need to migrate your data hauling operation from pickup trucks to 18 wheelers in the first place? Load? Speed? Safety?

How about - just for starters - a simple/non-radical/initial approach:
Keep everything the way it is except for actual data in Access tables that could go into SQL tables instead with Access tables linked to it. This will for instance overcome one of the biggest Access limits: 2 GB per "mdb".
(As you probably know, once over 2 GB - in 2000 & 2002/XP - Access could crash at any moment with data from it never to be seen again. )|||Thanks for your quick reply.

Speed is the main thing, and there have been multiple user contention problems leading to lockup. The site is upgrading everything for speed.

When you say "move the actual data only" What's the proper method - simplest, to move the data only, i.e., how do i create the SQL tables automatically/safely (150 tables), and then weeks/days later move the Access data to the new tables?

Thanks,

CF|||The real Client-Server solution would be "by the book" conversion of Access "mdb" into Access Project (mdp) as a front end and storing data with data processing in SQL S.
This of course is easier said than done, especially on live and often used db.

What is really easy (although not a real solution) is to first setup tables in SQL S., work out minor differences of data types between Access an SQL S., run SQL S. import wizard to populate the tables and then link to them from Access. For one thing it is a quick workaround for JET limit of 2 GB per mdb since linked tables do not take lot of room, for another is getting data into SQL S. to find out stress-free any conversion issues, like for instance often with date/time fields.

I had Access run like hell this primitive way on well indexed datasets of over 50 GB.sql

PL/SQL to TSQL

I need a tool to convert (at least 50%) my source code of 200+ stored procedures from PL/SQL to TSQL.
Could any one help me?
ThanksHi Carlos,

Check out our website http://www.dbbest.com

Pixel to inch.

Hi,

I am using SSRS 2005 + SP1.

Is there any better way to convert pixel to inch or cm?

Thanks n Regards,

UT.

where do you use pixel in rs2005?
you can set any size in cm or inch..
|||

Thx for the reply.

I was given a spec with all the unit of measure was in pixels. That's why I need to convert it into cm or inch.

Thanks and Regards,

UT.

|||

I guess you can follow this approach for the conversion:

x Pixels / 96 dpi (screen resolution) = y inches

-- Robert

PIVOT/CROSS TAB/Converting Rows to (multiple group) Columns

Hello All,

I am trying to convert the rows in a table to columns. I have found similar threads on the forum addressing this issue on a high level suggesting the use of cursors, PIVOT Transform, and other means. However, I would appreciate if someone can provide a concrete example in T-Sql for the following subset of my problem.

Consider that we have Product Category, Product and its monthly sales information retrieved as follows:

CategoryID ProductID ProductName Month UnitPrice QtySold SalesAmount 1 1 Panel Jan 5 10 50 1 1 Panel Feb 5 15 75 1 1 Panel Mar 5 20 100 1 2 Frame Jan 10 30 300 1 2 Frame Feb 10 25 250 1 2 Frame Mar 10 20 200 1 3 Glass Jan 20 10 200 1 3 Glass Feb 20 20 400 1 3 Glass Mar 20 30 600

I would like it to be converted into following result set:

CategoryID ProductID ProductName UnitPrice QtySold_Jan SalesAmt_Jan QtySold_Feb SalesAmt_Feb QtySold_Mar SalesAmt_Mar 1 1 Panel 5 10 50 15 75 20 100 1 2 Frame 10 30 300 25 250 20 200 1 3 Glass 20 10 200 20 400 30 600

I have purposefully included QtySold here as I need to display both Quantity and Sales as measured column groups in my report. Can this be achieved in sql? I would appreciate any responses.

Thanks.

What you are attempting to do is BEST done with the client application. SQL Server excels at storing and retreiving data. These kinds of 'transformations', while possible, are not the best use of a very expensive resource.

However, if you must, these articles demonstrate several variations of how to accomplish your goal -and they offer 'concrete' examples

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

.

Pivot Tables using SQL

Hi,

I need some help in writing an SQL script that would convert the results into a pivot table in Toad. I am extracting data from an Oracle database.

I need to have each post date as a column header from the SQL below:

select ca.ACCOUNT_ID, cd.CLIENT_NAME, od.post_date, sum(oi.PRICE)
from order_details od, client_details cd, client_account ca, order_items oi
where od.DELETED = 0
and od.SERVICE_ID = 300009
and cd.CLIENT_ID = od.CLIENT_ID
and cd.CLIENT_ID = ca.CLIENT_ID
and od.ORDER_ID = oi.ORDER_ID
and od.INVOICE_DATE = oi.IDATE
group by ca.ACCOUNT_ID, cd.CLIENT_NAME, od.post_date

Thanks for any helpCheck here:

http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:7086279412131,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:419593546543,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:766825833740,
http://asktom.oracle.com/pls/ask/f?p=4950:8:16663421538065257584::NO::F4950_P8_DISP LAYID,F4950_P8_CRITERIA:925229353765,
:eek:|||Thanks...will have a look|||Also, this question has been asked and answered many times on many forums, just search for 'PIVOT TABLE' or 'CROSS TAB QUERY'. :D

Pivot query error

I'm trying to simulate an Access query in SQL and reached a dead end.

SELECT * FROM (SELECT
AccountNumber
, Convert(varchar, startDate, 101) AS startDate
, Convert(varchar, resultDate, 101) AS resultDate
FROM [TableA]) AS D
PIVOT(SUM(Amount) FOR resultDate IN([02/27/2006],[02/28/2006],[03/01/2006],[03/02/2006],[Outstanding])) AS P

Now I want to add two things:
1) Add one more column 'Total' containing the total of amount for each startDate
2) Add a column 'OutStanding' in PIVOT which should contain SUM(Amount) where resultDate IS NULL.

How do I do it?declare @.TableA table (
AccountNumber int,
StartDate datetime,
ResultDate datetime,
amount decimal(10,2)
)

insert into @.TableA values (100, '02/27/2006', '02/28/2006', 300)
insert into @.TableA values (100, '02/28/2006', null, 500)
insert into @.TableA values (100, '03/01/2006', null, 800)
insert into @.TableA values (100, '02/27/2006', null, 100)
insert into @.TableA values (100, '02/27/2006', null, 200);

insert into @.TableA values (200, '02/27/2006', null, 50)
insert into @.TableA values (200, '02/28/2006', null, 100)
insert into @.TableA values (200, '03/01/2006', null, 200)
insert into @.TableA values (200, '02/27/2006', null, 50)
insert into @.TableA values (200, '02/27/2006', null, 100);

insert into @.TableA values (300, '02/27/2006', '02/28/2006', 50)
insert into @.TableA values (300, '02/28/2006', '03/01/2006', 100)
insert into @.TableA values (300, '03/01/2006', '03/05/2006', 200)
insert into @.TableA values (300, '02/27/2006', null, 50)
insert into @.TableA values (300, '02/27/2006', null, 100);

insert into @.TableA values (400, '02/27/2006', '02/28/2006', 50)
insert into @.TableA values (400, '02/28/2006', '03/01/2006', 100)
insert into @.TableA values (400, '03/01/2006', '03/05/2006', 200)
insert into @.TableA values (400, '02/27/2006', '03/05/2006', 50)
insert into @.TableA values (400, '02/27/2006', '03/07/2006', 100);

WITH Tot_CTE (AccountNumber, Total, OutStanding)
AS
(
SELECT AccountNumber, SUM(Amount), SUM(case when ResultDate is null then Amount else 0 end)
FROM
@.TableA
GROUP BY AccountNumber
)
SELECT a.*,b.Total,b.OutStanding
FROM (
SELECT * FROM (SELECT
AccountNumber
, Convert(varchar, startDate, 101) AS startDate
--, Convert(varchar, resultDate, 101) AS resultDate
,Amount
FROM @.TableA) AS D
PIVOT (SUM(Amount) FOR StartDate IN([02/27/2006],[02/28/2006],[03/01/2006],[03/02/2006])) AS P
) as a
join
tot_CTE b
on
a.accountNumber = b.AccountNumber|||

PIVOT has lot of restrictions and you cannot generate multiple aggregates for example. So It is easier to do this using a standard SQL query. And it is much more efficient since you can compute all the necessary aggregates in one pass of the data. Try query below. I had to guess the details about your schema and what each column means. But you should get the idea.

SELECT t.AccountNumber

, t.StartDate

, SUM(CASE WHEN t.resultDate IS NULL THEN t.Amount END) as OutStanding

, SUM(CASE t.resultDate WHEN '20060227' THEN t.Amount END) as "20060227"

, SUM(CASE t.resultDate WHEN '20060228' THEN t.Amount END) as "20060228"

...

, SUM(t.Amount) AS Total

FROM TableA AS t

WHERE t.resultDate is null

or t.resultDate between '20060227' and '20060302' -- modify accordingly

GROUP BY t.AccountNumber, t.StartDate -- may need to strip time part depending on your data