MS Service Pack frustrations: time

Showing posts with label time. Show all posts

Friday, March 30, 2012

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
identical table TESTTBL. I need to make sure that tables TESTTBL in
both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
following C# code snippet fails. Is it possible to execute command
across two servers or am I wasting my time? Any help on this will be
appreciated.
string sSql= "delete SERVER-2.TESTDB.TESTTBL where
SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
from SERVER-1.TESTDB.TESTTBL )";
SqlConnection thisConnection1 = null;
SqlConnection thisConnection2 = null;
SqlCommand thisCommand1 = null;
SqlCommand thisCommand2 = null;
thisConnection1 = new SqlConnection(connectionString1);
thisConnection1.Open();
thisConnection2 = new SqlConnection(connectionString2);
thisConnection2.Open();
thisCommand1 = new SqlCommand(sSql, thisConnection1);
try
{
thisCommand1.ExecuteNonQuery();
}
catch (SqlException oE)
{
MessageBox.Show(oE.Message.ToString());
}Have you consider using REPLICATION between tow servers?
<bkasmai@.gmail.com> wrote in message
news:1144070684.346122.72030@.v46g2000cwv.googlegroups.com...
> Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
> identical table TESTTBL. I need to make sure that tables TESTTBL in
> both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
> which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
> would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
> make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
> following C# code snippet fails. Is it possible to execute command
> across two servers or am I wasting my time? Any help on this will be
> appreciated.
> string sSql= "delete SERVER-2.TESTDB.TESTTBL where
> SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
> from SERVER-1.TESTDB.TESTTBL )";
> SqlConnection thisConnection1 = null;
> SqlConnection thisConnection2 = null;
> SqlCommand thisCommand1 = null;
> SqlCommand thisCommand2 = null;
> thisConnection1 = new SqlConnection(connectionString1);
> thisConnection1.Open();
> thisConnection2 = new SqlConnection(connectionString2);
> thisConnection2.Open();
> thisCommand1 = new SqlCommand(sSql, thisConnection1);
> try
> {
> thisCommand1.ExecuteNonQuery();
> }
> catch (SqlException oE)
> {
> MessageBox.Show(oE.Message.ToString());
> }
>|||Yes. Past experience prevent me from using replication.
SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.|||Well, another option is to BACKUP LOG file on the source SERVER and
RESTORE it on destination. But I see the problem here because if I remember
well the destination SERVER must be read only....
<bkasmai@.gmail.com> wrote in message
news:1144073079.759225.46870@.e56g2000cwe.googlegroups.com...
> Yes. Past experience prevent me from using replication.
> SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
> SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.
>|||The sync needs to be done every hour through a application written in
c# !

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
identical table TESTTBL. I need to make sure that tables TESTTBL in
both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
following C# code snippet fails. Is it possible to execute command
across two servers or am I wasting my time? Any help on this will be
appreciated.
string sSql= "delete SERVER-2.TESTDB.TESTTBL where
SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
from SERVER-1.TESTDB.TESTTBL )";
SqlConnection thisConnection1 = null;
SqlConnection thisConnection2 = null;
SqlCommand thisCommand1 = null;
SqlCommand thisCommand2 = null;
thisConnection1 = new SqlConnection(connectionString1);
thisConnection1.Open();
thisConnection2 = new SqlConnection(connectionString2);
thisConnection2.Open();
thisCommand1 = new SqlCommand(sSql, thisConnection1);
try
{
thisCommand1.ExecuteNonQuery();
}
catch (SqlException oE)
{
MessageBox.Show(oE.Message.ToString());
}
Have you consider using REPLICATION between tow servers?
<bkasmai@.gmail.com> wrote in message
news:1144070684.346122.72030@.v46g2000cwv.googlegro ups.com...
> Two sql servers SERVER-1 and SERVER-2 each with database TESTDB and
> identical table TESTTBL. I need to make sure that tables TESTTBL in
> both servers are in sync by deleting records in SERVER-2.TESTDB.TESTTBL
> which do not exist in SERVER-1.TESTDB.TESTTBL. The next requirement
> would be to UPDATE and INSERT records in SERVER-2.TESTDB.TESTTBL to
> make sure it to stay in sync with SERVER-2.TESTDB.TESTTBL . The
> following C# code snippet fails. Is it possible to execute command
> across two servers or am I wasting my time? Any help on this will be
> appreciated.
> string sSql= "delete SERVER-2.TESTDB.TESTTBL where
> SERVER-2.TESTDB.TESTTBL.PRIMARY_FIELD1 not in (select PRIMARY_FIELD1
> from SERVER-1.TESTDB.TESTTBL )";
> SqlConnection thisConnection1 = null;
> SqlConnection thisConnection2 = null;
> SqlCommand thisCommand1 = null;
> SqlCommand thisCommand2 = null;
> thisConnection1 = new SqlConnection(connectionString1);
> thisConnection1.Open();
> thisConnection2 = new SqlConnection(connectionString2);
> thisConnection2.Open();
> thisCommand1 = new SqlCommand(sSql, thisConnection1);
> try
> {
> thisCommand1.ExecuteNonQuery();
> }
> catch (SqlException oE)
> {
> MessageBox.Show(oE.Message.ToString());
> }
>
|||Yes. Past experience prevent me from using replication.
SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.
|||Well, another option is to BACKUP LOG file on the source SERVER and
RESTORE it on destination. But I see the problem here because if I remember
well the destination SERVER must be read only....
<bkasmai@.gmail.com> wrote in message
news:1144073079.759225.46870@.e56g2000cwe.googlegro ups.com...
> Yes. Past experience prevent me from using replication.
> SERVER-1.TESTDB1 and SERVER-2.TESTDB are not identical but
> SERVER-1.TESTDB1.TESTTBL and SERVER-1.TESTDB1.TESTTBL must be in sync.
>
|||The sync needs to be done every hour through a application written in
c# !

