How To Open Multiple SqlConnection Objects In A SqlServer Project

Jun 5, 2006

I have a sql server project where I have added 1 stored procedure (named StoredProcedure1) and 1 class (named MyClass). MyClass has two functions Func1() and Func2(). The stored procedure simply instantiates MyClass and calls Func1().

Func1() creates a SqlConnection object using "context connection=true" - opens the connection and then calls Func2(). Func2() also creates a SqlConnection object using "context connection=true" - and attempts to open the connection. However when attempting to the Open() call inside Func2(), the code just exits. What gives? The code is pasted below, thanks!

*******THE STORED PROCEDURE**************
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using SqlServerProject1;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure1()
{
// Put your code here
MyClass obj = new MyClass();
obj.Func1();
obj = null;
}
};

*******THE CLASS "MYCLASS"**************
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public class MyClass
{
public MyClass()
{

}

public void Func1()
{
using ( SqlConnection conn = new SqlConnection("context connection=true") )
{
conn.Open();

Func2();
conn.Close();
}
}

public void Func2()
{
using ( SqlConnection conn = new SqlConnection("context connection=true") )
{
if ( conn.State != ConnectionState.Open )
conn.Open();

if ( conn.State == ConnectionState.Open )
conn.Close();
}
}
}

View 1 Replies


ADVERTISEMENT

Performance Problems When Using Multiple SqlConnection Objects?

Feb 9, 2007

Hello,
 
I am planning to use three databases on the local SQL Server. DB1 will be for App1, DB2 for App2, and DB3 will hold tables, that both, App1 and App2 need to access.
 
Obviously, I need in App1 and App2 at least two SqlConnection and SqlCommand objects, one that access their respective databases, and one that will access DB3.
 Will this result in a worse performance? (The using of two SqlConnection objects in one aspx page as example, and switching them)
Should I for better performance get rid of DB3 and add the tables there to DB1 and DB2 ? (Thus multiplying data, but, if the performance will be much better, it will be fine with me)
 
I would be thankful for any advices.

View 4 Replies View Related

Can't Create A Setup Project For Project Referencing Microsoft.SqlServer.ManagedDTS

Feb 3, 2007

I have a very small project written in VB.Net 2005 using the SQL Server 2005 SSiS DTSx package.

I took a SQL Server 2000 dts package and using the SQL Server 3005 legacy tools migrated it so I could still use the package withing SQL 2005 until I can build one using BI/SSIS.

Anyway,I added the reference Microsoft.SqlServer.ManagedDTS so I could then use the Microsoft.SqlServer.Dts.Runtime so I can execute the commands:

Dim oApp As New Application
Dim oPkg As New Package
oPkg = oApp.LoadPackage(g_DTSx_Directory & "AOC copy Generic1 CSV to AOC_verify_file_1.dtsx", Nothing)
Dim oResults As DTSExecResult
oResults = oPkg.Execute

Ok. That works fine. Executes without a hitch. So now I try and create a setup project for this and I use the setup wizard.

During the creation of the setup project I get a message that states:
The following files may have dependencies that cannot be determined automatically. Please confirm that all dependencies have been added to the project.
C:windowssystem32msxml6.dll

OK. The dll is part of the reference I mentioned above and I have no idea what other dependencies it may have.

How do I find this out?

Has anyone else created a project like this and experenced the same?

I am on a clean build running WinXP Pro with SP2 - VS2005 with SP1 and the SQL Server 2005 tools.

View 1 Replies View Related

Gridview Binding With SqlConnection Objects...

Jun 19, 2007

Hello All, I am new to data access and
i have got the problem to display the data into the page by binding the gridview with sqlConnection, sqlCommand and sqlDataReader objects.  The actually code is written as:protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{SqlConnection myConnection;
SqlCommand myCommand;SqlDataReader myReader;
myConnection = new SqlConnection();myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["LatteConnectionString"].ConnectionString;
myCommand = new SqlCommand();myCommand.CommandText = "select * from AntiVirusVendors";myCommand.CommandType = CommandType.Text;
myCommand.Connection = myConnection;
myCommand.Connection.Open();myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
GridView1.DataSource = myReader;
GridView1.DataBind();
myCommand.Dispose();
myConnection.Dispose();
}
 
}
 
and the GridView in html is listed as:
<div>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
So the problem is ---> there is nothing shown in the page, no errors no anything.... just the empty page.
Any ideas would be appreciated. Thanks in advance!
Joe

