C# SMO: Unable to get objects by name but able to get by index

Replicating Issue

StoredProcedure sp = db.StoredProcedures["spName"]; // Returns null

Does not return the same as 

StoredProcedure sp = db.StoredProcedures[0]; // Returns object

If I traverse the array (technically a collection), I will find spName. Even if I do:

StoredProcedure sp = db.StoredProcedures[0]; // Returns object

StoredProcedure spByName = db.StoredProcedures[sp.Name]; // returns null

Possible Solution

First I tried. https://www.tek-tips.com/viewthread.cfm?qid=1736324

StoredProcedure storedProcedure =
db.StoredProcedures.Cast<StoredProcedure>().SingleOrDefault(sp => sp.Schema == "YourSchemaName" && sp.Name == "YourSprocName"); 

This worked and this caused me to see that the schema is explicitly entered. After some more research, I could not find how you would provide the schema to the stored procedure name.

Later I tried to look under Database which has a property, DefaultSchema. After setting this, this also returned the correct result.

So the simplest solution:

db.DefaultSchema = "schemaName";

StoredProcedure sp = db.StoredProcedures["spName"]; 

Root Cause Analysis

For my specific case, our DBA changed the default schema of the user. 

Work Life: MS Sql SMO Script StringCollection, Understanding why result is a StringCollection

Understanding StringCollection with MS Sql SMO Script took me some time to understand better. For some reason it did not occur to me why there are multiple lines. I initially did not care, so I did some workarounds.

Initially, I didn't care because I was using it to compare the same objects across multiple servers so as long as all environments were consistent it did not matter, so I just joined it all together or removed the first two values.

I was then put in charge of managing the source control. The script that was generated for database project did not include the ANSI_NULL or QUOTED_IDENTIFIER options. There are over 10k objects so I cannot possibly manually remove them.

I tried to find a ScriptionOption to remove them but all the answerers bypasses the question by saying that it is required (or at least highly recommended). Most questioners problems are that they are missing the GO command.

Although there are plenty of questions to remove, I could not find any answers that removed them. I have gone through many versions of the same unsatisfying answers. The best is just to add it to the code to ignore those lines or add GO after each record.

Summary

Each record in the StringCollection is a SQL command. Although satisfying to know the answer, a little disappointed in myself that it took this look to figure it out.

I still don't like that none of the Microsoft solutions are ever consistent though. SSMS includes not only ANSI_NULLS and QUOTED_IDENTIFER but also the USE [DatabaseName]. While the SMO Script method includes the ANSI_NULL and QUOTED_IDENTIFIER but does not include the USE. And then the database import into Visual Studio or Azure Data Studio do not include ANSI_NULLS and QUOTED_IDENTIFER but includes a GO at the end.

Run as Script

Join all the rows with a GO command between each record:

myStringCollection.Join("\r\nGO\r\n");

For Source Control

Because database project does not include the ANSI_NULL and QUOTED_IDENTIFIER, I remove all those records. Requesting multiple objects is more troublesome, so I just request one object at a time. The reason it is more troublesome is because there is no strong correlation between the script and the object it is for without assuming it is in the same order as the request or parsing the script. Because this is for source control, it is not worth the risk of pulling the incorrect script.

Buggish: MS SQL SMO StoredProcedure ScriptHeader(false) with renamed stored procedure

The SMO StoredProcedure ScriptHeader(false) will return the original stored procedure name (ie this is the script originally used to create the stored procedure). Because it has been renamed, this is no longer the value that I want. I want a create script with the new name.

According to this post: https://social.msdn.microsoft.com/Forums/silverlight/en-US/6de78652-3780-403b-893f-da4ef8a01ed8/textheader?forum=sqlsmoanddmo

This is a known issue. It also references:

https://www.sqlservercentral.com/articles/ssis-%e2%80%93-transfer-sql-server-objects-debugged

https://www.sqlservercentral.com/forums/topic/ssis-%e2%80%93-transfer-sql-server-objects-debugged

My resolution is to use the ScriptHeader(true) and change ALTER PROCEDURE to CREATE PROCEDURE as it is easier than parsing or replacing the stored procedure name. I also need to use ScriptHeader to preserve the documentation in the header.

I also use this to move db objects from one server to another.

