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

I think this is because you only ever return the first row from the query's result. The select ... into ... will only retrieve one row and the query select * from result returns only that single record: You also don't need a PL/pgSQL function, a plain SQL function will work just fine: CREATE OR REPLACE FUNCTION getMessageFromSites(ids TEXT) RETURNS ...


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

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 ...


2

This ought not happen, but there have been several bugs that mean it might, depending on your version of SQL Server, and which patches you have applied. The relevant Microsoft KB articles are listed below: FIX: Incorrect result when you execute a query that uses WITH RECOMPILE option in SQL Server 2012 or SQL Server 2014 FIX: A query that uses parameters ...


2

You wrote "case closed", but I'll reopen. There is just too much gone wrong ... Database design and test setting CREATE TABLE patient ( patient_id int PRIMARY KEY , site_held_at int NOT NULL ); CREATE TABLE messageq ( messageq_id varchar PRIMARY KEY -- varchar ?! , patient_id int NOT NULL REFERENCES patient , message_body varchar NOT NULL ...


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 ...


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 ...



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