Environment Variables Within SQL

Is there some way I can access system environment variables within a SQL script? For example, if I have a script "foo.sql" that I'm calling from isql, I want to be able to substitute <hostname> with an env variable I set at the command line:

foo.sql:

update tblFoo set HostName = <hostname>

Thanks.

TerenceQ1 Is there some way I can access system environment variables within a SQL script?

A1 Yes.

Note: The following is in regard to osql (however it should hold true for isql as well)

If issuing queries from osql, (also should work using batch files), one may use environment variables i.e.( %variablename% ) directly. For example:

Define the following two environment variables:
Set TargetDB = Pubs
Set TargetTable = Authors

Then run the following example (replace SqlServer with your SqlServer instance name before running) from the command prompt:

Example:

osql SSqlServer -E -dPubs -q"exit(Select Au_LName from %TargetDB%..%TargetTable% Order By Au_LName Go Select Count(*) As 'AuthorsCount' From %TargetTable%)"|||Note that DBA's suggestion works because all the variables have been substituted PRIOR to running osql.exe. This method won't work with a script. To my knowledge there is no way to reference environment variables from SQL server.|||RE:
Note that DBA's suggestion works because all the variables have been substituted PRIOR to running osql.exe. This method won't work with a script. To my knowledge there is no way to reference environment variables from SQL server.

A good point. If the simple approach demonstrated is unworkable for the requirements at hand; another approach to consider may be to create one or more stored procedures which may be executed such that the desired results may be achieved indirectly.

For example, several utility procs may be created which shell out to the OS and execute OS commands directly or that call short VB scripts to gather, set, and / or otherwise manipulate the environment variables as required.

Enumerate Subscriptions in a Merge Replicated CE Database?

How can I list all of the Subscriptions in a CE Database that was created with the .Net System.Data.SqlServerCe.SqlCeReplication.AddSubscription method?

After I create the CE database in an sdf file, I can connect to it with SQL Servr Mgmt Studio, then see the list of Subscriptions in the Object TreeView, under "Replication"

But I can't find that Subscription list in any properties or methods of System.Data.SqlServerCe.SqlCeReplication or System.Data.SqlServerCe.SqlCeConnection

Should I be looking in RMO features like Microsoft.SqlServer.Replication?

We want to confirm that a local CE database was last synced from the expected SQL Server database by checking the Publisher property of each subscription in the CE database.

Thanks

There are a number of system tables available with all the required information, see http://blogs.msdn.com/sqlservercompact/archive/2007/02/19/howto-detect-the-database-is-merge-replicated.aspx|||

Thanks.

Relational database systems keep their data in relational tables - even SQL Server CE!

Select*from __sysMergeSubscriptions

;Select*from __sysMergeArticles

Enumerate SQL Server built-in functions

Hi

I am trying to build a tree similar to the one in SQL Server Management Studio for the system functions in the SQL language.

I would like to group them by type (e.g. string functions) and display information about the parameters and return types, etc.

Is there a way to get these programmatically? I would like to avoid typing them out by hand.

Thanks

Chris

Hi Chris,

I'm not sure this is the right forum for this question. You can retrieve this information from the sytem views. E.g.:

select

ob.name as func,

parms.name as parm,

parms.parameter_id as pid,

ty.name as type

from

sys.all_objects ob,

sys.all_parameters parms,

sys.types as ty

where

ob.type = 'FN'

and ob.object_id = parms.object_id

and parms.user_type_id = ty.user_type_id

You can tweak this to get exactly what you're looking for.

Cheers,

-Isaac

|||I guess he means the really builtin functions like CHARINDEX and so on. Otherwise I would have suggested him to use the INFORMATION_SCHEMA views to retrieve the data.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens

that's right. I do mean the really built-in functions.

Thanks anyway.

Chris

|||

I asked some of the gurus here. Alas, it looks like there really isn't a way to do this.

Sorry,

-Isaac

|||

Thanks anyway Isaac.

Chris

Enumerate SQL Server built-in functions

Hi

I am trying to build a tree similar to the one in SQL Server Management Studio for the system functions in the SQL language.

I would like to group them by type (e.g. string functions) and display information about the parameters and return types, etc.

Is there a way to get these programmatically? I would like to avoid typing them out by hand.

