SQL 7.0 running in 6.5 mode
I have a stored proc that is pulling varchar data from a column and trying to use it in the rest of the proc. The problem is that in some of the data there is a single quote (ie Dave's). How can I pass this data in a useable form.
Thanks in advance,
Will Anderson
Using the following:
SQL Server: SQL Server 2012
Visual Studio 2012
I have created an SSIS package where I have added an Execute SQL Task to run an existing stored procedure in my SQL database.
General Tab:
Result Set: None
Connection Type: OLE DB
SourceType: Direct Input
IsQueryStoredProcedure: False (this is greyed out and cannot be changed)
Bypass Prepare: True
SQL Statement: EXEC FL_CUSTOM_sp_ml_location_load ?, ?;
Parameter Mapping:
Variable Name Direction Data Type Prmtr Name Prmtr Size
User: system_cd Input NVARCHAR 0 10
User: location_type_cd Input NVARCHAR 1 10
Variables:
location_type_cd - Data type - string; Value - Store (this is static)
system_cd - Data type - string - ??????
The system code changes based on the system field for each record in the load table
Sample Data:
SysStr # Str_Nm
3 7421Store1
3 7454Store2
1815061Store3
1815063Store4
1615064Store5
1615065Store6
1615066Store7
7725155Store8
STORED PROCEDURE: The stored procedure takes data from a load table and inserts it into another table:
Stored procedure variables:
ALTER PROCEDURE [dbo].[sp_ml_location_load]
(@system_cd nvarchar(10), @location_type_cd nvarchar(10))
AS
BEGIN .....................
This is an example of what I want to accomplish: I need to be able to group all system 3 records, then pass 3 as the parameter for system_cd, run the stored procedure for those records, then group all system 18 records, then pass 18 as the parameter for system_cd, run the stored procedure for those records and keep doing this for each different system in the table until all records are processed.
I am not sure how or if it can be done to pass the system parameter to the stored procedure based on the system # in the sys field of the data.
I have an issue with using multiple parameters in SQL Reporting services where data is passed in from a stored procedure
When running the report in design mode - I can type in a parameter sting and it runs fine
In the report preview screen I can select single parameters by ticking the drop down list and again it runs fine
as soon as I tick more than one I get an error
An error occurred during local report processing
Query execution failed for data set €˜data'
Must declare the scalar variable '@parameter'
Some info...
The dataset 'workshop' is using a sproc to return the data string?
I get multiple values back fine in the sproc using this piece of code
(select [str] from iter_charlist_to_table( @Parameter, DEFAULT) ))
I have report parameters set to Multi-Value
Looking through the online books it says...
You can define a multivalued parameter for any report parameter that you create.
However, if you want to pass multiple parameter values back to a query, the following requirements must be satisfied:
The data source must be SQL Server, Oracle, or Analysis Services.
The data source cannot be a stored procedure. Reporting Services does not support passing a multivalued parameter array to a stored procedure.
The query must use an IN clause to specify the parameter.
Am I trying to do the impossible ?
Hello,
since a couple of days I'm fighting with RS 2005 and the Stored Procedure.
I have to display the result of a parameterized query and I created a SP that based in the parameter does something:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE PROCEDURE [schema].[spCreateReportTest]
@Name nvarchar(20)= ''
AS
BEGIN
declare @slqSelectQuery nvarchar(MAX);
SET NOCOUNT ON
set @slqSelectQuery = N'SELECT field1,field2,field3 from table'
if (@Name <> '')
begin
set @slqSelectQuery = @slqSelectQuery + ' where field2=''' + @Name + ''''
end
EXEC sp_executesql @slqSelectQuery
end
Inside my business Intelligence Project I created:
-the shared data source with the connection String
- a data set :
CommandType = Stored Procedure
Query String = schema.spCreateReportTest
When I run the Query by mean of the "!" icon, the parameter is Prompted and based on the value I provide the proper result set is displayed.
Now I move to "Layout" and my undertanding is that I have to create a report Paramater which values is passed to the SP's parameter...
So inside"Layout" tab, I added the parameter: Name
allow blank value is checked and is non-queried
the problem is that when I move to Preview -> I set the value into the parameter field automatically created but when I click on "View Report" nothing has been generated!!
What is wrong? What I forgot??
Thankx for any help!
Marina B.
I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
Hi all, I have a stored proc which returns twice the result and I dontknow why. Can someone have a look at the following code?BTW, I commented the last SELECT/JOIN, cause that one doubled theresult too.CREATE procedure ent_tasks_per_user_company (@companyName as varchar(50),@resourceName as varchar(50))ASSELECTtasks.WPROJ_ID as WPROJ_ID, tasks.ENT_ProjectUniqueID asProjectUniqueID, tasks.ENT_TaskUniqueID as TaskUniqueID,tasks.TaskEnterpriseOutlineCode1ID as TaskEnterpriseOutlineCode1ID,codes.OC_NAME as OC_NAME, codes.OC_DESCRIPTION as OC_DESCRIPTION,codes.OC_CACHED_FULL_NAME as OC_CACHED_FULL_NAME,taskStd.TaskName as TaskName, taskStd.TaskResourceNames asTaskResourceNames, taskStd.TaskPercentComplete as TaskPercentCompleteINTO #myTempFROM MSP_VIEW_PROJ_TASKS_ENT as tasksINNER JOIN MSP_OUTLINE_CODES as codesON(codes.CODE_UID = tasks.TaskEnterpriseOutlineCode1IDANDcodes.OC_CACHED_FULL_NAME LIKE @companyName + '.%')INNER JOIN MSP_VIEW_PROJ_TASKS_STD as taskStdON(taskStd.WPROJ_ID = tasks.WPROJ_IDANDtaskStd.TaskUniqueID = tasks.ENT_TaskUniqueID--AND--taskStd.TaskResourceNames LIKE '%' + @resourceName + '%')WHERE (tasks.TaskEnterpriseOutlineCode1ID <-1)/*SELECT #myTemp.*, taskCode.OC_NAME as Department FROM #myTempINNER JOIN MSP_OUTLINE_CODES taskCodeON(taskCode.CODE_UID = #myTemp.TaskEnterpriseOutlineCode1ID)*/SELECT * FROM #myTemp WHERE #myTemp.TaskResourceNames LIKE '%' +@resourceName + '%'Thank you!Chris
View 3 Replies View Relatedgot a blank mssql 2005 express database. my table name is aspnet_IPNUmber. got two (2) columns IPNumberStart and IPNumberEnd both varchar(Max).
could somebody make a sample stored procedure for me that will insert the following records? im still learning how to make stored procs and my webhost only allow creating database in my domain but not uploading my database.
977600512
977666047
977731584
977764351
1024000000
1024032767
1024334848
1024334911
1024361504
1024361727
1024361760
1024361775
1024361792
1024361799
1024361824
1024361839
1024361984
1024362495
1024365824
1024366335
1024368128
1024368383
1024369408
1024369919
1024370688
1024371455
1024372224
1024372479
1024373248
1024373503
1024373888
1024374015
1024376192
1024376319
1024376480
1024376511
1024376832
1024393215
1025277952
1025294335
1062222976
1062223039
1062244312
1062244319
1062262784
1062263039
1064211840
1064211967
1072922624
1072922879
1072926720
1072926975
1072934400
1072934655
1072934944
1072934975
1072935680
1072935807
1072936448
1072936959
1074757800
1074757807
1077003688
1077003695
1078428256
1078428263
1079387904
1079388159
1079406080
1079406591
1081582080
1081582087
1081583216
1081583231
1081584168
1081584191
1081589104
1081589111
1091692644
1091692653
1093057408
1093057423
1103678544
1103678551
1103678656
1103678719
1104003456
1104003583
1104265216
1104265727
1104492288
1104492543
1104881088
1104881151
1105153216
1105153279
1106484352
1106484415
1106564608
1106564863
1113643148
1113643157
1113644092
1113644121
1114520064
1114520319
1114520576
1114520831
1120306176
1120306943
1120307968
1120308223
1120310016
1120310783
1120311808
1120312447
1120312576
1120312831
1121469912
1121469919
1122125979
1122125988
1139015776
1139015783
1139016000
1139016063
1211605088
1211605103
1211608032
1211608047
1247174064
1247174071
1247174368
1247174383
1254967080
1254967087
1254973664
1254973671
1266551520
1266551527
1266570304
1266570319
1432131584
1432133631
1946173664
1946173679
1946173952
1946174015
1946176512
1946176767
1949466624
1949499391
1950545920
1950547967
1950648320
1950650367
1952251904
1952284671
1960207360
1960207615
1966784512
1966792703
1969694720
1969696767
1969811456
1969815551
1984151552
1984153599
1985480704
1985482751
1986404352
1986406399
1996627968
1996630015
1998290944
1998299135
2030108672
2030125055
2033377280
2033385471
2033582080
2033614847
2033623040
2033625087
2033893376
2033909759
2036334592
2036465663
2038366208
2038374399
2046951424
2047082495
2050084864
2050088959
2050228224
2050490367
2056273920
2056290303
2072528896
2072530943
2075148288
2075150335
2079508480
2079510527
2080800768
2080817151
2081652736
2081685503
2085814272
2085847039
2087190528
2087452671
2090737664
2090745855
2094596096
2094628863
2097479680
2097545215
2101116928
2101149695
2111045632
2111078399
2113683520
2113683679
2113683744
2113684095
2113684176
2113684255
2113684272
2113684431
2113684440
2113684479
2113684544
2113684735
2113684992
2113685007
2113685024
2113685047
2113685120
2113685231
2113685248
2113686079
2113688320
2113689087
2113690112
2113690367
2113691904
2113692031
2113692160
2113692415
2113694720
2113695231
2113695488
2113695743
2704978756
2704978759
2782658560
2782724095
3231309056
3231311103
3233590784
3233591039
3233668864
3233669119
3236102144
3236106239
3262474113
3262474113
3262474143
3262474143
3262474193
3262474193
3278940156
3278940159
3278942516
3278942519
3278942612
3278942615
3325562880
3325566975
3326118524
3326118527
3326119248
3326119251
3326122972
3326122973
3334995968
3335000063
3389001728
3389005823
3389020928
3389021183
3389092352
3389092863
3389259776
3389263871
3389579264
3389587455
3389788416
3389788927
3389936896
3389937663
3391663104
3391664127
3391722240
3391722495
3391906816
3391907839
3392109824
3392110335
3392110592
3392111103
3392111360
3392112127
3392112640
3392114175
3392446464
3392450559
3392741376
3392765951
3392799232
3392799487
3392856064
3392864255
3392931840
3392933887
3393011712
3393019903
3393302528
3393306623
3393560576
3393568767
3393609728
3393613823
3393695744
3393699839
3393744896
3393748991
3393822720
3393830911
3393910784
3393911807
3394079232
3394079743
3394125824
3394142207
3394279424
3394281471
3394347008
3394355199
3394507776
3394508799
3394527232
3394535423
3394682880
3394686975
3394832384
3394834431
3394879488
3394883583
3394910208
3394912255
3394928640
3394936831
3395002368
3395006463
3395059712
3395067903
3395280896
3395284991
3397027072
3397027327
3397070848
3397074943
3397156864
3397165055
3397263360
3397267455
3397394432
3397402623
3397763072
3397771263
3397793792
3397794303
3398004736
3398008831
3398074368
3398090751
3398612992
3398613503
3398638096
3398638111
3398638120
3398638135
3398638160
3398638167
3398638192
3398638207
3398638432
3398638447
3398638528
3398638575
3398638592
3398638655
3398638720
3398638847
3398638880
3398638911
3398639008
3398639231
3398639248
3398639263
3398639424
3398639455
3398639488
3398639615
3398646784
3398647039
3398902272
3398902783
3399655424
3399659519
3399729152
3399745535
3399786496
3399794687
3399826432
3399826943
3399924736
3399925759
3400336384
3400336639
3400337152
3400337407
3400515584
3400531967
3400998912
3401003007
3406565888
3406566143
3407987712
3407987967
3408066048
3408066303
3409396480
3409396735
3410804736
3410821119
3411052544
3411054591
3411152896
3411154943
3411156992
3411161087
3411212288
3411212799
3411320832
3411329023
3411509248
3411542015
3411806208
3411808255
3412251104
3412251119
3412322304
3412324351
3412606976
3412615167
3413106688
3413110783
3413262336
3413270527
3413344256
3413360639
3413574656
3413575679
3414155520
3414155775
3414230016
3414230527
3414376448
3414409215
3415803392
3415805951
3416131584
3416133631
3416301568
3416317951
3416473728
3416473855
3416487424
3416487487
3416719360
3416727551
3416735744
3416752127
3416850432
3416851455
3416981504
3416982527
3416983040
3416983551
3417047040
3417055231
3417178112
3417179135
3417243648
3417244671
3417374720
3417440255
3418163200
3418165247
3418243072
3418251263
3418326528
3418327039
3418396784
3418396799
3418399232
3418399359
3418399440
3418399455
3418401536
3418401599
3418401632
3418401647
3418401720
3418401727
3418401888
3418401903
3418649888
3418649951
3418652160
3418652163
3418652168
3418652171
3418652184
3418652207
3419412480
3419414527
3419783168
3419791359
3419881472
3419897855
3419924480
3419926527
3448257792
3448258047
3453373136
3453373143
3453374568
3453374583
3453374792
3453374807
3459338496
3459339263
3460948736
3460948799
3463602688
3463602943
3465438208
3465438463
3465475072
3465475583
3465476352
3465476607
3466044904
3466044911
3468076000
3468076031
3468085192
3468085199
3468085552
3468085567
3468096768
3468096895
3470660008
3470660015
3470660896
3470660903
3473096193
3473096447
3474193408
3474193663
3474193920
3474194431
3480605440
3480605695
3480605952
3480606207
3481029376
3481029631
3481032960
3481033727
3481039360
3481039871
3486607872
3486608127
3486615296
3486615551
3486624000
3486624255
3489738752
3489740799
3494454129
3494454158
3496290760
3496290767
3496292320
3496292335
3504922624
3504923391
3505119232
3505119487
3508082688
3508082943
3508098304
3508098559
3508100608
3508100863
3508281344
3508281599
3508286912
3508286927
3508337152
3508337663
3509834208
3509834223
3509836872
3509836879
3512562944
3512563071
3512563968
3512564095
3512565248
3512565503
3512577600
3512577631
3512590976
3512591103
3512592896
3512593151
3512598272
3512598527
3518895720
3518895727
3523297280
3523317759
3523477504
3523493887
3523502080
3523510271
3523559424
3523575807
3524132864
3524145151
3524263936
3524266495
3524266752
3524274175
3524274432
3524296703
3524747264
3524755455
3524763648
3524781791
3524781824
3524788223
3535380480
3535388671
3537190912
3537240063
3570076944
3570076951
3624298496
3624299519
3628154240
3628154303
3632480608
3632480615
3632481288
3632481295
3632483856
3632483863
3632484080
3632484087
3632485632
3632485647
3632490688
3632490695
3632494560
3632494567
3680124928
3680133119
3715719168
3715727359
3732799488
3732832255
3732865024
3732930559
Hi
I have coded the simple login page in vb .net which calls the stored proc to verify whether the user login details exists in the database. The stored procudure returns data back when I execute it in the SQL SERVER Management studio. But when I execute the stored proc in the 'Run stored Proc' wizard' , it is not retuning any data back. Connection string works fine as another SQL select command returns data in the same page.. I have included the VB code . Please help me to sort out this problem.Thank you.
If Not ((txtuser.Text = "") Or (txtpassword.Text = "")) Then
Dim conn As New SqlConnection()
conn.ConnectionString = Session("constr")
conn.Open()
Dim cmd As New SqlCommand("dbo.CheckLogin", conn)
cmd.CommandType = CommandType.StoredProcedure
' Create a SqlParameter for each parameter in the stored procedure.
Dim usernameParam As New SqlParameter("@userName", SqlDbType.VarChar, 10)
usernameParam.Value = Trim(txtuser.Text)
Dim pswdParam As New SqlParameter("@password", SqlDbType.NVarChar, 10)
pswdParam.Value = Trim(txtpassword.Text)
Dim useridParam As New SqlParameter("@userid", SqlDbType.NChar, 5)
Dim usercodeParam As New SqlParameter("@usercode", SqlDbType.VarChar, 10)
Dim levelParam As New SqlParameter("@levelname", SqlDbType.VarChar, 50)
'IMPORTANT - must set Direction as Output
useridParam.Direction = ParameterDirection.Output
usercodeParam.Direction = ParameterDirection.Output
levelParam.Direction = ParameterDirection.Output
'Finally, add the parameter to the Command's Parameters collection
cmd.Parameters.Add(usernameParam)
cmd.Parameters.Add(pswdParam)
cmd.Parameters.Add(useridParam)
cmd.Parameters.Add(usercodeParam)
cmd.Parameters.Add(levelParam)
Dim reader1 As SqlDataReader
Try
If conn.State = ConnectionState.Closed Then
conn.Open()
End If
Try
reader1 = cmd.ExecuteReader
Using reader1
If reader1.Read Then
Response.Write(CStr(reader1.Read))
Session("userid") = reader1.GetValue(0)
Session("usercode") = CStr(usercodeParam.Value)
Session("level") = CStr(levelParam.Value)
Server.Transfer("home.aspx")
Else
ErrorLbl.Text = "Inavlid Login. Please Try logging again" & Session("userid") & Session("usercode") & Session("level")
End If
End Using
Catch ex As InvalidOperationException
ErrorLbl.Text = ex.ToString()
End Try
Finally
If conn.State <> ConnectionState.Closed Then
conn.Close()
End If
End Try
Else
ErrorLbl.Text = "Please enter you username and password"
End If
Hi All,
I want to insert data using a stored proc. Can anyone tell me the correct syntax for inserting data into a table using a stored proc?
Thanks
Hi!I have this table:Units -id uniqueidentified (PK), -groupName NVARCHAR(50) NOT NULL, -name NVARCHAR(50) NOT NULL, -ratio float NULL and the stored proc that simply returns all rows:ALTER PROCEDURE dbo.ilgSP_GetUnitsAS SELECT [id], [groupName], [name], [ratio] FROM [Units] ORDER BY [groupName], [name]If I select 'Show Table Data' in Visual Studio 2005 I see all rows from the table. If I 'Execute' my stored from VS 2005 I get this:Running [dbo].[ilgSP_GetUnits].id groupName name ratio -------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------- No rows affected.(1 row(s) returned)@RETURN_VALUE = 0Finished running [dbo].[ilgSP_GetUnits].And I don't get any data in my ASP.NET application. WHY?Thanks!
View 1 Replies View RelatedHello,
I have created a MS Exchange 2000 link server in my MS SQL Server 2k. I have created a stored procedure (and a view...) using info from that linked server. When I am logged on the server as the Administrator, I can call my stored proc without any problems. When I use another computer (and I am not logged as the admin of the server) and I call the stored procedure, the following error is always raised :
Server: Msg 7302, Level 16, State 1, Procedure test_proc, Line 3
" Impossible de créer une instance du fournisseur OLE DB 'exoledb.DataSource.1'. "
<== I know it is a french error but it can be translated as : "Unable to instancied the OLE DB 'exoledb.DataSource.1' provider"
I would like to know if I can make run my stored proc in the admin account or what should I do to make it work
I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedHey,
can one of you please show me how to import data from a text file into a temp table in a stored proc.
thanks
Zoey
Hi...
I want to retrieve SQL 2000 Encrypted Column Data From SQL 2005 strored proc. My Stored Procedure was on SQL 2000 and it works fine....Then I restore Database From SQL 2000 to SQL 2005. The Following Statement is on my store proce.
select user_id , Encrypt(user_pass) from OpenRowset('SQLOLEDB','myserver';'sa';'mypass',databasename.dbo.users) as a
The Following Error I get When I execute the above statement.
Msg 195, Level 15, State 10, Line 1
'Encrypt' is not a recognized built-in function name.
Thank you.
Bal.
I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View Related
I need to loop the recordset returned from a ExecuteSQL task and transform each row using a Data Conversion task (or a Script Task).
I know how to loop the recordset returned by an ExecuteSQL task:
http://www.sqlis.com/59.aspx
I loop the returned recordset (which is mapped to a User variable of type System.Object) and assign the Variable Mappings in the ForEach Loop to different user variables which map to the Exec proc resultset (with names and data types).
I assume to now use these as the Available Input columns for the Data Conversion task, I drag a Data Flow task inside the For Each Loop container and double-click it, then add a Data Conversion task.
But the Input columns (which I entered in the Variable Mappings in the ForEach Loop containers) dont show up in the Available Input columns of the Data Conversion task.
How do I link the Variable Mappings in the ForEach Loop containers from the recordset returned by the Execute SQL Task to the Available Input columns of the Data Conversion task?
.......................
If this is not possible, and the advice is to use the OLEDB data flow as the input for the Data Conversion task (which is something I tried too), then the results from an OLEDB Command (using EXEC sp_myproc) are not mapped to the Available Input columns of the Data Conversion task either (as its not an explicit SQL Statement and the runtime results from a stored proc exection)
I would like to use the ExecuteSQL task to do this as the Package is clean and comprehensible. Which is the easiest best way to map the returned results from a Stored proc execution to the Available Input columns of any Data Flow transformation task for the transform operations I need to execute on each row of data?
[ Could not find any useful advice on this anywhere ]
thanks in advance!
Hi,
We have an application written in ASP that calls a MS-SQL stored procedure and passes several parameters. Once in a while, we get this error, but most of the time it works. We can also run this in SQL query analyzer without ever a problem. We've looked at the obvious and found nothing wrong.
Please help! Any suggestions are appreciated.
(I posted this in ASP discussion board but no one replied)
Colleen
--------------------------------------------------------------------------------
|
In which system table the information about the optional parameters passed to stored procedure are stored.I know about the tbl_columns and all that stuff. From where can i can come to know the parameter is mandatory or optional.--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedHi there i really cant understand why this should be a problem... it seems that i cant create a table from a stored procedure when passing the tablenam from application like this...
CREATE PROCEDURE dbo.CreateTable
@TableName NVARCHAR(50)
AS
BEGIN
create table @TableName ([id] int identity(1,1),[diller] int)
end
GO
THIS CANT BE TRUE !!!!!!!!
I have a scenario where I need to compare a single DateTime field in a stored procedure against multiple values passed into the proc.So I was thinking I could pass in the DateTime values into the stored procedure with a User Defined Table type ... and then in the stored procedure I would need to run through that table of values and compare against the CreatedWhenUTC value.I could have the following queries for example:
WHERE CreatedWhenUTC <= dateValue1 OR CreatedWhenUTC <= dateValue2 OR CreatedWhenUTC <= dateValue 3
The <= is determined by another operator param passed in. So the query could also be:
WHERE CreatedWhenUTC > dateValue1 OR CreatedWhenUTC > dateValue2 OR CreateWhenUTC > dateValue3 OR CreateWhenUTC > dateValue4
Currently i am using SQL Server 2012 Import/Export Wizard to upload data to sql tables manually. However i was trying to write a procedure to update that table. and on the time of execution, if i can pass excel. Is there any way to pass excel to stored procedure parameter?
View 1 Replies View RelatedHi,I'm using c# with a tableadapter to call stored procedures. I'm running into a problem where if I have over a certain byte size or number of parameters being passed into my stored proc I get an exception that reads: "Cannot evaluate expression because a thread is stopped at a point where garbage collection is impossible, possibly because the code is optimized." If I remove one parameter, the problem goes away. Has anyone run into this before? Thanks,Mark
View 3 Replies View RelatedI'm trying to replace a view with stored procedure for faster performance. the View is called by end user using a query as below, I need to pass the date as parameter for sp to execute with quotes for it to execute with correct results. Â I tried to pass the date as parameter but could not execute stored procedure with correct results. Is there any way to put quotes around returned date from sub query :Â
Execute statement is like
Exec dbo.storedProc1Â select max(date) from table
I need to pass the above as :
Exec dbo.storedProc '2015-03-24'Â .
Somehow passing the date as parameter is giving me empty result set.
I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
Using a string of IDs passed into a stored procedure as a VARCHAR parameter ('1,2,100,1020,') in an IN without parsing the list to a temp table or table variable. Here's the situation, I've got a stored procedure that is called all the time. It's working with some larger tables (100+ Million rows). The procedure passes in as one of the variables a list of IDs for the large table. This list can have anywhere from 1 to ~100 IDs passed to it.
Currently, we are using a function to parse the list of IDs into a temp table then joining the temp table to get the query:
CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
[Code] .....
The problem we're running into is that since this proc gets called so often, we sometimes run into tempDB contention that slows this down. In my testing (unfortunately I don't have a good way of generating a production load) swapping the #table for an @table didn't make any difference which makes sense to me given that they are both allocated in the tempDB. One approach that I tried was that since the SELECT query is pretty simple, I moved it to dynamic SQL:
CREATE PROCEDURE [dbo].[GetStuff] (
@IdList varchar(max)
)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
[Code] ....
The problem I had there, is that it creates an Ad Hoc plan for the query and only reuses it if the same list of parameters are passed in, so I get a higher CPU cost because it compiles a plan and it also causes the plan cache to bloat since the parameter list is almost always different. Is there an approach that I haven't considered that may get the best of both worlds, avoiding or minimizing tempDB contention but also not having to compile a new plan every time the proc is run?
When a SQL statement is executed against a SQL Server database is therea server-side setting which dictates the size of the fetch buffer? Weare having some blocking issues on a new server install which do notoccur on the old server until a much larger volume of data is selected.Any help appreciated.
View 2 Replies View RelatedMy package sets a variable in an ExecSQL task. This variable is then used as a parameter in a DataFlow task that follows. Normally everything works just fine. However, sometimes if the package fails inbetween the step that sets the variable and the DataFlow, the default value of the variable is stored in the checkpoint file. (It is not failing in the step that sets the variable) When the package is restarted I can see that the variable is not set to the data value in the database, but rather it has its default (design time) value.
Anyone else see this bug?
I am writing a stored procedure that takes in a customer number, a current (most recent) sales order quote, a prior (to most current) sales order quote, a current item 1, and a prior item 1, all of these parameters are required.Then I have current item 2, prior item 2, current item 3, prior item 3, which are optional.
I added an IF to check for the value of current item 2, prior item 2, current item 3, prior item 3, if there are values, then variable tables are created and filled with data, then are retrieved. As it is, my stored procedure returns 3 sets of data when current item 1, prior item 1, current item 2, prior item 2, current item 3, prior item 3 are passed to it, and only one if 2, and 3 are omitted.I would like to learn how can I return this as a one data set, either using a full outer join, or a union all?I am including a copy of my stored procedure as it is.
I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.
Error from SQL Debug:
---
Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.
---
Error from page execution:
---
Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.
Source Error:
Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")
Line 78:
Line 79: cmd.ExecuteNonQuery()
---
Below is what I currently have for my stored procedure and the pertinent code from the page itself.
Stored Procedure:
---
CREATE PROCEDURE dbo.AdminAddUser( @username varchar(100),
@password varchar(100),
@email varchar(100),
@acct_type varchar(100),
@realname varchar(100),
@billname varchar(100),
@addr1 varchar(100),
@addr2 varchar(100),
@city varchar(100),
@state varchar(100),
@country varchar(100),
@zip varchar(100),
@memo varchar(100) )
AS
BEGIN TRAN
--
-- Returns 1 if successful
-- 2 if username already exists
-- 0 if there was an error adding the user
--
SET NOCOUNT ON
DECLARE @error int, @rowcount int
--
-- Make sure that there isn't already a user with this username
--
SELECT userID
FROM users
WHERE username=@username
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @error <> 0 OR @rowcount > 0 BEGIN
ROLLBACK TRAN
RETURN 2
END
--
-- Set expiration date
--
DECLARE @expDate AS smalldatetime
IF @acct_type = "new_1yr" BEGIN
SET @expDate = DATEADD( yyyy, 1, GETDATE() )
END
IF @acct_type = "new_life" BEGIN
SET @expDate = DATEADD( yyyy, 40, GETDATE() )
END
DECLARE @paidCopies AS decimal
SET @paidCopies = 5
--
-- Add this user to the database
--
IF @acct_type <> "new" BEGIN
INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userPaidCopies, userExpirationDate, userLastPaidDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@paidCopies, @expDate, GETDATE(), @addr1, @addr2, @city, @state, @country, @zip, @memo )
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END
END
IF @acct_type = "new" BEGIN
INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@addr1, @addr2, @city, @state, @country, @zip, @memo )
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END
END
COMMIT TRAN
RETURN 1
GO
---
Page Code:
---
Sub AddUser(Sender as Object, e as EventArgs)
Dim db_conn_str As String
Dim db_conn As OleDbConnection
Dim resultAs Int32
Dim cmdAs OleDbCommand
db_conn_str = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=xxxxx; User ID=xxxxx; PWD=xxxxx;"
db_conn = New OleDbConnection( db_conn_str )
db_conn.Open()
cmd = new OleDbCommand( "AdminAddUser", db_conn )
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( "result", OleDbType.Integer ).Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add( "@username", OleDbType.VarChar, 100 ).Value = Request.Form("userName")
cmd.Parameters.Add( "@password", OleDbType.VarChar, 100 ).Value = Request.Form("userPass")
cmd.Parameters.Add( "@email", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@acct_type", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@realname", OleDbType.VarChar, 100 ).Value = Request.Form("userRealName")
cmd.Parameters.Add( "@billname", OleDbType.VarChar, 100 ).Value = Request.Form("userBname")
cmd.Parameters.Add( "@addr1", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr1")
cmd.Parameters.Add( "@addr2", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr2")
cmd.Parameters.Add( "@city", OleDbType.VarChar, 100 ).Value = Request.Form("userCity")
cmd.Parameters.Add( "@state", OleDbType.VarChar, 100 ).Value = Request.Form("userState")
cmd.Parameters.Add( "@country", OleDbType.VarChar, 100 ).Value = Request.Form("userCountry")
cmd.Parameters.Add( "@memo", OleDbType.VarChar, 100 ).Value = Request.Form("userMemo")
cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")
cmd.ExecuteNonQuery()
(...)
---