Wednesday, March 28, 2012

MS SQL Server Run time Error

We are using MS SQL Server 2000 as back end database for our application. It was working fine certainly we are getting Run time error while try to open this application through front end.

The Error number says that the SQL Transaction Log file is file. find belo the error

Thanks in advance.
S KaliyanI think I missed the error in the post. If this is still unresolved can you resend with the appropriate error?

MS SQL server management studio express

hi there !

Well i hope u all great people are having great time. Well i am new to asp.net and struggling in database deployment to server. I took webhosting from datapacket.net, and using helm control panel. to deploy database, i used sql management studio express as suggested by some one. Now i am not able to access any of database or tables, although i got connected when i altered configuration surface setting to allow server to go to remote server. Now please help me, how to deploy database ?

Thanks

I did not understand you question.

You get connected to the server, but you cannot see the database ? So how do you know you are connected to the server ?

you can connect to the server all of the databases in the server are listed in the object explorer of you Managment studio express and you cannot connect to the database ? If is this option it is probably your credencials.

Are there any errors message ? Can you post them if it is ?

|||

Hi,

Since you haven't give us the detail situation, so I just conclude some of the reasons which is generally happened for you to refer.

From your words, " i got connected when i altered configuration surface setting to allow server to go to remote server", it seems you have connected to the remote database server successfully by your management tool. So I guess, the problem is occurred while connecting to your database in your application. If so, there's nothing related with the deployment of the database. Please do check your connection string which your application use to see if it is working properly.

If the situation is just like after you have uploaded your database onto your server, you can see the database and tables but you can't implement some action such as select,update,delete and etc, then it seems the problem is caused by the wrong setting of database or tables' owners or roles. Just try to find if the role on your SqlServer has the permission to implement these database objects.

Thanks.

Monday, March 26, 2012

MS SQL server login failed

I have MS SQL server desktop version installed localy and I have been using
it till last time I had to change my windows XP password. After that I could
not start service and I am getting an error message: "The service did not
start due to a logon failure", and "An error 1069 - The service did not start
due to a logon failure) occured while perfrorming this service operation on
the MSSQLServer service".
I have installed MS SQL server and configured to use Windows authentication,
and it worked in the past. Few months ago I have downloaded windows updates
and since then whenever I change windows password I have problem logging in
MS SQL.
Is there a way to fix that problem without reinstalling MS SQL?
DjL
It sounds like you may be running the service using your
Windows login. You can check from Services applet in the
Administrative Tools. Check the Logon tab for the
MSSQLSERVER service to see what login is being used.
If you are using your login, you need to change the password
for the MSSQLSERVER service also whenever you change your
Windows password.
-Sue
On Mon, 18 Sep 2006 07:44:02 -0700, Djordje Lekovic
<DjordjeLekovic@.discussions.microsoft.com> wrote:

>I have MS SQL server desktop version installed localy and I have been using
>it till last time I had to change my windows XP password. After that I could
>not start service and I am getting an error message: "The service did not
>start due to a logon failure", and "An error 1069 - The service did not start
>due to a logon failure) occured while perfrorming this service operation on
>the MSSQLServer service".
>I have installed MS SQL server and configured to use Windows authentication,
>and it worked in the past. Few months ago I have downloaded windows updates
>and since then whenever I change windows password I have problem logging in
>MS SQL.
>Is there a way to fix that problem without reinstalling MS SQL?
|||Thx

It worked.
DjL
"Sue Hoegemeier" wrote:

> It sounds like you may be running the service using your
> Windows login. You can check from Services applet in the
> Administrative Tools. Check the Logon tab for the
> MSSQLSERVER service to see what login is being used.
> If you are using your login, you need to change the password
> for the MSSQLSERVER service also whenever you change your
> Windows password.
> -Sue
> On Mon, 18 Sep 2006 07:44:02 -0700, Djordje Lekovic
> <DjordjeLekovic@.discussions.microsoft.com> wrote:
>
>

MS SQL server login failed

