Data source name not found and no default driver specified

I know this may be a simple problem, but here it is:

I have SQL server express installed on Windows 2003 server (Testing) it works fine;

When I move it to my production server (Windows 2003) I get the error message"Data source name not found and no default driver specified "

The system DSN in ODBC connections works fine, or at least completes the test ok.

My application is written in Classic ASP

PLEASE HELP

Thanks

Can you provide your connection string?|||"Driver={SQL Server};Server=localhost;Database=TEST;Uid=MyUser;Pwd=MyPass;"

Data source name not found and no default driver specified

I know this may be a simple problem, but here it is:

I have SQL server express installed on Windows 2003 server (Testing) it works fine;

When I move it to my production server (Windows 2003) I get the error message"Data source name not found and no default driver specified "

The system DSN in ODBC connections works fine, or at least completes the test ok.

My application is written in Classic ASP

PLEASE HELP

Thanks

Can you provide your connection string?|||"Driver={SQL Server};Server=localhost;Database=TEST;Uid=MyUser;Pwd=MyPass;"

Data Source

I have a problem connecting Visual Studio 2005 to my DataBase file. I've downloaded northwind.mdf . Three weeks ago I installed SQL express and I used Visual Studio in the following manner: From server Explorer tab I choose Add connection.. , Change data source and I selected Microsoft SQL Server Database File (SqlClient), next I've selected my NORTHWND.MDF and when I pressed Test connection , it succeded. Recently, I've purchsed SQL server developer edition. The problem is when I follow the steps above I reveive:

An error has occurred while establishing a connection to the server, When connecting to SQL Server 2005, this failture may be caused by the fact that under the default SQL Server does not allow remote connections. ( provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

So , I've checked and changed remote connections to allow TCP/IP , but I receive the same error.

I've set up Visual Studio to SQL Server Instance Name : NAME , where NAME is the name assigned to my SQL Server. Worthless

The thing is it is working in another way : From server Explorer tab I choose Add connection.. , Change data source and I selected Microsoft SQL Server (SqlClient) , next I've selected server name to NAME , then Attach a database file . Test connection : succeded . This is because I do not have the northwind database attached directly in my SQL server ( and I do not want to ) .

Why with Developer edition I do not receive the same results ? Where is the mistake?

Thank you

Connecting to a database is a feature available only in SQL Server Express edition, but not in any other SQL Server version. This feature intention is to allow database applications development without the need to have a DBA who attaches the database files. For more information I would recommend to visit SQL Server 2005 Express Edition User Instances (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsse/html/sqlexpuserinst.asp).

If you want to use SQL Server developer edition, I would recommend attaching the database to SQL Server. For more information on this topic you can visit the following link: How to: Attach a Database File to SQL Server Express (http://msdn2.microsoft.com/en-us/library/ms165673.aspx).

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Data retrieving or querying problem

Hi, I am using visual web developer 2005 express edition and Microsoft SQL 2005 to develop a fast-food ordering website and I am having trouble in retrieving data from a database table into a form. Can some one please teach me the way to write the syntax forretrieving or selecting a inserted value from the database? I have only know the syntax to insert data value from a form which is something like this:

'Create a New Connection to our daabase

Dim testAs SqlDataSource =New SqlDataSource()

test.ConnectionString = ConfigurationManager.ConnectionStrings(

"Connectionstring1").ToString'This is the SQL Insert Command

test.InsertCommand =

"Insert into Customer([Initial],[Correspondent_Name],[Correspondent_No],[Payment_Type]) VALUES (@.Initial,@.Correspondent_Name,@.Correspondent_No,@.Payment_Type)"'Each of this insert a value into the appropriate command

test.InsertParameters.Add(

"Initial", DropDownList4.SelectedValue)'This is the selected "Initial" DropDownList value

test.InsertParameters.Add(

"Correspondent_Name", TextBox10.Text)'This is the Correspondent_Name value

test.InsertParameters.Add(

"Correspondent_No", TextBox3.Text)'This is correspondent_no value

test.InsertParameters.Add(

"Payment_Type","Pay upon delivery")'This indicates that the payment is made with credit card

test.Insert()

But I have no idea the syntax to retrieve a data. Please help me with this matter as it is important to me. Thanks in advance!

Regards,

Ivan

Hi,

There are many ways to retrieve data from a database table into a form. It depends on what kind of controls would you like to bound with.

If you want to display the data in a table, the easist way is to use a GridView control and assign its DataSourceID with the SqlDataSource control's ID.

Another way is to use DataView to retrieve the set of data.eg:

DataSourceSelectArguments ar =new DataSourceSelectArguments();DataView dv = (DataView)SqlDataSource1.Select(ar);this.TextBox1.Text = dv.Table.Rows[0]["CategoryName"].ToString();

In this way you can retrieve the value of any row any column manually.

Thanks.

|||

One of the ways how can you select data from database is this:

Imports System.Data.SqlClient

Dim test As SqlConnection = New SqlConnection()
test.ConnectionString = ConfigurationManager.ConnectionStrings("Connectionstring1").ConnectionString
Dim command As SqlCommand = test.CreateCommand()
command.CommandText = "SELECT * FROM Customer"
Dim GV1 As GridView = New GridView()
test.Open()
GV1.DataSource = command.ExecuteReader()
GV1.DataBind()
test.Close()
form1.Controls.Add(GV1)

Data processing extensions on SQL Server Express Edition?

I developed nice reports using custom data processing extensions. When I deployed the reports on my report server (I am using the express edition of SQL Server 2005) I was surprised to see that my reports were not rendering successfully.

After searching the web, I found this page listing the supported/unsupported features of SQL Server 2005 Express Edition: http://msdn2.microsoft.com/en-us/library/ms365166.aspx

On this page it clearly says “The Reporting Services API extensible platform for delivery, data processing, rendering, and security is not supported.”

Is there a way to get my reports to work on the express edition?

If not, which minimal version of SQL Server should a buy to get it to work (workgroup, standard or enterprise)?

Thanks for your help.

You will need Standard or Enterprise edition:

http://msdn2.microsoft.com/en-us/library/ms143761.aspx

Data problem when publishing website

I published my website directly to its production folder then opened Sql Express Management Studio and attached the ASPNETDB file to it in that folder. However, the location information displayed in the Databases window shows the file is actually mapped back to my development folder. ?

Has anyone else encountered this problem?

Yes, I did verify that I selected the correct location.

Okay, my mistake, it isn't actually mapped back to that file, it just names the database with the original path. Gotta wonder what genius thought that one up.

Data migration from MSAccess to SQL Express 2005

Hi ,

I have a requirement to migrate the data from an existing MS Access database to a newly designed SQL Express 2005 database . Need less to say the table structures in both are totally different.I would like to know how can i handle a scenerio where i want to map table A in access to table B in SQL express (the schema of both different and the number of columns can vary too) , how do i migrate the data from table A in Access to Table B in SQL express using SSMA?

Also i would appreciate if some one can tell me is SSMA the right tool for this , or should i use the upsizing wizard of MS Access. The constraint here is that the data needs to be migrated to a completely new schema. I just need to migrate data only and no other objects.

Thanks

Mahesh

Hello,

I am not replying here with any solution as such.

I would like to do same thing.

I have built complete application using MS Access 2003. Some of the highlights of this application are:

Customized login for each user without using User Level Workgroup Security features.

Each user is assigned 1 of 10 different roles. One of the roles is Admin role

Only Admin role has access to database window and all objects like tables, queries, forms, macros, modules etc.

Shift key is disabled so no one can access database window.

Admin can enabled shift key and get temporary access to database window. Shift key gets disabled on exit again.

Application has data capture front-end forms, one-click reports, quick query tool using front-end forms without query grid etc.

Only certain role can add new data, only certain role can edit data, data gets locked after certain time or status of data etc. Only ceratin role can upload/downlaod data etc.

Application is also password protected. Regular user can open application without knowing password as password is integrated in vba code. This password is essential as no one can export data from other database.

Currently all tables are stored in seperate database and linked in main application.

I would like to move all tables to SQL Server Express. I am assuming that by doing this I will be able to secure all tables better and it will also help me increasing size of application beyond 2 GB.

Please let me know step by step process to move Access Tables to SQL server express.

Thanks

|||

Hi Mahesh,

refer http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1056639&SiteID=1 which is answered.

Welcome on a board Adukio,

using SSMA you may migrate your Access DB to SQL 2005 Refer the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1056639&SiteID=1

I would suggest to refer this thread too http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1033679&SiteID=1

Hemantgiri S. Goswami

|||

Hemant,

Thanks for the reply. In fact i had downloaded the SSMA and trying a few things. I don't see a option where in some kind of a column mapping can be done in this tool. What i mean is that :

Table1 (Access Tale) Table2 (SQL Express Table)

Column1 Column1

Column2 Column2

Column3

If we assume a scenerio like the one above where i need to migrate table from a access table to SQL table , if the number of columns do not match (this is very much possible as my target schema has been completely redesigned) , SSMA fails to migrate the data. So i need to know is there a provision for handling a scenerio like this in SSMA?

Thanks

Mahesh

|||

Hi Mahesha,

SSMA does not handle data transformation, which is what you're wanting to do. (The Access Upsizing Wizard doesn't do this either.) The only SQL tool I know of that can do this is SSIS, wich is not included in SQL Express. If you have another version of SQL Server 2005 available, say SQL Dev, you can use SSIS to create a data transformation.

If you don't have another edition of SQL available, you will need to do this manually. I would suggest migrating the data from Access to a new database on your SQL Server, and then use queries to transform the data into your new tables. If this is a process you have to do regularly you can probably work out a process of pulling data into temporary tables and then appending them to your new schema all using Stored Procedures.

Mike

|||

Thanks Mike, I think that answers my query. The data migration is a one time activity here , so i think i don't really need to use temp tables here. May be i'll go with queries and stored procedures.

Thanks!

Mahesh

Data Looking for a Good Home - Where Might It Be?

Been coding mainframes 25 years; just started learning this platform. Fish out of water.

Have 2005 VWebDeveloper Express and 2005 SQL Betas. I have a million bytes of data to test my asp.net app locally. 100 columns, 200 rows and 6 items within each row. The data grows by 1 row per day; app. 5000 bytes.

MSDN tutorials that come with VWD Express talk about a "Northwind SQL Server" being required to, at least, follow the tutorials. This is very unclear.

Q. What is Northwind? And is it in anyway relevant to testing a VWD Express app?

Q. What are you folks using for a server to test your apps locally?

Q. Where do you find the most clear, concise guide for learning how to use SQL 2005 Express from a VWD Express app?

Q. Would you use "XML Data Store" instead?

Thanks for your consideration.Q. What is Northwind?  And is it in anyway relevant to testing a VWD Express app?
A. Northwind is a sample database and only needed if you want to test the msdn examples or examples using this database.
Q. What are you folks using for a server to test your apps locally?
I use SQL Server Developer Edition or Express Edition on my Win XP Prof. .
Q. Where do you find the most clear, concise guide for learning how to use SQL 2005 Express from a VWD Express app?
A. You may take a look at codeproject.com or msdn.microsoft.com
Q. Would you use "XML  Data Store" instead?
A. Iwould use it only for small and not complex databases.

Data Insert via SQL Express using VB Issue

I've been doing a lot of research lately to determine the most secure way to issue an insert statement to insert my data into my SQL database. I'm coding this following using Visual Basic:

Protected Sub submitButton_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Dim strConnAs String ="server=.\SQLEXPRESS;database=C:\SJ-site\SJ\App_Data\SJDb.mdf; User Id=user; password=password"Dim MyQueryAs String ="Insert into sjTable (PostID, Category, TitleofPost, Description, Price, Loc, ContactInfo, DatePosted, Disclaimer) Values (@.Category, @.TitleofPost, @.Description, @.Price, @.Loc, @.ContactInfo, @.DatePosted)"Dim MyConnAs New Data.SqlClient.SqlConnection(strConn)Dim cmdAs New Data.SqlClient.SqlCommand(MyQuery, MyConn)Dim curDateAs String On Error Resume Next MyConn.Open()If Err.Number <> 0Then MsgBox("There was an error connecting to the database.", MsgBoxStyle.OkOnly) Err.Number = 0End If curDate = Now()With cmd.Parameters .Add(New System.Data.SqlClient.SqlParameter("@.Category", categoryDropDown.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.TiteofPost", titleTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Description", descriptionTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Price", priceTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Loc", locationTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.ContactInfo", contactTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.DatePosted", curDate))End With If Err.Number <> 0Then MsgBox("There was an error inserting the data into the database.", MsgBoxStyle.OkOnly) Err.Number = 0End If MyConn.Close()If Err.Number <> 0Then MsgBox("There was an erro closing the database connection.", MsgBoxStyle.OkOnly) Err.Number = 0End If Server.Transfer("submitted.aspx")End Sub

The code gives no errors, it directs me straight to thesubmitted.aspx page but when I query the database it is not insertingany data into my database. .

My Error log shows the following:
2006-06-27 12:14:16.68 spid51 Starting up database 'C:\SJ-SITE\SJ\APP_DATA\SJDB.MDF'.
2006-06-27 12:14:26.93 Logon Error: 17828, Severity: 20, State: 3.
2006-06-27 12:14:26.93 Logon The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: <local machine>]

I am able to log into the database using my account via SQL Server Management Studio Express. I've been all over the forums and google looking for a solution but everything I've been trying to mock up from other people's code hasn't helped. Thanks in advance for your assistance

Hi, do you have 17190 error in your SQL ERRORLOG when SQL startup?

2006-03-24 10:37:09.80 Server Error: 17190, Severity: 16, State: 1.
2006-03-24 10:37:09.80 Server FallBack certificate initialization failed with error code: 1.
2006-03-24 10:37:09.81 Server Warning:Encryption is not available, could not find a valid certificate to load.

And is your SQL service startup account a local account whose password has been changed recently?

If so, try to delete the file in folder

C:\Documents and Settings\<SQL Server startup
account>\Application Data\Microsoft\Crypto\RSA\S-1-5-21-963106725-2405722570-177647575-1023

The last part (S-1-5-21-963106725-2405722570-177647575-1023) may be different for
every account. Then restartSQL Server services to take effect.

|||I went ahead and upgraded to SQL Express 2005 SP1 w/ Advanced Services. I re-built my user accounts and what not. Now, I am still getting no error messages but also when I open the log file the last thing it says is:
2006-06-28 09:06:57.93 spid51 Starting up database 'C:\SJ-SITE\SJ\APP_DATA\SUGARJACKS.MDF'.

The data from the forms still isn't inserting into the database. The code hasn't changed and like I said the only thing I did was upgrade the SQL Express and re-build the user account.
|||On a side note I just wanted to say that yes I did change the database name and yes I did update the connection string accordingly in the code.

Data from Access to SQL 2005

Hello -
I'm trying to migrate data from an Access 2000 database to SQL 2005
Express programatically. Both databases have the same schema.
Most primary keys are Auto Increment fields. I'm having the issue that
even if I set primary key manually in my code, the Auto Increment rules
seem to override this. This is very undesirable, as it would corrupt
the relationships of existing data when I read from Access and Write to SQL.
How can I add rows to the SQL database bypassing the Auto Increment value?
i.e.:
ds_SQL.tblCompany.row(x).item("CompanyID") = ds_Access.tblCompany.row(x).item("CompanyID")
The data migrates, but the Auto Increment value is used. I'm using
typed datasets, ensured the ReadOnly value is False in the Dataset, and
even tried turning off Auto Increment at the Dataset level.
Thanks - I'm lost...
Wayne P.This is a multi-part message in MIME format.
--=_NextPart_000_040F_01C6C10F.1CE1AD20
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Add the following statement BEFORE each of your insert statements.
SET IDENTITY_INSERT dbo.MyTable ON
INSERT INTO dbo.MyTable
SELECT {ColumnList}
FROM {AccessTable}
When finished, determine the highest IDENTITY value and reset the =IDENTITY seed for the table.
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
"WPedersen" <wayne.pedersen@.no.spam.teksol.com> wrote in message =news:OIfLoXUwGHA.4416@.TK2MSFTNGP03.phx.gbl...
> Hello -
> > I'm trying to migrate data from an Access 2000 database to SQL 2005 > Express programatically. Both databases have the same schema.
> > Most primary keys are Auto Increment fields. I'm having the issue that =
> even if I set primary key manually in my code, the Auto Increment =rules > seem to override this. This is very undesirable, as it would corrupt > the relationships of existing data when I read from Access and Write =to SQL.
> > How can I add rows to the SQL database bypassing the Auto Increment =value?
> i.e.:
> ds_SQL.tblCompany.row(x).item("CompanyID") =3D > ds_Access.tblCompany.row(x).item("CompanyID")
> > The data migrates, but the Auto Increment value is used. I'm using > typed datasets, ensured the ReadOnly value is False in the Dataset, =and > even tried turning off Auto Increment at the Dataset level.
> > Thanks - I'm lost...
> > > Wayne P.
--=_NextPart_000_040F_01C6C10F.1CE1AD20
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Add the following statement BEFORE each =of your insert statements.
SET IDENTITY_INSERT dbo.MyTable =ON
INSERT INTO =dbo.MyTable
SELECT {ColumnList}
FROM {AccessTable}
When finished, determine the highest =IDENTITY value and reset the IDENTITY seed for the table.
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
"WPedersen" wrote in message news:OIfLoXUwGHA.4416@.TK2MSFTNGP03.phx.gbl...> Hello =-> > I'm trying to migrate data from an Access 2000 database to SQL 2005 > =Express programatically. Both databases have the same schema.> => Most primary keys are Auto Increment fields. I'm having the issue that => even if I set primary key manually in my code, the Auto Increment rules => seem to override this. This is very undesirable, as it would =corrupt > the relationships of existing data when I read from Access and =Write to SQL.> > How can I add rows to the SQL database bypassing =the Auto Increment value?> i.e.:> ds_SQL.tblCompany.row(x).item("CompanyID") =3D > ds_Access.tblCompany.row(x).item("CompanyID")> > The data migrates, but the Auto Increment value is used. I'm using > =typed datasets, ensured the ReadOnly value is False in the Dataset, and => even tried turning off Auto Increment at the Dataset level.> > =Thanks - I'm lost...> > > Wayne P.

---

--=_NextPart_000_040F_01C6C10F.1CE1AD20--|||WPedersen wrote:
> Hello -
> I'm trying to migrate data from an Access 2000 database to SQL 2005
> Express programatically. Both databases have the same schema.
> Most primary keys are Auto Increment fields. I'm having the issue that
> even if I set primary key manually in my code, the Auto Increment rules
> seem to override this. This is very undesirable, as it would corrupt
> the relationships of existing data when I read from Access and Write to SQL.
> How can I add rows to the SQL database bypassing the Auto Increment value?
> i.e.:
> ds_SQL.tblCompany.row(x).item("CompanyID") => ds_Access.tblCompany.row(x).item("CompanyID")
> The data migrates, but the Auto Increment value is used. I'm using
> typed datasets, ensured the ReadOnly value is False in the Dataset, and
> even tried turning off Auto Increment at the Dataset level.
> Thanks - I'm lost...
>
> Wayne P.
Look at identity_insert option in BOL.
Regards
Amish Shah
http://shahamishm.tripod.com|||Arnie / Amish:
Thanks!
This is what I needed!
Wayne P.
Arnie Rowland wrote:
> Add the following statement BEFORE each of your insert statements.
> SET IDENTITY_INSERT dbo.MyTable ON
> INSERT INTO dbo.MyTable
> SELECT {ColumnList}
> FROM {AccessTable}
>
> When finished, determine the highest IDENTITY value and reset the
> IDENTITY seed for the table.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "WPedersen" <wayne.pedersen@.no.spam.teksol.com
> <mailto:wayne.pedersen@.no.spam.teksol.com>> wrote in message
> news:OIfLoXUwGHA.4416@.TK2MSFTNGP03.phx.gbl...
> > Hello -
> >
> > I'm trying to migrate data from an Access 2000 database to SQL 2005
> > Express programatically. Both databases have the same schema.
> >
> > Most primary keys are Auto Increment fields. I'm having the issue that
> > even if I set primary key manually in my code, the Auto Increment rules
> > seem to override this. This is very undesirable, as it would corrupt
> > the relationships of existing data when I read from Access and Write
> to SQL.
> >
> > How can I add rows to the SQL database bypassing the Auto Increment
> value?
> > i.e.:
> > ds_SQL.tblCompany.row(x).item("CompanyID") => > ds_Access.tblCompany.row(x).item("CompanyID")
> >
> > The data migrates, but the Auto Increment value is used. I'm using
> > typed datasets, ensured the ReadOnly value is False in the Dataset, and
> > even tried turning off Auto Increment at the Dataset level.
> >
> > Thanks - I'm lost...
> >
> >
> > Wayne P.