problem with srever 2005 and Oracle

I am creating report model witch have connection to Oracle database. I was
created the connection and data source view, but then I want to create
Report model with wizard I am geting the error: ORA-02179: valid options:
ISOLATION LEVEL {SERIAZABLE | READ COMMITTED}.
how to change isolation level to valid oracle connection isolation level.It is my understanding that the model part of RS in 2005 is not yet
compatible with Oracle. I am tied to find where I read that on technet
but I can't. Maybe someone else can elaborate.
Thanks!|||Work-around for Report Builder:
1. Create linked server connection to Oracle database using the
OraOLEDB.Oracle provider(more up-to-date than Microsoft's).
2. Create a Data Source using the native SQL provider to the SQL Server
where you created in step 1.
3. Create a data source view; do not select objects.
4. Right-click in the DSV designer pane and create a New Named Query. Build
your query against the linked server (i.e. use 4-part names: select * from
linkedservername..schema.object). Repeat step 4 for each object you wish to
add to your model.
5. Add logical keys where applicable.
6. Build your model.
7. Deploy & build reports using Report Builder :-)
X

Problem with SQL Update when a field is NULL

I am having a problem with an update statement. I am using compareallvalues with a SQL data source. When one of the old values is null the update does not go through.

i.e. This does not work if something is NULL

UPDATE myTable SET something = @.something
WHERE ID = @.o_ID AND something = @.o_something

Thank You,
Jason

The reason is ANSI SQL NULL is an unknown so comparing NULL to NULL is not NULL but an unknown. Try the link below for work around. Hope this helps.

http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls_printversion.asp

|||Will there be a huge performance impact if I use Coalesce() 144 times in a statement?|||

No I think you should try SET ANSI_NULLS OFF instead. Try the link below for details from Microsoft. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_9rec.asp

|||It still does not work when I use set ANSI_NULLS OFF|||

I have two option there is a new tool that gives you intelisense in SQL Server Query Tools called SQL Prompt see if it can give you some idea of how to get the info. The other is a link with long code using COALESCE and ISNULL. Hope this helps.

http://www.red-gate.com/products/SQL_Prompt/index.htm?utm_source=sscentral&utm_medium=banner&utm_campaign=sqlprompt

http://weblogs.sqlteam.com/mladenp/articles/2937.aspx

Problem with source from Books Online - Application.LoadPackage

I'm trying to execute a package in the file system from an c# assembly. In BOL there is the following sample code available:

ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/dtsref9mref/html/M_Microsoft_SqlServer_Dts_Runtime_Application_LoadPackage_1_cfc592c3.htm

Application.LoadPackage Method (String, IDTSEvents)

class ApplicationTests
{
static void Main(string[] args)
{
// The variable pkg points to the location of the
// ExecuteProcess package sample installed with
// the SSIS samples.
string pkg = @."C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\ExecuteProcess Sample\ExecuteProcess\UsingExecuteProcess.dtsx";

Application app = new Application();
Package p = app.LoadPackage(pkg, null);
// Now that the package is loaded, we can query on
// its properties.
int n = p.Configurations.Count;
DtsProperty p2 = p.Properties["VersionGUID"];
DTSProtectionLevel pl = p.ProtectionLevel;

Console.WriteLine("Number of configurations = " + n);
Console.WriteLine("VersionGUID = " + p2);
Console.WriteLine("ProtectionLevel = " + pl);
}
}

I added the reference to "Microsoft.SQLServer.DTSRuntimeWrap" and declared it by

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

No I'm not able to compile because app.LoadPackage(pkg, null); needs an additional IDTSEvents90. After changing the row to

Package p = app.LoadPackage(pkg, true, null);

I'm getting the following errors:

Error 1 Cannot implicitly convert type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackage90' to 'Microsoft.SqlServer.Dts.Runtime.Wrapper.Package'. An explicit conversion exists (are you missing a cast?)

Error 2 The type or namespace name 'DtsProperty' could not be found (are you missing a using directive or an assembly reference?)

Error 3 'Microsoft.SqlServer.Dts.Runtime.Wrapper.Package' does not contain a definition for 'Properties'

Can someone please give me an example how to start the Package now in the latest code syntax and set the values of a variable from outside?

I am using the following versions

Microsoft Visual Studio 2005
Version 8.0.50727.26 (RTM.050727-2600)
Microsoft .NET Framework
Version 2.0.50727

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.1314.00

Best regards,
Dirk

Add a reference to Microsoft.SqlServer.ManagedDts.dll and use namespace Microsoft.SqlServer.Dts.Runtime (without .Wrapper).

.Wrapper assembly and namespace contain COM interop declarations - i.e. if you want to work with unmanaged object model directly. It is usually more convinient to work with managed object model built on top of it.sql

problem with retreving a excel data through excel source component.

Hello,

I have a problem with retreving a excel data through excel source component.

I have source component as Excel Source which will connect to my .xls sheet.

To retrieve the values from the sheet i am using a query as,

"SELECT F14,F3 FROM [Charac Defn & Assgnment$]"

The column F14 is not formatted so that the format of the cell is "General" I have a different type of values in the F14 column such as "PE","PES",15,20,20.00,8888.9999 etc..

While i click on preview button of Excel source it shows only the text values and not the int or decimal values, its returning NULL for those cells. I tried to use convert function, its throwing an error as

TITLE: Microsoft Visual Studio

There was an error displaying the preview.

ADDITIONAL INFORMATION:

Undefined function 'Convert' in expression. (Microsoft JET Database Engine)

Is there any other function to change the format of the cell or i need to some thing else

Please help me how to solve this issue.

Why don't you try formatting the cell as text in excel and then change the data type if needed in the data flow.

|||

Or try to save the file as csv file, then use flat file source to import the csv file into your ssis package.

|||

Dhivya.S wrote:

Hello,

I have a problem with retreving a excel data through excel source component.

I have source component as Excel Source which will connect to my .xls sheet.

To retrieve the values from the sheet i am using a query as,

"SELECT F14,F3 FROM [Charac Defn & Assgnment$]"

The column F14 is not formatted so that the format of the cell is "General" I have a different type of values in the F14 column such as "PE","PES",15,20,20.00,8888.9999 etc..

While i click on preview button of Excel source it shows only the text values and not the int or decimal values, its returning NULL for those cells. I tried to use convert function, its throwing an error as

TITLE: Microsoft Visual Studio

There was an error displaying the preview.

ADDITIONAL INFORMATION:

Undefined function 'Convert' in expression. (Microsoft JET Database Engine)

Is there any other function to change the format of the cell or i need to some thing else

Please help me how to solve this issue.

Hi Dhivya,

SSIS considers the majority type among your values. Since you must be having more number of string data in the column F14, it is considering it as a varchar column and thus ignoring the numeric data.

Do you really want the data to be uploaded into the destination table like this?

In this case, a single quotation (') needs to be prefixed to each numeric value so that it is considered as a string.

Please do that and it should work now.

Regards,

B@.ns

Problem with Report Model.

Hi,

I am using report model as my data source and when I preview my report, I got the following error message:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create command for data source 'xxxx'. (rsErrorCreatingCommand)
An attempt has been made to use a data extension 'SQL' that is not registered for this report server. (rsSemanticQueryExtensionNotFound)

Anyone has any idea how to fix it?

Many Thanks,

UT

A couple questions:

What Version & Edition of SQL Server do you have installed?
Do you see this same error when you publish and render the report from Report Manager?
Is the Data Source local to your Report Server?

- Jason Tremper

|||

Hi Jason,

Thx for your reply and I am sorry for not replying earlier.

I am using SQL 2005 with the SP1 + hotfix.

Yes, same error message occur when I view it from Report Manager.

The data source is local to Report Server

Thanks,

UT

|||I have the same problem. Have you found a solution ?|||The problem solved by SP2.

Thanks and Regards,
UT

Problem with Report Model.

Hi,

I am using report model as my data source and when I preview my report, I got the following error message:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create command for data source 'xxxx'. (rsErrorCreatingCommand)
An attempt has been made to use a data extension 'SQL' that is not registered for this report server. (rsSemanticQueryExtensionNotFound)

Anyone has any idea how to fix it?

Many Thanks,

UT

A couple questions:

What Version & Edition of SQL Server do you have installed?
Do you see this same error when you publish and render the report from Report Manager?
Is the Data Source local to your Report Server?

- Jason Tremper

|||

Hi Jason,

Thx for your reply and I am sorry for not replying earlier.

I am using SQL 2005 with the SP1 + hotfix.

Yes, same error message occur when I view it from Report Manager.

The data source is local to Report Server

Thanks,

UT

|||I have the same problem. Have you found a solution ?|||The problem solved by SP2.

Thanks and Regards,
UT

Problem with report DataSource

Hello,
I am deploying reports via the web service. I create a folder, then create
a data source. Once I create a report, I make a reference to the data source
created and change the reports invalid data source to the reference.
When I did this with a data source with credentials stored on the report
server, it worked with no problems. When I changed this to prompt for
crendentials, accessing the report via web services cannot find the data
source associated with the report (the one defined in the rdl does not exist
in the folder fyi). Is this because a reference has no name in the database?
It does work via the Report Manager.
Here are some code snippets:
this.ReportService.CreateReport(reportType.ToString(), "/" + dbName, true,
definition, null);
//change its datasource to the new one
DataSourceReference reference = new DataSourceReference();
reference.Reference = "/" + dbName + "/" + dbName;
DataSource[] dataSources = this.ReportService.GetReportDataSources("/" +
dbName + "/" + reportType.ToString() );
for(int i=0;i<dataSources.Length;i++)
{
if(dataSources[i].Item is InvalidDataSourceReference)
{
dataSources[i].Item = reference;
}
}
this.ReportService.SetReportDataSources("/" + dbName + "/" +
reportType.ToString() ,dataSources);
and for credentials:
DataSourceCredentials d = new DataSourceCredentials();
d.DataSourceName = dbName;
d.UserName = userName;
d.Password = password;
this.mDataSourceCredentials = new DataSourceCredentials[]{d};
any help would be greatly appreciated.It seems recently that my posts never get replied to - Im sure others must
share the same frustration. The solution I came up with was to read the .rdl
file in as xml, search for all data source nodes and replace the old name
(attribute) with the new name. I also created the data source as a data
source, not as a reference.
"comet61" wrote:
> Hello,
> I am deploying reports via the web service. I create a folder, then create
> a data source. Once I create a report, I make a reference to the data source
> created and change the reports invalid data source to the reference.
> When I did this with a data source with credentials stored on the report
> server, it worked with no problems. When I changed this to prompt for
> crendentials, accessing the report via web services cannot find the data
> source associated with the report (the one defined in the rdl does not exist
> in the folder fyi). Is this because a reference has no name in the database?
> It does work via the Report Manager.
> Here are some code snippets:
> this.ReportService.CreateReport(reportType.ToString(), "/" + dbName, true,
> definition, null);
> //change its datasource to the new one
> DataSourceReference reference = new DataSourceReference();
> reference.Reference = "/" + dbName + "/" + dbName;
> DataSource[] dataSources = this.ReportService.GetReportDataSources("/" +
> dbName + "/" + reportType.ToString() );
> for(int i=0;i<dataSources.Length;i++)
> {
> if(dataSources[i].Item is InvalidDataSourceReference)
> {
> dataSources[i].Item = reference;
> }
> }
> this.ReportService.SetReportDataSources("/" + dbName + "/" +
> reportType.ToString() ,dataSources);
> and for credentials:
> DataSourceCredentials d = new DataSourceCredentials();
> d.DataSourceName = dbName;
> d.UserName = userName;
> d.Password = password;
> this.mDataSourceCredentials = new DataSourceCredentials[]{d};
> any help would be greatly appreciated.
>