SQL 2012 :: Dumping Fields In A Single Column Of Database?
Apr 3, 2014
While working with a vast variety of support projects, i find a sql design where all the fields in a single form (say about 100 fields which are dump data as they are not related to any reports and searching criteria) are dumped in a sql database column in a XML format. See below an example
I have the following result set but I want to stack or transpose the 3 fields into a single column. I may add more fields later, but right now I want to know what's the best and simplest way.
I am developing an application for a big office which uses SQL Server 2000. Apart from my database, on that server, there are two databases by other companies. The administrator also has access to server but the client only wants him to backup the database.
I have two questions:
1) First of all (if it is possible) I would like to protect my own database from the other companies.
I don't want them to:
see the data in the tables (around 20 tables) make changes to the stored procedures (more than 100 stored procedures) be able to backup the database
2) The client will save sensitive data to the database (mainly currency amounts, salaries etc) which he wants to keep hidden. I am using float type for these fields and I would like to make the data encrypted. I could do it for nvarchar fields but changing these float to nvarchar would be time consuming.
I have a description field in a table which also stores unit of measure in the same column but with some space between them, I need to split these into two different columns.
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
where including/excluding a single column in an empty staging table would influence a resultset returning from distributed query? Both servers are SQL Server 2012. Nothing special about the staging table. It contains 12 columns with a mixture of INT and NVARCHAR(256) columns. In one case I exclude the column and the query returns in 17 seconds. When I include it the query does not return. Excluding the INSERT INTO the staging table and query returns in 17 secs with and without the column.
My site is hosted with 1and1. They tell me I need to dump my SQL database before uploading it to their site. I have never done this before. I have looked in the online help and have asked them for help, but nothing seems to work. I've tried queries like the following:DUMP DATABASE dbname TO DISK = 'C:folderfilename'It tells me it doesn't recognize "dbname", which is the name of the MDF file (without the ".mdf" extension). So I'm not sure what I should be using for database name.I'm using Visual Studio 2005 Professional. Can someone help me with dumping my database? Thanks!
Hi:)I need to dump data from database in to *.sql file. I try it using DUMPDATABASE but it not work that I need it.So, how can I dump data from database in to *.sql file and then run itusing: osql -E -i nameOfDumpedDatabase.sqlThanks for help, greetings, gregory :)
We have a particular database sat on SQL Server 2012 box along with about 20 other databases.
What I require is a method/Script/Audit that will simply track anyone who logs (successfully / unsuccessful) into this one particular database on the server (The single database is the key as the end user does not want information on any of the other databases that sit on the server), it also has to log time the attempt was made and it must track the logins via SQL Server or the application itself that is attached to the database.
Is it possible to put master database to single mode, if yes then whats the procedure. The reason behind the scenario is to restore master database from backup for which its strictly recommended to put the database in single mode. But I m not able to do so.
I am executing a stored procedure in one database (Database1) that pulls data from another database (Database2) that is the back end for a third party application. Some of the fields in that other database are now encrypted. I need to decrypt those fields but since the query is running in a database other than where the data lives (which is also where the symmetric key + cert lives), I am getting the following error: "Cannot find the symmetric key" Below is an example of what I am running in the stored procedure:
OPEN SYMMETRIC KEY [XXXXKey] DECRYPTION BY CERTIFICATE [XXXX_CERT]; select CONVERT(Varchar(50), DECRYPTBYKEY( <ENCRYPTED FIELD> )) FROM Database2.dbo.TABLE1 CLOSE SYMMETRIC KEY [XXXXKey];
What do I need to add to Database1 so the stored procedure can decrypt the data it pulls from Database2?
Why would I see high CXPACKET waits on a database instance with only one CPU? Since the server only has 1 CPU parallelism can't be used, or are I'm missing something here!?
SQL version Microsoft SQL Server 2012 - 11.0.5058.0 (X64) May 14 2014 18:34:29 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
Doing a data migration from one CRM to another and need to get a listing of all entities in current CRM, together with fields and field types. OK, I got the XRMToolBox which gives me that, but I'm hoping there is a sql tool out there that will do the same, plus give me a count on each field of the number of entries in that field in the database.
I'm struggling with this. I'd like to perform a joined query from two or more tables and take the same field from several rows of one table into seperate fields of a single row in a new table.
I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like: John Bob Number1
The only way around it now is that I store Other_Name in Table1.
Hi,All: I am using Sql 2000 db to storage my data,and I have a table including a column named "ScanDate" (Type: SmallDateTime),Now I just want to save the current system date to this column when I save my system data. when I saved it and I found that column value include the time data,not only date . So How can I just save date to my samlldatetime typed column ? thanks in advanced!
I have a very complex (for me, anyway) data transformation problem.
I've been given a flat-file of physician data from another system which must be automated for entry into the SQL server on a regular basis.
This was no problem until we discovered that several fields (all of which we wanted to use) had multiple pieces of information in them, separated by semicolons.
Well, this didn't seem to be too big of a problem, so I wrote a DTS activeX script to handle it. This is what I originally wrote:
'******************* Function Transform() 'Declare variables Dim strOffice Dim strOfficeNew Dim cChar Dim x Dim y Dim z
'Scrub values into new rows strOffice = DTSSource("Col050") x = 1 y = len(strOffice) z = 1
While x <= y cChar = Mid(strOffice, x 1) If cChar <> ";" Then strOfficeNew = strOfficeNew & cChar Else DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z DTSDestination("Addr_Office") = strOfficeNew strOfficeNew = "" z = z + 1 End If x = x + 1 Wend
'Insert final record after last semicolon If strOffice <> "" Then DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z DTSDestination("Addr_Office") = strOfficeNew End If
Transform = DTSTransformStat_OK End Function '*********************
This, of course, didn't work. WHat I got was the last part of the parsed data, which for the first record, was the second Address in the field.
I searched around, and found the following script that is supposed to allow multiple rows off of a single row, but I can't seem to merge the two and still get the data out clean.
'********** Dim nCounter nCounter = 4
Function Main()
if nCounter > 0 then Main = DTSTransformStat_SkipFetch DTSDestination("PatientNumber") = DTSSource("PatientNumber") Select Case nCounter Case 1 DTSDestination("PhysicianType") = "Admitting" DTSDestination("PhysicianId") = DTSSource ("AdmittingPhysician") Case 2 DTSDestination("PhysicianType") = "Attending" DTSDestination("PhysicianId") = DTSSource ("AttendingPhysician") Case 3 DTSDestination("PhysicianType") = "Referring" DTSDestination("PhysicianId") = DTSSource ("ReferringPhysician") Case 4 DTSDestination("PhysicianType") = "Consulting" DTSDestination("PhysicianId") = DTSSource ("ConsultingPhysician") End Select nCounter = nCounter - 1 else nCounter = 4 Main = DTSTransformStat_SkipInsert end if End Function '**************
I'm not a VB Script expert, so there's probably something very simple that I'm missing here... if someone could point it out, I'd be greatly appreciative.
How can we create a DB for a single attribute such as ORDER DETAILS, CASH RECEIPT, TAX INVOICE having more than 2 text fields.
Also, in every form attribute such as order id is not present - in order identify the same as a primay key. So, which other attributes or fields can be considered as a primary key.
Hi I need to export data to CSV file from Sql Reporting Services. I am including single quotations in my view to display in SRS for string fields. After export if we open in Notepad, for string field it is adding multiple quotations.
How to add single quotations for string fields - CSV files,
need help on update from one table to another like this this is my first table
tb_all_holiday
id
fname
Start_Date
End_Date
val_holiday
111 aaaa
15/03/2008 21/03/2008
1
222 bbbb
02/05/2008 09/05/2008
3
333 cccc
03/04/2008 15/05/2008
4
333 cccc
29/04/2008 07/07/2008
1
444 dddd
01/05/2008 02/05/2008
1
444 dddd
09/05/2008 19/08/2008
1
555 EEE
09/07/2008 09/08/2008
4
666 fff 10/09/2008 12/09/2008
1 this is my second table to insert into !
i need to insert to another table like this single row for each day from start_date TO END_DATE check each employee add row for each day insert all employee one after one
ID fname new_date val_holiday ----------------------------------------------------
question need help how can i use this code below not for insert i need it for update another table but only if exist the link to the code in this FORUM http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=3208536&SiteID=1&Quote=True
Adam Haines wrote:
GPS,
Since you have such a dependency on dates and date calculations, you should implement a calendar table. A calendar table will make calculation such as this much more simplistic.
Calendar table link http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.
Note the calendar table I use is a little different than this one. The only thing you will need to change is isodate to dt.
Now the code to get the results you need:
Code Snippet declare @t table( id int, fname char(4), Start_Date datetime, End_Date datetime, val_holiday int ) insert into @t values (111, 'aaaa', '3/15/2008', '03/21/2008', 1 ) insert into @t values (222, 'bbbb', '05/2/2008', '05/9/2008', 3) insert into @t values (333, 'cccc', '04/3/2008', '05/15/2008', 4) insert into @t values (333, 'cccc', '04/29/2008', '07/07/2008', 1 )
select id, fname, cal.ISODate, val_holiday from @t t1
inner join Calendar cal on cal.isodate >= t1.start_date and cal.ISODate <= t1.end_date
I have a database that is the publisher in transactional replication and also part of an availability group. I have put the pertinent certificates on all of the involved servers, and it is encrypted on all servers and operated as expected. However, we are adding additional security for personal data and we have targeted columns in multiple tables for column encryption. I have a master key and certificates that are stored in the master database. Following an example where I am to create the database master key:
-- Create database Key USE encrypt_test; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123'; GO
But when I try to create a certificate on the database:
-- Create self signed certificate USE encrypt_test; GO CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Protect Data'; GO
It get the following: Msg 15151, Level 16, State 1, Line 1 Cannot find the certificate 'Certificate1', because it does not exist or you do not have permission.
Can I add a database certificate to an already TDE enabled database and if not to I create the symmetric key through the certificate located on the master database? And how will that effect decrypting the column values in stored procedures and function on the user database?
I have a set of data spread across a number of tables regarding stock market data. An example of this follows:
Market Capitalization...
Date CompA CompB 01/01/11 100 5 02/01/11 102 4
Share Price....
Date CompA CompB 01/01/11 100 100 02/01/11 101 99
Event Data...
Date Company 01/01/11 CompA 02/01/11 CompB
Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.
I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.
For instance: SELECT Event.Date, Event.Company FROM Event
how do I now say.....
SELECT MarketCapitalisation.Column WHERE Column = Event.Company AND MarketCapitalisation.Date = Event.Date.
I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).
I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
i have database which has 25 tables. all tables have productid column. i need to find total records for product id = 20003 from all the tables in database.
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN (SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR (user2_guid = @userGuid))) AND (UserId IN (SELECT UserId FROM espace_ProfileProperty))
Hi,Would like to know the performance differenece between Multi-columnIndex vs Single Column Indexes. Let's say I have a table with col1,col2, col3 along with a primary key column and non-indexed columns.In queries, I will use col1, col2, and col3 together and some timesjust one or two of these three columns. My questions is, should Icreate one index contains col1, col2, and col3, or create 3 seperatedcolumns. I.e. each column has its own index. Any performancedifference?Thanks a lot.
