Adhoc report with join tables on

Does anyone know if user has the capability to join two entities with
additional field which is not a predefined primary key or foreign key at the
design time? User want to run the report by join tables with certain fields
on the fly. I have not found a way to allow them to do that unless they have
Microsoft visual studio 2005 or SQL server business intelligence development
studio installed. Is SQL server reporting service a right candidate to serve
user's request? Does anyone know any other tool with this capability?
Thanks!1. You can do this if you define the sql statement dynamically and allow the
user parameter choices that provide the ability to specify the tables and
joins expressions. (for how to define the sql statement dynamically,
building it up as an expression, just treat the command as an expression
like you would the expression to display in a text box. IOW, start it with
an = sign and build up the string or invoke a code function)
However, I don't think it's really the best way.
2. Does the user have access and understanding to create views on the
server? It might be best to define the report based on a view, and have the
report basically remain ignorant of the joins and table information. The
report would have a single parameter -- the name of the view to invoke --
and would send that information to a stored procedure which would validate
that the view exists and has appropriate columns, and then run the view or
error-handle as described in choice #3 below.
3. If the user does not have that ability or access, I think I would build
this report to run a stored procedure, passing the parameter information as
described in #1, and have the stored procedure built and execute the sql. I
could do better validation in the stored procedure (for example, validate
that the tables and fields chosen by the user actually exist, assuming these
elements cannot be a dropdown in the report interface). I would have the
sproc send back a default data set of one record with every item showing
appropriate error text (or something) if I couldn't handle it another way.
Basically I think choice #2 is the right way to go here and if the user
doesn't have that ability and access I'm wondering whether that user should
be specifying this information at all...
>L<
"Daisy" <diyfan@.msnews.group.post> wrote in message
news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> Does anyone know if user has the capability to join two entities with
> additional field which is not a predefined primary key or foreign key at
> the
> design time? User want to run the report by join tables with certain
> fields
> on the fly. I have not found a way to allow them to do that unless they
> have
> Microsoft visual studio 2005 or SQL server business intelligence
> development
> studio installed. Is SQL server reporting service a right candidate to
> serve
> user's request? Does anyone know any other tool with this capability?
> Thanks!|||Thank you very much for the idea! I had built the model with report builder
to let user chose any fields from the tables given for the report. Now user
wanted to join the table not based on the primary key field that specified in
the design time. They wanted to join the table by certain non key fields at
the run time. I was stucked. I was only thinking use report builder model to
let user do this. And the report model could not change the key field (join
relation) on the fly. I have not found anything online or in MSDN library
telling me how to define the key at run time. As you suggested by using the
reprot designer and the sql stored procedure it is feasible to achieve the
task. Hope I did not misunderstood your post. If so please let me know.
Thanks!
"Lisa Slater Nicholls" wrote:
> 1. You can do this if you define the sql statement dynamically and allow the
> user parameter choices that provide the ability to specify the tables and
> joins expressions. (for how to define the sql statement dynamically,
> building it up as an expression, just treat the command as an expression
> like you would the expression to display in a text box. IOW, start it with
> an = sign and build up the string or invoke a code function)
> However, I don't think it's really the best way.
> 2. Does the user have access and understanding to create views on the
> server? It might be best to define the report based on a view, and have the
> report basically remain ignorant of the joins and table information. The
> report would have a single parameter -- the name of the view to invoke --
> and would send that information to a stored procedure which would validate
> that the view exists and has appropriate columns, and then run the view or
> error-handle as described in choice #3 below.
> 3. If the user does not have that ability or access, I think I would build
> this report to run a stored procedure, passing the parameter information as
> described in #1, and have the stored procedure built and execute the sql. I
> could do better validation in the stored procedure (for example, validate
> that the tables and fields chosen by the user actually exist, assuming these
> elements cannot be a dropdown in the report interface). I would have the
> sproc send back a default data set of one record with every item showing
> appropriate error text (or something) if I couldn't handle it another way.
> Basically I think choice #2 is the right way to go here and if the user
> doesn't have that ability and access I'm wondering whether that user should
> be specifying this information at all...
> >L<
>
> "Daisy" <diyfan@.msnews.group.post> wrote in message
> news:DE6C8C52-FBE3-48D4-9EB6-F37BBD8C40E4@.microsoft.com...
> > Does anyone know if user has the capability to join two entities with
> > additional field which is not a predefined primary key or foreign key at
> > the
> > design time? User want to run the report by join tables with certain
> > fields
> > on the fly. I have not found a way to allow them to do that unless they
> > have
> > Microsoft visual studio 2005 or SQL server business intelligence
> > development
> > studio installed. Is SQL server reporting service a right candidate to
> > serve
> > user's request? Does anyone know any other tool with this capability?
> >
> > Thanks!
>

Ad-hoc INSERT of new Identity Value

