March 24, 2020 at 8:24 pm
I am getting this error in SQL Server Express 2017 when trying to script a table out to a new query. I can open it in design mode, run queries and so on but not generate scripts for tables...
Attempt to retrieve data for object failed for Server '(LocalDB)\MSSQLLocaDB'. (Microsoft.SqlServer.Smo)
Additional Information
Invalid Urn filter on server level: filter must be empty, or the server attribute must be equal with the true server name. (Microsoft.SqlServer.Smo)
I saw this fix on https://debabratahowlee.blogspot.com/2016/09/sql-server-invalid-urn-filter-on-server.html
Which basically says......
To check this, execute following:
1. Open cmd, type hostname and execute. OR you can also check the Hostname in SQL Server as well using the command: SELECT HOST_NAME() AS 'HostName
2. Open SSMS i.e. SQL management studio, in the new query window type SELECT @@SERVERNAME AS 'Server Name' and execute.
Result of 1 and 2 will be different.
To correct the server name, from “Server1” to “Server2”, run the query in SQL management studio as follows:
sp_dropserver 'Server1';
GO
sp_addserver 'Server2', local;
GO
So....
SELECT @@SERVERNAME AS 'Server Name'
returns
HP\LOCALDB#2517E378
Running "localhost" from CMD shows > HP
When I go to connect to the server in MS Management Console, it shows this as the server name: (LocalDB)\MSSQLLocalDB and I am using Windows Authentication. The system I am developing a Windows Service, uses SQL Logins, but I am just playing about in MS Management Console at the moment.
If I do the fix above (on that site) should I be changing the server name to HP\LOCALDB#2517E378 or (LocalDB)\MSSQLLocalDB or HP?
What should the sp_dropserver statement say, and what should the sp_addserver statement say?
I have not changed anything to do with the server name/instance since installing SQL Server Express on my local machine so I am confused to why this situation has happened. I installed SQL Server Express 2017, did a backup and restore of a DB from another server and changed the database collation.
Any help on this would be great as I have never seen this error and the article is not very clear on what the server name should actually be. A drop/add server statement with the right values would be wonderful!
Thanks in advance
March 25, 2020 at 7:41 pm
My advice - First script will be dropping the server name from the SQL Instance. So this will NEED to be the one that is stored in your databases. Since HP\LOCALDB#2517E378 is what SQL is giving you, that one would be Server1. Server2 will be (LocalDB)\MSSQLLocalDB OR .\MSSQLLocalDB OR HP\MSSQLLocalDB. I would go with the HP one as that is likely to be the most accurate one.
Worst case, since you said all you did was install SQL and restore a database to it, you could always detach the database (don't delete, just detach), then uninstall SQL Express and install SQL Express again then attach the database.
Did you get any errors or warnings while doing the restore?
Did you have this problem prior to restoring the database or did it only happen after attaching the database?
The database you restored, was it a "system" database or a user database?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 25, 2020 at 9:28 pm
Hi thanks for your response.
I did not get any problems with the backup / restore, I simply SFTP'd the backup from a dedicated server to my local PC and then restored in from Management Console.
This database is a custom DB I created and was running fine controlling an automated betting BOT on Betfair but then my old company decided to outsource their servers to OVH, a French hosting company, which doesn't allow HTTP access to any online betting site, especially Betfair. So the system has been on halt since that move and with the COVID19 shut down, I thought it would be a good time to get it up and running using just my local PC.
I am not sure "Server2 will be (LocalDB)\MSSQLLocalDB OR .\MSSQLLocalDB OR HP\MSSQLLocalDB" gives me much confidence in the name to use 🙂 - also I don't get why I can access table designers to edit table structures but not the generate scripts as surely the designer behind the scenes generates the DDL for the changes and runs that anyway, so I would have thought the design view as well as the scripts would show the same error.
I can get away with not needing to generate scripts, it was just that I wanted to post a problem on here, and I wanted to create the DDL for anyone to replicate the table structure etc.
I just don't understand how it has suddenly broken OR why I seem to have so many different names for my LocalDB Server - surely it should be the name that pops up in the "Connect To Server" dialog window which says "(LocalDB)\MSSQLLocalDB" ?
I have not edited any system databases apart from enabling CLR and adding two assemblys to handle regular expression Match and Replace into MSDB so that I can access that functionality from any other DB I may need it in.
Would that have caused the issue? It didn't when I was doing the same actions on SQL 2005+ .
March 25, 2020 at 9:55 pm
My best guess as to why it suddenly broke is that something changed on your system. Could have been a windows update, a sql update (although those are both unlikley), or some 3rd party tool like a registry cleanup tool or an antivirus or antimalware tool trying to be helpful. Or the instance might have been shutdown inproperly causing recoverable corruption in the databases that was recovered when the instance started up again but giving you weird results such as what you are seeing. Could be a bad sector on your disk. Could be gremlins. Could be a tool connected to the database ran some scripts that you were unaware of. Could be a large number of things. Having someone narrow down "what changed" without having any access to your system is going to be impossible. Depending on how far back your SQL logs go, you could use those to determine when the problem first occurred, but that is going to be a slow and painful process reading through the logs to find the first entry where that additional hex value is appended to the system name.
My advice is to run the command. It won't make things worse and you can always set it back to how it is now by running the scripts with the current name for server 1 and the original name for server 2. Server 1 is "old name", Server 2 is "new name" in the scripts you have.
The reason why I am not 100% sure which one you should use is that it has been quite a few years since I installed a default instance of SQL Server. I ALWAYS install it as a named instance when I install SQL Server as I find that easier to work with. Plus then I can run multiple SQL instances on the same box without having to worry about name issues.
Also a word of warning about RegEx in SQL Server - it is not a quick operation. You will get much better performance using RegEx at the application side.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 27, 2020 at 4:51 pm
Yes, I am always willing to blame a Windows Update for anything. Too many times I have had issues with my laptop and a restore to a point before the update has helped.
Not prepared to scan SQL or Windows event logs for the issue as I cannot even narrow down when it could have changed to a week, let alone a day. I just would have thought the design view when editing tables would have created similar DDL statements that clicking "script object as > create/alter" did and would have thrown the same error.
So you are recommending...
sp_dropserver 'HP\LOCALDB#2517E378';
GO
sp_addserver '(LocalDB)\MSSQLLocalDB', local
GO
As for the regular expressions. I know its always best to use them at the front end if possible but these are very simple CLR regular expressions just to do an IsMatch or RegReplace, not return groups of matches which need parsing as recordsets etc. They are in procs that were called by MS Agent on the dedicated server. Also the cases they are used are always in UDF's where a single string needs passing or testing where a single LIKE or PATINDEX statement cannot do the job and never in SELECT/WHERE/JOINS where a big dataset needs to be passed into them.
Also the usage is very minimal and I have used them in places where I did have multiple PATINDEX/LIKE statements which surely must have quite a big hit under the scenes anyway? Surely SQL itself uses some string parsing mechanism when needing to do PATINDEX/LIKE statements anyway?
As there is no MS Agent on Express I am still deciding how to replicate MS Agent usage on SQL Express which doesn't have it. Either my windows service will have a number of timers to run the timed jobs that I need or maybe Windows Task Scheduler. The only reason I am leaning towards timers in the service is it then keeps all the code used by the system in one app so if it was moved again the app / database is all that needs to be moved.
March 31, 2020 at 7:27 pm
Not looking through the logs sounds like you don't really want to find out what caused the problem. That is what the logs are there for. Without finding the cause, how do you know it won't happen again?
Fixing the symptoms is always a good step, but finding the root cause is critical in preventing it from happening again.
I do agree that SQL is using string parsing for things like PATINDEX and LIKE, but it is not full on RegEx. If it was me, I would still be doing the RegEx at the application side and not the database side. I like to use the right tool for the job where appropriate.
As for that drop and add server syntax, that is what I'd run. That or do a fresh install of SQL. I'd probably switch things over to SQL Developer edition so you get things like the MS Agent, but that one is only licensed for testing and development. Any production level use you'd need a Standard license.
I do like your service idea over the windows task scheduler mostly because it is easier to control and depending on the setup, you can have better logging and failed schedule notifications. That being said, I'd recommend building a configuration utility for the service if you were going that route so you can adjust the schedule without having to monkey around with the code too much. One thing to watch for in your service though is unhandled exceptions. End up with a NULL pointer reference (for example) and your service will crash with no end user notification. You need to be EXTRA careful with services to make sure you catch any and all exceptions and handle them appropriately. In some cases, having the service crash and restart MAY be the desired outcome, in others it may be better to log the error and stop the service until the error is resolved. Other times, you may be able to ignore the error and continue. In all 3 cases though, I recommend creating a log entry (event log or to a file on disk), and depending on the severity, maybe even sending out an email alert.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 31, 2020 at 8:16 pm
It looks to me like you have multiple instances installed on that workstation, and the one you are trying to use is actually installed and utilized as LocalDB. This is not the Express instance of SQL Server - it is a specialized version of SQL Server.
https://expressdb.io/sql-server-express-vs-localdb.html
https://blogs.msdn.microsoft.com/jerrynixon/2012/02/26/sql-express-v-localdb-v-sql-compact-edition/
Please review these articles - it may help.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
April 2, 2020 at 3:19 am
Well I don't want to look through months of sql or event logs because I have no idea when the problem started and have no way of narrowing it down, something I am sure you said would be a slow and laborious task unless I knew what specific terms to search for in these logs?
Do you know what terms I should be looking for to find when the "generate scripts as" feature stopped working?
If there is a way to scan the logs for certain events that relate to this feaure stopping then thats different but I'm not scanning months of SQL and Event logs not knowing what to look for when I can easily live without this feature.
I did not set up an instance specifically, I just installed SQL server express on my laptop and then restored the DB. It was working with the same Server name up until a point in time I am not aware of. It was only when I posted my problem about a Dutching algorithm and wanted to provide the DDL so people could replicate the problem that I noticed the error. When it stopped working I have no idea.
If you know what server name I should be dropping and the name of the server to add please let me know.
I am not buying SQL developer editon or a production version to run on my laptop. The whole point of me using Express is our production DB cannot work on a French hosting server so I am migrating it to my local laptop to use a Windows Service and SQL Express locally instead and the service won'tbe running 24 hours a day as it used to as it needed to run multiple websites as well as my Betting BOT, I am just trying to get my Betting BOT working again locally.
I will read those links when I can and if they solve the issue it meams I don't have to scan the logs which is good.
The Windows Service does already write to a custom event log so I can see any errors related to my service and if an error occurs that stops the service from running it is logged as an error so I can fix it whilst informational messages log when the service starts or stops or is paused or when timer initiated jobs fail.
As for RegEx I said they are all cases that were ran from Ms Agent jobs previously so no front end code is involved and they are all replacing code where multiple LIKE/PATINDEX function calls were being used.
If I could change the code to be front end code I would but its not that easy without a big rewrite and I just want to get the existing system up and running first before changing anything as I know it works as long as I can replace the use of MS AGENT with tiner initiated jobs.
For example I have a backend process to flag important races that day to be imported into a temp table for certain bets. It used to pass a racename into a function that flagged it as important by doing @racename LIKE '% grade[1-3]%' OR LIKE '% group[1-3]%' OR LIKE '% listed %' etc etc.
Replaced by SqlRegExMatch(@racename, '.+?(grade|group|listed|classic|prix du).*$')=1
Because the data for races I have scraped has changed formats over the years many times and sources have changed as well as well as the format of racenames. I just save the racename from the data source and a batch job flags any events that are "quality" races e.g group/grade/listed/classics (French, German or English) so that certain systems can use them.
In theory I could pass another parameter into the system when a racename is a classic but that doesn't help with analysing previous classic races where the data is already in the database but I still need to know if a race 10years old in a different format was a classic for KPIs and reporting as well as building up ROI on all previous classic races I have and so on, all backend jobs.
New races could have flags passed in but the historical data has been scrapped from multiple sources and getting the racename was key, finding out the type of race depending on a computer generated system comes once the races are in the system and for historical stats it needs to flag old races. The same flag from the front end could be used and stored so this process isn't repeated daily I know but this is just an example off the top off my head and I am sure without knowing the system you could think of imaginary solutions such as running a one off cursor to set a flag to update an important type of race in the DB for historicals and then use front end code to handle new races but that is leaving out a lot of logic I can't explain without scanning the code which I am not near at the moment.
Races are not just classics due to grade/group/listed but also racenames with certain words in e.g .*\b(King|Prince|Queen)\b(James|of Wales's|Anne|Mother)\b(stakes|chase).*$
You can inagine the LIKEs I had in the where clause and this is just a basic example for demonstration purposes. I have the "class" stored and that is group1 to 3 grade1 to 3, listed, classic etc. This is saved on input but the "important Races" backend job that fills the table up that is then looped through each day to set the flag is a backend job. It is a lot easier to change the regular expression in the backend UDF than the code as it handles historical and new events, which also often change formats as well as well as foreign classics where the racename is saved.
It is a bad example but the only one I can think of without searching the code for uses of SqlRegEx which I have said were minimal. So are you saying behind the scenes multiple LIKE/PATINDEX functions do not use any C++ regular expressions hidden by TSQL functions or do they use procedural loop string parsing instead? Which surely must be slowler the more LIKE/PATINDEX functions you use?
Anyway thanks for your replies I will check the links out tomorrow if I am feeling better. Think I am coming down with the Coronavirus so I haven't been well enough to code latley. It's hard enough just to breathe.
However I appreciate your comments.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply