New answers tagged t-sql
3
But it seems like, since the user is creating it, it should be able to set who owns it too.
Absolutely not. After creation, the object is owned by the schema owner, and it would be a security hole to allow users to take ownership of DBO's objects.
How can I get the creating user (SequenceCreator) to own the sequence that it creates without needing a ...
0
As others have said, there is no good way to detect if a parameter was set to its default value by the caller or by its omission.
To add an extra option to the list of workarounds, you could make the default instead something that will never get used and react differently to that. For instance:
CREATE PROCEDURE DefaultJiggeryPokery @InputParam INT = -...
5
Maybe something like this might work:-
CREATE PROC dbo.Test @Param1 INT = NULL
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS
(
SELECT 1
FROM sys.dm_exec_requests AS ER
CROSS APPLY sys.dm_exec_input_buffer(ER.session_id, ER.request_id) AS IB
WHERE ER.session_id = @@SPID
AND IB.event_info LIKE '%@Param1%'
...
-1
I would go for a universal placeholder in this case Exec Test @param1 then let the code snippet that calls this stored procedure to determine whether the @param1 value is set to NULL or left as an empty string given the scenario at hand.
3
As a workaround approach: if you can modify the caller code to supply a document instead, you can both
detect if a key/column exists in the supplied document to omit updates where a key is not present and
allow for set-null commands to valid keys
Consider the following (slightly bulky) POC (full db<>fiddle)
create proc Test
@payload nvarchar(255)...
0
I'm pretty sure there is no way to create or manipulate table-type values in SELECT statements which is what you seem to want.
For the single row example you could create a wrapper function which takes the values you want in the table variable as individual arguments, creates a table variable containing them, then calls the existing function and forwards on ...
2
ANSWER:
There is no way of telling if parameter in called procedure was set to it's default value by user or by system.
The value inside procedure looks exactly same and there is no information stored in the system if the value was passed by calling script or default value was assigned by SQL server.
WORKAROUND:
You can use second optional parameter like @...
6
The issue here is that SQL Server validates the objects when it parses the SQL statement and so if the object doesn't exist it fails, however, there is a trick to getting around this.
SQL Server parses object names in the IF statement, but does not validate objects in the ELSE portion. Instead of checking if the object exists and then taking action if it ...
0
I don't have access right now to a replicated instance to verify but I believe the system tables and DMVs should help.
sys.dm_repl_schemas "Returns information about table columns published by replication." Those will be the ones currently in the published article.
sys.columns will list all the columns in the source table. (You can join to sys....
2
To complement the answer already provided by Biju jose, here is one way to do it with dynamic SQL. If you add new rows to the dbo.eav table in my example, they automatically translate into new columns in the output.
USE tempdb;
GO
DROP TABLE IF EXISTS dbo.eav;
GO
CREATE TABLE dbo.eav
(
[ItemId] int NOT NULL
, [...
3
select itemid,
MAX(case
when itemattributename ='Application' then itemattributevalue
ELSE NULL END),
MAX(case
when itemattributename ='Color' then itemattributevalue
ELSE NULL END)Color,
ISNULL(MAX(case
when itemattributename ='Environmental Conditions' then
...
0
After experimenting with the example given by Hannah Vernon, I believe I've found the source of my issues with snapshot restore.
In my chase to repro this issue on my box, and in our test suite, we set the restore target database to single user prior to snapshot restore with a statement along the lines of alter database x set single_user with rollback ...
3
I'm attempting to repro your scenario, and am seeing the snapshot restore time between 600 to 800 milliseconds.
This is the repro:
USE master;
IF DB_ID('test_snapshot') IS NOT NULL
BEGIN
ALTER DATABASE test_snapshot SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE test_snapshot;
END
GO
CREATE DATABASE test_snapshot
ON (NAME = '...
Top 50 recent answers are included
