I have a couple of tables in my database (SQL server 2000)
1. Student -- Columns are as
"StudentID" -- INT
"StudentName" -- NVARCHAR
"DepartmentID" -- INT
2. Department -- Columns are as
"DepartmentID" -- INT
"DepartmentName" -- NVARCHAR
So in the table "Student", the field "DepartmentID" is a foreign key. Now I
tried out turning "Allow Null" on for the column "DepartmentID" in the
"Student" table. As soon as I did this, I was able to insert a row with
"DepartmentID" as <NULL>.
Now isn't this wrong? Or is it that the database overrides the checking of
the foreign key against the table "Department" when I check the "Allow Null"
option ON?
It would be really helpful if someone could elaborate on this.
Locate me in the blogosphere: http://spaces.msn.com/aayushpuriHi
Yes, referencing table may accept NULL's. Like people that don't have
children.
"Aayush Puri" <aayushpuri @. h o t m a i l . c o m> wrote in message
news:C5A2D39D-5C28-4661-B68B-B2CA6BA48CDF@.microsoft.com...
>I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
> I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow
> Null"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri|||Aayush Puri wrote:
> I have a couple of tables in my database (SQL server 2000)
> 1. Student -- Columns are as
> "StudentID" -- INT
> "StudentName" -- NVARCHAR
> "DepartmentID" -- INT
> 2. Department -- Columns are as
> "DepartmentID" -- INT
> "DepartmentName" -- NVARCHAR
> So in the table "Student", the field "DepartmentID" is a foreign key. Now
I
> tried out turning "Allow Null" on for the column "DepartmentID" in the
> "Student" table. As soon as I did this, I was able to insert a row with
> "DepartmentID" as <NULL>.
> Now isn't this wrong? Or is it that the database overrides the checking of
> the foreign key against the table "Department" when I check the "Allow Nul
l"
> option ON?
> It would be really helpful if someone could elaborate on this.
> --
> Locate me in the blogosphere: http://spaces.msn.com/aayushpuri
SQL's foreign key constraints work like check constraints.
Specifically, values are permitted as long as the constraint isn't
violated. A null comparison returns an UNKNOWN result rather than a
FALSE one and UNKNOWN isn't regarded as a violation of the constraint.
So the behaviour you mentioned is "right" according to the SQL
standard. Whether it makes good logical sense is a different matter.
I'd suggest that you either disallow nulls or that you create some
default value in the Depatrtments table to represent the case that you
would otherwise have used a null for - a "Not Applicable" department
for example.
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
--
Thursday, March 29, 2012
can we increment the column --autimaticallyis there any possiblity with tiggers
hi,
i am a beginner to ms sql server2000
i have a table
create table ddd (a int, b int)
by table structure is a b
now when i enter a value in b column suppose '2' in column b
bext time when i insert a value in the column a i have to get the value
in b as 3 is thi spossible with triggers
insert into gdg values (1,2)
a b
1 2
insert into gdg (a) values(2)
a b
2 3--> i have to get this 3 automatically
is there any method to get this
pls help me
satishis this homework?sql
i am a beginner to ms sql server2000
i have a table
create table ddd (a int, b int)
by table structure is a b
now when i enter a value in b column suppose '2' in column b
bext time when i insert a value in the column a i have to get the value
in b as 3 is thi spossible with triggers
insert into gdg values (1,2)
a b
1 2
insert into gdg (a) values(2)
a b
2 3--> i have to get this 3 automatically
is there any method to get this
pls help me
satishis this homework?sql
Can we include User Defined Functions in the Report Model
I have certain user defined functions in my database.
Can we include User Defined Functions in the Report Model.
Thank you,
Hi-
You can create custom expressions within a model using any combination of the supported semantic query functions in Model Designer. For example, you could create Employee.BirthWeek in a model by using the semantic query function Week(Employee.Birthday). The Employee.BirthWeek could then be consumed by reports through Report Builder.
However, you cannot create custom functions in code and reference them in a model. The underlying query translator would not be able to translate arbitrary code into T-SQL.
Hope that helps.
Thanks, Jon
Can we include a variable field value on the table header.
Can we include a variable field value on the table header.
I am doing a page break on every date change and also set the property of he table header to repeat on each page. By doing this ,I am able to get the table header on each page even on page break. I want the changed date to be displayed on the every page break and in the table header.
Please help.
You should be able to do this without issue. Assuming you have a field which you are pulling from that shows the date, you could do something like this as an expression in the table header.
="Report Header - " & Fields!Date.Value
Hope this helps.
Can we implement Pivot Tables using MSRS?
Can we implement Pivot Tables using Reporting Services 2005? I need to
allow user to select the pivot column at runtime. I mean dynamically
changing the criteria for pivot tables after report is rendered.
Thanks,
YogeshHi,
You can implement pivoting using matrix.
Amarnath.
"Yogi" wrote:
> Can we implement Pivot Tables using Reporting Services 2005? I need to
> allow user to select the pivot column at runtime. I mean dynamically
> changing the criteria for pivot tables after report is rendered.
> Thanks,
> Yogesh
>
allow user to select the pivot column at runtime. I mean dynamically
changing the criteria for pivot tables after report is rendered.
Thanks,
YogeshHi,
You can implement pivoting using matrix.
Amarnath.
"Yogi" wrote:
> Can we implement Pivot Tables using Reporting Services 2005? I need to
> allow user to select the pivot column at runtime. I mean dynamically
> changing the criteria for pivot tables after report is rendered.
> Thanks,
> Yogesh
>
Can we hide some parameters
There will be a pararmeters panels when we browse reports.
The user can modify the parameters there.
But there are some parameters I do not want the usrs to modify.
How can I hide these parameters of restrict user to modify?In the report manager go to the report properties and then to the Parameters
section. Specify a default value for the parameter and then delete the prompt
string (DO NOT uncheck the "Prompt user" check box)
Of course, the parameter value will always be the same, no matter which user
runs the report or what he/she does.
I hope this helps.
--
Please mark the correct/helpful answers!
The user can modify the parameters there.
But there are some parameters I do not want the usrs to modify.
How can I hide these parameters of restrict user to modify?In the report manager go to the report properties and then to the Parameters
section. Specify a default value for the parameter and then delete the prompt
string (DO NOT uncheck the "Prompt user" check box)
Of course, the parameter value will always be the same, no matter which user
runs the report or what he/she does.
I hope this helps.
--
Please mark the correct/helpful answers!
can we have temporary table in store procedure when using BCP ?
hi, good day,
when i run query in query analyzer
exec sp_test '2006-07-21'
it show result , however when i run it using BCP tools
exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c:\test.txt -c '
it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure
how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,
thank you :eek:Hmph. I would have thought that would work. Post the code for sp_test.|||thanks for reply, the query was constructed in this way
CREATE PROCEDURE [SP_TEST]
--------CREATE TEMP TABLE----------
SELECT
*
INTO #TMP_TBL1
FROM
TABLE A, TABLE B
SELECT
*
INTO #TMP_TBL2
FROM
TABLE C, TABLE D
----------BEGINNING OF PROCESS------
SELECT *
FROM
(
SELECT * FROM
#TMP_TBL1
UNION
#TMP_TBL2
)|||You are posting skeleton code. Post the actual code you are using, and the actual error message you receive.|||hi, blindman, sorry , i can't expose my company query and it would be few page query, but i use this simple store procedure to test on , it doesn't work as well
CREATE PROCEDURE [SP_TEST]
AS
SELECT *
INTO #TMP_CUSTOMER
FROM O_CUSTOMER
----------------------
SELECT DISTINCT
COUNT(CUST_CD)
FROM
#TMP_CUSTOMER
GO
when i run in query analyzer , using "exec SP_TEST" , it work
but when run
Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:\TEST.TXT -c -Slocalhost -Usa -Ppassword'
it give error "SQLState = S0002, NativeError = 208"
and show
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.
do u have any idea on it ?|||I tried you code and was able to duplicate your error. I tried a few work-arounds, but none of them were succesful. BCP seems to be failing to recognize any table created within the procedure, temporary or permanent.
I have not run into this before, but I have not used BCP very frequently. I am surprised, none the less.
Wait until Monday, and lets see if any other forum members recognize this error.|||Hi
Did a quick RTFM:
Copying Data To or From a Temporary Table
When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:
bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -PpasswordHowever, do not specify the database name when using global temporary tables because temporary tables exist only in tempdb. It is possible to use a local temporary table (for example, #temp_authors) only when bulk copying data using the BULK INSERT statement.The key point appears to be the last line. A rather interesting turn of phrase that says (in a roundabout way) - "you can't".
HTH
EDIT - in fact Nakata that is so poorly written (and assuming English isn't your first language - believe it or not it is my first language and I had to read it a couple of times) it basically says that local temp tables work with BULK INSERT but not (by implication) bcp.|||thanks pootle flump and blindman,
this is bad for me with the answer, i may need to re-write the whole query, my god , that really killing me ...any other alternative i can have if possible no need to re-write all query that involve temporary table :(|||Not a big user of bcp... I wonder if it has the same objection to Table variables...|||Hi,
This is a shortcoming of the way that bcp works, particularly when a stored procedure is used as the command for a "queryout" operation.
bcp needs to understand the format of the results it is about to receive, before it issues the query. If you use the "out" operation, giving a table name, it issues the following command:
SET FMTONLY ON
SELECT * FROM <table name>
SET FMTONLY OFF
If you use the "queryout" operator with an exec statement, it issues the following command:
SET FMTONLY ON
exec ...
SET FMTONLY OFF
Unfortunately, this technique only works with the simplest of stored procedures, where the format of the ouput can be determined WITHOUT ACTUALLY RUNNING THE CODE IN THE PROCEDURE. Your case does not work because #TMP_CUSTOMER does not exist unless you actually run the code.
FYI, a solution with a table variable WILL work, e.g.
<code>
create proc dbo.bills_test
as
begin
declare @.output table (col1... , col2 ... , ...)
insert @.output
select ...
select col1, col2 ...
from @.output
end
<\code>
you can play with this in query analyser, to get a stored proc that will work with bcp...
HTH,
Bill|||thanks everyone for contribution , i really learn a lot from this issue , i finally found that if i replace #tmp_tbl to tempdb..tmp_tbl , it work
will tempdb object will causing any performance issue , i really not sure ..
:beer:|||That may cause problems if more than one user executes your process simultaneously. You should consider using thompbil's suggestion and implementing a table variable.|||but I have not used BCP very frequently.
Oh, for the love of god....
In any case just put the results of the sproc into a table and bcp that out|||Oh, for the love of god....You got a problem wit dat, joisey-boy?|||tell me you use DTS...come on, you can tell us|||USE Northwind
GO
CREATE PROC mySproc99
AS
SET NOCOUNT ON
DECLARE @.myTabVar99 table (Col1 int)
INSERT INTO @.myTabVar99(Col1)
SELECT OrderId FROM Orders
SELECT * FROM @.myTabVar99
GO
EXEC mySproc99
exec master..xp_cmdshell 'bcp "exec Northwind.dbo.mySproc99" queryout c:\test.txt -c -T -S<servername>'
DROP PROC mySproc99
GO|||I do use DTS. To transfer data to staging tables. It's fine for migrating data. I don't do any processing within DTS.
DTS may not have the cache' of command-line utilities, then again DTS would have handled this poster's problem easily.sql
when i run query in query analyzer
exec sp_test '2006-07-21'
it show result , however when i run it using BCP tools
exec master..xp_cmdshell 'bcp "exec sp_test '2006-07-21' " queryout c:\test.txt -c '
it give me error which is something like "invalid object #temp_tbl",
i do using temporary table in store procedure
how to we solve it ? seem that bcp not support temporary table ? really need guidance from expert here ,
thank you :eek:Hmph. I would have thought that would work. Post the code for sp_test.|||thanks for reply, the query was constructed in this way
CREATE PROCEDURE [SP_TEST]
--------CREATE TEMP TABLE----------
SELECT
*
INTO #TMP_TBL1
FROM
TABLE A, TABLE B
SELECT
*
INTO #TMP_TBL2
FROM
TABLE C, TABLE D
----------BEGINNING OF PROCESS------
SELECT *
FROM
(
SELECT * FROM
#TMP_TBL1
UNION
#TMP_TBL2
)|||You are posting skeleton code. Post the actual code you are using, and the actual error message you receive.|||hi, blindman, sorry , i can't expose my company query and it would be few page query, but i use this simple store procedure to test on , it doesn't work as well
CREATE PROCEDURE [SP_TEST]
AS
SELECT *
INTO #TMP_CUSTOMER
FROM O_CUSTOMER
----------------------
SELECT DISTINCT
COUNT(CUST_CD)
FROM
#TMP_CUSTOMER
GO
when i run in query analyzer , using "exec SP_TEST" , it work
but when run
Exec Master..xp_CmdShell 'bcp "exec mydbtest..SP_TEST " queryout C:\TEST.TXT -c -Slocalhost -Usa -Ppassword'
it give error "SQLState = S0002, NativeError = 208"
and show
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#TMP_CUSTOMER'.
do u have any idea on it ?|||I tried you code and was able to duplicate your error. I tried a few work-arounds, but none of them were succesful. BCP seems to be failing to recognize any table created within the procedure, temporary or permanent.
I have not run into this before, but I have not used BCP very frequently. I am surprised, none the less.
Wait until Monday, and lets see if any other forum members recognize this error.|||Hi
Did a quick RTFM:
Copying Data To or From a Temporary Table
When using bcp or BULK INSERT to bulk copy data using a global temporary table, the table name must be specified at the command prompt, including initial number signs (##). For example, to bulk copy data from the global temporary table ##temp_authors to the Temp_authors.txt data file, execute at the command prompt:
bcp ##temp_authors out temp_authors.txt -c -Sservername -Usa -PpasswordHowever, do not specify the database name when using global temporary tables because temporary tables exist only in tempdb. It is possible to use a local temporary table (for example, #temp_authors) only when bulk copying data using the BULK INSERT statement.The key point appears to be the last line. A rather interesting turn of phrase that says (in a roundabout way) - "you can't".
HTH
EDIT - in fact Nakata that is so poorly written (and assuming English isn't your first language - believe it or not it is my first language and I had to read it a couple of times) it basically says that local temp tables work with BULK INSERT but not (by implication) bcp.|||thanks pootle flump and blindman,
this is bad for me with the answer, i may need to re-write the whole query, my god , that really killing me ...any other alternative i can have if possible no need to re-write all query that involve temporary table :(|||Not a big user of bcp... I wonder if it has the same objection to Table variables...|||Hi,
This is a shortcoming of the way that bcp works, particularly when a stored procedure is used as the command for a "queryout" operation.
bcp needs to understand the format of the results it is about to receive, before it issues the query. If you use the "out" operation, giving a table name, it issues the following command:
SET FMTONLY ON
SELECT * FROM <table name>
SET FMTONLY OFF
If you use the "queryout" operator with an exec statement, it issues the following command:
SET FMTONLY ON
exec ...
SET FMTONLY OFF
Unfortunately, this technique only works with the simplest of stored procedures, where the format of the ouput can be determined WITHOUT ACTUALLY RUNNING THE CODE IN THE PROCEDURE. Your case does not work because #TMP_CUSTOMER does not exist unless you actually run the code.
FYI, a solution with a table variable WILL work, e.g.
<code>
create proc dbo.bills_test
as
begin
declare @.output table (col1... , col2 ... , ...)
insert @.output
select ...
select col1, col2 ...
from @.output
end
<\code>
you can play with this in query analyser, to get a stored proc that will work with bcp...
HTH,
Bill|||thanks everyone for contribution , i really learn a lot from this issue , i finally found that if i replace #tmp_tbl to tempdb..tmp_tbl , it work
will tempdb object will causing any performance issue , i really not sure ..
:beer:|||That may cause problems if more than one user executes your process simultaneously. You should consider using thompbil's suggestion and implementing a table variable.|||but I have not used BCP very frequently.
Oh, for the love of god....
In any case just put the results of the sproc into a table and bcp that out|||Oh, for the love of god....You got a problem wit dat, joisey-boy?|||tell me you use DTS...come on, you can tell us|||USE Northwind
GO
CREATE PROC mySproc99
AS
SET NOCOUNT ON
DECLARE @.myTabVar99 table (Col1 int)
INSERT INTO @.myTabVar99(Col1)
SELECT OrderId FROM Orders
SELECT * FROM @.myTabVar99
GO
EXEC mySproc99
exec master..xp_cmdshell 'bcp "exec Northwind.dbo.mySproc99" queryout c:\test.txt -c -T -S<servername>'
DROP PROC mySproc99
GO|||I do use DTS. To transfer data to staging tables. It's fine for migrating data. I don't do any processing within DTS.
DTS may not have the cache' of command-line utilities, then again DTS would have handled this poster's problem easily.sql
Subscribe to:
Posts (Atom)