Hello,
I have a table with a primary key having IDENTITY (ID).
In case of an INSERT I want to fill another Column C1
with the new Identity value.
How can I achive this? By a Trigger maybe?
Thank you very much
JoachimTry
create table #t (c1 int not null identity(1,1),c2 char(1),c3 as c1)
insert into #t (c2) values ('a')
insert into #t (c2) values ('b')
insert into #t (c2) values ('c')
select * from #t
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim|||What would be the point of having redundant data in the table? Anyway, much
easier with a computed column than with a trigger, but this won't work if
your intention is to later update C1.
CREATE TABLE dbo.foo
(
id INT IDENTITY(1,1),
blat VARCHAR(32),
C1 AS id
);
INSERT dbo.foo(blat) SELECT 'bar';
SELECT * FROM dbo.foo;
DROP TABLE dbo.foo;
Or just use a view:
CREATE VIEW dbo.View_foo
AS
SELECT id, C1 = id
FROM dbo.foo;
But it's tough to provide a good answer if we have no idea. It's usually
better to describe the intended goal than to ask us how to accomplish the
solution you've already determined is the only way to do it...
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:OysGn1beGHA.5040@.TK2MSFTNGP03.phx.gbl...
> Hello,
> I have a table with a primary key having IDENTITY (ID).
> In case of an INSERT I want to fill another Column C1
> with the new Identity value.
> How can I achive this? By a Trigger maybe?
> Thank you very much
> Joachim

adding UNIQUE Constraint to existing column

