sql error: working

Showing posts with label working. Show all posts

Monday, March 19, 2012

"User Cancelled" Error while viewing Crystal report

Hi,
I have a Crystal Reports v10 Report that has been working fine
for the past X years. It is invoked by a VB6 app using the
Report Viewer. For some reason, sometimes when the report is
executed, it displays the message "User Cancelled" immediately
after the viewer window is opened, even though the user did not
cancel the report. None of the code has changed, neither has the
report.

Does anyone have any clue as to why this message should appear?

Very urgent, Thanks in Advance...

Regards,
AnandhSometimes I see this message when there is an issue with the print spooler on the computer where the report is being generated. Try going to Control Panel > Administrative Tools > Services and then restarting the Print Spooler service. After doing this, reboot the computer.

If this doesn't work, then it isn't the print spooler causing the problem.

Good Luck! :)

"use database" not working after "create database"

I didn't think you could use CREATE DATABASE within a transaction.
"ATS" <ATS@.discussions.microsoft.com> wrote in message
news:5F6A2B04-1923-4BDB-B036-FF1B7C445477@.microsoft.com...
> PRB: "use database" not working after "create database"
> Please help,
> I have the following query:
> set XACT_ABORT on
> begin transaction
> create database MY_DB
> use MY_DB
> commit transaction
> The "use" statement fails saying the database does not exists, but I get
no
> error on the "create". And when I go to the server, sure enough the DB is
not
> there, which it should not be if the TX rolled back. So what is wrong? If
the
> "create" is bad, why do I not see an error on it?I do not think you can use the use statement inside a transaction either
"Armando Prato" wrote:

> I didn't think you could use CREATE DATABASE within a transaction.
> "ATS" <ATS@.discussions.microsoft.com> wrote in message
> news:5F6A2B04-1923-4BDB-B036-FF1B7C445477@.microsoft.com...
> no
> not
> the
>
>

"Update" in Trigger not working

Hi,

I am trying to concatenate the columns (PrevEmp01, PrevEmp02, PrevEmp03, PrevEmp04, PrevEmp05) into column (ft) using trigger:

CREATE TRIGGER [tg_prevemp_ft_update] ON [tStaffDir_PrevEmp]
FOR INSERT, UPDATE
AS
UPDATE tStaffDir_PrevEmp SET ft = PrevEmp01 + ' ' + PrevEmp02 + ' ' + PrevEmp03 + ' ' + PrevEmp04 + ' ' + PrevEmp05

I would expect the (ft) column will be populated accordingly regardless if any of the columns are (Null).
But the Trigger will only work when all the 5 columns are populated. If one of the column is (Null), the (ft) column will be (Null) too.

Please advise. Many Thanks.

Try this:

CREATE TRIGGER [tg_prevemp_ft_update]ON [tStaffDir_PrevEmp]FOR INSERT,UPDATEASUPDATE tStaffDir_PrevEmpSET ft =ISNULL(PrevEmp01,'') +' ' +ISNULL(PrevEmp02,'') +' ' +ISNULL(PrevEmp03,'') +' ' +ISNULL(PrevEmp04,'') +' ' +ISNULL(PrevEmp05,'')

Good luck.

|||

Thanks! It worked.

Friday, March 16, 2012

"The Lab Is Not Available" message from ELearning Offline Player

I'm working on Course 2944: Updating Your Reporting Skills to MicrosoftÂ® SQL
Serverâ?¢ 2005 Reporting Services
When I attempt to do the lab "Designing a Report", I get this message. Any
idea WHY it's not available or what I can do to fix the problem and get to
the lab?
--
Thanks,
Mike Yeager
President
MikeYeager.comI have exactly the same problem with lab 2924.
Any ideas anyone?
Thanks|||I'm also having this problem.
It's not all the labs because the first one worked. I've tried course
2927 and 2926.
Did you have any luck with a solution?|||No luck. This newsgroup doesn't seem to be monitored by MS staff.
--
Mike Yeager
President
MikeYeager.com

Sunday, March 11, 2012

"Stored Procedure" working every 5 minute, how?

hi
i working on SQL2000, and want to make "Stored Procedure" that's working
every 5 mibute, without making any calling.
how i can make that ?
--
Best Regards
Tark M. Siala
Development Manager
INTERNATIONAL COMPUTER CENTER (ICC.Networking)
Mobile: +218-91-3125900
E-Mail: tarksiala@.icc-libya.com
Messenger: tarksiala@.hotmail.com
Web Page: http://www.icc-libya.com
Blog: http://spaces.msn.com/tarksiala
======================================Hi Tark
You can set up a job to run the stored procedure on a 5-minute schedule.
Look up How To Create a Job.
(I also suggest you don't include quite so many newsgroups in your list.
What does this question have to do with clustering anyway? You included the
msde group also, does that mean you are running on msde? If so, you should
say that.)
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tark Siala" <tarksiala@.icc-libya.com> wrote in message
news:eH4GDSyXGHA.3936@.TK2MSFTNGP05.phx.gbl...
> hi
> i working on SQL2000, and want to make "Stored Procedure" that's working
> every 5 mibute, without making any calling.
> how i can make that ?
> --
> Best Regards
> Tark M. Siala
> Development Manager
> INTERNATIONAL COMPUTER CENTER (ICC.Networking)
> Mobile: +218-91-3125900
> E-Mail: tarksiala@.icc-libya.com
> Messenger: tarksiala@.hotmail.com
> Web Page: http://www.icc-libya.com
> Blog: http://spaces.msn.com/tarksiala
> ======================================
>
>

"SQL Server does not exist or access denied"

Hi everyone!

I receive error "SQL Server does not exist or access denied" when I try to run a web application from my notebook. (when I'm working at home) The weird thing is that it occurs sporadically. Sometimes the connection works just fine. Other times it fails. When I run the application on my workstation at the office everything works perfectly.

Here's the stack trace:

[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +484
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Torris.Common.Data.GetDataSet(String ConnectionString, String sql) in c:\_\Manager\common\data.cs:14
Torris.Manager.Sizes.ListSizes() in C:\_\Manager\Sizes.ascx.cs:145
Torris.Manager.Sizes.Page_Load(Object sender, EventArgs e) in C:\_\Manager\Sizes.ascx.cs:77
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Page.ProcessRequestMain() +731

-----------------

Of course, I make sure to change the connection strings in both environments.

Anyone have an idea of what I can look at to try to troubleshoot?

Thanks as always!!

Emi ^_^Hi Emi,

This is virtually always an access issue. How are you trying to connect to the db? IOW, what is your connection string? Are you sure your laptop is setup properly?

The other thing that strikes me is connection pooling. Do you do anything with pooling--change defaults, etc.--in the app?

Don|||Hi!

Thanks for responding. Still having the issue - it's so frustrating. I'm storing my connection string in the web.config which is then read and used in my Data.cs (database class)

Here's the connection string. In this case, the database is on the same machine as the web application. (similar to the how the client's production server is setup):


<add key="ConnectionString" value="server=MyComputerName;database=DatabaseName;uid=sa;pwd=NonBlankPassword;" />

An example of the actual data access - nothing complex here.


public static void ExecuteSql(string ConnectionString, string sql)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
conn.Dispose();
}

