Id getting generated differently

Hi,

I created a PDA application with a database, which has a table with a uniqueidentifier field and primarykey.

While doing the bulk insert from dataset into sql mobile database, It is inserting the record but it is not inserting the id which was entered into the sql server 2005 database, instead the id by creating a new id and the code is as below.

conAdap = new SqlCeDataAdapter(strQuery, conSqlceConnection);

SqlCeCommandBuilder cmdBuilder = new SqlCeCommandBuilder(conAdap);

conAdap.Fill(dsData);

int r =conAdap.Update(dsData);

Please help me.

Thank you,

Prashant

Hi Prashant - I'm not sure I understand your issue. Could you explain in more detail and also give me somee information about the schema of the table itself? I'm interested in the list of columns, their types, PK, FKs, Indexes, and default values you have assigned on columns.

I'm also interested to know if the table is the product of an RDA pull with tracking on or merge replication article.

thanks,

Darren

ID for New record

Hi,
How can I insert a new record in sql table and retrun the ID of that reocrd using a stored procedure?
Thanks,From Books Online:

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.@.IDENTITY AS 'Identity'

In stored proc:

create procedure sp_Test

@.outputvalue int output
as

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @.outputvalue = @.@.IDENTITY|||Thanks DMWCincy.|||Watch out fot @.@.IDENTITY, better use SCOPE_IDENTITY() besuase @.@.IDENTITY will return the last increment for the table, not necessarily the one that was generated by you transaction.

Id (indentity) is increments on faults.

Hi,

When i eg. manually ad entries to a table and, cancels the insert Ms SQL
increment the counter on the ID anyway. Is there a way to avoid this
behavior?

Regards
AndersFlare (dct_flare@.hotmail.com) writes:
> When i eg. manually ad entries to a table and, cancels the insert Ms SQL
> increment the counter on the ID anyway. Is there a way to avoid this
> behavior?

Yes, don't use the IDENTITY property, but roll your own. IDENTITY works
that way by design. By grabbing one number which never has to be
rolled back, insertions into tables with IDENTITY columns can scale
better.

One way to get a key on your on is:

BEGIN TRANSACTION

SELECT @.id = coalesce(MAX(id), 0) + 1 FROM tbl (UPDLOCK)

INSERT tbl (id, col1, col2, ...)
VALUES (@.id, @.par1, @.par2, ...)

COMMIT TRANSACTION

The UPDLOCK is required to avoid that two processes grab the
same id.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

IAM error?

I have gotten the following error a few times on just one database that I
have:
Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of the
range of this database.
This error can only be fixed by running dbcc checkdb allow_data_loss. Can
anyone tell me what may be causing this error and what I can change or avoid
so it stops happening?
TIA
jjYou have corruption in the database. Unless you have backups from before
you got the corruption you are probably going to have to loose some data by
running CHECKDB as mentioned. The first choice is always try to restore
from known good backups though.
--
Andrew J. Kelly SQL MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:u%2311wOGwFHA.2808@.TK2MSFTNGP10.phx.gbl...
>I have gotten the following error a few times on just one database that I
>have:
> Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of the
> range of this database.
> This error can only be fixed by running dbcc checkdb allow_data_loss. Can
> anyone tell me what may be causing this error and what I can change or
> avoid so it stops happening?
> TIA
> jj
>|||Actually I fixed the errors with the aforementioned script but since I only
have these errors on this one db every few months I'm wondering what's going
on.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23nGwJ$HwFHA.3556@.TK2MSFTNGP12.phx.gbl...
> You have corruption in the database. Unless you have backups from before
> you got the corruption you are probably going to have to loose some data
> by running CHECKDB as mentioned. The first choice is always try to
> restore from known good backups though.
> --
> Andrew J. Kelly SQL MVP
>
> "jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
> news:u%2311wOGwFHA.2808@.TK2MSFTNGP10.phx.gbl...
>>I have gotten the following error a few times on just one database that I
>>have:
>> Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of
>> the range of this database.
>> This error can only be fixed by running dbcc checkdb allow_data_loss. Can
>> anyone tell me what may be causing this error and what I can change or
>> avoid so it stops happening?
>> TIA
>> jj
>|||Sounds like you have hardware problems. That is the most common cause of
corruption these days. Fixing the problem with DBCC CHECKDB is not the best
solution. You loose data each time you fix it that way. Do you know what
data you lost? Your database is no longer solid with regards to data
integrity but you haven't a clue as to where. Not a good situation to be
in.
--
Andrew J. Kelly SQL MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:OLEZlDIwFHA.664@.tk2msftngp13.phx.gbl...
> Actually I fixed the errors with the aforementioned script but since I
> only have these errors on this one db every few months I'm wondering
> what's going on.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23nGwJ$HwFHA.3556@.TK2MSFTNGP12.phx.gbl...
>> You have corruption in the database. Unless you have backups from before
>> you got the corruption you are probably going to have to loose some data
>> by running CHECKDB as mentioned. The first choice is always try to
>> restore from known good backups though.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
>> news:u%2311wOGwFHA.2808@.TK2MSFTNGP10.phx.gbl...
>>I have gotten the following error a few times on just one database that I
>>have:
>> Table error: IAM page (1:511426) (object ID 2, index ID 255) is out of
>> the range of this database.
>> This error can only be fixed by running dbcc checkdb allow_data_loss.
>> Can anyone tell me what may be causing this error and what I can change
>> or avoid so it stops happening?
>> TIA
>> jj
>>
>

I4 to I8 in Aggregation

I'm using an Aggregation task to summarize an input file by item and week before inserting it into a SQL table.

Two of the fields I'm summing, because their totals per record can occasionally exceed 32k, are defined as int (I4) instead of smallint (I2). However, the summarized total never exceeds the value an int can hold.

I ran into a problem on the insert, however, with SSIS telling me it couldn't insert an I8 value into an I4 table field. I discovered the metadata for the summed totals had automatically been set to bigint (I8), and the mapping was failing.

I didn't see a way to change that metadata within the Aggregation task itself, so I added a Data Conversion task to convert the totals to four-byte signed integers and enable the mapping. Was that the proper workaround?

Based on your problrem description, it is the workaround that I would have used, for what that's worth.

I/O tracking for a Sql Table?

I'm looking for a way to monitor I/O(select, delete, & updates) to a particular SQL Table for a period of time. Any suggestions?SQL Profiler (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_mon_perf_86ib.asp)?

