Newbie question on physical file last mod date vs. virtual DB/Log

I think I understand the basic features of log files and how to
backup/truncate them to avoid oversized files. Where I need
help is understanding why the O/S rarely puts a new datetime
on the physical files. It appears to only update the log file
time when the log file grows. The DB file may go a month or more
without updating the timestamp. Our backups of the DB grow in
size and appear to be OK. CHECKPOINT commands don't force
the physical disk to update. Is there any other way to do it
(short of drastic measures like detaching the DB)?
Here's my concern: Suppose the server crashes due to a power
failure/UPS failure/whatever. When it restarts it will look at the
log to recover the DB. The physical DB file appears to be a month old
and the log datetime appears to be a day or two old.
If I truncated my log at any time in the last month then it seems like
it will not be able to recover correctly.
Is this really a problem or is it OK?
Environment: SQL Server 2000 on Win2K ServerWhy and when Windows updates the datetime for the file, I don't know. Perhaps somebody in the
windows forum can answer that. However:
> Here's my concern: Suppose the server crashes due to a power
> failure/UPS failure/whatever. When it restarts it will look at the
> log to recover the DB.
Correct. SQL Server know where to find the ldf file, it is stored both in the mdf file as well as in
the master database.
> The physical DB file appears to be a month old
Doesn't matter to SQL Server.
> and the log datetime appears to be a day or two old.
Can you explain what you mean by "log datetime"?
> If I truncated my log at any time in the last month then it seems like
> it will not be able to recover correctly.
What do you mean by "truncated"? Something like BACKUP LOG ... WITH TRUNCATE_ONLY? As long as you
haven't deleted the log file and replaced with an older version, you are fine. SQL server will not
remove log records needed to do recovery of the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Don Anthony" <DonAnthony@.discussions.microsoft.com> wrote in message
news:0817C2B3-1F18-4B50-A4C8-6A54D52D0D87@.microsoft.com...
>I think I understand the basic features of log files and how to
> backup/truncate them to avoid oversized files. Where I need
> help is understanding why the O/S rarely puts a new datetime
> on the physical files. It appears to only update the log file
> time when the log file grows. The DB file may go a month or more
> without updating the timestamp. Our backups of the DB grow in
> size and appear to be OK. CHECKPOINT commands don't force
> the physical disk to update. Is there any other way to do it
> (short of drastic measures like detaching the DB)?
> Here's my concern: Suppose the server crashes due to a power
> failure/UPS failure/whatever. When it restarts it will look at the
> log to recover the DB. The physical DB file appears to be a month old
> and the log datetime appears to be a day or two old.
> If I truncated my log at any time in the last month then it seems like
> it will not be able to recover correctly.
> Is this really a problem or is it OK?
> Environment: SQL Server 2000 on Win2K Server
>|||Re: Can you explain what you mean by "log datetime"?
Answer: The last modification time on the log file (i.e, both
the DB file and the Log file appear to be "old" on the disk).
Re: What do you mean by "truncated"?
Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
Answer: Yes.
If the log file had a recent last modification time (the Windows File)
then it all makes sense. It seems like "magic" because it "looks" like
SQL Server does recovery with an very old DB file and a not-so-recent
Log file. Is it possible the disk files are actually updated by SQL
Server without changing the disk file last modification time?
Thank you for your help.|||> Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
Yes, this is what is happening. Again, check with the Windows people under what conditions the NTFS
file timestamps are changed. I understand that you find this ... interesting, but just don't worry
about the file timestamps.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Don Anthony" <DonAnthony@.discussions.microsoft.com> wrote in message
news:898EB1A4-A02E-456F-B82A-4391608EAD6A@.microsoft.com...
> Re: Can you explain what you mean by "log datetime"?
> Answer: The last modification time on the log file (i.e, both
> the DB file and the Log file appear to be "old" on the disk).
> Re: What do you mean by "truncated"?
> Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
> Answer: Yes.
> If the log file had a recent last modification time (the Windows File)
> then it all makes sense. It seems like "magic" because it "looks" like
> SQL Server does recovery with an very old DB file and a not-so-recent
> Log file. Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
> Thank you for your help.
>|||Don Anthony wrote:
> Re: Can you explain what you mean by "log datetime"?
> Answer: The last modification time on the log file (i.e, both
> the DB file and the Log file appear to be "old" on the disk).
> Re: What do you mean by "truncated"?
> Something like BACKUP LOG ... WITH TRUNCATE_ONLY?
> Answer: Yes.
> If the log file had a recent last modification time (the Windows File)
> then it all makes sense. It seems like "magic" because it "looks" like
> SQL Server does recovery with an very old DB file and a not-so-recent
> Log file. Is it possible the disk files are actually updated by SQL
> Server without changing the disk file last modification time?
> Thank you for your help.
>
I'm not sure, but I'd think that the file timestamp for the logfile and
database file are only updated when the files are actually changed. That
could e.g. be when the file grows or shrinks.
I've just looked at one of our databases, and here the date for the
database file is 26. febr. 2006 and for the logfile it's 3. jan. 2006.
Like Tibors says, SQL server isn't using these file timestamps for
anything, so help yourself and don't worry about them...:-).
Regards
Steen

Newbie Question - Shrinking DB & Physical Database Size

Hi! I'm new to SQL Server 2K and have a very basic question. Will the
"Shrink Database" function reduce the physical size of the database
file (as seen in Windows) after records have been deleted?

The physical database size is currently 1.2 GB but even after deleting
all the records, its still 1.2 GB.

Thanks in advance for any assistance.

Jason"Jaosn S" <jason_sweet@.earthlink.net> wrote in message
news:8976695f.0312151650.5dd48fea@.posting.google.c om...
> Hi! I'm new to SQL Server 2K and have a very basic question. Will the
> "Shrink Database" function reduce the physical size of the database
> file (as seen in Windows) after records have been deleted?

"maybe"

It's not exactly analogous to say Access where there is a "compact" command.

And generally you do not want to keep changing the size of your DB. (it can
cause fragmentation at the disk level).

> The physical database size is currently 1.2 GB but even after deleting
> all the records, its still 1.2 GB.

In this case, you should be able to shrink it.

> Thanks in advance for any assistance.
> Jasonsql

Newbie on permissions: ADO.NET, C++.NET, SQL SERVER 2005 EXPRESS, Visual Studio 2005

This is a very basic question, perhaps more of a Windows XP
Professional OS permissions question than a dB or programming
question: how to create and access SQL SERVER databases from an
account other than "Administrator"; for example, from a "Power User"
account?

As anything other than an "Administrator" user (i.e. as a Power User),
I keep getting (when I try from inside of MS Visual Studio 2005
development environment) the error message: "CREATE DATABASE
permission denied in database 'master'

Using the SQL Server Express Surface Area Configuration tool, I set
the parameters below to "enabled" (they were disabled). This only
helped in one respect: now I can create a database with Visual Studio
2005 (using the Server Explorer tool) when logged in as an
"Administrator". But for security reasons (which I'm not even sure
are valid, but at least in my mind they are) I would like to log in as
a Power User.

My configuration: Windows OS on a standalone Pentium 4 PC connected
to the internet--I'm using Visual Studio 2005 and programming in
C#.NET and C++.NET using ADO.NET. I don't need to access any other PC
in any network--I'm just learning the language at this point.

Any ideas? I did remove some prior versions of SQL Server '7' which
helped remove some other unrelated error messages, and, like I say,
from inside the Administrator account I can program and create
databases using the Server Explorer of Visual Studio 2005, but I'd
like to do so from a non-Admin account.

Also whether I can disable some of the parameters below--i.e., do I
really need the "xp_cmdshell" enabled?

Thanks!

RL

Configuring and Managing SQL Server Express
For improved manageability and security, SQL Server 2005 provides
more control over the SQL Server surface area on your system. To
minimize the surface area, the following default configurations have
been applied to your instance of SQL server:

oTCP/IP connections are disabled [changed to enabled]
oNamed Pipes is disabled [changed to enabled]
oSQL Browser must be started manually
oOPENROWSET and OPENDATASOURCE have been disabled
oCLR integration is disabled [changed to enabled]
oOLE automation is disabled [changed to enabled]
oxp_cmdshell is disabled [changed to enabled]

[This works fine but only from inside "Administrator"--RL]raylopez99 (raylopez99@.yahoo.com) writes:

Quote:

Originally Posted by

This is a very basic question, perhaps more of a Windows XP
Professional OS permissions question than a dB or programming
question: how to create and access SQL SERVER databases from an
account other than "Administrator"; for example, from a "Power User"
account?
>
As anything other than an "Administrator" user (i.e. as a Power User),
I keep getting (when I try from inside of MS Visual Studio 2005
development environment) the error message: "CREATE DATABASE
permission denied in database 'master'

That's indeed an issue of SQL Server permissions.

When you are logged in as an Administrator in Windows and connect to
SQL Server, you account maps to BUILTIN\Administrator which has sysadmin
privilege in SQL Server. That is, you can do anything.

WHen you connect with some other Windows user, no get no such extra
thrills, but you need to grant that login rights to do things. For instance

GRANT CREATE DATABASE TO DOMAIN\PowerUser

You can also add that user a role which has the privileges you want,
for instance to the sysadmin role.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mar 28, 3:45 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

raylopez99 (raylope...@.yahoo.com) writes:

Quote:

Originally Posted by

WHen you connect with some other Windows user, no get no such extra
thrills, but you need to grant that login rights to do things. For instance
>
GRANT CREATE DATABASE TO DOMAIN\PowerUser
>
You can also add that user a role which has the privileges you want,
for instance to the sysadmin role.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>

Erland Sommarskog-- thanks.

At the risk of looking even more stupid, if you know of how to "also
add that user a role which has the privileges you want, for instance
to the sysadmin role" within Visual Studio 2005 and/or Windows XP (for
the program SQL Server 2005 Express Edition), please feel free to let
me know. I just want to add the Power User to have Administrator
access for the Visual Studio 2005, when working on ADO.NET (SQL Server
2005), not for all programs, if possible. For now I will simply
program while logged in as an Administrator, which seems to be a good
workaround to my problem.

I've also ordered some books on ADO.NET and SQL SERVER from O'Reilly
and Microsoft Press; if you have any favorites for a C#/C++ programmer
hobbiest, let me know.

RL|||raylopez99 (raylopez99@.yahoo.com) writes:

Quote:

Originally Posted by

At the risk of looking even more stupid, if you know of how to "also
add that user a role which has the privileges you want, for instance
to the sysadmin role" within Visual Studio 2005 and/or Windows XP (for
the program SQL Server 2005 Express Edition), please feel free to let
me know. I just want to add the Power User to have Administrator
access for the Visual Studio 2005, when working on ADO.NET (SQL Server
2005), not for all programs, if possible. For now I will simply
program while logged in as an Administrator, which seems to be a good
workaround to my problem.

Do I understand this correctly that you want your user to have heavy
perms when connected through VS and your application, but not when it's
connected through Management Studio? There is no way you can assign
permissions per application. Permissions are per logins and users.

Of course, it's a legit requirement that a user should only be able to
access objects in the database through the application, as the application
then can control what the user can see and update. There are a couple of
ways to implement this requirement. The method that has been most tested
and rried is to use stored procedures. There are several ways that
permissions can be granted through stored procedures, whereof the most
useful is ownership chaining. If all stored procedures and tables are
owned by dbo, the users only need execute permissions to the stored
procedures.

There are ways to handle this without stored procedures, but I am less
of fond of these methods.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mar 29, 3:03 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

raylopez99 (raylope...@.yahoo.com) writes:

Quote:

Originally Posted by

>
Do I understand this correctly that you want your user to have heavy
perms when connected through VS and your application, but not when it's
connected through Management Studio? There is no way you can assign
permissions per application. Permissions are per logins and users.
>
Of course, it's a legit requirement that a user should only be able to
access objects in the database through the application, as the application
then can control what the user can see and update. There are a couple of
ways to implement this requirement. The method that has been most tested
and rried is to use stored procedures. There are several ways that
permissions can be granted through stored procedures, whereof the most
useful is ownership chaining. If all stored procedures and tables are
owned by dbo, the users only need execute permissions to the stored
procedures.
>
There are ways to handle this without stored procedures, but I am less
of fond of these methods.
>

Thank you Erland. I see the problem is not as simple as I thought. I
also see I have two problems: one is what you addressed, the other is
more simple: how to use VS2005 from an account other than
"Administrator" when working on databases. So far I've not been able
to figure out this, and only use "Adminstrator" to code. This simple
question can be answered by an experienced user of VS2005, and is to
an extent a trivial question since I can do programming in VS as
"Administrator" (it's annoying to switch users using Windows XP Pro,
but it's only an annoyance, nothing more).

THanks for your help,

RL|||raylopez99 (raylopez99@.yahoo.com) writes:

Quote:

Originally Posted by

Thank you Erland. I see the problem is not as simple as I thought. I
also see I have two problems: one is what you addressed, the other is
more simple: how to use VS2005 from an account other than
"Administrator" when working on databases. So far I've not been able
to figure out this, and only use "Adminstrator" to code.

I did not answer that question, since I was uncertain of the scope of
your question. But it's fairly simple, although there are several options.

One is to enable SQL Server Authentication through Management Studio.
(Right-click the server itself in the Object Explorer, select Properties
and go the the Security tab. You need to restart SQL Server for the
setting to take effect.) Then you can connect as sa from VS and have
sysadmin rights. The good thing with this is that when you connect
through your application with Windows authentication, you are a plain
user and can test that you have granted that user the right permissions.

The other option is to add your Windows user to the sysadmin role:

sp_addsrvrolemember 'sysadmin', 'MACHINE\User'

(If command fails, try swapping the parameters; I may not remember the
order correctly.)
You would first have to grant MACHINE\User access to the SQL Server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mar 30, 2:29 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

raylopez99 (raylope...@.yahoo.com) writes:

Quote:

Originally Posted by

Thank you Erland. I see the problem is not as simple as I thought. I
also see I have two problems: one is what you addressed, the other is
more simple: how to use VS2005 from an account other than
"Administrator" when working on databases. So far I've not been able
to figure out this, and only use "Adminstrator" to code.

>
I did not answer that question, since I was uncertain of the scope of
your question. But it's fairly simple, although there are several options.
>
One is to enable SQL Server Authentication through Management Studio.
(Right-click the server itself in the Object Explorer, select Properties
and go the the Security tab. You need to restart SQL Server for the
setting to take effect.) Then you can connect as sa from VS and have
sysadmin rights. The good thing with this is that when you connect
through your application with Windows authentication, you are a plain
user and can test that you have granted that user the right permissions.
>

Well, turns out I don't have "Management Studio" on my system (yet
strangely I was able to create a simple SQL database and run some
commands). If you don't have "Management Studio" you don't have
"Object Explorer", even though it's possible to have SQL Server 2005
Express (a 36.5 MB file) and not the SQL Server Management Studio
Express (a 46.1 MB file) installed on your PC, as I have. Details
here: http://go.microsoft.com/fwlink/?LinkId=65110
I'll post again if I'm successful, for anybody reading this thread in
the future.

