Does that make sense? I have 2 tables, one is an older version, one newer. The newer version has some additional fields, indexes, etc. I'd love to have a tool to run against them that would generate an ALTER TABLE script to modify the old table so that it matches the new table.
Is there anything like that out there?
SQL Server 2008 R2. SQL Server Management Studio 2016 CTP2. I am looking for a tool where I could create and save a nice graphical layout of some tables in a database and their relation to each other (how they join or connect). I get something like this in the Query Editor, where it shows the fields and joins of tables as I select them. However, I cannot save this representation to show say in a PowerPoint or Word document (Visio, etc.).Â
Is there a tool that would enable me to create a nice document showing my tables and their relationships, which would also allow me to annotate each table with notes about the fields, etc.?Â
I searched and found a reference to "Visual Database Tools" which is supposed to be part of Management Studio 2016. However, I cannot find such a set of tools listed anywhere in my menus.
[URL]
Hello,
I have SQL Server Express working however I don't see any tools to create a database and tables.
I am trying to create a database to store stock quotes that I will input from a c# program.
What tool should I use to design the database?
Thank you!
Toben
This is probably a problem with a pretty simple solution but i can't find the right control/data flow item to handle it
Scenario.
I determine the database date for my source data for a set of ETL jobs via a piece of SQL - this gets passed to a master package variable which is subsequently to be used as the "Load date" of the resulting child package ETL routines. However I only want the packages to run if the LoadDate has either not been run before or is the next one in the DW sequence.
To check for this, In my data warehouse I also have a table called Import_Registry where the date of each upload is stored at the end of the daily ETL routines. So I can obtain the potential NextUpload date via this bit of SQL script.
SELECT DATEADD(day, 1, MAX(Upload_Date)) AS NextUpload FROM Import_Registry
Problem. I need to compare these two dates (the source db date & the DW next upload date) to see if they match. If they do match, then I run all the ETL packages using the date. If they do not match, say for example if the source database date is less than the "NextUpload" date I want to exit the routines "gracefully" and log the failure.
How do I get this working - can't seem to get my head around how I can compare the 2 dates ?
Does anyone have an opinion on specific “data comparison tools�?
We are looking for something to use in our test or dev environments that will be able to compare snaps shots of the data in a database before verse after a test event.
We have been able to record and compare data in specific tables but are learning that other tables were also being changed that we didn’t track. We want to be able to see all changes to a database.
Michael
Can someone recommend what SQL Database compare tool to useand why?Thank you
View 1 Replies View RelatedHi all,
Does anyone know any open source tool out there for these purposes?
1. Compare data rowcounts from two databases of the same structure.(On different servers).
2. Compare the structures of two databases.(On different servers).
3. Tool a developer can run on his/her desktop to capture a log of the database that his application is hitting.
Again all, I am new to the database world. Thank you for all assistance.
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
I am looking for a tool to compare the result sets.
Is there any free tool or Microsoft built in tool that we can use to compare the result sets. Suppose if I change one thing in code and needs to test the am I getting the same result set or not? Instead of doing manually is there any way to compare both result set.
http://www.download.com/Firefly-Dat...j=uo&tag=button
View 1 Replies View RelatedHi,
I am not an expert with SQL2000 and need to create table(s) to match this xml format(if possible):
<?xml version="1.0" encoding="UTF-8" ?>
<MenuData xmlns="http://tempuri.org/MenuDataDataSet.xsd">
<Menu Id="RootMenu">
<Item1 Caption="About TTF FIS" Url="" Target="" Id="AboutItem">
<ChildMenu1 Id="AboutMenu">
<ChildItem Caption="FAQs" Url="Faqs.aspx" Target="_self" Id="FaqsItem" />
</ChildMenu1>
</Item1>
<Item2 Caption="Resource Solutions" Url="" Target="" Id="BizSolItem">
<ChildMenu2 Id="ResourceMenu">
<ChildItemMenu2 Caption="Resource Tracking" Url="" Target="" Id="AccItem">
<ChildMenuChild2 Id="AccMenu">
<ChildItemChild2 Caption="Resource_A" Url="Resource_A.aspx" Target="_self" Id="AItem" />
<ChildItemChild2 Caption="Resource_B" Url="Resource_B.aspx" Target="_self" Id="BItem" />
<ChildItemChild2 Caption="Resource_C" Url="Resource_C.aspx" Target="_self" Id="CItem" />
<ChildItemChild2 Caption="Resource_D" Url="Resource_D.aspx" Target="_self" Id="DItem" />
</ChildMenuChild2>
</ChildItemMenu2>
<ChildItemMenu2 Caption="Engineering" Url="Engineering.aspx" Target="_self" Id="EngItem" />
<ChildItemMenu2 Caption="Design" Url="Design.aspx" Target="_self" Id="MarItem" />
<ChildItemMenu2 Caption="Deployment" Url="Fire.aspx" Target="_self" Id="FireItem" />
</ChildMenu2>
</Item2>
<Item3 Caption="MES Solutions" Url="" Target="" Id="PerSolItem">
<ChildMenu3 Id="PerSolMenu">
<ChildItemMenu3 Caption="MES First Solution" Url="Education.aspx" Target="_self" Id="EduItem" />
<ChildItemMenu3 Caption="MES Second Solution" Url="Household.aspx" Target="_self" Id="HousItem" />
<ChildItemMenu3 Caption="MES Third Solution" Url="PersonalAccidents.aspx" Target="_self" Id="PerAcItem" />
<ChildItemMenu3 Caption="MES Fourth Solution" Url="Protection.aspx" Target="_self" Id="ProtItem" />
<ChildItemMenu3 Caption="MES Fifth Solution" Url="Retirement.aspx" Target="_self" Id="RetItem" />
<ChildItemMenu3 Caption="MES Sixth Solution" Url="Small Commercial Business.aspx" Target="_self"
Id="SmallBizItem" />
<ChildItemMenu3 Caption="MES Seventh Solution" Url="Term Life.aspx" Target="_self" Id="TermItem" />
<ChildItemMenu3 Caption="Others" Url="Yachts.aspx" Target="_self" Id="YacItem" />
</ChildMenu3>
</Item3>
<Item4 Caption="Our Company" Target="" Url="" Id="ComItem">
<ChildMenu4 Id="ComMenu">
<ChildItemMenu4 Caption="Identity" Url="Identity.aspx" Target="_self" Id="IdItem" />
<ChildItemMenu4 Caption="Promise" Url="Promise.aspx" Target="_self" Id="PromItem" />
</ChildMenu4>
</Item4>
</Menu>
</MenuData>
any help appriciated
Hi, hope someone can help.
I have a table of employees and two sites. Most employees only do shifts at the one site but may occasionally work at the other site. What i want is the details of those who have worked at both sites.
for e.g.
Employees
1 Dave
2 Peter
3 John
Site 1
Mon John
Tue Peter
Wed John
Thu John
Fri Peter
Site 2
Mon Dave
Tue Dave
Wed Dave
Thu Dave
Fri Peter
So the answer should be 2 Peter. Hope this explains the problem. Probably very obvious but it has me stumped.
Thanks
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
i have two tables one with id and one with storyid which match. i need to check which record is at top of table two. and then pull top four from table one where the top story from table two (storyid) is not one of the top four in table one (id). is this possible? can anyone help?
View 8 Replies View RelatedI have a table of customer information (ci), but I have to link to another table in order to get the street address (sa).
If I exclude the street address I get 33 results. If I make the link to street address I will only get 30 results.
What I found is that if the ci table field for country is blank and I try linking to sa table then that result will not be shown.
Is there a way to get all 33 results while linking the two tables?
Hello, Everyone
I had 2 tables contain data:
1) old_data: 601,195 records
firstname
lastname
address
city
state
zip
zip4
2) current_data: 410,185 records
firstname
lastname
address
city
state
zip
zip4
3)Questions: How I write a query make to delete records? From table 'old_data' delete match records reference to table 'current_data' and keep the remain good records are from 'current_data' table.
****Here's below my test query that came up with if there are any error and please provide me any suggestion or new query. Very important task ....Thanks you to all.
/*******
Delete current_data
From old_data Cross Join current_data
Where old_data.FirstName = current_data.FirstName and old_data.LastName = current_data.LastName
and old_data.Address = current_data.Address
********/
RV
I have a table variable @tbl with one column (user) with example Laura, Scott, and Kevin.
Then I have a table usergroups with two columns (groupid, user) with example:
1, Laura
1, Scott
2, Laura
2, Scott
2, Kevin
3, Laura
3, Kevin
3, Scott
3, Jim
4, Laura
4, Kevin
4, Scott
I want to find groupid 2 and 4 because they exactly match the content of @tbl.
I've been through a number of tutorials on how to enable row-level security based on a userID, but my problem is more complicated and I do not have sufficient understanding of report models to guess.
My security information is defined in a table within my database. It contains a username and an account mask. An account mask maps to 1 or more account codes contained in the other data tables in my report model. A user may have more than one account mask defined for his account.
I understand the concept of directly mapping the logged-in user to a field containing a matching username. Is it possible to do a two-step mapping, so that based on the user ID I can get the account mask(s) and then evaluate which account codes match the mask(s)?
Or is there a different/better way to set this up? Defining SQL roles/groups is not an option, because of some compatibility issues with external systems.
So far, because my security table has no defined relationship with the data tables, I have not even been able to get it into my report model (Would love any suggestions on that one, too.)
I'd appreciate any ideas or suggestions - even if only something to investigate.
Thanks,
Sarah
Hi All,I need to link two tables using partial word match.How can I write a SQL statement to do so? (I am using MS-Access.)Table One: [Table Name: tblStreet] [Field Name: Street]123 ABC Street124 ABC Street125 ABC Street1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street12 CBS Street100 Apple Road101 Apple Road102 Apple RoadTable Two: [Table Name: tblWord] [Field Name: Word]ABCCBSThe output should be:123 ABC Street124 ABC Street125 ABC Street10 CBS Street11 CBS Street12 CBS StreetCould this be done?Thank you in advanced.- Grasshopper -
View 2 Replies View RelatedI want to use Create a procedure if not exisit and Alter it if exisit.
Can I use Create and Alter keyword together while writing a procedure in SQL Server 2005? If YES than HOW?
Hi,
I'm a newbie with SQL so excuse my igorance. I'm running an application that uses MSDE2000 as its source db and would like to create a small [10 field] db that would also use MSDE. I want to use a separate db though and not share the first one. What I'm looking for is suggestions on a development tool to create the database with as I'm not familiar with SQL tools. I have a lot of Access experience though so the concepts are not new.
Database requirements are pretty basic.
10 fields [8 text, 2 numeric]
A button field to Add New Record
A search field
Column sorting.
That's it. I could do this in a pinch with Access so knowing what tool to use should make it easy. Any chance that I could do it with Access and then port it to a SQL db?
Thanks,
Bob
I need a tool that creates a script to recreate a db, including tables' data.Does it exists freeware ?thanks!
View 2 Replies View RelatedI have two table People and Employee, both have firstname and lastname as fields
I want to display only the names that don't match on firstname and lastname
CREATE TABLE #NAME1(FULLNAME VARCHAR (100))
INSERT INTO #NAME1(FULLNAME) VALUES('JOHN X. DOE')
INSERT INTO #NAME1(FULLNAME) VALUES('FITZGERALD F. SCOTT')
CREATE TABLE #NAME2(LASTNAME VARCHAR(25), MI VARCHAR(2), FIRSTNAME VARCHAR(25))
INSERT INTO #NAME2(LASTNAME, MI, FIRSTNAME) VALUES('DOE', 'X', 'JOHN')
INSERT INTO #NAME2(LASTNAME, FIRSTNAME) VALUES('FITZGERALD', 'F SCOTT')
My task is to find matches between these two tables on "name."
There are two tables
TABLE 1 (NAME - Groupseats)
id session course groupcode sub1 sub2 sub3
1 2015 ba1 137 HL EL Eco
2 2015 ba1 138 EL SL HS
3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS
2 15120002 2015 ba1 HL EL Eco
3 15120003 2015 ba1 SL EL His
4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
I am new to writing SQL code and I read that you can use ALTER statements to create an index for a table. How would I go about doing that? Everything that I have tried in Query Analyzer comes up with an error.
Any help is appreciated.
Thought I should post in the newbie forum for a while, instead. :-)
I have a couple of scripts that I've generated that drop a couple of system stored procedures and recreate them. I'm not sure why I did it in the first place, but I think it was that it wouldn't let me run an ALTER statement on them. Specifically, I'm now looking at sp_add_operator. I changed it to a 500 character email field instead of whatever it was (100, I think.)
/* Explanation: Why did I do that? SQL Mail is prohibited here, so I'm using CDO_Sysmail to email myself and the developers if a job fails. The list of people to email is determined by the owner of the database, who is also an operator in SQL. I get the list of emails from the email field of the operator properties. Hence, I need a bigger email field. Yes, I now know it would most likely be better to create an ADMIN database on each server for this kind of stuff. (Thanks to Tara for that blogged suggestion.) */
While I will probably go back to the default stored procedure, this got me to thinking: when would it be better to use an ALTER statement on a SProc rather than to do a DROP and CREATE?
Your thoughts, oh SQL gurus?
when i create a new stored proc, i say "CREATE PROCEDURE [sprocName]"
but the next time i edit the stored proc, it hasnt changed to "ALTER ..."
I manually change it to ALTER, and run it, and close it, and it reverts back to "CREATE..."
Is there some sort of privelage i have to enable? Has anyone encountered this before?
Help guys, please :-S
Also, if you know of a better place where i can post this thread, please let me know
Anyone have any compelling arguements for using one over the other?
Thanks
Mercy
Here is the alter statement that I am trying to use to create a relationship between 2 tables. This does not seem to work on mobile. What am I doing wrong?
ALTER TABLE [SubCategory] CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID])
REFERENCES [Category] ([CategoryID])
ON UPDATE CASCADE
ON DELETE CASCADE
Dear friends
I would like to alter columns so that I have have empty string records instead of null. How do I query all tables and all columns so I can alter the column properties programmatically?
Thanks
Cm
Without giving dbowner how can we grant permission to user to alter/create procs?
View 3 Replies View RelatedThe database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement, Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP' This returns the error message"Ad hoc updates to system catalogs are not allowed".
What is the correct way to delete a group of tables whose name match a pattern from within SSMS?