Convert/Export to dBASE (.dbf)

Dear all,

I'm a complete beginner when it comes to MS SQL Server Management Studio Express 2005, having only downloaded it yesterday. I have a large dataset which is too big to fit into Excel or Access, but appears to fully displayn in Server. I need to convert or export this to a .dbf (dBASE IV) file but have no idea how to go about doing this. I've had a look around online, and see frequent references to FoxPro and BCP (I'm guessing that this is some kind of command line... I don't see how to open this though). I'm hoping to ultimately import the file into ArcGIS, so the output file would have to be compatible with this.

I'd be very grateful for any offer of help or suggestions, but please give me a step-by-step, dummies guide, clearly and fully explaining what you mean, as like I said, this one is new to me!

Thanks very much for your help

smurray444

BCP is the Bulk copy application which is available with the client toosl of SQL Server. The usage is pretty easy and well described in the BOL (Books Online, the help of SQL Server). You will have to provide a table name to bulk the data out, a servername, a password, a destination file and a format for the output (like comma delimited, Tab delimited etc.)

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Wow, DBase IV. That came out about 1987 or so, didn't it?

As far as I am aware, there is not a specific method to export or output a *.dbf formatted file from SQL Server (or BCP).

My first recommendation is to contact the vendor for ArcGIS, and request their assistance. I would hope that they have subtantial experience importing data from disparate sources into their product.