RL

Information on OE below...

Using Object Explorer

Object Explorer, a component of SQL Server Management Studio, connects
to Database Engine instances, Analysis Services, Integration Services,
Reporting Services, and SQL Server Compact Edition. It provides a view
of all the objects in the server and presents a user interface to
manage them. The capabilities of Object Explorer vary slightly
depending on the type of server, but generally include the development
features for databases, and management features for all server types.

Viewing Object Explorer

Object Explorer is visible in the Management Studio by default. If you
cannot see Object Explorer, on the View menu, click Object Explorer.

Connecting Object Explorer to a Server

To use Object Explorer you must first connect to a server. Click
Connect on the Object Explorer toolbar and choose the type of server
from the drop-down list. The Connect to Server dialog box opens. To
connect, you must provide at least the name of the server and the
correct authentication information.|||raylopez99 (raylopez99@.yahoo.com) writes:

Quote:

Originally Posted by

Well, turns out I don't have "Management Studio" on my system (yet
strangely I was able to create a simple SQL database and run some
commands). If you don't have "Management Studio" you don't have
"Object Explorer", even though it's possible to have SQL Server 2005
Express (a 36.5 MB file) and not the SQL Server Management Studio
Express (a 46.1 MB file) installed on your PC, as I have. Details
here: http://go.microsoft.com/fwlink/?LinkId=65110

I would definitely recommend that you download and install SQL Server
Management Studio Express. In the long run it will be difficult to be
without it. Particularly if you ask questions in newsgroups, because most
people answering questions will assume that you have Management Studio in
some form. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mar 31, 2:42 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

>
I would definitely recommend that you download and install SQL Server
Management Studio Express. In the long run it will be difficult to be
without it. Particularly if you ask questions in newsgroups, because most
people answering questions will assume that you have Management Studio in
some form. :-)
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

Hi Erland--it worked! Thank you very much, now I can code as a non-
Administrator with no problem...except one: http://tinyurl.com/38ssp8
(a sort of bug in VS2005 doing SQL it seems)

However, at least I did solve this problem and I appreciate your
advice.

Cheers,

Ray|||raylopez99 (raylopez99@.yahoo.com) writes:

Quote:

Originally Posted by

Hi Erland--it worked! Thank you very much, now I can code as a non-
Administrator with no problem...except one: http://tinyurl.com/38ssp8
(a sort of bug in VS2005 doing SQL it seems)

As I understand that link, it's not a bug at all. If you want to create
a procedure, you use CREATE PROCEDURE. If you want to change an existing
procedure, you use ALTER PROCEDURE. Alternatively, you drop the existing
procedure first, but then you would have to reapply permissions. Visual
Studio helps you out by changing CREATE to ALTER for you.

Also, one thing to keep in mind is that you enter things into the database,
that is not a Save operation, although unfortunately some tools use that
terminology. As with all other programming code, you save your code to disk
and then put it under version control. Regard what's in the database as
binaries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Newbie help

OK, this is probably very basic, but to me it is all new.

I have three tables...tbl_photos, tbl_customers, tbl_register

I want to Insert INTO the cust_id field of tbl_register the cust_id field from tbl_customers where the email field from tbl_customers = str_email (from a form) and I also want to Insert into the photo_id field of tbl_register the photo_id field from tbl_photos where the code1 field from tbl_photos = str_code1 (from a form)

What would be the correct syntax for this?