NOTE: I think this only happens if a rename is used. To workaround this before, I would just run the alter script on itself. I wanted to get away from that because it would change the last updated datetime which I also used as part of my fuzzy logic in determining script versions that are not source controlled.

Upgrading Microsoft C# SMO from 160.2004021.0 to 161.47008.0 "cannot convert from 'system.data.sqlclient.sqlconnection' to 'Microsoft.SqlServer.Management.Common.IRenewableToken'"

 ERROR:

"cannot convert from 'system.data.sqlclient.sqlconnection' to 'Microsoft.SqlServer.Management.Common.IRenewableToken'"

Convert all System.Data.SqlClient to Microsoft.Data.SqlClient

RCA

Updated Microsoft.SqlServer.SqlManagement from 160.2004021.0 to 161.47008.0

Buggish: Microsoft SQL SP Rename for SMO TextHeader and ScriptHeader

 Background: I use SMO to promote objects from one database to another.

Issue: I get error "The name specified in the TextHeader property of StoredProcedure 'StoredProcedureName' must match Name property."

Cause: The one cause I found related to this is when a developer renames the stored procedure. I am assuming they use the sp_rename function. For some reason, this proc does not update the TextHeader or the ScriptHeader. When pulling these properties, these still reference the old name. In my old post, this was a speculation. The reason I found this was because this specific promotion includes renaming an existing stored procedure. In the past, my best guess is that they renamed during development so never needed to submit a rename change in higher environments.

Workaround: My fix is to use SSMS to get the alter stored procedure and execute as is. This unfortunately updates the updated date, but better than being stuck.

Fix request: When renaming object, make sure to update other properties that references the old name like TextHeader and ScriptHeader. 

Reference

https://douglastclee.blogspot.com/2020/09/buggish-c-smo-error-name-specified-in.html

Buggish: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds. (no answer)

 I tried killing this SQL session, but it never disappears. When I use "KILL ## WITH STATUSONLY", I get:

SPID ##: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

I know what is executing it which was my web tool that edits the sql server. I tried restarting the App Pool but this did not do anything. I eventually IISRESTART and the session disappears.

Buggish: Microsoft SMO Index Fillfactor Defaults Unwanted Value Instead of 0, null, or nonexistent.

I am trying to copy objects from one environment to another. I do not care whether the developer created the objects correctly or not. I only care that I create an almost exact copy of the object to another database.

One of the features of my tool is to automatically pull the index from a lower environment and creating it in the new environment. For some reason, this automatically fills in the fillfactor to the default value of the target database which I do not want. One, this is not how the developer designed his index. Two, when promote that change to the next environment it will copy that defaulted fillfactor which may be different on the target database. 

So now I have no way of telling whether the developer intentionally added a fillfactor (which from what I read online is intentional) or didn't set a value but was added because Microsoft SMO doesn't set the fillfactor to the value that I want.

I have set fillfactor to 0 and to 100. 0 will always set fillfactor to the target database default fillfactor. 100 will be 100, obviously. I cannot set the value null because it is byte type. I tried to leave it unset, but will still set to default.

This is pretty dumb because you can execute a script to not have fillfactor. And once you have fillfactor, you cannot even go into the database to set it to 0 (even if the default db server value is the same as the fillfactor).

Simply, I just want the index to be the same as the source database. If the source database does not specify a fillfactor, I want the target database to also not specify a fillfactor.

Buggish: C# SMO Error The name specified in the TextHeader property of StoredProcedure 'StoredProcedureName' must match Name property.

Error Message

The name specified in the TextHeader property of StoredProcedure 'spName' must match Name property. 

Results

None. I don't why this occurs.

Details

I created code to copy Stored Procedure from one server to another. This has worked have several weeks, and suddenly I have two SP that throws this error. The immediate cause is that the source TextHeader is different from its own name.

StoredProcedure spSource = dbSource.StoredProcedures[spName];

For some reason, spSource.Name is not the same as spSource.TextHeader.

I suspect the developer created the Stored Procedure then renamed it. This is not confirmed.

Edited 8/12/2021

Confirmed that this definitely occurs when a stored procedure is renamed. My fix is to use SSMS to get the alter stored procedure and execute as is. 

