Paul, I understand your rules. here is a trick that can help you out over the long run.
Make a new database and do all of your sprocs on there that point to the many different environments you currently work with.
Here you will have full authority to take data and munge it anyway you need without ever editing their data.
In your sprocs you can do this:
declare @Shiptemp Table( CoaId varchar(50) null ,BOLNumber varchar(50) null , IsUltra35 bit , MPRDNumber varchar(50) null <snipped> )
insert into @Shiptemp select distinct or1.t_orno CoaId , or1.t_orno BOLNumber ,case when mp.MPRD like '944%' then 1 else 0 end IsUltra35 , mp.MPRD MPRDNumber <snipped> from [ServerName].Databasename.dbo.Table
This above is how I start to manage different sets of data from 4 different systems to make a certification of analysis report that goes with every truckload we create.
The OE system does the order details. I then need to use some of those values to go into our TESTING system and get results by the lot#. Next you get the speicifications from their system and blend them with AD data to identify who is the quality manager.
All of that is done into separate Tables just like you do into cursors. You then join the datasets and send them back for printing in your report. It only requires two parameters and takes 1-5 seconds to generate depending if you need a new one or an OLD one.
This gives my vendor db(s) a safe place and I won't beat the snot out of their temp db accidentally. As the vendor makes changes to their DB in upgrades or patches I do not lose my work. In this new DB you could place your Data Warehouse as well. You can make Cubes and give users Power BI to start making sense of their data without coming to you for a new report every other day.
Looking forward you can also port this data to the cloud if you wanted because the company bought other firms.
Zero cost to your company with massive upside potential.
Safety of their data because now everything is accessed via sproc.
The various vendor systems don't need to know about any of this either. :)
On Fri, Jun 28, 2019 at 10:14 AM Paul H. Tarver paul@tpcqpc.com wrote:
I've never doubted the benefits of stored procedures and if I were an in-house programmer for a company with full admin rights and/or console access to the SQL Servers, I would be tempted to always use stored procedures myself. However, that is NOT the world I work in. My job is to build interfaces to move data between different systems. I am usually provided with READ-ONLY SQL credentials so I can then issue SELECT queries to extract data and then use the results of those queries to create data feeds into other systems.
Our systems pull data in one direction only and when I describe dynamic SQL statements I'm referring to something little like this (although most are far more complicated queries with lots of moving parts):
lcWhereClause = "WHERE emp.CpnyID = '" +ALLTRIM(thisform.CoCode.value)
TEXT TO lcSQLCmd TEXTMERGE NOSHOW SELECT CAST(emp.CpnyID AS CHAR(20)) AS compid, CAST(emp.EmpId AS CHAR(20)) AS emplid, emp.NameFirst as fname, emp.NameMiddle as mname, emp.NameLast as lname, emp.StrtDate as hire_date FROM dbo.Employee emp <<lcWhereClause>> ENDTEXT lnStatus = SQLEXEC(lnSQLHandle, lcSQLCmd, "EmpList")We accept and validate the selection of the CoCode by the user and then we construct the "dynamic query." I suspect your perception of a Dynamic Query is greatly different than mine. The point of my original comment was to praise the ease with which I can construct SQL statements in a TEXT/ENDTEXT construct and I think this example shows that
Thanks!
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Stephen Russell Sent: Friday, June 28, 2019 9:27 AM To: profoxtech@leafe.com Subject: Re: [NF] What would you miss from VFP, when migrating
I am backing off of licenses for SQL Enterprise down to Standard for 2/3 of all my SQL Server usage in my new deployments. Use to have a total of 96 cores running Ent. and now seeing if we can only use 30. Having virtual guests instead of a single bad ass box makes this a lot easier to do.
Dynamic SQL can burn you.
https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-atta ck/ https://www.cnet.com/news/sony-playstation-site-victim-of-sql-injection-attack/
Making a stored procedure is common sense. Why you cannot see the beauty of it for long term source code is lost on me. Say you make a change to a table. You can easily find every sproc that referenced that table with this statement and miss all that you have fixed: declare @text varchar(50) , @stringtosearch varchar(100) , @comment varchar(150)
set @text = 'Warehouse' set @comment ='%WarehouseChange fixed%'
SET @stringtosearch = '%' +@text + '%'
SELECT Distinct SO.Name FROM sysobjects SO (NOLOCK) INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID AND SO.Type = 'P' AND SC.Text LIKE @stringtosearch and SO.id not in (select distinct SO1.ID FROM sysobjects SO1 (NOLOCK) INNER JOIN syscomments SC1 (NOLOCK) on SO1.Id = SC1.ID AND SO1.Type = 'P' AND SC1.Text LIKE @comment)
ORDER BY SO.Name
You can then cross reference every place that the table was used and see if you need to tweak the data access to include the change you just made to the column.
We just got handed an oh by the way that hits a major focus on how we track sales. We use to give all sales to the plant that made them, which makes sense. Over time we have created warehouses in areas of the country to hold product for delivery to a customer rich area. Sure the ERP already did this but the early reporting team never saw that value.
All of these changes are only in our BI/reporting system or our customer portal. We have to identify over 1000 sprocs to validate that nothing needs to be done here and only 150 really need to be altered.
How would you find that in your prgs? I use the power of the db engine to do a lot of things like this for me.
On Thu, Jun 27, 2019 at 6:16 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
On 6/27/2019 6:39 PM, Paul H. Tarver wrote:
Give me a little credit for being a better programmer than that.
C'mon, Paul -- it's mega-million$ $teve we're talking about here. Mr. Deep Pockets with SQL Server blinders on usually with only Stored Procedures being the only viable safe option.
lol
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]