|
I have the following stored procedure (written in an "Execute SQL Task" in SSIS).
How do i get the value from @Error and set it to Global Variable in SSIS? The global variable name is "varError"
DECLARE @Error int
EXEC @Error = sp_delete_temp_people_import_tables
|
|
|
|
|
If you're using OLEDB
1) Set your string as
EXEC ? = sp_delete_temp_people_import_tables
2) In the Parameter mapping page, add a new parameter. Set the variable name to varError, Parameter Direction to "Output" and parameter name to "0"
If you're using ADO.NET
1) Just give your SP name in the SQLStatement box
2) Set IsQueryStoredProcedure to true
3) Add a new parameter. Set the variable name to varError, Parameter Direction to "Output" and parameter name to the name you've specified in the SP
Remember, if you're using ADO.NET, the SP must use an OUTPUT parameter and NOT a return value.
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Im using OLEDB
Ive configured the properties as you said
Variable Name = User::intError
Direction = Output
Data Type = Long_Integer (global variable is expecting an int32)
Parameter Name = 0
but whe i run i get the following
##############################################
--Execute Stored procedure
EXEC ? = sp_delete_temp_people_import_tables
" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task
################################################
When i try parsing it before i ran the package i get the same error as above - "Syntax error, permission violation, or other nonspecific error".
|
|
|
|
|
Is your SP returning a value through the RETURN statement or through an OUTPUT parameter?
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
This is my Stored Procedure
##############################
ALTER PROCEDURE [dbo].[sp_delete_temp_people_import_tables] AS
DECLARE @Error int
SELECT @Error = 0
SET XACT_ABORT ON
BEGIN TRANSACTION
DELETE FROM tempDataImport
SELECT @Error = @@ERROR
IF @Error <> 0 GOTO Error_Handler
COMMIT TRANSACTION
RETURN @Error
Error_Handler:
ROLLBACK TRANSACTION
RETURN @Error
###########################
|
|
|
|
|
I realy dont know what do try next. Ive done every thing and nothing see to work. 
|
|
|
|
|
|
Thanks SimulationofSai. That link really helped. I got it working
|
|
|
|
|
You're welcome. It's a pleasure. 
SG
Cause is effect concealed. Effect is cause revealed.
|
|
|
|
|
Hi Experts,
I had developed an Application in VB6 which has the database in MS SQL 2000. But that application I cant use to distribute frequently as I need to al teast install MSDE at the point. I just wanted to make an .exe with some encypted format files. which actually contain the data. Which in future if I needed can again convert into mssql. if support needed.
Is that possible, I have chosen ODBC but it supports .txt in open format which shows everything that is in database.
Thanks/ Jai
|
|
|
|
|
You can install SQL Server Express edition on the server if that is what you are asking.
As long as your application has the correct connection strings you can then directly query the MDF file/database.
Alternatively store the data as encrypted XML files (if VB6 supports XML).
Or encrypt the txt files and decrypt them on reading them into your application.
You always pass failure on the way to success.
|
|
|
|
|
Hi..
I'm Using the following Expression for Division in SSRS-2005 in C# Web Applications..
=Round(((Fields!Price.Value*100)/Sum(Fields!CPU.Value, "DataSet1_AmountDetails")),2) & "%"
When I'm Using this Expression i'm getting the Result as i'm expecting ..
ie.. 10.25%, 15.45%, 12.14%,.....
But the Problem is When i'm adding all the Values it is giving me 102.56%..
But I want to Show the Chart for only 100%..
If i'm not adding this Decimal points i'm getting the Total of 99%.. (ie 10,15,12,.......)
What can I do Now for getting the Chart to Show the Result of 100%..
Please give me any Suggestions..
Thank You..
|
|
|
|
|
Obviously, if you apply rounding to values and then sum the values the sum of those values may be more/less than 100% (depending on whether the values were generally rounded up or down). The solution is to only apply rounding on the presentation of the numbers by using a format expression. Your actual calculations should work with the original values, so summing the values won't result in rounding errors.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Then How Can I Solve my Problem ..
|
|
|
|
|
Aswanth wrote: Then How Can I Solve my Problem ..
Didn't you read my reply? I explained what you need to do. I'm not going to write the code for you - as a professional developer you should be able to do that.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hi all,
Iam in search of generating generic stored procedures(one procedure for a single operation or multiple for all the tables by passing dynamic queries)is it possible????
and want to know whether parameterized stored procedures are pre-compiled
or not?????
plz send ur views asap
thanks in advance
regards
mamatha
|
|
|
|
|
It's a really, really bad idea. SQL Server will generate a query plan for the first set of parameters, cache it, and reuse it for all future calls to that stored procedure regardless of how the parameters change.
Stored procedures work best when the shape of the query - the tables involved, how they're joined, the columns filtered by the WHERE clause, and the order of the output - remains stable. They don't work if you try to change the shape of the query.
SQL Server accepts parameterized SQL text - with @variables in the command text - and it caches the query plans for parameterized queries. It also tries to automatically deduce where parameters should be if no parameters were used - this is referred to as auto-parameterization. It still caches the query plan even if it can't deduce the parameters. However, it ranks the cached query plans so that a plan for a stored procedure is likely to be kept longer than a parameterized query, which is likely to be kept longer than an auto-parameterized query, which in turn is likely to be kept longer than a non-parameterized query.
Unless you really want to prevent users from SELECTing from the base tables - using the security barrier you can erect using stored procedures - I strongly recommend using parameterized, dynamically-constructed queries submitted as text.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
I want some help regarding the Northwind database that comes with MS-Access.
when we opens the database it shows the various forms, reports, tables, queries. I want to know how can i use this database as an application.
Is there a way to build an application of this DB?
Or any other way by which i can use the DB, by creating the application in programming language.
|
|
|
|
|
humdumof wrote: I want to know how can i use this database as an application.
Yes and No:
The No bit - a database is not an application it is just a collection of data.
The Yes bit - see below.
humdumof wrote: Is there a way to build an application of this DB?
Yes - you will need MS-Access or another application that can connect to an MDB database to do this.
humdumof wrote: Or any other way by which i can use the DB, by creating the application in programming language.
See just above - to quote a couple of programming languages you could use - C#, VB.Net... .
You always pass failure on the way to success.
|
|
|
|
|
thanks for replying.
GuyThiebaut wrote: Yes - you will need MS-Access or another application that can connect to an MDB database to do this.
I know i need to use access for using this. But when i open the file it opens up the whole MS-Access, It having various forms, and that also can be used after opening MS-access. Cant we open these forms directly like an application.
I think for this i have to use programming lang?
I am familier with C#.net.
But will need some answer,
a) do i have to create from scratch, or can save my efforts with some tools who can do this.
b)How to use the reports presents in DB, there is tool in Visual Studio crystal reports, can this help me.
Or any suggestion how I start/create my application
|
|
|
|
|
humdumof wrote: I know i need to use access for using this. But when i open the file it opens up the whole MS-Access, It having various forms, and that also can be used after opening MS-access. Cant we open these forms directly like an application.
In the Access database window: drag and drop the forms, reports etc onto the desktop.
You will then be able to open these forms directly without having to go through the switchboard etc.
In answer to a and b: I think it would be a good idea to pick up a manual on Access.
(Access has all the tools you need to write tables,queries,forms,reports within it)
You always pass failure on the way to success.
|
|
|
|
|
Is there any sql query that tells the ip address of the database server on which the query run.
Regards
Rajesh
Rajesh Kumar
modified on Saturday, March 8, 2008 12:54 AM
|
|
|
|
|
Hello!
This should help your cause:
exec master..xp_cmdshell 'ipconfig'
and don't forget to enable xp_cmdshell. Following is the way to enable it:
Goto: Start Menu => All Programs => Microsoft SQL Server 2005 => Configuration Tools => SQL Server Surface Area Configuration
Click Surface Area Configuration for Features
Click xp_cmdShell and enable it.
HTH, Regards,
Adeel
Do rate the reply, if it helps or even if it doesnot, because it helps the members to know, what solved the issue. Thanks.
|
|
|
|
|
Thanks...
But I do not have access to tht server. May be it is hosted somewhere in US.
How to proceed ?
|
|
|
|
|
IS THERE ANY PROVISION TO ASCERTAIN NEXT AND PREVIOUS RECORD IN SQL SERVER 2000 ?
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
