Boolean Parameter

Hi,

I have a Summary report and a Detail (drillthrough) report.

the summary report displays.

Summary report

Code Snippet

Adult | Male | Count

--

Yes | Yes | 50

Yes | No | 9

No | Yes | 20

No | No | 50

| 129

When the user clicks on the hightlighted count it links to the Details report displaying each of the records referenced.

The detail report uses 2 boolean parameters Adult & Male, this works perfectly for the first 4 lines displayed, however if the user clicks on the total value to display all the records, i'm unable to provide the NULL value.

Any ideas?

Hi,

This may be due to the way you pass parameters to the sub-report.

you may not get answers if you are not more specific.

How is the total item generated. Why use a NULL value instead of something like "ALL" or "%" when the user select the total.

This may be easier to catch in the sub or linked report, the sub report will easilly interpret LIKE '%' rather than some NULL value.

Please provide some more details.

Philippe

|||

Hi,

The report queries against a database field that is a boolean but can accept a NULL value.

So i configured the recieving report to accept a boolean value "True, False & also Null".

If i used LIKE as the where criteria it obviously wouldn't be as efficent.

Regards

Ian

|||

What about using a dropdown param (whether multi select or not, depending on your needs) rather than a boolean here? Provide a tinyint to your procedure, if you're using a procedure to receive the parameter values, in which one value (say, 2) represents null. Interpret the results of what you get as this parameter to build up your query -- you could interpret it as an IN() rather than as a LIKE, or as ORs if you prefer (although the ORs are probably going to be interpreted internally just like the IN).

One really good way to do this is to use ISNULL(TheField,2) in your WHERE condition...

The point is, the default parameter representation for booleans with those radio buttons (with or without null checkbox) is hokey to start with, why not just use a dropdown with labels you like to represent them and integer values underneath.

>L<

|||

Hi Lisa,

Thank you for your input, that sounds like the way that i should have done it. i'll check out the dropdown options.

i would like a rant about the SRS report and boolean parameter at this stage....however i don't have the time or anyone else the want to read it. lol

cheers everyone.

ian

|||>> would like a rant about the SRS report and boolean parameter

I have done this before and generally don't care whether anybody wants to read it when I'm in the mood to rant on this sort of topic -- although I blog the rant rather than posting to a forum in that case <g>.

In this case, I honestly think the rant would be misdirected. As I have said several times in posts on this forum (because it is NOT a rant <s>), the default parameter representation is just that: a default. You can hide this panel, present your own parameter interface, and pass params to the report. I think this is always what was intended. The default just had to be generic enough to work without any sort of business logic connection and was never expected to be "the interface that you presented in your polished application".

>L<

Boolean Parameter

Hi,

I have a Summary report and a Detail (drillthrough) report.

the summary report displays.

Summary report

Code Snippet

Adult | Male | Count

--

Yes | Yes | 50

Yes | No | 9

No | Yes | 20

No | No | 50

| 129

When the user clicks on the hightlighted count it links to the Details report displaying each of the records referenced.

The detail report uses 2 boolean parameters Adult & Male, this works perfectly for the first 4 lines displayed, however if the user clicks on the total value to display all the records, i'm unable to provide the NULL value.

Any ideas?

Hi,

This may be due to the way you pass parameters to the sub-report.

you may not get answers if you are not more specific.

How is the total item generated. Why use a NULL value instead of something like "ALL" or "%" when the user select the total.

This may be easier to catch in the sub or linked report, the sub report will easilly interpret LIKE '%' rather than some NULL value.

Please provide some more details.

Philippe

|||

Hi,

The report queries against a database field that is a boolean but can accept a NULL value.

So i configured the recieving report to accept a boolean value "True, False & also Null".

If i used LIKE as the where criteria it obviously wouldn't be as efficent.

Regards

Ian

|||

What about using a dropdown param (whether multi select or not, depending on your needs) rather than a boolean here? Provide a tinyint to your procedure, if you're using a procedure to receive the parameter values, in which one value (say, 2) represents null. Interpret the results of what you get as this parameter to build up your query -- you could interpret it as an IN() rather than as a LIKE, or as ORs if you prefer (although the ORs are probably going to be interpreted internally just like the IN).

One really good way to do this is to use ISNULL(TheField,2) in your WHERE condition...

The point is, the default parameter representation for booleans with those radio buttons (with or without null checkbox) is hokey to start with, why not just use a dropdown with labels you like to represent them and integer values underneath.

>L<

|||

Hi Lisa,

Thank you for your input, that sounds like the way that i should have done it. i'll check out the dropdown options.

i would like a rant about the SRS report and boolean parameter at this stage....however i don't have the time or anyone else the want to read it. lol

cheers everyone.

ian

|||>> would like a rant about the SRS report and boolean parameter

I have done this before and generally don't care whether anybody wants to read it when I'm in the mood to rant on this sort of topic -- although I blog the rant rather than posting to a forum in that case <g>.

In this case, I honestly think the rant would be misdirected. As I have said several times in posts on this forum (because it is NOT a rant <s>), the default parameter representation is just that: a default. You can hide this panel, present your own parameter interface, and pass params to the report. I think this is always what was intended. The default just had to be generic enough to work without any sort of business logic connection and was never expected to be "the interface that you presented in your polished application".

>L<

Boolean Parameter

Hi,

I have a Summary report and a Detail (drillthrough) report.

the summary report displays.

Summary report

Code Snippet

Adult | Male | Count

--

Yes | Yes | 50

Yes | No | 9

No | Yes | 20

No | No | 50

| 129

When the user clicks on the hightlighted count it links to the Details report displaying each of the records referenced.

The detail report uses 2 boolean parameters Adult & Male, this works perfectly for the first 4 lines displayed, however if the user clicks on the total value to display all the records, i'm unable to provide the NULL value.

Any ideas?

Hi,

This may be due to the way you pass parameters to the sub-report.

you may not get answers if you are not more specific.

How is the total item generated. Why use a NULL value instead of something like "ALL" or "%" when the user select the total.

This may be easier to catch in the sub or linked report, the sub report will easilly interpret LIKE '%' rather than some NULL value.

Please provide some more details.

Philippe

|||

Hi,

The report queries against a database field that is a boolean but can accept a NULL value.

So i configured the recieving report to accept a boolean value "True, False & also Null".

If i used LIKE as the where criteria it obviously wouldn't be as efficent.

Regards

Ian

|||

What about using a dropdown param (whether multi select or not, depending on your needs) rather than a boolean here? Provide a tinyint to your procedure, if you're using a procedure to receive the parameter values, in which one value (say, 2) represents null. Interpret the results of what you get as this parameter to build up your query -- you could interpret it as an IN() rather than as a LIKE, or as ORs if you prefer (although the ORs are probably going to be interpreted internally just like the IN).

One really good way to do this is to use ISNULL(TheField,2) in your WHERE condition...

The point is, the default parameter representation for booleans with those radio buttons (with or without null checkbox) is hokey to start with, why not just use a dropdown with labels you like to represent them and integer values underneath.

>L<

|||

Hi Lisa,

Thank you for your input, that sounds like the way that i should have done it. i'll check out the dropdown options.

i would like a rant about the SRS report and boolean parameter at this stage....however i don't have the time or anyone else the want to read it. lol

cheers everyone.

ian

|||>> would like a rant about the SRS report and boolean parameter

I have done this before and generally don't care whether anybody wants to read it when I'm in the mood to rant on this sort of topic -- although I blog the rant rather than posting to a forum in that case <g>.

In this case, I honestly think the rant would be misdirected. As I have said several times in posts on this forum (because it is NOT a rant <s>), the default parameter representation is just that: a default. You can hide this panel, present your own parameter interface, and pass params to the report. I think this is always what was intended. The default just had to be generic enough to work without any sort of business logic connection and was never expected to be "the interface that you presented in your polished application".

>L<

Bookmark in Code not working.

I have a report in reporting services that has a bookmark value asigned to each row. In the following Page_Load event for the page, I'm getting the following error on the ReportViewer1.JumpToBookmark("6") statement:

"An exception of type 'System.InvalidOperationException' occurred in Microsoft.ReportViewer.WebForms.dll but was not handled in user code

Additional information: Some parameters or credentials have not been specified"

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

If Not IsPostBack Then

Dim strReport1 As String = "/ReportDirectory/CurrentWeekSales"

ReportViewer1.ServerReport.ReportServerUrl = New System.Uri(CStr(Session.Item("ReportServer")))
ReportViewer1.ServerReport.ReportPath = strReport1
ReportViewer1.ServerReport.Timeout = 500000

Do While ReportViewer1.ServerReport.IsDrillthroughReport
ReportViewer1.PerformBack()
Loop

Dim RptParameters1 As ReportParameter() = New ReportParameter(0) {}
RptParameters1(0) = New ReportParameter("EBO_EmpKey", CStr(Session.Item("UserKey")))
ReportViewer1.ServerReport.SetParameters(RptParameters1)

ReportViewer1.ServerReport.Refresh()

ReportViewer1.JumpToBookmark("6")

End If
End Sub

What am I missing?

ThanksI too am having a similar problem. I find that JumpToBookmark() simply does not work no matter what I try. Interestingly, setting the 'action' property of a TextBox within the report to jump to the target bookmark seems to work. Unfortunately, I need to be able to do this in code without having to force the user to click on the report.

Is this a known problem, or is there something else on the viewer and/or report that needs to be set?

Thanks,

Ian.

boilerplate code/template for custom task UI?

does anyone know of any boilerplate code or templates (codesmith or other code-generation software) that can be used to produce a UI for a custom task?

Yes, I have one in my book:

Kirk Haselden
Author "SQL Server Integration Services"

Boilerplate activation SQL for high-throughput

OK, so assume I am recycling dialogs in my client code, and assume I am doing something similar to get a dialog handle in my TSQL. What should the activated stored procedure that is processing my queue look like if I am expecting thousands of messages per second? Assume also that there is a small bit of logic need to process each individual message? I am building for a high-throughput scenario and would like to get as much as possible out of each second-tier service broker server as possible before the aggregated data is moved up the chain to a master. The first tier is Express on a web server and exists primarily only as a forwarding mechanism.

It depends on what type of logic your activation procedure will be doing. If it is just taking a message off of the queue, then receive the message and log it (or whatever you need to do). It the logic is more intesive, and requires transactions your procedure code will be more complicated. Heres a link to check out activation procedures: http://articles.techrepublic.com.com/5100-9592_11-6156264.html

block comment??

Is there a block comment character that I can use in my SQL script files to
comment large sections of SQL code? Something like /* ... */.
Thanks.
-b"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:452FF1A6-BE3E-4292-9A04-D8AF07908794@.microsoft.com...
> Is there a block comment character that I can use in my SQL script files
> to
> comment large sections of SQL code? Something like /* ... */.
> Thanks.
> -b
This isn't a joke, is it?
From Books OnLine:
/*...*/ (Comment)
Indicates user-provided text. The text between the /* and */ commenting
characters is not evaluated by the server.|||You have is the right commenting characters /*...*/ there for multi-line
block commenting.
( Also in QA, you can select a large chunk of code & press Ctrl+Shft+C to
comment it & Ctrl+Shift+R to remove it. )
Anith|||That's what I thought it meant too, but when I created a SQL script using
this to comment out a section of code that is not yet fully debugged and the
n
ran the script file, I got several errors from code inside the block comment
.
The specific commands were dealing with foreign key constraints, but they
were within the block comment.
Is that any problem with nesting line comments ( '--') inside a block
comment?
-b
"Raymond D'Anjou" wrote:

> "Barry" <Barry@.discussions.microsoft.com> wrote in message
> news:452FF1A6-BE3E-4292-9A04-D8AF07908794@.microsoft.com...
> This isn't a joke, is it?
> From Books OnLine:
> /*...*/ (Comment)
> Indicates user-provided text. The text between the /* and */ commenting
> characters is not evaluated by the server.
>
>|||I can't see what would cause the problems.
Did you highlight some code before running the script?
Mixing block and line comments does not generate an error (that I know of).
Go alone on a line throws an error:
/*comments
GO
comments*/
Trying to nest block comments doesn't work either:
/*comment
/*comment2*/
comment*/
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
> That's what I thought it meant too, but when I created a SQL script using
> this to comment out a section of code that is not yet fully debugged and
> then
> ran the script file, I got several errors from code inside the block
> comment.
> The specific commands were dealing with foreign key constraints, but they
> were within the block comment.
> Is that any problem with nesting line comments ( '--') inside a block
> comment?
> -b
> "Raymond D'Anjou" wrote:
>|||Barry
If you just commented out a whole block of code, you maybe have a GO
somewhere in there. This can cause problems.
The following is from Inside SQL Server 2000:
---
GO isn't an SQL command or keyword. It's the end-of-batch signal understood
only by certain client tools. The client interprets it to mean that
everything since the last GO should be sent to the server for execution. SQL
Server never sees the GO command and has no idea what it means. With a
custom application, a batch is executed with a single SQLExecute from ODBC
(or dbsqlexec from DB-Library).
If you include the GO command in the query window, SQL Query Analyzer breaks
up your statement into the indicated batches behind the scenes. Each batch
(as marked by the GO command) is sent individually to SQL Server.
A collection of batches that are frequently executed together is sometimes
called a script. Most of the client tools provide a mechanism for loading a
script that you've saved to a text file and for executing it. In SQL Query
Analyzer, you can use the File/Open command to load a script. From the
command-line OSQL or ISQL programs, we can specify the /i flag followed by a
filename to indicate that the SQL Server batches to execute should come from
the specified file. Alternatively, if we're using OSQL or ISQL
interactively, we can read in a file containing one or more SQL statement by
typing :r followed by the filename. (See SQL Server Books Online for details
about using OSQL and ISQL.)
The fact that the client tool and not SQL Server processes GO can lead to
some unexpected behavior. Suppose you have a script containing several
batches. During testing, you want to comment out a couple of the batches to
ignore them for the time being. Your commented script might look something
like this:
SELECT * FROM authors
/*
GO
SELECT * FROM sales
GO
SELECT * FROM publishers
GO
*/
SELECT * FROM titles
GO
The intention here was to comment out the SELECT from the sales and
publishers tables and to run the SELECT from authors and titles as a single
batch. However, if you run this script from SQL Query Analyzer, you'll get
exactly the opposite behavior! That is, you'll see the data from the sales
and publishers tables but not from authors or titles. If you look at this
script from the perspective of the client tool, the behavior makes sense.
The tool doesn't try to interpret any of your SQL statements; it just breaks
the statements into batches to be sent to SQL Server. A batch is marked by a
GO command at the beginning of a line.
So the above script contains four batches. The first batch (everything
before the first GO) is:
SELECT * FROM authors
/*
SQL Server generates an error message because there's an open comment with
no corresponding close comment.
The second and third batches are:
SELECT * FROM sales
and
SELECT * FROM publishers
Both of these batches are perfectly legal, and SQL Server can process them
and return results.
The fourth batch is:
*/
SELECT * FROM titles
SQL Server also generates an error for this last one because it has a close
comment without an open comment marker, and no data is returned.
If you want to comment out statements within a script that can contain the
end-of-batch GO command, you should use the alternative comment marker-the
double dash-in front of every GO. Alternatively, you can just use the double
dash in front of every line you want to comment out. Your script would then
look like this:
SELECT * FROM authors
-- GO
-- SELECT * FROM sales
-- GO
-- SELECT * FROM publishers
-- GO
SELECT * FROM titles
GO
SQL Query Analyzer makes it easy to comment out a group of lines as in the
code above. You can highlight all the lines to be commented out, and from
the Edit menu, choose Advanced/Comment Out. The keystroke combination to
perform the same action is Ctrl-Shift-C. With this revised script, the
client tool won't recognize the GO as the end-of-batch marker because it's
not the first thing on a line. The client will consider this script to be
one single batch and send it to SQL Server as such.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
> That's what I thought it meant too, but when I created a SQL script using
> this to comment out a section of code that is not yet fully debugged and
> then
> ran the script file, I got several errors from code inside the block
> comment.
> The specific commands were dealing with foreign key constraints, but they
> were within the block comment.
> Is that any problem with nesting line comments ( '--') inside a block
> comment?
> -b
> "Raymond D'Anjou" wrote:
>
>|||I've verified that there are no improperly commented GO statements. When I
rerun the script the error comes after several other similar calls from
inside the comment block (1034 lines into the block of 1137 lines). Is it
possible that these comment blocks are too long?
"Kalen Delaney" wrote:

> Barry
> If you just commented out a whole block of code, you maybe have a GO
> somewhere in there. This can cause problems.
> The following is from Inside SQL Server 2000:
> ---
> GO isn't an SQL command or keyword. It's the end-of-batch signal understoo
d
> only by certain client tools. The client interprets it to mean that
> everything since the last GO should be sent to the server for execution. S
QL
> Server never sees the GO command and has no idea what it means. With a
> custom application, a batch is executed with a single SQLExecute from ODBC
> (or dbsqlexec from DB-Library).
> If you include the GO command in the query window, SQL Query Analyzer brea
ks
> up your statement into the indicated batches behind the scenes. Each batch
> (as marked by the GO command) is sent individually to SQL Server.
> A collection of batches that are frequently executed together is sometimes
> called a script. Most of the client tools provide a mechanism for loading
a
> script that you've saved to a text file and for executing it. In SQL Query
> Analyzer, you can use the File/Open command to load a script. From the
> command-line OSQL or ISQL programs, we can specify the /i flag followed by
a
> filename to indicate that the SQL Server batches to execute should come fr
om
> the specified file. Alternatively, if we're using OSQL or ISQL
> interactively, we can read in a file containing one or more SQL statement
by
> typing :r followed by the filename. (See SQL Server Books Online for detai
ls
> about using OSQL and ISQL.)
> The fact that the client tool and not SQL Server processes GO can lead to
> some unexpected behavior. Suppose you have a script containing several
> batches. During testing, you want to comment out a couple of the batches t
o
> ignore them for the time being. Your commented script might look something
> like this:
> SELECT * FROM authors
> /*
> GO
> SELECT * FROM sales
> GO
> SELECT * FROM publishers
> GO
> */
> SELECT * FROM titles
> GO
> The intention here was to comment out the SELECT from the sales and
> publishers tables and to run the SELECT from authors and titles as a singl
e
> batch. However, if you run this script from SQL Query Analyzer, you'll get
> exactly the opposite behavior! That is, you'll see the data from the sales
> and publishers tables but not from authors or titles. If you look at this
> script from the perspective of the client tool, the behavior makes sense.
> The tool doesn't try to interpret any of your SQL statements; it just brea
ks
> the statements into batches to be sent to SQL Server. A batch is marked by
a
> GO command at the beginning of a line.
> So the above script contains four batches. The first batch (everything
> before the first GO) is:
> SELECT * FROM authors
> /*
> SQL Server generates an error message because there's an open comment with
> no corresponding close comment.
> The second and third batches are:
> SELECT * FROM sales
> and
> SELECT * FROM publishers
> Both of these batches are perfectly legal, and SQL Server can process them
> and return results.
> The fourth batch is:
> */
> SELECT * FROM titles
> SQL Server also generates an error for this last one because it has a clos
e
> comment without an open comment marker, and no data is returned.
> If you want to comment out statements within a script that can contain the
> end-of-batch GO command, you should use the alternative comment marker-the
> double dash-in front of every GO. Alternatively, you can just use the doub
le
> dash in front of every line you want to comment out. Your script would the
n
> look like this:
> SELECT * FROM authors
> -- GO
> -- SELECT * FROM sales
> -- GO
> -- SELECT * FROM publishers
> -- GO
> SELECT * FROM titles
> GO
> SQL Query Analyzer makes it easy to comment out a group of lines as in the
> code above. You can highlight all the lines to be commented out, and from
> the Edit menu, choose Advanced/Comment Out. The keystroke combination to
> perform the same action is Ctrl-Shift-C. With this revised script, the
> client tool won't recognize the GO as the end-of-batch marker because it's
> not the first thing on a line. The client will consider this script to be
> one single batch and send it to SQL Server as such.
>
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Barry" <Barry@.discussions.microsoft.com> wrote in message
> news:045BF8F8-3EDE-44C9-8F69-AEBAB5FB6936@.microsoft.com...
>
>|||OK - Here is what I've narrowed it down to.
If I comment out (using block comments) anything in the script except
another '*' character, it seems to work. However when the commented section
includes a '*' it causes the code to uncomment.
For example the following generated an error
/*
-- if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_PolicySectionMap_policyFileID]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
*/
but changing the '*' to 'id' did not.
I decided to use QA to comment the block with line comments instead.
-b
"Barry" wrote:
> I've verified that there are no improperly commented GO statements. When
I
> rerun the script the error comes after several other similar calls from
> inside the comment block (1034 lines into the block of 1137 lines). Is it
> possible that these comment blocks are too long?
> "Kalen Delaney" wrote:
>|||Barry
This seems very strange.
This batch works for me:
USE pubs
select * from authors
/*
select * from titles
*/
select * from publishers
What exact version are you using? Are you getting the error using QA?
Thanks
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:846D3E3A-34D8-47EF-A22D-CAC114D7CCB8@.microsoft.com...
> OK - Here is what I've narrowed it down to.
> If I comment out (using block comments) anything in the script except
> another '*' character, it seems to work. However when the commented
> section
> includes a '*' it causes the code to uncomment.
> For example the following generated an error
> /*
> -- if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FK_PolicySectionMap_policyFileID]') and
> OBJECTPROPERTY(id,
> N'IsForeignKey') = 1)
> */
> but changing the '*' to 'id' did not.
> I decided to use QA to comment the block with line comments instead.
> -b
> "Barry" wrote:
>
>|||Did you copy this SQL into notepad (or another 3rd party editor) and then
paste back into Enterprise Manager or Query Analyzer? There is a very rare
(but insideous) bug at least in SQL Server 2000 where a line ending in a
CRTL byte but no LF byte (or perhaps vice versa) will look normal when
viewed in QA or EM, but when executed, the SQL interpreter will think there
is no new line.
For example:
Here is what it looks like when viewed in Enterprise Manager:
-- Here is a comment
print 'Hello World'
Here is what gets executed:
-- Here is a comment print 'Hello World'
"Barry" <Barry@.discussions.microsoft.com> wrote in message
news:145BDC63-89E3-4979-9377-29199B3A10EB@.microsoft.com...
> I've verified that there are no improperly commented GO statements. When
> I
> rerun the script the error comes after several other similar calls from
> inside the comment block (1034 lines into the block of 1137 lines). Is it
> possible that these comment blocks are too long?
> "Kalen Delaney" wrote:
>

BLOB Merge Replication

Hi,

I have setup a Merge Replication between a SQL Server 2005 and a SQL Server Mobile using native C++ code in Windows CE 5. Replication of normal data types (nvarchar, ints etc) works great, but when I try to replicate a blob about 1.5 MB the performance is awful. The download phase seems to be quick while the applytime (printed in debugger) when the database on my CE device is updated is about 3 minutes for 1.5MB binary data which is unacceptable since the database gets locked up during the applytime. The BLOB column is defined as image data type which shall hold up to 1GB data. I have tried to disable compression during replication, but it doesnt matter. Does anyone have some experience of using merge replication with blobs in SQL Server Mobile?

My Idea was to use merge replication as a way to transfer CAB-files for installation on the Windows CE 5 device by storing them as BLOB:s, but the bad performance may make it impossible.

Ideas? Anyone?

Best regards

Johan Johansson

I'm appending the debug output from the merge replication of the table containing the 1.5 MB BLOB.

SQLCECA30: Client Agent Log Start --
SQLCECA30: 08/23/2006-16:37:41 CSSCEMerge::SingleRun id=0 hr=0 bread=8027251 bwritten=273 c_urows=0 c_drows=0 s_urows=5 s_drows=0 md_n=0 md_c=0 md_r=0 app=mpsim.exe
SQLCECA30: 08/23/2006-16:37:41 CSSCEMerge::Run totalsynctime=222 uploadtime=371 applytime=217185 id=0 publisher='se-cwo-ddb-1' publication='Software' initialsync=1 bread=8027251 bwritten=273 c_urows=0 c_drows=0 s_urows=5 s_drows=0 loops=1 app=mpsim.exe

