|
CREATE PROCEDURE sp_GetEZQueryList @@sUser as nchar(500), @@sSupervisor as char(1), @@sAllowed as varchar(8000) AS
--set @@sUser = REPLACE(@@sUser, '''''','''''')
IF @@sSupervisor <> 'Y'
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
WHERE (EzqUserName=@@sUser)
OR (EzqUserName='SYSTEM' AND Cast(EzqFlxID as varchar(10)) IN (select * FROM intlist_to_tbl(@@sAllowed)))
ELSE
SELECT
EzqDateMod, EzqFlxID, EzqUserName, EzqName, EzqData, EzqDescrip
FROM EZQuery
GO
The SP is retrieving @@sUser from VB and its getting the value Do'Sullivan.
When I ran a trace I found it is executing the SP like this
exec sp_getezquerylist 'DO' + CHAR(39) + 'SULLIVAN', 'n', '100'
Think + CHAR(39) + is causing the error
Thanks
|
|
|
|
|
|
Little Debugging..
CREATE PROCEDURE sp_GetEZQueryList
(
@sUser AS nchar(500) ,
@sSupervisor AS char(1) ,
@sAllowed AS varchar(8000)
)
AS
IF @sSupervisor <> 'Y'
SELECT
EzqDateMod ,
EzqFlxID ,
EzqUserName ,
EzqName ,
EzqData ,
EzqDescrip
FROM
EZQuery
WHERE
( EzqUserName = @sUser )
OR
( EzqUserName = 'SYSTEM' )
ELSE
SELECT
EzqDateMod ,
EzqFlxID ,
EzqUserName ,
EzqName ,
EzqData ,
EzqDescrip
FROM
EZQuery
GO
exec sp_getezquerylist 'DO''SULLIVAN', 'N', '100'
Executes fine, without the IN clase to look up
The source of your issues appear to be in this function (I assume its a function based on the nomenclature) intlist_to_tbl. If intlist_to_tbl is really a function why are you sending that data to the db, and converting it instead of storing it in the db?
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
I'm fairly new tyo SSRS, so bear with me...
I created a report that is getting its data from a stored procedure. When I run the stored procesure in the data tab it is returning 68 rows, yet only 1 rows shows up on the report in preview, and, it's the last row in the dataset.
If I export the data as a CSV to the desktop, there is only one row in the data.
I have no clue how to debug this. Could use some help.
Thanks
Everything makes sense in someone's mind
modified on Sunday, April 10, 2011 4:49 PM
|
|
|
|
|
I have run into this a couple of times, look for a {file name}.rdl.data file and delete it it. That contains a cached set of data for the report.
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
hi everybody,
I have a big problem with update statement, let me mention the situation by exact script and results!
first create trans table=>
create table trans(
id bigint identity(1,1) not null,
price decimal(18,0) null,
relatedid bigint null)
Then fill it by this script
Insert into trans (price,relatedid)values(100,NULL)
Insert into trans (price,relatedid)values(100,NULL)
Insert into trans (price,relatedid)values(500,NULL)
Insert into trans (price,relatedid)values(1,NULL)
Insert into trans (price,relatedid)values(1,3)
Now run this query!
Update trans
set price=(case when relatedid is null then (select avg(price) from trans where id=<t.id)
else (select top(1) price from trans where id=t.relatedid) end)
from trans t
After execution of query I expect this result:
id-----price-----relatedid
1 100 NULL
2 300 NULL
3 200 NULL
4 200 3 <======== realy expected!!!!
But this is the result!!!
id-----price-----relatedid
1 100 NULL
2 300 NULL
3 200 NULL <======= #3 row
4 1 3 <===== wrong value, i mean this is the value before update
row with id 3 has been updated before but update doesnt know this!!!!!
WHAT I NEED i need to get expected result from this execution!!!! plzzzzzzzzzzz
modified on Tuesday, April 12, 2011 10:34 AM
|
|
|
|
|
It sounds like you have processing half in your database and half in you application. Change the design to move all the processing into one platform or make sure the database returns the results from the update (possibly with a simple select from updated record changing the return type to a datatable)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
So are you saying that the function doesn't see the refreshed situation. If that's the case, perhaps you're running the update in a different transaction or you are feeding 'old' values as parameters to the function.
The function should be able to fetch the current data from the database even if the transaction is still going on as long as you use the same transaction.
|
|
|
|
|
plz take look at edited message...
|
|
|
|
|
Answerred the later post.
|
|
|
|
|
how to select maximum number from table using groupby keyword with two column name
|
|
|
|
|
A little example data would help the answering, but basically you group by like:
SELECT MAX(NumberField),
Field1,
Field2
FROM YourTable
GROUP BY Field1,
Field2
For info and examples, see: GROUP BY (Transact-SQL)[^]
|
|
|
|
|
You have already posted this in the Q&A section.
Try avoiding cross-posts.
The funniest thing about this particular signature is that by the time you realise it doesn't say anything it's too late to stop reading it.
|
|
|
|
|
Hello,
We are in the process of copying over our live database over to a new more robust server.
Is there a script/utility to create all the database objects?
Thanks!
|
|
|
|
|
|
The Database Publishing Wizard[^].
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
If you are just moving the database do backup/restore or detach/attach. These create the tables for you.
|
|
|
|
|
how can i sort data base in c#
ty
|
|
|
|
|
ace300 wrote: how can i sort data base in c#
what do you mean by sorting a database? a database holds tables, in no particular order. And each table holds rows, in no particular order. Getting data out of a database in some sorted way is to be handled by the individual query, not by the database overall.
Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
|
|
|
|
|
I concur with Luc. You don't "sort" a database, you specify that you want the returned data from a query to be "sorted".
General SQL syntax:
Select Field1, Field2
from Table1
Where ...
Order by Field1
The important thing here is the "order by" clause of the select query.
|
|
|
|
|
Do you mean Indexing? Google it!
♫ 99 little bugs in the code,
99 bugs in the code
We fix a bug, compile it again
101 little bugs in the code ♫
|
|
|
|
|
We have a legacy Access DB that is located on a file server. We use Excel VBA to manipulate the database and are having performance issues.
There is a query (stored procedure) in the DB that zeros out data in a large table (~250,000 rows) in preparation for the day's P/L. If I log directly into the DB, I can run the procedure in a second or two. If I execute the procedure in VBA, it takes several minutes and creates a 68 MB temporary file on the local computer.
I tried begin/commit/rollback transactions but that had no effect. I also tried client and server side cursors. Very little difference. I'm guessing it is saving undo information locally. Is there a switch to prevent this?
Is there something I am missing here?
Thanks very much,
Mark Jackson
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=S:\Database\database.mdb;" & _
"Jet OLEDB:System Database=s:\database\security.mdw;" & _
"User ID=xxx;Password=xxx;"
Set conn = New ADODB.Connection
conn.ConnectionString = strCnn
conn.Open
conn.CommandTimeout = 0
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandTimeout = 0
' Set Active values to NULL
cmd.CommandType = adCmdStoredProc
strCmd = "UpdateActiveSetNullValues"
cmd.CommandText = strCmd
cmd.Execute Options:=adExecuteNoRecords
|
|
|
|
|
Most likely this is because Access is just a flat-file and the operations against the data are done by the (local) driver. So there's no server side application to handle this. This causes the data to be transferred over the network etc.
One thing is that you could try to modify the stored procedure. If you're having cursors and loops, try to modify them to work on sets. Another this is that you could create a small program on the server side (for example as a service) which would do the actual operation when called. The operation itself wouldn't be faster but much less network traffic would be included.
And of course if you have the option, you could consider migrating to SQL Server which would include a real server side RDBMS.
|
|
|
|
|
For a variety of reasons migrating isn't an option in the short term although I will do that eventually.
I am curious why I can log into the DB via Access on my local computer with the files on the server, and run the query in two seconds. If I run the exact same query in ADO/VBA, it takes a couple minutes.
I know Access mostly uses the old DAO objects. That may be the difference.
|
|
|
|
|
The difference is that it's not a database-server. It's not meant to be accessed from a remote computer, and even though it's possible, it'll never be fast.
Why is migrating not an option? You could migrate the tables and leave your logic, code, forms and data in the MS-Access database. The database could fetch it's records from Sql Server, without the embedded code ever noticing a difference.
--edit
There's a list of possible bottlenecks over here[^], could be usefull.
I are Troll 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
