get % distribution of invalid data?

We've got the requirement where we need to load data (usually dirty of course) from a flat file, and for every column, if the value is invalid, I'm putting a -1 in the field as it flows through the data stream - no problem. (I'm converting all invalids to -1 so they'll go into my eventual int column)

My question though is - at the end of the data flow, is there any smooth way to capture the % invalid values in each column. An example: If my table is as follows...where the # 1 signifies "clean data" and -1 signifies dirty.

col1 col2 col3 col4

1 1 1 1

1 -1 -1 1

-1 1 -1 1

-1 1 -1 1

I would want my % distribution query/ssis result to return:

col1 col2 col3 col4

.5 .25 .75 0

I do not want to do a select/ssis process for each column. Any way to do this all in one shot?

You could write a script component to track the number of good and bad values, and output them to an asynchronous output. Or execute a SQL statement after the data flow to get the statistics.|||

jwelch wrote:

execute a SQL statement after the data flow to get the statistics.

That's what I would try first....just to keep it simpler

Geometry Data type in SQL Server 2005

Does SQL Server 2005 have a geometry data type?
I am trying to figure out to use SQL Server 2005 or Oracle
The primary requirement is to be able to use any GIS software in the develop
ment of a new workflow, to access data, analyze and map data stored in a dat
abase.
The following is what I was able to find on the web.
While both Oracle and Microsoft SQL Server support the storage and maintenan
ce of geo-spatial information, each vendor has chosen a fundamentally differ
ent approach to the implementation of this capability.
Oracle has developed a geometry data type that is fully integrated with the
underlying Oracle 9i kernel. Oracle allows users to interact directly with t
he database using SQL language. In addition, Oracle has published the struct
ure of its geometry, making it an open specification. Any GIS software is ca
pable accessing the data stored in Oracle. In addition, all business rules f
or data and geo-spatial data integrity is built into this model, and any acc
ess, input, edits or other interaction must adhere to the rules that are des
igned in the database no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. Eac
h GIS vendor is therefore responsible for developing a method for storing GI
S information in this database. Thus, Intergraph, ESRI, and MapInfo have dev
eloped their own binary geometry structures to allow them to store geo-spati
al information in SQL Server. This approach makes the geo-spatial informatio
n dependent on the GIS software that is used.
Can anyone please help me!docsql wrote:
> Does SQL Server 2005 have a geometry data type?
> I am trying to figure out to use SQL Server 2005 or Oracle
> The primary requirement is to be able to use any GIS software in the devel
opment of a new workflow, to access data, analyze and map data stored in a d
atabase.
> The following is what I was able to find on the web.
> While both Oracle and Microsoft SQL Server support the storage and mainten
ance of geo-spatial information, each vendor has chosen a fundamentally diff
erent approach to the implementation of this capability.
> Oracle has developed a geometry data type that is fully integrated with the underl
ying Oracle 9i kernel. Oracle allows users to interact directly with the database us
ing SQL language. In addition, Oracle has published the structure of its geometry, m
aki
ng it an open specification. Any GIS software is capable accessing the data
stored in Oracle. In addition, all business rules for data and geo-spatial d
ata integrity is built into this model, and any access, input, edits or othe
r interaction must adhere t
o the rules that are designed in the database no matter the application accessing the databa
se.
> Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS
vendor is therefore responsible for developing a method for storing GIS information
in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binar
y g
eometry structures to allow them to store geo-spatial information in SQL Server. This approa
ch makes the geo-spatial information dependent on the GIS software that is used.een">
>
>
There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I've never heard of a geometry data type. ESRI's products do run on
SQL though - SDE is the one that comes to mind. You can use SQL if
you're using ESRI....see esri's site:
www.esri.com
SDE page:
http://www.esri.com/software/arcgis/arcsde/index.html
http://www.esri.com/news/releases/0...arcsde_sql.html

Geometry Data type in SQL Server 2005

Does SQL Server 2005 have a geometry data type?
I am trying to figure out to use SQL Server 2005 or Oracle
The primary requirement is to be able to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.
The following is what I was able to find on the web.
While both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach to the implementation of this capability.
Oracle has developed a geometry data type that is fully integrated with the underlying Oracle 9i kernel. Oracle allows users to interact directly with the database using SQL language. In addition, Oracle has published the structure of its geometry, making it an open specification. Any GIS software is capable accessing the data stored in Oracle. In addition, all business rules for data and geo-spatial data integrity is built into this model, and any access, input, edits or other interaction must adhere to the rules that are designed in the database no matter the application accessing the database.
Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binary geometry structures to allow them to store geo-spatial information in SQL Server. This approach makes the geo-spatial information dependent on the GIS software that is used.
Can anyone please help me!
docsql wrote:
> Does SQL Server 2005 have a geometry data type?
> I am trying to figure out to use SQL Server 2005 or Oracle
> The primary requirement is to be able to use any GIS software in the development of a new workflow, to access data, analyze and map data stored in a database.
> The following is what I was able to find on the web.
> While both Oracle and Microsoft SQL Server support the storage and maintenance of geo-spatial information, each vendor has chosen a fundamentally different approach to the implementation of this capability.
> Oracle has developed a geometry data type that is fully integrated with the underlying Oracle 9i kernel. Oracle allows users to interact directly with the database using SQL language. In addition, Oracle has published the structure of its geometry, maki
ng it an open specification. Any GIS software is capable accessing the data stored in Oracle. In addition, all business rules for data and geo-spatial data integrity is built into this model, and any access, input, edits or other interaction must adhere t
o the rules that are designed in the database no matter the application accessing the database.
> Conversely, Microsoft has not developed a geometry object in SQL Server. Each GIS vendor is therefore responsible for developing a method for storing GIS information in this database. Thus, Intergraph, ESRI, and MapInfo have developed their own binary g
eometry structures to allow them to store geo-spatial information in SQL Server. This approach makes the geo-spatial information dependent on the GIS software that is used.
>
>
There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||There is no specific datatype. That doesn't prevent you storing
geo-spacial data as your post explains. You can define your own complex
datatypes in SQL Server.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
|||I've never heard of a geometry data type. ESRI's products do run on
SQL though - SDE is the one that comes to mind. You can use SQL if
you're using ESRI....see esri's site:
www.esri.com
SDE page:
http://www.esri.com/software/arcgis/arcsde/index.html
http://www.esri.com/news/releases/06...rcsde_sql.html
sql

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating XML data based on the Schema

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Can a stored procedure in SQL Server 2005 generate XML data based on the schema?

Yes...

Create Proc dbo.GenXMLWithSchemaValidation
As
Begin
Set Nocount On