I have MS SQL server desktop version installed localy and I have been using
it till last time I had to change my Windows XP password. After that I could
not start service and I am getting an error message: "The service did not
start due to a logon failure", and "An error 1069 - The service did not star
t
due to a logon failure) occured while perfrorming this service operation on
the MSSQLServer service".
I have installed MS SQL server and configured to use Windows authentication,
and it worked in the past. Few months ago I have downloaded windows updates
and since then whenever I change windows password I have problem logging in
MS SQL.
Is there a way to fix that problem without reinstalling MS SQL?
DjLIt sounds like you may be running the service using your
Windows login. You can check from Services applet in the
Administrative Tools. Check the Logon tab for the
MSSQLSERVER service to see what login is being used.
If you are using your login, you need to change the password
for the MSSQLSERVER service also whenever you change your
Windows password.
-Sue
On Mon, 18 Sep 2006 07:44:02 -0700, Djordje Lekovic
<DjordjeLekovic@.discussions.microsoft.com> wrote:

>I have MS SQL server desktop version installed localy and I have been using
>it till last time I had to change my Windows XP password. After that I coul
d
>not start service and I am getting an error message: "The service did not
>start due to a logon failure", and "An error 1069 - The service did not sta
rt
>due to a logon failure) occured while perfrorming this service operation on
>the MSSQLServer service".
>I have installed MS SQL server and configured to use Windows authentication
,
>and it worked in the past. Few months ago I have downloaded windows updates
>and since then whenever I change windows password I have problem logging in
>MS SQL.
>Is there a way to fix that problem without reinstalling MS SQL?|||Thx

Friday, March 23, 2012

MS SQL Server 2005 hang

Hi All,
We get very strange hang situation at our customers from time to time.
I execute very simple query like
insert into <table1>
select <columns> from <table2>
where <conditions>
Table <table1> has clustered index on float column.
Normally this query is executing, say, 2 minutes. But sometimes it
suddenly begins to hang for 2 hours and go to query timeout. I did not
find something special or different in execution plan.
The only workaround I have found is to recreate <table1>. I just copy
all data from this table to another table, then drop table <table1>,
then create it with adding necessary index and then copy data back
from temptable to original one. And it helps! The same data is easily
inserted in 2 minutes.
I have never experienced such problem on SQL Server 2000, only on
2005. Unfortunately, we cannot reproduce it on our environment but
there are no visible differences in server or db options.
Probably somebody already solved such problem or can advise where to
go. Any help would be appreciated.
Thanks in advance!Hi
"prudon@.inbox.ru" wrote:
> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>
Have you checked the version of SQL 2005 that you are running? Make sure
that it is up to date. Also look for blocking
http://support.microsoft.com/kb/271509 missing indexes
http://msdn2.microsoft.com/en-us/library/ms345524.aspx or out of date
statistics http://msdn2.microsoft.com/en-us/library/ms190397.aspx
John|||1) Almost certainly a blocking situation. moving (potentially large)
amounts of data like this is often a performance issue because the locks
escalate to full table, preventing ANY other update/delete/insert access to
the table for the duration of the transaction.
2) My gut tells me to question a clustered index on a float datatype.
TheSQLGuru
President
Indicium Resources, Inc.
<prudon@.inbox.ru> wrote in message
news:1180681124.707731.111770@.q69g2000hsb.googlegroups.com...
> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>|||Thank you very much!
The specific thing of our application that there is only one connect
per database. So, there are no other transactions on those database. I
cannot understand why we didn't experienced such problems on SQL
Server 2000 for more than 4 years nowhere. If the problem is in
clustered index, why "re-creating" table with index helps to avoid the
problem. Next time I get such problem I will check statistics, but I'm
afraid it will not give anything.
Many thanks for your feedback

MS SQL Server 2005 hang

Hi All,
We get very strange hang situation at our customers from time to time.
I execute very simple query like
insert into <table1>
select <columns> from <table2>
where <conditions>
Table <table1> has clustered index on float column.
Normally this query is executing, say, 2 minutes. But sometimes it
suddenly begins to hang for 2 hours and go to query timeout. I did not
find something special or different in execution plan.
The only workaround I have found is to recreate <table1>. I just copy
all data from this table to another table, then drop table <table1>,
then create it with adding necessary index and then copy data back
from temptable to original one. And it helps! The same data is easily
inserted in 2 minutes.
I have never experienced such problem on SQL Server 2000, only on
2005. Unfortunately, we cannot reproduce it on our environment but
there are no visible differences in server or db options.
Probably somebody already solved such problem or can advise where to
go. Any help would be appreciated.
Thanks in advance!Hi
"prudon@.inbox.ru" wrote:

> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>
Have you checked the version of SQL 2005 that you are running? Make sure
that it is up to date. Also look for blocking
http://support.microsoft.com/kb/271509 missing indexes
http://msdn2.microsoft.com/en-us/library/ms345524.aspx or out of date
statistics http://msdn2.microsoft.com/en-us/library/ms190397.aspx
John|||1) Almost certainly a blocking situation. moving (potentially large)
amounts of data like this is often a performance issue because the locks
escalate to full table, preventing ANY other update/delete/insert access to
the table for the duration of the transaction.
2) My gut tells me to question a clustered index on a float datatype.
TheSQLGuru
President
Indicium Resources, Inc.
<prudon@.inbox.ru> wrote in message
news:1180681124.707731.111770@.q69g2000hsb.googlegroups.com...
> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>|||Thank you very much!
The specific thing of our application that there is only one connect
per database. So, there are no other transactions on those database. I
cannot understand why we didn't experienced such problems on SQL
Server 2000 for more than 4 years nowhere. If the problem is in
clustered index, why "re-creating" table with index helps to avoid the
problem. Next time I get such problem I will check statistics, but I'm
afraid it will not give anything.
Many thanks for your feedback

