Creating an Excel linked server

Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
executed the following:
EXEC sp_addlinkedserver test2,
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'd:\test1.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
Then I try to select: select * from test2...test
and I get the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "test2".
Can anyone tell me what I am doing wrong? Thanks!Hi Ric,
Place the Excel file on a location where the SQL Server service account has
access to. Also, remove the sp_addlinkedsrvlogin statement. Try this just for
testing:
1) Move the Excel file to a location where SQL Server has access to, maybe,
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
2) Run the same sp_addlinkedserver command
3) Run your select statement. By the way, do you have a range named 'test'
in your Excel file?
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Ric" wrote:
> Hello, I am trying to create a linked server to an Excel file (SQL 2005). I
> executed the following:
> EXEC sp_addlinkedserver test2,
> 'Jet 4.0',
> 'Microsoft.Jet.OLEDB.4.0',
> 'd:\test1.xls',
> NULL,
> 'Excel 5.0'
> EXEC sp_addlinkedsrvlogin test2, false, rhofing, null
> Then I try to select: select * from test2...test
> and I get the following error:
> Msg 7399, Level 16, State 1, Line 1
> The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "test2"
> reported an error. The provider did not give any information about the error.
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "test2".
> Can anyone tell me what I am doing wrong? Thanks!
>

Creating an Audit trail on a table using a trigger

Hi,
This is kind of following on from my last couple of posts regarding Identity
columns and so on.
Basically, I want to ensure that for a particular table, every row has a
numeric reference. This reference must be unique and gapless. Ideally it
should order in the sequence of the records being inserted however this
isn't an absolute requirement.
From my (limited) understanding of SQLS, I think I can achieve this with a
FOR INSERT Trigger - in that the trigger is fired every time a row is
inserted and the trigger is the same transaction as the initial insert hence
I avoid any concurrency issues.
However I'm not completely sure how to achieve this. I think that my trigger
should be along the lines of this...
CREATE TRIGGER AssignAuditReference ON tblBooking
FOR INSERT
AS
DECLARE @.Ref int
--Get the highest reference and add one.
SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
--Update the inserted row to have a booking_referecen of the new reference
obtained above.
Update tblBooking
SET Booking_Reference = @.ref
WHERE Booking_ID = INSERTED.Booking_ID
However I'm getting problems with the INSERTED table not being recognised.
I understood that the INSERTED table contained the row that the insert that
started the trigger inserted.
Two questions:
1. Where am I going wrong with my trigger. Have I misunderstood some key
point of using triggers.
2. Is this the right approach to achieve what I am after? Are there any
better approaches...Hi Chris,
You have to mention the Inserted Table in your Update Query
Update tblBooking
SET tblBooking.Booking_Reference = @.Ref
FROM tblBooking
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
Because of the lack between getting the @.Ref-Value and writing it in the
table i would prefer an inline Query and Update
Update tblBooking
SET tblBooking.Booking_Reference = NewJobIdTable.NewJobId
FROM tblBooking,
(
Select ISNULL(MAX(job_id),0)+1 AS NewJobId from tblBooking
) NewJobIdTable
INNER JOIN INSERTED
ON (tblBooking.Booking_ID= INSERTED.Booking_ID)
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Chris Strug" <hotmail@.solace1884.com> schrieb im Newsbeitrag
news:eBcd9GOQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> Hi,
> This is kind of following on from my last couple of posts regarding
> Identity
> columns and so on.
> Basically, I want to ensure that for a particular table, every row has a
> numeric reference. This reference must be unique and gapless. Ideally it
> should order in the sequence of the records being inserted however this
> isn't an absolute requirement.
> From my (limited) understanding of SQLS, I think I can achieve this with a
> FOR INSERT Trigger - in that the trigger is fired every time a row is
> inserted and the trigger is the same transaction as the initial insert
> hence
> I avoid any concurrency issues.
> However I'm not completely sure how to achieve this. I think that my
> trigger
> should be along the lines of this...
> CREATE TRIGGER AssignAuditReference ON tblBooking
> FOR INSERT
> AS
> DECLARE @.Ref int
> --Get the highest reference and add one.
> SELECT @.Ref = isnull(max(job_id),0)+1 from tblBooking
> --Update the inserted row to have a booking_referecen of the new
> reference
> obtained above.
> Update tblBooking
> SET Booking_Reference = @.ref
> WHERE Booking_ID = INSERTED.Booking_ID
> However I'm getting problems with the INSERTED table not being recognised.
> I understood that the INSERTED table contained the row that the insert
> that
> started the trigger inserted.
> Two questions:
> 1. Where am I going wrong with my trigger. Have I misunderstood some key
> point of using triggers.
> 2. Is this the right approach to achieve what I am after? Are there any
> better approaches...
>|||Syntactically, your UPDATE statement is missing the FROM clasue:
UPDATE tblBooking
SET Booking_Reference = @.ref
FROM tblBooking , INSERTED
WHERE Booking_ID = INSERTED.Booking_ID
1) This will FAIL if more than one row is inserted - not a good idea
for maintaining an audit trail. 2) I don't see what advantage this has
over the more concise and reliable solution(s) already discussed in
your earlier threads. For example:
INSERT INTO tblBooking (booking_reference, x, y, z, ...)
SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
FROM tblBooking
IMO an incrementing counter is a poor way to maintain an audit trail
anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
preserve the history of changes to rows? This is easy to do in triggers
or in your data access code and doesn't suffer the inevitable and
serious blocking problems that your approach implies.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1113478283.991829.222620@.g14g2000cwa.googlegroups.com...
> Syntactically, your UPDATE statement is missing the FROM clasue:
> UPDATE tblBooking
> SET Booking_Reference = @.ref
> FROM tblBooking , INSERTED
> WHERE Booking_ID = INSERTED.Booking_ID
> 1) This will FAIL if more than one row is inserted - not a good idea
> for maintaining an audit trail. 2) I don't see what advantage this has
> over the more concise and reliable solution(s) already discussed in
> your earlier threads. For example:
> INSERT INTO tblBooking (booking_reference, x, y, z, ...)
> SELECT COALESCE(MAX(booking_reference),0)+1, 'foo', 'bar', 1234, ...
> FROM tblBooking
> IMO an incrementing counter is a poor way to maintain an audit trail
> anyway. Why not just store the CURRENT_TIMESTAMP on each row and then
> preserve the history of changes to rows? This is easy to do in triggers
> or in your data access code and doesn't suffer the inevitable and
> serious blocking problems that your approach implies.
> --
> David Portas
> SQL Server MVP
> --
>
First of all thanks to both David and Jens for their replies.
Apologies for repeating myself, I just want to make sure that I understand
what I'm doing rather than repeating it parrot fashion into my database.
Regarding the trigger, I was under the impression that the trigger would
occur for every new row, I gather that it in fact applies to every INSERT.
Ahh... That makes things clearer.
Regards the actual implementation (TIMESTAMP vs. numeric reference),
unfortunately this is out of my hands. I've been informed that this a is a
non negotiatable requirement. What can you do?
if I may ask one more question, assuming that I did attempt to implement my
apprioach using triggers, could you expand on the blocking problems that you
would expect me to face?
Anyway, thank you once again for taking the time to help me, I do appreciate
it.
Regards
Chris.

