|
The 4 different configurations can all be put in 1 XML file? Any ideas would be gladly received.
|
|
|
|
|
Here's the code I can run with no problems:
declare @AnneeSelectionnee numeric
select @AnneeSelectionnee = 2009
declare @DateDernierImport datetime
select @DateDernierImport = 'Nov 14 2008 12:00AM'
SELECT
LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
FROM T_ASFSTAG0
WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee) AND
((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or
(isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)))
Please note that T_ASFSTAG0 is a view that is partly defined as follows:
FROM OPENQUERY(ISERIES_SQLDBF, 'SELECT * FROM tstadvdbf.ASFSTAG0') AS DerivedTable1
And the following is the relevant code from the stored procedure that fails:
print @AnneeSelectionnee--2009
print @DateDernierImport--Nov 14 2008 12:00AM
print '------before the select-----------'
print convert(datetime,convert(varchar,@DateDernierImport,103),103)
print '------right before the select-----------'
SELECT --line 60--
LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
FROM T_ASFSTAG0
WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee) AND
((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or
(isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)))
print '------after the select and before the insert-----------'
That select statement (on "line 60") reports an error. We never see the "after the select and before the insert" print message. I thought it might have been a conversion problem but as long as an AND statement works like && does in C# (and, AFAIK, it does) then we should have no problems with the (isdate(x) AND convert(datetime(x)).
This is the error I receive:
Server: Msg 7399, Level 16, State 1, Procedure P_IMPORT_STAGES_PREPA, Line 60
OLE DB provider 'MSDASQL' reported an error. One or more arguments were reported invalid by the provider.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x80070057: One or more arguments were reported invalid by the provider.].
Any help or hints is greatly appreciated.
Alex
|
|
|
|
|
IMHO the only way to debug this stuff is hack it down to its simplest level and start adding columns until the error occurs. You will either find a mysterious undocumented bug or an obvious headslapping one.
So maybe progressively alter your SP as follows:
SELECT 'A' As C1, 'B' As C2, 'C' As C3
FROM T_ASFSTAG0
WHERE (T_ASFSTAG0.STANS1=@AnneeSelectionnee)
/*
AND ((isdate(T_ASFSTAG0.STCRT1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STCRT1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)) or (isdate(T_ASFSTAG0.STMAJ1)=1 and convert(datetime,convert(varchar,T_ASFSTAG0.STMAJ1,103),103)>=convert(datetime,convert(varchar,@DateDernierImport,103),103)))
*/
Does that work? keep adding bits in till it breaks.
Also having a select * anywhere in code makes me nervous so maybe it could help things if you alter your view to select specific columns from the table.
|
|
|
|
|
I went one higher and did the following:
print '------right before the select-----------'
--2009
--Nov 14 2008 12:00AM
SELECT --line 67--
LTRIM(RTRIM(T_ASFSTAG0.STANS1)),
LTRIM(RTRIM(T_ASFSTAG0.STHFO1)), LTRIM(RTRIM(T_ASFSTAG0.STRSP1))
FROM T_ASFSTAG0
print '------after the select and before the insert-----------'
Essentially, it's just the referencing of the view that makes it fail.
By the way, the view is defined with specific fields...
SELECT STANS1, STSTA1--,... snip many others
FROM OPENQUERY(ISERIES_SQLDBF, 'SELECT * FROM tstadvdbf.ASFSTAG0') AS DerivedTable1
The error I got was:
Server: Msg 7399, Level 16, State 1, Procedure P_IMPORT_STAGES_PREPA, Line 67
OLE DB provider 'MSDASQL' reported an error. One or more arguments were reported invalid by the provider.
OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x80070057: One or more arguments were reported invalid by the provider.].
Alex
|
|
|
|
|
... I meant this star:
'SELECT * FROM tstadvdbf.ASFSTAG0'
maybe if you reduce the fields it will cut out the field causing trouble.
Some other ideas:
1. Compare the query plan for the working and failing case and see if there is any difference (for the linked server part of it)
2. try select * into table from yourview (I know know its a star  ) and see what data types you get in the new table.
Maybe this will shed some light on it.
|
|
|
|
|
i create trigger in mainserver and connect it throuth local using linked server but if my main server is off then how can i run the trigger in local server without connecting main server
|
|
|
|
|
Please post your question only once. If you make a mistake then just edit the original.
|
|
|
|
|
rupal2 wrote: trigger in mainserver
+
rupal2 wrote: main server is off
=
No chance to fire the trigger. The local server isn't going to clone everything that the mainserver has. If the linked server is down, then everything within that server is down too.
Your options are relative limited; you could try to move the trigger to your local database, or turn on the main-server.
I are Troll 
|
|
|
|
|
i create trigger in mainserver and connect it throuth localserver using linked server but if my mainserver is off then how can i run the trigger in local server.
|
|
|
|
|
Hi all,
I have a problem with executing function in Oracle 10g.
Below mentioned is the function :-
FUNCTION UnpackArray
(
Source IN VARCHAR2 DEFAULT NULL,
Delimiter IN CHAR DEFAULT ','
)
RETURN reSourceArray0 PIPELINED
IS
SourceArray00 SourceArray0:=SourceArray0(NULL);
TYPE REFCURSOR IS REF CURSOR;
CURSOR0 REFCURSOR;
DelLen int;
Pos int;
Cnt int;
str int;
LEN int;
Holder VARCHAR2(255);
BEGIN
IF Source is null or Delimiter is null THEN
Return;
END IF;
IF RTRIM(LTRIM(Source)) = ' ' THEN
Return;
END IF;
SELECT LENGTH(RTRIM(Delimiter)) INTO DelLen FROM DUAL;
SELECT INSTR(UPPER(Source), UPPER(Delimiter)) INTO Pos FROM DUAL;
IF Pos = 0 THEN
BEGIN
INSERT INTO UnpackArray_TBL
( Data )
VALUES ( Source );
return;
OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;
END;
END IF;
SELECT 1 INTO str FROM DUAL;
<< LABEL4 >>
WHILE Pos > 0
LOOP
BEGIN
SELECT Pos - str INTO len FROM DUAL;
SELECT SUBSTR(Source, str, len) INTO Holder FROM DUAL;
INSERT INTO UnpackArray_TBL
VALUES ( Holder );
SELECT Pos + DelLen INTO str FROM DUAL;
SELECT INSTR(UPPER(Source), UPPER(Delimiter), str) INTO Pos FROM DUAL;
OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;
END;
END LOOP;
SELECT SUBSTR(Source, str, length(RTRIM(Source))) INTO Holder FROM DUAL;
IF length(RTRIM(Holder)) > 0 THEN
INSERT INTO UnpackArray_TBL
VALUES ( Holder );
OPEN CURSOR0 FOR SELECT * FROM UnpackArray_TBL;
END IF;
Return; LOOP
FETCH CURSOR0 INTO
SourceArray00.Data;
EXIT WHEN CURSOR0%NOTFOUND;
PIPE ROW(SourceArray00);
END LOOP;
CLOSE CURSOR0;
RETURN;
END;
While executing this am getting the error like:
Running "EDOCS"."UNPACKARRAY" ( SOURCE = admin, DELIMITER = ).
Parameter 'RETURN_VALUE': No size set for variable length data type: String.
can anybody help me to solve this...?
Thanks in advance.
|
|
|
|
|
step 1 : create a Table types named "DIVISION_ARRAY"
CREATE TYPE "DIVISION_ARRAY" AS TABLE OF VARCHAR2(12)
step 2 : execute the below procedures
CREATE OR REPLACE FUNCTION "UNPACKDIVISIONARRAY" <br />
(Source IN VARCHAR2 DEFAULT NULL,<br />
Delimiter IN CHAR DEFAULT ',')<br />
RETURN division_array PIPELINED IS<br />
l_list varchar2(32767) := Source;<br />
l_idx pls_integer;<br />
l_value varchar2(32767);<br />
BEGIN<br />
loop<br />
l_idx := instr(l_list,Delimiter);<br />
if l_idx > 0 then<br />
pipe row(substr(l_list,1,l_idx-1));<br />
l_list := substr(l_list,l_idx+length(Delimiter));<br />
<br />
else<br />
pipe row(l_list);<br />
exit;<br />
end if;<br />
end loop;<br />
return;<br />
end UNPACKDIVISIONARRAY;
step 3 : execute the below sql to see the result
Select column_value from TABLE(UNPACKDIVISIONARRAY('111,113'))
hope that this will help you.
regards,
Sumith Koshy Thankan,
OASYS Information Technology.
Dubai
|
|
|
|
|
Hi,
This has been reposted in MySql forum.
Jeff
modified on Thursday, September 17, 2009 10:48 PM
|
|
|
|
|
Jeff has moved my reply to the MySql forum.
This message will autodestruct in a few days, provided no one replies to it here.
Luc Pattyn
Have a look at my entry for the lean-and-mean competition; please provide comments, feedback, discussion, and don’t forget to vote for it! Thank you.
Local announcement (Antwerp region): Lange Wapper? Neen!
modified on Friday, September 18, 2009 1:45 PM
|
|
|
|
|
|
|
Hii Everybody,
I have one doubt or fundamental unclarity about database.
I would like to know what happens or how does any RDMS database responds to multiple queries being requested from several clients on a table simultaneously ?
Example :
There is a table called customer which has all the required fields of a customer namely age, name. dob etc...
Now there are several clients around the world trying to query this table with several requests. Some clients are trying to insert customer details, some are trying to delete customer details and others are trying to read those customer details simultaneously on customer table.
The clients trying to insert, delete are admins while the clients trying to read are staffs.
In this case how would the table handle those multiple queries at the same time.Would it crash, behave differently etc.. ?
Aspiring Techie,
Vishnu Nath
|
|
|
|
|
|
This is, as the previous post said, its a complex subject, but you need to accept the fact that this is what databases do, and do very well, and they work - as long as you understand about isolation levels and transactions. At my current client there are upwards of a thousand users htting teh database with inserts, updates, delets and plain old reads all day long without any real problems, and this is commonplace.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks for giving out your suggestion.
Well databases must be having robust engine to handle the queries.
But its fuctioning and understanding is important while building a database driven web application.
Also was thinking how facebook, orkut would be using their databases to manage their user queries around the world.
Aspiring Techie,
Vishnu Nath
|
|
|
|
|
Vishnu Nath wrote: But its fuctioning and understanding is important while building a database driven web application.
So you need to do more research than you will get from asking in a forum. Buy a few books on your chosen database (SQL Server, Oracle etc) and investigate further.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
I wanted to get some opinions on this. This is my first time I’ve been tasked with doing this. I need to deploy a SQL Server database. My question is this…is it better to script the creation of the database, or is it better to ship an already structured database?
Thank you in advance.
|
|
|
|
|
Restoring a database is certainly easier - but in terms of best practice, i'd script it!
|
|
|
|
|
I used to work for a software company and we would always ship a series of scripts along with our application to create the basic database structure and seed the lookup tables with basic information.
Advantage: Store your database scripts in a version control repository along with your applicaiton source code so that any given build of your source has a corresponding set of database scripts.
Also, when you have to upgrade the system from version x to y, you should be able to deliver scripts that can update the database schema accordingly.
|
|
|
|
|
Thank you very much for your input. I was thinking scripting, but I wanted to get some real-world information. I really appreciate it.
|
|
|
|
|
Please help me out, what are the types of deadlocks in SQL Server; and how are the ways to solve it?
Regards,
John.L.Ponratnam
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