MS SQL Server 2005 hang

Hi All,
We get very strange hang situation at our customers from time to time.
I execute very simple query like
insert into <table1>
select <columns> from <table2>
where <conditions>
Table <table1> has clustered index on float column.
Normally this query is executing, say, 2 minutes. But sometimes it
suddenly begins to hang for 2 hours and go to query timeout. I did not
find something special or different in execution plan.
The only workaround I have found is to recreate <table1>. I just copy
all data from this table to another table, then drop table <table1>,
then create it with adding necessary index and then copy data back
from temptable to original one. And it helps! The same data is easily
inserted in 2 minutes.
I have never experienced such problem on SQL Server 2000, only on
2005. Unfortunately, we cannot reproduce it on our environment but
there are no visible differences in server or db options.
Probably somebody already solved such problem or can advise where to
go. Any help would be appreciated.
Thanks in advance!
Hi
"prudon@.inbox.ru" wrote:

> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>
Have you checked the version of SQL 2005 that you are running? Make sure
that it is up to date. Also look for blocking
http://support.microsoft.com/kb/271509 missing indexes
http://msdn2.microsoft.com/en-us/library/ms345524.aspx or out of date
statistics http://msdn2.microsoft.com/en-us/library/ms190397.aspx
John
|||1) Almost certainly a blocking situation. moving (potentially large)
amounts of data like this is often a performance issue because the locks
escalate to full table, preventing ANY other update/delete/insert access to
the table for the duration of the transaction.
2) My gut tells me to question a clustered index on a float datatype.
TheSQLGuru
President
Indicium Resources, Inc.
<prudon@.inbox.ru> wrote in message
news:1180681124.707731.111770@.q69g2000hsb.googlegr oups.com...
> Hi All,
> We get very strange hang situation at our customers from time to time.
> I execute very simple query like
> insert into <table1>
> select <columns> from <table2>
> where <conditions>
> Table <table1> has clustered index on float column.
> Normally this query is executing, say, 2 minutes. But sometimes it
> suddenly begins to hang for 2 hours and go to query timeout. I did not
> find something special or different in execution plan.
> The only workaround I have found is to recreate <table1>. I just copy
> all data from this table to another table, then drop table <table1>,
> then create it with adding necessary index and then copy data back
> from temptable to original one. And it helps! The same data is easily
> inserted in 2 minutes.
> I have never experienced such problem on SQL Server 2000, only on
> 2005. Unfortunately, we cannot reproduce it on our environment but
> there are no visible differences in server or db options.
> Probably somebody already solved such problem or can advise where to
> go. Any help would be appreciated.
> Thanks in advance!
>
|||Thank you very much!
The specific thing of our application that there is only one connect
per database. So, there are no other transactions on those database. I
cannot understand why we didn't experienced such problems on SQL
Server 2000 for more than 4 years nowhere. If the problem is in
clustered index, why "re-creating" table with index helps to avoid the
problem. Next time I get such problem I will check statistics, but I'm
afraid it will not give anything.
Many thanks for your feedback

Wednesday, March 21, 2012

MS SQL Server 2000 VPN problem

Hi,
I'm using MS SQL Server 2000 from long time and its amazing i got stuck this
time. Here is a scenario:
1. I have a client and i used to connect from VPN to login their network and
i can do then whatever i want except i can connect to their MS SQL Server
from my Enterprise Manager as well.
2. I use Remote Desktop Connection to login to different machine so i went
inside the MS SQL Server and see its working fine. Even it works fine when
users are loggin from their local domain name.
Any suggestions will be apreciated.
Thanks.
What is the exact error you get? How are you trying to connect when you get
it (what tool, what protocol, by IP or Name or Name,port or IP,port)?
From a VPN you might need to specify the port since UDP 1434 might be
blocked for certain types of connections. Or even the SQL Server TCP port
(by default it's 1433 for a default instance, it's dynamic for a named
instance) might be blocked by a firewall.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi,
Please can you give me steps on how to connect to instance of two SQL Server through VPN.
I shall appricaite it very well.
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Yusuf,
test that you can see the server through the SQL tcpip port (default is
1433).
If you're logged on the domain you can use (a) windows authentication,
otherwise you have to use (b) pass-through authentication or (c) sql
standard authentication
Regards,
Paul Ibison

MS SQL Server 2000 VPN problem

Hi,
I'm using MS SQL Server 2000 from long time and its amazing i got stuck this
time. Here is a scenario:
1. I have a client and i used to connect from VPN to login their network and
i can do then whatever i want except i can connect to their MS SQL Server
from my Enterprise Manager as well.
2. I use Remote Desktop Connection to login to different machine so i went
inside the MS SQL Server and see its working fine. Even it works fine when
users are loggin from their local domain name.
Any suggestions will be apreciated.
Thanks.What is the exact error you get? How are you trying to connect when you get
it (what tool, what protocol, by IP or Name or Name,port or IP,port)?
From a VPN you might need to specify the port since UDP 1434 might be
blocked for certain types of connections. Or even the SQL Server TCP port
(by default it's 1433 for a default instance, it's dynamic for a named
instance) might be blocked by a firewall.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Please can you give me steps on how to connect to instance of two SQL Server
through VPN.
I shall appricaite it very well.
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||Yusuf,
test that you can see the server through the SQL tcpip port (default is
1433).
If you're logged on the domain you can use (a) windows authentication,
otherwise you have to use (b) pass-through authentication or (c) sql
standard authentication
Regards,
Paul Ibison

MS SQL server 2000 Job limit?

Is there a limit to how many jobs you can have total - or a limit on
how many jobs can be running at one time.
I am looking at something that will use 600 very small jobs (take
about 2 seconds to run). WIll this cause a problem?
Thanks for any help.
Aaron
The limit you would be more likely to hit would be worker
threads per job subsystem. You can view the subsystems and
their max worker threads by executing:
exec msdb..sp_enum_sqlagent_subsystems
You can modify the CmdExec, ActiveScripting and TSQL max
worker threads by modifying the values in the registry if
needed. The registry keys for CmdExec and ActiveScripting
exist in the registry - for TSQL threads, you need to create
the key.
-Sue
On 28 Jun 2004 07:06:08 -0700, aewood21@.yahoo.com (Aaron
Wood) wrote:

>Is there a limit to how many jobs you can have total - or a limit on
>how many jobs can be running at one time.
>I am looking at something that will use 600 very small jobs (take
>about 2 seconds to run). WIll this cause a problem?
>Thanks for any help.
>Aaron
|||The limit you would be more likely to hit would be worker
threads per job subsystem. You can view the subsystems and
their max worker threads by executing:
exec msdb..sp_enum_sqlagent_subsystems
You can modify the CmdExec, ActiveScripting and TSQL max
worker threads by modifying the values in the registry if
needed. The registry keys for CmdExec and ActiveScripting
exist in the registry - for TSQL threads, you need to create
the key.
-Sue
On 28 Jun 2004 07:06:08 -0700, aewood21@.yahoo.com (Aaron
Wood) wrote:

>Is there a limit to how many jobs you can have total - or a limit on
>how many jobs can be running at one time.
>I am looking at something that will use 600 very small jobs (take
>about 2 seconds to run). WIll this cause a problem?
>Thanks for any help.
>Aaron

MS SQL Server 2000 / MS Access - ODBC connection question

Hello all!

I have such question to all of you.
I have some tables linked from MS SQL Server 2000. Is time of processing
query
based on these linked tables from MS SQL Server 2000, faster or slower than
the time of processing the same query based on tables, which are not linked
but
imported to MS Access?

Can you answer this question?

Thank you in advance for posts
Marcin from Poland.No idea - it depends how big the tables are, what your queries look
like, how you execute your queries etc. If you're using stored
procedures, for example, then I would expect MSSQL to be faster, as all
the processing happens on the server, and it can use indexes, caching
etc. The best way to get an answer is simply to test it yourself.

Simon

Monday, March 19, 2012

MS SQL performance from 10" to 3 minutes

Hello all !

I am runing from .NET application an SQL Query
it normally return the rows in 10 seconds
but time to time the application turn 2 or 3 minutes and nearlly crash (or crash)

with exactly the same datas in database

what can be the reasons ?

thank youcheck whether the session is getting expired or not if not kill it.|||other reason could be lock put on the table during the transaction which may keep the DB server busy.
Also check if some other query seeks a large resultset from DB.|||ppavan21 if I kill the session and a user is logged-in he will be thrown, I cannot do it , or do you see a solution ?

wash : is there a way to unlock ? ot what can I do ?

on 5 rows it takes normally less than one second, sometimes it can turn a few minutes and crash with exactly the sames datas

thank you|||the reasons for this can vary widely.

things to check...

1. open up the task manager to see if it is the sqlserver process consuming resources. Are you running anything on the machine? IIS? exchange?

2. run sp_who\sp_who2\sp_lock to look for blocking\resource intensive operations or excessive locking.

3. Open up the performance monitor and make sure you disk que length is under 3.

4. Have you looked at the execution plan of the query that varies in execution time? Are there any table\index scan as opposed to index seeks in the plan? If the query can return vastly varying amounts of data, have you tried adding WITH RECOMPILE to the query? Have you recompiled the stored procedure lately? Are the indexes that the query is using heavily fragmented?

That should keep you busy.|||RECOMPILE ? i didn't know it was even possible
how do yo do it ?|||recompiling is sometimes beneficial if there has been a large amount of data added to your database recently which can have the effect of making your execution plan out of date.

see sp_recompile in Books Online.|||Sean,

I believe you assume That this is a sproc

I got Money that it's not|||oh probably not. dude can probably use a little BOL reading anyways.

Monday, March 12, 2012

MS SQL Joins

Hi Folks,
I writing a store procedure, the first three parts work pretty well. The last select statement has about 8 outer joins in it. every time I run the store procedure, I get an error message for the last part. Below are the error message and the store procedure:

Store Procedure:

--Create Procedure dbo.IMS_Donation

--AS

