SQL Server 2014 :: Extracting Data From Database?
Nov 5, 2015
I am running an hotel application for all the bookings.
I would like to get all the data of all the guests that stayed in the hotel (name, stay duration and dates, payment, etc).However, the GUI would make me go over one booking at a time, which would take me forever.
The application has an SQL database locally on my computer.
Will I be able to use SQL server and extract all the guests data from the last year and export it into a file, e.g. A CSV file?
View 8 Replies
ADVERTISEMENT
Dec 4, 2013
And have chosen the destination - unstructered (flat) file. But the wizard proposes to export only one table (dbo.Acocount) and all the others from the list are not exported. How can I export ALL the data into one file.I need to do this to edit the syntax in the editor and then import this data and database structure into Postgresql
View 4 Replies
View Related
Jul 18, 2014
I have a drop down menu for transmission. Inside the drop down menu, there are 1.Any 2.Auto 3.Manual options
Now I want to retrieve from my database, if the client select '1.Any' option. Meaning the output I want it to display all the products which have both auto and manual for transmission.
I have no problem retrieving all the products which either only have auto option or manual option.
View 2 Replies
View Related
Sep 22, 2015
I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link. URL....Here is the code that I have tried -
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
[code]...
The access database file path is - 'C:SQL ProjectTestDB1001.mdb'.The Table from which I want to import the data is - [Table1001]. but when I run this script, I get this error -9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.
Secondly I need to make 2 more changes to the code posted above.
1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.
2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')
View 7 Replies
View Related
Dec 29, 2014
how to identify the data leakage in a database , as I heard in one of my environment?
what is the meaning for data leakage ?
View 3 Replies
View Related
Oct 14, 2015
how to fetch data from oracle database in sql server 2014
example:
oracle schema :t1
sql server :t2
now am in t2 sql server database
now am executing below query
select * from t1.tablename ;
View 1 Replies
View Related
Apr 8, 2015
My company is migrating all their servers to a new data center and I get to specify what we need for the db servers.
We've got a 22 prod servers (mainly physical) with a couple of TB of data on sql 2000 to 2012.
We expect to move to sql2014, and consolidate and virtualise where ever possible.
But I'd like start with specifying an overall architecture for this: some Best Practices to guide the build at a server and an installation level
View 1 Replies
View Related
Apr 21, 2015
USE <database>
select * from sys.database_files
and
select * from sys.master_files where database_id= <db id>
give me different size of memory optimized file in <database>
Microsoft SQL Server 2014 - 12.0.2456.0 (X64)
View 1 Replies
View Related
Oct 9, 2013
I'm trying to move specific data from three linked tables on a source database to three tables on a destination database by generating dynamic SQL INSERT scripts but am getting stuck on the last set of INSERT statements. I don't think I can use SSIS because the source autonumber fields may already exist on the destination side but I could be wrong.
For simplicity, Table 1 (T1) has one autonumber key of PK1 as well as other fields (A1, B1, etc.).
Table 2 (T2) has one autonumber key of PK2 and a foreign key (FK1) that links back to PK1 as well as other fields (A2, B2, etc.).
Table 3 (T3) has one autonumber key of PK3 and a foreign key (FK2) that links back to PK2 as well as other fields (A3, B3, etc.).
Like this:
T1: PK1, A1, B1, etc.
T2: PK2, FK1, A2, B2, etc.
T3: PK3, FK2, A3, B3, etc.
So, I'm able to query the source database T1 to generate my dynamic SQL INSERT statements that will be run on the destination side. I'm also able to generate my dynamic SQL statements to insert into T2 but when I get to T3 I currently am stuck figuring out how to insert because the destination side is unable to match it's FK2 to the just inserted PK2. It throws the below error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
View 3 Replies
View Related
Jan 22, 2008
Hi!
I'm analyzing the problem of extracting data from sql server 6.5 in order to load it into oracle.
I already know about the bcp tool, but I'd like to know if you would consider any other approach based on SSIS, or any other tool like SQL Load.
regards, and thanks in advance for your help.
View 1 Replies
View Related
Oct 1, 2014
I have a Windows Server 2012 R2 2 node cluster with SQL Server 2014 FCI installed. Data files are on a separate Windows Server 2012 R2 file server. Data files share has been permissioned to the SQL Server service and SQL Server Agent service accounts as Full Control. NTFS Permissions are Full Control.
When I try to attach a database
CREATE DATABASE AdventureWorksDW2012
ON (FILENAME = 'apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf')
FOR ATTACHI get this error:
Msg 5120, Level 16, State 101, Line 4
Unable to open the physical file "apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf". Operating system error 5: "5(Access is denied.)".
If I log into the file server (called APRICOT) and look at the NTFS permissions they all look good. I have also reapplied the NTFS permissions from the root folder down.
EDIT
If I log on to one of the nodes in the cluster as the SQL Server service account and navigate to apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATA and copy and paste the data file, it works fine.
EDIT2:
If I log on to the file server and Enable Inheritance at the root level, then Replace all child objects with inheritable permission entries from this object, I get this error:
User Account Control settings on all nodes and the file server are set to Never notify
View 0 Replies
View Related
Mar 22, 2002
Has anyone been able to solve the issue with setting up a package in EM, which connects to an Exchange(.edb) database and extracts the data into a SQL Server db. Previous message posts don't offer much insight. So far, all I have is using Access 2000, to connect & move the data, then extract from here.
Thanks in advance for any help.
BV
View 1 Replies
View Related
Feb 15, 2007
I have a form in Notes that is huge. I need to extract a part/field from that form into SQL SERVER. If I use NotesSQL Thru SQL DTSPackages, I am afraid it will run very long. Is there any other option on that?
Any help is appreciated.
View 2 Replies
View Related
Mar 15, 2007
Hey all, WE have a document management system where by Adminstrators can upload documents, once the document is uploaded the binary data is stored on in a folder on the web server. We used to stored the documents in the actaul db table, but we found that there were to many documents and it was using alot of space on db server. So my boss has decided we are now going to upload the binary data onto the web server. Currently we are donig this with new documents which have been added or documents which are gettinguploaded when reloading, but there are many documents in the db table which have not been updated and are still embedded in the db table. So i need to figure out how to go about copying the data storewd in the db table and storing it in web servers folder location. I've tried various things for a enitre day but im going round in circiles. MemoryStream mStream = new MemoryStream((Byte[])dtrResults["file"]); BinaryReader bReader = new BinaryReader(mStream); int intFileSize = (int)mStream.Length; Byte[] byteFile = (Byte[])dtrResults["file"]; i can get to this state but then how do i create a folder on the BinaryREader to then store the binary data of the file to the location. BinaryReader bReader2 = new BinaryReader(File.Open(strDocFolder + strSavedFileName, FileMode.Create)); int count2 = bReader2.Read(byteFile, 0, intFileSize); bReader2.Close();i've also tried this but when the file gets created in the folder there is no content. i do know that the file does contain content as ive tried this and downlaoding the file from that page acctually works string strContentTpe = WValue.WStr(dtrResults["contenttype"]); int intFileSize = VValue.VInt(dtrResults["filesize"]); / Byte[] byteFile = ((Byte[])dtrResults["file"]); //Downloads the data correctly Response.ClearContent(); Response.ClearHeaders(); Response.AddHeader("Content-Disposition", "attachment; filename="" + WValue.WStr(dtrResults["docfilename"]) + """); Response.AddHeader("Content-Length", WValue.WStr(intFileSize)); Response.ContentType = strContentTpe; Response.BinaryWrite(byteFile); I hope ive made some snese andthat someone can hlep me. Have a nice dayZal
View 2 Replies
View Related
Feb 7, 2001
I have a coworker who needs to extract data from Oracle into SQL Server 6.5 and Sybase as part of an interface that runs daily. How is the best way to do this?
Tony
View 1 Replies
View Related
Sep 28, 2015
I'm trying to extract some data from an XML column, into the demo below I would like to obtain the CommandText value but my attempts so far are in vain, I'm fairly sure its just a path issue in the .query command but I just can't seem to get it to work.
create table #demo (field1 xml)
insert into #demo (field1)
values ('<SharedDataSet xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/01/shareddatasetdefinition">
<DataSet Name="DataSet1">
[Code] ....
View 6 Replies
View Related
Apr 28, 2008
Hi All,
We are extracting the data from ORACLE databse into SQL Server 2005 database. Both are hosted on different servers.
For this we have created linked server on SQL Server and we using the OPENQUERY to extract the data. Its taking too much time to extract the data.
Is there any other alternate thing for OPENQUERY clause.
Please help me on this.
Thanks in advance.
Thanks,
Ramesh.
View 3 Replies
View Related
Mar 5, 2007
Dear Sir/Madam,
I have some data which is stores in Foxpro dbf files & Access 200 MDB file. Can i extract import these files into SQL Server 2005
View 3 Replies
View Related
Aug 26, 2006
Is there a way of extracting data from MS Excel using MS SQL Server 2005 Express?
View 1 Replies
View Related
Dec 17, 2010
I am using MS SQL 2008, and I am trying to extract data from a MySQL database. I am having trouble extracting the data I need.
In SQL Server management studio, I can see the linked server...I can browse the different databases on the server. I can see user and system tables in all of the databases.
When I try and query a linked table (select * from server.db.table) I get Invalid object name 'servername.databasename.tablename'.
When I try script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get an error '[servername].[databasename]..[tablename] contains no columns that can be selected or the current user does not have permissions on that object.'
This leads me to believe that it is a permissions problem, but if I have access to the MySQL database using MySQL and the same login/password) and can retrieve the data there, then I think my login credentials should be enough using MS SQL. I guess I think its odd that I could have enough credentials to get in and see table names, but not do a select against it.
View 8 Replies
View Related
Mar 19, 2015
From my query I am getting results like below in one of the column:
'immediate due 14,289.00
04/15/15 5,213.00
05/15/15 5,213.00
06/15/15 5,213.00
07/15/15 5,213.00
08/15/15 5,213.00
09/15/15 5,213.00
10/15/15 5,213.00
11/15/15 5,210.00'
this same type of many rows are there (i just mentioned one) but having same pattern with tabs as delimiter in between dates and amount.
I need something that shows Date on one side representing particular amount on the other
For Immediate Due it will be current date and the amount besides it.
how can I achieve this.
View 8 Replies
View Related
Mar 12, 2007
Dear all,
I am doing a project where I heed to extract data from a table in a sql server and then automatically create a bar chart with this data preferrably in PowerPoint (or excel). I was thinking myself that it may be done in vba/macro added to a button in powerpoint. If anyone knows how to do this i would be very grateful or if anyone know a useful website could they post it up.
Thanks for your help
Laura
View 3 Replies
View Related
Jun 3, 2014
1) We are providing a e governance solution for an organization,where we are providing a centralized database,Client have provided 5 Database server for the same.how can we position the Database Server? there are 5000 Concurrent users and 25000 users,SAN Storage for approx. 60 TB,Database size of 2 TB and growth of 1 TB every year
2) How many instance can we have for above said Case?
3) How much RAM Required ?
View 0 Replies
View Related
Sep 3, 2014
I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B". There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".I look at sys.databases : not encrypted.I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.
I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :
Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
View 6 Replies
View Related
Aug 6, 2015
I want to restore a database (from an encrypted .bak file) - but *not* over the live original if you take my meaning. Encryption is the standard AES-256 that comes with Sql Server 2014 btw. I don't want the original touched/altered in any way. I would like to capture a success message if possible.I can extract the physical device name of the database in question using the following code:
SELECT physical_device_name, *
FROM msdb.dbo.backupmediafamily
WHERE media_set_id =(SELECT TOP 1 media_set_id
FROM msdb.dbo.backupset
WHERE database_name='MyDatabase'
AND type='D'
ORDER BY backup_start_date DESC)
I would like if the newly restored database was rename to something different than 'MyDatabase' (as shown above) and has different logs than the original. If possible, and capture a success message when restored.
View 5 Replies
View Related
Aug 12, 2015
I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.
The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?
Is there any performance impact if we enable CDC on OLTP db?
Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?
What is the best way to implement CDC to take incremental changes for reporting.
View 0 Replies
View Related
Aug 11, 2015
Example of data in CSV are as follows:
"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS
[Code] ....
View 5 Replies
View Related
May 22, 2006
The situation is I need info from a certain SharePoint site that is in a sql database. The problem is The SharePoint site currently contains data from 9 different projects, all using the same database. The database is a complex setup and it would not be an easy operation to extract the special information alone. The database is run with Microsoft SQL and is comprised of 4 separate databases. 3 databases organize the data (500+mb of data total) and 1 stores the data for all the sites (12.5+GB and increasing at a rate of 100-300mb per day).
How can I get the special data I need from this setup???
Please help
View 3 Replies
View Related
Feb 6, 2001
hi all,
i have a device file which i know for sure contains all the data for a database (not the log). is there any way to recover the data from this file?
thanx.
:-)
hemant
View 2 Replies
View Related
Mar 27, 2008
I want to extract a particular portion of primary key field data.For example- suppose there are fifty data in PK field starting from 1...50.Now I want only to extract data from 25 to 50.It is bcos I want to retrieve only the new data that is inserted after a specified time.For example-today there is data starting from 1 to 25 which I have retrieved and saved elsewhere.By tomorrow there will be new data starting from 26 to 50 or more.Now I just want to extract this additional data from the database so that I dont need to extract all data again.Could anybody pls tell me how to do this.How can I do it using common SQL bcos I have to make a program for it.
View 1 Replies
View Related
May 19, 2004
Cheers
I would really apprecite if anyone could make a comment on this...
what i wanna know is....
there are two tables, called 'A' and 'B' with identical structures...
is it possible to Perform the following in a single SQL ....
* If A->Field1 = B->Field1 then A->Field2 + b->Field2
* Records of table 'A' which not in table 'B' and
* Records of table 'B' which not in table 'A'
Thanks
View 5 Replies
View Related
Mar 22, 2007
This is my sample table Data
table1
TagID|TagName
--------------
1|xyz
2|123abc
3|a3bc
4|arsdew
5|xyc324
..............
..............
TagID--Primary Key
table2
TopicID|Topic
----------------
1|jkkj
2|kjgg
3|jhkj
4|refc
5|huhf
8|fyhj
9|jjk
...............
.............
..........
TopicID---Primary Key
table3
DetID|TagID|TopicID
-----------------------
1|1|1
2|1|2
3|1|3
4|2|4
5|2|5
8|3|8
9|3|9
....................
...................
.................
DetID---Primary key
TagID and TopicID----Foreign key
i need Topic based on TagName.
This xyz(TagName) have a 3 Topic. i need the 3 Topic
Once i select 123abc(TagName) that particular Topic I need..
any Query in Single Line.......................
View 2 Replies
View Related
