Tag Info

Hot answers tagged

5

This should do what you need. DECLARE @Id INT create table #tmpXmlreturn ( Data xml, ) create table #Ids( Id int ) INSERT INTO #Ids select dbo.Workers.Id as Id from dbo.Workers WHILE EXISTS(SELECT * FROM #Ids) BEGIN SELECT TOP 1 @Id = Id FROM #Ids INSERT INTO #tmpXmlreturn Exec dbo.GetBranchByWorkerId @Id DELETE FROM #Ids WHERE Id = ...


5

Another approach, which still uses dynamic SQL but no ugly cursor scaffolding (and allows you to examine the step which failed and the error number generated, without bubbling the error to the caller): DECLARE @step INT = 0, @result INT = 0, @sql NVARCHAR(MAX) = N''; DECLARE @tbl TABLE([step] INT PRIMARY KEY, [pname] NVARCHAR(513)); INSERT ...


4

If you don't need the result values later you can do it shorter this way: -- procedures to test with create proc proc1 as print '1' return 0 GO create proc proc2 as print '2' return 1 GO create proc proc3 as print '3' return 0 GO if object_id('dbo.testproc') is null exec('create procedure dbo.testproc as return(0)') GO alter PROCEDURE dbo.testproc AS ...


3

In Vedran's code which you reference, he also signs the stored procedure in the other database. I do not see in your code that you have taken that final step. Erland Sommarskog has an extensive discussion at http://www.sommarskog.se/grantperm.html of this problem. This includes a discussion of, in his words, "the Problematic EXECUTE AS". In the following ...


2

As per the comment, perhaps something like this: CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST ( PersonID NUMBER, TableName NVARCHAR2, BuyerOrSeller INTEGER, a_CURSOR OUT sys_refcursor ) AS v_stmt_str VARCHAR2(200); BEGIN if BuyerOrSeller = 1 then v_stmt_str := 'SELECT TBLDEALS.*,t.* FROM TBLDEALS inner JOIN ' || tablename || ...


2

It sounds like you're dumping 30 million rows into an existing table, then immediately trying to update those rows. I have a couple of thoughts, which may or may not work for you (since I can't see your code currently): 1) Is there a way to combine the inserts with the updates? It seems suspect to me that you're creating 30 million rows, then immediately ...


1

Two thoughts, both revolving around Impersonation: From the description of the issue as well as the error messages, they all deal with errors related to attempts to Impersonate / EXECUTE AS. This attempt to switch the execution context could be the entire problem. Why are you using EXECUTE AS LOGIN = N'CrossDbCertLogin'? That login (and the associated ...


1

If you have Oracle Database Release 12c Release 1 (12.1.0.2) then you can store JSON in the database in a VARCHAR2, CLOB, or BLOB column, and then query it in SQL using JSON path expressions (analogous to XPath expressions for XML). You can use SQL functions json_value, json_query, and json_table, and SQL conditions json_exists, is json, is not json, and ...


1

Using NOT EXISTS should work, eg SELECT * --!!TODO add column list FROM dbo.CompanyDomains cd WHERE CompanyId = @CompanyId AND NOT EXISTS ( SELECT * FROM @domains t WHERE cd.Domain = t.domain )


1

What I'm totally stopped on is I don't see any equivalent of a List is SQL. Is there a way to build all this up in a list? So a list in SQL Server is basically a table. You can use table-valued parameters to pass in your complete list from C#. You basically just create a table type to define that list you are passing in, see the example in the MSDN ...



Only top voted, non community-wiki answers of a minimum length are eligible