Hello,
I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
duplicate key using WITH NOCHECK in SQL Server 2000.
Here is my SQL syntax:
ALTER TABLE user_email WITH NOCHECK
ADD CONSTRAINT unq_user_email_email UNIQUE (email)
Query Analyzer keeps giving me the error message that duplicate key was foun
d:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 4. Most significant primary key is 'user1@.abc.com'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
I learned from Books Online that "WITH NOCHECK" allows to add an unverified
constraint and prevents the validation against existing rows.
I tried using SQLServer Enterprise Manager, Manage Indexes and got the same
error message even though that I had checked the "Ignore Duplicate Values"
checkbox.
Can someone please tell me how i can add a UNIQUE CONSTRAINT without
removing the duplicate keys.
Thank you so much for your help!
--
MitraSQL-Server uses a unique index to enforce a UNIQUE constraint. A unique
index does not allow duplicates, under no circumstances.
The following text is also part of the BOL article:
"The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY
and UNIQUE constraints."
HTH,
Gert-Jan
mitra wrote:
> Hello,
> I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
> duplicate key using WITH NOCHECK in SQL Server 2000.
> Here is my SQL syntax:
> ALTER TABLE user_email WITH NOCHECK
> ADD CONSTRAINT unq_user_email_email UNIQUE (email)
> Query Analyzer keeps giving me the error message that duplicate key was fo
und:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 4. Most significant primary key is 'user1@.abc.com'.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> The statement has been terminated.
> I learned from Books Online that "WITH NOCHECK" allows to add an unverifie
d
> constraint and prevents the validation against existing rows.
> I tried using SQLServer Enterprise Manager, Manage Indexes and got the sam
e
> error message even though that I had checked the "Ignore Duplicate Values"
> checkbox.
> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
> Thank you so much for your help!
> --
> Mitra|||> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
Thankfully you can't. From BOL:
"IGNORE_DUP_KEY
Controls what happens when an attempt is made to insert a duplicate key
value into a column that is part of a unique clustered index. If
IGNORE_DUP_KEY was specified for the index and an INSERT statement that
creates a duplicate key is executed, SQL Server issues a warning and ignores
the duplicate row."
When it says ignores the duplicate row, it means it doesn't insert it.
If you want to do what you are suggesting you will need to build a trigger.
Then just join to the parent table to see if the values already exist (make
sure to take care of the case where duplicate values are inserted via the
INSERT statement. If you want more information about how to do this, I
could help for out (as could others here :).
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:3BB0328C-8FC1-40A8-A614-D8E2C0B43859@.microsoft.com...
> Hello,
> I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
> duplicate key using WITH NOCHECK in SQL Server 2000.
> Here is my SQL syntax:
> ALTER TABLE user_email WITH NOCHECK
> ADD CONSTRAINT unq_user_email_email UNIQUE (email)
>
> Query Analyzer keeps giving me the error message that duplicate key was
> found:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 4. Most significant primary key is 'user1@.abc.com'.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> The statement has been terminated.
>
> I learned from Books Online that "WITH NOCHECK" allows to add an
> unverified
> constraint and prevents the validation against existing rows.
> I tried using SQLServer Enterprise Manager, Manage Indexes and got the
> same
> error message even though that I had checked the "Ignore Duplicate Values"
> checkbox.
> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
> Thank you so much for your help!
> --
> Mitra|||On Thu, 03 Feb 2005 21:18:09 +0100, Gert-Jan Strik wrote:

> SQL-Server uses a unique index to enforce a UNIQUE constraint. A unique
> index does not allow duplicates, under no circumstances.
> The following text is also part of the BOL article:
> "The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY
> and UNIQUE constraints."
I guess to achieve the effect you want, you could use a trigger:
create table user_email (email varchar(20) not null)
insert into user_email values ('rpresser@.nowhere.com')
insert into user_email values ('rpresser@.nowhere.com')
insert into user_email values ('rpresser@.nowhere.com')
go
select * from user_email
go
CREATE TRIGGER user_email_add
ON user_email
FOR INSERT
AS
IF EXISTS (select * FROM user_email U, inserted WHERE U.email =
inserted.email)
BEGIN
RAISERROR ('Email must be unique',16,1)
ROLLBACK TRANSACTION
END
go
insert into user_email values ('rpresser@.nowhere.com')
Results in this output:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
email
--
rpresser@.imtek.com
rpresser@.imtek.com
rpresser@.imtek.com
(3 row(s) affected)
Server: Msg 50000, Level 16, State 1, Procedure user_email_add, Line 7
Email must be unique|||Not quite. Two problems. First, the trigger fires after the insert. No
matter what you insert it will fail because whatever is in the inserted
table will already be in the table. Second, you are forgetting the case
where the user inserts duplicates in the insert statement:
insert into user_email
select 'bob@.bob.com'
union
select 'bob@.bob.com'
Here you have to consider the uniqueness of the values in the inserted
table.
Using his table, this code will work:
CREATE TRIGGER user_email_add
ON user_email
FOR INSERT, UPDATE
AS
IF EXISTS ( select user_email.email
FROM inserted
join user_email
on user_email.email = inserted.email
group by user_email.email
having count(*) > 1 )
begin
RAISERROR ('Email must be unique.',16,1)
ROLLBACK TRANSACTION
return
end
go
Note that I changed it to an UPDATE trigger also, since otherwise:
UPDATE user_email
SET email = 'fred@.server.com'
would work just fine. I would still suggest against this approach. Clean
up the data and life will be peachy :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:l5jr9dfyygw7$.dlg@.rpresser.invalid...
> On Thu, 03 Feb 2005 21:18:09 +0100, Gert-Jan Strik wrote:
>
> I guess to achieve the effect you want, you could use a trigger:
> create table user_email (email varchar(20) not null)
> insert into user_email values ('rpresser@.nowhere.com')
> insert into user_email values ('rpresser@.nowhere.com')
> insert into user_email values ('rpresser@.nowhere.com')
> go
> select * from user_email
> go
> CREATE TRIGGER user_email_add
> ON user_email
> FOR INSERT
> AS
> IF EXISTS (select * FROM user_email U, inserted WHERE U.email =
> inserted.email)
> BEGIN
> RAISERROR ('Email must be unique',16,1)
> ROLLBACK TRANSACTION
> END
> go
> insert into user_email values ('rpresser@.nowhere.com')
>
> Results in this output:
> (1 row(s) affected)
>
> (1 row(s) affected)
>
> (1 row(s) affected)
> email
> --
> rpresser@.imtek.com
> rpresser@.imtek.com
> rpresser@.imtek.com
> (3 row(s) affected)
> Server: Msg 50000, Level 16, State 1, Procedure user_email_add, Line 7
> Email must be unique

adding primary key to an existing table

Hi all,

I there a way to check if the column is set to NOT NULL is equal to true, if not set to true i have to set it to true before adding my primary key or else i'll get an error message.

in my SP i have a ALTER TABLE table1 ADD PRIMARY KEY (colum1)

My question is, how can i check if the column is set to not null is false and how can i update to set it true?

Thanks.

Teo,

You can use the DMVs sys.tables and sys.columns to determine if a column is nullable.

select t.name, c.name, c.is_nullable
from sys.tables t join sys.columns c
on t.object_id = c.object_id
where t.name = 'fact'

Secondly, to change a column to NOT NULL if it is, use ALTER TABLE ... ALTER COLUMN.

ALTER TABLE dbo.fact ALTER COLUMN dim1_id INT NOT NULL

Regards,

Galex

|||

Why you want to check for NULL. without checking it just set it to not null

ALTER TABLE table1 alter column col1 int not null

ALTER TABLE table1 ADD PRIMARY KEY (col1)

it will not retun any errors even it is already set to not null

Adding data to SQL database through Access

My table resides in a SQL database: It has two fields:
1. NameID - KEY 2. Name
I want the user to have the ability to add new names in an Access form and
disallow any names that already exist. SQL does not give me the ability to
set the Name field to 'No Duplicates' and Access does not let me set the NO
DUP property since this is a linked table.
What code or settings is required to disallow duplicates in a no-key field
between Access and SQL?First of all, are you using a .mdb or .adp file
I think the best way to do it is use the DLookup function in Access on the
AFterUpdate Event...
Please look at DLookup in the Help file in MS Access
Ed
"SharonInGa" wrote:

> My table resides in a SQL database: It has two fields:
> 1. NameID - KEY 2. Name
> I want the user to have the ability to add new names in an Access form and
> disallow any names that already exist. SQL does not give me the ability to
> set the Name field to 'No Duplicates' and Access does not let me set the N
O
> DUP property since this is a linked table.
> What code or settings is required to disallow duplicates in a no-key field
> between Access and SQL?
>|||What you seem to be looking for is a unique constraint. You
can find more information on this in SQL Server books
online. It's the same as the 'No Duplicates' in Access.
-Sue
On Thu, 20 Jan 2005 13:37:02 -0800, "SharonInGa"
<SharonInGa@.discussions.microsoft.com> wrote:

>My table resides in a SQL database: It has two fields:
>1. NameID - KEY 2. Name
>I want the user to have the ability to add new names in an Access form and
>disallow any names that already exist. SQL does not give me the ability to
>set the Name field to 'No Duplicates' and Access does not let me set the NO
>DUP property since this is a linked table.
>What code or settings is required to disallow duplicates in a no-key field
>between Access and SQL?

Adding data to SQL database through Access

My table resides in a SQL database: It has two fields:
1. NameID - KEY 2. Name
I want the user to have the ability to add new names in an Access form and
disallow any names that already exist. SQL does not give me the ability to
set the Name field to 'No Duplicates' and Access does not let me set the NO
DUP property since this is a linked table.
What code or settings is required to disallow duplicates in a no-key field
between Access and SQL?
First of all, are you using a .mdb or .adp file
I think the best way to do it is use the DLookup function in Access on the
AFterUpdate Event...
Please look at DLookup in the Help file in MS Access
Ed
"SharonInGa" wrote:

> My table resides in a SQL database: It has two fields:
> 1. NameID - KEY 2. Name
> I want the user to have the ability to add new names in an Access form and
> disallow any names that already exist. SQL does not give me the ability to
> set the Name field to 'No Duplicates' and Access does not let me set the NO
> DUP property since this is a linked table.
> What code or settings is required to disallow duplicates in a no-key field
> between Access and SQL?
>
|||What you seem to be looking for is a unique constraint. You
can find more information on this in SQL Server books
online. It's the same as the 'No Duplicates' in Access.
-Sue
On Thu, 20 Jan 2005 13:37:02 -0800, "SharonInGa"
<SharonInGa@.discussions.microsoft.com> wrote:

>My table resides in a SQL database: It has two fields:
>1. NameID - KEY 2. Name
>I want the user to have the ability to add new names in an Access form and
>disallow any names that already exist. SQL does not give me the ability to
>set the Name field to 'No Duplicates' and Access does not let me set the NO
>DUP property since this is a linked table.
>What code or settings is required to disallow duplicates in a no-key field
>between Access and SQL?

Adding data to SQL database through Access

My table resides in a SQL database: It has two fields:
1. NameID - KEY 2. Name
I want the user to have the ability to add new names in an Access form and
disallow any names that already exist. SQL does not give me the ability to
set the Name field to 'No Duplicates' and Access does not let me set the NO
DUP property since this is a linked table.
What code or settings is required to disallow duplicates in a no-key field
between Access and SQL?First of all, are you using a .mdb or .adp file
I think the best way to do it is use the DLookup function in Access on the
AFterUpdate Event...
Please look at DLookup in the Help file in MS Access
Ed
"SharonInGa" wrote:
> My table resides in a SQL database: It has two fields:
> 1. NameID - KEY 2. Name
> I want the user to have the ability to add new names in an Access form and
> disallow any names that already exist. SQL does not give me the ability to
> set the Name field to 'No Duplicates' and Access does not let me set the NO
> DUP property since this is a linked table.
> What code or settings is required to disallow duplicates in a no-key field
> between Access and SQL?
>|||What you seem to be looking for is a unique constraint. You
can find more information on this in SQL Server books
online. It's the same as the 'No Duplicates' in Access.
-Sue
On Thu, 20 Jan 2005 13:37:02 -0800, "SharonInGa"
<SharonInGa@.discussions.microsoft.com> wrote:
>My table resides in a SQL database: It has two fields:
>1. NameID - KEY 2. Name
>I want the user to have the ability to add new names in an Access form and
>disallow any names that already exist. SQL does not give me the ability to
>set the Name field to 'No Duplicates' and Access does not let me set the NO
>DUP property since this is a linked table.
>What code or settings is required to disallow duplicates in a no-key field
>between Access and SQL?