Like I mentioned before, all works great on my workstation at the office. This might totally be a hardware - issue. Is there anything wrong with the above code that would cause any pooling problems.

Thanks again!

Emi ^_^|||Hi Emi,

Okay, assuming that your sa password in the actual connection string is right, everything else looks okay.

One thing to check is whether the SS2K server is set up to use SQL Server authentication rather than Windows integrated authentication only. You can do that in Enterprise Manager, right-clicking on the server, and selecting Properties. Under the Security tab make sure that "SQL Server and Windows" is selected.

Sigh. But never mind about that. This doesn't explain the sporadic nature of the error. There is something going on with the connection pool. You're not doing anything with the connection pool in your code, but it has a problem when it goes to the pool for a connection. Hmm. And it's happening deep within the .NET framework.

Have you monitored the pool to make sure that connections are still available? It's a weird error to get for a depleted pool, though.

This will take more research. Anyone else have any ideas?

Don

"Server has not yet been opened" - XP

I have the "Crystal Reports Viewer Control" in one of my VB 6.0 applications. The report was working great on the Windows 2000 OS, but when I moved the application to Windows XP it now gives the error "Server has not yet been opened". I have made sure that a system DSN has been created for the ODBC connection. I have connected to the DSN using the "Crystal SQL Connectivity Test Utility - SQLCON32". I even tried registering or re-registering many of the crystal dependencies. I have Crystal Reports 8.5 Professional Edition loaded and the report works fine if I open it with the Professional Edition, but not the viewer. I am baffled.

Code;
'* Start creating the crystal report for the crystal viewer.
Set crystal = New CRAXDRT.Application
Set sRPT = crystal.OpenReport(App.Path & "\DailyIS.rpt")
sRPT.DiscardSavedData
DoEvents
sRPT.Database.Tables(1).SetLogOnInfo "oracle", gDSN, gUserID, gPassWord
sRPT.Database.SetDataSource RS
crViewer.ReportSource = sRPT

crViewer.ViewReport '* (Where the error pops up)

Any ideas what my Windows XP issue might be?'* Start creating the crystal report for the crystal viewer.
Set crystal = New CRAXDRT.Application
Set sRPT = crystal.OpenReport(App.Path & "\DailyIS.rpt")
sRPT.DiscardSavedData
DoEvents

sRPT.Database.SetDataSource RS
crViewer.ReportSource = sRPT
crViewer.ViewReport '* (Where the error pops up)

if you are set ADODB.recordset in CR you don't need

sRPT.Database.Tables(1).SetLogOnInfo "oracle", gDSN, gUserID, gPassWord|||Thank you hensa22 for that information. I commented out that line of code and ran the report in Windows 2000 and received the message "Server has not yet been opened.", but the report displays with that line of code left in. In Windows XP I receive the message "Server has not yet been opened." with or without the code.

Thursday, March 8, 2012

"SA" Insuffient Permission

Hi!
I just set up an alert to know when someone try to use the SA account.
This morning I received the alert in my inbox. Is working fine.
Do you know if there is any way that I can Know WHO is the user or computer
trying to use the SA account?
You help will be really aprecitated.
Kind Regards.
Hernan VilarHi,
Alert can fire a task which will search the sysprocesses table and
insert into a audit table (Normal table) with the below info,
HOSTNAME,
NTusername,
GETDATE()
Thanks
Hari
MCDBA
"Hernan Vilar" <hvilar@.corin.co.uk> wrote in message
news:0FE5FF70-C4F2-457B-8A68-F916DF6E3550@.microsoft.com...

quote:

> Hi!
> I just set up an alert to know when someone try to use the SA account.
> This morning I received the alert in my inbox. Is working fine.
> Do you know if there is any way that I can Know WHO is the user or

computer trying to use the SA account?

quote:

> You help will be really aprecitated.
> Kind Regards.
> Hernan Vilar

|||If the connection using SA is actually made this method will work. If the
connection fails then the method will not work bedause there is no entry in
sysprocesses. If the connection fails, I am not aware of a method to
determine who attempted the connection.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I would be carefull of this , I have 2 different servers that get hit
10-20,000 a night, it could get messy.
I would consolidate the logins or you may get a few emails 8-)..
Don
"Hernan Vilar" <hvilar@.corin.co.uk> wrote in message
news:0FE5FF70-C4F2-457B-8A68-F916DF6E3550@.microsoft.com...

quote:

> Hi!
> I just set up an alert to know when someone try to use the SA account.
> This morning I received the alert in my inbox. Is working fine.
> Do you know if there is any way that I can Know WHO is the user or

computer trying to use the SA account?

quote:

> You help will be really aprecitated.
> Kind Regards.
> Hernan Vilar

"Right" section of BorderStyle not working in last column

I have a multi-column report (with a header). I'm trying to draw a 1pt
border around the entire header. Not the report or table, just the header.
All of the sections of the border appear correctly except the sections in
the last column to the right. The "right" section of the last column does
not display in any mode.
Has anyone run into this? Is there a workaround?
Thanks!You can get a "clipped" or "missing" information in the last column when the
following is not true:
PageWidth - Left Margin - Right Marging >= (#Columns * ColumnWidth) +
((#Columns -1) * Column Spacing).
Please check how your multi column report is setup.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Creamer" <MichaelCreamer@.discussions.microsoft.com> wrote in
message news:425BF87E-437C-4B97-88B2-28207E96C53D@.microsoft.com...
>I have a multi-column report (with a header). I'm trying to draw a 1pt
> border around the entire header. Not the report or table, just the
> header.
> All of the sections of the border appear correctly except the sections in
> the last column to the right. The "right" section of the last column does
> not display in any mode.
> Has anyone run into this? Is there a workaround?
> Thanks!

Tuesday, March 6, 2012

"Pages breaks" when I add a page footer to the report

i have a report that calls several subreports. I had everything working fine
until I added a page footer to the main report. All I put in it was a small
textbox that is going to hold page numbers...
Anybody know why its page breaking? I hope it has nothing to do with the
implicit keep together when calling subreports...
Let me know if anybody has any ideas.. I've tried many things an nothing
works..
JoshuaThe length of the page exceeds the page size so it "page breaks" so try
checking the length of the page and the page you selected say A4 former
should be always less than later otherwise page break occurs.
Amarnath
"gdjoshua" wrote:
> i have a report that calls several subreports. I had everything working fine
> until I added a page footer to the main report. All I put in it was a small
> textbox that is going to hold page numbers...
> Anybody know why its page breaking? I hope it has nothing to do with the
> implicit keep together when calling subreports...
> Let me know if anybody has any ideas.. I've tried many things an nothing
> works..
> Joshua|||Not quite sure I understand what your saying... My main report in the
designer fits within the margins I have set up...
Explain further...
"Amarnath" wrote:
> The length of the page exceeds the page size so it "page breaks" so try
> checking the length of the page and the page you selected say A4 former
> should be always less than later otherwise page break occurs.
> Amarnath
> "gdjoshua" wrote:
> > i have a report that calls several subreports. I had everything working fine
> > until I added a page footer to the main report. All I put in it was a small
> > textbox that is going to hold page numbers...
> >
> > Anybody know why its page breaking? I hope it has nothing to do with the
> > implicit keep together when calling subreports...
> >
> > Let me know if anybody has any ideas.. I've tried many things an nothing
> > works..
> >
> > Joshua

Saturday, February 25, 2012

"Multi relational" table design question

Hi! Im working on a webapplication and has serious thoughts about howto optimize my table structure. To explain:

My tablestructure today

(simplified):

tbl_customers
cust_id
name
....

tbl_contacts
con_id
name
....

tbl_groups
grp_id
name
....

My subtables look like this(alternative 1):

tbl_sub_phone
phone_id
parent_type
parent_id
phone_area
phone_nr
....

tbl_sub_email
mail_id
parent_type
parent_id
email
....

As seen above every contact, group and customer can be assigned an unlimited amount of phonenumbers or emailadresses.
For example when entering a new email or a customer following will be inserted in tbl_sub_email: parent_type = 'cst', parent_id= '2' (the cust_id from tbl_customers), email ='gwerg@.fe.com'

The problem is i am uncertain if this is a very unefficient way of handling it? i see two alternatives:

Alternative 2:
i create x subtables for each table for example tbl_customers will get its mailadresses and phonenumbers contained in tbl_customers_phone and tbl_customers_email
What i am uncertain of here is if this would make things alot more troublesome when searching p? example after a specific phonenumber.

Alternative 3:

(simplified):

tbl_customers
cust_id
name
....

tbl_contacts
con_id
name
....

tbl_groups
grp_id
name
....

tables connection objects to subobjects

tbl_customers_phone
id
cust_id
phone_id

tbl_contacts_phone
id
con_id
phone_id

tbl_customers_mail
id
cust_id
mail_id

subtables

tbl_sub_phone
phone_id
phone_area
phone_nr
....

tbl_sub_email
mail_id
email
....

Ranking these three models, wich would be the most efficient and most inefficient performanswise?
What i want to avoid is performanceproblems when listing the objects, my indexing skills are a bit limited although im doing alot of reading and testing regarding this.
So thats why im asking for advice so that i can minimize the need of rebuilding the table structure when the application already has been starting to get used.

I also have another general question.

I have alot of select querys when i need to fetch data from several different tables.
Most of them is that i for example get an application from tbl_applications table, and that tables contains the columns cat1, cat2 and cat3 (wich are categories and contain the primary key integer to the tbl_sub_categorys table)
With 3 joins i retrieve these 3 category names returning 1 result with all the info i need.

Since ive been getting som strange results from the query analyzer(i got results that using clustered indexing for the primary key resulted in a slower query (higher cost)) i actually have another question.

Can it generally be summed up that a single query(join or subquery) generaly ils faster than getting the data in separate selects?
In the example above this i have the options either of using joins = 1 query or doing 2 querys and sorting the categorys codewise in aspx pages or doing 4 querys, one for the app followed by 1 for every category.
Any input regarding this?

As i said earlier im looking for the most efficient way of doing the things abov, would greatly appriechiate any input! Big Smile

Hi,

I don't know what tools come with SQL Server Express, but most full version RDBMS have tools that let you look at execution plans for queries and show the reletive cost of actions a given query plan will take. Look for terms like 'show plan', 'show query plan', 'query optimizer', or options for whatever 'query designer' tool you may have. You can also save a querry with an execution plan that works well as a stored procedure, which helps even more. Selective denormalization of a database (adding redundant data columns to a table), can improve performance, if the number of tables referenced by a single query exceeds a certain number (something like >7), depending on the RDBMS (you can look that up in the help). Doing this means you'll have to cover any data integrity issues on your own, though.

Indexing generally helps, but you'll want to test to see if it does in any given situation by testing. Also, consider the physical ordering of whatever tables you have. For any given table, there can be only one order in which the rows are stored. When a query needs to read through a table to find the rows it needs, the physical sort oder is the most effecient.

Doesn't sound like data integrity is a prime concern in your app. Keep in mind though, that in many cases, what you do to make queries more effecient can open up your database to data integrity issues. Optimally, you can copy a subset of a database where data integrity is the prime concern, to a database setup just for queries (maybe that's what you have already). This way, you can optimize without worrying about effects that might have on the data you need to keep safe. Also, you won't have people hitting and locking up a line-of-business database, if they don't need to.

Just some things to look into. In the end, you can chase optimization forever, so get an idea of what degree or performance is good enough, and then weigh that against the other issues you face. It could be that other parts of your app will show a better return on the time you spend than data structure optimization. Hope it helps. BRN..

|||

Well as i understand the main thing is to avoid table scans since this can be a *** when u reach a certain number of querys. Have done some tests inserting approx 50 000 - 100 000 rows and then checking performance degradation, wich is quite significant. What i aim at is creating a table structure that can be optimised as easy as possible. I have some lack in indexing skills wich makes me wonder how much one can improve the table structure i have now without splitting the subtables or creating intermediate tables that are used when listning the objects.

|||

Think i will split the tables for now and, if i see any reason to it, join them later(wich i dont think + if i would, it would be easier than the other way around).

And yeah, optimizing the code probably would be something worth spending time on as well. But will go with what i have now and read more into indexing and query optimization (The ladder wich i think is quite important to be able to chosse the right query/code structure).

Friday, February 24, 2012

"IS NOT NULL" is not working

We have data in datetime field and in the simple select statement where
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?Run the following and post the results:
select
dateofbusiness
, count (*)
from
MyTable
group by
dateofbusiness
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104271245.345995.62690@.f14g2000cwb.googlegroups.com...
We have data in datetime field and in the simple select statement where
clause "where dateofbusiness is not null" no data is returned. This
works in our other databases. This is a simple select with no
aggregation functions such as count(*) and no group bys. No joins.
We checked for table corruption and rebuilt the table and indexes.
I'm completely at a loss.
Anybody run into this?|||Try,
where ISDATE(dateofbusiness) <> 0
--
Message posted via http://www.sqlmonster.com|||2003-03-25 00:00:00.000 15
2003-03-26 00:00:00.000 17
2003-03-27 00:00:00.000 73
2003-03-28 00:00:00.000 9
2003-04-11 00:00:00.000 18
2003-04-14 00:00:00.000 131
2003-04-15 00:00:00.000 11
2003-04-25 00:00:00.000 2
2003-04-28 00:00:00.000 18
2003-05-06 00:00:00.000 17
2003-05-08 00:00:00.000 16
2003-05-12 00:00:00.000 23
2003-05-13 00:00:00.000 80
2003-05-16 00:00:00.000 27
2003-05-19 00:00:00.000 15
2003-06-05 00:00:00.000 23
2003-06-16 00:00:00.000 11942
2003-06-17 00:00:00.000 12038
2003-06-18 00:00:00.000 12166
2003-06-19 00:00:00.000 12307
2003-06-20 00:00:00.000 12502
2003-06-21 00:00:00.000 11840
2003-06-22 00:00:00.000 11341
2003-06-23 00:00:00.000 11784
2003-06-24 00:00:00.000 12055
2003-06-25 00:00:00.000 11905
2003-06-26 00:00:00.000 12088
2003-06-27 00:00:00.000 12451
2003-06-28 00:00:00.000 11956
2003-06-29 00:00:00.000 11303
2003-06-30 00:00:00.000 12072
2003-07-01 00:00:00.000 11545
2003-07-02 00:00:00.000 11604
2003-07-03 00:00:00.000 11857
2003-07-04 00:00:00.000 636
2003-07-05 00:00:00.000 11045
2003-07-06 00:00:00.000 10303
2003-07-07 00:00:00.000 11324
2003-07-08 00:00:00.000 11002
2003-07-09 00:00:00.000 11446
2003-07-10 00:00:00.000 11486
2003-07-11 00:00:00.000 12115
2003-07-12 00:00:00.000 11240
2003-07-13 00:00:00.000 10871
2003-07-14 00:00:00.000 11395
2003-07-15 00:00:00.000 11563
2003-07-16 00:00:00.000 19
2003-08-27 00:00:00.000 26
2003-08-28 00:00:00.000 25
2003-09-16 00:00:00.000 805
2003-09-17 00:00:00.000 806
2003-09-18 00:00:00.000 783
2003-09-19 00:00:00.000 365
2003-09-20 00:00:00.000 312
2003-09-21 00:00:00.000 318
2003-09-22 00:00:00.000 334
2003-09-23 00:00:00.000 347
2003-09-24 00:00:00.000 358
2003-09-25 00:00:00.000 390
2003-09-26 00:00:00.000 388
2003-09-27 00:00:00.000 354
2003-09-28 00:00:00.000 340
2003-09-29 00:00:00.000 334
2003-09-30 00:00:00.000 367
2003-10-02 00:00:00.000 2
2003-10-03 00:00:00.000 7
2003-10-06 00:00:00.000 19
2003-10-08 00:00:00.000 13
2003-10-22 00:00:00.000 15
2003-12-01 00:00:00.000 82068
2003-12-02 00:00:00.000 83850
2003-12-03 00:00:00.000 84996
2003-12-04 00:00:00.000 85926
2003-12-05 00:00:00.000 89212
2003-12-06 00:00:00.000 86304
2003-12-07 00:00:00.000 75057
2003-12-08 00:00:00.000 83997
2003-12-09 00:00:00.000 83979
2003-12-10 00:00:00.000 86040
2003-12-11 00:00:00.000 87731
2003-12-12 00:00:00.000 91075
2003-12-13 00:00:00.000 88179
2003-12-14 00:00:00.000 73306
2003-12-15 00:00:00.000 161373
2003-12-16 00:00:00.000 94041
2003-12-17 00:00:00.000 97228
2003-12-18 00:00:00.000 99609
2003-12-19 00:00:00.000 102561
2003-12-20 00:00:00.000 98621
2003-12-21 00:00:00.000 85470
2003-12-22 00:00:00.000 102115
2003-12-23 00:00:00.000 103167
2003-12-24 00:00:00.000 50463
2003-12-26 00:00:00.000 95627
2003-12-27 00:00:00.000 93059
2003-12-28 00:00:00.000 87425
2003-12-29 00:00:00.000 96371
2003-12-30 00:00:00.000 97959
2003-12-31 00:00:00.000 81578
2004-02-01 00:00:00.000 384
2004-02-06 00:00:00.000 26
2004-02-12 00:00:00.000 52
2004-02-17 00:00:00.000 22
2004-02-19 00:00:00.000 17
2004-02-20 00:00:00.000 50
2004-02-21 00:00:00.000 107
2004-02-23 00:00:00.000 90
2004-02-24 00:00:00.000 22
2004-02-27 00:00:00.000 56
2004-03-01 00:00:00.000 56
2004-03-02 00:00:00.000 22
2004-03-05 00:00:00.000 107
2004-03-08 00:00:00.000 22
2004-03-11 00:00:00.000 501
2004-04-22 00:00:00.000 12
2004-04-29 00:00:00.000 22
2004-04-30 00:00:00.000 48
2004-05-03 00:00:00.000 44
2004-05-04 00:00:00.000 19
2004-05-07 00:00:00.000 22
2004-05-10 00:00:00.000 22
2004-05-13 00:00:00.000 1998
2004-05-26 00:00:00.000 15
2004-05-28 00:00:00.000 5
2004-06-01 00:00:00.000 52
2004-06-02 00:00:00.000 44
2004-06-03 00:00:00.000 31
2004-06-04 00:00:00.000 18
2004-06-07 00:00:00.000 18
2004-06-16 00:00:00.000 672
2004-06-17 00:00:00.000 698
2004-06-19 00:00:00.000 2
2004-06-20 00:00:00.000 640
2004-06-21 00:00:00.000 692
2004-06-22 00:00:00.000 326
2004-06-23 00:00:00.000 378
2004-06-24 00:00:00.000 400
2004-06-25 00:00:00.000 348
2004-06-26 00:00:00.000 345
2004-06-29 00:00:00.000 25
2004-06-30 00:00:00.000 82
2004-07-01 00:00:00.000 23
2004-07-08 00:00:00.000 21
2004-07-12 00:00:00.000 21
2004-07-15 00:00:00.000 467
2004-07-19 00:00:00.000 432
2004-07-23 00:00:00.000 427
2004-07-27 00:00:00.000 21
2004-08-08 00:00:00.000 392|||Also try,
"where convert(char(10),dateofbusiness,120) is not null"
and post results,
Jon
--
Message posted via http://www.sqlmonster.com|||OK, so there are none that are not null. Could you please post the DDL for
your table and the exact query you ran?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104272808.874749.162530@.z14g2000cwz.googlegroups.com...
2003-03-25 00:00:00.000 15
2003-03-26 00:00:00.000 17
2003-03-27 00:00:00.000 73
2003-03-28 00:00:00.000 9
2003-04-11 00:00:00.000 18
2003-04-14 00:00:00.000 131
2003-04-15 00:00:00.000 11
2003-04-25 00:00:00.000 2
2003-04-28 00:00:00.000 18
2003-05-06 00:00:00.000 17
2003-05-08 00:00:00.000 16
2003-05-12 00:00:00.000 23
2003-05-13 00:00:00.000 80
2003-05-16 00:00:00.000 27
2003-05-19 00:00:00.000 15
2003-06-05 00:00:00.000 23
2003-06-16 00:00:00.000 11942
2003-06-17 00:00:00.000 12038
2003-06-18 00:00:00.000 12166
2003-06-19 00:00:00.000 12307
2003-06-20 00:00:00.000 12502
2003-06-21 00:00:00.000 11840
2003-06-22 00:00:00.000 11341
2003-06-23 00:00:00.000 11784
2003-06-24 00:00:00.000 12055
2003-06-25 00:00:00.000 11905
2003-06-26 00:00:00.000 12088
2003-06-27 00:00:00.000 12451
2003-06-28 00:00:00.000 11956
2003-06-29 00:00:00.000 11303
2003-06-30 00:00:00.000 12072
2003-07-01 00:00:00.000 11545
2003-07-02 00:00:00.000 11604
2003-07-03 00:00:00.000 11857
2003-07-04 00:00:00.000 636
2003-07-05 00:00:00.000 11045
2003-07-06 00:00:00.000 10303
2003-07-07 00:00:00.000 11324
2003-07-08 00:00:00.000 11002
2003-07-09 00:00:00.000 11446
2003-07-10 00:00:00.000 11486
2003-07-11 00:00:00.000 12115
2003-07-12 00:00:00.000 11240
2003-07-13 00:00:00.000 10871
2003-07-14 00:00:00.000 11395
2003-07-15 00:00:00.000 11563
2003-07-16 00:00:00.000 19
2003-08-27 00:00:00.000 26
2003-08-28 00:00:00.000 25
2003-09-16 00:00:00.000 805
2003-09-17 00:00:00.000 806
2003-09-18 00:00:00.000 783
2003-09-19 00:00:00.000 365
2003-09-20 00:00:00.000 312
2003-09-21 00:00:00.000 318
2003-09-22 00:00:00.000 334
2003-09-23 00:00:00.000 347
2003-09-24 00:00:00.000 358
2003-09-25 00:00:00.000 390
2003-09-26 00:00:00.000 388
2003-09-27 00:00:00.000 354
2003-09-28 00:00:00.000 340
2003-09-29 00:00:00.000 334
2003-09-30 00:00:00.000 367
2003-10-02 00:00:00.000 2
2003-10-03 00:00:00.000 7
2003-10-06 00:00:00.000 19
2003-10-08 00:00:00.000 13
2003-10-22 00:00:00.000 15
2003-12-01 00:00:00.000 82068
2003-12-02 00:00:00.000 83850
2003-12-03 00:00:00.000 84996
2003-12-04 00:00:00.000 85926
2003-12-05 00:00:00.000 89212
2003-12-06 00:00:00.000 86304
2003-12-07 00:00:00.000 75057
2003-12-08 00:00:00.000 83997
2003-12-09 00:00:00.000 83979
2003-12-10 00:00:00.000 86040
2003-12-11 00:00:00.000 87731
2003-12-12 00:00:00.000 91075
2003-12-13 00:00:00.000 88179
2003-12-14 00:00:00.000 73306
2003-12-15 00:00:00.000 161373
2003-12-16 00:00:00.000 94041
2003-12-17 00:00:00.000 97228
2003-12-18 00:00:00.000 99609
2003-12-19 00:00:00.000 102561
2003-12-20 00:00:00.000 98621
2003-12-21 00:00:00.000 85470
2003-12-22 00:00:00.000 102115
2003-12-23 00:00:00.000 103167
2003-12-24 00:00:00.000 50463
2003-12-26 00:00:00.000 95627
2003-12-27 00:00:00.000 93059
2003-12-28 00:00:00.000 87425
2003-12-29 00:00:00.000 96371
2003-12-30 00:00:00.000 97959
2003-12-31 00:00:00.000 81578
2004-02-01 00:00:00.000 384
2004-02-06 00:00:00.000 26
2004-02-12 00:00:00.000 52
2004-02-17 00:00:00.000 22
2004-02-19 00:00:00.000 17
2004-02-20 00:00:00.000 50
2004-02-21 00:00:00.000 107
2004-02-23 00:00:00.000 90
2004-02-24 00:00:00.000 22
2004-02-27 00:00:00.000 56
2004-03-01 00:00:00.000 56
2004-03-02 00:00:00.000 22
2004-03-05 00:00:00.000 107
2004-03-08 00:00:00.000 22
2004-03-11 00:00:00.000 501
2004-04-22 00:00:00.000 12
2004-04-29 00:00:00.000 22
2004-04-30 00:00:00.000 48
2004-05-03 00:00:00.000 44
2004-05-04 00:00:00.000 19
2004-05-07 00:00:00.000 22
2004-05-10 00:00:00.000 22
2004-05-13 00:00:00.000 1998
2004-05-26 00:00:00.000 15
2004-05-28 00:00:00.000 5
2004-06-01 00:00:00.000 52
2004-06-02 00:00:00.000 44
2004-06-03 00:00:00.000 31
2004-06-04 00:00:00.000 18
2004-06-07 00:00:00.000 18
2004-06-16 00:00:00.000 672
2004-06-17 00:00:00.000 698
2004-06-19 00:00:00.000 2
2004-06-20 00:00:00.000 640
2004-06-21 00:00:00.000 692
2004-06-22 00:00:00.000 326
2004-06-23 00:00:00.000 378
2004-06-24 00:00:00.000 400
2004-06-25 00:00:00.000 348
2004-06-26 00:00:00.000 345
2004-06-29 00:00:00.000 25
2004-06-30 00:00:00.000 82
2004-07-01 00:00:00.000 23
2004-07-08 00:00:00.000 21
2004-07-12 00:00:00.000 21
2004-07-15 00:00:00.000 467
2004-07-19 00:00:00.000 432
2004-07-23 00:00:00.000 427
2004-07-27 00:00:00.000 21
2004-08-08 00:00:00.000 392|||And to emphasize futher the same statement with the count(*) function
returned no results. And without the count function and group by it
will return results, however, when keying in on an additional field
such as storeid like "where storeid = 34 and DateofBusiness is not
null" nothing returns. If you filter by store id, i see dateofbusiness
had values in it?
set transaction isolation level read uncommitted
select
dateofbusiness,
count (*)
from
Temp_HstSalesByInterval
where dateofbusiness is not null
group by
dateofbusiness|||John we have already done this as a hotfix to our database,
"rtrim(dateofbusiness) is not null" works, too. However we have over
2000 databases that do not have the hot fix, and other code affecting
the table.
SQL is not doing what its supposed to be doing and I'm looking for an
answer. I don't usually post until....|||Ken,
I did run into this problem in the past and am trying to remember what caused it to happen and how I fixed it. I've tried to replicate the date-time like your example and my query worked just fine.
I thought it had something to do with the hh:mm:ss being all zeros, but I'm not sure that's the problem. I also tried the ansi-null option on the db and it still worked. I also tried the connection object and thought maybe one of the settings was incorrectly set, but that did reproduce your error.
If I find out, I'll post or you can email me at: corncrowe@.aol.com
Jon
--
Message posted via http://www.sqlmonster.com|||CREATE TABLE [dbo].[Temp_HstSalesByInterval] (
[DateOfBusiness] [datetime] NULL ,
[FKStoreId] [int] NULL ,
[FKRevenueId] [int] NULL ,
[Period] [int] NULL ,
[Type] [int] NULL ,
[TypeId] [int] NULL ,
[TypeId2] [int] NULL ,
[Amount] [float] NULL ,
[OpenHour] [int] NULL ,
[lCount] [int] NULL ,
[DestinationServer] [int] NULL
) GO
CREATE CLUSTERED INDEX [IX_Temp_HstSalesByInterval] ON
[dbo].[Temp_HstSalesByInterval]([DateOfBusiness] DESC , [FKStoreId],
[DestinationServer]) ON [PRIMARY]
GO
select * from Temp_HstSalesByInterval where fkstoreid = 23 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 23 will return
results and dateofbusiness is populated.|||Ken,
The all zeros in hh:mm:ss is most likely the problem. I asked the other programmer here and we both agreed that we encountered this problem when trying to query a date field with zeros in the hh:mm:ss stamp.
Why or how did you manage to write all zeros on a date/time field? In SQL there is no such thing as 00:00:00?
Sorry,
Jon
--
Message posted via http://www.sqlmonster.com|||Ken,
Maybe we are wrong about the zeros. But threw that out for you,
Jon
--
Message posted via http://www.sqlmonster.com|||> Why or how did you manage to write all zeros on a date/time field? In SQL
there is no such thing as 00:00:00?
Midnight is not a valid time?|||create table lookatdate (d datetime null)
insert lookatdate values ('09/04/2004')
select * from lookatdate
returns 2004-09-04 00:00:00.000|||This never happenend. Jon mistook reading one field as two fields.
09/23/2004 00:00:00:000|||> This never happenend. Jon mistook reading one field as two fields.
> 09/23/2004 00:00:00:000
Okay, maybe another reason to visit the link from FAQ #5006 to generate
insert statements, instead of presenting sample data in heap style...
http://vyaskn.tripod.com/code.htm#inserts|||Well maybe there something to what you are saying, but why does it work
in the other databases on the same server?|||Ken,
I have another suggestion which may sound odd. But consider this:
"Specifies that the Boolean result be negated. The predicate reverses its return values, returning TRUE if the value is not NULL, and FALSE if the value is NULL."
"The WHERE and HAVING clauses in a SELECT statement control the rows from the source tables that are used to build the result set. WHERE and HAVING are filters. They specify a series of search conditions, and only those rows that meet the terms of the search conditions are used to build the result set. Those rows meeting the search conditions are said to be qualified to participate in the result set. (Microsoft)"
I am thinking that when you build the resultset with "not null" it evaluates the criteria as "unknown" and thereby won't produce any resultset. But when you trim or otherwise convert the datatype then the query returns a resultset.
Try a couple other approaches to see if you get a resultset:
select * from tblName
where not (datefield) is null
-or-
select somefield where datefield is not null
I am also wondering if building the resultset with the all "*" isn't masking the issue either? But either way, NOT NULL evaluates to a true condition if the value is not null. Otherwise, I think the field "as defined" is unknown.
I tried to replicate your error with a test database and couldn't. I tried several db_options and ansi null defaults and still couldn't reproduce your error. I do vaguely remember this problem, but can't for the life of me remember what cause it or how I fixed it.
Sorry I couldn't be more of a help,
Jon
--
Message posted via http://www.sqlmonster.com|||Ken,
The first suggestion is to make sure that FKstoreid 34 actually has data.
If so, then if the query works on other databases on the same server, that
would suggest something is peculiar to the data in that database.
What happens if you restore that particular db to another SQL server with
the same version and run the query? If it still returns no results, that
would suggest definitely something in the data is causing the issue.
At this stage in SQL Server 2000's lifecyle, it seems unlikely that your
data has uncovered a bug in the query engine, but it's possible. Try adding
an identity column as a primary key and see if that makes a difference.
Maybe grouping on a date column in a heap...?
Ron
--
Ron Talmage
SQL Server MVP
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104273359.147443.196280@.f14g2000cwb.googlegroups.com...
> And to emphasize futher the same statement with the count(*) function
> returned no results. And without the count function and group by it
> will return results, however, when keying in on an additional field
> such as storeid like "where storeid = 34 and DateofBusiness is not
> null" nothing returns. If you filter by store id, i see dateofbusiness
> had values in it?
> set transaction isolation level read uncommitted
> select
> dateofbusiness,
> count (*)
> from
> Temp_HstSalesByInterval
> where dateofbusiness is not null
> group by
> dateofbusiness
>|||Hi Ron,
The store exists. we are not doing any group bys or joins. Its a very
simple select statement.
select * from Temp_HstSalesByInterval where fkstoreid = 34 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 34 (without
above) will return
results and dateofbusiness is populated.|||I'm wondering if it's possible to create a new table and populate it with
the contents of the old one - preferably by bcp'ing the data out and back in
again.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104333999.756145.81940@.f14g2000cwb.googlegroups.com...
Hi Ron,
The store exists. we are not doing any group bys or joins. Its a very
simple select statement.
select * from Temp_HstSalesByInterval where fkstoreid = 34 and
dateofbusiness is not null
select * from Temp_HstSalesByInterval where fkstoreid = 34 (without
above) will return
results and dateofbusiness is populated.|||Hi Jon
select * from tblName
where not (datefield) is null
this produces no records
select somefield where datefield is not null
This will work, but when you add "and fkstoreid = anynumber" nothing
returns. If you query only on fkstoreid only you will see the results
and dateofbusiness is populated. I did an update to this table to set
datetime field with refreshed values and that did not fix it.
The actual select statement lists the fields not (*). both have the
same affect.|||> select somefield where datefield is not null
> This will work, but when you add "and fkstoreid = anynumber" nothing
> returns.
This does not seem possible. How large is your database? I am wondering if
you would be able to post a copy of it online and someone could try to
reproduce the problem on your actual data. I am guessing there is something
else inaccurate here that you're not catching...|||Hi Tom,
We did this with DTS.|||Ken,
Another (way out there) suggestion. Maybe, just maybe, there is a constraint on the fkStoreId between parent and child? I know this sounds weird, but maybe when you consider the where clause is failing when you combine both the fkStoreId and DateofBusiness fields in the same statement.
The condition is returning false for some reason. I know it's really a long shot, but could there be an issue with the Stores table referential integrity? Is the fkStoreId an orphan? I seriously doubt that this is the culprit, but weirder things have been know to happen in SQL world.
I did create your temp table and inserted rows of data, but couldn't reproduce the error. I don't know what the parent table looks like so I could do anything on that thread.
Good luck,
Jon
--
Message posted via http://www.sqlmonster.com|||Hi Jon, there is no contstraint defined on fkstoreid. However manually
dropping the nonunique clustered index(DateofBusiness, FKStoreid,
DestinationServer) columns (all allow nulls) and recreated it and now
not null is working finally! :)
The other db man here swears he ran DBCC REINDEX on the table and that
did not fix the issue. Isn't running DBCC REINDEX the same as dropping
and recreating an index? Any know issues with this?|||This is great news. It's hard to say what happened here. If you have a
copy of the database before you did the DBREINDEX, I'd be tempted to run
DBCC CHECKTABLE and see if it found anything.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104344333.997556.246980@.c13g2000cwb.googlegroups.com...
Hi Jon, there is no contstraint defined on fkstoreid. However manually
dropping the nonunique clustered index(DateofBusiness, FKStoreid,
DestinationServer) columns (all allow nulls) and recreated it and now
not null is working finally! :)
The other db man here swears he ran DBCC REINDEX on the table and that
did not fix the issue. Isn't running DBCC REINDEX the same as dropping
and recreating an index? Any know issues with this?|||Jon, when I recreated the nonunique clustered index DateOfBusiness
order was changed from descending to ascending. "IS NOT NULL" on
dateofbusiness works. When it was set back to descending it doesn't
work.
On the same table in other databases, descending does not have an issue
with the "IS NOT NULL" not working. However, this table has 3 million
records and the tables in the other databases have less than 200,000
records.
Throughing this out to see if you can recreate the issue?|||Ken,
Sorry if you did this already, but have you posted the result of
SELECT @.@.VERSION ? Also, is there anything you can see different with
this particular database, such as a compatibility level setting or
something?
Steve Kass
Drew University
Ken wrote:
>Jon, when I recreated the nonunique clustered index DateOfBusiness
>order was changed from descending to ascending. "IS NOT NULL" on
>dateofbusiness works. When it was set back to descending it doesn't
>work.
>On the same table in other databases, descending does not have an issue
>with the "IS NOT NULL" not working. However, this table has 3 million
>records and the tables in the other databases have less than 200,000
>records.
>Throughing this out to see if you can recreate the issue?
>
>|||Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
compatability on all databases 8.0|||There has a been a security hotfix issued since then. That would bring the
version to 8.00.818:
http://www.microsoft.com/downloads/details.aspx?FamilyID=9814AE9D-BD44-40C5-ADD3-B8C99618E68D&displaylang=en
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104349480.164105.285990@.c13g2000cwb.googlegroups.com...
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
Edition on Windows NT 5.2 (Build 3790: )
compatability on all databases 8.0|||Hm. Are the "working" databases on the same instance of SQL Server? If
not, and the others are a later version,
try installing 8.00.818 (or later) and see if the problem goes away.
There are sometimes bug fixes in security hotfixes, since the hotfixes
are cumulative. If that's not the problem, I'll see if I can come up
with some other questions!
SK
Ken wrote:
>Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
>14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
>Edition on Windows NT 5.2 (Build 3790: )
>compatability on all databases 8.0
>
>|||Ken,
"nonunique clustered index DateOfBusiness order was changed from descending to ascending. "IS NOT NULL" on dateofbusiness works. When it was set back to descending it doesn't work."
CREATE CLUSTERED INDEX [IX_Temp_HstSalesByInterval] ON
[dbo].[Temp_HstSalesByInterval]([DateOfBusiness] DESC , [FKStoreId],
[DestinationServer]) ON [PRIMARY]
I was leading toward the index as my next suggestion. The index is a composite and is made up of different datatypes. When you were doing the query the index is backwards (desc) order for date and acs for the other two fields. That's probably why the query never returned anything. I can replicate this error on a test database with 30 million call records using essentially the same logic. I'll let you know later what happens.
I probably wouldn't use mix order for my indices. Maybe that was what I had done in the past and cause a problem? Anyway, glad you were able to correct the problem.
Jon
--
Message posted via http://www.sqlmonster.com|||Hi Jon, please replicate the error. Why does it only happen on tables
with a large number of rows?
My other thought could still be a data issue depending on what gets
evaluated first?|||> My other thought could still be a data issue depending on what gets
> evaluated first?
The WHERE clause does not work that way. Put the individual clauses in any
order, and the result will be the same...|||Hi Tom,
The hotfix does not address the particular problem in any
documentation. We have a lot of servers (clustered) and my boss never
lets me do anything on a guess, only until I have found the problem and
I can qualify it. If I could validate that the problem was server wide
and this occurrance was a documented bug or issue, I would implement
this asap.
I do appreciate your assistance. Your suggestion may be a fix to this,
but I'm on a supertanker thats not easy to steer or deploy changes.|||I hear ya. Nevertheless, the patch is a security hotfix and closes a
vulnerability. As another poster mentioned, fixes are cumulative and it is
possible that your issue may have been addressed there.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Ken" <kshapley@.sbcglobal.net> wrote in message
news:1104353652.360283.184350@.f14g2000cwb.googlegroups.com...
Hi Tom,
The hotfix does not address the particular problem in any
documentation. We have a lot of servers (clustered) and my boss never
lets me do anything on a guess, only until I have found the problem and
I can qualify it. If I could validate that the problem was server wide
and this occurrance was a documented bug or issue, I would implement
this asap.
I do appreciate your assistance. Your suggestion may be a fix to this,
but I'm on a supertanker thats not easy to steer or deploy changes.|||Ken,
Maybe this newsgroup thread will help. Sorry I didn't remember this,
but it looks to me like the same bug you're seeing, and 8.00.818 fixed it:
http://groups-beta.google.com/groups?hl=en&q=8.00.818+fix+bug+kass+-turkish&qt_s=Search+Groups
SK
Ken wrote:
>Hi Tom,
>The hotfix does not address the particular problem in any
>documentation. We have a lot of servers (clustered) and my boss never
>lets me do anything on a guess, only until I have found the problem and
>I can qualify it. If I could validate that the problem was server wide
>and this occurrance was a documented bug or issue, I would implement
>this asap.
>I do appreciate your assistance. Your suggestion may be a fix to this,
>but I'm on a supertanker thats not easy to steer or deploy changes.
>
>|||Jon, I have a sneaky suspicioun that its in the data. When we move the
same data to another table it produces the same error. If we move or
create different data it does not. Weird.