Creating Access tables in SQL

I'm using Coldfusion and trying to create a Access table with a autonumber field (SID).

I'm using the following query but to be honest I haven't got a clue how to set the first field (SID) to Autonumber

<cfquery name="createtable_users" datasource="#attributes.dsn#">
Create Table #tbl.code#_Users
(
SID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)
</cfquery>

The code in my SQL book is designed purely for SQL DBs and is not having any of it.

The error is -

Error Diagnostic Information
ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in CREATE TABLE statement.

SQL = "Create Table test1_Users ( SID INT NOT NULL PRIMARY KEY DEFAULT 1, Firstname VARCHAR(50) NOT NULL, Surname VARCHAR(50) NOT NULL, Address VARCHAR(150) NOT NULL, Town VARCHAR(50) NOT NULL, County VARCHAR(50) NOT NULL, Postcode VARCHAR(50) NOT NULL, email VARCHAR(50), phone VARCHAR(50) NOT NULL, mobile VARCHAR(50) )"

Which is really helpful as you can see.

Thanks in advance for any help.Create Table #tbl.code#_Users
(
SID COUNTER PRIMARY KEY,
Firstname VARCHAR(50) NOT NULL,
Surname VARCHAR(50) NOT NULL,
Address VARCHAR(150) NOT NULL,
Town VARCHAR(50) NOT NULL,
County VARCHAR(50) NOT NULL,
Postcode VARCHAR(50) NOT NULL,
email VARCHAR(50),
phone VARCHAR(50) NOT NULL,
mobile VARCHAR(50)
)|||Thanks for that works a treat.sql

Creating a Unique Index

Hi

I tried the following from the help file...

When you create or modify a unique index, you can set an option to
ignore duplicate keys. If this option is set and you attempt to create
duplicate keys by adding or updating data that affects multiple rows
(with the INSERT or UPDATE statement), the row that causes the
duplicates is not added or, in the case of an update, discarded.

For example, if you try to update "Smith" to "Jones" in a table where
"Jones" already exists, you end up with one "Jones" and no "Smith" in
the resulting table. The original "Smith" row is lost because an
UPDATE statement is actually a DELETE followed by an INSERT. "Smith"
was deleted and the attempt to insert an additional "Jones" failed.
The whole transaction cannot be rolled back because the purpose of
this option is to allow a transaction in spite of the presence of
duplicates.

But when I did it the original "Smith" row was not lost.

I am doing something wrong or is the help file incorrect.

DanThose paragraphs are referring to the IGNORE_DUP_KEYS option which is not
the default when creating an index. Did you specify the IGNORE_DUP_KEYS
option on your CREATE INDEX statement?

Why do you want to ignore duplicate keys in this way? Typically, it would be
better to put the code to ignore duplicates in your INSERT or UPDATE
statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
IGNORE_DUP_KEYS option is a little strange and very non-standard and
non-relational as this article explains.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi

"Did you specify the IGNORE_DUP_KEYS"

Yes I did.

The issue I have is using a update statement with a table that has
IGNORE_DUP_KEYS index as the help file says --

"if you try to update "Smith" to "Jones" in a table where "Jones"
already exists, you end up with one "Jones" and no "Smith" in the
resulting table. The original "Smith" row is lost because an UPDATE
statement is actually a DELETE followed by an INSERT"

But when I try this, the original "Smith" row is not lost...

So am I doing something wrong or is the help file wrong.

Could you give it a try?

Thanks

Dan

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<lI-dnYK39q4-Y1yi4p2dnA@.giganews.com>...
> Those paragraphs are referring to the IGNORE_DUP_KEYS option which is not
> the default when creating an index. Did you specify the IGNORE_DUP_KEYS
> option on your CREATE INDEX statement?
> Why do you want to ignore duplicate keys in this way? Typically, it would be
> better to put the code to ignore duplicates in your INSERT or UPDATE
> statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
> IGNORE_DUP_KEYS option is a little strange and very non-standard and
> non-relational as this article explains.|||You're right. The UPDATE statement described should produce an error
("Cannot insert duplicate key"). The RTM version of Books Online is wrong
and that page has been changed in the latest version:

http://msdn.microsoft.com/library/e...uniqueindex.asp

--
David Portas
----
Please reply only to the newsgroup
--

Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
Try:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux
|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>
|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>
|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegr oups.com...
>

Creating a trigger for the row being updated

Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tuxTry:
update m
set
UpdatedDate = getdate ()
, UpdatedBy = CURRENT_USER
from
MyTable m
join
inserted i on i.ItemCode = m.ItemCode
join
deleted d on d.ItemCode = i.ItemCode
where
i.ShortDescription <> d.ShortDescription
or i.Quantity <> d.Quantity
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
Hello group,
I have a question regarding an update trigger.
For example, I have the following fields in a table..
ItemCode
ShortDescription
Quantity
UpdatedDate
UpdatedBy
I want to create a trigger that updates the "UpdatedDate" and
"UpdatedBy" fields when either the "ShortDescription" or "Quantity"
fields get updated.
I have figured out how to use the "bitwise" operators so as to target
only columns 2 and 3 for chages, however, telling the trigger to only
update the records which has been changed is proving to be a
challenge.
Thanks in advance
Leenux_tux|||leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||The problem with this code is that is does not exactly meet the spec:
'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
fields when either the "ShortDescription" or "Quantity" fields get updated.'
IOW, if both of these columns are set to their original values, the you are
updating the UpdatedDate and UpdatedBy columns, which you don't want to do.
You need to join also on the deleted virtual table and compare the values in
inserted vs. deleted.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
leenux_tux,
Assuming that ItemCode is an unchanging primary key:
UPDATE M
SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
FROM MyTable M
JOIN inserted i
ON M.ItemCode = i.ItemCode
The inserted table contains inserted or updated rows and the deleted table
contains deleted rows. Experience says that this trigger may actually take
more time to run for a single row than the original update ran.
If you are updating the row through a stored procedure (recommended) or
through other controllable code, considering directly supplying the data and
name in the initial update rather than having the trigger do clean up.
RLF
"leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
> Hello group,
> I have a question regarding an update trigger.
> For example, I have the following fields in a table..
> ItemCode
> ShortDescription
> Quantity
> UpdatedDate
> UpdatedBy
> I want to create a trigger that updates the "UpdatedDate" and
> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
> fields get updated.
> I have figured out how to use the "bitwise" operators so as to target
> only columns 2 and 3 for chages, however, telling the trigger to only
> update the records which has been changed is proving to be a
> challenge.
> Thanks in advance
> Leenux_tux
>|||Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>|||That emphasizes the fact that a PK should never change. There are
exceptions, though.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uh5I%23VIqHHA.3892@.TK2MSFTNGP05.phx.gbl...
Tom,
You are correct. My mistake.
Of course, if Item_Code is NOT an unchanging primary key, then things still
get interesting.
RLF
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23qS6J8HqHHA.1216@.TK2MSFTNGP03.phx.gbl...
> The problem with this code is that is does not exactly meet the spec:
> 'I want to create a trigger that updates the "UpdatedDate" and "UpdatedBy"
> fields when either the "ShortDescription" or "Quantity" fields get
> updated.'
> IOW, if both of these columns are set to their original values, the you
> are
> updating the UpdatedDate and UpdatedBy columns, which you don't want to
> do.
> You need to join also on the deleted virtual table and compare the values
> in
> inserted vs. deleted.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uO23zPEqHHA.3948@.TK2MSFTNGP05.phx.gbl...
> leenux_tux,
> Assuming that ItemCode is an unchanging primary key:
> UPDATE M
> SET UpdatedDate = GETDATE(), UpdatedBy = SUSER_SNAME()
> FROM MyTable M
> JOIN inserted i
> ON M.ItemCode = i.ItemCode
> The inserted table contains inserted or updated rows and the deleted table
> contains deleted rows. Experience says that this trigger may actually
> take
> more time to run for a single row than the original update ran.
> If you are updating the row through a stored procedure (recommended) or
> through other controllable code, considering directly supplying the data
> and
> name in the initial update rather than having the trigger do clean up.
> RLF
> "leenux_tux" <nigel.henderson@.connecta.ltd.uk> wrote in message
> news:1181137271.655691.191540@.m36g2000hse.googlegroups.com...
>> Hello group,
>> I have a question regarding an update trigger.
>> For example, I have the following fields in a table..
>> ItemCode
>> ShortDescription
>> Quantity
>> UpdatedDate
>> UpdatedBy
>> I want to create a trigger that updates the "UpdatedDate" and
>> "UpdatedBy" fields when either the "ShortDescription" or "Quantity"
>> fields get updated.
>> I have figured out how to use the "bitwise" operators so as to target
>> only columns 2 and 3 for chages, however, telling the trigger to only
>> update the records which has been changed is proving to be a
>> challenge.
>> Thanks in advance
>> Leenux_tux
>

Creating A Trigger

Hallo,
I am new on this forum but I have an urgent problem. How can I

1. Create a trigger which has the following characteristics:
It should be named reorder.
It should fire after an update of the qty column on the stock table, if the new
value of qty is <= 5.
New should be referenced as n.
The triggered action should insert the values n.itemno and current timestamp
into the reorder table,
whereby when an inventory item in the STOCK table falls below a quantity of 6, the REORDER table will have a row inserted.

Any kind of help is very much appreciated.

Quote:

Originally Posted by JKAG

Hallo,
I am new on this forum but I have an urgent problem. How can I

1. Create a trigger which has the following characteristics:
It should be named reorder.
It should fire after an update of the qty column on the stock table, if the new
value of qty is <= 5.
New should be referenced as n.
The triggered action should insert the values n.itemno and current timestamp
into the reorder table,
whereby when an inventory item in the STOCK table falls below a quantity of 6, the REORDER table will have a row inserted.

Any kind of help is very much appreciated.

hi jkag,

Check out the Trigger i have developed in sqlserver 2000.

sample tables used:
create table stock
(
itemid int identity(1,1) primary key,
prdname varchar(100),
qty int,
)

create table reorder
(
reoid int identity(1,1) primary key,
itemid int,
prdname varchar(100),
qty int,
currdate timestamp
)

create Trigger ReorderTrigg
on stock
for update
as
begin
insert into reorder(itemid,prdname,qty) select itemid,prdname,qty from stock where qty<=5
end
go

Go through the trigger and do let me know if it has solved ur query. If not also, pm me your problem. I shall try it. Gud Luck!

cheers,
jaisql

Creating a table (like a pivottable in Excel)...how?

I have a table in my DB with the following columns: PlayerID, RoundNum, Score.

PlayerID RoundNum Score
1 1 10
1 2 10
1 3 10

Any scoring for my game is going to end up in this table. However, I would like to display the score standings with a player's name at the far left, and with each Round as a column:

R1 R2 R3
Player1 10 10 10

Can any of the SQL gurus tell me if this is possible, and how it can be done? Thanks!

http://forums.asp.net/1194798/ShowPost.aspx|||That is exactly what I was looking for. Thanks for a) giving the inital explanation and b) pointing me to it.

creating a Smart Average

Hello all,

I need to create "Smart Average" calculating sales following way:

Average of the "active months" of the customer.

For instance, if we're in November, we'll take the first month this customer bought (for instance October) and we'll create an average ([sales for October – November]/2)

How do I accomplish this in MDX?

Thanks,

Liran

Liran

You need first to find the first month when customer bought something. Unfortunately FirstNonEmpty semiadditive measure won't help here, since it will return the measure value, not the member on Time dimension itself. So you will have to do it in MDX - something like this:

Avg(NonEmpty(Date.Month.Month.MEMBERS, Measures.Sales).Item(0):Date.Month.CurrentMember, Measures.Sales)

HTH

Mosha (http://www.mosha.com/msolap)

sql

Creating a publication

hi
I was following the walkthrough "Creating a Mobile Application with SQL Server Mobile" and when I got to the point where you create a "local publication" I couldn't find the link "Local Publication" in my Object Explorer.

I read all the help in books online however id did not tell me how to bring that link there.

I did install the replication component using the CD installation. I have SQL Server 2005 Standard Edition and Visual Studio 2005

I also found the help "Using the Publication Wizard to Create a Publication" but did not know where to locate or start the wizard.

any help will be appreciated.

1. Run sqlwb.exe to launch SQL Management Studio.

2. Make a connection to the SQL server you have installed. So it is shown as the root node of the tree view in the Object Explorer.

3. Look for Replication\Local Publications alone the tree view hierarchy.

4. Right click on the "Local Publications" to show the context menu - you can select "New Publication ...".

Thanks.

This posting is provided "AS IS" with no warranties, and confers no rights.

|||hi
thanks for your reply.
the problem is there is no "Local Publication" folder. The only thing is "Local Subscription"

how can I add that folder?
|||

I suspect you can add that folder. In general SQL 2005 Standard SKU should be able to do publication. Please check one more time that you do have Standard SKU not SQL Express SKU. You can get this information as "select @.@.version".

Thanks.

Creating a Param Subscription

When i attempt to create a new e-mail subscription to a report i get
the following error message:
"Subscriptions cannot be created because the credentials used to run
the report are not stored, the report is using user-defined parameter
values, or if a linked report, the link is no longer valid."
I have attempted everything to get this resolved. It works when i try
subscribing to a report that does not use any parameters. The
credentials are "stored securely in the report server" and i have the
default values set for my two parameters.
How do i get around this message in order to create a subscription?Does the report use the User object? If so then you can not subscribe to
it.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"muris" <rmuris@.hotmail.com> wrote in message
news:1114004323.057963.57760@.f14g2000cwb.googlegroups.com...
> When i attempt to create a new e-mail subscription to a report i get
> the following error message:
> "Subscriptions cannot be created because the credentials used to run
> the report are not stored, the report is using user-defined parameter
> values, or if a linked report, the link is no longer valid."
> I have attempted everything to get this resolved. It works when i try
> subscribing to a report that does not use any parameters. The
> credentials are "stored securely in the report server" and i have the
> default values set for my two parameters.
> How do i get around this message in order to create a subscription?
>|||I do not use the User Object.

Creating a new database

I created a database using the following command :

create database krish on (Name='krish', filename='C:\Program Files\Microsoft SQL Server\MSSQL\data\krish.mdf', size=25, maxsize=50,filegrowth=5%)

This actually created the database. Now, I wanted to view this database info being stored inside the SQL Server system tables. I looked at "sysdatabases" table and found an entry as expected for "krish" but I could not trace where the info corresponding to the size of the database was stored ie.25MB . (I looked ad "sysdevices" but couldn't find any entry for the newly created database).

In which table is it stored ?

Any help is appreciated.select size
from krish.dbo.sysfiles|||Thanks Hans. That worked

Creating a model in Report Manager

Can anyone help me?

I've read the instructions on the following page

http://msdn2.microsoft.com/en-us/library/ms159118.aspx

about how to generate a report model from a cube.

I can set up the cube as a data source but when I look at the properties there is no option to "Generate Model".

I am running SQL 2005 DE SP2(CTP).

The option is just not there for me. Any ideas?

Thanks in advance

Are you saying you don't get the 'Apply', 'Move','Generate Model','Delete' buttons at the bottom of the page?|||

Thanks for the reply. I get the 'Apply', 'Move' and 'Delete' buttons but not the 'Generate Model' one.

Very strange?

|||

The following conditions need to be satisfied for the button to be shown:

1) you must be using a shared data source

2) the data source must point to either a SQL server database, or a SQL Server 2005 Analysis Services cube, or if using SQL Server 2005 SP2 you can also use an Oracle data source.

3) you have read properties on the shared data source (you meet this condition it appears)

4) you have create model permission on the folder you're viewing from the data source from. This permission is in the Manage Models task; it is in the default Content Manager role.

Hope that helps,

-Lukasz

|||

Thanks Lukasz.

I'm not too sure what the exact problem was in the end but when I tried it again it worked.

yayomayn

Creating a local SQL Server Compact Edition DB

Hi All ...

I'm setting up replication for the 1st time following the steps in the Books Online to get it up and running:

ms-help://MS.SSCE.v31.EN/ssmmain3/html/5a82aa7a-41a3-4246-a01a-2b1e4b2fdfe9.htm.

Anyhow, I get down to the section labeled - Create a new SQL Server Compact Edition database and am having a problem. I open SQL Server Management Studio as instructed. I click Connect and then am supposed to select SQL Server Compact Edition - yet can't find that option.

And, yes, I've installed Compact Edition on my development laptop. So, how do I get this option so that I can create the local DB for Compact Edition and continue development?

UPDATE:

Ok, duh moment, I have SQL Server Management Studio Express loaded on my laptop, so did not have the option. I've attempted to uninstall SQL Server Management Studio Express and install the full version from the SQL Server CDs. However, when I go to install the full version it tells me that the management tools are already installed and won't let me install anything. So, how do I get around this so that I can install full version of SQL Server Management Studio and then get the option to connect to the SQL Server Compact Edition?

Any help will be appreciated.

UPDATE 2:

Ok, figured it out. You can't just uninstall SQL Server Management Studio express, you must go in to Add/Remove Programs, SQL Server 2005, Change. Then select to uninstall the Workstation components. Then you can go use the SQL Server install discs to install the full blown SQL Server Management Studio along with the other Workstation components.

Thanks ...

David L. Collison

Any day above ground is a good day!

As a script-kiddie wannabe, I will suggest that you can use the following vbscript as well:

<job id="SqlServerCompactEditionDatabaseCreator">
<script language="VBScript">

Option Explicit

Dim strConnectionString
Dim oCatalog

strConnectionString = "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=" & WScript.Arguments(0) & ";"

Set oCatalog = WScript.CreateObject("ADOX.Catalog")
oCatalog.Create strConnectionString

</script>
</job>

Save the script to something like SqlServerCompactEditionDatabaseCreator.wsf, and you can create a SQL Server Compact Edition database with something like cscript SqlServerCompactEditionDatabaseCreator.wsf foo.sdf from the command prompt.

Then again, that is probably just my 2 cents

-Raymond

Creating a Dynamic Temporary Table

