sql database integrity: column

Showing posts with label column. Show all posts

Tuesday, March 27, 2012

Creating xsd schema file for a xml file ...

Hi ...
I need to bulk insert the xml data below. I am working on some issues related to the Itentity column (you may have read the other question).
The xml below uses elements with attributes. Is the schema file formated correctly to extract the attributes from the SITE element and then also extract the attributes from the ATMOSPHERIC element ... ? Not sure about the schema file ?
Thanks,
Chris

////////////////////////////////////////////////////
// schema file
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="site" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="sysid" type="xsd:string" sql:field="SystemId"/>
<xsd:attribute name="rpuid" type="xsd:string" sql:field="RpuId"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="atmospheric" sql:relation="MacgowanTestRWISRawAtmospheric" >
<xsd:complexType>
<xsd:attribute name="datetime" type="xsd:date" sql:field="ObsDateTime"/>
<xsd:attribute name="airtemp" type="xsd:string" sql:field="Temperature"/>
<xsd:attribute name="dewpoint" type="xsd:string" sql:field="DewPoint"/>
</xsd:complexType>
</xsd:element>
</xsd:schema>

////////////////////////////////////////////////////
// xml data
<?xml version="1.0"?>
<odot_rwis_site_info>
<site id="200000" number="1" sysid="200" rpuid="0" name="1-SR127 @. SR249" longitude="-84.554946" latitude="41.383527">
<atmospheric datetime="12/05/2005 03:48:00 PM" airtemp="-490" dewpoint="-800" relativehumidity="73" windspeedavg="11" windspeedgust="19" winddirectionavg="265" winddirectiongust="295" pressure="65535" precipitationintensity="None" precipitationtype="None" precipitationrate="0" precipitationaccumulation="-1" visibility="2000" />
<sensors>
<surface id="0" datetime="12/05/2005 03:48:00 PM" name="North Bound Driving Lane" surfacecondition="Dry" surfacetemp="1900" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="450" waterlevel="0">
<traffic datetime="12/05/2005 03:48:00 PM" occupancy="0" avgspeed="82" volume="21" sftemp="1900" sfstate="255">
<normalbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="0" bincount="7" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="1" bincount="0" />
</normalbins>
<longbins>
<bin datetime="12/05/2005 03:48:00 PM" binnumber="2" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="3" bincount="0" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="4" bincount="1" />
<bin datetime="12/05/2005 03:48:00 PM" binnumber="5" bincount="0" />
</longbins>
</traffic>
</surface>
<surface id="1" datetime="12/05/2005 03:48:00 PM" name="Bridge Deck Simulator" surfacecondition="Other" surfacetemp="-60" freezingtemp="32767" chemicalfactor="255" chemicalpercent="255" depth="32767" icepercent="255" subsurfacetemp="-999999" waterlevel="0" />
</sensors>
</site>
</odot_rwis_site_info>

////////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestRWISRawAtmospheric] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_DataSourceId] DEFAULT ('OH'),
[ProductInstanceId] [char] (38) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_ProductInstanceId] DEFAULT ('5abbbc86-fb2c-4703-9589-b55f763ee150'),
[SystemId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[RpuId] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SensorId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObsDateTime] [datetime] NULL ,
[InsertDateTime] [datetime] NOT NULL CONSTRAINT [DF_MacgowanTestRWISRawAtmospheric_InsertDateTime] DEFAULT (getdate()),
[Temperature] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DewPoint] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RelativeHumidity] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSPD] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WSPDGust] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRMin] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRAvg] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WDIRMax] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipIntensity] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipType] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipRate] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Pressure] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Visability] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AirTempMax] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AirTempMin] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WetBulbTemp] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastPrecipStart] [datetime] NULL ,
[LastPrecipEnd] [datetime] NULL ,
[PrecipAccum1Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum3Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum6Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum12Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PrecipAccum24Hour] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_MacgowanTestRWISRawAtmospheric] PRIMARY KEY NONCLUSTERED
(
[RecordId]
) WITH FILLFACTOR = 70 ON [PRIMARY]
) ON [PRIMARY]
GO

Not sure if I understand the question fully. Are you having any specific issues with bulk loading this data? or the issue is validating your XML data against XSD?

thanks

|||Hi ...

Sorry about the confusion. I was having some issues with bulk

loading using the IDENTITY Column in the target table. I was

setting the COM attribute KeepIdentity to TRUE rather than FALSE.

The schema and xml are correct.

Thanks,

Chris

Creating xsd schema file for a xml file ...

Not sure if I understand the question fully. Are you having any specific issues with bulk loading this data? or the issue is validating your XML data against XSD?

thanks

|||Hi ...
Sorry about the confusion. I was having some issues with bulk loading using the IDENTITY Column in the target table. I was setting the COM attribute KeepIdentity to TRUE rather than FALSE.
The schema and xml are correct.
Thanks,
Chris

Sunday, March 25, 2012

Creating Unique Indexes on Partition table..

In partition tables, SQL Server forces to include the Partition column as part of unique index/unique constraint or primary key. Is there a way to over-ride this option.

We have a table, which is partitioned on a Calculated Column (generated using custom logic). Also we need to enforce uniqueness based on few other columns. But SQL Server does not allow indexes/contraints on these columns without including the partition column.

Thanks for your help.

Well, you can create a non-aligned unique index on a partitioned table, but doing that will in essence not allow meta-data-only partition switching. Depending on your needs, that may be ok or not. You could also use a DML trigger to enforce uniqueness instead of a unique index. Or, if your environment allows for the use of them, you could always create an indexed view on the table and create a unique index on that view that will in essence enforce the uniqueness on the corresponding table as well.

For more information, see the following topic in SQL 2005 BOL:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/5af648b1-8454-4c17-a47e-f9656572440b.htm

HTH,

Creating Total to-date query...