Otherwise, you may Google? "dbf file format" to determine how to create your own dbf file -IF you have to go that route. (I would exhaust the ArcGIS possibilities first.

Convert Varchar(10) to datetime

Dear Friends,

I'm having some problems to convert a varchar(10) to datetime. The problem is:

I have on table with some fileds, and I will talk only about 2. I have the field 1 that is a datetime with the format 2004-02-16 00:00:00.000 and filed 2 that is a varchar(10) with the format 16/02/2004.

I need to convert the field 2 to datetime to use the condition Field1>Field2

At the moment I have this:

SELECT RKFolder, RKData,CONVERT(DATETIME,CONVERT(VARCHAR(10), Maturity, 121), 121)AS Campo2

FROM FL_BondsSynthetic

And the error is:

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Could you help me?

Hopefully, this will give you the clue you seek:

DECLARE @.MyDate varchar(10)
SET @.MyDate = '16/02/2004'

SET dateformat dmy
SELECT convert( datetime, @.MyDate )

If you get an 'Out of Range' error, there is some non-conforming data that will have to be found and corrected.

|||I am moving this to the Transact-SQL forum, which can provide further insights.|||don't change the dateformat

specify the style instead

DECLARE @.MyDate varchar(10)
SET @.MyDate = '16/02/2004'
SELECT convert( datetime, @.MyDate, 103)sqlsql

Convert Varchar(10) to datetime

Dear Friends,

I'm having some problems to convert a varchar(10) to datetime. The problem is:

I have on table with some fileds, and I will talk only about 2. I have the field 1 that is a datetime with the format 2004-02-16 00:00:00.000 and filed 2 that is a varchar(10) with the format 16/02/2004.

I need to convert the field 2 to datetime to use the condition Field1>Field2

At the moment I have this:

SELECT RKFolder, RKData, CONVERT(DATETIME, CONVERT(VARCHAR(10), Maturity, 121), 121) AS Campo2

FROM FL_BondsSynthetic

And the error is:

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Could you help me?

Hopefully, this will give you the clue you seek:

DECLARE @.MyDate varchar(10)
SET @.MyDate = '16/02/2004'

SET dateformat dmy
SELECT convert( datetime, @.MyDate )

If you get an 'Out of Range' error, there is some non-conforming data that will have to be found and corrected.

|||I am moving this to the Transact-SQL forum, which can provide further insights.|||don't change the dateformat

specify the style instead

DECLARE @.MyDate varchar(10)
SET @.MyDate = '16/02/2004'
SELECT convert( datetime, @.MyDate, 103)

convert timestamp to datetime

Dear all,

Could anyone advise how to convert timestemp to Datetime value using T-SQL?

for exmple of timestamp 0x00083D9C95BBF180 and I want to have it in readable datetime.

Many thanks

LG

you have to use convert

ex.

declare @.x timestamp

select @.x = convert(timestamp,getdate())

select @.x, convert(datetime,@.x) as readabledatetime, convert(smalldatetime,convert(datetime,@.x)) as readablesmalldatetime|||You couldn't convert timestamp to datetime. Timestamp is a row version not datetime of row change. When you add or update row timestamp increase
|||

AS Konstantin indicates, the timestamp datatype has nothing to do with date and time values. It is a sequential binary number that increments upon any change to the database.

It is useful for managing sequence, or determining if data has been altered. But you can't get datetime from it...

|||arnie and konstantin are correct, we'll just have to ask LG how he implemented the timestamp field or variable that he had to come up with a requirement of converting timestamp to a datetime|||

I'm use this statement.

CONVERT(CHAR(19), GETDATE(),20)

Convert Text to Time

Dear all,

I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.

Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?

If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?

Thank you

GeorgeSQL Server is pretty good about implicitly converting text to time. If the time for is really odd, then you may need to use the CONVERT function or create a custom function.

Can you do this for all 30 tables in a single query? MAYBE using a union query, but don't count on it.|||I am not using SQL Server but Microsoft Access.

I time had an odd format but i have managed to manipulate it to the format 00:00:00.

I only need to convert the field from text to date/time. I have tried convert() and CDate() but i cannot make it to work.

My table is named "01" and the field "Duration".

Can anyone provide me with the full code to make the change.

I will use "Macros" and "RunSQLQuery" to make the converion for all tables.

This is the last problem i have to solve in order to make it work. I have been working on this database for the last week.

Please note that i am new in SQL with MS Access. I have started only one moth ago|||I would suggest that you post this question in the MS-Access (http://www.dbforums.com/f84) forum. They have more experience with the Access GUI and might be able to give you better suggestions.

I tried using Cdate("13:23:45") in an Access query, and it worked nicely for me. I suspect that that is the conversion that you need, but I'm not certain about what code you need to derive a properly formatted time string.

-PatP|||Ok, I thought of something else.

There is no need to change the data type of the table. I just created a query with all the fields of the table but for Duration i put: CDate([Duration]). The query returns the results as Date/Time and from there i can perform the calculations i require.

It works.

Thanks again, problem solved.|||Gee, you just have to love it when you inadvertanly solve a problem!

-PatP

Convert Text to Time

Dear all,

I have 30 tables with the same stracture (01 to 30). One of the fields is duration but has a text data type.

Is there a way to convert the duration field into "Time" date type with format "Long Time" using one query only?

If i have to have one query for each table, can i create a new query or a procedure through a command button that runs all the queries?

Thank you

GeorgeFirst, you will need to use the CAST or CONVERT SQL function to make your text a datetime field (assuming you need the seconds in the time otherwise smalldatetime will work also). Then use the DATEPART function to return just the time portion of the field.|||I am new in SQL and the book i use doesn't offer much help in th convert function.

Can you give me the sql statement to convert the field duration ofthe table 01 from text to datetime?

Thanks|||MSDN is a big help. See the documentation on Convert() (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp) either online or in your copy of Books Online (which is installed as part of the SQL client tools).

-PatP|||I still can't figure it out.

I use the following:

UPDATE 01
set CONVERT (datetime, Duration);

and

UPDATE 01
set CAST (Duration as datetime);

I get a syntax error after (|||You'll have to post at least a few lines of code for me to be able to help you. I can't figure out what you want from what you've posted so far.

-PatP|||My table is named "01".

One of the fields is "Duration".

The table is imported from a csv file. The Duration is not in a valid date/time format so i have to import it as "Text", modify it and convert is to "Date/Time".

I can easily do this from the design view of the table but since i have 31 similar tables, i require a faster way (i don't want to go through all 31 tables to make the changes, it takes too long). The fastest way i can think is through SQl.

So, it would be much apreciated if someone can provide me with the command to update the date type of "Duration" field from "Text" to "Date/Time" using SQL or a Macro.

I will use the command to do the same change for all 31 tables.

This is the last problem i have to solve before i can make the database work.

Convert string to datetime - Performance

Dear Friends,

I have a doubt relation when converting a field…

I ‘m converting a string to datetime inside a SQL command in an OLE DB Source using this:

convert(datetime,[Maturity],103) As MaturityDate

It’s better to use inside the OLE DB Source or is better to convert it with data conversion transformation?

Regards!!

Thanks

Probably just a matter of opinion. SSIS can do datatype conversions, but has to create a new column in the pipeline to hold the new type, which is a little bit inefficient. I generally don't like things "hidden" in the OLE DB Source, but in this case I think that's what I'd do.
|||

But witch of these options is more fast?!

Regards!

|||

PedroCGD wrote:

But witch of these options is more fast?!

Regards!

I think SQL would be faster. And because I hate being wrong, I tested it before I said that. I averaged the times for three runs of 20,000 rows and SQL appears 12% faster than SSIS.
|||

PedroCGD wrote:

But witch of these options is more fast?!

Regards!

Why don't you try it and tell us. You're in the best position to judge because its your data.

-Jamie

|||

I'm agree with you jamie!! :-)

What a question I made... eheheh... I can see the time in the logs of the cube!!!

Sorry...

Tomorrow I will give you the response!! thanks both!!

|||Someone help me?!|||

PedroCGD wrote:

Someone help me?!

Umm, with what? Call 911? |||

sorry was teh wrong post!!

And relating to this post, the better choice is converting the date in OLE DB SOurce.

Thanks!

|||

PedroCGD wrote:

sorry was teh wrong post!!

And relating to this post, the better choice is converting the date in OLE DB SOurce.

Thanks!

Good to knw (and as expected). Thanks Pedro.

convert SQL 2005 back to SQL 2000

Dear Sir,
Our new test environment running MS SQL 2005. We have finished an
application and need to migrate that to a live server which run SQL 2000.
I have tried enough but with no luck. How can I convert our new SQL 2005
back to 2000 server? The new database contain table schema and quiet some
initial data already. How should I do this?
regards,
Guoqi Zheng
http://www.ureader.com"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ur
eader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
There's no real easy way to do this.
Best bet is to script out the schema, bulk copy out the data and rebuild on
the new box.

> regards,
> Guoqi Zheng
> http://www.ureader.com|||You have to script out all of the objects and then transfer the data using
either BCP, DTS, or SSIS. A 2005 database can not be loaded into 2000 and
it can NOT be downgraded.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ur
eader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
> regards,
> Guoqi Zheng
> http://www.ureader.com|||It can be downgraded by setting the DB options to 80 compatibility level IF
the DB doesn't use any of the newer 2005 features.
However, I think it is a mistake to not just upgrade the other server to
2005. Upgrading takes so much work, and if the DB is already working in
2005, then why go through all the hassle downgrading and upgrading when that
same fundamental expense can be borne one time to just pay for the 2005
license.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ec7TGrEYGHA.3328@.TK2MSFTNGP02.phx.gbl...
> You have to script out all of the objects and then transfer the data using
> either BCP, DTS, or SSIS. A 2005 database can not be loaded into 2000 and
> it can NOT be downgraded.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:2ea036c11bb84a0eba336a26d25d5624@.ur
eader.com...
>|||Changing the database compatibility level will not allow Guoqi Zheng to go
back to SQL 2000. The on-disk database format will still be SQL 2005 and
will not be recognized by older versions of SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"AFN" <replywithingroup@.notreal.com> wrote in message
news:EL90g.3630$3W1.59@.tornado.socal.rr.com...
> It can be downgraded by setting the DB options to 80 compatibility level
> IF the DB doesn't use any of the newer 2005 features.
> However, I think it is a mistake to not just upgrade the other server to
> 2005. Upgrading takes so much work, and if the DB is already working in
> 2005, then why go through all the hassle downgrading and upgrading when
> that same fundamental expense can be borne one time to just pay for the
> 2005 license.
>
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:ec7TGrEYGHA.3328@.TK2MSFTNGP02.phx.gbl...
>|||If you're not using any SQL 2005 specific features, you can use
Transactional Replication to keep a warm SQL 2000 fallback option open.
Regards,
Greg Linwood
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ur
eader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
> regards,
> Guoqi Zheng
> http://www.ureader.com

convert SQL 2005 back to SQL 2000

Dear Sir,
Our new test environment running MS SQL 2005. We have finished an
application and need to migrate that to a live server which run SQL 2000.
I have tried enough but with no luck. How can I convert our new SQL 2005
back to 2000 server? The new database contain table schema and quiet some
initial data already. How should I do this?
regards,
Guoqi Zheng
http://www.ureader.com"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
There's no real easy way to do this.
Best bet is to script out the schema, bulk copy out the data and rebuild on
the new box.
> regards,
> Guoqi Zheng
> http://www.ureader.com|||You have to script out all of the objects and then transfer the data using
either BCP, DTS, or SSIS. A 2005 database can not be loaded into 2000 and
it can NOT be downgraded.
--
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
> regards,
> Guoqi Zheng
> http://www.ureader.com|||It can be downgraded by setting the DB options to 80 compatibility level IF
the DB doesn't use any of the newer 2005 features.
However, I think it is a mistake to not just upgrade the other server to
2005. Upgrading takes so much work, and if the DB is already working in
2005, then why go through all the hassle downgrading and upgrading when that
same fundamental expense can be borne one time to just pay for the 2005
license.
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:ec7TGrEYGHA.3328@.TK2MSFTNGP02.phx.gbl...
> You have to script out all of the objects and then transfer the data using
> either BCP, DTS, or SSIS. A 2005 database can not be loaded into 2000 and
> it can NOT be downgraded.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "guoqi zheng" <no@.sorry.com> wrote in message
> news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
>> Dear Sir,
>> Our new test environment running MS SQL 2005. We have finished an
>> application and need to migrate that to a live server which run SQL 2000.
>> I have tried enough but with no luck. How can I convert our new SQL 2005
>> back to 2000 server? The new database contain table schema and quiet some
>> initial data already. How should I do this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>|||Changing the database compatibility level will not allow Guoqi Zheng to go
back to SQL 2000. The on-disk database format will still be SQL 2005 and
will not be recognized by older versions of SQL Server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"AFN" <replywithingroup@.notreal.com> wrote in message
news:EL90g.3630$3W1.59@.tornado.socal.rr.com...
> It can be downgraded by setting the DB options to 80 compatibility level
> IF the DB doesn't use any of the newer 2005 features.
> However, I think it is a mistake to not just upgrade the other server to
> 2005. Upgrading takes so much work, and if the DB is already working in
> 2005, then why go through all the hassle downgrading and upgrading when
> that same fundamental expense can be borne one time to just pay for the
> 2005 license.
>
> "Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
> news:ec7TGrEYGHA.3328@.TK2MSFTNGP02.phx.gbl...
>> You have to script out all of the objects and then transfer the data
>> using either BCP, DTS, or SSIS. A 2005 database can not be loaded into
>> 2000 and it can NOT be downgraded.
>> --
>> Mike
>> http://www.solidqualitylearning.com
>> Disclaimer: This communication is an original work and represents my sole
>> views on the subject. It does not represent the views of any other
>> person or entity either by inference or direct reference.
>>
>> "guoqi zheng" <no@.sorry.com> wrote in message
>> news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
>> Dear Sir,
>> Our new test environment running MS SQL 2005. We have finished an
>> application and need to migrate that to a live server which run SQL
>> 2000.
>> I have tried enough but with no luck. How can I convert our new SQL 2005
>> back to 2000 server? The new database contain table schema and quiet
>> some
>> initial data already. How should I do this?
>> regards,
>> Guoqi Zheng
>> http://www.ureader.com
>>
>|||If you're not using any SQL 2005 specific features, you can use
Transactional Replication to keep a warm SQL 2000 fallback option open.
Regards,
Greg Linwood
SQL Server MVP
"guoqi zheng" <no@.sorry.com> wrote in message
news:2ea036c11bb84a0eba336a26d25d5624@.ureader.com...
> Dear Sir,
> Our new test environment running MS SQL 2005. We have finished an
> application and need to migrate that to a live server which run SQL 2000.
> I have tried enough but with no luck. How can I convert our new SQL 2005
> back to 2000 server? The new database contain table schema and quiet some
> initial data already. How should I do this?
> regards,
> Guoqi Zheng
> http://www.ureader.com

Convert gridview to report

Dear all,

May I know have anyone try to convert the gridview which bind to a sqldatasource to a report (any kind of report format)?

Thanks

HI

You can export gridview to excel for report, refer toExtensive Study of GridView Export to Excel for details.

Convert Function

Dear Friends,

I have a OLEDB Source and on it I have a convert(datetime,GetDate() ,102) AS RKData.

The field RKDATA returns me the value 2007-05-02 11:23:00...

How can I return only the date without time? Like:

2007-05-02 00:00:00...

Hi PedroCGD,

Either of these should work,

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()))) as RKData

or

SELECT CAST(CAST(YEAR(GetDate()) AS VARCHAR(4)) + '/' + CAST(MONTH(GetDate()) AS VARCHAR(2)) + '/' + CAST(DAY(GetDate()) AS VARCHAR(2)) AS DATETIME) as RKData

|||Add a derived column transformation to the data flow and use the following expression:

(DT_DBTIMESTAMP)(DT_DBDATE)([RKDATA])|||

Subhash,

It works. And How can I do If I want to increment 15 days to the getdate?

Thanks!

|||

PedroCGD wrote:

Subhash,

It works. And How can I do If I want to increment 15 days to the getdate?

Thanks!

Use the dateadd() function either in your query or in a derived column.

Just so you know, technically, your question should be in the Transact-SQL forum. That is the reason I've given you SSIS methods of solving your question -- to tie it to SSIS.|||

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()+15))) as RKData

