|
Have no idea what database program you are using so I will speak about MS SQL Server.
A stored procedure may touch many tables. One set of data may be put into many tables.
If Table1 and Table2 do not have a shared column (PersonID for example) it becomes very hard to join the two.
|
|
|
|
|
I solved the problem. Thank you a lot.
I used the nested BEGIN and END between two tables.
Cheers.
|
|
|
|
|
What nested BEGIN and END..? used in your Stored Procedure.
|
|
|
|
|
I want to insert the sql Error Message into the Error Table and return the user defined Error Message. Below is my code, if it sucess then Return Null else it should return Error message like 'Error on Inserting' at the same time it should save the actual error details in the Error table
Create PROCEDURE addTitle
AS
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
Create table #temp(ID int,fName varchar(20))
Insert into #temp(ID,fName)values('a1','test')
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
Insert into apl_Error(Error_Desc,Error_Date) select RTrim(LTrim(Cast(ERROR_STATE() as CHAR(10))))+','+ RTrim(LTrim(Cast(ERROR_LINE() as CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE(),GETDATE()
END CATCH
Return
|
|
|
|
|
You will need a "translation" table that gives the message you want based on the error.
|
|
|
|
|
Hi,
Try like this... If you need add TRANSACTION also in this Code.
CREATE PROCEDURE addTitle
@ErrorDtls VARCHAR(100) OUTPUT
BEGIN
BEGIN TRY
Create table #temp(ID int,fName varchar(20))
Insert into #temp(ID,fName)values('a1','test')
SELECT @ErrorDtls = NULL
END TRY
BEGIN CATCH
Insert into apl_Error(Error_Desc,Error_Date)
SELECT RTrim(LTrim(Cast(ERROR_STATE() as CHAR(10))))+','+ RTrim(LTrim(Cast(ERROR_LINE() as CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE(), GETDATE()
SELECT @ErrorDtls ='Error on Inserting'
END CATCH
END
DECLARE @ErrorDtls VARCHAR(100)
EXEC addTitle @ErrorDtls=@ErrorDtls OUTPUT
SELECT @ErrorDtls
GVPRabu
|
|
|
|
|
Hi
First create table
CREATE TABLE apl_Error
(
Error_Desc VARCHAR(MAX),
Error_Date DATE
)
After that create this procedure
CREATE PROC SAMPLE
@ERROR_DESC VARCHAR(MAX) OUTPUT
AS
BEGIN
BEGIN TRY
CREATE TABLE #TABLE(ID int,fName varchar(20))
INSERT INTO #TABLE VALUES('A1','CHOICE')
SELECT @ERROR_DESC=NULL
END TRY
BEGIN CATCH
INSERT INTO apl_Error(Error_Desc,Error_Date)
SELECT cast( RTRIM(LTRIM(CAST(ERROR_STATE() as CHAR(10)))) +','+ RTRIM(LTRIM(CAST(ERROR_LINE() AS CHAR(5))))+','+ERROR_PROCEDURE()+','+ERROR_MESSAGE() as varchar(max)),GETDATE()
SELECT @ERROR_DESC='Error on Inserting'
END CATCH
END
to execute this procedure
DECLARE @NAME VARCHAR(MAX)
EXEC SAMPLE @ERROR_DESC=@NAME OUTPUT
SELECT @NAME AS Error_Description
then see apl_Error table now you can see the message and date.
Error_Desc Error_Date
1,8,SAMPLE,Conversion failed when converting the varchar value 'A1' to data type int. 2013-07-05
|
|
|
|
|
TSQL is my biggest weakness here. I really don't enjoy writing it but you have to know it to get things done.
I'm looking for some help here, on making a better search system, and I'm not sure which direction to go. I guess at first, I should get the TSQL right, or better at least.
I have a FTS catalog with partNumber, Title, Description, VendorName for the productInfo table.
I wrote this 2 years ago, but it's pretty weak, and doesn't work well.
For instance, if I search part number "06-CR10", it produces nothing, but if I search "CR-10", it produces a result.
I have trouble with 2 words or phrases like "tile spacers" using CONTAIN, but it works fine using FREETEXT.
my question is:
Should I be detecting multiple words, and use different TSQL based on word counts? Or just better TSQL?
I'm just looking for suggestions.
Dim mySelectQuery As String = "SELECT " & _
"ProductID, Category, SubCategory, ShortDescription, LongDescription, Thumbnail, PartNumber, Price " & _
"FROM ProductInfo WHERE " & _
"FREETEXT(ShortDescription, @Query) " & _
"OR FREETEXT(LongDescription, @Query) " & _
"OR FREETEXT(ProductHTML, @Query) " & _
"OR FREETEXT(PartNumber, @Query) " & _
"ORDER BY RANK() OVER (Order BY PartNumber, ShortDescription, LongDescription) DESC"
|
|
|
|
|
Short answer: use both CONTAIN and FREETEXT.
Less short answer: The idea is to call a PROCEDURE which complements the use of the higher energy union of the two methods.
Until discovering INFORMATION_SCHEMA ("Views") I was adhereing, for many years, to using LIKE without even considering FULLTEXT search-enabled tables, if you can believe that. Besides the overhead requirement of LIKE and the stored procedure call (yeah, using "String" would be ok), I had only the sys.x catalog from which to draw the associations into a database relationship.
Best answer: hey, whatever works for you.
|
|
|
|
|
Sounded complex on the fist reading. But very informative.
I'll do some more reading on some of the words you used, to learn more about it.
But in the meantime, I experimented with FREETEXT, CONTAINS and LIKE an sort of solved my partNumber issue by using LIKE for the partNumber, so now the part number works.
Dim mySelectQuery As String = "SELECT COUNT(ProductID) FROM ProductInfo WHERE " & _
"FREETEXT(ShortDescription, @Query) " & _
"OR FREETEXT(LongDescription, @Query) " & _
"OR FREETEXT(ProductHTML, @Query) " & _
"OR PartNumber LIKE @Query2"
I will try searching each column with specific ways to obtain the result I'm looking for and keep refining it for now.
|
|
|
|
|
Also, use the BOL (Books on Line) for ssmse. Specifically look up INFORMATION_SCHEMA. Chock full of pre-associations the View approach to sys.objects is.
Ironically, it was the "SPECIFIC_CATALOG" of "View" (really I still don't quite comprehend what that word means ...) that happened concurrently with my first attempt to use FULLTEXT indexes, and quick succeed, that made me toss my entire search system at the time.
But yeah, use all three; an even better idea.
|
|
|
|
|
Thanks
I'll take a peek first, might be overwhelming to comprehend, but I should be able to absorb some of it.
|
|
|
|
|
I have richtextbox and i save content of it into sql database, now i want to encode string and represent as html encoded in rdlc report.
Any help?
|
|
|
|
|
hi experts,
my stored procedure is very big...i want to debug it..when i start debugging it , i am getting the following error
failed to start debugger
additional information:
error hresult e_fail has been returned from a call to a com component.(mscorlib)
i googled regarding that...i am not able to debug...
my db is remote sql server 2008 r2
coud anyone help me pls
|
|
|
|
|
Member 8701813 wrote: my stored procedure is very big.
That's a design-problem. Cut it into smaller pieces that are easier to debug/verify.
Member 8701813 wrote: i am not able to debug...
It means that the debugger doesn't work; I'd suggest running the sproc and see where it fails, and with what error. Past that part in a post here, and we'll have a look 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
hi,
you are telling that your sp is big instead of at one time u debugging can u cut that sp and check which part is throwing error in your Sp because i had also tried like that only when i faced this situation. I think it will help you.
Regards,
S Azarudhin
|
|
|
|
|
see my trick posted in code project, it may help you, however you will have to modify the existing code to call the logger on all the places where you feel the exception handling is weak or may need some values to trace the reason.
Logging error, debug, info messages into log tables
Sudhir Kankal
|
|
|
|
|
try to print messages over the screen it really helps you!!!!
Mani Prabhakar
|
|
|
|
|
The System.Data.OracleClient in .NET is now obsolete (deprecated).
So I installed the ODP.NET from Oracle (I have both 4.112.3.0 and 2.112.3. versions of Oracle.DataAccess.dll).
When running VS, I see both under the Add References (not really sure of what the difference between version).
I am using .NET Framework 4.0 so I included 4.112.3.0 version in the project.
Problem is that the Microsoft and .NET framework versions yield different results.
For instance the Oracle version won't permit || in the data set (aka SELECT A || B || C || 'x' as Dummy from ...) - have to use Concat(Concat (A, B), C), ... etc to make valid table adapter SQL statement in the ODP.NET version.
Additionally, it appears not to function identically so worried about potential errors being introduced in the datasets.
Is there any clear article identifying the differences between the two libraries (most articles say just to replace but that is the issue, they are not identical).
|
|
|
|
|
Dale Haessel wrote: I have both 4.112.3.0 and 2.112.3. versions of Oracle.DataAccess.dll
The first number in the Oracle ODP version indicates which framework version it's intended for.
Dale Haessel wrote: For instance the Oracle version won't permit || in the data set (aka SELECT A ||
B || C || 'x' as Dummy from ...) - have to use Concat(Concat (A, B), C), ... etc
to make valid table adapter SQL statement in the ODP.NET version.
I never had that problem, what error message do you get?
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Using VS dataset / table adapter design wizard, it gives:
The wizard detected the following problems when configuring the TableAdapter: "RCM_MRO_BOM": Details:
Generated SELECT Statement
Error in SELECT clause: expression near '|'.
Missing FROM CLause.
Error in SELECT clause: expression near ','.
Unable to parse query text.
...
Wizard Configuration Text =
SELECT BOM_REF_KEY, DESCRIPTION, BOM_REVIEWED, COMMENTS || ' ' as X, 1 as MatchType FROM SAPRCM.RCM_MRO_BOM a
|
|
|
|
|
Aha, the design wizard. That sucks big donkey balls if you excuse my expression, and not just when using with Oracle in my opinion.
Use Oracle SQL Developer[^] for creating your queries instead, or SQLTools[^] if you want something lightweight and high performance.
I personally also prefer DataReaders instead of TableAdapters, but the comparison and reasoning is to much for a forum post on my behalf, read Piebalds post[^] for some reasoning, otherwise I'd recommend Google.
Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln
|
|
|
|
|
Dear all,
I am new to the SSIS. I have many tables from old database (SQL 2000) and have to transfer to a new database (SQL 2012).
I have created one SSIS project and have successfully shipped the data of one table already. I have also added a 'Execute SQL Task' to clean out table and reset identity column before the 'Data Flow Task'.
Now what is the best way to import the rest? Import all tables in just one package? Or create one package for each table and create one package to 'call' all other package one by one?
I think it is possible to create one package and use parameter to go over a pre-defined set of tables.
|
|
|
|
|
Why SSIS, do you expect to have to repeat this process?
If it is a one off exercise then I would set up a linked server and drag the data across using scripts, saving the scripts of course.
Actually I would backup and restore onto the 2012 server if it will work, I'm not sure the restore would work over such a wide version range!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Well, I have already imported several tables. And the last one is a painful one. As for the last one, I still have to split that (old) one into five new tables.
I think I am going to try to use the linked server approach. Thanks.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