I am trying to create a query where
the data is for a time period but there is
one column that would show a Total-to-date.

I've tried google and searching this forum
but everyone talks about year-to-date or
period-to-date but I don't want that. I would
like to do :
[from whenever data started being entered]-to-date

I know I can hack this by doing an aggregate like
so:

WITH MEMBER [Timesheet].[Date].[TimeChargedTotal] AS
'Aggregate ( { [Timesheet].[Date].[Year].&[1900].&[1].&[1]:[Timesheet].[Date].[Year].&[2006].&[9].&[15] } )'

Here I am betting on the fact that there is no data before 1-1-1900
which is a safe bet, but I'd prefer to do it the 'right' way if there is one.

Thanks.

If you're using AS 2005, then you can try the shorthand:

{ NULL : [Timesheet].[Date].[Year].&[2006].&[9].&[15] }

|||

Something like [Time].[Date].FirstChild:[Time].[Date].Lastchild might help. Be aware of performance issues with a large dimension.

Regards

Thomas Ivarsson

|||

Thanks guys.

Both answers work.
Is there any performance difference between
using NULL or [Time].[Date].FirstChild as the
beginning of the range?

|||Well, I'm still unclear about the usage scenario - if the calculated member is defined on the [Time].[Date] hierarchy, then on which hierarchy will the user select a time period? If you can translate this to the Adventure Works cube, that would help clarify.|||

I am not familiar enough with adventureworks
to give a proper example.

However I will attempt to explain my scenario.
We have a timesheet system where time spent
on various projects/tasks is collected and then
billed to a client.

A report needs to be created that shows the
previous weeks time (a particualr 1 week period with
a start and end date) spent on a particular project/task
by employee X and the total time spent so far on that
project/task by emplyee X up until the end of that period.
This means that year-to-date (or period-to-date) scenario
does not work because we want ALL time up until
the end of the selected period and there is no guarantee
that a project will last a week, month, year, etc...

Hope that somewhat clarifies the usage scenario.

|||

Okay this is not working the way I anticipated.
I am clearly not understanding MDX and the book
I am refering to:
'SQL Server 2005 Analysis Services 2005 with MDX'
is of no help.

Here is what I am trying to accomplish conceptually:

WITH MEMBER [Measures].[TimeChargedToDate] AS
'AGGREGATE ( {NULL:@.ToTimesheetDate} )'

