Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

foreign key

I want to make a foreign key relationship between two tables but the key is
multiple fields. I am getting an error message when I try.
'''''?Can you post DDL for your tables and the code you're trying to use to create
the foreign key constraint?
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> I want to make a foreign key relationship between two tables but the key
is
> multiple fields. I am getting an error message when I try.
> '''''?|||ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName)
Be sure to list the composite columnc in the same order.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23S3OJUNyEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Can you post DDL for your tables and the code you're trying to use to
> create
> the foreign key constraint?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
>> I want to make a foreign key relationship between two tables but the key
> is
>> multiple fields. I am getting an error message when I try.
>> '''''?
>|||Script and error message
ALTER TABLE MNP_MINE_PROD
ADD CONSTRAINT FK_TEST
FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'FK_TEST'. The conflict occurred in database 'S_DEV', table 'MOR_MINE_OP_RPT'.
"sql" wrote:
> I want to make a foreign key relationship between two tables but the key is
> multiple fields. I am getting an error message when I try.
> '''''?|||You have some rows in MNP_MINE_PROD that aren't in MOR_MINE_OP_RPT. So the
FK can't be created... try this:
SELECT *
FROM MNP_MINE_PROD A
WHERE NOT EXISTS
(SELECT *
FROM MOR_MINE_OP_RPT B
WHERE A.MNE_ID = B.MNE_ID
AND A.MOR_YEAR=B.MOR_YEAR
AND A.ORT_ID = B.ORT_ID)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:57509E6C-7118-4B0C-A0DF-B3EF4FCF9464@.microsoft.com...
> Script and error message
> ALTER TABLE MNP_MINE_PROD
> ADD CONSTRAINT FK_TEST
> FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
> REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_TEST'. The conflict occurred in database 'S_DEV', table
'MOR_MINE_OP_RPT'.
>
> "sql" wrote:
> > I want to make a foreign key relationship between two tables but the key
is
> > multiple fields. I am getting an error message when I try.
> > '''''?

Foreign Key

I would like to create a foreign key but the Primary table has 2 fields as it Primary Key. Is there a way to create a Foreign Key that links only on one field of the primary key.

Ex: table 1: id int , language char(2), description varchar(100) PK = ID + language

table 2 : id int, idlanguage int PK = id FK (idLanguage refers to id from table 1)

This cause an error because the foreign key does not include all part of the primary key.

Rufen

If your table1.id is unique, then create primary key only on that column, if not, then you should add laguage in your table2 column because there will be no way to distinguish between languages that have the same id.
|||

I know that there will be no way to distinguish all records that have the same id, but that is what I want. When I delete a record from Table 1, I want to delete all record from table 2 that have this id (foreign from table 1).

|||

You can implement the foreign key logic using triggers.

For eg. For Delete

CREATE TRIGGER trg

ON table1

FOR DELETE

AS

BEGIN

DELETE FROM table2

WHERE idlanguage in (SELECT id FROM deleted)

END

You can have similar trigger for insert and update

sql

foreign key

I want to make a foreign key relationship between two tables but the key is
multiple fields. I am getting an error message when I try.
??????
Can you post DDL for your tables and the code you're trying to use to create
the foreign key constraint?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"sql" <sql@.discussions.microsoft.com> wrote in message
news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> I want to make a foreign key relationship between two tables but the key
is
> multiple fields. I am getting an error message when I try.
> ??????
|||ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName)
Be sure to list the composite columnc in the same order.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23S3OJUNyEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Can you post DDL for your tables and the code you're trying to use to
> create
> the foreign key constraint?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> is
>
|||Script and error message
ALTER TABLE MNP_MINE_PROD
ADD CONSTRAINT FK_TEST
FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'FK_TEST'. The conflict occurred in database 'S_DEV', table 'MOR_MINE_OP_RPT'.
"sql" wrote:

> I want to make a foreign key relationship between two tables but the key is
> multiple fields. I am getting an error message when I try.
> ??????
|||You have some rows in MNP_MINE_PROD that aren't in MOR_MINE_OP_RPT. So the
FK can't be created... try this:
SELECT *
FROM MNP_MINE_PROD A
WHERE NOT EXISTS
(SELECT *
FROM MOR_MINE_OP_RPT B
WHERE A.MNE_ID = B.MNE_ID
AND A.MOR_YEAR=B.MOR_YEAR
AND A.ORT_ID = B.ORT_ID)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"sql" <sql@.discussions.microsoft.com> wrote in message
news:57509E6C-7118-4B0C-A0DF-B3EF4FCF9464@.microsoft.com...
> Script and error message
> ALTER TABLE MNP_MINE_PROD
> ADD CONSTRAINT FK_TEST
> FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
> REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_TEST'. The conflict occurred in database 'S_DEV', table
'MOR_MINE_OP_RPT'.[vbcol=seagreen]
>
> "sql" wrote:
is[vbcol=seagreen]

foreign key

I want to make a foreign key relationship between two tables but the key is
multiple fields. I am getting an error message when I try.
'''''?Can you post DDL for your tables and the code you're trying to use to create
the foreign key constraint?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> I want to make a foreign key relationship between two tables but the key
is
> multiple fields. I am getting an error message when I try.
> '''''?|||ALTER TABLE SecondaryTableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ForeignKeyColumns)
REFERENCES dbo.PrimaryTable (PrimaryKeyColumnName)
Be sure to list the composite columnc in the same order.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
Enterprise Data Architect, www.Compassion.com
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23S3OJUNyEHA.2788@.TK2MSFTNGP15.phx.gbl...
> Can you post DDL for your tables and the code you're trying to use to
> create
> the foreign key constraint?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:186C8701-6A4E-4C68-81E7-29EB3A668400@.microsoft.com...
> is
>|||Script and error message
ALTER TABLE MNP_MINE_PROD
ADD CONSTRAINT FK_TEST
FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
Server: Msg 547, Level 16, State 1, Line 1
ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
'FK_TEST'. The conflict occurred in database 'S_DEV', table 'MOR_MINE_OP_RPT
'.
"sql" wrote:

> I want to make a foreign key relationship between two tables but the key i
s
> multiple fields. I am getting an error message when I try.
> '''''?|||You have some rows in MNP_MINE_PROD that aren't in MOR_MINE_OP_RPT. So the
FK can't be created... try this:
SELECT *
FROM MNP_MINE_PROD A
WHERE NOT EXISTS
(SELECT *
FROM MOR_MINE_OP_RPT B
WHERE A.MNE_ID = B.MNE_ID
AND A.MOR_YEAR=B.MOR_YEAR
AND A.ORT_ID = B.ORT_ID)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"sql" <sql@.discussions.microsoft.com> wrote in message
news:57509E6C-7118-4B0C-A0DF-B3EF4FCF9464@.microsoft.com...
> Script and error message
> ALTER TABLE MNP_MINE_PROD
> ADD CONSTRAINT FK_TEST
> FOREIGN KEY (MNE_ID, MOR_YEAR, ORT_ID)
> REFERENCES MOR_MINE_OP_RPT (MNE_ID, MOR_YEAR, ORT_ID)
> Server: Msg 547, Level 16, State 1, Line 1
> ALTER TABLE statement conflicted with TABLE FOREIGN KEY constraint
> 'FK_TEST'. The conflict occurred in database 'S_DEV', table
'MOR_MINE_OP_RPT'.[vbcol=seagreen]
>
> "sql" wrote:
>
is[vbcol=seagreen]sql

foreign characters are not being imported into the table correctly

hello everyone,

i have few fields that contain foreign characters with diacritic marks which are not getting imported correctly.

below is the import format:

- File type: ASCII
- Row delimiter: carriage return and line feed {CR/LF}
- Column delimiter: Tab
- Text qualifier: None

Please advice.

Here is the errors i'm getting:

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Country_str_local_long_name" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Country_str_local_long_name" (37)" failed because truncation occurred, and the truncation row disposition on "output column "Country_str_local_long_name" (37)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "L:\Country.txt" on data row 6.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - Country_txt" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

Hi,

Have you set the properties for the locale and the default code page?

If you're using a Flat File source, these properties are available in the Flat File Connection Manager Editor dialog box. You open this dialog box by double clicking the Flat File Source control, and then clicking New in the Flat File Source Editor dialog box.

sql

Force prompt when using default parameters

I wrote an sp and assigned static values to fields for easy testing, after
that I created the parameters and assigned default values to them, now
everytime I click preview I don't get a prompt for new values, the report
automatically runs with the defaults.
I want to be able to use the default values most of the time but also want
the option to change them if needed. The only option is to stop the report
and enter different values. This is strange because I have other reports that
have all default values but give me a prompt in preview.Someone answered this a few ones back.
Add a boolean "Run Report" parameter to the report with no default.
-T
"Ash_Cat" <AshCat@.discussions.microsoft.com> wrote in message
news:3974BD37-7DCD-4FBF-8602-F660A7ADE92C@.microsoft.com...
>I wrote an sp and assigned static values to fields for easy testing, after
> that I created the parameters and assigned default values to them, now
> everytime I click preview I don't get a prompt for new values, the report
> automatically runs with the defaults.
> I want to be able to use the default values most of the time but also want
> the option to change them if needed. The only option is to stop the report
> and enter different values. This is strange because I have other reports
> that
> have all default values but give me a prompt in preview.|||Cool! Thx Tim
"Tim Dot NoSpam" wrote:
> Someone answered this a few ones back.
> Add a boolean "Run Report" parameter to the report with no default.
> -T
> "Ash_Cat" <AshCat@.discussions.microsoft.com> wrote in message
> news:3974BD37-7DCD-4FBF-8602-F660A7ADE92C@.microsoft.com...
> >I wrote an sp and assigned static values to fields for easy testing, after
> > that I created the parameters and assigned default values to them, now
> > everytime I click preview I don't get a prompt for new values, the report
> > automatically runs with the defaults.
> > I want to be able to use the default values most of the time but also want
> > the option to change them if needed. The only option is to stop the report
> > and enter different values. This is strange because I have other reports
> > that
> > have all default values but give me a prompt in preview.
>
>

Force fields upper-case

Almost all of our character fields are stored in upper-case. Is there an easy way to force SQL Server char and varchar fields to upper-case? Something I can do in SQL Server instead of in the client? It needs to apply to any new records.

There are some exceptions (email addresses for one). I don't mind going through each field and changing something.

Thanks!

You could define an INSTEAD OF Insert trigger, and apply the UPPER() function to the columns you want in upper case.|||

Dale,

Is there a way to INSERT INTO <mytable> all fields, but also force the text ones to uppercase? I'm not sure how to do it without listing each field individually.

Brian

|||

I know, tedious.

I thought maybe COLLATE would provide something, but I've not been able to find an answer through that either.

For XML Fields Missing

SQL Server 2000 Enterprise Edition SP3a

Windows 2003 Enterprise Edition

I am trying to export a subset of a table to XML (using the where clause) and I am not getting all fields exported. I have tried using Auto, Raw, and Explicit and they all omit these fields. Here is my schema:

CREATE TABLE [dbo].[TableName] (
[TableID] [uniqueidentifier] NOT NULL ,
[Field01] [char] (6) NOT NULL ,
[Field02] [char] (4) NOT NULL ,
[Field03] [int] NOT NULL ,
[Field04] [varchar] (50) NOT NULL ,
[Field05] [char] (4) NULL ,
[Field06] [smalldatetime] NOT NULL ,
[Field07] [smallint] NULL ,
[Field08] [tinyint] NULL ,
[Field09] [tinyint] NULL ,
[Field10] [tinyint] NULL ,
[Field11] [smalldatetime] NULL ,
[Field12] [smalldatetime] NULL ,
[Field13] [money] NULL ,
[Field14] [money] NULL ,
[Field15] [smalldatetime] NULL ,
[Field16] [varchar] (50) ,
[Field17] [money] NULL ,
[Field18] [money] NULL ,
[Field19] [bit] NULL
) ON [PRIMARY]

When the XML is generated, I am not getting Field07, Field08, Field09, Field10, Field12, Field13, Field14, Field15, and Field16.

Here is my query:

CREATE PROCEDURE dbasp_get_TableData

(@.TableID uniqueidentifier) AS

SELECT
TableID, Field01, Field02, Field03, Field04, Field05, Field06, Field07,
Field08, Field09, Field10, Field11, Field12,
Field13, Field14, Field15, Field16,
Field17, Field18, Field19

FROM database.dbo.TableName

WHERE tableid = @.TableID

FOR XML AUTO, ELEMENTS

I still get the same problem if I do a Select * statement. All fields have data, so NULL cannot be an issue.

Please help!!! It does not make sense.

Thank you in advance.

I couldn't repro your problem. Following is my repro and result. Please provide your repro script and the version of your SQL 2000.

use tempdb
go

CREATE TABLE [dbo].[TableName] (
[TableID] [uniqueidentifier] NOT NULL ,
[Field01] [char] (6) NOT NULL ,
[Field02] [char] (4) NOT NULL ,
[Field03] [int] NOT NULL ,
[Field04] [varchar] (50) NOT NULL ,
[Field05] [char] (4) NULL ,
[Field06] [smalldatetime] NOT NULL ,
[Field07] [smallint] NULL ,
[Field08] [tinyint] NULL ,
[Field09] [tinyint] NULL ,
[Field10] [tinyint] NULL ,
[Field11] [smalldatetime] NULL ,
[Field12] [smalldatetime] NULL ,
[Field13] [money] NULL ,
[Field14] [money] NULL ,
[Field15] [smalldatetime] NULL ,
[Field16] [varchar] (50) ,
[Field17] [money] NULL ,
[Field18] [money] NULL ,
[Field19] [bit] NULL
) ON [PRIMARY]
go

insert TableName values(newid(), 'abc', 'def', 3, '04', '05', getdate(), 7, 8, 9, 10, getdate(), getdate(), 13.1, 14.5, getdate(), '16varchar', 17.3, 18.5, 1)
go

CREATE PROCEDURE dbasp_get_TableData

(@.TableID uniqueidentifier) AS

SELECT
TableID, Field01, Field02, Field03, Field04, Field05, Field06, Field07,
Field08, Field09, Field10, Field11, Field12,
Field13, Field14, Field15, Field16,
Field17, Field18, Field19

FROM tempdb.dbo.TableName

WHERE tableid = @.TableID

FOR XML AUTO, ELEMENTS
go

exec dbasp_get_TableData '9883A32F-3EF4-408F-BFEC-6540598FEBC1'

Result I get:

==================

<tempdb.dbo.TableName><TableID>9883A32F-3EF4-408F-BFEC-6540598FEBC1</TableID><Field01>abc </Field01><Field02>def </Field02><Field03>3</Field03><Field04>04</Field04><Field05>05 </Field05><Field06>2007-01-16T10:21:00</Field06><Field07>7</Field07><Field08>8</Field08><Field09>9</Field09><Field10>10</Field10><Field11>2007-01-16T10:21:00</Field11><Field12>2007-01-16T10:21:00</Field12><Field13>13.1000</Field13><Field14>14.5000</Field14><Field15>2007-01-16T10:21:00</Field15><Field16>16varchar</Field16><Field17>17.3000</Field17><Field18>18.5000</Field18><Field19>1</Field19></tempdb.dbo.TableName>

|||I did find where the problem was coming from. I reproduced the same result as your example. Somehow I had changed the GUID that was used when I knew that all fields were accounted for (not null) to a GUID that had several NULL values. Those fields don't get exported (not even the field names) when it is null. I have been able to get my code to work by utilizing ISNULL. Thank you for your time in helping me.

For XML Fields Missing

SQL Server 2000 Enterprise Edition SP3a

Windows 2003 Enterprise Edition

I am trying to export a subset of a table to XML (using the where clause) and I am not getting all fields exported. I have tried using Auto, Raw, and Explicit and they all omit these fields. Here is my schema:

CREATE TABLE [dbo].[TableName] (
[TableID] [uniqueidentifier] NOT NULL ,
[Field01] [char] (6) NOT NULL ,
[Field02] [char] (4) NOT NULL ,
[Field03] [int] NOT NULL ,
[Field04] [varchar] (50) NOT NULL ,
[Field05] [char] (4) NULL ,
[Field06] [smalldatetime] NOT NULL ,
[Field07] [smallint] NULL ,
[Field08] [tinyint] NULL ,
[Field09] [tinyint] NULL ,
[Field10] [tinyint] NULL ,
[Field11] [smalldatetime] NULL ,
[Field12] [smalldatetime] NULL ,
[Field13] [money] NULL ,
[Field14] [money] NULL ,
[Field15] [smalldatetime] NULL ,
[Field16] [varchar] (50) ,
[Field17] [money] NULL ,
[Field18] [money] NULL ,
[Field19] [bit] NULL
) ON [PRIMARY]

