mapping XML data to variable

I can’t figure out how to map xml data stored in a table to a variable in integration service.

For example:
I would like to use a “for each loop container” to iterate through a row set selected from database. Each row has three columns, an integer, a string and an xml data. In the variable mappings, I can map the integer column and the string column to a variable with type of int and a variable with type of string. But I am having trouble to map the xml data column to any variable. I tried using either a string variable or object. It always reports error like “variable mapping number X to variable XXX can’t apply”.

Any help?

This is a supported scenario. Ensure that:

The column is actually being loaded into the record set: Check the column mappings in the recordset dest The value being mapped to the variable is less than 4000 characters long: select max(datalength(xmlCol)) from XmlTable

mapping Windows credentials to access linked server

Hi all,
I have linked SQL Server "SRV2" to SQL Server "SRV1" through
sp_addlinkedserver.
In my scenario, Windows user "U1" has access to "SRV1" while Windows
user "U2" has access to "SRV2".
Whenever I access "SRV1" as "U1" and execute a distributed query which
involves "SRV2", I would like "U1" to be mapped to "U2" for accessing
"SRV2".
Does anyone know whether this is possible and how?
I know that I can pass-through "U1" credentials to "SRV2" with
delegation and the default mapping, or map "U1" to a SQL User "sqlU2"
that can access "SRV2".
However what I would like to do is to map Windows user "U1" to Windows
user "U2".
I am using SQL Server 2005 which comes with Visual Studio Beta2.
Thanks in advance for any help,
-GianlucaHi
As fas as I know you will have to create a new login in "SRV2". with the
same permissions.
"Gianluca Torta" <giatorta@.hotmail.com> wrote in message
news:1121206171.989233.19240@.f14g2000cwb.googlegroups.com...
> Hi all,
> I have linked SQL Server "SRV2" to SQL Server "SRV1" through
> sp_addlinkedserver.
> In my scenario, Windows user "U1" has access to "SRV1" while Windows
> user "U2" has access to "SRV2".
> Whenever I access "SRV1" as "U1" and execute a distributed query which
> involves "SRV2", I would like "U1" to be mapped to "U2" for accessing
> "SRV2".
> Does anyone know whether this is possible and how?
> I know that I can pass-through "U1" credentials to "SRV2" with
> delegation and the default mapping, or map "U1" to a SQL User "sqlU2"
> that can access "SRV2".
> However what I would like to do is to map Windows user "U1" to Windows
> user "U2".
> I am using SQL Server 2005 which comes with Visual Studio Beta2.
> Thanks in advance for any help,
> -Gianluca
>

Mapping Variables to Resultsets

Is there a problem mapping variables to resultsets with a bigint as datatype?
I've tried using int64, and others, but all fail except for object datatype.
I cant use object datatype because i wont be able to use it as a derived column.

Any ideas here?

Is this a bug in the current SSIS (bigint casting to variable)?
If i modified the table structure to int and the variable to int32, it works fine.|||Is this on Windows 2000, by any chance?|||It's on Windows XP.
Thanks.|||

Big integers are returned now as strings. So if you choose your variable as a string variable you should be able to get the result correctly. This will probably change. soon.

|||This no longer seems to be the case and only maps to "object." Why is this and when will it be fixed?|||I submitted a Connect bug on this some time ago (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=260967), and they've indicated that it was on the work list for Katmai (SQL Server 2008). I just tested against the November CTP and selecting a bigint column and storing it in an Int64 variable works.

Mapping Variables to Resultsets

Is there a problem mapping variables to resultsets with a bigint as datatype?
I've tried using int64, and others, but all fail except for object datatype.
I cant use object datatype because i wont be able to use it as a derived column.

Any ideas here?

Is this a bug in the current SSIS (bigint casting to variable)?
If i modified the table structure to int and the variable to int32, it works fine.
|||Is this on Windows 2000, by any chance?|||It's on Windows XP.
Thanks.
|||

Big integers are returned now as strings. So if you choose your variable as a string variable you should be able to get the result correctly. This will probably change. soon.

|||This no longer seems to be the case and only maps to "object." Why is this and when will it be fixed?sql

Mapping User Defined Data Type to Base Data Type

Hi,

I am trying to map a user-defined datatype to it's base data type in SQL Server 2000/2005. Let's say I have created a udt named ssn which is actually a char datatype with length 9. I need a query that would map the udt with the base datatype and give the typename of both. I have been using the sys.types table but I still can't see the link. Any help would be appreciated.

Thanks

Here is an Information_Schema view that I created a long time ago in my databases. Most of it I actually copied from a sql 2000 system sproc. You should be able to use it to construct what you need:

SELECT TOP 100 PERCENT
*,
ColumnName + ' ' +
UsedDataType +
CASE WHEN UserDefinedDataType IS NULL THEN
CASE WHEN collationID IS NOT NULL THEN
'('+CAST(CharacterMaxLength AS VARCHAR(10))+')'
ELSE ''
END
ELSE
''
END
AS ColumnDefinition,
ColumnName + ' ' +
UPPER(basedatatype) +
CASE WHEN collationID IS NOT NULL THEN '('+CAST(CharacterMaxLength AS VARCHAR(10))+')' ELSE '' END
AS ColumnDefinition2,
UsedDataType +
CASE WHEN UserDefinedDataType IS NULL THEN
CASE WHEN collationID IS NOT NULL THEN
'('+CAST(CharacterMaxLength AS VARCHAR(10))+')'
ELSE ''
END
ELSE
''
END
AS DefinedDataType
FROM
(
SELECT
DB_NAME() AS DatabaseName,
CASE obj.xtype WHEN 'U' THEN 'TABLE' WHEN 'V' THEN 'VIEW' WHEN 'P' THEN 'PROCEDURE' END AS ObjectType,
USER_NAME(obj.uid) AS TableSchema,
obj.name AS TableName,
col.name AS ColumnName,
col.colid AS ColumnPosition,
com.text AS DefaultValue,
CASE col.isnullable WHEN 1 THEN 'YES' ELSE 'NO' end AS IsNullable,
spt_dtp.LOCAL_TYPE_NAME AS BaseDataType,
CASE WHEN typ.xusertype > 256 THEN typ.name ELSE UPPER(typ.name) END AS UsedDataType,
CONVERT(INT, OdbcPrec(col.xtype, col.length, col.xprec) + spt_dtp.charbin) AS CharacterMaxLength,
NULLIF(col.xprec, 0) AS NumericPrecision,
col.scale AS NumericScale,
CONVERT(SYSNAME, CASE WHEN typ.xusertype > 256 THEN typ.name ELSE NULL END) AS UserDefinedDataType,
OBJECT_NAME(cdefault) AS ColumnDefaultName ,
typ.CollationID
FROM
sysobjects obj,
master.dbo.spt_datatype_info spt_dtp,
systypes typ,
syscolumns col
LEFT OUTER JOIN syscomments com on col.cdefault = com.id AND com.colid = 1,
master.dbo.syscharsets a_cha
WHERE
obj.id = col.id AND
typ.xtype = spt_dtp.ss_dtype AND
(spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2) AND
obj.xtype in ('U', 'V', 'P') AND
col.xusertype = typ.xusertype AND
(
spt_dtp.AUTO_INCREMENT IS NULL OR spt_dtp.AUTO_INCREMENT = 0) AND
a_cha.id = ISNULL(CONVERT(TINYINT, CollationPropertyFromID(col.collationid, 'sqlcharset')),
CONVERT(TINYINT, ServerProperty('sqlcharset'))
) and obj.type = 'u'

) a
ORDER BY
TableName, ColumnPosition ASC

Mapping UDF Parameters to Variables

As mentioned in a previous posting, I have an in-line table valued UDF with three input parameters. I can set this up as an OLEDB Datasource SQL Command Text with parameter markers (i.e. "?") and test it successfully in the Generic Query Builder. The parameter markers are correctly associated with the input parameters of the UDF and the parameters can be entered at execution time into a parameters table.

So near and yet so far. When I attempt to map the parameter markers with Package Variables there is an error message saying the the parameter details cannot be retrieved from the function. If the function was in a foriegn (e.g. Oracle) database I might accept this as just one of those things but this is a SQL 2005 database and compatability should be complete. Add to this that the Generic Query Builder has no problem with the same UDF and nor does Reporting Services and I have to assume that this is a bug plain and simple.

The only solution that I have seen suggested is to embed the SQL Command text in a Package Variable and change it at execution time but I regard this as a second rate solution.

Dick,

This doesn't help you, but I think there IS a bug with the OLEDB data source and passing variables. I have exactly the same issue with a simple query to a Microsoft FoxPro data source (which I've brought to this forum before and got no solution).

I went with the package variable solution - it's not as elegant but it works

Do you know where we should post bug reports for SQL 2005?

Rich

|||

Thanks Rich,