Select Distinct D_VST_ID as 'DRWLOC_ID', D_VST_INSTID as 'DRWLOC_INSTID'
Into Donor_Visit1
From DNR_VST_DB_REC
Where D_VST_DATE Between 20010101 AND 20040512
AND D_VST_DONTYP in ('AP', 'WB', 'RP', 'E2', 'E1')
AND D_VST_STATUS = 'DN'
ORDER BY D_VST_ID
GO

SELECT DRWLOC_ID as 'COUNT_ID', DRWLOC_INSTID as 'COUNT_INSTID',
count(*) as 'COUNT_VISITS'
INTO Donor_Visit2
FROM DNR_VST_DB_REC, Donor_Visit1
Where D_VST_ID = DRWLOC_ID
AND NOT EXISTS (Select R_DCC_ID
From REC_DCC_DB_REC
Where R_DCC_ID = DRWLOC_ID
AND R_DCC_INSTID = DRWLOC_INSTID
AND R_DCC_CALLCD = 'DC')
GROUP BY DRWLOC_ID, DRWLOC_INSTID
GO

SELECT DVT1.DRWLOC_ID as'COMP_ID', CMP.l_CMP_UNITNO as 'COMP_UNITID',
CMP.L_CMP_INSTID as 'COMP_INSTID', count(*) as 'COMP_COMPTOT'
INTO Donor_Visit3
FROM LAB_CMP_DB_REC CMP, Donor_Visit1 DVT1, DNR_VST_DB_REC VST, CMP_VST_Jct CVT
WHERE CMP.L_CMP_INSTID = DVT1.DRWLOC_INSTID
AND VST.D_VST_ID = DVT1.DRWLOC_ID
AND VST.D_VST_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_UNITNO = CVT.L_CMP_UNITNO
AND CMP.L_CMP_STATCD != 'MOD'
AND CMP.L_CMP_CMPCD NOT IN ('INC', 'EMTY')
AND VST.D_VST_DATE BETWEEN 20010101 AND 20040512
AND VST.D_VST_STATUS = 'DN'
GROUP BY DVT1.DRWLOC_ID, CMP.L_CMP_UNITNO, CMP.L_CMP_INSTID

GO

SELECT DISTINCT
NAM.N_NAM_ID AS 'ID1',
NAM.N_NAM_INSTID AS 'INSTID1',
NAM.N_NAM_FNAME AS 'FNAME1',
NAM.N_NAM_MINITIAL AS 'MINITIAL1',
NAM.N_NAM_LNAME AS 'LNAME1',
PER.N_PER_BIRTH AS 'BIRTH1',
ADR.N_ADR_ADDR1 AS 'ADDR1',
ADR.N_ADR_ADDR2 AS 'ADDR2',
ADR.N_ADR_CITY AS 'CITY1',
ADR.N_ADR_STATE AS 'STATE1',
SUBSTRING(ADR.N_ADR_ZIP, 1,5) AS 'ZIP1',
PER.N_PER_EMAIL AS 'EMAIL1',
PER.N_PER_GENDER AS 'GENDER1',
PHNA.N_PHN_AREACD AS 'AREAD1',
PHNA.N_PHN_PREFIX AS 'PREFIXD1',
PHNA.N_PHN_NUMBER AS 'NBRD1',
PHNA.N_PHN_EXTENTN AS 'EXTD1',
PHNB.N_PHN_AREACD AS 'AREAD2',
PHNB.N_PHN_PREFIX AS 'PREFIXD2',
PHNB.N_PHN_NUMBER AS 'NBRE2',
PHNB.N_PHN_EXTENTN AS 'EXTD2',
BTY.D_BTY_ABO AS 'ABO1',
BTY.D_BTY_RHESUS AS 'RHI',
VST.D_VST_DATE AS 'FIRST1',
DV2.COUNT_VISITS AS 'COUNT',
SUM(DTS.D_DTS_DONSUM) AS 'AWARD',
ELG.D_ELG_RWBDTE AS 'ELIG1'
--INTO Donor_Visit4
From Donor_Visit2 DV2
RIGHT OUTER JOIN DNR_DTS_DB_REC DTS
ON DV2.COUNT_INSTID = DTS.D_DTS_INSTID
RIGHT OUTER JOIN NAT_PER_DB_REC PER
ON DV2.COUNT_INSTID = PER.N_PER_INSTID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY
ON DV2.COUNT_INSTID = BTY.D_BTY_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNA
ON DV2.COUNT_INSTID = PHNA.N_PHN_INSTID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB
ON DV2.COUNT_INSTID = PHNB.N_PHN_INSTID
RIGHT OUTER JOIN DNR_DTS_DB_REC DNT
ON DV2.COUNT_ID = DNT.D_DTS_ID
RIGHT OUTER JOIN NAT_PER_DB_REC PER1
ON DV2.COUNT_ID = PER1.N_PER_ID
RIGHT OUTER JOIN DNR_BTY_DB_REC BTY1
ON DV2.COUNT_ID = BTY1.D_BTY_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA1
ON DV2.COUNT_ID = PHNA1.N_PHN_ID
RIGHT OUTER JOIN NAT_PHN_DB_REC PHNB1
ON DV2.COUNT_ID = PHNB1.N_PHN_ID
LEFT OUTER JOIN NAT_PHN_DB_REC PHNA2
ON PHNA2.N_PHN_PHTYP = 'D'
LEFT OUTER JOIN NAT_PHN_DB_REC PHNB2
ON PHNB2.N_PHN_PHTYP = 'E',
--LEFT OUTER JOIN DNR_DTS_DB_REC DTS1
--DTS1.D_DTS_CNTTYP <> 'N',
DNR_ELG_DB_REC ELG, NAT_NAM_DB_REC NAM, NAT_ADR_DB_REC ADR, DNR_VST_DB_REC VST
WHERE DV2.COUNT_INSTID = VST.D_VST_INSTID
AND DV2.COUNT_INSTID = ELG.D_ELG_INSTID
AND DV2.COUNT_INSTID = N_NAM_INSTID
AND DV2.COUNT_INSTID = N_ADR_INSTID
AND DV2.COUNT_INSTID = VST.D_VST_INSTID
--AND DV2.COUNT_INSTID = ELG.D_ELG_ID
AND NAM.N_NAM_SEQNO = 0
AND VST.D_VST_DATE = (SELECT MIN(VSTB.D_VST_DATE)
FROM DNR_VST_DB_REC VSTB
WHERE VST.D_VST_INSTID = VSTB.D_VST_INSTID
AND VSTB.D_VST_STATUS = 'DN'
AND VST.D_VST_ID = VSTB.D_VST_ID)
AND NOT EXISTS (SELECT R_DRC_ID
FROM REC_DRC_DB_REC
WHERE R_DRC_ID = COUNT_ID
AND R_DRC_INSTID = COUNT_INSTID
AND R_DRC_RESPCD = '15')
GROUP BY
NAM.N_NAM_ID,
NAM.N_NAM_INSTID,
NAM.N_NAM_FNAME,
NAM.N_NAM_MINITIAL,
NAM.N_NAM_LNAME,
PER.N_PER_BIRTH,
ADR.N_ADR_ADDR1,
ADR.N_ADR_ADDR2,
ADR.N_ADR_CITY,
ADR.N_ADR_STATE,
ADR.N_ADR_ZIP,
PER.N_PER_EMAIL,
PER.N_PER_GENDER,
PHNA.N_PHN_AREACD,
PHNA.N_PHN_PREFIX,
PHNA.N_PHN_NUMBER,
PHNA.N_PHN_EXTENTN,
PHNB.N_PHN_AREACD,
PHNB.N_PHN_PREFIX,
PHNB.N_PHN_NUMBER,
PHNB.N_PHN_EXTENTN,
BTY.D_BTY_ABO,
BTY.D_BTY_RHESUS,
VST.D_VST_DATE,
DV2.COUNT_VISITS,
DTS.D_DTS_DONSUM,
ELG.D_ELG_RWBDTE