DECLARE @.x XML (HumanResources.HRResumeSchemaCollection)
SET @.x = (
SELECT ContactID,
FirstName,
LastName,
AdditionalContactInfo.query('
declare namespace aci="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactInfo";
declare namespace act="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ContactTypes";
//act:telephoneNumber/act:number') as MorePhoneNumbers
FROM Person.Contact
FOR XML AUTO, TYPE)
SELECT @.x
Set Nocount Off
End

Hope this helps,

Derek

|||

Thanks Derek

i appreciate your help.

Vaishu

Generating report using batch process

Hi all,

I have tried asking the same question in other forums. All i get is links

Please help me. I have the following requirement:

I have the following tables:
Theater - TheaterId, TheaterName, Revenues,locationid, stateid
State - StateId, StateName
Location - LocationId, LocationName, StateId

I want to generate reports that will tell me the revenue generated for each theater in each location in a state. I want to run a batch process which will loop through the 3 tables and will passing the location and state id as parameters one by one. I want each report to be generated as a pdf and stored in a location. How do I do this?

Thanks.

Two methods come to mind:

1. You can run reports from the command line using the rs command (documentation) as described in the following SQLJunkies article:

http://sqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

which doesn't seem to be up for me so here is the Google cache link:

http://64.233.167.104/search?q=cache:XvoyQYWNs50Jqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk&hl=en&gl=us&strip=1

Within the VB script that you run, query your tables and feed the parameters.

2. You could use a variant of a Data Driven Subscription. You will need the Enterprise version of SSRS to use Data Driven Subscriptions. Here is a tutorial on building one:

http://msdn2.microsoft.com/en-us/library/ms169673.aspx

The change that you will make to the standard script is in the Define a query to retrieve subscriber data section. You will just add to the standard query a join to your three tables and use the query results as parameters to the report.

I came accross this article by Jason Selburg which claims to allow you to build your own data driven subscriptions using Standard Edition:

http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp

and his follow up:

http://www.sqlservercentral.com/columnists/jselburg/2824.asp

The reason that you are getting links is that people are willing take the time to give assistance and pointers but not specific solutions. The problem you describe is an interesting one and one that will I will now probably try and cover in my blog in the near future. When I do, I will post the link here and you should be able to get code that is closer to a complete solution.

Larry Smithmier

|||

dear larry,

thanks for the link.

vidkshi

Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
Regards,
MZeeshan
Ive used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
--
Regards,
MZeeshanIve used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

Generating Replication alerts using SMTP.

I cannot use SQL Mail due to the requirement of outlook client installation
on the server (that I cannot do). The other option is to use SMTP.
I have tried to use xp_smtp_sendmail by adding tasks in the system generated
jobs but it doesn't seem to work.
Anyone any ideas?
Regards,
MZeeshanIve used it just fine. I made these procs because I needed to add a job step
named "Alert" to every job on every server that I have. Keep in mind this
will change the way your job steps are run, and may not be applicable to all
jobs. (Say if they have 5 steps, and steps 1 - 4 are Quit with Failure when
the step fails. )
Use msdb
go
ALTER procedure admin_AddAlertJobStep
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.command varchar(256)
set @.command = 'exec admin_AlertFailedJob '''+ @.JobName
+''','''+@.Recipient+''''
exec sp_add_jobstep @.job_name = @.JobName
, @.step_name = 'Alert'
, @.command = @.Command
, @.on_success_action = 2
, @.on_fail_action = 2
declare @.StepId int
set @.StepId = (select max(step_id) - 1 from SysJobSteps sjs
inner join SysJobs sj on sjs.job_id = sj.job_id
where sj.Name = @.JobName)
exec sp_update_jobstep
@.job_name = @.JobName
, @.step_id = @.StepId
, @.on_fail_action = 3
use master
go
ALTER procedure admin_AlertFailedJob
@.JobName varchar(256),
@.Recipient varchar(256)
as
declare @.rc int
declare @.ServerName varchar(256)
set @.ServerName = (select @.@.ServerName)
set @.JobName = @.JobName + ' failed!'
exec @.rc = master.dbo.xp_smtp_sendmail
@.FROM = @.ServerName
,@.TO = @.Recipient
,@.Subject = @.JobName
,@.server = N'address'
,@.Port = 25
"MZeeshan" <mzeeshan@.community.nospam> wrote in message
news:50D1D59D-BA02-46A3-96FE-1770B00E15D8@.microsoft.com...
>I cannot use SQL Mail due to the requirement of outlook client installation
> on the server (that I cannot do). The other option is to use SMTP.
> I have tried to use xp_smtp_sendmail by adding tasks in the system
> generated
> jobs but it doesn't seem to work.
> Anyone any ideas?
> --
> Regards,
> MZeeshan

Generating a Group of Reports

We are thinking of a possible requirement for our custom application (which
will use Reporting Services), but we are unsure if it is possible.
Say you have 20 individual reports. And, these 20 reports are split up into
4 report groups (5 reports per report group). This is more of a logical
grouping (or maybe the report groups represent folders on the report
server). Anyhow, is it possible to generate the entire report group (thus
generating all 5 reports in the report group at one time) instead of having
to generate each individual report? Does Reporting Services support such an
idea?
Thanks.Yeah, I have a similar question(s).
Is it possible to have 5 separate reports (RDLs) and when they are executed
have the output of each report concatenated into 1 long report (probably
with a page break between each report)?
Or, does one have to loop through each report, calling it via the URL
method, and displaying each report in a separate browser instance (thus 5
separate windows opened to see all five reports)?
Or'?
Any help is appreciated. Thanks in advance.
"Dex" <dex@.yahoo.com> wrote in message
news:%23dePSvpmEHA.3396@.tk2msftngp13.phx.gbl...
> We are thinking of a possible requirement for our custom application
(which
> will use Reporting Services), but we are unsure if it is possible.
> Say you have 20 individual reports. And, these 20 reports are split up
into
> 4 report groups (5 reports per report group). This is more of a logical
> grouping (or maybe the report groups represent folders on the report
> server). Anyhow, is it possible to generate the entire report group (thus
> generating all 5 reports in the report group at one time) instead of
having
> to generate each individual report? Does Reporting Services support such
an
> idea?
> Thanks.
>

Generate Sequence Number based on other columns

Hi,
Please help me to find a solution.

My requirement is similar to this.
Example:

TABLEA(pk_id, pol_mas_id,pol_seq_id)

values are
pk_id pol_mas_id pol_seq_id
1 1
2 1
3 2
4 2
5 3
6 3
7 3

Now i need to update the column "pol_seq_id" as below.

pk_id pol_mas_id pol_seq_id
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1
6 3 2
7 3 3

Currently i am using cursor and you know performance sucks.

Is there any way to increment the data in the column(pol_seq_id) based on other column (pol_mas_id).

Thanks in Advance!!

Try:

select

pk_id, pol_mas_id, row_number() over(partition by pol_mas_id order by pk_id) as pol_seq_id

from

dbo.t1

go

It is helpful to have an index by (pol_mas_id, pk_id)

AMB

|||Thanks a lot for the response !!!
It works for select as when i am trying to update the same i am getting this error
"Windowed functions can only appear in the SELECT or ORDER BY clauses."
so i have populated the same into #temp table and then updated with join statement.

It works
|||

Try using a CTE.

;with cte

as

(

select

pk_id, pol_mas_id, pol_seq_id, row_number() over(partition by pol_mas_id order by pk_id) as rn

from

dbo.t1

go

)

update cte

set pol_seq_id = rn;

AMB

Generarting XML based on Schema in SQLSERVER 2005

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

|||

Todd Pfleiger wrote:

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

thanks for your help

Generarting XML based on Schema in SQLSERVER 2005

Hi,

i have the following requirement

Can a stored procedure in SQL Server 2005 generate XML data based on the schema, We don't prefer to manually build an xml string inside the stored proc?

Is there any SQL Server 2005 feature to do it if possible?

i appreciate your help.

Thanks,

Vaishu

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

|||

Todd Pfleiger wrote:

Yes this is possible. Michael Rys' article about FOR XML support and the Xml datatype shows examples of this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/forxml2k5.asp

The section entitled "Integration with the Xml Datatype" would most likely address your scenario.

thanks for your help