-PatP|||SQL Profiler would be usefull to you !|||Thanks for tip. I'm new to SQL Server 2000, but learning fast! Thanks againsql

I/O error

Hello,
I am getting following while creating a non-clustered
index on a table having 20 million records. Can anyone
suggest a solution for this...
Server: Msg 823, Level 24, State 11, Line 1
I/O error 33(error not found) detected during write at
offset 0x000002c8450000 in
file 'F:\SQL2K_Data\PROD_Data.MDF'.
Connection Broken
Regards,
ManojManoj,
This error came from the Operating system and not SQL Server. It
indicates a problem with your underlying hardware, most probably your
disk subsystem. Can you reproduce it on another server?
Run sqlhdtst available from http://support.microsoft.com/?id=178444 to
reproduce the error on your hardware.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Manoj Raheja wrote:
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>|||Manoj
It seems to be serious problem.
I suggest to restore database from the last BACKUP or if you don't have then
from query Analyser, master database, execute
dbcc checkdb(<databas_name> )
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:1bfe301c451ed$eaba6680$a101280a@.phx
.gbl...
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>

I/O error

Hello,
I am getting following while creating a non-clustered
index on a table having 20 million records. Can anyone
suggest a solution for this...
Server: Msg 823, Level 24, State 11, Line 1
I/O error 33(error not found) detected during write at
offset 0x000002c8450000 in
file 'F:\SQL2K_Data\PROD_Data.MDF'.
Connection Broken
Regards,
Manoj
Manoj,
This error came from the Operating system and not SQL Server. It
indicates a problem with your underlying hardware, most probably your
disk subsystem. Can you reproduce it on another server?
Run sqlhdtst available from http://support.microsoft.com/?id=178444 to
reproduce the error on your hardware.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Manoj Raheja wrote:
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>
|||Manoj
It seems to be serious problem.
I suggest to restore database from the last BACKUP or if you don't have then
from query Analyser, master database, execute
dbcc checkdb(<databas_name>)
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:1bfe301c451ed$eaba6680$a101280a@.phx.gbl...
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>
sql

I/O error

Hello,
I am getting following while creating a non-clustered
index on a table having 20 million records. Can anyone
suggest a solution for this...
Server: Msg 823, Level 24, State 11, Line 1
I/O error 33(error not found) detected during write at
offset 0x000002c8450000 in
file 'F:\SQL2K_Data\PROD_Data.MDF'.
Connection Broken
Regards,
ManojManoj,
This error came from the Operating system and not SQL Server. It
indicates a problem with your underlying hardware, most probably your
disk subsystem. Can you reproduce it on another server?
Run sqlhdtst available from http://support.microsoft.com/?id=178444 to
reproduce the error on your hardware.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Manoj Raheja wrote:
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>|||Manoj
It seems to be serious problem.
I suggest to restore database from the last BACKUP or if you don't have then
from query Analyser, master database, execute
dbcc checkdb(<databas_name>)
"Manoj Raheja" <manoj_raheja@.hotmail.com> wrote in message
news:1bfe301c451ed$eaba6680$a101280a@.phx.gbl...
> Hello,
> I am getting following while creating a non-clustered
> index on a table having 20 million records. Can anyone
> suggest a solution for this...
> Server: Msg 823, Level 24, State 11, Line 1
> I/O error 33(error not found) detected during write at
> offset 0x000002c8450000 in
> file 'F:\SQL2K_Data\PROD_Data.MDF'.
> Connection Broken
> Regards,
> Manoj
>

I'm Stumped (Complex Joins With and Updates)

My head is spinning trying to figure this one out. Ok. Here is the schema...

