SQL Server
Everything SQL
-
SQLlocalDB – and How to add a latest version of a LocalDB instance
For those that develop with a backend DB, localdb has always been a good ‘go to’ option. A Dev db hosted on a localDB instance provides a good base that is simple to move to Production. It also has a benefit of Migrations. (Yes, SQL Server is not the only DB engine to provide such, but we will talk about SQL Server here). LocalDB is a handy, for free, SQL instance that basically comes with VS installations (yes, now a days you do have to pick the correct workload). With that in mind, those of us that long ago installed the venerable LocalDB, we were often suck with older versions…
-
BCP and Clustered SQL Oh the Errors
I got a curious message concerning problems that someone from work was having issues getting a relatively simple BCP (Bulk Copy for SQL) to work. So here we go: bcp "select top 5 [Data] from [ADataBase].[dbo].[ATable] where [ProcessedTime] <= GetDate()-1 and MessageType=''SomeProduct'' order by [ProcessedTime] desc for xml raw, root(''MyexportRoot'')"' + ' QUERYOUT ""B:\BCPBatch\Data\sample.xml"" -T -c -S TheSQL\Instance'; It’s a little complicated… Basically pull data from a table in DB on a SQL Instance… and output as xml to an xml file. After spending, a few hours, getting all kinds of errors about drive not found, path not found, can’t open the file… bah bah bah. It’s like what the…
-
A slight issue when one wants to install SQL 2019 Dev, etc The ODBC 17 SQL Driver
On my way to getting the latest SQL Server installed on one of my Dev PC’s- I ran across the issue of getting prompt for: msoledbsql.msi What the hell… ODBC Driver 17 is what that is about. Apparently, the SQL installed is not smart enough to know that one already has ODBC 17 SQL Driver installed and runs into a major “cannot resolve” the problem. The end result is that one does not get the SQL instance installed… But after reading lots of “not really helping” blurbs from the Internet, the Solution is simple… well mostly. You have to uninstall the “Microsoft ODBC Driver 17 for SQL Server” – one…
-
Powershell Create of the SSISDB Catalog
So when management gets this [insert explicative] idea to strip away my MSDN enterprise access, and I need to create an SSISDB catalog on my dev server- what the hell am I to do? Well, maybe not simple… I put on my PowerShell hack through hat. Shall we? In your favorite SQL instance (we’ll just use localhost for my examples) start with making sure you have CLR ENABLED first: EXEC sp_configure 'clr enabled', 1; RECONFIGURE; GO Do this in a query window in SSMS of course. Now the PowerShell: # Load the IntegrationServices Assembly [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") # Store the IntegrationServices Assembly namespace to avoid typing it every time $ISNamespace =…
-
The SQL Server Windows credentialing to work with ASP.Core and other IIS Web Sites
So somewhere in the backwaters of connecting an IIS hosted web app (a.k.a ASP Core 2.x SPA like app that uses SQL Server as it’s data host), we find the secret not so secret way to wire up a Windows Credential for Database access. Write this down: IIS AppPool\DefaultAppPool or Generically IIS AppPool\<Name My Pool> We start here in IIS with our favorite App Pool- Now this one is just for example… an ASP Core app pool, of course, would have a No Managed Code like this: Either way, the SQL Windows credential set up is the same…. In SQL server the new login looks like this: Note the IIS…
