calculated field question

I'm trying to do a calculation on a couple of number and
return a decimal number. An example of what I'm trying
to do is:
select (44-2)/44
How can I make this return 0.96
TIA,
Vic
Hi,
select round((44-2)/44.0,2)
Thanks
Hari
MCDBA
"Vic" <vduran@.specpro-inc.com> wrote in message
news:2472501c45f6c$b91a1ee0$a401280a@.phx.gbl...
> I'm trying to do a calculation on a couple of number and
> return a decimal number. An example of what I'm trying
> to do is:
> select (44-2)/44
> How can I make this return 0.96
> TIA,
> Vic

calculated field question

I'm trying to do a calculation on a couple of number and
return a decimal number. An example of what I'm trying
to do is:
select (44-2)/44
How can I make this return 0.96
TIA,
VicHi,
select round((44-2)/44.0,2)
Thanks
Hari
MCDBA
"Vic" <vduran@.specpro-inc.com> wrote in message
news:2472501c45f6c$b91a1ee0$a401280a@.phx
.gbl...
> I'm trying to do a calculation on a couple of number and
> return a decimal number. An example of what I'm trying
> to do is:
> select (44-2)/44
> How can I make this return 0.96
> TIA,
> Vicsql

calculated field question

I'm trying to do a calculation on a couple of number and
return a decimal number. An example of what I'm trying
to do is:
select (44-2)/44
How can I make this return 0.96
TIA,
VicHi,
select round((44-2)/44.0,2)
--
Thanks
Hari
MCDBA
"Vic" <vduran@.specpro-inc.com> wrote in message
news:2472501c45f6c$b91a1ee0$a401280a@.phx.gbl...
> I'm trying to do a calculation on a couple of number and
> return a decimal number. An example of what I'm trying
> to do is:
> select (44-2)/44
> How can I make this return 0.96
> TIA,
> Vic

Calculated field in Footer...running total

Hello,

How do I add unique values on the report? For example say I have this in my report:

Customer: Food Purchased: Amount:

Judy Cat Food $12

Sarah Dog Food $13.50

Diane Rabbit Food $17

Jason Dog Food $16

Tammy Dog Food $15

In the footer of the report I want to print a summary box that looks like this:

Product: Number Purchased: Total:

Cat Food 1 $12

Dog Food 3 $44.50

Rabbit Food 1 $17

How do I do this?

Thanks!

group on the food purchased fields and do a count on the group.|||

I'm not an expert at this. It is easy to say what do to. Can you tell me how to do it?

|||

Hello,

I don't think there is a way to have a summary like that in the footer of your report. But this will add your group totals, then you can just hide the details if you need.

Right click on the row handle for your detail row and select 'Insert Group'. In the box that pops up, give the group a name (or leave the default), select the Food Purchased field as your expression and select the box for 'Include group footer'. This will create two new rows in your table, a header and footer. In one of your footers textbox's, enter this to get the count of the group:

=CountRows()

If you don't want to see the details and just want the summary, click the row handle and change the Visibility -> Hidden propery to True. This will hid your details and only show the group footers.

Hope this helps.

Jarret

|||I have one question if the data of your main report is not fit in one page then the footer will repeat on next page. Does it ok with your requirement ?|||

I am converting this report from a crystal report. So yea, in the report now it is in a Report Footer and it only appears on the last page. This is how I need it because I was told to make the SQL Report look exactly like the CR. Unfortunately it seems like SRS doesn't give you a really nice option of doing Running Total fields like in Crystal. In Crystal they created a running total field and said "count all that are Dog Food" and then another running total that said "Count all that are Cat Food." I am not sure if putting it on the group footer will work because I need the report to display as it is. And to just say =CountRows() seems like it will count all rows and return 5. But I haven't tried it.

I created another report and put all the footer stuff in a table footer. And the table footer only appeared on the last page. I would like to put all this information in the table footer not the report footer. Sorry if I didn't explain that too well.