--

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [snmpPoller_HostConfig](

[id] [int] IDENTITY(1,1) NOT NULL,

[friendlyName] [nvarchar](50) NOT NULL,

[hostname] [nvarchar](255) NOT NULL,

[community] [nvarchar](255) NOT NULL,

[version] [int] NOT NULL,

[queryTimeout] [int] NOT NULL,

[isActive] [bit] NOT NULL,

[passedSanityCheck] [bit] NOT NULL CONSTRAINT [DF_snmpPoller_HostConfig_passedSanityCheck] DEFAULT ((0)),

CONSTRAINT [PK_snmpPoller_HostConfig] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [snmpPoller_TargetConfig](

[id] [int] IDENTITY(1,1) NOT NULL,

[hostId] [int] NOT NULL,

[description] [nvarchar](50) NOT NULL,

[oid] [nvarchar](255) NOT NULL,

[interval] [int] NOT NULL,

[lastPollTimestamp] [datetime] NOT NULL,

[nextPollTime] AS (dateadd(second,[interval],[lastPollTimestamp])),

[isActive] [bit] NOT NULL,

[isPolling] [bit] NOT NULL,

CONSTRAINT [PK_snmpPoller_TargetConfig] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[snmpPoller_TargetConfig] WITH CHECK ADD CONSTRAINT [FK_snmpPoller_TargetConfig_snmpPoller_HostConfig] FOREIGN KEY([hostId])

REFERENCES [snmpPoller_HostConfig] ([id])

Ok. So I have this query

WITH WorkTable ( Tag, Parent, [Host!1!hostId],

[Target!2!targetId], [Host!1!hostname], [Host!1!community],

[Host!1!version], [Host!1!timeout], [Target!2!oid], [Target!2!interval]

)

AS

(

SELECT DISTINCT

1 AS Tag,

NULL AS Parent,

H.id AS [Host!1!hostId],

NULL AS [Target!2!targetId],

hostname AS [Host!1!hostname],

community AS [Host!1!community],

version AS [Host!1!version],

queryTimeout AS [Host!1!timeout],

NULL AS [Target!2!oid],

NULL AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

UNION ALL

SELECT DISTINCT

2 AS Tag,

1 AS Parent,

hostId AS [Host!1!hostId],

T.id AS [Target!2!targetId],

NULL AS [Host!1!hostname],

NULL AS [Host!1!community],

NULL AS [Host!1!version],

NULL AS [Host!1!timeout],

oid AS [Target!2!oid],

interval AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

)

SELECT * FROM WorkTable

ORDER BY [Host!1!hostId], [Target!2!targetId]

FOR XML EXPLICIT, ROOT ('Hosts')

That generates this xml

<Hosts>

<Host hostId="6" hostname="XXX.XXX.XXX.XXX" community="XXXXX" version="2" timeout="30">

<Target targetId="1757" oid=".1.3.6.1.2.1.31.1.1.1.6." interval="30" />

<Target targetId="1758" oid=".1.3.6.1.2.1.31.1.1.1.10." interval="30" />

<Target targetId="1759" oid=".1.3.6.1.2.1.31.1.1.1.7." interval="30" />

<Target targetId="1760" oid=".1.3.6.1.2.1.31.1.1.1.11." interval="30" />

</Host>

<Host hostId="8" hostname=" XXX.XXX.XXX.XXX " community="XXXXXX" version="2" timeout="30">

<Target targetId="2037" oid=".1.3.6.1.2.1.31.1.1.1.6." interval="30" />

<Target targetId="2038" oid=".1.3.6.1.2.1.31.1.1.1.10." interval="30" />

<Target targetId="2039" oid=".1.3.6.1.2.1.31.1.1.1.7." interval="30" />

<Target targetId="2040" oid=".1.3.6.1.2.1.31.1.1.1.11." interval="30" />

</Host>

</Hosts>

Now the problem is that the xml that I generate is to ‘big’. When I submit a get request to the host, I can only fit 128 OIDs into the request PDU. My query, as it is, returns many hosts with upwards with 1000 targets. So question number 1 is how do return the top 128 targets for each host?

Now here is the real problem. I am replacing a working design in an effort to pick up a scale point. The current design has a dispatcher that will hand off the hostId to a work queue where it is picked up by a ‘thread’. This thread will then execute this sql.

SqlCommand^ command = connection->CreateCommand();

command->Parameters->AddWithValue("@.hostId", hostId);
command->CommandText =

"DECLARE @.resultTable TABLE("+

" targetId int NOT NULL,"+

" hostId int NOT NULL,"+

" oid nvarchar(255) NOT NULL"+

")"+

""+

"UPDATE TOP(128) snmpPoller_TargetConfig"+

" SET isPolling = 1 "+

" OUTPUT INSERTED.id, INSERTED.hostId, INSERTED.oid INTO @.resultTable"+

"WHERE (isPolling = 0) AND (isActive = 1) AND (id = @.hostId) AND (lastPollTimestamp < { fn NOW() })"+

""+

""+

"SELECT * FROM @.resultTable";

SqlDataReader^ reader = command->ExecuteReader();

So rather than make (host * (number of targets / 128)) calls per dispatch cycle, I need to make it all in one call.

So SQL gurus…. What’s the kung-fu? How do I get all my hosts with the top 128 targets AND set a flag on all targets in one atomic shot (I believe this ‘one-shot’ statement can be built off the with(), but I’m nothing but confused at this point)

Paul

Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com

is it possible to use ROW_NUM() ranking function within your query and then get the outputs based on row num. Also if you paste your sample data it shall aid the understanding|||

I actually just came up with this. It works just fine, but I'd like to consolidate it down to one statement. Any thoughts?

--

BEGIN TRANSACTION

DECLARE @.buffer TABLE

(

Tag int,

Parent int,

[Host!1!hostId] int,

[Target!2!targetId] int,

[Host!1!hostname] nvarchar(255),

[Host!1!community] nvarchar(255),

[Host!1!version] int,

[Host!1!timeout] int,

[Target!2!oid] nvarchar(255),

[Target!2!interval] int

);

WITH WorkTable ( Tag, Parent, [Host!1!hostId],

[Target!2!targetId], [Host!1!hostname], [Host!1!community],

[Host!1!version], [Host!1!timeout], [Target!2!oid], [Target!2!interval]

)

AS

(

SELECT DISTINCT

1 AS Tag,

NULL AS Parent,

H.id AS [Host!1!hostId],

NULL AS [Target!2!targetId],

hostname AS [Host!1!hostname],

community AS [Host!1!community],

version AS [Host!1!version],

queryTimeout AS [Host!1!timeout],

NULL AS [Target!2!oid],

NULL AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

UNION ALL

SELECT DISTINCT

2 AS Tag,

1 AS Parent,

hostId AS [Host!1!hostId],

T.id AS [Target!2!targetId],

NULL AS [Host!1!hostname],

NULL AS [Host!1!community],

NULL AS [Host!1!version],

NULL AS [Host!1!timeout],

oid AS [Target!2!oid],

interval AS [Target!2!interval]

FROM snmpPoller_HostConfig H, snmpPoller_TargetConfig T

WHERE H.id = T.hostId

AND t.isPolling = 0

AND t.isActive = 1

AND h.isActive = 1

AND t.lastPollTimestamp < { fn NOW() }

AND h.passedSanityCheck = 1

AND t.id IN(

SELECT TOP(128) id FROM snmpPoller_TargetConfig

WHERE isPolling = 0 AND isActive = 1 AND lastPollTimestamp < { fn NOW() }

AND hostId = h.id

)

)

INSERT INTO @.buffer

SELECT * FROM WorkTable

UPDATE t SET isPolling = 1

FROM snmpPoller_TargetConfig t,

@.buffer b

WHERE

t.id = b.[Target!2!targetId]

SELECT * FROM @.buffer

ORDER BY [Host!1!hostId], [Target!2!targetId]

FOR XML EXPLICIT, ROOT ('Hosts')

COMMIT TRANSACTION

Sorry, I can't post the data, the schema should be suffecient. Just say there for 100 hosts records and 750 target records per host.

|||

Please take a look at the OUTPUT clause. You can just perform the UPDATE statement and get the relevant columns (even expressions) from the inserted/deleted tables into a table variable for example. You can then do the SELECT against the table variable. Please take a look at my blog post below for one such example:

http://blogs.msdn.com/sqltips/archive/2005/06/13/OUTPUT_clause.aspx

I'm having issues with nested table...

Hey gang,

I'm having some issues with nested table. This is my setup. [ProductTable] is the case table, and [CustomersTable] is a nested table. I'm trying to organize my algorithms around products.

[ProductTable]<[CustomersTable]

[ProductTable] table only has product ID, and it is key.

[CustomersTable] table has variety of customer attributes (productID, customerID, location, demographics...) and CustomerRevenue is predict_only. ProductName is the key for the nested table.

I keep getting this error when I'm processing the mining models (Logical Regression and Neural Net).

Error (Data mining): In mining model, Estimate Neural Net, the algorithm does not allow table column as predictable.

Error (Data mining): Error validating attribute for the 'Estimate Neural Net' mining model.

When using Decision Tree, it processes OK, but the result is totally wrong. The model is empty.

Any ideas?

-Young K.

P.S. I'm trying to great a single model for multiple products. This is a label saving device that I'm trying. If this doens't work, I'll have to create a model for each product.

Just an idea... it might help to get all the columns needed in a single view in the database insted of using nested tables.

It's not the actual solution to your problem but it might be a workaround
|||

I thought of that, and that lead me to my original question... How good are the estimation (or regression) type analysis if I build a single model for multiple products? For example, if I have data for customers buying cars, motorcycles and boats, should I put them all in one model ? Or should I build 3 different models for 3 seperate products?

Can I build a single model to predict who will buy a car and/or boats and/or motorcycle and/or boat? Should I build a seperate model for car, motorcycle and boat? Is there a difference in accuracy?

I assumed that I needed to build 3 models for 3 products. And I used nested table to create a single model for multiple products. With nested tables, I can clearly seperate data between different products.

Any thoughts?

-Young K.

|||

You can solve the problem you want to, but you are misusing nested tables.

The data you are analyzing is defined by your case table. The nested table simply describes attributes of your case. In your case you have "Products" as the case and "Customers" as an attribute of "Product". The key of the nested table indicates in individual attribute or set of attributes. For example, you will have an attribute "Customer 3's Gender". Of course, customer 3's gender is unlikely to change for each product in their basket.

Another way of thinking about the problem is that your case identifier indicates what is anonymous or "unimportant" about your model. You are trying to spot trends in product purchasing behavior across customers. An individual customer is anonymous or "unimportant", the information about the customer and the products they buy are important.

You could have customers as the case table and have a nested table of products that contains the product name and product revenue from that customer. You would need to make both the table and the product revenue "Predict Only" to ensure that the predictions are not influenced by other product revenues or the existence of other products. If you use Decision Trees in this case, you will get a tree for each product revenue based on customer demographics.

To get a prediction of a product revenure given customer info you would use a query something like this:

SELECT (SELECT Predict(Revenue) FROM Products WHERE [Product Name]='Car') as t FROM ...

|||

Thank you, Jamie. That helped a lot.

-Young K

sql

I wrote my own configuration tool to maintain the connection strings in a SQL table

I wrote my own VB app to maintain all of my connection strings and link them to packages. I then grab them at run time and set them as variables in memory.Sorry this was suppose to be a reply not a new post.

I was asked for this in interview...?? how to solve this..

Hi groups, I got this question in interview...
I have 3 table,
(1)select * from Mark_Details
(2)select * from Mark
(3)select * from Student
(1)select * from Mark_Details
--
StudId Markid Marks
--
A1 1 75
A1 2 70
A1 3 80
A1 4 85
A1 5 90
A2 1 70
A2 2 75
A2 3 80
A2 4 90
A2 5 80
(2)select * from Mark
--
Markid markname
--
1 Maths
2 Science
3 Social
4 English
5 Tamil
(3)select * from Student
--
StudId studname
--
A1 Selva
A2 Akbar
----
--
Is there any way to get the output given below...I tried subqueries but not
got it so for..is it possbile.?
----
--
Studid Studname Maths science Social English Tamil
----
--
A1 Selva 75 70 80 85 90
A2 Akbar 70 75 80 90 80
----
--
please suggest me...
MahesMahesh Kumar.R wrote:
> Hi groups, I got this question in interview...
> I have 3 table,
> (1)select * from Mark_Details
> (2)select * from Mark
> (3)select * from Student
> (1)select * from Mark_Details
> --
> StudId Markid Marks
> --
> A1 1 75
> A1 2 70
> A1 3 80
> A1 4 85
> A1 5 90
> A2 1 70
> A2 2 75
> A2 3 80
> A2 4 90
> A2 5 80
> (2)select * from Mark
> --
> Markid markname
> --
> 1 Maths
> 2 Science
> 3 Social
> 4 English
> 5 Tamil
> (3)select * from Student
> --
> StudId studname
> --
> A1 Selva
> A2 Akbar
> ----
--
> Is there any way to get the output given below...I tried subqueries but n
ot got it so for..is it possbile.?
> ----
--
> Studid Studname Maths science Social English Tamil
> ----
--
> A1 Selva 75 70 80 85 90
> A2 Akbar 70 75 80 90 80
> ----
--
> please suggest me...
> Mahes
I hope they gave you a better spec than you have here - like DDL
including primary and foreign keys and constraints. Here are two
solutions, obviously with some assumptions about the keys in your
example tables.
In SQL Server 2000:
SELECT S.studid, S.studname,
SUM(CASE WHEN M.markname = 'maths' THEN marks END) AS maths,
SUM(CASE WHEN M.markname = 'science' THEN marks END) AS science,
SUM(CASE WHEN M.markname = 'social' THEN marks END) AS social,
SUM(CASE WHEN M.markname = 'english' THEN marks END) AS english,
SUM(CASE WHEN M.markname = 'tamil' THEN marks END) AS tamil
FROM mark_details AS D
JOIN stud AS S
ON D.studid = S.studid
JOIN mark AS M
ON D.markid = M.markid
GROUP BY S.studid, S.studname ;
In SQL Server 2005:
WITH student_marks (studid, studname, markname, marks)
AS (
SELECT S.studid, S.studname, M.markname, D.marks
FROM mark_details AS D
JOIN stud AS S
ON D.studid = S.studid
JOIN mark AS M
ON D.markid = M.markid
)
SELECT
D.studid,
D.studname,
P.maths,
P.science,
P.social,
P.english,
P.tamil
FROM
student_marks AS D
PIVOT (
SUM(marks)
FOR markname IN ([maths],[science],[social],[english],[t
amil])
) AS P
Both of these are untested.
David Portas
SQL Server MVP
--|||Like David mentioned, these specs are incomplete. I'd like to add that even
with DDL and constraint info, there are a number of techniques that will
work with for the test data provided yet won't produce the desired results
when other complexities are added. For example, should students that take
only some or none of the courses be included?
Asking the right questions can do more to demonstrate your knowledge than
providing a correct answer. At a minimum, it's a good idea to list any
assumptions made and/or provide alternative solutions when the provided
information is incomplete.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mahesh Kumar.R" <maheshkumar@.sninform.com> wrote in message
news:uzZm0MZ9FHA.2640@.tk2msftngp13.phx.gbl...
Hi groups, I got this question in interview...
I have 3 table,
(1)select * from Mark_Details
(2)select * from Mark
(3)select * from Student
(1)select * from Mark_Details
--
StudId Markid Marks
--
A1 1 75
A1 2 70
A1 3 80
A1 4 85
A1 5 90
A2 1 70
A2 2 75
A2 3 80
A2 4 90
A2 5 80
(2)select * from Mark
--
Markid markname
--
1 Maths
2 Science
3 Social
4 English
5 Tamil
(3)select * from Student
--
StudId studname
--
A1 Selva
A2 Akbar
----
--
Is there any way to get the output given below...I tried subqueries but not
got it so for..is it possbile.?
----
--
Studid Studname Maths science Social English Tamil
----
--
A1 Selva 75 70 80 85 90
A2 Akbar 70 75 80 90 80
----
--
please suggest me...
Mahes|||I think a dynamic query can work this things out
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Mahesh Kumar.R" wrote:

> Hi groups, I got this question in interview...
> I have 3 table,
> (1)select * from Mark_Details
> (2)select * from Mark
> (3)select * from Student
> (1)select * from Mark_Details
> --
> StudId Markid Marks
> --
> A1 1 75
> A1 2 70
> A1 3 80
> A1 4 85
> A1 5 90
> A2 1 70
> A2 2 75
> A2 3 80
> A2 4 90
> A2 5 80
> (2)select * from Mark
> --
> Markid markname
> --
> 1 Maths
> 2 Science
> 3 Social
> 4 English
> 5 Tamil
> (3)select * from Student
> --
> StudId studname
> --
> A1 Selva
> A2 Akbar
> ----
--
> Is there any way to get the output given below...I tried subqueries but n
ot got it so for..is it possbile.?
> ----
--
> Studid Studname Maths science Social English Tamil
> ----
--
> A1 Selva 75 70 80 85 90
> A2 Akbar 70 75 80 90 80
> ----
--
> please suggest me...
> Mahes
>|||For SQL Server 2005, this should work. No subqueries or dynamic SQL needed.
:)
-- Prep tables
create table dbo.marks
(
studentID char(2),
subjectID tinyint,
score tinyint
)
go
create table dbo.subjects
(
subjectID tinyint,
subjectName varchar(15)
)
go
create table dbo.students
(
studentID char(2),
studentName varchar(15)
)
go
-- Spin up data
insert into dbo.marks values('A1','1','75')
insert into dbo.marks values('A1','2','70')
insert into dbo.marks values('A1','3','80')
insert into dbo.marks values('A1','4','85')
insert into dbo.marks values('A1','5','90')
insert into dbo.marks values('A2','1','70')
insert into dbo.marks values('A2','2','75')
insert into dbo.marks values('A2','3','80')
insert into dbo.marks values('A2','4','90')
insert into dbo.marks values('A2','5','80')
insert into dbo.subjects values(1,'Maths')
insert into dbo.subjects values(2,'Science')
insert into dbo.subjects values(3,'Social')
insert into dbo.subjects values(4,'English')
insert into dbo.subjects values(5,'Tamil')
insert into dbo.students values('A1','Selva')
insert into dbo.students values('A2','Akbar')
go
-- solution
with m(studID,studName,subject,score) as
(
select s.studentID,s.StudentName,b.subjectName,m.score
from dbo.students s
join dbo.marks m on s.studentID = m.studentID
join dbo.subjects b on m.subjectID = b.subjectID
)
select StudID,StudName,Maths,Science,Social,Eng
lish,Tamil
from m
pivot
(
max(score)
for subject in ([Maths],[Science],[Social],[English],[T
amil])
) p
Thanks!
Kent|||Mahesh Kumar.R wrote:
> Hi groups, I got this question in interview...
> I have 3 table,
> (1)select * from Mark_Details
> (2)select * from Mark
> (3)select * from Student
> (1)select * from Mark_Details
> --
> StudId Markid Marks
> --
> A1 1 75
> A1 2 70
> A1 3 80
> A1 4 85
> A1 5 90
> A2 1 70
> A2 2 75
> A2 3 80
> A2 4 90
> A2 5 80
> (2)select * from Mark
> --
> Markid markname
> --
> 1 Maths
> 2 Science
> 3 Social
> 4 English
> 5 Tamil
> (3)select * from Student
> --
> StudId studname
> --
> A1 Selva
> A2 Akbar
> ----
--
> Is there any way to get the output given below...I tried subqueries but n
ot got it so for..is it possbile.?
> ----
--
> Studid Studname Maths science Social English Tamil
> ----
--
> A1 Selva 75 70 80 85 90
> A2 Akbar 70 75 80 90 80
> ----
--
> please suggest me...
Sheesh! Why don't they just ask "Do you know what pivot query is"?
Regarding vocabulary, it looks like SQL area seriously lacks one. In
the other thread I see people pointlessy competing piling up
subqueries, istead of just saying "Look, that is just interval coalesce
problem. Look it up in the book ..."
Admittedly there is no such a book yet. I'm writing the one!|||Well Thanks for all your inputs :)..I feel complete now for asking MORE
......
In simple, How to convert ('N' rows ) into ('N' Columns )...I mean 'n' is
dynamic.so i'm not going to give this time mark=Maths..etc..
Mahes.~
"Mikito Harakiri" <mikharakiri_nospaum@.yahoo.com> wrote in message
news:1133408783.342538.244800@.g43g2000cwa.googlegroups.com...
> Mahesh Kumar.R wrote:
> ----
--
not got it so for..is it possbile.?
> ----
--
> ----
--
> ----
--
> Sheesh! Why don't they just ask "Do you know what pivot query is"?
> Regarding vocabulary, it looks like SQL area seriously lacks one. In
> the other thread I see people pointlessy competing piling up
> subqueries, istead of just saying "Look, that is just interval coalesce
> problem. Look it up in the book ..."
> Admittedly there is no such a book yet. I'm writing the one!
>|||Mahesh Kumar.R wrote:
> Well Thanks for all your inputs :)..I feel complete now for asking MORE
> ......
> In simple, How to convert ('N' rows ) into ('N' Columns )...I mean 'n' is
> dynamic.so i'm not going to give this time mark=Maths..etc..
> Mahes.~
>
http://www.aspfaq.com/show.asp?id=2462
David Portas
SQL Server MVP
--|||finally I learned a concept called " CROSS TAB REPORTS in SQL "..thanks for
all..
Mahes
http://spaces.msn.com/members/cyberiafreak
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1133442541.843844.176260@.g47g2000cwa.googlegroups.com...
> Mahesh Kumar.R wrote:
is
> http://www.aspfaq.com/show.asp?id=2462
> --
> David Portas
> SQL Server MVP
> --
>sql

