Cannot perform needed expression

Is there a work around for trying this in my footer table field in my
SSRS 2005 report?
=SUM((Max(Fields!FeeGoal_AZ.Value) / (Fields!FeeSchedule.Value) * 100)
doesn't allow aggregate functions inside SUM...but I need to do this!Try creating a calculated field called MaxGoal using
=Max(Fields!FeeGoal_AZ.Value) as the expression, then doing
=SUM((Fields!MaxGoal.Value / Fields!FeeSchedule.Value) * 100)

Cannot insert the value NULL into column 'ID', table

I am new with SQL and have been using Access. Now with Access, when I
enter in a record, the ID field (which is my primary key) is
automatically incremented.
I am thinking from the error it is not doing that here, is there
something I am missing? (probably a lot)
ChuckCan you show us your INSERT statement? For a column with the IDENTITY attribute, you don't list the
column in the column name list, and you obviously don't specify a value for it either. Simplified
example:
CREATE TABLE t(c1 int IDENTITY, c2 char(5))
INSERT INTO t(c2)
VALUES('Hello')
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<cvillard@.gmail.com> wrote in message news:1124228310.779917.26320@.o13g2000cwo.googlegroups.com...
>I am new with SQL and have been using Access. Now with Access, when I
> enter in a record, the ID field (which is my primary key) is
> automatically incremented.
> I am thinking from the error it is not doing that here, is there
> something I am missing? (probably a lot)
> Chuck
>|||This is a sample of my code... am I doing something wierd? Looks
different.
if Request.Form("Action") = "add" then
if Request.Form("txtPassword") = Request.Form("txtConfirm") then
rs.open "SELECT * FROM Users", conn, 1, 3
rs.AddNew
rs("Username") = Request.Form("txtUsername")
rs("Password") = Request.Form("txtPassword")
rs("Email_Address") = Request.Form("txtEmailAddress")
if Request.Form("cbRights") = true then
rs("User_Rights") = "Adminstrator"
else
rs("User_Rights") = "User"
end if
rs("Name") = Request.Form("txtName")
rs("Create_Date") = Date()
rs.Update
Response.Write("<p align='center'><b>The User Profile for " &
rs("Name") & " has been added.</b></p>")
Response.Write("<p align='center'>The user will receive an email
with the account details shortly.</p>")
Response.Write("<p align='center'><input type='button' value='Close
Window' onClick='javascript:window.close()'></p>")|||Odd. When I went back in the server didnt have Identity setup for the
ID field. Now it seems to be working.

Cannot insert the value NULL into column 'ID', table

I am new with SQL and have been using Access. Now with Access, when I
enter in a record, the ID field (which is my primary key) is
automatically incremented.
I am thinking from the error it is not doing that here, is there
something I am missing? (probably a lot)
ChuckCan you show us your INSERT statement? For a column with the IDENTITY attrib
ute, you don't list the
column in the column name list, and you obviously don't specify a value for
it either. Simplified
example:
CREATE TABLE t(c1 int IDENTITY, c2 char(5))
INSERT INTO t(c2)
VALUES('Hello')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<cvillard@.gmail.com> wrote in message news:1124228310.779917.26320@.o13g2000cwo.googlegroups.
com...
>I am new with SQL and have been using Access. Now with Access, when I
> enter in a record, the ID field (which is my primary key) is
> automatically incremented.
> I am thinking from the error it is not doing that here, is there
> something I am missing? (probably a lot)
> Chuck
>|||This is a sample of my code... am I doing something wierd? Looks
different.
if Request.Form("Action") = "add" then
if Request.Form("txtPassword") = Request.Form("txtConfirm") then
rs.open "SELECT * FROM Users", conn, 1, 3
rs.AddNew
rs("Username") = Request.Form("txtUsername")
rs("Password") = Request.Form("txtPassword")
rs("Email_Address") = Request.Form("txtEmailAddress")
if Request.Form("cbRights") = true then
rs("User_Rights") = "Adminstrator"
else
rs("User_Rights") = "User"
end if
rs("Name") = Request.Form("txtName")
rs("Create_Date") = Date()
rs.Update
Response.Write("<p align='center'><b>The User Profile for " &
rs("Name") & " has been added.</b></p>")
Response.Write("<p align='center'>The user will receive an email
with the account details shortly.</p>")
Response.Write("<p align='center'><input type='button' value='Close
Window' onClick='javascript:window.close()'></p>")|||Odd. When I went back in the server didnt have Identity setup for the
ID field. Now it seems to be working.

Cannot insert the value NULL into column 'ID', table

I am new with SQL and have been using Access. Now with Access, when I
enter in a record, the ID field (which is my primary key) is
automatically incremented.
I am thinking from the error it is not doing that here, is there
something I am missing? (probably a lot)
Chuck
Can you show us your INSERT statement? For a column with the IDENTITY attribute, you don't list the
column in the column name list, and you obviously don't specify a value for it either. Simplified
example:
CREATE TABLE t(c1 int IDENTITY, c2 char(5))
INSERT INTO t(c2)
VALUES('Hello')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<cvillard@.gmail.com> wrote in message news:1124228310.779917.26320@.o13g2000cwo.googlegro ups.com...
>I am new with SQL and have been using Access. Now with Access, when I
> enter in a record, the ID field (which is my primary key) is
> automatically incremented.
> I am thinking from the error it is not doing that here, is there
> something I am missing? (probably a lot)
> Chuck
>
|||This is a sample of my code... am I doing something wierd? Looks
different.
if Request.Form("Action") = "add" then
if Request.Form("txtPassword") = Request.Form("txtConfirm") then
rs.open "SELECT * FROM Users", conn, 1, 3
rs.AddNew
rs("Username") = Request.Form("txtUsername")
rs("Password") = Request.Form("txtPassword")
rs("Email_Address") = Request.Form("txtEmailAddress")
if Request.Form("cbRights") = true then
rs("User_Rights") = "Adminstrator"
else
rs("User_Rights") = "User"
end if
rs("Name") = Request.Form("txtName")
rs("Create_Date") = Date()
rs.Update
Response.Write("<p align='center'><b>The User Profile for " &
rs("Name") & " has been added.</b></p>")
Response.Write("<p align='center'>The user will receive an email
with the account details shortly.</p>")
Response.Write("<p align='center'><input type='button' value='Close
Window' onClick='javascript:window.close()'></p>")
|||Odd. When I went back in the server didnt have Identity setup for the
ID field. Now it seems to be working.

cannot insert null

i am using asp2003 and sql server 2000. in my SQL Server i am generating a field city_id automatically by incrementing the value by 1 and it is the primary key. so when i insert any data using my asp.net application it works fine on my system but it gives me error when i upload it to the web server. the error is

Cannot insert the value NULL into column 'city_id', table 'nevadb.nevauser.city_master'; column does not allow nulls. INSERT fails. The statement has been terminated.

any help?

Are you sure that city_id is an auto incrementing field on the production server? Have you double-checked that?

|||

Try to set this option before you insert data to the table:

SET IDENTITY_INSERT OFF;

INSERT ....

|||yes. this is the problem... actually when i copy my database from my computer to web server using import/export utility of sql server 2000. then it do not copy all the checks (in my case identity). even import/export utility do have option to copy identity values but it is not working the way it should be. can you suggest any solution to me. some one told me that i have to write some kind of script and run it on the server. does anybody have any idea, what i shoul do to resolve this problem.|||well i found the solution... i created sql queries and run it on the server through query analyser. SQL queries can be automatically generated through the enterprise manager, by right clicking on a particular table>>> all tasks >>>> Genetare SQL Script.....
Thank you every body for replying

Cannot index bit field

What's the logic behind that? I have a large table that has a field called
"Deleted" to indicate that a record has been deleted. For various reasons
very few records will be marked Deleted. If I do a select where Deleted = 1
it will need to scan the entire table for those few deleted records when I
would have thought it would be able to look up an index to quickly find
those records.
Regards,
MichaelMichael,
Beginning with SQL Server 2000, indexes can contain bit columns.
Are you using an earlier version, or is the database in question set
to an earlier compatibility level?
Steve Kass
Drew University
Michael C wrote:

>What's the logic behind that? I have a large table that has a field called
>"Deleted" to indicate that a record has been deleted. For various reasons
>very few records will be marked Deleted. If I do a select where Deleted = 1
>it will need to scan the entire table for those few deleted records when I
>would have thought it would be able to look up an index to quickly find
>those records.
>Regards,
>Michael
>
>|||Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; you have not even learned the
vocabulary.
The use of deleted flags gives me flashbacks to the late 1950's when we
had tape file systems that used flags like that! Your entire approach
to RDBMS is **totally* wrong, at the foundational level.
Kid, listen to me; you are being told this by one of the people who
wrote the SQL standards. You might want to think about that.|||Hi Micheal,
Steve's right. Bit columns are support in indexes in SQL 2000. Perhapes
you are being caught by the issue that EM does allow you to create indexes
with bit columns from within the Table Design form? If you need a graphic
tool to create indexes right click on the table object and use the All Tasks
Manage Indexes option
--
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:%23nneZC7mFHA.2472@.TK2MSFTNGP15.phx.gbl...
> What's the logic behind that? I have a large table that has a field called
> "Deleted" to indicate that a record has been deleted. For various reasons
> very few records will be marked Deleted. If I do a select where Deleted =
> 1 it will need to scan the entire table for those few deleted records when
> I would have thought it would be able to look up an index to quickly find
> those records.
> Regards,
> Michael
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123468040.458991.66810@.z14g2000cwz.googlegroups.com...
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; you have not even learned the
> vocabulary.
> Kid, listen to me; you are being told this by one of the people who
> wrote the SQL standards. You might want to think about that.
No offence joe but you really are an idiot. I'm well aware you helped write
the SQL standards you remind the group on a regular basis. It's possibly why
databases are so behind the rest of computing world. There's one thing I
really dislike when dealing with computer people is those who overuse the
lingo in an attempt to make themselves look smarter. Such as your use of
"RDBMS" instead of database and insistance a column not be called a field.

> The use of deleted flags gives me flashbacks to the late 1950's when we
> had tape file systems that used flags like that! Your entire approach
> to RDBMS is **totally* wrong, at the foundational level.
What do you suggest instead of a deleted field? The record still needs to be
there so historical reports can run. The only thing I can see is that maybe
I should have used a field called DeletedDate but I don't need that
information.
Michael|||"GregO" <grego@.community.nospam> wrote in message
news:%23viqnW8mFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi Micheal,
> Steve's right. Bit columns are support in indexes in SQL 2000. Perhapes
> you are being caught by the issue that EM does allow you to create indexes
> with bit columns from within the Table Design form? If you need a graphic
> tool to create indexes right click on the table object and use the All
> Tasks Manage Indexes option
Cool! That looks like it, I can create the index with query analyser no
problem. Enterprise manager wouldn't let me select it from the dropdown or
type the name. Now I just have to check if there's any improvement in
performance, it seems to "select * from table where deleted = 1" almost
instantly without the index anyway but there may be performance advantages
in more complex queries.
Michael|||"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:O8rUXg9mFHA.2860@.TK2MSFTNGP15.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1123468040.458991.66810@.z14g2000cwz.googlegroups.com...
> No offence joe but you really are an idiot. I'm well aware you helped
> write the SQL standards you remind the group on a regular basis. It's
> possibly why databases are so behind the rest of computing world. There's
> one thing I really dislike when dealing with computer people is those who
> overuse the lingo in an attempt to make themselves look smarter. Such as
> your use of "RDBMS" instead of database and insistance a column not be
> called a field.
>
> What do you suggest instead of a deleted field? The record still needs to
> be there so historical reports can run. The only thing I can see is that
> maybe I should have used a field called DeletedDate but I don't need that
> information.
> Michael
>
You'll get in trouble, you called it a field again ;-)
Your approach of using a bit field (I do it to) is OK. The only alternative
that I know of is using a second table for deleted rows. The idea here is
that the active rows are in MyTable and the deleted rows are in
MyTableHistory. There are several advantages to this method...
1. Less storage. Yep, one bit per row.
2. When you don't need to access the deleted rows there's no need to query
the table. If there are alot of delete's, this can boost the performance.
3. If you need to query, both active and inactive rows use a Union All
query. You could also create a view to do this repeatedly for you.
The only downside is that you need to physically move the row to a different
table, this should be done in a tranaction, but this physical movement could
cause other issues with data integrity.
Regards
Colin Dawson
www.cjdawson.com|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:544444
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:OhCG9j9mFHA.1948@.TK2MSFTNGP12.phx.gbl...
> "GregO" <grego@.community.nospam> wrote in message
> news:%23viqnW8mFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Cool! That looks like it, I can create the index with query analyser no
> problem. Enterprise manager wouldn't let me select it from the dropdown or
> type the name. Now I just have to check if there's any improvement in
> performance, it seems to "select * from table where deleted = 1" almost
> instantly without the index anyway but there may be performance advantages
> in more complex queries.
> Michael
>
You could always use SQL Profiler to see the number of reads needed to
complete the query. Also take a look at the Actual Execution plan, this
will show how the query is processed.
Regards
Colin Dawson
www.cjdawson.com|||> There's one thing I
> really dislike when dealing with computer people is those who overuse the
> lingo in an attempt to make themselves look smarter. Such as your use of
> "RDBMS" instead of database and insistance a column not be called a field
Correct terminology helps when it is used to aid clarity and precision.
Do you really think that "RDBMS" means the same as "database"? If so,
then Joe may have made a valid point. Unlike you, Joe didn't use any
personal abuse to make his point.
In answer to your question, note that although you can index a BIT
column, those indexes tend to be of limited value because of the low
cardinality of BIT. Use Query Analyzer to create the index rather than
BIT.
David Portas
SQL Server MVP
--|||Joe invariably favors encoding schemes over flags. After running into a few
issues with flags, I have come to agree with him - encodings adapt better to
changing business requirements.
Instead of having a column of type Bit that indicates whether a row is
deleted or not, you could simply have a status column of type Char(1) - this
will take the same amount of space (both of them take up one byte of storage
according to Books Online), however the status column can have additional
values added at a later date without changing the schema. You could then
have a "pending" status, a "new" status, etc. Also, you can add an index
from Enterprise Manager (not that that is a big deal, really). And there is
no additional work on your part - instead of writing "WHERE Deleted = 1",
you write " WHERE Status = 'D' " (or whatever). Simple!
The last time I dealt with this was in a security settings table where the
previous developer had started with a simple flag indicating whether a user
was an administrator for the custom application in question. Later, the
business added additional user "types" (supervisor, auditor, etc.), and each
time the developer added a new flag column. However, the types were mutually
exclusive (you could not be an auditor and a supervisor at the same time),
but no check constraints were added. Even if check constraints where added,
they would be ugly. It was far better in the long run to simply change the
table to remove the bit flags.
The point is that there is always a way to turn a flag column into an
encoding, which adds no additional work on the DBA or the app developer, but
still provides all the functionality.
IHTH
"Michael C" <mculley@.NOSPAMoptushome.com.au> wrote in message
news:O8rUXg9mFHA.2860@.TK2MSFTNGP15.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1123468040.458991.66810@.z14g2000cwz.googlegroups.com...
> No offence joe but you really are an idiot. I'm well aware you helped
write
> the SQL standards you remind the group on a regular basis. It's possibly
why
> databases are so behind the rest of computing world. There's one thing I
> really dislike when dealing with computer people is those who overuse the
> lingo in an attempt to make themselves look smarter. Such as your use of
> "RDBMS" instead of database and insistance a column not be called a field.
>
> What do you suggest instead of a deleted field? The record still needs to
be
> there so historical reports can run. The only thing I can see is that
maybe
> I should have used a field called DeletedDate but I don't need that
> information.
> Michael
>

Cannot figure out how to write this script?

Hi was wondering if somebody could help on this one.
I have a table called LocalisationText and a field called "ResourceText".After localization bad data went into the ResourceText field (double and triple quotes instead of singles).

EG
Entrada ""{0}"" en ubicación """"{1}"""""
在或大约 ""{0}"" 的命令出错
User Post Code Group of Type ""{0}"" is ""{1}""

Now I have been asked to write a script that
converts all the double/triple quotes preceding {number} with single quotes .

The above corrected should look like

Entrada "{0}" en ubicación "{1}"
在或大约 "{0}" 的命令出错
User Post Code Group of Type "{0}" is "{1}"

Can you help?

Is this a text/ntext column or varchar/nvarchar column? Please take a look at PATINDEX function. You can use a pattern like '%""{[0-9]}""%' to locate the double-quotes you want to replace and then use updatetext for text/ntext or stuff for varchar/nvarchar. And you will need to perform this iteratively until you don't find any matches for the pattern. This will provide a replace with search based on a pattern behavior.|||

Hi there

thanks for your reply.

The column in question is an nvarchar 512.

I will look into patindex function .

Do you have a quick example how I could do it?

thanks

|||

You can then use just replace like:

declare @.s nvarchar(512), @.p int
set @.s = N'Entrada ""{0}"" en ubicación """"{1}"""""
在或大约 ""{0}"" 的命令出错
User Post Code Group of Type ""{0}"" is ""{1}""
'
print replace(replace(replace(@.s, '""', '"'), '""', '"'), '""', '"')

|||

Or you can do your update in a loop and additionally include required criteria

SELECT top 1 * FROM LocalisationText
WHILE @.@.ROWCOUNT > 0
BEGIN

UPDATE LocalisationText_Test
SET ResourceText = REPLACE(ResourceText,'''''','''')
WHERE ResourceText LIKE '%''''{[0-9]%'
OR ResourceText LIKE '%[0-9]}''''%'

END

GO

|||

thanks a lot for your reply that worked!!!!!!!

thanks again

|||You don't need the loop since REPLACE will replace all occurrences of the specified string. And the WHERE clause of UPDATE gets the qualifying rows. Just a simple UPDATE will suffice.

Cannot figure out how to write this script?

Hi was wondering if somebody could help on this one.
I have a table called LocalisationText and a field called "ResourceText".After localization bad data went into the ResourceText field (double and triple quotes instead of singles).

EG
Entrada ""{0}"" en ubicación """"{1}"""""
在或大约 ""{0}"" 的命令出错
User Post Code Group of Type ""{0}"" is ""{1}""

Now I have been asked to write a script that
converts all the double/triple quotes preceding {number} with single quotes .

The above corrected should look like

Entrada "{0}" en ubicación "{1}"
在或大约 "{0}" 的命令出错
User Post Code Group of Type "{0}" is "{1}"

Can you help?

Is this a text/ntext column or varchar/nvarchar column? Please take a look at PATINDEX function. You can use a pattern like '%""{[0-9]}""%' to locate the double-quotes you want to replace and then use updatetext for text/ntext or stuff for varchar/nvarchar. And you will need to perform this iteratively until you don't find any matches for the pattern. This will provide a replace with search based on a pattern behavior.|||

Hi there

thanks for your reply.

The column in question is an nvarchar 512.

I will look into patindex function .

Do you have a quick example how I could do it?

thanks

|||

You can then use just replace like:

declare @.s nvarchar(512), @.p int
set @.s = N'Entrada ""{0}"" en ubicación """"{1}"""""
在或大约 ""{0}"" 的命令出错
User Post Code Group of Type ""{0}"" is ""{1}""
'
print replace(replace(replace(@.s, '""', '"'), '""', '"'), '""', '"')

|||

Or you can do your update in a loop and additionally include required criteria

SELECT top 1 * FROM LocalisationText
WHILE @.@.ROWCOUNT > 0
BEGIN

UPDATE LocalisationText_Test
SET ResourceText = REPLACE(ResourceText,'''''','''')
WHERE ResourceText LIKE '%''''{[0-9]%'
OR ResourceText LIKE '%[0-9]}''''%'

END

GO

|||

thanks a lot for your reply that worked!!!!!!!

thanks again

|||You don't need the loop since REPLACE will replace all occurrences of the specified string. And the WHERE clause of UPDATE gets the qualifying rows. Just a simple UPDATE will suffice.