I am very surprised that SRS doesn't have an easy way to do this.

|||

Hello,

Using =CountRows() will give you the number of rows in that scope, so if you put this in your group header/footer, it will show the number of rows in that group. If you place it in the table header/footer, you will get the total number of rows in the table.

Jarret

|||

How do I put the conditional on? Currently it is counting all rows in the database for each type of food. What if I want to say "only count the rows that are on the report" (or only records that happened in the past week)?

|||

This worked in the table footer!

=Count(iif(Trim(Fields!FoodPurch.Value)="Dog Food",Fields!FoodPurch.Value, Nothing))

Calculated field

can I create a field whose values will be derived from
other fields in the table without writing a stored proc or
script? For example if I have a table called Salary with
three fields: hours, rate and GrossPay. I want the
grosspay field to be updated automatically if values have
been provided for the hours and rate fields. Is this
feasible?
Thanks for your help in advance.
This is called a computed column. The values are not stored but are
calculated when a result set is requested. The syntax is documented under
the the CREATE TABLE and ALTER TABLE commands in BOL (Books On-Line).
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible?
> Thanks for your help in advance.
|||lala,
I believe you can use a trigger to accomplish what you are asking. in the
BOL navigate to 'create trigger', and you can look at some examples there.
essentially, every time someone updates those columns, the trigger should be
able to poulate the third column.
hth
"lala" wrote:

> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible?
> Thanks for your help in advance.
>
|||In addition to the other posts: consider having a view where you define the calculated columns and
use that view. This way you don't have to "litter" your tables with calculated values.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible?
> Thanks for your help in advance.

Calculated field

can I create a field whose values will be derived from
other fields in the table without writing a stored proc or
script? For example if I have a table called Salary with
three fields: hours, rate and GrossPay. I want the
grosspay field to be updated automatically if values have
been provided for the hours and rate fields. Is this
feasible'
Thanks for your help in advance.This is called a computed column. The values are not stored but are
calculated when a result set is requested. The syntax is documented under
the the CREATE TABLE and ALTER TABLE commands in BOL (Books On-Line).
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.|||lala,
I believe you can use a trigger to accomplish what you are asking. in the
BOL navigate to 'create trigger', and you can look at some examples there.
essentially, every time someone updates those columns, the trigger should be
able to poulate the third column.
hth
"lala" wrote:
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.
>|||In addition to the other posts: consider having a view where you define the calculated columns and
use that view. This way you don't have to "litter" your tables with calculated values.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"lala" <anonymous@.discussions.microsoft.com> wrote in message
news:194b01c4aafa$39e6cef0$7d02280a@.phx.gbl...
> can I create a field whose values will be derived from
> other fields in the table without writing a stored proc or
> script? For example if I have a table called Salary with
> three fields: hours, rate and GrossPay. I want the
> grosspay field to be updated automatically if values have
> been provided for the hours and rate fields. Is this
> feasible'
> Thanks for your help in advance.sql

Cal Measure % against total

How to define calculated member whose value should be the row value divided by total of all rows. Example, sales of 2005 was 50k and sales for 2006 was 100k. The % 2005 sales is 33% of total (150k) and 2006 is 67%.

Hi

For example to get the percentage of the Unit Sales for one customer of all customers, you write:

[Measures].[Unit Sales] / ([Measures].[Unit Sales], [Customers].[All Customers])

The key is, to divide by a MDX tuple ([Measures].[Unit Sales], [Customers].[All Customers]) which brings the value for all customers.

Hans

|||

Thank Hans

This problem is solved. Please guide me to get the same result with multiple dimensions.

Also, i have a variance calculated member, which is on the date time diminsion [Sale of 2005] - [Sale of 2006] = [Variance 2005]. I would like to see three measures, [Year 2005 Sale], [Year 2006 Sale] and variance. But i get Sales and variance figure under Year 2005 and same under Year 2006. How to get the desired result.

