Property Owner is not available for Database '[DTA_TaxMan]'. This property may not exist

I get the following error everytime I try to go to the properties of
one of my databases.
The help link was useless.
I am not sure what to do. I am the top level administrator to my
server.
===================================
Cannot show requested dialog.
===================================
Cannot show requested dialog. (SqlMgmt)
Program Location:
at
Microsoft.SqlServer.Management.SqlMgmt.DefaultLaun chFormHostedControlAllocator.AllocateDialog(XmlDoc ument
initializationXml, IServiceProvider dialogServiceProvider,
CDataContainer dc)
at
Microsoft.SqlServer.Management.SqlMgmt.DefaultLaun chFormHostedControlAllocator.Microsoft.SqlServer.M anagement.SqlMgmt.ILaunchFormHostedControlAllocato r.CreateDialog(XmlDocument
initializationXml, IServiceProvider dialogServiceProvider)
at
Microsoft.SqlServer.Management.SqlMgmt.LaunchForm. InitializeForm(XmlDocument
doc, IServiceProvider provider, ISqlControlCollection control)
at
Microsoft.SqlServer.Management.SqlMgmt.LaunchForm. .ctor(XmlDocument
doc, IServiceProvider provider)
at
Microsoft.SqlServer.Management.UI.VSIntegration.Ob jectExplorer.ToolsMenuItem.OnCreateAndShowForm(ISe rviceProvider
sp, XmlDocument doc)
at
Microsoft.SqlServer.Management.SqlMgmt.RunningForm sTable.RunningFormsTableImpl.ThreadStarter.StartTh read()
===================================
Property Owner is not available for Database '[DTA_TaxMan]'. This
property may not exist for this object, or may not be retrievable due
to insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00. 2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo. ExceptionTemplates.PropertyCannotBeRetrievedExcept ionText&EvtID=Owner&LinkId=20476[/url]
Program Location:
at
Microsoft.SqlServer.Management.Smo.PropertyCollect ion.HandleNullValue(Int32
index)
at Microsoft.SqlServer.Management.Smo.Database.get_Ow ner()
at
Microsoft.SqlServer.Management.SqlManagerUI.Create DatabaseData.DatabasePrototype.DatabaseData..ctor( CDataContainer
context, String databaseName)
at
Microsoft.SqlServer.Management.SqlManagerUI.Create DatabaseData.DatabasePrototype.LoadDefinition(Stri ng
newName)
at
Microsoft.SqlServer.Management.SqlManagerUI.Create DatabaseData.DatabasePrototype..ctor(CDataContaine r
context)
at
Microsoft.SqlServer.Management.SqlManagerUI.DBProp Sheet..ctor(CDataContainer
context)
I have also signed back in with SA, and still having the same problem.
sql

Property Owner is not available for Database '[DTA_TaxMan]'. This property may not exist

I get the following error everytime I try to go to the properties of
one of my databases.
The help link was useless.
I am not sure what to do. I am the top level administrator to my
server.
===================================
Cannot show requested dialog.
===================================
Cannot show requested dialog. (SqlMgmt)
--
Program Location:
at
Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.AllocateDialog(XmlDocument
initializationXml, IServiceProvider dialogServiceProvider,
CDataContainer dc)
at
Microsoft.SqlServer.Management.SqlMgmt.DefaultLaunchFormHostedControlAllocator.Microsoft.SqlServer.Management.SqlMgmt.ILaunchFormHostedControlAllocator.CreateDialog(XmlDocument
initializationXml, IServiceProvider dialogServiceProvider)
at
Microsoft.SqlServer.Management.SqlMgmt.LaunchForm.InitializeForm(XmlDocument
doc, IServiceProvider provider, ISqlControlCollection control)
at
Microsoft.SqlServer.Management.SqlMgmt.LaunchForm..ctor(XmlDocument
doc, IServiceProvider provider)
at
Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ToolsMenuItem.OnCreateAndShowForm(IServiceProvider
sp, XmlDocument doc)
at
Microsoft.SqlServer.Management.SqlMgmt.RunningFormsTable.RunningFormsTableImpl.ThreadStarter.StartThread()
===================================
Property Owner is not available for Database '[DTA_TaxMan]'. This
property may not exist for this object, or may not be retrievable due
to insufficient access rights. (Microsoft.SqlServer.Smo)
--
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.PropertyCannotBeRetrievedExceptionText&EvtID=Owner&LinkId=20476
--
Program Location:
at
Microsoft.SqlServer.Management.Smo.PropertyCollection.HandleNullValue(Int32
index)
at Microsoft.SqlServer.Management.Smo.Database.get_Owner()
at
Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.DatabaseData..ctor(CDataContainer
context, String databaseName)
at
Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype.LoadDefinition(String
newName)
at
Microsoft.SqlServer.Management.SqlManagerUI.CreateDatabaseData.DatabasePrototype..ctor(CDataContainer
context)
at
Microsoft.SqlServer.Management.SqlManagerUI.DBPropSheet..ctor(CDataContainer
context)I have also signed back in with SA, and still having the same problem.

Property IsPrimaryFile is not available for DataFile

Hi all,
I posted this following difficulty in
microsoft.public.sqlserver.programming forum.But could not get a
single answer.So I posted this here also.
I have restored a database in SQL SERVER 2005.After restoring the
database, I can make query on that particular database.But problem is
when I right click on the database name(to open the properties etc.),
I am getting the following error ....
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
Property IsPrimaryFile is not available for DataFile
'[FINSIEBPRD01_Data]'. This property may not exist for this object,
or
may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)
As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
compatibility level to 90, but still I am getting the problem.
Please help.
Regards,
MukutThis may sound silly, but double check the logical file names in sql 2005
after the restore. I just had this exact same problem, and after lots of
head-scratching noticed that SOMEHOW the logical filenames were different
after the restore to sql 2005. In my case I had 'jobsafe_dataa' and
'jobsafe_logg' instead of 'jobsafe_data' and 'jobsafe_log'. I simply used
the following to rename the logical files back to what they were, and it
works fine thereafter.
ALTER DATABASE pubs
MODIFY FILE (NAME = pubs, NEWNAME = pubs_data)
Let me know if that helps
Michael
"Mukut" wrote:

