Calculated Column performance

Hi,
Is calulation more efficent in the SQL CODE or in a Calculated Column? If
I'm correct the Calculated Column is done on the client SELECT query every
time where as my INSERT TSQL code will only do it once on the INSERT?
Thanks
DECLARE
@.ACCDCC_Table TABLE
(cnt INT NULL,
Time INT NULL,
Location FLOAT NULL,
FPM1 FLOAT NULL,
FPM2 FLOAT NULL,
FPM_Diff AS (
CASE
WHEN
FPM1 IS NULL OR
FPM2 IS NULL THEN NULL
ELSE
FPM2 - FPM1
END),
AccDcc VARCHAR(10) NULL
)
as per doing this:
UPDATE
@.ACCDCC_Table
SET
FPM_Diff = FPM2 - FPM1
WHERE
FPM_Diff IS NULL
--
don> Is calulation more efficent in the SQL CODE or in a Calculated Column? If
> I'm correct the Calculated Column is done on the client SELECT query every
> time where as my INSERT TSQL code will only do it once on the INSERT?
That's correct.
The flip side is that you will have to constantly maintain the value in the
"calculated" column if you're going to rely on doing it manually.
A|||Thanks
"AB - MVP" wrote:

> That's correct.
> The flip side is that you will have to constantly maintain the value in th
e
> "calculated" column if you're going to rely on doing it manually.
> A
>
>|||Think you meant you have to maintain it when you are doing it once, on
update. If it's calculated automatically, every time you "select" the
column, the value is not being stored in database, It's being re-calculated
every time you do a select, so there's no maintenance required.
Which is better depends on whether you need
A) Insert/Update Performance, and/Or storage Size Constraints -- Use
Calculated Column, or
B) Select Performance is the main concern -- Then Use persisted Column and
maintain it upon every Insert/Update
"AB - MVP" wrote:

> That's correct.
> The flip side is that you will have to constantly maintain the value in th
e
> "calculated" column if you're going to rely on doing it manually.
> A
>
>|||Thanks
"CBretana" wrote:
> Think you meant you have to maintain it when you are doing it once, on
> update. If it's calculated automatically, every time you "select" the
> column, the value is not being stored in database, It's being re-calculate
d
> every time you do a select, so there's no maintenance required.
> Which is better depends on whether you need
> A) Insert/Update Performance, and/Or storage Size Constraints -- Use
> Calculated Column, or
> B) Select Performance is the main concern -- Then Use persisted Column and
> maintain it upon every Insert/Update
> "AB - MVP" wrote:
>|||I guess someone missed the meaning of "quotes" around "calculated"... <sigh>
"donron" <donron@.discussions.microsoft.com> wrote in message
news:2CCDA1CD-B43E-4D0E-AEFF-6C77615F5755@.microsoft.com...
> Thanks
> "CBretana" wrote:
>|||that would be me... but rereading, (I may be just dense this am), but I'm
still not sure what you mean by it... I thought it was just a typo...
"AB - MVP" wrote:

> I guess someone missed the meaning of "quotes" around "calculated"... <sig
h>
>
>
> "donron" <donron@.discussions.microsoft.com> wrote in message
> news:2CCDA1CD-B43E-4D0E-AEFF-6C77615F5755@.microsoft.com...
>
>

Calculate in report or in stored procedure

I have a requirement for two reports, which are basically except that one
shows values, whereas the other shows percentages. I would like to code this
as one report that modifies its display behaviour based on a parameter that
is passed in ( value 'P' or 'V').
The format of the cells in the grid will be an expression, giving integer
for values and one decimal place for percenatges.
If the percentages version is requested I can do the percentage calculation
in one of two places...
1. in the report with an expression against the cell Value.
2. in the stored procedure so that percentages are returned.
Which is best practice? If ReportingServices is running on the same SQL
Server as the source database is there a preference for overall performance?
Thanks,
AndrewHello Andrew,
Calculating in Report will run the calculating works in the
ReportingService process.
My suggestion is that you could calculating in the stored procedure which
is faster.
Also for the performance consideration, you could use the Report Cache.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
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.|||On Jun 4, 10:28 am, Duke (AN247) <D...@.newsgroup.nospam> wrote:
> I have a requirement for two reports, which are basically except that one
> shows values, whereas the other shows percentages. I would like to code this
> as one report that modifies its display behaviour based on a parameter that
> is passed in ( value 'P' or 'V').
> The format of the cells in the grid will be an expression, giving integer
> for values and one decimal place for percenatges.
> If the percentages version is requested I can do the percentage calculation
> in one of two places...
> 1. in the report with an expression against the cell Value.
> 2. in the stored procedure so that percentages are returned.
> Which is best practice? If ReportingServices is running on the same SQL
> Server as the source database is there a preference for overall performance?
> Thanks,
> Andrew
My personal preference is to do as much as possible in the stored
procedure. As far as performance gain, it depends on the size of the
returned dataset to the report and how complicated the expressions are
in the report. That said, you should be able to use some fairly simple
expressions to achieve the results you are looking for. So you could
use an expression similar to this in the Format property:
=iif(Parameters!ParameterName.Value = "P", #,0.0%, #,0)
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks. I'd been leaning towards doing as much in the sproc as possible which
ties in with your answers. So, I'll calculate the percentage in the sproc
and the format in the report.
Cheers,
Andrew|||On Jun 5, 6:03 am, Duke (AN247) <D...@.newsgroup.nospam> wrote:
> Thanks. I'd been leaning towards doing as much in the sproc as possible which
> ties in with your answers. So, I'll calculate the percentage in the sproc
> and the format in the report.
> Cheers,
> Andrew
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Calculate Checksum for Class 128 Barcode

We use a fixed length class 128 barcode and I would like to generate a
calculated table that assembles the barcode including start code, stop code
and checksum. Has anyone done this? Any hints?
Thanks
IT
PHYTOSANIT PHYTOSAN wrote:
> We use a fixed length class 128 barcode and I would like to generate a
> calculated table that assembles the barcode including start code,
> stop code and checksum. Has anyone done this? Any hints?
> Thanks
> IT
> PHYTOSAN
See if this helps:
http://www.idautomation.com/code128faq.html
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I will look into that, thank's for your help!
IT PHYTOSAN
"David Gugick" wrote:
> IT PHYTOSAN wrote:
> > We use a fixed length class 128 barcode and I would like to generate a
> > calculated table that assembles the barcode including start code,
> > stop code and checksum. Has anyone done this? Any hints?
> >
> > Thanks
> >
> > IT
> >
> > PHYTOSAN
> See if this helps:
> http://www.idautomation.com/code128faq.html
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Calculate Checksum for Class 128 Barcode

We use a fixed length class 128 barcode and I would like to generate a
calculated table that assembles the barcode including start code, stop code
and checksum. Has anyone done this? Any hints?
Thanks
IT
PHYTOSANIT PHYTOSAN wrote:
> We use a fixed length class 128 barcode and I would like to generate a
> calculated table that assembles the barcode including start code,
> stop code and checksum. Has anyone done this? Any hints?
> Thanks
> IT
> PHYTOSAN
See if this helps:
http://www.idautomation.com/code128faq.html
David Gugick
Quest Software
www.imceda.com
www.quest.com|||I will look into that, thank's for your help!
IT PHYTOSAN
"David Gugick" wrote:

> IT PHYTOSAN wrote:
> See if this helps:
> http://www.idautomation.com/code128faq.html
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Calculate Checksum for Class 128 Barcode

We use a fixed length class 128 barcode and I would like to generate a
calculated table that assembles the barcode including start code, stop code
and checksum. Has anyone done this? Any hints?
Thanks
IT
PHYTOSAN
IT PHYTOSAN wrote:
> We use a fixed length class 128 barcode and I would like to generate a
> calculated table that assembles the barcode including start code,
> stop code and checksum. Has anyone done this? Any hints?
> Thanks
> IT
> PHYTOSAN
See if this helps:
http://www.idautomation.com/code128faq.html
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||I will look into that, thank's for your help!
IT PHYTOSAN
"David Gugick" wrote:

> IT PHYTOSAN wrote:
> See if this helps:
> http://www.idautomation.com/code128faq.html
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Caching of inline queries

Hi,
I am trying to evaluate the benefits of writing stored procedures over
using inline SQL code using ADO.net and SqlServer. I am coming from an
oracle background, and am trying to understand under what
circumstances SqlServer will recompile my query, and when it will use
a pre-compiled version. The Oracle term for this behavior is
'bind-variable'. The only Oracle/bind-variable related information I
found here was
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=e0dtNgY%24CHA.2572%40TK2MSFTNGP11.phx.gbl
It seems to indicate that in order to avoid recompilation that I could
use stored procedures, format my queries using sp_executesql, or use
the prepared statement in my command object and reuse the object.
I am confused because by examining the syscacheobjects, it seems my
parameterized query is cached and reused automatically.
Here is what I am doing to test this. I have some code that looks like
this:
SqlConnection MyConnection = new SqlConnection(ConnectionString);
MyConnection.Open();
SqlCommand MyCommand = new SqlCommand("SELECT GStudentId, Lastname,
FirstName FROM student WHERE GStudentId=@.StudentId", MyConnection);
MyCommand.Parameters.Add("@.StudentId", _GStudentId);
SqlDataReader MyReader = MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
I call this code on a simple web page, which lets me easily execute it
again and again.
I then do the following query inside Query Analyzer:
select usecounts, sql from syscacheobjects
where sql like '%SELECT GStudentId, Lastname, FirstName FROM student
WHERE GStudentId=@.StudentId%'
As I execute the page over and over the use count goes up.
Syscacheobjects actually returns two rows, one with a cached object
type of ?Executable Plan' (this is the one with the ?incrementing
usecounts'), and one with a type of ?Compiled Plan'. What are these
each used for?
Doesn't this mean that Sql Server is caching the query? I am
recreating the command object from scratch each time, yet it seems to
use the same "cached execution plan".
I am very happy with this behavior, yet it is in contradiction to the
other information I was able to find.
Can someone validate?
Thanks!
DaveSQL Server will do SIMPLE parameter substitution to reuse an ad-hoc plan.
That's probably what's happening in your example, but this will only happen
in trivial cases. You're better off with a proc us sp_executesql.
This paper is a good starting point for understanding SQL Server
behaivior...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_queryrecompilation.asp
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"HaukiDog" <HaukiDog@.hotmail.com> wrote in message
news:6d3bc6d9.0311241301.58817947@.posting.google.com...
> Hi,
> I am trying to evaluate the benefits of writing stored procedures over
> using inline SQL code using ADO.net and SqlServer. I am coming from an
> oracle background, and am trying to understand under what
> circumstances SqlServer will recompile my query, and when it will use
> a pre-compiled version. The Oracle term for this behavior is
> 'bind-variable'. The only Oracle/bind-variable related information I
> found here was
>
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=e0dtNgY%24CHA.2572%40TK2MSFTNGP11.phx.gbl
> It seems to indicate that in order to avoid recompilation that I could
> use stored procedures, format my queries using sp_executesql, or use
> the prepared statement in my command object and reuse the object.
> I am confused because by examining the syscacheobjects, it seems my
> parameterized query is cached and reused automatically.
> Here is what I am doing to test this. I have some code that looks like
> this:
> SqlConnection MyConnection = new SqlConnection(ConnectionString);
> MyConnection.Open();
> SqlCommand MyCommand = new SqlCommand("SELECT GStudentId, Lastname,
> FirstName FROM student WHERE GStudentId=@.StudentId", MyConnection);
> MyCommand.Parameters.Add("@.StudentId", _GStudentId);
> SqlDataReader MyReader => MyCommand.ExecuteReader(CommandBehavior.CloseConnection);
> I call this code on a simple web page, which lets me easily execute it
> again and again.
> I then do the following query inside Query Analyzer:
> select usecounts, sql from syscacheobjects
> where sql like '%SELECT GStudentId, Lastname, FirstName FROM student
> WHERE GStudentId=@.StudentId%'
> As I execute the page over and over the use count goes up.
> Syscacheobjects actually returns two rows, one with a cached object
> type of 'Executable Plan' (this is the one with the 'incrementing
> usecounts'), and one with a type of 'Compiled Plan'. What are these
> each used for?
> Doesn't this mean that Sql Server is caching the query? I am
> recreating the command object from scratch each time, yet it seems to
> use the same "cached execution plan".
> I am very happy with this behavior, yet it is in contradiction to the
> other information I was able to find.
> Can someone validate?
> Thanks!
> Dave

Caching ForeignKey Collections & StoredProcedure Collections

I have a code generator for sql server databases, and I want to switch it over to using SMO instead of my custom built queries, but it's just painfully slow right now. With my custom stuff, I get complete generation in about 5 seconds, with SMO, it runs in about 12 minutes ( I got this down from about 45 minutes on the first run by setting my defaultinitfields to everything, and prefetching everything).

90% of the time is taken up in looking up the parameters for storedprocedures, as well as looking at the foreign keys of a table.

You can't set defaultinitfields on collections ( at least not that I can figure out), so what can I do?

Come on guys, this is supposedly a professional product. It's been almost two weeks and not a single developer has cared to provide a response?

And no, this isn't a bump, it's someone that's trying to get work done with something that looks to be completely inadequate, and hasn't received a bit of attention.

-- Colin

C++ SSCE example

Hi

Does anyone have an example code in C++ to create a SSCE db and access data?

Many thanks

Ozzie

Install the developer tools SDK. You you find at the following location assuming default installation path.

C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\SDK\Samples\NorthwindOleDb.zip

This is a device sample, but you can port it to desktop. Or you can copy paste code, the code for creating/opening and other database operations would be the same for device & desktop.

|||I have attempted to run the NorthwindOleDb project. The project builds without errors and when I start it it opens in the Pocket PC 2003 emulator but fails when trying to create the dialog to display employee info and fails at the line:
hr = InitDatabase();
I have SSCE dll's installed, the SSCE SDK and VS 2005 SP1. Any suggestions would be greatly appreciated. Im sure this should just work straight out of the box.

Ozzie

C++ code sample for working with Inegration Service

Hi All,

Are there samples for working with Inegration Service ?

As I see in shipped samples are C# and VB only.

Particulary I need a C++ code for "Enumerating Available Packages Programmatically"

Thanks a lot.

Sergiy

The code is very similar to C# code, except that instead of using C# classes you should use COM interfaces, declared in DTS.H (from C:\Program Files\Microsoft SQL Server\90\SDK\Include).
Here is some code to get you started (I've skipped any error checking for clarity):

#include "dts.h"

void EnumPackages()
{
CComPtr<IDTSApplication90> app;
CComPtr<IDTSPackageInfos90> pkgInfos;

app.CoCreateInstance(__uuidof(Application));
app->GetDtsServerPackageInfos(CComBSTR(L"File System"), CComBSTR(L"."), &pkgInfos);

LONG count;
pkgInfos->get_Count(&count);

printf("found %d packages", count);
}

int _tmain(int argc, _TCHAR* argv[])
{
CoInitializeEx(NULL, COINIT_MULTITHREADED);

EnumPackages();

CoUninitialize();
return 0;
}

If you like VC build-in COM support classes, you may #import <dts.tlb> instead of #include <dts.h> - this gives you better wrappers and HRESULT-to-exception translation.

|||

HiMichael,

Thanks a lot for answer. It really help.

Sergiy

C++ code sample for working with Inegration Service

Hi All,

Are there samples for working with Inegration Service ?

As I see in shipped samples are C# and VB only.

Particulary I need a C++ code for "Enumerating Available Packages Programmatically"

Thanks a lot.

Sergiy

The code is very similar to C# code, except that instead of using C# classes you should use COM interfaces, declared in DTS.H (from C:\Program Files\Microsoft SQL Server\90\SDK\Include).
Here is some code to get you started (I've skipped any error checking for clarity):

#include "dts.h"

void EnumPackages()
{
CComPtr<IDTSApplication90> app;
CComPtr<IDTSPackageInfos90> pkgInfos;

app.CoCreateInstance(__uuidof(Application));
app->GetDtsServerPackageInfos(CComBSTR(L"File System"), CComBSTR(L"."), &pkgInfos);

LONG count;
pkgInfos->get_Count(&count);

printf("found %d packages", count);
}

int _tmain(int argc, _TCHAR* argv[])
{
CoInitializeEx(NULL, COINIT_MULTITHREADED);

EnumPackages();

CoUninitialize();
return 0;
}

If you like VC build-in COM support classes, you may #import <dts.tlb> instead of #include <dts.h> - this gives you better wrappers and HRESULT-to-exception translation.

|||

Hi Michael,

Thanks a lot for answer. It really help.

Sergiy

C# with ADO 2.8: Receiving "Current Recordset does not support updating"

Hi All,

I was using VB6 to access a MS SQL Server database. The code worked and works fine. I then decided to migrate the code to C#.Net 2005 using ADO 2.8 (not ADO.Net). Doing that yields with the same exact code the error message, "Current Recordset does not support updating".

I did a whole bunch of Google searches and didn't see anything useful. Mainly the advice from Microsoft and others is to make sure the mode on the connection string is set to "ReadWrite", as the default is "Read Only" and to make sure to set the lock type to either optimistic or pessimistic. Still others said that the code should set the CursorLocation property of the recordset.

I can safely say that I have been setting the mode to "Read/Write" since the start and have played around with the lock type, cursor location, and open method. Nothing works on C#, BUT VB6 is so totally happy with everything.

The provider works fine, as VB6 works fine, and the lock type is also fine, so therefore the built in suggestions do not apply.

My code is:

// Connection string template. Filled in properly in real code.
strConnect = "Server={0};Database={1};"

// Set the connection properties.
this.SQLConnection.ConnectionString = strConnect;
this.SQLConnection.Provider = "SQLOLEDB";
this.SQLConnection.Mode = adModeReadWrite;

// Open the connection.
this.SQLConnection.Open(strConnect, strUserName, strPassword, -1);

=================

// Create the ADO objects needed.
dbRSAdd = new Recordset();

// Open the recordset.
dbRSAdd.Open(strTable, dbCatalog.ActiveConnection, CursorTypeEnum.adOpenDynamic, LockTypeEnum.adLockOptimistic, (int)CommandTypeEnum.adCmdTable);

// Cycle through each record to add.
dbRS.MoveFirst();
for (lRecord = 0; lRecord < dbRS.RecordCount; lRecord++)
{
// Add a new record.
dbRS.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value);

...
}

// NOTE: The code crashes with the call to 'AddNew'.

Any advice?Hi All,

(Head between my legs.) I can't tell you how long I looked at the code only to look at the code on this post and immediately see the problem. I had two record sets. The first record set (dbRS) is read only. I made the stupid mistake of not putting dbRSAdd with the AddNew. VB6 uses a 'With' statement, whereas C# does not support a 'With' statement. My problem was that I wasn't careful in copying and pasting, when filling in the prefix before the '.'.

Oops!

C# UDF project call C++ model (SQL Server 2005)?

I have some legacy C++ code and I am creating a C# project for UDF function
and another project for C++ classes. I always got error message when I am
trying to add reference to the class lib project:
A reference to 'classModel' could not be added. SQL Server projects can
reference only other SQL Server projects.
I tried to create the C++ project as SQL Server project too and the error
message is the same.examnotes <nick@.discussions.microsoft.com> wrote in
news:E39C7050-FC85-425C-91B3-B76040D2B164@.microsoft.com:

> I have some legacy C++ code and I am creating a C# project for UDF
> function and another project for C++ classes. I always got error
> message when I am trying to add reference to the class lib project:
> A reference to 'classModel' could not be added. SQL Server projects
> can reference only other SQL Server projects.
That is because the VS SQL Server Project doesn't allow you to reference
any other project types (or assemblies already defined in the database).
You can instead use my project type for this:
http://staff.develop.com/nielsb/Per...b8d3-4ace-a54e-
26411f9eac09.aspx (watch out for linebreaks). However, in this scenario
I wonder if that is the real problem, see below.

> I tried to create the C++ project as SQL Server project too and the
> error message is the same.
OK, so is the C++ project managed code? If not you can not use it inside
SQL Server. In that case you have to either do COM interop against the
C++ classes, or P/Invoke.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb at develop dot com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||IC, thanks.
I avoid to create the UDF in C++(Managed) because not much example, support
information about C++ user defined function programming. And I am not famila
r
with managed C++ syntax.
"Niels Berglund" wrote:

> examnotes <nick@.discussions.microsoft.com> wrote in
> news:E39C7050-FC85-425C-91B3-B76040D2B164@.microsoft.com:
>
> That is because the VS SQL Server Project doesn't allow you to reference
> any other project types (or assemblies already defined in the database).
> You can instead use my project type for this:
> http://staff.develop.com/nielsb/Per...b8d3-4ace-a54e-
> 26411f9eac09.aspx (watch out for linebreaks). However, in this scenario
> I wonder if that is the real problem, see below.
>
> OK, so is the C++ project managed code? If not you can not use it inside
> SQL Server. In that case you have to either do COM interop against the
> C++ classes, or P/Invoke.
> Niels
>
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb at develop dot com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>

c# Stored Procedure / OpenXML Failure - Can Anyone Help..??

Hi,

I have some c# code which calls a SP which is erroring Basically I pass in a XML string which can be upto 5 MB is size (not sure about overflow issues here), which then calls a SP which inserts the data into a SQL table.

The c# code is as follows:

---C#-------

SqlConnection conn =new SqlConnection(DBConn);

using(StreamReader sr =new StreamReader(xmlLocationString))

{

try

{

string @.xmlInput = sr.ReadToEnd();

SqlCommand cmd =new SqlCommand();

cmd.Connection=conn;

cmd.CommandText = "[AddArgentinaTrades]";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new System.Data.SqlClient.SqlParameter("@.xmlInput", SqlDbType.Text,5120000));

cmd.Parameters["@.xmlInput"].Direction = ParameterDirection.Output;

conn.Open();

cmd.ExecuteNonQuery();

}

catch(SqlException SqlExp)

{

Console.WriteLine(SqlExp.Message);

}

finally

{

conn.Close();

sr.Close();

}

}

------Stored Proc--------

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'AddArgentinaTrades' AND Type ='P')
DROP PROCEDURE AddArgentinaTrades
GO

CREATE PROCEDURE AddArgentinaTrades
@.xmlInput as text
AS

Declare @.idoc int

EXEC master.dbo.sp_xml_preparedocument @.idoc OUTPUT, @.xmlInput

INSERT INTO MarketRiskdev.dbo.Import_Argentina
SELECT un_cid, tnum, snum, cid, entityid, ctype, why, comp, oc, bs, ae, cp, trd_date, set_date, mat_date, val_date,
trader, famt, price, coupon, next_coupon, last_coupon, cpnfreq, cpnrate, cpntype, daycounttype, exch_notion,
contract_spot, base_cur, year_basis, buy_currency, buy_amount, sell_currency, sell_amount, [timestamp] FROM OPENXML(@.idoc, 'ArgentinaInputFile/Data',2)
WITH (un_cid varchar(50), tnum nvarchar(50), snum nvarchar(50), cid varchar(50), entityid varchar(50), ctype varchar(50),
why varchar(50),
comp varchar(50),
oc varchar(50),
bs varchar(50),
ae varchar(50),
cp varchar(50),
trd_date datetime,
set_date datetime,
mat_date datetime,
val_date datetime,
trader varchar(50),
famt float(8),
price float(8),
coupon float(8),
next_coupon datetime,
last_coupon datetime,
cpnfreq int,
cpnrate float,
cpntype int,
daycounttype smallint,
exch_notion smallint,
contract_spot float(8),
base_cur varchar(50),
year_basis int,
buy_currency varchar(50),
buy_currency varchar(50),
buy_amount float(8),
sell_currency varchar(50),
sell_amount float(8),
[timestamp] varchar(50))

EXEC master.dbo.sp_xml_removedocument @.idoc

GO

Error Msg:

A severe error occurred on the current command. The results, if any, should be
discarded.

Can anyone help here as I have no idea. I have tried reducing the size of XML to 5KB and still get the same error??

I don't think it is the size of your xml file, try the link below to modify your SQL statement. Hope this helps.

http://msdn.microsoft.com/msdnmag/issues/05/06/DataPoints/default.aspx

C# Sql easy help

if i have the following code, how do i know if the transaction was actually successful, so basically where in this code can i write TRANSACTION SUCCESFUL?

try
{
//Open up the connection
conn.Open();
//Setup the Transaction
trans = conn.BeginTransaction();
//First query in transaction
pre_query = "Delete from Menu where spec_name="
+ "'"
+ spec_name_array[i].Text.ToString()
+ "'" ;
//Second query in transaction
query = "INSERT into Menu VALUES ('"
+ Calendar1.SelectedDate
+"','"
+ spec_name_array[i].Text.ToString()
+"','"
+ spec_desc_array[i].Text.ToString()
+"','"
+spec_price_array[i].Text.ToString()
+"',1)";
SqlCommand comm = new SqlCommand(pre_query,conn);
//Setup the command to handle transaction
comm.Transaction = trans;
//Execute first query
comm.ExecuteNonQuery();
//Add in second query
comm.CommandText = query;
//Execute second query
comm.ExecuteNonQuery();
trans.Commit();
}
catch(SqlException ex)
{
com_label.Text = "Submission Not complete, did you forget the date or something else?";
//Undo all queries
trans.Rollback();
}
finally
{
conn.Close();
}

make some bool variable
and set it to false in catch
otherwise it is successfull.

Personally I suggest you to do the whole thing in the stored procedure

C# render method error - invalid arguements

I have the following c# code for rendering reports. The VB equivilant
works perfect but this example bombs on the render method:
ReportingService rs = new ReportingService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
DataSourceCredentials[] credentials = null;
byte[] result = null;
string reportPath = "/Capital Request/Request";
string historyID = null;
string format = "PDF";
string devInfo = "<DeviceInfo><HTMLFragment>false</HTMLFragment><Zoom>100</Zoom><Toolbar>FALSE</Toolbar></DeviceInfo>";
string showHideToggle = null;
string encoding;
string mimeType;
Warning[] warnings = null;
ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;
SessionHeader sh = new SessionHeader();
rs.SessionHeaderValue = sh;
try
{
result = rs.Render(reportPath, format, historyID,
devInfo, parameters, credentials,
showHideToggle, out encoding, out mimeType,
out reportHistoryParameters, out warnings,
out streamIDs);
sh.SessionId = rs.SessionHeaderValue.SessionId;
}
The error is as follows:
CS1502: The best overloaded method match for
'RSProxy.ReportingService.Render(string, string, string, string,
RSProxy.ParameterValue[], RSProxy.DataSourceCredentials[], string, ref
string, ref string, ref RSProxy.ParameterValue[], ref
RSProxy.Warning[], ref string[])' has some invalid arguments
I have tried everything. Any help is appreciated.use the ref keyword in place of the out keyword.
"Stephen" <switter@.enpathmed.com> wrote in message
news:1104010447.834507.213950@.c13g2000cwb.googlegroups.com...
> I have the following c# code for rendering reports. The VB equivilant
> works perfect but this example bombs on the render method:
> ReportingService rs = new ReportingService();
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> DataSourceCredentials[] credentials = null;
> byte[] result = null;
> string reportPath = "/Capital Request/Request";
> string historyID = null;
> string format = "PDF";
> string devInfo =>
"<DeviceInfo><HTMLFragment>false</HTMLFragment><Zoom>100</Zoom><Toolbar>FALS
E</Toolbar></DeviceInfo>";
> string showHideToggle = null;
> string encoding;
> string mimeType;
> Warning[] warnings = null;
> ParameterValue[] reportHistoryParameters = null;
> string[] streamIDs = null;
> SessionHeader sh = new SessionHeader();
> rs.SessionHeaderValue = sh;
> try
> {
> result = rs.Render(reportPath, format, historyID,
> devInfo, parameters, credentials,
> showHideToggle, out encoding, out mimeType,
> out reportHistoryParameters, out warnings,
> out streamIDs);
> sh.SessionId = rs.SessionHeaderValue.SessionId;
> }
> The error is as follows:
> CS1502: The best overloaded method match for
> 'RSProxy.ReportingService.Render(string, string, string, string,
> RSProxy.ParameterValue[], RSProxy.DataSourceCredentials[], string, ref
> string, ref string, ref RSProxy.ParameterValue[], ref
> RSProxy.Warning[], ref string[])' has some invalid arguments
> I have tried everything. Any help is appreciated.
>|||That was it! At least in part. The only other thing is the encoding
and mimetype variables were not set:
string showHideToggle = null;
string encoding;
string mimeType;
I got an unassigned variable error so I did the following:
string showHideToggle = null;
string encoding=null;
string mimeType=null;
and it worked. This example came straight out of BOL. Seems there is
a problem with the example. Thats for correcting that for me. I
really appreciate it as I have been working on this for about two
weeks.
Tim Ellison wrote:
> use the ref keyword in place of the out keyword.
> "Stephen" <switter@.enpathmed.com> wrote in message
> news:1104010447.834507.213950@.c13g2000cwb.googlegroups.com...
> > I have the following c# code for rendering reports. The VB
equivilant
> > works perfect but this example bombs on the render method:
> >
> > ReportingService rs = new ReportingService();
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
> > DataSourceCredentials[] credentials = null;
> >
> > byte[] result = null;
> > string reportPath = "/Capital Request/Request";
> > string historyID = null;
> > string format = "PDF";
> > string devInfo => >
>
"<DeviceInfo><HTMLFragment>false</HTMLFragment><Zoom>100</Zoom><Toolbar>FALS
> E</Toolbar></DeviceInfo>";
> > string showHideToggle = null;
> > string encoding;
> > string mimeType;
> > Warning[] warnings = null;
> > ParameterValue[] reportHistoryParameters = null;
> > string[] streamIDs = null;
> > SessionHeader sh = new SessionHeader();
> > rs.SessionHeaderValue = sh;
> >
> > try
> > {
> > result = rs.Render(reportPath, format, historyID,
> > devInfo, parameters, credentials,
> > showHideToggle, out encoding, out mimeType,
> > out reportHistoryParameters, out warnings,
> > out streamIDs);
> > sh.SessionId = rs.SessionHeaderValue.SessionId;
> > }
> >
> > The error is as follows:
> >
> > CS1502: The best overloaded method match for
> > 'RSProxy.ReportingService.Render(string, string, string, string,
> > RSProxy.ParameterValue[], RSProxy.DataSourceCredentials[], string,
ref
> > string, ref string, ref RSProxy.ParameterValue[], ref
> > RSProxy.Warning[], ref string[])' has some invalid arguments
> > I have tried everything. Any help is appreciated.
> >

C# code to attach database to sql express

i need this help , i am trying but its not getting attached

in command lprompt its working good but comming to c# code its not working

first i am copying database and log file to data folder then i used this code

string AttachCommand = @."CREATE DATABASE myrokero ON ( FILENAME = N '"+sqlPath+"myrokero.mdf' ),( FILENAME = N'"+sqlPath+"myrokero.ldf' ) FOR ATTACH ";

processStart = new ProcessStartInfo("sqlcmd.exe ", " -E -q " + AttachCommand.ToString());

processStart.UseShellExecute = false;

processStart.RedirectStandardOutput = true;

processStart.RedirectStandardError = true;

processStart.CreateNoWindow = true;

process = Process.Start(processStart);

process.WaitForExit();

Didi you debug your command to execute to see if the executed SQL is the same as your sql executed in QA with the right syntax ? DId you then try to attach the database with the debbuged code to see if that works ? If so and it worked, there could be probably a security issue within the Process you are starting, try to start that with SQL Authentication. Or try to impersonate the process with the current credentials of the current Windows identity.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

C# code or document for loading excel sheet into an sql table

Hi,

I am trying to find some document or code that will load an excel spreadsheet into an sqlserver database.

Can anyone please point me in the right direction.http://www.databasejournal.com/features/mssql/article.php/3331881|||Thanks a lot for your help I will take a look at this now.

C# code in rs?

Hello.
I wrote somw code in VB in the code section of a report.
I saw in a few places thet u can wrtie in C#.

Is it true? When I tried I got an error.

Thanks
From what I understand, any coding done in the report has to be in VB format. You cannot do code in C#.|||Hi.

I saw on the web a few places that used C# sharp

Maybe there are some special configuration for it?
|||I know that somehow you can reference and call external class files that have been written in C#, but for custom code in the report, it has to be VB. This link might help - http://msdn2.microsoft.com/en-us/library/ms155798.aspx

C# code for saving data from excel to mssql database

Hello everyone,

I am trying to find some code or documentation that I can use to create a web page that will save data from an excel file to a mssql databaseAfter you save the Excel file, open it and read records from it using this article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;316934

And then use normal ADO.NET procedures to update the SQL Server database.|||Thanks a lot I will take a look at this article now|||Thanks for the tip all worked well on my local machine but when I uploaded it to an external server and tested I keep getting this error:

System.Data.OleDb.OleDbException: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data. at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider() at System.Data.OleDb.OleDbConnection.Open() at summitPortal.controls.bulk.insertdata(String sSheetPath) at summitPortal.controls.bulk.ImageButton1_Click(Object sender, ImageClickEventArgs e) at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) at System.Web.UI.WebControls.ImageButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain()

The file is definately not in use because I even restarted the computer and tried again.
I also checked the permissions and they seem to be ok.

Any ideas anyone?|||I'm not so sure about this but did you check yourExcel file is read-only or not? sometimes I get some error when I open read-only files. anyway, I will try what you're doin right now.|||Check out this Microsoft troubleshooting article:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q306269

C# code convert to SSIS

Hi, all

I've seach the threads before. a lot of discussion about C# to VSA.

So could anyone with experience on conversion from C# to SSIS/VSA give me a suggestion?

I 've C# code application which complete some ETL process. Now I need to convert all the ETL process to SSIS pacakge. the function of part of C# code is to get the result from stored procedure and then write the result to excel file, not row by row.

the C# code call Microsoft.Office.Interop.Excel as a object to write excel file.

But in VSA, I can't add Microsoft.Office.Interop.Excel as a reference so I can convert C# to VB.net regarding writing excel file.

So any solution about that?

any help will be appreciated.........

To add a reference to an assembly from wit5hin VSA, it needs to be in a special folder - %windir%\Microsoft.net\framework\v2.0.xxxxx

You should also make sure it is in the GAC, as this requried at run-time.

If you have C# code already, perhaps it would be simpler to wrap this in a custom task, staying with C#, rather than swapping to VB.Net and using the script task.

Samples available from MS, and good references in Books Online for building tasks.

Is automating excel from an unattended process support yet? It was never a good idea last time I looked, and was best accompanied with a regularly scheduled "kill excel.exe" command!

|||

Darren, thanks for you response.

add a reference to assembly - based on the discussion on http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=19740&SiteID=1

it seems that no way to add, that seems the limitation of VSA.

"wrap this in a custom task" that means in the Toolbox of SSIS, there are a component named custom task? I am sorry I am fresh to SSIS. could you help to point to the location of the samples from MS or books online?

thanks.

|||

If you copy the file to the correct folder, it will then appear in the Add Reference dialog. Have you copied the assembly to the folder? The whole thread is about how you CAN add references, but how daft the method it, so it is possible, but you need to copy the file.

Referencing Other Assemblies in Scripting Solutions
(http://msdn2.microsoft.com/en-us/library/9b655bcd-19f6-43d8-9f89-1b4d299c6380.aspx)

Tasks appear in the toolbox. You can write yoru own task and add it to the toolbox. Tasks can be written in any .Net language, and obviously they are not written in VSA, but a full development tool such as Visual Studio.

Extending Packages with Custom Objects
(http://msdn2.microsoft.com/en-us/library/26616eb8-9e80-434d-b22a-ece1b00f449d.aspx)

Developing a Custom Task
(http://msdn2.microsoft.com/en-us/library/dcbd8615-fa6d-4ddb-b8a5-0b19dddd6239.aspx)

|||

Hi, Darren

for the first question "add reference".

I think I should follow the apporach "Using a Custom Managed Assembly"

which is in the URL you copied to me.

So I should copy Microsoft.Office.Interop.Excel which locates on my "c:\assembly" to %windir%\Microsoft.NET\Framework\v2.0.xxxxx, right?

Acually, this file can't be copied, no copy option when you right click.............