not a GROUP BY expression??

Why am I get this error message when I execute this query?

SELECT ODisciplina.BOUI FROM ODisciplina , Disciplina$student
WHERE ((Disciplina$student.PARENT$(+)=ODisciplina.BOUI))
GROUP BY (ODisciplina.BOUI )HAVING( Count(CHILD$)=0 )
ORDER BY (ODisciplina.NAME)

without the 3rd line it works and without the last line it works too.Tks, I've found the error. It should be:

SELECT ODisciplina.BOUI FROM ODisciplina , Disciplina$student
WHERE ((Disciplina$student.PARENT$(+)=ODisciplina.BOUI))
GROUP BY (ODisciplina.BOUI, ODisciplina.NAME )HAVING( Count(CHILD$)=0 )
ORDER BY (ODisciplina.NAME)

NoRows Property: need to print table header.

Is there way to print the Header column of a table along with the
NoRows message ?
Seems like it just prints the NoRows message and doesnt render any part
of the table.
Thanks
RaviYEah thats right, "no ros" indicate not to render the actual element only a
notice that there is no data, you could go for a workaround to toggle
visibilty for this control and put another control under the actual control
which would be displayed with a dummy row to just display the column
headers. I know that quite the double to maintain, but it´s actually a
possibility.
HTH, Jens SUessmeyer.
--
http://www.sqlserver2005.de
--
"Ravi R" <bofobofo@.yahoo.com> schrieb im Newsbeitrag
news:1114794174.442413.16990@.f14g2000cwb.googlegroups.com...
> Is there way to print the Header column of a table along with the
> NoRows message ?
> Seems like it just prints the NoRows message and doesnt render any part
> of the table.
>
> Thanks
> Ravi
>

NoRows Property in RS2005

Is the NoRows property of a Data Region broken in Reporting Services 2005?
I have set the message to be displayed for both subreports and tables and
the message is never displayed when the data set is empty. It used to work
in RS2000.
Thanks,
TomHi Tom,
Welcome to use MSDN Managed Newsgroup Support.
I have tested on my side. The NoRows Property works fine in SQL Reporting
Service 2005. One thing to note is that, if you put a table in a
dataregion, you can only get the NoRows Message defined in the dataregion,
not the one in table.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Wei Lu,
The NoRows property is available for both my table & included subreports,
however, in the case of the subreports, they still process even if the
NoRows property is set & the data set is empty.
As I said in my previous post:
I have tried setting the NoRows property for both the subreport and the
table but neither works. That's what prompted me to ask if NoRows is broken
in RS2005.
The documentation clearly states "When the dataset for a data region returns
no data, the data region is not rendered. Instead, a text box is rendered
that displays the value of the NoRows property." and just as clearly, it
doesn't work for me.
Tom
"Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
news:ZZf44cVTGHA.1952@.TK2MSFTNGXA03.phx.gbl...
> Hi Tom,
> Welcome to use MSDN Managed Newsgroup Support.
> I have tested on my side. The NoRows Property works fine in SQL Reporting
> Service 2005. One thing to note is that, if you put a table in a
> dataregion, you can only get the NoRows Message defined in the dataregion,
> not the one in table.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hi Tom,
I have tested in my side. It still works fine for subreport.
Here is my steps:
1. I create a report named Report1. Query data from a datasource, and put
the data in a table. I did not set the NoRows Property.
2. I create a report named Report2. Add a subreport control. And then I
set the ReportName as Report1 and NoRows Property to "Test".
3. I delete all the data in the database so that there will be no data in
the datasource.
4. I deploy the report to the report server and when I access the Report2,
it shows "Test" correctly.
Would you please check this in your side and tell me the result?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.sql

Normalisation Help

Not sure if this is the right place to put this message but, I am creating a stock control system for pc componenets, and need some help normalising the data, so that i can then implement the database.
The attibutes which need normalising are:

ProductID
Component
Price
Stock_level
Minimum_level
Reorder_amount
SupplierID
Supplier_name
Address1
Address2
Address3
Postcode
County
Email
TelNo
OrderCode
Quantity
Date_of_Order
Price
Quantity
Date
Total

If anybody could show me how to normalise this to third normal form, and show first and second Normal form, i would be very grateful.Hello,

is this a kind of homework ?