SELECT NON EMPTY
{
[Measures].[Time Charged],
[Measures].[TimeChargedToDate]
} ON COLUMNS,
NON EMPTY
{
([Project].[Project Number].[Project Number].ALLMEMBERS
* [Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS )
}
ON ROWS
FROM
(
SELECT ( STRTOSET(@.ProjectProjectNumber, CONSTRAINED) ) ON COLUMNS
FROM
(
SELECT ( STRTOMEMBER(@.FromTimesheetDate, CONSTRAINED)
: STRTOMEMBER(@.ToTimesheetDate, CONSTRAINED) ) ON COLUMNS
FROM [Timesheet_Cube]
)
)

So the moral of the story here is that I want to get
a list of all the projects, then all the users that have
charged time to each project. Then I want the total
hours for the time range specified AS WELL AS the
total time charge up until the cutoff date (@.ToTimesheetDate).

Now as I have just learned you cannot have aggregates
in your Measures, so can some kind soul please
point me to a source where I can ejumicate myself on
this MDX magic? It seems that its much more difficult
to operate with ranges of time in MDX than I though it should be.

|||If you are trying to do something like I
described above, here is how to do it:

WITH MEMBER [Measures].[TimeChargedToDate] AS
'SUM({NULL:[Timesheet].[Date].&[2006].&[9].&[15]},[Time Charged])'

SELECT NON EMPTY
{
[Measures].[Time Charged],
[Measures].[TimeChargedToDate]
} ON COLUMNS,
NON EMPTY
{
([Project].[Project Number].&[PA01203] * [Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS )
} ON ROWS
FROM
(
SELECT
{[Timesheet].[Date].[Year].&[2006].&[9].&[2] : [Timesheet].[Date].[Year].&[2006].&[9].&[15]} ON COLUMNS
FROM
(
SELECT ( [Project].[Project Number].&[PA01203] ) ON COLUMNS
FROM [Timesheet_Cube]
)
)

|||If you are trying to do something like I
described above, here is how to do it:

WITH MEMBER [Measures].[TimeChargedToDate] AS
'SUM({NULL:[Timesheet].[Date].&[2006].&[9].&[15]},[Time Charged])'

SELECT NON EMPTY
{
[Measures].[Time Charged],
[Measures].[TimeChargedToDate]
} ON COLUMNS,
NON EMPTY
{
(
[Project].[Project Number].&[PA01203] *
[Staff Member].[Staff Member Name].[Staff Member Name].ALLMEMBERS
)
} ON ROWS
FROM
(
SELECT
{[Timesheet].[Date].[Year].&[2006].&[9].&[2] : [Timesheet].[Date].[Year].&[2006].&[9].&[15]}
ON COLUMNS
FROM
(
SELECT ( [Project].[Project Number].&[PA01203] ) ON COLUMNS
FROM [Timesheet_Cube]
)
)

Thursday, March 22, 2012

Creating temporary table

Hi,

How can I create a temporary table say "Tblabc" with column fields

ShmCoy char(2)
ShmAcno char(10)
ShmName1 varchar(60)
ShmName2 varchar(60)

and fill the table from the data extracted from the statement...

"select logdetail from shractivitylog"

The above query returns single value field the data seperated with a ''

Ex:
BRLight Blue Duck

in this case I should get
ShmCoy = 'BR'
ShmAcno = ''
ShmName1 = 'Light Blue Duck'
ShmName2 = ''

I want to do this job with single SQL query. Is it possible. Pls help.

Herewith I am providing the sample data

BRLight Blue Duck
0234578
BRAqua Duck
0234586
UBAqua Duck

Regards,
OmavHi.

I think that is better using a stored procedure, but you can try with
this:

create table shractivitylog (logdetail varchar(50))
go

insert into shractivitylog values ('BRLight Blue Duck');
insert into shractivitylog values ('0234578');
insert into shractivitylog values ('BRAqua Duck');
insert into shractivitylog values ('0234586');
insert into shractivitylog values ('UBAqua Duck');

select cast(substring(logdetail,
1,
charindex('',logdetail)-1
) as char(2)) as ShmCoy,
cast(substring(logdetail,
charindex('',logdetail)+1,
charindex('',logdetail,charindex('',logdetail)+1 )-(charindex('',logdetail)+1)
) as char(10)) as ShmAcno,
cast(substring(logdetail,
charindex('',logdetail,charindex('',logdetail)+1 )+1,
charindex('',logdetail,charindex('',logdetail,ch arindex('',logdetail)+1)+1)-(charindex('',logdetail,charindex('',logdetail)+ 1)+1)
) as varchar(60)) as ShmName1,
cast(substring(logdetail,
charindex('',logdetail,charindex('',logdetail,ch arindex('',logdetail)+1)+1)+1,
charindex('',logdetail,charindex('',logdetail,ch arindex('',logdetail,charindex('',logdetail)+1)+ 1)+1)-(charindex('',logdetail,charindex('',logdetail,c harindex('',logdetail)+1)+1)+1)
) as varchar(60)) as ShmName2
into ##tblabc
from shractivitylog

select * from ##tblabc

Bye!

kiran@.boardroomlimited.com (Omavlana) wrote in message news:<b14098ab.0310080226.64bf03c6@.posting.google.com>...
> Hi,
> How can I create a temporary table say "Tblabc" with column fields
> ShmCoy char(2)
> ShmAcno char(10)
> ShmName1 varchar(60)
> ShmName2 varchar(60)
> and fill the table from the data extracted from the statement...
> "select logdetail from shractivitylog"
>
> The above query returns single value field the data seperated with a ''
> Ex:
> BRLight Blue Duck
> in this case I should get
> ShmCoy = 'BR'
> ShmAcno = ''
> ShmName1 = 'Light Blue Duck'
> ShmName2 = ''
> I want to do this job with single SQL query. Is it possible. Pls help.
>
> Herewith I am providing the sample data
> BRLight Blue Duck
> 0234578
> BRAqua Duck
> 0234586
> UBAqua Duck
>
> Regards,
> Omav

Creating temporary table

Hi,
How can I create a temporary table say "Tblabc" with column fields
ShmCoy char(2)
ShmAcno char(10)
ShmName1 varchar(60)
ShmName2 varchar(60)
and fill the table from the data extracted from the statement...
"select logdetail from shractivitylog"
The above query returns single value field the data seperated with a '·'
Ex:
BR··Light Blue Duck··
in this case I should get
ShmCoy = 'BR'
ShmAcno = ''
ShmName1 = 'Light Blue Duck'
ShmName2 = ''
I want to do this job with single SQL query. Is it possible. Pls help.
Herewith I am providing the sample data
BR··Light Blue Duck··
·0234578···
BR··Aqua Duck··
·0234586···
UB··Aqua Duck··
Regards,
OmavYou might consider copying the data out to a .Txt file with BCP. You could
then insert the data using BULK INSERT
--
HTH
Ryan Waight, MCDBA, MCSE
"Omavlana" <kiran@.boardroomlimited.com> wrote in message
news:b14098ab.0310080228.30a01631@.posting.google.com...
> Hi,
> How can I create a temporary table say "Tblabc" with column fields
> ShmCoy char(2)
> ShmAcno char(10)
> ShmName1 varchar(60)
> ShmName2 varchar(60)
> and fill the table from the data extracted from the statement...
> "select logdetail from shractivitylog"
>
> The above query returns single value field the data seperated with a '·'
> Ex:
> BR··Light Blue Duck··
> in this case I should get
> ShmCoy = 'BR'
> ShmAcno = ''
> ShmName1 = 'Light Blue Duck'
> ShmName2 = ''
> I want to do this job with single SQL query. Is it possible. Pls help.
>
> Herewith I am providing the sample data
> BR··Light Blue Duck··
> ·0234578···
> BR··Aqua Duck··
> ·0234586···
> UB··Aqua Duck··
>
> Regards,
> Omavsql

Tuesday, March 20, 2012

Creating table where column_names are results from query from other table

How I can create table where column name is result of query of second table?

Next cod doesnt work:

Use Pubs
declare @.naz varchar(40)
declare naziv cursor
for
select top 1 au_lname from authors

open naziv
FETCH NEXT FROM naziv INTO @.naz
close naziv
deallocate naziv
GO

CREATE TABLE #t1
(@.naz varchar(20),
quote int
)
GO

DROP TABLE #t1

use dynamic sql, although it won't work for temp tables.

EXECUTE('create table t1 (' + @.naz + ' varchar(20), quote int)'

Also, you don't need a cursor to do what you're doing above, it's overkill.

|||You don't need a cursor if you are only returning one row. Otherwise, you will need a cursor to exec the dynamic sql. If you just want the code to execute to create a number of tables, then you can do all the above in a stored procedure and have it output the code, without resorting to dynamic SQL (as you will be doing the executing). Thus:

declare @.dynsql varchar(100)
set @.dynsql = 'CREATE TABLE #t1 ( ' + @.naz + ' varchar(20), quote int ) GO'

sp_executesql @.dynsql

If you want to be really clever, and want to be able to dynamically set the type, size and precision of the column(s) to create, query INFORMATION_SCHEMA.COLUMNS for this information

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = @.naz

You will probably want to specify the tablename of the originating column that you are copying in the new table in the above query aswell.

HTH

For more SQL tips, check out my blog:
|||Use ALTER TABLE with dynamic SQL to add the columns. The base definition will be used in the CREATE TABLE statement.

Sunday, March 11, 2012

creating sp to export column to comma delimited list

Hello everyone, I am interested in creating a stored procedure that can
extract a column from a table and put it into a comma delimited list. Can
someone give me some examples of how this can be done. Thanks in advance for
any help.
Message posted via http://www.webservertalk.comLook at this example:
http://milambda.blogspot.com/2005/0...s-as-array.html
And mind the warnings, please.
Maybe you could also tell us what it is that you're actually trying to
achieve?
ML
http://milambda.blogspot.com/|||In general, a good approach is to bring the return the resultset to the
client and massage the data to appropriate display format. As far as
workarounds for doing it at the server, you can check out the following
links:
( For 2005 )
http://groups.google.com/group/micr...br />
9b9b968a
( For 2000 only )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||----
---
CREATE PROCEDURE sp_exportcolumn(
@.p_servername VARCHAR(500) = NULL ,
@.p_databasename VARCHAR(500)= NULL,
@.p_tablename VARCHAR(500)= NULL,
@.p_columnname VARCHAR(500)= NULL,
@.p_outputfile VARCHAR(500)= NULL
)
AS
BEGIN
DECLARE @.v_sql AS VARCHAR(4000)
DECLARE @.v_cmd AS VARCHAR(8000)
/*
-- use these to test the script prior to passing parameters in.
SET @.p_columnname = 'mycolumname'
SET @.p_databasename = 'mydbname'
SET @.p_tablename = 'mytable'
SET @.p_outputfile = 'c:\folderpath\testdynamicbcp.txt'
SET @.p_servername = 'server.domain'
*/
SET @.v_sql = 'SELECT TOP 100 ' + @.p_columnname+' FROM
['+@.p_databasename+'].[dbo].['+@.p_tablename+'] '
SET @.v_cmd = 'bcp "'+@.v_sql+'" queryout "'+ @.p_outputfile+'" -S "'+
@.p_servername +'" -c -b 100000 -m 20000 -T '
PRINT @.v_cmd
EXEC master..xp_cmdshell @.v_cmd
END
----
---
Below you can test without passing parameters if you uncomment the
default values...
otherwise pass in the parameters...
----
---
EXEC sp_exportcolumn
OR
EXEC sp_exportcolumn @.p_servername ,@.p_databasename , @.p_tablename ,
@.p_columnname , @.p_outputfile|||I apologize for the broadness of my request. What I am trying to do is
extract email addresses from a column and then paste them into a web page
that will send out the emails with an attachment. The webpage calls for the
email addresses to be separated by a comma.
ML wrote:
>Look at this example:
>http://milambda.blogspot.com/2005/0...s-as-array.html
>And mind the warnings, please.
>Maybe you could also tell us what it is that you're actually trying to
>achieve?
>ML
>--
>http://milambda.blogspot.com/
Message posted via http://www.webservertalk.com|||The previous code outputted the list with CRLF as separator...For your
purpose you may want
To change the @.v_cmd to give "," as the row terminator, since that is
the format you want in at end...
Also, consider limiting the data by adding a where clause and data
validation if data is not already clean.
SET @.v_cmd = 'bcp "'+@.v_sql+'" queryout "'+ @.p_outputfile+'" -S "'+
@.p_servername +'" -c -b 100000 -m 20000 -T -r ","'|||Thanks for the advice. I give this a try.
dizzler wrote:
>The previous code outputted the list with CRLF as separator...For your
>purpose you may want
>To change the @.v_cmd to give "," as the row terminator, since that is
>the format you want in at end...
>Also, consider limiting the data by adding a where clause and data
>validation if data is not already clean.
>SET @.v_cmd = 'bcp "'+@.v_sql+'" queryout "'+ @.p_outputfile+'" -S "'+
>@.p_servername +'" -c -b 100000 -m 20000 -T -r ","'
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200604/1

Creating reports in MS SQL Server Report Server

Hi...
Need some help with creating reports in MS SQL Server Report Server and column name "Profit / Loss (%)".
Got an error when I try to run the code below.
The purpose is to avoid a division by zero... or do anyone know a better way solve this?

=IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)=0, 0, IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)<>0, -1, SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)))
/
IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)=0, 1, IIF(SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)=0 AND SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value)<>0, 1, SUM(Fields!RECOGNITION_REVENUE_RECOGNIZED.Value)))
Error message:
The value expression for the textbox ‘textbox86’ refers to the field ‘PROFIT_AND_LOSS_AMOUNT’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

