CASE statement

Hello,
I created a view in SQL Server Express 2005 that contained CASE END
statements.
I use this view in a .NET 2005 project, which works fine on my machine.
When I deploy my project to a machine that is running SQL Server 200, I get
the following error:
The Query Designer does not support the CASE SQL contruct.
Any way around this'
TIA,
Amber"amber" <amber@.discussions.microsoft.com> wrote in message
news:C5E9214E-628F-4CC3-8CC4-4DD7A5347999@.microsoft.com...
> Hello,
> I created a view in SQL Server Express 2005 that contained CASE END
> statements.
> I use this view in a .NET 2005 project, which works fine on my machine.
> When I deploy my project to a machine that is running SQL Server 200, I
> get
> the following error:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>
If you could include the CASE statement that you used, and any other
relevant DDL we could be more helpful.
Rick Sawtell|||Which SQL 200x? :) 2000 or 2005?
The way around this: don't use the Query Designer. Script it and run the
code in Query Analyzer.
amber wrote:
> Hello,
> I created a view in SQL Server Express 2005 that contained CASE END
> statements.
> I use this view in a .NET 2005 project, which works fine on my machine.
> When I deploy my project to a machine that is running SQL Server 200, I ge
t
> the following error:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>|||"amber" wrote:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>
Yes. Learn to write SQL instead of using the Query Designer. The designer is
a crutch that will do you know favours in the long run. If you want to use
anything more than very basic stuff then you must avoid it.
David Portas
SQL Server MVP
--

Case sensitive AS server and VBA functions

I use VBA function, e.g. ABS, in an MDX statment, and try to deploy the project to a case-sensitive AS 2005 server -- got and error message "An unexpected exception occured". No such problem when server isn't case sensitive.

Any one came across this problem and has any idea how to overcome this problem?

Thanks.

Try to contact Customer support and report your problem.

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

|||How to connect them? I tried the feedback site, but it's seems no body in MS read it.|||

Try going through http://support.microsoft.com/oas/default.aspx?gprid=2855 .

Navigate to the edition of SQL server you are running.

Which VBA function are you trying to use? Have you tried to spell them with all capital letters?

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

|||

I tried to use ABS() and VAL(). I tried to spell it ABS, abs, Abs even AbS, aBs, etc. Simply doesn't work. Microsoft doen't care, and customer support costs money. Why should I pay for their Bugs? Hello, MS, somebody at home?

|||

If you willing to share your design, I would be happy to take a look.

Feel free to contact me by removing the "noreply.online." part of my display email.

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

sql

Case or Pivot how?

My query is that in table I have username and role name column
Eg
Username rolename project iD
Rahul dataentry 2
Vikas verifier 2
Binu verifier 3
Debashish dataentry 3
Now I want the result like
Dataentry verifier PROJECT ID
Rahul VIKAS 2
Debashish Binu 3
i have tried using case but the result is giving like
Dataentry verifier PROJECT ID
rahul null 2
null VIKAS 2
Debashish null 3
null Binu 3HI guys
My query is that in table I have username and role name column
Eg
Username rolename project iD
Rahul dataentry 2
Vikas verifier 2
Binu verifier 3
Debashish dataentry 3
Now I want the result like
Dataentry verifier PROJECT ID
Rahul VIKAS 2
Debashish Binu 3
i have tried using case but the result is giving like
Dataentry verifier PROJECT ID
rahul null 2
null VIKAS 2
Debashish null 3
null Binu 3
thanks in adnavce
pls help my whole work is stuck up bcoz of this|||On Jun 12, 12:33 pm, genius.j...@.gmail.com wrote:
> HI guys
> My query is that in table I have username and role name column
> Eg
> Username rolename project iD
> Rahul dataentry 2
> Vikas verifier 2
> Binu verifier 3
> Debashish dataentry 3
> Now I want the result like
> Dataentry verifier PROJECT ID
> Rahul VIKAS 2
> Debashish Binu 3
> i have tried using case but the result is giving like
> Dataentry verifier PROJECT ID
> rahul null 2
> null VIKAS 2
> Debashish null 3
> null Binu 3
> thanks in adnavce
> pls help my whole work is stuck up bcoz of this
select
max(case when rolename = 'dataentry ' then username end ) as
dataentry ,
max(case when rolename = 'verifier ' then username end ) as verifier ,
project_id
from table
group by project_id