|||

About the only thing you can do is to factor out the BLOB itself into a related table. For example, let's say right now you have a table called "Orders" which has 10 columns, one of which is a customer signature (image column in SQL Mobile). Every time any column in the orders table is updated, the whole row is replicated, including the image. It would be more efficient to have an Orders table and a separate OrderSignatures table, related by a foreign key.

Darren

|||

Yes, thank you for the suggestion.

I must say that I'm dissapointed in the performance. I mean 1.5 MB binary data is not alot in these days and it is almost impossible to handle for SQL Server Mobile in a merge replication. Can I expect the same bad performance when I replicate alot of rows (lets say 3000) with more ordinary data (say a few columns with ints and varchar(50)) or is this performance problem only related to dealing with BLOBs?

Johan

|||

Johan,

I was thinking about your post some more and it occurs to me that you are trying to solve the problem of automatically downloading CAB files

from the server to the CE5 device. I also frequently have this requirement when a mobile app must be automatically updating. There are some

great samples (they are not native code, but you can get the idea of how to do this) of auto-updating by downloading CABs to the device at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/AutoUpdater.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/AUTD_Functionality_NETCF_WebServices.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetcomp/html/DeploymentPatterns.asp

Regards,

Darren

|||

Thank you Darren,

I will have a look at the documents before I decide how I'm going to implement this.

Best regards

Johan

Blanks in ColumnNames causes ODBC-Error

I use Blanks in ColumnNames ( I know that this isnt very good, but a
lot of code and querys had to be changed if I would remove all blanks
in all columnnames).
When I link this tables with ODBC in my ACC97 - project, some of the
tables causes an ODBC-Error.

Are there possibilities to workaround this error?

Thanks, Andreas Lauffer, easySoft. GmbH, GermanyOn Tue, 16 Sep 2003 10:38:52 +0100, Andreas Lauffer
<a.lauffer@.easysoft.de> wrote:

>I use Blanks in ColumnNames ( I know that this isnt very good, but a
>lot of code and querys had to be changed if I would remove all blanks
>in all columnnames).
>When I link this tables with ODBC in my ACC97 - project, some of the
>tables causes an ODBC-Error.
>Are there possibilities to workaround this error?
>Thanks, Andreas Lauffer, easySoft. GmbH, Germany
I think enclose the column name in quotes like

table."col umn"

blank cells cause trouble on join

