Prior to ASP .Net I use to create a connection object for SQL Server in my global.asa file and I would reference the obj whenever needed. how can I do the same in asp .net.
Here is my old asp sample:
Global.asa file:
Sub Session_OnStart
set session("conn") = createobject("adodb.connection") session("conn").Open "PROVIDER=MSDASQL;DRIVER={SQL Server};SERVER=xxx.xxx.xxx.xxx;DATABASE=xxxxx_db;UID=xx;PWD=xxxxxxxx;"
End Sub
Call from Other Pages:
sqlstr = "select * from members WHERE memberid = '" & session("mbr_memberno") & "'" set objRS = session("conn").Execute(sqlstr)
Hi forum, thought about time I updated from OleDB to SqlClient. My remote host company supports MS SQL Server 2005, I have created a testsql.mdf, now I want to connect to that database,( im used to writing Access conn like below and have updated my OleDB code to Sql ). <%@ Import Namespace="System.Data"%><%@ Import Namespace="System.Data.OleDb" %> strAccessConn ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:\inetpub\vhosts\blablabla\httpdoc\App_Data\testdb1.mdb"; How do I write the SQL connection string? <%@ Import Namespace="System.Data"%><%@ Import Namespace="System.Data.SqlClient" %> strSqlConn =?????? this bit ????? This is the remote host path LocalSqlServer data source=.SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true This my first SQL data application so need some help thanks Paul
I have downloaded some code on how to upload images to a sql database and I am getting this error message
"SQL Server does not exist or access denied. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: SQL Server does not exist or access denied."
I have opened Enterprise Manager and made sure I was a "user" and everything seems to be ok there. The connection string looks like this "server=localhost;uid=sa;pwd=;database=ImageUpload" Could anyone give me an idea on what to check or try next
When I moved my SQL server to another machine within my LAN, it stopped functioning. server=(192.168.1.3)\DB;IntegratedSecurity=True;uid=;pwd=;database=AspNetDb
Could someone kindly give me some advice? I appreciate your assistance.
Does anyone know how to create trusted connections?. What I want to do is to have connection to a sql server that's in a different domain as I am (a NT workstation). I tried to create a login id on the server with my nt id but got an error:
Hi Forum, I am having issues with hosted Sql05 server conn. Ive made the move from Oledb to Sql, so am unsure if trouble is permissions or connection related! Exception Details: System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection. <connectionStrings> <remove name="LocalSqlServer" /> <add name="ConnectionString" connectionString="Data Source=**.**.**.**;Initial Catalog=***_Database.mdf;Integrated Security=True;" /> </connectionStrings> <system.web> <roleManager enabled="true" defaultProvider="SqlRoleProvider"> <providers> <clear /> <add name="SqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ConnectionString" /> </providers> </roleManager> Ive tried some conn varients re security id, but its always the same error. I can use SQL Server Management Exp to login/manage database, using SQL authentication. The web app is the conn I cant open, hosting company support useles, now thats something new, cheers P
Just installed AJAX for VS2005 and started to build a AJAX enabled web site. I got the following message after creating a database (SQL) and Dataset "FOLLOWING ERROR OCCURED WHLE GETTING CONNECTION STRING INFORMATION FROM CONFIGURATION:'CANNOT GET WEB APPLICATION SERVICE'. I didn't have this problem before,can it be related to AJAX? Thank you in advance,
I have IIS4/ASP on one server (Nomad) and SQL7 on another server (Reliant) in a rack. I am attempting to retrieve records from a table.
I am using this dsnless connection script and I am receiving a login failure: http://204.84.96.168/wcc/trustees/default.asp
set con1 = createObject("ADODB.connection")con1.open "Provider=sqloledb;Server=204.84.96.99;Database=wc c;UID=andy;PWD=sterling;" set rs=con1.Execute (sql)
I have set up a new login called andy with the above password and associated the login with the wcc database and the one table that I want to pull data from AND given the login db_creator permissions and checked all of the "select, edit, delete. etc" options.
However when I view who has OWNER status of the wcc database it still reflects the user dbo. Could this be the problem? If so, how do I fix it?
I just realized I have this question. Let's say you are building a simple package that just loads some data from a flat file into a database table.
And let's say that many people will be logging on to the server to run this package from the file system, (so each person will have their own Window's login.) Some people will have rights to read/write to tables database, but some will not. They just need to be able to run the package.
So, when you set up the OLE DB Destination conn mgr, should you choose Windows Authentication or SQL Server login?
Because what if someone does not have permission to write to the database? Won't the package fail to execute since it's relying on the Window user's login to for the OLE DB conn mgr?
I have a Save method in my class file that connects to my sql db to 4 datasets, which I then wrap together. I am currently instantiating 4 different Sql Client connections to do this. Would it be more efficient to use just one?
Yesterday, i changed my sql connection string and i added ;
AttachDbFilename=|DataDirectory|\dbase.mdf
And i moved mdf file to project's debug directory by using cut-paste. I deleted dbase from databases section in SQL Managament Studio. And i attached dbase according to new path(project debug path).
After these, problem started to occur. If i open a table in SQLMS i cant start debugging my application, it throws exception " Cannot open user default database .login failed. "
And when i start my application , i cant use SQLMS to see my tables or to run query.It says ; ...cannot be opened due to inaccesible file....
It seems like only 1 application can use my db at a time. But before i moved my database, i could both open tables in SQLMS and i could start running my application at the same time
I have a project (using classic ASP & SQL Server) which adds one execute sql statement at a time to a temporary array, and then I join that array with a chr(30) (record separator), to a string variable called strSQL. I then run the following line of code:
conn.execute(strSQL)
I was wondering if there was any limitation to how large the strSQL variable can be? Reason I ask is because thru log writes I can see all of my sql execute lines exist in the variable strSQL prior to running the "conn.execute(strSQL)" command; however, not all of the lines run at the time of execution. Remember, this bug only is occuring whenever I have say over 600 sql lines to execute.
My understanding is that there was no limitation on the size of the string strSQL; however, in the interest of getting the bug fixed quick enough, I decided to just run a loop for each sql statment and run "conn.execute(strSQL)" every 50 times. This, in turn, has solved the problem and I do save all of my data; however, my original bug still exists.
Does anyone know why I have to split the sql commands and execute.com every 50 times instead of just being able to do it once ?
I found out that I could use SQLDMO to enumerate objects in a SQL server. However, I can't connect to my server, even though I have both "windows and SQL server" authentication on "(local) Windows NT".
Here is the code (it does not contain the validation in this snippet):
Imports System.Data.SqlClient Imports SQLDMO
Public Class ADO_2 Inherits System.Web.UI.Page Protected WithEvents txtQuery As System.Web.UI.WebControls.TextBox Protected WithEvents txtresults As System.Web.UI.WebControls.TextBox Protected WithEvents lstDB As System.Web.UI.WebControls.DropDownList Protected WithEvents lblResult As System.Web.UI.WebControls.Label Protected WithEvents cmdExecute As System.Web.UI.WebControls.Button Protected WithEvents cmdNext As System.Web.UI.WebControls.Button
Private m_objConn As New SqlConnection() Private m_objSQLServer As SQLDMO.SQLServer
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
EnumerateDatabases()
'TEMP lstDB.Items.Add("Northwind") lstDB.Items.Add("Pubs") 'END TEMP End Sub
Private Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Unload
m_objConn.Close() 'Just in case..... End Sub
Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click
Dim objCommand As SqlCommand Dim objDataReader As SqlDataReader Dim intField As Integer
objCommand = New SqlCommand(txtQuery.Text, m_objConn) objDataReader = objCommand.ExecuteReader
While objDataReader.Read txtresults.Text &= vbNewLine For intField = 0 To objDataReader.FieldCount - 1 txtresults.Text &= objDataReader(intField).ToString.PadRight(15) Next End While
m_objSQLServer = New SQLDMO.SQLServer() m_objSQLServer.Connect("radu")
'Iterate through all databases on the target server: For Each objSQLDatabase In m_objSQLServer.Databases lstDB.Items.Add(objSQLDatabase.Name) Next objSQLDatabase
EnumerateDatabasesExit: objSQLDatabase = Nothing m_objSQLServer.Close() m_objSQLServer = Nothing Exit Sub
EnumerateDatabasesError: lblResult.Text = Err.Number.ToString & ", " & Err.Description Resume EnumerateDatabasesExit End Sub End Class
Why can't I connect with m_objSQLServer.Connect("radu") ? I also tried m_objSQLServer.Connect("radu", "", ""). Otherwise, I can connect just fine with m_objConn.Open() (after I select a db from the dropdownlist, which is for now populated by hand)....
I have a unix shell script. I need to establish connection to the MS SQL server to execute packages and jobs. Could you please help me out on how to establish this connection as I am new to SQL server. I am using SQL Server 2000.
I have a package(SSIS) that works fine on a local SQL Server. Once I deploy it to the server A, and change the connection strings, it throws the following error:
Error: The AcquireConnection method call to the connection manager "DatabaseName" failed with error code xxxxx. Error: component "OLE DB Destination"(16) failed validation and returned error code xxxx.
Error: An OLE DB error has occurred. Error Code: 0x80040E4D. Al OLE DB record is available. Source: "Microsoft SQL Native Client" Description: "Communication Link Failure"
Error: Error: An OLE DB error has occurred. Error Code: 0x80040E4D. Al OLE DB record is available. Source: "Microsoft SQL Native Client" Description: "TCP Provider: An existing connection was forcibly closed by the remote host."
Error: Error: An OLE DB error has occurred. Error Code: 0x80040E4D. Al OLE DB record is available. Source: "Microsoft SQL Native Client" Description: "Login failed for user 'username'."
I have confirmed from my Network Engineer that this is not a Network issue. I can connect to the the target sql server from SQL Server A(where the package is running). The only thing that has changed is the connection string. In my package I connect to a local database on the server, and a remote SQL server. The connection to the local sql server works fine from the package. The remote SQL Server connection string is not working, I think. I also noticed that after I enter my password in the conn string, within the VS 2005, after building it, and deploying it on the server, I check back the conn string, and it is no longer there. I have read some posts about it, and I understand that SSIS hides it. What is the work around here? I have never used config files for SSIS, so I would need exact instructions to do that, if that is the way to go. Has someone else ever ran into this error? I have seen some posts on the forum with similar error, but they are not providing any solid answers. In my case, I need to know how to implement either a config file for conn strings, or how to deploy with conn strings which retains password. I can connect to a remote SQL Server from SERVER A utilizing SQL Management Studio, therefore any firewall/port issues are not relevant. I feel the issue is with conn strings, and I need some solid advise on how to implement them especially, where in my case, one of my connections is to a remote sql box, utilizing Sql server authenticaion(which involves use of password). Thanks.
Here is how my current connection string looks like and notice there is no attribute for password:
Data Source=192.1.1.1xxx;User ID=username;Initial Catalog=databasename;Provider=SQLNCLI.1;Persist Security Info=True;
Now, here's the problem. In the preview screen, it shows only up to column 518 correctly. In column 519, it shows the remaining hundreds of columns all glommed together as one big string, like: "data", 123, 10/17/2007, "more data", etc
Anyways, I am wondering what to do about this?
When I attempt to run the data flow I get this error: [Flat File Source [1]] Error: The column delimiter for column "Column 519" was not found.
However, the good news is that I only need the first 9 columns of the file. Some preprocessing in order, maybe?
Hello again,and thanx again -I now found something unexpected - that hopefully is the cause of myproblem? :In Man. studio security anmeldungen (engl. login names or users oraccounts or so?) right clicking on any user and properties, status
Hello everyone, I have the sql connection string configured in my web application as data source=(local); provider=sqloledb.1; user id=<<user id>>; password=<<password>>; database=<<database>>
My understanding is that when (local) is used, the connection method will fall to shared memory / named pipes. I have names pipes and TCP/IP enabled in the configuration manager.
My machine is also running Comodo Firewall .
Now this is what is happening. When I try to access the asp.net application, I get a component exception in comodo firewall that indicates w3wp.exe is trying to access the sql server via the machine's IP over port 1433. This happens when I try to load the application in the browser.
What I dont understand is that when (local) is configured, it should only access thru shared memory / named pipes. Why is it trying to connect thru TCP port. Is this because I have the sql username and password? My SQL server is configured for mixed mode authentication.
i have configure a SSIS package in BIDS to use an Env. Var. to store the location of the .dtsconfig file for the connection to my SSIS configuration table in SQL. I than use another Env. Var. to retrieve the source conn. str. from my SQL SSIS configuration table. Once my package loads, i select a simple sql task to execute, but it comes back and says that it cannot make the connection. How does the SQLServer connection string know what password to use? This is where i'm confused. I believe it loads the connection string for my source database but it doesn't get the password. I just started working in SSIS and i'm trying to get my head around all of the dynamic options of SSIS. I appreciate your time and help.
Problem Details: package A has 1 sql execute task Env.Var. 1 stores the location of the .dtsconfig file that stores the connection data to ssis sqlserver configuration table (used to store additional package properties in sql server) Env. Var. 2 stores the connection string of my source database. package protection level set at "EncryptSensitiveWithPassword" the sql task is assigned to the source connection string,right click and execute task.
here is the error: Failed to acquire connection "source database" Connection may not be configured correctly or you may not have the right permission on this connection.
I'm executing this from within BIDS just to check that my setup is going to work.
Here are some items i've tried or troubleshoot:
1. If I roll back the 2 package Configs or disable using package configs and replace my original source connection manager and create a new connection manager than i can get the sql task to successfully execute again. It won't usually work by just disable the package configs. i had to delete the existing source connection manager.
2. create a new package trying the same steps as above.
3. change the package protection level to "encryptSensitiveWithPassword" expecting this to retain the source connection password. I tried a variety of package protection levels.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection
Newbie here, so thanks in advance for any help. SQL Server 2000 on Windows 2000, Mixed Mode authentication IS enabled.
System DSN set on 8 workstations, a mix of 2000 - 98 and XP Pro
App uses DSN connection, specifies sa user and password. DSN's all set up the same, Client config is TCP/IP, SQL Server also set to only accept TCP/IP.
Trying to authenticate using SQL Server Authentication. XP Pro machines generate the above error. 98 and 2000 machines connect flawlessly.
I am stumped. Have read the MS knowledgebase articles, read threads here. Nothing seems to apply. It's an Internet app, so using Windows Authentication is not desired. I just don't understand why XP pukes while the other MS platforms connect just fine.
If anyone has any thoughts, I'd appreciate suggestions!
Hi I have written a piece of code for Login form which reads the user id and password from db. It works fine with the Sql server 2000 but I get a error with Sql server 2005. SqlConnection conn = new SqlConnection("Data Source=D\SQLEXPRESS;Initial Catalog=model;Integrated Security=True"); SqlCommand cmd = new SqlCommand("Select * from JsLoginDetails", conn); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if ((Login1.UserName == dr.GetValue(0).ToString()) && Login1.Password == dr.GetValue(1).ToString()) { Response.Redirect("MainJs.aspx"); } else { Login1.FailureText = "Invalid Userid Or Password"; } } dr.Dispose(); conn.Close(); } I get and error Invalid object name 'JsLoginDetails'. pls help thnksdiv
Here's an interesting problem. I have to set up connection managers for some files. The thing is, sometimes the files have data in them, sometimes not.
The files that don't have data in them just have some header info, so the file isn't technically empty, but I won't want to load these files when they're empty.
What would be an approach to solving this problem? I can't eliminate the file based on file size, since it's not 0, and there is no set file size that would be a reliable threshold, since they're small files to begin with.