> Hi all,
> I posted this following difficulty in
> microsoft.public.sqlserver.programming forum.But could not get a
> single answer.So I posted this here also.
> I have restored a database in SQL SERVER 2005.After restoring the
> database, I can make query on that particular database.But problem is
> when I right click on the database name(to open the properties etc.),
> I am getting the following error ....
> TITLE: Microsoft SQL Server Management Studio
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsPrimaryFile is not available for DataFile
> '[FINSIEBPRD01_Data]'. This property may not exist for this object,
> or
> may not be retrievable due to insufficient access rights.
> (Microsoft.SqlServer.Smo)
> As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
> compatibility level to 90, but still I am getting the problem.
> Please help.
>
> Regards,
> Mukut
>

Property IsPrimaryFile is not available for DataFile

Hi all,
I posted this following difficulty in
microsoft.public.sqlserver.programming forum.But could not get a
single answer.So I posted this here also.
I have restored a database in SQL SERVER 2005.After restoring the
database, I can make query on that particular database.But problem is
when I right click on the database name(to open the properties etc.),
I am getting the following error ....
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
--
Property IsPrimaryFile is not available for DataFile
'[FINSIEBPRD01_Data]'. This property may not exist for this object,
or
may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)
As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
compatibility level to 90, but still I am getting the problem.
Please help.
Regards,
MukutThis may sound silly, but double check the logical file names in sql 2005
after the restore. I just had this exact same problem, and after lots of
head-scratching noticed that SOMEHOW the logical filenames were different
after the restore to sql 2005. In my case I had 'jobsafe_dataa' and
'jobsafe_logg' instead of 'jobsafe_data' and 'jobsafe_log'. I simply used
the following to rename the logical files back to what they were, and it
works fine thereafter.
ALTER DATABASE pubs
MODIFY FILE (NAME = pubs, NEWNAME = pubs_data)
Let me know if that helps
Michael
"Mukut" wrote:
> Hi all,
> I posted this following difficulty in
> microsoft.public.sqlserver.programming forum.But could not get a
> single answer.So I posted this here also.
> I have restored a database in SQL SERVER 2005.After restoring the
> database, I can make query on that particular database.But problem is
> when I right click on the database name(to open the properties etc.),
> I am getting the following error ....
> TITLE: Microsoft SQL Server Management Studio
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsPrimaryFile is not available for DataFile
> '[FINSIEBPRD01_Data]'. This property may not exist for this object,
> or
> may not be retrievable due to insufficient access rights.
> (Microsoft.SqlServer.Smo)
> As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
> compatibility level to 90, but still I am getting the problem.
> Please help.
>
> Regards,
> Mukut
>sql

Property IsPrimaryFile is not available for DataFile

Hi all,
I posted this following difficulty in
microsoft.public.sqlserver.programming forum.But could not get a
single answer.So I posted this here also.
I have restored a database in SQL SERVER 2005.After restoring the
database, I can make query on that particular database.But problem is
when I right click on the database name(to open the properties etc.),
I am getting the following error ....
TITLE: Microsoft SQL Server Management Studio
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
Property IsPrimaryFile is not available for DataFile
'[FINSIEBPRD01_Data]'. This property may not exist for this object,
or
may not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)
As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
compatibility level to 90, but still I am getting the problem.
Please help.
Regards,
Mukut
This may sound silly, but double check the logical file names in sql 2005
after the restore. I just had this exact same problem, and after lots of
head-scratching noticed that SOMEHOW the logical filenames were different
after the restore to sql 2005. In my case I had 'jobsafe_dataa' and
'jobsafe_logg' instead of 'jobsafe_data' and 'jobsafe_log'. I simply used
the following to rename the logical files back to what they were, and it
works fine thereafter.
ALTER DATABASE pubs
MODIFY FILE (NAME = pubs, NEWNAME = pubs_data)
Let me know if that helps
Michael
"Mukut" wrote:

> Hi all,
> I posted this following difficulty in
> microsoft.public.sqlserver.programming forum.But could not get a
> single answer.So I posted this here also.
> I have restored a database in SQL SERVER 2005.After restoring the
> database, I can make query on that particular database.But problem is
> when I right click on the database name(to open the properties etc.),
> I am getting the following error ....
> TITLE: Microsoft SQL Server Management Studio
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (SqlMgmt)
> --
> Property IsPrimaryFile is not available for DataFile
> '[FINSIEBPRD01_Data]'. This property may not exist for this object,
> or
> may not be retrievable due to insufficient access rights.
> (Microsoft.SqlServer.Smo)
> As per the suggestion of Andrew J. Kelly SQL MVP, I have changed the
> compatibility level to 90, but still I am getting the problem.
> Please help.
>
> Regards,
> Mukut
>

Property AnsiNullsStatus is not available for UserDefinedFunction...

If you create a table UDF in a SQL 8 database from Server Management
Studio, and then attempt to edit it again, you get the following error:
Property AnsiNullsStatus is not available for UserDefinedFunction
'[dbo].[TF_TEST]'. This property may not exist for this object, or may
not be retrievable due to insufficient access rights.
I have reproduced this with a number of UDFs
The UDF appears to be OK, but it is kind of irritating to have to open
up a different tool to edit it again.
Hello,
I have tested the issue on my side, but I didn't reproduce the issue. To
help me troubleshoot the issue, please post here the detail steps to
reproduce the issue. For your reference, I tested the issue by performing
the following steps:
1. Create a function on a SQL server 2000 database using SQL server 2005
management studio.
create function myuf()
returns table
return (select * from authors)
select * from test5.dbo.myuf()
2. Alter the function:
alter function myuf()
returns table
return (select * from authors where au_id='172-32-1176')
The following command works fine:
select * from test5.dbo.myuf()
I look forward to hearing from you.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Try these steps:
1.Create a multi-statement table function (not an inline; that works
OK) on a SQL 2000 db using SQL server 2005 management studio. I used
the wizard, but doing it by hand produces the same results:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:ScpoRich
-- Create date: 11/25/2005
-- Description:Test Table Function
-- =============================================
CREATE FUNCTION MSDNTest
(
-- Add the parameters for the function here
@.p1 int,
@.p2 char
)
RETURNS
@.Table_Var TABLE
(
c1 int,
c2 int
)
AS
BEGIN
insert into @.Table_Var(c1,c2)
values(@.p1,100)
RETURN
END
GO
2.The following command works fine:
SELECT * FROM [pubs].[dbo].[MSDNTest] (1,'')
3.The following command works fine:
ALTER FUNCTION MSDNTest
(@.p1 int, @.p2 char)
)
RETURNS
@.Table_Var TABLE (c1 int, c2 int)
AS
BEGIN
insert into @.Table_Var(c1,c2)
values(100,@.p1)
RETURN
END
GO
4.Right click function in the Object explorer and select 'Modify' from
the context menu.
5.Observe a dialog box with the following message:
TITLE: Microsoft SQL Server Management Studio
Property QuotedIdentifierStatus is not available for UserDefinedFunction
'[dbo].[MSDNTest]'. This property may not exist for this object, or may
not be retrievable due to insufficient access rights.
(Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdN...s&LinkId=20476
BUTTONS:
OK
6.Selecting Script AS -> CREATE To or Script As -> ALTER to will cause
similar errors to occur.
Sophie Guo [MSFT] wrote:
> Hello,
> I have tested the issue on my side, but I didn't reproduce the issue. To
> help me troubleshoot the issue, please post here the detail steps to
> reproduce the issue. For your reference, I tested the issue by performing
> the following steps:
> 1. Create a function on a SQL server 2000 database using SQL server 2005
> management studio.
> create function myuf()
> returns table
> return (select * from authors)
> select * from test5.dbo.myuf()
> 2. Alter the function:
> alter function myuf()
> returns table
> return (select * from authors where au_id='172-32-1176')
> The following command works fine:
> select * from test5.dbo.myuf()
> I look forward to hearing from you.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
|||Hi,
I have been having a similar problem whereby I am trying to script a
bunch of table functions from a SQL Server 2000 database through SQL
Management Studio. I have fiddled around with DB Options but to no
avail. Is there any resolution on this?
Cheers
Dave Mc
david.mcmahon@.ridgian.co.uk
http://www.xmlexperience.com
|||Rich (scporich@.community.nospam) writes:
> If you create a table UDF in a SQL 8 database from Server Management
> Studio, and then attempt to edit it again, you get the following error:
> Property AnsiNullsStatus is not available for UserDefinedFunction
> '[dbo].[TF_TEST]'. This property may not exist for this object, or may
> not be retrievable due to insufficient access rights.
> I have reproduced this with a number of UDFs
> The UDF appears to be OK, but it is kind of irritating to have to open
> up a different tool to edit it again.
There is a very similar bug on
http://lab.msdn.microsoft.com/produc...5-62d1fb31d0a4
which is reported as fixed for SP1.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Hello,
Based on my test, when connectting to a SQL2000 database using the SQL
Managemenet Studio, I can reproduce the issue. However, if I create a SQL
server 2000 database in SQL Managemenet Studio, which means the
compatibility level is "SQL server 2000(80)", everything works fine.
Therefore I think the workaround is to import the SQL server 2000 database
into SQL server 2005 instance. You can import the database using the Copy
database wizard.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Erland Sommarskog wrote:
> Rich (scporich@.community.nospam) writes:
>
> There is a very similar bug on
> http://lab.msdn.microsoft.com/produc...5-62d1fb31d0a4
> which is reported as fixed for SP1.
>
Yep - That looks like the one - thanks for the post.
Rich

Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
================================================== =====
SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid =
commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
================================================== =======
TIA
Kay
hi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ================================================== =====
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ================================================== =======
>
> TIA
> Kay
>
>
|||Its tough to tell you without more information about these tables, their keys
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itself.
HTH
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ================================================== =====
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ================================================== =======
>
> TIA
> Kay
>
>
sql

Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
========================================
===============
SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid =
commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
========================================
=================
TIA
Kayhi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ========================================
===============
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ========================================
=================
>
> TIA
> Kay
>
>|||Its tough to tell you without more information about these tables, their key
s
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itsel
f.
HTH
"Kay" wrote:

> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> ========================================
===============
> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid =
> commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> ========================================
=================
>
> TIA
> Kay
>
>

Proper indexs against query and optimization

Viewing trace following query gives Duration - 517470
Which indexes should be created on tables and how to make this query
optimized.
======================================================= SELECT top 1 package_description.name,
package_description.tier,
package_description.pid
FROM package_description
inner join package on package_description.pid = package.package_id
inner join courses on package.course_id = courses.id
inner join commission on package_description.pid = commission.package_id
inner join cinfo on commission.owner_id = cinfo.cid
WHERE (courses.id = 45448) and
(cinfo.cid = 121) and
(package_description.type <> 2)
and package_description.state_id = 41
ORDER BY package_description.available ASC
=========================================================
TIA
Kayhi,
check your indexes and see if you are using them properly at your join
tables. may be you also need to open an execution plan and see at which step
your query is taking most precentage. also may be you should also defragment
or reubild your indexes after checking the showcontig (focus on the log and
extent results).
thx,
Tomer
"Kay" wrote:
> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> =======================================================> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid => commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> =========================================================>
> TIA
> Kay
>
>|||Its tough to tell you without more information about these tables, their keys
and the relationships between them (and the execution plan). Also, from the
looks of the join it appears that your model is potentially de-normalized,
this adds another potential issue.
But, from what you have listed.
A good starting point is the following (these are not always true, but a
good starting point)
1) Make sure all the tables have a primary key
2) Set the primary key as clustered
3) Create a non-clustered index on the foreign keys
So, in your case
indexes for package_description
ON pid PK clustered
ON state_id, type, available NonClustered
indexes for package
ON package_id PK Clustered
ON course_id NonClustered
index for courses
id PK Clustered
index for commission
package_ID PK clustered
owner_id nonclustered
index cinfo
cid PK Clustered
Be forewarned, this is a bit of a blind guess. But, it will hopefully get
you started in the right direction. The root of your issue could very well
be outside of just index creation, and might be related to your schema itself.
HTH
"Kay" wrote:
> Viewing trace following query gives Duration - 517470
> Which indexes should be created on tables and how to make this query
> optimized.
> =======================================================> SELECT top 1 package_description.name,
> package_description.tier,
> package_description.pid
> FROM package_description
> inner join package on package_description.pid = package.package_id
> inner join courses on package.course_id = courses.id
> inner join commission on package_description.pid => commission.package_id
> inner join cinfo on commission.owner_id = cinfo.cid
> WHERE (courses.id = 45448) and
> (cinfo.cid = 121) and
> (package_description.type <> 2)
> and package_description.state_id = 41
> ORDER BY package_description.available ASC
> =========================================================>
> TIA
> Kay
>
>

Project opening - XML Document Error (118, 17)