I hope that description makes sence.You cant do this using single query. Use 2 steps:
a. Get email from tbl_customers table. Since you are using values from user input in query, you have to preconstruct sql query, like:

str='select cust_id
from tbl_customers
where email=' & value_from_field

row=dbobject.execute (str)

b. Insert value from step a into tbl_register

Newbie Basic Question - DISTINCT & PKs

Could somebody please clarify this simple question for me? I'm clearly missing something basic...

I'm in the process of trying to teach myself T-SQL out of a WROX book, and am having a problem wrapping my head around this example, I understand the concept of Primary Keys through using MS Access, but haven't used TSQL extensively (or at all for that matter...)

Using the classic "Northwind" database, using this query:

SELECT COUNT(OrderID)
FROM [Order Details]

This returns 2155, which is the total number of rows with an OrderID, the primary key. Now, as I understood it, a PK has to be unique, but yet this query on the same DB:

SELECT COUNT(DISTINCT OrderID)
FROM [Order Details]

returns 830 rows. If the PK (OrderID) is unqiue, how is the DISTINCT function returning fewer rows? Wouldn't that imply there are duplicate PKs?

I'm sure there's a simple answer to this, but I just can't seem to get my head around it... Thank you so much for taking the time to explain this to a rookie!Because orderId isn't the primary key of [order details]. The PK of that table is OrderId, ProductId. Check sp_help '[Order Details] ' for the details. OrderId is a Foreign Key reference to Orders, where the PK is orderId.|||

Aaaah.... I see, I think I mis-interpreted the information I was getting from Management Studio. Thank you so much for taking the time to correct a silly mistake!

Newbie : Wharehousing Basic Question under SQL Server

Hi,
Currently, I'm working on a SQL Server DWH.
I work on a snowflake Schema. Let say that I've on product table wich is
linked using a FK on a product grouping key to a product group label.
If I update my product refrential, this works but ...
in the real life, if a new product appears and if this grouping code doesn't
existing my loading fails.
Do you have any idea/tricks in order to load the into the product
referential adding dynamicly a new key into the grouping referential
(setting product group to a defalut value)
thk's in advance,
Renaud Harduin
Paris - France
<Renaud Harduin> wrote in message
news:4085820c$0$24993$afc38c87@.news.easynet.fr...
> Do you have any idea/tricks in order to load the into the product
> referential adding dynamicly a new key into the grouping referential
> (setting product group to a defalut value)
Default is the only way to solve this issue (other than properly
determining a product group at import time, which would be preferable).
Create an "Unknown" default group and set that as the default on your group
FK column.
|||Thk's
Renaud Harduin
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OYhbvx%23JEHA.2704@.TK2MSFTNGP10.phx.gbl...
> <Renaud Harduin> wrote in message
> news:4085820c$0$24993$afc38c87@.news.easynet.fr...
> Default is the only way to solve this issue (other than properly
> determining a product group at import time, which would be preferable).
> Create an "Unknown" default group and set that as the default on your
group
> FK column.
>

Newbie : Wharehousing Basic Question under SQL Server

Hi,
Currently, I'm working on a SQL Server DWH.
I work on a snowflake Schema. Let say that I've on product table wich is
linked using a FK on a product grouping key to a product group label.
If I update my product refrential, this works but ...
in the real life, if a new product appears and if this grouping code doesn't
existing my loading fails.
Do you have any idea/tricks in order to load the into the product
referential adding dynamicly a new key into the grouping referential
(setting product group to a defalut value)
thk's in advance,
Renaud Harduin
Paris - France<Renaud Harduin> wrote in message
news:4085820c$0$24993$afc38c87@.news.easynet.fr...
> Do you have any idea/tricks in order to load the into the product
> referential adding dynamicly a new key into the grouping referential
> (setting product group to a defalut value)
Default is the only way to solve this issue (other than properly
determining a product group at import time, which would be preferable).
Create an "Unknown" default group and set that as the default on your group
FK column.|||Thk's
Renaud Harduin
"Adam Machanic" <amachanic@.air-worldwide.nospamallowed.com> a crit dans le
message de news:OYhbvx%23JEHA.2704@.TK2MSFTNGP10.phx.gbl...
> <Renaud Harduin> wrote in message
> news:4085820c$0$24993$afc38c87@.news.easynet.fr...
> Default is the only way to solve this issue (other than properly
> determining a product group at import time, which would be preferable).
> Create an "Unknown" default group and set that as the default on your
group
> FK column.
>

NewBee Trigger Question

I want to create a basic insert trigger. In T1 I add a row, which creates
a
new ID. The trigger fires after the insert, how do I get the new ID to add
to the child table?
I know this is as simple as it gets but I've read about 10 posts and dont'
see it?
Thanks in advance.
Greg P.CREATE TRIGGER YourTriggerName
ON T1
FOR INSERT
AS
DECLARE @.newid int
IF @.@.ROWCOUNT=1
BEGIN
SET @.newid=(Select col1 FROM inserted)
--Do what you want do to with the @.newid here
END
Nathan H. Omukwenyi
"Greg P" <gsp@.newsgroups.nospam> wrote in message
news:872807BA-17FA-47A7-AAE1-AAF397488904@.microsoft.com...
>I want to create a basic insert trigger. In T1 I add a row, which creates
>a
> new ID. The trigger fires after the insert, how do I get the new ID to
> add
> to the child table?
> I know this is as simple as it gets but I've read about 10 posts and dont'
> see it?
> Thanks in advance.
> Greg P.|||try this...
create trigger trig1 on T1 after insert
as
begin
insert into [child table] ([new id])
select [new id] from inserted
end
"Greg P" wrote:

> I want to create a basic insert trigger. In T1 I add a row, which create
s a
> new ID. The trigger fires after the insert, how do I get the new ID to ad
d
> to the child table?
> I know this is as simple as it gets but I've read about 10 posts and dont'
> see it?
> Thanks in advance.
> Greg P.|||I read up on how to use the inserted table and this seems to answer the
question I posted, yet i have a bit of a different use than what was posted.
I need to look up values in two other tables before I can do my insert. I a
m
doing this with cursors. From what I am understanding I can't Declare
anything in a trigger, so to use the cursors I am calling a stored procedure
.
Inside this stored procedure is where I need to access the data in the
inserted table. Should I create a temp table and somehow copy the info from
the Inserted table into there?
FYI: I want to insert initail values for a 3 unique Id's into a 4th table.
So the inserted table contain the first ID's and I open cursors to store the
other 2 sets of ids. Then I am nesting the three cursors to insert a row fo
r
each of the three ID combinations.
IDCol1 IDCol2 IDCol 3 Tbl4Col
1 1 1 0
1 1 2 0
1 2 1 0
2 1 1 0
2 1 2 0
2 2 1 0
ect...
I hope that all makes sense. From what I know I can't do this in a trigger,
maye I can?
Thanks,
Greg
"Nathan H. Omukwenyi" wrote:

> CREATE TRIGGER YourTriggerName
> ON T1
> FOR INSERT
> AS
> DECLARE @.newid int
> IF @.@.ROWCOUNT=1
> BEGIN
> SET @.newid=(Select col1 FROM inserted)
> --Do what you want do to with the @.newid here
> END
>
> Nathan H. Omukwenyi
> "Greg P" <gsp@.newsgroups.nospam> wrote in message
> news:872807BA-17FA-47A7-AAE1-AAF397488904@.microsoft.com...
>
>|||Omni,
Any ideas on my new post?
Thanks,
Greg p
"Omnibuzz" wrote:
> try this...
> create trigger trig1 on T1 after insert
> as
> begin
> insert into [child table] ([new id])
> select [new id] from inserted
> end
> --
>
>
> "Greg P" wrote:
>|||>> want to create a basic insert trigger. In T1 I add a row, which creates a new ID
[sic]. The trigger fires after the insert, how do I get the new ID to add to the chil
d [sic] table? <<
Stop using SQL and go back to a network database. You have described
how they work as they build pointer chains as the data is inserted. I
am not kidding -- read a DB history book. You even used the term
"child" instead of "referenced" table!! Pure network/pointer chain
database concepts and terms, not anything like RDBMS.
Perhaps you should have read one book on RDBMS instead?
You do not create a relational key. It already exists in the real
world and you discover it.
Triggers are a kludge for putting procedural code into a declarative
language.
You need to start over; you do not know what you are doing. People
here will give you kludges to get rid of you quickly because we cannot
give you a 1-2 year course in RDBMS. Telling someone to "smash rats
with a rock when they get near your baby" is easier than "improve the
sewer system by learning civil engineering so rats are not a problem"
Look up this article: http://www.apa.org/journals/psp/psp7761121.html
Journal of Personality and Social Psychology
Unskilled and Unaware of It: How Difficulties in Recognizing One's Own
Incompetence Lead to Inflated Self-Assessments
Remember it takes SIX years to become a Union Journeyman Carpenter in
New York State. How many years to be an SQL programmer? A few ws
in a ceritificate training class!|||On Tue, 9 May 2006 13:10:02 -0700, Greg P wrote:

>I read up on how to use the inserted table and this seems to answer the
>question I posted, yet i have a bit of a different use than what was posted
.
>I need to look up values in two other tables before I can do my insert. I
am
>doing this with cursors. From what I am understanding I can't Declare
>anything in a trigger, so to use the cursors I am calling a stored procedure.[/colo
r]
Hi Greg,
First misunderstanding: you CAN declare anything in a trigger. Whoever
told you otherwise obviously has little experience and even less
knowledge of SQL Server.
Second misunderstanding: Never ever use a cursor (*). And especially not
inside a trigger. Unless you want to ruin your performance and your
scalability, of course.
(*) Okay, there are SOME situations where a cursor is the best choice,
but they are very rare - only experienced DB programmers should be
allowed to use cursors, because it takes a lot of experience to
recognize a situation that might benefit from a cursor.
>Inside this stored procedure is where I need to access the data in the
>inserted table. Should I create a temp table and somehow copy the info fro
m
>the Inserted table into there?
If you MUST use the values from the inserted table in a stored
procedure, then yes, you must copy the data from inserted to some other
(preferably temporary) table.
But I don't think that this is the correct solution in your case.

>FYI: I want to insert initail values for a 3 unique Id's into a 4th table.
>So the inserted table contain the first ID's and I open cursors to store th
e
>other 2 sets of ids. Then I am nesting the three cursors to insert a row f
or
>each of the three ID combinations.
>IDCol1 IDCol2 IDCol 3 Tbl4Col
> 1 1 1 0
> 1 1 2 0
> 1 2 1 0
> 2 1 1 0
> 2 1 2 0
> 2 2 1 0
>ect...
>I hope that all makes sense.
To be blunt - not at all.
Please post the structure of all relevant tables, as CREATE TABLE
statements. Don't forget to include all constraints, properties and
indexes. Then, post some illustrative sample rows of data (as INSERT
statements), one or two sample INSERT statements that should fire the
trigger and the end results you need to have in your table after the
trigger has finished execution. With that information, we can probably
help you write this trigger without cursors or temp tables.
Hugo Kornelis, SQL Server MVP|||Hugo,
Thanks for the response. What I'm looking do is actually quite easily
explain. I'll use 4 tables and three relationsip, Widgets (WidegetsID PK),
Colors(ColorsIDPK), Sizes(SizesID PK) and WidgetsUsed (WidgetsUsedID,
WidgetsID FK, ColorsID Fk, )
I do an insert of multiple widgets creating multiple rows which are stored
in the "Insert" Table of the trigger. When a new Widget is inserted I need
to initialize the WidgetsUsed table. This means inserting a new record for
each size and color possibility and setting . (This table will need a row fo
r
each color and size that widget can come in)
If 2 Widgets were added A and B, and the colors are stored in the colors
table, and the sizes are stored in the sizes table. For this example lets
say there are three color in the color table and two sizes in the color
table. So for each Widget inserted into the widget table I want to add 6
records into the Widgets used table. Finally I'll call the field in the
table I'm updating MyDataField.
I know the idea of using curosr is bad now and they they are very
inefficient, but what I was thinking was I would use the cursors like old
adodb recordsets and loop through each one like the procedure below. I thin
k
this is a pretty thourough description of what I'm doing. Thanks for your
effort.
CREATE TABLE [dbo].[tblWidgetsUsed](
[WidgetUsedID] [uniqueidentifier] NULL,
[WidgetID] [uniqueidentifier] NULL,
[ColorID] [uniqueidentifier] NULL,
[SizeID] [uniqueidentifier] NULL,
[WidgetsOrdered] [numeric](18, 0) NULL
) ON [PRIMARY]
CREATE PROCEDURE dbo.spTrigUtilInsert
-- Add the parameters for the stored procedure here
@.WidgetID as int = 0,
@.ColorId as int = 0,
@.SizeID as int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare curWidget Cursor for
SELECT WidgetID FROM tblTemp --created from Insert in the trigger
open curWidget
fetch next from curWidget into @.WidgetID
--for each newly insertered record
while (@.@.Fetch_Status <> -1)
Declare curColor Cursor for SELECT ColorID FROM dbo.tblColor
open curColor
Fetch next from curColor into @.ColorID
-- For each Color
while (@.@.Fetch_Status <> -1)
-- Each Size
Declare curSize Cursor for SELECT DISTINCT SizeID FROM
dbo.tblSize
open curSize
fetch next from curSize into @.SizeID
While (@.@.Fetch_Status<>-1)
insert into tblWidgetsOrdered("WidgetID", "ColorID", "SizeID",
"NumOrdered")
Values (@.WidgetId, @.ColorID, @.SizeID, 0)
END
GO
Obviously these are not my real tables, because of security issues with my
project I can't use real tables but I think this show you what I'm looking t
o
do. I am using VS2005 Windows form (which is why I discuussed child and
parent tables, because I need to handle the insert order myself... I will
have a reply for the extreemly rude gent that is all high and mighty...)
Let me know what you think the way to do this is. I'm upgrading an access
based app to SQL Server 2005 and would like to take advantage of triggers to
initalize these rows. In the old applicaiton recordsets did the work.
Thanks again Hugo.
Greg P.
"Hugo Kornelis" wrote:

> On Tue, 9 May 2006 13:10:02 -0700, Greg P wrote:
>
> Hi Greg,
> First misunderstanding: you CAN declare anything in a trigger. Whoever
> told you otherwise obviously has little experience and even less
> knowledge of SQL Server.
> Second misunderstanding: Never ever use a cursor (*). And especially not
> inside a trigger. Unless you want to ruin your performance and your
> scalability, of course.
> (*) Okay, there are SOME situations where a cursor is the best choice,
> but they are very rare - only experienced DB programmers should be
> allowed to use cursors, because it takes a lot of experience to
> recognize a situation that might benefit from a cursor.
>
> If you MUST use the values from the inserted table in a stored
> procedure, then yes, you must copy the data from inserted to some other
> (preferably temporary) table.
> But I don't think that this is the correct solution in your case.
>
> To be blunt - not at all.
> Please post the structure of all relevant tables, as CREATE TABLE
> statements. Don't forget to include all constraints, properties and
> indexes. Then, post some illustrative sample rows of data (as INSERT
> statements), one or two sample INSERT statements that should fire the
> trigger and the end results you need to have in your table after the
> trigger has finished execution. With that information, we can probably
> help you write this trigger without cursors or temp tables.
> --
> Hugo Kornelis, SQL Server MVP
>|||For anyone else reading this please do not think I would ever speak this way
if it were not for the post this gentlemen made first.
Hey Genius,
Mr F&*%ing high and mighty... did you see the title of the post. I admitted
to being unfamiliar to using Triggers and Cursors and was looking for some
advice from this newsgroup. Your slam of a person who claims to be
unknowledgeable in topic shows absolute insecurity you informed prick. Yes
once again I will say you are more informed more than me about this, that’
s
why I’m asking the questions moron!!! You could explain nicely what issue
s
have yet I would have to charge you $150 an hour to be your psychologist
because I’m sure no one else want to talk to you and it still isn’t enou
gh
money to listen to your useless babble.
FYI, I have a degree in computer science and do understand RDBMS very
clearly. I have designed and implemented many solutions in many different
technologies. Now I’m learning to work with a new one, SQL Server 2005.
I know that when you update tables in VS2005 you need to handle the
add/mod/deletes yourself through typed datasets. These method must be calle
d
by hand and the terminology used for this process includes Parent, Child, an
d
Grandchild tables. Here is one link to such a reference in the updating
multiple tables section:
http://www.15seconds.com/issue/051123.htm
I also have a WROX’s Visual Basic 2005 Database Programming book in front
of
me, which is the “most advanced” book in this series which even has a di
agram
on page 173 discussing the use of Parent, Child and Grandchild insert, updat
e
and deletes.
So now I have to question what do you really know? It seems to me not much.
You can talk very loud and very rudely… yet not very intelligently. Pleas
e
do not lower the average IQ of my posts again with your “knowledge”.
Regards,
Greg P.
"--CELKO--" wrote:

> Stop using SQL and go back to a network database. You have described
> how they work as they build pointer chains as the data is inserted. I
> am not kidding -- read a DB history book. You even used the term
> "child" instead of "referenced" table!! Pure network/pointer chain
> database concepts and terms, not anything like RDBMS.
>
> Perhaps you should have read one book on RDBMS instead?
> You do not create a relational key. It already exists in the real
> world and you discover it.
> Triggers are a kludge for putting procedural code into a declarative
> language.
> You need to start over; you do not know what you are doing. People
> here will give you kludges to get rid of you quickly because we cannot
> give you a 1-2 year course in RDBMS. Telling someone to "smash rats
> with a rock when they get near your baby" is easier than "improve the
> sewer system by learning civil engineering so rats are not a problem"
> Look up this article: http://www.apa.org/journals/psp/psp7761121.html
> Journal of Personality and Social Psychology
> Unskilled and Unaware of It: How Difficulties in Recognizing One's Own
> Incompetence Lead to Inflated Self-Assessments
> Remember it takes SIX years to become a Union Journeyman Carpenter in
> New York State. How many years to be an SQL programmer? A few ws
> in a ceritificate training class!
>|||Greg,
I dunno if this is what you want. And sorry for the delayed reply.. And
try to decipher this because I just got up from bed :)
try this...
create trigger trig1 on Widgets after insert
as
begin
insert into WidgetsUsed(WidgetID, ColorID, SizeID, NumOrdered)
select
a.WidegetsID,
b.colorsID,
c.sizesID,
0
from inserted a,
colors B,
sizes c
end
Let me know if this was what you wanted.