ActiveX Script Task] Error:

Hi,
I'm trying to reset the below Global varialbe in SSIS - Activex script.

Function Main()
DTSGlobalVariables("GxvFilename").value =null
Main = DTSTaskExecResult_Success
End Function

Getting the below Error when executing the package.

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x060339E4

Please provide me solution for this.

Thanks,

You can read and write global variables from ActiveX Script, but I have to ask why bother when you have the much more powerfull VB.net in the Script Task.

I don't belive null is a valid keyword in VBScript. Nothing is more usual, and use a Set statement. Saying all that I tried and got the same error. Unless anyone can come up with something better I'd say this is a backward compatability issue, and you woudl be better off moving to the Script Task.

|||

Thanks Darren,

The Activex Script is not working as like SQL 2000. We nee to use " " instead of null for resting the global variables.
MS need to give some reasonable error message for this kind of error.

Anyway I made it work in Activex Script iteself.

ActiveX Script Task] Error:

Hi,
I'm trying to reset the below Global varialbe in SSIS - Activex script.

Function Main()
DTSGlobalVariables("GxvFilename").value =null
Main = DTSTaskExecResult_Success
End Function

Getting the below Error when executing the package.

[ActiveX Script Task] Error: Retrieving the file name for a component failed with error code 0x060339E4

Please provide me solution for this.

Thanks,

You can read and write global variables from ActiveX Script, but I have to ask why bother when you have the much more powerfull VB.net in the Script Task.

I don't belive null is a valid keyword in VBScript. Nothing is more usual, and use a Set statement. Saying all that I tried and got the same error. Unless anyone can come up with something better I'd say this is a backward compatability issue, and you woudl be better off moving to the Script Task.

|||

Thanks Darren,

The Activex Script is not working as like SQL 2000. We nee to use " " instead of null for resting the global variables.
MS need to give some reasonable error message for this kind of error.

Anyway I made it work in Activex Script iteself.

acronym (sql server backup problem)

i try to backup database (e.g., myDataBase) via the sql server 2000
wizard. (i use default value when process to do the backup, except
change to save database to disk)
and in anther machine i try to restore it. however, it occurrs that
the user would lose its login name that associates with sql server
2000 in another machine.(even though i create one the same as that in
backup machine) therefore, i have to use command like 'select * from
owner.table_name' to access tables in database. how to prevent this
happened when performing databasebackup procedure? or any ways to
correct this after the database restored in another machine?
i appreciate any suggestions, sincerely.
arsene
Maybe this can help.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Ana
"jason" <freeonfair@.yahoo.co.uk> wrote in message
news:ce06a859.0405130128.51c8f272@.posting.google.c om...
> i try to backup database (e.g., myDataBase) via the sql server 2000
> wizard. (i use default value when process to do the backup, except
> change to save database to disk)
> and in anther machine i try to restore it. however, it occurrs that
> the user would lose its login name that associates with sql server
> 2000 in another machine.(even though i create one the same as that in
> backup machine) therefore, i have to use command like 'select * from
> owner.table_name' to access tables in database. how to prevent this
> happened when performing databasebackup procedure? or any ways to
> correct this after the database restored in another machine?
> i appreciate any suggestions, sincerely.
> arsene

acronym (sql server backup problem)

i try to backup database (e.g., myDataBase) via the sql server 2000
wizard. (i use default value when process to do the backup, except
change to save database to disk)
and in anther machine i try to restore it. however, it occurrs that
the user would lose its login name that associates with sql server
2000 in another machine.(even though i create one the same as that in
backup machine) therefore, i have to use command like 'select * from
owner.table_name' to access tables in database. how to prevent this
happened when performing databasebackup procedure? or any ways to
correct this after the database restored in another machine?
i appreciate any suggestions, sincerely.
arseneMaybe this can help.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Ana
"jason" <freeonfair@.yahoo.co.uk> wrote in message
news:ce06a859.0405130128.51c8f272@.posting.google.com...
> i try to backup database (e.g., myDataBase) via the sql server 2000
> wizard. (i use default value when process to do the backup, except
> change to save database to disk)
> and in anther machine i try to restore it. however, it occurrs that
> the user would lose its login name that associates with sql server
> 2000 in another machine.(even though i create one the same as that in
> backup machine) therefore, i have to use command like 'select * from
> owner.table_name' to access tables in database. how to prevent this
> happened when performing databasebackup procedure? or any ways to
> correct this after the database restored in another machine?
> i appreciate any suggestions, sincerely.
> arsene

acronym (sql server backup problem)

i try to backup database (e.g., myDataBase) via the sql server 2000
wizard. (i use default value when process to do the backup, except
change to save database to disk)
and in anther machine i try to restore it. however, it occurrs that
the user would lose its login name that associates with sql server
2000 in another machine.(even though i create one the same as that in
backup machine) therefore, i have to use command like 'select * from
owner.table_name' to access tables in database. how to prevent this
happened when performing databasebackup procedure? or any ways to
correct this after the database restored in another machine?
i appreciate any suggestions, sincerely.
arseneMaybe this can help.
HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/default.aspx?kbid=314546
Ana
"jason" <freeonfair@.yahoo.co.uk> wrote in message
news:ce06a859.0405130128.51c8f272@.posting.google.com...
> i try to backup database (e.g., myDataBase) via the sql server 2000
> wizard. (i use default value when process to do the backup, except
> change to save database to disk)
> and in anther machine i try to restore it. however, it occurrs that
> the user would lose its login name that associates with sql server
> 2000 in another machine.(even though i create one the same as that in
> backup machine) therefore, i have to use command like 'select * from
> owner.table_name' to access tables in database. how to prevent this
> happened when performing databasebackup procedure? or any ways to
> correct this after the database restored in another machine?
> i appreciate any suggestions, sincerely.
> arsene

AcquireConnection returns null value

I have the following code in my custom source component's AcquireConnection method -

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;

if (cmado == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.");

// Get the underlying connection object.
this.oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

if (oledbConnection == null)

throw new Exception("The ConnectionManager is not an ADO.NET connection.");

isConnected = true;

}

The value of oledbConnection is null.

I am trying to invoke the above method within a package that I am trying to create dynamically. Any help ?

private const string ADONETConnectionString = "Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

ConnectionManager PubTRCM = package.Connections.Add("ADO.NET:OLEDB");

PubTRCM.Name = "PubTR";

PubTRCM.ConnectionString = String.Format(ADONETConnectionString, paramPubServer, paramPubTRDB);

#region Create OLEDB Source Component

//Create a OLEDB Source Component
IDTSComponentMetaData90 Source = dataFlow.ComponentMetaDataCollection.New();
Source.Name = "OLEDBSource";

Source.ComponentClassID = typeof(Microsoft.Samples.SqlServer.Dts.AdoSourceSample).AssemblyQualifiedName;

//Get the design time instance of the source
CManagedComponentWrapper SourceDesignTime = Source.Instantiate();

//Initialize the component
SourceDesignTime.ProvideComponentProperties();

if (Source.RuntimeConnectionCollection.Count > 0)
{
Source.RuntimeConnectionCollection[0].ConnectionManagerID = PubTRCM.ID;
Source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(PubTRCM);
}

SourceDesignTime.SetComponentProperty("SqlStatement", "Select PlantId from PlantDim");

//Reinitialize the metadata
SourceDesignTime.AcquireConnections(null);
SourceDesignTime.ReinitializeMetaData();
SourceDesignTime.ReleaseConnections();

#endregion

So what stage does the problem start? Is cmado an object? If so what type? What is the result from cmado.AcquireConnection, never mind the cast?

Have you tried debugging your component whilst it is being called like this?

Does the component work in the designer, I assume not.

|||

I could resolve this problem by changing the connection string from -

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

to

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;";

and adding the connection manager to the package as follows -

ConnectionManager CMgr = package.Connections.Add("ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");

Thanks,

Reni

AcquireConnection returns null value

I have the following code in my custom source component's AcquireConnection method -

if (ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager != null)

{

ConnectionManager cm = Microsoft.SqlServer.Dts.Runtime.DtsConvert.ToConnectionManager(ComponentMetaData.RuntimeConnectionCollection[0].ConnectionManager);

ConnectionManagerAdoNet cmado = cm.InnerObject as ConnectionManagerAdoNet;

if (cmado == null)

throw new Exception("The ConnectionManager " + cm.Name + " is not an ADO.NET connection.");

// Get the underlying connection object.
this.oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;

if (oledbConnection == null)

throw new Exception("The ConnectionManager is not an ADO.NET connection.");

isConnected = true;

}

The value of oledbConnection is null.

I am trying to invoke the above method within a package that I am trying to create dynamically. Any help ?

private const string ADONETConnectionString = "Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

ConnectionManager PubTRCM = package.Connections.Add("ADO.NET:OLEDB");

PubTRCM.Name = "PubTR";

PubTRCM.ConnectionString = String.Format(ADONETConnectionString, paramPubServer, paramPubTRDB);

#region Create OLEDB Source Component

//Create a OLEDB Source Component
IDTSComponentMetaData90 Source = dataFlow.ComponentMetaDataCollection.New();
Source.Name = "OLEDBSource";

Source.ComponentClassID = typeof(Microsoft.Samples.SqlServer.Dts.AdoSourceSample).AssemblyQualifiedName;

//Get the design time instance of the source
CManagedComponentWrapper SourceDesignTime = Source.Instantiate();

//Initialize the component
SourceDesignTime.ProvideComponentProperties();

if (Source.RuntimeConnectionCollection.Count > 0)
{
Source.RuntimeConnectionCollection[0].ConnectionManagerID = PubTRCM.ID;
Source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(PubTRCM);
}

SourceDesignTime.SetComponentProperty("SqlStatement", "Select PlantId from PlantDim");

//Reinitialize the metadata
SourceDesignTime.AcquireConnections(null);
SourceDesignTime.ReinitializeMetaData();
SourceDesignTime.ReleaseConnections();

#endregion

So what stage does the problem start? Is cmado an object? If so what type? What is the result from cmado.AcquireConnection, never mind the cast?

Have you tried debugging your component whilst it is being called like this?

Does the component work in the designer, I assume not.

|||

I could resolve this problem by changing the connection string from -

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=True;";

to

"Provider=SQLOLEDB.1;Data Source={0};Initial Catalog={1};Integrated Security=SSPI;";

and adding the connection manager to the package as follows -

ConnectionManager CMgr = package.Connections.Add("ADO.NETystem.Data.OleDb.OleDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089");

Thanks,

Reni

Accumulated value in table detailrow

I have a dataset with a GROUP BY connected to my table.
In the detailrow I want to add a accumulated value calculated from another
field.
Like this:
MonthX ValueY Acc. value
--
Month 1 10 10
Month 2 20 30
Month 3 30 60
Month 4 40 100
Can I do it by referencing the textboxes in the table, or can i change my
transact-SQL in any way?
(SELECT MonthX,SUM(SomeValue)/SUM(AnotherValue) AS ValueY FROM MyTable GROUP
BY MonthX ORDER BY MonthX)
Please help!Take a look at the reporting services RunningValue function.
"Olof" <Olof@.discussions.microsoft.com> wrote in message
news:4FF60820-A413-4CDB-BAB3-991DBDB8759A@.microsoft.com...
>I have a dataset with a GROUP BY connected to my table.
> In the detailrow I want to add a accumulated value calculated from another
> field.
> Like this:
> MonthX ValueY Acc. value
> --
> Month 1 10 10
> Month 2 20 30
> Month 3 30 60
> Month 4 40 100
> Can I do it by referencing the textboxes in the table, or can i change my
> transact-SQL in any way?
> (SELECT MonthX,SUM(SomeValue)/SUM(AnotherValue) AS ValueY FROM MyTable
> GROUP
> BY MonthX ORDER BY MonthX)
> Please help!|||Thank you very much Stephen, that function was exactly what I needed!!
Greetings
Olof
"Stephen Farmer" wrote:
> Take a look at the reporting services RunningValue function.
>
> "Olof" <Olof@.discussions.microsoft.com> wrote in message
> news:4FF60820-A413-4CDB-BAB3-991DBDB8759A@.microsoft.com...
> >I have a dataset with a GROUP BY connected to my table.
> > In the detailrow I want to add a accumulated value calculated from another
> > field.
> > Like this:
> > MonthX ValueY Acc. value
> > --
> > Month 1 10 10
> > Month 2 20 30
> > Month 3 30 60
> > Month 4 40 100
> >
> > Can I do it by referencing the textboxes in the table, or can i change my
> > transact-SQL in any way?
> > (SELECT MonthX,SUM(SomeValue)/SUM(AnotherValue) AS ValueY FROM MyTable
> > GROUP
> > BY MonthX ORDER BY MonthX)
> >
> > Please help!
>
>

Accumulate values in an aditional column

Hi I have a table like this:

CLIENT Value

a 12
b 11
c 8
d 5
e 4

I want to accumulate this values in an aditional column

CLIENT Value ACUM
a 12 12
b 11 12 + 11 = 23
c 8 23 + 8 = 31
d 5 31 + 5 = 36
e 4 36 + 4 = 40

Thks for your help

Rgds

Harry

CREATE TABLE dbo.RunningTotal

(

Entry int

,RunningTotal int

)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(100,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(200,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(300,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(400,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(500,NULL)

UPDATE dbo.RunningTotal

SET RunningTotal = RT2.RunningTotal

FROM dbo.RunningTotal RT1

INNER JOIN

(

SELECT Entry

,(SELECT SUM(Entry) FROM dbo.RunningTotal WHERE Entry <= rt.Entry) As RunningTotal

FROM dbo.RunningTotal rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.RunningTotal

Resultset:

Entry RunningTotal

100 100

200 300

300 600

400 1000

500 1500

|||

What would I have to do if i want to decrement this values?

CLIENT Value ACUM
a 12 12
b 11 11-12 = -1
c 8 8-11 = -3
d 10 10-8 = 2
e 4 4 -10 = -6

Hope to recieve some news soon

Rgds & a lot of thks

Harry

|||

There are endless variations depending on table structure, data and what you want to do with the data.

Here is one other example:

CREATE TABLE dbo.Balance

(

ID int

,Entry int

,RunningTotal int

)

TRUNCATE TABLE dbo.Balance

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(1,500,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(2,-400,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(3,-300,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(4,-200,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(5,-100,NULL)

UPDATE dbo.Balance

SET RunningTotal = RT2.RunningTotal

FROM dbo.Balance RT1

INNER JOIN

(

SELECT Entry

,(SELECT -SUM(-Entry) FROM dbo.Balance WHERE ID <= rt.ID ) As RunningTotal

FROM dbo.Balance rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.Balance

IDEntryRunningTotal

1500500

2-400100

3-300-200

4-200-400

5-100-500

|||

I try this , but the values only are growing

the table with this input values must be

ID Entry RunningTotal

1 500 500

2 -400 -400 - 500 = -900

3 -300 -300-(-900) = 600

4 -200 -200-(-300) = 100

5 -100 -100 - 100 = -200

How coud I do this?

|||

That is a good method and works perfectly for relatively small number of records

Unfortunatly for a large number it is too slow ,,, :(:(:( do you have another faster method ?

I'm a little desparate

Thanks

|||Yes. do it in your front end application or reporting tool.

Accumulate values in an aditional column

Hi I have a table like this:

CLIENT Value

a 12
b 11
c 8
d 5
e 4

I want to accumulate this values in an aditional column

CLIENT Value ACUM
a 12 12
b 11 12 + 11 = 23
c 8 23 + 8 = 31
d 5 31 + 5 = 36
e 4 36 + 4 = 40

Thks for your help

Rgds

Harry

CREATE TABLE dbo.RunningTotal

(

Entry int

,RunningTotal int

)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(100,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(200,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(300,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(400,NULL)

INSERT INTO dbo.RunningTotal (Entry,RunningTotal)VALUES(500,NULL)

UPDATE dbo.RunningTotal

SET RunningTotal = RT2.RunningTotal

FROM dbo.RunningTotal RT1

INNER JOIN

(

SELECT Entry

,(SELECT SUM(Entry) FROM dbo.RunningTotal WHERE Entry <= rt.Entry) As RunningTotal

FROM dbo.RunningTotal rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.RunningTotal

Resultset:

Entry RunningTotal

100 100

200 300

300 600

400 1000

500 1500

|||

What would I have to do if i want to decrement this values?

CLIENT Value ACUM
a 12 12
b 11 11-12 = -1
c 8 8-11 = -3
d 10 10-8 = 2
e 4 4 -10 = -6

Hope to recieve some news soon

Rgds & a lot of thks

Harry

|||

There are endless variations depending on table structure, data and what you want to do with the data.

Here is one other example:

CREATE TABLE dbo.Balance

(

ID int

,Entry int

,RunningTotal int

)

TRUNCATE TABLE dbo.Balance

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(1,500,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(2,-400,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(3,-300,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(4,-200,NULL)

INSERT INTO dbo.Balance (ID,Entry,RunningTotal)VALUES(5,-100,NULL)

UPDATE dbo.Balance

SET RunningTotal = RT2.RunningTotal

FROM dbo.Balance RT1

INNER JOIN

(

SELECT Entry

,(SELECT -SUM(-Entry) FROM dbo.Balance WHERE ID <= rt.ID ) As RunningTotal

FROM dbo.Balance rt

) RT2

ON RT1.Entry = RT2.Entry

SELECT * FROM dbo.Balance

IDEntryRunningTotal

1500500

2-400100

3-300-200

4-200-400

5-100-500

|||

I try this , but the values only are growing

the table with this input values must be

ID Entry RunningTotal

1 500 500

2 -400 -400 - 500 = -900

3 -300 -300-(-900) = 600

4 -200 -200-(-300) = 100

5 -100 -100 - 100 = -200

How coud I do this?

|||

That is a good method and works perfectly for relatively small number of records

Unfortunatly for a large number it is too slow ,,, :(:(:( do you have another faster method ?

I'm a little desparate

Thanks

|||Yes. do it in your front end application or reporting tool.

Accessing Values Parameter Values from another report

Hi, How can I display a value of a report parameter from one report into a textbox on another report?

If you are calling the 2nd report from the first, setup a parameter in the 2nd report, and pass that value to it from the first report. You can set this up in the navigation window.

BobP

Accessing value from SqlDataSource

I have a SqlDataSource that returns a list of companies and their details by ProductID. It also returns the name of the product associated with the ProductID as the final column (which means it appears for every record returned). I already have a way of determining how many rows were returned, and use that information in a label to say "Your search has returnedx records".

protected void dsGetSuppliersByProduct_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
int RecordCount = e.AffectedRows;
if (RecordCount == 0)
{ lblRecordCount.Text = "<p>No Records found"; }
else
{
if (RecordCount == 1)
{ lblRecordCount.Text = "<p>Your search returned 1 record"; }
else
{ lblRecordCount.Text = "<p>Your search returned " + RecordCount + " records"; }
}
string ProductName;
}

How can I access the ProductName value so that I can extend the label text to say "Your search has returnedx records for <ProductName>" ?

I found a way to do what I wanted. I changed the label to a literal control (for display purposes) and accessed the ProductName value in the RowDataBound event. I then applied it to another literal. My first go resulted in the ProductName appearing as many times as there were rows, so I checked to see if the Literal.Text had already been assigned. So now my code looks like this:

protected void dsGetSuppliersByProduct_Selected(object sender, SqlDataSourceStatusEventArgs e)
{
int RecordCount = e.AffectedRows;
if (RecordCount == 0)
{ ltRecordCount.Text = "<p>No Records found"; }
else
{
if (RecordCount == 1)
{ ltRecordCount.Text = "<p>1 supplier"; }
else
{ ltRecordCount.Text = "<p>" + RecordCount + " suppliers"; }

}

}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string pName = DataBinder.Eval(e.Row.DataItem, "ProductName").ToString();
if (ltProductName.Text == "")
{
ltProductName.Text = " of <strong>" + pName + "</strong></p>";
}
}
}

Accessing the variable in Execute SQL Task

I have a variable SYear with the data type Integer and the value 2005.

Connection type is OLE DB, and my sql statement is:

DECLARE @.MyYear Int

SET @.MyYear = ?

I have set the variable User::Syear as Input with the parameter name=0, there is no result set. I set BypassPrepare both True and False. However, this simple statement does not work. I keep having an error. If I put integer value directly into @.MyYear, it is fine. What is wrong in here? Somebody help me!

The OLE-DB parameter support is more orientated to queries, so this should work-

SELECT * FROM Table WHERE YearCol = ?

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

EXEC MyProc ?, ?

Why and how I have never really got to the bottom off, but that is what I have found to work.

|||This is VERY annoying. It is a type cast problem between the .Net type and the SQL type. When it can't cast the .NET type to the SQL type you selected, it just fails SILENTLY and gives you NOTHING in your task and NO ERROR.

What EXACTLY is the type of SYear, Int32 or Int16. And what EXACTLY did you set the parameter type too.

See this doc: http://msdn2.microsoft.com/en-us/library/4e5xt97a.aspx

I have had much better luck always setting the parameter type to varchar. At least then it gets passed as something.

Also, BypassPrepare must be True or it will fail with an error, that again does not tell you anything about setting BypassPrepare to True.|||

DarrenSQLIS wrote:

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

Why not build the SQL statement in a variable expression first and then use that variable?|||

If you are asking me, then yep, it is valid enough, just didn't pop into my head when writing that reply. I prefer parameters myself, but use either. Couple of pros and cons here-

Execute SQL
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExecuteSQL.html)

|||

Thank you all for your replies.

First of all, I would like to explain why I need to use the variable @.Year1. In my SQL codes in Execute SQL Task, I need to use the value of SYear over and over again. If I use ? instead of @.Year, I need to define SYear in the Parameter Mapping as many times as I use '?'. Is that correct? SYear is a data-type 'Int32'. In the parameter mapping, the data-type used is 'LONG' (only choices here are LONG or SHORT for integer value). Any comment is appreciated. Thank you.

|||You are doing it correctly. Having a var of "Int32" and mapping it to a parameter type "LONG" is correct. Is SYear in the correct "scope"?

You must have "BypassPrepare" set to True otherwise you will always get an error using ?

When I do the same thing it works fine for me. I setup a global var called SYear Int32 and set it as a Parameter, Direction=Input, Type=LONG, ParameterName = 0

SQLStatement:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

What is the error message you get?

Also, make sure you have the SP1 installed on your local machine as well as the server.|||

Thank you, Tom.

I have created the test package and the scope of SYear is the package which contains only this Execute SQL Task.

The one thing I do not understand is that the code 1 works but not code 2. Why? It is not logical.

**Code 1

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = ?

**Code 2

DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

The error message I got is (I set the result set NONE.):

SSIS package "Package1.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package1.dtsx" finished: Failure.

|||

I have seen this behaviour. It has to do with OLE DB limitations regarding variables. You can't use variables in OLE DB if your script contains multiple SQL statements. Your Code 1 contains one statement, so it works fine. Your Code 2 contains three, which doesn't work. I've got two workarounds for you:

1. use a DataReader source

2. Use multiple consecutive Execute SQL Tasks.

Attention: The DataReader Source syntax for using variables is different.

Configure your task to use the right variable. For instance, if you map package variable SYear to Parameter name @.SYear, then your code should look something like this:

DECLARE @.MyYear Int

SET @.MyYear = @.SYear

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

you don't need to declare @.SYear.

Regards,

Pipo

|||

To be clear, there are two more workarounds further up this thread, stored procedures and property expressions.

It is also advisable to issue SET NOCOUNT ON when working with parameters and result sets.

|||

Thank you for your reply, Pipo. But, Tom used 3 SQL statements with the input variable and he said it worked (please look at the previous reply). I do understand that there are some other ways to get around this problem, but I still would like to find out what is causing this. I have about 500-fields syntax check to clean the data for every year. I would not like to create the separate program for each year. That is why I would like to accept the user input (year) and use it for 500 syntax checks. The program should be flexible, shouldn′t it?

I really appreciate replies from you all.

Thank you.

|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure. Should I use the Execute SQL Task and set the SQL Source Type = File Connection?

|||What do you mean by code 2 doesn't work?

You need to have result set set to something other than "None" if it returns a record set. That is what is generating the error you are seeing.

My guess from the code you posted is, SQL is implicitly converting DB030 into the correct type on code 1 and code 2 you are not comparing an INT to an INT. What is the data type of DB030?

Do you have SP1 installed on the server and the client? What version does it show. The current version (pre SP2) is 9.0.2153.|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure.

Instead of using a SELECT statement or other T-SQL in your Execute SQL Task, you call the stored procedure with placeholders for parameters, so it may look like this-

EXEC myproc ?, ?

? is still the placeholder for the parameter in the same way it is when using a SELECT such as SELECT * FROM table WHERE id = ? AND id2 = ?

|||

yhalldorsson,

To be honest, I overlooked Tom's little remark that it works fine for him.

I've been over this some time ago, and couldn't get it to work. To make sure I didn't miss anything, I tried again just now, using the exact same code showen above:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

I used the same parameter mapping as described by Tom, but I can't get it working. I get the exact same error message as you do.

So, to be honest, I'm getting curious as to what's the (hidden) difference between Tom's attempt and mine (and yours, presumably).

(To be complete: I used Tom's Parameter mapping and code, Single row result set, and put the Result Name Year into another variable of type int32.

And I set BypassPrepare to True)

Regards,

Pipo

Accessing the variable in Execute SQL Task

I have a variable SYear with the data type Integer and the value 2005.

Connection type is OLE DB, and my sql statement is:

DECLARE @.MyYear Int

SET @.MyYear = ?

I have set the variable User::Syear as Input with the parameter name=0, there is no result set. I set BypassPrepare both True and False. However, this simple statement does not work. I keep having an error. If I put integer value directly into @.MyYear, it is fine. What is wrong in here? Somebody help me!

The OLE-DB parameter support is more orientated to queries, so this should work-

SELECT * FROM Table WHERE YearCol = ?

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

EXEC MyProc ?, ?

Why and how I have never really got to the bottom off, but that is what I have found to work.

|||This is VERY annoying. It is a type cast problem between the .Net type and the SQL type. When it can't cast the .NET type to the SQL type you selected, it just fails SILENTLY and gives you NOTHING in your task and NO ERROR.

What EXACTLY is the type of SYear, Int32 or Int16. And what EXACTLY did you set the parameter type too.

See this doc: http://msdn2.microsoft.com/en-us/library/4e5xt97a.aspx

I have had much better luck always setting the parameter type to varchar. At least then it gets passed as something.

Also, BypassPrepare must be True or it will fail with an error, that again does not tell you anything about setting BypassPrepare to True.

|||

DarrenSQLIS wrote:

The parameter support is not geared to big lumps of procedural SQL, but simpler DML statements that share common structures between all OLE-DB implementations. Try re-writing the query to use parameters inline. If you really need local variables wrap the code in a stored procedure, then just call the proc, it acts as abstraction interface that is simple enough for OLE-DB to deal with, e.g.

Why not build the SQL statement in a variable expression first and then use that variable?|||

If you are asking me, then yep, it is valid enough, just didn't pop into my head when writing that reply. I prefer parameters myself, but use either. Couple of pros and cons here-

Execute SQL
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ExecuteSQL.html)

|||

Thank you all for your replies.

First of all, I would like to explain why I need to use the variable @.Year1. In my SQL codes in Execute SQL Task, I need to use the value of SYear over and over again. If I use ? instead of @.Year, I need to define SYear in the Parameter Mapping as many times as I use '?'. Is that correct? SYear is a data-type 'Int32'. In the parameter mapping, the data-type used is 'LONG' (only choices here are LONG or SHORT for integer value). Any comment is appreciated. Thank you.

|||You are doing it correctly. Having a var of "Int32" and mapping it to a parameter type "LONG" is correct. Is SYear in the correct "scope"?

You must have "BypassPrepare" set to True otherwise you will always get an error using ?
When I do the same thing it works fine for me. I setup a global var called SYear Int32 and set it as a Parameter, Direction=Input, Type=LONG, ParameterName = 0

SQLStatement:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

What is the error message you get?

Also, make sure you have the SP1 installed on your local machine as well as the server.

|||

Thank you, Tom.

I have created the test package and the scope of SYear is the package which contains only this Execute SQL Task.

The one thing I do not understand is that the code 1 works but not code 2. Why? It is not logical.

**Code 1

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = ?

**Code 2

DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

The error message I got is (I set the result set NONE.):

SSIS package "Package1.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "DECLARE @.MyYear Int

SET @.MyYear = ?

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package1.dtsx" finished: Failure.

|||

I have seen this behaviour. It has to do with OLE DB limitations regarding variables. You can't use variables in OLE DB if your script contains multiple SQL statements. Your Code 1 contains one statement, so it works fine. Your Code 2 contains three, which doesn't work. I've got two workarounds for you:

1. use a DataReader source

2. Use multiple consecutive Execute SQL Tasks.

Attention: The DataReader Source syntax for using variables is different.

Configure your task to use the right variable. For instance, if you map package variable SYear to Parameter name @.SYear, then your code should look something like this:

DECLARE @.MyYear Int

SET @.MyYear = @.SYear

SELECT [DB030]

,[DB010]

FROM [dbo].[D_Kafla]

WHERE DB030 = @.MyYear

you don't need to declare @.SYear.

Regards,

Pipo

|||

To be clear, there are two more workarounds further up this thread, stored procedures and property expressions.

It is also advisable to issue SET NOCOUNT ON when working with parameters and result sets.

|||

Thank you for your reply, Pipo. But, Tom used 3 SQL statements with the input variable and he said it worked (please look at the previous reply). I do understand that there are some other ways to get around this problem, but I still would like to find out what is causing this. I have about 500-fields syntax check to clean the data for every year. I would not like to create the separate program for each year. That is why I would like to accept the user input (year) and use it for 500 syntax checks. The program should be flexible, shouldn′t it?

I really appreciate replies from you all.

Thank you.

|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure. Should I use the Execute SQL Task and set the SQL Source Type = File Connection?

|||What do you mean by code 2 doesn't work?

You need to have result set set to something other than "None" if it returns a record set. That is what is generating the error you are seeing.

My guess from the code you posted is, SQL is implicitly converting DB030 into the correct type on code 1 and code 2 you are not comparing an INT to an INT. What is the data type of DB030?

Do you have SP1 installed on the server and the client? What version does it show. The current version (pre SP2) is 9.0.2153.

|||

Thank you Darren. When the sotre procedures are used, how is the variable used (the variable declared for a whole package)? I am not quite sure how I can call the stored procedure.

Instead of using a SELECT statement or other T-SQL in your Execute SQL Task, you call the stored procedure with placeholders for parameters, so it may look like this-

EXEC myproc ?, ?

? is still the placeholder for the parameter in the same way it is when using a SELECT such as SELECT * FROM table WHERE id = ? AND id2 = ?

|||

yhalldorsson,

To be honest, I overlooked Tom's little remark that it works fine for him.

I've been over this some time ago, and couldn't get it to work. To make sure I didn't miss anything, I tried again just now, using the exact same code showen above:

DECLARE @.MyYear int
SET @.MyYear = ?

SELECT Year = @.MyYear

I used the same parameter mapping as described by Tom, but I can't get it working. I get the exact same error message as you do.

So, to be honest, I'm getting curious as to what's the (hidden) difference between Tom's attempt and mine (and yours, presumably).

(To be complete: I used Tom's Parameter mapping and code, Single row result set, and put the Result Name Year into another variable of type int32.

And I set BypassPrepare to True)

Regards,

Pipo

Accessing table cell

I have a simple table that has a couple of fixed number of columns and the
rows are populated from the database. I have a need to get the value from the
cell which i know will always be the second column and third row. Is there a
way to access that cell by maybe using indexes or is this even possible?
Any help or guidance would be greatly appreciated.But if you know the value is coming from some field in a table, why can't you
use that instead of getting the value of the field from the cell which is not
possible.
Amarnath
"appu" wrote:
> I have a simple table that has a couple of fixed number of columns and the
> rows are populated from the database. I have a need to get the value from the
> cell which i know will always be the second column and third row. Is there a
> way to access that cell by maybe using indexes or is this even possible?
> Any help or guidance would be greatly appreciated.|||I need to access that cell because the report will be exported to excel and
the user will enter a number in that cell. The value from that cell will act
as a denomiator to calculate other cells in the table.
"Amarnath" wrote:
> But if you know the value is coming from some field in a table, why can't you
> use that instead of getting the value of the field from the cell which is not
> possible.
> Amarnath
> "appu" wrote:
> > I have a simple table that has a couple of fixed number of columns and the
> > rows are populated from the database. I have a need to get the value from the
> > cell which i know will always be the second column and third row. Is there a
> > way to access that cell by maybe using indexes or is this even possible?
> > Any help or guidance would be greatly appreciated.

Accessing substrings

Hi All,

I have an attribute kfld which is varchar2 type and has value as shown below: e.g. kfld= "abc= 2bv= 1fed= 30thb= 200"

my problem is the position of field "fed" is not fixed in the string, So can I get the value '30' (which follows the substring 'fed=' and precedes any alphabet string like tbh, ignore whitespaces) in the output of a single query for a given record.

Rgrd,
tkselect substring(kfld from position('fed=' in kfld)) from ...

this gets you the substring '30thb= 200'

however, detecting the first alphabet now becomes a little trickier|||Do I win a prize for gratuitous use of string functions?

This is using SQL Server, but I assume other systems have similar functions.

declare @.string as varchar(40)
set @.string='abc= 2bv= 1fed= 30thb= 200'

select ltrim(substring(substring(@.string,charindex('fed', @.string)+4,len(@.string)), 1,patindex('%[a-Z]%',substring(@.string,charindex('fed',@.string)+4,le n(@.string)))-1))

That assumes the number following 'fed' is always followed by a letter. If it's "@." or "$", it won't work. Of course, you need to adapt for your table and column.|||no fair!! i was trying to restrict myself to the language of this particular forum|||I did admit I was cheating. Just trying to get the user going in the right direction. Well, maybe not the RIGHT direction, but a direction none-the-less.|||varchar2 screams Oracle at me... should we be looking for Oracle solutions?|||The string that is contained looks like it should be normalised in some fashion or another. Perhaps to a table of id, key=>values. Difficult to tell thought without some more meaningful data.

What string functions are standard in SQL?|||standard SQL string functions are concatenation (accomplished with double pipes ||), UPPER, LOWER, TRIM, TRANSLATE, CHAR_LENGTH, BIT_LENGTH, OCTET_LENGTH, SUBSTRING, and POSITION

Accessing Stored Procedure from IIS

I have a stored procedure that is supposed to
1. Increment a counter in Table A via a transaction
2. Use this value as the primary key to add in an address to customers
Table B
(Referenced as a "DECLARE @.CustomerID INT" just after the AS
clause)
3. Return the primary key.
This works perfectly when being called from Query Analyzer supplying values
in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
execution falls right through without adding the customer or incrementing
the counter or giving an error. All conditional routines are executed, but
no work is being done.
Is there anything I can do to raise some sort of error to let me know what
is or isn't happening?
adovbs.inc is linked and the "conditional code" I refer to swaps the stored
procedure name (for add/edit) to add in one more parameter needed for
editing records. The parameters are referenced in exactly the same order as
they are in the procedures, with the return value being mentioned first.
The append parameters lines have been rewritten in short form, long form,
and in a "with" block as shown.
for example:
(Blocked within conditional code)
adocmd.CommandType = adCmdStoredProc
adocmd.CommandText = "spr_AddCustomer"
adocmd.ActiveConnection = conn.ConnectionObject
set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
adParamReturnValue, 0)
adocmd.parameters.append param
(Conditional code end)
With adocmd
set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
company)
.parameters.append param
set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
firstname)
.parameters.append param
set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
1, middleinitial)
.parameters.append param
set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
lastname)
.parameters.append param
... (continuing to add parameters in the same order as SP)
.execute lngRecs,,adexecutenorecords
CustomerId = .Parameters("@.RETURN_VALUE").Value
End WithMake sure you disable "on error resume next" in your ASP page.
Make sure the stored procedure has SET NOCOUNT ON at the beginning.
Have a look at http://www.aspfaq.com/2201
http://www.aspfaq.com/
(Reverse address to reply.)
"stjulian" <anonymous@.discussions.microsoft.com> wrote in message
news:#l$GjY2GFHA.2736@.TK2MSFTNGP09.phx.gbl...
> I have a stored procedure that is supposed to
> 1. Increment a counter in Table A via a transaction
> 2. Use this value as the primary key to add in an address to customers
> Table B
> (Referenced as a "DECLARE @.CustomerID INT" just after the AS
> clause)
> 3. Return the primary key.
> This works perfectly when being called from Query Analyzer supplying
values
> in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
> execution falls right through without adding the customer or incrementing
> the counter or giving an error. All conditional routines are executed, but
> no work is being done.
> Is there anything I can do to raise some sort of error to let me know what
> is or isn't happening?
> adovbs.inc is linked and the "conditional code" I refer to swaps the
stored
> procedure name (for add/edit) to add in one more parameter needed for
> editing records. The parameters are referenced in exactly the same order
as
> they are in the procedures, with the return value being mentioned first.
> The append parameters lines have been rewritten in short form, long form,
> and in a "with" block as shown.
> for example:
> (Blocked within conditional code)
> adocmd.CommandType = adCmdStoredProc
> adocmd.CommandText = "spr_AddCustomer"
> adocmd.ActiveConnection = conn.ConnectionObject
> set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> adocmd.parameters.append param
> (Conditional code end)
> With adocmd
> set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
> company)
> .parameters.append param
> set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
> firstname)
> .parameters.append param
> set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
> 1, middleinitial)
> .parameters.append param
> set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
> lastname)
> .parameters.append param
> ... (continuing to add parameters in the same order as SP)
> .execute lngRecs,,adexecutenorecords
> CustomerId = .Parameters("@.RETURN_VALUE").Value
> End With
>|||Hi
You don't provide DDL for the procedure.
http://www.aspfaq.com/etiquette.asp?id=5006
You may want to check what is happening using profiler, and make sure that
NOCOUNT is ON.
John
"stjulian" wrote:

> I have a stored procedure that is supposed to
> 1. Increment a counter in Table A via a transaction
> 2. Use this value as the primary key to add in an address to customers
> Table B
> (Referenced as a "DECLARE @.CustomerID INT" just after the AS
> clause)
> 3. Return the primary key.
> This works perfectly when being called from Query Analyzer supplying value
s
> in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
> execution falls right through without adding the customer or incrementing
> the counter or giving an error. All conditional routines are executed, but
> no work is being done.
> Is there anything I can do to raise some sort of error to let me know what
> is or isn't happening?
> adovbs.inc is linked and the "conditional code" I refer to swaps the store
d
> procedure name (for add/edit) to add in one more parameter needed for
> editing records. The parameters are referenced in exactly the same order a
s
> they are in the procedures, with the return value being mentioned first.
> The append parameters lines have been rewritten in short form, long form,
> and in a "with" block as shown.
> for example:
> (Blocked within conditional code)
> adocmd.CommandType = adCmdStoredProc
> adocmd.CommandText = "spr_AddCustomer"
> adocmd.ActiveConnection = conn.ConnectionObject
> set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> adocmd.parameters.append param
> (Conditional code end)
> With adocmd
> set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
> company)
> .parameters.append param
> set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
> firstname)
> .parameters.append param
> set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
> 1, middleinitial)
> .parameters.append param
> set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
> lastname)
> .parameters.append param
> ... (continuing to add parameters in the same order as SP)
> .execute lngRecs,,adexecutenorecords
> CustomerId = .Parameters("@.RETURN_VALUE").Value
> End With
>
>|||Thank you both for your attention...
DDL follows
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.spr_WriteCustomers Created: 2/24/05
JS ******/
CREATE PROCEDURE spr_WriteCustomers
@.Company varchar(40) = NULL,
@.FirstName varchar(15) = NULL,
@.MiddleInitial varchar(1) = NULL,
@.LastName varchar(20) = NULL,
@.Title varchar(30) = NULL,
@.BillingAttnLine varchar(40) = NULL,
@.BillingAddress1 varchar(40) = NULL,
@.BillingAddress2 varchar(40) = NULL,
@.BillingCity varchar(20) = NULL,
@.BillingState varchar(3) = NULL,
@.BillingZip varchar(10) = NULL,
@.FK_CountryCode varchar(3) = NULL,
@.BillingCountry varchar(25) = NULL,
@.BillingPhone varchar(25) = NULL,
@.BillingFax varchar(15) = NULL,
@.ShippingFirstName varchar(15) = NULL,
@.ShippingLastName varchar(20) = NULL,
@.ShippingCompany varchar(40) = NULL,
@.ShippingTitle varchar(40) = NULL,
@.ShippingAttnLine varchar(40) = NULL,
@.ShippingAddress1 varchar(40) = NULL,
@.ShippingAddress2 varchar(40) = NULL,
@.ShippingCity varchar(20) = NULL,
@.ShippingState varchar(3) = NULL,
@.ShippingZip varchar(10) = NULL,
@.FK_SCountryCode varchar(3) = NULL,
@.ShippingCountry varchar(25) = NULL,
@.ShippingPhone varchar(25) = NULL,
@.ShippingFax varchar(15) = NULL,
@.FK_CustomerTierID int = 0,
@.UserName varchar(45) = NULL,
@.Password varchar(20) = NULL,
@.EMail varchar(45) = NULL,
@.TaxExempt bit = 0,
@.NoEmail bit= 0,
@.GREETING1 varchar(35) = NULL,
@.GREETING2 varchar(35) = NULL,
@.BelongsTo int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.custid INT
DECLARE @.CREATEDATE DATETIME
-- Begin process
--Get New CustomerID
BEGIN TRAN
SELECT @.custid = nextid
FROM tblAutoNumber
WHERE TableName = 'tblCustomers'
UPDATE tblAutoNumber
SET nextid = @.custid + 1
WHERE TableName = 'tblCustomers'
COMMIT TRAN
SELECT @.CREATEDATE = getdate()
BEGIN
INSERT INTO tblCustomers
(PK_ID,
Company,
FirstName,
MiddleInitial,
LastName,
Title,
BillingAttnLine,
BillingAddress1,
BillingAddress2,
BillingCity,
BillingState,
BillingZip,
FK_CountryCode,
BillingCountry,
ShippingFirstName,
ShippingLastName,
ShippingCompany,
ShippingTitle,
ShippingAttnLine,
ShippingAddress1,
ShippingAddress2,
ShippingCity,
ShippingState,
ShippingZip,
FK_SCountryCode,
ShippingCountry,
FK_CustomerTierID,
UserName,
Password,
Email,
BillingPhone,
ShippingPhone,
BillingFax,
ShippingFax,
LeaseStatus,
LeaseCreditLimit,
FK_CurrencyId,
DisableLogin,
LastModified,
Created,
TaxExempt,
NoEmail,
GREETING1,
GREETING2,
TaxExemptVerified,
AutoCancel,
LastLogin,
BelongsTo)
VALUES(
@.custid,
@.Company,
@.FirstName,
@.MiddleInitial,
@.LastName,
@.Title,
@.BillingAttnLine,
@.BillingAddress1,
@.BillingAddress2,
@.BillingCity,
@.BillingState,
@.BillingZip,
@.FK_CountryCode,
@.BillingCountry,
@.ShippingFirstName,
@.ShippingLastName,
@.ShippingCompany,
@.ShippingTitle,
@.ShippingAttnLine,
@.ShippingAddress1,
@.ShippingAddress2,
@.ShippingCity,
@.ShippingState,
@.ShippingZip,
@.FK_SCountryCode,
@.ShippingCountry,
@.FK_CustomerTierID,
@.UserName,
@.Password,
@.Email,
@.BillingPhone,
@.ShippingPhone,
@.BillingFax,
@.ShippingFax,
'',
0,
0,
0,
@.CREATEDATE,
@.CREATEDATE,
@.TaxExempt,
@.NoEmail,
@.GREETING1,
@.GREETING2,
0,
0,
@.CREATEDATE,
@.BelongsTo)
END
RETURN @.custid
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.com...
> Hi
> You don't provide DDL for the procedure.
> http://www.aspfaq.com/etiquette.asp?id=5006
> You may want to check what is happening using profiler, and make sure that
> NOCOUNT is ON.
> John
>
> "stjulian" wrote:
>|||Wait, I think I got it ... The On Error was in an include file.
Thank you all for your help.
Julian
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.com...
> Hi
> You don't provide DDL for the procedure.
> http://www.aspfaq.com/etiquette.asp?id=5006
> You may want to check what is happening using profiler, and make sure that
> NOCOUNT is ON.
> John
>
> "stjulian" wrote:
>

Accessing single value using tuple

Hello,

I tried to access a single cell value using tuple but am getting incorrect result. I know the value is there and the tuple exists because I was able to retrieve the value using allmembers. This is a simple MDX query, I hope someone can tell me what I did wrong.

SELECT non empty { [Measures].[Weight]} ON COLUMNS,

non empty

{

([Time].[Year].[2007] ,

[Category Type].[Raw Materials] ,

[Category].allmembers)

}

ON ROWS FROM [MCube]

The query above returns:

2007 Raw Materials All 2,450,000

2007 Raw Materials Yarn & Fiber 1,250,000

2007 Raw Materials Auxiliary Materials 1,200,000

As you can see from the query above that the weight for Auxiliary Materials for 2007 is 1.2M. However, the query below returns no result as if the tuple for Auxiliary Materials does not exist. What did I do wrong?

SELECT non empty { [Measures].[Weight]} ON COLUMNS,

non empty

{

([Time].[Year].[2007] ,

[Category Type].[Raw Materials] ,

[Category].[Auxiliary Materials])

}

ON ROWS FROM [MCube]

Thanks,

SB

Do you have same problem if you remove NON EMPTY from columns and rows? And you are using Analysis Services 2000, right?

Vidas Matelis

|||

Hi Vidas,

Yes, same problem without "non empty" on both row and column. The result is an empty cell with weight column heading and no row heading.

I am using Analysis Services 2005.

Thanks.

SB

|||

SouBee,

Could you please try to specifie full member qualifier: dimensionName.hierarchyName.Member .Try to do this in both statements (One that works, and second that don't). Example: [Category].[Category].AllMembers, etc. Maybe there is some name matching between hierarchy and level.

You can also try build similar query in any front end and see if results are then same inconstent. Capturing statement with SQL Profiler might also help.

Vidas Matelis

|||

Hi Vidas,

Thanks for taking the time to read my posts.I had greatly simplified my query to make it easier to understand. I apologize for not fully qualifying the dimensions. I know I can get the cube browser to return the resultset I need, the only problem is to get RS Query Browser to do the same. When "Aggregate(calculated member)" is used in the report, RS automatically modified the MDX query and caused a big chunk of the resultset to be truncated. Thanks for your suggestion to use SQL Profiler because I was able to capture the MDX query I need (with minor modification) from executing Analysis Service cube browser.

This is the MDX query generated by RS 2005, it truncates the resultset and produces incorrect result when Aggregate(calculated member) is used:

SELECT NON EMPTY { [Measures].[Weight] } ON COLUMNS,

NON EMPTY
{[Time].[Year].[Year].ALLMEMBERS *
[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *
{[Raw Materials].[Category].[All]} *
{[Raw Materials].[Material Type].[All]},

[Time].[Year].[Year].ALLMEMBERS *
[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *
[Raw Materials].[Category].[Category].ALLMEMBERS *
{[Raw Materials].[Material Type].[All]},

([Time].[Year].[Year].ALLMEMBERS *
[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *
[Raw Materials].[Category].[Category].ALLMEMBERS *
[Raw Materials].[Material Type].[Material Type].ALLMEMBERS
)

} ON ROWS FROM [MCube]

This is the solution to my problem. It returns all data without truncating.

SELECT NON EMPTY { [Measures].[Weight} ON COLUMNS,

non empty

{

[Time].[Year].[Year].ALLMEMBERS *

[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *

{[Raw Materials].[Category].[All]} *

{[Raw Materials].[Material Type].[All]},

[Time].[Year].[Year].ALLMEMBERS *

[Raw Materials].[Category Type].[Category Type].ALLMEMBERS *

[Raw Materials].[Category].[Category].ALLMEMBERS *

{[Raw Materials].[Material Type].[All]},

CROSSJOIN

(

AddCalculatedMembers([Time].[Year].[Year].MEMBERS),

CROSSJOIN

(

AddCalculatedMembers([Raw Materials].[Category Type].[Category Type].MEMBERS),

CROSSJOIN

(

AddCalculatedMembers([Raw Materials].[Category].[Category].MEMBERS),

AddCalculatedMembers([Raw Materials].[Material Type].[Material Type].MEMBERS)

)

)

)

}

ON ROWS FROM [MCube]

Thanks.

SB