Case Function on a Cell

Hi all,
have a Matrix-based report that tracks project's %complete by period id for a
particular year, as follows:
- 2005
---
01 02 03 04 05 06 07 08 09 10 11 12
project1 10% 12% 18% 25% 38% 52%
project2 13% 29% 68% 89% 100%
project3 25% 68% 100%
:
:
:
I need to know how to change the header grop of Period_id "J F M A M J J A S
O N D" instead of "01 02 03 04 05 06 07 08 09 10 11 12" inside a statement
in the report, since i can not control the sql statement that feeds the
report.
Thank you in advance
Tony
--
Message posted via http://www.sqlmonster.comHave a look at my reply for "Proper syntax of CASE()" etc - in other words
write a little VB .net function in the Report's code box, then call the
funtion to return a string value
"Antony Altobelli via SQLMonster.com" wrote:
> Hi all,
> have a Matrix-based report that tracks project's %complete by period id for a
> particular year, as follows:
> - 2005
> ---
> 01 02 03 04 05 06 07 08 09 10 11 12
> project1 10% 12% 18% 25% 38% 52%
> project2 13% 29% 68% 89% 100%
> project3 25% 68% 100%
> :
> :
> :
> I need to know how to change the header grop of Period_id "J F M A M J J A S
> O N D" instead of "01 02 03 04 05 06 07 08 09 10 11 12" inside a statement
> in the report, since i can not control the sql statement that feeds the
> report.
> Thank you in advance
> Tony
> --
> Message posted via http://www.sqlmonster.com
>

Cascade Update

Does anyone know how to do a cascade update in SQL 2005 using the studio manager?

Basically, I have a project table with a status column. If the status is set to 2, then I need to update another table that references the project id.

You can create a foreign key with cascading. For example you have 2 tables: the referenced table tbl1 with projectid as primary key, and the referencing table tbl2 with projectid column:

Alter Table tbl2 Add
Constraint fk_tbl2_projectid
Foreign Key (projectid)
References tbl1(projectid)
On Update Cascade

You can get more information from these links:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_aa-az_3ied.asp

And

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_92ib.asp

Cascade Inserts/Clone a tree