While openeing the solution file (.sln), I got the following error:
"There is an error in XML document (118, 17)."
I am trying to open a project of SQL Server 2000 - Reporting Services.
Don't know what had happened actually.Hi,
Have you edited or changed anything on the rdl file / sln file.?
Amarnath
"Rohit Jain" wrote:
> While openeing the solution file (.sln), I got the following error:
> "There is an error in XML document (118, 17)."
> I am trying to open a project of SQL Server 2000 - Reporting Services.
> Don't know what had happened actually.|||Thanks Amarnath,
Yeah I have found the solution. Actually the error was occurring due to a
change in .rptproj file, and by this change the file itself got corrupted.
Thanks for your response Amarnath.
But stll I have 1 query - where I can found that XML document for which the
error was displayed.
"Amarnath" wrote:
> Hi,
> Have you edited or changed anything on the rdl file / sln file.?
> Amarnath
> "Rohit Jain" wrote:
> > While openeing the solution file (.sln), I got the following error:
> >
> > "There is an error in XML document (118, 17)."
> >
> > I am trying to open a project of SQL Server 2000 - Reporting Services.
> >
> > Don't know what had happened actually.

PROGRAMTICALLY ACCESS SQLDATASOURCE !

I am trying to acces an SQLDatasource in the code page, I have the following code but get the error as below, any one help please

The SQLDataSource returns 1 value named [ShippingRegion], I think that has somethjing to do with it ??!!

dsShippingRegion.Select(DataSourceSelectArguments.Empty)

Dim myReaderAs Data.IDataReader =CType(dsShippingRegion.Select(DataSourceSelectArguments.Empty), Data.IDataReader)
If myReader.ReadThen
If Convert.IsDBNull(myReader("ShippingRegion"))Then
Beep()
Else
Beep()
EndIf
EndIf

System.InvalidCastException was unhandled by user code
Message="Unable to cast object of type 'System.Data.DataView' to type 'System.Data.IDataReader'."
Source="App_Web_rd5quiy1"
StackTrace:
at admin_administer_shop_productaddnew.Page_Load(Object sender, EventArgs e) in E:\Web Development\WebSites\AJAX_sirs2hers\admin\administer_shop\productaddnew.aspx.vb:line 25
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

dsShippingRegion.Select(DataSourceSelectArguments.Empty)

In the SqlDataSource markup, set DataSourceMode="DataReader". If the DataSourceMode is DataSet (or is not set, since DataSet is the default), a DataView object is returned from Select, which is what you're seeing. If the DataSourceMode is DataReader, an IDataReader object is returned. Seehttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.select.aspx for more details.

Hope that helps.

Aaron

|||

Aaron

this is the code for adding a product, what and where would i place to retrieve the returned value from the SPROC, insert is marked at the bottom with HERE IS THE INSERT

Thanks for the help, changed datasourcemode as above

Steve

ProtectedSub Button1_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles Button1.Click

If Trim(FCKeditor1.Value) =""Then

Dim AlertMSG ="Please enter product description"

ShrekGlobal.GlobalFunctions.CreateMessageAlert(Me, AlertMSG,"strKey1")

ExitSub

EndIf

Dim UploadImageAsBoolean =True

dsProduct.InsertParameters.Clear()

Dim ShopIDAsNew ControlParameter("ShopID","hfShopID","Value")

dsProduct.InsertParameters.Add(ShopID)

Dim CategoryIDAsNew ControlParameter("CategoryID","ddlCategories","SelectedValue")

dsProduct.InsertParameters.Add(CategoryID)

Dim ModelNumberAsNew ControlParameter("ModelNumber","txtModelNumber","Text")

dsProduct.InsertParameters.Add(ModelNumber)

Dim ModelNameAsNew ControlParameter("ModelName","txtModelName","Text")

dsProduct.InsertParameters.Add(ModelName)

Dim UnitCostAsNew ControlParameter("UnitCost","txtUnitCost","Text")

dsProduct.InsertParameters.Add(UnitCost)

Dim StockAsNew ControlParameter("Stock","txtStock","Text")

dsProduct.InsertParameters.Add(Stock)

Dim ProductDescriptionAsNew ControlParameter("ProductDescription","FCKeditor1","Value")

dsProduct.InsertParameters.Add(ProductDescription)

Dim ImageFilenameAsString

If ddlMainImage.SelectedIndex = 0Then

ImageFilename =""

Else

ImageFilename =String.Format("{0}.jpg", ddlMainImage.SelectedValue)

EndIf