Thanks

Shekhar

|||

Hi Shekhar,

I'm not sure, if I did understand you right, but I think it's because your [Variance] is on the Time Dimension. I do it in my projects so, that I create a calculated member like:

MEMBER [Measures].[Year variance] AS ([Measures].[Unit Sales],[Time].Currentmember.Prevmember) - [Measures].[Unit Sales]

If Currentmember is Year 2007, den Prevmember is Year 2006 and so on.

If you use now all 3 in a select, you can see it "flat"

select

{ [Measures].[Year 2005 Sales], [Measures].[Year 2006 Sale], [Measures].[Year variance]} on columns,

.....

Hans

CAL license required?

Microsoft SQL Server 2000 client access license (CAL) licensing terms
states:
A SQL Server CAL is required for a device (for example, a personal
computer, workstation, terminal, personal digital assistant, or
mobile phone) to access or use the services or functionality of
either edition of SQL Server.
I have an application (exe) that accepts TCP/IP connections and stores
the IP addresses of the connecting computers or other devices to a table
stored at SQL Server 2000 Standard Edition database.
Do I have to have a CAL license for every connecting device?
I have an application (exe) that monitors caller numbers from a standard
phone line and stores the caller numbers to a table stored at SQL Server
2000 Standard Edition database.
Do I have to have a CAL license for every calling phone?
SQL Server 2000 CAL licence information can be found at
http://www.microsoft.com/sql/howtobuy/servercal.asp .
Microsoft license information makes it sound like any data that ever passed
through a SQL Server database at some stage in its life has to have a CAL.
We have all been assimilated now...
"JF" <noone@.nowhere.com> wrote in message
news:%23PARv8VVEHA.3264@.TK2MSFTNGP10.phx.gbl...
> Microsoft SQL Server 2000 client access license (CAL) licensing terms
> states:
> A SQL Server CAL is required for a device (for example, a personal
> computer, workstation, terminal, personal digital assistant, or
> mobile phone) to access or use the services or functionality of
> either edition of SQL Server.
> I have an application (exe) that accepts TCP/IP connections and stores
> the IP addresses of the connecting computers or other devices to a table
> stored at SQL Server 2000 Standard Edition database.
> Do I have to have a CAL license for every connecting device?
> I have an application (exe) that monitors caller numbers from a standard
> phone line and stores the caller numbers to a table stored at SQL Server
> 2000 Standard Edition database.
> Do I have to have a CAL license for every calling phone?
> SQL Server 2000 CAL licence information can be found at
> http://www.microsoft.com/sql/howtobuy/servercal.asp .
>
|||Licensing is a very complex beast, but I would guess that for the TCP/IP =
application you would need a CAL for each device that connects. This =
scenario sounds a bit like an internet app (or a multiplexed app), =
therefore you might have to the the per processor licensing route. =20
In the phone case I would guess that you only need one CAL (for the =
exe/phone combination) because you are only logging data as collected by =
your phone line and the exe. This scenario sounds like a service that =
monitors and log events to your database. =20
The difference is slight, but the first scenario has clients connecting =
to it (and to SQL Server). The second example is more like a monitor =
that you have in place.
Don't take my assumptions as right or wrong, they are only a guess.
--=20
Keith
"JF" <noone@.nowhere.com> wrote in message =
news:%23PARv8VVEHA.3264@.TK2MSFTNGP10.phx.gbl...
> Microsoft SQL Server 2000 client access license (CAL) licensing terms=20
> states:
>=20
> A SQL Server CAL is required for a device (for example, a personal
> computer, workstation, terminal, personal digital assistant, or
> mobile phone) to access or use the services or functionality of
> either edition of SQL Server.
>=20
> I have an application (exe) that accepts TCP/IP connections and stores =

> the IP addresses of the connecting computers or other devices to a =
table=20
> stored at SQL Server 2000 Standard Edition database.
> Do I have to have a CAL license for every connecting device?
>=20
> I have an application (exe) that monitors caller numbers from a =
standard=20
> phone line and stores the caller numbers to a table stored at SQL =
Server=20
> 2000 Standard Edition database.
> Do I have to have a CAL license for every calling phone?
>=20
> SQL Server 2000 CAL licence information can be found at=20
> http://www.microsoft.com/sql/howtobuy/servercal.asp .
>
|||If I am reading this correctly you have an application that when other
applications attach to it the first logs the IP address of the second. Does
the second application (or device) then extract data from the database or is
some other process done? If the purpose of the database is to store the IP
addresses captured by the first application you need a CAL for each machine
running that application. in the case of the second scenario you would need
one CAL for the machine running the phone number capturing application and
any other machine involved in extracting that information from the database.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"JF" <noone@.nowhere.com> wrote in message
news:%23PARv8VVEHA.3264@.TK2MSFTNGP10.phx.gbl...
> Microsoft SQL Server 2000 client access license (CAL) licensing terms
> states:
> A SQL Server CAL is required for a device (for example, a personal
> computer, workstation, terminal, personal digital assistant, or
> mobile phone) to access or use the services or functionality of
> either edition of SQL Server.
> I have an application (exe) that accepts TCP/IP connections and stores
> the IP addresses of the connecting computers or other devices to a table
> stored at SQL Server 2000 Standard Edition database.
> Do I have to have a CAL license for every connecting device?
> I have an application (exe) that monitors caller numbers from a standard
> phone line and stores the caller numbers to a table stored at SQL Server
> 2000 Standard Edition database.
> Do I have to have a CAL license for every calling phone?
> SQL Server 2000 CAL licence information can be found at
> http://www.microsoft.com/sql/howtobuy/servercal.asp .
>

CAL license required?

Microsoft SQL Server 2000 client access license (CAL) licensing terms
states:
A SQL Server CAL is required for a device (for example, a personal
computer, workstation, terminal, personal digital assistant, or
mobile phone) to access or use the services or functionality of
either edition of SQL Server.
I have an application (exe) that accepts TCP/IP connections and stores
the IP addresses of the connecting computers or other devices to a table
stored at SQL Server 2000 Standard Edition database.
Do I have to have a CAL license for every connecting device?
I have an application (exe) that monitors caller numbers from a standard
phone line and stores the caller numbers to a table stored at SQL Server
2000 Standard Edition database.
Do I have to have a CAL license for every calling phone?
SQL Server 2000 CAL licence information can be found at
http://www.microsoft.com/sql/howtobuy/servercal.asp .Microsoft license information makes it sound like any data that ever passed
through a SQL Server database at some stage in its life has to have a CAL.
We have all been assimilated now...
"JF" <noone@.nowhere.com> wrote in message
news:%23PARv8VVEHA.3264@.TK2MSFTNGP10.phx.gbl...
> Microsoft SQL Server 2000 client access license (CAL) licensing terms
> states:
> A SQL Server CAL is required for a device (for example, a personal
> computer, workstation, terminal, personal digital assistant, or
> mobile phone) to access or use the services or functionality of
> either edition of SQL Server.
> I have an application (exe) that accepts TCP/IP connections and stores
> the IP addresses of the connecting computers or other devices to a table
> stored at SQL Server 2000 Standard Edition database.
> Do I have to have a CAL license for every connecting device?
> I have an application (exe) that monitors caller numbers from a standard
> phone line and stores the caller numbers to a table stored at SQL Server
> 2000 Standard Edition database.
> Do I have to have a CAL license for every calling phone?
> SQL Server 2000 CAL licence information can be found at
> http://www.microsoft.com/sql/howtobuy/servercal.asp .
>|||Licensing is a very complex beast, but I would guess that for the TCP/IP =application you would need a CAL for each device that connects. This =scenario sounds a bit like an internet app (or a multiplexed app), =therefore you might have to the the per processor licensing route.
In the phone case I would guess that you only need one CAL (for the =exe/phone combination) because you are only logging data as collected by =your phone line and the exe. This scenario sounds like a service that =monitors and log events to your database.
The difference is slight, but the first scenario has clients connecting =to it (and to SQL Server). The second example is more like a monitor =that you have in place.
Don't take my assumptions as right or wrong, they are only a guess.
-- Keith
"JF" <noone@.nowhere.com> wrote in message =news:%23PARv8VVEHA.3264@.TK2MSFTNGP10.phx.gbl...
> Microsoft SQL Server 2000 client access license (CAL) licensing terms > states:
> > A SQL Server CAL is required for a device (for example, a personal
> computer, workstation, terminal, personal digital assistant, or
> mobile phone) to access or use the services or functionality of
> either edition of SQL Server.
> > I have an application (exe) that accepts TCP/IP connections and stores =
> the IP addresses of the connecting computers or other devices to a =table > stored at SQL Server 2000 Standard Edition database.
> Do I have to have a CAL license for every connecting device?
> > I have an application (exe) that monitors caller numbers from a =standard > phone line and stores the caller numbers to a table stored at SQL =Server > 2000 Standard Edition database.
> Do I have to have a CAL license for every calling phone?
> > SQL Server 2000 CAL licence information can be found at > http://www.microsoft.com/sql/howtobuy/servercal.asp .
>|||If I am reading this correctly you have an application that when other
applications attach to it the first logs the IP address of the second. Does
the second application (or device) then extract data from the database or is
some other process done? If the purpose of the database is to store the IP
addresses captured by the first application you need a CAL for each machine
running that application. in the case of the second scenario you would need
one CAL for the machine running the phone number capturing application and
any other machine involved in extracting that information from the database.
--
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"JF" <noone@.nowhere.com> wrote in message
news:%23PARv8VVEHA.3264@.TK2MSFTNGP10.phx.gbl...
> Microsoft SQL Server 2000 client access license (CAL) licensing terms
> states:
> A SQL Server CAL is required for a device (for example, a personal
> computer, workstation, terminal, personal digital assistant, or
> mobile phone) to access or use the services or functionality of
> either edition of SQL Server.
> I have an application (exe) that accepts TCP/IP connections and stores
> the IP addresses of the connecting computers or other devices to a table
> stored at SQL Server 2000 Standard Edition database.
> Do I have to have a CAL license for every connecting device?
> I have an application (exe) that monitors caller numbers from a standard
> phone line and stores the caller numbers to a table stored at SQL Server
> 2000 Standard Edition database.
> Do I have to have a CAL license for every calling phone?
> SQL Server 2000 CAL licence information can be found at
> http://www.microsoft.com/sql/howtobuy/servercal.asp .
>

CAL license required?

Microsoft SQL Server 2000 client access license (CAL) licensing terms
states:
A SQL Server CAL is required for a device (for example, a personal
computer, workstation, terminal, personal digital assistant, or
mobile phone) to access or use the services or functionality of
either edition of SQL Server.
I have an application (exe) that accepts TCP/IP connections and stores
the IP addresses of the connecting computers or other devices to a table
stored at SQL Server 2000 Standard Edition database.
Do I have to have a CAL license for every connecting device?
I have an application (exe) that monitors caller numbers from a standard
phone line and stores the caller numbers to a table stored at SQL Server
2000 Standard Edition database.
Do I have to have a CAL license for every calling phone?
SQL Server 2000 CAL licence information can be found at
http://www.microsoft.com/sql/howtobuy/servercal.asp .Microsoft license information makes it sound like any data that ever passed
through a SQL Server database at some stage in its life has to have a CAL.
We have all been assimilated now...
"JF" <noone@.nowhere.com> wrote in message
news:%23PARv8VVEHA.3264@.TK2MSFTNGP10.phx.gbl...
> Microsoft SQL Server 2000 client access license (CAL) licensing terms
> states:
> A SQL Server CAL is required for a device (for example, a personal
> computer, workstation, terminal, personal digital assistant, or
> mobile phone) to access or use the services or functionality of
> either edition of SQL Server.
> I have an application (exe) that accepts TCP/IP connections and stores
> the IP addresses of the connecting computers or other devices to a table
> stored at SQL Server 2000 Standard Edition database.
> Do I have to have a CAL license for every connecting device?
> I have an application (exe) that monitors caller numbers from a standard
> phone line and stores the caller numbers to a table stored at SQL Server
> 2000 Standard Edition database.
> Do I have to have a CAL license for every calling phone?
> SQL Server 2000 CAL licence information can be found at
> http://www.microsoft.com/sql/howtobuy/servercal.asp .
>|||Licensing is a very complex beast, but I would guess that for the TCP/IP =
application you would need a CAL for each device that connects. This =
scenario sounds a bit like an internet app (or a multiplexed app), =
therefore you might have to the the per processor licensing route. =20
In the phone case I would guess that you only need one CAL (for the =
exe/phone combination) because you are only logging data as collected by =
your phone line and the exe. This scenario sounds like a service that =
monitors and log events to your database. =20
The difference is slight, but the first scenario has clients connecting =
to it (and to SQL Server). The second example is more like a monitor =
that you have in place.
Don't take my assumptions as right or wrong, they are only a guess.
--=20
Keith
"JF" <noone@.nowhere.com> wrote in message =
news:%23PARv8VVEHA.3264@.TK2MSFTNGP10.phx.gbl...
> Microsoft SQL Server 2000 client access license (CAL) licensing terms=20
> states:
>=20
> A SQL Server CAL is required for a device (for example, a personal
> computer, workstation, terminal, personal digital assistant, or
> mobile phone) to access or use the services or functionality of
> either edition of SQL Server.
>=20
> I have an application (exe) that accepts TCP/IP connections and stores =

> the IP addresses of the connecting computers or other devices to a =
table=20
> stored at SQL Server 2000 Standard Edition database.
> Do I have to have a CAL license for every connecting device?
>=20
> I have an application (exe) that monitors caller numbers from a =
standard=20
> phone line and stores the caller numbers to a table stored at SQL =
Server=20
> 2000 Standard Edition database.
> Do I have to have a CAL license for every calling phone?
>=20
> SQL Server 2000 CAL licence information can be found at=20
> http://www.microsoft.com/sql/howtobuy/servercal.asp .
>|||If I am reading this correctly you have an application that when other
applications attach to it the first logs the IP address of the second. Does
the second application (or device) then extract data from the database or is
some other process done? If the purpose of the database is to store the IP
addresses captured by the first application you need a CAL for each machine
running that application. in the case of the second scenario you would need
one CAL for the machine running the phone number capturing application and
any other machine involved in extracting that information from the database.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"JF" <noone@.nowhere.com> wrote in message
news:%23PARv8VVEHA.3264@.TK2MSFTNGP10.phx.gbl...
> Microsoft SQL Server 2000 client access license (CAL) licensing terms
> states:
> A SQL Server CAL is required for a device (for example, a personal
> computer, workstation, terminal, personal digital assistant, or
> mobile phone) to access or use the services or functionality of
> either edition of SQL Server.
> I have an application (exe) that accepts TCP/IP connections and stores
> the IP addresses of the connecting computers or other devices to a table
> stored at SQL Server 2000 Standard Edition database.
> Do I have to have a CAL license for every connecting device?
> I have an application (exe) that monitors caller numbers from a standard
> phone line and stores the caller numbers to a table stored at SQL Server
> 2000 Standard Edition database.
> Do I have to have a CAL license for every calling phone?
> SQL Server 2000 CAL licence information can be found at
> http://www.microsoft.com/sql/howtobuy/servercal.asp .
>

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# 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# Master-Details (Separate Pages)

My question is setting up master detail pages using stored procedrues. In the tutorials C# Master-Details (Seperate Pages) example they use the following code for the master page for the navigation.

<asp:HyperLinkField HeaderText="View Details..." Text="View Details..." DataNavigateUrlFields="au_id"
DataNavigateUrlFormatString="DetailsView_cs.aspx?ID={0}" />

The call to the Details PageDetailsView_cs.aspxuses the following code

<asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand="SELECT dbo.authors.au_id, dbo.titles.title_id, dbo.titles.title, dbo.titles.type, dbo.titles.price, dbo.titles.notes FROM dbo.authors INNER JOIN dbo.titleauthor ON dbo.authors.au_id = dbo.titleauthor.au_id INNER JOIN dbo.titles ON dbo.titleauthor.title_id = dbo.titles.title_id WHERE (dbo.authors.au_id = @.au_id)"
ConnectionString="<%$ ConnectionStrings:Pubs %>">
<SelectParameters>
<asp:QueryStringParameter Name="au_id" DefaultValue="213-46-8915" QueryStringField="ID"/>
</SelectParameters>
</asp:SqlDataSource>

I can replicate the example calling the detail page but I am unable to make the detail page work when using a stored procedure as the asp:SQLDataSource. Using the above sql code as a stored procedure in the <SelectParameters> I am not able to return the data set using either asp:QueryStringParameter or asp:Parameter as I have built other forms using stored procedures and have tested the procedure and know that it works. Can someone point me in the right direction.

Thanks

The soulution works the same with stored procedures but you need to have

EnableSortingAndPagingCallbacks="false" not true

|||I spoke to soon, my previous post did not resole the issue of calling a stored procedure from the detail page. The master page passes the correct value to the detail but the procedure does not recognize it.

by vb.net create exe File to update sql server

is there any chance to create an exe file to update the sql server database by uisng windows schdule ?

for example this exe file will run to update my database, every night @. 12:00 AM.

this exe should be in vb.net

pllllzzzz help

What is your design for that ? Do you want to execute DDL or DML or just maintainance on the database ? You might check the option of the SQL Server Agent, which does Scheduling for SQL Server. More information would be helpful to help you.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

You can also VBScript from SQL Agent…

Bulkload XML to SQL2005

I have hundreds of large (500meg) XML files I need to upload into SQL2005. I followed the example from http://support.microsoft.com/default.aspx/kb/316005/en-us with no major problems. Unfortunatly my data can be several fields deep within the xml file (sample below).

I'm created a vbs file to process the bulkload.

I'm not able to figure out how to create the mapping (schema) for this file structure. I'm trying to use a mapping schema simular to what is used in the example.

How do I modify the mapping schema for my format?

Please help educate me. I am a noob, so please keep it simple.

Thanks

Charles W

XML File

<ROOT>
<Customers>
<CustomerId><IDno><pdat>2111</pdat></IDno></CustomerId>
<CompanyName><pdat>2Sean Chai</pdat></CompanyName>
<City><pdat>NY</pdat></City>
</Customers>
<Customers>
<CustomerId><IDno><pdat>2112</pdat></IDno></CustomerId>
<CompanyName><pdat>2Tom Johnston</pdat></CompanyName>
<City><pdat>LA</pdat></City>
</Customers>
<Customers>
<CustomerId><IDno><pdat>2113</pdat></IDno></CustomerId>
<CompanyName><pdat>2Institute of Art 3</pdat></CompanyName>
</Customers>
</ROOT>