I want write a Trigger that make a 3nd table that contain all the record

Hi
I want to write Trigger tha do this:
I have 2 table

main table & sub main table that have data like this
for example I have Bank (code 001) in main table and visa(code 0001) & mastercard(code 0002) in submain table.

or I have BMW(code 101) in main table and X5(code 0001) & X3(code 0001) in sub main table.

I want write a Trigger that make a 3nd table that contain all the record of that two table like this:

NAME CODE
Bank 001
visa card 0010001
master card 0010002

BMW 101
X5 1010001
X3 1010002
..........................................

Trigger MUST make code for any record ( main code * 1000 + submain code ) that is unic. and record name.

1) Set a foreign key in the "submain" table to reference the unique 3-digit codes in the main table.

2) Write an INSERT tigger on the "submain" table that inserts a JOIN of main+submain into the 3rd table.

Something like what you see below. You could also use a computed column as explained in the other post.

Thanks

set nocount on
go

use tempdb
go

create table main(
the_name char(6) not null primary key
, the_main_code char(3) not null constraint main_unique unique
)
go

insert into main values ('Banks', '001')
insert into main values ('Goods', '101')
go

create table submain(
the_name varchar(20) not null primary key
, the_submain_code char(4) not null
, the_main_code char(3) foreign key references main(the_main_code)
)
go

