Connecting to more Database

Hi ,

We have 10 web sites running and having 10 database stored in same server .
i need to check in all the 10 database that particular id has repated .
if it is repated i should not allow the user the create that id .

as it is we have 100k qry hitting the database per minute .. so have some performace issues .

if i write code to connect all the database is same file . will there be any performance issue .

or is there any other method to do that ...

Quote:

Originally Posted by rashmiks

Hi ,

We have 10 web sites running and having 10 database stored in same server .
i need to check in all the 10 database that particular id has repated .
if it is repated i should not allow the user the create that id .

as it is we have 100k qry hitting the database per minute .. so have some performace issues .

if i write code to connect all the database is same file . will there be any performance issue .

or is there any other method to do that ...

Hi there,

Kindly refer to below attached link. Please do not double post. Take care

Trigger

Connecting to Interbase from MSDE

Hi all,

I'm new to MSDE and need some help. I have a stored proc that used to sit in Interbase and when executed it would populate some tables. I now have to have these tables external to the interbase database and have chosen (for the initial phase at least) to use MSDE. I have created a table (the first of many) and now working on the procedure.

My question is how do I connect to the interbase database. I have an ODBC setup (which is used in a VB application on the client) and I assume I need to use that, but I'm not sure how to implement the connect command in the stored proc. Any ideas?

Cheers in advance,
Kevin.Just to add that I need to use a cursor within the procedure that reads the data from the Interbase database then based on the values writes to the MSDE table. Don't know if this makes it any clearer :-)

Cheers,
Kevin.

Connecting to DB Stored Proc with asp.net

I am used to using asp and i am trying to connect to a db and bring back a recordset from a stored procedure in asp.net, but I am getting the following error;

Server Error in '/' Application.

---

Object variable or With block variable not set.

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.NullReferenceException: Object variable or With block variable not set.
Source Error:

Line 58: Line 59: qry = "AWS_RPT_WEB_MAGSUBSSTATS_PROC "Line 60: rs = dbConn.Execute(qry)Line 61: %>Line 62:

Source File:D:\Program Files\IntranetDASHBOARD\wwwroot\ufr\publications\dpe\live\65.aspx Line:60
Stack Trace:

[NullReferenceException: Object variable or With block variable not set.] Microsoft.VisualBasic.CompilerServices.LateBinding.LateGet(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack) +927 ASP._65_aspx.__RenderForm1(HtmlTextWriter __output, Control parameterContainer) in D:\Program Files\IntranetDASHBOARD\wwwroot\ufr\publications\dpe\live\65.aspx:60 System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +27 System.Web.UI.HtmlControls.HtmlForm.RenderChildren(HtmlTextWriter writer) +44 System.Web.UI.HtmlControls.HtmlForm.Render(HtmlTextWriter output) +260 System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +241 ASP._65_aspx.__Render__control1(HtmlTextWriter __output, Control parameterContainer) in D:\Program Files\IntranetDASHBOARD\wwwroot\ufr\publications\dpe\live\65.aspx:52 System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +27 System.Web.UI.Control.Render(HtmlTextWriter writer) +7 System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +241 System.Web.UI.Page.ProcessRequestMain() +1926

Check the following :

Whether the appropriate dll's for data provider such as System.Data.dll,System.Data.OracleClient.dll are added to the references.

And make sure your connection object has connectionString property

Make sure the connection is opened prior to execute statement

|||

...and have you properly created "dbConn"?

Connecting to a different stored procedure at runtime

Hello..
I am using Crystal Reports 10. Am running the report through some asp pages. The report is tied to a stored procedure at design time. Can I make the report connect to a different stored procedure at runtime? Both these stored procs have the same output in terms of columns. They differ only in the data.
Thanks,
ManishTry this
In your Application Use

CR.Database.tables(0)="SP Name"

Connecting Payroll Data to Web

Hi,

We are currently trying to make payroll data stored on an SQL server available via a secured web site for our employees. This will allow them to view their direct deposit information by visiting a particular .asp page running on our web site. In order to enable this, we must create a connection to the SQL server. In order to verify the connection, we have created an Access ADP that will allow us to test our connection parameters. Once we can connect in Access we can write a connection string for use in our .asp pages. We believe that the following connection parameters should connect to the SQL server:

Server Name: hsa1.***.com:1433
User Name: payroll-web
Password: ***
Database: HSA

We have already created the payroll-web user name on the SQL server and given it permissions to access the HSA database running on the SQL server. However, when we create an Access ADP using these settings we receive the following error:

"Test failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen(ParseConnectParams())]Invalid connection."

At this point we are unaware of the cause of this error and have tried several different workarounds, none of which have worked:

Replaced hsa1.***.com:1433 with hsa1.***.com, 1433
Replaced payroll-web and *** with Administrator and ***
Removed HSA
Selected NT/Challenge Response Security instead of provising a user name and password

Does anyone have any ideas as to (i.) why this error is occuring and (ii.) what we can do to solve it and connect to the SQL Server using an Access ADP? Thanks so much for your help!

Warm Regards,

Kristopher A. Tillery
Director of E-Commerce, Harvard Student Agencies

Email: tillery@.fas.harvard.edu
Phone: +1 603 205 0228
Post: 359 Quincy Mail Center, Cambridge MA
Web: http://www.hsa.netI would suggest (on the machine acting as the client, probably your web server) that you use the SQL Client Configuration Tool to change the default network protocol from its current value of Named Pipes to TCP/IP.

-PatP|||Hi,

Thanks for the response. On the SQL Server we have don't have a "Client Configuration Tool" but we do have a "Client Network Utility." that is available in the SQL Server program group on the Start Menu. This utility shows that TCP/IP is already enabled as the first-choice protocol. Any ideas? Thanks again.|||I'm not sure, but according to msdn, your server doesn't exist. http://support.microsoft.com/default.aspx?scid=kb;en-us;275118

When you created the ADP, didn't you provide the server information at that time?|||The [DBNETLIB] in your error message indicates that the connection is being made using the Named Pipes protocol library.

Your hsa1.***.com:1433 server name is in DNS format. Typically, server names in this context are in WINS format so that the enumerator can find them without forcing a DNS resolution. It is also quite uncommon to have the database on a DMZ server, the database typically resides inside the firewall. How confident are you that the name you are using is correct for your database server?

Which version of MS-Access are you using? Have you applied the most current set of service patches to it from Microsoft?

If your SQL Client Network Utility specifies TCP/IP as the preferred protocol (listed at the top of the right hand protocol list, and there isn't a defined alias (on the second tab) for your server, then I'm not sure why you aren't using TCP/IP. That may be your whole problem, but I can't figure out what I'm missing.

-PatP|||The [DBNETLIB] in your error message indicates that the connection is being made using the Named Pipes protocol library.

Your hsa1.***.com:1433 server name is in DNS format. Typically, server names in this context are in WINS format so that the enumerator can find them without forcing a DNS resolution. It is also quite uncommon to have the database on a DMZ server, the database typically resides inside the firewall. How confident are you that the name you are using is correct for your database server?

Which version of MS-Access are you using? Have you applied the most current set of service patches to it from Microsoft?

If your SQL Client Network Utility specifies TCP/IP as the preferred protocol (listed at the top of the right hand protocol list, and there isn't a defined alias (on the second tab) for your server, then I'm not sure why you aren't using TCP/IP. That may be your whole problem, but I can't figure out what I'm missing.

-PatP|||Create a UDL file on the desktop of the client machine, form a valid connection, test it, and then open this file in notepad.

Client Network Configuration on SQL box has nothing to do whether the server listens on TCP or not. This is configured using Server Network Utility on the SQL box.|||Client Network Configuration on SQL box has nothing to do whether the server listens on TCP or not. This is configured using Server Network Utility on the SQL box.True, but based on the error message the connection process hasn't gotten far enough that it matters if the SQL server is listening or not... At least as far as I can tell, we're still trying to get the client machine to that point in the process.

-PatP|||Your hsa1.***.com:1433 server name is in DNS format. Typically, server names in this context are in WINS format so that the enumerator can find them without forcing a DNS resolution. It is also quite uncommon to have the database on a DMZ server, the database typically resides inside the firewall. How confident are you that the name you are using is correct for your database server?

Which version of MS-Access are you using? Have you applied the most current set of service patches to it from Microsoft?

If your SQL Client Network Utility specifies TCP/IP as the preferred protocol (listed at the top of the right hand protocol list, and there isn't a defined alias (on the second tab) for your server, then I'm not sure why you aren't using TCP/IP. That may be your whole problem, but I can't figure out what I'm missing.

Thanks for this message. I'm afraid this conversation has surpassed my technical knowledge a little bit. Please explain DNS vs. WINS format.

What I do know is that hsa1.***.com resolves to the IP address of the SQL server. If desired, I can use the IP address of the server directly but this doesn't prevent the original error.

I am using the most recent version of Access and connect to other SQL servers (externally hosted) regularly.

My server does specify TCP/IP as the top-right selection in the Client Network Utility. There is no defined alias in teh second tab. I don't understand what you mean by "why you aren't using TCP/IP," I thought I was?

This is getting confusing, but thanks so much. I look forward to hearig from you.|||Create a UDL file on the desktop of the client machine, form a valid connection, test it, and then open this file in notepad.

Client Network Configuration on SQL box has nothing to do whether the server listens on TCP or not. This is configured using Server Network Utility on the SQL box.

Thanks for the message. Do you have any more specific instructions on the creation of a UDL file? I am not familiar with this process. Also, do you know how to access the Server Netwok Utility on the SQL server? It's not listed in the SQL Server program group. Does it work within another program? Do I need to install it? Is there a different way to access it?

Thanks!|||Create a new TEXT document on your desktop by right-mouse-clicking on it, selecting New, Text document. Change the extension from .TXT to .UDL and you'll see how the default TEXT icon will change to Microsoft Data Link icon.

For SQL Server Network configuration look for svrnetcn.exe. Most likely it's in "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\svrnetcn.exe"|||Because hte SQL Client Network Utility has no alias for your server, and shows TCP/IP as the preferred network protocol (the top protocol in the enabled list), I would expect connections to be made using TCP/IP unless something intervened. This intervention could happen at the DSN (http://searchvb.techtarget.com/sDefinition/0,,sid8_gci874018,00.html) level, or in a connect string (http://support.microsoft.com/default.aspx?scid=kb;en-us;193128) to override the default library specified in the Client Network Utility.

The [DBNETLIB] string in your error message means that the client machine is trying to connect via the Named Pipes protocol library. That makes me think that something must have overridden the default protocol library and specified the use of Named Pipes. Unfortunately, I don't have a clue where that might have happened.

Since you regularly connect to other SQL Servers, I think that the SQL connectivity has to be Ok. What exactly do you mean by the phrase "externally hosted SQL Server" (I just want to be sure I'm not making unfounded assumptions)?

As a separate issue, I'd like you to connect to the SQL server using another tool than what you have been using, preferably SQL Query Analyzer. This will confirm that the client machine can connect successfully to the SQL Server at will, which would help to put many of my other questions to rest.

If you are interested in the difference between DNS (http://searchnetworking.techtarget.com/sDefinition/0,,sid7_gci213908,00.html) and WINS (http://searchexchange.techtarget.com/sDefinition/0,,sid43_gci214128,00.html) you can click the links for definitions. You can find additional information via MSDN (http://msdn.microsoft.com/), which is a great place to visit regularly for Microsoft related technical information.

-PatP|||If client MDAC is at 2.5 or earlier then the default library IS Named Pipes. And as a rule (AGAIN!!!), - NEVER RELY ON DEFAULTS!|||WOW:

This was not at all what I expected to fix the problem. After several hours of research, we found out through the server logs that the server was not listening on port 1433 because:

Error: 17826, Description: ods Could not set up ListenOn connection "1433". Operating system error 10013., An attempt was made to access a socket in a way forbidden by its access permissions." - This error can be solved by adding the MSSqlServer Service Account to the local administrators group.

So, we fixed the necessary permissions and are good to go. Thanks for the help.

Connecting a user from a backup database to a new login

All,
In SQL Server 2000, I loaded a backup file into a new local database.
The database has an existing user which owns several stored procedures,
tables, etc.
Using SQL Server authentication, I want to create a login of the same
name as the user, so that when I login, I have access to the objects
owned by that user without having to prefix the objects with the owner
name.
For instance exec sp_mystoredproc instead of exec
ownername.sp_mystoredproc. (In fact, several of the stored procedures
exec other procedures without the owner name prefix and to go add the
owner will be a tedious process)
When I try to add login and grant the login name to the existing user
it tells me it already exists.
I cannot drop the existing user because it owns a whole bunch of
objects.
What can I do to work around this? How do I connect a login to an
existing database user? Is there a system table where I can just map
the two?
Brad> What can I do to work around this? How do I connect a login to an
> existing database user? Is there a system table where I can just map
> the two?
See the sp_change_users_login stored procedure in the Books Online. Also,
the Best Practice is to always schema-qualify objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"brad" <brad.eckrose@.gmail.com> wrote in message
news:1168719405.103237.233140@.38g2000cwa.googlegroups.com...
> All,
> In SQL Server 2000, I loaded a backup file into a new local database.
> The database has an existing user which owns several stored procedures,
> tables, etc.
> Using SQL Server authentication, I want to create a login of the same
> name as the user, so that when I login, I have access to the objects
> owned by that user without having to prefix the objects with the owner
> name.
> For instance exec sp_mystoredproc instead of exec
> ownername.sp_mystoredproc. (In fact, several of the stored procedures
> exec other procedures without the owner name prefix and to go add the
> owner will be a tedious process)
> When I try to add login and grant the login name to the existing user
> it tells me it already exists.
> I cannot drop the existing user because it owns a whole bunch of
> objects.
> What can I do to work around this? How do I connect a login to an
> existing database user? Is there a system table where I can just map
> the two?
> Brad
>

Connecting a stored procedure to another server

I created a new stored procedure on one server because this is the only
server we can create new stored procedures on, however, all of my select
statements in this procedures are on a different server. How do I execute
this stored procedure on the other server, database, table, and login?Create a REMOTE Server or LINK Server, which you may not be able to do. You
can setup a Linked server to run under the context of one account.
"Ryan D" <RyanD@.discussions.microsoft.com> wrote in message
news:C749A1D6-F81F-4F50-8CDA-6681AF057BCD@.microsoft.com...
>I created a new stored procedure on one server because this is the only
> server we can create new stored procedures on, however, all of my select
> statements in this procedures are on a different server. How do I execute
> this stored procedure on the other server, database, table, and login?
>|||Andy,
I need the syntax for this please. Do you mean to use the
sp_addlinkedserver command? And then do I use the USE statement, followed b
y
the query?
"Andy Wilbourn" wrote:

> Create a REMOTE Server or LINK Server, which you may not be able to do. Yo
u
> can setup a Linked server to run under the context of one account.
> "Ryan D" <RyanD@.discussions.microsoft.com> wrote in message
> news:C749A1D6-F81F-4F50-8CDA-6681AF057BCD@.microsoft.com...
>
>|||Off the top of my head:
1. create a linked server.
2. change your select statements to have fully qualified names.
for example,
select colA, colB
from MyLinkServer.MyDatabase.dbo.MyTable
if you need to execute a stored procedure that exists on another server or
dynamic sql and it should run on that other server, exec
MyLinkServer.master.dbo.sp_executesql MySPName/@.SQL
Hope that helps,
Joe
"Ryan D" wrote:

> I created a new stored procedure on one server because this is the only
> server we can create new stored procedures on, however, all of my select
> statements in this procedures are on a different server. How do I execute
> this stored procedure on the other server, database, table, and login?
>

Connect to SQL Server

Hello,
It occurs something from strange.
I can reach Entreprise Manager, to visualize the tables, stored procedures...
But on the other hand I cannot install the Starter Kits "Connection Problem to the database".
Connections are done with SQL Server and Windows.

Do you have an idea?
Thanks.I think the starter kits assume yopu connect using Windows Authentication - meaning the _currently logged in account_ needs rights on the DB

if you're using mixed-mode as you suggest, then no wonder. see here :

http://rtfm.atrax.co.uk/infinitemonkeys/articles/miscellaneous/981.asp

but substitute your login account for installer access