Using SSIS To Output SP Return Data To A Share
May 4, 2008
I'm new to SSIS. I'm looking for basic pointers to return temp table created by a proc to be written to file shares. Any pointers/examples appreciated.
Thanks,
Siva
ADVERTISEMENT
Sending Isql Output To Remote Share
Sep 15, 1998I have a query I`d like to run using the isql command utility. The query works correctly and the output can be sent to a local file with no problem. When I try to designate a network location as an output file (e.g. abcdefgfile.txt), though, I get an error message "No such file or directory."
Is it possible to send SQL output to a remote location?
TIA!
Using Output Clause And Return Timestamp And Return Value
Oct 28, 2006I am trying to bring my stored proc's into the 21st century with try catch and the output clause. In the past I have returned info like the new timestamp, the new identity (if an insert sproc), username with output params and a return value as well. I have checked if error is a concurrency violation(I check if @@rowcount is 0 and if so my return value is a special number.)
I have used the old goto method for trapping errors committing or rolling back the transaction.
Now I want to use the try,catch with transactions. This is easy enough but how do I do what I had done before?
I get an error returning the new timestamp in the Output clause (tstamp is my timestamp field -- so I am using inserted.tstamp).
Plus how do I check for concerrency error. Is it the same as before and if so would the check of @@rowcount be in the catch section?
So how to return timestamp and a return value and how to check for concurrency all in the try/catch.
by the way I read that you could not return an identity in the output clause but I had no problem.
Thanks for help on this
SM haig
Output Data Is 2 TIMES The Input Data.SSIS.Newbie
Apr 10, 2008
Hello All,
I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.
I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.
What could be the reasons. Please help me out.
Thank You
Using SSIS To Access Network Share From Different Domain
Feb 12, 2008Hey All,
This is my first time using the forums here nor am I a SQL programmer. I'm trying to help a coworker figure out how to access files from a network share (using a UNC path) from a different domain. Right now we have three domains - Production, Development, and the Local. There is a one-way trust setup with the development and local domains, so using cross domain accounts is easy. Unfortunately, we cannot setup one right now with our production domain to any of the other two domains. Is there a way to pass production credentials from our local or development domain servers to a production server share? Am I even approaching this in the right way? Maybe there is a different method, any help would be GREATLY appreciated!
-Andrew
Network Administrator
SSIS Flakey Dealing With Share Names
Apr 19, 2007I have found SSIS is showing some strange behaviour when dealing with share names for file connections.
I have been using Package configurations and file connections to pick up excel files are using the name of the Share as the path to the file location.
What is strange is that SSIS appears sometimes to successfully see the file and other times fails with the error "....does not exist"
eg I have run a package successfully picking up these excel files using the share name. I have then run the same package IMMEDIATELY again using the exact same files from the same share location and it will then fail with the above error.
Has anyone else seen this behaviour ?
SSIS Package Storage In A UNC Network Share?
Aug 13, 2007Somehow I have an impression this can be done.
Somehow I tried myself by modifying MsDtsSrvr.ini.xml to point it to network UNC share, but it didn't work for me. It gave me an error
"Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
Additional Information: The storage localtion for the folder 'File System' cannot be accessed. (MsDtsSrvr)"
I checked my UNC permission, and I even allow full access from "everyone", but it still does not work.
I paste my MsDtsSrvr.ini.xml here too, and any help is greatly appreciated!
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>\test01mytest</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
Problem With Accessing Unix Share Drive When A SSIS Job Runs
Sep 13, 2007Hi I am trying to schedule a job to copy an MDB data file from Unix server to Windows 2003 server (Accfp1_data2_server). I have created a file copy SSIS package and tested it in the SSIS Visual Studio environment where it runs ok. The package was created while logged in as a domain administrator.
I then created a job to run this package (which is stored on a folder) using the credential of the same domain administrator who has full access privilege to both of these servers. However, the job fails whenever it is run manually or scheduled? The error message displayed is given below
---------------------------------------------------------------
Message
Executed as user: FORTIESABCITYG.
Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005.
All rights reserved. Started: 14:26:07 Error: 2007-09-13 14:26:12.56 Code: 0xC001401E
Source: CommunityContact - Copy MS Access Database Connection manager "CONTACT.mdb On Accfp1_data2_server"
Description: The file name "\Accfp1_data2_serverDATA2Arts&recAppsContactsCONTACT.mdb" specified in the connection was not valid.
End Error Error: 2007-09-13 14:26:12.56 Code: 0xC001401D Source: CommunityContact - Copy MS Access Database Description: Connection "CONTACT.mdb On Accfp1_data2_server" failed validation. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 14:26:07 Finished: 14:26:12 Elapsed: 5.297 seconds. The package execution failed. The step failed.
---------------------------------------------------------------
Please note that the job runs without problem when I change the source file to a Windows 2000 server share . How bizzare? Hope this is not a Microsoft's Trick?
Can anyone help?
SSIS Package Cannot Access Remote UNC File Via The Admin Share
Mar 6, 2008I am developing a package to restore a database from backup file on a remote server. I am having problems accessing the remote backup file when it is addressed via the admin share, in this case N$. It runs okay if a specific share is created but for some unknown reason fails via the adminshare.
I am executing the package job with a proxy account that is a member of the local administrators group on the remote server.
It appears that access via a remote admin share isn't possible from within a SSIS package. Is this the case?
SSIS Hard Time Getting Back XML Return Data From Stored Procedure Call Executed By Execute SQL Task
Aug 9, 2006I'm having a hard time to getting back an xml data back from a stored procedure executed by an Execute SQL task.
I'm passing in an XML data as a parameter and getting back resulting XML data as a parameter. The Execute SQL task is using ADO connection to do this job. The two parameters(in/out) are type of "string" and mapped as string.
When I execute the task, I get the following error message.
[Execute SQL Task] Error: Executing the query "dbo.PromissorPLEDataUpload" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@LogXML"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I also tried mapping the parameter as XML type, but that didn't work either.
If anyone knows what's going on or how to fix this problem please let me know. All I want to do is save returning XML data in the parameter to a local package variable.
Thanks
Output Parameter Not Return The Value
Nov 7, 2007 In my SP I have an output parameter,
the SP work good,
but in the command parameter I alwase get null value/
here is my code:
and in the profiler I see that the query work and return the value, here the query that I copy from the profiler:
thanks!
Return Output Parm
Oct 15, 2004The stored Procedure I am using
CREATE PROCEDURE GetUserID
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserID int OUTPUT
)
AS
SET @UserID = (SELECT fldResourceID
FROM tblResources
WHERE fldEmail = @Email AND fldPassword = @Password)
GO
The Code Behind to call it.
Dim intUserID As Integer = 0
Dim strConnectionString As String = dbConnect()
Dim objConnection As SqlConnection = New SqlConnection(strConnectionString)
objConnection.Open()
Dim objCmd As SqlCommand = New SqlCommand("EXEC GetUserID '" & strEmail & "','" & strPassword & "', " & intUserID, objConnection)
intUserID = objCmd.ExecuteScalar()
objCmd = Nothing
objConnection = Nothing
Return intUserID
However nothing is returned from the objCmd.ExecuteScalar line. Can anyone help me out here. All I need it to return is a single value. Thanks
Output Parameter Or Return Value
May 2, 2005I'm having a terrible time getting a return value from my stored procedure. The sp sets the value and returns it correctly in the query analyzer, but I can't get the calling code - using VB in VS2003 - to read the value. I've tried putting it into an integer, and using a Return, and also putting it into a string, and using an output parameter. Either way: nada. What am I doing wrong????
Here's the sp:
CREATE PROCEDURE sp_HOUSRoomAudits @Result VarChar(1) OUTPUT, @Dorm VarChar(12), @Room VarChar(5), @StuID VarChar(14) AS
DECLARE @Capacity IntDECLARE @Assigned Int--DECLARE @Result VarChar(1)DECLARE @BD BIT
SET @BD=(SELECT Boarding FROM HOUS_StudentMaster WHERE StudentID=@StuID)
SET @Capacity=(SELECT [Student Capacity] FROM HOUS_Rooms WHERE Bldg=@Dorm AND [Room #]=@Room)
SET @Assigned=(SELECT COUNT(PSID) FROM HOUS_StudentMaster a JOIN HOUS_Dorms b ON a.Dormatory=b.Dormitory WHERE b.Bldg=@Dorm AND a.Status='Active' AND a.Room=@Room)
IF (@BD=1 AND @Room='N/A') OR (@BD=0 AND @Room<>'N/A')
SET @Result='3'ELSE IF @Assigned<@Capacity SET @Result='1' ELSE SET @Result='2'
print @Result--RETURN @ResultGO
Here's the calling code; I'm leaving out all the dim's etc down to the output parameter:
Dim Parm4 As SqlParameter = scmdReturn.Parameters.Add("@Result", SqlDbType.VarChar)
Parm4.Direction = ParameterDirection.Output
Parm4.Value = "0"
Try
scmdReturn.ExecuteNonQuery()
strOut = Parm4.Value
Catch ex As Exception
Me.lblProblem.Text = "Failed to execute command, err = " & ex.Message
Me.lblProblem.Visible = True
Finally
scnnReturn.Close()
End Try
Hoping you can help, and thanks!
Ka
How To Share Read Only Data
Jun 15, 2007Hi,
I'm new to replication. Can anyone help with the following?
I want to publish the specific rows of data that are created on the publisher without accepting changes to those rows from subscribers, but do accept new rows (and changes to those rows) from subscribers.
EG. publisher creates rows 1 and 2 which are published to the subscriber
subscriber creates rows 3 and 4 which are merged back to the publisher
subscriber's updates (or delets) to rows 1 and 2 are not merged back to the publisher
So basically I want the publisher created rows to be published but remain as is whilst allowing subscriber created rows to be merged back to the publisher.
Hope is makes sense.
Thanks in advance,
Seedsy
Return Output Values From View
Jan 2, 2006How can I return values from my SQL view back into my aspx page and shove each returned value into variables I define for certain returned fields?
View 2 Replies View RelatedReturn 4 Output From A Function In Sqlserver
Jun 16, 2008Hi I need a function which gets 3 parameters and input and returns 4 integers as output.
I don't know how to return 4 integers as output
any idea is appreciated.
Return BIGINT OUTPUT Param To VB!?
May 9, 2006Please help me on this one.
I need to return a value to VB.
I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!?
Here's part of my stored proc
CREATE PROCEDURE dbo.uspCalcWeightedAverage @StartDate2 varchar(10), @EndDate2 varchar(10), @InMarket nvarchar(50), @InProductType int, @InWeekDay int, @WeightedAverage bigint OUTPUTAS......SELECT @WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)FROM TBL_PRODUCTDEFS PDF INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID AND [PDF].[HOUR] = [HHF].[HOUR] AND DATEPART(M,DBI.DATE) = [HHF].[Month]WHERE PDF.MARKETID = @InMarketID AND PDF.PRODUCTTYPEID = @InProductTypeID AND [PDF].[WD-WE] = @InWeekDay AND HHF.MARKETID = @InMarketID AND PDF.FLAG = 1GROUP BY FLAG
When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props:
attribute 64 (Long)
NumericScale 0 (Byte)
Precision 19 (Byte)
Size 0 (ADO_LNGPTR)
Type adBigInt
Value Null
I try to return it with the following code (got the code from a friend)
Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.RecordsetDim objAdoRecordset As ADODB.RecordsetDim objAdoCommand As ADODB.CommandDim lngCtr As Long On Error GoTo RunCommand_Error ' Create cmd object Set objAdoCommand = New ADODB.Command Set objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.CommandText = v_strStoredProcName objAdoCommand.CommandType = adCmdStoredProc Call objAdoCommand.Parameters.Refresh 'Stop For lngCtr = 0 To UBound(r_varParamValues) If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr) End If Next Set objAdoRecordset = New ADODB.Recordset objAdoRecordset.CursorLocation = adUseClient Set objAdoRecordset = objAdoCommand.Execute 'Stop For lngCtr = 0 To objAdoCommand.Parameters.Count - 1 If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value End If Next Set RunProcedure = objAdoRecordsetRunCommand_Exit: ' Collect your garbage here Exit FunctionRunCommand_Error: ' Collect your garbage here Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")End Function
PLEASE HELP.
Regards,
Sander
Transact SQL :: Return Value 1 For Output Parameter
Oct 7, 2015I have a stored proc which will insert and update the table. In this stored procedure I have a output parameter called @rows which is by default 0. Now when ever I insert or update the table and it is successful, then the output parameter should be 1 i.e the out parameter should return value 1 or else default 0.
Here is my code:
ALTER PROCEDURE [dbo].[sample]
(
@TestVARCHAR(256),
@Created_by Nvarchar (256),
@name nvarchar (100),
@rows int=0 output
)
[Code] ....
How To Get Store Procedure's Return Value And Output Parameters From BLL?
Oct 10, 2007ex:
myprocedure(@Cusname varchar(50), @Cusid int output)as
Insert into Customer(Cusname) values (@Cusname)SELECT @cusid = @@IDENTITY
i add the query to my adapter called CreatCustomer (@Cusnam,@Cusid)private Merp_CusListTableAdapter _CuslistAdapter = null;protected Merp_CusListTableAdapter Adapter
{
get
{if (_CuslistAdapter == null)
_CuslistAdapter = new Merp_CusListTableAdapter();return _CuslistAdapter;
}
}
Now how i write function in BLL to receive output paramter from creatcustomer function?
Return Output Parameter From Stored Procedure?
Feb 6, 2008I am trying to retrieve the Output value from running a Stored Procedure. How can I retrieve the value into a variable?
Below is the source code that I have:1 ' Insert question_text into Question table
2 Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
3 Dim cmd As New SqlCommand
4 Dim QuestionID As Integer
5
6 ' Set connection to DB
7 cmd.Connection = conn
8
9 cmd.CommandText = "usp_QuestionResponse_ins"
10 cmd.CommandType = Data.CommandType.StoredProcedure
11
12 Dim sp1 As SqlParameter = cmd.Parameters.Add(New SqlParameter("@question_id", SqlDbType.Int, 4)).Direction = ParameterDirection.Output
13 cmd.Parameters.Add(New SqlParameter("@assessment_id", AssessmentID))
14 cmd.Parameters.Add(New SqlParameter("@domain_id", DomainID))
15 cmd.Parameters.Add(New SqlParameter("@question_text_en", QuestionTextEn))
16 cmd.Parameters.Add(New SqlParameter("@question_text_sp", QuestionTextSp))
17 cmd.Parameters.Add(New SqlParameter("@category_en", CategoryEn))
18 cmd.Parameters.Add(New SqlParameter("@display_order", DisplayOrder))
19 cmd.Parameters.Add(New SqlParameter("@question_template_id", 3))
20 QuestionID = sp1.Value
21 cmd.Connection.Open()
22 cmd.ExecuteNonQuery()
23
24 conn.Close()
25 cmd.Dispose()
26
Here is the Stored Procedure that I have: 1 ALTER PROCEDURE usp_QuestionResponse_ins
2 (
3 @question_id int = null output,
4 @assessment_id int = null,
5 @domain_id int = null,
6 @question_text_en nvarchar(1000) = null,
7 @question_text_sp nvarchar(1000) = null,
8 @category_en nvarchar(500) = null,
9 @display_order smallint = null,
10 @question_template_id int = null
11 )
12 As
13 BEGIN
14 DECLARE @Err Int
15
16 DECLARE @QuestionID INT
17 DECLARE @ReturnValue INT
18 SET @ReturnValue = 0
19
20 DECLARE @CategoryId int
21 SELECT @CategoryId = NULL
22
23 -- Check to see if an answer has already been inserted (i.e. revisiting)
24 SELECT @CategoryId = category_id FROM category WHERE (category_name = @category_en) AND (active_f = 1) AND (delete_f = 0)
25 IF ( @CategoryId IS NOT NULL )
26 BEGIN
27 EXEC @ReturnValue = usp_question_ins @question_id OUTPUT, @assessment_id, @domain_id, 2, 1, 'Right', @display_order, 1, 8, @CategoryID, @question_text_en, Null, Null, 'Horizontal', 0, Null, Null, 0, 1, 0
28 SET @QuestionID = @ReturnValue
29 END
30
31 IF ( @QuestionID IS NOT NULL )
32 BEGIN
33 DECLARE @question_locale_id int
34 EXEC usp_question_locale_ins @QuestionID, 1, @question_text_en, 1, 0
35 EXEC usp_question_locale_ins @QuestionID, 2, @question_text_sp, 1, 0
36
37 END
38
39 RETURN @QuestionID
40 End
What am I doing wrong? How do I properly capture the QuestionID from the SP? Running the SP from command line works fine.
Thanks
Stored Procedure With Parameters OUTPUT And RETURN VALUE
Oct 26, 2000Hi
How I building a stored procedure that
return a output parameter and value return that I can
call from other stored procedure or VB ?
thank you in advance
SP Syntax Help - Return New Identity Value As Output Param
Feb 4, 1999I have a stored proc that will insert a new row into a table
with the values of the parameters you pass in. I need it to
return the value of the ID that's generated by an Identity
column once the row has been written and that value has
been generated. If I just do a SELECT Max(), I could
accidentally grab a row written by someone else, right?
My current sp looks like this:
CREATE PROCEDURE sp_SaveNewLabel
-- @LabelID int output
@LabelType int
, @Logo int
, @Field01 char(30)
AS
INSERT INTO tbLabel
(LabelType
, Logo
, Field01)
VALUES
(@LabelType
, @Logo
, @PrintCC
, @Field01)
How do I grab the new LabelID (the column is int, Identity) and return
it from the stored proc. Any help would be greatly appreciated...
Zack
Return Resultset As Output Parameter To VB Application
Nov 27, 2014I need to return a resultset consisting of database errors from SQL Server stored procedure's CATCH CLAUSE but stuck with it. Do I need to use cursors to return resultset and if so, then what is the type declaration for the OUTPUT parameter in my .NET application? I tried 'Object' and 'Variant' but did not work.
I also tried the simple way just using a SELECT statement to return and it works with one stored procedure but not with another as thus in my CATCH CLAUSE:
Code:
while (@I <= @count)
begin
BEGIN TRY
-- delete all previous rows inserted in @customerRow for previous counts @I
delete from @customerRow
-- this is inserting the current row that we want to save in database
insert into @customerRow
[Code] .....
This does not work when select is made in the CATCH block, ie it returns no rows to my .NET application:
Code:
begin catch
IF @@TranCount > 0 or XACT_STATE()= -1 ROLLBACK TRANSACTION;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
SELECT @ErrorMessage = ERROR_MESSAGE();
[Code] ....
Just to summarize the code and where the problem is. The code that works uses a SELECT * from temporary table not in the CATCH block and this works while the one that uses the same SELECT * from temporary table inside the CATCH does not return anything! Strange. The Wrong one is used in another Stored procedure from the Right one btw so am wondering why the same code does not work in those two different situations.
I Can Return INT From A Store Procedure But Get Error When OUTPUT A Varchar
Mar 21, 2008My store procedure get the QuestionID (PK) from the page and then it's to return a few varchars but gives me the error that string can't be converted to int. ALTER PROCEDURE [dbo].[usp_getQuestionsforEditPopulateText]@QuestionID int,@QuestionDescription varchar(MAX) OUTPUT,@Option1 varchar(50) OUTPUT,@Option2 varchar(50) OUTPUT,@Option3 varchar(50) OUTPUT,@Option4 varchar(50) OUTPUT,@Option5 varchar(50) OUTPUT,@reference varchar(50) OUTPUT,@chb1 int OUTPUT,@chb2 int OUTPUT,@chb3 int OUTPUT,@chb4 int OUTPUT,@chb5 int OUTPUTAsSet @QuestionDescription =(Select questionDescription from QuestionsBank Where questionID = @QuestionID)Set @Option1 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 1)Set @Option2 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 2)Set @Option3 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 3)Set @Option4 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 4)Set @Option5 =(Select optionDescription from options Where questionID = @QuestionID and optionNumber = 5)Set @reference = (Select referencedescription from reference where questionID = @QuestionID)Set @chb1 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 1)Set @chb2 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 2)Set @chb3 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 3)Set @chb4 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 4)Set @chb5 = (Select correctOption from options where questionID = @QuestionID and optionNumber = 5) RETURN This is what the page callsDim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection("myconnectionstring ") Dim cmdUpdate As New Data.SqlClient.SqlCommand("usp_getQuestionsforEditPopulateText", dbConnection) cmdUpdate.CommandType = Data.CommandType.StoredProcedure cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionID", Data.SqlDbType.Int)) cmdUpdate.Parameters("@QuestionID").Value = QuestionID cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@QuestionDescription", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@QuestionDescription").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option1", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option2", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option3", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option4", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@Option5", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@Option5").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@reference", Data.SqlDbType.VarChar)) cmdUpdate.Parameters("@reference").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb1", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb1").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb2", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb2").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb3", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb3").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb4", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb4").Direction = Data.ParameterDirection.Output cmdUpdate.Parameters.Add(New Data.SqlClient.SqlParameter("@chb5", Data.SqlDbType.Int)) cmdUpdate.Parameters("@chb5").Direction = Data.ParameterDirection.Output 'open connection dbConnection.Open() 'Execute non query cmdUpdate.ExecuteNonQuery() 'close connection dbConnection.Close()
View 8 Replies View RelatedCan I Return Output From Stored Procedure Into An HTA To Display In Browser?
Jul 20, 2005Hi all. I have a stored procedure on my sql server that returns asimple informtation about that tim a database was backed up. I owuldlike to create an HTA that operator types can look at to make surethat the backups finished, can someone help me do this?The stored procedure in called sp_lastback and the output looks likethis:database days since backup timestamp of backupdbase1 0 2004-10-14 00:41:21.000dbase2 0 2004-10-14 00:08:36.000dbase3 1 2004-10-13 22:46:57.000Can this be done? If someone could help me with this simple problem, Icould probably reuse the same method a 100 useful ways.Thanks in advance!
View 3 Replies View Related.NET Framework :: Stored Procedure Can't Return Output Param Value
Oct 8, 2015My stored procedure was running sqlserver 2012 but can't return output parameter value here i paste my SP ...
alter Procedure BD_InsertMobileClient_GpsLogPlanogram
(
@SalesPersonID int,
@MobileRowID int,
@StatusCode int output
)
[Code] .....
SqlCommand Return And Output Parameters Not Working, But Input Does?
Feb 18, 2006The foolowing code I cannot seem to get working right. There is an open connection c0 and a SqlCommand k0 persisting in class.The data in r0 is correct and gets the input arguments at r0=k0->ExecuteReader(), but nothing I do seems to get the output values. What am I missing about this?
System::Boolean rs::sp(System::String ^ ssp){
System::String ^ k0s0; bool bOK;
System::Data::SqlClient::SqlParameter ^ parami0;
System::Data::SqlClient::SqlParameter ^ parami1;
System::Data::SqlClient::SqlParameter ^ parami2;
System::Data::SqlClient::SqlParameter ^ paramz0;
System::Data::SqlClient::SqlParameter ^ paramz1;
System::Int32 pz0=0;System::Int32 pz1=0;
k0s = ssp;
k0->CommandType=System::Data::CommandType::StoredProcedure;
k0->CommandText=k0s;
paramz0=k0->Parameters->Add("@RETURN_VALUE", System::Data::SqlDbType::Int);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
//paramz0=k0->Parameters->AddWithValue("@RETURN_VALUE",pz0);
paramz0->Direction=System::Data::ParameterDirection::ReturnValue;
paramz0->DbType=System::Data::DbType::Int32;
parami0=k0->Parameters->AddWithValue("@DESCXV","chicken");
parami0->Direction=System::Data::ParameterDirection::Input;
parami1=k0->Parameters->AddWithValue("@SRCXV","UU");
parami1->Direction=System::Data::ParameterDirection::Input;
//paramz1=k0->Parameters->AddWithValue("@RCOUNT",pz1);
paramz1=k0->Parameters->Add("@RCOUNT",System::Data::SqlDbType::Int);
paramz1->Direction=System::Data::ParameterDirection::InputOutput;
paramz0->DbType=System::Data::DbType::Int32;
//k0->Parameters->GetParameter("@RCOUNT");
r0=k0->ExecuteReader();
//pz0=System::Convert::ToInt32(paramz0->SqlValue);
bOK=k0->Parameters->Contains("@RCOUNT");
//k0->Parameters->GetParameter("@RCOUNT");
pz0=System::Convert::ToInt32(paramz0->Value);
pz1=System::Convert::ToInt32(paramz1->Value);
ndx = -1;
while(r0->Read()){
if (ndx == -1){
ndx=0;
pai0ndx=0;
pad0ndx=0;
r0nf=r0->FieldCount::get();
for (iG1_20=0;iG1_20<r0nf;iG1_20++){
this->psf0[iG1_20]=this->r0->GetName(iG1_20);
this->psv0[iG1_20]=this->r0->GetDataTypeName(iG1_20);
this->psz0[iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
else {
pai0ndx=0;
pad0ndx=0;
for (iG1_20=0;iG1_20<r0nf;iG1_20++)
{ this->pas0[ndx,iG1_20]=System::Convert::ToString(this->r0->GetValue(iG1_20));
if (psv0[iG1_20]=="int") {pai0[ndx,pai0ndx]=System::Convert::ToInt32(r0->GetValue(iG1_20));pai0ndx++;}
if (psv0[iG1_20]=="float") {pad0[ndx,pad0ndx]=System::Convert::ToDouble(r0->GetValue(iG1_20));pad0ndx++;}
}
}
ndx++;
}
r0nr=ndx;
r0->Close();
k0->Parameters->Remove(paramz0);
k0->Parameters->Remove(parami1);
k0->Parameters->Remove(parami0);
k0->Parameters->Remove(paramz1);
return true;
}
How To Share User-defined Data Type In Different Database?
Nov 6, 2006Hi,everyone.
I have defined a data type "OC_BUN_NAME" in database "CVPRO". I want to create a table tempdb.dbo.CVPRO in SQL SERVER 2005 system Database tempdb. But SQL SERVER 2005 DBMS gives a Error Messages:"Can not find the data type OC_BUN_NAME".
How can I do? How to use data types in the other database?
Please give me some advice. Thank you in advance.
Transact SQL :: Bulk Insert When Data File Is On Network Share
Dec 3, 2015I am running Microsoft SQL Server 2012 SP on a Windows Server 2008 R2 Standard SP1 box. The SQL Server service is running as a simple windows domain user (nothing special, no admin rights, etc.) I am having some issues with using Bulk Insert when the data file is on a network share when using Windows Authentication. What is known is that the SQL Server service account has access to the network resource, which is shown by logging into SQL Server with a SQL account and doing the Bulk Insert. I also have rights to the files on the share, as shown by the fact that I put the files there. My SQL is in the form of:
Bulk Insert [table name] From '[server][share][filename]' With (FirstRow = 2, FormatFile='FormatFile.xml')
Now, when connecting to SQL Server with Windows Authentication and running the Bulk Insert I get the following error:
Msg 4861, Level 16, State 1, Line 2 Cannot bulk load because the file "[server][share][filename]" could not be opened. Operating system error code 5(Access is denied.).
I found this snip at
BULK INSERT (Transact-SQL)Security Account Delegation (Impersonation), which says, in part (emphasis mine):
To resolve this error [4861], use SQL Server Authentication and specify a SQL Server login that uses the security profile of the SQL Server process account, or configure Windows to enable security account delegation. For information about how to enable a user account to be trusted for delegation.
How to Configure the Server to be Trusted for Delegation, and we tried the unconstrained delegation and I rebooted the SQL server, but it still does not work. Later we tried constrained delegation and it still does not work.
I have verified the SPNs:
C:>setspn adsvc_sqlRegistered ServicePrincipalNames for CN=SVC_SQL,OU=Service Accounts,OU=Users,OU=ad domain,DC=ad,DC=local: MSSQLSvc/SQLQA.ad.local:1433 MSSQLSvc/SQLDev.ad.local:1433 MSSQLSvc/SQLQA.ad.local MSSQLSvc/SQLDev.ad.local
I have verified that my SQL connection is TCP and I am getting/using a Kerberos security token.
C:>sqlcmd -S tcp:SQLQA.ad.local,1433 -E1> Select dec.net_transport, dec.auth_scheme From sys.dm_exec_connections As dec Where session_id = @@Spid;2>
gonet_transport auth_scheme------------- -----------TCP KERBEROS(1 rows affected)1>
If I move the source file to a local drive (on the SQL server), all works fine, but I must be able to read from a file share?
How To Display Return Value From Stored Procedure Output Parameter In Query Analyzer
Jul 20, 2004I tried to display return value from stored procedure output parameter in Query Analyzer, but I do not know how to do it. Below is my stored procedure:
CREATE PROCEDURE UserLogin
(
@Email nvarchar(100),
@Password nvarchar(50),
@UserName nvarchar(100) OUTPUT
)
AS
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
GO
If I run the query in Query Analyzer directly, I can display @UserName:
DECLARE @UserName as nvarchar(100)
SELECT @UserName = Name FROM Users
WHERE Email = @Email AND Password = @Password
Select @UserName
But how can I display @UserName if I call stored procedure:
exec UserLogin 'email', 'password', ''
I believed it return int, right?
Thanks in advance for help.
SQL Server Admin 2014 :: Service SIDs With Data Files On CIFS Share?
Jan 13, 2014I want to use service SIDs for my SQL Service accounts but also want to have the data files on a NetApp filer CIFS share. The 2014 installer prevents installation if CIFS and Service SIDs are used. I tried to install with domain account on CIFS, and then to swap back to Service SIDs afterwards, but couldn't find a way to do it.
I granted the AD Computer account Full Control to the CIFS share, so it should work, but I just can't work it out at the moment.
SQL Server Admin 2014 :: Cannot Access Path When Attaching Database With Data Files On SMB Share
Oct 1, 2014I have a Windows Server 2012 R2 2 node cluster with SQL Server 2014 FCI installed. Data files are on a separate Windows Server 2012 R2 file server. Data files share has been permissioned to the SQL Server service and SQL Server Agent service accounts as Full Control. NTFS Permissions are Full Control.
When I try to attach a database
CREATE DATABASE AdventureWorksDW2012
ON (FILENAME = 'apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf')
FOR ATTACHI get this error:
Msg 5120, Level 16, State 101, Line 4
Unable to open the physical file "apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf". Operating system error 5: "5(Access is denied.)".
If I log into the file server (called APRICOT) and look at the NTFS permissions they all look good. I have also reapplied the NTFS permissions from the root folder down.
EDIT
If I log on to one of the nodes in the cluster as the SQL Server service account and navigate to apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATA and copy and paste the data file, it works fine.
EDIT2:
If I log on to the file server and Enable Inheritance at the root level, then Replace all child objects with inheritable permission entries from this object, I get this error:
User Account Control settings on all nodes and the file server are set to Never notify
Why SP Return SQLException When No Data Return ?
Jul 24, 2006hi, good day,
i have using BCP to output SP return data into txt file, however, when it return nothing , it give SQLException like "no rows affected" , i have try to find out the solution , which include put "SET NOCOUNT ON" command before select statement, but it doesn't help :(
anyone know how to handle the problem when SP return no data ?
thanks in advance