Showing posts with label variables. Show all posts
Showing posts with label variables. Show all posts

Sunday, March 11, 2012

Cannot use global variables in Table Footer

Hi,
I tried to use the global variables "totalpages" in my table footer to decide whether to hide or show if there are no records from the dataset.
But then I found out that global variables can only be used in page header/footer.
Is there any way i could check whether there's records returned, and hence control the visibility.
Thanks.Good Evening!
Right or wrong - but never mind me using stored procs for everything!

For ALL MS RS reports we use stored procedures - for everything...
So in every stored proc we count records in a lot of various ways - but primarily we use in the first line
Select
Count(1) as Expr1 - Expr1 is our indicator that data has been populated within the SQL Select for all records processed (may have some records or not)
You should never use Count(*) anyway because it does another "scan" of the DB to get the result...
So within MS RS the first thing we do is inspect EXPR1 for a value
No Value
Display "Selection Parameters Found No Information For Your Request" in a text box that was added to the Header of the Report
Else
Display the data within MS RS.
The header text box has an IIF condition and we BOLD in RED so it is visible for the users who do not like to read what they are getting
I don't know how to do if the SQL Select was a "TEXT" string versus a stored procedure! Probaly just as easy - I guess....
We have also in a lot of instances where we use a SQL Stored Proc driver which means and it works really nice if you have SELECTS <= 8000 characters...
We already know the fields to populate the MS RS REport so we just take the parameters passed - look up in a table the SQL for the particulare MS RS report and insert the parameters passed by MS RS so we always get a return value(s) from the SQL Stored Proc driver that performs and EXEC for the SQL select we pull from the table and then declare and insert in the SQL script the values the user passed from MS RS.
So the return value from the EXEC is passed back to use and we cast as EXPR1.
I got to "woordy" here - but nevertheless - I would hope that not only for no data from a Select is captured but also other error situations that might occur as well - so you can pass this information back as well especially if your environment is OLTP versus a warehouse...
Best regards



|||

eeyore21,
CountRows(Scope) where Scope is your dataset. ex: Place=IIF(CountRows("MyDataset") = 0, True, False) as Visibility->Hidden->Expession for your object that you want to hide/show based on record count.

Hope this helps,
Mike

|||

Thanks,
It sure helps alot.

Thursday, March 8, 2012

Cannot use anonymous methods inside SQLCLR...

Hi all,

The problem is: when you're trying to call in method MyMethod anonymous method that doesn't use local variables, deployment of the assembly will fail referring that MyMethod tries to store smth. in the static variable. Indeed, looking at the compiled CLR code, you can see that anonymous delegate is cached in the private static delegate and the call looks like:

If(ClassName.privateStaticDelegate == null)
ClassName.privateStaticDelegate = new MyDelegate(HiddenMethodName);
CallAnonymousMethod(ClassName.privateStaticDelegate);

Is there any workaround to fix this problem.

P.S. I googled about this problem and found only one article on it:
http://www.ayende.com/Blog/default,date,2005-12-26.aspx

Hi Almaz,

I'm not quite sure I understand the question, but we're pretty picky about statics in SAFE and EXTERNAL ACCESS assemblies. They should be okay if they're readonly or const, but otherwise not.

Cheers,

-Isaac

|||

Hi Isaac,

Here is the repro: Create a new SQL Server Project, add a following stored procedure and try to deploy the assembly:

using System;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure]
public static void StoredProcedure1()
{
int[] myIntArray = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11};
int i = Array.FindIndex(myIntArray, delegate (int value)
{
return value == 5;
});
}
}

Following variant works:

using System;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure]
public static void StoredProcedure1()
{
int[] myIntArray = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11};
int valueToFind = 5;
int i = Array.FindIndex(myIntArray, delegate (int value)
{
return value == valueToFind;
});
}
}

Thanks in advance,
Sergei Almazov

|||

Right, the problem is that we don't allow store into a static field. In this case, the store was an optimization by the C# compiler. We have a requirement that whenever compiler does this, it should mark the static field with [System.Runtime.CompilerServices.CompilerGenerated] attribute. Apparently, this didn't happen here.

There are two possible workarounds here:

1) mark the class with [System.Runtime.CompilerServices.CompilerGenerated] attribute

2) register the assembly as UNSAFE assembly.

We will follow up with C# compiler and fix the issue, so you can remove your workaround in the future.

-Xiaowei

Cannot use anonymous methods inside SQLCLR...

Hi all,

The problem is: when you're trying to call in method MyMethod anonymous method that doesn't use local variables, deployment of the assembly will fail referring that MyMethod tries to store smth. in the static variable. Indeed, looking at the compiled CLR code, you can see that anonymous delegate is cached in the private static delegate and the call looks like:

If(ClassName.privateStaticDelegate ==null)
ClassName.privateStaticDelegate =new MyDelegate(HiddenMethodName);
CallAnonymousMethod(ClassName.privateStaticDelegate);

Is there any workaround to fix this problem.

P.S. I googled about this problem and found only one article on it:
http://www.ayende.com/Blog/default,date,2005-12-26.aspx

Hi Almaz,

I'm not quite sure I understand the question, but we're pretty picky about statics in SAFE and EXTERNAL ACCESS assemblies. They should be okay if they're readonly or const, but otherwise not.

Cheers,

-Isaac

|||

Hi Isaac,

Here is the repro: Create a new SQL Server Project, add a following stored procedure and try to deploy the assembly:

using System;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure]
public static void StoredProcedure1()
{
int[] myIntArray = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11};
int i = Array.FindIndex(myIntArray, delegate (int value)
{
return value == 5;
});
}
}

Following variant works:

using System;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure]
public static void StoredProcedure1()
{
int[] myIntArray = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11};
int valueToFind = 5;
int i = Array.FindIndex(myIntArray, delegate (int value)
{
return value == valueToFind;
});
}
}

Thanks in advance,
Sergei Almazov

|||

Right, the problem is that we don't allow store into a static field. In this case, the store was an optimization by the C# compiler. We have a requirement that whenever compiler does this, it should mark the static field with [System.Runtime.CompilerServices.CompilerGenerated] attribute. Apparently, this didn't happen here.

There are two possible workarounds here:

1) mark the class with [System.Runtime.CompilerServices.CompilerGenerated] attribute

2) register the assembly as UNSAFE assembly.

We will follow up with C# compiler and fix the issue, so you can remove your workaround in the future.

-Xiaowei

Tuesday, February 14, 2012

Cannot save a long string

This is what I am trying to save and what is actually being saved:

All variables are STRINGS.

Saving: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:18:19 10:37:33 11:01:34 11:23:26 11:50:47 ^12:52:30 13:42:37 ^14:41:04 ^18:36:13
Saved: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:

Saving: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:34:40 10:39:02 11:12:45 11:39:33 ^12:18:51 13:11:24 ^14:04:52 15:18:43 ^19:13:10
Saved: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:

The codes:

Table scripted to a query:

USE [Analytical]
GO
/****** Object: Table [dbo].[DailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:44:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DailyTickMinMaxAnalysis](
[symbol] [varchar](50) NOT NULL,
[cDate] [varchar](50) NOT NULL,
[part] [varchar](50) NOT NULL,
[collection] [varchar](max) NULL,
CONSTRAINT [remPat_Trades] PRIMARY KEY CLUSTERED
(
[symbol] ASC,
[cDate] ASC,
[part] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Stored procedure is:


USE [Analytical]
GO
/****** Object: StoredProcedure [dbo].[InsertDailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:45:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[InsertDailyTickMinMaxAnalysis]
@.symbol varchar(50),
@.cDate varchar(50),
@.part varchar(50),
@.collection varchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.DailyTickMinMaxAnalysis (
symbol,
cDate,
part,
[collection])
Values (
@.symbol,
@.cDate,
@.part,
@.collection)
END

C# procedure:

public void saveHistTickMinMaxAnalysis ( int tableIndex )
{
if ( tableIndex != -1 )
{
using ( SqlConnection conn_Analytical = new SqlConnection ( "server=ddmnvt61\\sqlexpress;" +
"database=Analytical;Trusted_Connection=Yes" ) )
{
SqlCommand cmdm = new SqlCommand ( );
conn_Analytical.Open ( );
cmdm.Connection = conn_Analytical;
cmdm.CommandType = CommandType.StoredProcedure;
cmdm.CommandText = "InsertDailyTickMinMaxAnalysis";
cmdm.Parameters.Clear ( );
cmdm.Parameters.Add ( "@.symbol", SqlDbType.VarChar, 50, "symbol" );
cmdm.Parameters.Add ( "@.cDate", SqlDbType.VarChar, 50, "cDate" );
cmdm.Parameters.Add ( "@.part", SqlDbType.VarChar, 50, "part" ); // 2
cmdm.Parameters.Add ( "@.collection", SqlDbType.VarChar, 500, "collection" ); // 3
foreach ( DataRow row in Globals.dtSets.Tables[tableIndex].Rows )
{
cmdm.Parameters[0].Value = ( string )row[0];
cmdm.Parameters[1].Value = ( string )row[1];
cmdm.Parameters[2].Value = ( string )row[2];
cmdm.Parameters[3].Value = ( string )row[3];
Console.WriteLine ( "Saving {0}", ( string )row[3] ); // <= where the print out occurs to confirm that the right string is being passed
try
{
cmdm.ExecuteNonQuery ( );
}
catch { }
}
}
}
} // saveHistTickMinMaxAnalysis

Table is defined with the last columns[3].Length = 500;

What happened to the rest of my strings?

I would appreciate any help.

Many thanks.

Your parameter in your procedure is only VARCHAR(50), therefor it is implictly cut to 50 chars.

Jens K. Suessmeyer


http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Your parameter in your procedure is only VARCHAR(50), therefor it is implictly cut to 50 chars.

Jens K. Suessmeyer


http://www.sqlserver2005.de

Thank you Jens.

I actually figured it out right after I posted the message but in the end I did a complete workaround even without SQL Server. Probably was very tired that day. Goofy!

Sunday, February 12, 2012

Cannot save a long string

This is what I am trying to save and what is actually being saved:

All variables are STRINGS.

Saving: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:18:19 10:37:33 11:01:34 11:23:26 11:50:47 ^12:52:30 13:42:37 ^14:41:04 ^18:36:13
Saved: 08:02:40 08:18:05 ^09:34:07 09:39:25 ^10:02:50 10:

Saving: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:34:40 10:39:02 11:12:45 11:39:33 ^12:18:51 13:11:24 ^14:04:52 15:18:43 ^19:13:10
Saved: 08:08:52 08:59:55 ^09:38:04 09:54:20 ^10:15:37 10:

The codes:

Table scripted to a query:

USE [Analytical]
GO
/****** Object: Table [dbo].[DailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:44:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DailyTickMinMaxAnalysis](
[symbol] [varchar](50) NOT NULL,
[cDate] [varchar](50) NOT NULL,
[part] [varchar](50) NOT NULL,
[collection] [varchar](max) NULL,
CONSTRAINT [remPat_Trades] PRIMARY KEY CLUSTERED
(
[symbol] ASC,
[cDate] ASC,
[part] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Stored procedure is:


USE [Analytical]
GO
/****** Object: StoredProcedure [dbo].[InsertDailyTickMinMaxAnalysis] Script Date: 08/07/2007 15:45:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER PROCEDURE [dbo].[InsertDailyTickMinMaxAnalysis]
@.symbol varchar(50),
@.cDate varchar(50),
@.part varchar(50),
@.collection varchar(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.DailyTickMinMaxAnalysis (
symbol,
cDate,
part,
[collection])
Values (
@.symbol,
@.cDate,
@.part,
@.collection)
END

C# procedure:

public void saveHistTickMinMaxAnalysis ( int tableIndex )
{
if ( tableIndex != -1 )
{
using ( SqlConnection conn_Analytical = new SqlConnection ( "server=ddmnvt61\\sqlexpress;" +
"database=Analytical;Trusted_Connection=Yes" ) )
{
SqlCommand cmdm = new SqlCommand ( );
conn_Analytical.Open ( );
cmdm.Connection = conn_Analytical;
cmdm.CommandType = CommandType.StoredProcedure;
cmdm.CommandText = "InsertDailyTickMinMaxAnalysis";
cmdm.Parameters.Clear ( );
cmdm.Parameters.Add ( "@.symbol", SqlDbType.VarChar, 50, "symbol" );
cmdm.Parameters.Add ( "@.cDate", SqlDbType.VarChar, 50, "cDate" );
cmdm.Parameters.Add ( "@.part", SqlDbType.VarChar, 50, "part" ); // 2
cmdm.Parameters.Add ( "@.collection", SqlDbType.VarChar, 500, "collection" ); // 3
foreach ( DataRow row in Globals.dtSets.Tables[tableIndex].Rows )
{
cmdm.Parameters[0].Value = ( string )row[0];
cmdm.Parameters[1].Value = ( string )row[1];
cmdm.Parameters[2].Value = ( string )row[2];
cmdm.Parameters[3].Value = ( string )row[3];
Console.WriteLine ( "Saving {0}", ( string )row[3] ); // <= where the print out occurs to confirm that the right string is being passed
try
{
cmdm.ExecuteNonQuery ( );
}
catch { }
}
}
}
} // saveHistTickMinMaxAnalysis

Table is defined with the last columns[3].Length = 500;

What happened to the rest of my strings?

I would appreciate any help.

Many thanks.

Your parameter in your procedure is only VARCHAR(50), therefor it is implictly cut to 50 chars.

Jens K. Suessmeyer


http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

Your parameter in your procedure is only VARCHAR(50), therefor it is implictly cut to 50 chars.

Jens K. Suessmeyer


http://www.sqlserver2005.de

Thank you Jens.

I actually figured it out right after I posted the message but in the end I did a complete workaround even without SQL Server. Probably was very tired that day. Goofy!