sql server - Make ADO generate parameters for a temporary stored procedure? -

-

i'm trying ado recognize parameters stored procedure on sql sever. if normal stored procedure, works fine:

conn.execute "create proc normalsp (@i int output) set @i = 3" cmd.commandtype = adcmdstoredproc cmd.activeconnection = conn cmd.commandtext = "normalsp" cmd.parameters.refresh 'parameters list has @return_value , @i cmd.execute debug.print cmd("@i")

but if try same thing temporary stored procedure, can't parameters:

conn.execute "create proc #tempsp (@i int output) set @i = 3" cmd.commandtype = adcmdstoredproc cmd.activeconnection = conn cmd.commandtext = "#tempsp" cmd.parameters.refresh 'parameters list remains empty cmd.execute 'error: command expects parameter '@i', not supplied debug.print cmd("@i") 'error: item cannot found in collection

what more need parameters.refresh work temp sp normal sp?

asking 2 black boxes (legacy ado, oledb/odbc provider) why don't interpretation of context should expose not provide satisfactory answer, since original intent unclear...

assuming can accept short-lived, global temporary stored procedure ask temporary parameters (and in contradiction presumably downvote above), connect right db in first place:

complete, repeat-safe sample (using sql 2012 native client):

public sub doitemporarilyhaveparametersforado()      dim conn    adodb.connection     dim cmd     adodb.command     dim prm     adodb.parameter      set conn = new adodb.connection     set cmd = new adodb.command      conn.connectionstring = "driver={sql server native client 11.0};server=(local);database=tempdb;trusted_connection=yes;"     conn.cursorlocation = aduseserver     conn.open      conn.execute "if object_id('tempsp') not null drop proc tempsp"     conn.execute "create proc tempsp (@i int output) set @i = 3;"     cmd.activeconnection = conn     cmd.commandtype = adcmdstoredproc     cmd.commandtext = "tempsp"     cmd.parameters.refresh     each prm in cmd.parameters         debug.print prm.name & ": " & prm.value     next prm     debug.print "cmd.parameters.count: " & cmd.parameters.count     cmd.execute     debug.print cmd("@i")      conn.execute "if object_id('tempsp') not null drop proc tempsp"     conn.close      set prm = nothing     set cmd = nothing     set conn = nothing  end sub

immediate window output:

@return_value:  @i:  cmd.parameters.count: 2  3

some useful clarification msft:

temporary stored procedures useful when connecting earlier versions of sql server not support reuse of execution plans transact-sql statements or batches. applications connecting sql server 2000 , higher should use sp_executesql system stored procedure instead of temporary stored procedures.

....

if stored procedure not prefixed # or ## created directly in tempdb database, stored procedure automatically deleted when sql server shut down because tempdb re-created every time sql server started. procedures created directly in tempdb exist after creating connection terminated.

meaning of course if throw breakpoint vba sample round line 27, flip on ssms, connect (local), switch tempdb, , exec tempsp, told

procedure or function 'tempsp' expects parameter '@i', not supplied.

instead of

could not find stored procedure 'tempsp'.

before , after sample execution.


Comments

Post a Comment

Popular posts from this blog

php - render data via PDO::FETCH_FUNC vs loop -

-
i've been using 2 methods render data. the first one: function name($id,$name){ return '<div id="'.$id.'">'.$name.'</div>'; } echo implode($pdo->query("select id,name user")->fetchall(pdo::fetch_func,'name')); the second one: $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); foreach($users $user){ echo name($user->id,$user->name); } i don't understand how pdo::fetch_func works. tried figure out. however, not well-documented. could please explain fetch mode? , also, 1 performs better? thank you. both methods wrong , have learn how use templates , how separate business logic presentation logic. $users = $pdo->query("select id,name user")->fetchall(pdo::fetch_obj); tpl::assign('users', $users); is code business logic part. then in template <?php foreach $users $row): ?> <div id="<?=$...
Read more

c++ - OpenCV Error: Assertion failed <scn == 3 ::scn == 4> in unknown function, -

-
i read alot other solution still confused should mine... #include <opencv2/objdetect/objdetect.hpp> #include <opencv2/highgui/highgui.hpp> #include <opencv2/imgproc/imgproc.hpp> #include <iostream> #include <stdio.h> using namespace std; using namespace cv; int main(int argc, const char** argv) { //create cascade classifier object used face detection cascadeclassifier face_cascade; //use haarcascade_frontalface_alt.xml library face_cascade.load("haarcascade_frontalface_alt.xml"); //setup video capture device , link first capture device videocapture capturedevice; capturedevice.open(0); //setup image files used in capture process mat captureframe; mat grayscaleframe; //create window present results namedwindow("outputcapture", 1); //create loop capture , find faces while (true) { //capture new image frame capturedevice >> captureframe; ...
Read more

The canvas has been tainted by cross-origin data in chrome only -

-
uncaught securityerror: failed execute 'getimagedata' on 'canvasrenderingcontext2d': canvas has been tainted cross-origin data i getting above error in chrome. code(following) works fine in mozilla. have not corss domain issue. why above error in chrome? var wheel_canvas, wheel_context, can_w, can_h, wheel_grd; var large_radius = 160; var small_radius = 120; var wheel_canvas = document.getelementbyid('wheel_canvas'); var wheel_context = wheel_canvas.getcontext('2d'); var can_w = $(wheel_canvas).width(); var can_h = $(wheel_canvas).height(); var centerx = can_w / 2, centery = can_h / 2; var wheel_grd = null; test('#ff0000'); function test(hex) { $('#clrpicker').css({'left': 210, 'top': 210 }); inverted = hexbw(hex); $('#color_val_show').val(hex); current_color_hex_val_selected = hex; $('#color_val_show').css({'color':inverted,'background':hex}); fillgradient...
Read more