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 RepliesI am running SQL Server 2008 Enterprise edition and I was asked for a way to export the database schema (Tables and Columns and their connections to each other) to HTML. I tried googling this, but all I found was paid tools that offer this and I was wondering if there is anything integrated in the SQL server or a free tool that provides this functionality?
View 2 Replies View RelatedAnother problem.I have a report with a subreport(which is a matrix) which retrieves data from the database.My problem now comes when i export my report to html,together with the matrix subreport(which is so long),scroll bars wont show..What could be the reason for that?? I have checked the report properties but can't seem to get that..Thanks...
View 1 Replies View RelatedHey 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 RelatedHi
I am very new to sql...
I want to export the results of this query
select count(*) from mail where Completed='c'
to an html file.
Can this be done?
Thanks
Terence
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.
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
I have created a report with subreport.My subreport contains a graph/chart control which retrieves data from the database.My problem now,is when i deploy my report in HTML,the graph wont show.But it works perfectly in PDF and Excel..I am currentlt using now the getimage.aspx codes.I am confused now,what fornat will be used in the code(getimage.aspx.cs)Would i be evaluating the subreport??use HMTL as format??or PNG as fornat(because as i have read,PNG will be the format of the chart)??
This is my current code :
{
/// <summary>
/// Summary description for getimage.
/// </summary>
public partial class getimage : System.Web.UI.Page
{
protected void Page_Load(object sender, System.EventArgs e)
{
try
{
string reportPath = Server.UrlDecode(Request.Params["report"].ToString());
string streamID = Request.Params["streamid"].ToString();
string format = "HTML4.0";
ReportingService2005 rs = new ReportingService2005();
rs = (ReportingService2005)Session["rs"];
string mimeTypeImage = "";
string encodingImage = "";
byte[] image;
ReportExecutionService rptExec = new ReportExecutionService();
string deviceInfo;
string streamRoot;
streamRoot = "getimage.aspx?report=" + reportPath + "&streamid=" + streamID;
string extension = "";
Virtx.VF_ReportExecution.Warning[] warning = null;
string[] streamIDS = null;
switch (format)
{
case "HTML4.0":
case "HTML3.2":
{
deviceInfo = "<DeviceInfo>";
deviceInfo += "<StreamRoot>" + streamRoot + "</StreamRoot>";
deviceInfo += "<Toolbar>False</Toolbar>";
deviceInfo += "<Parameters>False</Parameters>";
deviceInfo += "<HTMLFragment>False</HTMLFragment>";
deviceInfo += "<StyleStream>False</StyleStream>";
deviceInfo += "<Section>0</Section>";
deviceInfo += "<Zoom></Zoom>";
deviceInfo += "</DeviceInfo>";
}
break;
default:
deviceInfo = "<DeviceInfo></DeviceInfo>";
break;
}
image = rptExec.Render("IMAGE", deviceInfo, out extension, out mimeTypeImage, out encodingImage, out warning, out streamIDS);
//image = rptExec.RenderStream(format, streamID, deviceInfo, out encodingImage, out mimeTypeImage);
Response.Clear();
Response.ContentType = mimeTypeImage;
Response.AppendHeader("content-length", image.Length.ToString());
Response.BinaryWrite(image);
Response.Flush();
Response.Close();
}
catch (System.Threading.ThreadAbortException) { }
catch (Exception ex)
{
Trace.Write(ex.Message);
Virtx.Tracer.WriteTrace(1, "Exception caught while performing getimage(): " + ex.Message);
}
}
#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}
#endregion
}
}
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
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.
I am using the query window n SQL server 2005 express to execute some sql statement but i want to export the resut to html.. is that possible within the sql statement?
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
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]....
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?
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....
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.
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).
Hello,
I just want to know how can I create a SSIS package to export a few distinct tables into distinct HTML pages.
If anyone can help.
Thanks in advance.
Best regards...
I need to export some Database data into a text file. My Query looks like this:
SELECT Category1, Category2, Category3
FROM dbo.tbl1
WHERE Category1 = 'JP-4'
AND Category2> 4;
This works fine to get the data, however there is some html formatting in the table entries such as
`<p>,</p>,
,</br>` etc.
So ideally I need to remove those when exporting the data to the text file. I've tried to do it with a simple replace query but that didn't work. I've also got an issue with line splits and would need to remove the ( ).
The Data format is something like this:
Category1: JP-4
Category2: 4
Category3:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p> <p>amet, consectetur, adipisci velit</p>
Category4:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p>
I got it to work like this with the replace function:
SELECT REPLACE(REPLACE("PHOTOGRAPHS",'<p>',''),'</p>','')
FROM dbo.khia_tbl
WHERE Category1= 'JP-4'
AND Category2> 4;
But the issue is that I've got 15 columns in total and that I need to do it for several different tags for each column so
,
</br>,
as well as "" and different spaces so that would be a lot and I thought there must be a better/more efficient way of doing it...
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
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 .
Hey folks,
I am looking for a way to export the DDL for table objects from MS SQL 2K. In EM you can right click a table, select "All Tasks" then "Generate SQL". When you preview the script, you can copy it to the text editor of your choice and save it. I'd like to find a way to do this programatically - either through TSQL commands or DTS would be best. I'm OK to do VBScript w/in DTS but I'm not up to writing a full blown VB app.
I seem to remember in my Sybase days that there was a utility called "defncopy" that could be used to extract DDL but there seems to be no analogue in MS SQL 2K.
The purpose of all this is to extract the data and schema of a table in ASCII text so that we can zip it and burn to CD for archiving purposes.
Any help appreciated!
Hi,
Not sure if this is a simple question...
I would like to export the schema of my database to allow me to print it out in an easily viewable form ( preferably in to visio ). Is this possible, perhaps through a wizard so i can just show the primary / secondary keys of the tables and how they all link ?
Many thanks for your responses.
Well I have NO IDEA how to do this... so I'll just admit my ignorance up front.
Anyway I have some customer records that need to be sent off for mailing.
The printing company takes XML files.
They provided me with an XML Schema (.xsd file)
I need to take the data in my table and export it to an .xml file to send off.
I tried doing something like this at first:
WITH XMLNAMESPACES (DEFAULT 'urn:blah.blah.blah...')
SELECT *
FROM
(SELECT CONVERT(varchar, GetDate(),120) AS submit_date,
'999999' AS client_billing_id,
customers.FirstNm AS customer_first_name,
customers.LastNm AS customer_last_name,
.....blah...additional fields..
FROM customers
) As Offset
FOR XML RAW ('Customer'), ROOT('CustomerMailingList'), ELEMENTS
Now that worked pretty well, it gave me a file that was formatted in what appeared to be the proper XML style, and I could save it and it "looked right".
However, the printers said there were a few errors and sent back a .XSD schema file for me to use.
Sounds great... but I haven't a clue on how to use it.
I've seen a few articles on how to manage existing schemas and how to store data IN the database, but nothing on how to EXPORT data.
As I understand it I just want to take data in my customers table and use the schema as a template / file mapping document to dump it out to XML.
So... help? :)
Thanks
This is a totally newbie question, but...
I've create a database, I'm able to script the schema to a query window, file, etc.
I can't for the life of me find out how to export my data so that it is scripted into insert statements. The data is standardized lists of data I will be distributing with the DB.
For those of you familiar with mySQL, this would be the output of the mysqldump command which dumps schema and data all into one file
mysqldumb <db> -u user -p > mydatafile.txt
thanks,
-David
Hi all,sorry for the rather trivial question but I couldn't figure this out bymyself.How do you export a db schema either from the commandline or from theGUI (if I remember correctly it's called Management Studio in the newversion).Thanks in advance,Lorenzo
View 2 Replies View RelatedWe have a "Comment" field that is saved as a HTML string to the DB. This field needs to be pulled into a report as rendered HTML.
I know this has been hashed out before, but has anybody found a good solution in the past couple of months?
We are thinking about storing two versions of the Comment in the DB: one with HTML, one as simple text. Has anybody found this an acceptable solution? I know it flies in the face of good DB design, but it seems the quickest, easiest solution...
Any word if this will be fixed in the next major release of SSRS? Can we expect this release any time soon?
Thanks for looking,
Smith
hi,
I'm reading some papers about reporting services; all is quite new for me so I wonder if the following is possible:
If I create a template for a report with two parameters (this must be possible according to my papers)... then, is it possible that my agents browse to an URL like: http://server/reportTemplate?param1='abc'¶m2='cde'
that they get an instant html report in their browser, based upon the actual data of that moment, and the parameters provided in the url.
is this possible?
thx
How can I create a script so that the field, when hyperlinked in the report, will open as an HTML file?
View 2 Replies View RelatedHi,
I want to display a varbinary field in the reports. the field contains value "<B> example </B>. When viewed in report viewer, it displays the value as such.
How to render it as html output?
thanks.
Hello Every one,
we have reports, for that we are using Visual studio and report viewer.
In Report viewer ,I Enabled print Option.
While i tried to Print on HTML report from report viewer; I set Properties Landscae but the output is coming like Potrait.
In my Rdl Interactive size Properties are width11 in and height 8.5 in
why it is coming like Potrait size.
can any one help me?
We've been struggling for 2 days to try and figure this out. We create a report with images, both in gif and jpg, and then setup a subscription. When the report is generated in both 5.0 and all browser format, no images appear, just the red cross. Funny thing is that in Firefow the same htm document renders OK. We tried setting the configuration table row UseSessionCookies to False as posted by others but this did not work. It seems so simple it's silly. What are we doing wrong? The image is set to embedded. Also the charts are not showing either.
Any help is MOST appreciated!!
BTW, in pdf and mhtml the images are fine
We do not have and _ in the the server name and we have SP2 also installed on a W2K3 server
Hi!
I have uploaded a html-page to the Report Manager, the html-page has a img-tag with a src="Picture1.png" in it. The image is then uploaded to the same folder as the html-page. When I browse the html-page the image is not found, red cross, does anyone know why this happens?
Regards,
Tommi