How can I confirm that JDBC is running and properly configured/registered in the system? I followed the installation instructions but I believe there has to be more to it than what the instructions show. It just says to extract sqljdbc__enu.exe (it's an executable zip file) into "C:\Program Files...
@AaronBertrand I admit I don't know much about this, but would BPE not be potentially useful for Azure VMs running SQL 2014 Standard, limited to 128GB Buffer Pool?
@user285oo6 Single quotes are for string literals, double quotes are for quoting identifiers. And there must be no comma between a column identifier and its type in CREATE TABLE.
Fair enough. You could do 90 - 10 (which would yield 80) in VALUES, but you can't do 90 - JUL - 10 there. JUL looks like a column name to Oracle and you can't reference any columns in this context.
You probably meant to specify a date there. If I were you, I would google for something like oracle date literals.
@AaronBertrand Having seen the output from the past and present of enterprise application developers I see nothing much has changed ...
Nothing like working Data Warehousing to see the nasty scabby bits of the enterprise application world. I've pretty much concluded that most enterprise applications are developed by incompetent people.
will work just fine - if you use standard SQL syntax for the alias. nlsGuid = CASE is invalid (standard) SQL. If you change that to case .. end as nlsGuid your query works just fine (and will still work in SQL Server). When I do that, your example query and data returns exactly the same result in h2 and SQL Server — a_horse_with_no_name3 hours ago
The guy was using the + for concatenating and the SQL-Server's syntax alias = ....
(and blamed h2sql for the "bug" in outer joins)
I wonder what are these guys drinking. Another one:
So, one guy, 5 years ago, said that his tables were "collapsing" with 2 million rows and that's it? You did not test? (By the way, I've added -1 to that non-answer unreasonable claim.) — ypercube48 secs ago
@AndriyM ya the documnetation says it should be given in a dd-monnth-yyyy format
The ANSI date literal contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD'). Alternatively you can specify an Oracle date value, as in the following example:
@PaulWhite I'm not sure how big you can configure the SSDs on those VMs - do you buy your own and ship it to Microsoft? It may help in cases where your total data+index size is somewhere between 120GB and (spare space on SSD + 120GB). I had high hopes for this feature but it seems more practical to just opt for the edition + VMs with more memory. It's not quite like jumping from a standard boxed product to enterprise.
@PaulWhite this post suggests that you need at least 512GB SSD in that config (4x-10x mem) and that your expectations should be low if your physical memory is above 64GB.
I have a mail in to Evgeny to (1) update the post to get rid of the "Enterprise required" but and (2) address the questions about why > 64GB will yield little gain - I think he's explained a bit of that to us privately but he should address it there too.
Don't have independent data flows in the same task. I know the Import/Export wizard will do that but just because a team at Microsoft does something, doesn't make it a best practice. The Data Flow gets its power and performance through "free" parallelization. If you don't want that, please, for t...
Process Flow:
We have a Data flow task inside a For each container.
Inside the Data flow task we have 3 ole-db source connection.
Each source connection executes a stored procedure and their results will be passed to the flat file designation.
right now all the 3 ole-db executes at same time. ...
Yes, you are now a Superhero, able to wield the mighty Mjölnir.
The rules are:
You can instantly close as a duplicate any question that was originally asked with a tag you have a gold badge for.
You can instantly reopen any question closed as a duplicate that was originally asked with a tag ...
I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id)
My question is, is it possible if the project_id and user_id's data is different
like project_id = '12345' and user_id = 'abc987'
Please reply
kind re...
Couldn't use an FTP protocol from opera or chrome but the help desk guy said I had permissions to download. "He doesn't mean use IE on the public internet, did he?" DOWNLOADING
The SYSDATETIME() function is evaluated once in your query, because it is considered a runtime constant.
You can try a windowing function such as ROW_NUMBER()
INSERT INTO table1
(invoice,
detailline,
somedata1,
somedata2)
SELECT ROW_NUMBER...
We have disabled all forms of community wiki automatic conversions, not just for answers but for questions as well. The feature was never really about encouraging substantive edits as much as it was about prevent abuse. And in fact it punished people who made not just substantive edits, but lots ...
@AndriyM It can be very useful as part of a clustered index, often in the lead... but to presume that two things can't happen at the same time is very risky.
@AaronBertrand There's a question where I'm thinking part of a solution could be a foreign key referencing a PK or UQ that includes a date column. Not a datetime, though, so maybe that's okay.
Yes, for a date, probably ok. Probably some isolated cases where it's ok for datetime too (the same customer can't place the same order from the same machine at the same millisecond, for example). But I don't see the purpose in including that in the key there.
@PaulWhite writing on NOLOCK. I could swear I remember Sch-S still being taken out when NOLOCK queries are issued. Now I can't even get Sch-S to show up under read committed.
What kind of foolish thing could my half-awake brain be thinking?
> All queries, including transactions running under row versioning-based isolation levels, acquire Sch-S (schema stability) locks during compilation and execution. Because of this, queries are blocked when a concurrent transaction holds a Sch-M (schema modification) lock on the table.
Where a question concerns query performance, how should the query be included in the question if it is very long (tens or even hundred of lines)?
On SO, code posted in a question should be whittled down to just the relevant lines. However, with an SQL query, subdividing it will change the behavi...
@ypercube Yes it is confusing. I usually try to avoid the counter-intuive first example by writing the query more explicitly. I assume its SQL standard.
> The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
easy repro:
SELECT *
FROM
(SELECT TOP (100) OBJECT_ID FROM sys.objects AS o
--ORDER BY object_id
UNION ALL
SELECT TOP (100) OBJECT_ID FROM sys.indexes AS i
ORDER BY object_id
) x
SELECT TOP (100) OBJECT_ID FROM sys.objects AS o
--ORDER BY name
UNION ALL
SELECT TOP (100) OBJECT_ID FROM sys.indexes AS i
ORDER BY object_id
the second one has a merge join
I'm presuming from the ORDER BY on the set?
yep
If I put the order by in the outer select in the top query I get the same plan
@ypercube yes, ORDER BY with TOP in a union, subquery etc. only dictates the rows to include. ORDER BY without TOP is not valid in a union except for at the end, because order by can only apply to the whole concatenated set.
:17815400 yes exactly - since there is no outer ORDER BY, SQL Server only has to use the order by to determine which 100 rows to include, it does not need to sort the output of the subquery. Outside of a subquery, that's not true.
@ypercube give different results, or give the same results but potentially in a different order?
Our data has some small character fields, and a few floating point and integer columns. About 36 columns in all. It is about 700 million rows. Is it feasible to use MS SQL Server to host this data? It is basically read-mostly, and writes happen once per day in batch. I was told by a person in ch...
This isn't huge at all for a reporting dataset with bulk loads. Assuming your average column is about 6 bytes, you're talking about 140GB total data. I can't imagine someone in charge of managing data who can't imagine that SQL Server would have an issue handling this. — JNK ♦29 secs ago
SQL Server will have no problems with this amount of data, assuming that you are not talking about Express, you use adequate hardware, you design the tables and indexes intelligently, etc. You can prototype and benchmark to prove "the person in charge" wrong, or you can just believe them and move on to something else. Would be interested in knowing what they think could handle this data if they don't think SQL Server could. Are they one of these "MySQL is webscale!" sheep? — Aaron Bertrand ♦10 secs ago
@Kermit In Greece, they abandoned uniforms in primary schools about 35 years ago (high schools 5-10 years before that). 10 years later, the conformity was to dress according to the latest fashion trends.
And I closed as "too broad" but it's actually "too narrow" - the only possible answer, really, is that yes, SQL Server can handle this (but you, on the other hand, could design and use it in such a way that it can't - but you could do that with any platform). — Aaron Bertrand ♦29 mins ago
When I worked in hosting, we'd get some customers who needed 40 web servers and 2 database clusters to handle the same traffic that other customers with similar businesses could manage with 1 web server and 1 db server.
(Due to horrible design or bizarre platform choices like Microsoft Commerce Server)
You can't use variables directly as table names; you need to use dynamic SQL to construct the whole query & concatenate the variable with the table name.
DECLARE @tblName VARCHAR(30);
SET @tblName = '...';
EXEC (';with tbl1_CTE as (select * from ' + @tblName + ') select * from tbl1_CTE');
getting below error while trying to create catlog in slq server 2014 integration services. any idea what i missed in installation or anywhere else?
The catalog backup file 'C:\Program Files\Microsoft SQL Server\120\DTS\Binn\SSISDBBackup.bak' could not be accessed. Make sure the database file exi...
In your insert statement, it looks like you forgot to add the name of the row:
It should look like:
INSERT INTO Bucket VALUES (Thenameyouwanttogivetherow)
WOW! I'm not sure why you downvote me. The table has Bucket table has only 2 columns, of which the first column is a primary key which is automatically added. I mistakenly type Thenameyouwanttogivetherow, because technically he will add a new row to the table, with the primary key automatically added. — Auguste9 mins ago
Hi Max, this is on my DBA box that I use for offloading DBCC checks, so I'm using a good backup. The growth settings for these databases are controlled by best practices issued by the 3rd party vendor. I'm not allowed to touch them. They're set to percentages, which I disagree with, but it's out of my control. Management isn't open to changing because we charge clients by space used for hosting. I do monthly maintenance on VLFs but they have been this high before and I've run the restore with no issues. Thanks. — sqldriver31 mins ago
Right, big scans will constantly force other data back to disk again (assume "big" > memory + BPE size) so it doesn't really matter if they were in physical memory or BPE, you're still paying the cost of moving data from the real disk into either physical or fake memory
in our env replications are used for reports mostly, so yeah, most of it is read operations
@AaronBertrand didn't experience much contention, but from time to time there are rogue sessions blocking the replication jobs (nothing too serious, though). But in a busy environment I'd believe a separate distributor is needed, as it can get pretty busy and big if the transaction retention is long (by default the Distribution database gets all rows from a transaction as separate rows, so this can get fluffy).