Think about the objects in the table ... there are
products, suppliers, addresses, orders ... these are the main objects.

Therefore you need a product, supplierer, address and order table.
The question is the relations between each object ??

Are there many prices for one product ??
Are there many adresses for one supplier and so on and so on

The other question ist ... what do you want with the second normal form ? Never heard, that somebody use this to create a physical database model ?!?!?!!?

Regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.comsql

Noobie SQL question

I am getting this message when I try to open the database in the enterprise manager. In all other locations it is telling me that the database is running any thoughts on how to resolve this?

The SQL Server \\CRISPINDB1 is not known to be running. Are you sure you wish to connect?

Anything at all will be a great help to me thanks in advance.is \\CRISPINDB1 your machine or a server ?|||you've got to start SQL Server services|||\\CRISPINDB1 is the name of the machine and the services are running. The machines that need to access the database seem to not be having a problem getting to it and using it. Only when I try to access it thru Enterprise Manager.|||I am using the Enterprise manager on the machine that the database is running. Sorry about not giving all the info to know whats going on.|||maybe you need to add the instancename

\\ServerName\InstanceName|||With further investigation I have learned that patients are a virtue I was just trying to expand the tree before the application had polled he database. Thank you for your help on this operator caused problem. And with this I am a little less of a Noob about SQL.

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Hi all,

Sometimes when I do "alter database ABCD set partner failover" I get the following message: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

In 99 percent of the cases after such message the first attempt to use an open connection would also raise an error such as "Exception: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"

After the first error all subsequent queries would run perfectly.

What am I missing?

Avi

The first message indicates that there were users found in the principal when you issued the failover. These users have to be killed and their transactions rolled back.

The second exception message tends to indicate that the connection getting the error was one of the users found in that database and they were killed.

|||

Thanks for the reply!

Could you please elaborate some more why the connection sometimes get killed and the action rolled back. Does not mirroring suppose to move the connection to the active database without killing it?

Thanks,

Avi

|||No, that is not how it works. It can reconnect to the new mirror, but the existing connection will get killed and its transaction rolled back.|||Standard database projection requires that if a connection is killed prior to the transaction being completed that the transaction must be rolled back. By initiating a failover all connections are severed and must therefor be rolled back.

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Hi all,

Sometimes when I do "alter database ABCD set partner failover" I get the following message: Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

In 99 percent of the cases after such message the first attempt to use an open connection would also raise an error such as "Exception: A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)"

After the first error all subsequent queries would run perfectly.

What am I missing?

Avi

The first message indicates that there were users found in the principal when you issued the failover. These users have to be killed and their transactions rolled back.

The second exception message tends to indicate that the connection getting the error was one of the users found in that database and they were killed.

|||

Thanks for the reply!

Could you please elaborate some more why the connection sometimes get killed and the action rolled back. Does not mirroring suppose to move the connection to the active database without killing it?

Thanks,

Avi

|||No, that is not how it works. It can reconnect to the new mirror, but the existing connection will get killed and its transaction rolled back.|||Standard database projection requires that if a connection is killed prior to the transaction being completed that the transaction must be rolled back. By initiating a failover all connections are severed and must therefor be rolled back.

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

Look through the resulting code and search for the phrase, "sp_add_jobstep." How many are listed?|||I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?
|||bump|||

graemeo wrote:

I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:

Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

Look through the resulting code and search for the phrase, "sp_add_jobstep." How many are listed?|||I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?
|||bump|||

graemeo wrote:

I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:

Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

Look through the resulting code and search for the phrase, "sp_add_jobstep." How many are listed?|||I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?
|||bump|||

graemeo wrote:

I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:

Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

Look through the resulting code and search for the phrase, "sp_add_jobstep." How many are listed?|||I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?
|||bump|||

graemeo wrote:

I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:

Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!sql

non-existent step?

My package runs fine in Business Studio. When I schedule it the error log message is 'jobmanager tried to run a non-existent step (2) for job ...'

There is only the one step defined in the package schedule. Does anyone know what the problem is?

Right-click on the agent job and select "Script Job as " and select create to new query window.

Look through the resulting code and search for the phrase, "sp_add_jobstep." How many are listed?|||I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?
|||bump|||

graemeo wrote:

I am having the same issue. When I look through the code as suggested I only see 4 steps, with step_id's 1 through 4. The step the manager is trying to run, however is listed as step 5.

