Tag Info

New answers tagged

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


0

The correct solution is not to try concatenating quotes around the argument. That is a very naive approach, and leads to SQL injection vulnerabilities. The QUOTE() function solves this, when it's needed. However, you do not need these things to be quoted at all in the queries you're running. Worse, adding literal quotes to these values is likely to get ...


0

This finally worked DELIMITER // CREATE PROCEDURE ja_logan_proc() BEGIN SELECT id, dest_msisdn, text_message INTO @the_id, @the_number, @the_message FROM incoming_sms where job_status = 0 AND text_message LIKE '%logan%' order by rand() limit 1; if((select search_number_in_r4(CONCAT('`',@the_number,'`'))) = 'true')then call ...


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


0

It's almost certainly a matter of the data for the first run are not in the cache and have to be read from disk. After that first run then the data is in cache and the query can run much more quickly. After several minutes other data needs to be in the cache and so the data for this query get's pushed out. There is a simple way to test this. Run the ...


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


0

If you want to remove them indexes run this script on each database you restore DECLARE @sql NVARCHAR(MAX); SET @sql = N''; SELECT @sql = @sql + N'DROP INDEX ' + QUOTENAME(name) + ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) + '.' + QUOTENAME(OBJECT_NAME([object_id])) + '; ' FROM sys.indexes WHERE index_id > 0 AND ...


0

A function has to return something (in your case an INTEGER) somewhere. Try SELECT SOLICITAR_DISPONIBILIDAD(1,0,2014,0) FROM DUAL


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


-1

Try using exec sp_executesql @sql,'@result int output', @result=@result output. This way you can pass variables in and out to your dynamic query.


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


0

It's interesting that SQL Express would run the sproc faster than standard edition, since Express has a 1GB limitation of memory. Please check to see what the max memory settings are set to for Setup 2 (right-click the SQL server > Properties > Memory). I doubt it's set to anything lower than your Express setup, but it's still worth checking. Is the sproc ...


0

I think you're mixing up Java (other language?) with MySQL's stored procedure language. Look here and here. Check out this by Guy Harrison, the guy who wrote this book.


0

Using the WHILE approach for a task like this works fine for a very short list of Ids, but it performs very poorly as the list increases. This is due to the cost of creating the temporary memory table holding the Ids, and its heavy querying and manipulation. You're having to go through the source keys multiple times and it grows fast with the list size. The ...


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


0

You probably want to use this syntax: CREATE TABLE IF NOT EXISTS `table1` ( -- create table ); -- returns only a warning if the table already exists Unless you want to check that the table name does not exist instance-wide, in which the way you "program with SQL" should be a bit different, for several reasons: SET @check_table_name := false; SELECT true ...


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


0

The problem with your case statement is the syntax is wrong. CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST ( PersonID NUMBER, TableName NVARCHAR2, BuyerOrSeller INTEGER, a_CURSOR OUT sys_refcursor ) AS BEGIN OPEN a_CURSOR FOR SELECT TBLDEALS.*,TableName.* FROM TBLDEALS inner JOIN Tablename on Tablename.ID = ...


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



Top 50 recent answers are included