Float vs Decimal

Hello, sorry if this question is silly
I'm about difference between Decimal and Float data types , if im
writing accounting application and use 10:4 as precision/scale in all
numbers , does it matters if i choose fields as Decimal or Float in that
case ?
in BOL its says about float that
Approximate number data types for use with floating point numeric data.
Floating point data is approximate; not all values in the data type range
can be precisely represented.
i do not know what that means? can anyone give small example adding 2
different numbers that will give different answers if column type is decimal
than float ?
Best Regards
Bassamfloats store numbers as base 2, decimals as base 10. You can lookup a full
explaination on google, I would not do it justice. Tey this to see how they
differ.
create table Test
(NumDecimal decimal(10,4)
, numFloat float
);
insert into Test (NumDecimal, numFloat) values (0.1, 0.1);
insert into Test (NumDecimal, numFloat) values (0.3, 0.3);
insert into Test (NumDecimal, numFloat) values (0.25, 0.25);
insert into Test (NumDecimal, numFloat) values (1.0/3.0, 1.0/3.0);
insert into Test (NumDecimal, numFloat) values (1.0/6.0, 1.0/6.0);
select * from test;
select numdecimal*3 , numfloat*3 from test;
drop table Test;
"Bassam" <bassam@.nptco.com.eg> wrote in message
news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl...
> Hello, sorry if this question is silly
> I'm about difference between Decimal and Float data types , if im
> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is
decimal
> than float ?
> --
> Best Regards
> Bassam
>
>|||> can anyone give small example adding 2
> different numbers that will give different answers if column type is decim
al
> than float ?
Run below in Query Analyzer and you will see:
DECLARE @.fa float, @.fb float, @.da decimal(10,4), @.db decimal(10,4)
SELECT @.fa = 3.1, @.da = 3.1
SELECT @.fb = 5.5, @.db = 5.5
SELECT @.fa + @.fb
SELECT @.da + @.db
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bassam" <bassam@.nptco.com.eg> wrote in message news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl.
.
> Hello, sorry if this question is silly
> I'm about difference between Decimal and Float data types , if im
> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is decim
al
> than float ?
> --
> Best Regards
> Bassam
>
>|||I'm not sure you'll get an example by adding 2 numbers, but an
important point to note is that floats will sometimes "miss by a bit",
so you'll find that if you're doing lots of division and multiplication
you might end up with 1.00000000016 rather than 1. Generally your
application will have a natural degree of accuracy which you're working
within, so decimals are much better to use as they will not cause this
kind of behaviour|||Bassam (bassam@.nptco.com.eg) writes:
> Hello, sorry if this question is silly
> I'm about difference between Decimal and Float data types , if im
> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is
> decimal than float ?
Run this in Query Analyzer:
declare @.d1 decimal(10, 4), @.d2 decimal(10, 4),
@.f1 float, @.f2 float
SELECT @.d1 = 98.234, @.d2 = 87.0987
SELECT @.f1 = 98.234, @.f2 = 87.0987
SELECT @.d1 = 98.234, @.d2 = 87.0987
SELECT @.d1 + @.d2, @.f1 + @.f2
More generally, while is valid and reasonable to write:
WHERE decimalcol = 0
the same is not true for
WHERE floatcal = 0
Because due to rounding errors, floatcol may have a value like
0.0000000000000123
It's possible to use float in an accounting application, but you have to
be very careful. Decimal has its pitfalls too, but is probably safer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||In an accounting application, exact values (decimal data type) should be
used.
For float and real data types, they are approximate as it a the specified
number of bits to store the mantissa of the float number in scientific
notation, in binary form. The conversion of a decimal value (with decimal
places) to a binary value usually results in a lost of precision.
Try the following.
select convert(decimal(10, 4), 111.1111) as MyDecimalValue,
convert(float(24), 111.1111) as MyFloatValue
Martin C K Poon
Senior Analyst Programmer
====================================
"Bassam" <bassam@.nptco.com.eg> bl
news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl g...
> Hello, sorry if this question is silly
> I'm about difference between Decimal and Float data types , if im
> writing accounting application and use 10:4 as precision/scale in all
> numbers , does it matters if i choose fields as Decimal or Float in that
> case ?
> in BOL its says about float that
> Approximate number data types for use with floating point numeric data.
> Floating point data is approximate; not all values in the data type range
> can be precisely represented.
> i do not know what that means? can anyone give small example adding 2
> different numbers that will give different answers if column type is
decimal
> than float ?
> --
> Best Regards
> Bassam
>
>|||you know, when running this in Management Studio I get
@.fa + @.fb = 8.6
@.da + @.db = 8.600
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23VAvd2rbGHA.2396@.TK2MSFTNGP02.phx.gbl...
>
> Run below in Query Analyzer and you will see:
> DECLARE @.fa float, @.fb float, @.da decimal(10,4), @.db decimal(10,4)
> SELECT @.fa = 3.1, @.da = 3.1
> SELECT @.fb = 5.5, @.db = 5.5
> SELECT @.fa + @.fb
> SELECT @.da + @.db
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bassam" <bassam@.nptco.com.eg> wrote in message
> news:OpLEDirbGHA.2456@.TK2MSFTNGP04.phx.gbl...|||Presenting the values returned (in binary format) from SQL Server is the tas
k of the client
application. Apparently, SSMS assumes that you aren't that concerned about a
ll the decimals when you
use float and real, while QA is more exact in the representation of these va
lues.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <ss@.Mailinator.com> wrote in message news:ufjgO$rbGHA.3800@.TK2MSFTNGP04.phx.gbl...[
color=darkred]
> you know, when running this in Management Studio I get
> @.fa + @.fb = 8.6
> @.da + @.db = 8.600
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23VAvd2rbGHA.2396@.TK2MSFTNGP02.phx.gbl...
>[/color]|||For completeness... what are the pitfalls of the Decimal datatype? I've
always found that as long as I'm careful with the precision the results
are accurate.|||I'd rather have the results from SSMS include all the decimals. Anyway to
force that, or is there an option to change?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OAme4FsbGHA.3388@.TK2MSFTNGP05.phx.gbl...
> Presenting the values returned (in binary format) from SQL Server is the
> task of the client application. Apparently, SSMS assumes that you aren't
> that concerned about all the decimals when you use float and real, while
> QA is more exact in the representation of these values.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Steve" <ss@.Mailinator.com> wrote in message
> news:ufjgO$rbGHA.3800@.TK2MSFTNGP04.phx.gbl...
>sql

Flatten N-Tier Hierarchy for reporting

Hello. I have a specification for a new application that states that a
particular item structure should support a series of arbitrary hierarchies.
Hierarchy types would be defined in a lookup table, such that items and chil
d
items would be of a particular "type," but this is really only for UI displa
y
purposes -- all items will stored in the same table in the database. Also,
the lowest level of a given heirarchy type should also support cost
accumulation. My hierarchy setup is a standard Id/ParentId model, and I
created an ItemDetail table (FK on the ItemID) to store the cost records.
I have built a c# prototype application that supports this structure, and
everything works like a champ. Here's the problem I have and question on
which I need input: I don't know how to report on it. Since the hierarchy
types have an arbitrary number of levels, how, thru TSQL, do I create a view
on which users can create reports? Through code (c#), I can recurse the
Items table and compare against the ItemTypes table to inspect the hierarchy
types and create tabular (flattened) data. My goal is to expose a view (or
set of views) for end users to use for ad-hoc reporting.
Below is a simplified/truncated data structure similar to my prototype
structure, and my desired end-result. Since I'm still in a prototype stage,
I'm not stuck to the data model, so I'm open to suggestions for improvement
to the design or suggestions on the reporting issue. I hope all this makes
sense, and thanks in advance.
Note: All the primary keys are simple identity columns, because the form of
the data that user knows as the key will vary.
CREATE TABLE [GroupTypes] (
[grptypPk] [int] IDENTITY (1, 1) NOT NULL ,
[grptypName] [varchar] (50) NOT NULL ,
)
CREATE TABLE [Groups] (
[grpPk] [int] IDENTITY (1, 1) NOT NULL ,
[grpName] [varchar] (50) NOT NULL ,
[grpGroupType_fk] [int] NOT NULL
)
CREATE TABLE [ItemTypes] (
[itmtypPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmtypName] [varchar] (50) NOT NULL ,
[itmtypParent] [int] NULL ,
[itmtypGroupType_fk] [int] NOT NULL
)
CREATE TABLE [Items] (
[itmPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmName] [varchar] (50) NOT NULL ,
[itmItemType_fk] [int] NOT NULL ,
[itmGroup_fk] [int] NOT NULL ,
[itmParent] [int] NULL
)
CREATE TABLE [ItemDetailTypes] (
[dtltypPk] [int] IDENTITY (1, 1) NOT NULL ,
[dtltypName] [varchar] (50) NOT NULL ,
)
CREATE TABLE [ItemDetail] (
[itmdtlPk] [int] IDENTITY (1, 1) NOT NULL ,
[itmdtlCost] [int] NOT NULL ,
[itmdtlItem_fk] [int] NOT NULL ,
[itmdtlDetailType_fk] [int] NOT NULL
)
/*----*/
DECLARE @.itmtyp2 INT
DECLARE @.itmtyp3 INT
DECLARE @.itm3 INT
DECLARE @.itm6 INT
DECLARE @.dtltyp1 INT
INSERT INTO GroupTypes (grptypName) VALUES ('Group Type 1')
SET @.grptyp1 = @.@.IDENTITY
INSERT INTO Groups (grpName, grpGroupType_fk) VALUES ('Group1', @.grptyp1)
SET @.grp1 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 1', NULL, @.grptyp1)
SET @.itmtyp1 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 2', @.itmtyp1, @.grptyp1)
SET @.itmtyp2 = @.@.IDENTITY
INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
('Item Type 3', @.itmtyp2, @.grptyp1)
SET @.itmtyp3 = @.@.IDENTITY
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item1', @.itmtyp1, @.grp1, NULL)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item2', @.itmtyp2, @.grp1, @.@.IDENTITY)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item3', @.itmtyp3, @.grp1, @.@.IDENTITY)
SET @.itm3 = @.@.IDENTITY
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item4', @.itmtyp1, @.grp1, NULL)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item5', @.itmtyp2, @.grp1, @.@.IDENTITY)
INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
('Item6', @.itmtyp3, @.grp1, @.@.IDENTITY)
SET @.itm6 = @.@.IDENTITY
INSERT INTO ItemDetailTypes (dtltypName) VALUES ('Detail Type 1')
SET @.dtltyp1 = @.@.IDENTITY
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(10, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(20, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(30, @.itm3, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(15, @.itm6, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(25, @.itm6, @.dtltyp1)
INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALUES
(30, @.itm6, @.dtltyp1)
/*----*/
Flattened data for Group1:
Group1 Item1 Item2 Item3
Group1 Item4 Item5 Item6
I believe this is an easy structure on which to report, and I could just
INNER JOIN on the ItemDetail table for Cost information.Hi
The best way to do this is to traverse the hierachy and build up the rows on
the the client.
There are many posts regarding hierarchies in SQL Server, so you may also
want to search Google for previous posts.
John
"Steve" wrote:

> Hello. I have a specification for a new application that states that a
> particular item structure should support a series of arbitrary hierarchies
.
> Hierarchy types would be defined in a lookup table, such that items and ch
ild
> items would be of a particular "type," but this is really only for UI disp
lay
> purposes -- all items will stored in the same table in the database. Also
,
> the lowest level of a given heirarchy type should also support cost
> accumulation. My hierarchy setup is a standard Id/ParentId model, and I
> created an ItemDetail table (FK on the ItemID) to store the cost records.
> I have built a c# prototype application that supports this structure, and
> everything works like a champ. Here's the problem I have and question on
> which I need input: I don't know how to report on it. Since the hierarchy
> types have an arbitrary number of levels, how, thru TSQL, do I create a vi
ew
> on which users can create reports? Through code (c#), I can recurse the
> Items table and compare against the ItemTypes table to inspect the hierarc
hy
> types and create tabular (flattened) data. My goal is to expose a view (o
r
> set of views) for end users to use for ad-hoc reporting.
> Below is a simplified/truncated data structure similar to my prototype
> structure, and my desired end-result. Since I'm still in a prototype stag
e,
> I'm not stuck to the data model, so I'm open to suggestions for improvemen
t
> to the design or suggestions on the reporting issue. I hope all this make
s
> sense, and thanks in advance.
>
> Note: All the primary keys are simple identity columns, because the form o
f
> the data that user knows as the key will vary.
> CREATE TABLE [GroupTypes] (
> [grptypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [grptypName] [varchar] (50) NOT NULL ,
> )
> CREATE TABLE [Groups] (
> [grpPk] [int] IDENTITY (1, 1) NOT NULL ,
> [grpName] [varchar] (50) NOT NULL ,
> [grpGroupType_fk] [int] NOT NULL
> )
> CREATE TABLE [ItemTypes] (
> [itmtypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmtypName] [varchar] (50) NOT NULL ,
> [itmtypParent] [int] NULL ,
> [itmtypGroupType_fk] [int] NOT NULL
> )
> CREATE TABLE [Items] (
> [itmPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmName] [varchar] (50) NOT NULL ,
> [itmItemType_fk] [int] NOT NULL ,
> [itmGroup_fk] [int] NOT NULL ,
> [itmParent] [int] NULL
> )
> CREATE TABLE [ItemDetailTypes] (
> [dtltypPk] [int] IDENTITY (1, 1) NOT NULL ,
> [dtltypName] [varchar] (50) NOT NULL ,
> )
> CREATE TABLE [ItemDetail] (
> [itmdtlPk] [int] IDENTITY (1, 1) NOT NULL ,
> [itmdtlCost] [int] NOT NULL ,
> [itmdtlItem_fk] [int] NOT NULL ,
> [itmdtlDetailType_fk] [int] NOT NULL
> )
>
> /*----*
/
>
> DECLARE @.itmtyp2 INT
> DECLARE @.itmtyp3 INT
> DECLARE @.itm3 INT
> DECLARE @.itm6 INT
> DECLARE @.dtltyp1 INT
>
> INSERT INTO GroupTypes (grptypName) VALUES ('Group Type 1')
> SET @.grptyp1 = @.@.IDENTITY
> INSERT INTO Groups (grpName, grpGroupType_fk) VALUES ('Group1', @.grptyp1)
> SET @.grp1 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 1', NULL, @.grptyp1)
> SET @.itmtyp1 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 2', @.itmtyp1, @.grptyp1)
> SET @.itmtyp2 = @.@.IDENTITY
> INSERT INTO ItemTypes (itmtypName,itmtypParent,itmtypGroupType
_fk) VALUES
> ('Item Type 3', @.itmtyp2, @.grptyp1)
> SET @.itmtyp3 = @.@.IDENTITY
>
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item1', @.itmtyp1, @.grp1, NULL)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item2', @.itmtyp2, @.grp1, @.@.IDENTITY)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item3', @.itmtyp3, @.grp1, @.@.IDENTITY)
> SET @.itm3 = @.@.IDENTITY
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item4', @.itmtyp1, @.grp1, NULL)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item5', @.itmtyp2, @.grp1, @.@.IDENTITY)
> INSERT INTO Items (itmName,itmItemType_fk,itmGroup_fk,itmP
arent) VALUES
> ('Item6', @.itmtyp3, @.grp1, @.@.IDENTITY)
> SET @.itm6 = @.@.IDENTITY
> INSERT INTO ItemDetailTypes (dtltypName) VALUES ('Detail Type 1')
> SET @.dtltyp1 = @.@.IDENTITY
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (10, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (20, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (30, @.itm3, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (15, @.itm6, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (25, @.itm6, @.dtltyp1)
> INSERT INTO ItemDetail (itmdtlCost,itmdtlItem_fk,itmdtlDetailTy
pe_fk) VALU
ES
> (30, @.itm6, @.dtltyp1)
> /*----*
/
> Flattened data for Group1:
> Group1 Item1 Item2 Item3
> Group1 Item4 Item5 Item6
> I believe this is an easy structure on which to report, and I could just
> INNER JOIN on the ItemDetail table for Cost information.
>|||Thanks John. I've actually done my due-diligence Googling, but I couldn't
find what I was looking for. My Google results returned _lots_ of results o
n
how to transform a flat dataset into a hierarchical one, but not the reverse
!
I'd like for the users to be able to use generic reporting tools, such as
Access, etc., to be able to report on the data. So I'm looking for a way, i
n
SQL, or more generally, at the database-level, to present the data in a way
that doesn't require special code in order to group/subtotal.
"John Bell" wrote:
> Hi
> The best way to do this is to traverse the hierachy and build up the rows
on
> the the client.
> There are many posts regarding hierarchies in SQL Server, so you may also
> want to search Google for previous posts.
> John
> "Steve" wrote:
>|||Hi
If you return your hierarchy in order then the client can flatten it. This
will be the fastest solution!
For traversing the hierarchy posts like http://tinyurl.com/o3rc are a good
start.
To produce a crosstab output from the above results try something like
http://www.windowsitpro.com/SQLServ...5608/15608.html
John
"Steve" wrote:
> Thanks John. I've actually done my due-diligence Googling, but I couldn't
> find what I was looking for. My Google results returned _lots_ of results
on
> how to transform a flat dataset into a hierarchical one, but not the rever
se!
> I'd like for the users to be able to use generic reporting tools, such as
> Access, etc., to be able to report on the data. So I'm looking for a way,
in
> SQL, or more generally, at the database-level, to present the data in a wa
y
> that doesn't require special code in order to group/subtotal.
>
> "John Bell" wrote:
>

Fixed Database Roles vs Application Roles

After reading Books Online, I am still confused with Database Role vs Application role.

My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.

If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?

Application roles prevent users from having direct permission on the database, and force them to access it via the application. If you grant permission to the users directly, then if you have lots of users, you will have lots of permissions to maintain. Further, with direct permissions, the user could start accessing the database through interfaces such as Microsoft Access which you may not want them to do.

Typically, application roles are used by applications that perform their own user authentication.

Hope this helps.

|||So, if I want users to access business data ONLY from my client application, I should use application roles. However, if I use application roles, does it mean that I don't have to assign any database role to an user? i.e. can I rely totally on application roles plus schema? Besides, what is the best practise to store the password of application roles in my application?|||

Yes. That's correct. If you use application roles, you just assign permissions to that app role. The application then activates it on connection. You don't need to do anything with user permissions.

As far as the password goes, one approach is to store the password encrypted in the registry on the application server. The application decrypts it before calling the setapprole procedure. This allows you to avoid storing the password in application code and allows you to change the password as well without having to recompile the app.

|||

Great questions. I'm new to AppRoles and am looking a good, simple example, showing how to use them, including the necessary setup on sql 2005 to make it all work. Anyone have a suggested link?

TIA,

barkingdog

P.S. BOL is accurate but too fragmented to be useful to a begineer. After I understand how do to something, then I usually appreciate what BOL is saying.

|||

Question:

Why use an application role over using a SQL User account for the application?

thanks,

Nate

|||

You can actually use a SQL account as well, but in previous versions of SQL Server the impersonation features were not as rich as they are in SQL Server 2005, so application roles used to be the only available way. Now you could as well perform an EXECUTE AS with NO REVERT and that would pretty much achieve what you get from using an application role. Also see the CREATE USER WITHOUT LOGIN statement for creating a principal that is sand-boxed to a database.

Raul also wrote an interesting post on users without login, which you might find useful: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx.

Thanks
Laurentiu

|||

This is great! thank you for the information.

Now my final question!

I have an application in which I want the SQL Profiler will see which user is executing what stored procedure or query, but those queries or stored procedures should only be executed from my application, and fail if they try to execute the query from another database app (some of my users have Query Analyzer)

AKA an application should be able to execute the stored procedure as the Logged in user (windows authentication), but if the same user using windows authentication connects to the same database using another application, like query analyzer, they will be denied. So when my DBA looks into the database, they can see in the application field in Query Profiler that Jimmy is executing sp_GetData from "Application1" (I have specified this in the connection string) but Paul is getting denied from executing sp_GetData from the "Query Analyzer" application.

Thanks for your help,

Nate

|||

This is currently not possible, as there is no mechanism available for authenticating applications.

Thanks

Laurentiu

|||I upgraded a db from SQL Server 2000 sp4 to SQL Server 2005 sp2. I've got a legacy app that had an application role associated with it. When I upgraded my db and attempted to launch my application, only one of my log-ins works. It's ironic because that login is not special. It isn't sysadmin or anything like that. In this old app, all the logins required dbo rights to the database and the app itself stuck them into the application role. I tried deleting and recreating my users and that didn't help. I tried a new user and that didn't help either. The application role converted but it isn't associated with a valid schema. (The default schema assigned to it doesn't exist.) Is it possible for an application role to become orphaned? I had problems with a few orphaned users when i was moving my databases over for testing, but I followed microsoft's instructions for dealing with orphaned users and it worked great. I'm stumped.|||

Application roles shouldn’t become orphaned. The root cause of the orphaned users is that the associated SID is invalid after restoring the DB on a different server (because the corresponding login doesn’t exist on the new server).Application roles have a SID, but it is not linked to master DB (or to any other DB), and it is possible to set a default schema on them as well (you can use ALTER APPLICATION ROLE syntax to change the default schema).

It may be possible that the application has a different case for the password, in SQL Server 2005 passwords are case sensitive. If this is the case, try resetting the password using ALTER APPLCIATION ROLE.

http://msdn2.microsoft.com/en-us/library/ms188900.aspx

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

The password for this application role is actually generated in script. See below. That default schema (xyz_user) doesn't exist when I look in SSMS. When I right-click on the application role and select properties, it shows the password as ****'s for security. What's strange is that it says the schemas owned by this role are "db_owner" and "xyz_user." Yet, when I expand the schemas folder, xyz_user isn't there. I tried creating it again, but that didn't help.

/****** Object: ApplicationRole [xyz_user] Script Date: 07/25/2007 13:51:07 ******/

/* To avoid disclosure of passwords, the password is generated in script. */

declare @.idx as int

declare @.randomPwd as nvarchar(64)

declare @.rnd as float

select @.idx = 0

select @.randomPwd = N''

select @.rnd = rand((@.@.CPU_BUSY % 100) + ((@.@.IDLE % 100) * 100) +

(DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))

while @.idx < 64

begin

select @.randomPwd = @.randomPwd + char((cast((@.rnd * 83) as int) + 43))

select @.idx = @.idx + 1

select @.rnd = rand()

end

declare @.statement nvarchar(4000)

select @.statement = N'CREATE APPLICATION ROLE [xyz_user] WITH DEFAULT_SCHEMA = [xyz_user], ' + N'PASSWORD = N' + QUOTENAME(@.randomPwd,'''')

EXEC dbo.sp_executesql @.statement

|||

That is indeed very strange. Can you try to repro it on a new (i.e. test) DB? I am suspecting there is something in the database that is affecting your results.

I tried on SQL Server 2005 Sp2, and I got no schemas owned by [user_xyz] (as expected), and no schema named [user_xyz] (again as expected).

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Fixed Database Roles vs Application Roles

After reading Books Online, I am still confused with Database Role vs Application role.

My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.

If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?

Application roles prevent users from having direct permission on the database, and force them to access it via the application. If you grant permission to the users directly, then if you have lots of users, you will have lots of permissions to maintain. Further, with direct permissions, the user could start accessing the database through interfaces such as Microsoft Access which you may not want them to do.

Typically, application roles are used by applications that perform their own user authentication.

Hope this helps.

|||So, if I want users to access business data ONLY from my client application, I should use application roles. However, if I use application roles, does it mean that I don't have to assign any database role to an user? i.e. can I rely totally on application roles plus schema? Besides, what is the best practise to store the password of application roles in my application?|||

Yes. That's correct. If you use application roles, you just assign permissions to that app role. The application then activates it on connection. You don't need to do anything with user permissions.

As far as the password goes, one approach is to store the password encrypted in the registry on the application server. The application decrypts it before calling the setapprole procedure. This allows you to avoid storing the password in application code and allows you to change the password as well without having to recompile the app.

|||

Great questions. I'm new to AppRoles and am looking a good, simple example, showing how to use them, including the necessary setup on sql 2005 to make it all work. Anyone have a suggested link?

TIA,

barkingdog

P.S. BOL is accurate but too fragmented to be useful to a begineer. After I understand how do to something, then I usually appreciate what BOL is saying.

|||

Question:

Why use an application role over using a SQL User account for the application?

thanks,

Nate

|||

You can actually use a SQL account as well, but in previous versions of SQL Server the impersonation features were not as rich as they are in SQL Server 2005, so application roles used to be the only available way. Now you could as well perform an EXECUTE AS with NO REVERT and that would pretty much achieve what you get from using an application role. Also see the CREATE USER WITHOUT LOGIN statement for creating a principal that is sand-boxed to a database.

Raul also wrote an interesting post on users without login, which you might find useful: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx.

Thanks
Laurentiu

|||

This is great! thank you for the information.

Now my final question!

I have an application in which I want the SQL Profiler will see which user is executing what stored procedure or query, but those queries or stored procedures should only be executed from my application, and fail if they try to execute the query from another database app (some of my users have Query Analyzer)

AKA an application should be able to execute the stored procedure as the Logged in user (windows authentication), but if the same user using windows authentication connects to the same database using another application, like query analyzer, they will be denied. So when my DBA looks into the database, they can see in the application field in Query Profiler that Jimmy is executing sp_GetData from "Application1" (I have specified this in the connection string) but Paul is getting denied from executing sp_GetData from the "Query Analyzer" application.

Thanks for your help,

Nate

|||

This is currently not possible, as there is no mechanism available for authenticating applications.

Thanks

Laurentiu

|||I upgraded a db from SQL Server 2000 sp4 to SQL Server 2005 sp2. I've got a legacy app that had an application role associated with it. When I upgraded my db and attempted to launch my application, only one of my log-ins works. It's ironic because that login is not special. It isn't sysadmin or anything like that. In this old app, all the logins required dbo rights to the database and the app itself stuck them into the application role. I tried deleting and recreating my users and that didn't help. I tried a new user and that didn't help either. The application role converted but it isn't associated with a valid schema. (The default schema assigned to it doesn't exist.) Is it possible for an application role to become orphaned? I had problems with a few orphaned users when i was moving my databases over for testing, but I followed microsoft's instructions for dealing with orphaned users and it worked great. I'm stumped.|||

Application roles shouldn’t become orphaned. The root cause of the orphaned users is that the associated SID is invalid after restoring the DB on a different server (because the corresponding login doesn’t exist on the new server).Application roles have a SID, but it is not linked to master DB (or to any other DB), and it is possible to set a default schema on them as well (you can use ALTER APPLICATION ROLE syntax to change the default schema).

It may be possible that the application has a different case for the password, in SQL Server 2005 passwords are case sensitive. If this is the case, try resetting the password using ALTER APPLCIATION ROLE.

http://msdn2.microsoft.com/en-us/library/ms188900.aspx

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

The password for this application role is actually generated in script. See below. That default schema (xyz_user) doesn't exist when I look in SSMS. When I right-click on the application role and select properties, it shows the password as ****'s for security. What's strange is that it says the schemas owned by this role are "db_owner" and "xyz_user." Yet, when I expand the schemas folder, xyz_user isn't there. I tried creating it again, but that didn't help.

/****** Object: ApplicationRole [xyz_user] Script Date: 07/25/2007 13:51:07 ******/

/* To avoid disclosure of passwords, the password is generated in script. */

declare @.idx as int

declare @.randomPwd as nvarchar(64)

declare @.rnd as float

select @.idx = 0

select @.randomPwd = N''

select @.rnd = rand((@.@.CPU_BUSY % 100) + ((@.@.IDLE % 100) * 100) +

(DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))

while @.idx < 64

begin

select @.randomPwd = @.randomPwd + char((cast((@.rnd * 83) as int) + 43))

select @.idx = @.idx + 1

select @.rnd = rand()

end

declare @.statement nvarchar(4000)

select @.statement = N'CREATE APPLICATION ROLE [xyz_user] WITH DEFAULT_SCHEMA = [xyz_user], ' + N'PASSWORD = N' + QUOTENAME(@.randomPwd,'''')

EXEC dbo.sp_executesql @.statement

|||

That is indeed very strange. Can you try to repro it on a new (i.e. test) DB? I am suspecting there is something in the database that is affecting your results.

I tried on SQL Server 2005 Sp2, and I got no schemas owned by [user_xyz] (as expected), and no schema named [user_xyz] (again as expected).

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Fixed Database Roles vs Application Roles

After reading Books Online, I am still confused with Database Role vs Application role.

My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.

If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?

Application roles prevent users from having direct permission on the database, and force them to access it via the application. If you grant permission to the users directly, then if you have lots of users, you will have lots of permissions to maintain. Further, with direct permissions, the user could start accessing the database through interfaces such as Microsoft Access which you may not want them to do.

Typically, application roles are used by applications that perform their own user authentication.

Hope this helps.

|||So, if I want users to access business data ONLY from my client application, I should use application roles. However, if I use application roles, does it mean that I don't have to assign any database role to an user? i.e. can I rely totally on application roles plus schema? Besides, what is the best practise to store the password of application roles in my application?
|||

Yes. That's correct. If you use application roles, you just assign permissions to that app role. The application then activates it on connection. You don't need to do anything with user permissions.

As far as the password goes, one approach is to store the password encrypted in the registry on the application server. The application decrypts it before calling the setapprole procedure. This allows you to avoid storing the password in application code and allows you to change the password as well without having to recompile the app.

|||

Great questions. I'm new to AppRoles and am looking a good, simple example, showing how to use them, including the necessary setup on sql 2005 to make it all work. Anyone have a suggested link?

TIA,

barkingdog

P.S. BOL is accurate but too fragmented to be useful to a begineer. After I understand how do to something, then I usually appreciate what BOL is saying.

|||

Question:

Why use an application role over using a SQL User account for the application?

thanks,

Nate

|||

You can actually use a SQL account as well, but in previous versions of SQL Server the impersonation features were not as rich as they are in SQL Server 2005, so application roles used to be the only available way. Now you could as well perform an EXECUTE AS with NO REVERT and that would pretty much achieve what you get from using an application role. Also see the CREATE USER WITHOUT LOGIN statement for creating a principal that is sand-boxed to a database.

Raul also wrote an interesting post on users without login, which you might find useful: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx.

Thanks
Laurentiu

|||

This is great! thank you for the information.

Now my final question!

I have an application in which I want the SQL Profiler will see which user is executing what stored procedure or query, but those queries or stored procedures should only be executed from my application, and fail if they try to execute the query from another database app (some of my users have Query Analyzer)

AKA an application should be able to execute the stored procedure as the Logged in user (windows authentication), but if the same user using windows authentication connects to the same database using another application, like query analyzer, they will be denied. So when my DBA looks into the database, they can see in the application field in Query Profiler that Jimmy is executing sp_GetData from "Application1" (I have specified this in the connection string) but Paul is getting denied from executing sp_GetData from the "Query Analyzer" application.

Thanks for your help,

Nate

|||

This is currently not possible, as there is no mechanism available for authenticating applications.

Thanks

Laurentiu

|||I upgraded a db from SQL Server 2000 sp4 to SQL Server 2005 sp2. I've got a legacy app that had an application role associated with it. When I upgraded my db and attempted to launch my application, only one of my log-ins works. It's ironic because that login is not special. It isn't sysadmin or anything like that. In this old app, all the logins required dbo rights to the database and the app itself stuck them into the application role. I tried deleting and recreating my users and that didn't help. I tried a new user and that didn't help either. The application role converted but it isn't associated with a valid schema. (The default schema assigned to it doesn't exist.) Is it possible for an application role to become orphaned? I had problems with a few orphaned users when i was moving my databases over for testing, but I followed microsoft's instructions for dealing with orphaned users and it worked great. I'm stumped.|||

Application roles shouldn’t become orphaned. The root cause of the orphaned users is that the associated SID is invalid after restoring the DB on a different server (because the corresponding login doesn’t exist on the new server).Application roles have a SID, but it is not linked to master DB (or to any other DB), and it is possible to set a default schema on them as well (you can use ALTER APPLICATION ROLE syntax to change the default schema).

It may be possible that the application has a different case for the password, in SQL Server 2005 passwords are case sensitive. If this is the case, try resetting the password using ALTER APPLCIATION ROLE.

http://msdn2.microsoft.com/en-us/library/ms188900.aspx

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

The password for this application role is actually generated in script. See below. That default schema (xyz_user) doesn't exist when I look in SSMS. When I right-click on the application role and select properties, it shows the password as ****'s for security. What's strange is that it says the schemas owned by this role are "db_owner" and "xyz_user." Yet, when I expand the schemas folder, xyz_user isn't there. I tried creating it again, but that didn't help.

/****** Object: ApplicationRole [xyz_user] Script Date: 07/25/2007 13:51:07 ******/

/* To avoid disclosure of passwords, the password is generated in script. */

declare @.idx as int

declare @.randomPwd as nvarchar(64)

declare @.rnd as float

select @.idx = 0

select @.randomPwd = N''

select @.rnd = rand((@.@.CPU_BUSY % 100) + ((@.@.IDLE % 100) * 100) +

(DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))

while @.idx < 64

begin

select @.randomPwd = @.randomPwd + char((cast((@.rnd * 83) as int) + 43))

select @.idx = @.idx + 1

select @.rnd = rand()

end

declare @.statement nvarchar(4000)

select @.statement = N'CREATE APPLICATION ROLE [xyz_user] WITH DEFAULT_SCHEMA = [xyz_user], ' + N'PASSWORD = N' + QUOTENAME(@.randomPwd,'''')

EXEC dbo.sp_executesql @.statement

|||

That is indeed very strange. Can you try to repro it on a new (i.e. test) DB? I am suspecting there is something in the database that is affecting your results.

I tried on SQL Server 2005 Sp2, and I got no schemas owned by [user_xyz] (as expected), and no schema named [user_xyz] (again as expected).

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Fixed Database Roles vs Application Roles

After reading Books Online, I am still confused with Database Role vs Application role.

My intention is to control the end users' authority on the database, where the end users will access through Winforms client application. With proper assignment of schema and database roles to an user, I believe this will enough to control the permisison of an user.

If this is the case, why Application role exists? When and why should I use Application Role? How is it different from Fixed Database Role?

Application roles prevent users from having direct permission on the database, and force them to access it via the application. If you grant permission to the users directly, then if you have lots of users, you will have lots of permissions to maintain. Further, with direct permissions, the user could start accessing the database through interfaces such as Microsoft Access which you may not want them to do.

Typically, application roles are used by applications that perform their own user authentication.

Hope this helps.

|||So, if I want users to access business data ONLY from my client application, I should use application roles. However, if I use application roles, does it mean that I don't have to assign any database role to an user? i.e. can I rely totally on application roles plus schema? Besides, what is the best practise to store the password of application roles in my application?
|||

Yes. That's correct. If you use application roles, you just assign permissions to that app role. The application then activates it on connection. You don't need to do anything with user permissions.

As far as the password goes, one approach is to store the password encrypted in the registry on the application server. The application decrypts it before calling the setapprole procedure. This allows you to avoid storing the password in application code and allows you to change the password as well without having to recompile the app.

|||

Great questions. I'm new to AppRoles and am looking a good, simple example, showing how to use them, including the necessary setup on sql 2005 to make it all work. Anyone have a suggested link?

TIA,

barkingdog

P.S. BOL is accurate but too fragmented to be useful to a begineer. After I understand how do to something, then I usually appreciate what BOL is saying.

|||

Question:

Why use an application role over using a SQL User account for the application?

thanks,

Nate

|||

You can actually use a SQL account as well, but in previous versions of SQL Server the impersonation features were not as rich as they are in SQL Server 2005, so application roles used to be the only available way. Now you could as well perform an EXECUTE AS with NO REVERT and that would pretty much achieve what you get from using an application role. Also see the CREATE USER WITHOUT LOGIN statement for creating a principal that is sand-boxed to a database.

Raul also wrote an interesting post on users without login, which you might find useful: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx.

Thanks
Laurentiu

|||

This is great! thank you for the information.

Now my final question!

I have an application in which I want the SQL Profiler will see which user is executing what stored procedure or query, but those queries or stored procedures should only be executed from my application, and fail if they try to execute the query from another database app (some of my users have Query Analyzer)

AKA an application should be able to execute the stored procedure as the Logged in user (windows authentication), but if the same user using windows authentication connects to the same database using another application, like query analyzer, they will be denied. So when my DBA looks into the database, they can see in the application field in Query Profiler that Jimmy is executing sp_GetData from "Application1" (I have specified this in the connection string) but Paul is getting denied from executing sp_GetData from the "Query Analyzer" application.

Thanks for your help,

Nate

|||

This is currently not possible, as there is no mechanism available for authenticating applications.

Thanks

Laurentiu

|||I upgraded a db from SQL Server 2000 sp4 to SQL Server 2005 sp2. I've got a legacy app that had an application role associated with it. When I upgraded my db and attempted to launch my application, only one of my log-ins works. It's ironic because that login is not special. It isn't sysadmin or anything like that. In this old app, all the logins required dbo rights to the database and the app itself stuck them into the application role. I tried deleting and recreating my users and that didn't help. I tried a new user and that didn't help either. The application role converted but it isn't associated with a valid schema. (The default schema assigned to it doesn't exist.) Is it possible for an application role to become orphaned? I had problems with a few orphaned users when i was moving my databases over for testing, but I followed microsoft's instructions for dealing with orphaned users and it worked great. I'm stumped.|||

Application roles shouldn’t become orphaned. The root cause of the orphaned users is that the associated SID is invalid after restoring the DB on a different server (because the corresponding login doesn’t exist on the new server).Application roles have a SID, but it is not linked to master DB (or to any other DB), and it is possible to set a default schema on them as well (you can use ALTER APPLICATION ROLE syntax to change the default schema).

It may be possible that the application has a different case for the password, in SQL Server 2005 passwords are case sensitive. If this is the case, try resetting the password using ALTER APPLCIATION ROLE.

http://msdn2.microsoft.com/en-us/library/ms188900.aspx

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

|||

The password for this application role is actually generated in script. See below. That default schema (xyz_user) doesn't exist when I look in SSMS. When I right-click on the application role and select properties, it shows the password as ****'s for security. What's strange is that it says the schemas owned by this role are "db_owner" and "xyz_user." Yet, when I expand the schemas folder, xyz_user isn't there. I tried creating it again, but that didn't help.

/****** Object: ApplicationRole [xyz_user] Script Date: 07/25/2007 13:51:07 ******/

/* To avoid disclosure of passwords, the password is generated in script. */

declare @.idx as int

declare @.randomPwd as nvarchar(64)

declare @.rnd as float

select @.idx = 0

select @.randomPwd = N''

select @.rnd = rand((@.@.CPU_BUSY % 100) + ((@.@.IDLE % 100) * 100) +

(DATEPART(ss, GETDATE()) * 10000) + ((cast(DATEPART(ms, GETDATE()) as int) % 100) * 1000000))

while @.idx < 64

begin

select @.randomPwd = @.randomPwd + char((cast((@.rnd * 83) as int) + 43))

select @.idx = @.idx + 1

select @.rnd = rand()

end

declare @.statement nvarchar(4000)

select @.statement = N'CREATE APPLICATION ROLE [xyz_user] WITH DEFAULT_SCHEMA = [xyz_user], ' + N'PASSWORD = N' + QUOTENAME(@.randomPwd,'''')

EXEC dbo.sp_executesql @.statement

|||

That is indeed very strange. Can you try to repro it on a new (i.e. test) DB? I am suspecting there is something in the database that is affecting your results.

I tried on SQL Server 2005 Sp2, and I got no schemas owned by [user_xyz] (as expected), and no schema named [user_xyz] (again as expected).

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

Fix my SQL "WHERE" Statement!

I'm working on an ASP Web application, and am having syntax issues in
a WHERE statement I'm trying to write that uses the CInt Function on a
field.

Basically, I want to select records using criteria of Race, Gender and
Crime Code. But the Crime Code field in the table is text, and I
cannot change it. I want to use a range of crime codes, so need to
convert it to an integer on-the-fly. Here's what I have in my code so
far:

varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "

varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
PrisonRelease.PID = Defendant.PID_Code "

varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "

varSQL = varSQL & "AND
(IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
(Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
"[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
Between 1800 And 1899) "

When I try to execute this code on my Web page, I get an error. But it
works fine in Access, with some minor syntax changes. What am I
missing?!

Thanks,
Rachel WeedenRachelWeeden@.hotmail.com (Rachel Weeden) wrote in message news:<f5066a28.0408230526.3f881906@.posting.google.com>...
> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden

I think it is because you are using [] brackets which is a access
syntax and not asp.|||RachelWeeden@.hotmail.com (Rachel Weeden) wrote in message news:<f5066a28.0408230526.3f881906@.posting.google.com>...
> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden

I think it is because you are using [] brackets which is a access
syntax and not asp.|||CInt is not supported in SQL-Server. You can use CAST or CONVERT
instead.

IIf it not supported in SQL-Server. You can use the CASE expression,
although it works slightly different, so you will need to rewrite that
part.

Have a look at SQL-Server Books Online for more information and
examples.

Hope this helps,
Gert-Jan

Rachel Weeden wrote:
> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden

--
(Please reply only to the newsgroup)|||"Rachel Weeden" wrote:

> I'm working on an ASP Web application, and am having syntax issues in
> a WHERE statement I'm trying to write that uses the CInt Function on a
> field.
> Basically, I want to select records using criteria of Race, Gender and
> Crime Code. But the Crime Code field in the table is text, and I
> cannot change it. I want to use a range of crime codes, so need to
> convert it to an integer on-the-fly. Here's what I have in my code so
> far:
> varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> PrisonRelease.PID = Defendant.PID_Code "
> varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> varSQL = varSQL & "AND
> (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> Between 1800 And 1899) "
> When I try to execute this code on my Web page, I get an error. But it
> works fine in Access, with some minor syntax changes. What am I
> missing?!
> Thanks,
> Rachel Weeden

Rachel,

[Note: I typed some of the T-SQL code in my newsreader, so formatting and
syntax may be a little goofy, but it should get you started in the right
direction.]

The square brackets are OK in T-SQL. The problem you're having is that your
WHERE clause is using VBA functions. While this is a cool feature in the
JET database engine, you can't use it in T-SQL (or any other DB environment
that I'm aware of). As others have mentioned:

- Use CAST or CONVERT instead of CInt (or any of the VB casting functions
e.g. CStr, CDbl, etc)

- Use CASE instead of IIf

Also,

- In VB, IsNull is a boolean function that returns true if the single
argument is NULL. In SQL Server T-SQL, ISNULL is a function that takes 2
parameters; if the first argument is NULL it returns the second else it
returns the first. For example:

ISNULL(NULL, 1) returns 1
...and...
ISNULL(2, 1) returns 2

A rough translation of your code would go something like (watch out for word
wrap and funny formatting)...

AND (
CASE
WHEN ISNULL(Defendant.[CRIME_CLASSIFICATION_CODE], '') = ''
THEN 9999

WHEN Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9]' AND
Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9][0-9]'
THEN 9999

ELSE
CASE WHEN CONVERT(int, Defendant.[CRIME_CLASSIFICATION_CODE]) BETWEEN
1800 AND 1899
THEN 1
ELSE 0
END
END
)

However, it appears you want something akin to "WHERE
Defendant.[CRIME_CLASSIFICATION_CODE] isn't an appropriate numeric
representation or it is numeric and is inclusively in the range 1800-1899".
If I'm correct, you could use something like this (tested in Query Analyzer
with SQL Server 2000)...

DECLARE @.tab TABLE (
d varchar(32),
ccc varchar(20)
)

INSERT @.tab VALUES ('Num outside range', '1750')
INSERT @.tab VALUES ('Num in range', '1800')
INSERT @.tab VALUES ('Not a num', 'aaa')
INSERT @.tab VALUES ('NULL', NULL)
INSERT @.tab VALUES ('Empty string', '')

SELECT *
FROM @.tab
WHERE CASE WHEN ISNUMERIC(ccc) = 1
THEN
CASE WHEN CONVERT(int, ccc) BETWEEN 1800 AND 1899
THEN 1
ELSE 0
END
ELSE 1
END = 1

This returns everything in the test table except the 'Num outside range'
row.

Craig|||Thanks for all the input, Craig - I have taken some time to look over
your code, and I understand the basics about replacing some of my VB
functions with T-SQL ones. Problem is, I am very inexperienced with
SQL (this page is my first project, really!), so the details are a
little confusing.

For example, I've never heard of T-SQL before. I assumed I was writing
a SQL statement in a VB script on an ASP page...but that's a new
acronym for me! Also, the code you included looks totally different
than anything else on my page, so I am having trouble figuring out
where it all fits in, etc.

But I will look into this a bit more, and I'm sure your suggestions
about CAST, CONVERT, CASE, etc. will come in handy.

Thanks again,
Rachel

"Craig Kelly" <cnkelly.nospam@.nospam.net> wrote in message news:<v5tWc.504132$Gx4.393231@.bgtnsc04-news.ops.worldnet.att.net>...
> "Rachel Weeden" wrote:
> > I'm working on an ASP Web application, and am having syntax issues in
> > a WHERE statement I'm trying to write that uses the CInt Function on a
> > field.
> > Basically, I want to select records using criteria of Race, Gender and
> > Crime Code. But the Crime Code field in the table is text, and I
> > cannot change it. I want to use a range of crime codes, so need to
> > convert it to an integer on-the-fly. Here's what I have in my code so
> > far:
> > varSQL = "SELECT PrisonRelease.*, Defendant.*, Arrest.* "
> > varSQL = varSQL & "FROM PrisonRelease LEFT JOIN (DEFENDANT LEFT JOIN
> > ARREST on DEFENDANT.Defendant_ID = ARREST.Defendant_ID) ON
> > PrisonRelease.PID = Defendant.PID_Code "
> > varSQL = varSQL & "WHERE DEFENDANT.Race_Type_Code_L in (" &
> > varRaceList & ")AND DEFENDANT.Gender In (" & varGenderList & ") "
> > varSQL = varSQL & "AND
> > (IIf(IsNull(Defendant.[CRIME_CLASSIFICATION_CODE]) Or
> > Defendant.[CRIME_CLASSIFICATION_CODE]="" Or
> > (Defendant.[CRIME_CLASSIFICATION_CODE]) Not Like "[0-9][0-9][0-9]" And
> > Defendant.[CRIME_CLASSIFICATION_CODE] Not Like
> > "[0-9][0-9][0-9][0-9]"),9999,CInt(Defendant.[CRIME_CLASSIFICATION_CODE])
> > Between 1800 And 1899) "
> > When I try to execute this code on my Web page, I get an error. But it
> > works fine in Access, with some minor syntax changes. What am I
> > missing?!
> > Thanks,
> > Rachel Weeden
> Rachel,
> [Note: I typed some of the T-SQL code in my newsreader, so formatting and
> syntax may be a little goofy, but it should get you started in the right
> direction.]
> The square brackets are OK in T-SQL. The problem you're having is that your
> WHERE clause is using VBA functions. While this is a cool feature in the
> JET database engine, you can't use it in T-SQL (or any other DB environment
> that I'm aware of). As others have mentioned:
> - Use CAST or CONVERT instead of CInt (or any of the VB casting functions
> e.g. CStr, CDbl, etc)
> - Use CASE instead of IIf
> Also,
> - In VB, IsNull is a boolean function that returns true if the single
> argument is NULL. In SQL Server T-SQL, ISNULL is a function that takes 2
> parameters; if the first argument is NULL it returns the second else it
> returns the first. For example:
> ISNULL(NULL, 1) returns 1
> ...and...
> ISNULL(2, 1) returns 2
> A rough translation of your code would go something like (watch out for word
> wrap and funny formatting)...
> AND (
> CASE
> WHEN ISNULL(Defendant.[CRIME_CLASSIFICATION_CODE], '') = ''
> THEN 9999
> WHEN Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9]' AND
> Defendant.[CRIME_CLASSIFICATION_CODE] Not Like '[0-9][0-9][0-9][0-9]'
> THEN 9999
> ELSE
> CASE WHEN CONVERT(int, Defendant.[CRIME_CLASSIFICATION_CODE]) BETWEEN
> 1800 AND 1899
> THEN 1
> ELSE 0
> END
> END
> )
> However, it appears you want something akin to "WHERE
> Defendant.[CRIME_CLASSIFICATION_CODE] isn't an appropriate numeric
> representation or it is numeric and is inclusively in the range 1800-1899".
> If I'm correct, you could use something like this (tested in Query Analyzer
> with SQL Server 2000)...
> DECLARE @.tab TABLE (
> d varchar(32),
> ccc varchar(20)
> )
> INSERT @.tab VALUES ('Num outside range', '1750')
> INSERT @.tab VALUES ('Num in range', '1800')
> INSERT @.tab VALUES ('Not a num', 'aaa')
> INSERT @.tab VALUES ('NULL', NULL)
> INSERT @.tab VALUES ('Empty string', '')
> SELECT *
> FROM @.tab
> WHERE CASE WHEN ISNUMERIC(ccc) = 1
> THEN
> CASE WHEN CONVERT(int, ccc) BETWEEN 1800 AND 1899
> THEN 1
> ELSE 0
> END
> ELSE 1
> END = 1
> This returns everything in the test table except the 'Num outside range'
> row.
> Craig

First time setting up SQL MSDE, trouble loggin in...

I just installed MSDE and I am getting errors when trying to install the Time Tracker application: "Unable to connect" and my only option is to cancel the installation.

I also get an error when trying to connect using Web Data Administrator: "Invalid username and/or password, or server does not exist. Also, please ensure that SQL Server Authentication is enabled on the server. " I am using the "sa" account with the correct password. I have also tried no password (just in case) and that doesn't work either.

I cannot confirm that I have SQL Server Authentication enabled. I have no idea how to check for this using SQL MSDE. This may be part of the problem...any thoughts on this issue?

My OS: Windows XP Professional Service Pack 1 (Build 2600)

My Web Server: Internet Information Services Version 5.1.2600.0

My .NET Framework: .NET Framework Version 1.1.4322.573

My SQL Server: Microsoft SQL Server Desktop Engine (SQL Server Version 8.00.760)

Other Info: .NET is fine and linked to IIS, all other .NET apps compile/run fine. SQL Server Service Manager is giving me the green arrow. My OS is MS WinXP Pro, all SP's and hot fixes and updates applied.

I will be monitoring this post and trying any suggestions right away & posting my success/failure right back here.

If anyone would like to see a screen shot of the error generated by the Time Tracker install, use the following link:

http://www.versyss.com/mike/shot.gif

Thanks in advance!First, to determine with authentication mode you are using, go to the Windows command prompt and type this command:

osql -d Master -Q "xp_loginconfig" -E

Terri|||Results of: osql -d Master -Q "xp_loginconfig" -E

Name: Config_Value
--------
login mode:Windows NT Authentication
default login:guest
default domain:Null
audit level:none
set hostname:false
map _ :domain separator
map $:Null
map #:-

So it looks like I am using Win. NT Auth. mode. Now how do I switch this to SQL Server Auth. or Mixed Mode? Remember this is a MSDE installation so I have no Enterprise Manager...

Thanks sof the help so far....Mike|||Without Enterprise Manager, you could edit the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

Change LoginMode to 1 for "Windows Only", 2 for "SQL Server and Windows"

As always, use care when editing the registry. It should be backed up first. If you've never edited the registry, you should read up on it first. I claim no responsibility!

Terri|||Terri, you RULE!

It worked. I edited the reg value you indicated, rebooted, ran "osql -d Master -Q "xp_loginconfig" -E" and verified my mode (which now reads "mixed") and was able to log into my server using Web Data Admin! Thanks so much! I hope this posts helps some other people as well!

P.S. The use of "localhost" didn't work for the server. I think I read somewhere that the "localhost" option has been disabled in MSDE. I don't know for sure but all I know is that "localhost" didn't work for me but the server name and IP address do...

Thanks again Terri...

first report long loaded

when I start work with my application to load the first report take long time.

after its load good.

my first question is:
Is it possible to load the report server when I start work with my application?

second:
I have a matrix report, is it possible to show all the column, but when I print, to print just the last 4 columns?

thanks alot
and good dayTry this to fasten your report:
Go into IIS >
Open Application Pool (above the "web sites" you probably know) >
Right click your reporting service pool and choose properties >
Choose "performance" tab and remove the selection from the "Shutdown worker process".

You can also try and change (in the first tab) the recycle worker process.
select the "recycle worker process" and give it number of minutes that is about 2-3 days.

HTH,
Roy.

First connection from Vista client to SQL Server 2005 times out

This is very odd.
I have a VB6 application. I have fixed it up to play nice with Vista,
and embedded a manifest (contents at the end of this post) into the
exe.
When I try to connect to the SQl Server 2005 server from a Vista
client, I get a timeout on the first try. My app asks to verify the db
settings (server name, db name, etc.), and I do that, retry and it
connects fine. It does this every time. It only fails to connect on
the first try, it always connects on the second try.
The app has an optional second database it can connect to, and it does
the same thing. Times out on the first connection attempt, click the
retry connection button and it connects just fine.
But here's the weird part. If I recompile the app, and do not embed a
manifest, it works fine with no other changes. So the manifest is
cause this for some reason.
Here's what the connect string looks like:
Provider=SQLOLEDB;Server=10.0.0.167;UID=;PWD=;DATA BASE=thedb;Trusted_connection=yes;Persist
Security Info=True
It doesn't matter if I use a trusted connection and "NT
authentication" or supply a user name/password for SQL Server
authentication. It doesn't matter if I put the server name or the IP
address. It always times out on the first connection and works on the
second connection, but only if there is a manifest embedded (otherwise
it connects fine on the first try).
(And yes, I'm aware the Persist Security Info is a no no -- the app
was copying the connect string around in a couple of places, and I'm
in the process of cleaning that up so this is temporary).
Here's what the manifest looks like:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1"
manifestVersion="1.0">
<trustInfo xmlns="urn:schemas-microsoft-com:asm.v3">
<security>
<requestedPrivileges>
<requestedExecutionLevel level="asinvoker"/>
</requestedPrivileges>
</security>
</trustInfo>
</assembly>
In this case, I am logged in from a domain user ID with domain
administrator permissions. Haven't even started trying to test it as a
mortal user yet.
Any suggestions?
Problem solved.
The XP2 machine hosting the SQL Server 2005 recently had a third-party
firewall installed and it was not configured properly. The "SQL
Server" allows did not allow everything that was needed. Opening port
1433 for TCP fixed the problem.
So, it is not a Vista problem, or a SQL Server 2005 problem, or a
problem with our app. Just a firewall configuration problem.
Doesn't explain why the app works without a manifest, or why it
connects fine the second time (the server is setup to allow several
protocols, so maybe the client uses a different one if TCP/IP doesn't
work). Makes me suspect how good the firewall actually is, though.
Oh, and it USED to work from other clients (XP and Win2K), but not
after installing the firewall, which I confirmed after going back to
an XP client to test again to try to see what was different.
My bad, never mind, carry on...
On Jul 13, 4:18 pm, rn...@.rviews.com wrote:
> This is very odd.
> I have a VB6 application. I have fixed it up to play nice with Vista,
> and embedded a manifest (contents at the end of this post) into the
> exe.
> When I try to connect to the SQl Server 2005 server from a Vista
> client, I get a timeout on the first try. My app asks to verify the db
> settings (server name, db name, etc.), and I do that, retry and it
> connects fine. It does this every time. It only fails to connect on
> the first try, it always connects on the second try.
> The app has an optional second database it can connect to, and it does
> the same thing. Times out on the first connection attempt, click the
> retry connection button and it connects just fine.
> But here's the weird part. If I recompile the app, and do not embed a
> manifest, it works fine with no other changes. So the manifest is
> cause this for some reason.
> Here's what the connect string looks like:
> Provider=SQLOLEDB;Server=10.0.0.167;UID=;PWD=;DATA BASE=thedb;Trusted_connecXtion=yes;Persist
> Security Info=True
> It doesn't matter if I use a trusted connection and "NT
> authentication" or supply a user name/password for SQL Server
> authentication. It doesn't matter if I put the server name or the IP
> address. It always times out on the first connection and works on the
> second connection, but only if there is a manifest embedded (otherwise
> it connects fine on the first try).
> (And yes, I'm aware the Persist Security Info is a no no -- the app
> was copying the connect string around in a couple of places, and I'm
> in the process of cleaning that up so this is temporary).
> Here's what the manifest looks like:
> <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
> <assembly xmlns="urn:schemas-microsoft-com:asm.v1"
> manifestVersion="1.0">
> <trustInfo xmlns="urn:schemas-microsoft-com:asm.v3">
> <security>
> <requestedPrivileges>
> <requestedExecutionLevel level="asinvoker"/>
> </requestedPrivileges>
> </security>
> </trustInfo>
> </assembly>
> In this case, I am logged in from a domain user ID with domain
> administrator permissions. Haven't even started trying to test it as a
> mortal user yet.
> Any suggestions?

First connection from Vista client to SQL Server 2005 times out

This is very odd.
I have a VB6 application. I have fixed it up to play nice with Vista,
and embedded a manifest (contents at the end of this post) into the
exe.
When I try to connect to the SQl Server 2005 server from a Vista
client, I get a timeout on the first try. My app asks to verify the db
settings (server name, db name, etc.), and I do that, retry and it
connects fine. It does this every time. It only fails to connect on
the first try, it always connects on the second try.
The app has an optional second database it can connect to, and it does
the same thing. Times out on the first connection attempt, click the
retry connection button and it connects just fine.
But here's the weird part. If I recompile the app, and do not embed a
manifest, it works fine with no other changes. So the manifest is
cause this for some reason.
Here's what the connect string looks like:
Provider=SQLOLEDB;Server=10.0.0. 167;UID=;PWD=;DATABASE=thedb;Trusted_con
nect
ion=yes;Persist
Security Info=True
It doesn't matter if I use a trusted connection and "NT
authentication" or supply a user name/password for SQL Server
authentication. It doesn't matter if I put the server name or the IP
address. It always times out on the first connection and works on the
second connection, but only if there is a manifest embedded (otherwise
it connects fine on the first try).
(And yes, I'm aware the Persist Security Info is a no no -- the app
was copying the connect string around in a couple of places, and I'm
in the process of cleaning that up so this is temporary).
Here's what the manifest looks like:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<assembly xmlns="urn:schemas-microsoft-com:asm.v1"
manifestVersion="1.0">
<trustInfo xmlns="urn:schemas-microsoft-com:asm.v3">
<security>
<requestedPrivileges>
<requestedExecutionLevel level="asinvoker"/>
</requestedPrivileges>
</security>
</trustInfo>
</assembly>
In this case, I am logged in from a domain user ID with domain
administrator permissions. Haven't even started trying to test it as a
mortal user yet.
Any suggestions?Problem solved.
The XP2 machine hosting the SQL Server 2005 recently had a third-party
firewall installed and it was not configured properly. The "SQL
Server" allows did not allow everything that was needed. Opening port
1433 for TCP fixed the problem.
So, it is not a Vista problem, or a SQL Server 2005 problem, or a
problem with our app. Just a firewall configuration problem.
Doesn't explain why the app works without a manifest, or why it
connects fine the second time (the server is setup to allow several
protocols, so maybe the client uses a different one if TCP/IP doesn't
work). Makes me suspect how good the firewall actually is, though.
Oh, and it USED to work from other clients (XP and Win2K), but not
after installing the firewall, which I confirmed after going back to
an XP client to test again to try to see what was different.
My bad, never mind, carry on...
On Jul 13, 4:18 pm, rn...@.rviews.com wrote:
> This is very odd.
> I have a VB6 application. I have fixed it up to play nice with Vista,
> and embedded a manifest (contents at the end of this post) into the
> exe.
> When I try to connect to the SQl Server 2005 server from a Vista
> client, I get a timeout on the first try. My app asks to verify the db
> settings (server name, db name, etc.), and I do that, retry and it
> connects fine. It does this every time. It only fails to connect on
> the first try, it always connects on the second try.
> The app has an optional second database it can connect to, and it does
> the same thing. Times out on the first connection attempt, click the
> retry connection button and it connects just fine.
> But here's the weird part. If I recompile the app, and do not embed a
> manifest, it works fine with no other changes. So the manifest is
> cause this for some reason.
> Here's what the connect string looks like:
> Provider=3DSQLOLEDB;Server=3D10.0.0.167;UID=3D;PWD=3D;DATABASE=3Dthedb;Tr=
usted_connec=ADtion=3Dyes;Persistseagreen">
> Security Info=3DTrue
> It doesn't matter if I use a trusted connection and "NT
> authentication" or supply a user name/password for SQL Server
> authentication. It doesn't matter if I put the server name or the IP
> address. It always times out on the first connection and works on the
> second connection, but only if there is a manifest embedded (otherwise
> it connects fine on the first try).
> (And yes, I'm aware the Persist Security Info is a no no -- the app
> was copying the connect string around in a couple of places, and I'm
> in the process of cleaning that up so this is temporary).
> Here's what the manifest looks like:
> <?xml version=3D"1.0" encoding=3D"UTF-8" standalone=3D"yes"?>
> <assembly xmlns=3D"urn:schemas-microsoft-com:asm.v1"
> manifestVersion=3D"1.0">
> <trustInfo xmlns=3D"urn:schemas-microsoft-com:asm.v3">
> <security>
> <requestedPrivileges>
> <requestedExecutionLevel level=3D"asinvoker"/>
> </requestedPrivileges>
> </security>
> </trustInfo>
> </assembly>
> In this case, I am logged in from a domain user ID with domain
> administrator permissions. Haven't even started trying to test it as a
> mortal user yet.
> Any suggestions?

First attempt to connect fails

I have a C# application that connects to a SQL Server Express 2005 instance. One of the testers here shuts his machine down every night and first thing in the morning when he fires up the application it fails to connect. If he tries to open it again right after that it connects. What would the failed attempt do that would fix the instance?

hi,

what is the reported exception?

regards

|||

If the C# application is using User Instances, the problem is likely a timeout. Check the connection string, if it is specifiying User Instance = TRUE, then add 'Connection Timeout=60' and the problem will likely go away. The issue is that a User Instance has to be started when the application is started and depending on hardware, it can take a bit longer than the default connection time out. Once it has started, it will hang around for 60 minutes after the last connection is dropped, so the second time you run the applciation, the User Instance is running and the connection can be made within the default timeout setting.

Mike

|||

The connection string doesn't have user instance in it. Should I just not be using user instances?

|||YOu don′t have to, depending on the machine and the workload it also could be that the database is closed again and will have to open after reconnecting. The autoclose option can be either turned off (turned on by default for Express instances) or the connection timeout can be increased as Mike pointed out.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Firing a java application from stored procedure

Hey all,
I've got a question and after doing some research I've found only a vague reference but no clear answer.

I have a java app that will be passing parameters to my stored procedure. I'll grab the requested info from the tables but instead of sending it back to the java app that sent the request, I need to send it to a "different" java app (the second java app will not be running at the time).

Can someone point me to a good source for executing java applications from a stored procedure?

Thanks in advance ...
tamSee this http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html link is any help.

Another link http://www.sswug.org/searchresults.asp%3Fkeywordstofind%3Djava,%2520sto red%2520procedures for information.

firewalls? port numbers? ancient curses cast upon my servers?

Hello All,
Hopefully someone has come across this...

I have the client side of my application installed in the US,
with the application and the database servers running in london.

The program language is C#, all built in .net, with the .net installer.
When the user in the US runs the program and it gets to the
import progress part (background processing occurring on London servers),
they are getting no feedback as to the progress of the import.
ie, nothing is being sent to the US from the london server.
But everything runs smooth the other way, ie, tables are written to
the sql db's in london etc... And I get data sending and receiving
here in london when i run the client app on my machine and send to
the servers here, so its some problem with the connection with the US....
firewalls? port numbers? ancient curses cast upon my servers?

Cheers mike

Is this using SQL Server Integration Services (the SQL Server 2005 replacement for DTS)?
(In any case, if the "import" part isn't giving feedback, you probably should investigate the "import" part, to see what it does, and revise it to give feedback -- it may be hard for anyone here to know what this "import" part is -- if you suspect a connection problem, network sniffing, or even simple use of sysinternals tcpview may be helpful.)

firewalls? port numbers? ancient curses cast upon my servers?

Hello All
I have the client side of my application installed in the US, with the
application and the database servers running in london.
The program language is C#, all built in .net, with the .net installer. When
the user in the US runs the program and it gets to the import progress part
(background processing occurring on London servers), they are getting no
feedback as to the progress of the import. ie, nothing is being sent to the
US from the london server. But everything runs smooth the other way, ie,
tables are written to the sql db's in london etc... And I get data sending
and receiving here in london when i run the client app on my machine and send
to the servers here, so its some problem with the connection with the US...
firewalls' port numbers' ancient curses cast upon my servers' anyone come
across anything like this?
Cheers mikeHave you tested access with Query Analyzer to see if you can connect, access
the database from both places and perform the import tasks? It's unclear
from your post whether you're just having problems with the import task or
if it's even connecting.
joe.
"blomm via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:54B0800E732D8@.SQLMonster.com...
> Hello All
> I have the client side of my application installed in the US, with the
> application and the database servers running in london.
> The program language is C#, all built in .net, with the .net installer.
> When
> the user in the US runs the program and it gets to the import progress
> part
> (background processing occurring on London servers), they are getting no
> feedback as to the progress of the import. ie, nothing is being sent to
> the
> US from the london server. But everything runs smooth the other way, ie,
> tables are written to the sql db's in london etc... And I get data
> sending
> and receiving here in london when i run the client app on my machine and
> send
> to the servers here, so its some problem with the connection with the
> US...
> firewalls' port numbers' ancient curses cast upon my servers' anyone
> come
> across anything like this?
> Cheers mike|||"It's unclear
from your post whether you're just having problems with the import task or
if it's even connecting."
Sorry, i will try and clarify:
it is connecting, theres problems with the callback functionality i think, if
that means anything to you.
okay, thanks for your pointers, i'm off to follow them up.
m

Firewall

We have several clients using one application on their Laptop to connect to Sql Server using either the dial-up or vpn client on their machine.Each client is behind the firewall.The query timeout on the sql server has been set to 0. Six out of 10 cases the application works fine,but when you let some screen open on the client's machine without any activity for may be 5 minutes(the application screen using sql server),come back and try to save the changes made by you and It's giving us the "Connection failure Error".
The same thing is not happeing over the LAN.

Any help in this matter is greatly appriciated.Can you modify the application to support disconnected recordsets and just update the changes as a batch ? Does it timeout on all of the machines outside of the firewall after the 5 minute mark ? Are you using connection pooling ?|||What is generating the error "Connection Failure" ? Is this the exact error message ?|||[DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.|||Are you able to access anything else behind the firewall - or do you lose all network access to the network behind the firewall ?|||Last time we were checking the connection behaviour in the Sql Server 2000 by using the sp_who command line and after some time(may be 6-8 minutes)we realized that there are no active connections between the Client and the Server.We think that the firewall is disconnecting the client with no activity after 10 minutes.Still searching|||We are using the CISCO firewall