|
It's not for you to decide who should and shouldn't answer your question: anything put here is fair game and your question is ridiculous: no one has the time to wade through all of that: You need to reduce the code to more manageable chunks.
SQL Ed wrote: Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.
Change the rules.
BTW; I don't see any 'profane' language in his response. You should also recognize that this is an international forum and some cultures swear far more as part of everyday language. Don't down vote just because you don't like what he says. I up-voted him to counter you and because his response was reasonable.
"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
|
|
|
|
|
It was a proper answer with good suggestions considering the info available. That we don't know the requirements should have been obvious to you as you haven't told us.
The suggestion that you need to modularize the code still stands though, but as you're limited to using only one sproc makes everything quite hard (what kind of requirement is that by the way?)
It seems though as you all the time use the same query with different filtering, so one way is to use a view for the main query, performance will suffer though.
|
|
|
|
|
SQL Ed wrote: Please don't respond to my questions anymore
This is an International forum and anyone can reply to any posts. If you don't like it, just ignore it.
SQL Ed wrote: punk kid
Nope, Eddy is a respected member here and his answers are always useful and helpful. Just see his reputation points.
SQL Ed wrote: profane language
As far as I can see, there's none.
SQL Ed wrote: if you have no idea what the requirements
Might be true, but you didn't tell us either.
SQL Ed wrote: Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.
That's not a technical constraint. A stored procedure should be able to call another many levels deeper and your 3rd party application need not even be aware of it, it just needs to know the name of the "outermost" stored procedure.
|
|
|
|
|
SQL Ed wrote: Please don't respond to my questions anymore
I could promise now, but would probably forget your name in a week.
SQL Ed wrote: i didn't post a question for some punk kid to use profane language.
The "friggin" was superfluous. It was partly an emotional response, something that simply seems to happen whenever I meet a 'solution' without a problem. As is, the requirement does not add value, on the contrary, it makes things more complex and error-prone.
SQL Ed wrote: Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.
As hard as it may sound, but IMO that's where the error is. Unless there's a very good justification (besides the remark that it's a requirement), I'd still suggest fixing the problem at it's root.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
i need a script by which i can get client IP address from sql server database when people do DML operation on table. so i was looking for solution and i found a script which does the things fine from sql server 2005. here is the script
CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
DECLARE @IP_Address varchar(255);
SELECT @IP_Address = client_net_address
FROM sys.dm_exec_connections
WHERE Session_id = @@SPID;
Return @IP_Address;
END
SELECT dbo.GetCurrentIP()
but the above script do not work in sql server 2000. so i need a way to get client ip from sql server 2000. please guide. thanks
tbhattacharjee
|
|
|
|
|
Have you read this. Second answer in that thread tells you the options are very limited for IP retrieving in Sql Server 2000
In Word you can only store 2 bytes. That is why I use Writer.
|
|
|
|
|
The following code keeps erroring out in MySQL Workbench. I have not been able to locate the syntax problem. Can anyone see whats wrong with this code. Runnining the code a section at a time still dosn't work. It errors at the first ; where I placed the MAD face. Cannot completely remove the errors they just keep changing places. Also is there a good library of code snippets to view if needed?
/*
PATCH MANAGEMENT
*/
DELIMITER $$
#MachineType means Server or Workstation as determined by the word server in the os field
DROP PROCEDURE IF EXISTS usp_cbi_PatchManagement$$
CREATE PROCEDURE usp_cbi_PatchManagement(MasterGrp varchar(30),Criteria varchar(30),LocationID varchar(30),
ClientName varchar(30))
BEGIN
SELECT IFNULL(COUNT(DISTINCT A.computerid),0) AS 'Dynamic Machine Counts'
FROM (SELECT HF.computerid, HF.hotfixID
FROM hotfixdata HFD
INNER JOIN hotfix HF
ON HF.hotfixid = HFD.hotfixid
AND HFD.ignore <> 1
LEFT OUTER JOIN Computers AS C
ON C.ComputerID=HF.ComputerID
LEFT OUTER JOIN locations AS L
ON C.LocationID=L.LocationID
LEFT OUTER JOIN Clients AS CL
ON C.ClientID=CL.ClientID
JOIN agentcomputers AS AC
ON C.ComputerID=AC.computerid
JOIN mastergroups AS MG
ON AC.groupid=MG.groupId
#Enter Case When here seperating different where clauses.
WHERE
CASE
WHEN
Criteria = 'Workstations Missing Patches'
THEN
INSTR(C.os,"server")=0
AND INSTR(C.os,"microsoft")>0
AND HF.installed <> 1
AND HF.Approved = 1
AND LocationID = L.Name
AND ClientName = Cl.Name
 AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Workstations with Empty Patch Tabs'
THEN
HF.hotfixid IS NULL
AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY))
AND INSTR(C.OS,"server")=0
AND INSTR(C.OS,"microsoft")>0
AND LocationID = L.Name
AND ClientName = Cl.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Workstations Pending Reboots'
THEN
(C.Flags & 1024) = 1024
AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY))
AND INSTR(C.OS,"server")=0
AND INSTR(C.OS,"microsoft")>0
AND LocationID = L.Name
AND ClientName = C.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Total Patch Workstations'
THEN
INSTR(C.os,"server")=0
AND INSTR(C.os,"microsoft")>0
AND LocationID = L.Name
AND ClientName = CL.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Servers Missing Patches'
THEN
INSTR(C.os,"server")>0
AND INSTR(C.os,"microsoft")>0
AND HF.installed <> 1
AND HF.Approved = 1
AND LocationID = L.Name
AND ClientName = Cl.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Servers Pending Reboots'
THEN
(C.Flags & 1024) = 1024
AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY))
AND INSTR(C.OS,"server")>0
AND INSTR(C.OS,"microsoft")>0
AND LocationID = L.Name
AND ClientName = C.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Total Patch Servers'
THEN
INSTR(C.os,"server")>0
AND INSTR(C.os,"microsoft")>0
AND LocationID = L.Name
AND ClientName = CL.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;
WHEN
Criteria = 'Total Patch Machines'
THEN
INSTR(C.os,"microsoft")>0
AND LocationID = 'all'
AND ClientName = 'all'
AND MasterGrp = 'all';
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Parameter not found!!');
GROUP BY HF.hotfixID, HF.ComputerID) A;
END;
END$$
|
|
|
|
|
A case when clause can only return values, not conditions.
|
|
|
|
|
Quote: type Exception report
message
descriptionThe server encountered an internal error () that prevented it from fulfilling this request.
exception
javax.servlet.ServletException: java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
root cause
java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application
|
|
|
|
|
Searching for the error message and ODBC with Google founds this MSDN article[^] as first result.
When using a 64-bit Java you must use the 64-bit driver.
|
|
|
|
|
|
I want to get a count of an ID where the ID is either = -999 or its not. So i have 2 counts in total. I used union all to get that but i think there would be better way to get the count from the same field. any help would be appreciated.
select count(ID) as Id_999 from table_A where ID='-999'
union all
select count(ID) from table_A where ID<>'-999'
there is no null values allowed so it either a number or -999 if null when table is loaded.
|
|
|
|
|
You could turn the counts into separate columns:
select SUM (
CASE
WHEN ID = '-999' THEN 1
ELSE 0
END)
AS ID999_count,
SUM (
CASE
WHEN ID <> '-999' THEN 1
ELSE 0
END)
AS nonID999_count
from table_A
Scott
|
|
|
|
|
Thanks Scott it works great. I was close to that but i was putting ID, then the sum(CASE statement). Thanks again.
|
|
|
|
|
Qazzy64 wrote: there is no null values allowed so it either a number or -999 if null when table is loaded.
Just curious; does that mean that you replace a null-test with a test for -999, and act according? Why was null disallowed anyway?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
When they load the table it has a number(ID) or if its null they load -999 so they know to reprocess it later on when other matching data shows up. Hope that helps.
|
|
|
|
|
The reason I asked is because it does not make any sense to me; you'd still have to deal with the same type of checks. The only difference being that the new "strategy" will be alien to new developers, and will cause weirder and harder to debug-exceptions than a null-reference would.
I might be missing some obvious advantage of the approach. So, where is it? What's the 'added value' of using a marker-value above a null-value?
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
I agree with you that doing anything causes more problems eventually. Myself i would not have brought the data forward until all fields are present from source file. But i do not make that choice and i got involved after project was started. This is more of a transactional system then data warehouse like they said. Hate to say it but it goes back to the way it was architected and going forward from there has been to get it to work. Very complex data modeling which i would have simplified by bringing it in and leveling data types. They chose to bring it forward anyway and then leave just before the DW level. Makes no sense but i wasnt in on those meetings. I get to deal with it and figure out if the data is accurate. Fun times. 
|
|
|
|
|
Hi Mr
i have a problem in ssis when i tried to use xml file to import to any data sources
i used Multicast to test no or rows returned but no returned rows
i can't attach xml file in the message
please save as GetGroups.xml and test it and send me the result
="1.0"="utf-16"
<DataSet>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="T">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" minOccurs="0" />
<xs:element name="NodeGroup" type="xs:string" minOccurs="0" />
<xs:element name="NodeCount" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet>
<T diffgr:id="T1" msdata:rowOrder="0">
<ID>648</ID>
<NodeGroup>Moustord</NodeGroup>
<NodeCount>5</NodeCount>
</T>
<T diffgr:id="T2" msdata:rowOrder="1">
<ID>646</ID>
<NodeGroup>Shell</NodeGroup>
<NodeCount>9</NodeCount>
</T>
<T diffgr:id="T3" msdata:rowOrder="2">
<ID>647</ID>
<NodeGroup>Suze</NodeGroup>
<NodeCount>4</NodeCount>
</T>
</NewDataSet>
</diffgr:diffgram>
</DataSet>
modified 9-Oct-12 1:55am.
|
|
|
|
|
DECLARE @xml XML
SET @xml = '<DataSet>
<xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
<xs:complexType>
<xs:choice minOccurs="0" maxOccurs="unbounded">
<xs:element name="T">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" minOccurs="0" />
<xs:element name="NodeGroup" type="xs:string" minOccurs="0" />
<xs:element name="NodeCount" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<NewDataSet>
<T diffgr:id="T1" msdata:rowOrder="0">
<ID>648</ID>
<NodeGroup>Moustord</NodeGroup>
<NodeCount>5</NodeCount>
</T>
<T diffgr:id="T2" msdata:rowOrder="1">
<ID>646</ID>
<NodeGroup>Shell</NodeGroup>
<NodeCount>9</NodeCount>
</T>
<T diffgr:id="T3" msdata:rowOrder="2">
<ID>647</ID>
<NodeGroup>Suze</NodeGroup>
<NodeCount>4</NodeCount>
</T>
</NewDataSet>
</diffgr:diffgram>
</DataSet>'
;WITH XMLNAMESPACES(
'http://www.w3.org/2001/XMLSchema' AS ns,
'urn:schemas-microsoft-com:xml-diffgram-v1' AS diffgr )
SELECT
fd.c.value('(ID/text())[1]', 'INT') 'a',
fd.c.value('(NodeGroup/text())[1]', 'nvarchar(255)') 'b',
fd.c.value('(NodeCount/text())[1]', 'INT') 'c'
FROM @xml.nodes('ns:DataSet/diffgr:diffgram/NewDataSet/T') fd(c)
|
|
|
|
|
Hi,
What's the problem with this code?:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_delete_channel`(IN param_channel_id int, IN param_volow_member_id int, OUT param_is_channel_created bit)
BEGIN
DELETE FROM member_posts WHERE member_channel_id = param_channel_id;
DELETE FROM member_channels WHERE member_channel_id = param_channel_id;
IF NOT EXISTS (SELECT member_channel_id FROM member_channels WHERE volow_member_id = param_volow_member_id) THEN
BEGIN
UPDATE volow_members SET is_channel_created = FALSE;
SET param_is_channel_created = FALSE;
END;
ELSE SET param_is_channel_created = TRUE;
END IF;
END
I am not getting any error on mysql neither on the .net application?!!
here is my .net code:
sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_command = new MySqlCommand("sp_delete_channel", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_connection.Open();
sql_command.Parameters.AddWithValue("param_channel_id", channel_id).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_volow_member_id", Convert.ToInt32(Session["volow_member_id"])).MySqlDbType = MySqlDbType.Int32;
sql_command.Parameters.AddWithValue("param_is_channel_created", MySqlDbType.Bit).Direction = ParameterDirection.Output;
int result_row = sql_command.ExecuteNonQuery();
if (Convert.ToBoolean(sql_command.Parameters["param_is_channel_created"].Value) != true)
{ Session["is_channel_created"] = false; }
|
|
|
|
|
Not getting any errors could sometimes be thought of as a good thing.
What's the problem?
|
|
|
|
|
that's what's making me crazy!
no error in Mysql nor C# code
but the MySQL code is not getting invoke. DELETE is not happening!
|
|
|
|
|
..
Try 'debugging'. What does the row-count return? What happens if you put a 'SELECT 1' in there, without any parameters, does that execute?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
How do you know that the DELETE did not happen? Did you check in the database?
What do you expect to get at int result_row = sql_command.ExecuteNonQuery();?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