create table combined (
the_main_name char(6)
, the_submain_name varchar(20)
, the_combined_code char(7)
)
go

create trigger make_combined on submain
for insert
as
insert into combined
select main.the_name, inserted.the_name, inserted.the_main_code + inserted.the_submain_code
from main join inserted
on main.the_main_code = inserted.the_main_code
go

insert into submain (the_name, the_submain_code, the_main_code) values ('visa card', '0001', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('master card', '0002', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X5', '0001', '101')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X3', '0002', '101')
go

select * from combined order by the_combined_code
go

drop table submain
go
drop table main
go
drop table combined
go

i want to use substring function in DTS for transformation

hi all
i want to use substring function in DTS for transformation but its give me
run time error
my SQL statement for Source Table is
Select SUBSTRING(Field_name, 1, 2) from table_name
any help for how to use SUBSTRING in DTS
Message posted via http://www.sqlmonster.com
amit
start on www.sqldts.com
"amit mota via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:028636b3e291457ebd710ac58f1e25f0@.SQLMonster.c om...
> hi all
> i want to use substring function in DTS for transformation but its give me
> run time error
> my SQL statement for Source Table is
> Select SUBSTRING(Field_name, 1, 2) from table_name
> any help for how to use SUBSTRING in DTS
> --
> Message posted via http://www.sqlmonster.com
|||What type of server are you connecting to in the DTS package?
Message posted via http://www.sqlmonster.com

i want to use substring function in DTS for transformation

hi all
i want to use substring function in DTS for transformation but its give me
run time error
my SQL statement for Source Table is
Select SUBSTRING(Field_name, 1, 2) from table_name
any help for how to use SUBSTRING in DTS
--
Message posted via http://www.sqlmonster.comamit
start on www.sqldts.com
"amit mota via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:028636b3e291457ebd710ac58f1e25f0@.SQLMonster.com...
> hi all
> i want to use substring function in DTS for transformation but its give me
> run time error
> my SQL statement for Source Table is
> Select SUBSTRING(Field_name, 1, 2) from table_name
> any help for how to use SUBSTRING in DTS
> --
> Message posted via http://www.sqlmonster.com|||What type of server are you connecting to in the DTS package?
--
Message posted via http://www.sqlmonster.com

i want to use substring function in DTS for transformation

hi all
i want to use substring function in DTS for transformation but its give me
run time error
my SQL statement for Source Table is
Select SUBSTRING(Field_name, 1, 2) from table_name
any help for how to use SUBSTRING in DTS
Message posted via http://www.droptable.comamit
start on www.sqldts.com
"amit mota via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:028636b3e291457ebd710ac58f1e25f0@.SQ
droptable.com...
> hi all
> i want to use substring function in DTS for transformation but its give me
> run time error
> my SQL statement for Source Table is
> Select SUBSTRING(Field_name, 1, 2) from table_name
> any help for how to use SUBSTRING in DTS
> --
> Message posted via http://www.droptable.com|||What type of server are you connecting to in the DTS package?
Message posted via http://www.droptable.comsql

I want to select the SECOND newest record in a table,....is this possible?

Hi!
I want to do a query against a SQL DB and by sorting a datetime field, I want to get the second newest record in the table, not the newest.
Can I do that?
/Johan Ch

You could do it like this:

SELECT TOP 1
*
FROM
(SELECT TOP 2 * FROM myTable Order by myDateTime DESC) AS A
ORDER BY
myDateTime ASC

I want to see the actual SQL being passed - how?

I'm getting a "Input string was not in a correct format."when I'm running a insert statement against my SQL Server 2005 db table. This helps me zilch as I cant see the actual SQL statement to see which one wasnt right. Using a SQLDatasource and a Formview btw.

Datasource is called xSqlIB and formview is called fmvIB.

Any ideas?

synergy, have you tried running the profiler against your db and see what is trying to be executed vs the db? also, have you tried to step through to where the string is being created? if you have access to sql server mgt studio, just go to tools and select sql server profiler. then you can see the string that is getting executed...hope this helps -- jp|||

Synergyauto:

I'm getting a "Input string was not in a correct format."when I'm running a insert statement against my SQL Server 2005 db table. This helps me zilch as I cant see the actual SQL statement to see which one wasnt right. Using a SQLDatasource and a Formview btw.

Datasource is called xSqlIB and formview is called fmvIB.

Any ideas?

That error is not coming from SQL Server; it is coming from your .NET code. So it's not getting as far as the SQL Server and running Profiler will not be of much assistance to you.

|||nice call tmorton...|||

You can set up a function in the xSqlIB_Inserting function that will give you your insert statement, you can write a quick loop to write out all of your varialbes to trace and see what they are. Set tracing to true on your page, and all the information you want will show up at the bottom in red. Good luck!

ProtectedSub sqlProjectData_Inserting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles sqlProjectData.InsertingTry

Trace.Warn(e.Command.CommandText)

Dim xAsInteger = 0DoUntil x = e.Command.Parameters.Count

Trace.Warn(e.Command.Parameters(x).ParameterName)

IfNot (e.Command.Parameters(x).ValueIsNothing)Then

Trace.Warn(e.Command.Parameters(x).Value.ToString)

EndIf

x += 1

LoopCatch exAs ExceptionDim oLoggerAsNew ErrorLogger.clsLogError(ex, Session, Request)

oLogger.LogError()

EndTryEndSub

|||

Unfortunately the page errors before xSqlIB_Inserting is ever called so that could isnt running (and I dont seem to have your ErrorLogger class). Trace function is nifty though and its cool to finally be able to see what the page sees so quickly. Here's a c/p of what the page shows in red. I dont see it showing me the actual db field that is freaking out on though:

Unhandled Execution Error
Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.Byte.Parse(String s, NumberStyles style, NumberFormatInfo info)
at System.String.System.IConvertible.ToByte(IFormatProvider provider)
at System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider)
at System.Web.UI.WebControls.Parameter.GetValue(Object value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges)
at System.Web.UI.WebControls.Parameter.get_ParameterValue()
at System.Web.UI.WebControls.ParameterCollection.GetValues(HttpContext context, Control control)
at System.Web.UI.WebControls.SqlDataSourceView.InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList)
at System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values)
at System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback)
at System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation)
at System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup)
at System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e)
at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
at System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e)
at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
at System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

