Hi,
This might be obvious - but if so I've missed it so far.
Is it possible to connect remotely to an Express database used in a website which is created in the App_Data folder by VWDEE 2005 and attached by filename?
As far as I can tell these databases don't have a name and aren't mounted until an attempt is made to access them. I certainly can't see any sign of it in SQL Management Studio.
Thanks,
Dale
Hi all,
I have SQL Server Express and VB 2005 Express installed in a Microsoft Windows XP Pro PC that is a terminal PC in our office Network. My Network Administrator has granted my terminal PC for Administrator Use. I tried to do the ADO.NET 2.0-VB 2005 programming in my terminal PC and I could not get a remote connection in the Window Form Application via Window Authorization. I do not know how to make this kind of remote connection in my Network. Please help and advise.
Thanks in advance,
Scott Chang
I have recently installed SQL Server Express on a desktop machine for a program that we use. I am in need of adding additional machines to gain access to the server for the database. Right now, the program is working on the local machine fine. What do I need to do or enable for access on a local LAN? All machines are XP Pro on a workgroup. Also, the software we use has a remote connection manager that requires inputting the server address for the machine. Is it better to use the computer name or IP address, and then what is that format? SQL Instance name is 'uesafh' if that helps. Any other information that I can provide, please let me know.
Thanks!
Dear All,I have TWO PCs, one of them having SQL Server 2005 Expressi get sure the TCP is enables , Active, Remote conneciton is enabled via TCP onlyfrom VS2005 (C#) i can add new sqlconnection contorl and configure it with any of the installed DBsbut when i got to the Other PC with VS2005 as well, i can't configure itall i can do is to see the server inthe server list.. but NO databases are retieved at all in the Select database drop down menu any solution or suggestion
View 1 Replies View Related
I have installed SQL server 2005 express on my main server.
It has been installed with windows authentication.
I can use sqlcmd -S machinenameinstancename to connect to the server, but when I try to connect from another machine on my lan, I get the error about user not part of trusted sql connection msg 18452 level 14 state 1
I have tried using a windows username and password from my server to access it but that fails also.
Any help would be appreciated
I am getting this error
"An error has occurred while establishing a connection to the server.when connecting to SQL Server 2005,this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider:Named Pipes Provider,error:40-Could not open a connection to SQL Server)(Microsoft SQL Server,Error:2)"
in SQL EXPRESS 2005
1.Just changing the service config settings that is TCP/IP to enable will it work?i changed i run my application but it didn't work they have written the code to the server(connection string to their server- if it was to common how to write the code ?) & is there any other changes ?
2.I have developed a VB.NET EXE file -In that application(inside the code) i am using run time connection string queries to connect to the sql server database & created run time tables.
Suppose if i want to install the application to all clients/users how to Configure that application - self configuration/self installer to the installing machine SQL SERVER.
How can i do this ?
3.Is there any way i can write config file for sql server along with exe application code ? How can i do this ?
Pls answer with details/screenshots or diagramatical (if possible)
pls reply asap
Dotnetnewbie
Hi ,
After reading the SQLexpress weblog , I tried the solution and its still not working
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
The client computer gives me this:
Run-Time error -2147217843 (80040e4d)
Log-in failed for user "KitGuest"
The connection string in the ADO object in my testing VB6 program is:
my_connection.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;User ID=kit;Initial Catalog=AdelaideMushroom;Data Source=KITSQLEXPRESS"
I am confused with the error massage as my conncection string is set to connect on UserID = Kit
I am not sure what is gone wrong. The same VB6 program runs perfectly on host machine
I am not sure is there anything to do with SQL Config. Manager's SQL server 2005 service's Log-ON setting:
Log On as - Build-in account: Network service
could Anyone who managed to get remote connection work give me some advise?? I am desperate. have read that weblog many times and do not know how to get it work
I am new to VB.Net coming from a VB6 background using the VB.Net Express and have even loaded the SQL Express. The learning curve is not to bad until you get to dealing with connecting to db's. However, in trying to fill a grid with data, manipulate it and then update it has not been any fun either. There does not seem to be enough information out there dealing with these two as of right now.
Anyway, trying to add a datasource connection for a grid has resulted in a real problem even using the connection wizard. I keep getting the same error that the default settings for SQL express does not allow remote connections. However, I thought that I have corrected that. Unfortunately, with VB express I cannot even seem to talk to my SQL 2000 server either because it gives me the same exact error. I am thoroughly frustrated with it and ready to just do it in VB6.
I am open to any thoughts on this. I do not like using bound controls, except in a few situations. I would like to create it unbound if at all possible. If someone could give me some ideas as to what to do and where to go with this I would appreciate the help. I have been doing a lot of reading and a descent understanding what is needed to talk to a SQL db.
Thanks in advance.
hello this the message error how can I solve it thank you
View 4 Replies View RelatedHi,
I have written a database application using .mdf in visual studio 2005 using vb.net (Windows XP sp2)
The app runs fine on the local machine. But I want to access the database from another computer (remote connection)
My Questions:
1. How to host the database file (mydb.mdf) on server machine?
2. How does connection string change on the client side?
Any other idea?
THanks!!
Good day.
I am trying to look at a friends database that they have developed. Not having used SQL Server Express before I am experiencing problems trying to remotely connect to their database.
He connects locally just fine of course.
I get the error message about remote connectionsand the default settings so I have enabled TCP/IP and Named Pipes as well as cleared SQLSERVER Express in my firewall settings. I also stopped and restarted the service, but still get the same error message.
My connection parameters are like:
Server type: Database Engine
Server name: http://sql.exampledatabase.net (should http be here? have tried with and without same error)
Authentication: SQL Server Authentication (windows authentication? If so, how do I change to such?)
Login: http://username (should http be here? have tried with and without same error)
Password: password
TITLE: Connect to Server
------------------------------
Cannot connect to http://sql.exampledatabase.net
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
Any help would be much appreciated.
BurntBack
posted to the forums a few weeks ago, followed the suggestion but the problem has not been squared, the link for my original post is at the bottom of this message.
have tried most of the suggestions in the forums, again see the link for details on steps i have tried, kept poking around and came up with a few more possibilities for the problem,
the error message is the one that says sql server express remote connnections are not enabled by default during the install of sql express,
backed up a little and it seems i can not even connect to the local machine, i ran the sqlcmd -s -e sqlexpress and the login timesouts and says that sql express refused the connection, have shared memory protocal and tcp/ip running also tried it with named pipes,
so that left me wonderin if maybe permission for sql express need to be adjusted, brought up SQL server Studio Mangement Express console and went to the only express instance on the local machine, brought up properties for sqlexpress and went to the permission section and noticed the following:
for the BuiltInadministrator and builtInUser account the only permission that is checked off is the "Conect SQL" which has only the Grant checkbox checked, also the grantor is sa, btw what does the checkbox With Grant do?? this was the same for the both accounts,
for the public account the only permission that is enabled is the View any database,
are there other permission which need to be granted or "with granted", also i assume i should not be using the sa account or should i?
other things going on include include Visual Studio 2005, i am able to get to the database thru Server Explorer, and can bring up the tables and see them, sometimes thos their is a red x on the mdf, but when i click it brings them up.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=217258&SiteID=1
Hi
have sql server express 2005 installed on a laptop running XP sp2 with IIS installed. installed sql express during the visual studio 2005 install
having problems with remote connection errors messages when using aspnet_regsql.exe as well as when trying to view pages with sql db involved. in most tests to fix this i disable my firewall, still getting error messages tho
sqlBrowser is running as is sqlexpress in services both on auto
in the surface area configuration tool, the remote connection is set to local and remote connections is using both tcp/ip and named pipes.
in sql config manager- shared memory, tcp/ip, and named pipes are enabled for both the server and the client, the order on the client is shared memory then tcp/ip then named pipes
inside the server's tcp/ip on the ip tab, IP1 is set to active, enabled, the ip address is 192.168.1.41 which is different from the laptop ip (192.168.1.39), there is no port number for IP1, should there be a port number?? dynamic port is 0
IP2 is set to active, enable with address 127.0.0.1 and also does not have a port number, dynamic port is 0
IPAll is using dynamic port 1232 but also has no port number.
for the client TCP/IP setting the default port is 1433 and it is also enabled.
also downloaded and intalled SSMS-EE and under properties for the sql server >connection, the allow remote connection box is checked. I am using the default instance sqlexpress, there is another instance microsoftbcm (what is that??) and it also has the allow remote connection box checked.
server security is set to windows authentication
keep getting
clues appreciated, take care
I have Microsoft SQL express server 2005 setup on one server and I have a website setup on another server, I am trying to get some data from the SQL server on the other one.
I am currently getting this error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) I have done plenty of searching on Google, and already been through the tutorial on changing the setting in ‘SQL Server 2005 Surface Area Configuration’ to allow remote connections
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspxhttp://support.microsoft.com/default.aspx?scid=kb;EN-US;914277
It works perfectly when I call data from my local machine.
Also I don’t know if it is a timeout issue, because I tested in on two other servers I have access to and it worked on one of them.
Any help would be very much appreciated.
Thanks in advance.
I followed the steps outlined in a blog I found to setup SS2005 express for remote connections. Everything appeared to go smooth until I actually tried to logon ;)
I have Sql Server 2005 Express installed on my WinXP PRO machine at home (no domain). I have opened the ports on the firewall, etc.
When I try and login from the SQLCMD command line tool I get a Login Failed error. Here is the connection string that I'm trying to use:
SQLCMD -S **.***.**.*SQLEXPRESS -U Steve -P MyPassword
The error:
Login failed for user 'Steve'.
When I use Management Studio Express on the same machine running Sql Server, it shows the User name: NEVERSTILLSteve
'NEVERSTILL' is the name of my computer.
If I try and use that same username with a remote connection I get the same error.
I have googled for the error and msg code, but nothing fitting my situation is coming up.
Anyone have any ideas for me?
Hello,
2 weeks ago, I installed a SQL Server 2005 Express on one of my computer. I fought to configure the remote access, but finnaly it did work.
Yesterday I had to move my server on another computer and I've never succeeded in configuring the remote access. I don't understand why it is so complicated to install remote access.
I explain you what I did, and if you could say me what i missed, it could be amazing !!
I install SQL Server 2005 Express including the SQL Server Management Studio Express.
I went to the SQL Server surface area configuration and i enable Remote Access (TCP) and turn on the SQL Browser
I enabled Server authentication => SQL Server and Windows Authentication mode.
I created users with all the permission i can give to them.
I restart one million time all the services and nothing...I tried to connect to the database using my local Microsoft SQL Server Management Studio Express, using the Visual studio Sever Explorer and with C# script using this connection string :
sConnectionString = " Server=myIP\SQLEXPRESS; Initial Catalog='myDatabase'; user id = myLogin; password = myPassword";
And I've never succeeded in connecting, I have always the same error :
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)which means the database is not reached.
Please tell me i've just missed a stupid thing.
hi ,all
what should i do to connect db file on the server ,
at first i putted the file in shared folder and i connected from server explorer .
iam not satisfied with this solution i want more professional one (ODBC,......,ect)
what is the best way .
My Tools are ;Vista OS,VS2008,SQlServer2005.
Thanx.
Hi,
I am having problem with remote connection via TCP/IP connection on default port 1433. TCP/IP connection are enabled on the server.
any possible solution ?
See problem below:
TITLE: Connect to Server
------------------------------
Cannot connect to ns1.iandigroupltd.com,1433.
------------------------------
ADDITIONAL INFORMATION:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10060&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
Hello,
I've been looking around in several SQL-Forums and in SQL Books Online but haven't found clear suggestions how to tackle my problem:
Our company has to fill a database with documentation for our contractor. Our subcontractors have to supply information from their branch/craft into this database, too.
Replication (without determining a type at this time) would do fine for data-exchange between us and our subcontractors.
But for working in different companies the networks are not connected
and in some companies/departments connection to the Internet is prohibited at all.
This would result in data-exchange based on files - which wont be a problem as data needs to be updated/exchanged only once a week and is usually done at meetings using DVD-Rs.
scenario in short:
- two-way exchange of data between SQL servers being not connected
- update needed once a week
- our server: SQL Server 2005 Standard (preferred)
- servers of our subcontractors: SQL Server Express 2005 (preferred)
That's the point where I dont know how to proceed:
Is two-way replication based on files possible at all?
I read about Snapshots, but to my understanding this replaces the "whole database" without caring about changes made to it and that's why it wont work for two-way exchange.
I would appreciate your hints how to solve this problem in general or whether I should look out for other solutions - MS Access might work with restrictions - but I would prefer SQL Server.
Thanks for reading until here and in advance for your answers.
Regards,
Michael__H
Hey all!
I've tried all I know in an effort to get SQL 2005 Express to allow remote connections on my customer's 2K3 server and I'm not having any luck. I've followed MSKB 914277, including adding the exceptions to their Windows firewall...even disabled the firewall altogether (for testing purposes), and I still can't get remote clients to connect. As suggested by various other posts, I used netstat -an to confirm that SQL is listening on port 1433 for the desired IP on the server.....and saw that it is listening. Here's a snippet:
TCP 192.168.0.2:1433 0.0.0.0:0 LISTENING
TCP 192.168.0.2:1433 192.168.0.2:1738 ESTABLISHED
TCP 192.168.0.2:1738 192.168.0.2:1433 ESTABLISHED
TCP 127.0.0.1:1433 0.0.0.0:0 LISTENING
I can connect to the database locally (i.e. on the 2K3 server itself) using just the IP address .... tried with a DSN, UDL, ODBC, and SSMSE...all of which have no problems (as shown by the two, "ESTABLISHED" entries in the netstat results above which were from running SSMSE locally on the server at the time of the netstat). But if a remote computer on the network tries to connect, they cannot connect. Sorry, I don't have a copy of the error message the clients are getting yet.
I asked The customer's IT to confirm that the clients CAN can "see" the server (i.e. that there wasn't some kind of router or firewall blocking their path) and they sent me a copy of the server's pfirewall.log file. Here is an entry from the file that seems to show that client requests ARE getting to the server:
2007-12-17 19:12:13 OPEN-INBOUND TCP 192.168.0.4 192.168.0.2 1213 1433 - - - - - - - - -
2007-12-17 19:12:13 CLOSE TCP 192.168.0.2 192.168.0.4 1433 1213 - - - - - - - - -
I'm starting to get in over my head here (I'm a programmer, not a DBA or Net Admin), but doesn't the above suggest that an incoming request came in from .4 on port 1213 and attempted to connect to .2 on port 1433, but the connection was denied or dropped by the server? If so, why? Is this related to another MS KB I read about connection pooling? I'm way over my head here and could use some help. Did I miss something subtle during configuration of SQL Express?
Thanks in advance.
I'M HAVING AN ISSUE, UNDERSTANDING, THE CONNECTION STRING.
I WANT TO CONNECT TO AN INSTANCE OF SQLEXPRESS ON A REMOTE SERVER WITH A FIXED IP ADDRESS
THE TCP PORT IS OPEN TO 1433
I OPENED THE PORT ON THE ROUTER AND THE WINDOWS 2003 FIREWALL
MY CODE IS AS FOLLOWS:
S = "Provider=SQLNCLI;"
S = S & "DATA SOURCE=44.66.777.888,1433SQLEXPRESS;"
S = S & "INITIAL CATALOG=TESTDB;"
S = S & "Persist Security Info=false;"
S = S & "UID=TEST999;"
S = S & "Pwd="TEST999888;"
CnMgt.ConnectionString = S
CnMgt.Open S
I'VE FOLLOWED THE STEPS OUTLINED IN ARTICLE 914277
CAN SOMEONE HELP?
THANK YOU
PS. IF THE CLIENT IS LOCAL, I HAVE NO ISSUE OPENING THE DATABASE.
I DO NEED TO OPEN THE DB FROM FROM CLIENTS.
Hi All,
We have a file based asp.net app built using Visual Web Developer and Sql Server Express 2005. We have finished development and testing and are now moving to the deployment stage. As a first step, we would like to be able to view it on a test machine using IIS (instead of VWDs built in web server). We have created a virtual directory in IIS and can view our app correctly at http:localhost/ForIIS_test.
However, when we get to a page that tries to access our Sql Sever Express database, we get the following error:
An attempt to attach an auto-named database for file C:Documents and SettingsClaudeMy DocumentsVisual Studio 2005WebSitesForIIS_testApp_Data estDatastore.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Does anyone know how to overcome this problem? Any help appreciated.
Claude.
I have implemented a package to load multiple files to a destination. Since the source was a txt file, i have created as flat file source. However now we are getting files in excel format as well.
Is there anyway the source gets changed dynamically based on the file extension, output of the foreach file enumerator? I can think one solution to have 2 dataflow tasks based on precedence constraining and expression one is for .txt and other one is for .xls.
Hi all,Just wondering if SQL 2005 express edition can be connected remotely. I've set up SQL 2005 Express on a separate machine and am having trouble connecting to it from VWD when creating the websites. Currently I've set up ASP.NET 2.0 Framework on the remote machine with IIS6 using Windows Server 2003 Web Edition. And I am directly editing the website remotely on the server machine from my PC. Could someone please help me in setting up a remote connection from VWD to the SQL Server?I've enabled TCP/IP setting in the SQL Configuration Manager but still getting errors. Could this be an issue with Express Edition? Because if it is I also have a copy of SQL Server 2005 to install. Any direction to resolve in this issue would be greatly appreciated.PS. I'm new to this so pls bear with me. Thanks.
View 1 Replies View RelatedHiI'm going insane.I have a club starter kit on my local server which is working fine.I have migrated the database to my remote host and seems to have transfered ok.For a brief period I was able to connect to the database remotely, but now I cannot I get the following, even though SQL Server Surface Area Config is set to accept remote connections.An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)This is my connection string;<add name="ClubSiteDB" connectionString="Data Source=xx.xxx.xx.xx,xxxx; Network Library=DBMSSOCN; Initial Catalog=tets; User ID=sqladminbob;Password=sqlbob4398;" providerName="System.Data.SqlClient="/>I hav also tried to use aspnet_regsql.exe to add the remote provider but I get the same message. Thanks
View 3 Replies View Related
When running the project in debug mode or non-debug mode, I get the following error from MS Visual Studio:
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Package [Connection manager "SYBASE_CONNECTION"]: The connection manager failed to defect from the transaction.
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
Any ideas???
When I create a new odbc connection to a SQL server 2005 Db I get a failure telling me dat de SQL server does not allow remote connections.
How can I allow the server to allow this.
Any help appreciated
regards
Hi,I have SQL Server Express Edition. I tried working out some ASP.NET Labs in my local system. Here is the link of the Virtual Lab which I tried. http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032286906&EventCategory=3&culture=en-US&CountryCode=USI recieve this error in my local system. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)I tried working out solutions from various websites. But the no solution is effective. Could anyone help me in solving this issue.
View 3 Replies View RelatedI'm getting "Access denied" error when I try to connect to SSIS from my desktop (Database Engine is fine). I'm a member of sysadmin within SQL, as well as an administrator on the server. I don't have any problem when I log on to the server directly via Remote Desktop and open SSIS.
I went into the surface area config tool, and I see that Database Engine and Analysis Services both expand and have a sub tabs to enable Services and Remote Connections, but Integration Services does not expand to a Remote Connections option, just Services. We're running MSDN Enterprise edition.
Does it matter if all the SQL services are using "Local System" for the logon ? I use an administrative logon on my SQL2000 boxes, and was fiddling with them on the 2005 box yesterday, but it seemed to cause more problems, so they are all Local System for now.
Am I missing something obvious ?
Hi all,
In my office computer network system (LAN), my Windows XP Pro PC has SQL Server Express and VB 2005 Express installed and I was granted to have the Administrator priviledge to access SQL Server Express. I tried to create a SQLCLR project in my terminal PC.
1) I tried to set up a remote connection to SQL Server Express in the following way: SQL Server EXpress => SQL Server Surface Area Configuration. In the Surface Area Configuration for Service and Connection - local, I clicked on Remote Connection of Database Engine, SQLEXPRESS and I had a "dot" on "Local and remote connections" and "Using TCP/IP only". Then I clicked on "Apply" and "OK" buttons. Then I went to restart my database engine in SQL Server Management. When I went to check SQL Server 2005 Surface Area Configuration, I saw the arrow is pointing to "Service", not to "Remote Connections"!!!??? Is it right/normal? Please comment on this matter and tell me how I can have "Remote Connecton" on after I selected it.
2) After I restarted the database engine (I guess!!), I executed the following project code (copied from a book) in my VB 2005 Express:
//////////////--Form9.vb---/////////////////
mports System.Data.SqlClient
Imports System.Data
Public Class Form9
Dim cnn1 As New SqlConnection
Private Sub Form5_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Compute top-level project folder and use it as a prefix for
'the primary data file
Dim int1 As Integer = InStr(My.Application.Info.DirectoryPath, "bin")
Dim strPath As String = Microsoft.VisualBasic.Left(My.Application.Info.DirectoryPath, int1 - 1)
Dim pdbfph As String = strPath & "northwnd.mdf"
Dim cst As String = "Data Source=.sqlexpress;" & _
"Integrated Security=SSPI;" & _
"AttachDBFileName=" & pdbfph
cnn1.ConnectionString = cst
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Create a command to create a table
Dim cmd1 As New SqlCommand
cmd1.CommandText = "CREATE TABLE FromExcel (" & _
"FirstName nvarchar(15), " & _
"LastName nvarchar(20), " & _
"PersonID int Not Null)"
cmd1.Connection = cnn1
'Invoke the command
Try
cnn1.Open()
cmd1.ExecuteNonQuery()
MessageBox.Show("Command succeeded.", "Outcome", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cnn1.Close()
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'Create a command to drop a table
Dim cmd1 As New SqlCommand
cmd1.CommandText = "DROP TABLE FromExcel"
cmd1.Connection = cnn1
'Invoke the command
Try
cnn1.Open()
cmd1.ExecuteNonQuery()
MessageBox.Show("Command succeeded.", "Outcome", _
MessageBoxButtons.OK, MessageBoxIcon.Information)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cnn1.Close()
End Try
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'Declare FromExcel Data Table and RowForExcel DataRow
Dim FromExcel As New DataTable
Dim RowForExcel As DataRow
FromExcel.Columns.Add("FirstName", GetType(SqlTypes.SqlString))
FromExcel.Columns.Add("LastName", GetType(SqlTypes.SqlString))
FromExcel.Columns.Add("PersonID", GetType(SqlTypes.SqlInt32))
'Create TextFieldParser for CSV file from spreadsheet
Dim crd1 As Microsoft.VisualBasic.FileIO.TextFieldParser
Dim strPath As String = _
Microsoft.VisualBasic.Left( _
My.Application.Info.DirectoryPath, _
InStr(My.Application.Info.DirectoryPath, "bin") - 1)
crd1 = My.Computer.FileSystem.OpenTextFieldParser _
(My.Computer.FileSystem.CombinePath(strPath, "Book1.csv"))
crd1.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited
crd1.Delimiters = New String() {","}
'Loop through rows of CSV file and populate
'RowForExcel DataRow for adding to FromExcel
'Rows collection
Dim currentRow As String()
Do Until crd1.EndOfData
Try
currentRow = crd1.ReadFields()
Dim currentField As String
Dim int1 As Integer = 1
RowForExcel = FromExcel.NewRow
For Each currentField In currentRow
Select Case int1
Case 1
RowForExcel("FirstName") = currentField
Case 2
RowForExcel("LastName") = currentField
Case 3
RowForExcel("PersonID") = CInt(currentField)
End Select
int1 += 1
Next
int1 = 1
FromExcel.Rows.Add(RowForExcel)
RowForExcel = FromExcel.NewRow
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
MsgBox("Line " & ex.Message & _
"is not valid and will be skipped.")
End Try
Loop
'Invoke the WriteToServer method fo the sqc1 SqlBulkCopy
'object to populate FromExcel table in the database with
'the FromExcel DataTable in the project
Try
cnn1.Open()
Using sqc1 As SqlBulkCopy = New SqlBulkCopy(cnn1)
sqc1.DestinationTableName = "dbo.FromExcel"
sqc1.WriteToServer(FromExcel)
End Using
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
cnn1.Close()
End Try
'Read the FromExcel table and display results in
'a message box
Dim strQuery As String = "SELECT * " & _
"FROM dbo.FromExcel "
Dim str1 As String = ""
Dim cmd1 As New SqlCommand(strQuery, cnn1)
cnn1.Open()
Dim rdr1 As SqlDataReader
rdr1 = cmd1.ExecuteReader()
Try
While rdr1.Read()
str1 += rdr1.GetString(0) & ", " & _
rdr1.GetString(1) & ", " & _
rdr1.GetSqlInt32(2).ToString & ControlChars.CrLf
End While
Finally
rdr1.Close()
cnn1.Close()
End Try
MessageBox.Show(str1, "FromExcel")
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////
I got the following error messages:
SecurityException was unhandled
Request for the permission of type 'System. Security. Permissions.FileIOPermission,mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'failed that is pointing to the code statement "Dim int1 As Integer = InStr (My.Application.Info.DirectoryPath, "bin")."
Troubleshooting tips:
Store application data in isolated storage.
When deploying an Office solution, check to make sure you have fulfilled all necessary requirments.
Use a certificate to obtain the required permission(s).
If an assembly implementing the custom security references other assemblies, add the referenced assemblies to the full trust assembly list.
Get general help for the exception.
I am a new Microsoft VS.NET Express user to do the SQL Server 2005 Express and VB 2005 Express programming by using the example of a tutorial book. Please help and tell me what is wrong in my SQLCLR sep-up and project coding and how to correct the problems.
Many Thanks in advance,
Scott Chang
Hi. I'm (very) new to SQL Server. I have run into what seems to be a very common problem, judging by the number of Google hits, but it's not actually in the FAQs here, as far as I can see.
Situation
I installed SQL Server Express with Advanced Services 2005 on my Windows XP Pro laptop, using (with one exception) the default install. The exception was to put the data files on a separate partition (D: ) where I have more free space.
SQL Server Management Studio Express connects happily, and I set up a single data import table.
Issue: bcp Fails to connect to default local instance
I initially tried bcp in output mode, prior to working in earnest on importing my data. However the command line-
bcp MyDatabaseName.dbo.UserProfile out UserProfile.txt -T -c
Fails with the error-
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
As I understand it, the local instance of SSE is the default, and should use a local (shared memory?) connection. This seems to work for Management Studion Express, but not bcp.
While investigating this problem, I tried enabling remote connections (all types) through the Surface Area Configuration wizard, and found that remote connections using either of:
bcp eSpacePh1.dbo.UserProfile out UserProfile.txt -T -c -S localhostsqlexpress
bcp eSpacePh1.dbo.UserProfile out UserProfile.txt -T -c -S mycomputernamesqlexpress
both work ok.
Extra Info
When starting Management Studio Express, it says something like "Configuring environment for the first time". However it says this every time it is started, not just the first run after installation, does this indicate some configuration data is not being successfully stored?
I have found threads that look similar to my issue-
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=836670&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3351349&SiteID=1
However these do not relate directly to bcp, and to be honest my knowledge of SSE is slender enough at this stage that i'm unsure if there are really relevant.
Any hints on how to proceed much appreciated.
Regards: Colin E.
Can VB 2005 express able to connect remote SQL 2005 express database?
I played with connection string many time and still could not success!
Thanks all.
I am thinking I must be doing something wrong..
I have dozens of packages that work as follows (high level... not listing all the steps just those relevant to this question)
- Get list of files in directory
- Join list to list of already imported files
- Those not imported put into an ADO.Net object
- Loop through ADO.Net record (which contains the filename) and import each file.
I just set the connection string of the flat file to be the variable in the loop (expressions.. connection string). Pretty standard stuff. Now I tried to do the same with a file connection (not a flat file) becuase I have a source that is from a mainframe and I had to write a custom source script and its not working. Basically the source script uses
oRead = oFile.OpenText(Me.Connections.FileConnection.ConnectionString)
And it opens the same file over and over (not ever changing as the ConnectionString expression changes like it does for flat files) and imports it even though I have verified the loop is correctly looping through all the different files.
Any thoughts as to what I am doing wrong?