April 8, 2016 at 10:44 am
Satiz (4/8/2016)
Phil Parkin (4/8/2016)
Satiz (4/8/2016)
I will be creating it in staging and will test it if its works fine then i will copy the same procedure and recreate it prod without making any modification in prodThose are environments, not databases.
I apologize.You are right. I only have mentioned it wrongly ..
We are clearly missing something here.
So, you have some sort of general database, DB1, which exists in Staging and in Prod, is that correct? The proc will be created there and will perform actions in DB2 (Staging) or DB3 (Production). Is that the architecture?
If so, why not just put the proc in DB2 and DB3 and forget about DB1?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 8, 2016 at 11:22 am
Phil Parkin (4/8/2016)
Satiz (4/8/2016)
Phil Parkin (4/8/2016)
Satiz (4/8/2016)
I will be creating it in staging and will test it if its works fine then i will copy the same procedure and recreate it prod without making any modification in prodThose are environments, not databases.
I apologize.You are right. I only have mentioned it wrongly ..
We are clearly missing something here.
So, you have some sort of general database, DB1, which exists in Staging and in Prod, is that correct? The proc will be created there and will perform actions in DB2 (Staging) or DB3 (Production). Is that the architecture?
If so, why not just put the proc in DB2 and DB3 and forget about DB1?
I have two Servers ,one server is staging and another is production different server .In staging I have 2 database "order_Test" and "lookup".
In this Lookup database, we will be creating a procedure in staging server. Like staging we will have 2 databases in production "order" and "lookup".
we will do the same in prod after the Member confirmation completed.
But the problem is that in staging we have the database name as "order_Test" and in production we have "order".After UAT completion,
we will move this procedure to Production while moving it, we need to change the database name if we are not changing it will impact the application.
So without altering the script to dynamic-sql.
I have altered the procedure but it is not working as expected . could you please check and let me know the changes.
ALTER procedure [dbo].[load_orders] @member varchar(255), @id varchar(255)
As
BEGIN
--SET NOCOUNT ON
DECLARE
@sqlStringvariables varchar(max),
--the select statement
@sqlStringSelect varchar(max),
--Insert Statement
@sqlStringInsert varchar(max),
--Catch
@sqlStringcatch varchar(max),
--update
@sqlStringupdate varchar(max),
--stage or production, to get the correct connect
@connection varchar(20) = case
when @@ServerName = 'Staging'
then '[order_Test]'
when @@ServerName = 'production'
then '[Orders]'
else ''
end
Set @sqlStringvariables='
Declare
--to hold value upon success (1) or fail (2) insert for hasLoaded, default to 0
@hasloadedResult int = 0,
--defaulting to 0
@max_RawOrderKey bigint = 0,
--the error variables
@err_num int = 0,
@err_sev int,
@err_state int ,
@err_pro varchar(100),
@err_lin int,
@err_mes varchar(2500)
SELECT @max_RawOrderKey = CONVERT(BIGINT, ISNULL(MAX(rawKey), 0)) FROM '+@connection+'.[dbo].[temp_raw_orders] where loaded = 0;'
Set @sqlStringInsert='
INSERT INTO '+@connection+'.dbo.Final_Orders
(
[member]
,[id]
,[FirstName]
,[LastName]
,[MiddleName]
,[DOB]
,[Gender]
,[HomePhone]
,[WorkPhone]
,[CellPhone]
,[PreferredPhone]
,[Street1]
,[Street2]
,[City]
,[State]
,[Zip]
)'
Set @sqlStringSelect='Select
'''+@member+'''[member]
,'''+@id+''' [id]
,LEFT(ord.FirstName,100) [FirstName]
,LEFT(ord.LastName,100) [LastName]
,''''[MiddleName]
, (ord.DOB) [DOB]
, CASE WHEN Isnull(Gender,'''') = ''Male'' Then ''M''
WHEN Isnull(Gender,'''') = ''Female'' Then ''F''
ELSE ''N'' END [Gender]
,(ord.HomePhone, 10) [HomePhone]
,(ord.WorkPhone, 10) [WorkPhone]
,(ord.CellPhone, 10) [CellPhone]
,'+@connection+'.[dbo].[fn_RemoveNonNumeric_len](
IIF(isnull(ord.PreferredPhone,'''')<>'''',ord.PreferredPhone,
(IIF(isnull(ord.[CellPhone],'''')<>'''',ord.[CellPhone],
IIF(isnull(ord.[HomePhone],'''')<>'''',ord.[HomePhone],
IIF(isnull(ord.[WorkPhone],'''')<>'''',ord.[WorkPhone],''''))))),10) [PreferredPhone]
,LEFT(ord.[Street1],100) [PStreet1]
,LEFT(ord.[Street2],100) [Street2]
,LEFT(ord.[City],50) [City]
,left(ord.[state],2) [state]
,ord.[Zip][Zip]
from '+@connection+'.[dbo].[temp_raw_Orders] ord
LEFT OUTER JOIN dbo.Load_tb lp ON
( lp.member ='''+@member+'''
AND lp.id='''+@id+''' )
WHERE ord.HasLoaded=0
AND ord.Rawkey<=@max_RawOrderKey
AND NOT EXISTS (
SELECT 1 FROM '+@connection+'.[dbo].[Final_Orders] ro
WHERE ro.member = '''+@member+'''
AND ro.id ='''+@id+'''
AND ro.recordID = ord.recordID
)'
set @sqlStringcatch='
--pop the error vars
select
@err_num = ERROR_NUMBER(),
@err_sev = ERROR_SEVERITY() ,
@err_state = ERROR_STATE() ,
@err_pro = coalesce(ERROR_PROCEDURE(), ''[dbo].[load_orders]'' ),
@err_lin = ERROR_LINE() ,
@err_mes = ERROR_MESSAGE()'
set @sqlStringupdate='
IF @err_num = 0
BEGIN
SET @hasloadedResult = 1;
END
--if the insert has failed
ELSE
BEGIN
SET @hasloadedResult = 2;
END
UPDATE '+@connection+'.[dbo].[temp_raw_Orders]
SET HasLoaded = @hasloadedResult
FROM '+@connection+'.[dbo].[temp_raw_Orders]
WHERE
rawKey <= @max_RawOrderKey
and
hasloaded = 0;
--raise an error if insert failed after the correct update is performed
IF @hasloadedResult = 2
BEGIN
--to hold the error message
DECLARE @err_str varchar(max) = ''The Insert failed for procedure ''+@err_pro+'' on line ''+cast(@err_lin as varchar(5))+'' with error number ''+cast(@err_num as varchar(5))+''.''+
''The error has the following message: ''+@err_mes;
--raise the error
RAISERROR( @err_str, @err_sev, @err_state );
end'
BEGIN TRY
Exec (@sqlStringvariables+@sqlStringInsert+@sqlStringSelect+@sqlStringupdate)
END TRY
BEGIN CATCH
Exec (@sqlStringvariables+@sqlStringcatch)
END CATCH
April 8, 2016 at 11:32 am
Without knowing anything else about the application/process/environment, what if you created the stored proc in the order database (the one that changes names) and then reference the lookup database (which doesn't change names, correct?) from there? Is that possible and would that solve your problem?
-G
April 8, 2016 at 11:40 am
Greg A Goss (4/8/2016)
Without knowing anything else about the application/process/environment, what if you created the stored proc in the order database (the one that changes names) and then reference the lookup database (which doesn't change names, correct?) from there? Is that possible and would that solve your problem?-G
I will add that this is a slightly unusual way to develop things, when separate environments are available. Everywhere I have worked keeps the database names the same (while being very careful to ensure zero cross-environment contamination).
I urge you to reconsider.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 8, 2016 at 11:46 am
Phil Parkin (4/8/2016)
Greg A Goss (4/8/2016)
Without knowing anything else about the application/process/environment, what if you created the stored proc in the order database (the one that changes names) and then reference the lookup database (which doesn't change names, correct?) from there? Is that possible and would that solve your problem?-G
I will add that this is a slightly unusual way to develop things, when separate environments are available. Everywhere I have worked keeps the database names the same (while being very careful to ensure zero cross-environment contamination).
I urge you to reconsider.
I was trying to avoid suggesting changing database names in case other processes referenced it, but from a design standpoint, I highly agree with Phil's suggestion. Is that something that is possible?
April 8, 2016 at 12:20 pm
since you're going through the trouble of doing dynamic sql because of changing database names, maybe its worth your while to look at parameters of sqlcmd. Its much more convenient, you skip all that escaping stuff, and sqlcmd parameters are really nice.
The downside is that you have to learn sqlcmd. I really like it because I can parameterize the heck out of it, and run jobs against any database or server from windows scheduler. The downside to that is that you have to use windows scheduler LOLOL
April 8, 2016 at 12:25 pm
patrickmcginnis59 10839 (4/8/2016)
--snipThe downside is that you have to learn sqlcmd. I really like it because I can parameterize the heck out of it, and run jobs against any database or server from windows scheduler. The downside to that is that you have to use windows scheduler LOLOL
sqlcmd scripts can be run from a SQL Agent job. From BOL:
The sqlcmd Utility lets you enter Transact-SQL statements, system procedures, and script files at the command prompt, in Query Editor in SQLCMD mode, in a Windows script file or in an operating system (Cmd.exe) job step of a SQL Server Agent job.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 8, 2016 at 12:42 pm
Wild idea:
Instead of using dynamic SQL, use synonyms for the cross-database calls.
April 9, 2016 at 4:46 am
Luis Cazares (4/8/2016)
Wild idea:Instead of using dynamic SQL, use synonyms for the cross-database calls.
Thanks, buddy ...I will try by using the synonym.
April 10, 2016 at 6:40 am
Satiz (4/9/2016)
Luis Cazares (4/8/2016)
Wild idea:Instead of using dynamic SQL, use synonyms for the cross-database calls.
Thanks, buddy ...I will try by using the synonym.
Hey guys i tried by using synonym but it is not working..Could you please work.......
April 10, 2016 at 8:35 am
Satiz (4/10/2016)
Satiz (4/9/2016)
Luis Cazares (4/8/2016)
Wild idea:Instead of using dynamic SQL, use synonyms for the cross-database calls.
Thanks, buddy ...I will try by using the synonym.
Hey guys i tried by using synonym but it is not working..Could you please work.......
Is your comment directed at the synonym or us?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 10, 2016 at 9:21 am
Phil Parkin (4/10/2016)
Satiz (4/10/2016)
Satiz (4/9/2016)
Luis Cazares (4/8/2016)
Wild idea:Instead of using dynamic SQL, use synonyms for the cross-database calls.
Thanks, buddy ...I will try by using the synonym.
Hey guys i tried by using synonym but it is not working..Could you please work.......
Is your comment directed at the synonym or us?
Directed to synonym .we can't able to use database name as synonym .guy's could You please help me .
April 10, 2016 at 9:33 am
Satiz (4/10/2016)
Phil Parkin (4/10/2016)
Satiz (4/10/2016)
Satiz (4/9/2016)
Luis Cazares (4/8/2016)
Wild idea:Instead of using dynamic SQL, use synonyms for the cross-database calls.
Thanks, buddy ...I will try by using the synonym.
Hey guys i tried by using synonym but it is not working..Could you please work.......
Is your comment directed at the synonym or us?
Directed to synonym .we can't able to use database name as synonym .guy's could You please help me .
The proc will live in your lookup database.
Create a synonym for table dbo.temp_raw_orders (and all the other tables which the proc references). These will reference the relevant tables in order_test or order.
Change your proc to reference the synonyms. Now your proc is static and the synonyms take care of the different DB names.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 11, 2016 at 7:50 am
I agree with Phil about having the same DB structure in all environments but I have worked at a company where the DB names has a suffix for the environment e.g. DB; DB_UAT; DB_DEV;
If you are stuck with what you have, consider using a package configuration to change the InitialCatalog of the connection manager - this could be via SSIS config database table or similar.
Jez
April 11, 2016 at 8:16 am
I would advise using simple update scripts on your database objects before propagating them to production. Since your objects are simple text any scripting language/powershell can manipulate your t-sql before moving it into production. I manage over 4000 databases and I can tell you trying to get development on board to fixing their naming conventions AFTER they have done the work is not easy, so I ask them for the for a production DB object list, per version, and then alter my scripts accordingly.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply