Help with Custom Security Extension and Application Pool Identity

This is an interesting one.
We have implemented a custom security extension that we call from
within an application. We pass in a userid and password into the
LogonUser method, which is then checked against our database.
However, we have a Master database, and then a few other small
databases for different clients, so the extension uses another
supplied value to go to the master database, and lookup which database
it needs to verify the user in.
So, in the RSReportServer.config file, we store the connection string
to the Master DB.
This is used to connect to the Master DB, and then lookup the
connection string for the secondary DB.
The connection string for the secondary DB uses Integrated
Authentication (as our application requires this).
When the Application Pool (in IIS) that ReportServer runs under, is
set to the NetworkUser, we get an error returned from the WebService
(called from within our application), which says that the Network
Service was not authorized to access the secondary DB. Understandable
:)
So, we changed the Application Pool, so that it uses a Domain account
as it's Identity. Now we receive the following error back (and in all
the log files I can find)..
System.Web.Services.Protocols.SoapException: An internal error
occurred on the report server. See the error log for more details.
--> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for
more details. --> System.IO.FileNotFoundException: The system cannot
find the file specified. at
System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
errorCode, IntPtr errorInfo) at
RSManagedCrypto.RSCrypto.ExportPublicKey() at
Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
at Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
at Microsoft.ReportingServices.Library.Storage.get_Connection() at
Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
storedProcedureName) at
Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(String
key) at Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty(String
name) at Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
name) at Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(String
name) at Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
source) at Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path, Boolean validate, Boolean convert, Boolean translate) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
path) at Microsoft.ReportingServices.Diagnostics.CatalogItemContext..ctor(IPathTranslator
pathTranslator, String userSuppliedPath, String parameterName) at
Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
String operation, SearchCondition[] properties) -- End of inner
exception stack trace -- at
Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
String operation, SearchCondition[] properties) at
Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
Conditions, CatalogItem[]& Items) -- End of inner exception stack
trace -- at Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
Conditions, CatalogItem[]& Items)
The Application Pool User is a member of the IIS_WPG group, and has
been granted write access to the Windows\Temp folder, and the MSSQL
folder where RS is installed (and sub folders).
I also tried adding it to the local machine admin group, and it made
no difference.
That user also has DBO access to all the DBs on our database server.
Can anyone help please?
Thanks
RichardWill you send the report server web service log file?
Also, can you verify that the new identity has a user profile on the machine
(the user should have an entry under c:\Documents and Settings)?
--
This posting is provided "AS IS" with no warranties, and confers no rights
"Richard Greenwell" <lazygun@.gmail.com> wrote in message
news:42b7583d.0406251224.18af1b8@.posting.google.com...
> This is an interesting one.
> We have implemented a custom security extension that we call from
> within an application. We pass in a userid and password into the
> LogonUser method, which is then checked against our database.
> However, we have a Master database, and then a few other small
> databases for different clients, so the extension uses another
> supplied value to go to the master database, and lookup which database
> it needs to verify the user in.
> So, in the RSReportServer.config file, we store the connection string
> to the Master DB.
> This is used to connect to the Master DB, and then lookup the
> connection string for the secondary DB.
> The connection string for the secondary DB uses Integrated
> Authentication (as our application requires this).
> When the Application Pool (in IIS) that ReportServer runs under, is
> set to the NetworkUser, we get an error returned from the WebService
> (called from within our application), which says that the Network
> Service was not authorized to access the secondary DB. Understandable
> :)
> So, we changed the Application Pool, so that it uses a Domain account
> as it's Identity. Now we receive the following error back (and in all
> the log files I can find)..
> System.Web.Services.Protocols.SoapException: An internal error
> occurred on the report server. See the error log for more details.
> -->
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more details. --> System.IO.FileNotFoundException: The system cannot
> find the file specified. at
> System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32
> errorCode, IntPtr errorInfo) at
> RSManagedCrypto.RSCrypto.ExportPublicKey() at
> Microsoft.ReportingServices.Library.ConnectionManager.GetEncryptionKey()
> at Microsoft.ReportingServices.Library.ConnectionManager.ConnectStorage()
> at
Microsoft.ReportingServices.Library.ConnectionManager.VerifyConnection()
> at Microsoft.ReportingServices.Library.ConnectionManager.get_Connection()
> at Microsoft.ReportingServices.Library.Storage.get_Connection() at
> Microsoft.ReportingServices.Library.Storage.NewStandardSqlCommand(String
> storedProcedureName) at
>
Microsoft.ReportingServices.Library.DBInterface.GetOneConfigurationInfo(Stri
ng
> key) at
Microsoft.ReportingServices.Library.CachedSystemProperties.GetSystemProperty
(String
> name) at
Microsoft.ReportingServices.Library.CachedSystemProperties.Get(String
> name) at
Microsoft.ReportingServices.Library.CachedSystemProperties.GetParameter(Stri
ng
> name) at
Microsoft.ReportingServices.Library.RSService.get_MyReportsEnabled()
> at Microsoft.ReportingServices.Library.RSService.PathToInternal(String
> source) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path, Boolean validate, Boolean convert, Boolean translate) at
> Microsoft.ReportingServices.Diagnostics.CatalogItemContext.SetPath(String
> path) at
Microsoft.ReportingServices.Diagnostics.CatalogItemContext..ctor(IPathTransl
ator
> pathTranslator, String userSuppliedPath, String parameterName) at
> Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
> String operation, SearchCondition[] properties) -- End of inner
> exception stack trace -- at
> Microsoft.ReportingServices.Library.RSService.FindItems(String folder,
> String operation, SearchCondition[] properties) at
> Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
> Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
> Conditions, CatalogItem[]& Items) -- End of inner exception stack
> trace -- at
Microsoft.ReportingServices.WebServer.ReportingService.FindItems(String
> Folder, BooleanOperatorEnum BooleanOperator, SearchCondition[]
> Conditions, CatalogItem[]& Items)
> The Application Pool User is a member of the IIS_WPG group, and has
> been granted write access to the Windows\Temp folder, and the MSSQL
> folder where RS is installed (and sub folders).
> I also tried adding it to the local machine admin group, and it made
> no difference.
> That user also has DBO access to all the DBs on our database server.
> Can anyone help please?
> Thanks
> Richard|||I have emailed you the log file for this problem.
The user that the application pool runs under does not have an entry
under Docs and Settings, but it is a different user to that which the
Report Server Windows Service runs under, which Does have an entry
under Docs and Settings.
I have never seen an app pool user have a Docs and Settings folder :)
Thank you
Richard
"Brian Hartman [MSFT]" <brianhartman@.hotmail.com> wrote in message news:<OTBYLlYXEHA.748@.TK2MSFTNGP11.phx.gbl>...
> Will you send the report server web service log file?
> Also, can you verify that the new identity has a user profile on the machine
> (the user should have an entry under c:\Documents and Settings)?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
>
<snip>sql

Help with a query please....

I have two tables, data_branchAddOns:
CREATE TABLE [dbo].[data_branchAddOns] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[branchId] [int] NOT NULL ,
[name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[displayText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[cost] [decimal](5, 2) NOT NULL ,
[ipt] [decimal](5, 2) NOT NULL ,
[adminCharge] [decimal](5, 2) NOT NULL ,
[deleted] [bit] NULL
)
data_branchDetails:
CREATE TABLE [dbo].[data_branchDetails] (
[id] [int] IDENTITY (1000, 1) NOT NULL ,
[name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[address4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[postcode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[telephone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[fax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[b2b] [bit] NOT NULL ,
[discountOffPlan] [decimal](5, 2) NOT NULL ,
[discount1to4] [decimal](5, 2) NOT NULL ,
[discount5to19] [decimal](5, 2) NOT NULL ,
[discount20plus] [decimal](5, 2) NOT NULL ,
[loading] [decimal](5, 2) NOT NULL ,
[dateAdded] [datetime] NOT NULL ,
[deleted] [bit] NOT NULL
)
One branch in branchDetails can have many branchAddOns. The key is ID
in branchDetails, and the foreign key in branchAddOns is branchId.
I have a problem in that I need to return a dataset in the form:
BranchName AddOnName1 AddOnCost1 AddOnName2 AddOnCost2 AddOnName3
AddOnCost3 ... to 5
So basically I'm turning the table on its side and mapping the related
records to be fields. Any idea how I can write a query to do this?
There are only ever 5 'Add Ons' related to each 'Branch' so I am okay
to map the field names in my procedure as I dont think it would be
possible to have a dynamic number of fields returned?
Thanks in advanceHi Chris,
you can write a correlated subquery and map different addon to different
column of your result set if there are a specific number of addons.
If there are differen number of addons for different branches, then the best
way is to fix the schema ouput with the max number of addon column and do th
e
same thing. You might wanna just load them with NULLS or something as per
your requirement.
hope this helps
Abhishek
"Chris Ashley" wrote:

> I have two tables, data_branchAddOns:
> CREATE TABLE [dbo].[data_branchAddOns] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [branchId] [int] NOT NULL ,
> [name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [displayText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [cost] [decimal](5, 2) NOT NULL ,
> [ipt] [decimal](5, 2) NOT NULL ,
> [adminCharge] [decimal](5, 2) NOT NULL ,
> [deleted] [bit] NULL
> )
> data_branchDetails:
> CREATE TABLE [dbo].[data_branchDetails] (
> [id] [int] IDENTITY (1000, 1) NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [postcode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [telephone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [fax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [b2b] [bit] NOT NULL ,
> [discountOffPlan] [decimal](5, 2) NOT NULL ,
> [discount1to4] [decimal](5, 2) NOT NULL ,
> [discount5to19] [decimal](5, 2) NOT NULL ,
> [discount20plus] [decimal](5, 2) NOT NULL ,
> [loading] [decimal](5, 2) NOT NULL ,
> [dateAdded] [datetime] NOT NULL ,
> [deleted] [bit] NOT NULL
> )
> One branch in branchDetails can have many branchAddOns. The key is ID
> in branchDetails, and the foreign key in branchAddOns is branchId.
>
> I have a problem in that I need to return a dataset in the form:
> BranchName AddOnName1 AddOnCost1 AddOnName2 AddOnCost2 AddOnName3
> AddOnCost3 ... to 5
> So basically I'm turning the table on its side and mapping the related
> records to be fields. Any idea how I can write a query to do this?
> There are only ever 5 'Add Ons' related to each 'Branch' so I am okay
> to map the field names in my procedure as I dont think it would be
> possible to have a dynamic number of fields returned?
> Thanks in advance
>|||Hi Chris
You may want to try something like:
SELECT B.name AS BranchName,
( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 1 ) AS AddOnName1,
( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 1 ) AS AddOnCost1,
( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 2 ) AS AddOnName2,
( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELECT
COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id )
= 2 ) AS AddOnCost2,
...
FROM data_branchDetails B
but it may be slow! This may be better!
SELECT B.name AS BranchName,
MAX(CASE WHEN S.Cnt = 1 THEN S.name END ) AS AddOnName1,
MAX(CASE WHEN S.Cnt = 1 THEN S.cost END ) AS AddOnCost1,
MAX(CASE WHEN S.Cnt = 2 THEN S.name END ) AS AddOnName2,
MAX(CASE WHEN S.Cnt = 2 THEN S.cost END ) AS AddOnCost2,
..
FROM data_branchDetails B
LEFT JOIN
( SELECT A.Branchid, A.name, A.Cost,
(SELECT COUNT(*) FROM data_branchAddOns C WHERE A.Branchid = C.Branchid AND
A.Id >= C.Id ) AS cnt
FROM data_branchAddOns A ) S ON S.Branchid = B.id
John
"Chris Ashley" wrote:

> I have two tables, data_branchAddOns:
> CREATE TABLE [dbo].[data_branchAddOns] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [branchId] [int] NOT NULL ,
> [name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [displayText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [cost] [decimal](5, 2) NOT NULL ,
> [ipt] [decimal](5, 2) NOT NULL ,
> [adminCharge] [decimal](5, 2) NOT NULL ,
> [deleted] [bit] NULL
> )
> data_branchDetails:
> CREATE TABLE [dbo].[data_branchDetails] (
> [id] [int] IDENTITY (1000, 1) NOT NULL ,
> [name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [address4] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [postcode] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [telephone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [fax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [website] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [b2b] [bit] NOT NULL ,
> [discountOffPlan] [decimal](5, 2) NOT NULL ,
> [discount1to4] [decimal](5, 2) NOT NULL ,
> [discount5to19] [decimal](5, 2) NOT NULL ,
> [discount20plus] [decimal](5, 2) NOT NULL ,
> [loading] [decimal](5, 2) NOT NULL ,
> [dateAdded] [datetime] NOT NULL ,
> [deleted] [bit] NOT NULL
> )
> One branch in branchDetails can have many branchAddOns. The key is ID
> in branchDetails, and the foreign key in branchAddOns is branchId.
>
> I have a problem in that I need to return a dataset in the form:
> BranchName AddOnName1 AddOnCost1 AddOnName2 AddOnCost2 AddOnName3
> AddOnCost3 ... to 5
> So basically I'm turning the table on its side and mapping the related
> records to be fields. Any idea how I can write a query to do this?
> There are only ever 5 'Add Ons' related to each 'Branch' so I am okay
> to map the field names in my procedure as I dont think it would be
> possible to have a dynamic number of fields returned?
> Thanks in advance
>|||That's really good. Thanks for the help guys.
I was worried I wasn't making any sense, as I am infinitely bad at
explaining SQL problems. :)
Cheers,
Chris
John Bell wrote:
> Hi Chris
> You may want to try something like:
> SELECT B.name AS BranchName,
> ( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 1 ) AS AddOnName1,
> ( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 1 ) AS AddOnCost1,
> ( SELECT A.name FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 2 ) AS AddOnName2,
> ( SELECT A.cost FROM data_branchAddOns A WHERE A.Branchid = B.id AND (SELE
CT
> COUNT(*) FROM data_branchAddOns C WHERE C.Branchid = B.id AND C.id <= A.id
)
> = 2 ) AS AddOnCost2,
> ...
> FROM data_branchDetails B
> but it may be slow! This may be better!
> SELECT B.name AS BranchName,
> MAX(CASE WHEN S.Cnt = 1 THEN S.name END ) AS AddOnName1,
> MAX(CASE WHEN S.Cnt = 1 THEN S.cost END ) AS AddOnCost1,
> MAX(CASE WHEN S.Cnt = 2 THEN S.name END ) AS AddOnName2,
> MAX(CASE WHEN S.Cnt = 2 THEN S.cost END ) AS AddOnCost2,
> ..
> FROM data_branchDetails B
> LEFT JOIN
> ( SELECT A.Branchid, A.name, A.Cost,
> (SELECT COUNT(*) FROM data_branchAddOns C WHERE A.Branchid = C.Branchid A
ND
> A.Id >= C.Id ) AS cnt
> FROM data_branchAddOns A ) S ON S.Branchid = B.id
> John
> "Chris Ashley" wrote:
>|||Hi Chris
The following details the things that will help you get quick answers from
the news groups http://www.aspfaq.com/etiquette.asp particularly
http://www.aspfaq.com/etiquette.asp?id=5006. Having the example data and the
expected results from that data also helps to remove any ambiguity that may
arise from just describing the problem in words.
John
"Chris Ashley" wrote:

> That's really good. Thanks for the help guys.
> I was worried I wasn't making any sense, as I am infinitely bad at
> explaining SQL problems. :)
> Cheers,
> Chris
> John Bell wrote:
>|||Hi John,
Thanks for that.
I've tried to amend the query like so:
SELECT B.name AS BranchName,
(SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
a.name = "homecover") AS AddOn1,
(SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
a.name = "policyfee") AS AddOn2,
(SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
a.name = "legalprotection") AS AddOn3
FROM data_branchDetails B
but I get the error:
Invalid column name 'homecover'.
Any ideas?
TIA,
Chris|||Hi Chris
Try using single quotes instead of the double ones that you have as you have
quoted identifiers set.
John
"Chris Ashley" wrote:

> Hi John,
> Thanks for that.
> I've tried to amend the query like so:
> SELECT B.name AS BranchName,
> (SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
> a.name = "homecover") AS AddOn1,
> (SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
> a.name = "policyfee") AS AddOn2,
> (SELECT a.cost from data_BranchAddOns a WHERE a.branchid = B.id AND
> a.name = "legalprotection") AS AddOn3
> FROM data_branchDetails B
> but I get the error:
> Invalid column name 'homecover'.
> Any ideas?
> TIA,
> Chris
>

Help with a complex UPDATE query

Well, I think it's complex anyway -- you might not :)

TableDef:
CREATE TABLE CustTransactions (
TransactionKey int IDENTITY(1,1) NOT NULL,
CustomerID int,
AmountSpent float,
CustSelected bit default 0);

TransactionKey is the primary key, CustomerID and AmountSpent are both
indexed (non unique).

What I would like to do is, for all of the records in descending order
of "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSE
such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say $50,000).

What I'm doing at the moment is a "SELECT * FROM CustTransactions WHERE
CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
through all the records until AmountSpent 50000, then continuine to
loop through the remainder of the records setting CustSelected = FALSE.
This does exactly what I want but is slow and inefficient. I am sure it
could be done in a single SQL statement with subqueries, but I lack the
knowledge and experience to figure out how.

The closest I can get is:-

UPDATE CustTransactions SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransactions WHERE
(((CustTransactions.CustSelected)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);

However, this mereley ensures only the top 50,000 customers by amount
spent remain "selected", not the top "X" customers whose total spend
is $50,000. I really need to replace the "SELECT TOP 50000" with some
form of "SELECT TOP (X rows until sum(AmountSpent) =50000)".

Is it even possible to achieve what I'm trying to do?

Thanks in advance for any assistance offered!
--
SlowerThanYouHi,

Consider the following sample data:

INSERT INTO CustTransactions VALUES (1, 1000, 0)
INSERT INTO CustTransactions VALUES (2, 1000, 1)
INSERT INTO CustTransactions VALUES (2, 2500, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (3, 30000, 1)
INSERT INTO CustTransactions VALUES (3, 17000, 1)

What is the expected result (the output of SELECT * FROM
CustTransactions) ?

Also consider this sample data:

INSERT INTO CustTransactions VALUES (1, 10000, 0)
INSERT INTO CustTransactions VALUES (2, 20000, 1)
INSERT INTO CustTransactions VALUES (2, 25000, 0)
INSERT INTO CustTransactions VALUES (2, 2500, 0)

What is the expected result in this case ?

Razvan

Slower Than You wrote:

Quote:

Originally Posted by

Well, I think it's complex anyway -- you might not :)
>
TableDef:
CREATE TABLE CustTransactions (
TransactionKey int IDENTITY(1,1) NOT NULL,
CustomerID int,
AmountSpent float,
CustSelected bit default 0);
>
TransactionKey is the primary key, CustomerID and AmountSpent are both
indexed (non unique).
>
What I would like to do is, for all of the records in descending order
of "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSE
such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say $50,000).
>
What I'm doing at the moment is a "SELECT * FROM CustTransactions WHERE
CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
through all the records until AmountSpent 50000, then continuine to
loop through the remainder of the records setting CustSelected = FALSE.
This does exactly what I want but is slow and inefficient. I am sure it
could be done in a single SQL statement with subqueries, but I lack the
knowledge and experience to figure out how.
>
The closest I can get is:-
>
UPDATE CustTransactions SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransactions WHERE
(((CustTransactions.CustSelected)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);
>
However, this mereley ensures only the top 50,000 customers by amount
spent remain "selected", not the top "X" customers whose total spend
is $50,000. I really need to replace the "SELECT TOP 50000" with some
form of "SELECT TOP (X rows until sum(AmountSpent) =50000)".
>
Is it even possible to achieve what I'm trying to do?
>
Thanks in advance for any assistance offered!
--
SlowerThanYou

|||You do not have a table at all; it is an attempt to mimic a deck of
punch cards. You confuse columns and fields, rows and records and use
the wrong data types.

do these transactions create a customer or a sale? Why is there DDL in
narratives? Why did youn use an IDENTITY columns? Why FLOAT for money?

CREATE TABLE SalesTransactions
(sales_nbr INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id),
sales_amt DECIMAL(12,2) NOT NULL);

Quote:

Originally Posted by

Quote:

Originally Posted by

>What I would like to do is, for all of the records [sic] in descending order of "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSE

such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say $50,000). <<

Did you know that SQL has no Boolean data type? That using BIT is
proprietary and an awful coding practice? We updated punch cards like
you are doing because we had no choice about it.

Quote:

Originally Posted by

Quote:

Originally Posted by

>What I'm doing at the moment is a "SELECT * FROM CustTransactions WHERE CustSelected = TRUE ORDER BY AmountSpent;", programatically looping through all the records [sic] until AmountSpent 50000, then continuine to loop through the remainder of the records [sic] setting CustSelected = FALSE.

You did not say what to do about ties; if I have five sales of
$50,000.00 which one would I mark?

give us a RELATIONAL spec and we can probably help you|||Razvan Socol wrote:

Quote:

Originally Posted by

Consider the following sample data:
>
INSERT INTO CustTransactions VALUES (1, 1000, 0)
INSERT INTO CustTransactions VALUES (2, 1000, 1)
INSERT INTO CustTransactions VALUES (2, 2500, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (3, 30000, 1)
INSERT INTO CustTransactions VALUES (3, 17000, 1)
>
What is the expected result (the output of SELECT * FROM
CustTransactions) ?

Hi Razvan,

Thanks for responding. The expected result for the above sample data
would be:-

1, 1000, 0
2, 1000, 0
2, 2500, 0
1, 1000, 1
1, 1000, 1
3, 30000, 1
3, 17000, 1

To clarify this:-

1) The first row is completely ignored because its CustSelected field
is FALSE (as would be any other records where CustSelected = 0)

2) The rows WHERE CustSelected = 1 are sorted in descending order of
AmountSpent (where two or more records have equal values for
AmountSpent, the ordered of them is arbitrary - I don't care).

3) Any rows that would cause the sum of AmountSpent WHERE CustSelected
= 1 to exceed our selection criteria ($50,000) have their
CustSelected value set to 0.

Quote:

Originally Posted by

>
Also consider this sample data:
>
INSERT INTO CustTransactions VALUES (1, 10000, 0)
INSERT INTO CustTransactions VALUES (2, 20000, 1)
INSERT INTO CustTransactions VALUES (2, 25000, 0)
INSERT INTO CustTransactions VALUES (2, 2500, 0)
>
What is the expected result in this case ?

Assuming our "target" figure is 50000 again:-

1, 10000, 0
2, 20000, 1
2, 25000, 0
2, 2500, 0

The three records where CustSelected = 0 are ignored. As a possible
point of additional interest, if the target figure was less than 20000
then row two would have had its CustSelected column set to 0 (because
this would have caused the "target" figure to be exceeded.

I hope I've done a better job of explaining my requirement this time
around!
--
SlowerThanYou|||--CELKO-- wrote:

Quote:

Originally Posted by

You do not have a table at all; it is an attempt to mimic a deck of
punch cards. You confuse columns and fields, rows and records and use
the wrong data types.
>
do these transactions create a customer or a sale? Why is there DDL
in narratives? Why did youn use an IDENTITY columns? Why FLOAT for
money?

Forget about the datatypes; they are largely irrelevant to the problem
I am trying to solve. I have abstracted the problem to attempt to make
it as easy to explain as possible. The real table I am trying to update
is, in fact, not called CustTransactions and has nothing to do with
"customers" and it does, in fact, have a non-monetary floating point
value that is the focus of my update. You are reading more than I
intended into the column names I've used in my example.

Quote:

Originally Posted by

Did you know that SQL has no Boolean data type? That using BIT is
proprietary and an awful coding practice? We updated punch cards like
you are doing because we had no choice about it.

No, I didn't know that SQL has no boolean data type, and that BIT is
proprietary, so thanks for that information. You can pretend it is an
integer type if you prefer. Again, do not read anything into the table
and field names I have used in my abstract example - just assume that
there is a True/False type flag that I need to record for each row
according to the critera I outlined.

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

What I'm doing at the moment is a "SELECT * FROM CustTransactions

WHERE CustSelected = TRUE ORDER BY AmountSpent;", programatically
looping through all the records [sic] until AmountSpent 50000, then
continuine to loop through the remainder of the records [sic] setting
CustSelected = FALSE.
>
You did not say what to do about ties; if I have five sales of
$50,000.00 which one would I mark?
>
give us a RELATIONAL spec and we can probably help you

Please have a look at my reply to Razvan, which I hope describes the
problem I am trying to solve more accurately than my previous post
(which was not as coherent as it might have been, for which I
apologise).

--
SlowerThanYou|||"Slower Than You" <no.way@.josewrote in
news:1163790273.8354.0@.iris.uk.clara.net:

Quote:

Originally Posted by

Well, I think it's complex anyway -- you might not :)
>
TableDef:
CREATE TABLE CustTransactions (
TransactionKey int IDENTITY(1,1) NOT NULL,
CustomerID int,
AmountSpent float,
CustSelected bit default 0);
>
TransactionKey is the primary key, CustomerID and AmountSpent are both
indexed (non unique).
>
What I would like to do is, for all of the records in descending order
of "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSE
such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say $50,000).
>
What I'm doing at the moment is a "SELECT * FROM CustTransactions WHERE
CustSelected = TRUE ORDER BY AmountSpent;", programatically looping
through all the records until AmountSpent 50000, then continuine to
loop through the remainder of the records setting CustSelected = FALSE.
This does exactly what I want but is slow and inefficient. I am sure it
could be done in a single SQL statement with subqueries, but I lack the
knowledge and experience to figure out how.
>
The closest I can get is:-
>
UPDATE CustTransactions SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransactions WHERE
(((CustTransactions.CustSelected)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);
>
However, this mereley ensures only the top 50,000 customers by amount
spent remain "selected", not the top "X" customers whose total spend
is $50,000. I really need to replace the "SELECT TOP 50000" with some
form of "SELECT TOP (X rows until sum(AmountSpent) =50000)".
>
Is it even possible to achieve what I'm trying to do?

See example 4 (cumulative sum) of
http://www.databasejournal.com/feat...10894_3373861_2
HTH

--
For e-mail address, remove the XXs|||Slower Than You wrote:

Quote:

Originally Posted by

Razvan Socol wrote:
>

Quote:

Originally Posted by

Consider the following sample data:

INSERT INTO CustTransactions VALUES (1, 1000, 0)
INSERT INTO CustTransactions VALUES (2, 1000, 1)
INSERT INTO CustTransactions VALUES (2, 2500, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (3, 30000, 1)
INSERT INTO CustTransactions VALUES (3, 17000, 1)

What is the expected result (the output of SELECT * FROM
CustTransactions) ?

>
Hi Razvan,
>
Thanks for responding. The expected result for the above sample data
would be:-
>
1, 1000, 0
2, 1000, 0
2, 2500, 0
1, 1000, 1
1, 1000, 1
3, 30000, 1
3, 17000, 1

The above result has a sum of 49000. From your narrative, I would
expect a result which has a sum of 49500, for example this:

1, 1000, 0
2, 1000, 0
2, 2500, 1
1, 1000, 0
1, 1000, 0
3, 30000, 1
3, 17000, 1

Which one is the correct result ?

Razvan|||Razvan Socol wrote:

Quote:

Originally Posted by

>
Slower Than You wrote:

Quote:

Originally Posted by

Razvan Socol wrote:

Quote:

Originally Posted by

Consider the following sample data:
>
INSERT INTO CustTransactions VALUES (1, 1000, 0)
INSERT INTO CustTransactions VALUES (2, 1000, 1)
INSERT INTO CustTransactions VALUES (2, 2500, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (1, 1000, 1)
INSERT INTO CustTransactions VALUES (3, 30000, 1)
INSERT INTO CustTransactions VALUES (3, 17000, 1)
>
What is the expected result (the output of SELECT * FROM
CustTransactions) ?

Hi Razvan,

Thanks for responding. The expected result for the above sample data
would be:-

1, 1000, 0
2, 1000, 0
2, 2500, 0
1, 1000, 1
1, 1000, 1
3, 30000, 1
3, 17000, 1

>
The above result has a sum of 49000. From your narrative, I would
expect a result which has a sum of 49500, for example this:
>
1, 1000, 0
2, 1000, 0
2, 2500, 1
1, 1000, 0
1, 1000, 0
3, 30000, 1
3, 17000, 1
>
Which one is the correct result ?

You are absolutely right - I was a little to hasty in putting my
response together. The sum of 49500 is correct.
--
SlowerThanYou|||Chris Cheney wrote:

Quote:

Originally Posted by

"Slower Than You" <no.way@.josewrote in
news:1163790273.8354.0@.iris.uk.clara.net:
>

Quote:

Originally Posted by

Well, I think it's complex anyway -- you might not :)

TableDef:
CREATE TABLE CustTransactions (
TransactionKey int IDENTITY(1,1) NOT NULL,
CustomerID int,
AmountSpent float,
CustSelected bit default 0);

TransactionKey is the primary key, CustomerID and AmountSpent are
both indexed (non unique).

What I would like to do is, for all of the records in descending
order of "AmountSpent" where "CustSelected = TRUE", set
CustSelected to FALSE such that the sum of all the AmountSpent
records with CustSelected = TRUE is no greater than a specified
amount (say $50,000).

What I'm doing at the moment is a "SELECT * FROM CustTransactions
WHERE CustSelected = TRUE ORDER BY AmountSpent;", programatically
looping through all the records until AmountSpent 50000, then
continuine to loop through the remainder of the records setting
CustSelected = FALSE. This does exactly what I want but is slow
and inefficient. I am sure it could be done in a single SQL
statement with subqueries, but I lack the knowledge and experience
to figure out how.

The closest I can get is:-

UPDATE CustTransactions SET CustSelected = FALSE
WHERE (CustSelected = TRUE)
AND TransactionKey NOT IN
(SELECT TOP 50000 TransactionKey FROM CustTransactions WHERE
(((CustTransactions.CustSelected)=TRUE))
ORDER BY AmountSpect DESC, TransactionKey ASC);

However, this mereley ensures only the top 50,000 customers by
amount spent remain "selected", not the top "X" customers whose
total spend is $50,000. I really need to replace the "SELECT TOP
50000" with some form of "SELECT TOP (X rows until sum(AmountSpent)
=50000)".

Is it even possible to achieve what I'm trying to do?

>
See example 4 (cumulative sum) of
http://www.databasejournal.com/feat...hp/10894_337386
1_2
>
HTH

Ahah! That helped enormously - thanks, much appreciated.
--
SlowerThanYou|||>No, I didn't know that SQL has no boolean data type, and that BIT is proprietary, so thanks for that information. You can pretend it is an integer type if you prefer. Again, do not read anything into the table and field [sic] names .. <<

O)kay. You have SERIOUS conceptual problems with SQL and RDBMS. The
reason that SQL has no BOOLEAN data types is one of those "mathematical
foundations" things that has to do with NULLs, 3-valued logic and
logic. In 25 words or less, we discover a state of being via
predicates rather than by looking for a flag.

In procedural, step-by-step file system models you set flags in step
(n) to pass control information to step (n+1) of the process. In the RM
model, multiple users can change the basic facts of a schema and thus
the criteria of the subset, so we do not store computed columns. You
compute subset membership at run time.

Fields have mean because of the program that reads them; columns have a
domain, a value and constraints in the schema -- totally separate from
any program that uses them -- which give them meaning.

It does not matter if you use a Standard data type; you are still not
programming with relational data model. Think in terms of predicates,
sets and declarations, not flags, sequences and procedures.|||--CELKO-- wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

No, I didn't know that SQL has no boolean data type, and that BIT

is proprietary, so thanks for that information. You can pretend it is
an integer type if you prefer. Again, do not read anything into the
table and field [sic] names .. <<
>
O)kay. You have SERIOUS conceptual problems with SQL and RDBMS.

Yeah well thanks for the opinion and all, but with the helpful efforts
of a number of posters to this group, I've understood and solved the
problem now and everything is just lovely. I'm happy, my customer is
happy, and my customer's customer is happy. Flowers bloom, birds sing,
and I've moved on to other things.

The last time I did any serious database development work was as a
young contractor, way back in the days of DBaseII before all this SQL
malarkey existed. In those days we had tables that consisted of records
made up of one or more fields. Rows and columns where for spreadsheets.
I'm sorry if that terminology annoys you but old habits die hard, and
it least it gives you a reason to try to act all superior, eh?
--
SlowerThanYou|||--CELKO-- wrote:

Quote:

Originally Posted by

You do not have a table at all; it is an attempt to mimic a deck of
punch cards. You confuse columns and fields, rows and records

This would be a lot more helpful if you'd explain the difference (or
rather, since the explanation is probably long-ish, include a URL
where the explanation can be found).

Quote:

Originally Posted by

do these transactions create a customer or a sale?

*looks down* Oh, you're alluding to SalesTransactions being a better
name than CustTransactions. But are you sure? Customers may engage
in sales, returns, credit memos and debit memos (the latter two are
used to adjust the customer's balance without inventory changing hands,
e.g. if they were over/undercharged for something). Of course, stuffing
multiple types of transactions into a single table without an explicit
TransactionType column is a separate error, but perhaps the table
definition was simplified by omitting columns not directly relevant to
the task at hand.

Quote:

Originally Posted by

Why is there DDL in narratives?

Why wouldn't there be? How many questions lacking DDL receive
the initial response "please post DDL to create your tables and
populate them with data illustrating the issue"?

Quote:

Originally Posted by

Quote:

Originally Posted by

Quote:

Originally Posted by

>>What I would like to do is, for all of the records [sic] in descending order of "AmountSpent" where "CustSelected = TRUE", set CustSelected to FALSE

such that the sum of all the AmountSpent records with CustSelected =
TRUE is no greater than a specified amount (say $50,000). <<

Quote:

Originally Posted by

You did not say what to do about ties; if I have five sales of
$50,000.00 which one would I mark?

This gap in the spec can be bridged by picking an arbitrary
rule (e.g. mark rows with lower TransactionKey first), on the
assumption that the questioner will be able to adjust that
part of the answer to fit whatever the actual rule is.

Quote:

Originally Posted by

give us a RELATIONAL spec and we can probably help you

This is a more general case of the above. "Your style is lousy,
so I'm going to point that out _and not answer your question_."|||--CELKO-- wrote:

Quote:

Originally Posted by

O)kay. You have SERIOUS conceptual problems with SQL and RDBMS. The
reason that SQL has no BOOLEAN data types is one of those "mathematical
foundations" things that has to do with NULLs, 3-valued logic and
logic.

This is not quite true. SQL in general has an optional BOOLEAN data
type; MSSQL in particular does not support the option.

http://troels.arvin.dk/db/rdbms/#data_types-boolean
Also, most of your message boils down to "you shouldn't store computed
data that can become outdated", but starting it out with the
above-quoted material gives the impression of "you shouldn't use
flags", which is untrue.

Quote:

Originally Posted by

Fields have mean because of the program that reads them; columns have
a domain, a value and constraints in the schema -- totally separate
from any program that uses them -- which give them meaning.

Aha, here's the answer to that "what's the difference between a field
and a column?" question that was raised earlier. No wonder I felt
confused - I'm familiar with program-independent constraints enforced
by the database, but did not strictly associate "column" with their
existence and "field" with their non-existence. (Spreadsheets, in
particular, play havoc with this.)

The intended difference between "row" and "record" is similarly
non-obvious to the lay reader, though I think I've read about it
before: namely, records have an inherent order, while rows have
no guaranteed order unless you specify one. (Spreadsheets play
havoc with this, too. So do certain indexes, especially clustering
indexes, which novices can easily mistake for an inherent order.)|||See if this is what you want:

CREATE TABLE SalesTransactions
(sales_nbr INTEGER NOT NULL PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES Customers (customer_id),
sales_amt DECIMAL(12,2) NOT NULL);

Create a VIEW or CTE with each customers sales ordered from high to
low. This is a greedy algorithm. The ROW_NUMBER() will randomly pick
an ordering in the event of ties.

Using that derived table, we can find the subset of purchase in each
customer that are at or below the threshold. amount, something like
this:

WITH (SELECT customer_id, sales_amt,
ROW_NUMBER()
OVER (PARTITION BY customer_id
ORDER BY sales_amt DESC)
FROM SalesTransactions AS S1)
AS SalesScores (customer_id, sales_amt, score)

SELECT S1.customer_id, S1.score
FROM SalesScores AS S1
WHERE @.threshold_amt <=
(SELECT SUM(S2.sales_amt)
FROM SalesScores AS S2
WHERE S1.customer_id = S2.customer_id
AND S1.score >= S2.score);

You can do this in one statement with the full OLAP features, which
would have a RANGE clause in the SUM() OVER() construct. SQL Server is
a bit behind.

But the important point is that you use virtual tables, rather than
mimicing a deck of punch cards. Think LOGICAL and not PHYSICAL! Think
sets, not sequences.

help with @@IDENTITY

hello,

I've got a problem reading the @.@.identity in vb.net
I tried it the way below and get the error: Public member 'EOF' on type 'Integer' not found.
(--> means with rsLastIdent)

comm_user = "SET NOCOUNT ON; INSERT INTO user (firstname, lastname, company, emailAddress) VALUES ...); SELECT @.@.IDENTITY AS Ident;"
comm = new SqlCommand(comm_user, dbConnection)
dbConnection.Open()
Try
 rsLastIdent = comm.ExecuteNonQuery()
 Catch ex As Exception
 Response.Write("Exception:")
 Response.Write(ex.ToString)
 End Try
if NOT rsLastIdent.EOF then
feed_userID = rsLastIdent.Fields.Item("Ident").Value
end if

The sql-statement is correct - I tried it on SQL Server and got the correct result, so something is wrong with my vb.net code...

Please can anybody help me and tell me how to declare my rsLastIdent or another way to code it in vb.net to get the @.@.identity?

Thanks a lot!

timWhat does ExecuteNonQuery return? It is not what you are trying to get at.

Try .ExecuteScalar() and cast the returned value to an int.

Dim rsLastIdent as integer
rsLastIdent=Integer.Parse(comm.executeScalar().ToString())

There are more elegant ways to do this, I expect.|||Douglas,

Thanks for your help!

executeScalar() was the function I needed! It works perfect now!

Regards,
Tim

Help w/ unique identity

Not sure if I have the right term in my Subject, but I have written an
application using VB6, which stores employee transactions in a SQL database.
I had originally designed the app for independent use in a standalone PC in
our remote offices but was then asked to expand its use to our corporate
office and import the transactions from all the locations into one database.
I have an Identity column in my table labeled "TransactionID" with the
Identity Seed and Indentity Increment set to the default of 1 and
automatically increments. The TransactionID number ensures that each
transaction is unique because other pieces of data collected in the table
could be the same such as StoreNumber, EmployeeNumber, etc.
All this is working fine, but now I want to import the transactions from the
remote location(s) into the SQL database at the corporate office. Now, there
is the very real possibility that a TransactionID from any of the remote
locations will match. Plus, when I import the records into the corporate
database, won't the master database want to reassign it a TransactionID as
well?
Since the TransactionID is unique at each store/office level, I was thinking
of formatting the TransactionID to something like "1234.01" where "1234" is
the next row in the table and "01" is the store number. This would be
separated by a decimal point. The store number would never exceed 2 digits
and the corporate office could be "00" or "99".
Is this possible or is there a better way to do this? I need to preserve the
TransactionID from the store/office because I use it to create an audit
trail if a transaction is ever modified.
Thanks,
BarryYou can add StoreID to the table and keep current Identity column as it (int
).
Perayu
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||I suggest you add another field in the table called store id
modify the unique constraint or the Pk to a composite (PK) or constraint
a composite pk is composed of at least two or more fields. In that way
you still preserve uniquesness of the data without violation atomicity of
the column
or better known as the domain integrity
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||> Since the TransactionID is unique at each store/office level, I was thinkingd">
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
I can not understand well because you are mixing names here.
My recommendation is to add a new column to the transaction table, to store
the [store] from where the transaccion was made. Do not use the formatting
approach (1234.01), then you will have to parse it into a select statement
and this will avoid sql server from considering it as a search argument.
Also, you can not create indexes based on a formula unless you create a
computed column and base the index on this column.
avoid this:
select c1,..., cn
from dbo.[transactions]
where right(transactionid, 2) = '01'
AMB
"BCS" wrote:

> Not sure if I have the right term in my Subject, but I have written an
> application using VB6, which stores employee transactions in a SQL databas
e.
> I had originally designed the app for independent use in a standalone PC i
n
> our remote offices but was then asked to expand its use to our corporate
> office and import the transactions from all the locations into one databas
e.
> I have an Identity column in my table labeled "TransactionID" with the
> Identity Seed and Indentity Increment set to the default of 1 and
> automatically increments. The TransactionID number ensures that each
> transaction is unique because other pieces of data collected in the table
> could be the same such as StoreNumber, EmployeeNumber, etc.
> All this is working fine, but now I want to import the transactions from t
he
> remote location(s) into the SQL database at the corporate office. Now, the
re
> is the very real possibility that a TransactionID from any of the remote
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
> Since the TransactionID is unique at each store/office level, I was thinki
ng
> of formatting the TransactionID to something like "1234.01" where "1234" i
s
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
> Is this possible or is there a better way to do this? I need to preserve t
he
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
> Thanks,
> Barry
>
>|||I already have a StoreNumber column, so I guess I just need to find some
info on constructing a Composite Constraint. Any good links?
Thanks,
Barry
<Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
news:EF3BF399-EDA4-4652-BC12-4321B73497D2@.microsoft.com...
> I suggest you add another field in the table called store id
> modify the unique constraint or the Pk to a composite (PK) or constraint
> a composite pk is composed of at least two or more fields. In that way
> you still preserve uniquesness of the data without violation atomicity of
> the column
> or better known as the domain integrity
>
> --
>
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "BCS" wrote:
>
database.
in
database.
table
the
there
as
thinking
is
digits
the|||
> locations will match. Plus, when I import the records into the corporate
> database, won't the master database want to reassign it a TransactionID as
> well?
>
You didn't mention, but I'm assuming that records in this table are only
inserted at the store level and are consolidated at the corporate level for
accounting and reporting purposes. The corporate data model can have the
same basic column layout as the store level model, but do not use the
identity property for TransactionID, becuase there is no need for it and
would create problem when importing.

> Since the TransactionID is unique at each store/office level, I was
> thinking
> of formatting the TransactionID to something like "1234.01" where "1234"
> is
> the next row in the table and "01" is the store number. This would be
> separated by a decimal point. The store number would never exceed 2 digits
> and the corporate office could be "00" or "99".
>
Identity columns (in this case TransactionID) must be integers, and you
cannot alter their format.

> Is this possible or is there a better way to do this? I need to preserve
> the
> TransactionID from the store/office because I use it to create an audit
> trail if a transaction is ever modified.
>
The combination of TransactionID and StoreID would make each transaction
unique at the corporate level, and this would be a good candidate for the
primary key at both the store and corporate level. When your primary key is
an identoty key, you should attempt to identify a natural key (ex: StoreID,
RegisterID, TransactionDateTime) and place a unique constraint on it.

> Thanks,
> Barry
>|||He probably means that the primary key should be composed of more than 1
column. For example:
CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED
(
[StoreNumber],
[TransactionID]
)
"BCS" <bswedeen@.tayloroil.com> wrote in message
news:rT_Oe.36020$es2.553871@.twister.southeast.rr.com...
>I already have a StoreNumber column, so I guess I just need to find some
> info on constructing a Composite Constraint. Any good links?
> Thanks,
> Barry
> <Jose G. de Jesus Jr MCP>; "MCDBA" <Email me> wrote in message
> news:EF3BF399-EDA4-4652-BC12-4321B73497D2@.microsoft.com...
> database.
> in
> database.
> table
> the
> there
> as
> thinking
> is
> digits
> the
>