Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgs
Hi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||You can check the stored procedure sp_helplogins
best Regards,
Chandra
http://chanduas.blogspot.com/
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
Showing posts with label default. Show all posts
Showing posts with label default. Show all posts
Thursday, March 29, 2012
Getting a list of user access to which databases - help
Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
Getting a list of user access to which databases - help
Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want t
o
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgsHi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs|||You can check the stored procedure sp_helplogins
--
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want t
o
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they a
re
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that user
A
> has the access to these users
> appreciate any advise
> tks & rdgs
Monday, March 26, 2012
GetReportParameters - How can I get the default value of the parameter?
I'm using the GetReportParameters method to get the parameters of my report.
I can get the name, type, etc., but I'm unsure of how to get the actual
value assigned to that parameter.
Any suggestions?Hi Bob:
See my article:
Using GetReportParameters in Reporting Services
http://odetocode.com/Articles/123.aspx
HTH,
--
Scott
http://www.OdeToCode.com
n Wed, 15 Sep 2004 16:48:01 -0600, "Bob Thomas" <bobthomas@.yahoo.com>
wrote:
>I'm using the GetReportParameters method to get the parameters of my report.
>I can get the name, type, etc., but I'm unsure of how to get the actual
>value assigned to that parameter.
>Any suggestions?
>
I can get the name, type, etc., but I'm unsure of how to get the actual
value assigned to that parameter.
Any suggestions?Hi Bob:
See my article:
Using GetReportParameters in Reporting Services
http://odetocode.com/Articles/123.aspx
HTH,
--
Scott
http://www.OdeToCode.com
n Wed, 15 Sep 2004 16:48:01 -0600, "Bob Thomas" <bobthomas@.yahoo.com>
wrote:
>I'm using the GetReportParameters method to get the parameters of my report.
>I can get the name, type, etc., but I'm unsure of how to get the actual
>value assigned to that parameter.
>Any suggestions?
>
Friday, March 23, 2012
GetDate() in Constraint using user's system time, Stored Proc using Server's.
All,
I have a table that has a Default Constraint for setting a DateTime field. I have a stored procedure that calls data from the table where the date field is <= GetDate().
I performed the following test:
1. Called insert stored proc and checked date field for recently added entry by query window ( 2007-03-01 11:09:44.000 ). This matches my (user) system date and time.
2. Immediately call GetDate() from the query window (2007-03-01 11:07:47.727). I assume this is the Server system date and time.
*note: These servers are on separate domains and therefore could have different system times.
This causes my select stored procedure to NOT return the values I just entered.
Any ideas on why this might occur? Does GetDate() run within the context of it's call (ie Called from application, uses web server system time, but called from query window uses server)?
If more that one server is involved I would check the system time delta between them and compare that to what you see in your test. In my experience two servers in the same domain getting time from the same server can be off by minutes...depending on how often they poll.|||Todd:
Are you saying that you are trying to have the trigger update a datetime field and then use the getdate() function to try match the inserted record? If so, that is not a very good idea; this procedure will not be "tight" enough.
Also, if you are trying to use getdate() as method of "water-marking" records so that you can dynamically process records according to whether or not they are greater than or equal to the getdate() watermark, that kind of process will also "leak" records from time to time. This is a problem that I have battled a number of times. I can put together a mock-up to demonstrate that leakage problem if you would like.
sqlWednesday, March 21, 2012
Getdate() default value does not get created
I have a table in which a field named DateCreated has the default value
(getdate()). THis has always worked before in other apps, it would naturally
write the computer's date time whenever a new record was created. I can't
figure out why all of a sudden the field in this table no longer get
populated with the default value.
Anyone have any ideas on where to start looking? I'm stumped.
BobAn INSERT that references the column will override the default, even
if it is assigned NULL. Any INSERT without the (column list) after
the table name will have this problem. The INSERT must use an
explicit column list, and the column must not appear in that list, for
the default to be applied.
Roy Harvey
Beacon Falls, CT
On Fri, 16 Jun 2006 15:57:44 -0400, "Bob" <bdufour@.sgiims.com> wrote:
>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would naturall
y
>write the computer's date time whenever a new record was created. I can't
>figure out why all of a sudden the field in this table no longer get
>populated with the default value.
>Anyone have any ideas on where to start looking? I'm stumped.
>Bob|||Can you show the table structure (CREATE TABLE, not a hand-written list of
columns) and the actual insert statement that fails to generate the correct
value?
"Bob" <bdufour@.sgiims.com> wrote in message
news:%239SVj8XkGHA.4528@.TK2MSFTNGP05.phx.gbl...
>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would
>naturally write the computer's date time whenever a new record was created.
>I can't figure out why all of a sudden the field in this table no longer
>get populated with the default value.
> Anyone have any ideas on where to start looking? I'm stumped.
> Bob
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Can you show the table structure (CREATE TABLE, not a hand-written list of
> columns) and the actual insert statement that fails to generate the corre
ct
> value?
>
Also any triggers that are on the table...
(getdate()). THis has always worked before in other apps, it would naturally
write the computer's date time whenever a new record was created. I can't
figure out why all of a sudden the field in this table no longer get
populated with the default value.
Anyone have any ideas on where to start looking? I'm stumped.
BobAn INSERT that references the column will override the default, even
if it is assigned NULL. Any INSERT without the (column list) after
the table name will have this problem. The INSERT must use an
explicit column list, and the column must not appear in that list, for
the default to be applied.
Roy Harvey
Beacon Falls, CT
On Fri, 16 Jun 2006 15:57:44 -0400, "Bob" <bdufour@.sgiims.com> wrote:
>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would naturall
y
>write the computer's date time whenever a new record was created. I can't
>figure out why all of a sudden the field in this table no longer get
>populated with the default value.
>Anyone have any ideas on where to start looking? I'm stumped.
>Bob|||Can you show the table structure (CREATE TABLE, not a hand-written list of
columns) and the actual insert statement that fails to generate the correct
value?
"Bob" <bdufour@.sgiims.com> wrote in message
news:%239SVj8XkGHA.4528@.TK2MSFTNGP05.phx.gbl...
>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would
>naturally write the computer's date time whenever a new record was created.
>I can't figure out why all of a sudden the field in this table no longer
>get populated with the default value.
> Anyone have any ideas on where to start looking? I'm stumped.
> Bob
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Can you show the table structure (CREATE TABLE, not a hand-written list of
> columns) and the actual insert statement that fails to generate the corre
ct
> value?
>
Also any triggers that are on the table...
GETDATE() as default value is out of sync
I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?
Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegro ups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>
|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||All fields are datetime fields. They are updated by stored procedures
(other than StartSession which is updated by the default value of
GETDATE on insert) using GETDATE on the sql server. The dates are not
passed from the client.
There are no triggers on the table.
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:
> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||All fields are datetime fields. The values are updated using stored
procedures that use the GETDATE function. I don't have any sample info
available right now, but I'll try to get something together and reply.
There's a table with a couple of datetime fields. There's a stored
procedure that inserts a new record into the table. When that happens,
the SessionStart datetime field value is set using the default value
GETDATE. It is not implicitly set by the stored procedure. There's an
identity field SessionID that is returned to identify this record for
later use. Then later, using this SessionID, a stored procedure is
called that updates a second datetime field using GETDATE. The second
datetime field has a value earlier than the datetime field that was
populated when the record was inserted. This doesn't make sense to me. I
have searched all stored procedures. Nothing ever sets the SessionStart
value. It is only populated during an insert using the default value
GETDATE. All datetime fields are populated using GETDATE, so I'm
confused how any of them could be earlier than the datetime field that
was populated on insert.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Here's the first procedure that inserts the new record into the table.
CREATE PROCEDURE StartSession@.nField1 int = 0,
@.nField2int = 0,
@.nField3int = 0,
@.nField4 int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nSessionID int
BEGIN TRANSACTION
INSERT INTO dbo.tblTracking(fldField1,
fldField2,
fldField3,
fldField4)
VALUES (@.nField1,
@.nField2,
@.nField3,
@.nField4 )
COMMIT TRANSACTION
SELECT @.nSessionID = SCOPE_IDENTITY()
--- return
recordset with values from new session
---DECLARE
@.nRowCount int
SELECT fldSessionID,
fldSessionStart,
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
Here's the second procedure that updates the other date field.
CREATE PROCEDURE StartTransaction@.nSessionID int
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.tblTracking SET fldTransStart=GETDATE() WHERE fldSessionID =
@.nSessionID
SELECT fldSessionID,
fldSessionStart,
fldTransStart
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
The table has the following fields
fldSessionID identity
fldSessionStart datetime (default=GETDATE)
fldTransStart datetime
fldTransEnd datetime
fldSessionClose datetime
fldField1 int (default 0)
fldField2 int (default 0)
fldField3 int (default 0)
fldField4 int (default 0)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||<snip>
Unfortunately, that does not show anyone how the other datetime columns are
populated - which is the likely source of the problem.
You should be able to easily find the error by creating a trigger on the
table that enforced the rules that you are expecting.
|||The other 2 datetime colums are identical to the StartTransaction stored
procedure except the names are EndTransaction and CloseSession and the
fields they update are fldTransEnd and fldSessionClose. Otherwise,
identical. The 3 datetime values that are implicitly set by these stored
procedures are all correct in that they increment with time. It's just
the original datetime value that's not implicitly populated, but rather
gets it's value from the default during insert that is incorrect. It is
later than any of the remaining values.
I'm not sure what you mean with the trigger. Do you mean that I could
check to see if the SessionStart is later than the current datetime
before I update the record? and still, that might tell me that it's
happening, which is what started this inquiry, but what else will that
tell me?
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||> the original datetime value that's not implicitly populated, but rather
> gets it's value from the default during insert that is incorrect. It is
> later than any of the remaining values.
Can you please show us the CREATE TABLE statement generated by Generate SQL
Script. See my signature for details -- please read the link in full.
Also, please show us *ALL* of the stored procedures. This way, we can try
to reproduce.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?
Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegro ups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>
|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||All fields are datetime fields. They are updated by stored procedures
(other than StartSession which is updated by the default value of
GETDATE on insert) using GETDATE on the sql server. The dates are not
passed from the client.
There are no triggers on the table.
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:
> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||All fields are datetime fields. The values are updated using stored
procedures that use the GETDATE function. I don't have any sample info
available right now, but I'll try to get something together and reply.
There's a table with a couple of datetime fields. There's a stored
procedure that inserts a new record into the table. When that happens,
the SessionStart datetime field value is set using the default value
GETDATE. It is not implicitly set by the stored procedure. There's an
identity field SessionID that is returned to identify this record for
later use. Then later, using this SessionID, a stored procedure is
called that updates a second datetime field using GETDATE. The second
datetime field has a value earlier than the datetime field that was
populated when the record was inserted. This doesn't make sense to me. I
have searched all stored procedures. Nothing ever sets the SessionStart
value. It is only populated during an insert using the default value
GETDATE. All datetime fields are populated using GETDATE, so I'm
confused how any of them could be earlier than the datetime field that
was populated on insert.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Here's the first procedure that inserts the new record into the table.
CREATE PROCEDURE StartSession@.nField1 int = 0,
@.nField2int = 0,
@.nField3int = 0,
@.nField4 int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nSessionID int
BEGIN TRANSACTION
INSERT INTO dbo.tblTracking(fldField1,
fldField2,
fldField3,
fldField4)
VALUES (@.nField1,
@.nField2,
@.nField3,
@.nField4 )
COMMIT TRANSACTION
SELECT @.nSessionID = SCOPE_IDENTITY()
--- return
recordset with values from new session
---DECLARE
@.nRowCount int
SELECT fldSessionID,
fldSessionStart,
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
Here's the second procedure that updates the other date field.
CREATE PROCEDURE StartTransaction@.nSessionID int
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.tblTracking SET fldTransStart=GETDATE() WHERE fldSessionID =
@.nSessionID
SELECT fldSessionID,
fldSessionStart,
fldTransStart
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
The table has the following fields
fldSessionID identity
fldSessionStart datetime (default=GETDATE)
fldTransStart datetime
fldTransEnd datetime
fldSessionClose datetime
fldField1 int (default 0)
fldField2 int (default 0)
fldField3 int (default 0)
fldField4 int (default 0)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||<snip>
Unfortunately, that does not show anyone how the other datetime columns are
populated - which is the likely source of the problem.
You should be able to easily find the error by creating a trigger on the
table that enforced the rules that you are expecting.
|||The other 2 datetime colums are identical to the StartTransaction stored
procedure except the names are EndTransaction and CloseSession and the
fields they update are fldTransEnd and fldSessionClose. Otherwise,
identical. The 3 datetime values that are implicitly set by these stored
procedures are all correct in that they increment with time. It's just
the original datetime value that's not implicitly populated, but rather
gets it's value from the default during insert that is incorrect. It is
later than any of the remaining values.
I'm not sure what you mean with the trigger. Do you mean that I could
check to see if the SessionStart is later than the current datetime
before I update the record? and still, that might tell me that it's
happening, which is what started this inquiry, but what else will that
tell me?
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||> the original datetime value that's not implicitly populated, but rather
> gets it's value from the default during insert that is incorrect. It is
> later than any of the remaining values.
Can you please show us the CREATE TABLE statement generated by Generate SQL
Script. See my signature for details -- please read the link in full.
Also, please show us *ALL* of the stored procedures. This way, we can try
to reproduce.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
GETDATE() as default value is out of sync
I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegroups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:
> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegroups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:
> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>
GETDATE() as default value is out of sync
I have a table which has several date fields (SessionStart, TransStart,
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegroups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||All fields are datetime fields. They are updated by stored procedures
(other than StartSession which is updated by the default value of
GETDATE on insert) using GETDATE on the sql server. The dates are not
passed from the client.
There are no triggers on the table.
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:
> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||All fields are datetime fields. The values are updated using stored
procedures that use the GETDATE function. I don't have any sample info
available right now, but I'll try to get something together and reply.
There's a table with a couple of datetime fields. There's a stored
procedure that inserts a new record into the table. When that happens,
the SessionStart datetime field value is set using the default value
GETDATE. It is not implicitly set by the stored procedure. There's an
identity field SessionID that is returned to identify this record for
later use. Then later, using this SessionID, a stored procedure is
called that updates a second datetime field using GETDATE. The second
datetime field has a value earlier than the datetime field that was
populated when the record was inserted. This doesn't make sense to me. I
have searched all stored procedures. Nothing ever sets the SessionStart
value. It is only populated during an insert using the default value
GETDATE. All datetime fields are populated using GETDATE, so I'm
confused how any of them could be earlier than the datetime field that
was populated on insert.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Here's the first procedure that inserts the new record into the table.
CREATE PROCEDURE StartSession @.nField1 int = 0,
@.nField2 int = 0,
@.nField3 int = 0,
@.nField4 int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nSessionID int
BEGIN TRANSACTION
INSERT INTO dbo.tblTracking( fldField1,
fldField2,
fldField3,
fldField4)
VALUES ( @.nField1,
@.nField2,
@.nField3,
@.nField4 )
COMMIT TRANSACTION
SELECT @.nSessionID = SCOPE_IDENTITY()
--- return
recordset with values from new session
---DECLARE
@.nRowCount int
SELECT fldSessionID,
fldSessionStart,
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
Here's the second procedure that updates the other date field.
CREATE PROCEDURE StartTransaction @.nSessionID int
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.tblTracking SET fldTransStart=GETDATE() WHERE fldSessionID =
@.nSessionID
SELECT fldSessionID,
fldSessionStart,
fldTransStart
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
The table has the following fields
fldSessionID identity
fldSessionStart datetime (default=GETDATE)
fldTransStart datetime
fldTransEnd datetime
fldSessionClose datetime
fldField1 int (default 0)
fldField2 int (default 0)
fldField3 int (default 0)
fldField4 int (default 0)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||<snip>
Unfortunately, that does not show anyone how the other datetime columns are
populated - which is the likely source of the problem.
You should be able to easily find the error by creating a trigger on the
table that enforced the rules that you are expecting.|||The other 2 datetime colums are identical to the StartTransaction stored
procedure except the names are EndTransaction and CloseSession and the
fields they update are fldTransEnd and fldSessionClose. Otherwise,
identical. The 3 datetime values that are implicitly set by these stored
procedures are all correct in that they increment with time. It's just
the original datetime value that's not implicitly populated, but rather
gets it's value from the default during insert that is incorrect. It is
later than any of the remaining values.
I'm not sure what you mean with the trigger. Do you mean that I could
check to see if the SessionStart is later than the current datetime
before I update the record? and still, that might tell me that it's
happening, which is what started this inquiry, but what else will that
tell me?
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||> the original datetime value that's not implicitly populated, but rather
> gets it's value from the default during insert that is incorrect. It is
> later than any of the remaining values.
Can you please show us the CREATE TABLE statement generated by Generate SQL
Script. See my signature for details -- please read the link in full.
Also, please show us *ALL* of the stored procedures. This way, we can try
to reproduce.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql
TransEnd, SessionClose). The SessionStart field is populated on insert
with the default value GETDATE(). After this record is inserted, I
update the other date fields in order as the transactions arrive.
What's happening is that the other date fields (TransStart, TransEnd
and SessionClose) have an earlier date value than the SessionStart
date. Nothing ever touches the SessionStart field; it is populated only
by its default value when the record is inserted. How can this happen?
For further clarification, this only happened to 4 records out of 500K.
It occurred while a backup was running on the server (not of this
database, but of another SQL database). All stored procedures are run
on the same server and are set with GETDATE(), so there's no issue with
clock variances between different servers. The differences in the dates
range from 400 milliseconds to 12 seconds.
Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
this application that matters and I need to understand what's happening
here. Could this be an issue with caching?Hi
I assume you UPDATE them with GETDATE() Function
Have you checked if exists a trigger on the table?
One more question , do you update them from the client or by stored
procedure?
"ccarson" <ccarson@.tpayment.com> wrote in message
news:1111586800.620105.36390@.f14g2000cwb.googlegroups.com...
> I have a table which has several date fields (SessionStart, TransStart,
> TransEnd, SessionClose). The SessionStart field is populated on insert
> with the default value GETDATE(). After this record is inserted, I
> update the other date fields in order as the transactions arrive.
> What's happening is that the other date fields (TransStart, TransEnd
> and SessionClose) have an earlier date value than the SessionStart
> date. Nothing ever touches the SessionStart field; it is populated only
> by its default value when the record is inserted. How can this happen?
> For further clarification, this only happened to 4 records out of 500K.
> It occurred while a backup was running on the server (not of this
> database, but of another SQL database). All stored procedures are run
> on the same server and are set with GETDATE(), so there's no issue with
> clock variances between different servers. The differences in the dates
> range from 400 milliseconds to 12 seconds.
> Any ideas? I know 4 out of 500K doesn't sound like a big deal, but in
> this application that matters and I need to understand what's happening
> here. Could this be an issue with caching?
>|||> I have a table which has several date fields
What is a "date field"? Do you mean a SMALLDATETIME column, or a DATETIME
column? Are they all either one or the other, or is there a mix?
SMALLDATETIME rounds to the nearest minute, so could easily explain away any
discrepancies between other columns with finer granularity.
Can you provide a repro that exhibits this behavior? This includes table
structure, sample data, stored procedure(s) and the calls to those procs
that exhibit the behavior.
Neither the OS nor SQL Server will go back in time, except for daylight
savings time. My guess is that either your columns are a mixture of
SMALLDATETIME and DATETIME, or your stored procedure accepts date/time
values from your application.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||All fields are datetime fields. They are updated by stored procedures
(other than StartSession which is updated by the default value of
GETDATE on insert) using GETDATE on the sql server. The dates are not
passed from the client.
There are no triggers on the table.
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Can you post the sp code?
AMB
"Cassandra Carson" wrote:
> All fields are datetime fields. They are updated by stored procedures
> (other than StartSession which is updated by the default value of
> GETDATE on insert) using GETDATE on the sql server. The dates are not
> passed from the client.
> There are no triggers on the table.
> Thanks for responding.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>|||All fields are datetime fields. The values are updated using stored
procedures that use the GETDATE function. I don't have any sample info
available right now, but I'll try to get something together and reply.
There's a table with a couple of datetime fields. There's a stored
procedure that inserts a new record into the table. When that happens,
the SessionStart datetime field value is set using the default value
GETDATE. It is not implicitly set by the stored procedure. There's an
identity field SessionID that is returned to identify this record for
later use. Then later, using this SessionID, a stored procedure is
called that updates a second datetime field using GETDATE. The second
datetime field has a value earlier than the datetime field that was
populated when the record was inserted. This doesn't make sense to me. I
have searched all stored procedures. Nothing ever sets the SessionStart
value. It is only populated during an insert using the default value
GETDATE. All datetime fields are populated using GETDATE, so I'm
confused how any of them could be earlier than the datetime field that
was populated on insert.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Here's the first procedure that inserts the new record into the table.
CREATE PROCEDURE StartSession @.nField1 int = 0,
@.nField2 int = 0,
@.nField3 int = 0,
@.nField4 int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nSessionID int
BEGIN TRANSACTION
INSERT INTO dbo.tblTracking( fldField1,
fldField2,
fldField3,
fldField4)
VALUES ( @.nField1,
@.nField2,
@.nField3,
@.nField4 )
COMMIT TRANSACTION
SELECT @.nSessionID = SCOPE_IDENTITY()
--- return
recordset with values from new session
---DECLARE
@.nRowCount int
SELECT fldSessionID,
fldSessionStart,
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
Here's the second procedure that updates the other date field.
CREATE PROCEDURE StartTransaction @.nSessionID int
AS
BEGIN
SET NOCOUNT ON
UPDATE dbo.tblTracking SET fldTransStart=GETDATE() WHERE fldSessionID =
@.nSessionID
SELECT fldSessionID,
fldSessionStart,
fldTransStart
fldField1,
fldField2,
fldField3,
fldField4
FROM dbo.tblTracking WITH(READUNCOMMITTED)
WHERE fldSessionID = @.nSessionID
END
The table has the following fields
fldSessionID identity
fldSessionStart datetime (default=GETDATE)
fldTransStart datetime
fldTransEnd datetime
fldSessionClose datetime
fldField1 int (default 0)
fldField2 int (default 0)
fldField3 int (default 0)
fldField4 int (default 0)
Thanks
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||<snip>
Unfortunately, that does not show anyone how the other datetime columns are
populated - which is the likely source of the problem.
You should be able to easily find the error by creating a trigger on the
table that enforced the rules that you are expecting.|||The other 2 datetime colums are identical to the StartTransaction stored
procedure except the names are EndTransaction and CloseSession and the
fields they update are fldTransEnd and fldSessionClose. Otherwise,
identical. The 3 datetime values that are implicitly set by these stored
procedures are all correct in that they increment with time. It's just
the original datetime value that's not implicitly populated, but rather
gets it's value from the default during insert that is incorrect. It is
later than any of the remaining values.
I'm not sure what you mean with the trigger. Do you mean that I could
check to see if the SessionStart is later than the current datetime
before I update the record? and still, that might tell me that it's
happening, which is what started this inquiry, but what else will that
tell me?
Thanks for responding.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||> the original datetime value that's not implicitly populated, but rather
> gets it's value from the default during insert that is incorrect. It is
> later than any of the remaining values.
Can you please show us the CREATE TABLE statement generated by Generate SQL
Script. See my signature for details -- please read the link in full.
Also, please show us *ALL* of the stored procedures. This way, we can try
to reproduce.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.sql
GetDate
I am using GetDate() as the default value on selected fields to record the
date/time that a record is inserted into my tables, however, is it possible
to use a similar procedure to automatically insert the date/time into a
field, but ONLY if the record is subject to an update - thus recording the
date/time a record was last updated.
ThanksKeith
You have to write a TRIGGER FOR UPDATE (For more details please refer to the
BOL) .
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Hi,
Either you have to explicitly update (Overwrite) the date column with an
Update statement or use Update triggers
to obtain this.
update table
set col1 = @.col1 ,col2 = @.col2,
date = getdate()
where ...
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e9ywN5jFEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Keith
> You have to write a TRIGGER FOR UPDATE (For more details please refer to
the
> BOL) .
>
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Keith can you use your client app to do this...if it's an asp app...you can
use a hidden field to update the column....
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||I know I can do this, but as I am in the early stages of this app, I wanted
to try and shift as much as possible to server side to minimise the
client-server traffic and 'hopefully' increase security.
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> Keith can you use your client app to do this...if it's an asp app...you
can
> use a hidden field to update the column....
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message
news:#0KV3omFEHA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||That's barely any client server traffic...plus triggers would eat up
more of your server resources...try both out and run some counters to
baseline some performance...
"Keith" <@..> wrote in message news:u5KuzTlFEHA.3724@.TK2MSFTNGP11.phx.gbl...
> I know I can do this, but as I am in the early stages of this app, I
wanted
> to try and shift as much as possible to server side to minimise the
> client-server traffic and 'hopefully' increase security.
>
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> can
news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> the
a
> the
>|||Yes, and do this in many places. However we removed these for performance re
asons on several tables, and the difference was measurable. Maybe using INST
EAD OF triggers for this would have helped the speed, though we never tested
this.
I don't know the performance implications of the SQL Anywhere solution as we
don't use the product. I just know the feature is available and it's specif
ied as DDL, kind of a default on update as well as insert.
It just sounded like a simple solution to a common requirement.
Mike Kruchten
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:ejHNOxmFEHA.35
40@.TK2MSFTNGP09.phx.gbl...
Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message news:#0KV3omFE
HA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>sql
date/time that a record is inserted into my tables, however, is it possible
to use a similar procedure to automatically insert the date/time into a
field, but ONLY if the record is subject to an update - thus recording the
date/time a record was last updated.
ThanksKeith
You have to write a TRIGGER FOR UPDATE (For more details please refer to the
BOL) .
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Hi,
Either you have to explicitly update (Overwrite) the date column with an
Update statement or use Update triggers
to obtain this.
update table
set col1 = @.col1 ,col2 = @.col2,
date = getdate()
where ...
Thanks
Hari
MCDBA
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e9ywN5jFEHA.2944@.TK2MSFTNGP12.phx.gbl...
> Keith
> You have to write a TRIGGER FOR UPDATE (For more details please refer to
the
> BOL) .
>
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Keith can you use your client app to do this...if it's an asp app...you can
use a hidden field to update the column....
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||I know I can do this, but as I am in the early stages of this app, I wanted
to try and shift as much as possible to server side to minimise the
client-server traffic and 'hopefully' increase security.
"SMAN" <ksanti@.nycap.rr.com> wrote in message
news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> Keith can you use your client app to do this...if it's an asp app...you
can
> use a hidden field to update the column....
> "Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
the
> possible
the
>|||Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message
news:#0KV3omFEHA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>|||That's barely any client server traffic...plus triggers would eat up
more of your server resources...try both out and run some counters to
baseline some performance...
"Keith" <@..> wrote in message news:u5KuzTlFEHA.3724@.TK2MSFTNGP11.phx.gbl...
> I know I can do this, but as I am in the early stages of this app, I
wanted
> to try and shift as much as possible to server side to minimise the
> client-server traffic and 'hopefully' increase security.
>
> "SMAN" <ksanti@.nycap.rr.com> wrote in message
> news:eZw2fKlFEHA.3080@.tk2msftngp13.phx.gbl...
> can
news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> the
a
> the
>|||Yes, and do this in many places. However we removed these for performance re
asons on several tables, and the difference was measurable. Maybe using INST
EAD OF triggers for this would have helped the speed, though we never tested
this.
I don't know the performance implications of the SQL Anywhere solution as we
don't use the product. I just know the feature is available and it's specif
ied as DDL, kind of a default on update as well as insert.
It just sounded like a simple solution to a common requirement.
Mike Kruchten
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message news:ejHNOxmFEHA.35
40@.TK2MSFTNGP09.phx.gbl...
Actually, this functionality has been in place for over a decade in the form
of triggers:
create trigger triu_MyTable on MyTable after insert, update
as
if @.@.ROWCOUNT = 0 return
update MyTable
set
LastUpdateDateTime = getdate ()
where
PK in (select PK from inserted)
go
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike Kruchten" <mkruchten@.fsisolutions.com> wrote in message news:#0KV3omFE
HA.1240@.TK2MSFTNGP10.phx.gbl...
Would be nice, wouldn't it. Sybase SQL Anywhere has this functionality.
Maybe next year Yukon will have it.
Mike Kruchten
"Keith" <@..> wrote in message news:OyxRm0jFEHA.688@.tk2msftngp13.phx.gbl...
> I am using GetDate() as the default value on selected fields to record the
> date/time that a record is inserted into my tables, however, is it
possible
> to use a similar procedure to automatically insert the date/time into a
> field, but ONLY if the record is subject to an update - thus recording the
> date/time a record was last updated.
> Thanks
>sql
Monday, March 12, 2012
Get the system date format?
Hi!
I'm trying to get hold of the systems date format in my code so that I can change a date I've got. Dates are stored with default format in the database. I'm presenting a date in a messagebox (where it becomes text) and I want to change the format to the system format before I put it in the text. The date must be right with different kind of system settings, swedish, uk...
Can I do that?
I'm using:
to_char(date, 'DD/MM/YYYY')
This works, but of course the format will be uk all the time.
I dont want to write the format, because then i does not mater what my system settings are.
Hope i made my self understandable..Maybee i should have written "get the date format from regional settings".
I'm trying to get hold of the systems date format in my code so that I can change a date I've got. Dates are stored with default format in the database. I'm presenting a date in a messagebox (where it becomes text) and I want to change the format to the system format before I put it in the text. The date must be right with different kind of system settings, swedish, uk...
Can I do that?
I'm using:
to_char(date, 'DD/MM/YYYY')
This works, but of course the format will be uk all the time.
I dont want to write the format, because then i does not mater what my system settings are.
Hope i made my self understandable..Maybee i should have written "get the date format from regional settings".
Friday, March 9, 2012
Get the default snapshot folder path
How can i get the unc path to the default snapshot folder using TSQL? I am
using Transactional replication on SQLServer 2005. I have used
sp_helppublication but that only tells me whether my publication uses the
default snapshot folder or not. My publication does use the default snapshot
folder so the alt_snapshot_folder is NULL as expected.
Thanks,
Ian.
use [distribution]
select value from ::fn_listextendedproperty('SnapshotFolder', 'user',
'dbo', 'table', 'UIProperties', null, null)
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dinnebier" <dinnebier@.community.nospam> wrote in message
news:B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com...
> How can i get the unc path to the default snapshot folder using TSQL? I am
> using Transactional replication on SQLServer 2005. I have used
> sp_helppublication but that only tells me whether my publication uses the
> default snapshot folder or not. My publication does use the default
> snapshot
> folder so the alt_snapshot_folder is NULL as expected.
> Thanks,
> Ian.
|||Thanks for your rapid response Hilary. That's great. I would have never
thought of using fn_listextendedproperty. However I have realised that that
would only work if the publisher is also the distributor. Having done some
more research on this I have found the following TQSL produces the result I
require:
DECLARE @.result nvarchar(255)
exec sp_helpdistributor @.directory = @.result OUTPUT
PRINT @.result
This works regardless of whether I run this at the publisher or the
subscriber.
In fact both solutions are fine for my requirements.
Thanks again,
Ian.
"Hilary Cotter" wrote:
> use [distribution]
> select value from ::fn_listextendedproperty('SnapshotFolder', 'user',
> 'dbo', 'table', 'UIProperties', null, null)
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "dinnebier" <dinnebier@.community.nospam> wrote in message
> news:B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com...
>
>
|||Hello,
Thank you for posting here.
I am glad to hear that the resolution for your requirements has been found.
Thank you for investing time in this issue and for sharing the solution.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Get the default snapshot folder path
| thread-index: AcetnLeE/hNy56AyQvapXKihY41Auw==
| X-WBNR-Posting-Host: 207.46.193.207
| From: =?Utf-8?B?ZGlubmViaWVy?= <dinnebier@.community.nospam>
| Subject: Get the default snapshot folder path
| Date: Wed, 13 Jun 2007 02:25:00 -0700
| Lines: 8
| Message-ID: <B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
| Newsgroups: microsoft.public.sqlserver.replication
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.replication:3509
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| How can i get the unc path to the default snapshot folder using TSQL? I
am
| using Transactional replication on SQLServer 2005. I have used
| sp_helppublication but that only tells me whether my publication uses the
| default snapshot folder or not. My publication does use the default
snapshot
| folder so the alt_snapshot_folder is NULL as expected.
|
| Thanks,
| Ian.
|
using Transactional replication on SQLServer 2005. I have used
sp_helppublication but that only tells me whether my publication uses the
default snapshot folder or not. My publication does use the default snapshot
folder so the alt_snapshot_folder is NULL as expected.
Thanks,
Ian.
use [distribution]
select value from ::fn_listextendedproperty('SnapshotFolder', 'user',
'dbo', 'table', 'UIProperties', null, null)
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dinnebier" <dinnebier@.community.nospam> wrote in message
news:B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com...
> How can i get the unc path to the default snapshot folder using TSQL? I am
> using Transactional replication on SQLServer 2005. I have used
> sp_helppublication but that only tells me whether my publication uses the
> default snapshot folder or not. My publication does use the default
> snapshot
> folder so the alt_snapshot_folder is NULL as expected.
> Thanks,
> Ian.
|||Thanks for your rapid response Hilary. That's great. I would have never
thought of using fn_listextendedproperty. However I have realised that that
would only work if the publisher is also the distributor. Having done some
more research on this I have found the following TQSL produces the result I
require:
DECLARE @.result nvarchar(255)
exec sp_helpdistributor @.directory = @.result OUTPUT
PRINT @.result
This works regardless of whether I run this at the publisher or the
subscriber.
In fact both solutions are fine for my requirements.
Thanks again,
Ian.
"Hilary Cotter" wrote:
> use [distribution]
> select value from ::fn_listextendedproperty('SnapshotFolder', 'user',
> 'dbo', 'table', 'UIProperties', null, null)
>
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "dinnebier" <dinnebier@.community.nospam> wrote in message
> news:B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com...
>
>
|||Hello,
Thank you for posting here.
I am glad to hear that the resolution for your requirements has been found.
Thank you for investing time in this issue and for sharing the solution.
Have a nice day!
Best regards,
Adams Qu, MCSE 2000, MCDBA
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Get the default snapshot folder path
| thread-index: AcetnLeE/hNy56AyQvapXKihY41Auw==
| X-WBNR-Posting-Host: 207.46.193.207
| From: =?Utf-8?B?ZGlubmViaWVy?= <dinnebier@.community.nospam>
| Subject: Get the default snapshot folder path
| Date: Wed, 13 Jun 2007 02:25:00 -0700
| Lines: 8
| Message-ID: <B09AC18E-B2E5-4FE7-BAC3-E5A020840E40@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826
| Newsgroups: microsoft.public.sqlserver.replication
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.replication:3509
| NNTP-Posting-Host: tk2msftibfm01.phx.gbl 10.40.244.149
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| How can i get the unc path to the default snapshot folder using TSQL? I
am
| using Transactional replication on SQLServer 2005. I have used
| sp_helppublication but that only tells me whether my publication uses the
| default snapshot folder or not. My publication does use the default
snapshot
| folder so the alt_snapshot_folder is NULL as expected.
|
| Thanks,
| Ian.
|
get the data directory
Hi,
I am searching for how getting the data directory where default mdf files are based.
(C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data)
My goal is to deploy mdf file in this folder during installation.
Thanks
hi,
I'm little in trouble giving advieces as well as registry values are not honored as the seem..
you can query, via a NON documented extended stored procedure the Windows registry for some info..
you can see a HKLM\Microsoft\Microsoft SQL Server key..
SQL Server 2005 registers instances as MSSQL.1, MSSQL.n, and this for every engine type, like Report server, Olap engine, ...
but you can get the MSSQL.x value querying the \Instance Names\SQL key as well
something like
SET NOCOUNT ON;DECLARE @.test varchar(256);
DECLARE @.instance VARCHAR(128);
DECLARE @.regKey VARCHAR(128);
SELECT @.instance = CONVERT(varchar, SERVERPROPERTY('InstanceName'));
IF @.instance IS NULL
SET @.regKey = 'MSSQLServer';
ELSE
SET @.regKey = @.instance;
SELECT @.regKey AS [Instance name];
EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
@.value_name = @.regKey,
@.value = @.test OUTPUT;
SELECT @.test AS [base instance directory];
SET @.regKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @.test + '\Setup';
EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key = @.regKey ,
@.value_name = 'SQLDataRoot',
@.value = @.test OUTPUT;
SELECT @.test AS [SQL Path as per Setup key];
--<-
Instance name
MSSQLServer
base instance directory
MSSQL.1
SQL Path as per Setup key
--
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL
but here problems arise... as usually data folder is in \...\MSSQL.1\MSSQL\Data , but that Data part is not shown anywhere.. .you can "perhaps" going littbe bit further, querying the \Parameters\SQLArg0 , which reports the full path of the master.mdf file formatted as the startup parameter required by SQL Server, thats to say
SQLArg0 = "-dC:\Program Files\..here full path.......\master.mdf"
and parse the result to extract the required "path".. or just hope no one changes that path and hardcode a + '\Data\' addition...
but, again, this whole post is in complete undocumented and unsupported mode..
regards
|||Thanks for your help Andrea
Subscribe to:
Posts (Atom)