SQL Server and SSAS
-
We have a custom process that automatically refreshes cubes in SSAS. At this moment it is quite simple. I want to expand the process so that, when a refresh fails, the owner of the cube gets an email about the information of the error. When a cube fails to refresh, the error message returned looks like below:
OLE DB provider "MSOLAP" for linked server "ZAPPE1SQL2\SQL2016" returned message "The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: 'OLE DB or ODBC error: Could not find stored procedure 'db.schema.sp'.; 42000.
OLE DB or ODBC error: Operation canceled; HY008.
The database operation was cancelled because of an earlier failure.
'..".
Msg 7215, Level 17, State 1, Line 5
Could not execute statement on remote server 'ZAPPE1SQL2\SQL2016'.The actual error is on the very first line. However, the problem is that when wrapping the whole thing in a try/catch block, it only returns the error "Could not execute statement on remote server 'ZAPPE1SQL2\SQL2016'". Any idea how to return the actual error message?
-
I managed to throw a horrible hack together. I am calling sqlcmd through xp_cmdshell and reading the output from there. Yes the words "horrible hack" can not be emphasised enough, but that's the only option I have for now.
-
@Vault_Dweller said in SQL Server and SSAS:
Any idea how to return the actual error message?
First question: is the message actually in there somewhere in the first place? (If the information isn't being handed back, you only have the option of horrible hacks, of course.) Exceptions can have all sorts of squirrelly bits inside in all languages so you'll need to take a good long look around a real example. In particular, if this is Java then be sure to check the
causeproperty of the exception and, if you're unlucky, thesuppressedproperty too. But there could be other things too.
-
@dkf The process is written in T-SQL.
And for those unfamiliar with SQL errors, a more detailed explanation of the exception:
OLE DB provider "MSOLAP" for linked server "ZAPPE1SQL2\SQL2016" returned message "The JSON DDL request failed with the following error: Failed to execute XMLA. Error returned: 'OLE DB or ODBC error: Could not find stored procedure 'db.schema.sp'.; 42000.
OLE DB or ODBC error: Operation canceled; HY008.
The database operation was cancelled because of an earlier failure.
'..".
Msg 7215, Level 17, State 1, Line 5
Could not execute statement on remote server 'ZAPPE1SQL2\SQL2016'.
As mentioned, the error I'm interested in is on the first line (I'm interested in the part in Italics). The actual error thrown by SQL (the only part in red, which is the identifier for errors, and the only part that can be caught) is in bold. The rest are the equivalent of print statements in SQL.
-
@Vault_Dweller said in SQL Server and SSAS:
The rest are the equivalent of print statements in SQL.
That's what I feared. Horrible hacks it is then.
-
So, I have started a project at work to get rid of all xp_cmdshell calls on our server, while having completely forgotten about this "solution"
Which means this question is open again, though I suspect there isn't an actual solution for this.
-
@Vault_Dweller I have absolutely no idea if this helps or not. I'll just leave it here for you to shoot it down.
Need to get the SQL Server "PRINT" value in C#
I have a SP that Prints the results into SQL Server, but I need to use that value in C#. Changing the PRINT to SELECT is not an option right now. I tried to SqlCommand.ExecuteScalar() but that did...
-
@Zecc This might actually be useful as a last resort. The problem is that I'm not using C# at the moment, I'm doing all the calls from within SQL Server. However, I can probably create a CLR SP to do the processing, which should enable this option for me.
-
I have no idea if you can do it in SSAS, but when using ODBC the client can request the error messages through diagnostics functions.
Handling Errors and Messages - SQL Server
Learn about what diagnostic information is returned when an application calls an ODBC function, including success or failure and detailed information.
SQLGetDiagRecI highly doubt this will be available to you, but that's likely where the SQL errors are being sourced from (well I'm sure there's a native client function that does this, since you're probably not using ODBC...?)
-
@Tsaukpaetra I'm using SQL Server's native OLE DB driver to connect to SSAS from SQL Server. A quick Google suggests that this isn't available to me.
-
@Zecc Actually, when executing the statement through a CLR stored proc, the OLE DB error actually comes through as a proper error, rather than an informational message. So handling that is trivial through C# try/catch. But you get an upvote anyway for getting me to use a CLR stored proc.
-
On a side note, it's amazing how many processes I have created over the years which use xp_cmdshell.
-
@Vault_Dweller said in SQL Server and SSAS:
On a side note, it's amazing how many processes I have created over the years which use xp_cmdshell.
And yet the inheritor will not be amazed, by and large. Enraged, disappointed, resigned, shell-shocked, do come to mind.
-
@Vault_Dweller said in SQL Server and SSAS:
On a side note, it's amazing how many processes I have created over the years which use xp_cmdshell.
Bad news... for security reasons, someone should have disabled xp_cmdshell at least ten years ago. Worse news, whoever came for xp_cmdshell will be coming for your CLR next.
It's not a great idea to use SQL Server as a client. By this, I mean that no process on the SQL Server should be making outbound connections (either to other servers or to itself). You shouldn't be running anything that matters as a scheduled SQL Job. Use Windows Task Scheduler, cron, or one of a billion automation platforms and build normal programs or scripts.
Really, just don't do it. Someone out there is going to say "but, what about database backups and database maintenance?". Use actual backup software for backups. At the very least, run a centralized process to connect to all of your servers and kick off backups.
-
@Jaime If you see the stuff that happens on our server you will have a heart attack, die, and then turn in your grave.
-
@Jaime said in SQL Server and SSAS:
whoever came for xp_cmdshell
That would be me
@Jaime said in SQL Server and SSAS:
no process on the SQL Server should be making outbound connections
I agree, but for this specific process I'm not sure there is an alternative. A little bit of background: We have an SP that gets widely used called LoadQueryIntoTable. This works the same as an INSERT … EXEC statement but with some additional functionality (data type validation, abitility to only insert subset of columns, etc.). This functionality is achieved via a C# program (which is part of a bigger framework). You have to understand our culture to understand the existence of this SP, but in short, it needs to exist. I have tried implementing the functionality in SQL itself, but it is impossible to derive the metadata of some queries, which is the requirement here. We have a "new" solution which doesn't call the exe directly, but calls an SP on a linked server, where the framework's database resides. This call can't be done via linked server directly as some calls are done via Service Broker which runs under sa. The CLR will use a service account.
So my question is, if you can't avoid having SQL Server as your client (via LoadQueryIntoTable) what are your options?
EDIT: I lied. This is another use case. We discussed a better solution for my original problem this morning.
-
@Vault_Dweller said in SQL Server and SSAS:
So my question is, if you can't avoid having SQL Server as your client (via LoadQueryIntoTable) what are your options?
This seems like a typical case of something a middle tier server should do. You need shared functionality that is reasonably sophisticated and touches multiple data sources.
@Vault_Dweller said in SQL Server and SSAS:
You have to understand our culture to understand the existence of this SP
I'm sure your culture is one where either your tools or your people only know one way to work: bind a UI element directly to a database action. If this is the case, then this is the root of all of your problems.
-
@Jaime Let me rephrase my question. Developers develop SQL stored procs. Within these SPs, they sometimes need do an INSERT INTO... EXEC... statement, but with some added functionality, which isn't achievable within SQL itself. How would you solve this?
-
@Vault_Dweller Yes, if you have to do it from a sproc, your options are limited. But I’d question why it “has” to be a stored proc to start with, instead of an outside process/executable.
-
@Vault_Dweller said in SQL Server and SSAS:
@Jaime Let me rephrase my question. Developers develop SQL stored procs. Within these SPs, they sometimes need do an INSERT INTO... EXEC... statement, but with some added functionality, which isn't achievable within SQL itself. How would you solve this?
Maybe a trigger or calculated field
-
@Jaime said in SQL Server and SSAS:
You shouldn't be running anything that matters as a scheduled SQL Job. Use Windows Task Scheduler, cron, or one of a billion automation platforms and build normal programs or scripts.
Huh. Is there some particular reason for this? Is this a SQL Server specific thing or a general DBMS recommendation?
We have an app (3rd party COTS that we integrate with) that uses SQL Server but we're mostly on Oracle (and I know we have some nightly DB jobs that run, though we have more that's fired off by external stuff, depending on what it is), and yes, we follow all the STIGs and get audited up the ass on them and this is not something that's ever come up to my knowledge (though I'm not intimately involved in the STIGs or the audit process, so...).
-
@Vault_Dweller said in SQL Server and SSAS:
Let me rephrase my question. Developers develop SQL stored procs.
They should stop. T-SQL is a horrible language for anything other than set based operations. CLR stored procs are the right technology, but in the wrong place.
The main problem with writing code in SPs (except code whose sole purpose is to access or mutate data in the database the SP lives in), is that eventually you're going to need to access something else. An encryption library, an external service, another database, etc. Once that happens, your decision to "write all my app code in stored procedures" comes back to make doing the work you need to do much harder.
-
@Jaime I think I forgot to mention that this is a Data Warehouse for reporting and analytics. SPs are used for data cleaning, modelling, etc. The reporting layer is also based on SPs. This may or may not be a WTF, but that's the way it is.
EDIT: We have over 1,000 reports, so "doing it another way" isn't really an option.
-
@boomzilla said in SQL Server and SSAS:
Is there some particular reason for this? Is this a SQL Server specific thing or a general DBMS recommendation?
General.
This becomes problematic from several perspectives.
First, from a security perspective, many database vulnerabilities start off as SQL Injection vulnerabilities. These are then leveraged by jumping out of the database and into the internal network. This escalation can be mitigated by simply turning off all features that provide a jump-off point. This is why Microsoft disabled the sp_cmdshell system stored procedure in default installs over fifteen years ago.
It is also really hard to manage identity and access if you call out from a database server. The modern way to do this is with tokens (usually OAUTH). Few databases have any support at all to do this.
Second, from a process standpoint, is the external calls are a common point of embedded configuration. At best, it makes building an environment more complex. At worst, it introduces risk of the test environment bleeding into production. It's one of the practices that make a DevOps transition really difficult.
Third, from an architecture standpoint, it hides a lot of complexity from decisions that really need to be aware of it. You end up with a recipe for surprise performance problems, cyclic dependencies, and surprise dependencies. You also can't leverage some common things like app flow monitoring and standard caching technologies because most of these are designed to handle HTTP requests and responses.
It also is a roadblock to database consolidation. I worked somewhere that consolidated over 800 SQL Servers to 12 mega-servers (for a license savings in the millions of dollars). With so many applications per server (and clustering on, so all 12 had to be identical), the rules had to be very streamlined. If you asked them to configure an ODBC connection so you can do a linked server call to an Excel spreadsheet, they'd simply say no and hang up the phone.
Fourth, database server licenses are typically the most expensive licenses a company owns. They are typically priced by the processor. Anything else that runs on a licensed CPU dilutes your value.
-
@Jaime said in SQL Server and SSAS:
Forth, database server licenses are typically the most expensive licenses a company owns.
Must be a very specialized Forth.
-
@Jaime said in SQL Server and SSAS:
@boomzilla said in SQL Server and SSAS:
Is there some particular reason for this? Is this a SQL Server specific thing or a general DBMS recommendation?
General.
This becomes problematic from several perspectives.
First, from a security perspective, many database vulnerabilities start off as SQL Injection vulnerabilities. These are then leveraged by jumping out of the database and into the internal network. This escalation can be mitigated by simply turning off all features that provide a jump-off point. This is why Microsoft disabled the sp_cmdshell system stored procedure in default installs over fifteen years ago.
It is also really hard to manage identity and access if you call out from a database server. The modern way to do this is with tokens (usually OAUTH). Few databases have any support at all to do this.
OK, I guess I misunderstood the point about DB jobs to be more general than you meant. Our DB jobs do internal DB stuff (update materialized views, etc), not call out to anything external. Yes, calling out from the DB seems obviously problematic to me.
-
@boomzilla said in SQL Server and SSAS:
OK, I guess I misunderstood the point about DB jobs to be more general than you meant. Our DB jobs do internal DB stuff (update materialized views, etc), not call out to anything external. Yes, calling out from the DB seems obviously problematic to me.
Microsoft's SQL Agent isn't actually a very good scheduling system. Did you know that when you create a job, the login that created the job becomes the owner? If that login is ever deleted, the job stops running. Additionally, the job status reporting mechanism leaves much to be desired, alerts need to be set up one at a time, each server is managed independently.
Most SQL Jobs simply execute a simple SQL statement on a schedule. There are plenty of products that will provide the same functionality with better manageability and visibility.
-
@Jaime said in SQL Server and SSAS:
Most SQL Jobs simply execute a simple SQL statement on a schedule. There are plenty of products that will provide the same functionality with better manageability and visibility.
In a (currently saner and preferable) "dumb data" approach, this holds. For the departing "clever data" approach, it doesn't. The driver for this cycle of reincarnation is effective distance between data and compute. If and when hyperconvergence gets legs, the pendulum will want to swing back to clever.
-
@Jaime said in SQL Server and SSAS:
@boomzilla said in SQL Server and SSAS:
OK, I guess I misunderstood the point about DB jobs to be more general than you meant. Our DB jobs do internal DB stuff (update materialized views, etc), not call out to anything external. Yes, calling out from the DB seems obviously problematic to me.
Microsoft's SQL Agent isn't actually a very good scheduling system. Did you know that when you create a job, the login that created the job becomes the owner? If that login is ever deleted, the job stops running. Additionally, the job status reporting mechanism leaves much to be desired, alerts need to be set up one at a time, each server is managed independently.
Most SQL Jobs simply execute a simple SQL statement on a schedule. There are plenty of products that will provide the same functionality with better manageability and visibility.
I have very little knowledge of SQL Server. On Oracle we don't generally have lots of logins. Certainly not assigned to people.
-
@boomzilla said in SQL Server and SSAS:
@Jaime said in SQL Server and SSAS:
@boomzilla said in SQL Server and SSAS:
OK, I guess I misunderstood the point about DB jobs to be more general than you meant. Our DB jobs do internal DB stuff (update materialized views, etc), not call out to anything external. Yes, calling out from the DB seems obviously problematic to me.
Microsoft's SQL Agent isn't actually a very good scheduling system. Did you know that when you create a job, the login that created the job becomes the owner? If that login is ever deleted, the job stops running. Additionally, the job status reporting mechanism leaves much to be desired, alerts need to be set up one at a time, each server is managed independently.
Most SQL Jobs simply execute a simple SQL statement on a schedule. There are plenty of products that will provide the same functionality with better manageability and visibility.
I have very little knowledge of SQL Server. On Oracle we don't generally have lots of logins. Certainly not assigned to people.
Each human and each application role and each maintenance task still needs one. The benighted make many of these the same.
-
@Gribnit said in SQL Server and SSAS:
@boomzilla said in SQL Server and SSAS:
@Jaime said in SQL Server and SSAS:
@boomzilla said in SQL Server and SSAS:
OK, I guess I misunderstood the point about DB jobs to be more general than you meant. Our DB jobs do internal DB stuff (update materialized views, etc), not call out to anything external. Yes, calling out from the DB seems obviously problematic to me.
Microsoft's SQL Agent isn't actually a very good scheduling system. Did you know that when you create a job, the login that created the job becomes the owner? If that login is ever deleted, the job stops running. Additionally, the job status reporting mechanism leaves much to be desired, alerts need to be set up one at a time, each server is managed independently.
Most SQL Jobs simply execute a simple SQL statement on a schedule. There are plenty of products that will provide the same functionality with better manageability and visibility.
I have very little knowledge of SQL Server. On Oracle we don't generally have lots of logins. Certainly not assigned to people.
Each human and each application role and each maintenance task still needs one. The benighted make many of these the same.
True. We really only have one component that doesn't have this. I had advocated to get it split out at one point but
carried the day. So our java app server and another C++ thing both use the same credentials.
-
@boomzilla said in SQL Server and SSAS:
So our java app server and another C++ thing both use the same credentials.
Say 25 Hail Edgars and be at peace.
-
@Jaime said in SQL Server and SSAS:
database server licenses are typically the most expensive licenses a company owns
Sounds like the right approach there is to say that, unless there's an actual proven need for the extra functionality that a commercial DB brings to the business, to use an open source database. Yes, they don't necessarily do as much, but cutting those license costs to and consequently increasing deployment flexibility is a powerful motivator.
No, fancy integration with SSIS is not a sufficient reason. Or at least not a several-million-bucks-a-year sufficient reason.
-
@dkf said in SQL Server and SSAS:
No, fancy integration with SSIS is not a sufficient reason.
How about a more locked-in integration, then?
-
@dkf said in SQL Server and SSAS:
No, fancy integration with SSIS is not a sufficient reason. Or at least not a several-million-bucks-a-year sufficient reason.
There are some days when facing certain issues when I'd say that Oracle's AWR is worth the cost on its own over SQL Server (which for us is more expensive anyway) or MariaDB.
@dkf said in SQL Server and SSAS:
increasing deployment flexibility
MariaDB Enterprise even has an Oracle mode which works with a subset of PL/SQL, although I've not had chance to try it out.
-
-
@Jaime said in SQL Server and SSAS:
Did you know that when you create a job, the login that created the job becomes the owner? If that login is ever deleted, the job stops running.
How often are you deleting you service accounts that this is an actual problem?
-
@Tsaukpaetra said in SQL Server and SSAS:
@Jaime said in SQL Server and SSAS:
over 800 SQL Servers
Geezus fuck!
800 SQL servers across a large enterprise is totally believable. My little chunk of the company has about 30 currently, and we're responsible for about 5% of the company's revenue*. Scale that out, and you get 500 company-wide (ignoring cost centers like IT), which is within shouting distance of that 800 figure.
@Tsaukpaetra said in SQL Server and SSAS:
How often are you deleting you service accounts that this is an actual problem?
How often do you have a service account creating a job instead of a person doing it manually?
(We make sure to assign ownership of the job to
dboto avoid this problem, but I can't imagine automating job creation for how we use them.)*
Somewhere between 0 and 15% - the (public) annual report lumps us together with two other business units for a total of 15%. In lieu of any more specific information I just divided that by 3.
-
@Unperverted-Vixen said in SQL Server and SSAS:
@Tsaukpaetra said in SQL Server and SSAS:
How often are you deleting you service accounts that this is an actual problem?
How often do you have a service account creating a job instead of a person doing it manually?
Pretty sure everyone just uses the sa for that.
-
@Tsaukpaetra said in SQL Server and SSAS:
Pretty sure everyone just uses the sa for that
There's a bunch of levels of maturity here. Most don't need to go whole hog, but maturity level 0 will bite you in the ass, which was the point of bringing up this little factoid in the first place.
Maturity Level 0 - Too many people are in the sysadmins role, and random people simply run SSMS and make jobs. Every time there's employee turnover, things seem to randomly break. These organizations probably assume the problems are due to sabotage.
Maturity Level 1 - sa has an easy password that everyone knows. Everyone manages SQL Server by running SSMS and logging in as sa.
Maturity Level 2 - sa has a complex password that is unique to that server and is regularly changed. People administer the server by running SSMS as sa via a privileged account management tool.
Maturity Level 3 - sa is disabled and people have dedicated admin accounts. Only the admin accounts have sysadmin to the SQL Servers. This practice re-introduces the problem of needing to assign jobs to a system account.
Maturity Level 4 - Jobs are built on non-production server, and scripted. The scripts are checked into a source code repository and some DevOps process runs the scripts in production.
