Hi,
DevStudio 2003, SQL Server 2005, Windows 2000 Pro SP4
I have been using the Enterprise Library Data Access Application Block to
connect to a local SQL 2000 database. The connection string parameters are:
<connectionStrings>
<connectionString name="Temp Connection String">
<parameters>
<parameter name="database" value="Temp" isSensitive="false" />
<parameter name="Integrated Security" value="True" isSensitive="false"
/>
<parameter name="packet size" value="4096" isSensitive="false" />
<parameter name="persist security info" value="false"
isSensitive="false" />
<parameter name="server" value="(local)" isSensitive="false" />
</parameters>
</connectionString>
</connectionStrings>
Having then removed SQL2000 and replaced it with SQL2005, the above
connection parameters no longer work. In order to connect, I'm having to
replace "(local)" with the explicit IP address of the local PC.
Can SQL 2005 be made to work with "(local)"? Having to change the "server"
value to the IP address means changing my installer to fill in this value,
which I'd rather not do. It also means that other developers in the team
have to change the connection string parameters in order to test on their
local databases.
For your information, SQL 2005 is configured to use both the Shared Memory &
Named Pipes protocols. Native Client configuration WAS setup with Shared
Memory first and Named Pipes second. I've tried disabling Shared Memory and
forcing Named Pipes to be used (on the basis that the SQL 2000 installation
was using Named Pipes) but to no avail.
Regards
David RazzettiHmmh, (local) should work on SQL2k5, whats the error message the
application Block is throwing ?
HTH, jens Suessmeyer.|||Hi Jens,
I'm using the application block to serve my code with SqlConnection objects.
The application block successfully supplies a SqlConnection object, but a
call to 'Open()' on the connection throws the SqlException "SQL Server does
not exist or access denied".
Regards
David Razzetti
"Jens" wrote:
> Hmmh, (local) should work on SQL2k5, whats the error message the
> application Block is throwing ?
> HTH, jens Suessmeyer.
>|||Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
?
HTH, Jens Suessmeyer.|||Hi Jens,
"." appears to work correctly with both SQL 2000 and SQL 2005, so I guess I
can use that instead of "(local)". I'd still be interested to hear from
anyone who knows why "(local)" might not be working on my installation of SQ
L
2005.
Regards
David Razzetti
"Jens" wrote:
> Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
> ?
> HTH, Jens Suessmeyer.
>|||Don=B4t know what version / build you are using but in earlier build the
local was replaced by localhost.
HTH, jens Suessmeyer.
Showing posts with label connection. Show all posts
Showing posts with label connection. Show all posts
Friday, March 30, 2012
Need to change connection string for SQL 2005?
Hi,
DevStudio 2003, SQL Server 2005, Windows 2000 Pro SP4
I have been using the Enterprise Library Data Access Application Block to
connect to a local SQL 2000 database. The connection string parameters are:
<connectionStrings>
<connectionString name="Temp Connection String">
<parameters>
<parameter name="database" value="Temp" isSensitive="false" />
<parameter name="Integrated Security" value="True" isSensitive="false"
/>
<parameter name="packet size" value="4096" isSensitive="false" />
<parameter name="persist security info" value="false"
isSensitive="false" />
<parameter name="server" value="(local)" isSensitive="false" />
</parameters>
</connectionString>
</connectionStrings>
Having then removed SQL2000 and replaced it with SQL2005, the above
connection parameters no longer work. In order to connect, I'm having to
replace "(local)" with the explicit IP address of the local PC.
Can SQL 2005 be made to work with "(local)"? Having to change the "server"
value to the IP address means changing my installer to fill in this value,
which I'd rather not do. It also means that other developers in the team
have to change the connection string parameters in order to test on their
local databases.
For your information, SQL 2005 is configured to use both the Shared Memory &
Named Pipes protocols. Native Client configuration WAS setup with Shared
Memory first and Named Pipes second. I've tried disabling Shared Memory and
forcing Named Pipes to be used (on the basis that the SQL 2000 installation
was using Named Pipes) but to no avail.
Regards
David Razzetti
Hmmh, (local) should work on SQL2k5, whats the error message the
application Block is throwing ?
HTH, jens Suessmeyer.
|||Hi Jens,
I'm using the application block to serve my code with SqlConnection objects.
The application block successfully supplies a SqlConnection object, but a
call to 'Open()' on the connection throws the SqlException "SQL Server does
not exist or access denied".
Regards
David Razzetti
"Jens" wrote:
> Hmmh, (local) should work on SQL2k5, whats the error message the
> application Block is throwing ?
> HTH, jens Suessmeyer.
>
|||Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
?
HTH, Jens Suessmeyer.
|||Hi Jens,
"." appears to work correctly with both SQL 2000 and SQL 2005, so I guess I
can use that instead of "(local)". I'd still be interested to hear from
anyone who knows why "(local)" might not be working on my installation of SQL
2005.
Regards
David Razzetti
"Jens" wrote:
> Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
> ?
> HTH, Jens Suessmeyer.
>
|||Don=B4t know what version / build you are using but in earlier build the
local was replaced by localhost.
HTH, jens Suessmeyer.
sql
DevStudio 2003, SQL Server 2005, Windows 2000 Pro SP4
I have been using the Enterprise Library Data Access Application Block to
connect to a local SQL 2000 database. The connection string parameters are:
<connectionStrings>
<connectionString name="Temp Connection String">
<parameters>
<parameter name="database" value="Temp" isSensitive="false" />
<parameter name="Integrated Security" value="True" isSensitive="false"
/>
<parameter name="packet size" value="4096" isSensitive="false" />
<parameter name="persist security info" value="false"
isSensitive="false" />
<parameter name="server" value="(local)" isSensitive="false" />
</parameters>
</connectionString>
</connectionStrings>
Having then removed SQL2000 and replaced it with SQL2005, the above
connection parameters no longer work. In order to connect, I'm having to
replace "(local)" with the explicit IP address of the local PC.
Can SQL 2005 be made to work with "(local)"? Having to change the "server"
value to the IP address means changing my installer to fill in this value,
which I'd rather not do. It also means that other developers in the team
have to change the connection string parameters in order to test on their
local databases.
For your information, SQL 2005 is configured to use both the Shared Memory &
Named Pipes protocols. Native Client configuration WAS setup with Shared
Memory first and Named Pipes second. I've tried disabling Shared Memory and
forcing Named Pipes to be used (on the basis that the SQL 2000 installation
was using Named Pipes) but to no avail.
Regards
David Razzetti
Hmmh, (local) should work on SQL2k5, whats the error message the
application Block is throwing ?
HTH, jens Suessmeyer.
|||Hi Jens,
I'm using the application block to serve my code with SqlConnection objects.
The application block successfully supplies a SqlConnection object, but a
call to 'Open()' on the connection throws the SqlException "SQL Server does
not exist or access denied".
Regards
David Razzetti
"Jens" wrote:
> Hmmh, (local) should work on SQL2k5, whats the error message the
> application Block is throwing ?
> HTH, jens Suessmeyer.
>
|||Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
?
HTH, Jens Suessmeyer.
|||Hi Jens,
"." appears to work correctly with both SQL 2000 and SQL 2005, so I guess I
can use that instead of "(local)". I'd still be interested to hear from
anyone who knows why "(local)" might not be working on my installation of SQL
2005.
Regards
David Razzetti
"Jens" wrote:
> Hmmh, strange one. Didi you try just a "." for connecting to SQL Server
> ?
> HTH, Jens Suessmeyer.
>
|||Don=B4t know what version / build you are using but in earlier build the
local was replaced by localhost.
HTH, jens Suessmeyer.
sql
Monday, March 26, 2012
Need suggestion or comments for n-Tier applications.
using System;namespace BaResearch.Data.msSql{interface ISqlDataObject { System.Data.SqlClient.SqlConnection Connection {get; }string ConnectionString {get;set; } }}___________________________________
using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;namespace BaResearch.Data.msSql{public class SqlDataObject : ISqlDataObject {private string _ConnectionString;public SqlConnection Connection {get {return new SqlConnection(_ConnectionString); } }public string ConnectionString {get {return _ConnectionString; }set { _ConnectionString =value; } } }}__________________________________
using System;namespace BaResearch.Data{interface IBid {string Client {get;set; }string Contact {get;set; }string Sponsor {get;set; }string Priority {get;set; }string BidStatus {get;set; } }}_____________________________________________
using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.SqlClient;using System.Configuration;namespace BaResearch.Data.msSql{public class Bid : SqlDataObject, IBid {#region private member & variablesprivate int _BidID;private string _DateCreated;private string _CreatedBy;private string _Client;private string _Contact;private string _Sponsor;private string _Priority;private string _BidStatus;private SqlDataAdapter sqlda =new SqlDataAdapter();private SqlCommand _command;private SqlParameter[] _parameters = {new SqlParameter("@.client",SqlDbType.NVarChar),new SqlParameter("@.contact",SqlDbType.NVarChar),new SqlParameter("@.sponsor",SqlDbType.NVarChar),new SqlParameter("@.priority",SqlDbType.NVarChar),new SqlParameter("@.bidstatus",SqlDbType.NVarChar), };#endregion #region public member variables - stored procedures//private string sp_Create = "bids_sp_insert"; //private string sp_Update_ByID = "bids_sp_updatebyid"; //private string sp_Delete_ByID = "bids_sp_deletebyid"; //private string sp_Select_All = "bids_sp_selectall"; //private string sp_Select_ByID = "bids_sp_selectbyid"; //private string sp_Select_ByValue = "bids_sp_selectbyvalue";#endregion #region constructors and destructorspublic Bid() {if (this.ConnectionString ==null) {this.ConnectionString = ConfigurationManager.ConnectionStrings["SQL.ConnectionString"].ConnectionString; }else {new Bid(this.ConnectionString); } }public Bid(string ConnectionString) {this.ConnectionString = ConnectionString; }#endregion #region methods & events/// <summary> /// Attach parameters to an SqlCommand /// </summary> /// <param name="sqlcmd">SqlCommand</param>private void attachparameters(SqlCommand sqlcmd) { sqlcmd.Parameters.Clear();foreach (SqlParameter paramin _parameters) { sqlcmd.Parameters.Add(param); } }/// <summary> /// Assign value to the parameters /// </summary> /// <param name="sqlcmd">SqlCommand</param> /// <param name="value">Client object</param>private void assignparametervalues(SqlCommand sqlcmd, Bidvalue) {this.attachparameters(sqlcmd);// todo: assign parameter values here sqlcmd.Parameters[0].Value =value.Client; sqlcmd.Parameters[1].Value =value.Contact; sqlcmd.Parameters[2].Value =value.Sponsor; sqlcmd.Parameters[3].Value =value.Priority; sqlcmd.Parameters[4].Value =value.BidStatus; }public virtual void Create(Bidvalue) { _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."INSERT INTO bids (client, contact, sponsor, priority, bidstatus, createdby) VALUES (@.client, @.contact, @.sponsor, @.priority, @.bidstatus, @.createdby);"; _command.CommandType = CommandType.Text;try { sqlda.InsertCommand = _command;this.assignparametervalues(sqlda.InsertCommand,value); sqlda.InsertCommand.Parameters.AddWithValue("@.createdby",value.CreatedBy); sqlda.InsertCommand.Parameters.Add("@.bidid", SqlDbType.Int); sqlda.InsertCommand.Parameters["@.bidid"].Direction = ParameterDirection.ReturnValue; sqlda.InsertCommand.Connection.Open(); sqlda.InsertCommand.ExecuteNonQuery(); sqlda.InsertCommand.Connection.Close(); sqlda.InsertCommand.Connection.Dispose(); }catch (Exception e) {throw new Exception(e.Message); } }public virtual int Update(Bidvalue) {int result; _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."UPDATE bids SET client=@.client, contact=@.contact, sponsor=@.sponsor, priority=@.priority, bidstatus=@.bidstatus WHERE bidid=@.bidid"; _command.CommandType = CommandType.Text;try { sqlda.UpdateCommand = _command;using (SqlCommand _cmd = sqlda.UpdateCommand) {this.assignparametervalues(_cmd,value); _cmd.Parameters.AddWithValue("@.bidid",value.BidID); _cmd.Connection.Open(); result = _cmd.ExecuteNonQuery(); _cmd.Connection.Close(); _cmd.Connection.Dispose(); } }catch (Exception e) {throw new Exception(e.Message); }return result; }public virtual void Delete(Bidvalue) { _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."DELETE bids WHERE bidid=@.bidid;"; _command.CommandType = CommandType.Text;try { sqlda.DeleteCommand = _command;using (SqlCommand _cmd = sqlda.DeleteCommand) { _cmd.Parameters.AddWithValue("@.bidid",value.BidID); _cmd.Connection.Open(); _cmd.ExecuteNonQuery(); _cmd.Connection.Close(); _cmd.Connection.Dispose(); } }catch (Exception e) {throw new Exception(e.Message); } }public virtual DataSet List(string filter) { DataSet result =new DataSet(); _command =new SqlCommand(); _command.Connection =this.Connection; _command.CommandText = @."SELECT * FROM bids_view_listall WHERE (clientname like @.value) or (contactname like @.value) or (sponsor like @.value) ORDER BY datecreated DESC"; _command.CommandType = CommandType.Text;try {if (filter !=null) { filter = filter.Replace(" ","%"); } sqlda.SelectCommand = _command; sqlda.SelectCommand.Parameters.AddWithValue("@.value","%" + filter +"%"); sqlda.Fill(result); }catch (Exception e) {throw new Exception(e.Message); }return (DataSet)result; }#endregion #region IBid Memberspublic string Client {get {return _Client; }set { _Client =value; } }public string Contact {get {return _Contact; }set { _Contact =value; } }public string Sponsor {get {return _Sponsor; }set { _Sponsor =value; } }public string Priority {get {return _Priority; }set { _Priority =value; } }public string BidStatus {get {return _BidStatus; }set { _BidStatus =value; } }#endregion #region Propertiespublic int BidID {get {return _BidID; }set { _BidID =value; } }public string DateCreated {get {return _DateCreated; }set { _DateCreated =value; } }public string CreatedBy {get {return _CreatedBy; }set { _CreatedBy =value; } }#endregion }}_____________________________________________
Am I on a right path? I will greatly appreciate in any comments or suggestions for a design. I am creating an n-tier application and i don't know if my design is right. I dont have the proper schooling for creating this kind of applications and I am still on layer of data access and business logic.
I tested it on a console application and web app, for now its working. But, how about if a change a different type of database? I studied some sample applications downloaded online and a little bit of reading from a book.
Although, I already completed a past project with a module (dll) for a web application and already have implemented it.
I am still confused on how to implement a data library that can be used in different type of applications and services (windows, web) and can change the type of database by changing some configuration and not change the code itself.
I there someone who can show me a pattern for creating a library that can be used to any type of applications. It will be a great help for me and I will greatly appreciate it.
since no one replied, i have to search it for my self
http://www.dofactory.com/Patterns/Patterns.aspx.
I still didn't buy it, but, are these kind of books going to help me on what am i searching for?
|||Sorry did not reply you sooner but here are some good samples, and the database agnostic stuff not that simple but check the Achitecture forum for references to Petshop a Java application converted to .NET by Microsoft. And good books I prefer Martin Fowler books but he is not a beginner's writer the best beginner's book is by Craig Larman because he is complete.
http://www.microsoft.com/belux/msdn/nl/community/columns/hyatt/ntier1.mspx
http://www.microsoft.com/belux/msdn/nl/community/columns/hyatt/ntier2.mspx
http://www.microsoft.com/belux/msdn/nl/community/columns/hyatt/ntier3.mspx
http://www.dotnetbips.com/articles/displayarticle.aspx?id=515
Free Objects development resources, Post again if you still have question. Hope this helps.
http://forums.asp.net/thread/1462028.aspx
|||Thanks for your help.
jd2001:
Thanks for your help.
I am glad I could help
Friday, March 23, 2012
Need SQL Server 2005 tool similar to Oracle's Connection Manager
I would like to know if there is a comparable tool in SQL Server 2005 to the
Oracle Connection Manager (link to Overview on Oracle product below)
http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htm
Hi
"Jason" wrote:
> I would like to know if there is a comparable tool in SQL Server 2005 to the
> Oracle Connection Manager (link to Overview on Oracle product below)
> http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htm
>
Apart from connection pooling I don't know of any third party product that
does anything like this.
For example see http://msdn2.microsoft.com/en-us/library/ms716319.aspx
John
sql
Oracle Connection Manager (link to Overview on Oracle product below)
http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htm
Hi
"Jason" wrote:
> I would like to know if there is a comparable tool in SQL Server 2005 to the
> Oracle Connection Manager (link to Overview on Oracle product below)
> http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htm
>
Apart from connection pooling I don't know of any third party product that
does anything like this.
For example see http://msdn2.microsoft.com/en-us/library/ms716319.aspx
John
sql
Need SQL Server 2005 tool similar to Oracle's Connection Manager
I would like to know if there is a comparable tool in SQL Server 2005 to the
Oracle Connection Manager (link to Overview on Oracle product below)
http://www.oracle.com/technology/pr...an_overview.htmHi
"Jason" wrote:
> I would like to know if there is a comparable tool in SQL Server 2005 to t
he
> Oracle Connection Manager (link to Overview on Oracle product below)
> Apart from connection pooling I don't know of any third party product thatdoes anything like this.For example see [url]http://msdn2.microsoft.com/en-us/library/ms716319.aspx" target="_blank">http://www.oracle.com/technology/pr...y/ms716319.aspx
John
Oracle Connection Manager (link to Overview on Oracle product below)
http://www.oracle.com/technology/pr...an_overview.htmHi
"Jason" wrote:
> I would like to know if there is a comparable tool in SQL Server 2005 to t
he
> Oracle Connection Manager (link to Overview on Oracle product below)
> Apart from connection pooling I don't know of any third party product thatdoes anything like this.For example see [url]http://msdn2.microsoft.com/en-us/library/ms716319.aspx" target="_blank">http://www.oracle.com/technology/pr...y/ms716319.aspx
John
Need SQL Server 2005 tool similar to Oracle's Connection Manager
I would like to know if there is a comparable tool in SQL Server 2005 to the
Oracle Connection Manager (link to Overview on Oracle product below)
http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htmHi
"Jason" wrote:
> I would like to know if there is a comparable tool in SQL Server 2005 to the
> Oracle Connection Manager (link to Overview on Oracle product below)
> http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htm
>
Apart from connection pooling I don't know of any third party product that
does anything like this.
For example see http://msdn2.microsoft.com/en-us/library/ms716319.aspx
John
Oracle Connection Manager (link to Overview on Oracle product below)
http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htmHi
"Jason" wrote:
> I would like to know if there is a comparable tool in SQL Server 2005 to the
> Oracle Connection Manager (link to Overview on Oracle product below)
> http://www.oracle.com/technology/products/oraclenet/htdocs/cman_overview.htm
>
Apart from connection pooling I don't know of any third party product that
does anything like this.
For example see http://msdn2.microsoft.com/en-us/library/ms716319.aspx
John
Monday, March 19, 2012
Need simple connect/SQL Select in aspx.vb
I have searched for what I think is a simple solution.
In my aspx.vb code page, I need to make a connection to SQL Server (my connection strings are in the web.config file) and then make a simple SQL Select to return one or 2 values.
For example, the user will select a customer on the ASP form, then I need to read a couple of default values from that user's record and display them back onto the form.
Can someone provide the sample code or direct me to this? I'm thinking that this is rather simple, but I can't seem to make it work. If I see a sample of exactly what I need to do, such as reading a value using the Northwind sample DB, then I can modify it to fit my table structure, etc.
Thank you.
Hi,
Here is a sample code that you can run on a web form which reads an Id value from a textbox and then returns the firstname and lastname of the customer.
PrivateSub Button5_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles Button5.ClickDim sqlConnectionStringAsString = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString")Dim sqlConnectionAs SqlClient.SqlConnection =New SqlClient.SqlConnection(sqlConnectionString)Dim sqlCommandAs SqlClient.SqlCommand =New SqlClient.SqlCommand("SELECT FirstName, LastName FROM Customers Where CustomerId = @.CustomerId")Dim sqlParamAs SqlClient.SqlParameter =New SqlClient.SqlParameter("@.CustomerId",CInt(txtCustomerId.Text))sqlCommand.Parameters.Add(sqlParam)
sqlCommand.Connection = sqlConnection
sqlConnection.Open()
Dim drAs SqlClient.SqlDataReaderdr = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
While dr.ReadtxtFirstName.Text =
CType(dr.GetValue(dr.GetOrdinal("FirstName")),String)txtLastName.Text =
CType(dr.GetValue(dr.GetOrdinal("LastName")),String)EndWhiledr.Close()
EndSub
You can use the below line of code to get the connection string from the web.config file
System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString")
You should have placed the below configuration declaration within the configuration tags (<configuration></configuration>)
<appSettings><addkey="ConnectionString"value="..."/></appSettings>
Eralper
http://www.kodyaz.com
|||
How come my intellisense does not automatically pop-up the wording 'SqlClient...' in my dim statements. Nothing seems to be wrong with my set up as I have been working fine for months, but now that I am trying to ad-hoc querries I don't have access to these facilities..
Dim sqlConnectionAsSqlClient.SqlConnection =New SqlClient.SqlConnection(sqlConnectionString)Dim sqlCommandAsSqlClient.SqlCommand =New SqlClient.SqlCommand("SELECT FirstName, LastName FROM Customers Where CustomerId = @.CustomerId")Dim sqlParamAsSqlClient.SqlParameter =New SqlClient.SqlParameter("@.CustomerId",CInt(txtCustomerId.Text))
|||
Never mind, I just had to add
Imports System.Data
Imports System.Data.SqlClient
Friday, March 9, 2012
Need opinion about this design "Thread"
Hi all,
I am having problem with SQL connection at Godaddy where my pool connection gets MAX OUT. When it happens, I cannot access the database.
This is the thread about the problem:http://forums.asp.net/thread/1665023.aspx
I just created this with "THREAD". I hope someone who has experiences with thread can give me some advice about my design. This is my first time.
staticobject Locked =newobject();
publicobject ExecuteCommand(string queryStr,string type){
//*************************************************************************************//
// ExecuteCommand: Returns an object //
//*************************************************************************************//
Thread t =null;
lock(Locked)
{
SQLString = queryStr;
switch(type)
{
case"ExecuteNonQuery":
t =newThread(ExecuteNonQuery);
break;
case"ExecuteScalar":
t =newThread(ExecuteScalar);
break;
case"GetDataReader":
t =newThread(GetDataReader);
break;
}
t.Start();
t.Join();
}
returnnull;
}
First of, does this work at all? It runs, but is it a good design in term of Thread? Since I use LOCK, do I still need the t.Join() function? The switch with the three cases, is that OKAY? Basically, I'm clueless. If you read my other post, you will get an idea what I'm trying to do. Any feedback would be very very appreciated.
Thank you.
It looks a little odd to me.
You start a thread, and then immediately you do a "join" which effectively says "wait for the thread to finish before continuing". Why not just call a function instead of messing with a thread? The effect is the same and it is simpler.
Make sure you are Closing or Disposing any and all database connections when you are done using them. The Using statement is the most reliable way of doing this.
|||SGWellen,
Thank you for the input. I use the "join" right after the "start" because I saw it on several tutorials. The "start" function automatically calls the function that is assign in the switch statement.
If I'm not wrong, I think I don't even need to "join" function there, because the whole script is inside the LOCK. Hmmmm I guess I need to dig in it further. Thank you SGWellen.
Need Novell Network Connection without logging on first
I am new to SSIS but this does not seem like it should be a hard thing to do. Here is the background:
Windows Server 2003
SQL Server 2005
SSIS
Logged in to server as Admin
I've created a SSIS package that I want to run as a Job. In it there is a Ole DB Connection to a VFP Database on a Novell Server. Client for Netware Services is started when server is but NOT logged into. When I created the package everything runs fine. Note that I had to login when I created the Data Link. I can even run it from the SQL Server Package Store. As soon as I restart the server and try to run it I get an error that the file path is invalid in both SSIS and the Package Store. I check the connection string and it looks good.
The connection is availible in My Network Places
If I open the connection and login it runs fine again.
How do I intiate the network connection and then close it again?
Cory Bonallo
Retail Services
Developer
Aren't there some NET USE commands (or NetWare equivalents) that you could use to create the connection without having to log in?|||
I've never used anything like that. Should there be a setting or something in the SSIS connection manager? Do you have any examples? Thanks for your help!
Cory Bonallo
Retail Services
Developer
|||
Cory Bonallo wrote:
I've never used anything like that. Should there be a setting or something in the SSIS connection manager? Do you have any examples? Thanks for your help!
No, this is more in the Windows arena than SSIS. Taking the Novell client out of the picture for the moment, when you're not logged into the machine, none of your drive mappings are active since those are user-specific. Your package may work fine when you're logged it, but when it tries to run unattended with nobody logged in, it can no longer access mapped drives and fails.
This comes up from time to time here on the forum, but the search is failing me at the moment for good threads. This one applies, but isn't a stellar example.
With the Windows client, the NET USE command (try "net use /?" from the Windows command prompt) can be used to make these connections. If you're running the SSIS package from SQL Agent, you can use CmdExec steps before your package to create a connection with NET USE and one after the package to delete it.
I have no idea how having the NetWare client in the picture might change things. I don't know if NET USE will work or if there is a different command you have to use to accomplish this.
|||
Well I thought that did it. But now I can't get it to run in a job step. I cant seem to figure out the syntax or something for cmdExec.
Here is what I did:
in dos window typed "net use \\jds1\vol3 /user:admin" works great!
Created a job step as Operating System (cmd Exec) and typed in:
"net use" \\jds1\vol3 /user:admin
Got this error:
So I tried several other changes and still same error...
What am I missing?
Cory
|||Try:"C:\WINDOWS\system32\net.exe" use \\server\share /user:admin
"net use" is not a command. "use" is an argument to net.exe.|||
Thank you Phil. The more I use this tool the dumber I feel! So now I don't get an error but it just sits on that step. I'm assuming it has something to do with a return success code. Try as I might I just can't find any examples or instructions on how to code for this. Is there a way to reture a success code. I left it a zero because the articles I found said that was the default success code. Agail thanks for your help!
Cory
|||
Thank you Phil. The more I use this tool the dumber I feel! So now I don't get an error but it just sits on that step. I'm assuming it has something to do with a return success code. Try as I might I just can't find any examples or instructions on how to code for this. Is there a way to reture a success code. I left it a zero because the articles I found said that was the default success code. Agail thanks for your help!
Cory
I don't think its related to the return code. I think it probably isn't returning. I see you're specifying a user name but no password. The Net.exe may be prompting and waiting for a response. Try passing the password too.
"C:\WINDOWS\system32\net.exe" use \\server\share /user:admin mypassword
|||
You were right. It was the password. Now can you tell me why the connection is not persisting to the next job step? So here is what I want to do:
Step 1: open network connection
Step 2: run dtsx package that uses that connection
Step 3: Close network connection
Seems simple enough?
Cory
|||Look at either mapping a drive to that share, or using the /PERSISTENT:YES flag.|||
Look at either mapping a drive to that share, or using the /PERSISTENT:YES flag.
Or combining all three into a single step? Perhaps by putting them in a batch file?
|||
Or combining all three into a single step? Perhaps by putting them in a batch file?
Yep.|||
Thabk you both very much. What I ended up doing is adding an Excute Process Task to Open the connection, run the rest of the steps of the package and then close the connection. Thanks again to all!!!
Cory
|||I did get the net use working in the Job as a part of an SSIS package. Now I am trying to use the same process to open a connection to Novell in the Query editor and setting up a stored procedure that a Web service can call to get a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the command succeeds but does not make the conncetion availible to any other session. Then when I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. When I used impersonate it did not change the user from NT AUTHORITY/? I can't a channel opened.
Cory
|||I did get the net use working in the Job as a part of an SSIS package. Now I am trying to use the same process to open a connection to Novell in the Query editor and setting up a stored procedure that a Web service can call to get a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the command succeeds but does not make the conncetion availible to any other session. Then when I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. When I used impersonate it did not change the user from NT AUTHORITY/? I can't a channel opened.
Cory
Need Novell Network Connection without logging on first
I am new to SSIS but this does not seem like it should be a hard thing to do. Here is the background:
Windows Server 2003
SQL Server 2005
SSIS
Logged in to server as Admin
I've created a SSIS package that I want to run as a Job. In it there is a Ole DB Connection to a VFP Database on a Novell Server. Client for Netware Services is started when server is but NOT logged into. When I created the package everything runs fine. Note that I had to login when I created the Data Link. I can even run it from the SQL Server Package Store. As soon as I restart the server and try to run it I get an error that the file path is invalid in both SSIS and the Package Store. I check the connection string and it looks good.
The connection is availible in My Network Places
If I open the connection and login it runs fine again.
How do I intiate the network connection and then close it again?
Cory Bonallo
Retail Services
Developer
Aren't there some NET USE commands (or NetWare equivalents) that you could use to create the connection without having to log in?|||
I've never used anything like that. Should there be a setting or something in the SSIS connection manager? Do you have any examples? Thanks for your help!
Cory Bonallo
Retail Services
Developer
|||
I've never used anything like that. Should there be a setting or something in the SSIS connection manager? Do you have any examples? Thanks for your help!
No, this is more in the Windows arena than SSIS. Taking the Novell client out of the picture for the moment, when you're not logged into the machine, none of your drive mappings are active since those are user-specific. Your package may work fine when you're logged it, but when it tries to run unattended with nobody logged in, it can no longer access mapped drives and fails.
This comes up from time to time here on the forum, but the search is failing me at the moment for good threads. This one applies, but isn't a stellar example.
With the Windows client, the NET USE command (try "net use /?" from the Windows command prompt) can be used to make these connections. If you're running the SSIS package from SQL Agent, you can use CmdExec steps before your package to create a connection with NET USE and one after the package to delete it.
I have no idea how having the NetWare client in the picture might change things. I don't know if NET USE will work or if there is a different command you have to use to accomplish this.
|||
Well I thought that did it. But now I can't get it to run in a job step. I cant seem to figure out the syntax or something for cmdExec.
Here is what I did:
in dos window typed "net use \\jds1\vol3 /user:admin" works great!
Created a job step as Operating System (cmd Exec) and typed in:
"net use" \\jds1\vol3 /user:admin
Got this error:
So I tried several other changes and still same error...
What am I missing?
Cory
|||Try:"C:\WINDOWS\system32\net.exe" use \\server\share /user:admin
"net use" is not a command. "use" is an argument to net.exe.|||
Thank you Phil. The more I use this tool the dumber I feel! So now I don't get an error but it just sits on that step. I'm assuming it has something to do with a return success code. Try as I might I just can't find any examples or instructions on how to code for this. Is there a way to reture a success code. I left it a zero because the articles I found said that was the default success code. Agail thanks for your help!
Cory
|||
Thank you Phil. The more I use this tool the dumber I feel! So now I don't get an error but it just sits on that step. I'm assuming it has something to do with a return success code. Try as I might I just can't find any examples or instructions on how to code for this. Is there a way to reture a success code. I left it a zero because the articles I found said that was the default success code. Agail thanks for your help!
Cory
I don't think its related to the return code. I think it probably isn't returning. I see you're specifying a user name but no password. The Net.exe may be prompting and waiting for a response. Try passing the password too.
"C:\WINDOWS\system32\net.exe" use \\server\share /user:admin mypassword
|||
You were right. It was the password. Now can you tell me why the connection is not persisting to the next job step? So here is what I want to do:
Step 1: open network connection
Step 2: run dtsx package that uses that connection
Step 3: Close network connection
Seems simple enough?
Cory
|||Look at either mapping a drive to that share, or using the /PERSISTENT:YES flag.|||
Look at either mapping a drive to that share, or using the /PERSISTENT:YES flag.
Or combining all three into a single step? Perhaps by putting them in a batch file?
|||
Or combining all three into a single step? Perhaps by putting them in a batch file?
Yep.|||
Thabk you both very much. What I ended up doing is adding an Excute Process Task to Open the connection, run the rest of the steps of the package and then close the connection. Thanks again to all!!!
Cory
|||I did get the net use working in the Job as a part of an SSIS package. Now I am trying to use the same process to open a connection to Novell in the Query editor and setting up a stored procedure that a Web service can call to get a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the command succeeds but does not make the conncetion availible to any other session. Then when I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. When I used impersonate it did not change the user from NT AUTHORITY/? I can't a channel opened.
Cory
|||I did get the net use working in the Job as a part of an SSIS package. Now I am trying to use the same process to open a connection to Novell in the Query editor and setting up a stored procedure that a Web service can call to get a connection to an access.mdb linked server on the Novell drive. When I try to execute via xp_cmdshell the command succeeds but does not make the conncetion availible to any other session. Then when I ran it in a batch to do a simple file copy the net use command succeeded but the copy failed. When I used impersonate it did not change the user from NT AUTHORITY/? I can't a channel opened.
Cory
Wednesday, March 7, 2012
Need info about odbc and sql through access2k
I recently started with a company that uses access for a front end and sql2000 for the back. They have set up an ODBC connection though access2k to reach sql2k. This works fine but fouls up in regards to the record locking. You can set record locking in access but it has no effect. In the office help it says 'data in a form, report, or query from an Open Database Connectivity (ODBC) database is treated as if the No Locks setting were chosen, regardless of the RecordLocks property setting.'
Also, the access lock option info says it 'could' lock the few records around the record you wish to lock.
So my question is this: How can i (without destroying this ODBC link from access2k to sql2k) get locks for individual records to work successfully?
any info would be great, thankshttp://advisor.com/doc/13043 for information about record locking in Access.
For SQL server http://www.sql-server-performance.com/reducing_locks.asp and
http://www.databasejournal.com/features/mssql/article.php/3289661
HTH
Also, the access lock option info says it 'could' lock the few records around the record you wish to lock.
So my question is this: How can i (without destroying this ODBC link from access2k to sql2k) get locks for individual records to work successfully?
any info would be great, thankshttp://advisor.com/doc/13043 for information about record locking in Access.
For SQL server http://www.sql-server-performance.com/reducing_locks.asp and
http://www.databasejournal.com/features/mssql/article.php/3289661
HTH
Saturday, February 25, 2012
Need Help! Connection Count to Linked DB2 Server Reaches limit (SQL1040N) when execute dyn
Need Help! Connection Count to Linked DB2 Server Reaches limit
(SQL1040N) when execute dynamic generated OPENQUERY T-SQL
Hello, every body.
I created a linked server to DB2 8.1 database which called
GRR_DB2Server. In my stored procedure p_FetchRawData, I need to read
some data from this linked server GRR_DB2Server and insert them into
local SQLServer table SQLServer_A.
Query to GRR_DB2Server joins 3 large DB2 tables DB2_A, DB2_B, DB2_C
(every table has about 1 million records), and part of the query
condition stored as record in table SQLServer_B in local SQLServer.
At first I directly join these 4 tables in one T-SQL statements, but to
my disappointment I found the performance very low afer some practice.
So I changed the T-SQL to use cursor to loop for fetching every row
data in SQLServer_D condition table to some procedure variables, and
then in this loop I generated dynamic T-SQL string which orgnize the
condition and form one OPENQUERY statement.
The pseud code something like this:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC @.Dynamic_SQL
END
.=2E..
END
But when execute this stored procedure p_FetchRawData, when the loop
count is too big, then I got the error:
[OLE/DB provider returned message: SQL1040N
=E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94=E7= =94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6=95= =B0=E7=9B=AE=E3=80=82
SQLSTATE=3D57030]
OLE DB error trace [OLE/DB Provider 'IBMDADB2'
IDBInitialize::Initialize returned 0x80040e69].
I understood this error meaning which said too many OPENQUERY
connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
their connections to linked server? How to fail these connections when
every OPENQUERY execution finished?
Thanks.
Regards,
Ling, Xiao-liSorry, the pseud code should lik this, just pseud code, in case someone
will question the pseud code validity:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC (@.Dynamic_SQL)
FETCH NEXT FROM condition_cursor INTO
local_variables
END
.=2E..
END
alingsjtu@.gmail.com =E5=86=99=E9=81=93=EF=BC=9A
> CREATE PROCEDURE p_FetchRawData variable_list
> AS
> BEGIN
> ...
> DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
> SELECT * FROM local_condition_table
> OPEN condition_cursor
> FETCH NEXT FROM condition_cursor INTO
> local_variables
> WHILE @.@.FETCH_STATUS =3D 0
> BEGIN
> SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
> SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
> ')'
> EXEC @.Dynamic_SQL
> END
> ...
> END
> But when execute this stored procedure p_FetchRawData, when the loop
> count is too big, then I got the error:
> [OLE/DB provider returned message: SQL1040N
> =E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94==E7=94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6==95=B0=E7=9B=AE=E3=80=82
> SQLSTATE=3D57030]
> OLE DB error trace [OLE/DB Provider 'IBMDADB2'
> IDBInitialize::Initialize returned 0x80040e69].
> I understood this error meaning which said too many OPENQUERY
> connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
> their connections to linked server? How to fail these connections when
> every OPENQUERY execution finished?
> > Thanks.
> > Regards,
> Ling, Xiao-li
(SQL1040N) when execute dynamic generated OPENQUERY T-SQL
Hello, every body.
I created a linked server to DB2 8.1 database which called
GRR_DB2Server. In my stored procedure p_FetchRawData, I need to read
some data from this linked server GRR_DB2Server and insert them into
local SQLServer table SQLServer_A.
Query to GRR_DB2Server joins 3 large DB2 tables DB2_A, DB2_B, DB2_C
(every table has about 1 million records), and part of the query
condition stored as record in table SQLServer_B in local SQLServer.
At first I directly join these 4 tables in one T-SQL statements, but to
my disappointment I found the performance very low afer some practice.
So I changed the T-SQL to use cursor to loop for fetching every row
data in SQLServer_D condition table to some procedure variables, and
then in this loop I generated dynamic T-SQL string which orgnize the
condition and form one OPENQUERY statement.
The pseud code something like this:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC @.Dynamic_SQL
END
.=2E..
END
But when execute this stored procedure p_FetchRawData, when the loop
count is too big, then I got the error:
[OLE/DB provider returned message: SQL1040N
=E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94=E7= =94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6=95= =B0=E7=9B=AE=E3=80=82
SQLSTATE=3D57030]
OLE DB error trace [OLE/DB Provider 'IBMDADB2'
IDBInitialize::Initialize returned 0x80040e69].
I understood this error meaning which said too many OPENQUERY
connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
their connections to linked server? How to fail these connections when
every OPENQUERY execution finished?
Thanks.
Regards,
Ling, Xiao-liSorry, the pseud code should lik this, just pseud code, in case someone
will question the pseud code validity:
CREATE PROCEDURE p_FetchRawData variable_list
AS
BEGIN
.=2E..
DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
SELECT * FROM local_condition_table
OPEN condition_cursor
FETCH NEXT FROM condition_cursor INTO
local_variables
WHILE @.@.FETCH_STATUS =3D 0
BEGIN
SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
')'
EXEC (@.Dynamic_SQL)
FETCH NEXT FROM condition_cursor INTO
local_variables
END
.=2E..
END
alingsjtu@.gmail.com =E5=86=99=E9=81=93=EF=BC=9A
> CREATE PROCEDURE p_FetchRawData variable_list
> AS
> BEGIN
> ...
> DECLARE condition_cursor CURSOR LOCAL FORWARD_ONLY FOR
> SELECT * FROM local_condition_table
> OPEN condition_cursor
> FETCH NEXT FROM condition_cursor INTO
> local_variables
> WHILE @.@.FETCH_STATUS =3D 0
> BEGIN
> SET @.Dynamic_SQL =3D 'SET IMPLICIT_TRANSACTIONS OFF INSERT INTO
> SQLServer_A SELECT * FROM OPENQUERY (GRR_DB2Server, ' + @.Dynamic_STR +
> ')'
> EXEC @.Dynamic_SQL
> END
> ...
> END
> But when execute this stored procedure p_FetchRawData, when the loop
> count is too big, then I got the error:
> [OLE/DB provider returned message: SQL1040N
> =E4=B8=8E=E6=95=B0=E6=8D=AE=E5=BA=93=E7=9B=B8=E8=BF=9E=E7=9A=84=E5=BA=94==E7=94=A8=E7=A8=8B=E5=BA=8F=E5=B7=B2=E8=BE=BE=E5=88=B0=E6=9C=80=E5=A4=A7=E6==95=B0=E7=9B=AE=E3=80=82
> SQLSTATE=3D57030]
> OLE DB error trace [OLE/DB Provider 'IBMDADB2'
> IDBInitialize::Initialize returned 0x80040e69].
> I understood this error meaning which said too many OPENQUERY
> connection. I just wonder why every DYNAMIC T-SQL EXECECUTION keeps
> their connections to linked server? How to fail these connections when
> every OPENQUERY execution finished?
> > Thanks.
> > Regards,
> Ling, Xiao-li
Subscribe to:
Posts (Atom)