Scripting Variables With Schema Compare Via Msbuild?
Jun 12, 2015
I'm trying to automate comparing the dacpacs we're generating from out builds against our production server to monitor drift.However, we use scripting variables to define cross database references.  The schema compare is showing up all the objects which reference the other database via scripting variable as being different to what is on the server i.e. it reports a change between a table referenced as  [$(db)].dbo.Table in the dacpac  and db.dbo.Table in the target database.
When I do a comparison in Visual studio between the project and the target database the variables seem to be appropriately replaced and the differences don't show. Â Obviously this is using a project instead of a dacpac but I'm hoping I can get the dacpac/db compare to behave similarly to the project/db comparison.
Is there a way to define what the scripting variables should resolve to when I run the comparison via msbuild?
Edit: Â I would prefer not to deploy the dacpac and diff the deployed db against the target database but if that's the only way....
View 5 Replies
ADVERTISEMENT
Mar 7, 2006
I'm trying to give permissions to a particular user to execute a collection of stored procedures. From what I read, it seems the best way is to define a role and schema - I've called it db_executeusersp, and use it to grant Execute permission to a set of stored procedures. I can then create the user and simply add that role to the user and all is wonderful.
But, I'm trying to figure out how to script this thing. In SQL Server Management studio, I can script the objects, but cannot figure out how to script the connection between the schema / role and stored procedure. All I get is a script of the object definition.
Any ideas??
View 1 Replies
View Related
Aug 28, 2006
Helo,
I'm using a simple scripting task to update the value of a variable:
Public Sub Main()
Dim EndeJahr As String
EndeJahr = InputBox("Text")
Dts.Variables("EndYear").Value = EndeJahr
MsgBox(Dts.Variables("EndYear").Value)
Dts.TaskResult = Dts.Results.Success
End Sub
The Package is working fine and the message box shows the actual value.
My problem is that when I change the of value of my variable, the value of variable is not changing, when I restart my package!!!
What's the problem?
any ideas?
View 8 Replies
View Related
Jul 20, 2005
Hey all,I am currently researching ways to compare databases via an XSD schema.I wrote a small app that creates a dataset from a database and exportsthat dataset to XSD. This gives me an XSD file with tables andrelationships representing the entire database.At this point, I am trying to find ways to compare these schemas. Doesanybody know of a way to do this easily and to record differences ifthere are any?Also, any information on comparing databases using any method would begreatly appreciated.Thanks,--Shock
View 3 Replies
View Related
Dec 3, 1999
I've searched quite a bit, and have found several leads on schema, stored procedure, and database contents comparison scripts and tools.
I'm now looking for recommendations on which ones are best, easiest:
ObjCompare.exe
sb_ABCompareDb.sql
sp_db_comp.sql
There's a mythical script from Andrew Z <mumble> that Mike Hotek talks about...
There's a DBCompare on the Back Office Resource Kit 2 CD, which of course is not in the umpteen MSDN CDs :-(
There's some *other* command line dbcompare, or maybe db_compare.
There's a DBA Compare.
I need to be able to compare divergent schemas from two developers to integrate their changes, so need schema and stored procedures compared only, and would also like to have something to compare staging servers and production servers.
Leads on other choices also welcome. I'd be happy to summarize and post, if warranted.
View 1 Replies
View Related
Oct 6, 2006
In the process of purging data to history tables,
we wanted to make sure that no schema changes have been done
to the main or the history table.
So to ensure identical schemas, we use this function:
ALTER FUNCTION dbo.fnCompareTableSchema
(
@t1Name NVARCHAR(257)
,@t2Name NVARCHAR(257)
)
RETURNS BIT
AS
/*
Compares the schema of 2 tables
If the schema is different RETURNS 0
If the schema is identical between the two table, RETURNS 1
NOTE: system tables or non-existant tables that are NOT in INFORMATION_SCHEMA views will compare equal (RETURNS 1)
==================================================================================================================
SAMPLE USAGE:
DECLARE @schemaOK BIT
SELECT @schemaOK = dbo.fnCompareTableSchema('dbo.table1','dbo.table2')
IF @schemaOK = 1
PRINT 'TABLE SCHEMA IDENTICAL'
ELSE
PRINT 'TABLE SCHEMA DIFFERENT'
==================================================================================================================
*/
BEGIN
IF @t1Name = @t2Name
RETURN 1
-- check if schema is different
IF EXISTS
(
SELECT*
FROM
(
SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
, COLUMN_DEFAULT, IS_NULLABLE
, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
, COLLATION_NAME
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t1Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t1Name,1)
UNION ALL
SELECTCOLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
, COLUMN_DEFAULT, IS_NULLABLE
, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
, COLLATION_NAME
FROMINFORMATION_SCHEMA.COLUMNS
WHERETABLE_SCHEMA = COALESCE(PARSENAME(@t2Name,2),'dbo') AND TABLE_NAME = PARSENAME(@t2Name,1)
) U
GROUP BY
COLUMN_NAME, ORDINAL_POSITION, DATA_TYPE
HAVING COUNT(*) <> 2
)
RETURN 0
-- schema identical
RETURN 1
END
View 6 Replies
View Related
Apr 16, 2015
I am looking for some SQL Scripts/tool to compare the two sql database and generate the difference results into Excel file. I am not looking for Sync/change scripts.
Example:
Result Type Desc
-----------------------
Col1 Column Added
Table1 Table Removed
View 9 Replies
View Related
Jun 30, 2015
Following an upgrade to SQL Server 2012, our shop's Schema Compare tool (Redgate SQL Compare) is no longer supporting our environment.We are starting to evaluate various 3rd party products to find a possible replacement, and would be interested in what products are favored by other IT shops who do a lot of database work.
Our shop is split about 75% SQL Server, 20% Oracle, and 5% I'll call other. Ideally a product would support SQL Server and Oracle, but our focus is on SQL server right now. On that platform we have ~50 servers spread across DevUATProd environments.In basic terms, we need a tool that can identify schema differences between DBs and generate synchronization scripts to support deploys between environments. Real-time synchronization is not a requirement (nor desirable), as deploys are a gated DBA function in our shop.
View 2 Replies
View Related
Mar 12, 2008
Just wondering if there are any tools available for SQL Server 2005 which allow the comparison and scripting of data and schema between two databases. This is so that I can migrate between Dev, QA, and Live easily.
A free tool would be best please..
Thanks in advance.
Gaj
View 11 Replies
View Related
Aug 25, 2014
We are getting regular SSDT/VS crashes when doing schema compares (source: project, target: server). Sometimes the compare succeeds but around 80% of the time we get a crash.
We are using VS2013 Update 3 and the latest version of SSDT.
Application : devenv.exe
Version du Framework : v4.0.30319
Description : le processus a été arrêté en raison d'une exception non gérée.
Informations sur l'exception : System.Runtime.InteropServices.COMException
Pile :
  à System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32, IntPtr)
[code]....
View 5 Replies
View Related
Jan 3, 2013
I've downloaded and installed the latest SQL Server Data Tools for VS 2012. Â Is there anyway to export the results of the schema comparison into a report in CSV/Html format? Â I understand that it can generate the sql diff script, but I want a readable report that I can use to show to people.
View 5 Replies
View Related
Jul 8, 2015
I just recently updated to SSDT 12.0.50512.0 using Visual Studio 2013 Ultimate. I typically use SSDT Schema Compare to synchronize my schema across multiple databases and different environments. After updating i encountered a major bug while updating our production schema.Typically during schema compare, the compare will prompt me to drop users and user roles from the database as they are not present in the project. I will exclude these so they database users and their roles aren't affected. After the update to SSDT I noticed that schema compare was only prompting me to drop the User, but didn't show anything about the user's roles. Not thinking much of it I went through my usual task of updating all the production databases. I soon found out that this did in fact remove the user roles even though it showed NOTHING in the schema compare UI indicating it would do so.
GO
PRINT N'Dropping <unnamed>...';
GO
EXECUTE sp_droprolemember @rolename = N'db_datareader', @membername = N'dbuser';
GO
PRINT N'Dropping <unnamed>...';
GO
EXECUTE sp_droprolemember @rolename = N'db_datawriter', @membername = N'dbuser';
You could say this is partially my fault for not checking the generated script before running it, but after months of this routine task I've never had an issue until this update.i'm not seeing the changes that will happen to my user roles in the schema compare UI?Â
View 2 Replies
View Related
Nov 22, 2012
I've made a comparison between a database project (sqlproj) and a database.
I can't exclude some items because they are dependent items on them. So they are implicitly included in the update script.
Unfortunately when I have unchecked all the items, some of them are still implicitly included.
It seems that there are circular dependency or some thing like that.
View 5 Replies
View Related
Jul 28, 2015
I am importing an existing database into a Visual Studio SQL Server Database project using Schema Compare. Â The Schema Compare works fine and I updated my project successfully. Â However, the project won't build because of the existence of 3-part names in some objects:
E.g. I import the database MyDB into a new project MyDB using Schema Compare. Â The database contains views with queries like this:
SELECT col1, col2. col3
FROM MyDB.dbo.MyTable
When trying to build this project I get errors like:
Error 39
SQL71561: View: [dbo].[vw_MyView] has an unresolved reference to object [MyDB].[dbo].[MyTable].
C:UsersRedirectionrittg2DocumentsVisual Studio 2012ProjectsMySolutionMyDBdboViewsvw_MyView_1.sql
but of course this is a bogus message since the view can clearly read from an object in the same database whether using 2-, 3-part naming (or 4-part naming for that matter).
How can I resolve these errors without editing the objects before running Schema Compare? (there are hundreds of them).
View 5 Replies
View Related
Mar 19, 2015
I'm rewriting a huge FOR XML EXPLICIT procedure to use FOR XML PATH, and need to compare previous output to the refactored one, so i didn't mess up XML structure.
The thing is, i'm not sure that SQL Server will always generate exactly same xml **string**, so i'd rather not compare by:
WHERE CAST(@xml_old AS NVARCHAR(MAX)) = CAST(@xml_new AS NVARCHAR(MAX))
nor do i want to manually validate every node, since the generated xml-structure is quite complex.
compare xmls by their "semantic value" ?
View 8 Replies
View Related
Aug 11, 2015
I have created Database projects in VS 2013 using SSDT. I have been mostly successful in creating and building the projects without any errors/warnings.
However for one of the databases in the project, when i do schema compare to apply the changes from a SQL Server Database to a Database Project in VS, code changes are not applied to the database project.
After i select the Update option in Schema compare window, I'm getting the following message
"Target update complete. Press Compere to refresh the comparison."
Even tough the message implies that target database is updated successfully, I do not see the objects i selected in schema compare being added to the target database project.
I see the following warning
"Target update: Could not update script for element 'dbo'"
I have 9 Database projects in the Solution and I'm able to apply changes to 8 of the database projects through schema compare successfully. I get the same warning after schema compare for all database projects.
I have same project level setting for all database projects in the solution. I'm using Visual Studio 2013 Premium Update 5 SQL Server Data Tools 12.0.41012.0
View 3 Replies
View Related
Jul 11, 2014
I have a database project where objects have been pulled in from the database using schema compare.
Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.
How to get these tables included in the project .
View 1 Replies
View Related
Mar 6, 2015
We're converting to new student info system. Sometimes registrar entered the same school into the schools table but spelled it differently. Trying to find all student assigned transfer credits from the same school but the school name is different. My db shows a max of 9 different schools students have rec'd transfer credits. Spending too much time trying to figure out best way to do it w/o a ton of IF stmts. Looking at Soundex and Difference functions. Still looks like a lot of coding. how to compare up to 9 string variables in sqlserver 2008?
View 2 Replies
View Related
Sep 6, 2007
What is the "official" line on deploying CLR SQL projects through the use of build scripts like NANT and MSBuild?
I'm obviously keen to hook up my project to my continuous integration build but the only things I have found that touch on the subject are:
Do it by hand yourself using xcopy of assemblies and T-SQL
Use SQLCLR project
This old post that says what *might* happen
Cheers
View 1 Replies
View Related
Nov 14, 2005
I have downloaded and "run" these programs:
View 6 Replies
View Related
Mar 3, 2006
VS2005 has a deployment feature that uses a database connection string to automatically connect to SQL2005 and create the assembly and procedures within the IDE. I am unable to access this feature from MSBUILD or any other command prompt utility so far which is a requirement for our internal build procedures.
I am looking for the ability perform a command line deployment of a SQL CLR project and pass in the proper server and database name also on the command line (or config file if necessary).
Does this functionality exist out of the box? If not, any suggestions on how to accomplish this?
-Ben
View 1 Replies
View Related
Mar 16, 2004
Hi to all
Is there any option in sql server DTS or any other third party tool that can script data. By scripting data i mean that....
if a table "Employee" contains 50 rows, i want the tool to write 50 insert queries for me so that i can run in it anywhere.
Problem is i have to insert data in a remote server where i cannot use DTS. I just have a text area to write my query and press the run button..
Hope u understand my problem. In case of any explanation please reply. Waiting for your response. Thanx in advance.
by to all
View 1 Replies
View Related
Sep 27, 2007
Locally I develop in SQL server 2005 enterprise. Recently I recreated my db on the server of my hosting company (in sql server 2005 express).I basically recreated the tables and copied the data in it.I now receive the following error when I hit the DB:The 'System.Web.Security.SqlMembershipProvider' requires a
database schema compatible with schema version '1'. However, the
current database schema is not compatible with this version. You may
need to either install a compatible schema with aspnet_regsql.exe
(available in the framework installation directory), or upgrade the
provider to a newer version.I heard something about running aspnet_regsql.exe, but I dont have that access to the DB. Also I dont know if this command does anything more than creating the membership tables and filling it with some default data...Any other solutions/thought on what this can be?Thanks!
View 4 Replies
View Related
May 27, 2008
I have 35+ tables and 15+ stored procedures with SchemaA, now I want to transfer them to SchemaB.
I know how to do one by one...!
alter schema SchemaB transfer
SchemaA.TableA
but it will take long time...!
Thanks,
View 3 Replies
View Related
Apr 12, 2008
Hello everybody!I'm using ASP.NET 3.5, MSSQL 2005I bought virtual web hosting .On new user registrations i have an error =(The 'System.Web.Security.SqlMembershipProvider' requires a database schema compatible with schema version '1'. However, the current database schema is not compatible with this version. You may need to either install a compatible schema with aspnet_regsql.exe (available in the framework installation directory), or upgrade the provider to a newer version. On my virtual machine it work fine but on web hosting i have an error =(What can you propose to me?
View 2 Replies
View Related
May 8, 2007
Hello,
I would like to use SSIS tool to move the data from one database schema to another database schema.
For example:
Source table has
1. UserName (varchar 20) (no null)
2. Email (varchar 50) (can be null)
Destination table has
1. UserID (uniqueidentifier - GUID)
2. UserName (varchar 50) (no null)
3. EmailAddress (nvarchar 50) (can be null)
4. DateTime
Questions:
1. What controls do I use in my Data Flow to make data move between databases with different data types and include new value in UserID as a new GUID and DateTime as a date (GETDATE)?
OLE DB Source, OLE DB Destination, Data Converson and .....
How do I insert Guid and Date at the same time?
2. I have many tables to do data moving. Any sugestions? How do I architect my project? If I create many data flows for each table - it will look complicated.
Please give me some advices here.
Thanks.
View 3 Replies
View Related
Sep 4, 2006
Hi,
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
João
View 8 Replies
View Related
Jan 24, 2006
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
View 2 Replies
View Related
Apr 19, 2006
I used SSEUtil to add a schema to my database but I am having problems. Used these steps:SSEUtil -c> USE "c:Rich.mdf"> GO>!RUN Resume.SQL//indicates success>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema not shown in list> USE master>GO>SELECT * FROM SYS.XML_SCHEMA_COLLECTIONS>GO//schema is shown in the queryIt appears that the schema is not added to the desired database, so when I try to use the schema in Visual Studio, the schema does not appear when I connect to the Rich.mdf database. Any ideas on what I am doing wrong or why this might be happening?ThanksKevin
View 3 Replies
View Related
Nov 21, 2011
I am using sql server 2008 R2.I want to copy all the objects of one schema and put it in another schema. I want to do that from command prompt.
In oracle we can export the objects of one user and import to another user using exp and imp. I want similar type.
View 5 Replies
View Related
Jul 22, 2007
Table MediaImportLog
column ↘ImportIndex ImportFileTime ImportSource
value ↘80507 20060506001100 815
80511 20061109120011 CRD � P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table BillerChain
column↘BillerInfoCode ChainCode
value ↘750 815
value ↘81162 CRD
Table Biller
column↘CompanyCode BillerCode
value ↘999 750
value ↘81162 516
TAble DataBackup
column↘CompanyCode Keepmonth
value ↘999 6
value ↘81162 12
---------------------------------------------------
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ?
thank you very much
View 3 Replies
View Related
Jun 29, 2001
SQL 7
I am trying to run a script written for an asp page (pure vbscript) in a active script job. I am getting errors on:
Server.CreateObject("ADODB.Connection")
Yes, ADO is up to date and loaded. The error says
Error Code: 0 Error Source= Microsoft VBScript runtime error Error Description: Object required: 'Server' Error on Line 14. The step failed.
Thoughts ..
Craig
View 1 Replies
View Related
Sep 14, 2000
I know it's a simple question, just can't figure it out:
How do I script a new column into an existing table in SQL? I am using MS SQL 7.0 and need to create several new columns in existing tables.
thanks all.
View 2 Replies
View Related
