Converting an Access database

Converting an Access database

I uninstalled sqlserver2005 express and reinstalled sqlserver2005 ctp. Now, everything is in order.

I've just searched books online for convert(conversion) and import(imports, importing).

I see no instructions on how to convert my access 2003 database into an sqlserver database.

It was easy enough in sqlserver 2000. However, I've uninstalled that. Is there a way of coverting in sqlserver 2005? I'm in big trouble if I can't.

A related question. I'm very familiar with Access 2003. So I'll continue to develop the database structure in Access. Can I import the incremental changes? Or do I have to delete the current conversion and re-import(assuming I can) the whole new database each time?

dennist685

You can use the access upsizing wizard (tools-> database utilities ->upsizing wizard) from within access to push the data. Alternatively, use SSIS from SQL 2005 to import the data from access.

Cathal|||Cathal,

Thank you very much. I had no idea one could do it from Access.

dennist685|||Search as I could, I couldn't find SSIS in sqlserver 2005

Dennist685

Converting an Access database

Converting an Access database

I uninstalled sqlserver2005 express and reinstalled sqlserver2005 ctp. Now, everything is in order.

I've just searched books online for convert(conversion) and import(imports, importing).

I see no instructions on how to convert my access 2003 database into an sqlserver database.

It was easy enough in sqlserver 2000. However, I've uninstalled that. Is there a way of coverting in sqlserver 2005? I'm in big trouble if I can't.

A related question. I'm very familiar with Access 2003. So I'll continue to develop the database structure in Access. Can I import the incremental changes? Or do I have to delete the current conversion and re-import(assuming I can) the whole new database each time?

dennist685

You can use the access upsizing wizard (tools-> database utilities ->upsizing wizard) from within access to push the data. Alternatively, use SSIS from SQL 2005 to import the data from access.

Cathal|||Cathal,

Thank you very much. I had no idea one could do it from Access.

dennist685|||Search as I could, I couldn't find SSIS in sqlserver 2005

Dennist685

Converting 2000 rdl files to 2005

Hi, how do I go about converting 2000 rdl files to 2005 format?
I have searched on the web and people recommend opening up the
existing rdl files in report designer or create a new microsoft report
project from visual studio 2005.
My problem is that when I open up the rdl files in 2005 I just have
the XML contents and no designer support. Also, I looked to create a
new report project from the File-->New-->Project prompt in VS2005 but
I do not have a Report Project available to me...
I'm probably missing something basic here so any help appreciated :)
Thanks
MarkusThe difference in the rdl files are minimal. Change the 2003 namespace to:
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
and there are few other properties that can be added or changed once you
figure out the missing VS2005 components.
Those properties or elements are: InteractiveWidth like:
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
and InteractiveHeight like:
<Width>10.125in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
The converse is true when you design a report in VS2005 and need to go back
to VS2003 for SQL Server 2000. I always just comment out InteractiveHeigth
and InteractiveWidth and change the "2005/01" to "2003/10".
Rob.
"MarkusJNZ@.gmail.com" wrote:
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||I think you have not installed the developper tools of SQL Server 2005
install the client tools from the SQL Server CD and check the options to
insure you install the client components for RS.
after this, Visual Studio will convert the reports automatically.
but an RS2000 report can be executed and rendered by reporting services
without any change.
<MarkusJNZ@.gmail.com> wrote in message
news:1177894103.172863.251460@.n59g2000hsh.googlegroups.com...
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||On Apr 30, 1:41 pm, "Jeje" <willg...@.hotmail.com> wrote:
> I think you have not installed the developper tools of SQL Server 2005
> install the client tools from the SQL Server CD and check the options to
> insure you install the client components for RS.
> after this, Visual Studio will convert the reports automatically.
> but an RS2000 report can be executed and rendered by reporting services
> without any change.
> <Markus...@.gmail.com> wrote in message
> news:1177894103.172863.251460@.n59g2000hsh.googlegroups.com...
>
> > Hi, how do I go about converting 2000 rdl files to 2005 format?
> > I have searched on the web and people recommend opening up the
> > existing rdl files in report designer or create a new microsoft report
> > project from visual studio 2005.
> > My problem is that when I open up the rdl files in 2005 I just have
> > the XML contents and no designer support. Also, I looked to create a
> > new report project from the File-->New-->Project prompt in VS2005 but
> > I do not have a Report Project available to me...
> > I'm probably missing something basic here so any help appreciated :)
> > Thanks
> > Markus- Hide quoted text -
> - Show quoted text -
Hi, thanks for your help; I went to install the client tools and I was
told that I already had them installed and that the install would not
go ahead as I was not changing anything on my local machine :-/
Any ideas welcome
Thanks
Markus|||Hi, I tried to do the install again and this was the message I
received
"Build version check:
Your upgrade is blocked because the existing component is a later
version than the version you are trying to upgrade to. To modify an
existing SQL Server component, go to Add or Remove Programs in Control
Panel, select the component you would like to modify, and then click
Change/Remove."
Thanks again
Markus|||Hi, is your rdl's files, created using report builder 2000? I get a doubt
that the designer doesn't shows means, just some simple reports are created
using RB and saved as reports. Pl confirm.
Amarnath
"MarkusJNZ@.gmail.com" wrote:
> Hi, how do I go about converting 2000 rdl files to 2005 format?
> I have searched on the web and people recommend opening up the
> existing rdl files in report designer or create a new microsoft report
> project from visual studio 2005.
> My problem is that when I open up the rdl files in 2005 I just have
> the XML contents and no designer support. Also, I looked to create a
> new report project from the File-->New-->Project prompt in VS2005 but
> I do not have a Report Project available to me...
> I'm probably missing something basic here so any help appreciated :)
> Thanks
> Markus
>|||sorry, first time I see this message.
you have to check which applications are already installed, maybe you can
just change the installed components of an existing SQL 2005 installation.
else try to uninstall all your components and try again.
<MarkusJNZ@.gmail.com> wrote in message
news:1177910981.456177.237840@.o5g2000hsb.googlegroups.com...
> Hi, I tried to do the install again and this was the message I
> received
> "Build version check:
> Your upgrade is blocked because the existing component is a later
> version than the version you are trying to upgrade to. To modify an
> existing SQL Server component, go to Add or Remove Programs in Control
> Panel, select the component you would like to modify, and then click
> Change/Remove."
> Thanks again
> Markus
>|||On Apr 30, 9:19 pm, "Jeje" <willg...@.hotmail.com> wrote:
> sorry, first time I see this message.
> you have to check which applications are already installed, maybe you can
> just change the installed components of an existing SQL 2005 installation.
> else try to uninstall all your components and try again.
> <Markus...@.gmail.com> wrote in message
> news:1177910981.456177.237840@.o5g2000hsb.googlegroups.com...
>
> > Hi, I tried to do the install again and this was the message I
> > received
> > "Build version check:
> > Your upgrade is blocked because the existing component is a later
> > version than the version you are trying to upgrade to. To modify an
> > existing SQL Server component, go to Add or Remove Programs in Control
> > Panel, select the component you would like to modify, and then click
> > Change/Remove."
> > Thanks again
> > Markus- Hide quoted text -
> - Show quoted text -
Hi, thanks for your help everyone. It looks like I will have to
uninstall and then reintall MSSQL 2005 on my local machine :/
Thanks again
Markussqlsql

Convert varchar to date on a column

I have a table with a column defined as a nvarchar. The strings contained in the columns are in the form of YYYYMMDD. I searched the forums here and believe that if I have a date as a string then YYYYMMDD is the correct format for a date stored as string. However, I think I need to store the date as a Date type for selecting, sorting, searching, and indexing. What is the best method of converting the entire column to a Date type from a nvarchar type with the assumption that all the string dates are in the same YYYYMMDD format? When I tried to modify the table using the Managment Studio Express, I get a warning: "Data might be lost converting column 'Date' from 'nvarchar(50)'.

Thanks!

DeBug

Update: I just noticed that all the text fields have double quotes at the start and end of each text string so my so called YYYYMMDD is really "YYYYMMDD". So I guess the answer would need to include how to scrub the " with an UPDATE command. I used the DTS that came with Express to import the data from a flat file.

Moving to TSQL forum. There are many ways i can think of doing this, one of the easiest would be to create a new column, call it DateCol2 with datetime datatype, and then do an update statement on the table to set this column properly. it might involve some parsing, but if you make a mistake, you can always try it again since this is just a new column. Once you've updated it correctly, you can drop the original column and rename the new column appropriately.

Someone here can probably give you the proper update statement.

|||

With the help of Google, a little trial and error, and two cups of coffee here is what worked:

I used the DTS Wizard (Hey! SQL Express has a DTS wizard!) to select the flat file with the source data but entered the "Text Qualifier" as a single double quote character. I had previously left that field blank on the first 14 attempts :) Once the column was populated with the string as YYYYMMDD and not "YYYYMMDD" with the quotes, the modify table colulmn converted the strings to dates. I still got the warning message but a quick glance at the table showed a successful conversion. I will format the dates to mm/dd/yyyy for reports and the end user application on the fly (I think...).

I would still like to know what are the advantages, if any, of using that column as a Date type versus a Varchar type. Anyone?

Doug DeBug

|||

For starters, with datetime datatype you can do arithmatic operations using built-in functions (dateadd, datediff, datefirst, etc.), you can also reference it in multiple ways - "December 27, 2006" or "12/27/2006", or using convert(). You can read more about this datatype in Books Online topic "Using Date and Time Data", http://msdn2.microsoft.com/en-us/library/ms180878.aspx.

|||

Create Table #test(mydate varchar(25))

Alter Table #test

Alter column mydate datetime

drop table #test

Adamus

|||

Doug DeBug wrote:

With the help of Google, a little trial and error, and two cups of coffee here is what worked:

I used the DTS Wizard (Hey! SQL Express has a DTS wizard!) to select the flat file with the source data but entered the "Text Qualifier" as a single double quote character. I had previously left that field blank on the first 14 attempts :) Once the column was populated with the string as YYYYMMDD and not "YYYYMMDD" with the quotes, the modify table colulmn converted the strings to dates. I still got the warning message but a quick glance at the table showed a successful conversion. I will format the dates to mm/dd/yyyy for reports and the end user application on the fly (I think...).

I would still like to know what are the advantages, if any, of using that column as a Date type versus a Varchar type. Anyone?

Doug DeBug

Using the field as a datetime vs. varchar() is the logical thing to do. There's no advantage but only a disadvantage. You can't use operators as comparison and you remove the possibility of using BETWEEN.

Adamus

|||

You can do the convert using UPDATE statement and then do ALTER like:

update tbl

set your_col = replace(your_col, '"', '')

go

alter tbl alter column your_col smalldatetime

go

As for the benefits of using datetime/smalldatetime vs varchar type, the obvious ones are:

1. Appropriate type checking and domain enforcement

2. Better performance

3. Compatibility with other built-in date functions (although they accept strings it depends on the format)

4. Storage depending on how you store the date value in character format

5. Ordering semantics that follow the date or datetime rules

There are cases where you may want to store datetime values in more compact form than using datetime/smalldatetime but those need to be done with care & careful consideration.

|||

Thanks for the example. I will have not used T-SQL as much I should but have relied on the GUIs in the past. I will need a solution to update the customer's database at least once per week. Getting the things like quoted strings out of the way now is a plus.

Regards,

DeBug