i have a loop that can run say 30k times. in there i am using the try / catch / finally blocks to handle ADO.NET Transactions (thanks CADDRE!)
Should i open and close the SQL Connection for each record? or open it and close it outside of the loop only once ?
thanks in advance,
mcm
Hi most of my code follows the following format in a lot of my pages, my question is; Am i approaching it the right way in terms of performance, sql injection attacks and anything someone more knowledgeable than myself can think off, please feel free to criticise the code. Thank youprotected void Page_Load(object sender, EventArgs e)
{string strID = Request.QueryString["id"];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand command = new SqlCommand("stream_EventsByID", conn);
command.CommandType = CommandType.StoredProcedure;command.Parameters.Add("@eventID", SqlDbType.Int).Value = Request.QueryString["id"];
conn.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
eventList.DataSource = reader;
eventList.DataBind();
conn.Close();
}
}
If Someone could please show me some example VB code where i can open the my Sqlconnection in the Page_Load subroutien... and then close that SqlConnection in the Page_Unload routine. I want to be able to execute Sql command without having to re-open and re-close the connection.
Thanks,
Greg
I am preparing a monthly cash flow statement and for doing so, I need to determine the monthly opening and closing cash balance. To simplify, this is what I did.
1. Dragged months to the columns labels
2. Created a slicer for selecting the Financial Year. In this slicer, I chose 2015-16 i.e. April 1, 2015 to March 31, 2016.
Since I have data only for two months of this Financial Year i.e. April and May, only these two months show up in the column labels.
To determine the monthly opening cash balance, I added the following measure
=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),PREVIOUSMONTH(calendar[Date]))
To determine the monthly closing cash balance, I added the following measure
=CLOSINGBALANCEMONTH(SUM(bank_book[Balance]),calendar[Date])
Much to my surprise, I got the result as seen in the image below. Cells B5, C6 and D5 are blank. On going through my Bank Book, I realised that
1. There is no figure in cell B5 (Opening balance of April) because there was no transaction on the last day of March i.e. March 31. The last transaction was on March 28
2. There is no figure in cell C6 (Closing balance of May) because there was no transaction on the last day of May i.e. May 31. The last transaction was on May 30
3. There is no figure in cell D5 (Opening balance of June) - same reason as mentioned in point 2 above.
As seen in the image, I have also computed the monthly "Last date of previous month" and "last date of current month" but do not know how to make use of them in computing the opening and closing Cash balances.
Please also note that there can be multiple transactions on the last day of any month. For e.g., let's say the last day of transaction in May 2015 was May 30 (not may 31) but there were multiple transactions on this last day (both inflow and outflow).
So I cannot simply determine the last day of transaction and take MAX/MIN/SUM that day. I have to take the final balance on that day.
How to solve this problem i.e. in determining the opening and closing cash balances.
I've written a small dos batch script that runs a simple query throughosql and then calls an exe which sends an email if the returned value
Quote:
We're experiencing a problem where intermittently our SSIS packages will hang. There are no log errors or events in the event viewer. It will happen whether the package is executed from the SQL Job Agent or run from BIDs. When running from BIDs it appears to hang inside one of the data flows (several parallel pipes with sorts, merge joins etc...). It appears to hang in multiple pipes within the data flow component. The problem is reproducable, we just kill it and re-run, and it appears to hang in the same places.
Now here's the odd thing: as we simply open and close some of the components in the pipe line after the place it hangs, a subsequent run will go further in the pipeline before hanging. If we open and close all the components after the point it initially hung, the data flow will run fine, from there on out. When I say "open and close" I mean no changes are made, we simply double-click the component, like a merge join, then click 'close.'
To me this does not seem like a memory problem but likely something is wrong with the metadata, where opening a component and closing it somehow alters the metadata to "right it".
This seems to occur intermittently after we make modifications to the package. It's like if you make any mod, even unrelated to the data flow, you then have to go through and open and close every component in your package to ensure it will work. Again, no errors or warnings are fired.
Has anyone seen this type of problem?
I have a Table Having Date,Opening,Addition,Sale values where opening value comes in the very first row other times it is zero.
In ssrs how can i have a report showing closing value = Opening+Addition-Sale in current row (it is simple for 1st row ). this closing be the opening value in next row and same formula to be continued...
Date Opening AdditionTotalTank saleClosing Stock
01-11-15 14435 0 14435 8243 20627
02-11-15 0 15000 15000 9433
03-11-15 0 9000 9000 9436
04-11-15 0 12000 12000 8392
05-11-15 0 6000 6000 8157
06-11-15 0 12000 12000 8456
07-11-15 0 15000 15000 10903
08-11-15 0 6000 6000 8485
09-11-15 0 6000 6000 9413
10-11-15 0 21000 21000 6413
hello to alli am finding my self in confusing problem.
everyay log file of my application increase by 10 GB.every time i found more than 100 connections open in SDQL Server 2005 under my database.
i an using SQLHELPER Class by microsoft.i ma not putting my code in try-catch block. example
of one of my frequently used function is as follows:
protected Int64 GetMemberID() { String SqlSelect = "SELECT * FROM MemberMaster WHERE MemberUserName= '" +
Session["UserName"].ToString() + "'"; SqlDataReader dr1 =
SqlHelper.ExecuteReader(ConfigurationManager.ConnectionStrings["CowcallConnectionString2"].T
oString(), CommandType.Text, SqlSelect); while (dr1.Read()) { CurrentMemberID = Int64.Parse(dr1["MemberID"].ToString().Trim()); } return CurrentMemberID; }
well i doubt i am opening connection and do not closing it. moreover i do not know how to close connection in above code when i am using sql helper class.please give me yours suggestion regarding my code even if it is not solving above two problems.
I have a VB (sorry!) app using SQL Express DB. It was all created using the wizard so I didn't have to manually deal with opening connections etc.
Now, I need to be able to email the .mdf file from within the application, but I can't do this because the file is in use.
I have tried closing all forms with DB connections and then doing it but it takes around 6 minutes before the file becomes "free" and attachable to the email.
Can anyone suggest a way of releasing the file so that I can email it?
Many thanks,
Rich
I have setup my loop to loop through textboxes and fill the according textboxes with data it retrives, if seems to work fine, but there is a problem with opening and closing the connection below is my codeint i = 0;for (i = 1; i <= 3; i++)
{
//This gets the stock ID from the textbox.string stock_ID = ((TextBox)Panel1.FindControl("txtID" + i.ToString())).Text;
//This is the sql statement.string sql = "SELECT [n_or_sh], [title], [cost_price], [selling_price] FROM tbl_stock WHERE stock_ID = " + stock_ID;
//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn);
myConn.Open();
//This is a reader for the results to go in.SqlDataReader dr = sqlCmd.ExecuteReader();
//This reads the first result from the sqlReader
dr.Read();
//This sets the title label text to the value of the description column.TextBox currentBox1 = (TextBox)Panel1.FindControl("txtDesc" + i);
string strtxtDesc = currentBox1.Text;
strtxtDesc = dr["title"].ToString();
} // end of loop
myConn.Close();
} // end of button click
i have tried putting the myConn.Close() in different places but it dosnt seem to work!
any advice or tips ?
RegardsJez
I'm new to ASP.NET and I've searched before posting. I have a simple form containing a FormView control. The FormView uses a SQLDataSource. This works fine and in 30 seconds I have a working form without writing any code.A form built this way causes problems with connection pooling because I am not closing the connection. What is the best method for me to remedy this? Every resource I've found explains that I have to explicitly close the connection. How do I do that in this case? Dispose the SQLDataSource in the FormView DataBound event? Rewrite the form so I can control opening and closing of the connection?Thanks in advance.
View 1 Replies View RelatedHi
I access a DB by as abvious creating a DB Connection and then sending a querry to it. Finally creating an SQL Command and then exuting it.
But the thng is I am using a static function:
which opens a new connection and returns a data reader based on the querry. i.e
pubblic static SqlConnection OpenConnection()
{
try
{
string connectionString = "Data Source=mm\SQLEXPRESS;Initial Catalog=monsoonDB;Integrated Security=True";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
return conn;
}
catch (Exception ex)
{
throw;
}
}
Now I have the execute selectquerry function.....
public static SqlDataReader ExecuteSelectQuerry(string querry)
{
try
{
SqlConnection conn = OpenConnection();
SqlCommand scomm = new SqlCommand(querry, conn);
SqlDataReader sdr = scomm.ExecuteReader();
return sdr;
}
catch (Exception e)
{
return null;
}
}}
now my question is....the instance of conn is limited only to that function only, and not returned outside. Whereas only the SqlDataReader is returned outside. Does that have any abnormal affect on the application. Since my ASP Application is getting locked (not doing anything, nt even post back!) sometimes after a few DB operation.
Arent these connections and instances Managed (auto garabage collection)?? Can memory leaks under such a situation occur.
Please help
Thanks
I have created my query to do what it needs to do but i'm getting error when i click the button, it says there is an error opening my connectiong....
I.E.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
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: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.Source Error:
Line 28:
Line 29: //open the connection
Line 30: myConnection.Open();
Line 31:
Line 32: //create a commandSource File: c:Documents and SettingsplanPlanDatabaseBZAvuAdd.aspx.cs Line: 30 Stack Trace:
[SqlException (0x80131904): Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +171
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +34
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +606
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +193
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +502
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +429
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +70
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +512
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +89
System.Data.SqlClient.SqlConnection.Open() +160
_Default.insertIntoVU() in c:Documents and SettingsplanPlanDatabaseBZAvuAdd.aspx.cs:30
_Default.addAppButton_Click(Object sender, EventArgs e) in c:Documents and SettingsplanPlanDatabaseBZAvuAdd.aspx.cs:127
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +98
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4919
therefor is this saying that I have to login before even testing this thing or what??
I'm developing an intranet site in ASP.NET 2.0 but I can't seem to connect to the DB from within my code. I've created a .vb class that houses a private Connection() that other functions within the class can call to connect to the database. In the calling function, I've declared my connection object and called the "Open" method on the object. However, when I attempt to execute the stored procedure command by calling the "ExecuteScalar" method, I get the following error:
"ExecuteScalar requires an open and available Connection. The connection's current state is closed."
Here's the code from my class:
Imports System.Data
Imports System.Data.SqlClient
Namespace Encompass
Public Class EncompassSecurity
Public Shared Function GetHRIDByNTUserID(ByVal strNTUserID) As String
Dim strHRID As String
'Create command object
Dim cmd As New SqlCommand("usp_Get_HRID_By_NTUserID", Connection())
cmd.CommandType = CommandType.StoredProcedure
'Open DB connection
Dim DBConnection As SqlConnection = Connection()
DBConnection.Open()
'Input parameters
Dim inNTUserParam As New SqlParameter("@NT_UserID", SqlDbType.VarChar)
inNTUserParam.Direction = ParameterDirection.Input
inNTUserParam.Value = strNTUserID
cmd.Parameters.Add(inNTUserParam)
'Output parameters
Dim outHRIDParam As New SqlParameter("@HRID", SqlDbType.Int)
outHRIDParam.Direction = ParameterDirection.Output
cmd.Parameters.Add(outHRIDParam)
'Run stored procedure
strHRID = cmd.ExecuteScalar()
Return (strHRID)
'Close DB connection
DBConnection.Close()
End Function
Private Shared Function Connection() As SqlConnection
Dim strConnectionString As String
strConnectionString = ConfigurationManager.ConnectionStrings("Conn").ConnectionString
Return New SqlConnection(strConnectionString)
End Function
End Class
End Namespace
Here's the code from my web.config file:
<?xml version="1.0"?>
<!--
Note: As an alternative to hand editing this file you can use the
web admin tool to configure settings for your application. Use
the Website->Asp.Net Configuration option in Visual Studio.
A full list of settings and comments can be found in
machine.config.comments usually located in
WindowsMicrosoft.NetFrameworkv2.xConfig
-->
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
<connectionStrings>
<add name="Conn" connectionString="Data Source=ServerName;Initial Catalog=NPASDV;uid=UserName;password=*******;"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.
Visual Basic options:
Set strict="true" to disallow all data type conversions
where data loss can occur.
Set explicit="true" to force declaration of all variables.
-->
<roleManager defaultProvider="AspNetWindowsTokenRoleProvider" />
<compilation debug="true" strict="false" explicit="true" />
<pages>
<namespaces>
<clear />
<add namespace="System" />
<add namespace="System.Collections" />
<add namespace="System.Collections.Specialized" />
<add namespace="System.Configuration" />
<add namespace="System.Text" />
<add namespace="System.Text.RegularExpressions" />
<add namespace="System.Web" />
<add namespace="System.Web.Caching" />
<add namespace="System.Web.SessionState" />
<add namespace="System.Web.Security" />
<add namespace="System.Web.Profile" />
<add namespace="System.Web.UI" />
<add namespace="System.Web.UI.WebControls" />
<add namespace="System.Web.UI.WebControls.WebParts" />
<add namespace="System.Web.UI.HtmlControls" />
</namespaces>
</pages>
<!--
The <authentication> section enables configuration
of the security authentication mode used by
ASP.NET to identify an incoming user.
-->
<authentication mode="Windows" />
<!--
The <customErrors> section enables configuration
of what to do if/when an unhandled error occurs
during the execution of a request. Specifically,
it enables developers to configure html error pages
to be displayed in place of a error stack trace.
<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->
</system.web>
</configuration>
What am I doing wrong? Any help would be most appreciated!!
Manuel
Hello, Im new with databases.
I know that opening a connection to a DB is expensive. Usually I write a method that opens a connection to the DB then I execute a query and then close the connection. Pretty standard.
OK, so how do I handle opening connections to the database when I need to run multiple queries. For example, i have a webpage that need to query the database to see if the user has moderator privledges, then depending on that query I have to query the DB again for moderator specific information or non-modertaor information.
So in this case how do i handle opening connections to the DB. Is it ok to generally have to open a connection to a DB multiple times on a page load?
The obvious solution is to keep the connection open. That is, open a connection, query the Db, keep the connection open, do the conditional statment ( if is_Moderator) then query the DB again for the info that I need, and then close the connection. But, from all the books that Ive been reading this is not a good practice because business logic should not been in the dataAccess layer.
Any help would be much appreciated.
When the databse file's "copy to output" property is set to "copy always" my data is not saved and when I
change it to "do not copy" it gives me the error "cannot attach the database file", and does not open my connection.
I dont know what to do.....
Well here is my connection string, I used these details to succesfully access the server from mySQL gui.
SqlConnection shhfd = new SqlConnection ("Data Source=http://www.mydomain.co.uk; " +
"user id=mteccles; password=mypass; connection timeout=30; "
then... try { shhfd.Open(); blah blah blah.
The exception caught is as follows.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at WebService1.Service1.HelloWorld() in g:inetpubwwwrootwebservice1service1.asmx.cs:line 120
My guess is that something in the web.config isn't right or maybe the host (webfusion) is strange. Thanks if anyone can help.
Hi,
I have a application running on Biztalk server , VS 2008 and SQL server 2008.
I have opened a SQL connection string and referring some DB which works absoluetly fine.
But in my code in C#, i try to open another connection with some different database, as i apply the connection.open(), it gives me a very strange error of Sql server 2005. And i dont have Sql 2005 on my box.
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)
Can you please help me out??
Thanks.
As you see in the images the connection is closing. During the read it counts 5 columns which is correct. When I step through the code it closes the connection when it hits dt.Load(reader) and nothing is loaded into the datatable.
------------------------------------------------------------AS I STEP THROUGH -----------------------------------------------------------------------------------------------------------------------
Please help,
Thanks,
Tom
I am getting the error message below when trying to connect to SQL Server 2000. Here are the details:
Problem is occuring with a new website trying to connect to SQL Server. The error occurs right at the open statement.
Using SQL Server 2000
Web server is on a different machine than SQL Server
I have many websites connecting to this SQL Server. Websites are all on the same webserver.
I can connect to this SQL Server using the Query Analyzer using the same ID and PW as in my connection string
My web application runs OK using this SQL Server when I run it from my laptop - just changed the connection string Initial Catalog parameter.
Any help would be appreciated.
Jay
---
Error Message
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)
(Hope this isn't a "stupid" question, but I haven't been able to find a straight-forward answer anywhere)"
I currently have code that iterates through a dataview's records, making a change to a field in some of the records. The way I have this coded, a conection has to opened & closed for each individual record that's updated:
dsrcUserIae.UpdateCommand = "UPDATE UserIAE SET blnCorrect = @blnCorrect WHERE (ID = @ID)" dsrcUserIae.UpdateParameters.Add("blnCorrect", SqlDbType.Bit) dsrcUserIae.UpdateParameters.Add("ID", SqlDbType.Int)
Dim myDataView As DataView = CType(dsrcUserIae.Select(DataSourceSelectArguments.Empty), DataView) For Each myRow As DataRowView In myDataView If myRow("FkUsersAnswerID") = myRow("AnswerID") Then intCorrect = 1 Else intCorrect = 0 End If dsrcUserIae.UpdateParameters.Item("blnCorrect").DefaultValue = intCorrect dsrcUserIae.UpdateParameters.Item("ID").DefaultValue = myRow("ID") intUpdateResult = dsrcUserIae.Update() Next
It seems like I should be able to do something like this (call update once), but I'm not sure how...
dsrcUserIae.UpdateCommand = "UPDATE UserIAE SET blnCorrect = @blnCorrect WHERE (ID = @ID)" dsrcUserIae.UpdateParameters.Add("blnCorrect", SqlDbType.Bit) dsrcUserIae.UpdateParameters.Add("ID", SqlDbType.Int)
Dim myDataView As DataView = CType(dsrcUserIae.Select(DataSourceSelectArguments.Empty), DataView) For Each myRow As DataRowView In myDataView If myRow("FkUsersAnswerID") = myRow("AnswerID") Then myRow("blnCorrect") = 1 Else myRow("blnCorrect") = False End If Next
intUpdateResult = dsrcUserIae.Update() 'Want all changed myRow("blnCorrect") to be updated to datasource
Can anybody explain how to do the bulk update? I've seen some info about AcceptChanges and Merge, but I'm not sure if they apply here, or if they more for Transactions.
Hi,
I'm writing an application with Visual Studio 2005 (Visual Basic .NET 2.0) which uses a SQL Compact Edition 3.5 database. On my HTC Touch with Windows Mobile 6.1 installed the application crashes, without any error message, if I try to open a connection. But...On the Windows Mobile 6 Pro emulator the connection opens normaly. The whole application runs perfect.
On both "devices" are the same dll's, of version 3.5.0.0.
I'm using the following code to open the connection:
Code Snippet
Private mySQLce_strConnection As String = "Data Source ='" & App_Path() & "MControl_SQLce.sdf" & "';"
Dim localConnection As New SqlCeConnection(mySQLce_strConnection)
localConnection.Open()
I tried so many things but without any success. But...I'm able to open the database (MControl_SQLce.sdf) with the Query Analyzer 3.5 on the Mobile. No problem.
This is my configuration:
Dev PC:
Windows XP SP2
Visual Studio 2005 Pro
.NET Framework 2.0 SP1
.NET Compact Framework 2.0
SQL Server Compact Edition 3.5 (this version I reference to in my VS project)
Mobile Device (HTC Touch):
Windows Mobile 6.1
SQL Server Compact Edition 3.5
Any suggestions? I can't figure this out.
Hello everyone. I'm very new to asp.net. I've been coding in vb.net for a while now (getting my associates degree in desktop application programming) and my boss wants all of my new applications to be web based now. Well, i decided to take some initiative on this and begin a new project for my department. This is a very small project. So far, everything is coming along alright (i found an eBook that's helped me quite a bit). Everything was cool until yesterday when i tried to connect to our sql server database. I get an error message stating:
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Because i'm testing this on my system as well as the remote system which hosts the application, i can see the error message on the remote system. Here is the error it's giving me:
Server Error in '/' Application.
--------------------------------------------------------------------------------
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
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: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
Source Error:
Line 57: sConnString = "Server=F11602A2120564OPS;Initial Catalog=OpsMonitor;User ID='public';Password=;Integrated Security=SSPI;"'Connection String
Line 58: sConn = New SqlConnection(sConnString)'Initialize New Connection
Line 59: sConn.Open'Open the connection
Line 60: End Sub
Line 61: </script>
Source File: c:inetpubwwwrootatm_reporting.aspx Line: 59
Stack Trace:
[SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +474
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +383
ASP.atm_reporting_aspx.OpenConn() in c:inetpubwwwrootatm_reporting.aspx:59
ASP.atm_reporting_aspx.Page_Load(Object Src, EventArgs E) in c:inetpubwwwrootatm_reporting.aspx:23
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
You can see my SQL Connection string in there as well. Can someone please help me with this? I can post the code to the entire page as well if that would help. I'm developing this with Dreamweaver MX 2004 (i like dreamweaver MUCH better than visual studio, especially for web design/development). Thanks,
Eric
I am getting an error in Replication between SQL Server 2005 and SQL Express when "Connecting to Subscriber". Detailed error message is given below. Do we need to increase the logintimeout for "Connecting to Subscriber". How can we increase it?
Message
2007-10-15 06:37:58.398 Startup Delay: 8503 (msecs)
2007-10-15 06:38:06.898 Connecting to Distributor 'ACR-MANGO'
2007-10-15 06:38:06.976 Initializing
2007-10-15 06:38:06.976 Parameter values obtained from agent profile:
-bcpbatchsize 2147473647
-commitbatchsize 100
-commitbatchthreshold 1000
-historyverboselevel 1
-keepalivemessageinterval 300
-logintimeout 15
-maxbcpthreads 1
-maxdeliveredtransactions 0
-pollinginterval 5000
-querytimeout 1800
-skiperrors
-transactionsperhistory 100
2007-10-15 06:38:06.991 Connecting to Subscriber 'ACR-ANJILISQLEXPRESS'
2007-10-15 06:38:46.133 Agent message code 20084. The process could not connect to Subscriber 'ACR-ANJILISQLEXPRESS'.
2007-10-15 06:38:46.148 Category:NULL
Source: Microsoft SQL Native Client
Number: 08001
Message: Unable to complete login process due to delay in opening server connection
2007-10-15 06:38:46.148 The agent failed with a 'Retry' status. Try to run the agent at a later time.
I've got a problem with this query. I'm not great at joins and so I'm not sure how to fix this.
I have items not in the CMO table (the last join), but I still want them to at least show up. They're being excluded:
SELECT OM.DOCNUM, OM.NAME_LAST, OM.NAME_FIRST, LV.DESCRIPTION AS Sex, ' ' AS Mandatory, CASE WHEN (LSI.Q1 = 1 OR
LSI.Q2 = 1 OR
LSI.Q3 = 1 OR
LSI.Q4 = 1 OR
LSI.Q5 = 1 OR
LSI.Q6 = 1 OR
LSI.Q7 = 1 OR
LSI.Q8 = 1 OR
LSI.Q9 = 1 OR
LSI.Q10 = 1 OR
LSI.Q12 = 1 OR
LSI.Q13 = 1 OR
LSI.Q14 = 1 OR
LSI.Q15 = 1 OR
LSI.Q16 = 1 OR
LSI.Q33 = 1 OR
LSI.Q34 = 1 OR
LSI.Q39 = 1 OR
LSI.Q39 = 0 OR
LSI.Q40 = 1 OR
LSI.Q40 = 0 OR
LSI.Q51 = 1 OR
LSI.Q51 = 0) THEN 'Y' ELSE ' ' END AS Optional, OM.CASE_MANAGER_ID_U AS CASE_MANAGER_ID,
CASE WHEN (CMO.REMOVAL_RSN_ID_LV = 1022) THEN 'Y' ELSE ' ' END AS Active, CASE WHEN (CMO.REMOVAL_RSN_ID_LV = 1015)
THEN 'Y' ELSE ' ' END AS Complete
FROM dbo.OFFENDER_MAIN AS OM LEFT OUTER JOIN
dbo.LOOKUP_VALUES AS LV ON LV.ID = OM.GENDER_ID_LV LEFT OUTER JOIN
dbo.LSI_ANSWERS AS LSI ON LSI.DOCNUM = OM.DOCNUM LEFT JOIN
dbo.COMMUNITY_MOD_OBJ AS CMO ON CMO.DOCNUM = OM.DOCNUM
WHERE (LSI.COMIT_ACTIVE = 1) AND (CMO.OBJECTIVE_CODE_ID_LV = 1011) AND (CMO.REMOVAL_RSN_ID_LV = 1022 OR
CMO.REMOVAL_RSN_ID_LV = 1015)
AND (OM.FACILITY_ID_F <> 1)
I have this code:
Declare @sql as varchar(4000)
declare @tbl as varchar(100)
declare @exists as bit
select @tbl = 'ACA_RSF'
select @sql = 'select count(*) from [member_score] where source_tbl = ''' + @tbl + ''''
print @sql
exec (@sql)
and it returns 18 million for a record count.I have this scalar returning function, which models the above, and it returns zero:
select dbo.fnGet_Rec_Count('ACA_RSF') as cnt
here is the code:
alter FUNCTION spGet_Rec_Count
(
@source_tbl varchar(100)
)
RETURNS bigint
AS
BEGIN
-- Declare the return variable here
DECLARE @count bigint
-- Add the T-SQL statements to compute the return value here
select @count = (select count(*) from [member_score] where source_tbl = ''' + @tbl + ''')
-- Return the result of the function
RETURN @count
END
GO
I get zero regardless of where @count is declared as in or bigint.
I upsized an access database with a key / index on ordernumber and linenumber.
However if I open the table in the Management Studio the records aren't ordered this way (same goes for select * from table) I get:
Ordernumber
Linenumber
200724001
37
200724004
3
200724006
33
200724001
3
200724011
19
200724014
5
200724006
37
200724011
19
200724006
28
Same goes for my crystal reports files, since the records aren't ordered by ordernumber / linenumber all my formulas go bezerk..
Am I wrong thinking the records should be ordered according to the prim. index?
Please help because I don't want to have to change all my 40+ reports to include an "ORDER BY"
Best regards,
Mike
Hi - I am attempting to run an SSIS package through a SQL Agent job step. The package is fairly basic - I run some Execute SQL tasks, build an ADO recordset, enter into a For-Each loop, and perform some table updates based on the ADO recordset. So far so good. Approximately 1/3 of the records in my For-Each loop are processed, then I get a "Failed to acquire connection error". All I am doing for each iteration of the For-Each loop is updating one record in a table based upon the current value that is being processed from the ADO recordset. I am running the job on a 64-bit box, version 9.00.3050.00. I have also run this on two other 64-bit boxes (versions 9.00.1399.06 and 9.00.1406.00) and had the same issue. The owner of the Agent job is a sysadmin on the box. I don't understand why some items would be processed, then the connection drops mid-way through processing. What's even odder is the connection is dropped at the same point every time I run. The connection that I have defined in my package (I only have one connection defined in my package) is Provider type "Native OLEDBSQL Native Client", and I am using Windows authentication. The version of Visual Studio that I have developed this package in is 8.0.50727.42. Please help.
View 7 Replies View RelatedHi all,
I have a Send Mail Task in my control flow to notify users that the processing is done. I want to avoid the package to fall in error if the Send Mail task failed.
What is the best practice to do that ?
Should I raise the MaximumErrorCount of theSend Mail Task ? Should I play with ErrorHandler ?
I'd like to transfer some records between the following 2 tables. Surely this should be a no-brainer - what am i missing that is making this so impenetrable?
I am currently: Hoping someone can help me get here: (this is my first time of using SSIS btw).
here is the source table (MS Sql Server 2005, SP 2)
CREATE TABLE [dbo].[imagine_divs](
[div_mnemonic] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_date] [int] NULL,
[div_amount] [float] NULL,
[div_status] [nvarchar](16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_curr_mnem] [nvarchar](11) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[div_upd_date] [int] NULL,
[div_payment_date] [int] NULL
) ON [PRIMARY]
here is the target table (Oracle 9i)
CREATE TABLE myschema.imagine_divs
(div_mnemonic NVARCHAR2(11),
div_date NUMBER(*,0),
div_amount NUMBER(20,5),
div_status NVARCHAR2(16),
div_curr_mnem NVARCHAR2(11),
div_upd_date NUMBER(*,0),
div_payment_date NUMBER(*,0))
/
I used the SSIS Import and Export wizard to copy data between the two tables, and attempted to execute it. I use Sql Native Provider on source, and Native Ole DBOracle Provider for OLEDB. however, I get an error:
[Destination - IMAGINE_DIVS [37]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "OraOLEDB" Hresult: 0x80004005 Description: "ORA-12571: TNSacket writer failure".
I notice that the wizard has created a data flow task with 3 steps: source - imagine_divs, Destination - IMAGINE_DIVS and "data conversion 1".
Data Conversion 1 seems to be taking my source nvarchar columns and converting them to DT_STR with twice the size (for example div_mnemonic become DT_STR, size: 22).
If I change the mappings in the OLE DB Destination Editor, such that only the numeric and date-typed columns are included in the transfer, it works fine.
If I include any string-typed column in the destination editor mappings, I get the TNS Packet Writer error. If I remove the Data Conversion step and connection teh source and destination tasks directly, i get validation errors saying that:
Error 2 Validation error. Data Flow Task: OLE DB Destination [294]: Columns "div_mnemonic" and "DIV_MNEMONIC" cannot convert between unicode and non-unicode string data types. Package4.dtsx 0 0
this is despite the fact that everything is unicode here (right?)
what in the world is going on?
I have a list of servers on a table, I take this list and pass to a for each loop container and that will grab some system information from each server that we give for our internal auditors.
The issue that I'm currently having with this package is that some times, 1 or 2 servers from the list are down and/or extremelly busy and it times out, either case causes the whole package to fail.
What I'm trying to do is, test the connection to the server prior passing the server name to the For Each Loop Container and log that information somewhere (Maybe a boolean field in a table), so then I check after the package finish and validate that the server was actually up or down.
Any ideas or suggestions?
Thanks,
Igor Santos
Hello-
I'm trying to read a table that has database connection information for other DBs and use this within an "Execute SL Task" task. I have seen a number of posts that talk about this possibility, but I have not been able to get it to work yet.
When I've tried to set the connection to a variable (@[User::DB_ConnectionStr]) in the Expressions area of the SQL Task, the Connection type defaults to OLE DB and I can't seem to force it back to ADO or ADO.net.
I've tried doing this with the variable being set to both a connection object and a String with the connection string, but neither seems to work.
Any suggestions? Should this be a string value of the connection string? Am I missing something when trying to set the connection type?
Thanks much!
Tristan
I have a script component which loads a file which is in a custom format. The script component is inside a For Each Loop Container and it uses a flat file connection manager. The loop sets the connection string for the connection manager.
The problem I'm having is that the connection string needs to be set to something every time that I start the package but I don't know ahead of time what file there will be, so I get a System.IO.FileNotFoundException error on the script component. If I manually set the variable for the connection string and point it to a file that exists, then the package runs fine but at the end of the package the connection string is set to the last file loaded and this file will no longer exist the next time the package runs.
I hope this makes sense...