//chwa

This error message is referring to the scope of the field ‘PROFIT_AND_LOSS_AMOUNT’. It cannot find it in the dataset. Try doing

SUM(Fields!PROFIT_AND_LOSS_AMOUNT.Value, "dataset name here")

for all those things above.

Thursday, March 8, 2012

creating replication with extra column

Hi ,
I have a few tables that i wanted to replicate from ServerA to ServerB ,
however, these tables in ServerB needs a country code column as it's sort of
a Data Center.
How i can add the column during replication setup ?
kindly advise
tks & rdgs
Use the ALTER TABLE statement to add the column to the subscription table.
Note the column must allow NULLs or have a DEFAULT constraint.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:3329F606-26D6-4D88-8E45-D623C8787E49@.microsoft.com...
> Hi ,
> I have a few tables that i wanted to replicate from ServerA to ServerB ,
> however, these tables in ServerB needs a country code column as it's sort
> of
> a Data Center.
> How i can add the column during replication setup ?
> kindly advise
> tks & rdgs

creating replication with extra column

Hi ,
I have a few tables that i wanted to replicate from ServerA to ServerB ,
however, these tables in ServerB needs a country code column as it's sort of
a Data Center.
How i can add the column during replication setup ?
kindly advise
tks & rdgsUse the ALTER TABLE statement to add the column to the subscription table.
Note the column must allow NULLs or have a DEFAULT constraint.
HTH
Jerry
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:3329F606-26D6-4D88-8E45-D623C8787E49@.microsoft.com...
> Hi ,
> I have a few tables that i wanted to replicate from ServerA to ServerB ,
> however, these tables in ServerB needs a country code column as it's sort
> of
> a Data Center.
> How i can add the column during replication setup ?
> kindly advise
> tks & rdgs

