Raster Catalog: running

Showing posts with label running. Show all posts

Friday, March 30, 2012

Reading a record without placing a lock

Dear All,
I have one server application running which continously reading and
updating a DB.
While there is a Reporting tool which generating reports.
The reports can fail, but the server application cannot. So, I need to
run a query in the Reporting tool without placing a lock on the DB
(totally transaprent to the server).
Currently this is what I am doing.
SELECT * FROM Data WITH (NOLOCK);
Question:
1. Am I doing it correctly?
2. Is there a better way of doing? Example: setting the LOCK MODE
(instead of specifying NOLOCK on every command)
Thanks in advance.<ckkwan@.my-deja.com> wrote in message
news:e9061fce-a45f-4866-9f90-9a3e0043c5fc@.s33g2000pri.googlegroups.com...
> Dear All,
> I have one server application running which continously reading and
> updating a DB.
> While there is a Reporting tool which generating reports.
> The reports can fail, but the server application cannot. So, I need to
> run a query in the Reporting tool without placing a lock on the DB
> (totally transaprent to the server).
> Currently this is what I am doing.
> SELECT * FROM Data WITH (NOLOCK);
> Question:
> 1. Am I doing it correctly?
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
> Thanks in advance.
Hi
You can set the transaction isolation level to read uncommitted for the
session, but then you are potentially going to have dirty reads. Other ways
to do this would be to offload the reporting database either by using log
shipping, replication, mirroring or a snapshot.
John|||Hi
> 1. Am I doing it correctly?
No. What if some user inserts/deletes the row while you are reading. You
are about to get an inconsistent data. For example
you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
so while you read page A another connection inserst the value let me say
50, but you have already read data from the page A , so it moves the all
data to a new created page so now that data looks like a)10,40 ,50,
b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
60(duplicate) from page D as well.
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
at SNAPSHOT ISOLATION LEVEL in the BOL
<ckkwan@.my-deja.com> wrote in message
news:e9061fce-a45f-4866-9f90-9a3e0043c5fc@.s33g2000pri.googlegroups.com...
> Dear All,
> I have one server application running which continously reading and
> updating a DB.
> While there is a Reporting tool which generating reports.
> The reports can fail, but the server application cannot. So, I need to
> run a query in the Reporting tool without placing a lock on the DB
> (totally transaprent to the server).
> Currently this is what I am doing.
> SELECT * FROM Data WITH (NOLOCK);
> Question:
> 1. Am I doing it correctly?
> 2. Is there a better way of doing? Example: setting the LOCK MODE
> (instead of specifying NOLOCK on every command)
> Thanks in advance.|||Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24=A0pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row =A0while =A0you are reading.= You
> are about to get an inconsistent data. For example
> =A0you have tree pages with data =A0like a) 10,40,60 b) 80,100,90 =A0c)110=,70,85 ,
> so while =A0you read page A =A0another connection inserst the value let me= say
> 50, but you have already read data =A0from the page A , so it moves the al=l
> data to a new created page so now that data looks like =A0a)10,40 ,50,
> b) 80,100,90 =A0c)110,70,85 ,d)60 ... and =A0as you keep reading =A0you g=et
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint =A0or if you use SQL Server 2005 =A0take a =look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message|||<ckkwan@.my-deja.com> wrote in message
news:2d0f36bc-e70e-40c6-8961-145d20c66e59@.q1g2000prf.googlegroups.com...
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row while you are reading. You
> are about to get an inconsistent data. For example
> you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
> so while you read page A another connection inserst the value let me say
> 50, but you have already read data from the page A , so it moves the all
> data to a new created page so now that data looks like a)10,40 ,50,
> b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message
Hi
I can't see how unreliability and inconsistence made the user requirements
for this system!
John|||<<There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).>>
The ANSI SQL Compliant way to describe how much you want to be isolated from other users is the SET
TRANSACTION ISOLATION command. SQL Server supports this, and READ UNCOMMITTED seems to do what you
want. Apparently Informix has a non-standard command named LOCK MODE, something that SQL Server do
not have. Assuming these indeed do the same thing, I support MS for using the ANSI SQL compliant
name for the command instead of some other command name. If they do not do the same, perhaps you can
enlighten un in what way they differ?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<ckkwan@.my-deja.com> wrote in message
news:2d0f36bc-e70e-40c6-8961-145d20c66e59@.q1g2000prf.googlegroups.com...
Thanks for the info, as I have mentioned earlier in my post, the
Reporting tool can afford to fail, so I don't really mind the data
inconsistency.
There is something like LOCK MODE in informix where we can set the
LOCK hint globally for a specific connection. Is there something
similar in SqlServer (and no, this is not the ISOLATION LEVEL).
On Apr 13, 7:24 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Hi
> > 1. Am I doing it correctly?
> No. What if some user inserts/deletes the row while you are reading. You
> are about to get an inconsistent data. For example
> you have tree pages with data like a) 10,40,60 b) 80,100,90 c)110,70,85 ,
> so while you read page A another connection inserst the value let me say
> 50, but you have already read data from the page A , so it moves the all
> data to a new created page so now that data looks like a)10,40 ,50,
> b) 80,100,90 c)110,70,85 ,d)60 ... and as you keep reading you get
> 60(duplicate) from page D as well.
> > 2. Is there a better way of doing? Example: setting the LOCK MODE
> > (instead of specifying NOLOCK on every command)
> Yes , you can use TABLOCK hint or if you use SQL Server 2005 take a look
> at SNAPSHOT ISOLATION LEVEL in the BOL
> <ckk...@.my-deja.com> wrote in message

Re-adding noded to cluster

Running Sql Server 2000 SP3a on a two node cluster with one instance
running on the active node (Active/Passive)
We have to replace one of the physical nodes (repeated hw failures) -
how do I go about replacing the node within SQL Server. The NT guys
are telling me - that have to install from scratch the OS and bring it
up to the same OS patch levels of the remaining stable node.
So, once I the new node has been re-added to the phsical cluster...how
do i get SQL Server 2000 installed and how do I get it upgraded to SPS
3A
Joe E O
Surprising that you should ask that. On 10/27, I posted the following list
of instructions.
Good luck.
Anthony Thomas
It is easy to evict a cluster node, repair or rebuild, and, perhaps even
upgrade it; however, on many occasions, we have had considerable trouble
reintroducing that newly built node back into the cluster configuration.
The following represents the cleanest solution we have come up with to date:
Here's the sequence of events.
1. Run SQL Server setup to manage the virtual server configuration and add
the new node to this configuration. This installs the RTM binaries to the
new node. It also installs the dreaded SCM (Service Control Manager), but
also the Cluster Network utility. (KB295589)
2. Log into the new node and remove the SCM from the system tray and startup
group. (Service Pack Read Me docs).
3. On both nodes, create Client Side Aliases using Named Pipe for the
virtual server instance. (KB815431)
4. Reboot the added node.
5. Apply SP3a (this was the prior headache). If you do not, because of the
3-digit Build number versus the SP4 4-digit build number, that stupid Win2K3
security dialog about only supporting SS2K if SP3 or later will launch on
the
unattended remote installation in interactive mode, which hangs. (KB905286)
(KB902955) (KB329329)
6. Reboot the added node.
7. Apply SP4.
8. Reboot the added node.
9. Apply HF 2187.
10. Reboot the added node.
11. On both nodes, switch the Client Side aliases to TCP protocol.
12. Now, here's the new error. When attempting to run the instance on the
newly added node, the cluster log will report that it could not start the
sqlsrvres and fail with error 435.
What this means is that the registry still contains the default out of box
registry keys. Primarily, the Named Pipe default pipe and the TCP default
port, as well as the default Start Up parameters. If any of these have been
modified from default, services will start up but fail on the newly added
node.
The fix is to log into the new node and use regedit to connect to the other
node. Export the following keys and save them to the local node.
HKLM\Cluster
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server
HKLM\SOFTWARE\Microsoft\MSSQLSERVER
Then merge these exports to the new node (local registry).
This last one is undocumented. If anyone kind find a KB reference, it would
be appreciated.
Sincerely,
Anthony Thomas

"Joe E O" <josephobrien@.hotmail.com> wrote in message
news:1163777568.605425.238240@.j44g2000cwa.googlegr oups.com...
> Running Sql Server 2000 SP3a on a two node cluster with one instance
> running on the active node (Active/Passive)
> We have to replace one of the physical nodes (repeated hw failures) -
> how do I go about replacing the node within SQL Server. The NT guys
> are telling me - that have to install from scratch the OS and bring it
> up to the same OS patch levels of the remaining stable node.
> So, once I the new node has been re-added to the phsical cluster...how
> do i get SQL Server 2000 installed and how do I get it upgraded to SPS
> 3A
>
> Joe E O
>
sql

Read_committed_snapshot

Hello All,

I have another problem with setting READ_COMMITTED_SNAPSHOT on SQL Server 2005 now.

The problem is:L
In SQL Server 2005, when running following statement:

ALTER DATABASE bugdb2
SET READ_COMMITTED_SNAPSHOT ON

It takes so long time to fnish. Actually I have been waiting 25 minutes for it, it is still not yet finished.

Do you know why?

Thanks a lot

MelFrom BOL,
When the READ_COMMITTED_SNAPSHOT database option is set ON, the mechanisms used to support the option are activated immediately. When setting the READ_COMMITTED_SNAPSHOT option, only the connection executing the ALTER DATABASE command is allowed in the database. There must be no other open connection in the database until ALTER DATABASE is complete. The database does not have to be in single-user mode.

SO close other connections in that database.|||Thanks Mallier :)

Do you know how to use that "termination" option like:

ALTER DATABASE bugdb2
SET READ_COMMITTED_SNAPSHOT ON
WITH <termination>

I still have not yet figured out how to use that.|||ALTER DATABASE bugdb2
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE|||Again,In order to set READ_COMMITTED_SNAPSHOT ON or OFF, there must be no active connections to the database except for the connection executing the ALTER DATABASE command

ALTER DATABASE bugdb2
SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE -- or ucan specify the time in seconds|||Thanks Mallier, I am trying it now ..

Wednesday, March 28, 2012

Read this first! FAQ for DBForums Microsoft SQL Server forum

The current version of Microsoft SQL is SQL 2005.

If you are running a different version of Microsoft SQL, it is your responsibility to state what version you are using (for example, SQL 2000, service pack 2). If you don't include information about what version of Microsoft SQL you are using, we'll assume that you are running SQL 2005 and the answer that we give you may or may not work with the version of Microsoft SQL that you are using.

Kudos to r937 for reminding me to include this version information in our FAQ

General Topics

What is a FAQ? (http://www.dbforums.com/showthread.php?t=1212452#post4527529)
How to ask a question to get quick and correct answers? (http://www.dbforums.com/showthread.php?t=1212452#post4527530)
How do I "join the community"? (http://www.dbforums.com/showthread.php?t=1212452#post4527531)
What does Microsoft have to say about group participation? (http://www.dbforums.com/showthread.php?t=1212452#post4527533)

Homework

How NOT to ask for help! (http://www.dbforums.com/showthread.php?p=6226875#post6226875)

SQL Server Topics

How do I get DDL for my tables? (http://www.dbforums.com/showthread.php?t=1212452#post4527532)An FAQ is a list of Frequently Asked Questions in a forum, newsgroup, or other (presumably online) place. These are questions that regularly appear because pretty much everybody asks them at one time or another. This is a really good place to look for a general "look and feel" for both the forum itself, the people who post there, and the topic in general.

FAQ Index (http://www.dbforums.com/showthread.php?t=1212452)|||Originally posted by Brett Kaiser, updated by Pat Phelan

Please state your problem in the context of a business requirement. Please do not force a narrowly focused technical solution, which may or may not be of any value.

It may also be a distraction to what the actual solution would be. To aid in the solution please do the following if possible

1. State the question

"How do I find the earliest row entered"

2. Please post the DDL of your tables (Including Indexes, and constraints)

Like

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 char(1), Col3 datetime, PRIMARY KEY (Col1))

3. Post some sample data in the form of DML

Like

INSERT INTO myTable99(Col2, Col3)
SELECT 'a', '01/01/2005' UNION ALL
SELECT 'b', '02/01/2005' UNION ALL
SELECT 'c', '03/01/2005'

4. Post whatever DML that you have attempted already...

SELECT * FROM myTable99 a CROSS JOIN myTable99 b

5. Post the expected results

Col1 Col2 Col3
---- -- ----------------
1 a 2005-01-01 00:00:00.000

Good Luck. If these instruction are followed, you will most likely get an answer in minutes.

And don't forget to use [ code] [ /code] tags when posting code, just eliminate the spaces I have used here.

FAQ Index (http://www.dbforums.com/showthread.php?t=1212452)|||Originally posted by Brett Kaiser, updated by Pat Phelan

First and foremost, just join in! Ask or answer a question, add a comment to an existing message thread, or stop by the Yak Corral (http://www.dbforums.com/showthread.php?t=989246).

You can also add yourself here:

http://www.frappr.com/dbforums

FAQ Index (http://www.dbforums.com/showthread.php?t=1212452)|||Originally posted by Brett Kaiser, updated by Pat Phelan

1. Go to Enterprise Manager.

2. Open the database folder to display all of the tables.

3. Right Click on the table(s) you want.

4. Choose Menu options All Tasks>Generate SQL Scripts

5. Look at the dialog, there are three tabs. Make sure you pick all the correct options (indexes, keys, ect)

6. Click Preview.

7. Copy and paste the code.

FAQ Index (http://www.dbforums.com/showthread.php?t=1212452)|||See their Knowledgebase article 555375 (http://support.microsoft.com/kb/q555375).

FAQ Index (http://www.dbforums.com/showthread.php?t=1212452)|||In discussion of poor practices in another forum, we unearthed some old threads here that demonstrate the wrong way to ask for help. If you want examples of how NOT to ask for help, these are some good examples!

With thanks to Blindman for bringing these posts back to public view, and to R937 for suggesting that they be imortalized!

The Introduction (http://www.dbforums.com/showthread.php?t=1607194)

It gets better! (http://www.dbforums.com/showthread.php?t=1607193)

Taking this process a few steps further:

1) If you are asking questions for a course, please say so up front. We need to take a different approach when helping you with homework than we do with folks that simply need an answer to a "real world" problem.

2) It helps us a great deal if you can post a link to the assignment, or if you can scan the assignment and post it with your questions. That way we know exactly what the assignment requires, and we can infer a lot about what they're trying to get you to learn in the assignment.

3) Don't expect us to just do your homework for you. Life isn't like that, and you'll cheat yourself more than you can cheat the school/teacher if all you do is copy what someone else has done for you. We'll be glad to help, but you really don't want to turn us loose on your homework assignment... We can be evil! :D

4) If you have a partial solution worked out, or have at least tried something, post that too. If we can see what you've tried, we can probably help you a lot more than if we "start cold" because we can then see more of how you're thinking and where we can help.

FAQ Index (http://www.dbforums.com/showthread.php?t=1212452)

Read the profiler output.

In 2005 I am running the profiler on 2 large batch processes that we run.
I am focusing on READS. Query below.
select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
where Reads > 15000
and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
and loginName != 'ELECTRACASH\srussell'
--and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
Or should I be going for duration instead?
I am excluding a B2B report at this time as well as one SP that I have
tuned.
Any ideas on this method of madness?
TIA
__Stephen_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||_Stephen wrote:
> In 2005 I am running the profiler on 2 large batch processes that we run.
> I am focusing on READS. Query below.
> select * from ecdbprod.[ASPState].[dbo].[Tweaks2]
> where Reads > 15000
> and substring(TextData, 1,31 ) != 'exec MAR_Get_Daily_Trans_Report '
> and loginName != 'ELECTRACASH\srussell'
> --and substring(TextData, 1,20 ) != 'exec ARP_getPrevious'
> Or should I be going for duration instead?
> I am excluding a B2B report at this time as well as one SP that I have
> tuned.
> Any ideas on this method of madness?
> TIA
> __Stephen
>
Reads is generally a good thing to focus on, but it really depends on
what your bottlenecks are. If your server is CPU-bound, focus on CPU.
If it is I/O-bound, focus on reads.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> My indicators for the performance monitor are showing huge lock counts, an
d
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess whe
n
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> Reads is generally a good thing to focus on, but it really depends on what
> your bottlenecks are. If your server is CPU-bound, focus on CPU. If it is
> I/O-bound, focus on reads.
My indicators for the performance monitor are showing huge lock counts, and
when I drill around I see that the counts can be up into the 10,000 for
brief bursts. The CPUs are chugging along around 25% usage as a guess when
you try to put all the graph lines together.
Locks to me are more IO then CPU so I'll stick to that tact.
Thanks again.|||_Stephen wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O3irMBalGHA.2112@.TK2MSFTNGP04.phx.gbl...
> My indicators for the performance monitor are showing huge lock counts, an
d
> when I drill around I see that the counts can be up into the 10,000 for
> brief bursts. The CPUs are chugging along around 25% usage as a guess whe
n
> you try to put all the graph lines together.
> Locks to me are more IO then CPU so I'll stick to that tact.
> Thanks again.
>
Are you monitoring scans? High reads and the bursts of lock counts
could be caused by excessive table or index scans, indicating the need
to better indexing...

Monday, March 26, 2012

Read the profiler output.

Read only DB when moving database (error 5105 "device activation...")

Windows 2003 server
SQL2000 installed and running
Create a database on C:\Data called Ctest
Create a database on F:\Data called Ftest
Detach both databases.
Move Ftest to c:\Data folder.
Move Ctest to F:\Data folder.
Reattach both databases from their new locations.
Here's the problem --
Both are read only now.
Un-checking the Read only properties causes an error 5105 "device
activation..."
I do the same thing on another system and they don't attach as read
only.
Any ideas on this one?
Mark GMark,
Check the file properties for the .mdf and .ldf files - are they read-only?
Also since this is a test box try restarting the server as well.
HTH
Jerry
"Mark G" <megriep@.gmail.com> wrote in message
news:1128185904.036026.202210@.o13g2000cwo.googlegroups.com...
> Windows 2003 server
> SQL2000 installed and running
> Create a database on C:\Data called Ctest
> Create a database on F:\Data called Ftest
> Detach both databases.
>
> Move Ftest to c:\Data folder.
> Move Ctest to F:\Data folder.
>
> Reattach both databases from their new locations.
>
> Here's the problem --
> Both are read only now.
> Un-checking the Read only properties causes an error 5105 "device
> activation..."
>
> I do the same thing on another system and they don't attach as read
> only.
>
> Any ideas on this one?
>
> Mark G
>|||And also verify that the SQL Server service account has proper permission on the files.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:Onzf8DrxFHA.3400@.TK2MSFTNGP14.phx.gbl...
> Mark,
> Check the file properties for the .mdf and .ldf files - are they read-only? Also since this is a
> test box try restarting the server as well.
> HTH
> Jerry
> "Mark G" <megriep@.gmail.com> wrote in message
> news:1128185904.036026.202210@.o13g2000cwo.googlegroups.com...
>> Windows 2003 server
>> SQL2000 installed and running
>> Create a database on C:\Data called Ctest
>> Create a database on F:\Data called Ftest
>> Detach both databases.
>>
>> Move Ftest to c:\Data folder.
>> Move Ctest to F:\Data folder.
>>
>> Reattach both databases from their new locations.
>>
>> Here's the problem --
>> Both are read only now.
>> Un-checking the Read only properties causes an error 5105 "device
>> activation..."
>>
>> I do the same thing on another system and they don't attach as read
>> only.
>>
>> Any ideas on this one?
>>
>> Mark G
>|||Thanks,
Yes, it's a permissions thing--something to do with Windows. Not sure
why one system has the problem and the other doesn't. I'll have to
look into it.
Thanks for all your help.
Mark.

Read Only Cursor

I have a sproc that has been running for over 2 years. We then alter a tabl
e structure
increasince the size of 3 fields, and correspondingly alter an Insert statem
ent for this
table. Now an 'update tablename where current of mycur' much later in the c
ode, issues an error of
'The cursor is Read Only' and fails.
The cursor syntax were :
SELECT ld_employee_no,
adjusted_hours
FROM Labrdet
WHERE ld_employee_no = @.dIFf_cur_empno
AND ld_prod_id + ld_prod_category + ld_prod_activity <> '02263'
ORDER BY ld_employee_no, adjusted_hours desc
I changed last line to the following and it works.
SELECT ld_employee_no,
adjusted_hours
FROM Labrdet
WHERE ld_employee_no = @.dIFf_cur_empno
AND ld_prod_id + ld_prod_category + ld_prod_activity <> '02263'
FOR UPDATE OF adjusted_hours
Does anyone have any idea, or should I post more info? We'd really like to
know
why the DDL change and the Insert change would affect a cursor update.
TIA,
Marc MillerWithout seeing the full repro I'm guessing that you changed something
that caused an implicit conversion to a static cursor. Always specify
cursor options explicitly to avoid this.
Could you explain why you are using a cursor at all? Again just
guessing by your code fragment this looks very like a straight data mod
that ought to be possible in an UPDATE with no cursor at all. If this
is a 2 year code legacy then maybe now would be a good time to review
and replace it.
David Portas
SQL Server MVP
--|||David,
I have a table of salaried employee time entires. Reporting requires,
however, that I only
show 40 hours per employee, even though they report overtime hours. Their
time is reported
in quarter hours increments and I need to loop and decrement/increment the
line items by the amount of
the overtime until I can best adjust each line 'evenly' (sort of an
allocation type basis.) to a total of 40 hours
for each person.
I have no idea in the world how I would use an UPDATE to accomplish this.
Thanks,
Marc Miller
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1128538402.789619.48450@.z14g2000cwz.googlegroups.com...
> Without seeing the full repro I'm guessing that you changed something
> that caused an implicit conversion to a static cursor. Always specify
> cursor options explicitly to avoid this.
> Could you explain why you are using a cursor at all? Again just
> guessing by your code fragment this looks very like a straight data mod
> that ought to be possible in an UPDATE with no cursor at all. If this
> is a 2 year code legacy then maybe now would be a good time to review
> and replace it.
> --
> David Portas
> SQL Server MVP
> --
>|||> Reporting requires,
> however, that I only
> show 40 hours per employee
If that's just a reporting requirement why do you need to update the
table? Wouldn't it suffice to put the calc in a SELECT statement?

> I have no idea in the world how I would use an UPDATE to accomplish this.
If you want help with that please post DDL, sample data and required
results as described here:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--

Friday, March 23, 2012

Read from tables when another transactions isolationlevel is ReadCommited

I need to be able to read from tables while a transaction is running. This
transaction could take 20-30 minutes. I don't want to be able to read
uncommitted data that has been written in this transaction though.
I looked at NOLOCK but that says it reads uncommitted data.
Any suggestions?READPAST allows you to read past the locked rows.
Keith
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:eLo9e5$SFHA.2096@.TK2MSFTNGP14.phx.gbl...
>I need to be able to read from tables while a transaction is running. This
> transaction could take 20-30 minutes. I don't want to be able to read
> uncommitted data that has been written in this transaction though.
> I looked at NOLOCK but that says it reads uncommitted data.
> Any suggestions?
>|||With nolock you could read dirty rows. In this case you'd be safe to read
committed.
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:eLo9e5$SFHA.2096@.TK2MSFTNGP14.phx.gbl...
>I need to be able to read from tables while a transaction is running. This
> transaction could take 20-30 minutes. I don't want to be able to read
> uncommitted data that has been written in this transaction though.
> I looked at NOLOCK but that says it reads uncommitted data.
> Any suggestions?
>|||If the isolation level is set to ReadCommited for the transaction, does that
cause the table to lock or just the rows being inserted, updated?
If it only effects the inserted and updated then the READPAST will work.
Is there anyway to perform the write while within a transaction without
locking the tables at all?
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:uRnvitATFHA.3152@.TK2MSFTNGP12.phx.gbl...
> READPAST allows you to read past the locked rows.
> --
> Keith
>
> "Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
> news:eLo9e5$SFHA.2096@.TK2MSFTNGP14.phx.gbl...
This
>|||Hello Joe,
Could find a solution? I've a case same with yours. READPAST works and I can
read unlocked rows. But do you know a way to also read locked rows with the
ir original (before lock) values?sql

Wednesday, March 21, 2012

READ COMMITTED SNAPSHOT ON causes performance degradation

I am running a benchmark test with multiple connections running the same
stored procedure with different parameters. This stored procedures does only
SELECT. There are no other activity on the database.
The stored procedure containst this select
SELECT Model,AVG(Price),MIN(Price),MAX(Price),COUNT(*)
FROM SH_Product
WHERE Project_Number = @.Station
AND EmployeeID = 0
AND Type = @.Match100
GROUP BY Model
ORDER BY Model
When the database is set in READ COMMITTED SNAPSHOT OFF mode, the number of
transactions per second increases linearly as more and more connections are
added.
But when the database is set to READ COMMITTED SNAPSHOT ON, the performance
degrades after 20 users, the total transactions processed per second remains
constant when number of users increase. That means for each user the
transactions per second reduces.
I can understand this if there was any other INSERT/UPDATE/DELETE activity
happening on the database, as SELECT will have to traverse the row version
chain to get the data, but in SELECT only environment, how can the
performance degrade.
With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
overhead for SQL Server. I have a PSS ticket open for this, but I am getting
a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
row version it is slower, but my point is if there is no data change why does
SQL Server has to go to tempdb?
Am I missing something?. Please help.
Thank youOn Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
(snip)
>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>happening on the database, as SELECT will have to traverse the row version
>chain to get the data, but in SELECT only environment, how can the
>performance degrade.
>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>overhead for SQL Server. I have a PSS ticket open for this, but I am getting
>a satisfactory answer. All I get is since SELECT needs to go to tempdb to get
>row version it is slower, but my point is if there is no data change why does
>SQL Server has to go to tempdb?
Hi Shailesh,
I'm not intimately familiar with the internals of READ COMMITTED
SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
can't know that there are no previous row versions there without looking
first.
Have you considered setting the database to READ ONLY? That will fully
eliminate all locking overhead.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Thanks Hugo
I am seeing this behavior while running a benchmark, which has different
sets of tests, one of them being CPU intensive test which only does SELECT.
It is not on a real production database so putting database in READ ONLY
mode is not an issue, but I wanted to understand the performance issue
without doing it.
I looked at page file structure in Kalen Delaney's book and I don't see any
information about whether SQL server puts a status bit on the page itself
for locked rows. But with READ COMMITTED SNAPSHOT ON, SQL server puts a 14
byte data in each row to store Transaction Sequence number (XSN), it is only
added when the row is updated. So logically speaking when a connection tries
to SELECT from a row, it has a XSN and when it goes to check the row in disk
if there is no XSN field then it should immediately know that the row is not
modified and should not go to tempdb to check.
Even if there is XSN for the row, and if it's value is less than SELECT XSN
then it should check lock records before going to tempdb. And this overhead
is also incurred when database is in READ COMMITTED SNAPSHOT OFF mode. So I
don't really get why the performance suffers so much.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:m3oqf31go4ti3d37736nsegqp2patoqjin@.4ax.com...
> On Thu, 27 Sep 2007 12:31:01 -0700, Shailesh Khanal wrote:
> (snip)
>>I can understand this if there was any other INSERT/UPDATE/DELETE activity
>>happening on the database, as SELECT will have to traverse the row version
>>chain to get the data, but in SELECT only environment, how can the
>>performance degrade.
>>With READ COMMITTED SNAPSHOT ON, there are no locks to acquire hence less
>>overhead for SQL Server. I have a PSS ticket open for this, but I am
>>getting
>>a satisfactory answer. All I get is since SELECT needs to go to tempdb to
>>get
>>row version it is slower, but my point is if there is no data change why
>>does
>>SQL Server has to go to tempdb?
> Hi Shailesh,
> I'm not intimately familiar with the internals of READ COMMITTED
> SNAPSHOT, but my guess is that SQL Server has to go to tempdb because it
> can't know that there are no previous row versions there without looking
> first.
> Have you considered setting the database to READ ONLY? That will fully
> eliminate all locking overhead.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Read a Database Datetime (datatype) value and pass it into a Parameter

I'm running the following stored proceudre that I will eventually be using checboxes and a sqlDataAdapter to fill a DataGrid using VB.Net.

When I attempt to execute the SP on the server side for testing, it throws me the error "syntax error converting datetime from character string".

conversion of datatypes is something I'm still new to so I can't begin to understand how to write the code thus why I'm seeking help. Here's the SP Code:

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTERPROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18)='00:AC:12:E5:76:9C',

@.CREATED asdatetime='4/25/2007 8:40:50 AM',

@.MODIFIED asdatetime='5/19/2007 5:05:04 AM',

@.WORKSTATION_NAME as varchar(13)='B000E7FF53C72',

@.IP_ADDRESS as varchar(15)='171.136.201.142',

@.USER_NAME as varchar(8)='nbe5533',

@.OPERATING_SYSTEM as varchar(25)='Windows XP Professional',

@.SERVICE_PACK as varchar(3)='2.0',

@.BAND_VERSION as varchar(7)='5.06 b2',

@.WORKSTATION_OU as varchar(200)='CN=B001321D14A41,OU=Desktops,OU=Agents,OU=Card,OU=Customer Service and Support,OU=Utility,OU=NCG,OU=Workstations,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.WORKSTATION_OWNER as varchar(200)='CN=Davis\, Wally NBE5533,OU=Distributed Server Support,OU=NCG Administrators,OU=Accounts,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.MANUFACTURER as varchar(26)='Dell Computer Corporation;',

@.MODEL as varchar(40)='Latitude D600;',

@.CHASSIS as varchar(10)='8;12;',

@.SERIAL_NUMBER as varchar(30)='.83SFB51.CN486434737027.;',

@.PROCESSOR as varchar(100)='Intel(R) Pentium(R) M processor 1600MHz;',

@.HARD_DRIVE as varchar(40)='FUJITSU MHV2040AH;',

@.HARD_DRIVE_SIZE as varchar(30)='40007761920;',

@.MEMORY as varchar(22)='1073741824;1073741824;',

@.NAME as varchar(100)='KB887979',

@.VERSION as varchar(20)='VALUE DOES NOT EXIST',

@.BUILD as varchar(45)='1.3',

@.INSTALL_STATUS as varchar(20)='1',

@.INSTALL_DATE as varchar(21)='3/31/2005 2:40:34 PM',

@.PACKAGE_NAME as varchar(90)='CRYSTAL_REPORTS_ACTIVEX_VIEWER_10.0_9.2_8.6_8.5_20.05.08.01_WKS_XP2KNT_BAND_I1^EDE'

-- SET NOCOUNT ON;

SELECT main.MAC, main.CREATED, main.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNERJOIN

hardware ON main.MAC = hardware.MAC INNERJOIN

network ON main.MAC = network.MAC INNERJOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE'%'+@.MAC+'%'AND MAIN.CREATED LIKE'%'+@.CREATED+'%'AND MAIN.MODIFIED LIKE'%'+@.MODIFIED+'%'AND NETWORK.WORKSTATION_NAME LIKE'%'+@.WORKSTATION_NAME+'%'AND NETWORK.IP_ADDRESS LIKE'%'+@.IP_ADDRESS+'%'AND NETWORK.USER_NAMELIKE'%'+@.USER_NAME+'%'AND NETWORK.OPERATING_SYSTEM LIKE'%'+@.OPERATING_SYSTEM+'%'AND NETWORK.SERVICE_PACK LIKE'%'+@.SERVICE_PACK+'%'AND NETWORK.BAND_VERSION LIKE'%'+@.BAND_VERSION+'%'AND NETWORK.WORKSTATION_OU LIKE'%'+@.WORKSTATION_OU+'%'AND NETWORK.WORKSTATION_OWNER LIKE'%'+@.WORKSTATION_OWNER+'%'AND HARDWARE.MANUFACTURER LIKE'%'+@.MANUFACTURER+'%'AND HARDWARE.MODEL LIKE'%'+@.MODEL+'%'AND HARDWARE.CHASSIS LIKE'%'+@.CHASSIS+'%'AND HARDWARE.SERIAL_NUMBER LIKE'%'+@.SERIAL_NUMBER+'%'AND HARDWARE.PROCESSOR LIKE'%'+@.PROCESSOR+'%'AND HARDWARE.HARD_DRIVE LIKE'%'+@.HARD_DRIVE+'%'AND HARDWARE.HARD_DRIVE_SIZE LIKE'%'+@.HARD_DRIVE_SIZE+'%'AND HARDWARE.MEMORY LIKE'%'+@.MEMORY+'%'AND SOFTWARE.NAME LIKE'%'+@.NAME+'%'AND SOFTWARE.VERSION LIKE'%'+@.VERSION+'%'AND SOFTWARE.BUILD LIKE'%'+@.BUILD+'%'AND SOFTWARE.INSTALL_STATUS LIKE'%'+@.INSTALL_STATUS+'%'AND SOFTWARE.INSTALL_DATE LIKE'%'+@.INSTALL_DATE+'%'AND SOFTWARE.PACKAGE_NAME LIKE'%'+@.PACKAGE_NAME+'%'

Thank you,

Wallace

hi, this is because you were concatenating a datetime variable with a string '%'

change this lines on your where clause

--AND MAIN.CREATED LIKE '%'+@.CREATED+'%'
AND MAIN.CREATED = @.CREATED
--AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'
AND MAIN.MODIFIED = @.MODIFIED|||

CREATED LIKE '%'+@.CREATED+'%'

Wallace,

I guess that I am at a loss about why you are concatenating wildcards to a datetime value.

Is there a particular problem you are attempting to solve by so doing?

Without converstion, you cannot add characters ( '%' ) to a datetime datatype. (What exactly do you hope to accomplish by adding '%' to the datetime?)

I am assuming that Main.Created is a datetime datetype.

In fact, I wonder if any of the above parameters really need to have '%' added to each side of the value...

|||

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

Are you doing this like to compare parts of dates? Like:

drop table dateRow
go
create table dateRow
(
dateValue datetime
)
insert into dateRow
select '20070101'
union all
select '20070201'
union all
select '20070301'
union all
select '20070401'
union all
select '20070501'
union all
select '20070601'
go
--find rows from 2007
select *
from dateRow
where convert(varchar(8),datevalue,112) like '2007_'
go
--find rows from January
select *
from dateRow
where convert(varchar(8),datevalue,112) like '_01__'

--find rows from June
select *
from dateRow
where convert(varchar(8),datevalue,112) like '_06__'

Interesting idea...Probably not perfect in terms of performance. A better way to do this involves having a table of dates that you can join to your date value (if you don't have time values.) Then you can index the month, year, or day values for fast searching.

Here is an article with technique to load the date table: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

|||

This doesn't cause any errors on my server...

Code Snippet

CREATE PROCEDURE dbo.uspPvtSelectCommand
( @.CREATED as datetime = '4/25/2007 8:40:50 AM',
@.MODIFIED as datetime = '5/19/2007 5:05:04 AM'
)
AS
SELECT getdate(), @.Created, @.Modified
GO

I think the error comes from the concatenation of the datetime parameter as I indicated earlier.

LIKE '%'+@.CREATED+'%' AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'

The following fails with such an error...

Code Snippet

ALTER PROCEDURE dbo.uspPvtSelectCommand
( @.CREATED as datetime = '4/25/2007 8:40:50 AM',
@.MODIFIED as datetime = '5/19/2007 5:05:04 AM'
)
AS
SELECT getdate(), @.Created, ( '%' +@.Modified + '%' )
GO

EXECUTE dbo.uspPvtSelectCommand

Server: Msg 241, Level 16, State 1, Procedure uspPvtSelectCommand, Line 6
Conversion failed when converting datetime from character string.

|||

Hi Arnie,

I figured I would give everyone who's been so kind to help with a little more information. I have a DataGrid in my vb.net app.

I have 25 fields/columns and when I go to click on "Preview" from the DataGrid, it hit's the second field parameter "CREATED" and throws the error, " Enter a value for parameter "CREATED". What I have are a bunch of checkboxes on my form, so that our Managers can click on any combination of checkboxs (that represents a field in one of 4 tables), it will pull up only those checkboxex (fields) of that data, use the SP to join those fields and then store it in the datagrid and then a separate sub-routine that exports it to Excel. So, right now, when I go back to recreate a new SQLDataAdapter in vb.net, when I select the stored procedure, it doesn't see the list of parameters, but, it is connected to the right db server so, the problem seems to be the way my Stored procedure is written.

I have since removed the concatenation and changed it so that MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED but still the error.

Any further assistance would be appreciated. Here is what the SP looks like now.

USE platform_validation_tool

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTERPROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18),

@.CREATED asdatetime,

@.MODIFIED asdatetime,

@.WORKSTATION_NAME as varchar(13),

@.IP_ADDRESS as varchar(15),

@.USER_NAME as varchar(8),

@.OPERATING_SYSTEM as varchar(25),

@.SERVICE_PACK as varchar(3),

@.BAND_VERSION as varchar(7),

@.WORKSTATION_OU as varchar(200),

@.WORKSTATION_OWNER as varchar(200),

@.MANUFACTURER as varchar(26),

@.MODEL as varchar(40),

@.CHASSIS as varchar(10),

@.SERIAL_NUMBER as varchar(30),

@.PROCESSOR as varchar(100),

@.HARD_DRIVE as varchar(40),

@.HARD_DRIVE_SIZE as varchar(30),

@.MEMORY as varchar(22),

@.NAME as varchar(100),

@.VERSION as varchar(20),

@.BUILD as varchar(45),

@.INSTALL_STATUS as varchar(20),

@.INSTALL_DATE as varchar(21),

@.PACKAGE_NAME as varchar(90)

-- SET NOCOUNT ON;

SELECT main.MAC,getdate(), @.CREATED, @.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNERJOIN

hardware ON main.MAC = hardware.MAC INNERJOIN

network ON main.MAC = network.MAC INNERJOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE'%'+@.MAC+'%'AND MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED AND NETWORK.WORKSTATION_NAME LIKE'%'+@.WORKSTATION_NAME+'%'AND NETWORK.IP_ADDRESS LIKE'%'+@.IP_ADDRESS+'%'AND NETWORK.USER_NAMELIKE'%'+@.USER_NAME+'%'AND NETWORK.OPERATING_SYSTEM LIKE'%'+@.OPERATING_SYSTEM+'%'AND NETWORK.SERVICE_PACK LIKE'%'+@.SERVICE_PACK+'%'AND NETWORK.BAND_VERSION LIKE'%'+@.BAND_VERSION+'%'AND NETWORK.WORKSTATION_OU LIKE'%'+@.WORKSTATION_OU+'%'AND NETWORK.WORKSTATION_OWNER LIKE'%'+@.WORKSTATION_OWNER+'%'AND HARDWARE.MANUFACTURER LIKE'%'+@.MANUFACTURER+'%'AND HARDWARE.MODEL LIKE'%'+@.MODEL+'%'AND HARDWARE.CHASSIS LIKE'%'+@.CHASSIS+'%'AND HARDWARE.SERIAL_NUMBER LIKE'%'+@.SERIAL_NUMBER+'%'AND HARDWARE.PROCESSOR LIKE'%'+@.PROCESSOR+'%'AND HARDWARE.HARD_DRIVE LIKE'%'+@.HARD_DRIVE+'%'AND HARDWARE.HARD_DRIVE_SIZE LIKE'%'+@.HARD_DRIVE_SIZE+'%'AND HARDWARE.MEMORY LIKE'%'+@.MEMORY+'%'AND SOFTWARE.NAME LIKE'%'+@.NAME+'%'AND SOFTWARE.VERSION LIKE'%'+@.VERSION+'%'AND SOFTWARE.BUILD LIKE'%'+@.BUILD+'%'AND SOFTWARE.INSTALL_STATUS LIKE'%'+@.INSTALL_STATUS+'%'AND SOFTWARE.INSTALL_DATE LIKE'%'+@.INSTALL_DATE+'%'AND SOFTWARE.PACKAGE_NAME LIKE'%'+@.PACKAGE_NAME+'%'

Thanks and Sincerely,

Wallace

|||

Wallace,

I need you to clarify.

If I understand this correctly, you are passing into the stored procedure the checkbox values for each of the parameters listed. Is that correct?

And what checkboxes are checked determines what data is expected?

OR,

Is there actual values in the input parameters, AND the presence of a check indicated to use that value in the WHERE clause...

|||That's correct, I want to pass values, being read from the database, into the sql sp parameters, based on which checkboxes are checked.

The values will be read from the Database, passed into the parameter, and joined via the SP into a DataGrid. I have a separate vb.net sub routine that will take all of this and export to excel.

Thanks,

Wally

|||

I'm trying to understand and help, but I'm still confused. You replied "That's correct..." to two diametrically opposed questions.

Do you wish to collect multiple rows of data from the database and display that data in the DataGrid?

How do you determine what should be displayed?

Are all columns always returned and displayed?

Is this a search routine where you pass in some search criteria, hence the input parameters?

What is the application code that calls the stored procedure? (Please post.)

|||

Arnie,

We have a database that has 4 tables, 25 columns of data to pull from. Software/Hardware data is pulled from the PC's and stored on this database.

The vb.net application I'm creating will connect to a database called "platform_validation_tool" running on SQL 2005, and

will use an SQLConnection, SQLDataAdapter and DataSet to mirror the tables.

Whether the user checks one checkbox or all 25, I want it to pull any column of data based on those checkboxes checked, i.e. using the SQLDataAdapter and it's methods, it will search the database, pass it into the input parameters, and then return it to the DataGrid for display. That is the first step. Right now, I can't get the stored procedure to work because of the datetime datatype to convert to a string. Once I can jump this hurdle, then I'll setup the rest of the code in vb.net. Here's some of the vb.net code that will allow me to retrieve the data from the database by identifying the specific SourceColumn and then Fill the DataGrid:

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

etc, and so on.,

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName".

Let me know if it needs further explanation. I've only been in programming for 3 months. Thank you for your patience.

Wally

|||hi,

i'm just curious on how did you present the values in your checkboxes? a.) is it along side on a data grid? or b.) does your checkbox has an input box along side it where the user can input a search string then ticks the checkbox if it should be included in the search?

if your doing b. does the user need to input the time for the created and modified date?|||How about posting some of the vb.net code that invokes the sp?
SQLDataSource definition, etc.|||

DaleJ,

My code is at work and until I return there, simplest way I can put it is the routine goes like this:

The checkboxes are just inside of a GroupBox on a separate Form and not imbedded within or next to the DataGrid.

If checkbox1.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

Else checkbox1.checked = False

End If

If checkbox2.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

Else checkbox2.checked = False Then

End If

Item(1), Item(2), item(3), etc. is the logical order of the column parameters, i.e. Item(0) = @.ReturnValue, Item(1) = @.MAC, Item(2) = @.CREATED, Item(3) = @.MODIFIED, etc.

There will be 25 checkboxes in all, from 4 tables. There's other code that I still need to plug in but this is the jist of it. I just can't get the stored procedure to input any of the data from the MODIFIED and CREATED columns in the database (using DateTime datatype) to convert over to a varchar string.

Finally, after the checkboxes have been evaluated as checked or unchecked, it will run the stored procedure on all the checkboxes whose boolean is True with the Fill method below .

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName" and fills the DataSet.

It may seem elementary but it's the easiest way for me to start out learning to code until I get a couple of vb.net classes under my belt.

Wallace

|||

The following code example 'should' find a match for any parameters passed in by your users. If this works for you, I suspect that you could do away with the checkboxes on the form AND the IF-End IF blocks -they will not be needed. Just set all the parameters

Since you are starting out learning, I suggest that you quickly drop using all caps. We all have learned to read using mixed case and we recognize and read mixed case with greater ease than all caps. However, there is a 'tradition' of using caps for the SQL language words.

Also, rigorously following good formatting principles will make your code easier to read, and easier to maintain.

Code Snippet

ALTER PROCEDURE [dbo].[uspPvtSelectCommand]
( @.MAC varchar(18),
@.Created datetime,
@.Modified datetime,
@.Workstation_Name varchar(13),
@.IP_Address varchar(15),
@.User_Name varchar(8),
@.Operating_System varchar(25),
@.Service_Pack varchar(3),
@.Band_Version varchar(7),
@.Workstation_OU varchar(200),
@.Workstation_Owner varchar(200),
@.Manufacturer varchar(26),
@.Model varchar(40),
@.Chassis varchar(10),
@.Serial_Number varchar(30),
@.Processor varchar(100),
@.Hard_Drive varchar(40),
@.Hard_Drive_Size varchar(30),
@.Memory varchar(22),
@.Name varchar(100),
@.Version varchar(20),
@.Build varchar(45),
@.Install_Status varchar(20),
@.Install_Date varchar(21),
@.Package_Name varchar(90)
)
AS

SET NOCOUNT ON;

SELECT
m.MAC,
getdate(),
m.Created,
m.Modified,
h.Manufacturer,
h.Model,
h.Chassis,
h.Serial_Number,
h.Processor,
h.Hard_Drive,
h.Hard_Drive_Size,
h.Memory,
n.Workstation_Name,
n.IP_Address,
n.[User_Name],
n.Operating_System,
n.Service_Pack,
n.Band_Version,
n.Workstation_OU,
n.Workstation_Owner,
s.MAC,
s.[Name],
s.Version,
s.Build,
s.Install_Status,
s.Install_Date,
s.Package_Name
FROM Main m
JOIN Hardware h
ON m.MAC = h.MAC
JOIN Network n
ON m.MAC = n.MAC
JOIN Software s
ON m.MAC = s.MAC
WHERE ( m.MAC = @.MAC
AND m.Created = coalesce( nullif( @.Created, 0 ), m.Created )
AND m.Modified = coalesce( nullif( @.Modified, 0 ), m.Modified )
AND n.Workstation_Name = coalesce( nullif( @.Workstation_Name, '' ), n.Workstation_Name )
AND n.IP_Address = coalesce( nullif( @.IP_Address, '' ), n.IP_Address )
AND n.[User_Name] = coalesce( nullif( @.User_Name, '' ), n.[User_Name] )
AND n.Operating_System = coalesce( nullif( @.Operating_System, '' ), n.Operating_System )
AND n.Service_Pack = coalesce( nullif( @.Service_Pack, '' ), n.Service_Pack )
AND n.Band_Version = coalesce( nullif( @.Band_Version, '' ), n.Band_Version )
AND n.Workstation_OU = coalesce( nullif( @.Workstation_OU, '' ), n.Workstation_OU )
AND n.Workstation_Owner = coalesce( nullif( @.Workstation_Owner, '' ), n.Workstation_Owner )
AND h.Manufacturer = coalesce( nullif( @.Manufacturer, '' ), h.Manufacturer )
AND h.Model = coalesce( nullif( @.Model, '' ), h.Model )
AND h.Chassis = coalesce( nullif( @.Chassis, '' ), h.Chassis )
AND h.Serial_Number = coalesce( nullif( @.Serial_Number, '' ), h.Serial_Number )
AND h.Processor = coalesce( nullif( @.Processor, '' ), h.Processor )
AND h.Hard_Drive = coalesce( nullif( @.Hard_Drive, '' ), h.Hard_Drive )
AND h.Hard_Drive_Size = coalesce( nullif( @.Hard_Drive_Size, '' ), h.Hard_Drive_Size )
AND h.Memory = coalesce( nullif( @.Memory, '' ), h.Memory )
AND s.[Name] = coalesce( nullif( @.Name, '' ), s.[Name] )
AND s.Version = coalesce( nullif( @.Version, '' ), s.Version )
AND s.Build = coalesce( nullif( @.Build, '' ), s.Build )
AND s.Install_Status = coalesce( nullif( @.Install_Status, '' ), s.Install_Status )
AND s.Install_Date = coalesce( nullif( @.Install_Date, '' ), s.Install_Date )
AND s.Package_Name = coalesce( nullif( @.Package_Name, '' ), s.Package_Name )

|||

Hey Arnie,

Thank you for the tips. I'll be sure to apply them as a newcomer.

The code you supplied me for the @.CREATED and @.MODIFIED parameters didn't create any errors when I Executed the stored procedure. I went into the datagrid on my vb.net form, clicked on the preview button, and again, it threw me the error, "Enter a value for paramter "CREATED". What I did then was added two dates (format is > 04/04/2007) in the datagrid "value" fields to see what it would return. The results it returned are as follows: Type = Int32, Value = 0. It looks as if it reads the data I inputted into the datagrid value field it recognizes this date format as an Integer but somehow didn't return this data in the results window.

Now, when it hit the 4th line, where I started adding the code you updated, "AND n.Workstation_Name = coalesce( nullif( @.Workstation_Name, '' ), n.Workstation_Name )", in the WHERE clause, it threw this error:

Msg 306, Level 16, State 1, Procedure uspPvtSelectCommand, Line 34

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

The bottom line is that the stored procedure will not read the date and time info from the CREATED or the MODIFIED fields in the main.MAC table. This is the format in the database for both fields -> 4/25/2007 5:05:04 AM

I'm still trying to find some material on converting this date and time string value to a datetime datatype value.

Any other suggestions are most welcome.

Wally

Read a Database Datetime (datatype) value and pass it into a Parameter

I'm running the following stored proceudre that I will eventually be using checboxes and a sqlDataAdapter to fill a DataGrid using VB.Net.

When I attempt to execute the SP on the server side for testing, it throws me the error "syntax error converting datetime from character string".

conversion of datatypes is something I'm still new to so I can't begin to understand how to write the code thus why I'm seeking help. Here's the SP Code:

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18) = '00:AC:12:E5:76:9C',

@.CREATED as datetime = '4/25/2007 8:40:50 AM',

@.MODIFIED as datetime = '5/19/2007 5:05:04 AM',

@.WORKSTATION_NAME as varchar(13) = 'B000E7FF53C72',

@.IP_ADDRESS as varchar(15) = '171.136.201.142',

@.USER_NAME as varchar(8) = 'nbe5533',

@.OPERATING_SYSTEM as varchar(25) = 'Windows XP Professional',

@.SERVICE_PACK as varchar(3) = '2.0',

@.BAND_VERSION as varchar(7) = '5.06 b2',

@.WORKSTATION_OU as varchar(200) = 'CN=B001321D14A41,OU=Desktops,OU=Agents,OU=Card,OU=Customer Service and Support,OU=Utility,OU=NCG,OU=Workstations,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.WORKSTATION_OWNER as varchar(200) = 'CN=Davis\, Wally NBE5533,OU=Distributed Server Support,OU=NCG Administrators,OU=Accounts,OU=BAND,DC=corp,DC=bankofamerica,DC=com',

@.MANUFACTURER as varchar(26) = 'Dell Computer Corporation;',

@.MODEL as varchar(40) = 'Latitude D600;',

@.CHASSIS as varchar(10) = '8;12;',

@.SERIAL_NUMBER as varchar(30) = '.83SFB51.CN486434737027.;',

@.PROCESSOR as varchar(100) = 'Intel(R) Pentium(R) M processor 1600MHz;',

@.HARD_DRIVE as varchar(40) = 'FUJITSU MHV2040AH;',

@.HARD_DRIVE_SIZE as varchar(30) = '40007761920;',

@.MEMORY as varchar(22) = '1073741824;1073741824;',

@.NAME as varchar(100) = 'KB887979',

@.VERSION as varchar(20) = 'VALUE DOES NOT EXIST',

@.BUILD as varchar(45) = '1.3',

@.INSTALL_STATUS as varchar(20) = '1',

@.INSTALL_DATE as varchar(21) = '3/31/2005 2:40:34 PM' ,

@.PACKAGE_NAME as varchar(90) = 'CRYSTAL_REPORTS_ACTIVEX_VIEWER_10.0_9.2_8.6_8.5_20.05.08.01_WKS_XP2KNT_BAND_I1^EDE'

-- SET NOCOUNT ON;

SELECT main.MAC, main.CREATED, main.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNER JOIN

hardware ON main.MAC = hardware.MAC INNER JOIN

network ON main.MAC = network.MAC INNER JOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE '%'+@.MAC+'%' AND MAIN.CREATED LIKE '%'+@.CREATED+'%' AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%' AND NETWORK.WORKSTATION_NAME LIKE '%'+@.WORKSTATION_NAME+'%' AND NETWORK.IP_ADDRESS LIKE '%'+@.IP_ADDRESS+'%' AND NETWORK.USER_NAME LIKE '%'+@.USER_NAME+'%' AND NETWORK.OPERATING_SYSTEM LIKE '%'+@.OPERATING_SYSTEM+'%' AND NETWORK.SERVICE_PACK LIKE '%'+@.SERVICE_PACK+'%' AND NETWORK.BAND_VERSION LIKE '%'+@.BAND_VERSION+'%' AND NETWORK.WORKSTATION_OU LIKE '%'+@.WORKSTATION_OU+'%' AND NETWORK.WORKSTATION_OWNER LIKE '%'+@.WORKSTATION_OWNER+'%' AND HARDWARE.MANUFACTURER LIKE '%'+@.MANUFACTURER+'%' AND HARDWARE.MODEL LIKE '%'+@.MODEL+'%' AND HARDWARE.CHASSIS LIKE '%'+@.CHASSIS+'%' AND HARDWARE.SERIAL_NUMBER LIKE '%'+@.SERIAL_NUMBER+'%' AND HARDWARE.PROCESSOR LIKE '%'+@.PROCESSOR+'%' AND HARDWARE.HARD_DRIVE LIKE '%'+@.HARD_DRIVE+'%' AND HARDWARE.HARD_DRIVE_SIZE LIKE '%'+@.HARD_DRIVE_SIZE+'%' AND HARDWARE.MEMORY LIKE '%'+@.MEMORY+'%' AND SOFTWARE.NAME LIKE '%'+@.NAME+'%' AND SOFTWARE.VERSION LIKE '%'+@.VERSION+'%' AND SOFTWARE.BUILD LIKE '%'+@.BUILD+'%' AND SOFTWARE.INSTALL_STATUS LIKE '%'+@.INSTALL_STATUS+'%' AND SOFTWARE.INSTALL_DATE LIKE '%'+@.INSTALL_DATE+'%' AND SOFTWARE.PACKAGE_NAME LIKE '%'+@.PACKAGE_NAME+'%'

Thank you,

Wallace

hi, this is because you were concatenating a datetime variable with a string '%'

change this lines on your where clause

--AND MAIN.CREATED LIKE '%'+@.CREATED+'%'
AND MAIN.CREATED = @.CREATED
--AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'
AND MAIN.MODIFIED = @.MODIFIED|||

CREATED LIKE '%'+@.CREATED+'%'

Wallace,

I guess that I am at a loss about why you are concatenating wildcards to a datetime value.

Is there a particular problem you are attempting to solve by so doing?

Without converstion, you cannot add characters ( '%' ) to a datetime datatype. (What exactly do you hope to accomplish by adding '%' to the datetime?)

I am assuming that Main.Created is a datetime datetype.

In fact, I wonder if any of the above parameters really need to have '%' added to each side of the value...

|||

As soon as it hits the @.CREATED as datetime =.... this is where it throws that error. Any idea on how to convert the datetime data type to a character string?

Are you doing this like to compare parts of dates? Like:

--find rows from June
select *
from dateRow
where convert(varchar(8),datevalue,112) like '_06__'

Here is an article with technique to load the date table: http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1349.entry

|||

This doesn't cause any errors on my server...

Code Snippet

CREATE PROCEDURE dbo.uspPvtSelectCommand
( @.CREATED as datetime = '4/25/2007 8:40:50 AM',
@.MODIFIED as datetime = '5/19/2007 5:05:04 AM'
)
AS
SELECT getdate(), @.Created, @.Modified
GO

I think the error comes from the concatenation of the datetime parameter as I indicated earlier.

LIKE '%'+@.CREATED+'%' AND MAIN.MODIFIED LIKE '%'+@.MODIFIED+'%'

The following fails with such an error...

Code Snippet

EXECUTE dbo.uspPvtSelectCommand

Server: Msg 241, Level 16, State 1, Procedure uspPvtSelectCommand, Line 6
Conversion failed when converting datetime from character string.

|||

Hi Arnie,

I figured I would give everyone who's been so kind to help with a little more information. I have a DataGrid in my vb.net app.

I have since removed the concatenation and changed it so that MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED but still the error.

Any further assistance would be appreciated. Here is what the SP looks like now.

USE platform_validation_tool

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[uspPvtSelectCommand]

@.MAC as varchar(18),

@.CREATED as datetime,

@.MODIFIED as datetime,

@.WORKSTATION_NAME as varchar(13),

@.IP_ADDRESS as varchar(15),

@.USER_NAME as varchar(8),

@.OPERATING_SYSTEM as varchar(25),

@.SERVICE_PACK as varchar(3),

@.BAND_VERSION as varchar(7),

@.WORKSTATION_OU as varchar(200),

@.WORKSTATION_OWNER as varchar(200),

@.MANUFACTURER as varchar(26),

@.MODEL as varchar(40),

@.CHASSIS as varchar(10),

@.SERIAL_NUMBER as varchar(30),

@.PROCESSOR as varchar(100),

@.HARD_DRIVE as varchar(40),

@.HARD_DRIVE_SIZE as varchar(30),

@.MEMORY as varchar(22),

@.NAME as varchar(100),

@.VERSION as varchar(20),

@.BUILD as varchar(45),

@.INSTALL_STATUS as varchar(20),

@.INSTALL_DATE as varchar(21),

@.PACKAGE_NAME as varchar(90)

-- SET NOCOUNT ON;

SELECT main.MAC, getdate(), @.CREATED, @.MODIFIED, hardware.MANUFACTURER, hardware.MODEL, hardware.CHASSIS, hardware.SERIAL_NUMBER, hardware.PROCESSOR,

hardware.HARD_DRIVE, hardware.HARD_DRIVE_SIZE, hardware.MEMORY, network.WORKSTATION_NAME, network.IP_ADDRESS,

network.USER_NAME, network.OPERATING_SYSTEM, network.SERVICE_PACK, network.BAND_VERSION, network.WORKSTATION_OU,

network.WORKSTATION_OWNER, software.MAC, software.NAME, software.VERSION, software.BUILD, software.INSTALL_STATUS,

software.INSTALL_DATE, software.PACKAGE_NAME

FROM main INNER JOIN

hardware ON main.MAC = hardware.MAC INNER JOIN

network ON main.MAC = network.MAC INNER JOIN

software ON main.MAC = software.MAC

WHERE MAIN.MAC LIKE '%'+@.MAC+'%' AND MAIN.CREATED = @.CREATED AND MAIN.MODIFIED = @.MODIFIED AND NETWORK.WORKSTATION_NAME LIKE '%'+@.WORKSTATION_NAME+'%' AND NETWORK.IP_ADDRESS LIKE '%'+@.IP_ADDRESS+'%' AND NETWORK.USER_NAME LIKE '%'+@.USER_NAME+'%' AND NETWORK.OPERATING_SYSTEM LIKE '%'+@.OPERATING_SYSTEM+'%' AND NETWORK.SERVICE_PACK LIKE '%'+@.SERVICE_PACK+'%' AND NETWORK.BAND_VERSION LIKE '%'+@.BAND_VERSION+'%' AND NETWORK.WORKSTATION_OU LIKE '%'+@.WORKSTATION_OU+'%' AND NETWORK.WORKSTATION_OWNER LIKE '%'+@.WORKSTATION_OWNER+'%' AND HARDWARE.MANUFACTURER LIKE '%'+@.MANUFACTURER+'%' AND HARDWARE.MODEL LIKE '%'+@.MODEL+'%' AND HARDWARE.CHASSIS LIKE '%'+@.CHASSIS+'%' AND HARDWARE.SERIAL_NUMBER LIKE '%'+@.SERIAL_NUMBER+'%' AND HARDWARE.PROCESSOR LIKE '%'+@.PROCESSOR+'%' AND HARDWARE.HARD_DRIVE LIKE '%'+@.HARD_DRIVE+'%' AND HARDWARE.HARD_DRIVE_SIZE LIKE '%'+@.HARD_DRIVE_SIZE+'%' AND HARDWARE.MEMORY LIKE '%'+@.MEMORY+'%' AND SOFTWARE.NAME LIKE '%'+@.NAME+'%' AND SOFTWARE.VERSION LIKE '%'+@.VERSION+'%' AND SOFTWARE.BUILD LIKE '%'+@.BUILD+'%' AND SOFTWARE.INSTALL_STATUS LIKE '%'+@.INSTALL_STATUS+'%' AND SOFTWARE.INSTALL_DATE LIKE '%'+@.INSTALL_DATE+'%' AND SOFTWARE.PACKAGE_NAME LIKE '%'+@.PACKAGE_NAME+'%'

Thanks and Sincerely,

Wallace

|||

Wallace,

I need you to clarify.

If I understand this correctly, you are passing into the stored procedure the checkbox values for each of the parameters listed. Is that correct?

And what checkboxes are checked determines what data is expected?

OR,

Is there actual values in the input parameters, AND the presence of a check indicated to use that value in the WHERE clause...

|||That's correct, I want to pass values, being read from the database, into the sql sp parameters, based on which checkboxes are checked.

The values will be read from the Database, passed into the parameter, and joined via the SP into a DataGrid. I have a separate vb.net sub routine that will take all of this and export to excel.

Thanks,

Wally

|||

I'm trying to understand and help, but I'm still confused. You replied "That's correct..." to two diametrically opposed questions.

Do you wish to collect multiple rows of data from the database and display that data in the DataGrid?

How do you determine what should be displayed?

Are all columns always returned and displayed?

Is this a search routine where you pass in some search criteria, hence the input parameters?

What is the application code that calls the stored procedure? (Please post.)

|||

Arnie,

We have a database that has 4 tables, 25 columns of data to pull from. Software/Hardware data is pulled from the PC's and stored on this database.

The vb.net application I'm creating will connect to a database called "platform_validation_tool" running on SQL 2005, and

will use an SQLConnection, SQLDataAdapter and DataSet to mirror the tables.

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

etc, and so on.,

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName".

Let me know if it needs further explanation. I've only been in programming for 3 months. Thank you for your patience.

Wally

|||hi,

if your doing b. does the user need to input the time for the created and modified date?|||How about posting some of the vb.net code that invokes the sp?
SQLDataSource definition, etc.|||

DaleJ,

My code is at work and until I return there, simplest way I can put it is the routine goes like this:

The checkboxes are just inside of a GroupBox on a separate Form and not imbedded within or next to the DataGrid.

If checkbox1.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(1).Value = SourceColumn

Else checkbox1.checked = False

End If

If checkbox2.checked = True Then

SqlDA_SinglePkgName.SelectCommand.Parameters.Item(2).Value = SourceColumn

Else checkbox2.checked = False Then

End If

Item(1), Item(2), item(3), etc. is the logical order of the column parameters, i.e. Item(0) = @.ReturnValue, Item(1) = @.MAC, Item(2) = @.CREATED, Item(3) = @.MODIFIED, etc.

Finally, after the checkboxes have been evaluated as checked or unchecked, it will run the stored procedure on all the checkboxes whose boolean is True with the Fill method below .

SqlDA_SinglePkgName.Fill(WkstnAndSoftwareVerDS1.uspSelectAnyPkgName) --> This is the line of code that calls the stored procedure "uspSelectAnyPkgName" and fills the DataSet.

It may seem elementary but it's the easiest way for me to start out learning to code until I get a couple of vb.net classes under my belt.

Wallace

|||

Also, rigorously following good formatting principles will make your code easier to read, and easier to maintain.

Code Snippet

SET NOCOUNT ON;

|||

Hey Arnie,

Thank you for the tips. I'll be sure to apply them as a newcomer.

Msg 306, Level 16, State 1, Procedure uspPvtSelectCommand, Line 34

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I'm still trying to find some material on converting this date and time string value to a datetime datatype value.

Any other suggestions are most welcome.

Wally

Monday, March 12, 2012

RDLC Object datasource can't get System.Web through webform reportviewer

I am running an RDLC with a object datasource. In the object I try to
access System.Web.Httphandler to get the session, but it is nothing.
The reportviewer running my rdlc from a webform, and it seems as though
there is a setting or something to enable the object datasource access
to the session but I cannot figure it out.
Can someone help?Currently I get this error when it tries to access
System.Web.Httphandler.Current
Microsoft.Reporting.WebForms.AspNetSessionExpiredException|||lotta typos today, I am accessing:
System.Web.HttpContext.Current.Session("PhysicalWebPath")
and Current is Nothing|||This is not too surprising. The web control uses web services. I am not sure
it has a physical web path. What are you trying to determine?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <gfricke@.gmail.com> wrote in message
news:1132681439.261358.193190@.g43g2000cwa.googlegroups.com...
> lotta typos today, I am accessing:
> System.Web.HttpContext.Current.Session("PhysicalWebPath")
> and Current is Nothing
>|||Just trying to grab a session value, so if its a web service that the
web control uses, is there an EnableSession true setting I cause use in
my object datasource like I do in a standard webservice to share the
session?|||Sorry, can't help you there. I have been using the winform control.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <gfricke@.gmail.com> wrote in message
news:1132689249.764262.282850@.f14g2000cwb.googlegroups.com...
> Just trying to grab a session value, so if its a web service that the
> web control uses, is there an EnableSession true setting I cause use in
> my object datasource like I do in a standard webservice to share the
> session?
>|||On a side note, can I see these webservices? Like does it create a
asmx file or something I can connect to in IE for the object
datasources the reportviewer communicates with?|||The webservices are documented and you can use them yourself. Prior to 2005
people would (and still can) roll there own, not using the control. The
previous control (really a sample) used URL integration and so had some
difficulties.
In Books Online look for web services and you will see lots of
documentation.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <gfricke@.gmail.com> wrote in message
news:1132690648.480837.290540@.o13g2000cwo.googlegroups.com...
> On a side note, can I see these webservices? Like does it create a
> asmx file or something I can connect to in IE for the object
> datasources the reportviewer communicates with?
>

Monday, February 20, 2012

Rate calculation with matrix reports, How?

I have a report like this, and I would need to implement drilldown on
both column group and row group, and i am running into problems:
2003
Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
Total_Rate
West 10 10% 20 20% 50 50% 20 20% | 100
100%
East 20 10% 30 15% 20 10% 130 65% | 200
100%
North 20 20% 20 20% 40 40% 20 20% | 100
100%
South 30 30% 20 20% 10 10% 20 20% | 100
100%
----
Total 80 16% 90 18% 120 24% 190 38% 500
100%
I can do sum on the numbers, but the rate calculation is difficult.
Does anyone know how to do this?You can use the scope argument to aggregate functions to define a scope over
which to calculate a total.
For a percent-of-total calculation like you describe, you would want
something like this:
=Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
where "matrix1_Region" is the name of your row group
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Nick" <deadlocklegend@.gmail.com> wrote in message
news:313b74d.0407261518.4e5582b6@.posting.google.com...
> I have a report like this, and I would need to implement drilldown on
> both column group and row group, and i am running into problems:
> 2003
> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> Total_Rate
> West 10 10% 20 20% 50 50% 20 20% | 100
> 100%
> East 20 10% 30 15% 20 10% 130 65% | 200
> 100%
> North 20 20% 20 20% 40 40% 20 20% | 100
> 100%
> South 30 30% 20 20% 10 10% 20 20% | 100
> 100%
> ----
> Total 80 16% 90 18% 120 24% 190 38% 500
> 100%
> I can do sum on the numbers, but the rate calculation is difficult.
> Does anyone know how to do this?|||Where exactly within the matrix would you put the
calculation? Thanks.
>--Original Message--
>You can use the scope argument to aggregate functions to
define a scope over
>which to calculate a total.
>For a percent-of-total calculation like you describe,
you would want
>something like this:
>=Sum(Fields!Sales.Value)/Sum(Fields!
Sales.Value,"matrix1_Region")
>where "matrix1_Region" is the name of your row group
>--
>This post is provided 'AS IS' with no warranties, and
confers no rights. All
>rights reserved. Some assembly required. Batteries not
included. Your
>mileage may vary. Objects in mirror may be closer than
they appear. No user
>serviceable parts inside. Opening cover voids warranty.
Keep out of reach of
>children under 3.
>"Nick" <deadlocklegend@.gmail.com> wrote in message
>news:313b74d.0407261518.4e5582b6@.posting.google.com...
>> I have a report like this, and I would need to
implement drilldown on
>> both column group and row group, and i am running into
problems:
>> 2003
>> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4
Q4_Rate | Total
>> Total_Rate
>> West 10 10% 20 20% 50 50% 20
20% | 100
>> 100%
>> East 20 10% 30 15% 20 10% 130
65% | 200
>> 100%
>> North 20 20% 20 20% 40 40% 20
20% | 100
>> 100%
>> South 30 30% 20 20% 10 10% 20
20% | 100
>> 100%
>> ----
--
>> Total 80 16% 90 18% 120 24% 190
38% 500
>> 100%
>> I can do sum on the numbers, but the rate calculation
is difficult.
>> Does anyone know how to do this?
>
>.
>|||As the value of the second data cell.
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Terry" <anonymous@.discussions.microsoft.com> wrote in message
news:53f101c47417$66c96140$a301280a@.phx.gbl...
> Where exactly within the matrix would you put the
> calculation? Thanks.
>
> >--Original Message--
> >You can use the scope argument to aggregate functions to
> define a scope over
> >which to calculate a total.
> >For a percent-of-total calculation like you describe,
> you would want
> >something like this:
> >
> >=Sum(Fields!Sales.Value)/Sum(Fields!
> Sales.Value,"matrix1_Region")
> >where "matrix1_Region" is the name of your row group
> >
> >--
> >This post is provided 'AS IS' with no warranties, and
> confers no rights. All
> >rights reserved. Some assembly required. Batteries not
> included. Your
> >mileage may vary. Objects in mirror may be closer than
> they appear. No user
> >serviceable parts inside. Opening cover voids warranty.
> Keep out of reach of
> >children under 3.
> >"Nick" <deadlocklegend@.gmail.com> wrote in message
> >news:313b74d.0407261518.4e5582b6@.posting.google.com...
> >> I have a report like this, and I would need to
> implement drilldown on
> >> both column group and row group, and i am running into
> problems:
> >>
> >> 2003
> >> Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4
> Q4_Rate | Total
> >> Total_Rate
> >> West 10 10% 20 20% 50 50% 20
> 20% | 100
> >> 100%
> >> East 20 10% 30 15% 20 10% 130
> 65% | 200
> >> 100%
> >> North 20 20% 20 20% 40 40% 20
> 20% | 100
> >> 100%
> >> South 30 30% 20 20% 10 10% 20
> 20% | 100
> >> 100%
> >> ----
> --
> >> Total 80 16% 90 18% 120 24% 190
> 38% 500
> >> 100%
> >>
> >> I can do sum on the numbers, but the rate calculation
> is difficult.
> >> Does anyone know how to do this?
> >
> >
> >.
> >|||Thanks much. That worked.
But the requirement has changed slightly because sometimes the Quarter
numbers don't add up to the "supposed" yearly number for several
reasons. Instead of dividing by the sum of all quarter numbers, I
need to get from proc a yearly number and calculate the percentage by
the yearly number and then tally up. I am running into problems
because when I use sum(Q_No)/First(Year_No) the rates work right, but
the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
right but the individual rates are wrong.
Any help very much appreciated.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> You can use the scope argument to aggregate functions to define a scope over
> which to calculate a total.
> For a percent-of-total calculation like you describe, you would want
> something like this:
> =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> where "matrix1_Region" is the name of your row group
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Nick" <deadlocklegend@.gmail.com> wrote in message
> news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > I have a report like this, and I would need to implement drilldown on
> > both column group and row group, and i am running into problems:
> >
> > 2003
> > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > Total_Rate
> > West 10 10% 20 20% 50 50% 20 20% | 100
> > 100%
> > East 20 10% 30 15% 20 10% 130 65% | 200
> > 100%
> > North 20 20% 20 20% 40 40% 20 20% | 100
> > 100%
> > South 30 30% 20 20% 10 10% 20 20% | 100
> > 100%
> > ----
> > Total 80 16% 90 18% 120 24% 190 38% 500
> > 100%
> >
> > I can do sum on the numbers, but the rate calculation is difficult.
> > Does anyone know how to do this?|||If you need a different calculation in the total cells than in the detail
cells, that's where the InScope function comes in.
You can do something like this:
=iif(InScope("matrix1_Quarter"),Calculation1,Calculation2)
In your case, it would be:
=iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"Nick" <deadlocklegend@.gmail.com> wrote in message
news:313b74d.0407271256.179a0f98@.posting.google.com...
> Thanks much. That worked.
> But the requirement has changed slightly because sometimes the Quarter
> numbers don't add up to the "supposed" yearly number for several
> reasons. Instead of dividing by the sum of all quarter numbers, I
> need to get from proc a yearly number and calculate the percentage by
> the yearly number and then tally up. I am running into problems
> because when I use sum(Q_No)/First(Year_No) the rates work right, but
> the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
> right but the individual rates are wrong.
> Any help very much appreciated.
> "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> > You can use the scope argument to aggregate functions to define a scope
over
> > which to calculate a total.
> > For a percent-of-total calculation like you describe, you would want
> > something like this:
> >
> > =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> > where "matrix1_Region" is the name of your row group
> >
> > --
> > This post is provided 'AS IS' with no warranties, and confers no rights.
All
> > rights reserved. Some assembly required. Batteries not included. Your
> > mileage may vary. Objects in mirror may be closer than they appear. No
user
> > serviceable parts inside. Opening cover voids warranty. Keep out of
reach of
> > children under 3.
> > "Nick" <deadlocklegend@.gmail.com> wrote in message
> > news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > > I have a report like this, and I would need to implement drilldown on
> > > both column group and row group, and i am running into problems:
> > >
> > > 2003
> > > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > > Total_Rate
> > > West 10 10% 20 20% 50 50% 20 20% | 100
> > > 100%
> > > East 20 10% 30 15% 20 10% 130 65% | 200
> > > 100%
> > > North 20 20% 20 20% 40 40% 20 20% | 100
> > > 100%
> > > South 30 30% 20 20% 10 10% 20 20% | 100
> > > 100%
> > > ----
> > > Total 80 16% 90 18% 120 24% 190 38% 500
> > > 100%
> > >
> > > I can do sum on the numbers, but the rate calculation is difficult.
> > > Does anyone know how to do this?|||thanks Chris.
=iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
didn't work, but this did,
=iif(InScope("matrix1_Region"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
However, there is a problem when the year was collapsed, the sum of
yearly number gets multiplied by the number of Q, which is 4
=iif(InScope("matrix1_Region"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value) *
CountDistinct(Fields!Q_No.Value))
Thanks a lot for your help.
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message news:<OLHBCbCdEHA.592@.TK2MSFTNGP11.phx.gbl>...
> If you need a different calculation in the total cells than in the detail
> cells, that's where the InScope function comes in.
> You can do something like this:
> =iif(InScope("matrix1_Quarter"),Calculation1,Calculation2)
> In your case, it would be:
> =iif(InScope("matrix1_Quarter"),Sum(Fields!Q_No.Value)/First(Fields!Year_No.
> Value), Sum(Fields!Q_No.Value)/Sum(Fields!Year_No.Value))
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "Nick" <deadlocklegend@.gmail.com> wrote in message
> news:313b74d.0407271256.179a0f98@.posting.google.com...
> > Thanks much. That worked.
> >
> > But the requirement has changed slightly because sometimes the Quarter
> > numbers don't add up to the "supposed" yearly number for several
> > reasons. Instead of dividing by the sum of all quarter numbers, I
> > need to get from proc a yearly number and calculate the percentage by
> > the yearly number and then tally up. I am running into problems
> > because when I use sum(Q_No)/First(Year_No) the rates work right, but
> > the total doesn't. When I use sum(Q_No)/Sum(year_no) the total is
> > right but the individual rates are wrong.
> >
> > Any help very much appreciated.
> >
> > "Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
> news:<u9Notg2cEHA.3512@.TK2MSFTNGP12.phx.gbl>...
> > > You can use the scope argument to aggregate functions to define a scope
> over
> > > which to calculate a total.
> > > For a percent-of-total calculation like you describe, you would want
> > > something like this:
> > >
> > > =Sum(Fields!Sales.Value)/Sum(Fields!Sales.Value,"matrix1_Region")
> > > where "matrix1_Region" is the name of your row group
> > >
> > > --
> > > This post is provided 'AS IS' with no warranties, and confers no rights.
> All
> > > rights reserved. Some assembly required. Batteries not included. Your
> > > mileage may vary. Objects in mirror may be closer than they appear. No
> user
> > > serviceable parts inside. Opening cover voids warranty. Keep out of
> reach of
> > > children under 3.
> > > "Nick" <deadlocklegend@.gmail.com> wrote in message
> > > news:313b74d.0407261518.4e5582b6@.posting.google.com...
> > > > I have a report like this, and I would need to implement drilldown on
> > > > both column group and row group, and i am running into problems:
> > > >
> > > > 2003
> > > > Q1 Q1_Rate Q2 Q2_Rate Q3 Q3_Rate Q4 Q4_Rate | Total
> > > > Total_Rate
> > > > West 10 10% 20 20% 50 50% 20 20% | 100
> > > > 100%
> > > > East 20 10% 30 15% 20 10% 130 65% | 200
> > > > 100%
> > > > North 20 20% 20 20% 40 40% 20 20% | 100
> > > > 100%
> > > > South 30 30% 20 20% 10 10% 20 20% | 100
> > > > 100%
> > > > ----
> > > > Total 80 16% 90 18% 120 24% 190 38% 500
> > > > 100%
> > > >
> > > > I can do sum on the numbers, but the rate calculation is difficult.
> > > > Does anyone know how to do this?

Friday, March 30, 2012

Reading a record without placing a lock

Re-adding noded to cluster

Read_committed_snapshot

Wednesday, March 28, 2012

Read this first! FAQ for DBForums Microsoft SQL Server forum

Read the profiler output.

Monday, March 26, 2012

Read the profiler output.

Read only DB when moving database (error 5105 "device activation...")

Read Only Cursor

Friday, March 23, 2012

Read from tables when another transactions isolationlevel is ReadCommited

Wednesday, March 21, 2012

READ COMMITTED SNAPSHOT ON causes performance degradation

Read a Database Datetime (datatype) value and pass it into a Parameter

Read a Database Datetime (datatype) value and pass it into a Parameter

Monday, March 12, 2012

RDLC Object datasource can't get System.Web through webform reportviewer

Monday, February 20, 2012

Rate calculation with matrix reports, How?

Raster Catalog

Blog Archive

About Me