Error Message:

(845 row(s) affected)

(844 row(s) affected)

(396 row(s) affected)

Server: Msg 9002, Level 17, State 6, Line 2
The log file for database 'tempdb' is full. Back up the transaction log for the database to free up some log space.
Server: Msg 1105, Level 17, State 1, Line 2
Could not allocate space for object '(SYSTEM table id: -109901351)' in database 'TEMPDB' because the 'DEFAULT' filegroup is full.Ok, its a hog.

First, see if you can "blow out" tempdb using DBCC SHRINKDATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_3pd1.asp).

If that doesn't help enough, see if you can create an index that the GROUP BY expression can use... It is often enough to get the first three or four columns covered, since that can buy you an enormous reduction in staging space.

If that doesn't help, buy more disk!

-PatP|||My God, I mean Oh Codd, you have 27 GROUP BY's!!!!... You realize that your tempdb would be the bottleneck throughout the life of your app! Are you sure you need all 27?.. Click on estimated execution plan icon in QA and see what you get there.

MS SQL getDate() Function remove Time

Hi,

I am creating creating a table with a Date column dd-mm-yyyy. But I
cant seem to find a SQL function that just returns today's date.
getDate() returns the time as well so I cant use it.

The reason is simply that I want to update/overwrite over and over
again all records from current day but not touch the ones from
yesterday etc and with the timestamp in there I just end up adding
more and more rows for the same day.

In other words I only want to preserve rows are from yesterday or
older but overwrite ones from today.

Any help will be appricated.

Thank you!

YasOn Jul 4, 3:53 pm, Yas <yas...@.gmail.comwrote:

Quote:

Originally Posted by

Hi,
>
I am creating creating a table with a Date column dd-mm-yyyy. But I
cant seem to find a SQL function that just returns today's date.
getDate() returns the time as well so I cant use it.
>
The reason is simply that I want to update/overwrite over and over
again all records from current day but not touch the ones from
yesterday etc and with the timestamp in there I just end up adding
more and more rows for the same day.
>
In other words I only want to preserve rows are from yesterday or
older but overwrite ones from today.
>
Any help will be appricated.
>
Thank you!
>
Yas

AFAIK a there is no DATE type in MS SQL, only DATETIME so you cannot
store only the date part.
You can use SELECT CONVERT(VARCHAR(8),datevalue,112) to return the
datetime in YYYYMMDD format without the time but it's stored as a
VARCHAR not a DATETIME.|||On 4 Jul, 17:14, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

