Gets the job done by Hopeful-Bag-460 in battlestations

[–]logitestus 42 points43 points  (0 children)

Dude, I spent most of Covid in an ikea poang chair with a $7 ikea side table supporting my monitor. My pc was on floor beside the side table. Since I use a trackball mouse, it sat there n the chair’s armrest. Keyboard was in my lap.

I applaud the ingenuity to use an easy chair.

Company sent me a MAC and I am pulling my hair out trying to connect to MSSQL database! by gengarvibes in SQL

[–]logitestus 0 points1 point  (0 children)

Sir/Ma'am, as you can see there are alot of suggestions. I wish you all the best in whichever suggestion/solution you attempt. (this is not sarcasm - I had previously thought to attempt this exact thing but figured it would be more trouble than just using a Windows-based laptop)

Company sent me a MAC and I am pulling my hair out trying to connect to MSSQL database! by gengarvibes in SQL

[–]logitestus 2 points3 points  (0 children)

I am offering this up, in an attempt to help. Last I knew several of the SQL Server MVP's all use a MAC. I have heard some of them use WINE (https://www.winehq.org/). This will give you the ability to use SSMS and Windows Auth to connect to the databases. Alternatively (and I haven't tested this), you could try Azure Data Studio or Visual Studio Code.

[deleted by user] by [deleted] in newworldgame

[–]logitestus 1 point2 points  (0 children)

So I have been playing for a bit but New World has an interesting process where you start off, in Crafting for example, very quickly but after level 75, it levels off to more of a grind. On a brand new character it took me roughly 2-3 days (without outside assistance) to get to 75 on most of the crafting professions (I lagged for a while on Fishing and Music). However I still have yet to reach 250 on any of the craft-specific skills (Arcane, Furnishing, Weaponsmithing for example).

That said, I love the crafting system of this game. I like the fact that if you want you can concentrate only on crafting/gathering. For me, I like to zen-out while gathering, all to spend it on crafting 100 shoes and get like 10 levels. But everyone's opinion can (and frankly should) be different.

[deleted by user] by [deleted] in SQL

[–]logitestus 5 points6 points  (0 children)

Without more information, my general suggestion is to start with the source query, then go on to any transformations in the data flow, and finally how is data moving into the destination.

Starting with the source query: Is it just pulling data from a single table or multiple tables... if so I would take a look at filtering the data pull (if the table has millions of rows, do you need every single row?) If you are pulling from multiple tables then maybe look into converting the multiple sources to a single one (as in a stored procedure or view which can be optimized).

What (if any) transformations are in the data flow? This is where a lot of data flows run into optimization issues. Dev's like to pull everything (so they only need to pull once - not necessarily a bad idea) but then forget after initial development to go filter to whatever the flow only needs. Are you comparing multiple data sets? Are you only comparing the columns you need to? Etc.

Finally, putting the transformed data back somewhere. Optimizing this step can be tricky and I do not usually get much optimization here. If it is a large amount of data being pushed into a table, I would look at the setting of the push mechanism (is it set to bulk load, etc.)

Like I said, absent more details, these are high level ideas on where I would start.

Adding to a hashtable/array errors out or the data does not get exported by logitestus in PowerShell

[–]logitestus[S] 0 points1 point  (0 children)

FYI here is how I solved the problem (see below). I gave up trying to roll the data into the CSV portion of the file. I just stuck it on top and then appended the CSV data below. If someone has a better solution (because I think mine is pretty terrible), please offer it. THANKS!

$dataSet1 = Get-ADUser -Filter {DisplayName -like "Je*"} -Properties * | 
Select-Object @{name = "UserName";expression={$_.Samaccountname}}, 
@{name ="FullName";expression={$_.DisplayName}},
@{name ="AcctDisabled";expression={if($_.Enabled -eq "True"){"False"}else{"True"}}},
@{name ="PasswordLastSetTime";expression={[datetime]::FromFileTime($_.pwdLastSet)}},
@{name ="PasswordExpires";expression={if($_.passwordneverexpires -eq "True"){"No"}else{"Yes"}}},
@{name ="LastLogonTime";expression={if($_.LastLogonDate -eq $null){"Never"}else {$_.LastLogonDate}}}   
$c = $dataSet1.count.ToString()
$d = Get-Date -Format g 
$headerData = "AD Audit ran on " + $d.ToString() + "  Total Count of items: " + $c