Thanks

Chris

Hi Chris,

I'm not sure this is the right forum for this question. You can retrieve this information from the sytem views. E.g.:

select

ob.name as func,

parms.name as parm,

parms.parameter_id as pid,

ty.name as type

from

sys.all_objects ob,

sys.all_parameters parms,

sys.types as ty

where

ob.type = 'FN'

and ob.object_id = parms.object_id

and parms.user_type_id = ty.user_type_id

You can tweak this to get exactly what you're looking for.

Cheers,

-Isaac

|||I guess he means the really builtin functions like CHARINDEX and so on. Otherwise I would have suggested him to use the INFORMATION_SCHEMA views to retrieve the data.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Jens

that's right. I do mean the really built-in functions.

Thanks anyway.

Chris

|||

I asked some of the gurus here. Alas, it looks like there really isn't a way to do this.

Sorry,

-Isaac

|||

Thanks anyway Isaac.

Chris

Enumerate ODBC System records

By calling CDatabase::OpenEx(NULL, CDatabase::forceODBCDialog)
you get the ODBC DSN Control panel selection screen thingy to choose from.
That screen has File, System and perhaps more tabs on them
I want to not go through that selection window, instead only enumerate the
System DSNs in my own app.
I know this info is stored in the windows registry, but I'm looking to do
this using the right way: documented API's.
Any idea ?
LisaTake a look at the SQLDataSources API. This will allow you to enumerate the
DSNs.
HTH
--
============
Frank Hickman
NobleSoft, Inc.
============
Replace the _nosp@.m_ with @. to reply.
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:OjuNz$02DHA.1760@.TK2MSFTNGP10.phx.gbl...

quote:

---

> By calling CDatabase::OpenEx(NULL, CDatabase::forceODBCDialog)
> you get the ODBC DSN Control panel selection screen thingy to choose from.
> That screen has File, System and perhaps more tabs on them
> I want to not go through that selection window, instead only enumerate the
> System DSNs in my own app.
> I know this info is stored in the windows registry, but I'm looking to do
> this using the right way: documented API's.
> Any idea ?
> Lisa
>

---

EnumAvailableSqlServers does not return all instances

Hello All,

When I execute

SmoApplication.EnumAvailableSqlServers("AServerNameHere"), passing in the hostname of a system I know to be running multiple instances (2x 2000 and 1 x 2005), it only returns/finds the default unnamed instance. I thought this could have been some form of security issue as per http://blogs.msdn.com/sql_protocols/archive/2005/09/24/473502.aspx, however this is not the case because:

1. When I execute SmoAppliction.EnumAvailableSqlServers() (ie - without passing in a servername) ALL instances are discovered on the above hostname.

2. If i maunally execute SmoApplication.EnumAvailableSqlServers("AServernamehere\Instance") it successfully returns/discovers the instance I explicitly pass it.

Isn't this method supposed to return all instances running on the hostname that is specified? If so, in what manner are you to pass in the hostname ("Hostname\%" or "Hostname\*")?

Cheers

Rob

Have you tried passing in the hostname only without the instance name?|||

Hi Michiel,

Absolutely; this is the issue as when I pass in the hostname, EnumAvailableSqlServers only returns the default unnamed instance and not the other n named instances.

Cheers

Rob

|||Sorry to BTT, but does anyone have any info on this?sql

EnumAvailableSqlServers does not return all instances

Hello All,

When I execute

SmoApplication.EnumAvailableSqlServers("AServerNameHere"), passing in the hostname of a system I know to be running multiple instances (2x 2000 and 1 x 2005), it only returns/finds the default unnamed instance. I thought this could have been some form of security issue as per http://blogs.msdn.com/sql_protocols/archive/2005/09/24/473502.aspx, however this is not the case because:

1. When I execute SmoAppliction.EnumAvailableSqlServers() (ie - without passing in a servername) ALL instances are discovered on the above hostname.

2. If i maunally execute SmoApplication.EnumAvailableSqlServers("AServernamehere\Instance") it successfully returns/discovers the instance I explicitly pass it.

Isn't this method supposed to return all instances running on the hostname that is specified? If so, in what manner are you to pass in the hostname ("Hostname\%" or "Hostname\*")?

Cheers

Rob

Have you tried passing in the hostname only without the instance name?|||

Hi Michiel,

Absolutely; this is the issue as when I pass in the hostname, EnumAvailableSqlServers only returns the default unnamed instance and not the other n named instances.

Cheers

Rob

|||Sorry to BTT, but does anyone have any info on this?

EnterpriseLibrary 2006 DATA ACCESS LAYER

in the class library i written the code name :customer is the lib name

using System;

using System.Collections.Generic;

using System.Text;

namespace Customer

{ class Entites

{

public int inTest;

public int inTest2;

}

}

Now in the Class1.cs i written the code

i am getting the data from the database by using enterprise lib 2006 connection function

now HOW TO BIND THE DATA TO LIST AND RETURN TYPE IS LIST

PLEASE CHECK THE CODE AND REDEFINE THE CODE

using System;

using System.Data ;

using System.Collections.Generic;

using System.Collections.Generic;

using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;

using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

using System.Collections;

using System.Xml.Serialization;

using System.Data.Common;

using Customer;

namespace Customer

{

class Class1

{

public List<Entites> getdata(int id)

{

Database db = DatabaseFactory.CreateDatabase("mycon");

System.Data.Common.DbCommand cmd ;

cmd = db.GetStoredProcCommand("GET_CUSTOMER");

cmd.CommandType = CommandType.StoredProcedure;

db.AddInParameter(cmd,"@.CID",System.Data.DbType.Int32,id);

List<Entites> objEntites = new List<Entites>();

using (IDataReader dr = db.ExecuteReader(cmd))

foreach (Entites obj in dr)

{

objEntites.inTest = obj.inTest;--ERROR LINE

// objEntites.Add(obj);

}

return objEntites;

}

}

}

Error 2 foreach statement cannot operate on variables of type 'System.Data.IDataReader' because 'System.Data.IDataReader' does not contain a public definition for 'GetEnumerator' D:\KOTI_PRJS\Enterprise\Customer\Class1.cs 34 13 Customer

Is this related to SSIS? If not, let me know and I'll be happy to move it to a more appropriate forum. If it is, please describe where the problem is occurring in SSIS.

Enterprise Manager not in programs list

Hi There,
I have SQL 2000 running on my 2003 server. The service is running in my
system tray, but when I go to start-programs, my MS SQL-enterprise manager
has disappeared. It has been there in the past and I have done a re-boot.
Where has it gone? Is there another way to get to enterprise manager? Maybe
the executible?
Please help!Bruce,
Are you loged in with the same account?
Might try creating a shortcut using:
C:\WINDOWS\system32\mmc.exe /s "C:\Program Files\Microsoft SQL
Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"
Be sure to check the paths for your system.
HTH
Jerry
"BruceS" <BruceS@.discussions.microsoft.com> wrote in message
news:330D03F1-28AE-4E8E-96E8-38AFD0A4DC59@.microsoft.com...
> Hi There,
> I have SQL 2000 running on my 2003 server. The service is running in my
> system tray, but when I go to start-programs, my MS SQL-enterprise manager
> has disappeared. It has been there in the past and I have done a re-boot.
> Where has it gone? Is there another way to get to enterprise manager?
> Maybe
> the executible?
> Please help!|||Hey Jerry,
Thanks for the quick response!
It seems that this file "SQL Server Enterprise Manager.MSC" is missing. I
don't see it in the path you mentioned, or anywhere else. Can I re-create
this?
"Jerry Spivey" wrote:
> Bruce,
> Are you loged in with the same account?
> Might try creating a shortcut using:
> C:\WINDOWS\system32\mmc.exe /s "C:\Program Files\Microsoft SQL
> Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"
> Be sure to check the paths for your system.
> HTH
> Jerry
> "BruceS" <BruceS@.discussions.microsoft.com> wrote in message
> news:330D03F1-28AE-4E8E-96E8-38AFD0A4DC59@.microsoft.com...
> > Hi There,
> >
> > I have SQL 2000 running on my 2003 server. The service is running in my
> > system tray, but when I go to start-programs, my MS SQL-enterprise manager
> > has disappeared. It has been there in the past and I have done a re-boot.
> > Where has it gone? Is there another way to get to enterprise manager?
> > Maybe
> > the executible?
> >
> > Please help!
>
>|||Hmmm...not sure. Maybe copy it from a diffent system? Yeah the path may
be different depending on your installation. EM is a snap-in to the
Microsoft Management Console (MMC) so you could just create a new MMC and
then add in the EM snap-in. This approach also allows you to add in
additional useful snap-ins.
You can create one by Start-->Run and typing in MMC and pressing OK.
You can see a picture of this in the System Administration chapter of the
SQL Server 2000 Operations Guide I wrote a while back at:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops4.mspx
HTH
Jerry
"BruceS" <BruceS@.discussions.microsoft.com> wrote in message
news:5D9E729F-0511-4180-90CD-9C8CF5D8BF64@.microsoft.com...
> Hey Jerry,
> Thanks for the quick response!
> It seems that this file "SQL Server Enterprise Manager.MSC" is missing. I
> don't see it in the path you mentioned, or anywhere else. Can I re-create
> this?
> "Jerry Spivey" wrote:
>> Bruce,
>> Are you loged in with the same account?
>> Might try creating a shortcut using:
>> C:\WINDOWS\system32\mmc.exe /s "C:\Program Files\Microsoft SQL
>> Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC"
>> Be sure to check the paths for your system.
>> HTH
>> Jerry
>> "BruceS" <BruceS@.discussions.microsoft.com> wrote in message
>> news:330D03F1-28AE-4E8E-96E8-38AFD0A4DC59@.microsoft.com...
>> > Hi There,
>> >
>> > I have SQL 2000 running on my 2003 server. The service is running in my
>> > system tray, but when I go to start-programs, my MS SQL-enterprise
>> > manager
>> > has disappeared. It has been there in the past and I have done a
>> > re-boot.
>> > Where has it gone? Is there another way to get to enterprise manager?
>> > Maybe
>> > the executible?
>> >
>> > Please help!
>>

Enterprise Manager Job Scheduler and the Time change problem

We just noticed a problem with the SQL Server Agent's Job Scheduler. During
the time change the other week, the system clock would reset itself every
day. I've seen it go an hour ahead and an hour behind. During this time,
some of the jobs got out of synch.
Let's say we have a few jobs that run every 4 hours. one of the clock
changes happened in the hour before it was to run, so afterward, the current
time is now passed when the job was scheduled to run. Since the time for
it's next scheduled run has already passed, the job just sits there and never
runs again.
Why does SQL Server not see that a jobs scheduled time has passed, and just
continue with the next run in the schedule?
Hi
"Granola Pete" wrote:

> We just noticed a problem with the SQL Server Agent's Job Scheduler. During
> the time change the other week, the system clock would reset itself every
> day. I've seen it go an hour ahead and an hour behind. During this time,
> some of the jobs got out of synch.
> Let's say we have a few jobs that run every 4 hours. one of the clock
> changes happened in the hour before it was to run, so afterward, the current
> time is now passed when the job was scheduled to run. Since the time for
> it's next scheduled run has already passed, the job just sits there and never
> runs again.
> Why does SQL Server not see that a jobs scheduled time has passed, and just
> continue with the next run in the schedule?
Is the system time being effected and where/how is the time being
synchronised to?
John
|||Yes the system clock is what was being affected. From about Monday the 12th
until about Wed 14th or Thurs 15th, the clock would reset itself each morning
to a different time. It is synching itself to "time.windows.com". It has
stabilized itself now.
Having the time switch near the time when a scheduled job is supposed to run
doesn't seem much of a problem.
It is more troubling that the Agent Job Scheduler couldn't recover the
schedule after "missing" a run at a certain time. Granted, I should have
been checking the jobs, but there is usually no need unless I get
notification that an error has occured.
"John Bell" wrote:

> Hi
> "Granola Pete" wrote:
>
> Is the system time being effected and where/how is the time being
> synchronised to?
> John
|||Hi
"Granola Pete" wrote:

> Yes the system clock is what was being affected. From about Monday the 12th
> until about Wed 14th or Thurs 15th, the clock would reset itself each morning
> to a different time. It is synching itself to "time.windows.com". It has
> stabilized itself now.
> Having the time switch near the time when a scheduled job is supposed to run
> doesn't seem much of a problem.
> It is more troubling that the Agent Job Scheduler couldn't recover the
> schedule after "missing" a run at a certain time. Granted, I should have
> been checking the jobs, but there is usually no need unless I get
> notification that an error has occured.
>
I am not sure how you are will reliably know that there has been a run
missed because of a time change! There could be numerous scenarios of time
changes that you would have to provide decissions on whether or not the job
should run.
John
|||Well, I know that a run has been missed and that it hasn't run again simply
by the fact that the scheduler shows the "Last Run Status(Start Date)" field
shows the 11th and the "Next Run Date" shows the 12th and it is already the
20th, no history of the run since 11th.
the problem isn't so much that it missed it, but that it didn't hit the next
scheduled time to run.
The system doesn't necessarily have to be smart enough to look back and see
when the last time it ran. If it is on a recurring schedule, say every 4
hours, it doesn't need to know that it didn't run at noon, only that it needs
to run at 4 and then 8, etc. but I would hope it is smart enough to know
that if it's "Next Run Date" is somehow an earlier date than the current
time, that it recovers or at least throws an error.
The thing is, if a job fails once, it still runs the next time it is
scheduled to run. How is that any different from missing a run time? It is
just odd to me that it would work in one instance, but not the other.
"John Bell" wrote:
> I am not sure how you are will reliably know that there has been a run
> missed because of a time change! There could be numerous scenarios of time
> changes that you would have to provide decissions on whether or not the job
> should run.
> John

Enterprise Manager Job Scheduler and the Time change problem

We just noticed a problem with the SQL Server Agent's Job Scheduler. During
the time change the other week, the system clock would reset itself every
day. I've seen it go an hour ahead and an hour behind. During this time,
some of the jobs got out of synch.
Let's say we have a few jobs that run every 4 hours. one of the clock
changes happened in the hour before it was to run, so afterward, the current
time is now passed when the job was scheduled to run. Since the time for
it's next scheduled run has already passed, the job just sits there and neve
r
runs again.
Why does SQL Server not see that a jobs scheduled time has passed, and just
continue with the next run in the schedule?Hi
"Granola Pete" wrote:

> We just noticed a problem with the SQL Server Agent's Job Scheduler. Duri
ng
> the time change the other week, the system clock would reset itself every
> day. I've seen it go an hour ahead and an hour behind. During this time,
> some of the jobs got out of synch.
> Let's say we have a few jobs that run every 4 hours. one of the clock
> changes happened in the hour before it was to run, so afterward, the curre
nt
> time is now passed when the job was scheduled to run. Since the time for
> it's next scheduled run has already passed, the job just sits there and ne
ver
> runs again.
> Why does SQL Server not see that a jobs scheduled time has passed, and jus
t
> continue with the next run in the schedule?
Is the system time being effected and where/how is the time being
synchronised to?
John|||Yes the system clock is what was being affected. From about Monday the 12th
until about Wed 14th or Thurs 15th, the clock would reset itself each mornin
g
to a different time. It is synching itself to "time.windows.com". It has
stabilized itself now.
Having the time switch near the time when a scheduled job is supposed to run
doesn't seem much of a problem.
It is more troubling that the Agent Job Scheduler couldn't recover the
schedule after "missing" a run at a certain time. Granted, I should have
been checking the jobs, but there is usually no need unless I get
notification that an error has occured.
"John Bell" wrote:

> Hi
> "Granola Pete" wrote:
>
> Is the system time being effected and where/how is the time being
> synchronised to?
> John|||Hi
"Granola Pete" wrote:

> Yes the system clock is what was being affected. From about Monday the 12
th
> until about Wed 14th or Thurs 15th, the clock would reset itself each morn
ing
> to a different time. It is synching itself to "time.windows.com". It has
> stabilized itself now.
> Having the time switch near the time when a scheduled job is supposed to r
un
> doesn't seem much of a problem.
> It is more troubling that the Agent Job Scheduler couldn't recover the
> schedule after "missing" a run at a certain time. Granted, I should have
> been checking the jobs, but there is usually no need unless I get
> notification that an error has occured.
>
I am not sure how you are will reliably know that there has been a run
missed because of a time change! There could be numerous scenarios of time
changes that you would have to provide decissions on whether or not the job
should run.
John|||Well, I know that a run has been missed and that it hasn't run again simply
by the fact that the scheduler shows the "Last Run Status(Start Date)" field
shows the 11th and the "Next Run Date" shows the 12th and it is already the
20th, no history of the run since 11th.
the problem isn't so much that it missed it, but that it didn't hit the next
scheduled time to run.
The system doesn't necessarily have to be smart enough to look back and see
when the last time it ran. If it is on a recurring schedule, say every 4
hours, it doesn't need to know that it didn't run at noon, only that it need
s
to run at 4 and then 8, etc. but I would hope it is smart enough to know
that if it's "Next Run Date" is somehow an earlier date than the current
time, that it recovers or at least throws an error.
The thing is, if a job fails once, it still runs the next time it is
scheduled to run. How is that any different from missing a run time? It i
s
just odd to me that it would work in one instance, but not the other.
"John Bell" wrote:
> I am not sure how you are will reliably know that there has been a run
> missed because of a time change! There could be numerous scenarios of time
> changes that you would have to provide decissions on whether or not the jo
b
> should run.
> John

Enterprise Manager Job Scheduler and the Time change problem

We just noticed a problem with the SQL Server Agent's Job Scheduler. During
the time change the other week, the system clock would reset itself every
day. I've seen it go an hour ahead and an hour behind. During this time,
some of the jobs got out of synch.
Let's say we have a few jobs that run every 4 hours. one of the clock
changes happened in the hour before it was to run, so afterward, the current
time is now passed when the job was scheduled to run. Since the time for
it's next scheduled run has already passed, the job just sits there and never
runs again.
Why does SQL Server not see that a jobs scheduled time has passed, and just
continue with the next run in the schedule?Hi
"Granola Pete" wrote:
> We just noticed a problem with the SQL Server Agent's Job Scheduler. During
> the time change the other week, the system clock would reset itself every
> day. I've seen it go an hour ahead and an hour behind. During this time,
> some of the jobs got out of synch.
> Let's say we have a few jobs that run every 4 hours. one of the clock
> changes happened in the hour before it was to run, so afterward, the current
> time is now passed when the job was scheduled to run. Since the time for
> it's next scheduled run has already passed, the job just sits there and never
> runs again.
> Why does SQL Server not see that a jobs scheduled time has passed, and just
> continue with the next run in the schedule?
Is the system time being effected and where/how is the time being
synchronised to?
John|||Yes the system clock is what was being affected. From about Monday the 12th
until about Wed 14th or Thurs 15th, the clock would reset itself each morning
to a different time. It is synching itself to "time.windows.com". It has
stabilized itself now.
Having the time switch near the time when a scheduled job is supposed to run
doesn't seem much of a problem.
It is more troubling that the Agent Job Scheduler couldn't recover the
schedule after "missing" a run at a certain time. Granted, I should have
been checking the jobs, but there is usually no need unless I get
notification that an error has occured.
"John Bell" wrote:
> Hi
> "Granola Pete" wrote:
> > We just noticed a problem with the SQL Server Agent's Job Scheduler. During
> > the time change the other week, the system clock would reset itself every
> > day. I've seen it go an hour ahead and an hour behind. During this time,
> > some of the jobs got out of synch.
> >
> > Let's say we have a few jobs that run every 4 hours. one of the clock
> > changes happened in the hour before it was to run, so afterward, the current
> > time is now passed when the job was scheduled to run. Since the time for
> > it's next scheduled run has already passed, the job just sits there and never
> > runs again.
> >
> > Why does SQL Server not see that a jobs scheduled time has passed, and just
> > continue with the next run in the schedule?
> Is the system time being effected and where/how is the time being
> synchronised to?
> John|||Hi
"Granola Pete" wrote:
> Yes the system clock is what was being affected. From about Monday the 12th
> until about Wed 14th or Thurs 15th, the clock would reset itself each morning
> to a different time. It is synching itself to "time.windows.com". It has
> stabilized itself now.
> Having the time switch near the time when a scheduled job is supposed to run
> doesn't seem much of a problem.
> It is more troubling that the Agent Job Scheduler couldn't recover the
> schedule after "missing" a run at a certain time. Granted, I should have
> been checking the jobs, but there is usually no need unless I get
> notification that an error has occured.
>
I am not sure how you are will reliably know that there has been a run
missed because of a time change! There could be numerous scenarios of time
changes that you would have to provide decissions on whether or not the job
should run.
John|||Well, I know that a run has been missed and that it hasn't run again simply
by the fact that the scheduler shows the "Last Run Status(Start Date)" field
shows the 11th and the "Next Run Date" shows the 12th and it is already the
20th, no history of the run since 11th.
the problem isn't so much that it missed it, but that it didn't hit the next
scheduled time to run.
The system doesn't necessarily have to be smart enough to look back and see
when the last time it ran. If it is on a recurring schedule, say every 4
hours, it doesn't need to know that it didn't run at noon, only that it needs
to run at 4 and then 8, etc. but I would hope it is smart enough to know
that if it's "Next Run Date" is somehow an earlier date than the current
time, that it recovers or at least throws an error.
The thing is, if a job fails once, it still runs the next time it is
scheduled to run. How is that any different from missing a run time? It is
just odd to me that it would work in one instance, but not the other.
"John Bell" wrote:
> I am not sure how you are will reliably know that there has been a run
> missed because of a time change! There could be numerous scenarios of time
> changes that you would have to provide decissions on whether or not the job
> should run.
> John