View 1 Replies View Related

The Insides Of The SqlConnection, SqlCommand, And SqlDataReader, Objects

Mar 22, 2008

Im just curious, can anyone explain what exactly the methods do within the objects (sqlconnection, sqlCommand, SqlDataReader) . and why it takes three objects to carry out a connection. Php only used one object I believe.
 
But most of all i would like to know how they work. like sqlCommand carrys the connection (sqlcommand.connection) while sql connection retrieves the address connection (sqlconnection(connectionstring)). what does Sqlconnection do once it gets this address. and what does it give sqlcommand when its ready to take out the query and put it in the reader once the sqlconnection.open()  to make it simple. let me put it this way
 sqlConnection con = new SqlConnection(_connectionstring);    // server address comes through here. along with security and database name what exactly does the object con do ? it runs through the constructor and does what ?
SqlCommand cmd = new SqlCommand();  cmd.Connection = con;  //So now what ever the SqlConnection did with the _connectionstring. it now passes it through the connection property. the connection property does something with value of con. what is it ??
cmd.CommandText = 'select, ID, Name, Price.....'       // I think i know what that does
con.Open()                    // I know that the connection opens here. but what exactly does this method do to make it open ????????
sqlDataReader reader  = cmd.ExecuteReader();    im some what confused with this one. sqldataReader reader was never declared and its not even a static method. What exactly is it ?????       cmd.ExecuteReader() now takes the property connection which did something with the con value. then it used the commandtext property to request the query. it takes con.open does something and now its execute reader. Im a lil confused
last but not least, con.close() what does that do ?? its alot but can anyone fill me in. or should i say fill(me); in lol, get it. never mind.
                                                                                              

View 1 Replies View Related

SQLConnection.Open() Problem

Feb 14, 2008

Hi, 
When i run my script it gets about 5 lines before the bottom and kicks this out SQLConnection.Open()
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)
Does this mean that SQL Server 2005 Express doesnt allow remote connections and is there anything i can do to get round this (im running it locally)? My data Source is - SQLDataSource1 - Picturesdb.MDF and my local server is Localhost:1921. Code behind Below,
Imports System.IO
Imports System.DataImports System.Data.SqlClient
 Partial Class WebForm1
Inherits System.Web.UI.PageProtected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
Dim strFileName As String
Dim strFilePath As String
Dim strFolder As StringDim SQLConnection As Data.SqlClient.SqlConnection Dim SQLCommand As Data.SqlClient.SqlCommand
strFolder = "C:Documents and SettingsRoss HintonMy DocumentsVisual Studio 2005WebSitesuploadsiteimagesfolder"
strFileName = oFile.PostedFile.FileName
strFileName = Path.GetFileName(strFileName)
If (Not Directory.Exists(strFolder)) Then
Directory.CreateDirectory(strFolder)
End If
strFilePath = strFolder & strFileName
If File.Exists(strFilePath) Then
lblUploadResult.Text = strFileName & " already exists on the server!"
ElseSQLConnection = New SqlConnection("localhost:1921")SQLCommand = New SqlCommand
SQLCommand.CommandType = CommandType.StoredProcedure
SQLCommand.CommandText = "spInsertImageName" ' your sp name
SQLCommand.Connection = SQLConnection
SQLConnection.Open()
SQLCommand.Parameters.AddWithValue("@image", strFileName) ' sp parameter name
SQLCommand.ExecuteNonQuery()
oFile.PostedFile.SaveAs(strFilePath)
lblUploadResult.Text = strFileName & " has been successfully uploaded."
End If
frmConfirmation.Visible = TrueEnd Sub
End Class

View 2 Replies View Related

Sqlconnection Open State

Apr 15, 2007

how to check the connection state sqlif (oconn.State.tostring()="Closed")or is there a more approiate waythanksMJ

View 1 Replies View Related

Open SQLConnection When Database Stopped

Dec 8, 2003

I'm hoping someone can explain to me exactly what the SQLConnection.Open() method does, especially when the database is stopped. I'm trying to include some error processing in my program, specifically to ensure the database is up and running. But even with the database stopped, the Open() statement works fine. Later, when I try to read from the database, I get the error. I'd like to stop it before it gets any further. Why is the Open() statement "working" even with the database stopped?

View 6 Replies View Related

Sqlconnection.open Slow With Integrated Security

Feb 11, 2008

Hello, I started profiling a website that i'm developing yesterday (asp.net 2.0) and noticed that sqlconnection.open is ridiculously slow (between 3-10 seconds) when using integrated security=true in the connection string.  If I use SQL authentication instead and pass the username and password in the connection string, sqlconnection.open is instantaneous. 
My enviornment is as follows: 


Sql server is on a win2003 x64 server.

View 3 Replies View Related

Connect To Sqlserver By Sqlconnection From Windows CE 5.0

Feb 6, 2007

Currently I cannot connect to a database, I got the system.exception €śPlatformNotSupportedExcoption€?. This database is Polish (COLLATE Polish_CI_AS), when I collate this database to latin general it works fine. I connect by using the system.data.sqlclient.sqlconnection, as far as I can see I cannot set the locale identifier here€¦ What can I do???
Thanks for your help!
regards, Jan

View 3 Replies View Related

Open Objects Value

Jul 31, 2000

SQL server 6.5 stopped responding. The last error messages in the SQL Server Error Log / NT Event log suggested that I increase the Open Objects / Open Databases values. I rebooted the server, increase the configuration values, and rebooted the server again for the new configuration to take affect. I suspect that the front-end application does not release the objects properly when it should; therefore increasing Open Objects / Databases configuration values will only be a temporary fix until they reach the new maximum in some time. However to prove that I need to be able to determine how many open objects / databases are on the server at a point of time. Is there a procedure or a method to determine how many open objects / dtabases are on server?

Any comments or suggestions are greatly appreciated.

Thank you
Marina Somers

View 1 Replies View Related

Open Objects Parameter May Be Too Low.......

Sep 18, 2001

SQL 6.5
NT 4.3.3

Recently in the error.log the following message appeared:

Warning: Open Objects paramter may be too low
attempt was made to free up descriptors in localdes()
Run sp_configure to increase parameter value

Please indicate why this message appears and what measures must be taken to prevent it from re-occuring.

Thank You in advance!

View 2 Replies View Related

Cannot Open Objects Using SSDT

Jun 4, 2015

I have SQL server 2014  database installed on a Windows 2012 server and SSDT also got installed ( 2013 shell)

I can connect to a database and see all objects using SSDT , but I cannot write any queries or modify any of the objects,

I am new to the SSDT, and this is the first time I am going to configure SSDT to work with SQL Server.

View 7 Replies View Related

Cannot Open SSIS Project In BIDS

Apr 30, 2007

Hi,
I have been working on an SSIS project for sometime now. The project files are located on a remote server. Suddenly I am not able to open the solution I get a lot of error messages and all the data flow taks are gone. I later found out that SSIS encrypts packages, so that other users will not be able to see them. Fine, but I have been using the same windows user account for months now. What could be the problem?
This is what I get when trying to open the solution:

There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.

And the error list also contains messages saying "Could not load from xml".

Any pointers will be much appreciated.

TIA

View 9 Replies View Related

How Do You Open An Express Project In Standard?

Sep 21, 2006

I developed a project (web application) using the Express editions.

How do I migrate the project from Express (SQL Server Express & VWD Express) to Visual Studio 2005 Standard and SQL Server Standard?

Thanks if you can answer this.

Ken

View 7 Replies View Related

Why Won't My SQL CE Database Open In VS2008 Project?

May 10, 2008



I've just upgraded my C# app from .NET 2.0 to 3.5 in VS 2008. Now my SQL Server CE database file won't open. When I run the app, it falls over on the connection.Open() method call, saying I have to upgrade the database to 3.5. It says if it was created in 3.0 or 3.1 to run the compact/repair utility. Well, I did that - a full compaction - but it didn't help at all. According to somewhere on MSDN, I should get an option to upgrade if I reconnect to the database in Server Explorer. Not so. It connects quite happily. If I try to connect to some earlier copies of the database, I do get this dialog, so I am assuming that it has already been upgraded. But then why won't the damn thing work?

I tried running the command-line upgrade.exe too, but being a bit clueless when it comes to the command line, I couldn't get it to work. Kept telling me the thing wouldn't run in Win32 mode, even though I was running it from the command prompt (sigh!).

View 2 Replies View Related

How To List All The Open Objects On The SQL Server

Apr 1, 2004

Hi, is there a way to find all the objects that are currently open on the server, that includes:

Tables, Views, SPs, Extended SPs, Triggers, Rules, Defaults, Indexes on the tables?

I can get some information from syslockinfo table but I can find a way to get the full picture.

Any help on that would be appreciated.

Dim

View 9 Replies View Related

Cannot Open Database Problem When Copy Project To IIS

Oct 24, 2007

Hi all.I use VWD 2005 Express with SQL Server 2005 Express as a database.I copy my project to IIS and try to run it at the IIS Web Server.I already amended the connection string at web.config file as below:  <connectionStrings>
<add name="GeekSpeakConnectionString" connectionString="Data Source=SEN-M09SQLEXPRESS;Database=GeekSpeak.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>But, I got this error:Cannot open database "GeekSpeak.mdf" requested by the login. The login
failed.Login failed for user 'SEN-M09ASPNET'. I also has granted to user SEN-M09ASPNET . Still, I got this error. Why?

View 1 Replies View Related

Can´t Open Sql Database Tables From Access Adp Project

Apr 1, 2004

hi there,

we can´t open most of the sql server tables from our Access
project any more, which has to be related to collation or
extended property problems.

trying to open any table will result in the information
"The stored procedure has been executed but did not return
any records" after the first try
and later on nothing will happen any more; tables won´t
open.

basically this started to happen after recreating the sql
database due to resolution 1 in Microsoft Knowledge Base
Article - 318989:
collation of the server is Latin1_General_CI_AS
collation of the old database was
SQL_Latin1_General_CP1_CI_AS, which led to the error
described in the article.
collation of the new database is set to <Database
Standard>, now i have the problems described above.

anybody some good help on that?

ps: we can´t really rebuild the user database all the
time, because we got flight data in about 4 billion
records in there, so we will need anything quick and
easy...
thanks

ron

View 2 Replies View Related

SQL Server 2008 :: Cannot Open New SSIS Project

Nov 2, 2012

I installed SQL Server 2008 R2 and Visual Studio 2008. However, after several installations and un-installations. I could no longer use the SSIS to create New Projects. I cleaned out the registry with a Registry cleaner from Wise. I deleted the SQL Server and Visual Studio folders as well.

When I create a New SSIS Project, I immediately get an error as follows:

"Microsoft Visual Studio"
"An error prevented the view from loading."
"Additional information:"
"Specified Module cannot be Found. (Exception from HRESULT: 0x8007007E)"
"(System.Windows.Forms)"

What this means is that I cannot create any new SSIS Projects.

View 9 Replies View Related

When You Open Your SSIS Project With Other User, Delays?

Oct 27, 2006

hi everyone,

A couple of issues.

1)I usually connect to my remote server via TS using a specific login (it belong to Admins) and through that user I open my SSIS projects (30 packages). Now, I have a new user and the same operation take a long time and I don't know why (same permissions) the reasons for this big delay.

2)When I'm gonna to press "Delete all Breakpoints" from Debug menu BIDS is closed. Previously appears a Microsoft dialog (Microsoft has encountered an error...) asking if you inform or not of these errors.

Thanks in advance and regards,









View 2 Replies View Related

Sqlserver Storing Objects

Jun 14, 2008

I want to serialize a dataset and store it in database.How can i do that.In sqlserver 2005 which data type i need to use for storing objects.
Please advice

View 3 Replies View Related

Why Do All Packages In The Project Open When Running One In Debug Mode?

Feb 7, 2006

Does anyone know why this happens? When I run one of the packages in my project (by hitting the play button in the designer), all of the other packages in that project open before it starts running?

Thank you.

View 6 Replies View Related

Can't Open/upgrade SSRS 2005 Project In VS 2008

Nov 25, 2007

I have a solution that contains SQL Server Reporting Services 2005 project. I upgraded the solution to VS 2008. The report project shows that it is not loaded. Trying to load it gives me the error - "cannot be opened because its project type (.rptproj) is not supported by this version of visual studio". Okay, so I do I convert it?!

View 14 Replies View Related

Open A New Project Using SQL Server Management Studio Express

Aug 17, 2006

Dear Friends

I am unable to open e new project using SQL Server management Studio Express. On the File menu it displays only "Query with current connection" and "Data base Engine Query". Please help me on thi Do I have to Install SQL Server management Studio instead of using express version. Cant I use SQL Server and SQL server management studio for deploy to make a small database for my a wharehouse. Please help me



Amila

View 1 Replies View Related

Automatically Open A Default Project On Application Launch

Oct 31, 2006

I was wondering if there was a way to set a default project to open in Microsoft SQL Server 2005 when the application launches.

Perhaps there's a certain registry key I could edit?

View 1 Replies View Related

Script Needed To Edit Open Objects In Sybase

Feb 23, 2006

Currently I must manually complete the following commands to set a new valuefor the "open objects" and "open indexes". I need a way script this processin a batch file.COMMANDS RAN: This is nothing to script, these commands obtain the currentvalues for open objects and open indexes.isql -Usa -Pmanager -SCOP1_DSexecute sp_countmetadata "open objects"execute sp_countmetadata "open indexes"goRESULTS: below are the results, there are 5124 user objects and 2122 userindexes. I need a way to be able to multiple these numbers by a set valueand then use the new value and reset the user objects and user indexes. Thequestion is how do I isolate the results for each result so I can run thecalculations below. Here is the calculation I need:current user objects * 1.05 = new valuecurrent user indexes * 1.1 = new valueThere are 5124 user objects in all database(s), requiring 6985 Kbytes ofmemory.The 'open objects' configuration parameter is currently set to 500.(return status = 0)There are 2122 user indexes in all database(s), requiring 5902 Kbytes ofmemory.The 'open indexes' configuration parameter is currently set to 5000.(return status = 0)SETTING THE NEW VALUES: Once I have the new values, I can set the ojects andindexes as seen below.1> execute sp_configure "open objects", 53802> execute sp_configure "open indexes", 23343> go

View 2 Replies View Related

Equivalent Of Table With Objects In SQLServer

Aug 2, 2004

I am jus migrating my oracle set up to sql server. I have oracle tables with my own object types. Is there any equivalent in SQL Server tables.

Any advice/suggestions would be gratefully received.

-Sam

View 3 Replies View Related

Transfer SQLServer Objects Task Failure

Jan 10, 2008



I have written an SSIS package with a Transfer SQLServer Objects task which I want to use to copy database objects from a SQL Server 2000 database to SQL Server 2005.

When I run this task, I find the following error:

[Transfer SQL Server Objects Task] Error: Execution failed with the following error: "Version80 database compatibility level is not supported


Is there a way around this aside from changing the compatibility level of my SQL Server 2005 database?

Any help is much appreciated.

View 5 Replies View Related

Open SSIS Project Error: Unable To Cast COM Object Of Type

Oct 4, 2006

When I open up my existing SSIS project, I always get this error. Does anyone know what was wrong ?



TITLE: Microsoft Visual Studio
------------------------------

Unable to cast COM object of type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.PackageNeutralClass' to interface type 'Microsoft.SqlServer.Dts.Runtime.IObjectWithSite'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{FC4801A3-2BA9-11CF-A229-00AA003D7352}' failed due to the following error: The application called an interface that was marshalled for a different thread. (Exception from HRESULT: 0x8001010E (RPC_E_WRONG_THREAD)).

View 32 Replies View Related

How To Deploy Sqlserver Database And Tables With Vb.net Project Setup

Jan 18, 2008

i have done a project in vb.net ,now i want to create setup for sql server database so that we can run easily on client machine,plzzzzzzzzz give me answer for deploying

View 1 Replies View Related

Deleting Multiple Columns From Multiple Objects

Apr 2, 2008

Is there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.

View 2 Replies View Related

Good Practice When Working With Objects Using SQLServer As A Secure Store.

Oct 11, 2006



I'm sure this has been asked plenty of times before, so I'm after a link to a good answer.

I have tens of thousands of milk crates, holding dozens of different types of milk in hundreds of locations. I am used to working with objects but not databases. For this situation however I want the security of SQLServer transactions to track, for example, when a robot moves a crate from one location to another.

I am thinking of using SQLServer as a store. On startup I want to get my ecosystem of objects out of the store. While I am running, I'll just use objects. When I change an object property I want it to securely persist. I don't want to snapshot the whole menagerie of object states, just update the values that changed. Which will sometimes include the addition or deletion of objects. How do I do this? Is there an example somewhere that does this (or approximately this)?

I use VB and have Visual Studio 2005. (Which, by the way, is stunning. I thought all that "you will use less time and code more and better" talk was just hype. But its for real. Amazing product.)

tia

John









View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved