Converting data from MySQL to SQL Server 2005

Hello,

Finally making the move to ASP.NET! I've been advised that to begin with it is probably best to start using SQL Server 2005 with ASP.NET. All my sites are currently using MySQL. Can anyone advise a way for me to import all the data from a MySQL database to a SQL Server 2005 database. Apologies if this isn't directly related to ASP.NET but any help would be greatly appreciated.

Thanks

Check out this page:

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx

|||

Perfect. Great help. Thanks a lot.

Converting Data from Access 2000 to SQL Server 2000

Hi,
I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.

Thanks.
HOW TO: Convert an Access Database to SQL Server

converting Crystal Reports to SSRS

I came across a website that claimed "There are approximately 116 functions in Crystal Reports that do not have a direct equivalent in VB.NET or Reporting Services (SSRS)." Does anyone know what these functions are?I know this is not a direct answer to your question, but we looked into a way to convert Crystal Reports to SQL Reports, but were unable to find a viable solution. There is an application called RPT2RDL that claims to convert some files, but it does not handle a good many functions even though some are supported by SQL Reporting Services. This has left us manually recreating the reports which takes a great deal of time.

Converted Access database gives problems due to field names

Hi

I have been working with asp.net against an Access database (created by someone else several years ago). Now I have to move the database to run under MSSQL Server. I successfully updated it via the Access migration tool, and it's running fine under MSDE.

However, many of the fields in one table are 'named' as 'numbers' - eg 101, 102, 34 etc. Therefore SQL commands such as:

update Mytable set 101 = "test" where ID = 2

fail with Incorrect syntax near '101'.

I would never set up such a database structure myself, but I am stuck with it. Is there any way to make MSSQL commands work with this sort of naming?

Thanks for any help you can give.

LeeYou could try:

update Mytable set [101] = "test" where ID = 2

I have not tried this with fields that are all numbers, but I expect it should work.sqlsql

convert word doc to pdf

I have to convert word doc to pdf file use asp net.

I think maybe it possible make report in the report services, and then save them in pdf format.

but I dont know if I can make report from doc file.
and also if I can get the url of the file by query string.

Is it possible?

thanks!try again......|||

Do you have static Word documents that need 1-time conversion, or do you have Word documents that represent report styles or definitions that you want to export to PDF regularly with different data? In the first case, I'd say RS is not the right tool. In the second case, I think you'll get good results by designing the report definition from scratch using Report Designer, using the Word documents as prototypes.

Convert text file format

Hi,

How can I convert a text file (.txt) into SQL in ASP.net 2.0 ? The sample of the file format is like that ...

09/03/2007 08:41 "Fung, Kitty" Granted Access D1 Main 2354 111

09/03/2007 08:42 "Ng, Jaclyn" Granted Access D1 Main 21906 18

09/03/2007 08:42 "Leung, Agnes" Granted Access D1 Main 21920 18

Cheers

Hi Stephen Ho,

How do you want to convert this text file into SQL?

|||

This is not standard text file (comma delimited or fixed fields) so you have to create your own parser so write application which will :

1 read line of text

2 parse line to fields (if you need data in fields)

3 insert fields into table row (or whole line as single field content)

4 check if next line exists and if yes go to step 1

but maybe you can change the format how your events are logged to file for example to comma delimited format and it will be easier to import it to SQL using SSIS package.

Convert IP Address to Long

Hi, can I use a SQL server Stored Procedure to convert an IP Address to Long?

In VB.NET I use the following code (hope that helps).

Private Function ConvertToLong(ByVal IPAddress As Object) As Object
Dim x As Integer
Dim Pos As Integer
Dim PrevPos As Integer
Dim Num As Integer
If UBound(Split(IPAddress, ".")) = 3 Then
' On Error Resume Next
For x = 1 To 4
Pos = InStr(PrevPos + 1, IPAddress, ".", 1)
If x = 4 Then Pos = Len(IPAddress) + 1
Num = Int(Mid(IPAddress, PrevPos + 1, Pos - PrevPos - 1))
If Num > 255 Then
ConvertToLong = "0"
Exit Function
End If
PrevPos = Pos
ConvertToLong = ((Num Mod 256) * (256 ^ (4 - x))) + ConvertToLong
Next
End If
End Function

Here's a UDF that should do what you are looking for.

Usage: SELECT dbo.fnStringIPToLongIP('192.168.0.1')

CREATE FUNCTION dbo.fnStringIPToLongIP (@.IPAddress AS varchar(15))
RETURNS bigint AS
BEGIN
DECLARE
@.x Integer,
@.Pos Integer,
@.PrevPos Integer,
@.Num Integer,
@.ConvertToLong bigint
SET @.ConvertToLong = 0
IF LEN(RTRIM(REPLACE(@.IPAddress,'.',''))) = LEN(RTRIM(@.IPAddress))-3
BEGIN
SET @.X = 1
SET @.PrevPos = 0
WHILE @.X <= 4
BEGIN
SET @.Pos = CHARINDEX('.',@.IPAddress,@.PrevPos + 1)
IF @.x = 4
SET @.Pos = Len(@.IPAddress) + 1
SET @.Num = SUBSTRING(@.IPAddress, @.PrevPos + 1, @.Pos - @.PrevPos - 1)
If @.Num > 255
BEGIN
SET @.ConvertToLong = '0'
SET @.X = 5
BREAK
END
SET @.PrevPos = @.Pos
SET @.ConvertToLong = ((@.Num % 256) * CAST(POWER(256,(4 - @.x)) AS bigint)) + @.ConvertToLong
SET @.X = @.X + 1
END
END
RETURN(@.ConvertToLong)
END

Terri|||Thanks a million!!!|||FYI...The EasyWay.NET

Dim lng As Long = System.Net.IPAddress.Parse("192.168.0.1").Address

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

Convert Date in SELECT (remove a Time)

I have the following SELECT statement on a ASP.NET page. The problem is
that the field CreatedOn contains a value like 12/9/2004 HH:MM:SS AM but all
I care about is the actual date. Is there somekind of SQL Convert function
where I can just read the date itself? "Today()" doesn't return actual time
and I don't want it. If I do that, the SELECT will never find records. I
simply want to match dates. I tried CDate(CreatedOn) but that of course
didn't work.
TIA!
SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
CreatedOn = Today()There's a section about this in http://www.karaszi.com/SQLServer/info_datetime.asp. Note that it is
impossible to return date only as datetime datatype. Datetime always include date and time. You can
return a string, or sometimes set the time to 00:00:00.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:ul4F%239f3EHA.208@.TK2MSFTNGP12.phx.gbl...
> I have the following SELECT statement on a ASP.NET page. The problem is
> that the field CreatedOn contains a value like 12/9/2004 HH:MM:SS AM but all
> I care about is the actual date. Is there somekind of SQL Convert function
> where I can just read the date itself? "Today()" doesn't return actual time
> and I don't want it. If I do that, the SELECT will never find records. I
> simply want to match dates. I tried CDate(CreatedOn) but that of course
> didn't work.
> TIA!
> SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
> CreatedOn = Today()
>
>|||See my answer in another thread:
Stored procedures and date comparison
--
http://www.aspfaq.com/
(Reverse address to reply.)
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:ul4F#9f3EHA.208@.TK2MSFTNGP12.phx.gbl...
> I have the following SELECT statement on a ASP.NET page. The problem is
> that the field CreatedOn contains a value like 12/9/2004 HH:MM:SS AM but
all
> I care about is the actual date. Is there somekind of SQL Convert
function
> where I can just read the date itself? "Today()" doesn't return actual
time
> and I don't want it. If I do that, the SELECT will never find records. I
> simply want to match dates. I tried CDate(CreatedOn) but that of course
> didn't work.
> TIA!
> SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
> CreatedOn = Today()
>
>|||One way is to use DatePart function
SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
DatePart(yyyy,CreatedOn) = DatePart(yyyy,GetDate()) and
DatePart(mm,CreatedOn) = DatePart(mm,GetDate()) and
DatePart(dd,CreatedOn) = DatePart(dd,GetDate())
"D. Shane Fowlkes" <shanefowlkes@.h-o-t-m-a-i-l.com> wrote in message
news:ul4F%239f3EHA.208@.TK2MSFTNGP12.phx.gbl...
>I have the following SELECT statement on a ASP.NET page. The problem is
> that the field CreatedOn contains a value like 12/9/2004 HH:MM:SS AM but
> all
> I care about is the actual date. Is there somekind of SQL Convert
> function
> where I can just read the date itself? "Today()" doesn't return actual
> time
> and I don't want it. If I do that, the SELECT will never find records. I
> simply want to match dates. I tried CDate(CreatedOn) but that of course
> didn't work.
> TIA!
> SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
> CreatedOn = Today()
>
>|||you can use convert as follows
convert(varchar(12),CreatedOn)=convert(varchar(12),getdate())
athi.
"D. Shane Fowlkes" wrote:
> I have the following SELECT statement on a ASP.NET page. The problem is
> that the field CreatedOn contains a value like 12/9/2004 HH:MM:SS AM but all
> I care about is the actual date. Is there somekind of SQL Convert function
> where I can just read the date itself? "Today()" doesn't return actual time
> and I don't want it. If I do that, the SELECT will never find records. I
> simply want to match dates. I tried CDate(CreatedOn) but that of course
> didn't work.
> TIA!
> SELECT [ID] FROM PurchaseOrders WHERE Status = 'I' AND POTypeID = 1 AND
> CreatedOn = Today()
>
>
>