Creating relationship between column and replacement column

Hello,
I have a table (tblManchineParts) that is the list of all machine parts.
The second table (tblReplacementParts) is a list of all the machine parts
and the machine part that replaced it. I tried to create two foreign key
relationships to enforce Referential Integrity:
tblMachineParts.MachinePart_ID and tblReplacementParts.MachinePart_ID
and
tblMachineParts.MachinePart_ID and tblReplacementParts.ReplacementPart_ID
When I did this, I got the error: Introducing second foreign key may cause
cycles or multiple cascade paths. My question is, how do I enforce
referential integrity on both the fields in the table tblReplacementParts
since their values have to be in the table tblMachineParts?
--
Thanks in advance,
Steven
tblMachineParts
--
MachinePart_ID (varchar 255)
tblReplacementParts
--
MachinePart_ID (varchar 255)
ReplacementPart_ID (varchar 255)
examples
(tblMachineParts)
MachinePart_ID
--
Part01
part02
Part03
(tblReplacementParts)
MachinePart_ID ReplacementPart_ID
-- --
Part02 Part03Hi, Steven
You can enforce referential integrity, you just can't use cascade
updates. For more informations, see this thread:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/a802401b5d653f00
Razvan

Creating relationship between column and replacement column

Hello,
I have a table (tblManchineParts) that is the list of all machine parts.
The second table (tblReplacementParts) is a list of all the machine parts
and the machine part that replaced it. I tried to create two foreign key
relationships to enforce Referential Integrity:
tblMachineParts.MachinePart_ID and tblReplacementParts.MachinePart_ID
and
tblMachineParts.MachinePart_ID and tblReplacementParts.ReplacementPart_ID
When I did this, I got the error: Introducing second foreign key may cause
cycles or multiple cascade paths. My question is, how do I enforce
referential integrity on both the fields in the table tblReplacementParts
since their values have to be in the table tblMachineParts?
Thanks in advance,
Steven
tblMachineParts
--
MachinePart_ID (varchar 255)
tblReplacementParts
--
MachinePart_ID (varchar 255)
ReplacementPart_ID (varchar 255)
examples
(tblMachineParts)
MachinePart_ID
--
Part01
part02
Part03
(tblReplacementParts)
MachinePart_ID ReplacementPart_ID
-- --
Part02 Part03Hi, Steven
You can enforce referential integrity, you just can't use cascade
updates. For more informations, see this thread:
http://groups.google.com/group/micr...802401b5d653f00
Razvan

Creating relationship between column and replacement column

Hello,
I have a table (tblManchineParts) that is the list of all machine parts.
The second table (tblReplacementParts) is a list of all the machine parts
and the machine part that replaced it. I tried to create two foreign key
relationships to enforce Referential Integrity:
tblMachineParts.MachinePart_ID and tblReplacementParts.MachinePart_ID
and
tblMachineParts.MachinePart_ID and tblReplacementParts.ReplacementPart_ID
When I did this, I got the error: Introducing second foreign key may cause
cycles or multiple cascade paths. My question is, how do I enforce
referential integrity on both the fields in the table tblReplacementParts
since their values have to be in the table tblMachineParts?
Thanks in advance,
Steven
tblMachineParts
MachinePart_ID (varchar 255)
tblReplacementParts
MachinePart_ID (varchar 255)
ReplacementPart_ID (varchar 255)
examples
(tblMachineParts)
MachinePart_ID
Part01
part02
Part03
(tblReplacementParts)
MachinePart_ID ReplacementPart_ID
-- --
Part02 Part03
Hi, Steven
You can enforce referential integrity, you just can't use cascade
updates. For more informations, see this thread:
http://groups.google.com/group/micro...02401b5d653f00
Razvan

Wednesday, March 7, 2012

Creating PK/FK Relationship

Ok, if I have a table (Title_Author) that has the ISBN and Au_ID (author id) and I want to make sure that the Titles table ISBN column has a PK/FK relationship with the Title_Author table, I should be able to create a relationship to (just) the ISBN column. No? Well, the CONSTRAINT x FOREIGN KEY clause says it's an invalid relationship.

Ideas?

is the ISBN column the primary key (or part of a composite primary key) on the Titles table?

-Darren

|||Part of a composite key.

Creating one specific value as unique

Hello, I have a question about SQL Serer.
I don't know how to make a "constrain" whitch protect one column in table
for specific value.
In example:
I would to have unique value '33' in column a
'id' , 'a'
1, 33
2, 45
3, 45
4, 45
6, 33 - I want to have exception i this momentUNIQUE
Create Table Test
(
ID INT,
Nombres INT UNIQUE
)
Insert into Test Values (1,1)
Insert into Test Values (1,1)
Look in BOL --Section CREATE TABLE
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
Newsbeitrag news:3669E088-9E38-4CEF-94E1-A6A5715B4134@.microsoft.com...
> Hello, I have a question about SQL Serer.
> I don't know how to make a "constrain" whitch protect one column in table
> for specific value.
> In example:
> I would to have unique value '33' in column a
> 'id' , 'a'
> 1, 33
> 2, 45
> 3, 45
> 4, 45
> 6, 33 - I want to have exception i this moment|||Jens
I think it is not exactly what he wanted
>I would to have unique value '33' in column a
'>id' , 'a'
>1, 33
>2, 45
>3, 45
>4, 45
> 6, 33 - I want to have exception i this moment
It will be thrown an error when you try to insert a value=45
Well ,the following solution in terms of performance amy be not ideal , so
you can re-write it as
a trigger to do the same thing.
CREATE TABLE TableA
(
col INT
)
INSERT INTO TableA VALUES (20)--WORKS
GO
CREATE FUNCTION dbo.fn_check_TableA()
RETURNS bit
AS
BEGIN
IF EXISTS(SELECT *
FROM TableA
WHERE col = 20
GROUP BY col
HAVING COUNT(*)>1)
RETURN 0
RETURN 1
END
GO
ALTER TABLE TableA
ADD CONSTRAINT df_col CHECK (dbo.fn_check_TableA()=1)
GO
INSERT INTO TableA VALUES (30)--works
INSERT INTO TableA VALUES (30)--works
INSERT INTO TableA VALUES (20)--failed
DROP TABLE TableA
DROP FUNCTION dbo.fn_check_TableA
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:O6qk2DOQFHA.3076@.TK2MSFTNGP14.phx.gbl...
> UNIQUE
>
> Create Table Test
> (
> ID INT,
> Nombres INT UNIQUE
> )
> Insert into Test Values (1,1)
> Insert into Test Values (1,1)
> Look in BOL --Section CREATE TABLE
> HTH, Jens Smeyer.
> --
> http://www.sqlserver2005.de
> --
> "Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:3669E088-9E38-4CEF-94E1-A6A5715B4134@.microsoft.com...
table
>|||Didnt he want a exception ?
"I want to have exception i this moment"
Jens.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:OZgR0NOQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> Jens
> I think it is not exactly what he wanted
> '>id' , 'a'
> It will be thrown an error when you try to insert a value=45
> Well ,the following solution in terms of performance amy be not ideal , so
> you can re-write it as
> a trigger to do the same thing.
> CREATE TABLE TableA
> (
> col INT
> )
> INSERT INTO TableA VALUES (20)--WORKS
> GO
> CREATE FUNCTION dbo.fn_check_TableA()
> RETURNS bit
> AS
> BEGIN
> IF EXISTS(SELECT *
> FROM TableA
> WHERE col = 20
> GROUP BY col
> HAVING COUNT(*)>1)
> RETURN 0
> RETURN 1
> END
> GO
> ALTER TABLE TableA
> ADD CONSTRAINT df_col CHECK (dbo.fn_check_TableA()=1)
> GO
> INSERT INTO TableA VALUES (30)--works
> INSERT INTO TableA VALUES (30)--works
> INSERT INTO TableA VALUES (20)--failed
> DROP TABLE TableA
> DROP FUNCTION dbo.fn_check_TableA
>
>
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:O6qk2DOQFHA.3076@.TK2MSFTNGP14.phx.gbl...
> table
>|||Try this ...
ALTER TABLE [dbo].[test] WITH NOCHECK ADD
CONSTRAINT [IX_test] UNIQUE NONCLUSTERED
(
id
)|||Yep, but it still should be allowed to insert another value,45? Am I right?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:u1VpVQOQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> Didnt he want a exception ?
> "I want to have exception i this moment"
> Jens.
>
> "Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
> news:OZgR0NOQFHA.3544@.TK2MSFTNGP12.phx.gbl...
so
>|||Dead Jans thank you for yor reply, but you did not understand my question .
If i use yours script i will get all values as unique. But I would like to
get ONLY ONE - SPECIFIC VALUE (in my example 33) as unique , other values
can duplicate.
"Jens Sü?meyer" wrote:

> UNIQUE
>
> Create Table Test
> (
> ID INT,
> Nombres INT UNIQUE
> )
> Insert into Test Values (1,1)
> Insert into Test Values (1,1)
> Look in BOL --Section CREATE TABLE
> HTH, Jens Sü?meyer.|||Thank Uri Dimanti, this answer solve part of my problem :)
But i have another question - how to solve this problem on sql server ce (on
sql server ce i can't create functions) ?
Mariusz Wilk|||Yeah, bit i think that ok, because in his example on exception is thown in
the fifth line.
So the exception or the Contraint should be just palin unique.
Jens.
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:uFQoxSOQFHA.2580@.TK2MSFTNGP10.phx.gbl...
> Yep, but it still should be allowed to insert another value,45? Am I
> right?
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:u1VpVQOQFHA.3196@.TK2MSFTNGP12.phx.gbl...
> so
>|||OK, understand.
Thanks for clearing up.
"Mariusz Wilk" <Mariusz Wilk@.discussions.microsoft.com> schrieb im
Newsbeitrag news:C233C0DD-E834-4744-BFC6-FB2C5422F1E9@.microsoft.com...
> Dead Jans thank you for yor reply, but you did not understand my question
> .
> If i use yours script i will get all values as unique. But I would like to
> get ONLY ONE - SPECIFIC VALUE (in my example 33) as unique , other
> values
> can duplicate.
>
> "Jens Smeyer" wrote:
>
>
>

Saturday, February 25, 2012

Creating New Indexes

I have a table that I need to update and the update is killing everything
else because the column I need to update appears to not be indexed. Now, I
thought I could go into the Design Table screen and add an index, but that
doesn't seem to have helped me. So, how then do I check what indexes and/or
keys exist on a given table? Thanks.
WillieWillie,
Try:
EXEC SP_HELPINDEX YOURTABLENAME
or use Manage Indexes in Query Analyzer.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hi,
The best way Jelly told u
if u need more then u can search like this
declare @.id varchar(20)
select @.id=id from sysobjects where name='custtable'
select * from sysindexes where id=@.id
from
Doller|||You don't need to index the column that's being updated to improve
performance just the index the columns in the WHERE clause.
Exmaple
1 million rows or
col1 int
col2 int
UPDATE table SET col1 = 5 WHERE col2 = 9
A full table scan must be performed to locate the data, if you add an index
on col1 a table scan will still be performed because what you're searching
on is col2
you should place an index on col2. This will enable SQL server to locate the
row quickly.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hmm... OK, so I go in and the Index does exist. Now, the column I am
updating is the same column that I am querying on. Basically, I am trying to
do a bulk update on a column and that column is the only thing that
designates it. Here is the table create script:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CustomerProduct]') and OBJECTPROPERTY(id, N'IsUse
rTable')
= 1)
drop table [dbo].[CustomerProduct]
GO
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_A
S NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
And the Index info:
customerproduct_Site_ProductNumber nonclustered located on PRIMARY iSiteId,
chProductNumber
CustomerProduct0 nonclustered located on PRIMARY iOwnerId, iProductId
IX_CustomerProduct_Source nonclustered located on PRIMARY iSourceId
NNXCustomerProduct_iContactId nonclustered located on PRIMARY iContactId
NNXcustomerproduct_ownerid nonclustered located on PRIMARY iOwnerId
NNXcustomerproduct_prodnumber nonclustered located on PRIMARY
chProductNumber
NNXcustomerproduct_serialnum nonclustered located on PRIMARY vchSerialNumber
NNXCustomerProductInsertDate nonclustered located on PRIMARY dtInsertDate
NNXCustomerProductRecStat nonclustered located on PRIMARY tiRecordStatus
PKNUCCustomerProduct clustered, unique, primary key located on PRIMARY
iProductId, iSiteId
and finally the field I am searching/updating on is chProductNumber. Thanks
again for your help.
Willie
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:LLn1f.7212$nF6.3110@.fe04.news.easynews.com...
> You don't need to index the column that's being updated to improve
> performance just the index the columns in the WHERE clause.
> Exmaple
> 1 million rows or
> col1 int
> col2 int
> UPDATE table SET col1 = 5 WHERE col2 = 9
> A full table scan must be performed to locate the data, if you add an
> index on col1 a table scan will still be performed because what you're
> searching on is col2
> you should place an index on col2. This will enable SQL server to locate
> the row quickly.
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>

Creating New Indexes

I have a table that I need to update and the update is killing everything
else because the column I need to update appears to not be indexed. Now, I
thought I could go into the Design Table screen and add an index, but that
doesn't seem to have helped me. So, how then do I check what indexes and/or
keys exist on a given table? Thanks.
Willie
Willie,
Try:
EXEC SP_HELPINDEX YOURTABLENAME
or use Manage Indexes in Query Analyzer.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>
|||Hi,
The best way Jelly told u
if u need more then u can search like this
declare @.id varchar(20)
select @.id=id from sysobjects where name='custtable'
select * from sysindexes where id=@.id
from
Doller
|||You don't need to index the column that's being updated to improve
performance just the index the columns in the WHERE clause.
Exmaple
1 million rows or
col1 int
col2 int
UPDATE table SET col1 = 5 WHERE col2 = 9
A full table scan must be performed to locate the data, if you add an index
on col1 a table scan will still be performed because what you're searching
on is col2
you should place an index on col2. This will enable SQL server to locate the
row quickly.
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>
|||Hmm... OK, so I go in and the Index does exist. Now, the column I am
updating is the same column that I am querying on. Basically, I am trying to
do a bulk update on a column and that column is the only thing that
designates it. Here is the table create script:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CustomerProduct]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[CustomerProduct]
GO
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
And the Index info:
customerproduct_Site_ProductNumber nonclustered located on PRIMARY iSiteId,
chProductNumber
CustomerProduct0 nonclustered located on PRIMARY iOwnerId, iProductId
IX_CustomerProduct_Source nonclustered located on PRIMARY iSourceId
NNXCustomerProduct_iContactId nonclustered located on PRIMARY iContactId
NNXcustomerproduct_ownerid nonclustered located on PRIMARY iOwnerId
NNXcustomerproduct_prodnumber nonclustered located on PRIMARY
chProductNumber
NNXcustomerproduct_serialnum nonclustered located on PRIMARY vchSerialNumber
NNXCustomerProductInsertDate nonclustered located on PRIMARY dtInsertDate
NNXCustomerProductRecStat nonclustered located on PRIMARY tiRecordStatus
PKNUCCustomerProduct clustered, unique, primary key located on PRIMARY
iProductId, iSiteId
and finally the field I am searching/updating on is chProductNumber. Thanks
again for your help.
Willie
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:LLn1f.7212$nF6.3110@.fe04.news.easynews.com...
> You don't need to index the column that's being updated to improve
> performance just the index the columns in the WHERE clause.
> Exmaple
> 1 million rows or
> col1 int
> col2 int
> UPDATE table SET col1 = 5 WHERE col2 = 9
> A full table scan must be performed to locate the data, if you add an
> index on col1 a table scan will still be performed because what you're
> searching on is col2
> you should place an index on col2. This will enable SQL server to locate
> the row quickly.
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>

Creating New Indexes