dsProduct.InsertParameters.Add("ProductImage", ImageFilename)Dim ShippingIDUK1AsNew ControlParameter("ShippingID1","ddlUKShipping1","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDUK1)

Dim ShippingAmountUK1AsNew ControlParameter("ShippingAmount1","txtUKShipping1","Text")

dsProduct.InsertParameters.Add(ShippingAmountUK1)

Dim ShippingIDUK2AsNew ControlParameter("ShippingID2","ddlUKShipping2","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDUK2)

Dim ShippingAmountUK2AsNew ControlParameter("ShippingAmount2","txtUKShipping2","Text")

dsProduct.InsertParameters.Add(ShippingAmountUK2)

Dim ShippingIDUK3AsNew ControlParameter("ShippingID3","ddlUKShipping3","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDUK3)

Dim ShippingAmountUK3AsNew ControlParameter("ShippingAmount3","txtUKShipping3","Text")

dsProduct.InsertParameters.Add(ShippingAmountUK3)

Dim ShippingIDUK4AsNew ControlParameter("ShippingID4","ddlUKShipping4","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDUK4)

Dim ShippingAmountUK4AsNew ControlParameter("ShippingAmount4","txtUKShipping4","Text")

dsProduct.InsertParameters.Add(ShippingAmountUK4)

Dim ShippingIDUK5AsNew ControlParameter("ShippingID5","ddlUKShipping5","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDUK5)

Dim ShippingAmountUK5AsNew ControlParameter("ShippingAmount5","txtUKShipping5","Text")

dsProduct.InsertParameters.Add(ShippingAmountUK5)

Dim ShippingIDInt1AsNew ControlParameter("ShippingID6","ddlIntShipping1","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDInt1)

Dim ShippingAmountInt1AsNew ControlParameter("ShippingAmount6","txtIntShipping1","Text")

dsProduct.InsertParameters.Add(ShippingAmountInt1)

Dim ShippingIDInt2AsNew ControlParameter("ShippingID7","ddlIntShipping2","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDInt2)

Dim ShippingAmountInt2AsNew ControlParameter("ShippingAmount7","txtIntShipping2","Text")

dsProduct.InsertParameters.Add(ShippingAmountInt2)

Dim ShippingIDInt3AsNew ControlParameter("ShippingID8","ddlIntShipping3","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDInt3)

Dim ShippingAmountInt3AsNew ControlParameter("ShippingAmount8","txtIntShipping3","Text")

dsProduct.InsertParameters.Add(ShippingAmountInt3)

Dim ShippingIDInt4AsNew ControlParameter("ShippingID9","ddlIntShipping4","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDInt4)

Dim ShippingAmountInt4AsNew ControlParameter("ShippingAmount9","txtIntShipping4","Text")

dsProduct.InsertParameters.Add(ShippingAmountInt4)

Dim ShippingIDInt5AsNew ControlParameter("ShippingID10","ddlIntShipping5","SelectedValue")

dsProduct.InsertParameters.Add(ShippingIDInt5)

Dim ShippingAmountInt5AsNew ControlParameter("ShippingAmount10","txtIntShipping5","Text")

dsProduct.InsertParameters.Add(ShippingAmountInt5)

'Dim ProductImage As New ControlParameter("ProductImage", "siProductImage", "NewFileName")

'dsProduct.InsertParameters.Add(ProductImage)

Try

dsProduct.Insert() HERE IS THE INSERT

Dim xAsInteger

For x = 1To 8

'Dim SaveDirectory As String = String.Format("{0}\", Server.MapPath(TemporarySaveDirectory), x)

MsgBox(ddlCategories.SelectedValue)

Dim ProductIDAsInteger = 1

Dim SaveDirectoryAsString =String.Format("~\shopping_mall\images\shops\{0}\{1}\{2}\", Session("ShopID"), ddlCategories.SelectedValue, ProductID)

If ShrekGlobal.GlobalFunctions.FileMove(String.Format("{0}.jpg", x), Server.MapPath(TemporarySaveDirectory), Server.MapPath(SaveDirectory))Then

Beep()

Else

Beep()

EndIf

Next

'siProductImage.TemporarySaveDirectory = String.Format("~\shopping_mall\images\shops\{0}\{1}", hfShopID.Value, ddlCategories.SelectedValue)

'siProductImage.Upload()

ClearPage()

Catch exAs Exception

'UploadImage = False

DeleteImage(0)

MsgBox(String.Format("{0} ~ {1}","Error", ex.Message.ToString()))

'_lblInfo1.Text = sb.ToString

' Consider customizing the message for the EmailNotSentPanel in the ShowAds page.

'Beep()

'_lblInfo1.Text = Now + " :: " + sb.ToString

EndTry

'If UploadImage Then

'Dim TemporarySaveDirectory As String

'aveDirectory =

'End If

EndSub

|||

Try this article:http://aspnet.4guysfromrolla.com/articles/050207-1.aspx

Hope that helps.

Aaron

Programmatically configuring error and truncation dispositions for row redirection

Hi,

I have created a SSIS package programmatically using C#.

The package should do the following take data from source A, and place rows into destination B, if there are any error rows then redirect the rows to destination C. In my package I have the following components:

DTSAdapter.OLEDBSource.1 - Used as the Source

DTSAdapter.OLEDBDestination.1 - Used for the Destination Output - (let me call this normalOutput)

DTSAdapter.OLEDBDestination.1 - Used for the Destination Error Output - (let me call this errorOutput)

All my mappings appear to be correct, I build and save the package and receive a Successful validation and Success on Execution.

However, When I open the application using the Execute Package Utility I get the warning:

Warning:No rows will be sent to the error output(s). Configure error or truncation dispositions to redirect rows to the error output(s), or delete data flow transformations or destinations that are attached to the error output(s)

How do I get around this?

I have placed on the DTSAdapter.OLEDBDestination.1 (Used for the Destination Output), on the input collection I have placed:

normalOutput.InputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;

normalOutput.InputCollection[0].TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;

normalOutput.OutputCollection[0].ExclusionGroup = 1;

on the DTSAdapter.OLEDBDestination.1 (Used for the Destination Error Output) I have placed:

errorOutput.OutputCollection[0].ExclusionGroup = 1;

However this does not work, I just get the wanring displayed above.

I have also tried to set the

OutputCollection[0].SynchronousInputID for both the error output and the normal output to the same values

so that:

normalOutput.OutputCollection[0].SynchronousInputID = normalOutput.InputCollection[0].ID

errorOutput.OutputCollection[0].SynchronousInputID = normalOutput.InputCollection[0].ID

However, the above scenario does not pass the package validation, in the Execute Package Utiltity, I get the wanring mentioned above and also the error:

Error: The input "OLE DB Destination Input" (16) has an invalid error or truncation row disposition.

So my question is what are the correct configuration settings to have in this scenario?

Thanks

Just looking at your code, I see a couple things to suggest. Where you are setting the Error and Truncation dispositions to redirect, I think that needs to be done on every item in the OutputCollection and not on the InputCollection. I also think you should not be setting anything for the ExclusionGroup.

I suggest you mock up what you're trying to create manually in BIDS and look at the Advanced Editor for OLE DB to get an idea for what properties need to be set where.

Also, in the beginning of your message you listed the same component for the normal and error outputs. Was that a typo?
|||

Thanks JayH, I ended up setting the ErrorRowDisposition to redirect on the InputCollection[0] of the NormalDestination (OLE DB Destination) and then creating an error path from the NormalDestination to the ErrorDestination (OLE DB Destination) .

This works for errors such as trying to place a varchar(20) column into a float column, but for some reason you are not allowed to set the TruncationRowDisposition to redirect at this level.

So, I went ahead a created a package on the VS IDE and set the TruncationRow dispositions to redirect on every column in the Source (OLE DB Source), and made a path from the error output to a ErrorDestination (OLE DB Destination).

and then... nothing...no redirection, when running the package using the Execute Package Utility, I see the warnings that a truncation will occur, but it just goes ahead and truncates the data in the column and placing the row into the NormalDestination and I get no rows redirecting to my ErrorDestination.

Is there something I am missing? Should be using a transformation component?

|||I think I'm missing the bigger picture. I'm not sure how a transformation component could help you. I'm imagining a single source OLE DB and two destination OLE DBs, one "normal" and the other "error".

You are correct that you can't set truncation disposition on an OLE DB destination. You can set an error disposition to redirect, but only if you're not fastloading. If there is an error when fastloading, the entire load will fail.

Your pipeline metadata should have the correct column definitions, and they should match your destination. Thus you should have no truncations or type mismatches detected at the destination. The place to detect truncations is on the source when the data is read and put into the pipeline. The only errors that should get detected at the destination are constraint violations.

If you'd like to send me code, my email is jay underscore hackney at hotmail dot com.
|||

No your spot on that is the final setup I had on Friday.

Point to note: I currently have my MaxInsertCommitSize set to 1000 which allows the fast loading.

Now back to the matter at hand, so, my package now has all the output columns on the source have both the ErrorRowDispositions and TruncationRowDispositions set to redirect and I've removed the ErrorDisposition from the "nomal" destination.

Now when I run the package I receive a truncation error, it should then perform the redirect but no rows are inserted and I get the following error message:

Error: There was an error with input column "City" (109) on input "OLE DB Destination Input" (16). The column status returned was "Text was truncated or one or more characters had no match in the target code page"

Let me break down the City column:

On the OLE DB Source is varchar(50)

On the "normal" destination is varchar(20)

On the "error" destination is varchar(MAX)

I want the truncation error to occur and redirect into the "error" destination where it should be inserted without any issues.

|||Regarding the MaximumInsertCommitSize, this does not enable any type of error redirection when fastloading. All it means is that instead of your whole load failing due to an error, only that 1,000 row chunk containing the error fails, and the rest of the load is considered successful.

Everything you're describing sounds correct to me. Have you saved the programmatically generated package and viewed it in the IDE to verify that everything was created correctly? Have you set the IsErrorOut on your error output?

|||Sorry. Have to correct myself. The failed chunks do get redirected.
|||

Thanks for the information on the fastloading, for the time being I have switched it off...

Regarding the the IsErrorOut field on the error output that is... (source.OutputCollection[1].IsErrorOut), it is already set to true by the framework.

Yes, the package is saved and i've looked at it in the IDE to check it and everything is fine:

However on executing the package I am left with:

Error: An OLE DB error has occurred. Error code 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Descriptioni "Invalid character value for cast specification" Information: The final commit for the data insertion has ended.

Error: There was an error with input column "City" (108) on input "OLE DB Destination Input" (29). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

Error: the "input "OLE DB Destination Input" (29)" failed because error code 0xC0209078 occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component.

The strange thing is the "OLE DB Destination Input" the error refers to in the last line belongs to the "source" component. However, this should be left to "Fail component", because the redirections have been set on the output items.

Looking at the External and Input columns, on the error destination the DataTypes for the External columns are DT_TEXT and on the Input columns they match the type and length of the "normal" Destination OutputColumns which seems correct to me.

I'm at a loss....

Can you advise of any good books on programming SSIS (and handling error outputs!) using C#?

|||

Ok, I remade the new package with the VS IDE, with only one mapping on the City column.

I map the "source" city (varchar(50)) to "destination" city (varchar(5))

I map the "source" city (Error output - varchar(50)) to "error destination" city (varchar(MAX))

I set the the errorRow and truncationRow dispositions on the "source" on the city column to RedirectRow.

I then run the package and get the warning that a truncation could occur on the column city, but the package just runs through and places all the rows into the destination table, no rows are redirected - (Note that there are is data in the source which should be redirected).

Looking at the "destination" all the truncations have gone ahead leaving me with 5 character strings in the City column.

Surely I must be doing something wrong here?

|||No books that I know of. Darren probably has the most experience doing this type of stuff, but he apparently hasn't seen fit to comment.

Are you controlling your source data for the testing? Do you know that there aren't some invalid characters causing this error?

I think the best way to determine what your code should be doing is by comparing it to the XML of a package created in the IDE. You may also need to simplify your package so you can isolate components until they are working correctly. Maybe you should consider just using dead end components like Unions instead of OLE DB destinations for your normal and error outputs.

These are the disposition-related settings I think you should be using for your Source component. Are you setting the UsesDispositions on your source component?

OLE DB Source Component
UsesDispositions=True

NormalOutput
IsErrorOut=False
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

Normal OutputColumn
ErrorOrTruncationOperation="Conversion"
ErrorRowDisposition=RD_RedirectRow
TruncationRowDisposition=RD_RedirectRow

ErrorOutput
IsErrorOut=True
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

Error OutputColumn
ErrorOrTruncationOperation=""
ErrorRowDisposition=RD_NotUsed
TruncationRowDisposition=RD_NotUsed

|||

Yep, I already had a windiff moment with it to find any differences between a IDE made package and a dynamically created one, thats how I've ironed out initial issues.

I've also used the example shown here:

http://blogs.conchango.com/jamiethomson/archive/2005/08/08/1969.aspx

Setting up the whole package in the IDE and using my database tables instead, mapping just the city column and no other columns, and all the happens is I get a warning about the imminent truncation and the truncation goes ahead. So I get nothing in my error destination.

This is getting me extremely fustrated, is there anyone who has an example of setting up a simple TruncationRow redirect?

Jamie Thompson could you knock one up?

|||I think you just said that if you create a package in the IDE (using your tables), that you can't get the redirection to work there either? That's the second indication I've heard that this may be a different problem.

Let's try to establish a baseline of functionality by creating a simple package in the IDE. If you have AdventureWorks installed, try it with that database first to take your data out of the equation. If not then just use your own table.
OLE DB Source in table mode that loads the Person.Address table from the AdventureWorks database. On the columns tab, select only the City column (it is an nvarchar(30))
On the Error Output tab, set the error and truncation dispostions to redirect row Close the Source, right-click it, and select "Show Advanced Editor" On the "Input and Output Properties" tab, open "OLE DB Source Output", open "Output Columns" Select the City column and change the Length property to 10 and click OK. The Source will now warn about truncation.
Drop two Union All components on the data flow surface, connect one to the normal (green) output from the source, and the other to the error (red) output.|||

Mr JayH you are a godsend.

From reading your points the problem with the my package was made clear in the line:

Select the City column and change the Length property to 10 and click OK. The Source will now warn about truncation.
|||

Just incase anyone else runs into this problem here is my final mappings code for the "Source".

private void CreateMappings()

{

// Map OutColumns to there external metadata columns for my mappings

foreach (SSISMapping mapping in dtsMappings)

{

IDTSOutputColumn90 outputColumn = component.OutputCollection[0].OutputColumnCollection[mapping.ToColumn.Name];

IDTSExternalMetadataColumn90 exMetaDataColumn = component.OutputCollection[0].ExternalMetadataColumnCollection[mapping.FromColumn.Name];

componentInstance.MapOutputColumn(component.OutputCollection[0].ID, outputColumn.ID, exMetaDataColumn.ID, true);

outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;

outputColumn.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;

// Note that this must come AFTER the mapping because otherwise the properties will

//be mapped to the ExternalMetaColumn's properties

outputColumn.SetDataTypeProperties(SSISUtilities.GetDataType(mapping.ToColumn.DataType),

mapping.ToColumn.Length,

mapping.ToColumn.Precision,

mapping.ToColumn.Scale,

SSISUtilities.DEFAULTCODEPAGE);

}

}

Note that SSISUtilities and SSISMapping are not part of the framework.

Programmatically Access The ExecuteProcessTask

Hi all,

I am trying to programmatically create an Execute Process Task in an SSIS package.

So far, I have the following:

Private package As Package
Dim th As TaskHost = TryCast(package.Executables.Add("STOCK:ExecuteProcessTask"), TaskHost)
th.Name = "Execute Process Task"
th.Description = "Execute Process Task"

That will get me the ExecuteProcessTask in my package that I want. But now, I would like to set the properties of it (i.e., the executable and arguments)

Basically, my IDE does not have any idea what an "ExecuteProcessTask" is. After lots of research, I cannot find out which assembly I need to reference in order to gain access to this object. In addition, whenever I try to Import the assembly that I think it is (i.e. Microsoft.SqlServer.ExecProcTask, or Microsoft.SqlServer.Dts.Tasks.ExecuteProcess), none of them work. I can see in my Assembly Cache that the .dll is registered correctly ...

What is going on?
What do you mean "none of them work"? When you set a reference to the Microsoft.SqlServer.ExecProcTask.dll then the code below should work.

Code Snippet

Dim execProc As Microsoft.SqlServer.Dts.Tasks.ExecuteProcess.ExecuteProcess = CType(th.InnerObject, Microsoft.SqlServer.Dts.Tasks.ExecuteProcess.ExecuteProcess)
execProc.Executable = "executable.exe"
execProc.Arguments = "/arguments"

|||Really, my problem is that I can't access the assembly that I need.

Basically, I need access to the Microsoft.SqlServer.Dts.Tasks.ExecProcTask assembly-

But, when I try to add a reference to it, I do not see it. In addition, with the interface in SSIS, I cannot "browse" for the assembly.

The only assembly that shows up in Intellisense is Microsoft.SqlServer.Dts.Tasks.ScriptTask

I am editing a script task- so what good is a script task if I can't access the assemblies I need?

What can I do?

|||Ah, you need to copy it from C:\Program Files\Microsoft SQL Server\90\DTS\Tasks to C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727. Then it will show up in your References.
|||Graahhhhh!! I just came up with that idea, tried it out and it worked- graahh-- wasted so much time and was so frustrated by that yesterday because of something so simple- I hate that!

Programmatic Render of Reports into PDF format

Hi,
Can anybody please convert the following C# code into VB.Net Code.I tried
but its saying Object Reference not set to an instance of an object.
Or give me VB.NET(Web Application, CodeBehind:VB.Net) code to
programmatically render reports into PDF format.
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[1];
parameters[0] = new ParameterValue();
parameters[0].Name = "Parameter1";
parameters[0].Value = Request.QueryString["id"];
Thanks
Rajesh YDim Parameters(1) As New ParameterValue
Parameters(0) = New ParameterValue
Parameters(0).Name = "Parameter1"
Parameters(0).Value = Request.QueryString("id")
"Rajesh Yennam" wrote:
> Hi,
> Can anybody please convert the following C# code into VB.Net Code.I tried
> but its saying Object Reference not set to an instance of an object.
> Or give me VB.NET(Web Application, CodeBehind:VB.Net) code to
> programmatically render reports into PDF format.
> // Prepare report parameter.
> ParameterValue[] parameters = new ParameterValue[1];
> parameters[0] = new ParameterValue();
> parameters[0].Name = "Parameter1";
> parameters[0].Value = Request.QueryString["id"];
> Thanks
> Rajesh Y|||Hi Rajesh:
I'd suspect the line of code :
parameters[0].Value = Request.QueryString["id"];
I'd guess you do not have an id parameter in the query string. Double
check the query string.
HTH,
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Fri, 29 Oct 2004 03:43:01 -0700, "Rajesh Yennam"
<RajeshYennam@.discussions.microsoft.com> wrote:
>Hi,
>Can anybody please convert the following C# code into VB.Net Code.I tried
>but its saying Object Reference not set to an instance of an object.
>Or give me VB.NET(Web Application, CodeBehind:VB.Net) code to
>programmatically render reports into PDF format.
>// Prepare report parameter.
> ParameterValue[] parameters = new ParameterValue[1];
> parameters[0] = new ParameterValue();
> parameters[0].Name = "Parameter1";
> parameters[0].Value = Request.QueryString["id"];
>Thanks
>Rajesh Y

programatically setting reportViewer datasource

I'm trying to set the DataSource of a ReportViewer programmatically. I'm doing the following but I'm getting the error

"A data source instance has not been supplied for the data source 'DataSet1_ORG'". DataSet1_Org is the xml datasource that the .rdlc is bound to.With ReportViewer1 Dim rdsAsNew ReportDataSource("rdsReport","sqlReport") 'sqlReport is a SQLDataSource on the webform

.LocalReport.DataSources.Clear()

.LocalReport.DataSources.Add(rds)

.LocalReport.ReportPath = Server.MapPath("MyReport.rdlc")

End With

I know I'm close...it's just a bit elusive.

Thanks, Alex

What you can try is go to the Report menu, then to the Data Sources option, remove what is there and add the appropriate data source again.

|||

Hello

you need to click on the reportviewer and select the choose data source and select the datasource that u want

Programatically create a script

I need some help with the following issue. I need vb.net code that will create a T-SQL script. For example given the orders table in northwind I would get the followingwith indexes, RI, ect. :
CREATE TABLE [dbo].[Orders] (
[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EmployeeID] [int] NULL ,
[OrderDate] [datetime] NULL ,
[RequiredDate] [datetime] NULL ,
[ShippedDate] [datetime] NULL ,
[ShipVia] [int] NULL ,
[Freight] [money] NULL ,
[ShipName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipAddress] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCity] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipRegion] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipPostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ShipCountry] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
(
[OrderID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] WITH NOCHECK ADD
CONSTRAINT [DF_Orders_Freight] DEFAULT (0) FOR [Freight]
GO

CREATE INDEX [CustomerID] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

CREATE INDEX [CustomersOrders] ON [dbo].[Orders]([CustomerID]) ON [PRIMARY]
GO

CREATE INDEX [EmployeeID] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

CREATE INDEX [EmployeesOrders] ON [dbo].[Orders]([EmployeeID]) ON [PRIMARY]
GO

CREATE INDEX [OrderDate] ON [dbo].[Orders]([OrderDate]) ON [PRIMARY]
GO

CREATE INDEX [ShippedDate] ON [dbo].[Orders]([ShippedDate]) ON [PRIMARY]
GO

CREATE INDEX [ShippersOrders] ON [dbo].[Orders]([ShipVia]) ON [PRIMARY]
GO

CREATE INDEX [ShipPostalCode] ON [dbo].[Orders]([ShipPostalCode]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Orders] ADD
CONSTRAINT [FK_Orders_Customers] FOREIGN KEY
(
[CustomerID]
) REFERENCES [dbo].[Customers] (
[CustomerID]
),
CONSTRAINT [FK_Orders_Employees] FOREIGN KEY
(
[EmployeeID]
) REFERENCES [dbo].[Employees] (
[EmployeeID]
),
CONSTRAINT [FK_Orders_Shippers] FOREIGN KEY
(
[ShipVia]
) REFERENCES [dbo].[Shippers] (
[ShipperID]
)
GO

It's not VB.NET code, it's a component, but here's something that does what you want:
http://thycotic.com/dotnet_dbscripter.html

programatic transferdatabasetask in ssis

I am trying to code a package that runs a transferdatabasetask with the following code

Dim package As New Package()

package.PackageType = DTSPackageType.DTSDesigner90

package.Name = "transfer db task"

package.Description = "transfer db task"

package.CreatorComputerName = System.Environment.MachineName

package.CreatorName = System.Environment.UserName

Dim dest As ConnectionManager = package.Connections.Add("OLEDB")

dest.Name = "Dest"

dest.ConnectionString = "Data Source=NSW97V9F1S\NSW97V9F1S;Initial Catalog=RGTemp;User Id=rgTest;Password=12345"

'dest.ConnectionString = "SqlServerName=PDNCNLNJ1S\SQLSERVER2005;UseWindowsAuthentication=True;UserName=sa;"

Dim source As ConnectionManager = package.Connections.Add("OLEDB")

source.Name = "Source"

source.ConnectionString = "Data Source=NSW97V9F1S\NSW97V9F1S;Initial Catalog=RGTemp;User Id=rgTest;Password=12345"

'source.ConnectionString = "SqlServerName=NSW97V9F1S\NSW97V9F1S;UseWindowsAuthentication=True;UserName=;"

Dim th As TaskHost = TryCast(package.Executables.Add("STOCK:TransferDatabaseTask"), TaskHost)

th.Name = "transfer db task"

th.Description = "The transfer task"

th.Properties("Action").SetValue(th, 0) '0: copy

th.Properties("Method").SetValue(th, 1) '1: destination online

th.Properties("DestinationConnection").SetValue(th, dest.ID)

th.Properties("DestinationDatabaseFiles").SetValue(th, "rgTemp1.mdf,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA,'';rgTemp1_log.ldf,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA,''")

'th.Properties("DestinationDatabaseFiles").SetValue(th, "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\rgTemp1.mdf;C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\rgTemp1_log.ldf")

th.Properties("DestinationDatabaseName").SetValue(th, "RGTemp1")

th.Properties("DestinationOverwrite").SetValue(th, True)

th.Properties("ReattachSourceDatabase").SetValue(th, False)

th.Properties("SourceConnection").SetValue(th, source.ID)

th.Properties("SourceDatabaseFiles").SetValue(th, "'rgTemp.mdf','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA','';'rgTemp_log.ldf','C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA',''")

MsgBox(th.Properties("SourceDatabaseFiles").GetValue(th).ToString)

MsgBox(th.Properties("DestinationDatabaseFiles").GetValue(th).ToString)

th.Properties("SourceDatabaseName").SetValue(th, "RGTemp")

Dim status As DTSExecResult = package.Validate(Nothing, Nothing, Nothing, Nothing)

' If the package validated successfully, then execute it.

If status = DTSExecResult.Success Then

' Execute the package

Dim result As DTSExecResult = package.Execute(Nothing, Nothing, Nothing, Nothing, Nothing)

End If

'Dts.TaskResult = Dts.Results.Success

End Sub

The problem is that the package validation fails and the 'execute' statement never runs. the two message boxes report zero length strings in the source and destination files properties. It seems that the hard coded filenames that I have provided are not correct.

Can anyone shed any light on what is wrong here:

regards

Ray

What is the error message on validation?

When you add connection manager, the type should be "SMOServer" instead of "OLEDB". So, use something like:
package.Connections.Add("SMOServer")

Setting the properties is easier if you get the InnerObject from task host as in:
TransferDatabaseTask task = (TransferDatabaseTask) th.InnerObject;

For source and destination connections, you should set the name of the connection manager instead of ID. (I am not sure if using the ID is correct)

You have to escape '\' and " in the values for source and destination database file as
"\"rgTemp.mdf\",\"C:\\Program

Files\\Microsoft SQL

Server\\MSSQL.1\\MSSQL\\DATA\",\"\";"\"rgTemp.ldf\",\"C:\\Program

Files\\Microsoft SQL

Server\\MSSQL.1\\MSSQL\\DATA\",\"\";|||

Thanks for the reply

I tried your suggestions and got as follows

Using SMOServer invalidated the connection strings and I could only set them in a format that doesn't allow SQL Server login or a password

I escaped the file names in VB as double double quotes and that fixed the source and destination file name assignments

But the routine still returns 'failed' from the 'validate' call. there is no error message the 'validate' routine just returns 'failed' with no exception or message that I can find.

if i comment out the validate call the 'execute' call just returns failed with no exception.

I am not getting any help from the system here.

we have made a step forward here thanks to you but I am still stuck

Do you have any other ideas?

regards

Ray

|||To create connection manager you can use the following code:
connectionString = String.Format("SqlServerName={0};UseWindowsAuthentication=true;", serverName);
//connectionString = String.Format("SqlServerName={0};UseWindowsAuthentication=false;UserName={1};Password={2}",serverName, userName, passwd);

ConnectionManager connectionManager = package.Connections.Add("SMOServer");
connectionManager.ConnectionString = connectionString;
connectionManager.Name = connectionManagerName;