Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'PartNrFabrikant'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'omschrijving'.
Msg 209, Level 16, State 1, Line 8
Ambiguous column name 'verkoopprijs'.
Msg 116, Level 16, State 1, Line 13
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
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.
I want to group the following data attached by CUSTACCT, YEAR, PERIOD(month). As you can see in the attached example, I see duplicates for late fees and Exchange fees but none for FLIGHT_HRS. I want to see the data grouped by CUSTACCT by YEAR then by PERIOD for each of these: FLIGHTHRSSUM, LATEFEESUM, EXCHFEESUM without duplicates.
Is this possible?
This is my current SQL
SELECT dbo.VIEW_FLIGHT_HRS_TOT.YEAR AS FLTHRSYEAR, dbo.VIEW_FLIGHT_HRS_TOT.PERIOD AS FLTHRSPERIOD, dbo.VIEW_FLIGHT_HRS_TOT.FLIGHTHRSSUM, dbo.VIEW_LATE_FEES_TOT.PERIOD AS LATEFEEPERIOD, dbo.VIEW_LATE_FEES_TOT.YEAR AS LATEFEEYEAR, dbo.VIEW_LATE_FEES_TOT.LATEFEESUM,
We are facing a problem with loading data from .pdf files from vendor..pdf files have data in tabular format and we would like to insert those fields into a SQL table.We do not want to insert the physical location of the file but, we need to insert the data within the file.How can we read a pdf file?
I have a scenario where one partner can have multiple competency like Grade A,Grade B or Grade C and Grade AB,Grade BC etc.
I need to pull the records of competency of higher level also there is a chance of changing this competency level in feature like Grade A of Grade AA like this.
What would be the best option to select this kind of records.
We have an application that can spit out our Facility Structure into the following format. However, we have a need to take that data and feed it into another system. However, as you can see it is organized in a PARENT / CHILD structure.
Indent Level Key: 1System 2Facility 3Service Line 4Division 5Department
If you notice, each additional row is the child row to its parent above it as long as the Indent Level Key continues to increment. Once we start going back up the structure we essentially have a new Division/Service Line/or Facility depending on how far back we jump on the Indent Level for that next row. Here is some sample data.
--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#Test_Data','U') IS NOT NULL DROP TABLE #Test_Data
--===== Create the test table with SET ANSI_NULLS ON
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?
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
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.
Recently, I partitioned one of my largest tables into multiple monthly field groups. For the current month, it is attached to my "Active' table. The older records are kept in the "historical" table. I need an efficient way to pull records when have a date range that can be spread across both tables.
Basically I've got two tables with a common column (in this case it's UserID)
The structure of the table Users is basically:
UserID FirstName LastName 1 Jim Smith 2 Nancy Orr 3 Ken Stephenson
And the structure of the classes table is
UserID ClassID 1 AAA 1 BBB 2 AAA 3 BBB
What I'd like to do is select the FirstName and LastName the users table who have a UserID in both Classes and Users.
The tricky part of it is that I only want to return the FirstName and LastName of userID who have multiple ClassID in the classes table. (hopefully I'm making sense when I explain this)
In this case I'd like it to return just Jim Smith's name because he's the only userID who has two ClassID entries.
So far I've come up with
select UserId, count(*) as MultipleClasses from classes group by UserID having count(*) > 1;
which outputs the UserID and a colum called MultipleClasses with how many classes each ID returned has.
how would I do something like
Select firtname, lastname from users where Userid=
The output from that initial bit finding users with multiple classes.
I'm having an issue in encrypting large documents. I know that previous editions ENCRYPTBYKEY had a maximum size of 8,000 byte limitation. Does SQL Server 2014 have any new features that overcomes this limitation?
Using SQL Server 2014 Std Symmetric key with AES_256
why the statement "where WD.WRKD_WORK_DATE BETWEEN DATEADD(DD, -6, GETDATE()) AND GETDATE()) = 0' is successful, but "WD.WRKD_WORK_DATE BETWEEN (DATEADD(DD, -16, GETDATE())) AND (DATEADD(DD, -3, GETDATE()))" is an invalid statement? I've tried a lot of different syntax variations of this statement and cannot get to work between two lookback dates.
I've been trying to make the following query more performant by breaking it up into smaller pieces.
SELECT MT.A3+MT.A4 AS A34,MT.A3 -- ,M.* FROM Master_TAB M JOIN (SELECT M.A1,t3.A3,t3.A4,M.A6,M.A2,ROW_NUMBER() OVER (PARTITION BY A1,A6,A3,A4 ORDER BY A5 DESC) AS rownum
[Code] ....
I know that the Spill is caused by the Sort but I can't remove the sort (sort can't be done in front end). My master table had 1.7 million rows and almost 200 columns (bad design? I know but can't be changed as there's too much that would be affected) every row is little over 1KB
Here's my attempt...
-- MASTER_TAB has 1.7 million rows and 50 columns CREATE TABLE [dbo].[tmp_ABC]( [A1] [varchar](13) NOT NULL, [A2] [varchar](5) NOT NULL, [A3] [varchar](4) NOT NULL, [A4] [varchar](4) NOT NULL, [A5] [int] NULL ) ON [PRIMARY]
[Code] ...
This is the Query that is causing the Spill (in reality I'm supposed to bring back all 200 columns fro the master table but for debug purposes I limited the columns)
Select c.A3+c.A4 as A34, c.A3, c.A1 -- M.* from tmp_DEF c join MASTER_TAB M on M.A1 = c.A1 and M.A2 = c.A2 order by c.A3, C.A4
if I just run the following I get no spill:
Select c.A3+c.A4 as A34, c.A3, c.A1 from tmp_DEF c order by c.A3, C.A4
as soon as I add the Master table as a Join I get the Spill...
I read many articles, tried many suggested things (creating indexes... clustered, non-clustered) without success. Maybe I'm totally in Left Field and should enhance the performance going another route?
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...
We have oracle linked server created on one of the sql server 2008 standard , we are fetching data from oracle and updating some records in sql server . Previously its working fine but we are suddenly facing below issue.
Below error occurred during process .
OLE DB provider "OraOLEDB.Oracle" for linked server "<linkedservername>" returned message "". Msg 7346, Level 16, State 2, Line 1 Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "<linked server name>".
I'm a web developer who writes transact-SQL to make my web applications run properly. I'm not real strong in other areas of SQL. Let me explain our set-up and then I'll explain what I want to do:
We have an ecommerce web site and all sales are saved in a SQL Server 2008 R2 database at our hosting company. We also have a local Windows 2012 network that has SQL Server 2014 Express installed.
Here is what I want to do:
I want to copy sales rows from the SQL Server 2008 database at our hosting company and save them in the SQL Server 2014 Express database on our local Windows 2012 server. I'd like to automate this if possible so that it happens each night perhaps. I know there is a way to schedule SQL jobs but I've never actually done this. I also would need to know how to attach to our hosting company DB as well as our local network DB.
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')
We have deleted 120GB of data but space did not released even after 2 days. Is there any reason for this? tell me how exactly it releases the space after truncating a 120GB table?
I am trying to replicate data from a view in the publisher to a table in the subscriber (transaction replication). I do not need the view's base table, or the view itself, replicated to the subscriber. I only want to data from the view to feed a table in the subscriber.
I would like to move the data / findings into a perm table?The reason for this is so clients can connect to the table using excel. I have another stored procedure which is setup in this process already and it works well. I basically have the stored procedure setup on a task to run early in the morning so when clients get up they connect and get their data.
USE [MMAUDIT] GO CREATE PROCEDURE [dbo].[MMA_AUDIT_QUESTIONS_SUMM_STG] AS BEGIN
If data is modified (by an insert, update, or delete) while the backup is running, will the backup contain those changes or will it be added to the database afterwards?
I was running an operation to shrink file/emptyfile a data file, and then remove it.
It blocked and caused a huge mess, I suspect on the removal part. But I want to confirm that the emptyfile completed (and that the engine isn't going to try to put more data in there for when I schedule the removal part again a week or more from now).
How does the engine know not to put any more data in there, and how long does that situation last?