resolve my question... dont need derivated column... I can include in SQL Statment of OLEDBSource

Thanks!

|||

PedroCGD wrote:

SELECT CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()+15))) as RKData

resolve my question... dont need derivated column... I can include in SQL Statment of OLEDBSource

Thanks!

This is a more appropriate statement because it works with date routines and is, frankly, more readable:

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(20), DATEADD("d",15,GETDATE()),101))
|||

in the datareader source... it works if the database is SQL Server, but in my case the database is SYBASE and I use ODBC in datareader...

The statment:

SELECT Folders.Folders_ShortName, Bonds.Bonds_Name, Currencies.Currencies_ShortName, BondsDeals.CaptureDate,
BondsDeals.ValueDate, BondsDeals.Quantity, BondsDeals.DealType

FROM (((Bonds INNER JOIN BondsDeals ON Bonds.Bonds_Id = BondsDeals.Bonds_Id)
INNER JOIN Folders ON BondsDeals.Folders_Id = Folders.Folders_Id)
INNER JOIN Currencies ON Bonds.Currencies_Id = Currencies.Currencies_Id)

WHERE BondsDeals.CaptureDate<CONVERT(DATETIME, FLOOR(CONVERT(FLOAT, GETDATE()+1)))
AND Folders.Folders_ShortName Not Like 'FID%'
AND Folders.Folders_ShortName Not Like 'FPS%'
AND Folders.Folders_ShortName Not Like 'VDR%'

The error:

"...An error occurred executing the provider SQL command."

"... INT is incompatible with DATETIME..."

|||

Yes, I think your tip is better... but my datareader (use to link via ODBC to SYBASE) doesnt work... if the database is SQL works, but in link to SYBASE, teh DataReader Source doesnt recognize DateAdd...

Do you have tips?

Convert from Access DB to SQL DB....

Dear All,

I would like to convert from Access To SQL DB undervisual Studio.Net 2005...

How can I do it easily, or if there any software to do this automaticlly, please your help..

Awaiting your valuable reply.

Many thanks in advance for your cooperation and continuous support...

What version of MS Access are you using?

Most of the Access versions like XP/2003 all have an upgrade wizard that allows you to convert your existing database to SQL Server.

In Access 2003 I believe you can use the Upsizing Wizard which can be accessed from Tools -> Database Utilities -> Upsizing wizard

Alternatively you can always import all the tables/data/procedures into SQL Server using Enterprise Manager/Management Studio.

|||

Microsoft guide you to good support for this:

The easiest way to convert an Access database to SQL Server is to use the Upsizing Wizard. The Upsizing Wizard:

?Preserves database structure, including data, indexes, anddefault settings.?Automatically converts Access validation rules and defaultsettings to the appropriate SQL Server equivalents.?Maintains table relationships and referential integrityafter you upsize.