What could be causing this?

Have you tried recreating the job? What job type is it? (SSIS, Command, etc...)

|||Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

|||

graemeo wrote:

Found the source of the issue - the @.on_success_action parameter of the final step was set to 'go to the next step' and not 'quit with success'. It works now, thanks

Good to know!

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during our
migration to SQL Server 2005. Any help is appreciated.
Thanks,
Dee
I dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during ou
r
migration to SQL Server 2005. Any help is appreciated.
Thanks,
DeeI dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
David Portas
SQL Server MVP
--

Non-ANSI Outer Join in MSSQL2K5

Hi,
When we ran upgrade advisor for 2005 against our existing database, we
received the following error message.
The query uses non-ANSI outer join operators ("*="or "=*"). to run this
query without modification, please set the compatiblity level for current
database to 80 or lower, using stored procedure SP_dbcmptlevel. it is
strongly recommended to rewrite the query using ANSI outer join operators
(LEFT OUTER JOIN, RIGTH OUTER JOIN). in the future versions of SQL server,
non-ANSI join operators will not be supported even in backward compatibility
modes.
We have quite a few files/objects where we have the non-ANSI standard outer
join syntax that we would have to convert to use the ANSI outer join. Does
anyone know of any tool that will automatically convert out non-ANSI outer
join to the ANSI one? This would definitely save us a lot of time during our
migration to SQL Server 2005. Any help is appreciated.
Thanks,
DeeI dont know of such a tool. One of the reasons why the old syntax is
deprecated is because of its inherent ambiguity. There has never been a
formal, standard definition of how to interpret queries that contain both
inner and outer style predicates in the WHERE clause. For that reason any
automated method of conversion to the new syntax probably couldn't be 100%
reliable.
--
David Portas
SQL Server MVP
--

Non-admin Citrix users cannot connect to SQL Server

We have an application that accesses a SQL 2000 server that is running on
several Citrix servers. Recently users started getting a message from the
application that there was a catastrophic failure when connecting to SQL
server. This was only happening if the user was not an administrator on the
Citrix server.
I tried connecting to SQL server from Enterprise Manager and Query Analyzer.
If I was logged on as an admin it worked, but if not, it did not work. In
Query Analyzer if I tried to log on to SQL server using a bad password, I got
back a message about bad user or password. When I put in the correct
password (or if using integrated security) the screen briefly acts like it is
connecting but then the entire isqlw program shuts down. Enterprise manager
shuts down when I try to connect to a server. As a non-admin user I am able
to connect using ISQL.exe.
We've checked on rights to the SQL files and everything looks OK. Is there
anything else to check?
Thanks
LehrSJ
Hello there,
I would like to suggest you applied the SQL 2000 SP4 first.
And please let me know the error log in the SQL Server.
This issue may related with some permission of folder. If you use the SQL
Authentication to connect the SQL server, did this issue occur?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Non Usesfull error Message

ERROR MSG:

Msg 515, Level 16, State 2, Server SQL\CIS, Procedure ssp_gas_CustomerCancellation_A, Line 161

Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.

The statement has been terminated.

Why didn't it actually tell me the Column and table name? i've never seen this before?

Anyone can help

Thanks

Marios

Oh I know this isn't the right forum but i couldn't find the right one to post in.

How is the procedure executed?

Not sure about why the error message is like that. Are there many fields in the table that do not allow nulls, I'd start checking those.

|||

It is called from another Stored Procedure the Stored Procedure that it rasises the error in is about 600lines of code and there are serval places where it Inserts int Tables and Temp Tables. I've looked at the data passed and which fields they are passed in to but i don't see where this error happends. It has only happend in our Production Database i can not so far reproduce it in our development environment. I don't understand why it doesn't show me the column and table

Thanks

Marios

|||

Do you explicity create your temporary tables or create/populate them through select into statments? Maybe that is why it doesn't show the table/column name (the fact that that it is a temporary table).

Nevermind, just tested that and it doesn't seem to be the case.

|||

no i explicitly defne the tables for temp tables before using them and but they are declare

CREATE #TableName

(

)

|||The only other thing I can think of, and I realize it may be impractical due to the large amount of code within the stored procedure is to use try...catch statements.|||i can't because it's a SQL 2000 server