To remove the time from a datetime such as getdate():
>
SELECT dateadd(day,datediff(day,0,getdate()),0)
>
I strongly suggest not storing a date column as a string. Use a
datetime and just set the time to zeroes if you only need the date.

Hi I am storing the coumn as datetime and not string. However, using
the above suggestion (day,datediff(day,0,getdate()),0) I get a column
with Date+Time set to Zeros. OK, but the problem is when I run the
update/insert records command again It doesn't overwrite the columns
with today's date, its as if sql is secretly inserting the time by it
self and even though to my eyes the rows is exactly the same SQL adds
a new row thinking it is distinct.

I would like that if the table had a rowOld with: (ColValue1,
ColValue2,2007-07-04 00.00.00.000)
If I use the above suggestion and insert a rowNew with same values
(ColValue1, ColValue2, 2007-07-04 00.00.00.000)
...It should overwrite rowOld with rowNew, not insert rowNew as a new
row.
...and only insert as a new rowNew2 when this row has a different date
eg. 2007-07-05 00.00.00.000

I thought it would as time is now set to Zeros, but it doesn't. Is SQL
marking each row in the examples above with a time stamp? even though
it is not shown in the row value?

Thanks again :-)

Yas|||On Wed, 04 Jul 2007 08:55:49 -0700, Yas <yasar1@.gmail.comwrote:

Quote:

Originally Posted by

>I would like that if the table had a rowOld with: (ColValue1,
>ColValue2,2007-07-04 00.00.00.000)
>If I use the above suggestion and insert a rowNew with same values
>(ColValue1, ColValue2, 2007-07-04 00.00.00.000)
>...It should overwrite rowOld with rowNew, not insert rowNew as a new
>row.
>...and only insert as a new rowNew2 when this row has a different date
>eg. 2007-07-05 00.00.00.000

You can write an INSERT for a new row, or an UPDATE for an existing
row, but you have to choose which it is to be. In your case you have
to find out if the row exists and then run INSERT or UPDATE depending
on what you find.

Microsoft is adding MERGE to the next release of SQL Server, which
would allow you to write one command to accomplish both functions, but
it is not available today.

Roy Harvey
Beacon Falls, CT

Friday, March 9, 2012

MS SQL Backup Schedule

When I try to schedule a COMPLETE backup to Disk, I select the overwrite box
and schedule. Then I select DAILY and a Time, click apply, and then OK. Whe
n
I go back in to review the setting it is back the way it was with no
schedule.
What am I doing wrong ?I'm unable to reproduce this on my sql2k+sp3a. Perhaps, you want to update
to the latest service pack.
http://microsoft.com/sql
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
> When I try to schedule a COMPLETE backup to Disk, I select the overwrite
box
> and schedule. Then I select DAILY and a Time, click apply, and then OK.
When
> I go back in to review the setting it is back the way it was with no
> schedule.
> What am I doing wrong ?|||That dialog only creates an SQL Server agent job. Check in SQL Server Agent,
Jobs and you will see
you job(s) there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
> When I try to schedule a COMPLETE backup to Disk, I select the overwrite b
ox
> and schedule. Then I select DAILY and a Time, click apply, and then OK. W
hen
> I go back in to review the setting it is back the way it was with no
> schedule.
> What am I doing wrong ?|||Thank you Tibor, where can I see the scheduled jobs ?
gerrym
"Tibor Karaszi" wrote:

> That dialog only creates an SQL Server agent job. Check in SQL Server Agen
t, Jobs and you will see
> you job(s) there.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
> news:CC53F517-4247-4CA8-9477-2225742E3494@.microsoft.com...
>
>|||Enterprise Manager, Management, SQL Server Agent, Jobs.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Gerrym" <Gerrym@.discussions.microsoft.com> wrote in message
news:7DC355DB-A511-4B91-A46D-17B309D4879C@.microsoft.com...[vbcol=seagreen]
> Thank you Tibor, where can I see the scheduled jobs ?
> gerrym
> "Tibor Karaszi" wrote:
>
see[vbcol=seagreen]

Friday, March 30, 2012

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

MS SQL2000 - synchronizing tables across two sql servers - wasting my time?

Wednesday, March 28, 2012

MS SQL Server Run time Error

MS SQL server management studio express

Monday, March 26, 2012

MS SQL server login failed

MS SQL server login failed

Friday, March 23, 2012

MS SQL Server 2005 hang

MS SQL Server 2005 hang

MS SQL Server 2005 hang

Wednesday, March 21, 2012

MS SQL Server 2000 VPN problem

MS SQL Server 2000 VPN problem

MS SQL server 2000 Job limit?

MS SQL server 2000 Job limit?

MS SQL server 2000 Job limit?

MS SQL Server 2000 / MS Access - ODBC connection question

Monday, March 19, 2012

MS SQL performance from 10" to 3 minutes

Monday, March 12, 2012

MS SQL Joins

MS SQL getDate() Function remove Time

Friday, March 9, 2012

MS SQL Backup Schedule

MS Service Pack frustrations

Blog Archive

About Me