To run the Upsizing Wizard in Access 2000, on theTools menu, point toDatabase Utilities, and then clickUpsizing Wizard.

You will got details information from his link

http://support.microsoft.com/kb/307598

Ahsan

|||

Thanks for your replying...

I've got Access 2007, and I've done this way.

However, it asks me to choose the Sql server and it gives me connection error when I use local !!!, and I'm using the Visual Studio.Net 2005 which has Sql Express Edition..

So, I would like to migrate the Access DB to my project in Visual Studio 2005 ( ASP.net -> C# language ).

I'd like you to know also that I haven't installed SQL Server 2005 or 2003. Do u think that I need it to do this?

Many thanks...

|||

ali Manshow:

However, it asks me to choose the Sql server and it gives me connection error when I use local !!!, and I'm using the Visual Studio.Net 2005 which has Sql Express Edition..

what is your SQLEXPRESS instance called as this should be the name you should be typing into access

ali Manshow:

I'd like you to know also that I haven't installed SQL Server 2005 or 2003. Do u think that I need it to do this?

SQLExpress is enough and you would not need to install SQL Server 2000/2005

|||

You should not install the SQL server 2005 because visual studio 2005 has built in Sql Server 2005 Express. So you can easily use this.For connect with sql express you simply use ./SQLExpress in server field.

Ahsan

|||

Thanks I have tried this, but it still gives me connection error because of wrong Sql Server or Access Denied..??

Do u think that I have to change the settings for SQL login:

There are 3 options:

1) Local Service.

2) Local System.

3) Network Service.

Which options shall I choose.

Or do I need to change any settings in Access 2007 DB.

Thanks so much...

convert dates

dear experts,
i'm working in a website in asp.net where all the data is coming from stored procedures, i have also a callendar, now what i want is that when a client click a date i want to send this value to the stored procedure and the query the database according to that.
i have a day, week, month selection, i did the following in C#
date1(if the client click on a day)
= Calendar1.SelectedDate.ToShortDateString(); this one is simple
but i want to know how to convert the date for the week and the date for the month so the query knows which date and the query the database according to that date.
for the week selection i have just the value stored in DateW = (just the week number) and for the month DateM = (month number 3) how can i convert these dates in sqlserver so the query knows for example if a month selected is march, then the C# value will be 3 and i want the query knows that this is march(from 01-03-2007 till 31-03-2007)?!!!
any suggestions will be very appreciated.
thanks experts.Don't send the bits and pieces of the date string to SQL Server. Convert them to a valic datetime value in C# and then send that to the sproc.|||Hi,

For the first date and the last date of a month check the article at http://www.kodyaz.com/content/FirstAndLastDaysOfMonth.aspx

But also the following select may help you for both month and week

SELECT
firstdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
lastdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1) ,
firstdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0),
lastdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)

Eralper
http://www.kodyaz.com|||thanks for the quick reply,
actually it can not be converted from int to datetime in C# because in C# it is Gregorian calendar which gives you just the week number and the month number, what i want is if there is a way to take this value which is dateM = 3 which is march and convert it in the stored procedure so the query will know that this is march?!!!!
thanks|||Hi,

Do you mean you have only the month and the year parameters?

If so, please check the following sql code,

declare @.y int, @.m int
set @.m = 12
set @.y = 2007

SELECT
firstdate_month =
CAST(
CAST(@.y as varchar(4)) + '/' + CASE WHEN @.m > 9 THEN CAST(@.m as varchar(2)) ELSE '0' + CAST(@.m as char(1)) END + '/01'
as datetime
),
lastdate_month =
DATEADD(dd,-1,
CAST(
CAST(
CASE WHEN @.m < 12 THEN @.y ELSE @.y + 1 END as varchar(4)
) +
'/' +
CASE
WHEN (@.m+1 < 10) THEN '0' + CAST(@.m+1 as char(1))
WHEN (@.m+1 > 9 and @.m+1 < 12) THEN CAST(@.m+1 as varchar(2))
ELSE '01'
END +
'/01'
as datetime
)
)

Eralper
http://www.kodyaz.com|||yes it is something like that, but i already have the query for the database and two parameters, the daystamp is one of them. how can i combine the SP i have and the one you give me? plus how do i need to do the week date, in C# i have just the week number, how can i tell to the SP which date to query the database for example dateW = 13 which is the week 13 of the year?!!
thanks my friend for the help,
good looking|||Hi,

I prepared the following script for the week problem. This is a little bit different when compared with the month version.

But this has to be modified for dates if the week is not in the current year.

declare @.w int
set @.w = 1

SELECT
DATEADD(
dd,
(@.w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
),
DATEADD(
dd,
(@.w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)
)

Eralper
http://www.kodyaz.com|||thanks man for the help,
last question :) i have already a stored procedure that have 2 parameters one of these parameter is the date, how can i combine the sp i already have with the 2 SP you gives me, the @.w is the one i need to give to my stored procedure to query the date, so how can i send the value from the C# code convert it with your sp and then feed it to the my sp to query the database according to the converted date?!!!

thanks