August 25, 2017 at 12:30 pm
Hi All,
I have a questions regarding Azure. On my regular SQL Server user DB, we have a function which is executing the SP -- "master..xp_regread ". I know, in Azure you cannot use "database.scheme.object" nor we can do "use database" to switch between master and user database.
Is there any way I can run the same SP in the function in Azure SQL database?
Any help is appreciated.
With regards,
D
August 25, 2017 at 12:40 pm
I'm pretty sure there is no actual registry to read in Azure SQLDB...VM yes, but SQL DB no...its a DB as a service, inside a virtual namespace called a SQL Server...
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
August 25, 2017 at 1:03 pm
I don't think there is a direct way of using 3 part naming convention to execute cross database objects. but you can look into elastic query if that is an option, refer below link
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-query-overview
August 25, 2017 at 1:16 pm
Hi,
Thanks for the feedback. I was thinking about the same, there is no registry since Azure SQL database in placed on a logical server so there wont be any registry key.
About elastic database query. It will work for between two user database. As per my understanding , the master database is already available on each logical instance of Azure SQL database.
Is it save to assume, in Azure SQL database we can not use " xp_regread"?
Regards,
D
August 25, 2017 at 1:25 pm
One more thing,
when I run this SP
declare @dir nvarchar(4000)
exec master.dbo.xp_regread
N'HKEY_LOCAL_MACHINE',
N'Software\Microsoft\MSSQLServer\Setup',
N'SQLPath',
@dir output
select @dir
I do see the results, but I cant seems to find it in master database in my on-premises SQL Server. Am I missing something?
August 25, 2017 at 1:28 pm
ok, the SP is under extended SP.
Thanks,
B
August 30, 2017 at 6:55 am
You absolutely cannot reference master from another database, so that won't work at all (currently, wait until the Instance as a Service is released, that could change). My immediate though on xp_regread is that you can't run an extended stored procedure either. However, rather than go off my memory (increasingly a silly thing to rely on), I went and tested it. xp_regread doesn't exist in the master database of an Azure SQL Database server.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 30, 2017 at 7:11 am
Ummm....first I've heard of this! Now I'm very excited! 😀
------------------------------------------------------------------------------------------------Standing in the gap between Consultant and ContractorKevin3NFDallasDBAs.com/BlogWhy is my SQL Log File HUGE?!?![/url]The future of the DBA role...[/url]SQL Security Model in Plain English[/url]
August 30, 2017 at 7:20 am
Grant Fritchey - Wednesday, August 30, 2017 6:55 AM...Instance as a Service...
Ummm....first I've heard of this! Now I'm very excited! 😀
Cross database queries, Agent, all sorts of stuff that people felt was problematic about the database as a platform.
It's in preview, but it's still somewhat private. I haven't gotten my hooks into it yet. I just know what I've read.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 30, 2017 at 12:42 pm
I Will be so happy when they will have "instance as a service", I like the term by the way. Not only that , while I was researching, as far as i understood, AWS on their DB as a service are providing agent, cross database quires and all. So I guess it's already there but just not in Azure SQL database.
Thanks
B
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply