Help find better option:Views from table in various Db Viz local d

I am having performance issues Timeout, blocking , long running stored
procedures etc.
Application is written in C sharp and uses database sql2000 sp3.
I have about 25 databases, view is used that joins tables from all db with
left outer join and multiple conditions in where clause.
Online updates are taking place which again goes back to that particular
table in spec. db. Triggers are used at few places to update the view. Every
where stored procedures are used extensively instead of t-sql. In sp at
places table variables are used to minimise locks.
In the initial phase only with each database having few thousand records
user start getting timeout ,slow response & blocking complaints.
Should I copy the view to have local copy of data and have job that run
every min or 2 to update local table . In that too I am not clear that how
useful it will be to keep local copy bcz. it will grow huge in size with
growth in all db.
Very complex thing I see is continuous updates back and forth , which may
keep local copy block for most of the time or else user wan't have latest
data available in their app.
Any one who has worked with similar situation can be lot more useful,
I will highly appreciate any suggestions or discussions from sql experts.
Thanks,Views can be good or bad. If you follow the rules for creating partitioned
views then only the needed table(s) will be scanned. Else, every table in a
view will be scanned which would introduce more unnecessary locks.
http://msdn.microsoft.com/library/e...des_06_9mlv.asp
-oj
"Sameer Raval" <Sameer Raval @.discussions.microsoft.com> wrote in message
news:DB036029-CA4E-463D-B475-19BCE875ACA7@.microsoft.com...
>I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
> I have about 25 databases, view is used that joins tables from all db with
> left outer join and multiple conditions in where clause.
> Online updates are taking place which again goes back to that particular
> table in spec. db. Triggers are used at few places to update the view.
> Every
> where stored procedures are used extensively instead of t-sql. In sp at
> places table variables are used to minimise locks.
> In the initial phase only with each database having few thousand records
> user start getting timeout ,slow response & blocking complaints.
> Should I copy the view to have local copy of data and have job that run
> every min or 2 to update local table . In that too I am not clear that how
> useful it will be to keep local copy bcz. it will grow huge in size with
> growth in all db.
> Very complex thing I see is continuous updates back and forth , which may
> keep local copy block for most of the time or else user wan't have latest
> data available in their app.
> Any one who has worked with similar situation can be lot more useful,
> I will highly appreciate any suggestions or discussions from sql
> experts.
> Thanks,
>
>
>
>
>
>
>
>|||Sameer
> I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
Have you started to investigate the queries? Have you defined indexes on the
tables?
http://www.sql-server-performance.com
"Sameer Raval" <Sameer Raval @.discussions.microsoft.com> wrote in message
news:DB036029-CA4E-463D-B475-19BCE875ACA7@.microsoft.com...
> I am having performance issues Timeout, blocking , long running stored
> procedures etc.
> Application is written in C sharp and uses database sql2000 sp3.
> I have about 25 databases, view is used that joins tables from all db with
> left outer join and multiple conditions in where clause.
> Online updates are taking place which again goes back to that particular
> table in spec. db. Triggers are used at few places to update the view.
Every
> where stored procedures are used extensively instead of t-sql. In sp at
> places table variables are used to minimise locks.
> In the initial phase only with each database having few thousand records
> user start getting timeout ,slow response & blocking complaints.
> Should I copy the view to have local copy of data and have job that run
> every min or 2 to update local table . In that too I am not clear that how
> useful it will be to keep local copy bcz. it will grow huge in size with
> growth in all db.
> Very complex thing I see is continuous updates back and forth , which may
> keep local copy block for most of the time or else user wan't have latest
> data available in their app.
> Any one who has worked with similar situation can be lot more useful,
> I will highly appreciate any suggestions or discussions from sql
experts.
> Thanks,
>
>
>
>
>
>
>
>

help creating linked server to Unify ELS on SCO unix

hello,
I'm trying to create a linked server from an SQL2000 to a Unify ELS
(very old, odbc is version 1) database on SCO unix.
The odbc driver is old but it works fine when used by applications for
creating reports.
What I want to do is make a linked server from the SCO box to the win2k
box. So, I'm using enterprise manager to create the linked server.
The DSN of the unify database is CORE_OFFICE1
I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
Datasource name not found and no default driver specified.
When I create the linked server I chose the OLE DB for ODBC drivers.
I put 'CORE_OFFICE1' for the Data Source name. I also setup the
security with the name of a unix account for login/password.
What else should I have?
I'm new at this linked server stuff!
Product Name:
Data Source:
Provider String:
Location:
Catalog:
Thanks,
Oskar
Did you install the ODBC driver on the SQL Server box - not
just your PC - and create a system DSN on the SQL Server
box?
-Sue
On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
<nothing@.nothing.gone> wrote:

>hello,
>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>(very old, odbc is version 1) database on SCO unix.
>The odbc driver is old but it works fine when used by applications for
>creating reports.
>What I want to do is make a linked server from the SCO box to the win2k
>box. So, I'm using enterprise manager to create the linked server.
>The DSN of the unify database is CORE_OFFICE1
>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>Datasource name not found and no default driver specified.
>When I create the linked server I chose the OLE DB for ODBC drivers.
>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>security with the name of a unix account for login/password.
>What else should I have?
>I'm new at this linked server stuff!
>Product Name:
>Data Source:
>Provider String:
>Location:
>Catalog:
>Thanks,
>Oskar
|||Sue Hoegemeier wrote:
> Did you install the ODBC driver on the SQL Server box - not
> just your PC - and create a system DSN on the SQL Server
> box?
Yes, the odbc driver is installed on the sql server box. However, I had
it as a User DSN instead of a System DSN. I changed that and now I get
this error:
Error 7399, authentication failed. Data source rejected connection attempt.
I put the 'sa' user as the local account on the linked server security
tab and I put the unix user/password as the remote account.
I think this is getting closer, but it's still not there!
Thanks,
Oskar

> -Sue
> On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
> <nothing@.nothing.gone> wrote:
>
>
|||Yeah...it's closer. Your just stuck on the authentication from SQL to
Unify. You may want to try first by just setting the security context
so that anyone accessing the linked server uses the same login - that
will at least eliminate who you are logging in as for now.
As you are doing this in enterprise manger, on the security tab,
remove the stuff you put in. Then select the last option "Be made
using this security context" and then enter the login and password
needed to access Unify.
-Sue
On Mon, 16 Aug 2004 09:00:08 -0500, pheonix1t <nothing@.nothing.gone>
wrote:
[vbcol=seagreen]
>Sue Hoegemeier wrote:
>Yes, the odbc driver is installed on the sql server box. However, I had
>it as a User DSN instead of a System DSN. I changed that and now I get
>this error:
>Error 7399, authentication failed. Data source rejected connection attempt.
>I put the 'sa' user as the local account on the linked server security
>tab and I put the unix user/password as the remote account.
>I think this is getting closer, but it's still not there!
>Thanks,
>Oskar
|||update
I found out that the unify driver only works in the USER DSN, not the
SYSTEM DSN. So, after a bit of research I've found that it's possible
to do a kluge by using an Access MDB file to connect to the Unify
database via linked table manager. Then, make the linked server using
the Access driver on the SYSTEM DSN.
How does this sound to you? Kinda wild, huh?
Now, I'm in the process of trying to make it work.
Ah, job security!!
Oskar
ps. Any and all advice welcome

help creating linked server to Unify ELS on SCO unix

hello,
I'm trying to create a linked server from an SQL2000 to a Unify ELS
(very old, odbc is version 1) database on SCO unix.
The odbc driver is old but it works fine when used by applications for
creating reports.
What I want to do is make a linked server from the SCO box to the win2k
box. So, I'm using enterprise manager to create the linked server.
The DSN of the unify database is CORE_OFFICE1
I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
Datasource name not found and no default driver specified.
When I create the linked server I chose the OLE DB for ODBC drivers.
I put 'CORE_OFFICE1' for the Data Source name. I also setup the
security with the name of a unix account for login/password.
What else should I have?
I'm new at this linked server stuff!
Product Name:
Data Source:
Provider String:
Location:
Catalog:
Thanks,
OskarDid you install the ODBC driver on the SQL Server box - not
just your PC - and create a system DSN on the SQL Server
box?
-Sue
On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
<nothing@.nothing.gone> wrote:

>hello,
>I'm trying to create a linked server from an SQL2000 to a Unify ELS
>(very old, odbc is version 1) database on SCO unix.
>The odbc driver is old but it works fine when used by applications for
>creating reports.
>What I want to do is make a linked server from the SCO box to the win2k
>box. So, I'm using enterprise manager to create the linked server.
>The DSN of the unify database is CORE_OFFICE1
>I'm getting 'Error 7399: OLE DB provider 'MSDASQL' reported an error.
>Datasource name not found and no default driver specified.
>When I create the linked server I chose the OLE DB for ODBC drivers.
>I put 'CORE_OFFICE1' for the Data Source name. I also setup the
>security with the name of a unix account for login/password.
>What else should I have?
>I'm new at this linked server stuff!
>Product Name:
>Data Source:
>Provider String:
>Location:
>Catalog:
>Thanks,
>Oskar|||Sue Hoegemeier wrote:
> Did you install the ODBC driver on the SQL Server box - not
> just your PC - and create a system DSN on the SQL Server
> box?
Yes, the odbc driver is installed on the sql server box. However, I had
it as a User DSN instead of a System DSN. I changed that and now I get
this error:
Error 7399, authentication failed. Data source rejected connection attempt.
I put the 'sa' user as the local account on the linked server security
tab and I put the unix user/password as the remote account.
I think this is getting closer, but it's still not there!
Thanks,
Oskar

> -Sue
> On Sun, 15 Aug 2004 22:03:00 -0500, pheonix1t
> <nothing@.nothing.gone> wrote:
>
>|||Yeah...it's closer. Your just stuck on the authentication from SQL to
Unify. You may want to try first by just setting the security context
so that anyone accessing the linked server uses the same login - that
will at least eliminate who you are logging in as for now.
As you are doing this in enterprise manger, on the security tab,
remove the stuff you put in. Then select the last option "Be made
using this security context" and then enter the login and password
needed to access Unify.
-Sue
On Mon, 16 Aug 2004 09:00:08 -0500, pheonix1t <nothing@.nothing.gone>
wrote:
[vbcol=seagreen]
>Sue Hoegemeier wrote:
>Yes, the odbc driver is installed on the sql server box. However, I had
>it as a User DSN instead of a System DSN. I changed that and now I get
>this error:
>Error 7399, authentication failed. Data source rejected connection attempt
.
>I put the 'sa' user as the local account on the linked server security
>tab and I put the unix user/password as the remote account.
>I think this is getting closer, but it's still not there!
>Thanks,
>Oskar
>|||update
I found out that the unify driver only works in the USER DSN, not the
SYSTEM DSN. So, after a bit of research I've found that it's possible
to do a kluge by using an Access MDB file to connect to the Unify
database via linked table manager. Then, make the linked server using
the Access driver on the SYSTEM DSN.
How does this sound to you? Kinda wild, huh?
Now, I'm in the process of trying to make it work.
Ah, job security!!
Oskar
ps. Any and all advice welcome

Help : "error: 40 - Could not open a connection to SQL Server"

I'm using VS2005 and SQL2000. I created a database name "myDatabase" inSQL Server 2000 and built a website that connet to this database. WhenI'm running this website in VS2005 IDE (press F5 or Ctrl-F5),everything is OK. I publiced this site. When I connect to database onmy computer, everything is OK also, but when I connect to database onanother computer, an error occurred like this :
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005,
this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)"
Now, I don't know how to fix it. Pls help me as soon as possible.
Regards.
By default, SQL Server 2005 doesn't have TCP/IP connectivity enabled after installation. To enable TCP/IP connectivity, go to SQL Server Configuration Manager, select the Network Configuration Node, select TCP/IP and set it to Enabled. After enabling TCP/IP connectivity, you should be able to connect.|||

I'm getting the same error but when I try to access my SQL Database on my Web Hosts server. How do I enable TCP/IP on their server if I don't have access to it?

|||Is the username, password, database name, and address of the SQL server correct?