I have a dataset I'm doing analysis on. It turns out it can easily be enriched with demographic and community data which vastly improves the analytical results.
In order to do this I'm joining in demographic and community data before doing analysis. I need to exclude some fields from my core sam...
In the RTM release of SQL Server 2012 (and in previous versions), yes, they are quite different. The Express version was missing a bunch of functionality, most notably the ability to manage SQL Server Agent (even on non-Express instances). There are other things missing like Profiler but technica...
@Aaron does this mean that you can get profiler with Express now or am I reading that wrong?
I accidently executed TRUNCATE command on a wrong table and all my data is gone. We do have a backup but its 2 weeks old and doesn’t have all the latest data.
Any ideas how can we rollback this command and get the data back if possible? Are there any third party tools that can do this?
We are trying to understand for academic purpose how a non clustered index with two columns organized internally inside SQL server 2008 as compared to non clustered index with one column. We tried Google, but did not get any convincing answers.
@ypercube I think it is still there for backwards compatibility reasons and the "who to blame" could be Ashton-Tate (DBase) which was involved in the early SQL Server days. DBase/FoxPro also uses * for integer overflows with the fine difference that it also stored the * in a integer column if a overflow occurred.
@JackDouglas I think it would be exceedingly churlish of them to bounce you unless they're really standing room only. Maybe you could ask Bethany Mazewski and see how many RSVPs they've got. Given that you're a mod on DBA.SE I think you've got as much right to be there as anyone.
I have my own Server with Voice Switch and not really familiar with SQL.
Having problem with following:
02-25 12:05:32 MySQL error description : Duplicate entry '0' for key 'UQ_CallsIDCC'
02-25 12:05:32 MySQL sql state : Duplicate entry '0' for key 'UQ_CallsIDCC'
(this error gives from switch co...
@bluefeet yes, I host some of the databases (mostly the pg and older MySQL ones) and the other day I changed my firewall settings... you can probably guess the rest
Which sadly I witnessed first hand. Not bending a door off against a concrete pillar but a college roommate opened the door and the pin holding the door together sheared off. Perfect moment of silence followed 5 us in unison "What'd you do?"
@ConcernedOfTunbridgeWells Unfortunately it isn't: I can't use it for what I was hoping to use it for... I might think of something else, but if you'd like it back please shout :(
Hi Jack. Thanks everyone for letting me know about the outage. Normally I have a script running every few minutes to check if everything is running smoothly. This has kept the site going strong for a long time, and would have still if I hadn't disabled it "temporarily" while doing some other work.....
@JackDouglas I don't have any immediate need for it. I've got a 640GB one in my dev box and I don't have anything with full-height PCI-e slots to put it in.
I'd be curious as to how my DB2 experience compares to others. We run it on zOS here. Seems such a hackneyed thing to use from an SSIS perspective as the environment name is part of the table name (DEV.Licensing vs PROD.Licensing) Oh well, time for scrum
I don't know if we have any DB/2 wonks on the books here, but @CadeRoux works with Teradata quite a bit and you can get VM images of their express edition. Not sure what the licensing allows you to do, though.
Well I've got to get ready for work, I'll check back later. If you all want to work with Jack on whatever else you'd like to see up there, that would be great. Thanks everyone!
@billinkc I can set you up with a VM, and you'll need to liaise with Jake about the rest: basically he needs a way of isolating the SQLFiddle run for other runs eg by wrapping the whole lot in a transaction/schema/database/whatever
@Zane It should be using connection pooling but to force single retained connection across the package, under your connection manager there should be a property RetainSameConnection or the like which is False by default
I have a multi-tiered job structure, and subjobs run within transactions, logging happens through a separate SQL connection, and you can set dependencies to allow parallelism
it does a top sort on steps and subjobs to make sure they run in dependency order
You can learn .NET through PS, I didn't know any before I started scripting
Favourite reason for loving PS is this: something is broken and I need to fix it. I can write a .NET application, send to DBAs saying "please run on the production server" and get laughed out the state. Write the same thing in PS since "it's just a script" and ask them to run and it gets done.
2
They could review the code but they won't whereas an EXE is a scary black box
@JackDouglas No, but I've been idly considering whether I should get one for my dev machine. However the 800GB ones are about £1,500 so I'll probably stick with my 320s for a while yet.
The guranteed performance consistency is a nice feature and they're way, way cheaper than the pulsars and suchlike from Seagate et. al.
@billinkc My favourite reason for using Excel/VBA as a dev platform for odd little satellite systems is that you can deploy it by email without having to involve production at all ;-}
Not that I would dream of deploying applications into production without going through the appropriate process.
@billinkc You're damn right it is. I don't know what you pack in to that .exe and I won't be arsed to parse your classes and inheritance model. A script that uses known and vetted objects is fine in my book.
@MikeFal Given that it pops up random contacts and badgers you to endorse them I'd take it with a grain of salt. However, like all such things not playing the game can have negative consequences so you're faced with Hobson's choice.
That's really the beautiful thing about .NET and even JRE. F' the languages. Don't like one, get another one that is more to your cup of tea. Just stop re-inventing the wheel for IO, string manipulation and all that junk.
@billinkc That's why we enthusiasts are pursuing things like the V8 core as the underpinning for a common JS runtime, and things like the module loading pattern, so we all agree on one common IO interface, as it were.
i'm starter in sqlserver indexing. i have one table with this Structure
CREATE TABLE [dbo].[Report3](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Mesc] [nvarchar](50) NULL,
[Line] [nvarchar](5) NULL,
[Unit] [nvarchar](5) NULL,
[Discription] [nvarchar](500) NULL,
[InvQty] [bigint]...
Looking in an actual execution plan, is there a way to quickly discern whether I'm subject to parameter sniffing? Is it a matter of running same proc with a different value and comparing plans?
Plan Explorer does contain a lot of nice things to make the information more presentable, and fix a few bugs etc., but it can only pick out what's already in the plan XML (and therefore often in SSMS properties)
@JackDouglas Surprisingly no, but the ODBC on Linux was not the best - and everything else on Linux is problematic and requires constant trawling of the Internet. I'm just saying if I had to write a script and I knew it might need to be portable, I would rule out PowerShell immediately (unfortunately).
@jcolebrand If js with node got a lot more mature real quickly...
And you can use node to script stuff on any environment, and you can distribute the node files between platforms. Especially if you use stuff like "path.join" instead of appending your own slashes.
I was wondering if anyone was aware of a way to connect to a Microsoft SQL database from Node.js. I'm aware of the MySQL drivers, but I have data that I need to pull from a MS SQL database and would rather pull directly from Node.js rather than hack a PHP script of some sort in place.
@jcolebrand I would want something which could execute stored procedures with parameters and deal with result sets completely portably between Windows and Linux (assuming an ODBC connection already set up on Linux). Bulk load is probably out of the question on Linux because TDS doesn't support it, but that would be really nice.
@PaulWhite Thanks for the reply @PaulWhite... after the DBAs researched it some more, the wait type turned out to be RESOURCE_SEMAPHORE... I believe they're just using the DMVs to determine this...
@PaulWhite I think we're just screwed... we're still running 32-bit for some unholy reason... and this just seems to be one of many issues that have resulted from that.
@MichaelFredrickson No worries, just making sure you knew. I wonder why you have so many queries waiting for memory? Some hardware to buy or queries to tune there I'd say.
@jcolebrand I would prefer to only be on Windows. But client was reluctant to spin up functional Windows Server separate from the SQL Server for these loads. Right now it's on a Windows VM in the cloud because of that. It's kind of silly. But after having to deal with that and seeing how well Perl handled the early experiments, I have to wonder why we don't have a cross-platform PowerShell. Windows is still easier to deal with for support and troubleshooting, so can it be all about OS sales?
@PaulWhite I think I just have a perfect storm of things that consume NonBufferPool memory... the applications open tens of thousands of connections to Sql Server (Don't use connection pooling), extended stored procedures, OLE automation, very very large query plans... It's been a problem for years, and I think the consensus is that our only viable fix is to get onto 64-bit.
@swasheck Very much so... it's a problem with the executives though. It was a startup where the founders of the company who wrote the original code are now the CTO, COO, CEO, etc... so in their minds, the code has worked fine for years... so if there's a problem now, it has to be in the database and its administration.
At some point, code can't overcome the limitations of the system. I'd definitely be pushing for a 64-bit upgrade. Plus probably beefier hardware, but I don't know your specs.
@MichaelFredrickson Crikey, you must have -g set to something horrible. 64-bit and more physical memory might help, but you're going to have to address the underlying causes at some stage. #TellingYouStuffYouKnow
@MikeFal well this too. it's easier to throw hardware at the problem. but by doing that you make yourself look foolish when , 3 months down the road, the same old problems resurface
then you've lost some credibility in the "you need to rewrite your code" game
@swasheck Sure. It's incumbent on me as a DBA to prove my case. But you can't cram the proverbial 10 lbs of crap into a 5 lb bag. There comes a time when, no matter how good the code, you need better hardware/system.
@jcolebrand Yes, that's how silly it is that they won't simply stand up a Windows machine for these things. Since we're starting to generate Excel reports from SSIS, they've basically committed to Windows for more other processes now as well. I think they have more servers coming in or something, but this is kind of inconsequential - I'm using the smallest Amazon VM.
@MikeFal oh, i'm not necessarily disagreeing with you at all. also, you're much better at this than i am so i'll usually defer to you. i'm just noting the politics that surround situations like this. find the biggest pain point and proceed with caution
@PaulWhite -g768; The server has 48gb of ram... does that constitute horrible? The DBA who originally advocated for that setting has since resigned, so I can't speak to the reasoning behind it.
@jcolebrand Various. I do SSIS development mainly on a Windows workstation, but can also do it in BIDS on the Windows Server VM, sometimes remotely from Chrome OS or OSX. Sometimes I edit the scripts on the Mac - whatever.
@jcolebrand The early ETL didn't really require Windows since I was bulk loading outside of SSIS, but now that we are doing quite a few Excel exports, Windows is kind of required for SSIS. Still, Linux would probably be preferred for them because of their reluctance to add a Windows functional server.
@jcolebrand But in that case, there would be a whole lot more to research about generating Excel spreadsheets in node.
@JNK For a cube it's probably better to do a daily snapshot by group by folder, although that will use lots of disk. For a SQL report you could do something with date ranges and just report any access records where the date falls in the range.
@jcolebrand It's actually xlsm because I have to have a macro to update pivot tables on the dashboard worksheet and data types on the data worksheets because of SSIS issues with numbers as text. That might not be an issue with an excel library in node that lets you rapidly fill multiple worksheets and gives you decent control over the datatypes of different columns - a la the old-school VBA Excel COM automation, which SSIS doesn't really do in it's Excel Destination mode.
@jcolebrand Another process is loading spreadsheets, processing them to generate exception reports in Excel and then potentially applying the changes.
@jcolebrand Well, it's the shoemaker's children - all their development resources are on their customers and distributors. There's all these partners doing administrative sharing and I don't think they have any resources to automate on their side either, so sadly it's going to be spreadsheets for data interchange. And offline reports via email including data which they can also play with are a requirement.
I have a table MyTable, which has a foreign key field OtherTableID.
I already have a few thousand rows in MyTable. Now I want to randomize OtherTableID so that it has some random (but valid) value of an entry in OtherTable.
Any ideas how to do this in one neat query, short of using a cursor?
@AaronBertrand I know. I just think that the "don't care about duplicates" was because he wants truly random values from the other table. But, that's just my interpretation
't care about duplicates" in my interpretation means that he'd be happy with an answer even if it provides duplicates. If he wanted duplicates, he wouldn't have stated it that way.
I see your point. But the way I see it is not that he really wants duplicates, but simply that he wants random values, not caring about duplicates. The way you did it (and I upvoted your answer because its a valid interpretation), kinda gets values that aren't "truly random", because it prevents duplication. I'm actually only asking because I would like to know what I had to do to make my version of the query work
10. SOLVE THE FIZZBUZZ PROBLEM WITH SQL CODE.
Here’s a quote of the FizzBuzz problem:
Write a program that prints the numbers from 1 to 100. But for multiples of three print “Fizz” instead of the number and for the multiples of five print “Buzz”. For numbers which are multiples of both three and five print “FizzBuzz”.
Challenge the DBA to do this on a whiteboard with pseudocode. The code doesn’t have to be perfect syntax, but we’re looking for the ability to solve this problem clearly and quickly. I’d ask this of any type of DBA, production or development.
Isn't the best way to do this in "pure" syntax to use a number table and a case statement?
@jcolebrand Yes, that would be the easiest solution... but if you want to be pedantic.. the requirements call for print, which might exclude the use of a set-based select approach.
@jcolebrand I think it would be a better question if it didn't say print... and see if they come up with the preferred set-based approach like you did...
@jcolebrand a cursor... or if you want something really ugly, you could have a select statement that builds up a dynamic sql command to execute 100 print statements...
@BrentO question about the deviousness of http://www.brentozar.com/archive/2009/01/top-10-interview-questions-to-ask-senior-dbas/ … should it TSQL _print_ or just select into a result set? ;-)
@AaronBertrand I think that that solution would perform more poorly than a result-set (which is probably not the right name, but that tabular data view that I can use programmatically)
to me a TSQL while statement is the equivalent of a cursor, regardless of syntax.
@jcolebrand if the object is to PRINT, though, that's like saying I'd love to get to work in a Ferrari. That's only useful if you have access to a Ferrari.
Why did PostgreSQL choose an elephant as it's logo? It wasn't clear from logo wiki.
UPDATE: Now wiki updated :) Thanks!
I accidently executed TRUNCATE command on a wrong table and all my data is gone. We do have a backup but its 2 weeks old and doesn’t have all the latest data.
Any ideas how can we rollback this command and get the data back if possible? Are there any third party tools that can do this?
We are trying to understand for academic purpose how a non clustered index with two columns organized internally inside SQL server 2008 as compared to non clustered index with one column. We tried Google, but did not get any convincing answers.
i'm starter in sqlserver indexing. i have one table with this Structure
CREATE TABLE [dbo].[Report3](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Mesc] [nvarchar](50) NULL,
[Line] [nvarchar](5) NULL,
[Unit] [nvarchar](5) NULL,
[Discription] [nvarchar](500) NULL,
[InvQty] [bigint]...
I was wondering if anyone was aware of a way to connect to a Microsoft SQL database from Node.js. I'm aware of the MySQL drivers, but I have data that I need to pull from a MS SQL database and would rather pull directly from Node.js rather than hack a PHP script of some sort in place.
I have problem connecting SQL Server 2008 R2, Yesterday I connected adn now I can'nt and get this error!
I have a table MyTable, which has a foreign key field OtherTableID.
I already have a few thousand rows in MyTable. Now I want to randomize OtherTableID so that it has some random (but valid) value of an entry in OtherTable.
Any ideas how to do this in one neat query, short of using a cursor?
Have you tried matching the case when you call the variables? it's possible your schema is case sensitive.