|||Anyone home? hehe|||synergy, what is the number you are trying to convert. i see the parse int32 in the printout and what is the code trying to work with that number? - jp|||

jdingo:

synergy, what is the number you are trying to convert. i see the parse int32 in the printout and what is the code trying to work with that number? - jp

Here's the whole datasource from code, I'm not doing any code behind work on the datasource.

<asp:SqlDataSourceID="xSqlIB"runat="server"ConnectionString="<%$ ConnectionStrings:TestETSAppConnectionString %>"

DeleteCommand="DELETE FROM [xECSIBHeader] WHERE [IBHeaderKey] = @.IBHeaderKey"InsertCommand="INSERT INTO [xECSIBHeader] ([IBNumber], [IBTyp], [IBStatus], [CATCode], [Project], [SeverityCode], [CoverageFeeType], [DateClosed], [AdjusterID], [ClaimNumber], [PolicyNumber], [InsuredName], [Location01], [Location02], [City], [State], [ZipCode], [GrossLoss], [SalesTaxPercent], [SalesTaxAmount], [LossDate], [Supplement], [SupplementExplaination], [MultipleClaim], [ReBilled], [ReBilledIBSubmitted], [OriginalIBNumber], [OriginalIBFee], [RevenueAmtApplied], [ExpenseAmtApplied], [SalesTaxAmtApplied], [CommissionDocBal], [DocBal], [DocumentSelected], [CommissionStatus], [InvoiceStatus], [StatementID], [TaxGroup]) VALUES (@.IBNumber, @.IBTyp, @.IBStatus, @.CATCode, @.Project, @.SeverityCode, @.CoverageFeeType, @.DateClosed, @.AdjusterID, @.ClaimNumber, @.PolicyNumber, @.InsuredName, @.Location01, @.Location02, @.City, @.State, @.ZipCode, @.GrossLoss, @.SalesTaxPercent, @.SalesTaxAmount, @.LossDate, @.Supplement, @.SupplementExplaination, @.MultipleClaim, @.ReBilled, @.ReBilledIBSubmitted, @.OriginalIBNumber, @.OriginalIBFee, @.RevenueAmtApplied, @.ExpenseAmtApplied, @.SalesTaxAmtApplied, @.CommissionDocBal, @.DocBal, @.DocumentSelected, @.CommissionStatus, @.InvoiceStatus, @.StatementID, @.TaxGroup)"SelectCommand="xECS_sp_SelectIBDetails"SelectCommandType="StoredProcedure"UpdateCommand="UPDATE [xECSIBHeader] SET [IBNumber] = @.IBNumber, [IBTyp] = @.IBTyp, [IBStatus] = @.IBStatus, [CATCode] = @.CATCode, [Project] = @.Project, [SeverityCode] = @.SeverityCode, [CoverageFeeType] = @.CoverageFeeType, [DateClosed] = @.DateClosed, [AdjusterID] = @.AdjusterID, [ClaimNumber] = @.ClaimNumber, [PolicyNumber] = @.PolicyNumber, [InsuredName] = @.InsuredName, [Location01] = @.Location01, [Location02] = @.Location02, [City] = @.City, [State] = @.State, [ZipCode] = @.ZipCode, [GrossLoss] = @.GrossLoss, [SalesTaxPercent] = @.SalesTaxPercent, [SalesTaxAmount] = @.SalesTaxAmount, [LossDate] = @.LossDate, [Supplement] = @.Supplement, [SupplementExplaination] = @.SupplementExplaination, [MultipleClaim] = @.MultipleClaim, [ReBilled] = @.ReBilled, [ReBilledIBSubmitted] = @.ReBilledIBSubmitted, [OriginalIBNumber] = @.OriginalIBNumber, [OriginalIBFee] = @.OriginalIBFee, [RevenueAmtApplied] = @.RevenueAmtApplied, [ExpenseAmtApplied] = @.ExpenseAmtApplied, [SalesTaxAmtApplied] = @.SalesTaxAmtApplied, [CommissionDocBal] = @.CommissionDocBal, [DocBal] = @.DocBal, [DocumentSelected] = @.DocumentSelected, [CommissionStatus] = @.CommissionStatus, [InvoiceStatus] = @.InvoiceStatus, [StatementID] = @.StatementID, [TaxGroup] = @.TaxGroup WHERE [IBHeaderKey] = @.IBHeaderKey"><DeleteParameters><asp:ParameterName="IBHeaderKey"Type="Int64"/></DeleteParameters><UpdateParameters><asp:ParameterName="IBNumber"Type="String"/><asp:ParameterDefaultValue="R"Name="IBTyp"Type="Byte"/><asp:ParameterDefaultValue="1"Name="IBStatus"Type="Byte"/><asp:ParameterName="CATCode"Type="String"/><asp:ParameterDefaultValue="C002003"Name="Project"Type="String"/><asp:ParameterName="SeverityCode"Type="Int16"/><asp:ParameterDefaultValue="1"Name="CoverageFeeType"Type="Int16"/><asp:ParameterDefaultValue="1/1/1900"Name="DateClosed"Type="DateTime"/><asp:ParameterDefaultValue="271"Name="AdjusterID"Type="Int32"/><asp:ParameterName="ClaimNumber"Type="String"/><asp:ParameterName="PolicyNumber"Type="String"/><asp:ParameterName="InsuredName"Type="String"/><asp:ParameterName="Location01"Type="String"/><asp:ParameterName="Location02"Type="String"/><asp:ParameterName="City"Type="String"/><asp:ParameterName="State"Type="String"/><asp:ParameterName="ZipCode"Type="String"/><asp:ParameterDefaultValue="0.00"Name="GrossLoss"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxPercent"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxAmount"Type="Double"/><asp:ParameterDefaultValue="1/1/1900"Name="LossDate"Type="DateTime"/><asp:ParameterName="Supplement"Type="Boolean"/><asp:ParameterName="SupplementExplaination"Type="String"/><asp:ParameterName="MultipleClaim"Type="Boolean"/><asp:ParameterName="ReBilled"Type="Boolean"/><asp:ParameterName="ReBilledIBSubmitted"Type="Boolean"/><asp:ParameterName="OriginalIBNumber"Type="String"/><asp:ParameterName="OriginalIBFee"Type="Double"/><asp:ParameterName="RevenueAmtApplied"Type="Double"/><asp:ParameterName="ExpenseAmtApplied"Type="Double"/><asp:ParameterName="SalesTaxAmtApplied"Type="Double"/><asp:ParameterName="CommissionDocBal"Type="Double"/><asp:ParameterName="DocBal"Type="Double"/><asp:ParameterName="DocumentSelected"Type="Boolean"/><asp:ParameterName="CommissionStatus"Type="Int16"/><asp:ParameterName="InvoiceStatus"Type="Int16"/><asp:ParameterName="StatementID"Type="Int32"/><asp:ParameterName="TaxGroup"Type="String"/><asp:ParameterName="IBHeaderKey"Type="Int64"/></UpdateParameters><SelectParameters><asp:QueryStringParameterDefaultValue="18R315973"Name="ClaimNumber"QueryStringField="ClaimNumber"Type="String"/></SelectParameters><InsertParameters><asp:ParameterName="IBNumber"Type="String"/><asp:ParameterDefaultValue="R"Name="IBTyp"Type="Byte"/><asp:ParameterDefaultValue="3"Name="IBStatus"Type="Byte"/><asp:ParameterName="CATCode"Type="String"/><asp:ParameterDefaultValue="C002003"Name="Project"Type="String"/><asp:ParameterName="SeverityCode"Type="Int16"/><asp:ParameterDefaultValue="1"Name="CoverageFeeType"Type="Int16"/><asp:ParameterDefaultValue="1/1/1900"Name="DateClosed"Type="DateTime"/><asp:ParameterDefaultValue="271"Name="AdjusterID"Type="Int32"/><asp:ParameterName="ClaimNumber"Type="String"/><asp:ParameterName="PolicyNumber"Type="String"/><asp:ParameterName="InsuredName"Type="String"/><asp:ParameterName="Location01"Type="String"/><asp:ParameterName="Location02"Type="String"/><asp:ParameterName="City"Type="String"/><asp:ParameterName="State"Type="String"/><asp:ParameterName="ZipCode"Type="String"/><asp:ParameterDefaultValue="0.00"Name="GrossLoss"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxPercent"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxAmount"Type="Double"/><asp:ParameterDefaultValue="1/1/1900"Name="LossDate"Type="DateTime"/><asp:ParameterName="Supplement"Type="Boolean"/><asp:ParameterName="SupplementExplaination"Type="String"/><asp:ParameterName="MultipleClaim"Type="Boolean"/><asp:ParameterName="ReBilled"Type="Boolean"/><asp:ParameterName="ReBilledIBSubmitted"Type="Boolean"/><asp:ParameterName="OriginalIBNumber"Type="String"/><asp:ParameterName="OriginalIBFee"Type="Double"/><asp:ParameterName="RevenueAmtApplied"Type="Double"/><asp:ParameterName="ExpenseAmtApplied"Type="Double"/><asp:ParameterName="SalesTaxAmtApplied"Type="Double"/><asp:ParameterName="CommissionDocBal"Type="Double"/><asp:ParameterName="DocBal"Type="Double"/><asp:ParameterName="DocumentSelected"Type="Boolean"/><asp:ParameterName="CommissionStatus"Type="Int16"/><asp:ParameterName="InvoiceStatus"Type="Int16"/><asp:ParameterName="StatementID"Type="Int32"/><asp:ParameterName="TaxGroup"Type="String"/></InsertParameters></asp:SqlDataSource>|||