XSD File

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<xs:element name="ROOT" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" ref="Customers"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Customers" sql:relation="Customer">
<xs:complexType>
<xs:sequence>
<xs:element ref="CustomerId" sql:field="CustomerId" />
<xs:element ref="CompanyName" sql:field="CompanyName" />
<xs:element minOccurs="0" ref="City" sql:field="City"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="CustomerId" type="xs:integer"/>
<xs:element name="CompanyName" type="xs:string"/>
<xs:element name="City" type="xs:NCName"/>
</xs:schema>

VBS

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=12T4581-CHUCK;database=MyDatabase;uid=XMLtest;pwd=XMLtest"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\customer3.xsd", "c:\customers3.xml"
Set objBL = Nothing

FYI:

To solve my problem, I created a .net application to modify my xml file into a format that I can work with. It really turned out to be the best solution for me because I have to make some other modifications.

I can take a 600k xml file and stip out the data I need in under 1 minute. At this rate I can prep an entire years worth of data in under 15 minutes.

Now I need to master XSD files.....

Charles W

|||

This is the recommended way to achieve your mapping. Bulkload would not support your shape of having multiple layers of wrappers around your scalar type. So Bulkload would support:

<City>NY</City>

But not:

<City><pdat>NY</pdat></City>

Bulkload XML to SQL2005

I have hundreds of large (500meg) XML files I need to upload into SQL2005. I followed the example from http://support.microsoft.com/default.aspx/kb/316005/en-us with no major problems. Unfortunatly my data can be several fields deep within the xml file (sample below).

I'm created a vbs file to process the bulkload.

I'm not able to figure out how to create the mapping (schema) for this file structure. I'm trying to use a mapping schema simular to what is used in the example.

How do I modify the mapping schema for my format?

Please help educate me. I am a noob, so please keep it simple.

Thanks

Charles W

XML File

<ROOT>
<Customers>
<CustomerId><IDno><pdat>2111</pdat></IDno></CustomerId>
<CompanyName><pdat>2Sean Chai</pdat></CompanyName>
<City><pdat>NY</pdat></City>
</Customers>
<Customers>
<CustomerId><IDno><pdat>2112</pdat></IDno></CustomerId>
<CompanyName><pdat>2Tom Johnston</pdat></CompanyName>
<City><pdat>LA</pdat></City>
</Customers>
<Customers>
<CustomerId><IDno><pdat>2113</pdat></IDno></CustomerId>
<CompanyName><pdat>2Institute of Art 3</pdat></CompanyName>
</Customers>
</ROOT>

XSD File

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"
xmlns:sql="urn:schemas-microsoft-com:xml-sql" >
<xs:element name="ROOT" sql:is-constant="1">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" ref="Customers"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Customers" sql:relation="Customer">
<xs:complexType>
<xs:sequence>
<xs:element ref="CustomerId" sql:field="CustomerId" />
<xs:element ref="CompanyName" sql:field="CompanyName" />
<xs:element minOccurs="0" ref="City" sql:field="City"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="CustomerId" type="xs:integer"/>
<xs:element name="CompanyName" type="xs:string"/>
<xs:element name="City" type="xs:NCName"/>
</xs:schema>

VBS

Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
objBL.ConnectionString = "provider=SQLOLEDB.1;data source=12T4581-CHUCK;database=MyDatabase;uid=XMLtest;pwd=XMLtest"
objBL.ErrorLogFile = "c:\error.log"
objBL.Execute "c:\customer3.xsd", "c:\customers3.xml"
Set objBL = Nothing

FYI:

To solve my problem, I created a .net application to modify my xml file into a format that I can work with. It really turned out to be the best solution for me because I have to make some other modifications.

I can take a 600k xml file and stip out the data I need in under 1 minute. At this rate I can prep an entire years worth of data in under 15 minutes.

Now I need to master XSD files.....

Charles W

|||

This is the recommended way to achieve your mapping. Bulkload would not support your shape of having multiple layers of wrappers around your scalar type. So Bulkload would support:

<City>NY</City>

But not:

<City><pdat>NY</pdat></City>