When I execute the following Stored Procedure with a parameter (EXEC MyProc
'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
@.MyTempTable that was passed in the SQL statement?
I meant to create the temp table named MyTab as passed to the SP from the
statement.
CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
SELECT *
INTO ##MyTempTable
FROM Customers
GOThe right answer is never pass a table name as a parameter. You need
to understand the basic idea of a data model and what a table means in
implementing a data model. Go back to basics. What is a table? A
model of a set of entities or relationships. EACH TABLE SHOULD BE A
DIFFERENT KIND OF ENTITY. What having a generic procedure works
equally on automobiles, octopi or Britney Spear's discology is saying
that your application is a disaster of design.
1) This is dangerous because some user can insert pretty much whatever
they wish -- consider the string 'Foobar; DELETE FROM Foobar; SELECT *
FROM Floob' in your statement string.
2) It says that you have no idea what you are doing, so you are giving
control of the application to any user, present or future. Remember
the basics of Software Engineering? Modules need weak coupling and
strong cohesion, etc. This is far more fundamental than just SQL; it
has to do with learning to programming at all.
3) If you have tables with the same structure which represent the same
kind of entities, then your schema is not orthogonal. Look up what
Chris Date has to say about this design flaw. Look up the term
attribute splitting.
4) You might have failed to tell the difference between data and
meta-data. The SQL engine has routines for that stuff and applications
do not work at that level, if you want to have any data integrity.
Stop writing code like this. You are mimicking a 1950's scratch tape
file. But more than that, you never used the parameter anywhere in the
procedure -- nothing happened to it. Also, I see you used the
"Magical NVARCHAR(50)" data type. Do you really have names that long?
I doubt it. You will eventually get such a garbage name; I can give
you a Chinese Suttra if you want to do use it :)
Use the Customers table in your statements. Unfortunately, we have no
idea what you wanted to do, so nobody can help you further.|||Shariq,
I have a little confusion as to what you are attempting to achieve.
The short answer to what you asking is as follows:
CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
@.MyTempTable nvarchar(50)=''
AS
If @.MyTempTable<>''
BEGIN
DECLARE @.sSTR varchar(2000)
SET @.sSTR = ' '
SET @.sSTR = @.sSTR + ' SELECT * INTO '
SET @.sSTR = @.sSTR + '#' + @.MyTempTable
SET @.sSTR = @.sSTR + ' FROM Customers '
EXEC (@.sSTR)
END
This will achieve the result your question poses, but does not a lot to
possibly achieve your intended goal.
The one drawback to the above code is that the EXEC(@.sSTR) command runs in
an independant thread from the Stored Procedure and such a table cannot be
accessed from another thread, unless you use the ## prefix, but this, again,
brings up it's own dilemmas as the same Stored Procedure acn only be run onc
e
at a time.
"Shariq" wrote:

> When I execute the following Stored Procedure with a parameter (EXEC MyPro
c
> 'MyTab'); it creates a table ##MyTempTable. What happened to the parameter
> @.MyTempTable that was passed in the SQL statement?
> I meant to create the temp table named MyTab as passed to the SP from the
> statement.
> CREATE PROCEDURE [dbo].[MyProc] (@.MyTempTable nvarchar(50)) AS
> SELECT *
> INTO ##MyTempTable
> FROM Customers
> GO
>|||Tony,
Thanks for you help; the code you provided is exactly what I was looking for
.
"Tony Scott" wrote:
> Shariq,
> I have a little confusion as to what you are attempting to achieve.
> The short answer to what you asking is as follows:
> CREATE STORED PROCEDURE [dbo].[usp_My_SProc]
> @.MyTempTable nvarchar(50)=''
> AS
> If @.MyTempTable<>''
> BEGIN
> DECLARE @.sSTR varchar(2000)
> SET @.sSTR = ' '
> SET @.sSTR = @.sSTR + ' SELECT * INTO '
> SET @.sSTR = @.sSTR + '#' + @.MyTempTable
> SET @.sSTR = @.sSTR + ' FROM Customers '
> EXEC (@.sSTR)
> END
> This will achieve the result your question poses, but does not a lot to
> possibly achieve your intended goal.
> The one drawback to the above code is that the EXEC(@.sSTR) command runs in
> an independant thread from the Stored Procedure and such a table cannot be
> accessed from another thread, unless you use the ## prefix, but this, agai
n,
> brings up it's own dilemmas as the same Stored Procedure acn only be run o
nce
> at a time.
>
> "Shariq" wrote:
>

Creating a dimension tablr

Hi Guys
Fairly new to this, I'm having a problem creating a hierarchial dimensionn
from a table with the following structure:
STATE | ZONE | BRANCH | CONTACT TYPE | CONTACT
--|--|--|--|--
9 records|7 records | 25 records | 6 records |3984 records
I need the structure to be as follows:
STATE
--ZONE
--BRANCH
--CONTACT TYPE
--CONTACT
I can't seem to get this right, I've created the dimension and added the
level above as an attribute to the current level. e.g.:
ZONE
--Attribute STATE
BRANCH
--Attribute ZONE
etc.
Then created the hierarchy as specified above, the dimension processes, but
it doesnt display correclty, all the properties on the dimension are set to
default, anyone care to help me?
I hope the above makes sense.
I forgot to add, you'll see that ZONE has less records than STATE, thats
because the current system is storing NAMES not keys, and a ZONE name can be
in more than on STATE, e.g. STATE:"New York" can have a zone called
ZONE:"AGRICULTURE" and STATE:"California" can have a zone called
ZONE:"AGRICULTURE"
|||Sorry, another thing... the record counts in the first post are DISTINCT
counts...

Creating a dataset

Im trying to complete my function which will allow me to insert data into a table by referencing it in the relevant pages, the following code is what i am using to take the SQL query, execute it and return the resultant dataset in destResponse. However i am getting the following error message; "Compiler Error Message:CS1026: ) expected". And it is coming from the following line;

SqlCommand command =newSqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData +")";

Here is my code below, please feel free to criticise it, and im sure the error above is not the only error i will be getting.

publicstaticvoid SQLExecute(string strTableName,string strData)

{

DataSet dsetResponse =newDataSet();

// create connection object

strConnection =ConfigurationManager.AppSettings["strConnectionString"];

SqlConnection conn =newSqlConnection(strConnection);

SqlCommand command =newSqlCommand("INSERT INTO" + strTableName + strData +") VALUES (" + strData +")";command.Fill(dsetResponse,"table");

conn.Close();

return dsetResponse;

}

("INSERT INTO" + strTableName + strData +" VALUES " + strData);

|||

let me know if it is ok

|||

your line should be like below

SqlCommand command =newSqlCommand("INSERT INTO " + strTableName + strData +") VALUES (" + strData +")");

|||

Missing a space after INTO.

Missing a closing parenthesis before the first semi-colon.

Missing a literal space and opening parenthesis between the strTableName and strData -- should be strTableName + ' (' + strData.

Used strData for both the column name list AND the column values.

SqlCommand command =new SqlCommand("INSERT INTO " + strTableName +" (" + strColumnList +") VALUES (" + strData +")");

|||

Hi thanks for responding, the one that worked was Motleys suggestion, however my next task is to return the results in a dataset; this is my code so far, am i on the right track?

publicstaticvoid SQLExecute(string strTableName,string strData)

{

String strConnection =null;

try

{

DataSet dsetResponse =newDataSet();

// create connection object

strConnection =ConfigurationManager.AppSettings["strConnectionString"];

SqlConnection conn =newSqlConnection(strConnection);

SqlCommand command =newSqlCommand("INSERT INTO " + strTableName +" (" + strData +") VALUES (" + strData +")");command.Fill(dsetResponse,"table");

conn.Close();

return dsetResponse;

}

}

Creating a database on a network path

When I try to create a new database on a network path, I get the following error:

"\\server\test\testdatabase.mdf" is on a network path that is not supported for database files.

Am I trying something that isn't possible? Should databases always be stored on the server PC running SQL (Express)?
I'm using Visual Basic Express and SQL Express (installed on a local PC not the server, so the connectionstring is ".\SQLEXPRESS"), TCP/IP is enabled.

Regards, EBAMore about storage in network path in SQL Server can be found here:

http://support.microsoft.com/kb/304261/en-us

Generally spoken, this is not supported.

HTH, Jens Suessmeyer.

http://www.slqserver2005.de|||Jens, thanks very much for your help. It would have taken me quite some time to find this answer. I guess I'll abandon this idea.

Regards, EBA|||

WOW, I am impressed!

Creating a database

When creating a database using OSQL -E -I CreateDatabase.sql
I have the following in my input file:
CREATE TABLE admin
(
ID char(7),
Pwd varchar(32)
)
GO
How could I make it so that Pwd does not allow nulls and has a default value
of 'Password'?
hi Terry,
Terry Olsen wrote:
> When creating a database using OSQL -E -I CreateDatabase.sql
> I have the following in my input file:
> CREATE TABLE admin
> (
> ID char(7),
> Pwd varchar(32)
> )
> GO
> How could I make it so that Pwd does not allow nulls and has a
> default value of 'Password'?
SET NOCOUNT ON
USE tempdb
CREATE TABLE dbo.Admin (
ID varchar(7) NOT NULL ,
Pwd varchar(32) NOT NULL DEFAULT 'Password'
)
INSERT INTO dbo.Admin VALUES ('Andrea' , DEFAULT )
SELECT * FROM dbo.Admin
DROP TABLE dbo.Admin
--<--
ID Pwd
-- --
Andrea Password
please have a look at
http://msdn.microsoft.com/library/de...eate2_8g9x.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Creating a cursor with a dynamic database name.

You will either know this or you won't. I want to do thisthe following two lines of TSQL in one dynamically but none of the Declare Cursor statements work (when I try to pass in the Database name using a parameter). How do i dynamicically create a cursor to a table using a dynamic database/catalog name?

DECLARE curTest1 CURSOR SELECT * FROM testDB1.dbo.MyTable
DECLARE curTest2 CURSOR SELECT * FROM testDB2.dbo.MyTable

I've tried the following

DECLARE @.CatalogName NVARCHAR(5)
DECLARE @.sqlStr NVARCHAR (4000)

SET @.CatalogName = 'TestDB'
SET @.sqlStr = 'SELECT * FROM ' + @.CatalogName + 'dbo.Mytable;'

DECLARE curTest CURSOR FOR SELECT * FROM @.CatalogName.dbo.MyTable -- Which obviously should not and does not work.
DECLARE curTest CURSOR FOR @.sqlSTR -- Which I thought would work but also does not work.

CLOSE curTest
DEALLOCATE curTest

My environment is SQL Server 2000 environment SP3the answer you are looking for is sp_executesql....

you can't use a parameter value in the way you are trying...

you would need to dynamicly create and execute your sql statement using sp_executesql.

there have been a few posts in the last few days that explain this to the nth degree.|||You can't declare with dynamic sql...

DECLARE @.declare varchar(2000)

SET @.declare = 'DECLARE @.x int'

sp_executesql(@.declare)

And why do you want to use a cursor?

Think of dynamic sql being "outside" the scope of the current thread...|||Actually you're both wrong. I figured it out.

Turns out you need to use the EXEC command to execute the string.|||Yes I am...and I wish I wasn't

Why would you want to do this?

You going to build the Fetches dynamically?

How about the Declarations of the variables...That I don't think you can do indynamic sql

but this (to my UTTER amazement)..wrks:

USE Northwind
GO

DECLARE @.cmd varchar(8000), @.ShippedDate datetime
SELECT @.cmd = 'DECLARE myCursor CURSOR FOR SELECT ShippedDate FROM Orders'
EXEC(@.Cmd)
OPEN myCursor
FETCH NEXT FROM myCursor INTO @.ShippedDate
SELECT @.ShippedDate
CLOSE myCursor
DEALLOCATE myCursor

Good luck...|||The short answer is consolidated reporting on Accounting systems.

Most modern accounting systems allow for multiple companies to be managed from one server. To accomodate this, a seperate database is created for each company but fortunately the structure of the tables does not change between companyies . As a result, to report consolidated figures for the entire organization you want to have catalog names passed in dynamically especially if your oganization contains many companies.|||Can you post the sproc?

I'd like to see if there's a non cursor way...|||Here is one of them.

CREATE PROCEDURE sp_Sales_Summary_Update_02_03

@.CatalogName NVARCHAR(5),
@.ItemNumber NVARCHAR(31),
@.Warehouse NVARCHAR(11),
@.PurchaseTableName NVARCHAR(8),
@.PurchaseLineTableName NVARCHAR(8)
AS

DECLARE @.FromTheYear INT
DECLARE @.FromTheWeek INT
DECLARE @.ToTheYear INT
DECLARE @.ToTheWeek INT
DECLARE @.QuantityOrdered NUMERIC(19,5)
DECLARE @.strCursorString NVARCHAR(4000)

SET @.strCursorString = ''
SET @.strCursorString = @.strCursorString + 'DECLARE curQuantityOrdered CURSOR FORWARD_ONLY FOR '
SET @.strCursorString = @.strCursorString + 'SELECT '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE) AS FromTheYear, '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE) AS FromTheWeek, '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE) AS ToTheYear, '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE) AS ToTheWeek, '
SET @.strCursorString = @.strCursorString + ' ''' + @.CatalogName + ''' AS CompanyID, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR AS ItemNumber, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE AS Warehouse, '

SET @.strCursorString = @.strCursorString + ' SUM(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.QTYORDER) AS QuantityOrdered '
SET @.strCursorString = @.strCursorString + 'FROM ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + ' LEFT OUTER JOIN ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + ' '
SET @.strCursorString = @.strCursorString + ' ON ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PONUMBER = ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.PONUMBER '
SET @.strCursorString = @.strCursorString + 'WHERE '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.POSTATUS <> 6) AND '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.QTYORDER <> 0) '
SET @.strCursorString = @.strCursorString + 'GROUP BY '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE '
SET @.strCursorString = @.strCursorString + 'HAVING '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR = ''' + @.ItemNumber + ''') AND '
SET @.strCursorString = @.strCursorString + ' (''' + @.CatalogName + ''' = ''' + @.CatalogName + ''') AND '
SET @.strCursorString = @.strCursorString + ' (' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE = ''' + @.Warehouse + ''') '
SET @.strCursorString = @.strCursorString + 'ORDER BY '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo. ' + @.PurchaseTableName + '.DOCDATE), '
SET @.strCursorString = @.strCursorString + ' YEAR(' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' DATEPART(WEEK, ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.PRMSHPDTE), '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.ITEMNMBR, '
SET @.strCursorString = @.strCursorString + ' ' + @.CatalogName + '.dbo.' + @.PurchaseLineTableName + '.LOCNCODE '
PRINT @.strCursorString
EXECUTE(@.strCursorString)

OPEN curQuantityOrdered

FETCH NEXT FROM curQuantityOrdered INTO @.FromTheYear, @.FromTheWeek, @.ToTheYear, @.ToTheWeek, @.CatalogName, @.ItemNumber, @.Warehouse, @.QuantityOrdered
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE tblSalesSummary
SET QuantityOrdered = @.QuantityOrdered
FROM
tblSalesSummary
WHERE
(CompanyID = @.CatalogName) AND
(ItemNumber = @.ItemNumber) AND
(Warehouse = @.Warehouse) AND
CASE
WHEN TheWeek < 10 THEN
CAST(TheYear AS nvarchar(4)) + '0'+ CAST(TheWeek AS nvarchar(2))
ELSE
CAST(TheYear AS nvarchar(4)) + CAST(TheWeek AS nvarchar(2))
END
BETWEEN
CASE
WHEN @.FromTheWeek < 10 THEN
CAST(@.FromTheYear AS nvarchar(4)) + '0'+ CAST(@.FromTheWeek AS nvarchar(2))
ELSE
CAST(@.FromTheYear AS nvarchar(4)) + CAST(@.FromTheWeek AS nvarchar(2))
END
AND
CASE
WHEN @.ToTheWeek < 10 THEN
CAST(@.ToTheYear AS nvarchar(4)) + '0'+ CAST(@.ToTheWeek AS nvarchar(2))
ELSE
CAST(@.ToTheYear AS nvarchar(4)) + CAST(@.ToTheWeek AS nvarchar(2))
END

FETCH NEXT FROM curQuantityOrdered INTO @.FromTheYear, @.FromTheWeek, @.ToTheYear, @.ToTheWeek, @.CatalogName, @.ItemNumber, @.Warehouse, @.QuantityOrdered
END
CLOSE curQuantityOrdered
DEALLOCATE curQuantityOrdered
GO|||It Looks like alot but is really 2 steps. The first BLOB of TSQL creates the cursor. The second BLOB of TSQL updates the destination table.|||WOW! Dude...how long does it take to run?|||This particular sp executes in a fraction of a second. However it is run multiple times and depending on the volume of data required for processing can add up to hours (inconjunction with the other sp's I have running).