Menu
I'm using SQL Server 2012 AlwaysOn listener and I want to use a connection with ApplicationIntent=ReadOnly parameter for calling some stored procedures, and this is my question: stored procedures which insert data into temp table can use connection with ApplicationIntent=ReadOnly ?
280
Stored procedures can reference temporary tables that are created during the current session. Within a stored procedure, you cannot create a temporary table, drop it, and then create a new temporary table with the same name.
When ApplicationIntent=ReadOnly, the client requests a read workload when connecting. The server enforces the intent at connection time, and during a USE database statement.
It's ok to explicitly include DROP TABLE or let SQL Server handle it for you each time the connection drops. If your stored procedures are already dropping temp tables, that's great. If they're not being dropped, I wouldn't bother spending the time and effort just to drop them.
Yes, the temp table is in the scope of the connection, so the nested stored procedure (sp2) will have access to #temp table create in sp1. Yes, in SQL 2008 we have ability to pass a table valued parameter (TVP) as input to a function or stored procedure.
According to Readable Secondary Replicas
Note
Though you cannot write data to secondary databases, you can write to read-write databases on the server instance that hosts the secondary replica, including user databases and system databases such as tempdb.
So the answer is your stored procedures will work.
99
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
