|
I am looping through a recordset, and I am both creating and destroying a QueryDef with what I believe to be a pass-through query. The records get processed correctly most of the time but then I get this abberant Error #3218 which states:
"DAO.QueryDefs. Could not update. Currently locked."
What does this mean and how do I prevent it from occurring? The application works correctly most of the time, but this error occurs intermittently, thus, it is the hardest cause to identify. Why is it locked up, and what is locked up?
|
|
|
|
|
Error 3218 "Could not update; currently locked."
This error occurs when a user tries to save a record that is locked by another user.
To handle this error, program your solution to wait for a short period of time, and then try to save the record again. Or, you can display a message that explains the problem and give users the opportunity to try the operation again.
|
|
|
|
|
I am trying tie a reporting services data source with a custom report item and can't seem to find any helpful samples/documentation anywhere. This task is taking much longer than it should have at this point and I could really use some help if there's anyone who has done this before.
What I want to do is create a series designer properties which allow me to :
1) select a data source from a list of available sources which have been defined for the report
2) select fields from the data source and assign them to designer properties
3) navigate the CustomReportItem.CustomData (is that the correct object?) object using the field names which were selected in step 2 (above) to retrieve the runtime values and bind them to my custom report item.
|
|
|
|
|
A) In the CustomReportItemDesigner class (your custom class which inherits from it as a base), override the InitializeNewComponent() method like this:
<br />
public override void InitializeNewComponent()<br />
{<br />
base.InitializeNewComponent();<br />
<br />
CustomData = new CustomData();<br />
<br />
<br />
CustomData.DataRowGroupings = new DataRowGroupings();<br />
CustomData.DataRowGroupings.DataGroupings = new List<DataGrouping>(1);<br />
CustomData.DataRowGroupings.DataGroupings.Add(new DataGrouping());<br />
CustomData.DataRowGroupings.DataGroupings[0].Static = true;<br />
<br />
CustomData.DataColumnGroupings = new DataColumnGroupings();<br />
CustomData.DataColumnGroupings.DataGroupings = new List<DataGrouping>(1);<br />
CustomData.DataColumnGroupings.DataGroupings.Add(new DataGrouping());<br />
CustomData.DataColumnGroupings.DataGroupings[0].Static = true;<br />
<br />
CustomData.DataRows = new List<DataRow>(1);<br />
CustomData.DataRows.Add(new DataRow());<br />
CustomData.DataRows[0].Add(new DataCell());<br />
<br />
CustomData.DataRows[0][0].Add(new DataValue("Label", String.Empty));<br />
CustomData.DataRows[0][0].Add(new DataValue("X", String.Empty));<br />
CustomData.DataRows[0][0].Add(new DataValue("Y", String.Empty));<br />
}<br />
B) Define custom designer properties to set the expressions for the DataValuesCollection (only showing one property here to keep things shorter)
<br />
[Browsable(true), Category("Data")]<br />
public string XValue<br />
{<br />
get<br />
{<br />
if (CustomData.DataRows.Count > 0 && CustomData.DataRows[0].Count > 0)<br />
return GetDataValue(CustomData.DataRows[0][0], "X");<br />
else<br />
return "X Coordinate";<br />
}<br />
set<br />
{<br />
SetDataValue(CustomData.DataRows[0][0], "X", value);<br />
}<br />
}<br />
<br />
<br />
private string GetDataValue(DataCell cell, string name)<br />
{<br />
foreach (DataValue value in cell)<br />
if (value.Name == name)<br />
return value.Value;<br />
return null;<br />
}<br />
<br />
private void SetDataValue(DataCell cell, string name, string expression)<br />
{<br />
foreach (DataValue value in cell)<br />
if (value.Name == name)<br />
{<br />
value.Value = expression;<br />
return;<br />
}<br />
DataValue datavalue = new DataValue(name, expression);<br />
cell.Add(datavalue);<br />
}<br />
C) Make sure your designer provides a property to set the data set name (CustomData.DataSetName).
D) Your CustomReportItem class can then read the data like this:
<br />
private void ReadCustomData()<br />
{ <br />
DataCellCollection rows = m_CRI.CustomData.DataCells;<br />
<br />
int xpos = -1, ypos = -1, labelpos = -1;<br />
<br />
for (int field = 0; field < rows[0, 0].DataValues.Count; field++)<br />
{<br />
switch (rows[0, 0].DataValues[field].Name)<br />
{<br />
case "X":<br />
xpos = field;<br />
break;<br />
case "Y":<br />
ypos = field;<br />
break;<br />
case "Label":<br />
labelpos = field;<br />
break;<br />
}<br />
}<br />
<br />
for (int row = 0; row < rows.RowCount; row++)<br />
{<br />
DataValueCollection values = rows[row, 0].DataValues;<br />
<br />
double x = Convert.ToDouble(values[xpos].Value);<br />
double y = Convert.ToDouble(values[ypos].Value);<br />
string label = (string)values[labelpos].Value;<br />
}<br />
}<br />
NOTE: This assumes the data in your data set is already grouped and the report does not need to perform any grouping on the data in your dataset.
|
|
|
|
|
I'm Working with Asp.Net with C#.. & I'm Generating Reports in SSRS-2005..
Till Now I'm Generating Reports in SSRS-2005 with Stored Procedure.. in Which I'm Generating Reports for One Particular User Details(ie I wrote Stored Procedure for Getting One Users Details)..
Now I Want to Generate the Reports for Different Users DYNAMICALLY (Here, I wrote another Stored Procedure which contains UsersID's as Input Parameters.. when we give the User ID then that Particular User's Details will be Executed).. Here I want to Place one Dropdownlist Control which contains All the User's ID's.. After selecting the Particular User ID, I Want to Generate that User's Details REPORT..
I done this as like that I generated Report for One User(ie as like First Report).. But it is Not Working..
Please give me the Suggestions for this...
Thank You..
|
|
|
|
|
A good while ago there was a lot of fuss made about not using this prefix. Is this still relevant?
|
|
|
|
|
Hi,
Our head SQL guy insists on using the 'sp' as a prefix ... or at the very least maybe two characters prefixing the 'sp' prefix related to the project.
it just makes sense.
Jammer
Going where everyone here has gone before! 
My Blog
|
|
|
|
|
My motivation is somewhat unusual: I'm prefixing all application procedures with ap and all system procedures with sp, where application procedures access data that changes during normal tasks, and system procedures access data that only changes during system configuration.
|
|
|
|
|
Jammer wrote: it just makes sense.
Does it? If so, why?
Isn't Hungarian notation[^] a bit passe these days? In Management Studion, stored procedures are listed under their own section so you don't need a naming convention to distinguish them from other objects. Also, stored procedures have a different object type in the SQL Server metadata so you can find all instances of them without having to search on a particular prefix.
Naming stored procedures based on what they do, not what they are seems the most sensible approach.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Agreed. I find hungarian notation just unnecessary ugly keystrokes. And I don't like putting all SQL statements in upper case, but I seem to be alone in my dislike of that.
Regards,
Rob Philpott.
|
|
|
|
|
This limitation has nothing to do with Hungarian Notation, and everything to do with maximising SQL Server performance. See my post below.
|
|
|
|
|
This article relates to a problem caused by prefixing stored procedure names with 'sp_'. The OP was talking about the prefix 'sp', which presumably doesn't cause the same problem.
My issue is with the practice of using unnecessary naming conventions. Prefixing a stored procedure with 'sp' adds no value. You can tell by the context in which it is used that a database object is a stored procedure. What is useful is to choose a name which indicates the its basic purpose.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
As I said above, yy prefix is not to indicate that it is a stored procedure, but to indicate a system procedure, one which access data not changed by the business processes of the application, such as lists for drop down etc. compared to 'transactional' data inserted by application procedures, prefixed with ap. My initial idea was these prefixes themselves prefixed with an acronym for the application name, but hosting objects for different applications in the same database is not one of my good practices. Databases are relatively cheap, and and cohesive application should have its own database.
|
|
|
|
|
Actually, the OP was asking about the fuss over the sp prefix, which was the fuss over using sp_. It may seem like splitting hairs, but there was never a problem with just using sp (and I don't think anybody ever raised a fuss over it).
pmarfleet wrote: My issue is with the practice of using unnecessary naming conventions
Agreed. Mindless mantra is annoying.
|
|
|
|
|
I think what you may be referring to is that if you prefix a stored procedure with 'sp_' SQL Server will look up all the extended stored procedures first to see if there is a match.
Apparently on large OLAP/OLTP systems this can result in a significant use of processor power.
I have named my stored procedures 'sp_'and am in the process of changing them and encouraging others not to use them.
I think at the end of the day unless your database is having to process tens to hundreds of millions of records via a stored procedure you should be ok with using 'sp_' as the prefix.
This is just an opinion and as you may discover DBA's tend to be very opinionated (except they see themselves as being 'right'  ).
You always pass failure on the way to success.
|
|
|
|
|
Yup. There is a performance penalty when using them in a database other than Master. There's a technical reason that this happens, which you can find here[^]. As far as I can tell, even in SQL 2008, this is still the case.
|
|
|
|
|
Excellent. I like that, let there be no dispute that this is bad pratice.
Regards,
Rob Philpott.
|
|
|
|
|
call your procs whatever you like, but I think it's dumb. Of course is a sp. SQL Server management studio does that grouping for you.
Christian Graus
Please read this if you don't understand the answer I've given you
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
My prefix sp is not to indicate that it's a stored procedure, but one of my system procedures. See my post above.[^] The third one down.
|
|
|
|
|
hi,
i'm using VS 2005 And SqlServer Express 2005,
i've build a nice app that conects to an sql server and reads info displays
changes the data and so on
the problem that i'm facing is how to tell App2 and App3 that App1 has made
cahanges in the database, i've tried a Thread that pulls Data and Checks if a change had been made
but it's hell, is there any other way
i Heared of Trigger Proc that can be placed in the server will that report to my App's about Changes
I'm Confused from all the Knowledge in the Subject of ADO.Net
THANKS in Advance
Have Fun
Never forget it
|
|
|
|
|
You are talking about Notification, check BOL although I'm not sure if it works on Express. Try googling for SQL 2005 query notification.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
THANKS,
it's not working in Express
Any Other IDEAS
Does A Thread that will pull info and will pporivde an Equal Method ti Compare The Dataset
is A Good IDEA ?
Have Fun
Never forget it
|
|
|
|
|
You could put a column on the database with the last changed datetime and check that in apps2 and 3 (on a timer maybe, keep last changed date between polls), but remember that sql server is only accurate to a third of a millisecond so if you are doing rapid inserts you could get multiple records per millisecond.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Sound Nice
i've got around and created a Thread that will
Fill a "Fake" DataSet and then some Checks to see what has Changed
The Thread will Fill the fake Dataset every 500 mili
because changes made by the user usualy take a lot more
it works nice and the importent thing is that only 10 users MAX will
work with the UI so it works great
THANKS anyway
Have Fun
Never forget it
|
|
|
|
|
Hi,
Please refresh my memory...
What the difference is between using & or + to join fields with text.
SELECT FirstName + ' ' + LastName AS FullName FROM table
or
SELECT FirstName & ' ' & LastName AS FullName FROM table
thanks,
Ron
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