I have a table that I need to update and the update is killing everything
else because the column I need to update appears to not be indexed. Now, I
thought I could go into the Design Table screen and add an index, but that
doesn't seem to have helped me. So, how then do I check what indexes and/or
keys exist on a given table? Thanks.
WillieWillie,
Try:
EXEC SP_HELPINDEX YOURTABLENAME
or use Manage Indexes in Query Analyzer.
HTH
Jerry
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hi,
The best way Jelly told u
if u need more then u can search like this
declare @.id varchar(20)
select @.id=id from sysobjects where name='custtable'
select * from sysindexes where id=@.id
from
Doller|||You don't need to index the column that's being updated to improve
performance just the index the columns in the WHERE clause.
Exmaple
1 million rows or
col1 int
col2 int
UPDATE table SET col1 = 5 WHERE col2 = 9
A full table scan must be performed to locate the data, if you add an index
on col1 a table scan will still be performed because what you're searching
on is col2
you should place an index on col2. This will enable SQL server to locate the
row quickly.
--
Nik Marshall-Blank MCSD/MCDBA
Linz, Austria
"Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>I have a table that I need to update and the update is killing everything
>else because the column I need to update appears to not be indexed. Now, I
>thought I could go into the Design Table screen and add an index, but that
>doesn't seem to have helped me. So, how then do I check what indexes and/or
>keys exist on a given table? Thanks.
> Willie
>|||Hmm... OK, so I go in and the Index does exist. Now, the column I am
updating is the same column that I am querying on. Basically, I am trying to
do a bulk update on a column and that column is the only thing that
designates it. Here is the table create script:
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[CustomerProduct]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[CustomerProduct]
GO
CREATE TABLE [dbo].[CustomerProduct] (
[iProductId] [int] NOT NULL ,
[iSiteId] [int] NOT NULL ,
[iOwnerId] [int] NOT NULL ,
[chLanguageCode] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[iContactId] [int] NULL ,
[chProductNumber] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[vchSerialNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[flQuantity] [OnyxFloat] NULL ,
[dtPurchaseDate] [datetime] NULL ,
[iTrackingId] [int] NULL ,
[iSourceId] [int] NULL ,
[iStatusId] [int] NULL ,
[iAccessCode] [int] NOT NULL ,
[vchUser1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[vchUser10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[chInsertBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtInsertDate] [datetime] NOT NULL ,
[chUpdateBy] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[dtUpdateDate] [datetime] NOT NULL ,
[tiRecordStatus] [tinyint] NOT NULL ,
[dtModifiedDate] [smalldatetime] NULL
) ON [PRIMARY]
GO
And the Index info:
customerproduct_Site_ProductNumber nonclustered located on PRIMARY iSiteId,
chProductNumber
CustomerProduct0 nonclustered located on PRIMARY iOwnerId, iProductId
IX_CustomerProduct_Source nonclustered located on PRIMARY iSourceId
NNXCustomerProduct_iContactId nonclustered located on PRIMARY iContactId
NNXcustomerproduct_ownerid nonclustered located on PRIMARY iOwnerId
NNXcustomerproduct_prodnumber nonclustered located on PRIMARY
chProductNumber
NNXcustomerproduct_serialnum nonclustered located on PRIMARY vchSerialNumber
NNXCustomerProductInsertDate nonclustered located on PRIMARY dtInsertDate
NNXCustomerProductRecStat nonclustered located on PRIMARY tiRecordStatus
PKNUCCustomerProduct clustered, unique, primary key located on PRIMARY
iProductId, iSiteId
and finally the field I am searching/updating on is chProductNumber. Thanks
again for your help.
Willie
"Nik Marshall-Blank" <Nik@.here.com> wrote in message
news:LLn1f.7212$nF6.3110@.fe04.news.easynews.com...
> You don't need to index the column that's being updated to improve
> performance just the index the columns in the WHERE clause.
> Exmaple
> 1 million rows or
> col1 int
> col2 int
> UPDATE table SET col1 = 5 WHERE col2 = 9
> A full table scan must be performed to locate the data, if you add an
> index on col1 a table scan will still be performed because what you're
> searching on is col2
> you should place an index on col2. This will enable SQL server to locate
> the row quickly.
> --
> Nik Marshall-Blank MCSD/MCDBA
> Linz, Austria
> "Willie Bodger" <williebnospam@.lap_ink.c_m> wrote in message
> news:%23qjZpdsyFHA.2516@.TK2MSFTNGP12.phx.gbl...
>>I have a table that I need to update and the update is killing everything
>>else because the column I need to update appears to not be indexed. Now, I
>>thought I could go into the Design Table screen and add an index, but that
>>doesn't seem to have helped me. So, how then do I check what indexes
>>and/or keys exist on a given table? Thanks.
>> Willie
>

Creating Nested Tables inside of a Existing SQL Server Express Table

Hello,

Quick question, I hope, I am trying to create a table that has a column that is a nested table in SQL Server 2005 Express Edition. Any ideas how I could go about doing this?

Sincerely,

James Simpson

Straightway Technologies Inc.

The only way to "create a table that has a column that is a nested table", is to use an xml datatype.

Refer to Books Online, Topic: XML [SQL Server]

|||

As Arnie indicates, SQL Server doesn't support TABLE as a data type within a table. There is probably another way to accomlish what you're trying to do without going to XML, but it's hard to say without know what your goal is. In general, I'd suggest that you create a new table for your "nested" information and then use a 1:Many relationship between the two tables to map the nested info to the parent record.

Mike

Tuesday, March 27, 2012

Creating xsd schema file for a xml file ...

Creating xsd schema file for a xml file ...

Sunday, March 25, 2012

Creating Unique Indexes on Partition table..

Creating Total to-date query...

Thursday, March 22, 2012

Creating temporary table

Creating temporary table

Tuesday, March 20, 2012

Creating table where column_names are results from query from other table

Sunday, March 11, 2012

creating sp to export column to comma delimited list

Creating reports in MS SQL Server Report Server

Thursday, March 8, 2012

creating replication with extra column

creating replication with extra column

Creating relationship between column and replacement column

Creating relationship between column and replacement column

Creating relationship between column and replacement column

Wednesday, March 7, 2012

Creating PK/FK Relationship

Creating one specific value as unique

Saturday, February 25, 2012

Creating New Indexes

Creating New Indexes

Creating New Indexes

Creating Nested Tables inside of a Existing SQL Server Express Table

sql database integrity

Blog Archive

About Me