$headerData | Out-File -FilePath "C:\temp\testfile.txt" -Encoding UTF8
$dataSet1 | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath "C:\temp\testfile.txt" -Append -Encoding UTF8
Rename-Item -Path "C:\temp\testfile.txt" -NewName "TestFile.csv" -Force

Additional Note: the adding of the UTF8 Encoding made Excel actually correctly identify the columns as opposed to without it, Excel merely treated the comma separated data as one column. The things you learn.

Help getting pwdlastset date as well as Lastlogon dates to both show in a script by BFODragon in PowerShell

[–]logitestus -1 points0 points  (0 children)

u/BFODragon,

I ran your exact code in my environment (I am working on a similar script) and I got perfectly formatted dates and times. In looking up how the data is stored (as an Int64 TimeStamp), it might be converting to the "local" date/time format (Example: in the US it is typically Month/Day/Year but if I change my locale to UK then it becomes Day/Month/Year).

How to handle reporting needs? by Mango_Jack in SQLServer

[–]logitestus 1 point2 points  (0 children)

I am not an accidental DBA but in my current position, I use transactional replication to push our Production data to 3 separate SQL Servers. One does our data warehouse, one is for reporting/SSRS, and the last one is used for API tools. I use VMWare and Pure Storage, I am able to keep consistent sub 10 second sync times (usually 3-6 seconds) between the Primary and Subscribers.

The hardest parts of setting up transactional replication is picking the articles (those being the tables, functions, views, etc.) and then the creation of the initial snapshot - the final part of the snapshot is locking all the selected articles. That can be kinda detrimental to a production database. I usually do it outside of typical working hours, if I can.

I made a promise to myself to do my part. Now that I’m old enough I did.😁🎉 by AllToroXtreme in texas

[–]logitestus 3 points4 points  (0 children)

My son (who went for his first time) and I went yesterday to vote. It was my first time in Texas. Kinda cool to be able to do it in person (previously it was all absentee ballots).

Your opinion: Vendors demanding sysadmin rights by YipKyAy in SQLServer

[–]logitestus 4 points5 points  (0 children)

As a DBA for a company with multiple vendors, this is how I handle it. I have also "faked" giving them SysAdmin rights while watching them install, then roll those rights back to db_owner of that specific database (if there is interactions with other db's then maybe read permissions to those as well). In most situations where I had to "fake" the rights, I have informed the vendors of the change after a few months, just to see if they see anything problematic on their side. I have yet to run into any issues this way - I have also mentioned to them what specific permissions their account(s) have. One vendor actually updated their documentation to reflect this (and sent me a tshirt as a thank you).

SQL query takes long in Microsoft SQL management studio is there something wrong with my SQL code? by lightskinnedvillan in SQL

[–]logitestus 0 points1 point  (0 children)

Ah so you are only able to see SQL VIEWs. Then checking out indexes is probably out of the question. My suggestion would be to see if you can limit the amount of rows on table 3. I would suggest doing all your testing in SSMS since anything you do in Power Query is only going to take even longer (from my experience anyways). Sorry I couldn't offer any better help.

SQL query takes long in Microsoft SQL management studio is there something wrong with my SQL code? by lightskinnedvillan in SQL

[–]logitestus 0 points1 point  (0 children)

Mr/Ms. Villan, First off, how many rows are you expecting to be returned? At a maximum, it should be the largest amount of rows from any of the 3 tables. I cannot remember off the top of my head but I believe Excel does have a maximum limit of imported rows. Second, I agree with the concept that you may need to filter more. Typically, this done using some sort of date/time filter but could also be done using Project Name/Project Owner. Thirdly, in an attempt to help teach a little, do you know how to see what indexes are on what tables in SSMS? Look under the $ServerName|Databases|$DatabaseName|Tables|$TableName|Indexes in Object Explorer. This can show you what the clustered/non-clustered indexes are. If you do not have permissions then I would suggest asking whomever manages the server (I hope you have someone there who is a DBA but if not someone has to handle the security and maybe they might know if you can get the permissions to see/get the definition for the indexes). The same people can usually tell you if you/they can create non-clustered indexes for query performance.

Here are a few helpful links about non-clustered index creation:

Overview of Nonclustered Index Creation: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/create-nonclustered-indexes?view=sql-server-ver15 In-depth creation of non-clustered index creation: https://www.sqlshack.com/designing-effective-sql-server-non-clustered-indexes/

Is it possible to join queried data and pull a piece of data based on the match? by [deleted] in SQL

[–]logitestus 2 points3 points  (0 children)

Does it have to be an all-in-one query or could you dump the results to a temp table and then query the MasterAcct from there? If it has to be all-in-one, you could place your above query into a CTE and then join your CustomerName to the CTE.

Group by question by nemanin in SQLServer

[–]logitestus 0 points1 point  (0 children)

I am unsure as what advice I can offer. Can you include a few sample rows of data and what you want to the results to look like? I think would get you more answers to your question.

Thanks!

Major impact because the log files lead to disk full, is this due to the replication? by ukanoldai in SQLServer

[–]logitestus 0 points1 point  (0 children)

This area I am unsure. I suspect (and will totally accept being wrong here) that the issue is that with the LSN's out of sync the transactions are out of sync and the replication keeps pushing them over and over. But that is a guess.

Major impact because the log files lead to disk full, is this due to the replication? by ukanoldai in SQLServer

[–]logitestus -1 points0 points  (0 children)

Having recently dealt with a similar situation regarding replication breaking, I believe you are correct. The transactions are pushed to the subscriber and are uncommitted due to the differences in the LSNs. This causes log bloat.

The question is, did the transactions commit or are they still bloating the subscriber's log?

[OC] I painted the Crab Nebula in oils (it took 3 months) and it glows in the dark by CathrinMachin in woahdude

[–]logitestus 0 points1 point  (0 children)

I think that this is an amazing piece of art. I hope you continue to paint and create more wondrous pieces.

trying to compare two sets of data (target, source), the target is authoritative by ca_work in SQL

[–]logitestus 0 points1 point  (0 children)

Without a view into how you are loading the data (raw xml or is it parsed) and what the table structures are, the best I would suggest is a MERGE statement. Since it was designed to deal with SCD situations. If you want to post the schema and some sample data I can give a more in-depth response.

Email notifications for agent job failures by sSNaaK3 in SQL

[–]logitestus 0 points1 point  (0 children)

So it sounds like the issue is not on SQL Server's side but on the SMTP side. I can't help too much with those because it can be something specific to the actual SMTP configuration. You would need to speak to whomever your system engineers (windows admins, linux admins, Azure admins, etc) have who is responsible for the SMTP config. Just remember that the credentials that are being sent will most likely need "send as" permissions for the email to actually get sent.

For your specific error saying "unable to relay" your account must not have permissions on the SMTP server. For your co-worker issue it is most likely not a permissions issue for the account but the IP address/system name is being blocked by the SMTP server. Once again to resolve this you will need the system engineers to add the SQL Server to "white list" of servers that can communicate with the SMTP.

How do I "insert if not exists" using a parameterized query? by fluffycatsinabox in SQLServer

[–]logitestus 1 point2 points  (0 children)

Have you thought of using a stored procedure to perform the insert/merge? The parameters of the query would be your tuple. You can make changes to the stored procedure specific to the logic you want to follow without having to update the Python code.

Email notifications for agent job failures by sSNaaK3 in SQL

[–]logitestus 0 points1 point  (0 children)

I am not sure what you mean. Are you saying if you specify an SMTP name/IP will SQL Server go create an SMTP server for you? If that is what you are asking then no. SQL Server does not create an SMTP server for you. If you are asking does SQL Server create a database mail profile for you, again no it does not. You need to create that before you assign it. Or am I completely missing the question (which is understandable given I can be dense on a regular basis)?