I have a few tables that I am joining together and one of the joins is on a
field that almost always has a code in it. Sometimes it is a blank (not a
null) if the clerk didn't type anything into it. The table that I am joining
to this one, has many cells in the join field thhat are blank and those rows
are used for commenting the other records in the table (I know it is stupid
but I didn't design it).
If I do a regular join whenever it comes upon one of these blanks it joins
to all of the comment records in the 2nd table.
What would be the easiest way to get around this problem?
Below is the statement as far as I got befor the last join messed me up.
cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
blank in all rows where they are being used as comments.
SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
codentry
from osmccsdb.cdocket
left outer join osmccsdb.cparty
on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
left outer join osmccsdb.ccharge
on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
left outer join osmccsdb.ccodes
on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
order by osmccsdb.cdocket.cdevtdt descLinda,
A LEFT JOIN will return all of the records from the table on left side of
the JOIN keyword and any matching records on the right. I don't have any
sample data to work with but you might look at the NULLIF function and see
if it might help you in your situation.
HTH
Jerry
"Linda Ibarra" <LindaIbarra@.discussions.microsoft.com> wrote in message
news:8953B911-7B98-4A20-BD74-C9FFA335B9B2@.microsoft.com...
>I have a few tables that I am joining together and one of the joins is on a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am
> joining
> to this one, has many cells in the join field thhat are blank and those
> rows
> are used for commenting the other records in the table (I know it is
> stupid
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||I see this with some tools and it is kind of a pain. First thing I would
note is that it is usually a bad idea to have user inputted values being
what you are joining on (but I know you said you didn't design it)
I would probaby just add a condition to your join that says something like:
AND ccodes.codevent <> '' AND the other column too
This will eliminate them from the join. Or if you want the '' rows from one
side, but not the other, then change the '' in the join:
case when ccodes.codevent = '' then 'NOT POSSIBLE' else ccodes.codevent end
Or you could use NULL instead of 'NOT POSSIBLE'
It might not be great for performance, so you might have to do some
trickiness if you have scads of data, but that is just the price you pay :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Linda Ibarra" <LindaIbarra@.discussions.microsoft.com> wrote in message
news:8953B911-7B98-4A20-BD74-C9FFA335B9B2@.microsoft.com...
>I have a few tables that I am joining together and one of the joins is on a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am
> joining
> to this one, has many cells in the join field thhat are blank and those
> rows
> are used for commenting the other records in the table (I know it is
> stupid
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||I would prefer updating those columns with some value,say string and
eliminate that in the where clause. and make it default so that problem wil
l
not come again
--
Regards
R.D
--Knowledge gets doubled when shared
"Linda Ibarra" wrote:

> I have a few tables that I am joining together and one of the joins is on
a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am joini
ng
> to this one, has many cells in the join field thhat are blank and those ro
ws
> are used for commenting the other records in the table (I know it is stupi
d
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||You are a freaking genius!!! I am not sure that I will be allowed to do tha
t
but if I can what a relief! These are court records and I am not sure how
weird they will be about me changing data.
"R.D" wrote:
> I would prefer updating those columns with some value,say string and
> eliminate that in the where clause. and make it default so that problem w
ill
> not come again
> --
> Regards
> R.D
> --Knowledge gets doubled when shared
>
> "Linda Ibarra" wrote:
>|||It was also pointed out to me that if I just created a view of this data
where the records with blank fields were not included and used that for the
join, that that would also eliminate this problem.
"Linda Ibarra" wrote:

> I have a few tables that I am joining together and one of the joins is on
a
> field that almost always has a code in it. Sometimes it is a blank (not a
> null) if the clerk didn't type anything into it. The table that I am joini
ng
> to this one, has many cells in the join field thhat are blank and those ro
ws
> are used for commenting the other records in the table (I know it is stupi
d
> but I didn't design it).
> If I do a regular join whenever it comes upon one of these blanks it joins
> to all of the comment records in the 2nd table.
> What would be the easiest way to get around this problem?
> Below is the statement as far as I got befor the last join messed me up.
> cdockets.cdevnttyp has the occasional blank in it and ccodes.codevent is
> blank in all rows where they are being used as comments.
> SELECT cdcaseid as caseID, cdevtno as eventNum, replace()cdevtdt as
> eventDate, cdevtcgpy as ChargedParty, cdcasetyp as caseType,
> cdevtjm as Judge, cdevtattny as Attorney,cpcaseid, cpcasetyp,
> cpartynum,cpname,cpattny, cgcur9, cgcur3, codeshort, codelong,
> codentry
> from osmccsdb.cdocket
> left outer join osmccsdb.cparty
> on osmccsdb.cparty.cpcaseid=osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccharge
> on osmccsdb.ccharge.cgcaseID = osmccsdb.cdocket.cdcaseid
> left outer join osmccsdb.ccodes
> on osmccsdb.cdocket.cdevttyp = osmccsdb.ccodes.codentry
> order by osmccsdb.cdocket.cdevtdt desc|||yup, you can always have a view. yet what if you want to use data in another
sproc or function or in another view. To solve this permanently have
amechanism where either you dont allow such values(?) or such blanks are
automatically replace with something like 'No Data'
--
Regards
R.D
--Knowledge gets doubled when shared
"Linda Ibarra" wrote:
> It was also pointed out to me that if I just created a view of this data
> where the records with blank fields were not included and used that for th
e
> join, that that would also eliminate this problem.
> "Linda Ibarra" wrote:
>

bizarre query problem

Below is part of a stored proc.
This code returns an error ONLY if Field.OrderId is included in the query.
It runs ok if the OrderID is not included.
The error recieved is:
Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Running the code without field.OrderID returns..
(0 row(s) affected)
(1 row(s) affected) << ALSO: What does this represent ?
(297 row(s) affected)
CREATE TABLE [OrderRequest_Equipment] (
[OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
[OrderID] [int] NULL ,
[Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MoveEarliest] [datetime] NULL ,
[MoveLatest] [datetime] NULL ,
[UnLoadEarliest] [datetime] NULL ,
[UnLoadLatest] [datetime] NULL ,
[EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MoveType] [int] NULL ,
[OffRentID] [int] NULL ,
[Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
DEFAULT (0),
[FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
DEFAULT (0),
[RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
(0),
[RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
(0),
[RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
(0),
[Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[trucking] [smallmoney] NULL ,
[fuel] [smallmoney] NULL ,
[JobNumber] [int] NULL ,
[UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FinishedAsOf] [smalldatetime] NULL ,
CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
(
[OR_EQ_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
----
--
CREATE TABLE [DCCPendingMoves] (
[jobNumber] [int] NOT NULL
) ON [PRIMARY]
GO
THIS IS THE QUERY CAUSING THE PROBLEM...
delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
OrderRequest_Equipment Table
set IDENTITY_INSERT OrderRequest_Equipment on
insert into OrderRequest_Equipment
(OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
FinishedAsOf)
select
OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
FinishedAsOf
from bobmlt.dcc.dbo.DCCPendingMoves_ore
set IDENTITY_INSERT OrderRequest_Equipment off
Thanks in advance for any help on this...
Bob McClellan.> (1 row(s) affected) << ALSO: What does this represent ?
This is a clue. You probably have an INSERT trigger on the table that is
not written correctly to handle multi-row inserts.
Hope this helps.
Dan Guzman
SQL Server MVP
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:emOh9lxBGHA.740@.TK2MSFTNGP12.phx.gbl...
> Below is part of a stored proc.
> This code returns an error ONLY if Field.OrderId is included in the query.
> It runs ok if the OrderID is not included.
> The error recieved is:
> Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.
> Running the code without field.OrderID returns..
> (0 row(s) affected)
>
> (1 row(s) affected) << ALSO: What does this represent ?
>
> (297 row(s) affected)
>
> CREATE TABLE [OrderRequest_Equipment] (
> [OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
> [OrderID] [int] NULL ,
> [Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveEarliest] [datetime] NULL ,
> [MoveLatest] [datetime] NULL ,
> [UnLoadEarliest] [datetime] NULL ,
> [UnLoadLatest] [datetime] NULL ,
> [EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveType] [int] NULL ,
> [OffRentID] [int] NULL ,
> [Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
> DEFAULT (0),
> [FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
> DEFAULT (0),
> [RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
> (0),
> [RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
> (0),
> [RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
> (0),
> [Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [trucking] [smallmoney] NULL ,
> [fuel] [smallmoney] NULL ,
> [JobNumber] [int] NULL ,
> [UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FinishedAsOf] [smalldatetime] NULL ,
> CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
> (
> [OR_EQ_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ----
--
> CREATE TABLE [DCCPendingMoves] (
> [jobNumber] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
> THIS IS THE QUERY CAUSING THE PROBLEM...
> delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
> OrderRequest_Equipment Table
> set IDENTITY_INSERT OrderRequest_Equipment on
> insert into OrderRequest_Equipment
> (OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf)
> select
> OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf
> from bobmlt.dcc.dbo.DCCPendingMoves_ore
> set IDENTITY_INSERT OrderRequest_Equipment off
>
> Thanks in advance for any help on this...
> Bob McClellan.
>|||Seems that you have a trigger defined on the table whch isn=B4t able to
process multiple rows affected.
Triggers are fired on a statement basis NOT on a row basis...
HTH, jens Suessmeyer.|||Awesome...
Thanks guys...
"John 3:16" <bobmcc@.tricoequipment.com> wrote in message
news:emOh9lxBGHA.740@.TK2MSFTNGP12.phx.gbl...
> Below is part of a stored proc.
> This code returns an error ONLY if Field.OrderId is included in the query.
> It runs ok if the OrderID is not included.
> The error recieved is:
> Server: Msg 512, Level 16, State 1, Procedure Jobs_TrackInserts, Line 18
> Subquery returned more than 1 value. This is not permitted when the
> subquery follows =, !=, <, <= , >, >= or when the subquery is used as an
> expression.
> The statement has been terminated.
> Running the code without field.OrderID returns..
> (0 row(s) affected)
>
> (1 row(s) affected) << ALSO: What does this represent ?
>
> (297 row(s) affected)
>
> CREATE TABLE [OrderRequest_Equipment] (
> [OR_EQ_id] [int] IDENTITY (1, 1) NOT NULL ,
> [OrderID] [int] NULL ,
> [Class] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Preference] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Attachment] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [UNIT] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveEarliest] [datetime] NULL ,
> [MoveLatest] [datetime] NULL ,
> [UnLoadEarliest] [datetime] NULL ,
> [UnLoadLatest] [datetime] NULL ,
> [EditedBy] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [MoveType] [int] NULL ,
> [OffRentID] [int] NULL ,
> [Action] [bit] NOT NULL CONSTRAINT [DF_OrderRequest_Equipment_Action]
> DEFAULT (0),
> [FromSiteID] [int] NULL CONSTRAINT [DF_OrderRequest_Equipment_FromSiteID]
> DEFAULT (0),
> [RD] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RD] DEFAULT
> (0),
> [RW] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RW] DEFAULT
> (0),
> [RM] [smallmoney] NULL CONSTRAINT [DF_OrderRequest_Equipment_RM] DEFAULT
> (0),
> [Dur] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [trucking] [smallmoney] NULL ,
> [fuel] [smallmoney] NULL ,
> [JobNumber] [int] NULL ,
> [UnitNotes] [nchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FinishedAsOf] [smalldatetime] NULL ,
> CONSTRAINT [PK_OrderRequest_ClassesNeeded] PRIMARY KEY NONCLUSTERED
> (
> [OR_EQ_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> ----
--
> CREATE TABLE [DCCPendingMoves] (
> [jobNumber] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
>
> THIS IS THE QUERY CAUSING THE PROBLEM...
> delete from bobmlt.dcc.dbo.OrderRequest_Equipment --Purge Jobs
> OrderRequest_Equipment Table
> set IDENTITY_INSERT OrderRequest_Equipment on
> insert into OrderRequest_Equipment
> (OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf)
> select
> OrderID, OR_EQ_id, Class, Preference, Attachment, UNIT, MoveEarliest,
> MoveLatest, UnLoadEarliest, UnLoadLatest, EditedBy, MoveType, OffRentID,
> Action, FromSiteID, RD, RW, RM, Dur, trucking, fuel, JobNumber, UnitNotes,
> FinishedAsOf
> from bobmlt.dcc.dbo.DCCPendingMoves_ore
> set IDENTITY_INSERT OrderRequest_Equipment off
>
> Thanks in advance for any help on this...
> Bob McClellan.
>

Bitmask fields in sysjobschedules

Does anybody have any code to show the the bitmask fields
in sysjobschedules as actual days of the week.
The end result i require is
name x
Freq_type 8
Freq_interval 129
to be converted into 6 table entries
NAME Day
x Mon
x Tues
x Wed
x Thur
x Fri
x Sat
etc
if the scheduled time could be included it would be most
helpful
Thanks
MatHere is an article I wrote about using an INT column to store different
statuses. This article should help you:
http://www.databasejournal.com/feat...cle.php/3359321
Here is some that might also be closer to your needs:
SELECT NAME,FREQ_TYPE, FREQ_INTERVAL,
CASE WHEN (FREQ_INTERVAL & 1) = 1 THEN 'SUN' end SUN,
CASE WHEN (FREQ_INTERVAL & 2) = 2 THEN 'MON' end MON,
CASE WHEN (FREQ_INTERVAL & 4) = 4 THEN 'TUE' end TUE,
CASE WHEN (FREQ_INTERVAL & 8) = 8 THEN 'WED' end WED,
CASE WHEN (FREQ_INTERVAL & 16) = 16 THEN 'THU' end THU,
CASE WHEN (FREQ_INTERVAL & 32) = 32 THEN 'FRI' end FRI,
CASE WHEN (FREQ_INTERVAL & 64) = 64 THEN 'SAT' end SAT FROM SYSJOBSCHEDULES
WHERE FREQ_TYPE=8
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:c4cf01c489d3$01dc8330$a601280a@.phx.gbl...
> Does anybody have any code to show the the bitmask fields
> in sysjobschedules as actual days of the week.
> The end result i require is
> name x
> Freq_type 8
> Freq_interval 129
> to be converted into 6 table entries
> NAME Day
> x Mon
> x Tues
> x Wed
> x Thur
> x Fri
> x Sat
> etc
> if the scheduled time could be included it would be most
> helpful
> Thanks
> Mat

Bitmask fields in sysjobschedules

Does anybody have any code to show the the bitmask fields
in sysjobschedules as actual days of the week.
The end result i require is
name x
Freq_type 8
Freq_interval 129
to be converted into 6 table entries
NAME Day
x Mon
x Tues
x Wed
x Thur
x Fri
x Sat
etc
if the scheduled time could be included it would be most
helpful
Thanks
MatHere is an article I wrote about using an INT column to store different
statuses. This article should help you:
http://www.databasejournal.com/features/mssql/article.php/3359321
Here is some that might also be closer to your needs:
SELECT NAME,FREQ_TYPE, FREQ_INTERVAL,
CASE WHEN (FREQ_INTERVAL & 1) = 1 THEN 'SUN' end SUN,
CASE WHEN (FREQ_INTERVAL & 2) = 2 THEN 'MON' end MON,
CASE WHEN (FREQ_INTERVAL & 4) = 4 THEN 'TUE' end TUE,
CASE WHEN (FREQ_INTERVAL & 8) = 8 THEN 'WED' end WED,
CASE WHEN (FREQ_INTERVAL & 16) = 16 THEN 'THU' end THU,
CASE WHEN (FREQ_INTERVAL & 32) = 32 THEN 'FRI' end FRI,
CASE WHEN (FREQ_INTERVAL & 64) = 64 THEN 'SAT' end SAT FROM SYSJOBSCHEDULES
WHERE FREQ_TYPE=8
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:c4cf01c489d3$01dc8330$a601280a@.phx.gbl...
> Does anybody have any code to show the the bitmask fields
> in sysjobschedules as actual days of the week.
> The end result i require is
> name x
> Freq_type 8
> Freq_interval 129
> to be converted into 6 table entries
> NAME Day
> x Mon
> x Tues
> x Wed
> x Thur
> x Fri
> x Sat
> etc
> if the scheduled time could be included it would be most
> helpful
> Thanks
> Mat

Bitmask fields in sysjobschedules

Does anybody have any code to show the the bitmask fields
in sysjobschedules as actual days of the week.
The end result i require is
name x
Freq_type 8
Freq_interval 129
to be converted into 6 table entries
NAME Day
x Mon
x Tues
x Wed
x Thur
x Fri
x Sat
etc
if the scheduled time could be included it would be most
helpful
Thanks
Mat
Here is an article I wrote about using an INT column to store different
statuses. This article should help you:
http://www.databasejournal.com/featu...le.php/3359321
Here is some that might also be closer to your needs:
SELECT NAME,FREQ_TYPE, FREQ_INTERVAL,
CASE WHEN (FREQ_INTERVAL & 1) = 1 THEN 'SUN' end SUN,
CASE WHEN (FREQ_INTERVAL & 2) = 2 THEN 'MON' end MON,
CASE WHEN (FREQ_INTERVAL & 4) = 4 THEN 'TUE' end TUE,
CASE WHEN (FREQ_INTERVAL & 8) = 8 THEN 'WED' end WED,
CASE WHEN (FREQ_INTERVAL & 16) = 16 THEN 'THU' end THU,
CASE WHEN (FREQ_INTERVAL & 32) = 32 THEN 'FRI' end FRI,
CASE WHEN (FREQ_INTERVAL & 64) = 64 THEN 'SAT' end SAT FROM SYSJOBSCHEDULES
WHERE FREQ_TYPE=8
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:c4cf01c489d3$01dc8330$a601280a@.phx.gbl...
> Does anybody have any code to show the the bitmask fields
> in sysjobschedules as actual days of the week.
> The end result i require is
> name x
> Freq_type 8
> Freq_interval 129
> to be converted into 6 table entries
> NAME Day
> x Mon
> x Tues
> x Wed
> x Thur
> x Fri
> x Sat
> etc
> if the scheduled time could be included it would be most
> helpful
> Thanks
> Mat

Bit field true/false

When I code my SQL statement and want to check equal condition on a bit
field, should I use True/False or 1/0?
David1/0
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||I would use the numeric one for many pratical reasons (Converting from
client Applications, Case Expressions, the handle of the numeric type, etc.)
Just my practical issues,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"David C" <dlchase@.lifetimeinc.com> schrieb im Newsbeitrag
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Before CELKO come in and get you a severe tongue-lashing about using bits
and about using the word 'fields' instead of 'columns'...
There is no boolean data type in SQL server.
So use 1 or 0 to check equality.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Y/N ?
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Maybe you should use CHAR(1) with a check constraint IN ('T', 'F').
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Not withstanding Don Celko and his tilting against Bit data types and the wo
rds
"field" and "record", with SQL Server I'll either use the Bit datatype or a
TinyInt with a Check constraint of In(0,1). I'll use Char(1) when the value
means something other than a boolean concept (e.g. true/false, on/off, yes/n
o)
like a Gender column with a check constraint of In('M', 'F').
Thomas
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit fi
eld,
> should I use True/False or 1/0?
> David
>|||Agree w/all above, but would add that Bit fields *CANNOT be indexed, and
since 1 to 8 bit fields will t ake up a full byte of storage anyway, I feel
it's generally better to use tinyints, with COnstraint In (0,1) as Thomas
suggested.
* The restriction on not being able to use a bit column in an index, may not
be relevant, if you never use the bit column in a order by, or Where clause,
or anywhere else where it might affect which rows are output or the order in
which they are output, then it doesn't matter... Also, even if you are using
such a query, if the values are close to evenly (50-50) distributed and not
skewed a lot (say, 95% true, 5% false) the query generally won't use an inde
x
anyway.
"David C" wrote:

> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>
>|||> Agree w/all above, but would add that Bit fields *CANNOT be indexed
This is not true. It is only Enterprise Manager which prevents you from
doing so, but it is certainly valid (and valid doesn't necessarily mean
useful). For more info, see http://www.aspfaq.com/2530|||R U sure? in SQL 2000, at least, the BOL says they can't...
bit data type, described...
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
Microsoft? SQL Server? optimizes the storage used for bit columns. If the
re
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on.
... etc...
I'm checking out the reference you posted...
"Aaron [SQL Server MVP]" wrote:

> This is not true. It is only Enterprise Manager which prevents you from
> doing so, but it is certainly valid (and valid doesn't necessarily mean
> useful). For more info, see http://www.aspfaq.com/2530
>
>

bit column behavior MSSQL7 and MSSQL2000

I was using MSSQL7 for a long period.
I upsized to MSSQL7 from Access some years ago. Without any particular reason when writing code in stored procedures, when I wanred to select some records having a bit column to true I used the syntax bitcolumn=-1 (and not bitcolumn=1). This behavior was used in Access. Everything worked fine. Then I moved to MSSQL2000 and by restoring the MSSQL7 database I had no problem.
However, in order to use some features of MSSQL2000 I had to run the sp
sp_dbcmptlevel <database>, 80
After that the condition bitcolumn=-1 didn't work.
Can anyone verify this behavior, since I have to make dozens of changes in my stored procedures and triggers?

Regards,
Manolis PerrakisBit datatype stored only value 1, 0, or Null.
Because you cannot use aggregate functions on bit values, sometimes it is preferable to use the tinyint data type instead.|||sp_dbcmptlevel <database>, 80
the sp set the compatibility of the database to 80 (sql server 2k). now the database is on 2k version, where in sql server 2k version the value of bit datatype is 1, 0, or null. so change the syntax bitcolumn=-1 into bitcolumn=1 to refer to "yes"