"IS NOT NULL" is not working

"Invalid distribution command" in transaction replication

HI
I had a replication working nicely for long time. SQL 2k, SP3.
One day it stoped working with the error "Invalid distribution command." and
last action is: insert into "ponim" ("tz_pone.
Transaction sequence number and command ID of last execution batch are
0x0000829800000ABB000100000000 and 1.
I think maybe that day I added a new column using sp_repladdcolumn.
I want to say that the same script I run for that replication I ran for
another 3 similar replication and they all still work prefectly.
Any Ideas what I can do?
Please take in consider that I have lots of data in the publication that
couldn't be send to the subscriber becouse of the above error.
Thanks
use sp_browsereplcmds to identiy this command. Then apply this command
on the subcriber.
Then update msreplication_subscripions with the xaxct_seqno for this
command. This will cause it to skip this command.

"Invalid distribution command" in transaction replication

HI
I had a replication working nicely for long time. SQL 2k, SP3.
One day it stoped working with the error "Invalid distribution command." and
last action is: insert into "ponim" ("tz_pone.
Transaction sequence number and command ID of last execution batch are
0x0000829800000ABB000100000000 and 1.
I think maybe that day I added a new column using sp_repladdcolumn.
I want to say that the same script I run for that replication I ran for
another 3 similar replication and they all still work prefectly.
Any Ideas what I can do?
Please take in consider that I have lots of data in the publication that
couldn't be send to the subscriber becouse of the above error.
Thanksuse sp_browsereplcmds to identiy this command. Then apply this command
on the subcriber.
Then update msreplication_subscripions with the xaxct_seqno for this
command. This will cause it to skip this command.

"Invalid distribution command" in transaction replication

HI
I had a replication working nicely for long time. SQL 2k, SP3.
One day it stoped working with the error "Invalid distribution
command." and
last action is: insert into "ponim" ("tz_pone.
Transaction sequence number and command ID of last execution batch are
0x0000829800000ABB000100000000 and 1.
I think maybe that day I added a new column using sp_repladdcolumn.
I want to say that the same script I run for that replication I ran for
another 3 similar replication and they all still work prefectly.
Any Ideas what I can do?
Please take in consider that I have lots of data in the publication
that
couldn't be send to the subscriber becouse of the above error.
Thanks
Please enable logging as per this article:
http://support.microsoft.com/?id=312292 to get more details about the
failing command.
sp_browsereplcmds could also be used to find these details.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

"Invalid distribution command" in transaction replication

HI
I had a replication working nicely for long time. SQL 2k, SP3.
One day it stoped working with the error "Invalid distribution command." and
last action is: insert into "ponim" ("tz_pone.
Transaction sequence number and command ID of last execution batch are
0x0000829800000ABB000100000000 and 1.
I think maybe that day I added a new column using sp_repladdcolumn.
I want to say that the same script I run for that replication I ran for
another 3 similar replication and they all still work prefectly.
Any Ideas what I can do?
Please take in consider that I have lots of data in the publication that
couldn't be send to the subscriber becouse of the above error.
Thanksuse sp_browsereplcmds to identiy this command. Then apply this command
on the subcriber.
Then update msreplication_subscripions with the xaxct_seqno for this
command. This will cause it to skip this command.

Sunday, February 19, 2012

"If you want a lot of information, one has to use cursors."

A developer I'm working with just gave me a stored procedure that uses three
cursors, two of which are nested into the first. These cursors retrieve data
from tables one row at a time to build temporary tables, which are used to
build a result set.
I told him not to use cursors because they're incredibly slow compared to
joins, and make the query much harder to read and maintain. He replied,
"If you want a lot of information, one has to use cursors."
I didn't know that. To think, I'd been using just plain old joins to get
data all these years. Guess I've been just doing it wrong.
PaulTell him "Cursors are useful if you don't know SQL"
(Acknowledgments to Nigel Rivett)
David Portas
SQL Server MVP
--|||The correct quote is if one has a lot of information and one chooses to use
cursors, one will see a significant decline in performance.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23nsiUcWuFHA.664@.tk2msftngp13.phx.gbl...
>A developer I'm working with just gave me a stored procedure that uses
>three cursors, two of which are nested into the first. These cursors
>retrieve data from tables one row at a time to build temporary tables,
>which are used to build a result set.
> I told him not to use cursors because they're incredibly slow compared to
> joins, and make the query much harder to read and maintain. He replied,
> "If you want a lot of information, one has to use cursors."
> I didn't know that. To think, I'd been using just plain old joins to get
> data all these years. Guess I've been just doing it wrong.
> Paul
>|||I didn't see a smiley face and if there's sarcasm there, it's not obvious
enough for me.
If this guy really ticks you off, you're going to have to prove him wrong
but this involves a bit of work for you re-coding the SP and the client
application.
Good luck.
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23nsiUcWuFHA.664@.tk2msftngp13.phx.gbl...
>A developer I'm working with just gave me a stored procedure that uses
>three cursors, two of which are nested into the first. These cursors
>retrieve data from tables one row at a time to build temporary tables,
>which are used to build a result set.
> I told him not to use cursors because they're incredibly slow compared to
> joins, and make the query much harder to read and maintain. He replied,
> "If you want a lot of information, one has to use cursors."
> I didn't know that. To think, I'd been using just plain old joins to get
> data all these years. Guess I've been just doing it wrong.
> Paul
>|||My rule of thumb is that a cursor runs 10 times slower than a query.
The nested cursors are usually a sign that he is mimicking a tape file
system instead of writing SQL.|||> "If you want a lot of information, one has to use cursors."
You could swap out "if you want a lot of information" and replace it with
"if one doesn't understand set theory"...|||Hi
It is a good idea to make your developers test on databases that have a
reasonable amount of realistic data.
John
"PJ6" <nobody@.nowhere.net> wrote in message
news:%23nsiUcWuFHA.664@.tk2msftngp13.phx.gbl...
>A developer I'm working with just gave me a stored procedure that uses
>three cursors, two of which are nested into the first. These cursors
>retrieve data from tables one row at a time to build temporary tables,
>which are used to build a result set.
> I told him not to use cursors because they're incredibly slow compared to
> joins, and make the query much harder to read and maintain. He replied,
> "If you want a lot of information, one has to use cursors."
> I didn't know that. To think, I'd been using just plain old joins to get
> data all these years. Guess I've been just doing it wrong.
> Paul
>|||You must not know how to write a cursor. If it's done right, a cursor will
only take twice as long as a set-based query, but in some cases--usually
those involving a self-join--a cursor will actually perform better.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126729006.844457.69490@.g14g2000cwa.googlegroups.com...
> My rule of thumb is that a cursor runs 10 times slower than a query.
> The nested cursors are usually a sign that he is mimicking a tape file
> system instead of writing SQL.
>|||He wrote the book on self joins.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:Or3yv1WuFHA.3932@.TK2MSFTNGP15.phx.gbl...
> You must not know how to write a cursor. If it's done right, a cursor
> will
> only take twice as long as a set-based query, but in some cases--usually
> those involving a self-join--a cursor will actually perform better.
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1126729006.844457.69490@.g14g2000cwa.googlegroups.com...
>|||I just used a cursor a few hours ago:
open dishwasher for all the dirty dishes
while (@.@.fetch_status = 0)
begin
fetch next dish
insert into dishwasher
dish
end
And it worked. Yes, pseudo-code is good enough for most of my kitchen
appliances.
ML

Monday, March 19, 2012

"User Cancelled" Error while viewing Crystal report

"use database" not working after "create database"

"Update" in Trigger not working

Friday, March 16, 2012

"The Lab Is Not Available" message from ELearning Offline Player

Sunday, March 11, 2012

"Stored Procedure" working every 5 minute, how?

"SQL Server does not exist or access denied"

"Server has not yet been opened" - XP

Thursday, March 8, 2012

"SA" Insuffient Permission

"Right" section of BorderStyle not working in last column

Tuesday, March 6, 2012

"Pages breaks" when I add a page footer to the report

Saturday, February 25, 2012

"Multi relational" table design question

Friday, February 24, 2012

"IS NOT NULL" is not working

"IS NOT NULL" is not working

"IS NOT NULL" is not working

"Invalid distribution command" in transaction replication

"Invalid distribution command" in transaction replication

"Invalid distribution command" in transaction replication

"Invalid distribution command" in transaction replication

"Invalid distribution command" in transaction replication

Sunday, February 19, 2012

"If you want a lot of information, one has to use cursors."

sql error

Blog Archive

About Me