Get SQL columns with specified default value

Query to find all columns with specified default value:

SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value" FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id 
LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id  WHERE SO.xtype = 'U' AND SM.TEXT = '[XXXXXXXXXXXXXX]' ORDER BY SO.[name], SC.colid  


Reference:
https://www.mssqltips.com/sqlservertip/1512/finding-and-listing-all-columns-in-a-sql-server-database-with-default-values/
- Option 1

GridView with multiple columns with the same name

Microsoft ASP.NET 4.5
Visual Studio 2013

Scenario: Joining to tables that have the same column names but different uses without using alias (because sometimes you don't have access to the data or query).

Solution: The first column will be the original column name. Each additional column will have an integer counter starting at 1 appended to the end of the column name.

Exampe: If the column name is XXXXXX, then the first column will be XXXXXX, the second column will be XXXXXX1, then XXXXXX2, etc, etc.

Buggish: Visual Studio 2015, MS SQL mass insert limitation

I just attempted to insert 43000+ records through Visual Studio 2015 SQL Server Object Explorer to my database on MS Azure cloud. For some reason, it errors out and states that there is an error on line 41357.

I ran that row independently and executed without error. I ran the query again and received the same error except the following line. Then I just ran all the inserts in batches. The final batch was the largest with 18,000+ records which took 3.5 minutes to execute.

So all my individual INSERT queries appears to work fine. Perhaps there is a limitation the number of records that can be sent in a single query.

Update single column to be case-sensitive

ALTER TABLE [table]
ALTER COLUMN [column] [type]
COLLATE SQL_Latin1_General_CP1_CS_AS [NOT NULL]

[NOT NULL] is optional

Storing Approximate or Partial Dates in Database

One of the common issues that I have in programming is how to store questionable dates. There are all different forms of questionable dates, but I have primarily been focused on partial dates where I may or may not have the date. The most common property is the a person's birthday. After a few different ways of storing the date, I have found that having an additional column to store the certainty of the date works best for me thus far.

In MS SQL, I have an additional binary column. I will have two columns, DOB (date) and DOBAppr (binary(1)). DOBAppr contains the certainty of DOB. If DOBAppr is 0 then the date is known exactly. If 1, the day is uncertain. In the application, I will only display Month and Year if DOBAppr is 1. If 2, the month is uncertain. This is uncommon to have only the year and day but the possibility is there. One example is when someone does not explicitly share their DOB but has mentioned that they shared the same day (and you know how old they are). If 3, the day and month is unknown. So basically, I have set it up like a binary flag. 4 is unknown year, 5 is unknown year and date, 6 is unknown year and month, 7 is all unknown.

In the previous solution, I used to broken the date into multiple fields, day (tinyint), month (tinyint), and year (smallint). If the value was unknown, I would leave the field as null or use 0 if null is not allowed. Although slightly easier to build the tables, there was quite a bit of overhead to programming. There are other insignificant inefficiencies. This solution has always just bugged me because it was just not a perfect fit even though it worked sufficiently.

Recently, I have expanded on my application and found other uses to using a flags. I was trying to store data on music genres and one of the field is origins. Often in sources, I only get date ranges or decades. So by simply expanding my flags to include the 4th-bit for decades, 5th-bit for centuries, and 6th-bit for millenniums.

While on this expansion, there is also another flag that I could use in the future like approximation (ie circa, c, ca, circ, or cca). Quite similar to partial dates, but this would also mean that the value provided may be incorrect. For example for circa 2015, this could include 2014 or 2016 (or a wider range depending on the object). For partial dates, 2015 would have high confidence that it is correct (or at least a very small chance for error). Although I have no implemented this yet, the solution still gives this flexibility. I probably would use the last bit.

Work: SQL Server - Allow Table Recreation

Solution

For SQL Server 2008:
Tools > Options > Designers > Table and Database Designers > Prevent saving changes that require table re-creation



Useful for development work, but worth re-enabling for production database to prevent someone from recreating a table especially for databases that hold data for reporting or contains a lot of data. Although recoverable, the server may be down for a long extended period of time. If recreation fails, the server will also be down until recovery is completed.

Error Message

Saving Changes is not permitted. The changes you have made require the following tables to be dropped and recreated. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be re-created.