Enterprise Manager

We have inherited a system with a Sybase DB and have been
asked to move it to SQL 7. We have converted the data and
the database with expected datatype problems and it seems
to be working. The application is a PowerBuilder 7
program that was using and ODBC and is now using OLE DB
for the SQL. In a one man environment it is great, move
it to the production machine and tell all 47 folks to sign
on, the Enterprise Manager quits responding, tables lock
all over the place and no one can do updates or inserts.
The tables have PK and FK with clustered indexes pretty
thoroughly placed. The machine is a NT 4.0 with 1GB of
RAM with a raid drive 20GB.
Any suggestions on what to do to increase performance on
this one?
TIA,
camiHello Cami !
First of all you must find out who is holding locks and what objects,
respectiv what actions are holding locks on the tables.
Jens Süßmeyer.
"Cami" <clawson@.informs.com> schrieb im Newsbeitrag
news:029801c36688$a4567220$a401280a@.phx.gbl...
> We have inherited a system with a Sybase DB and have been
> asked to move it to SQL 7. We have converted the data and
> the database with expected datatype problems and it seems
> to be working. The application is a PowerBuilder 7
> program that was using and ODBC and is now using OLE DB
> for the SQL. In a one man environment it is great, move
> it to the production machine and tell all 47 folks to sign
> on, the Enterprise Manager quits responding, tables lock
> all over the place and no one can do updates or inserts.
> The tables have PK and FK with clustered indexes pretty
> thoroughly placed. The machine is a NT 4.0 with 1GB of
> RAM with a raid drive 20GB.
> Any suggestions on what to do to increase performance on
> this one?
> TIA,
> cami
>|||Jens,
We have located the tables but it happens randomly on different tables and the most frustrating thing is the Enterprise Manager just dying while I'm trying to watch who is doing what and what is locking. I can do all of that in the QueryAnalyzer but what is locking up the EM?
tia,
cami
>--Original Message--
>Hello Cami !
>First of all you must find out who is holding locks and what objects,
>respectiv what actions are holding locks on the tables.
>Jens S=FC=DFmeyer.
>
>"Cami" <clawson@.informs.com> schrieb im Newsbeitrag
>news:029801c36688$a4567220$a401280a@.phx.gbl...
>> We have inherited a system with a Sybase DB and have been
>> asked to move it to SQL 7. We have converted the data and
>> the database with expected datatype problems and it seems
>> to be working. The application is a PowerBuilder 7
>> program that was using and ODBC and is now using OLE DB
>> for the SQL. In a one man environment it is great, move
>> it to the production machine and tell all 47 folks to sign
>> on, the Enterprise Manager quits responding, tables lock
>> all over the place and no one can do updates or inserts.
>> The tables have PK and FK with clustered indexes pretty
>> thoroughly placed. The machine is a NT 4.0 with 1GB of
>> RAM with a raid drive 20GB.
>> Any suggestions on what to do to increase performance on
>> this one?
>> TIA,
>> cami
>
>.
>|||I foresee running SQL profiler in your future. You will want to see if you
are having deadlocks and who/what is blocking who/what.
Here are a couple useful links
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_7a_3xrf.asp -
Detecting and Ending Deadlocks
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_00mr.asp -
Monitoring Server Performance and Activity
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/itcommunity/chats/trans/sql/sql1023.asp -
Performance Monitoring for the SQL Server Professional Chat
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Cami" <clawson@.informs.com> wrote in message
news:029801c36688$a4567220$a401280a@.phx.gbl...
> We have inherited a system with a Sybase DB and have been
> asked to move it to SQL 7. We have converted the data and
> the database with expected datatype problems and it seems
> to be working. The application is a PowerBuilder 7
> program that was using and ODBC and is now using OLE DB
> for the SQL. In a one man environment it is great, move
> it to the production machine and tell all 47 folks to sign
> on, the Enterprise Manager quits responding, tables lock
> all over the place and no one can do updates or inserts.
> The tables have PK and FK with clustered indexes pretty
> thoroughly placed. The machine is a NT 4.0 with 1GB of
> RAM with a raid drive 20GB.
> Any suggestions on what to do to increase performance on
> this one?
> TIA,
> cami
>

entering data into sql database

hi

I am working on webforms i have to insert system date into sql database when a button is clicked.

I have have 3 fields on the webform and a submit button.

I have to insert the date along with the other fileds into the sql database

when I am trying to insert date using getdate() it is getting inserted but not into the same row as that of other fields.

Can you plzzz help me with the code for inserting system data into the database into the same row as that of other fields

myCommand2 =New SqlCommand("Insert into Items(Requestdate) values (getdate()) where Hospital= '" & DropDownList2.SelectedItem.Text & "' ", myConnection)

ra = myCommand2.ExecuteNonQuery()

myConnection.Close()

THE ABOVE CODE GIVES AN SYNTAX ERROR NEAR "WHERE"

please help

you can also post me ur replies onurs_forever_tanya2001@.yahoo.co.in

Thanks bye

You can't specify awhere clause on an insert statement as the whole point of an insert is to add a record (i.e. it doesn't already exist). If you want to update and existing record, you will have to use anupdatestatement.

|||

by using update statement will i be able to insert new record then?

because i have to take system date while inserting a new record...

please help

|||

tanya2001:

by using update statement will i be able to insert new record then?

No. If you want to insert a record you have to use an insert statement (but remove your where clause). If you want to update an existing record, use an update statement.

|||

Hey tanya ,

Thanks for your question .

let me descrive some essesntioal stuff for you first.

Insert Statement , we use this statement when we wantadd new record to our table , it means that record doesn't exist and we are going to add that , so in this case we wont usingwhere on this state ment becuase there is no any rows and we want to add that.

Update Statement , we use this statement when we already we have one or more records in our table and we want just change some of their value we use update statement and we use where to specify which rows we are going to change.

In you solution , if you are going to Insert New value to table that doesn't not exist at all use Insert Statement, if you are going to change some value in your database use update.

|||

thanks for your answer but i want to insert system date as well with the new record in the database

so how can i insert the system date as well as the new record by entering a single submit button

its actually like i have 3 fields in a webform so i have to insert those three fields as well as system date should be inserted when submit is clicked

please help

|||

thanks all i got the answer

Enter Default Values for all columns in all tables except Primary Keys

How can i enter Default Values of " " to all the columns of type character
of all the tables (excluding system tables) and Default Values of 0
of all columns of type numbers. Excluding all primary key columns.

Thank youOn Sun, 3 Apr 2005 18:05:46 -0400, serge wrote:

>How can i enter Default Values of " " to all the columns of type character
>of all the tables (excluding system tables) and Default Values of 0
>of all columns of type numbers. Excluding all primary key columns.
>Thank you

Hi Serge,

CREATE TABLE Example (PKCol int NOT NULL PRIMARY KEY,
NumCol int NOT NULL DEFAULT 0,
CharCol varchar(20) NOT NULL DEFAULT '')
go
INSERT Example (PKCol)
VALUES (1)
SELECT * FROM Example
go
DROP TABLE Example
go

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)