I am creating a small project management application using SQL Server. There
are three main tables (prjProjects, prjTasks, prjDetails). A project can
have 0 to many tasks and a tasks can have 0 to many details.
Many of our projects are very similar in that they would consist of
basically the same task and detail lists/records. I would like to be able to
"clone" a project using a stored procedure. I will be accessing the tables
from multiple front ends such as MS Access, ColdFusion, and ASP. A stored
procedure would allow me to create a new, cloned project from any of these
front ends.
I am not sure where to start with this. I have searched Google and news
groups without finding much useful information. Do I need to create a cursor
to loop through records and perform inserts or is there a more efficient
method? The tables, significant fields, and records are created below. There
are additional fields for descriptions, dates, etc but if I get the basic
framework, I should be able to include the other fields. I would like to
duplicate the "TITLE" field values in the new records.
Hope I have provided enough info that someone can point me in the right
direction.
Duane Hookom
MS Access MVP
CREATE TABLE [dbo].[prjProjects]
(
[PR_ID] [int] IDENTITY (1, 1) NOT NULL ,
[PR_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[prjTasks] (
[TS_ID] [int] IDENTITY (1, 1) NOT NULL ,
[TS_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TS_PR_ID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[prjDetails]
(
[DT_ID] [int] IDENTITY (1, 1) NOT NULL ,
[DT_TITLE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DT_TS_ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjProjects] WITH NOCHECK ADD
CONSTRAINT [PK_prjProjects] PRIMARY KEY CLUSTERED
(
[PR_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjTasks] WITH NOCHECK ADD
CONSTRAINT [PK_prjTasks] PRIMARY KEY CLUSTERED
(
[TS_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjDetails] WITH NOCHECK ADD
CONSTRAINT [PK_prjDetails] PRIMARY KEY CLUSTERED
(
[DT_ID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prjTasks] ADD
CONSTRAINT [FK_prjTasks_prjProjects] FOREIGN KEY
(
[TS_PR_ID]
) REFERENCES [dbo].[prjProjects] (
[PR_ID]
)
GO
ALTER TABLE [dbo].[prjDetails] ADD
CONSTRAINT [FK_prjDetails_prjTasks] FOREIGN KEY
(
[DT_TS_ID]
) REFERENCES [dbo].[prjTasks] (
[TS_ID]
)
GO
INSERT INTO prjProjects (PR_TITLE) Values('Project to Clone')
GO
INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task One', 1)
GO
INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task Two', 1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 3 of Task
One',1)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
Two',2)
GO
INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
Two',2)
GO"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I am creating a small project management application using SQL Server.
>There are three main tables (prjProjects, prjTasks, prjDetails). A project
>can have 0 to many tasks and a tasks can have 0 to many details.
> Many of our projects are very similar in that they would consist of
> basically the same task and detail lists/records. I would like to be able
> to "clone" a project using a stored procedure. I will be accessing the
> tables from multiple front ends such as MS Access, ColdFusion, and ASP. A
> stored procedure would allow me to create a new, cloned project from any
> of these front ends.
> I am not sure where to start with this. I have searched Google and news
> groups without finding much useful information. Do I need to create a
> cursor to loop through records and perform inserts or is there a more
> efficient method? The tables, significant fields, and records are created
> below. There are additional fields for descriptions, dates, etc but if I
> get the basic framework, I should be able to include the other fields. I
> would like to duplicate the "TITLE" field values in the new records.
> Hope I have provided enough info that someone can point me in the right
> direction.
> --
> Duane Hookom
> MS Access MVP
>
First you should be to add some important constraints. These are my
assumptions of course but the keys will be important here.
ALTER TABLE dbo.prjProjects ALTER COLUMN PR_TITLE NVARCHAR(50) NOT NULL;
ALTER TABLE dbo.prjTasks ALTER COLUMN TS_TITLE NVARCHAR(50) NOT NULL;
ALTER TABLE dbo.prjProjects ADD CONSTRAINT AK1_prjProjects
UNIQUE (PR_TITLE);
ALTER TABLE dbo.prjTasks ADD CONSTRAINT AK1_prjTasks
UNIQUE (TS_TITLE, TS_PR_ID);
ALTER TABLE dbo.prjDetails ADD CONSTRAINT AK1_prjDetails
UNIQUE (DT_TITLE, DT_TS_ID);
Now create a proc something like this (error handling omitted for brevity):
CREATE PROC dbo.usp_project_copy
(
@.pr_id INT,
@.pr_title NVARCHAR(50),
@.new_pr_id INT OUTPUT
)
AS
BEGIN
INSERT INTO dbo.prjProjects (PR_TITLE)
VALUES (@.pr_title) ;
SET @.new_pr_id = SCOPE_IDENTITY();
INSERT INTO dbo.prjTasks (TS_TITLE, TS_PR_ID)
SELECT TS_TITLE, @.new_pr_id
FROM dbo.prjTasks
WHERE TS_PR_ID = @.pr_id ;
INSERT INTO dbo.prjDetails (DT_TITLE, DT_TS_ID)
SELECT D1.DT_TITLE, T2.TS_ID
FROM dbo.prjDetails AS D1
JOIN dbo.prjTasks AS T1
ON D1.DT_TS_ID = T1.TS_ID
AND T1.TS_PR_ID = @.pr_id
JOIN dbo.prjTasks AS T2
ON T1.TS_TITLE = T2.TS_TITLE
AND T2.TS_PR_ID = @.new_pr_id
AND T1.TS_TITLE = T2.TS_TITLE ;
RETURN
END
GO
DECLARE @.pr_id INT
EXEC dbo.usp_project_copy
@.pr_id = 1,
@.pr_title = 'New Project',
@.new_pr_id = @.pr_id OUTPUT ;
SELECT *
FROM prjProjects
WHERE PR_ID = @.pr_id ;
Thanks for including the DDL. Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||You could create a script file, very similar to the DDL/DML you offered,
adding additional elements (as David indicated), and then execute the script
file using OSQL (for SQL 2000) or SQLcmd (for SQL 2005)
If you were to use any variables in the script file, remember that when a GO
is encountered, the variable goes out of scope, so you would have to
re-create and initialize it again.
If you want to automate the process, the script file can be executed from a
SQL Agent Job, or even from a stored procedure.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>I am creating a small project management application using SQL Server.
>There are three main tables (prjProjects, prjTasks, prjDetails). A project
>can have 0 to many tasks and a tasks can have 0 to many details.
> Many of our projects are very similar in that they would consist of
> basically the same task and detail lists/records. I would like to be able
> to "clone" a project using a stored procedure. I will be accessing the
> tables from multiple front ends such as MS Access, ColdFusion, and ASP. A
> stored procedure would allow me to create a new, cloned project from any
> of these front ends.
> I am not sure where to start with this. I have searched Google and news
> groups without finding much useful information. Do I need to create a
> cursor to loop through records and perform inserts or is there a more
> efficient method? The tables, significant fields, and records are created
> below. There are additional fields for descriptions, dates, etc but if I
> get the basic framework, I should be able to include the other fields. I
> would like to duplicate the "TITLE" field values in the new records.
> Hope I have provided enough info that someone can point me in the right
> direction.
> --
> Duane Hookom
> MS Access MVP
>
> CREATE TABLE [dbo].[prjProjects]
> (
> [PR_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [PR_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[prjTasks] (
> [TS_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [TS_TITLE] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TS_PR_ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[prjDetails]
> (
> [DT_ID] [int] IDENTITY (1, 1) NOT NULL ,
> [DT_TITLE] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [DT_TS_ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjProjects] WITH NOCHECK ADD
> CONSTRAINT [PK_prjProjects] PRIMARY KEY CLUSTERED
> (
> [PR_ID]
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[prjTasks] WITH NOCHECK ADD
> CONSTRAINT [PK_prjTasks] PRIMARY KEY CLUSTERED
> (
> [TS_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjDetails] WITH NOCHECK ADD
> CONSTRAINT [PK_prjDetails] PRIMARY KEY CLUSTERED
> (
> [DT_ID]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[prjTasks] ADD
> CONSTRAINT [FK_prjTasks_prjProjects] FOREIGN KEY
> (
> [TS_PR_ID]
> ) REFERENCES [dbo].[prjProjects] (
> [PR_ID]
> )
> GO
> ALTER TABLE [dbo].[prjDetails] ADD
> CONSTRAINT [FK_prjDetails_prjTasks] FOREIGN KEY
> (
> [DT_TS_ID]
> ) REFERENCES [dbo].[prjTasks] (
> [TS_ID]
> )
> GO
> INSERT INTO prjProjects (PR_TITLE) Values('Project to Clone')
> GO
> INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task One', 1)
> GO
> INSERT INTO prjTasks (TS_TITLE, TS_PR_ID) Values('Task Two', 1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
> One',1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
> One',1)
> GO
>
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 3 of Task
> One',1)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 1 of Task
> Two',2)
> GO
> INSERT INTO prjDetails (DT_TITLE, DT_TS_ID) Values ('Detail 2 of Task
> Two',2)
> GO
>|||David and Arnie,
Thanks much. This should provide a good starting point for me. All of my
projects, tasks, and details have start and expected dates associated so I
will need to include them. I'm fairly sure I can get this on my own. If not,
I'll be back.
Duane Hookom
MS Access MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:OHES$RsmGHA.3796@.TK2MSFTNGP05.phx.gbl...
> "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
> news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
> First you should be to add some important constraints. These are my
> assumptions of course but the keys will be important here.
> ALTER TABLE dbo.prjProjects ALTER COLUMN PR_TITLE NVARCHAR(50) NOT NULL;
> ALTER TABLE dbo.prjTasks ALTER COLUMN TS_TITLE NVARCHAR(50) NOT NULL;
> ALTER TABLE dbo.prjProjects ADD CONSTRAINT AK1_prjProjects
> UNIQUE (PR_TITLE);
> ALTER TABLE dbo.prjTasks ADD CONSTRAINT AK1_prjTasks
> UNIQUE (TS_TITLE, TS_PR_ID);
> ALTER TABLE dbo.prjDetails ADD CONSTRAINT AK1_prjDetails
> UNIQUE (DT_TITLE, DT_TS_ID);
>
> Now create a proc something like this (error handling omitted for
> brevity):
> CREATE PROC dbo.usp_project_copy
> (
> @.pr_id INT,
> @.pr_title NVARCHAR(50),
> @.new_pr_id INT OUTPUT
> )
> AS
> BEGIN
> INSERT INTO dbo.prjProjects (PR_TITLE)
> VALUES (@.pr_title) ;
> SET @.new_pr_id = SCOPE_IDENTITY();
> INSERT INTO dbo.prjTasks (TS_TITLE, TS_PR_ID)
> SELECT TS_TITLE, @.new_pr_id
> FROM dbo.prjTasks
> WHERE TS_PR_ID = @.pr_id ;
> INSERT INTO dbo.prjDetails (DT_TITLE, DT_TS_ID)
> SELECT D1.DT_TITLE, T2.TS_ID
> FROM dbo.prjDetails AS D1
> JOIN dbo.prjTasks AS T1
> ON D1.DT_TS_ID = T1.TS_ID
> AND T1.TS_PR_ID = @.pr_id
> JOIN dbo.prjTasks AS T2
> ON T1.TS_TITLE = T2.TS_TITLE
> AND T2.TS_PR_ID = @.new_pr_id
> AND T1.TS_TITLE = T2.TS_TITLE ;
> RETURN
> END
> GO
> DECLARE @.pr_id INT
> EXEC dbo.usp_project_copy
> @.pr_id = 1,
> @.pr_title = 'New Project',
> @.new_pr_id = @.pr_id OUTPUT ;
> SELECT *
> FROM prjProjects
> WHERE PR_ID = @.pr_id ;
> Thanks for including the DDL. Hope this helps.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||As an additional piece of information.
I would have a stored procedure creates the script file. By so doing, then I
could pass in the changable pieces of data, the stored procedure could
re-create all variables after encountering a GO in the script, and the end
result is a comprehensive script file that does not require me to do a
'search and destroy' (I mean search are replace) with the chance of missing
some location where a value needed to be replaced.
I've done it with two different processes. One, just use PRINT statements
line by line, then copy the generated output into a new QA window and save
(or execute the script). Or, two, using OSQL, output the generated script to
a file. I find the first choice gives me greater control on formating,
readibility, etc.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:etEkjismGHA.4164@.TK2MSFTNGP03.phx.gbl...
> You could create a script file, very similar to the DDL/DML you offered,
> adding additional elements (as David indicated), and then execute the
> script file using OSQL (for SQL 2000) or SQLcmd (for SQL 2005)
> If you were to use any variables in the script file, remember that when a
> GO is encountered, the variable goes out of scope, so you would have to
> re-create and initialize it again.
> If you want to automate the process, the script file can be executed from
> a SQL Agent Job, or even from a stored procedure.
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote in message
> news:uSbpn1rmGHA.4716@.TK2MSFTNGP04.phx.gbl...
>

Capture a sql command

Hi All.

I have this project that I need help with. There are 9 tables that I need to capture everything that happens to them (update, insert, delete). I was thinking of creating triggers. If someone does any of these actions against them then I need to insert into another table the date, the table name, the command that was run, and the records that are affected by it. Now I know how to do the date and table name, that's easy. My question is how do I capture the command. Once I have the command I can get the records affected.

If anyone knows how to do this, please help.

Thanks,
ODanielsHave you considered trying SQL Profiler?|||I need to capture the records affected before they are affected first. Then execute the command to complete it. The profiler would give me everything that is going on. But will it give me the records affected?|||Don't think you can capture them that way, no.
Will be interesting to see if anyone can show a way to do it.|||Yes it would.
Thanks anyway.|||I need to capture the records affected before they are affected first. Then execute the command to complete it.Triggers operate after the command is executed, so if your requirements are strict about this (don't know why...) then triggers will not help you.
All your commands SHOULD come through stored procedures. You can easily put some code at the top of each procedure to log who ran it, when, and what parameters were supplied.

capacity planning.

hi
I want to start with a new project and need to do Database sizing.,capacity planning,server requirements..etc for sql 2k
can anyone help me in getting documents/material for this.
regard
sunnyYou can start with the capacity planning chapter in the SQL operations
guide:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/operate/opsguide/sqlops6.asp
--
Jacco Schalkwijk
SQL Server MVP
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:BD7035EB-33DB-4302-8EEA-9274F6E0FAAD@.microsoft.com...
> hi,
> I want to start with a new project and need to do Database
sizing.,capacity planning,server requirements..etc for sql 2k.
> can anyone help me in getting documents/material for this..
> regards
> sunny

capacity planning.

hi,
I want to start with a new project and need to do Database sizing.,capacity
planning,server requirements..etc for sql 2k.
can anyone help me in getting documents/material for this..
regards
sunnyYou can start with the capacity planning chapter in the SQL operations
guide:
http://www.microsoft.com/technet/tr...ide/sqlops6.asp
Jacco Schalkwijk
SQL Server MVP
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:BD7035EB-33DB-4302-8EEA-9274F6E0FAAD@.microsoft.com...
> hi,
> I want to start with a new project and need to do Database
sizing.,capacity planning,server requirements..etc for sql 2k.
> can anyone help me in getting documents/material for this..
> regards
> sunny

Can't use SQL Express in VS2005

When I try to add a new database to App_Data in a VS2005sp1 project I get "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user interface".

I have SQL2005sp2 and SQL Express on the system, both services are running, and an instance of the latter called SQLExpress which I can use through SQL Server Management Studio. sp_configure user instances enabled is set to 1. The SQL Server Instance Name in Options | Database Tools | Data Connections is set to SQLExpress ...

I have no idea how to proceed .. any ideas anyone?

Thanks

John

Most causes of this error are solved by deleting the folder where we store the SQL Express User Instance system files, which is C:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. You may need to reboot your computer after this, so just eliminate any concerns and reboot.

The once cause that this will not resolve is if you are working thorugh remote desktop. There is bug in windows that results in the remote login not have certain permissions. The problem is explained in http://support.microsoft.com/Default.aspx?id=896613 and you will need to call Microsoft Support to obtain the Hotfix for the problem.

Hopefully this helps.

Mike

|||

Thanks, Mike

That was it. Problem now solved.

John

|||I had the following trouble.
- Installed MSSQLEXPRESS
- Installed Visual Studio 2005
- Uninstalled MSSQLEXPRESS
- Installed MSSQLEXPRESS

->the bloody error started appearing.

The suggested method worked perfectly!!
I killed the folder, restarted Studio, now can connect to the databases.

Cheers,
Valekm