When the XML is generated, I am not getting Field07, Field08, Field09, Field10, Field12, Field13, Field14, Field15, and Field16.

Here is my query:

CREATE PROCEDURE dbasp_get_TableData

(@.TableID uniqueidentifier) AS

SELECT
TableID, Field01, Field02, Field03, Field04, Field05, Field06, Field07,
Field08, Field09, Field10, Field11, Field12,
Field13, Field14, Field15, Field16,
Field17, Field18, Field19

FROM database.dbo.TableName

WHERE tableid = @.TableID

FOR XML AUTO, ELEMENTS

I still get the same problem if I do a Select * statement. All fields have data, so NULL cannot be an issue.

Please help!!! It does not make sense.

Thank you in advance.

I couldn't repro your problem. Following is my repro and result. Please provide your repro script and the version of your SQL 2000.

use tempdb
go

CREATE TABLE [dbo].[TableName] (
[TableID] [uniqueidentifier] NOT NULL ,
[Field01] [char] (6) NOT NULL ,
[Field02] [char] (4) NOT NULL ,
[Field03] [int] NOT NULL ,
[Field04] [varchar] (50) NOT NULL ,
[Field05] [char] (4) NULL ,
[Field06] [smalldatetime] NOT NULL ,
[Field07] [smallint] NULL ,
[Field08] [tinyint] NULL ,
[Field09] [tinyint] NULL ,
[Field10] [tinyint] NULL ,
[Field11] [smalldatetime] NULL ,
[Field12] [smalldatetime] NULL ,
[Field13] [money] NULL ,
[Field14] [money] NULL ,
[Field15] [smalldatetime] NULL ,
[Field16] [varchar] (50) ,
[Field17] [money] NULL ,
[Field18] [money] NULL ,
[Field19] [bit] NULL
) ON [PRIMARY]
go

insert TableName values(newid(), 'abc', 'def', 3, '04', '05', getdate(), 7, 8, 9, 10, getdate(), getdate(), 13.1, 14.5, getdate(), '16varchar', 17.3, 18.5, 1)
go

CREATE PROCEDURE dbasp_get_TableData

(@.TableID uniqueidentifier) AS

SELECT
TableID, Field01, Field02, Field03, Field04, Field05, Field06, Field07,
Field08, Field09, Field10, Field11, Field12,
Field13, Field14, Field15, Field16,
Field17, Field18, Field19

FROM tempdb.dbo.TableName

WHERE tableid = @.TableID

FOR XML AUTO, ELEMENTS
go

exec dbasp_get_TableData '9883A32F-3EF4-408F-BFEC-6540598FEBC1'

Result I get:

==================

<tempdb.dbo.TableName><TableID>9883A32F-3EF4-408F-BFEC-6540598FEBC1</TableID><Field01>abc </Field01><Field02>def </Field02><Field03>3</Field03><Field04>04</Field04><Field05>05 </Field05><Field06>2007-01-16T10:21:00</Field06><Field07>7</Field07><Field08>8</Field08><Field09>9</Field09><Field10>10</Field10><Field11>2007-01-16T10:21:00</Field11><Field12>2007-01-16T10:21:00</Field12><Field13>13.1000</Field13><Field14>14.5000</Field14><Field15>2007-01-16T10:21:00</Field15><Field16>16varchar</Field16><Field17>17.3000</Field17><Field18>18.5000</Field18><Field19>1</Field19></tempdb.dbo.TableName>

|||I did find where the problem was coming from. I reproduced the same result as your example. Somehow I had changed the GUID that was used when I knew that all fields were accounted for (not null) to a GUID that had several NULL values. Those fields don't get exported (not even the field names) when it is null. I have been able to get my code to work by utilizing ISNULL. Thank you for your time in helping me.

Footer with dynamic values

In my report I need footer with fixed lenght anchored to a bottom. The
problem is what I need some dynamic fields (report values) in footer. How
can I manage this?I found answer by myself. :)
Who interested in problem read
http://msdn2.microsoft.com/en-us/library/ms159677
"gNM" <gedukas99@.hotmail.com> wrote in message
news:eaN8qB80FHA.3756@.tk2msftngp13.phx.gbl...
> In my report I need footer with fixed lenght anchored to a bottom. The
> problem is what I need some dynamic fields (report values) in footer. How
> can I manage this?
>