Dang, sorry, guess i hit "post" 3 times too many

________________________________________________________________

Note: duplicate posts were deleted by moderator tmorton

|||synergy, i dont have an exact answer from what i have seen, but if you are getting no where and i know you have lots of fields you are working with, but you could run your insert command trying each field at a time and see which one throws the error in case it is a simple matter some mismatch between and accepted value for a field in the DB and an actual value being passed in as a parameter. I know it is a more brute force approach but thats all i have at the moment. good luck -- jp|||Thanks for hanging with me jdingo, I noticed in the code above that 2 fields were listed as "byte" (dunno how it got that way) but they were really integer fields. So passing them "False" instead of an integer was causing the error. Thanks for the help!|||nice synergy, glad that you found it, many times i solve my own problems just by explaining them and going through them with others. have a good one-- jpsql

I want to give a user access to only one field in one table

I was trying to give permissions to a user for one field in one table only.
How can I do this?
It seems that I have to give permissions to the whole database.
I have SQL 2000 using Enterprise managerGRANT SELECT (<ColumnName> ) ON <TableName> TO <User>
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"AlanM" <nooneatall@.nowhere.com> wrote in message
news:ObRkcHf1EHA.1204@.TK2MSFTNGP10.phx.gbl...
>I was trying to give permissions to a user for one field in one table only.
> How can I do this?
> It seems that I have to give permissions to the whole database.
> I have SQL 2000 using Enterprise manager
>|||To add to Roji's response, you might also consider creating a view that
returns only the data the user should see and grant SELECT permissions on
only that view to the user/role. This allows vertical and horizontal
partitioning based on your requirements. See 'Using Views as Security
Mechanisms' <adminsql.chm::/ad_security_5whf.htm> in the Books Online for
more information.
CREATE VIEW MyView
AS
SELECT MyColumn
FROM MyTable
GO
GRANT SELECT ON MyView TO MyRole
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"AlanM" <nooneatall@.nowhere.com> wrote in message
news:ObRkcHf1EHA.1204@.TK2MSFTNGP10.phx.gbl...
>I was trying to give permissions to a user for one field in one table only.
> How can I do this?
> It seems that I have to give permissions to the whole database.
> I have SQL 2000 using Enterprise manager
>