How can we help you today? How can we help you today?
bhopenw
Activity overview Badges (0) Posts (9) Comments (21)

Comments

Sort by recent activity
I am having a very simular issue where a stored proc is being updated before the column is added. Is there a way to force order? I am using SQL COmpare 8.1 for the Cmd line via an automated powershell script. thanks, BJHop / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Wrong Sync Order
I am having a very simular issue where a stored proc is being updated before the column is added. Is there a way to force order? I am using SQL COmpare 8.1 for the Cmd line via an automated powers...
0 votes
DBO in Live db -- Permissions GRANT VIEW DEFINITION ON SCHEMA:: [dbo] TO [corp\dubele] GRANT VIEW DEFINITION ON SCHEMA:: [dbo] TO [CORP\HNANDIP] GRANT VIEW DEFINITION ON SCHEMA:: [dbo] TO [CORP\ibingha] GO There is no DBO.sql file in the Schema folder the pane shows the above in the Live DB area and blank pane for the scripts folder The only warnings I get are the following: IF clause evaluated as true SQL Compare considers only the first block of code in IF statements. Schema changes made by ELSE conditions do not appear in the comparison results. all having to do with users Hope this helps / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Schema DBO object always different
DBO in Live db-- Permissions GRANT VIEW DEFINITION ON SCHEMA:: [dbo] TO [corp\dubele] GRANT VIEW DEFINITION ON SCHEMA:: [dbo] TO [CORP\HNANDIP] GRANT VIEW DEFINITIO...
0 votes
As I said here is the powershell code I created to get the job done It will update sql changeset with a new workspace to poll I believe it will help us enfore local standards to help work with a local scp file This script got a bit more complex than I first thought but I think it works well enjoy Please let me know if you have any comments or questions #Set-SQLChangeset #this script set up SQL Changeset for use with a SCP and local workspace param([string] $serverPath = $(throw 'The TFVC server path to DB schema is required'), [string] $localPath = $(throw 'The local path to workspace is required'), [string] $tfsUrl = $('http://as73tfs01:8080')) ###MAIN### $ErrorActionPreference = "Stop" write-debug "Starting" #using [void] [Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.Client") [void] [Reflection.Assembly]::LoadWithPartialName("Microsoft.TeamFoundation.VersionControl.Client") #set variables $tfs = New-Object Microsoft.TeamFoundation.Client.TeamFoundationServer($tfsUrl) $vcs = $tfs.GetService([Microsoft.TeamFoundation.VersionControl.Client.VersionControlServer]) $wsCurrent = [Microsoft.TeamFoundation.VersionControl.Client.Workstation]::Current #may push to this to config file [string] $wsName = "SQLChangeset_" + $(Split-Path $localPath -leaf) [string] $path2ConfigXml = "C:\Documents and Settings\$($env:USERNAME)\Local Settings\Application Data\Red Gate\SQL Changeset\Configuration.xml" [string] $path2sqlCs = "C:\Program Files\Red Gate\SQL Compare 8\SQL Changeset\RedGate.SqlChangeset.exe" [string] $user = $Env:USERDOMAIN + "\" + $Env:USERNAME if (-not (Test-Path $path2ConfigXml)) { #SQL Changeset has not been started for first time Write-warning "Starting SQL Changeset for first time" Write-Warning "Please close once started" & $path2sqlCs } #we need to be sure that SQL CS is not running #if it is we need to find out what to do while ($(Get-Process | ?{$_.Name -eq 'RedGate.SqlChangeset'})) { Write-Host "SQL Changeset cannot be running while this process is completed" Write-Host "To exit right-click on the SQL ChangeSet icon in the notification" Write-Host "area in the right hand side of the status bar where the clock usually is" switch -regex (Read-Host -Prompt "[R] Retry, [K] Kill the process, [A] Abort") { "R|r"{} "A|a" { write-host -ForegroundColor Red "Abort: Cannot continue with Red Gate SQL Changeset running" return } "K|k" { Write-Warning "Killing the process may have unkown effects on SQLChangeset" switch -regex (Read-Host -Prompt "Are you sure you want to kill the SQL Changeset process [Y/N]") { "Y|y" { $sqlcs = $(Get-Process | ?{$_.Name -eq 'RedGate.SqlChangeset'}) $sqlcs.Kill() sleep -Seconds 5 } "N|n" {} } } } } if (-not (Test-Path $localPath)) { New-Item -Path $(Split-Path $localPath -Parent) -Name $(Split-Path $localPath -leaf) -ItemType Directory | Out-Null } else { if ($wsCurrent.IsMapped($localPath)) { Write-Host -ForegroundColor Red "Error: Local path already mapped; $localPath is already mapped on this workstation" return } } #check to ensure a workspace with same name not already created if($wsCurrent.GetAllLocalWorkspaceInfo() | ?{$_.Name -match $wsName}) { Write-Host -ForegroundColor Red "Error: workspace name - $wsName - already in use" Write-Host "Workspace name is taken for the local path directory name" return } ##assume directy name is a good name ie DB name $ws = $vcs.createworkspace($wsName) $ws.map($serverPath, $localPath) Write-Debug "Workspace created" [System.IO.FileInfo] $configXml = Get-Item $path2ConfigXml Copy-Item -Path $configXml.FullName -Destination $($configXml.FullName + ".bak") -Force [string] $configXmlContents = gc $configXml #check to make sure the server path has not already been used Write-Debug "configuration xml backed up" #search if ($configXmlContents -match "\$serverPath") { Write-Host -ForegroundColor Red "Error: $serverPath already in use in SQL Changeset" Write-Host "Workspace will be deleted" $ws.delete() return } [xml] $xml = [xml] (gc $configXml) $projects = $xml.ConfigurationFile.SelectSingleNode("Projects") #need to crate xml elements [System.Xml.XmlElement] $pair = $xml.CreateElement("PairOfScProviderOpenProjectInfo") [System.Xml.XmlElement] $scp = $xml.CreateElement("ScProvider") [System.Xml.XmlElement] $scpC = $xml.CreateElement("ScProvider") [System.Xml.XmlElement] $opi = $xml.CreateElement("OpenProjectInfo") [System.Xml.XmlElement] $opiC = $xml.CreateElement("OpenProjectInfo") $scpC.SetAttribute("Name", "Team Foundation Server MSSCCI Provider") $scpC.SetAttribute("Registry", "SOFTWARE\Microsoft\Team Foundation Server MSSCCI Provider") $scpC.SetAttribute("Dll", "C:\Program Files\Microsoft Team Foundation Server MSSCCI Provider\TfsMsscciProvider.dll") $scp.AppendChild($scpC) Write-Debug "Updating configuration xml" #This is the path SQL CS uses #I think the info it needs to get the workspace object [string] $wsPath = "$tfsUrl/|$wsName|$user" $opiC.SetAttribute("LocalPath", $localPath) $opiC.SetAttribute("Name", $serverPath) $opiC.SetAttribute("Path", $wsPath) $opiC.SetAttribute("User", $user) $opi.AppendChild($opiC) $pair.AppendChild($scp) $pair.AppendChild($opi) $projects.AppendChild($pair) $xml.Save($configXml.FullName) Write-Host "SQL Changeset has been successfully updated" Write-Host "Starting SQL Changeset" & $path2sqlCs / comments
  1. Community
  2. SQL Compare Previous Versions
  3. SQL Changeset setup
As I said here is the powershell code I created to get the job done It will update sql changeset with a new workspace to poll I believe it will help us enfore local standards to help work with a lo...
0 votes
Okay, It looks this is possible and it shouldn't be all that difficult using powershell. We use SQL Changeset (SQLCS) to connect to TFS 2005 sp1 When SQL Changeset first starts up it adds some elements to the users local Application Data folder (C:\Documents and Settings\bhopenw\Local Settings\Application Data\Red Gate\SQL Changeset). In this directory, there is a xml file named configuration.xml, this is the file use by SQLCS to registery the TFS/SCCI workspaces. With that fact found it is truly a simple powershell script to get this working. below is the workflow, I have not finished up the script yet but when I do I will post it. * Ensure SQLCS has been started at least once, need to have local app Data section set, if not start it. * Ensure SQLCS is not running, if it is prompt to stop or kill it * Create workspace with mappings set from parameters/config file * Add <PairOfScProviderOpenProjectInfo> node to configuration.xml * start SQLCS Where I see this working is we are going to have a common SCP and this script will set up the local environment (workspace) for the developers. Thanks, BJHop / comments
  1. Community
  2. SQL Compare Previous Versions
  3. SQL Changeset setup
Okay, It looks this is possible and it shouldn't be all that difficult using powershell. We use SQL Changeset (SQLCS) to connect to TFS 2005 sp1 When SQL Changeset first starts up it adds some elem...
0 votes
While I have got the table drop capture working I am searching for a way to see a column drop. I believe that only way to get this done is to use the SDK. I that is the way I'm going to move now, unless, anyone has a way w/o the SDK. Thanks, BJHop / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Capture warnings
While I have got the table drop capture working I am searching for a way to see a column drop. I believe that only way to get this done is to use the SDK. I that is the way I'm going to move now, ...
0 votes
Brian Good stuff, I like what you have done. This does look to be the way to go, thanks. Nevertheless, I think I am going to be push back on the BA to see if I can change the requirement to force backup for Table drops only for this release. This way I don't have to add the complexity of the SDK to my scripts for to a release due next week. Again thank you BJHop / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Capture warnings
Brian Good stuff, I like what you have done. This does look to be the way to go, thanks. Nevertheless, I think I am going to be push back on the BA to see if I can change the requirement to force ...
0 votes
Okay I was able to capture a table drop by examining the results of a sync script creation (Up script) command. I am running in Powershell therefore I was able to look of a line that begin with 'TABLE' and contains '<<' Now I just need a way to find column drops which I think will be a bit more of a challenge. Again any and all ideas are welcome thanks, BJHop / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Capture warnings
Okay I was able to capture a table drop by examining the results of a sync script creation (Up script) command. I am running in Powershell therefore I was able to look of a line that begin with 'T...
0 votes
I do see you line of thinking; if you are in a script why not just make a call to a backup script and I that is what I am going to do. Nevertheless, the backup before sync was a feature that was requested of my script a few days ago and I thought if I can just upgrade the scripts to SQL Compare 8 that would be easier than writing a backup script myself. thanks for your quick reply BJHop / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Backup before Sync from Cmd line
I do see you line of thinking; if you are in a script why not just make a call to a backup script and I that is what I am going to do. Nevertheless, the backup before sync was a feature that was ...
0 votes
Okay I forgot to have my V8 this morning just realized that real issue was not the fore mentioned SP but the one just below it dbo.SDISP_HLIC_GET_InquiryMaster.sql and the error message what telling me it all along I thought I had case sensitive option set Nevertheless, I am facing a new issue one that I have had before and just mentioned. SQL Compare keep considering one SP as different even I've synced multiple times. Issues is large block of Commented out code, as soon I ignore comments all is fine but I'd like to have comments synced Below is the code: SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO -- ============================================= -- Author: L&T Infotech -- Create date: 12th-Nov-2008 -- Description: To assign the products with the Waiver premium to a respective employee -- ============================================= CREATE PROCEDURE &#91;dbo&#93;.&#91;SDIsp_HLIC_Enroll_InsertUpdate_EmpWaiverOfPremium&#93; &#40; @p_employee_id bigint, @p_user_ad_id varchar&#40;30&#41;, @p_user_role varchar&#40;30&#41;, @p_prod_waiverofpremium_xml xml &#41; AS BEGIN SET NOCOUNT ON -- ============================================= BEGIN TRY CREATE TABLE #temp_PROD_DETAILS &#40; ROWID INT IDENTITY&#40;1,1&#41;, PLAN_TYPE VARCHAR&#40;30&#41;, PRODUCT_NAME VARCHAR&#40;30&#41;, EMP_PROD_RL_ID BIGINT, WAIVER_OF_PREM_EFFECTIVE_DATE DATETIME, WAIVER_OF_PREM_END_DATE DATETIME &#41; DECLARE @v_xmlhandle int EXEC SP_XML_PREPAREDOCUMENT @v_xmlhandle output, @p_prod_waiverofpremium_xml INSERT INTO #temp_PROD_DETAILS &#40;PLAN_TYPE, PRODUCT_NAME, EMP_PROD_RL_ID, WAIVER_OF_PREM_EFFECTIVE_DATE, WAIVER_OF_PREM_END_DATE &#41; &#40;SELECT * FROM OPENXML &#40;@v_xmlhandle, '/root/prod_waiverofpremium', 2&#41; WITH &#40;plan_type varchar&#40;30&#41;, product_name varchar&#40;30&#41;, emp_prod_rl_id bigint, waiverofpremium_effective_date datetime, waiverofpremium_end_date datetime &#41; &#41; select * from #temp_PROD_DETAILS DECLARE @v_nextrowid INT SET @v_nextrowid = &#40;SELECT COUNT&#40;*&#41; FROM #temp_PROD_DETAILS&#41; WHILE @v_nextrowid &gt; 0 BEGIN UPDATE PCPRCE SET PCPRCE.WAIVER_OF_PREM_EFFECTIVE_DATE = temp.WAIVER_OF_PREM_EFFECTIVE_DATE, PCPRCE.WAIVER_OF_PREM_END_DATE = temp.WAIVER_OF_PREM_END_DATE, PCPRCE.MODIFIED_BY = @p_user_ad_id, PCPRCE.MODIFIED_TM = GETDATE&#40;&#41; FROM PLAN_CLS_PROD_RL_CLS_EMPLOYEE as PCPRCE INNER JOIN #temp_PROD_DETAILS as temp on PCPRCE.PLAN_CLS_PROD_RL_CLS_EMP_ID = temp.EMP_PROD_RL_ID WHERE temp.rowid = @v_nextrowid AND PCPRCE.ACTIVE_IND = 'Y' AND PCPRCE.VERSION_NO = 0 SET @v_nextrowid = @v_nextrowid - 1 CONTINUE END -- WHILE LOOP END END TRY -- ============================================= BEGIN CATCH EXEC dbo.SDIsp_HLIC_Exception_Log 'Error while assigning the Waiver Products to the Employee' END CATCH SET NOCOUNT OFF END -- ================= /* EXEC SDIsp_HLIC_Enroll_InsertUpdate_EmpWaiverOfPremium @p_employee_id = 860, @p_user_ad_id = 'EMPLOYER13', @p_user_role = 'EMPLOYER', @p_prod_waiverofpremium_xml = '&lt;root&gt; &lt;product_details&gt; &lt;emp_prod_rl_id&gt;535&lt;/emp_prod_rl_id&gt; &lt;plan_type&gt;TRUE GROUP&lt;/plan_type&gt; &lt;product_name&gt;BASELIFE&lt;/product_name&gt; &lt;waiverofpremium_effective_date&gt;2008/12/02&lt;/waiverofpremium_effective_date&gt; &lt;waiverofpremium_end_date&gt;2009/05/30&lt;/waiverofpremium_end_date&gt; &lt;/product_details&gt; &lt;/root&gt;' select * from PLAN_CLS_PROD_RL_CLS_EMPLOYEE where PLAN_CLS_PROD_RL_CLS_EMP_ID = 535 */ /* SELECT PC.CLASS_ID, CER.CLS_EMP_ID, PCP.CLASS_PRODUCT_ID, -- PCPRCE.PLAN_CLS_PROD_RL_CLS_EMP_ID, PCPD.PROD_LIFE_INS_DETAIL_ID, PM.PRODUCT_NAME INTO #temp_1 FROM EMPLOYEE E INNER JOIN CLASS_EMPLOYEE_RL AS CER ON E.EMPLOYEE_ID = CER.EMPLOYEE_ID INNER JOIN PLAN_CLASS AS PC ON CER.CLASS_ID = PC.CLASS_ID INNER JOIN PLANS AS P ON PC.PLAN_ID = P.PLAN_ID INNER JOIN PLAN_TYPE_MASTER AS PTM ON P.PLAN_TYPE_ID = PTM.PLAN_TYPE_ID INNER JOIN PLAN_CLASS_PRODUCTS AS PCP ON PC.CLASS_ID = PCP.CLASS_ID INNER JOIN PLAN_CLASS_PRODUCT_DETAIL AS PCPD ON PCP.CLASS_PRODUCT_ID = PCPD.CLASS_PRODUCT_ID INNER JOIN PRODUCT_MASTER AS PM ON PCPD.PRODUCT_ID = PM.PRODUCT_ID INNER JOIN #temp_PROD_DETAILS AS temp ON PTM.PLAN_NAME = temp.PLAN_TYPE AND PM.PRODUCT_NAME = temp.PRODUCT_NAME -- INNER JOIN PLAN_CLS_PROD_RL_CLS_EMPLOYEE AS PCPRCE ON CER.CLS_EMP_ID = PCPRCE.CLS_EMP_ID AND PCP.CLASS_PRODUCT_ID = PCPRCE.CLASS_PRODUCT_ID WHERE temp.ROWID = @v_nextrowid AND E.EMPLOYEE_ID = @p_employee_id AND P.CLIENT_ID = @v_client_id AND E.ACTIVE_IND = 'Y' AND E.VERSION_NO = 0 -- AND CER.ACTIVE_IND = 'Y' AND CER.VERSION_NO = 0 AND P.ACTIVE_IND = 'Y' AND P.VERSION_NO = 0 AND PC.ACTIVE_IND = 'Y' AND PC.VERSION_NO = 0 AND PCP.ACTIVE_IND = 'Y' AND PCP.VERSION_NO = 0 AND PCPD.ACTIVE_IND = 'Y' AND PCPD.VERSION_NO = 0 -- AND PCPRCE.ACTIVE_IND = 'Y' AND PCPRCE.VERSION_NO = 0 select * from #temp_1 SELECT PLAN_CLS_PROD_RL_CLS_EMP_ID FROM PLAN_CLS_PROD_RL_CLS_EMPLOYEE AS PCPRCE INNER JOIN #temp_1 AS T ON PCPRCE.CLS_EMP_ID = T.CLS_EMP_ID AND PCPRCE.CLASS_PRODUCT_ID = T.CLASS_PRODUCT_ID --WHERE -- PCPRCE.ACTIVE_IND = 'Y' AND PCPRCE.VERSION_NO = 0 */ GO GRANT EXECUTE ON &#91;dbo&#93;.&#91;SDIsp_HLIC_Enroll_InsertUpdate_EmpWaiverOfPremium&#93; TO &#91;HLIC_READ&#93; GO / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Scripts Folder Stored Procedure Identical Name issue
Okay I forgot to have my V8 this morning just realized that real issue was not the fore mentioned SP but the one just below it dbo.SDISP_HLIC_GET_InquiryMaster.sql and the error message what tellin...
0 votes
Example of names are: dbo.SDISP_HLIC_GET_INQUIRY.sql dbo.SDISP_HLIC_GET_INQUIRY1.sql dbo.SDISP_HLIC_GET_INQUIRY2.sql These two SP are different Error Message: "A duplicate stored procedure name ([DBO].SDISP_HLIC_GET_INQUIRY]) has been found. This may occur if the SQL Server that you are reqistering is case sendsitive but the case sensitive option is not set" I've seen this once before when I was using Sql Compare v7.0 and we had some Full Text search code (Constraints) that it didn't like. The compare would keep added a new SP with a number appended it like above to the scripts folder. Trouble here is the SP are in the Live DB Thanks, BJHop / comments
  1. Community
  2. SQL Compare Previous Versions
  3. Scripts Folder Stored Procedure Identical Name issue
Example of names are: dbo.SDISP_HLIC_GET_INQUIRY.sql dbo.SDISP_HLIC_GET_INQUIRY1.sql dbo.SDISP_HLIC_GET_INQUIRY2.sql These two SP are different Error Message: "A duplicate stored procedure name ([D...
0 votes
Total activity
42
Last activity
Member since
Following
0 users
Followed by
0 users
Votes
0
Subscriptions
12