|
I just not wanting to recreate the wheel here.
I need to do the following:
1.Get two parts of the filename to use wihtin the sql. The filename look like this some name here.123456.1.txt
I need the 1 and the 123456 to populate two columns within the database table along with the data from the text file.
the text file is fixed width 1-9 is the first column 10-18 the second column etc.
I have around five hundred of these txt files I will need to loop through an process.
has anyone done anything like this is a stored proc not using ssis?
many thanks
|
|
|
|
|
Open up a console-window ([Windows Key]-[R], type cmd, press enter) and execute below command;
dir "C:\Windows\m*.log" /b >files.txt
Replace my Windows location (and the wildcard) with one that's more appropriate for you. Next step is easy, you copy and paste it into MS Access. You now have a table with filenames. You could loop those in Access, and fetch the correct file and save it with some VBA-code.
Then make a linked server (in Sql Server) to the Access database (one for generic imports like these) and simply copy the (appropriate) records over.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Thank you for your reply.
I need to be able to do all of thee above wihtin a stored proc without depending on any other app or tool. any thoughts? again thank you for your reply.
|
|
|
|
|
None; yes, it could be done using a sproc. Yes, you can list directories, manipulate strings.
How much of this sproc have you written yet?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
.
modified 10-Oct-12 20:30pm.
|
|
|
|
|
What 'architect' came up with that beauty?
Can a MySql sproc call another stored procedure? If yes, then I'd suggest splitting the else-if's and the query's for readability, lest this will become a maintenance-horror very quick. I'd also recommend strongly to use the stored-procedure-name to identify the query that your architect needs to execute and not a friggin' varchar-parameter.
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
Please don't respond to my questions anymore.i didn't post a question for some punk kid to use profane language.Also if you have no idea what the requirements are why would you reply with such a stupid reply. Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.
|
|
|
|
|
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. 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