It's some consolation the hear from someone else about the problem. Foxpro is a "foriegn" application (albiet a Microsoft one). It's the fact that the problem occurs within SQL Server 2005 that is a bit of a surprise. Add to this the fact that Reporting Services seems to handle UDF parameters correctly (indicating that there is no reason why it shouldn't work) and it is a bit frustrating.

As you say contructing the entire SQL Command Text as a Package Variable is a work around but this is not really what was intended, Mapping Package Variables to parameter markers is more within the spirit of SSIS and much nicer. It seems to work with Stored Procedures for example.

My company has links with MS so I will try to find out on Monday how to officially report this.

Best regards,

Dick Campbell

Mapping two schema definitions to one column

Hi,
I have a xml data file that looks like this
<CT_DATASET>
<DATE-INTERVENTIONDATE>
<YEARS>0</YEARS>
<MONTHS>0</MONTHS>
<DAYS>-1</DAYS>
</DATE-INTERVENTIONDATE>
<DESCTEXT>Preoperative CT Diagnosis</DESCTEXT>
<NUMBER_OF_SLICES>58</NUMBER_OF_SLICES>
<DICOM_TAGS>
<TAGNR>00080014</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080016</TAGNR>
<TAGVALUE>1.2.840.10008.5.1.4.1.1.4</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080018</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102.0.2187.8.1.0.7</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080070</TAGNR>
<TAGVALUE>Toshiba </TAGVALUE>
</DICOM_TAGS>
</CT_DATASET>
And I want to map the multiple tagnr and tagvalue to one column for the
patient.
My schema file looks liek this:
<xs:element name="CT_DATASET" sql:relation="CT_DATA"
sql:overflow-field="ADDITIONAL">
<xs:complexType>
<xs:sequence>
<xs:element ref="DATE-INTERVENTIONDATE" />
<xs:element ref="DESCTEXT" />
<xs:element ref="NUMBER_OF_SLICES" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="DICOM_TAGS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="NUMBER_OF_SLICES" type="xs:string" />
<xs:element name="DICOM_TAGS" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DATE-INTERVENTIONDATE" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="YEARS" />
<xs:element ref="MONTHS" />
<xs:element ref="DAYS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="YEARS" type="xs:string" sql:field="years"/>
<xs:element name="MONTHS" type="xs:string" sql:field="months" />
<xs:element name="DAYS" type="xs:string" sql:field="days"/>
<xs:element name="TAGNR" sql:field="TAG_NO:"/>
<xs:element name="TAGVALUE" sql:field="TAG VALUE"/>
</xs:schema>
I get an error which says:
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Does anybody know a way around. Any help would be appreciated. Please
do help!!!Hello Prakruthi,
Are you trying to use this as a validation schema in an XML Schema collectio
n
or a XDR for mapping queries?

> I get an error which says:
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Does anybody know a way around. Any help would be appreciated. Please
> do help!!!
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
Im trying to use this as a validation schema. And as you can see from
the xml data , each patient has multiple dicom tags and numbers. I want
to be able to map all these numbers into a single column. Is this
possible?|||Hi Kent,
I forgot to mention that Tag Numbers and Tag values are two separate
columns. But each column should be able to have multiple values.
Regards,
Prakruthi|||Hello Prakruthi,
Part of problem is that the schema you posted isn't a valid XSD schema:
a. The sql namespace (aliased by sql:) isn't defined by your schema.
b. The schema has some other problems, so I rewrote it -- sans the SQL parts
since I'm not sure what these are or do in your processor.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="q
ualified">
<xs:element name="CT_DATASET">
<xs:complexType>
<xs:sequence>
<xs:element name="DATE-INTERVENTIONDATE">
<xs:complexType>
<xs:sequence>
<xs:element name="YEARS" type="xs:string"/>
<xs:element name="MONTHS" type="xs:string"/>
<xs:element name="DAYS" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="NUMBER_OF_SLICES" type="xs:string"/>
<xs:element ref="DICOM_TAGS" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DICOM_TAGS">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="TAGNR" type="xs:string"/>
<xs:element name="TAGVALUE" type="xs:string"/>
</xs:schema>
I was able to catalog is into a SQL Sever 2005 XML Schema Collection succesf
ully.
Does any of that help?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Dear Kent,
Thanks for reorganising my schema in the right manner. But I dont think
you understood what I meant to say. Even when I used your modified
schema I could not map the three tagnr and tagvalues associated with
one one single patient into the tagnr and tagvalue column.I get an
error that says
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Is it not possible to map three different values to one column of the
same row? I hope you can help me out again this time.
Regards,
Prakruthi|||Hello Prakruthi,
That's why I asked if you were using this an mapping schema or as a validati
on
schema. You said "validation," so I fixed it for that. However, I don't beli
eve
you can target more than one value into a column using a single mapping.
Sadly, I don't have the time to work on this today before going on the road
for the next couple of w. However, it looks like Michael is back online,
so he might be able to help more.:)

> Thanks for reorganising my schema in the right manner. But I dont
> think
> you understood what I meant to say. Even when I used your modified
> schema I could not map the three tagnr and tagvalues associated with
> one one single patient into the tagnr and tagvalue column.I get an
> error that says
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Is it not possible to map three different values to one column of the
> same row? I hope you can help me out again this time.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi|||I have forwarded it to our mapping schema experts and I hope they will get
back to you.
Ping me if that is not the case.
Michael
"Prakruthi" <prakruthi.rao@.gmail.com> wrote in message
news:1143210046.980577.250430@.e56g2000cwe.googlegroups.com...
> Hi Kent,
> I wanted to say im not using it as a validation schema. I apologise for
> the typo error. I hope micheal would help me out with this issue.
> Regards,
> Prakruthi
>

Mapping two schema definitions to one column

Hi,
I have a xml data file that looks like this
<CT_DATASET>
<DATE-INTERVENTIONDATE>
<YEARS>0</YEARS>
<MONTHS>0</MONTHS>
<DAYS>-1</DAYS>
</DATE-INTERVENTIONDATE>
<DESCTEXT>Preoperative CT Diagnosis</DESCTEXT>
<NUMBER_OF_SLICES>58</NUMBER_OF_SLICES>
<DICOM_TAGS>
<TAGNR>00080014</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080016</TAGNR>
<TAGVALUE>1.2.840.10008.5.1.4.1.1.4</TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080018</TAGNR>
<TAGVALUE>1.2.840.113701.4.2.102.0.2187.8.1.0.7< /TAGVALUE>
</DICOM_TAGS>
<DICOM_TAGS>
<TAGNR>00080070</TAGNR>
<TAGVALUE>Toshiba </TAGVALUE>
</DICOM_TAGS>
</CT_DATASET>
And I want to map the multiple tagnr and tagvalue to one column for the
patient.
My schema file looks liek this:
<xs:element name="CT_DATASET" sql:relation="CT_DATA"
sql:overflow-field="ADDITIONAL">
<xs:complexType>
<xs:sequence>
<xs:element ref="DATE-INTERVENTIONDATE" />
<xs:element ref="DESCTEXT" />
<xs:element ref="NUMBER_OF_SLICES" />
<xs:element minOccurs="0" maxOccurs="unbounded" ref="DICOM_TAGS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="NUMBER_OF_SLICES" type="xs:string" />
<xs:element name="DICOM_TAGS" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DATE-INTERVENTIONDATE" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element ref="YEARS" />
<xs:element ref="MONTHS" />
<xs:element ref="DAYS" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="YEARS" type="xs:string" sql:field="years"/>
<xs:element name="MONTHS" type="xs:string" sql:field="months" />
<xs:element name="DAYS" type="xs:string" sql:field="days"/>
<xs:element name="TAGNR" sql:field="TAG_NO:"/>
<xs:element name="TAGVALUE" sql:field="TAG VALUE"/>
</xs:schema>
I get an error which says:
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Does anybody know a way around. Any help would be appreciated. Please
do help!!!
Hello Prakruthi,
Are you trying to use this as a validation schema in an XML Schema collection
or a XDR for mapping queries?

> I get an error which says:
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Does anybody know a way around. Any help would be appreciated. Please
> do help!!!
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Kent,
Im trying to use this as a validation schema. And as you can see from
the xml data , each patient has multiple dicom tags and numbers. I want
to be able to map all these numbers into a single column. Is this
possible?
|||Hi Kent,
I forgot to mention that Tag Numbers and Tag values are two separate
columns. But each column should be able to have multiple values.
Regards,
Prakruthi
|||Hello Prakruthi,
Part of problem is that the schema you posted isn't a valid XSD schema:
a. The sql namespace (aliased by sql isn't defined by your schema.
b. The schema has some other problems, so I rewrote it -- sans the SQL parts
since I'm not sure what these are or do in your processor.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xs:element name="CT_DATASET">
<xs:complexType>
<xs:sequence>
<xs:element name="DATE-INTERVENTIONDATE">
<xs:complexType>
<xs:sequence>
<xs:element name="YEARS" type="xs:string"/>
<xs:element name="MONTHS" type="xs:string"/>
<xs:element name="DAYS" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DESCTEXT" type="xs:string"/>
<xs:element name="NUMBER_OF_SLICES" type="xs:string"/>
<xs:element ref="DICOM_TAGS" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DICOM_TAGS">
<xs:complexType>
<xs:sequence>
<xs:element ref="TAGNR"/>
<xs:element ref="TAGVALUE"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="TAGNR" type="xs:string"/>
<xs:element name="TAGVALUE" type="xs:string"/>
</xs:schema>
I was able to catalog is into a SQL Sever 2005 XML Schema Collection succesfully.
Does any of that help?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Dear Kent,
Thanks for reorganising my schema in the right manner. But I dont think
you understood what I meant to say. Even when I used your modified
schema I could not map the three tagnr and tagvalues associated with
one one single patient into the tagnr and tagvalue column.I get an
error that says
Data mapping to column 'TAG_NO' was already found in the data. Make
sure that no two schema definitions map to the same column.
Is it not possible to map three different values to one column of the
same row? I hope you can help me out again this time.
Regards,
Prakruthi
|||Hello Prakruthi,
That's why I asked if you were using this an mapping schema or as a validation
schema. You said "validation," so I fixed it for that. However, I don't believe
you can target more than one value into a column using a single mapping.
Sadly, I don't have the time to work on this today before going on the road
for the next couple of week. However, it looks like Michael is back online,
so he might be able to help more.

> Thanks for reorganising my schema in the right manner. But I dont
> think
> you understood what I meant to say. Even when I used your modified
> schema I could not map the three tagnr and tagvalues associated with
> one one single patient into the tagnr and tagvalue column.I get an
> error that says
> Data mapping to column 'TAG_NO' was already found in the data. Make
> sure that no two schema definitions map to the same column.
> Is it not possible to map three different values to one column of the
> same row? I hope you can help me out again this time.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi
|||Hi Kent,
I wanted to say im not using it as a validation schema. I apologise for
the typo error. I hope micheal would help me out with this issue.
Regards,
Prakruthi
|||I have forwarded it to our mapping schema experts and I hope they will get
back to you.
Ping me if that is not the case.
Michael
"Prakruthi" <prakruthi.rao@.gmail.com> wrote in message
news:1143210046.980577.250430@.e56g2000cwe.googlegr oups.com...
> Hi Kent,
> I wanted to say im not using it as a validation schema. I apologise for
> the typo error. I hope micheal would help me out with this issue.
> Regards,
> Prakruthi
>

mapping two references from one table ?

Hi, I'm new to SQL programming and SQL 2000 server.
My problem is that I have a table that contains antenna information.
(Their can be different types of antenna's)
I have another table that points to the antenna table twice, because it
needs two different types of antennas. IE: GPS and radio
When I try to map the results, I don't know how to specify which one I
want to reference.
For example: This is what the query builder builds for me
SELECT DrtType.TxAntennaId, DrtType.GpsAntennaId, Antenna.AntennaId,
Antenna.AntennaName FROM DrtType INNER JOIN Antenna ON
DrtType.TxAntennaId = Antenna.AntennaId AND DrtType.GpsAntennaId =
Antenna.AntennaId
When I try to format the datagrid. I need to map each value that I use
individually
//// Add GPS Antenna
DataGridColumnStyle TextCol_3 = new DataGridTextBoxColumn();
TextCol_3.MappingName = "AntennaName";
TextCol_3.HeaderText = "Gps Antenna";
TextCol_3.Width = defaultWidth;
dgStyle.GridColumnStyles.Add(TextCol_3);
But I only have one AntennaName to map.
Can I do something like:
DrtType.TxAntennaId.AntennaName
DrtType.GpsAntennaId.AntennaName
Thank you for you helpOn 29 Nov 2005 18:11:07 -0800, oracle wrote:

>Hi, I'm new to SQL programming and SQL 2000 server.
>My problem is that I have a table that contains antenna information.
>(Their can be different types of antenna's)
>I have another table that points to the antenna table twice, because it
>needs two different types of antennas. IE: GPS and radio
>When I try to map the results, I don't know how to specify which one I
>want to reference.
>For example: This is what the query builder builds for me
>SELECT DrtType.TxAntennaId, DrtType.GpsAntennaId, Antenna.AntennaId,
>Antenna.AntennaName FROM DrtType INNER JOIN Antenna ON
>DrtType.TxAntennaId = Antenna.AntennaId AND DrtType.GpsAntennaId =
>Antenna.AntennaId
Hi oracle,
You have to join the Antenna table twice, and use aliases to
distinguishe between the two:
SELECT D.TxAntennaId, D.GpsAntennaId,
TX.AntennaName AS TX_Antenna,
GPS.AntennaName AS GPS_Antenna
FROM DrtType AS D
INNER JOIN Antenna AS TX
ON TX.AntennaId = D.TxAntennaId
INNER JOIN Antenna AS GPS
ON GPS = D.GpsAntennaId
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Mapping two measure groups with different dimension usage

Hello.

I am looking for a solution for the following situation:

I have a measure group for my business facts and a measure group for tolerances and targets.

The first measure group uses a date dimension, a site dimension, a project dimension and an employee dimension.

The second group shares all dimensions except the employee dimension.

Lets say I have the business fact sales per hour.
The target value for this fact is stored in the second measure group without the employee info. The targets are valid for all employees at that site, project and day.

When i browse the cube i want see the targets next to the actual data - even on employee level.

Therefore i have to set IgnoreUnrelatedDimensions in the properties of the second measure group to True.
But when i do this i get an entry for every employee present in the employee dimension, showing the tolerance and target.

I am lost at the moment and belive that there must be an elegant solution. Hopefully someone has an idea?

Thanks,
ThomasFrom your description, it's not clear in which cases the IgnoreUnrelatedDimensions doesn't meet your needs - could you describe some scenarios? You could selectively use the MDX ValidMeasure() function in calculations, instead of applying IgnoreUnrelatedDimensions.|||Hello Deepak.

Thanks for your reply.

I'll try to explain my problem better with an example.

Data in my Tolerance FactTable:

DateProjectSiteTolerance_Sales_per_Hour20070320Upsell1Hamburg220070321Upsell1Hamburg220070322Upsell1Hamburg3

Data in my Sales FactTable:

DateProjectSiteEmployeeSales_Per_Hour20070320Upsell1HamburgBob1.520070320Upsell1HamburgMike2.120070321Upsell1HamburgBob2.720070321Upsell1HamburgMike3.320070322Upsell1HamburgBob1.2

When I select 20070322 as Date in the Browser, i get a row for Mike, although he dind't work on 22th.

DateProjectSiteEmployeeSales_Per_HourTolerance_Sales_per_Hour20070322Upsell1HamburgBob1.2320070322Upsell1HamburgMike3

Thanks to a tip from Markus i am using Scope-Statements at the moment

SCOPE([Measures].[Tolerance_Sales_Per_Hour]);
THIS=IIF (ISEMPTY([Measures].[Sales_Per_Hour]),NULL,[Tolerance_Sales_Per_Hour]);
END SCOPE;

But we are not sure, if this is the best solution.

Best regards,
Thomas

Mapping table problem

HI guys
I need to put multiple coniditions on my table based on some combinations.

Here is my main table-

Practitioner

DHBName

PHOName

Practicename

PractitionerName

Locum

YCount

NCount

4

Canterbury DHB

Partnership Health (Canterbury)-596721

Linwood Avenue Medical Centre

Christine Abbasi

No

0

1

32

Canterbury DHB

Partnership Health (Canterbury)-596721

Felicia House Clinic (Pearson)

Nicci Pavey

No

0

1

197

Canterbury DHB

Partnership Health (Canterbury)-596721

Woodham Road Health Care

Peggy Shelton-Agar

No

0

1

394

Hawkes Bay DHB

Hawkes Bay PHO Limited-587862

The Doctors Napier

John Laird

YES

1

0

549

Canterbury DHB

Partnership Health (Canterbury)-596721

Linwood Avenue Medical Centre

Denise Armstrong

No

0

1

1286

Canterbury DHB

Partnership Health (Canterbury)-596721

Darfield Medical Centre Limited

Katherine Muscroft-Taylor

No

0

1

1501

Counties-Manukau DHB

Procare Network Manukau Limited-573183

Manukau City Health Centre

Caroline Shephard

YES

1

0

1932

Hawkes Bay DHB

Hawkes Bay PHO Limited-587862

Clive Medical Centre Ltd

Karen Irwin

YES

1

0

2226

Canterbury DHB

Partnership Health (Canterbury)-596721

Sumner Health Centre

Anne Scott

No

0

1

3243

Waitemata DHB

Harbour PHO Ltd-610733

Byron Medical

Rex Livingstone Sinclair

No

0

1

3263

Canterbury DHB

Partnership Health (Canterbury)-596721

Sumner Health Centre

Peter Wynn Nicholson

No

0

1

3381

Otago DHB

Otago Southern Region Primary Health Organisation-597004

Dr Visagie's Practice

Fay Young

NO

0

1

Here is my mapping table-

Locum Flag

No count

Yes count

Percentage

No

1

0

1

No

1

1

0.75

Yes

1

1

0.25

No

2

0

0.5

No

3

0

0.3333

No

4

0

0.25

Yes

2

1

0.125

No

2

1

0.75

Yes

0

1

0.75

Yes

0

2

0.375

Yes

0

3

0.25

Yes

0

4

0.1875

I have to add an additional column called 'percentage' in my main table by refering the mapping table.

For ex for a particular practitioner, Locum flag is 'Yes', nocount is 1 and yes count is 1, then percentage should be '0.25'

How should I do this?

So, when you are adding data to your main table, why not just add a chunk of code that would calculate what the percentage would be and keep the mapping table 'on paper' ? (And then store the result)|||

hi Tas_CRO

How do I add that chunk of code to my main table?

That is probably I need to know

Thanks

Mapping table problem

HI guys
I need to put multiple coniditions on my table based on some combinations.

Here is my main table-

PractitionerDHBNamePHONamePracticenamePractitionerNameLocumYCountNCount4Canterbury DHBPartnership Health (Canterbury)-596721Linwood Avenue Medical CentreChristine AbbasiNo0132Canterbury DHBPartnership Health (Canterbury)-596721Felicia House Clinic (Pearson)Nicci PaveyNo01197Canterbury DHBPartnership Health (Canterbury)-596721Woodham Road Health CarePeggy Shelton-AgarNo01394Hawkes Bay DHBHawkes Bay PHO Limited-587862The Doctors NapierJohn LairdYES10549Canterbury DHBPartnership Health (Canterbury)-596721Linwood Avenue Medical CentreDenise ArmstrongNo011286Canterbury DHBPartnership Health (Canterbury)-596721Darfield Medical Centre LimitedKatherine Muscroft-TaylorNo011501Counties-Manukau DHBProcare Network Manukau Limited-573183Manukau City Health CentreCaroline ShephardYES101932Hawkes Bay DHBHawkes Bay PHO Limited-587862Clive Medical Centre LtdKaren IrwinYES102226Canterbury DHBPartnership Health (Canterbury)-596721Sumner Health CentreAnne ScottNo013243Waitemata DHBHarbour PHO Ltd-610733Byron MedicalRex Livingstone SinclairNo013263Canterbury DHBPartnership Health (Canterbury)-596721Sumner Health CentrePeter Wynn NicholsonNo013381Otago DHBOtago Southern Region Primary Health Organisation-597004Dr Visagie's PracticeFay YoungNO01

Here is my mapping table-

Locum FlagNo countYes countPercentageNo101No110.75Yes110.25No200.5No300.3333No400.25Yes210.125No210.75Yes010.75Yes020.375Yes030.25Yes040.1875

I have to add an additional column called 'percentage' in my main table by refering the mapping table.

For ex for a particular practitioner, Locum flag is 'Yes', nocount is 1 and yes count is 1, then percentage should be '0.25'

How should I do this?

So, when you are adding data to your main table, why not just add a chunk of code that would calculate what the percentage would be and keep the mapping table 'on paper' ? (And then store the result)
|||

hi Tas_CRO

How do I add that chunk of code to my main table?

That is probably I need to know

Thanks

Mapping SQL Server Data Types to C DataTypes

Hi Can anyone point me to a document somewhere that shows a mapping of
SQL Server 2000 datatypes to C datatypes? I am writing some extended
stored procedures which need to be able to process pretty much any
data type, so I want to make sure I am taking them from SRVPROC and
storing them in the correct C data type.

Thanks,
Bruce[posted and mailed, please reply in news]

Bruce (sandell@.pacbell.net) writes:
> Hi Can anyone point me to a document somewhere that shows a mapping of
> SQL Server 2000 datatypes to C datatypes? I am writing some extended
> stored procedures which need to be able to process pretty much any
> data type, so I want to make sure I am taking them from SRVPROC and
> storing them in the correct C data type.

Books Online.

Building SQL Server Applications.
Extended Stored Procedures Programming
Extended Stored Procedure Programmer's Reference
Data Types (the last topic).

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Mapping Source and Destination columns

can somebody show an example of how to map source and destination columns when uploading a file to sql server?

Also, please send me the mapping when i want to map source to different destination columns.

I think you have to to be a bit more specific to get answers that will help you.

Mapping schema for GoogleBase XSD

I want to load into a SQL Server table with SQLXMLBulkload an xml file that
is formatted with google base xsd (http://base.google.com/base/base.xsd). I
need a mapping schema for that, correct? To map the elements of the xml to
my table. Does anyone have a mapping schema for this?
Steve
Hi Steve
The mapping depends on the database tables to which you want to map it to.
Best regards
Michael
"Steve Mc" <stevemc@.zillow.com> wrote in message
news:OGEN6NOiHHA.5052@.TK2MSFTNGP05.phx.gbl...
>I want to load into a SQL Server table with SQLXMLBulkload an xml file that
>is formatted with google base xsd (http://base.google.com/base/base.xsd).
>I need a mapping schema for that, correct? To map the elements of the xml
>to my table. Does anyone have a mapping schema for this?
> Steve
>
|||Hello,
Please take a look at http://msdn2.microsoft.com/en-us/library/ms172649.aspx
to understand how annotations work.
Let me know if you need further assistance.
Regards,
Monica Frintu
"Steve Mc" wrote:

> I want to load into a SQL Server table with SQLXMLBulkload an xml file that
> is formatted with google base xsd (http://base.google.com/base/base.xsd). I
> need a mapping schema for that, correct? To map the elements of the xml to
> my table. Does anyone have a mapping schema for this?
> Steve
>
>

Mapping schema for GoogleBase XSD

I want to load into a SQL Server table with SQLXMLBulkload an xml file that
is formatted with google base xsd (http://base.google.com/base/base.xsd). I
need a mapping schema for that, correct? To map the elements of the xml to
my table. Does anyone have a mapping schema for this?
SteveHi Steve
The mapping depends on the database tables to which you want to map it to.
Best regards
Michael
"Steve Mc" <stevemc@.zillow.com> wrote in message
news:OGEN6NOiHHA.5052@.TK2MSFTNGP05.phx.gbl...
>I want to load into a SQL Server table with SQLXMLBulkload an xml file that
>is formatted with google base xsd (http://base.google.com/base/base.xsd).
>I need a mapping schema for that, correct? To map the elements of the xml
>to my table. Does anyone have a mapping schema for this?
> Steve
>|||Hello,
Please take a look at http://msdn2.microsoft.com/en-us/library/ms172649.aspx
to understand how annotations work.
Let me know if you need further assistance.
Regards,
--
Monica Frintu
"Steve Mc" wrote:

> I want to load into a SQL Server table with SQLXMLBulkload an xml file tha
t
> is formatted with google base xsd (http://base.google.com/base/base.xsd).
I
> need a mapping schema for that, correct? To map the elements of the xml t
o
> my table. Does anyone have a mapping schema for this?
> Steve
>
>sql

Mapping Report Model Entities to Drill-through Reports

When you use Sql Mgmt Studio to assign a report as a drill-through target for
a model entity, you can assign reports for both "Single Instance" and
"Multiple Instance". Does anyone know what this means? I had a 2+-hour
support session with a member of MS SQL Reporting team, and he didn't know
either.
cheers,
Helen
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.comBy default when you save the file, where the datasource is connected through
RB,
If you have opened RB through some other server then you can attach where it
is for multiple instance of the servers. Basically to update on the report
server database of multiple servers is called multiple instance and single
server is called single instance.
Amarnath
"Helen Warn" wrote:
> When you use Sql Mgmt Studio to assign a report as a drill-through target for
> a model entity, you can assign reports for both "Single Instance" and
> "Multiple Instance". Does anyone know what this means? I had a 2+-hour
> support session with a member of MS SQL Reporting team, and he didn't know
> either.
> cheers,
> Helen
> --
> Helen Warn, PhD
> Agile Software Inc.
> www.agile-soft.com|||Actually that is not what it is used for. From
http://msdn2.microsoft.com/en-us/library/ms345298.aspx
(Lesson 12: Specifying Clickthrough Report Options from BOL):
"There are two clickthrough templates for each entity: a single instance
template and a multi-instance template. A single instance template is used
when the user clicks an instance of the entity. A multi-instance template is
used when the user clicks an aggregate value and multiple instances of the
entity are displayed."
I am sure that I am not the only person who fervently wishes that the
reference sectins of the documentation contained such information.
Cheers,
Helen
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com
"Amarnath" wrote:
> By default when you save the file, where the datasource is connected through
> RB,
> If you have opened RB through some other server then you can attach where it
> is for multiple instance of the servers. Basically to update on the report
> server database of multiple servers is called multiple instance and single
> server is called single instance.
> Amarnath
> "Helen Warn" wrote:
> > When you use Sql Mgmt Studio to assign a report as a drill-through target for
> > a model entity, you can assign reports for both "Single Instance" and
> > "Multiple Instance". Does anyone know what this means? I had a 2+-hour
> > support session with a member of MS SQL Reporting team, and he didn't know
> > either.
> >
> > cheers,
> >
> > Helen
> > --
> > Helen Warn, PhD
> > Agile Software Inc.
> > www.agile-soft.com

Mapping pages in database file?

Hi there,
Recently for some reason, after I was trying adjusting the indexes om my
test database, the size of the database, balloned from ~4GB to 13GB+. This
is after I rolledback all the changes I did. The database runs in full
recovery mode.
Now I got the transaction log figured out, and with the help of dbcc
loginfo, I can see what's being used in the log. But what I'd like to find
out is how large the parts of the database is; ie. a complete run down, on
what SQL Server 2000 uses storage space on.
For reference, a dbcc shrinkdatabase gives the following -
11 2 16120 128 16120 128
According to BOL, the last number (Estimated Pages) is what it guess on the
database can be shrunked to, however, that's extremely unlikely, as I know,
the DB should be around 4GB just after being created and filled with data.
How do I go about this little project?
Necessity is the plea for every infringement of human freedom. It is the
argument of tyrants; it is the creed of slaves.
-- William Pitt, 1783
Kim Noer wrote:

> How do I go about this little project?
By using DBCC SHOWCONTIG(table), investigating the Avg. Page Density (full)
and remembering that fillfactor works reverse of what previously thought. To
boil it down, I applied a fillfactor of 15%, when I should had applied 85%.
Clever.
Necessity is the plea for every infringement of human freedom. It is
the argument of tyrants; it is the creed of slaves. -- William Pitt,
1783
|||Kim
http://www.sql-server-performance.co...showcontig.asp
"Kim Noer" <kn@.nospam.dk> wrote in message
news:esALBYs8FHA.3048@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> Recently for some reason, after I was trying adjusting the indexes om my
> test database, the size of the database, balloned from ~4GB to 13GB+. This
> is after I rolledback all the changes I did. The database runs in full
> recovery mode.
> Now I got the transaction log figured out, and with the help of dbcc
> loginfo, I can see what's being used in the log. But what I'd like to find
> out is how large the parts of the database is; ie. a complete run down, on
> what SQL Server 2000 uses storage space on.
> For reference, a dbcc shrinkdatabase gives the following -
> 11 2 16120 128 16120 128
> According to BOL, the last number (Estimated Pages) is what it guess on
> the database can be shrunked to, however, that's extremely unlikely, as I
> know, the DB should be around 4GB just after being created and filled with
> data.
> How do I go about this little project?
> --
> Necessity is the plea for every infringement of human freedom. It is the
> argument of tyrants; it is the creed of slaves.
> -- William Pitt, 1783
>

Mapping pages in database file?

Hi there,
Recently for some reason, after I was trying adjusting the indexes om my
test database, the size of the database, balloned from ~4GB to 13GB+. This
is after I rolledback all the changes I did. The database runs in full
recovery mode.
Now I got the transaction log figured out, and with the help of dbcc
loginfo, I can see what's being used in the log. But what I'd like to find
out is how large the parts of the database is; ie. a complete run down, on
what SQL Server 2000 uses storage space on.
For reference, a dbcc shrinkdatabase gives the following -
11 2 16120 128 16120 128
According to BOL, the last number (Estimated Pages) is what it guess on the
database can be shrunked to, however, that's extremely unlikely, as I know,
the DB should be around 4GB just after being created and filled with data.
How do I go about this little project?
Necessity is the plea for every infringement of human freedom. It is the
argument of tyrants; it is the creed of slaves.
-- William Pitt, 1783Kim Noer wrote:

> How do I go about this little project?
By using DBCC SHOWCONTIG(table), investigating the Avg. Page Density (full)
and remembering that fillfactor works reverse of what previously thought. To
boil it down, I applied a fillfactor of 15%, when I should had applied 85%.
Clever.
Necessity is the plea for every infringement of human freedom. It is
the argument of tyrants; it is the creed of slaves. -- William Pitt,
1783|||Kim
http://www.sql-server-performance.c..._showcontig.asp
"Kim Noer" <kn@.nospam.dk> wrote in message
news:esALBYs8FHA.3048@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> Recently for some reason, after I was trying adjusting the indexes om my
> test database, the size of the database, balloned from ~4GB to 13GB+. This
> is after I rolledback all the changes I did. The database runs in full
> recovery mode.
> Now I got the transaction log figured out, and with the help of dbcc
> loginfo, I can see what's being used in the log. But what I'd like to find
> out is how large the parts of the database is; ie. a complete run down, on
> what SQL Server 2000 uses storage space on.
> For reference, a dbcc shrinkdatabase gives the following -
> 11 2 16120 128 16120 128
> According to BOL, the last number (Estimated Pages) is what it guess on
> the database can be shrunked to, however, that's extremely unlikely, as I
> know, the DB should be around 4GB just after being created and filled with
> data.
> How do I go about this little project?
> --
> Necessity is the plea for every infringement of human freedom. It is the
> argument of tyrants; it is the creed of slaves.
> -- William Pitt, 1783
>

Mapping pages in database file?

Hi there,
Recently for some reason, after I was trying adjusting the indexes om my
test database, the size of the database, balloned from ~4GB to 13GB+. This
is after I rolledback all the changes I did. The database runs in full
recovery mode.
Now I got the transaction log figured out, and with the help of dbcc
loginfo, I can see what's being used in the log. But what I'd like to find
out is how large the parts of the database is; ie. a complete run down, on
what SQL Server 2000 uses storage space on.
For reference, a dbcc shrinkdatabase gives the following -
11 2 16120 128 16120 128
According to BOL, the last number (Estimated Pages) is what it guess on the
database can be shrunked to, however, that's extremely unlikely, as I know,
the DB should be around 4GB just after being created and filled with data.
How do I go about this little project?
--
Necessity is the plea for every infringement of human freedom. It is the
argument of tyrants; it is the creed of slaves.
-- William Pitt, 1783Kim Noer wrote:
> How do I go about this little project?
By using DBCC SHOWCONTIG(table), investigating the Avg. Page Density (full)
and remembering that fillfactor works reverse of what previously thought. To
boil it down, I applied a fillfactor of 15%, when I should had applied 85%.
Clever.
--
Necessity is the plea for every infringement of human freedom. It is
the argument of tyrants; it is the creed of slaves. -- William Pitt,
1783|||Kim
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
"Kim Noer" <kn@.nospam.dk> wrote in message
news:esALBYs8FHA.3048@.TK2MSFTNGP10.phx.gbl...
> Hi there,
> Recently for some reason, after I was trying adjusting the indexes om my
> test database, the size of the database, balloned from ~4GB to 13GB+. This
> is after I rolledback all the changes I did. The database runs in full
> recovery mode.
> Now I got the transaction log figured out, and with the help of dbcc
> loginfo, I can see what's being used in the log. But what I'd like to find
> out is how large the parts of the database is; ie. a complete run down, on
> what SQL Server 2000 uses storage space on.
> For reference, a dbcc shrinkdatabase gives the following -
> 11 2 16120 128 16120 128
> According to BOL, the last number (Estimated Pages) is what it guess on
> the database can be shrunked to, however, that's extremely unlikely, as I
> know, the DB should be around 4GB just after being created and filled with
> data.
> How do I go about this little project?
> --
> Necessity is the plea for every infringement of human freedom. It is the
> argument of tyrants; it is the creed of slaves.
> -- William Pitt, 1783
>