I have a table in reporting services. I have a field that calcuates a
percentage and the report is sorted in ascending order by this
percentage. I want to draw a single red horizontal line at a
threshold, let's say 95% so that any record lower than 95% would be
above the line and any record higher would be below the line. How can
I do this?On Apr 4, 6:54 am, robertpet...@.hotmail.com wrote:
> I have a table in reporting services. I have a field that calcuates a
> percentage and the report is sorted in ascending order by this
> percentage. I want to draw a single red horizontal line at a
> threshold, let's say 95% so that any record lower than 95% would be
> above the line and any record higher would be below the line. How can
> I do this?
I would suggest adding an extra query/stored procedure return field/
column to include in the report that is used as a flag for the
percentages. Have the flag set for the minimum row at or above 95%.
Then in the report, set the table properties (F4) (BorderColor->Top
and BorderStyle->Top) according to that flag. Something like these
expressions should work:
For BorderColor:Top:
=iif(Fields!Flag.Value = 1, "Red", "White")
For BorderStyle:Top:
=iif(Fields!Flag.Value = 1, "Solid", "None")
Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultantsql
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Thursday, March 29, 2012
Draw Bitmap from Points in report
Hi, i have a database field that has a drawing stored as points, for example....
(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)
In my VB.NET application, i can take those points and recreate the image. I need to do the same in reporting services... i am trying to replace a legacy ACCESS report, that had the drawing object.
How can i recreate the image in reporting services? is there an easy way to do so? i believe i tried to create a class and tried to reference it and call the function to return the data as an image, but i got a system.drawing not found error...
There are at least two options:
1. use the built-in charts with chart type = scatter. Note: the scatter chart must have a category grouping based on a unique value (e.g. data point id in your case). I attached a small sample report to the bottom of this posting to show the idea.
2. or draw the image yourself and use it in Reporting Services. However, make sure to follow these steps:
2.1. Design and implement a custom assembly to generate images.
The custom assembly must retrieve the data on its own, take care of grouping/sorting the data, and generating the chart image.
Note: The custom assembly has to return the image as byte[]. It cannot return it as a System.Drawing.Image. You can often convert a System.Drawing.Image object with code similar to the following.
System.IO.MemoryStream renderedImage = new MemoryStream();
myChart.Save(renderedImage);
renderedImage.Position = 0;
return renderedImage.ToArray();
2.2. Add an image to the report.
Set the image type to Database. If the generated image is a bitmap in the PNG image format, set the image mimetype property to “image/png.” For the image value property, use an expression like the following.
=MyCustomAssembly.GenerateChart()
2.3. View the report in Report Designer Preview view to verify that the report is working correctly.
Note: In a default configuration, custom assemblies run in FullTrust in Report Designer preview. Hence, operations that require certain code access security permissions (such as file input/output, data provide access, etc.) are automatically granted these permissions in Fulltrust.
2.4. Deploy the custom assembly on a report server.
Make sure that the security policy configuration of the report server grants sufficient permissions to your custom assembly at runtime; otherwise the image generation will fail. For more information, see Understanding Code Access Security in Reporting Services (http://msdn2.microsoft.com/en-us/library/ms155108.aspx) in SQL Server 2005 Books Online.
-- Robert
=========================================
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AdventureWorks">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>67061ec4-b72e-4a04-a7f3-714536211b9c</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="chart1">
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Line</Subtype>
<Title />
<Height>2in</Height>
<CategoryAxis>
<Axis>
<Title />
<Style>
<Format>MMM dd</Format>
</Style>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.125in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>DataSet1</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Value1</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Top>0.125in</Top>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<MajorInterval>5</MajorInterval>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Scatter</Type>
<Width>3.5in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!MeasurementId.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=CDate(Fields!TimeStamp.Value)</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!MeasurementId.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>EarthTones</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=CDate(Fields!TimeStamp.Value)</Value>
</DataValue>
<DataValue>
<Value>=Fields!Value.Value</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderWidth>
<Default>4.5pt</Default>
</BorderWidth>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>2.25in</Height>
</Body>
<rd:ReportID>a068be44-d5ee-4243-91ed-445f05622d2c</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select 1 as MeasurementId, '07/16/2006' as TimeStamp, 10 as Value union
select 2 as MeasurementId, '07/17/2006' as TimeStamp, 10 as Value union
select 3 as MeasurementId, '07/17/2006' as TimeStamp, 8 as Value union
select 4 as MeasurementId, '07/18/2006' as TimeStamp, 8 as Value union
select 5 as MeasurementId, '07/19/2006' as TimeStamp, 10 as Value union
select 6 as MeasurementId, '07/19/2006' as TimeStamp, 12 as Value union
select 7 as MeasurementId, '07/20/2006' as TimeStamp, 12 as Value union
select 8 as MeasurementId, '07/21/2006' as TimeStamp, 12 as Value union
select 9 as MeasurementId, '07/21/2006' as TimeStamp, 9 as Value union
select 10 as MeasurementId, '07/22/2006' as TimeStamp, 9 as Value</CommandText>
<DataSourceName>AdventureWorks</DataSourceName>
</Query>
<Fields>
<Field Name="MeasurementId">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>MeasurementId</DataField>
</Field>
<Field Name="TimeStamp">
<rd:TypeName>System.String</rd:TypeName>
<DataField>TimeStamp</DataField>
</Field>
<Field Name="Value">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>Value</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Robert M. Bruckner, Microsoft</Author>
<Width>3.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Thanks for posting a reply ill try it first thing in the morning....
The drawing is collected on a handheld, its actually a signature (but it can be a drawing as well, so lots of points....), the coordinates of the drawing are saved in an xml file along with other data, and is then inserted into the database when the device is synced....
ill try the chart way first, and then the custom assembly again. when i tried it last time it was giving me the bitmap not defined error, i dont remember if i was returning the data as an image or as a byte... :) ill give it a try and post back here so that someone else can also make use of your help!
thank you.
|||omg...... thats for the reply and the hints, i doublechecked everything in your 2nd suggestion with what i had already done, the first one wasnt feasible... and after checking all your suggestions i realised that it had been working all this time!!!., it just doesnt work in DEBUG mode... :( when i ran the application itself outside of VS, it ran with no problems!!!!|||Make sure you have copied your assembly to the C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies folder.Draw Bitmap from Points in report
Hi, i have a database field that has a drawing stored as points, for example....
(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)(x1,y1)(x2,x2)
In my VB.NET application, i can take those points and recreate the image. I need to do the same in reporting services... i am trying to replace a legacy ACCESS report, that had the drawing object.
How can i recreate the image in reporting services? is there an easy way to do so? i believe i tried to create a class and tried to reference it and call the function to return the data as an image, but i got a system.drawing not found error...
There are at least two options:
1. use the built-in charts with chart type = scatter. Note: the scatter chart must have a category grouping based on a unique value (e.g. data point id in your case). I attached a small sample report to the bottom of this posting to show the idea.
2. or draw the image yourself and use it in Reporting Services. However, make sure to follow these steps:
2.1. Design and implement a custom assembly to generate images.
The custom assembly must retrieve the data on its own, take care of grouping/sorting the data, and generating the chart image.
Note: The custom assembly has to return the image as byte[]. It cannot return it as a System.Drawing.Image. You can often convert a System.Drawing.Image object with code similar to the following.
System.IO.MemoryStream renderedImage = new MemoryStream();
myChart.Save(renderedImage);
renderedImage.Position = 0;
return renderedImage.ToArray();
2.2. Add an image to the report.
Set the image type to Database. If the generated image is a bitmap in the PNG image format, set the image mimetype property to “image/png.” For the image value property, use an expression like the following.
=MyCustomAssembly.GenerateChart()
2.3. View the report in Report Designer Preview view to verify that the report is working correctly.
Note: In a default configuration, custom assemblies run in FullTrust in Report Designer preview. Hence, operations that require certain code access security permissions (such as file input/output, data provide access, etc.) are automatically granted these permissions in Fulltrust.
2.4. Deploy the custom assembly on a report server.
Make sure that the security policy configuration of the report server grants sufficient permissions to your custom assembly at runtime; otherwise the image generation will fail. For more information, see Understanding Code Access Security in Reporting Services (http://msdn2.microsoft.com/en-us/library/ms155108.aspx) in SQL Server 2005 Books Online.
-- Robert
=========================================
<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="AdventureWorks">
<DataSourceReference>AdventureWorks</DataSourceReference>
<rd:DataSourceID>67061ec4-b72e-4a04-a7f3-714536211b9c</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Chart Name="chart1">
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Subtype>Line</Subtype>
<Title />
<Height>2in</Height>
<CategoryAxis>
<Axis>
<Title />
<Style>
<Format>MMM dd</Format>
</Style>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</CategoryAxis>
<PointWidth>0</PointWidth>
<Left>0.125in</Left>
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<DataSetName>DataSet1</DataSetName>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Value1</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Top>0.125in</Top>
<PlotArea>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BackgroundColor>WhiteSmoke</BackgroundColor>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<MajorInterval>5</MajorInterval>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
<Type>Scatter</Type>
<Width>3.5in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!MeasurementId.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=CDate(Fields!TimeStamp.Value)</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!MeasurementId.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Palette>EarthTones</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=CDate(Fields!TimeStamp.Value)</Value>
</DataValue>
<DataValue>
<Value>=Fields!Value.Value</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<BorderWidth>
<Default>4.5pt</Default>
</BorderWidth>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
</Chart>
</ReportItems>
<Height>2.25in</Height>
</Body>
<rd:ReportID>a068be44-d5ee-4243-91ed-445f05622d2c</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select 1 as MeasurementId, '07/16/2006' as TimeStamp, 10 as Value union
select 2 as MeasurementId, '07/17/2006' as TimeStamp, 10 as Value union
select 3 as MeasurementId, '07/17/2006' as TimeStamp, 8 as Value union
select 4 as MeasurementId, '07/18/2006' as TimeStamp, 8 as Value union
select 5 as MeasurementId, '07/19/2006' as TimeStamp, 10 as Value union
select 6 as MeasurementId, '07/19/2006' as TimeStamp, 12 as Value union
select 7 as MeasurementId, '07/20/2006' as TimeStamp, 12 as Value union
select 8 as MeasurementId, '07/21/2006' as TimeStamp, 12 as Value union
select 9 as MeasurementId, '07/21/2006' as TimeStamp, 9 as Value union
select 10 as MeasurementId, '07/22/2006' as TimeStamp, 9 as Value</CommandText>
<DataSourceName>AdventureWorks</DataSourceName>
</Query>
<Fields>
<Field Name="MeasurementId">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>MeasurementId</DataField>
</Field>
<Field Name="TimeStamp">
<rd:TypeName>System.String</rd:TypeName>
<DataField>TimeStamp</DataField>
</Field>
<Field Name="Value">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>Value</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Robert M. Bruckner, Microsoft</Author>
<Width>3.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Thanks for posting a reply ill try it first thing in the morning....
The drawing is collected on a handheld, its actually a signature (but it can be a drawing as well, so lots of points....), the coordinates of the drawing are saved in an xml file along with other data, and is then inserted into the database when the device is synced....
ill try the chart way first, and then the custom assembly again. when i tried it last time it was giving me the bitmap not defined error, i dont remember if i was returning the data as an image or as a byte... :) ill give it a try and post back here so that someone else can also make use of your help!
thank you.
|||omg...... thats for the reply and the hints, i doublechecked everything in your 2nd suggestion with what i had already done, the first one wasnt feasible... and after checking all your suggestions i realised that it had been working all this time!!!., it just doesnt work in DEBUG mode... :( when i ran the application itself outside of VS, it ran with no problems!!!!|||Make sure you have copied your assembly to the C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies folder.Monday, March 19, 2012
Doubt
I need know what is field equal suid from SQL 7.0 and not exist in
SQL 2000.Use suid.|||What are you trying to do ?|||I wanna mean sid. That column exists in sql7.0 too, but the suid dont exist in 2k now.|||SELECT suid, name, dbname, Admin = sysadmin + serveradmin + setupadmin + processadmin + diskadmin + dbcreator
FROM master..syslogins WHERE loginname = ' & vfdstrLoginID_App & '
I want the the field equal suid.|||There is not really much "doubt" about it, this is (or at least, has been) a fairly common issue upgrading 6.x systems; System table suid columns have been changed since 6.x (removed in 7.0 / 2k) in favor of sid columns:
syslogins.suid --> syslogins.sid
sysdatabases.suid --> sysdatabases.sid
sysremotelogins.suid --> sysremotelogins.sid
sysusers.suid --> sysusers.sid
sysalternates.suid --> sysusers.isaliased
sysalternates.altsuid --> sysusers.isaliased
The related niladic functions also have changed: (the old ones are now "broken")
SUSER_ID () --> SUSER_SID (), returns the user sid given the user name
SUSER_NAME () --> SUSER_SNAME (), returns the user name given the sid
The typical 6.x tsql involving suid columns, SUSER_NAME (), SUSER_ID (), etc., e.g.(to generate an informational result set of "user information" by joining syslogins and sysusers on suid) no longer will work in 7.0, and 2k. therefore, you may wish to consider replacing SUID references with SID references, for example:
SELECT
SUSER_SNAME(syslogins.sid) AS '"user name"',
syslogins.loginname,
syslogins.sid,
syslogins.name,
syslogins.dbname,
syslogins.hasaccess,
syslogins.bulkadmin,
syslogins.dbcreator,
syslogins.diskadmin
FROM
syslogins
INNER JOIN
sysusers
ON
syslogins.sid = sysusers.sid|||There is not really much "doubt" about it, this is (or at least, has been) a fairly common issue upgrading 6.x systems; System table suid columns have been changed since 6.x (removed in 7.0 / 2k) in favor of sid columns:
The typical 6.x tsql involving suid columns, SUSER_NAME (), SUSER_ID (), etc., e.g.(to generate an informational result set of "user information" by joining syslogins and sysusers on suid) no longer will work in 7.0, and 2k. therefore, you may wish to consider replacing SUID references with SID references, for example:
For another example see: http://dbforums.com/t561459.html|||syslogins.suid --> syslogins.sid
Binary Hexadecimal
Not is funcionaly in my programation? I want
retorn a value binary, with is the suid ald what retorn
the value sequential of numbers.|||Check out the following article:
article (http://support.microsoft.com/default.aspx?scid=kb;en-us;104829)|||An MSDN link on SUSER_ID: (may be helpful?)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_00mc.asp
As stated in the link: "SUSER_SID returns a SUID only for a login that has an entry in the syslogins system table. In SQL Server 7.0, the security identification number (SID) replaces the server user identification number (SUID). SUSER_ID always returns NULL when used in Microsoft SQL Server 2000."|||Okay.
SELECT SUSER_ID ('sa') in SQL7.0
--
1 --> Binary
(1 row(s) affected)
SELECT SUSER_SID ('sa') in SQL2000
--
NULL
(1 row(s) affected)
OR
SELECT SUSER_SID('sa')
--
0x01 -- Hexa
(1 row(s) affected)
I want number sequential and binary per users, understand?|||Q1 I want number sequential and binary per users, understand?
A1 Maybe, (for the most part suid columns were used in joins to return information). Apparently your requirement is different.
Q1 Are you saying the application you are working with requires a 1s and 0s representation of Binary numbers? for example:
User IntRep HexRep Binary
Tom 10 0xA 1010
sa 1 0x1 0001
If you need to use the string '1010' for user Tom instead of 0xA, you could create a function or stored procedure to return 1010 (given a hex representation)?|||What are you trying to do (deja vu) ?
SQL 2000.Use suid.|||What are you trying to do ?|||I wanna mean sid. That column exists in sql7.0 too, but the suid dont exist in 2k now.|||SELECT suid, name, dbname, Admin = sysadmin + serveradmin + setupadmin + processadmin + diskadmin + dbcreator
FROM master..syslogins WHERE loginname = ' & vfdstrLoginID_App & '
I want the the field equal suid.|||There is not really much "doubt" about it, this is (or at least, has been) a fairly common issue upgrading 6.x systems; System table suid columns have been changed since 6.x (removed in 7.0 / 2k) in favor of sid columns:
syslogins.suid --> syslogins.sid
sysdatabases.suid --> sysdatabases.sid
sysremotelogins.suid --> sysremotelogins.sid
sysusers.suid --> sysusers.sid
sysalternates.suid --> sysusers.isaliased
sysalternates.altsuid --> sysusers.isaliased
The related niladic functions also have changed: (the old ones are now "broken")
SUSER_ID () --> SUSER_SID (), returns the user sid given the user name
SUSER_NAME () --> SUSER_SNAME (), returns the user name given the sid
The typical 6.x tsql involving suid columns, SUSER_NAME (), SUSER_ID (), etc., e.g.(to generate an informational result set of "user information" by joining syslogins and sysusers on suid) no longer will work in 7.0, and 2k. therefore, you may wish to consider replacing SUID references with SID references, for example:
SELECT
SUSER_SNAME(syslogins.sid) AS '"user name"',
syslogins.loginname,
syslogins.sid,
syslogins.name,
syslogins.dbname,
syslogins.hasaccess,
syslogins.bulkadmin,
syslogins.dbcreator,
syslogins.diskadmin
FROM
syslogins
INNER JOIN
sysusers
ON
syslogins.sid = sysusers.sid|||There is not really much "doubt" about it, this is (or at least, has been) a fairly common issue upgrading 6.x systems; System table suid columns have been changed since 6.x (removed in 7.0 / 2k) in favor of sid columns:
The typical 6.x tsql involving suid columns, SUSER_NAME (), SUSER_ID (), etc., e.g.(to generate an informational result set of "user information" by joining syslogins and sysusers on suid) no longer will work in 7.0, and 2k. therefore, you may wish to consider replacing SUID references with SID references, for example:
For another example see: http://dbforums.com/t561459.html|||syslogins.suid --> syslogins.sid
Binary Hexadecimal
Not is funcionaly in my programation? I want
retorn a value binary, with is the suid ald what retorn
the value sequential of numbers.|||Check out the following article:
article (http://support.microsoft.com/default.aspx?scid=kb;en-us;104829)|||An MSDN link on SUSER_ID: (may be helpful?)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_setu-sus_00mc.asp
As stated in the link: "SUSER_SID returns a SUID only for a login that has an entry in the syslogins system table. In SQL Server 7.0, the security identification number (SID) replaces the server user identification number (SUID). SUSER_ID always returns NULL when used in Microsoft SQL Server 2000."|||Okay.
SELECT SUSER_ID ('sa') in SQL7.0
--
1 --> Binary
(1 row(s) affected)
SELECT SUSER_SID ('sa') in SQL2000
--
NULL
(1 row(s) affected)
OR
SELECT SUSER_SID('sa')
--
0x01 -- Hexa
(1 row(s) affected)
I want number sequential and binary per users, understand?|||Q1 I want number sequential and binary per users, understand?
A1 Maybe, (for the most part suid columns were used in joins to return information). Apparently your requirement is different.
Q1 Are you saying the application you are working with requires a 1s and 0s representation of Binary numbers? for example:
User IntRep HexRep Binary
Tom 10 0xA 1010
sa 1 0x1 0001
If you need to use the string '1010' for user Tom instead of 0xA, you could create a function or stored procedure to return 1010 (given a hex representation)?|||What are you trying to do (deja vu) ?
doubt
hi,
How to search a word in the sqltable?.ie suppose the field name has data type nvarchar(200). From there i enter the persional address of that persion. now i want to know the information whoes are coming from particular city?. The city value can enter in the address field. how we can make search?...
with regards,
ks.kumar
*****************************************
* This message was posted via http://www.sqlmonster.com
*
* Report spam or abuse by clicking the following URL:
* http://www.sqlmonster.com/Uwe/Abuse...753175428d29c27
*****************************************You can use LIKE:
... WHERE col LIKE '%London%'
but this type of search is costly.
If you need to do this type of search then I suggest you separate the
address with a different column for each element: building, street, city,
state/region, postal code, country, etc. This is much easier and more
efficient to search.
--
David Portas
SQL Server MVP
--
Sunday, March 11, 2012
Double Inner Join
I have a table where 2 field in a table which are linked to 2 other different tables.
I need to retrieve values from the 3 tables.
Can I use twice the inner join ? like :
select f_tb2,f_tb3,* from tb1
inner join tb2 on tb2.f_id=tb1.an_id and
inner join tb3 on tb3.f_id=tb1.another_id
etc...
Or something like that ?
Or any other method ?
Thanks for help.Originally posted by Plarde
I have a table where 2 field in a table which are linked to 2 other different tables.
I need to retrieve values from the 3 tables.
Can I use twice the inner join ? like :
select f_tb2,f_tb3,* from tb1
inner join tb2 on tb2.f_id=tb1.an_id and
inner join tb3 on tb3.f_id=tb1.another_id
etc...
Or something like that ?
Or any other method ?
Thanks for help.
Yes you can join many columns in a column to any number of tables. Frequently there will be more than one column in a table which needs to correspond to other tables.
When you start talking about outer joining a table to more than one table is when you may run into limitations, but in this case you will definitely be able to do that :)|||yes you can do it, with one correction and one suggestion:
select f_tb2, f_tb3, tb1.*
from tb1
inner join tb2 on tb2.f_id=tb1.an_id and
inner join tb3 on tb3.f_id=tb1.another_id
correction: remove the word "and" from between the joins
suggestion: qualify the asterisk, otherwise it returns all columns from all tables|||here is a rather large query we use regularly with multiple joins:
select item.item,
Item.description,
LocInv.Item_Desc as LIDesc,
WOH.Item_Desc as WOHDesc,
WOD.Item_desc as WODDesc,
WI.Item_desc as WIDesc,
BOMD.Item_desc as BOMDDesc,
BOMH.Item_desc as BOMHDesc
from Item with (nolock)
inner join Location_inventory LocInv with (nolock)
on Item.Item = LocInv.Item
inner join Work_Order_Header WOH with (nolock)
on Item.Item = WOH.Item
inner join Work_Order_Detail WOD with (nolock)
on Item.Item = WOD.Item
inner join Work_Instruction WI with (nolock)
on Item.Item = WI.Item
inner join Bill_Of_Materials_Detail BOMD with (nolock)
on Item.Item = BOMD.Item
inner join Bill_Of_Materials_Header BOMH with (nolock)
on Item.Item = BOMH.Item
where (Item.Description <> LocInv.Item_Desc and Item.Item = LocInv.Item)
or (Item.Description <> WOH.Item_Desc and Item.Item = WOH.Item)
or (Item.Description <> WOD.Item_Desc and Item.Item = WOD.Item)
or (Item.Description <> WI.Item_Desc and Item.Item = WI.Item)
or (Item.Description <> BOMD.Item_Desc and Item.Item = BOMD.Item)
or (Item.Description <> BOMH.Item_Desc and Item.Item = BOMH.Item)
I need to retrieve values from the 3 tables.
Can I use twice the inner join ? like :
select f_tb2,f_tb3,* from tb1
inner join tb2 on tb2.f_id=tb1.an_id and
inner join tb3 on tb3.f_id=tb1.another_id
etc...
Or something like that ?
Or any other method ?
Thanks for help.Originally posted by Plarde
I have a table where 2 field in a table which are linked to 2 other different tables.
I need to retrieve values from the 3 tables.
Can I use twice the inner join ? like :
select f_tb2,f_tb3,* from tb1
inner join tb2 on tb2.f_id=tb1.an_id and
inner join tb3 on tb3.f_id=tb1.another_id
etc...
Or something like that ?
Or any other method ?
Thanks for help.
Yes you can join many columns in a column to any number of tables. Frequently there will be more than one column in a table which needs to correspond to other tables.
When you start talking about outer joining a table to more than one table is when you may run into limitations, but in this case you will definitely be able to do that :)|||yes you can do it, with one correction and one suggestion:
select f_tb2, f_tb3, tb1.*
from tb1
inner join tb2 on tb2.f_id=tb1.an_id and
inner join tb3 on tb3.f_id=tb1.another_id
correction: remove the word "and" from between the joins
suggestion: qualify the asterisk, otherwise it returns all columns from all tables|||here is a rather large query we use regularly with multiple joins:
select item.item,
Item.description,
LocInv.Item_Desc as LIDesc,
WOH.Item_Desc as WOHDesc,
WOD.Item_desc as WODDesc,
WI.Item_desc as WIDesc,
BOMD.Item_desc as BOMDDesc,
BOMH.Item_desc as BOMHDesc
from Item with (nolock)
inner join Location_inventory LocInv with (nolock)
on Item.Item = LocInv.Item
inner join Work_Order_Header WOH with (nolock)
on Item.Item = WOH.Item
inner join Work_Order_Detail WOD with (nolock)
on Item.Item = WOD.Item
inner join Work_Instruction WI with (nolock)
on Item.Item = WI.Item
inner join Bill_Of_Materials_Detail BOMD with (nolock)
on Item.Item = BOMD.Item
inner join Bill_Of_Materials_Header BOMH with (nolock)
on Item.Item = BOMH.Item
where (Item.Description <> LocInv.Item_Desc and Item.Item = LocInv.Item)
or (Item.Description <> WOH.Item_Desc and Item.Item = WOH.Item)
or (Item.Description <> WOD.Item_Desc and Item.Item = WOD.Item)
or (Item.Description <> WI.Item_Desc and Item.Item = WI.Item)
or (Item.Description <> BOMD.Item_Desc and Item.Item = BOMD.Item)
or (Item.Description <> BOMH.Item_Desc and Item.Item = BOMH.Item)
Friday, March 9, 2012
Double Byte Characters scrambled in PDF Output
Does anybody know how I can Clean up some European Characters such as "?"
in a text field:
"45000 m² new office project designed by Arquitectonica. Revised proposal
and clarifications issued to client on 26 Aug: 1239K? for MOEX, 985K for
Synthese as option. Ten competitors, including Coteba. Contract award
expected by 15 Sept 04."
Is there a certain data type to use or is there a conversion fucntion to
use?
HelpWhat you can do in this case is :
write custom code for all the clean up.
You can write code tab of the Report Properties in the Report tab under main menu.
Write your own VB.NET code.
>--Original Message--
>Does anybody know how I can Clean up some European Characters such as "?"
>in a text field:
>"45000 m=B2 new office project designed by Arquitectonica. Revised proposal
>and clarifications issued to client on 26 Aug: 1239K? for MOEX, 985K for
>Synthese as option. Ten competitors, including Coteba. Contract award
>expected by 15 Sept 04."
>Is there a certain data type to use or is there a conversion fucntion to
>use?
>Help
>
>.
>
in a text field:
"45000 m² new office project designed by Arquitectonica. Revised proposal
and clarifications issued to client on 26 Aug: 1239K? for MOEX, 985K for
Synthese as option. Ten competitors, including Coteba. Contract award
expected by 15 Sept 04."
Is there a certain data type to use or is there a conversion fucntion to
use?
HelpWhat you can do in this case is :
write custom code for all the clean up.
You can write code tab of the Report Properties in the Report tab under main menu.
Write your own VB.NET code.
>--Original Message--
>Does anybody know how I can Clean up some European Characters such as "?"
>in a text field:
>"45000 m=B2 new office project designed by Arquitectonica. Revised proposal
>and clarifications issued to client on 26 Aug: 1239K? for MOEX, 985K for
>Synthese as option. Ten competitors, including Coteba. Contract award
>expected by 15 Sept 04."
>Is there a certain data type to use or is there a conversion fucntion to
>use?
>Help
>
>.
>
Double Byte Character Problem
I am storing text in sql server 2000. DataType for the field is ntext. I am
storing a string which has characters both in english and in Japanese. If the
number of characters in string are upto 4000 then every thing works fine. If
I retrieve the data it shows me exactly the string which I stored. But the
moment chracters exceed 4000(even 4001) the japanese characters are converted
to question mark("?") . Collation for the database is
SQL_Latin1_General_CP1_Cl_AS. Please help me.
Thank you.
Junaid Rehman
Are you observing this behavior from Query Analyzer or your application
program?
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>I am storing text in sql server 2000. DataType for the field is ntext. I am
> storing a string which has characters both in english and in Japanese. If
> the
> number of characters in string are upto 4000 then every thing works fine.
> If
> I retrieve the data it shows me exactly the string which I stored. But the
> moment chracters exceed 4000(even 4001) the japanese characters are
> converted
> to question mark("?") . Collation for the database is
> SQL_Latin1_General_CP1_Cl_AS. Please help me.
> Thank you.
> Junaid Rehman
|||I made a simple page in vb.net which has two textboxes and a submit button. I
enter the text in first textbox and when I submit the form data is inserted
into database. When the characters entered in textbox are less then 4000 the
data which is inserted in database has japanese characters represented as
small boxes. Then I retrieve the the data stored in database in 2nd textbox
and it appears fine. But when characters entered in first textbox exceed 4000
the data which is inserted into database has japanese characters converted to
question mark("?") instead of those small boxes (which were there earlier
when characters were less then 4000). When I retrieve this text in 2nd box it
shows question marks instead of Japanese characters. I explained it in bit
detail so that you know what I am exactly doing.
Junaid Rehman
"Dan Guzman" wrote:
> Are you observing this behavior from Query Analyzer or your application
> program?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
> news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>
>
|||Below is a code snippet that properly stores unicode values as described in
your narrative. Also, I used a font that properly rendered the unicode
characters. You might check this against your code. If you still have
problems, please post your code.
Dim mySqlConnection As New SqlConnection(connectionString)
mySqlConnection.Open()
Dim mySqlCommand As New SqlCommand
mySqlCommand.Connection = mySqlConnection
'create test table
mySqlCommand.CommandText = "CREATE TABLE #MyTable(MyData ntext)"
mySqlCommand.ExecuteNonQuery()
'create unicode value from form
mySqlCommand.CommandText = "INSERT INTO #MyTable VALUES(@.MyData)"
Dim myDataParamter As New SqlParameter("@.MyData", SqlDbType.NText)
myDataParamter.Value = Me.TextBox1.Text
mySqlCommand.Parameters.Add(myDataParamter)
mySqlCommand.ExecuteNonQuery()
'retrieve inserted value
mySqlCommand.CommandText = "SELECT MyData FROM #MyTable"
Dim SqlDataReader As SqlDataReader = mySqlCommand.ExecuteReader
SqlDataReader.Read()
Me.TextBox2.Text = SqlDataReader.GetString(0)
SqlDataReader.Close()
'cleanup
mySqlCommand.CommandText = "DROP TABLE #MyTable"
mySqlCommand.ExecuteNonQuery()
mySqlConnection.Close()
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:6C3267D6-DD99-417A-9B65-D40FE6217DF2@.microsoft.com...[vbcol=seagreen]
>I made a simple page in vb.net which has two textboxes and a submit button.
>I
> enter the text in first textbox and when I submit the form data is
> inserted
> into database. When the characters entered in textbox are less then 4000
> the
> data which is inserted in database has japanese characters represented as
> small boxes. Then I retrieve the the data stored in database in 2nd
> textbox
> and it appears fine. But when characters entered in first textbox exceed
> 4000
> the data which is inserted into database has japanese characters converted
> to
> question mark("?") instead of those small boxes (which were there earlier
> when characters were less then 4000). When I retrieve this text in 2nd box
> it
> shows question marks instead of Japanese characters. I explained it in bit
> detail so that you know what I am exactly doing.
> Junaid Rehman
> "Dan Guzman" wrote:
storing a string which has characters both in english and in Japanese. If the
number of characters in string are upto 4000 then every thing works fine. If
I retrieve the data it shows me exactly the string which I stored. But the
moment chracters exceed 4000(even 4001) the japanese characters are converted
to question mark("?") . Collation for the database is
SQL_Latin1_General_CP1_Cl_AS. Please help me.
Thank you.
Junaid Rehman
Are you observing this behavior from Query Analyzer or your application
program?
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>I am storing text in sql server 2000. DataType for the field is ntext. I am
> storing a string which has characters both in english and in Japanese. If
> the
> number of characters in string are upto 4000 then every thing works fine.
> If
> I retrieve the data it shows me exactly the string which I stored. But the
> moment chracters exceed 4000(even 4001) the japanese characters are
> converted
> to question mark("?") . Collation for the database is
> SQL_Latin1_General_CP1_Cl_AS. Please help me.
> Thank you.
> Junaid Rehman
|||I made a simple page in vb.net which has two textboxes and a submit button. I
enter the text in first textbox and when I submit the form data is inserted
into database. When the characters entered in textbox are less then 4000 the
data which is inserted in database has japanese characters represented as
small boxes. Then I retrieve the the data stored in database in 2nd textbox
and it appears fine. But when characters entered in first textbox exceed 4000
the data which is inserted into database has japanese characters converted to
question mark("?") instead of those small boxes (which were there earlier
when characters were less then 4000). When I retrieve this text in 2nd box it
shows question marks instead of Japanese characters. I explained it in bit
detail so that you know what I am exactly doing.
Junaid Rehman
"Dan Guzman" wrote:
> Are you observing this behavior from Query Analyzer or your application
> program?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
> news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>
>
|||Below is a code snippet that properly stores unicode values as described in
your narrative. Also, I used a font that properly rendered the unicode
characters. You might check this against your code. If you still have
problems, please post your code.
Dim mySqlConnection As New SqlConnection(connectionString)
mySqlConnection.Open()
Dim mySqlCommand As New SqlCommand
mySqlCommand.Connection = mySqlConnection
'create test table
mySqlCommand.CommandText = "CREATE TABLE #MyTable(MyData ntext)"
mySqlCommand.ExecuteNonQuery()
'create unicode value from form
mySqlCommand.CommandText = "INSERT INTO #MyTable VALUES(@.MyData)"
Dim myDataParamter As New SqlParameter("@.MyData", SqlDbType.NText)
myDataParamter.Value = Me.TextBox1.Text
mySqlCommand.Parameters.Add(myDataParamter)
mySqlCommand.ExecuteNonQuery()
'retrieve inserted value
mySqlCommand.CommandText = "SELECT MyData FROM #MyTable"
Dim SqlDataReader As SqlDataReader = mySqlCommand.ExecuteReader
SqlDataReader.Read()
Me.TextBox2.Text = SqlDataReader.GetString(0)
SqlDataReader.Close()
'cleanup
mySqlCommand.CommandText = "DROP TABLE #MyTable"
mySqlCommand.ExecuteNonQuery()
mySqlConnection.Close()
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:6C3267D6-DD99-417A-9B65-D40FE6217DF2@.microsoft.com...[vbcol=seagreen]
>I made a simple page in vb.net which has two textboxes and a submit button.
>I
> enter the text in first textbox and when I submit the form data is
> inserted
> into database. When the characters entered in textbox are less then 4000
> the
> data which is inserted in database has japanese characters represented as
> small boxes. Then I retrieve the the data stored in database in 2nd
> textbox
> and it appears fine. But when characters entered in first textbox exceed
> 4000
> the data which is inserted into database has japanese characters converted
> to
> question mark("?") instead of those small boxes (which were there earlier
> when characters were less then 4000). When I retrieve this text in 2nd box
> it
> shows question marks instead of Japanese characters. I explained it in bit
> detail so that you know what I am exactly doing.
> Junaid Rehman
> "Dan Guzman" wrote:
Double Byte Character Problem
I am storing text in sql server 2000. DataType for the field is ntext. I am
storing a string which has characters both in english and in Japanese. If the
number of characters in string are upto 4000 then every thing works fine. If
I retrieve the data it shows me exactly the string which I stored. But the
moment chracters exceed 4000(even 4001) the japanese characters are converted
to question mark("?") . Collation for the database is
SQL_Latin1_General_CP1_Cl_AS. Please help me.
Thank you.
Junaid RehmanAre you observing this behavior from Query Analyzer or your application
program?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>I am storing text in sql server 2000. DataType for the field is ntext. I am
> storing a string which has characters both in english and in Japanese. If
> the
> number of characters in string are upto 4000 then every thing works fine.
> If
> I retrieve the data it shows me exactly the string which I stored. But the
> moment chracters exceed 4000(even 4001) the japanese characters are
> converted
> to question mark("?") . Collation for the database is
> SQL_Latin1_General_CP1_Cl_AS. Please help me.
> Thank you.
> Junaid Rehman|||I made a simple page in vb.net which has two textboxes and a submit button. I
enter the text in first textbox and when I submit the form data is inserted
into database. When the characters entered in textbox are less then 4000 the
data which is inserted in database has japanese characters represented as
small boxes. Then I retrieve the the data stored in database in 2nd textbox
and it appears fine. But when characters entered in first textbox exceed 4000
the data which is inserted into database has japanese characters converted to
question mark("?") instead of those small boxes (which were there earlier
when characters were less then 4000). When I retrieve this text in 2nd box it
shows question marks instead of Japanese characters. I explained it in bit
detail so that you know what I am exactly doing.
Junaid Rehman
"Dan Guzman" wrote:
> Are you observing this behavior from Query Analyzer or your application
> program?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
> news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
> >I am storing text in sql server 2000. DataType for the field is ntext. I am
> > storing a string which has characters both in english and in Japanese. If
> > the
> > number of characters in string are upto 4000 then every thing works fine.
> > If
> > I retrieve the data it shows me exactly the string which I stored. But the
> > moment chracters exceed 4000(even 4001) the japanese characters are
> > converted
> > to question mark("?") . Collation for the database is
> > SQL_Latin1_General_CP1_Cl_AS. Please help me.
> > Thank you.
> >
> > Junaid Rehman
>
>|||Below is a code snippet that properly stores unicode values as described in
your narrative. Also, I used a font that properly rendered the unicode
characters. You might check this against your code. If you still have
problems, please post your code.
Dim mySqlConnection As New SqlConnection(connectionString)
mySqlConnection.Open()
Dim mySqlCommand As New SqlCommand
mySqlCommand.Connection = mySqlConnection
'create test table
mySqlCommand.CommandText = "CREATE TABLE #MyTable(MyData ntext)"
mySqlCommand.ExecuteNonQuery()
'create unicode value from form
mySqlCommand.CommandText = "INSERT INTO #MyTable VALUES(@.MyData)"
Dim myDataParamter As New SqlParameter("@.MyData", SqlDbType.NText)
myDataParamter.Value = Me.TextBox1.Text
mySqlCommand.Parameters.Add(myDataParamter)
mySqlCommand.ExecuteNonQuery()
'retrieve inserted value
mySqlCommand.CommandText = "SELECT MyData FROM #MyTable"
Dim SqlDataReader As SqlDataReader = mySqlCommand.ExecuteReader
SqlDataReader.Read()
Me.TextBox2.Text = SqlDataReader.GetString(0)
SqlDataReader.Close()
'cleanup
mySqlCommand.CommandText = "DROP TABLE #MyTable"
mySqlCommand.ExecuteNonQuery()
mySqlConnection.Close()
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:6C3267D6-DD99-417A-9B65-D40FE6217DF2@.microsoft.com...
>I made a simple page in vb.net which has two textboxes and a submit button.
>I
> enter the text in first textbox and when I submit the form data is
> inserted
> into database. When the characters entered in textbox are less then 4000
> the
> data which is inserted in database has japanese characters represented as
> small boxes. Then I retrieve the the data stored in database in 2nd
> textbox
> and it appears fine. But when characters entered in first textbox exceed
> 4000
> the data which is inserted into database has japanese characters converted
> to
> question mark("?") instead of those small boxes (which were there earlier
> when characters were less then 4000). When I retrieve this text in 2nd box
> it
> shows question marks instead of Japanese characters. I explained it in bit
> detail so that you know what I am exactly doing.
> Junaid Rehman
> "Dan Guzman" wrote:
>> Are you observing this behavior from Query Analyzer or your application
>> program?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
>> news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>> >I am storing text in sql server 2000. DataType for the field is ntext. I
>> >am
>> > storing a string which has characters both in english and in Japanese.
>> > If
>> > the
>> > number of characters in string are upto 4000 then every thing works
>> > fine.
>> > If
>> > I retrieve the data it shows me exactly the string which I stored. But
>> > the
>> > moment chracters exceed 4000(even 4001) the japanese characters are
>> > converted
>> > to question mark("?") . Collation for the database is
>> > SQL_Latin1_General_CP1_Cl_AS. Please help me.
>> > Thank you.
>> >
>> > Junaid Rehman
>>
storing a string which has characters both in english and in Japanese. If the
number of characters in string are upto 4000 then every thing works fine. If
I retrieve the data it shows me exactly the string which I stored. But the
moment chracters exceed 4000(even 4001) the japanese characters are converted
to question mark("?") . Collation for the database is
SQL_Latin1_General_CP1_Cl_AS. Please help me.
Thank you.
Junaid RehmanAre you observing this behavior from Query Analyzer or your application
program?
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>I am storing text in sql server 2000. DataType for the field is ntext. I am
> storing a string which has characters both in english and in Japanese. If
> the
> number of characters in string are upto 4000 then every thing works fine.
> If
> I retrieve the data it shows me exactly the string which I stored. But the
> moment chracters exceed 4000(even 4001) the japanese characters are
> converted
> to question mark("?") . Collation for the database is
> SQL_Latin1_General_CP1_Cl_AS. Please help me.
> Thank you.
> Junaid Rehman|||I made a simple page in vb.net which has two textboxes and a submit button. I
enter the text in first textbox and when I submit the form data is inserted
into database. When the characters entered in textbox are less then 4000 the
data which is inserted in database has japanese characters represented as
small boxes. Then I retrieve the the data stored in database in 2nd textbox
and it appears fine. But when characters entered in first textbox exceed 4000
the data which is inserted into database has japanese characters converted to
question mark("?") instead of those small boxes (which were there earlier
when characters were less then 4000). When I retrieve this text in 2nd box it
shows question marks instead of Japanese characters. I explained it in bit
detail so that you know what I am exactly doing.
Junaid Rehman
"Dan Guzman" wrote:
> Are you observing this behavior from Query Analyzer or your application
> program?
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
> news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
> >I am storing text in sql server 2000. DataType for the field is ntext. I am
> > storing a string which has characters both in english and in Japanese. If
> > the
> > number of characters in string are upto 4000 then every thing works fine.
> > If
> > I retrieve the data it shows me exactly the string which I stored. But the
> > moment chracters exceed 4000(even 4001) the japanese characters are
> > converted
> > to question mark("?") . Collation for the database is
> > SQL_Latin1_General_CP1_Cl_AS. Please help me.
> > Thank you.
> >
> > Junaid Rehman
>
>|||Below is a code snippet that properly stores unicode values as described in
your narrative. Also, I used a font that properly rendered the unicode
characters. You might check this against your code. If you still have
problems, please post your code.
Dim mySqlConnection As New SqlConnection(connectionString)
mySqlConnection.Open()
Dim mySqlCommand As New SqlCommand
mySqlCommand.Connection = mySqlConnection
'create test table
mySqlCommand.CommandText = "CREATE TABLE #MyTable(MyData ntext)"
mySqlCommand.ExecuteNonQuery()
'create unicode value from form
mySqlCommand.CommandText = "INSERT INTO #MyTable VALUES(@.MyData)"
Dim myDataParamter As New SqlParameter("@.MyData", SqlDbType.NText)
myDataParamter.Value = Me.TextBox1.Text
mySqlCommand.Parameters.Add(myDataParamter)
mySqlCommand.ExecuteNonQuery()
'retrieve inserted value
mySqlCommand.CommandText = "SELECT MyData FROM #MyTable"
Dim SqlDataReader As SqlDataReader = mySqlCommand.ExecuteReader
SqlDataReader.Read()
Me.TextBox2.Text = SqlDataReader.GetString(0)
SqlDataReader.Close()
'cleanup
mySqlCommand.CommandText = "DROP TABLE #MyTable"
mySqlCommand.ExecuteNonQuery()
mySqlConnection.Close()
--
Hope this helps.
Dan Guzman
SQL Server MVP
"rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
news:6C3267D6-DD99-417A-9B65-D40FE6217DF2@.microsoft.com...
>I made a simple page in vb.net which has two textboxes and a submit button.
>I
> enter the text in first textbox and when I submit the form data is
> inserted
> into database. When the characters entered in textbox are less then 4000
> the
> data which is inserted in database has japanese characters represented as
> small boxes. Then I retrieve the the data stored in database in 2nd
> textbox
> and it appears fine. But when characters entered in first textbox exceed
> 4000
> the data which is inserted into database has japanese characters converted
> to
> question mark("?") instead of those small boxes (which were there earlier
> when characters were less then 4000). When I retrieve this text in 2nd box
> it
> shows question marks instead of Japanese characters. I explained it in bit
> detail so that you know what I am exactly doing.
> Junaid Rehman
> "Dan Guzman" wrote:
>> Are you observing this behavior from Query Analyzer or your application
>> program?
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "rehmanjr" <rehmanjr@.discussions.microsoft.com> wrote in message
>> news:29958380-55A3-4BC7-9383-5DE2738B0120@.microsoft.com...
>> >I am storing text in sql server 2000. DataType for the field is ntext. I
>> >am
>> > storing a string which has characters both in english and in Japanese.
>> > If
>> > the
>> > number of characters in string are upto 4000 then every thing works
>> > fine.
>> > If
>> > I retrieve the data it shows me exactly the string which I stored. But
>> > the
>> > moment chracters exceed 4000(even 4001) the japanese characters are
>> > converted
>> > to question mark("?") . Collation for the database is
>> > SQL_Latin1_General_CP1_Cl_AS. Please help me.
>> > Thank you.
>> >
>> > Junaid Rehman
>>
Sunday, February 26, 2012
Dont display the field if it contains a zero value
I'm working on a report for accounting. Usually debit and credit prob. I don't want to display the field if it contains zero. It creates a gap on my reports and want to avoid it.
Anyone who can help?
Thanks.Using CR 8.5 and RDC, each field has a setting called 'SuppressIfZero' and one called 'SuppressIfBlank'. As the name implies, setting the 'SuppressIfZero' to True will suppress the field if the value is Zero, setting the 'SuppressIfBlank' to True will suppress the field if it's blank.|||I did what you said but I still get a gap between my text. How can I eliminate it?
Thanks.|||Try to put the field in a separate section (If possible), and Suppress section if that value is blank.|||Hi
try suppressing the field or use a formula
if (fieldname) = "0" then "No Data"
I was working on the same thing in crystal reports 9 and got up to this finally.
c ya
Anyone who can help?
Thanks.Using CR 8.5 and RDC, each field has a setting called 'SuppressIfZero' and one called 'SuppressIfBlank'. As the name implies, setting the 'SuppressIfZero' to True will suppress the field if the value is Zero, setting the 'SuppressIfBlank' to True will suppress the field if it's blank.|||I did what you said but I still get a gap between my text. How can I eliminate it?
Thanks.|||Try to put the field in a separate section (If possible), and Suppress section if that value is blank.|||Hi
try suppressing the field or use a formula
if (fieldname) = "0" then "No Data"
I was working on the same thing in crystal reports 9 and got up to this finally.
c ya
Domains in SQL Server?
Hello all,
I've worked mostly with FirebirdSQL and Firebird has a nifty little
feature called "domains" which are basically pre-defined field types
which can included native field type (int, varchar, etc), constraints,
etc. Instead of defining an Int field with a default value of zero
that must be between 1 and 100000 everytime you need that kind of
field, you can just create that domain and assign that domain as a
field/column type when created a table.
This is a time saver as well as making data types standardized in my
database.
Is there an equivelant in SQL Server?
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."Yes, User Defined Data Types.
Have a look in SQL Books On Line for sp_addtype
You will need to bind a rule to your UDDT with the syntax "CREATE RULE" to
fully implement what you're after
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:Ox7G4Y4GGHA.532@.TK2MSFTNGP15.phx.gbl...
>
> Hello all,
> I've worked mostly with FirebirdSQL and Firebird has a nifty little
> feature called "domains" which are basically pre-defined field types
> which can included native field type (int, varchar, etc), constraints,
> etc. Instead of defining an Int field with a default value of zero
> that must be between 1 and 100000 everytime you need that kind of
> field, you can just create that domain and assign that domain as a
> field/column type when created a table.
> This is a time saver as well as making data types standardized in my
> database.
> Is there an equivelant in SQL Server?
>
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."|||Ryan enlightened me by writing:
> Yes, User Defined Data Types.
> Have a look in SQL Books On Line for sp_addtype
> You will need to bind a rule to your UDDT with the syntax "CREATE
> RULE" to fully implement what you're after
Excellent.
Thanks Ryan
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."|||But you should be aware that Default and Rule objects (they you tie to user
defined datatypes, or
"alias datatypes" as they are called in 2005) are deprecated in 2005. This m
eans that Default and
Rule objects will be removed in some future version of SQL Server (in favor
for Constraints).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <luv2program2000@.yahoo.com> wrote in message news:%23YmZyu4GGHA.216@.TK2MSFTNGP15.phx.g
bl...
> Ryan enlightened me by writing:
>
> Excellent.
> Thanks Ryan
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."
I've worked mostly with FirebirdSQL and Firebird has a nifty little
feature called "domains" which are basically pre-defined field types
which can included native field type (int, varchar, etc), constraints,
etc. Instead of defining an Int field with a default value of zero
that must be between 1 and 100000 everytime you need that kind of
field, you can just create that domain and assign that domain as a
field/column type when created a table.
This is a time saver as well as making data types standardized in my
database.
Is there an equivelant in SQL Server?
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."Yes, User Defined Data Types.
Have a look in SQL Books On Line for sp_addtype
You will need to bind a rule to your UDDT with the syntax "CREATE RULE" to
fully implement what you're after
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:Ox7G4Y4GGHA.532@.TK2MSFTNGP15.phx.gbl...
>
> Hello all,
> I've worked mostly with FirebirdSQL and Firebird has a nifty little
> feature called "domains" which are basically pre-defined field types
> which can included native field type (int, varchar, etc), constraints,
> etc. Instead of defining an Int field with a default value of zero
> that must be between 1 and 100000 everytime you need that kind of
> field, you can just create that domain and assign that domain as a
> field/column type when created a table.
> This is a time saver as well as making data types standardized in my
> database.
> Is there an equivelant in SQL Server?
>
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."|||Ryan enlightened me by writing:
> Yes, User Defined Data Types.
> Have a look in SQL Books On Line for sp_addtype
> You will need to bind a rule to your UDDT with the syntax "CREATE
> RULE" to fully implement what you're after
Excellent.
Thanks Ryan
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."|||But you should be aware that Default and Rule objects (they you tie to user
defined datatypes, or
"alias datatypes" as they are called in 2005) are deprecated in 2005. This m
eans that Default and
Rule objects will be removed in some future version of SQL Server (in favor
for Constraints).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <luv2program2000@.yahoo.com> wrote in message news:%23YmZyu4GGHA.216@.TK2MSFTNGP15.phx.g
bl...
> Ryan enlightened me by writing:
>
> Excellent.
> Thanks Ryan
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."
Domains in SQL Server?
Hello all,
I've worked mostly with FirebirdSQL and Firebird has a nifty little
feature called "domains" which are basically pre-defined field types
which can included native field type (int, varchar, etc), constraints,
etc. Instead of defining an Int field with a default value of zero
that must be between 1 and 100000 everytime you need that kind of
field, you can just create that domain and assign that domain as a
field/column type when created a table.
This is a time saver as well as making data types standardized in my
database.
Is there an equivelant in SQL Server?
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."
Yes, User Defined Data Types.
Have a look in SQL Books On Line for sp_addtype
You will need to bind a rule to your UDDT with the syntax "CREATE RULE" to
fully implement what you're after
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:Ox7G4Y4GGHA.532@.TK2MSFTNGP15.phx.gbl...
>
> Hello all,
> I've worked mostly with FirebirdSQL and Firebird has a nifty little
> feature called "domains" which are basically pre-defined field types
> which can included native field type (int, varchar, etc), constraints,
> etc. Instead of defining an Int field with a default value of zero
> that must be between 1 and 100000 everytime you need that kind of
> field, you can just create that domain and assign that domain as a
> field/column type when created a table.
> This is a time saver as well as making data types standardized in my
> database.
> Is there an equivelant in SQL Server?
>
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."
|||Ryan enlightened me by writing:
> Yes, User Defined Data Types.
> Have a look in SQL Books On Line for sp_addtype
> You will need to bind a rule to your UDDT with the syntax "CREATE
> RULE" to fully implement what you're after
Excellent.
Thanks Ryan
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."
|||But you should be aware that Default and Rule objects (they you tie to user defined datatypes, or
"alias datatypes" as they are called in 2005) are deprecated in 2005. This means that Default and
Rule objects will be removed in some future version of SQL Server (in favor for Constraints).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <luv2program2000@.yahoo.com> wrote in message news:%23YmZyu4GGHA.216@.TK2MSFTNGP15.phx.gbl...
> Ryan enlightened me by writing:
>
> Excellent.
> Thanks Ryan
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."
I've worked mostly with FirebirdSQL and Firebird has a nifty little
feature called "domains" which are basically pre-defined field types
which can included native field type (int, varchar, etc), constraints,
etc. Instead of defining an Int field with a default value of zero
that must be between 1 and 100000 everytime you need that kind of
field, you can just create that domain and assign that domain as a
field/column type when created a table.
This is a time saver as well as making data types standardized in my
database.
Is there an equivelant in SQL Server?
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."
Yes, User Defined Data Types.
Have a look in SQL Books On Line for sp_addtype
You will need to bind a rule to your UDDT with the syntax "CREATE RULE" to
fully implement what you're after
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:Ox7G4Y4GGHA.532@.TK2MSFTNGP15.phx.gbl...
>
> Hello all,
> I've worked mostly with FirebirdSQL and Firebird has a nifty little
> feature called "domains" which are basically pre-defined field types
> which can included native field type (int, varchar, etc), constraints,
> etc. Instead of defining an Int field with a default value of zero
> that must be between 1 and 100000 everytime you need that kind of
> field, you can just create that domain and assign that domain as a
> field/column type when created a table.
> This is a time saver as well as making data types standardized in my
> database.
> Is there an equivelant in SQL Server?
>
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."
|||Ryan enlightened me by writing:
> Yes, User Defined Data Types.
> Have a look in SQL Books On Line for sp_addtype
> You will need to bind a rule to your UDDT with the syntax "CREATE
> RULE" to fully implement what you're after
Excellent.
Thanks Ryan
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."
|||But you should be aware that Default and Rule objects (they you tie to user defined datatypes, or
"alias datatypes" as they are called in 2005) are deprecated in 2005. This means that Default and
Rule objects will be removed in some future version of SQL Server (in favor for Constraints).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <luv2program2000@.yahoo.com> wrote in message news:%23YmZyu4GGHA.216@.TK2MSFTNGP15.phx.gbl...
> Ryan enlightened me by writing:
>
> Excellent.
> Thanks Ryan
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."
Domains in SQL Server?
Hello all,
I've worked mostly with FirebirdSQL and Firebird has a nifty little
feature called "domains" which are basically pre-defined field types
which can included native field type (int, varchar, etc), constraints,
etc. Instead of defining an Int field with a default value of zero
that must be between 1 and 100000 everytime you need that kind of
field, you can just create that domain and assign that domain as a
field/column type when created a table.
This is a time saver as well as making data types standardized in my
database.
Is there an equivelant in SQL Server?
--
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."Yes, User Defined Data Types.
Have a look in SQL Books On Line for sp_addtype
You will need to bind a rule to your UDDT with the syntax "CREATE RULE" to
fully implement what you're after
--
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:Ox7G4Y4GGHA.532@.TK2MSFTNGP15.phx.gbl...
>
> Hello all,
> I've worked mostly with FirebirdSQL and Firebird has a nifty little
> feature called "domains" which are basically pre-defined field types
> which can included native field type (int, varchar, etc), constraints,
> etc. Instead of defining an Int field with a default value of zero
> that must be between 1 and 100000 everytime you need that kind of
> field, you can just create that domain and assign that domain as a
> field/column type when created a table.
> This is a time saver as well as making data types standardized in my
> database.
> Is there an equivelant in SQL Server?
>
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."|||Ryan enlightened me by writing:
> Yes, User Defined Data Types.
> Have a look in SQL Books On Line for sp_addtype
> You will need to bind a rule to your UDDT with the syntax "CREATE
> RULE" to fully implement what you're after
Excellent.
Thanks Ryan
--
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."|||But you should be aware that Default and Rule objects (they you tie to user defined datatypes, or
"alias datatypes" as they are called in 2005) are deprecated in 2005. This means that Default and
Rule objects will be removed in some future version of SQL Server (in favor for Constraints).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <luv2program2000@.yahoo.com> wrote in message news:%23YmZyu4GGHA.216@.TK2MSFTNGP15.phx.gbl...
> Ryan enlightened me by writing:
>> Yes, User Defined Data Types.
>> Have a look in SQL Books On Line for sp_addtype
>> You will need to bind a rule to your UDDT with the syntax "CREATE
>> RULE" to fully implement what you're after
> Excellent.
> Thanks Ryan
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."
I've worked mostly with FirebirdSQL and Firebird has a nifty little
feature called "domains" which are basically pre-defined field types
which can included native field type (int, varchar, etc), constraints,
etc. Instead of defining an Int field with a default value of zero
that must be between 1 and 100000 everytime you need that kind of
field, you can just create that domain and assign that domain as a
field/column type when created a table.
This is a time saver as well as making data types standardized in my
database.
Is there an equivelant in SQL Server?
--
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."Yes, User Defined Data Types.
Have a look in SQL Books On Line for sp_addtype
You will need to bind a rule to your UDDT with the syntax "CREATE RULE" to
fully implement what you're after
--
HTH. Ryan
"Lee" <luv2program2000@.yahoo.com> wrote in message
news:Ox7G4Y4GGHA.532@.TK2MSFTNGP15.phx.gbl...
>
> Hello all,
> I've worked mostly with FirebirdSQL and Firebird has a nifty little
> feature called "domains" which are basically pre-defined field types
> which can included native field type (int, varchar, etc), constraints,
> etc. Instead of defining an Int field with a default value of zero
> that must be between 1 and 100000 everytime you need that kind of
> field, you can just create that domain and assign that domain as a
> field/column type when created a table.
> This is a time saver as well as making data types standardized in my
> database.
> Is there an equivelant in SQL Server?
>
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."|||Ryan enlightened me by writing:
> Yes, User Defined Data Types.
> Have a look in SQL Books On Line for sp_addtype
> You will need to bind a rule to your UDDT with the syntax "CREATE
> RULE" to fully implement what you're after
Excellent.
Thanks Ryan
--
Warm Regards,
Lee
"Upon further investigation it appears that your software is missing
just one thing. It definitely needs more cow bell..."|||But you should be aware that Default and Rule objects (they you tie to user defined datatypes, or
"alias datatypes" as they are called in 2005) are deprecated in 2005. This means that Default and
Rule objects will be removed in some future version of SQL Server (in favor for Constraints).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Lee" <luv2program2000@.yahoo.com> wrote in message news:%23YmZyu4GGHA.216@.TK2MSFTNGP15.phx.gbl...
> Ryan enlightened me by writing:
>> Yes, User Defined Data Types.
>> Have a look in SQL Books On Line for sp_addtype
>> You will need to bind a rule to your UDDT with the syntax "CREATE
>> RULE" to fully implement what you're after
> Excellent.
> Thanks Ryan
> --
> Warm Regards,
> Lee
> "Upon further investigation it appears that your software is missing
> just one thing. It definitely needs more cow bell..."
Friday, February 17, 2012
doing a sum on a counted field
i have a dataset with a column that has 2 values. YES or NO.
i have to use a textbox inside a filtered(for yes) list that are to get
a count of yes. I have to use another list to do the same for NO.
using--Count(Fields!answer.Value). Now i want to perform a calculation
on the counts and for the life of me i cant do it. Since the txtboxes
are not contained in the same grouping i cannot reference them using
ReportItems!txt.value. Is there something i can change in my sql
statement to break out the counts as their own columns' please helpIn RS you could do a conditional sum using IIF:
=Sum( iif(Fields!answer.Value = "YES", 1, 0))
=Sum( iif(Fields!answer.Value = "NO", 1, 0))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"the finger" <m_kosurko@.hotmail.com> wrote in message
news:1115066710.809917.171580@.z14g2000cwz.googlegroups.com...
>i have a dataset with a column that has 2 values. YES or NO.
> i have to use a textbox inside a filtered(for yes) list that are to get
> a count of yes. I have to use another list to do the same for NO.
> using--Count(Fields!answer.Value). Now i want to perform a calculation
> on the counts and for the life of me i cant do it. Since the txtboxes
> are not contained in the same grouping i cannot reference them using
> ReportItems!txt.value. Is there something i can change in my sql
> statement to break out the counts as their own columns' please help
>
i have to use a textbox inside a filtered(for yes) list that are to get
a count of yes. I have to use another list to do the same for NO.
using--Count(Fields!answer.Value). Now i want to perform a calculation
on the counts and for the life of me i cant do it. Since the txtboxes
are not contained in the same grouping i cannot reference them using
ReportItems!txt.value. Is there something i can change in my sql
statement to break out the counts as their own columns' please helpIn RS you could do a conditional sum using IIF:
=Sum( iif(Fields!answer.Value = "YES", 1, 0))
=Sum( iif(Fields!answer.Value = "NO", 1, 0))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"the finger" <m_kosurko@.hotmail.com> wrote in message
news:1115066710.809917.171580@.z14g2000cwz.googlegroups.com...
>i have a dataset with a column that has 2 values. YES or NO.
> i have to use a textbox inside a filtered(for yes) list that are to get
> a count of yes. I have to use another list to do the same for NO.
> using--Count(Fields!answer.Value). Now i want to perform a calculation
> on the counts and for the life of me i cant do it. Since the txtboxes
> are not contained in the same grouping i cannot reference them using
> ReportItems!txt.value. Is there something i can change in my sql
> statement to break out the counts as their own columns' please help
>
Doing a SELECT x FROM y WHERE z IN (@param) in stored procedure?
Hello,
I have a table with a foreign key field. I need to retrieve all the records where the foreign key matches any of a set. In plain ol' SQL this is accomplished with the IN(a,b,c) statement but I can't get that to work in a stored procedure.
How would I do this? I can imagine that I could parse the input string and create a temporary table and use that to do a join but that seems rather convoluted.
Any tips highly appreciated! If I'm not being clear I'll gladly post more details.
Thanks,
Noc
PS SQL 2000, ASP.NET 1.1, VS 2003.Have a look at the following article:
http://www.sqlteam.com/item.asp?ItemID=11499|||Many thanks! Looks like I was on the righ track with the temporary table idea but it sure is a lot easier to implement with that sample code.
Doing a SELECT on a different field than the field in the GROUP BY
I'm using SQL Server 2005.
I've got table tblSaleAddresses which looks something like this:
AddressID SaleID Address1 Address2 ..... DateEffective
- -
1 100 Unit 20 100 Easy St 1 Feb 04
2 100 10 Any St 1 Oct 05
3 101 85 Main St 1 Mar 04
4 101 20 Lovers Ln 15 Sep 05
I want to select the current address for each SaleID - that is the address for each SaleID WHERE Max(DateEffective) and WHERE DateEffective <= GETDATE().
In the case above, I should get AddressID 1 (AddressID 2 isn't yet effective because it's still in the future) for SaleID 100 and AddressID 4 for SaleID 101.
I'm having trouble because I want to SELECT AddressID but GROUP BY SaleID.
I know you can do a GROUP BY subquery and then join on the SaleID and DateEffective, but it doesn't seem right to me you have to join on a date field (which theorectically isn't very unique). I expected that there would by a statement that would allow you to select the specific AddressID for the MAX(DateEffective)?
Or have I organised my data in the wrong way? And if so, what should I have done differently?
Here's the SELECT statement that joins on the date field:
SELECT t.*
FROM tblSaleAddresses t
INNER JOIN(
SELECT SaleID, MAX(DateEffective) AS MaxDateEffective
FROM tblSaleAddresses
WHERE DateEffective <= GETDATE()
GROUP BY SaleID
) d On t.SaleID = d.SaleID And t.DateEffective = d.MaxDateEffective
Thanks!
I posted few queries that show how to do this for a similar problem. Please see the thread titled "Get latest purchase of each vender". Here are few queries:
-- Works from SQL70 onwards:
select t1.*
from tblSaleAddresses as t1
where t1.AddressId = (select top 1 t2.AddressId
from tblSaleAddresses as t2
where t2.SaleID = t1.SaleID
and t2.DateEffective <= CURRENT_TIMESTAMP
order by t2.DateEffective DESC)
and t1.DateEffective <= CURRENT_TIMESTAMP;
-- SQL2005 specific:
select t2.*
from (
select t1.*
, ROW_NUMBER() OVER(PARTITION BY t1.SaleID ORDER BY t1.DateEffective DESC, t1.AddressID DESC) as AddrSeq
from tblSaleAddresses as t1
where t1.DateEffective <= CURRENT_TIMESTAMP
) as t2
where t2.AddrSeq = 1;
-- ANSI SQL query
select t1.*
from tblSaleAddresses as t1
join (
-- I concatenate date & id as binary data and take MAX on that first
-- then you can parse the address id of the max value
-- this works for positive values of AddressId and Date
-- you can character values that sorts correctly also.
select cast(right(max(cast(convert(char(8), t2.DateEffective, 112) as binary(8))
+ cast(t2.AddressID as binary(4))), 4) as int)
from tblSaleAddresses as t2
where t2.DateEffective <= CURRENT_TIMESTAMP
group by t2.SaleID
) as t3(AddressId)
on t3.AddressId = t1.AddressId;
I was already starting down the path of the new ranking functions including ROW_NUMBER, but I was still trying to figure it out.
Thanks for your help!
Regards,
Doing a partial replace of a field
Andy
You should be able to do this using the REPLACE function.
ie.
SELECT REPLACE('have a nice day', ' ', ' ')
Returns:
have a nice day
- Peter Ward
WARDY IT Solutions
"Andy" wrote:
> How could I do a replace of " " to " " in a string were the string is a
> field and the values are variable. I would like to use Enterprise Manager
or
> a stored proc.
> IE. Node.Nodename is equal to "have a nice day" I want to replace the "
> " with " " after nice.Not just a sentence. How do you do it for a field in a database table. I
would like to do it in Enterprise Manager Query or Query Builder. There are
also allot of sentences with varying text.
"P. Ward" wrote:
> Andy
> You should be able to do this using the REPLACE function.
> ie.
> SELECT REPLACE('have a nice day', ' ', ' ')
> Returns:
> have a nice day
>
> - Peter Ward
> WARDY IT Solutions
>
> "Andy" wrote:
>|||update table
set field = replace( field , 'twospaces' , 'onespace' );
where
field like '%twospaces%'
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:4183FA77-4739-4526-94EB-C2664A4CC2B6@.microsoft.com...
> Not just a sentence. How do you do it for a field in a database table. I
> would like to do it in Enterprise Manager Query or Query Builder. There
are
> also allot of sentences with varying text.
> "P. Ward" wrote:
>
a
Manager or
the "|||Although, saying that LIKE may ignore whitespace.
You can use where (CHARINDEX(field , 'twospaces')>0) - but this will not use
any indexing and will probably be slower.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:434fb7dc$0$136$7b0f0fd3@.mistral.news.newnet.co.uk...
> update table
> set field = replace( field , 'twospaces' , 'onespace' );
> where
> field like '%twospaces%'
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:4183FA77-4739-4526-94EB-C2664A4CC2B6@.microsoft.com...
I
> are
is
> a
> Manager or
> the "
>|||I tried this in query analyzer:
UPDATE dbo.MA_Node
SET NodeName = replace(NodeName, ' ', ' ');
WHERE (CHARINDEX(NodeName, ' ')>0)
and got this:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.
same for
"Rebecca York" wrote:
> update table
> set field = replace( field , 'twospaces' , 'onespace' );
> where
> field like '%twospaces%'
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:4183FA77-4739-4526-94EB-C2664A4CC2B6@.microsoft.com...
> are
> a
> Manager or
> the "
>
>|||Try removing the semi-colon at the end of the second line. That is a typo
(it signifies the end of a statement).
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:857EBD97-E2B3-4195-BEC1-7245F84BD3D2@.microsoft.com...
>I tried this in query analyzer:
> UPDATE dbo.MA_Node
> SET NodeName = replace(NodeName, ' ', ' ');
> WHERE (CHARINDEX(NodeName, ' ')>0)
> and got this:
> Server: Msg 156, Level 15, State 1, Line 3
> Incorrect syntax near the keyword 'WHERE'.
> same for
> "Rebecca York" wrote:
>|||On Fri, 14 Oct 2005 15:26:42 +0100, "Rebecca York" <rebecca.york {at}
2ndbyte.com> wrote:
>Although, saying that LIKE may ignore whitespace.
Hi Rebecca,
I don't know who told you that, but he/she has misinformed you:
CREATE TABLE Test
(a varchar(20) NOT NULL)
INSERT INTO Test (a)
SELECT 'One space'
UNION
SELECT 'Two spaces'
UNION
SELECT 'Three spaces'
go
SELECT a
FROM Test
WHERE a LIKE '% %'
go
DROP TABLE Test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
You should be able to do this using the REPLACE function.
ie.
SELECT REPLACE('have a nice day', ' ', ' ')
Returns:
have a nice day
- Peter Ward
WARDY IT Solutions
"Andy" wrote:
> How could I do a replace of " " to " " in a string were the string is a
> field and the values are variable. I would like to use Enterprise Manager
or
> a stored proc.
> IE. Node.Nodename is equal to "have a nice day" I want to replace the "
> " with " " after nice.Not just a sentence. How do you do it for a field in a database table. I
would like to do it in Enterprise Manager Query or Query Builder. There are
also allot of sentences with varying text.
"P. Ward" wrote:
> Andy
> You should be able to do this using the REPLACE function.
> ie.
> SELECT REPLACE('have a nice day', ' ', ' ')
> Returns:
> have a nice day
>
> - Peter Ward
> WARDY IT Solutions
>
> "Andy" wrote:
>|||update table
set field = replace( field , 'twospaces' , 'onespace' );
where
field like '%twospaces%'
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:4183FA77-4739-4526-94EB-C2664A4CC2B6@.microsoft.com...
> Not just a sentence. How do you do it for a field in a database table. I
> would like to do it in Enterprise Manager Query or Query Builder. There
are
> also allot of sentences with varying text.
> "P. Ward" wrote:
>
a
Manager or
the "|||Although, saying that LIKE may ignore whitespace.
You can use where (CHARINDEX(field , 'twospaces')>0) - but this will not use
any indexing and will probably be slower.
"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:434fb7dc$0$136$7b0f0fd3@.mistral.news.newnet.co.uk...
> update table
> set field = replace( field , 'twospaces' , 'onespace' );
> where
> field like '%twospaces%'
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:4183FA77-4739-4526-94EB-C2664A4CC2B6@.microsoft.com...
I
> are
is
> a
> Manager or
> the "
>|||I tried this in query analyzer:
UPDATE dbo.MA_Node
SET NodeName = replace(NodeName, ' ', ' ');
WHERE (CHARINDEX(NodeName, ' ')>0)
and got this:
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.
same for
"Rebecca York" wrote:
> update table
> set field = replace( field , 'twospaces' , 'onespace' );
> where
> field like '%twospaces%'
> "Andy" <Andy@.discussions.microsoft.com> wrote in message
> news:4183FA77-4739-4526-94EB-C2664A4CC2B6@.microsoft.com...
> are
> a
> Manager or
> the "
>
>|||Try removing the semi-colon at the end of the second line. That is a typo
(it signifies the end of a statement).
"Andy" <Andy@.discussions.microsoft.com> wrote in message
news:857EBD97-E2B3-4195-BEC1-7245F84BD3D2@.microsoft.com...
>I tried this in query analyzer:
> UPDATE dbo.MA_Node
> SET NodeName = replace(NodeName, ' ', ' ');
> WHERE (CHARINDEX(NodeName, ' ')>0)
> and got this:
> Server: Msg 156, Level 15, State 1, Line 3
> Incorrect syntax near the keyword 'WHERE'.
> same for
> "Rebecca York" wrote:
>|||On Fri, 14 Oct 2005 15:26:42 +0100, "Rebecca York" <rebecca.york {at}
2ndbyte.com> wrote:
>Although, saying that LIKE may ignore whitespace.
Hi Rebecca,
I don't know who told you that, but he/she has misinformed you:
CREATE TABLE Test
(a varchar(20) NOT NULL)
INSERT INTO Test (a)
SELECT 'One space'
UNION
SELECT 'Two spaces'
UNION
SELECT 'Three spaces'
go
SELECT a
FROM Test
WHERE a LIKE '% %'
go
DROP TABLE Test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Doing a partial replace of a field
How could I do a replace of " " to " " in a string were the string is a
field and the values are variable. I would like to use Enterprise Manager o
r
a stored proc.
IE. Node.Nodename is equal to "have a nice day" I want to replace the "
" with " " after nice.> How could I do a replace of " " to " " in a string were the string is a
> field and the values are variable. I would like to use Enterprise Manager
> or
> a stored proc.
> IE. Node.Nodename is equal to "have a nice day" I want to replace the "
> " with " " after nice.
CREATE FUNCTION dbo.RegexReplace (
@.value VARCHAR(4000),
@.replacement VARCHAR(4000),
@.pattern VARCHAR(4000),
@.ignoreCase BIT
) RETURNS NVARCHAR(4000) AS
BEGIN
DECLARE @.hResult INT
DECLARE @.pRegex INT
EXECUTE @.hResult = sp_OACreate 'VBScript.RegExp', @.pRegex OUTPUT
EXECUTE @.hResult = sp_OASetProperty @.pRegex, 'Pattern', @.pattern
EXECUTE @.hResult = sp_OASetProperty @.pRegex, 'Global', TRUE
EXECUTE @.hResult = sp_OASetProperty @.pRegex, 'IgnoreCase', @.ignoreCase
EXECUTE @.hResult = sp_OAMethod @.pRegex, 'Replace', @.value OUTPUT, @.value,
@.replacement
EXECUTE @.hResult = sp_OADestroy @.pRegex
RETURN @.value
END
UPDATE YourTable
SET YourColumn = dbo.RegexReplace(YourColumn, ' ', '\s+', 1)
...My two ... But I'm kind of masochistic that way.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane
field and the values are variable. I would like to use Enterprise Manager o
r
a stored proc.
IE. Node.Nodename is equal to "have a nice day" I want to replace the "
" with " " after nice.> How could I do a replace of " " to " " in a string were the string is a
> field and the values are variable. I would like to use Enterprise Manager
> or
> a stored proc.
> IE. Node.Nodename is equal to "have a nice day" I want to replace the "
> " with " " after nice.
CREATE FUNCTION dbo.RegexReplace (
@.value VARCHAR(4000),
@.replacement VARCHAR(4000),
@.pattern VARCHAR(4000),
@.ignoreCase BIT
) RETURNS NVARCHAR(4000) AS
BEGIN
DECLARE @.hResult INT
DECLARE @.pRegex INT
EXECUTE @.hResult = sp_OACreate 'VBScript.RegExp', @.pRegex OUTPUT
EXECUTE @.hResult = sp_OASetProperty @.pRegex, 'Pattern', @.pattern
EXECUTE @.hResult = sp_OASetProperty @.pRegex, 'Global', TRUE
EXECUTE @.hResult = sp_OASetProperty @.pRegex, 'IgnoreCase', @.ignoreCase
EXECUTE @.hResult = sp_OAMethod @.pRegex, 'Replace', @.value OUTPUT, @.value,
@.replacement
EXECUTE @.hResult = sp_OADestroy @.pRegex
RETURN @.value
END
UPDATE YourTable
SET YourColumn = dbo.RegexReplace(YourColumn, ' ', '\s+', 1)
...My two ... But I'm kind of masochistic that way.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane
Doing a join between XML data typed field and a relational table in Yukon (SQL 2005)
Hello,
I have a Yukon XML question and would be very happy if someone can
assist:
Let's assume I have 2 tables:
One will be product table Regular relational table with Product ID
as a primary key and some descriptive fields.
Second is an Order table it has Order ID as a PK and Order details
which is a field of XML data type and holds all lines in the order
including quantity, product id, prices, etc.
I want to make a join that will present the orders with all product
details for each order line.
Do you know how can I do a Join between XML field and relational
table?
Thanks,
Nitsan
"Nitsan" <nitsan.shaked@.intel.com> wrote in message
news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
[snip]
> Do you know how can I do a Join between XML field and relational
> table?
You should be able to do this with the new nesting of FOR XML statements in
Yukon. See:
http://sqljunkies.com/WebLog/mrys/ar...2/19/1208.aspx
Bryant
|||Thanks for your answer,
I looked into it and it looks like it was doing select on relational tables
and just created XML as an answer.
I am looking for the opposite - Query in XML content join relational table
to get data (even not necessarily in XML format).
Thanks,
Nitsan
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:OeRsQuBaEHA.524@.TK2MSFTNGP09.phx.gbl...
> "Nitsan" <nitsan.shaked@.intel.com> wrote in message
> news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
> [snip]
> You should be able to do this with the new nesting of FOR XML statements
in
> Yukon. See:
> http://sqljunkies.com/WebLog/mrys/ar...2/19/1208.aspx
> --
> Bryant
>
|||Even outside of the capabilties of FOR XML, you can do what you want.
You can use a value from the XML data type using the method xmltype.value()
in a SQL predicate. Or you can use the value of a sql:variable or sql:column
to do the join inside an XQuery.
Bob Beauchemin
http://staff.develop.com/bobb
"Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
news:ccuhjj$kr2$1@.news01.intel.com...
> Thanks for your answer,
> I looked into it and it looks like it was doing select on relational
tables
> and just created XML as an answer.
> I am looking for the opposite - Query in XML content join relational table
> to get data (even not necessarily in XML format).
> Thanks,
> Nitsan
> "Bryant Likes" <bryant@.suespammers.org> wrote in message
> news:OeRsQuBaEHA.524@.TK2MSFTNGP09.phx.gbl...
> in
>
|||Nitsan,
in SQL Server 2005 Beta 2 or SQL Server 2005 Express Edition Technical
Preview you can use nodes() method on XML type to get a row set out of an
XML instance and then do the join in T-SQL space:
create table products(ProdID int primary key, ProdDesc nvarchar(max))
go
create table orders(OrderID int primary key, OrdDetails xml)
go
insert products values(1, 'Prod1')
insert products values(2, 'Prod2')
go
insert orders values(1,
'<prod id="1">
<quantity>2</quantity>
<price>12345.67</price>
</prod>
<prod id="2">
<quantity>3</quantity>
<price>9876.54</price>
</prod>')
go
select OrderID, quantity, price, ProdDesc
from
(
select
OrderID,
prod.value('./@.id', 'int') as id,
prod.value('(./quantity)[1]', 'int') as quantity,
prod.value('(./price)[1]', 'money') as price
from orders cross apply orders.OrdDetails.nodes('/prod') as prods(prod)
) as OrdLine
join products on OrdLine.id=products.ProdID
go
You can then apply FOR XML to results if you need results as XML.
Regards,
Eugene Kogan,
Technical Lead,
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Nitsan" <nitsan.shaked@.intel.com> wrote in message
news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
> Hello,
> I have a Yukon XML question and would be very happy if someone can
> assist:
> Let's assume I have 2 tables:
> One will be product table - Regular relational table with Product ID
> as a primary key and some descriptive fields.
> Second is an Order table - it has Order ID as a PK and Order details
> which is a field of XML data type and holds all lines in the order
> including quantity, product id, prices, etc.
> I want to make a join that will present the orders with all product
> details for each order line.
> Do you know how can I do a Join between XML field and relational
> table?
> Thanks,
> Nitsan
|||Thanks,
Do you have a short example if usin git for this purpose?
"Bob Beauchemin" <no_bobb_spam@.develop.com> wrote in message
news:O9D73DEaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Even outside of the capabilties of FOR XML, you can do what you want.
> You can use a value from the XML data type using the method
xmltype.value()
> in a SQL predicate. Or you can use the value of a sql:variable or
sql:column[vbcol=seagreen]
> to do the join inside an XQuery.
> Bob Beauchemin
> http://staff.develop.com/bobb
>
> "Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
> news:ccuhjj$kr2$1@.news01.intel.com...
> tables
table[vbcol=seagreen]
statements
>
|||Thank you very much !!!
"Eugene Kogan" <eugene_kogan@.hotmail.com> wrote in message
news:uUd0kAJaEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Nitsan,
> in SQL Server 2005 Beta 2 or SQL Server 2005 Express Edition Technical
> Preview you can use nodes() method on XML type to get a row set out of an
> XML instance and then do the join in T-SQL space:
> create table products(ProdID int primary key, ProdDesc nvarchar(max))
> go
> create table orders(OrderID int primary key, OrdDetails xml)
> go
> insert products values(1, 'Prod1')
> insert products values(2, 'Prod2')
> go
> insert orders values(1,
> '<prod id="1">
> <quantity>2</quantity>
> <price>12345.67</price>
> </prod>
> <prod id="2">
> <quantity>3</quantity>
> <price>9876.54</price>
> </prod>')
> go
> select OrderID, quantity, price, ProdDesc
> from
> (
> select
> OrderID,
> prod.value('./@.id', 'int') as id,
> prod.value('(./quantity)[1]', 'int') as quantity,
> prod.value('(./price)[1]', 'money') as price
> from orders cross apply orders.OrdDetails.nodes('/prod') as prods(prod)
> ) as OrdLine
> join products on OrdLine.id=products.ProdID
> go
> You can then apply FOR XML to results if you need results as XML.
>
> Regards,
> Eugene Kogan,
> Technical Lead,
> SQL Server Engine
> Microsoft Corp
> [This posting is provided "AS IS" with no warranties, and confers no
> rights.]
> "Nitsan" <nitsan.shaked@.intel.com> wrote in message
> news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
>
|||Hi Nitsan,
Here's a really short example, starting with the authors table in the pubs
database:
create table xmlauth (
authbio xml)
go
insert xmlauth values('<author id="172-32-1176"><hobbies><golf score="78"
/><tennis/></hobbies></author>')
insert xmlauth values('<author id="213-46-8915"><hobbies><golf score="82"
/><bowling/></hobbies></author>')
If you know each document contains one author, you can use xml.value:
select authbio.query('/author/hobbies/*') as hobbies, au_lname
from authors, xmlauth
where authors.au_id =
xmlauth.authbio.value('author[1]/@.id', 'varchar(11)')
(if you have a schema collection which specifies a single author occurance,
you can leave out the subscript [1] in the value subquery)
If documents may contain more than one author, (e.g)
insert xmlauth values('
<authors>
<author id="274-80-9391"><hobbies><swimming
/><bowling/></hobbies></author>
<author id="274-80-9391"><hobbies><baseball
/><bowling/></hobbies></author>
</authors>')
you would need xml.nodes. Eugene's excellent example illustrates that.
You can even do this (caution: NOT recommended, cartesian product) using the
SQL value in the XQuery:
select * from
(
select authbio.query('
for $a in /author
where $a/@.id = sql:column("authors.au_id")
return $a/hobbies/*') as hobbies, au_lname
from xmlauth, authors
) as a
where hobbies.exist('/*')=1
or
select * from
(
select authbio.query('
for $a in /author
where $a/@.id = sql:column("authors.au_id")
return $a/hobbies/*') as hobbies, au_lname
from xmlauth, authors
) as a
where convert(nvarchar(100), hobbies) <> ''
Hope this helps,
Bob Beauchemin
http://staff.develop.com/bobb
"Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
news:cd034u$fo8$1@.news01.intel.com...
> Thanks,
> Do you have a short example if usin git for this purpose?
> "Bob Beauchemin" <no_bobb_spam@.develop.com> wrote in message
> news:O9D73DEaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> xmltype.value()
> sql:column
> table
> statements
>
I have a Yukon XML question and would be very happy if someone can
assist:
Let's assume I have 2 tables:
One will be product table Regular relational table with Product ID
as a primary key and some descriptive fields.
Second is an Order table it has Order ID as a PK and Order details
which is a field of XML data type and holds all lines in the order
including quantity, product id, prices, etc.
I want to make a join that will present the orders with all product
details for each order line.
Do you know how can I do a Join between XML field and relational
table?
Thanks,
Nitsan
"Nitsan" <nitsan.shaked@.intel.com> wrote in message
news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
[snip]
> Do you know how can I do a Join between XML field and relational
> table?
You should be able to do this with the new nesting of FOR XML statements in
Yukon. See:
http://sqljunkies.com/WebLog/mrys/ar...2/19/1208.aspx
Bryant
|||Thanks for your answer,
I looked into it and it looks like it was doing select on relational tables
and just created XML as an answer.
I am looking for the opposite - Query in XML content join relational table
to get data (even not necessarily in XML format).
Thanks,
Nitsan
"Bryant Likes" <bryant@.suespammers.org> wrote in message
news:OeRsQuBaEHA.524@.TK2MSFTNGP09.phx.gbl...
> "Nitsan" <nitsan.shaked@.intel.com> wrote in message
> news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
> [snip]
> You should be able to do this with the new nesting of FOR XML statements
in
> Yukon. See:
> http://sqljunkies.com/WebLog/mrys/ar...2/19/1208.aspx
> --
> Bryant
>
|||Even outside of the capabilties of FOR XML, you can do what you want.
You can use a value from the XML data type using the method xmltype.value()
in a SQL predicate. Or you can use the value of a sql:variable or sql:column
to do the join inside an XQuery.
Bob Beauchemin
http://staff.develop.com/bobb
"Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
news:ccuhjj$kr2$1@.news01.intel.com...
> Thanks for your answer,
> I looked into it and it looks like it was doing select on relational
tables
> and just created XML as an answer.
> I am looking for the opposite - Query in XML content join relational table
> to get data (even not necessarily in XML format).
> Thanks,
> Nitsan
> "Bryant Likes" <bryant@.suespammers.org> wrote in message
> news:OeRsQuBaEHA.524@.TK2MSFTNGP09.phx.gbl...
> in
>
|||Nitsan,
in SQL Server 2005 Beta 2 or SQL Server 2005 Express Edition Technical
Preview you can use nodes() method on XML type to get a row set out of an
XML instance and then do the join in T-SQL space:
create table products(ProdID int primary key, ProdDesc nvarchar(max))
go
create table orders(OrderID int primary key, OrdDetails xml)
go
insert products values(1, 'Prod1')
insert products values(2, 'Prod2')
go
insert orders values(1,
'<prod id="1">
<quantity>2</quantity>
<price>12345.67</price>
</prod>
<prod id="2">
<quantity>3</quantity>
<price>9876.54</price>
</prod>')
go
select OrderID, quantity, price, ProdDesc
from
(
select
OrderID,
prod.value('./@.id', 'int') as id,
prod.value('(./quantity)[1]', 'int') as quantity,
prod.value('(./price)[1]', 'money') as price
from orders cross apply orders.OrdDetails.nodes('/prod') as prods(prod)
) as OrdLine
join products on OrdLine.id=products.ProdID
go
You can then apply FOR XML to results if you need results as XML.
Regards,
Eugene Kogan,
Technical Lead,
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Nitsan" <nitsan.shaked@.intel.com> wrote in message
news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
> Hello,
> I have a Yukon XML question and would be very happy if someone can
> assist:
> Let's assume I have 2 tables:
> One will be product table - Regular relational table with Product ID
> as a primary key and some descriptive fields.
> Second is an Order table - it has Order ID as a PK and Order details
> which is a field of XML data type and holds all lines in the order
> including quantity, product id, prices, etc.
> I want to make a join that will present the orders with all product
> details for each order line.
> Do you know how can I do a Join between XML field and relational
> table?
> Thanks,
> Nitsan
|||Thanks,
Do you have a short example if usin git for this purpose?
"Bob Beauchemin" <no_bobb_spam@.develop.com> wrote in message
news:O9D73DEaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> Even outside of the capabilties of FOR XML, you can do what you want.
> You can use a value from the XML data type using the method
xmltype.value()
> in a SQL predicate. Or you can use the value of a sql:variable or
sql:column[vbcol=seagreen]
> to do the join inside an XQuery.
> Bob Beauchemin
> http://staff.develop.com/bobb
>
> "Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
> news:ccuhjj$kr2$1@.news01.intel.com...
> tables
table[vbcol=seagreen]
statements
>
|||Thank you very much !!!
"Eugene Kogan" <eugene_kogan@.hotmail.com> wrote in message
news:uUd0kAJaEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Nitsan,
> in SQL Server 2005 Beta 2 or SQL Server 2005 Express Edition Technical
> Preview you can use nodes() method on XML type to get a row set out of an
> XML instance and then do the join in T-SQL space:
> create table products(ProdID int primary key, ProdDesc nvarchar(max))
> go
> create table orders(OrderID int primary key, OrdDetails xml)
> go
> insert products values(1, 'Prod1')
> insert products values(2, 'Prod2')
> go
> insert orders values(1,
> '<prod id="1">
> <quantity>2</quantity>
> <price>12345.67</price>
> </prod>
> <prod id="2">
> <quantity>3</quantity>
> <price>9876.54</price>
> </prod>')
> go
> select OrderID, quantity, price, ProdDesc
> from
> (
> select
> OrderID,
> prod.value('./@.id', 'int') as id,
> prod.value('(./quantity)[1]', 'int') as quantity,
> prod.value('(./price)[1]', 'money') as price
> from orders cross apply orders.OrdDetails.nodes('/prod') as prods(prod)
> ) as OrdLine
> join products on OrdLine.id=products.ProdID
> go
> You can then apply FOR XML to results if you need results as XML.
>
> Regards,
> Eugene Kogan,
> Technical Lead,
> SQL Server Engine
> Microsoft Corp
> [This posting is provided "AS IS" with no warranties, and confers no
> rights.]
> "Nitsan" <nitsan.shaked@.intel.com> wrote in message
> news:31b6c8ae.0407120300.6902d2e1@.posting.google.c om...
>
|||Hi Nitsan,
Here's a really short example, starting with the authors table in the pubs
database:
create table xmlauth (
authbio xml)
go
insert xmlauth values('<author id="172-32-1176"><hobbies><golf score="78"
/><tennis/></hobbies></author>')
insert xmlauth values('<author id="213-46-8915"><hobbies><golf score="82"
/><bowling/></hobbies></author>')
If you know each document contains one author, you can use xml.value:
select authbio.query('/author/hobbies/*') as hobbies, au_lname
from authors, xmlauth
where authors.au_id =
xmlauth.authbio.value('author[1]/@.id', 'varchar(11)')
(if you have a schema collection which specifies a single author occurance,
you can leave out the subscript [1] in the value subquery)
If documents may contain more than one author, (e.g)
insert xmlauth values('
<authors>
<author id="274-80-9391"><hobbies><swimming
/><bowling/></hobbies></author>
<author id="274-80-9391"><hobbies><baseball
/><bowling/></hobbies></author>
</authors>')
you would need xml.nodes. Eugene's excellent example illustrates that.
You can even do this (caution: NOT recommended, cartesian product) using the
SQL value in the XQuery:
select * from
(
select authbio.query('
for $a in /author
where $a/@.id = sql:column("authors.au_id")
return $a/hobbies/*') as hobbies, au_lname
from xmlauth, authors
) as a
where hobbies.exist('/*')=1
or
select * from
(
select authbio.query('
for $a in /author
where $a/@.id = sql:column("authors.au_id")
return $a/hobbies/*') as hobbies, au_lname
from xmlauth, authors
) as a
where convert(nvarchar(100), hobbies) <> ''
Hope this helps,
Bob Beauchemin
http://staff.develop.com/bobb
"Nitsan Shaked" <nitsan.shaked@.intel.com> wrote in message
news:cd034u$fo8$1@.news01.intel.com...
> Thanks,
> Do you have a short example if usin git for this purpose?
> "Bob Beauchemin" <no_bobb_spam@.develop.com> wrote in message
> news:O9D73DEaEHA.3508@.TK2MSFTNGP09.phx.gbl...
> xmltype.value()
> sql:column
> table
> statements
>
Subscribe to:
Posts (Atom)