SPROC Cannot Pass Text Or Ntext

Apr 2, 2004

Hi there! I've read through several of these forums and am impressed by the talent out there. I'm hoping someone could share some insight on a little complication I've got.

I have a sproc that essentially is pulling all mail items from a profile, reading them in and storing them in a table. It is all working fine MINUS the fact that I cannot pass the message because it won't allow me to pass text or ntext.

If anyone has any ideas I would truly appreciate it. In the meantime I'll be searching through the forum looking for a way to make it happen.
Thx, Camey

Here's my code:

CREATE PROCEDURE USP_News_GETmail
AS

declare @status int
declare @msg_id varchar(64)
declare @originator varchar(255)
declare @cc_list varchar(255)
declare @msgsubject varchar(255)
declare @msgtext varchar(255)
declare @messages int
declare @resultmsg varchar(80)
declare @filename varchar(12)
declare @current_msg varchar(64)

declare @post_id int
declare@topic_id int
declare@forum_id int
declare@poster_id int
declare@post_time int
declare@poster_ip int
declare@post_username char(25)
declare@enable_bbcode smallint
declare@enable_html smallint
declare@enable_smilies smallint
declare@enable_sig smallint
declare@post_edit_time int
declare@post_edit_count smallint
declare@bbcode_uid char(10)
declare@post_subject char(60)
declare@post_text text

Set @msg_id = NULL
while (1=1)
begin
exec master..xp_findnextmsg @msg_id = @msg_id output
if @msg_id is null break

exec @status = master.dbo.xp_readmail
@msg_id,
@originator=@originator output,
@cc_list=@cc_list output,
@subject=@msgsubject output,
@message=@msgtext output

select @topic_id= 2000,
@forum_id = 1,
@poster_id= 3,
@post_time= Convert(int,getdate()),
@poster_ip= 1000196,
@post_username= 'yahoo-alibre',
@enable_bbcode= 1,
@enable_html= 0,
@enable_smilies= 0,
@enable_sig= 0,
@post_edit_time= NULL,
@post_edit_count= 0,
@bbcode_uid= NULL,
@post_subject= @msgsubject,
@post_text= @msgtext




SET IDENTITY_INSERT phpbb_test_posts ON

DECLARE @num int
SELECT @num=max(post_id)
FROM phpbb_test_posts
SELECT @num = @num + 1

INSERT INTO phpbb_test_posts (post_id,topic_id,forum_id,poster_id,post_time,pos ter_ip,post_username,enable_bbcode,enable_html,ena ble_smilies,enable_sig,post_edit_time,post_edit_co unt)
VALUES (@num,@topic_id,@forum_id,@poster_id,@post_time,@p oster_ip,@post_username,@enable_bbcode,@enable_htm l,@enable_smilies,@enable_sig,@post_edit_time,@pos t_edit_count)


INSERT INTO phpbb_test_posts_text (post_id,bbcode_uid,post_subject,post_text)
VALUES (@num,@bbcode_uid,@post_subject,@post_text)

exec master..xp_deletemail @msg_id


end
GO

View 4 Replies


ADVERTISEMENT

Pass A Collection To A SPROC

Feb 12, 2005

I am collecting companyID's form a data grid, I want to pass the selected values to a sproc via a variable. Any idea on the syntax?

this works using a query string within my code

WHERE (dbo.Promotions.ExpirationDate > GETDATE()) AND (dbo.Promotions.CompanyID IN (" + selectedCompanies + "))

this doesn't within my sproc

WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (@SelectedCompanies))

I also tried

WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (SELECT @SelectedCompanies))

and

WHERE (dbo.Promotions_ByLink.ExpirationDate > GETDATE()) AND (dbo.Promotions_ByLink.CompanyID IN (' + @SelectedCompanies + '))

Thanks

View 4 Replies View Related

Pass Concatenated String To SPROC

Jul 24, 2004

Hello,

We are creating an app to search through products. On the presentation layer, we allow a user to 'select' categories (up to 10 check boxes). When we get the selected check boxes, we create a concatenated string with the values.

My question is: when I pass the concatenated string to the SPROC, how would I write a select statement that would search through the category field, and find the values in the concatenated string?

Will I have to create Dynamic SQL to do this?...or... can I do something like this...




@ConcatenatedString --eg. 1,2,3,4,5,6,7

SELECT col1, col2, col3 FROM TABLE WHERE CategoryId LIKE @ConcatenatedString



Thanks for your help.

View 2 Replies View Related

Pass Current Date To SProc

Jan 23, 2004

All,

I have a stored procedure that uses two parameters (@StartDate, @EndDate) and I want to pass the current date (no time) and when I try:

EXEC sp_AMR_AllComplSummary convert(varchar(12),getdate(),101), convert(varchar(12),getdate(),101)

I get Incorrect syntax near the keyword 'convert'.

AND Trying:
EXEC sp_AMR_AllComplSummary [convert(varchar(12),getdate(),101)], [convert(varchar(12),getdate(),101)}]

I get Error converting data type nvarchar to datetime.

This should be simple and I am lost.
Thanks in advanced.

View 4 Replies View Related

Managing Ntext, Text With A Long Text Data

Aug 25, 2005

Hi,I have a problem to insert(update) a long text (more than 64K) intoSQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.MSDN says: "When the ntext, text, and image data values get larger,however, they must be handled on a block-by-block basis. BothTransact-SQL and the database APIs contain functions that allow applications towork with ntext, text, and image data block by block." Could somebodygive me an example how to do this, please.Thank you

View 2 Replies View Related

Ntext And Text In MSSQL 7.0

Mar 25, 1999

Hello everybody,

When I attempt to return a ntext or text field from a recordset to a client I receive the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80020009'

Why won't ASP permit me to render this field.

Sincerely,

Arthur Lambiris

View 1 Replies View Related

Text (or Ntext) Concatenation

Apr 4, 2004

i want to concatenate 2 columns of type text

what am trying to do is adding an instead of trigger on update which concatenates the old value in the text column with the new value

so the problem is how i can concatenate both values since i cant declare a variable of type text.

A solution may be dividing the text column into many varchar(8000) variables and then add them but i dont know how it can be implemented or if there's an easier solution

the code am trying:

CREATE TRIGGER InsteadTrigger on tbl
INSTEAD OF Update
AS
BEGIN

if exists(select null from deleted)
begin
--am considering updating only 1 row
update tbl set theValue=((select theValue from deleted)+(select theValue from inserted))
end



i tried the writetext and updatetext functions and the problem is always related to both columns concatenation


Any solution?

thx
samham

View 3 Replies View Related

Text And Ntext Fields

Jul 23, 2005

I've just gotten my data into SQL Server, and changed the connection stringon the program.There was a memo field (Access 2000) and we put that in a text field (with adefault length of 16?).Now, when displaying the data online (through asp pages), there are bigwhite-space gaps in the pages (that display the memo field data), imageshave a "double" url(http://www.mysite.com/thisfolder/ht...thelinksite.com)there are lots of place with " ".The field data has html tags in it, if that has anything to do with it.It seems like I've heard that there are issues with text fields. Is thiswhat they meant? Is there anything I can do?Thanks, JA

View 4 Replies View Related

Converting Text Dat To Ntext Data

Mar 26, 2006

Hi all,

My organization have a web-based application and needs it to support multilingual so we will be adapting our app to use unicode. However, one of our problems is to convert existing data from text to ntext. I couldn't find anything that document this. What is the best way to do that? I would like to be able to migrate the data from an existing text column to another ntext column in the table.

If I can't do that, what are the options? If it's possible, I would like to be able to do this in sql script.


Thanks a mil, in advance.

Eddie

View 2 Replies View Related

Ntext && Text Storage - How Do These Work?

Nov 28, 2004

Howdy,


Can some one please explain how the storage of a huge text or ntext object ( say a string of characters 100 KB in size ) is carried out please?

With a data page maximum size of 8000 bytes, how does SQL handle the storage of such an string - it would obviously obverlap multiple rows etc.
Does the application writing it to the database have to split the object over multiple rows and manually keep track of which bit is in which row so it can be re-created later in correct sequence by adding the bits back together, or is it done some how differently?

Any help most welcome as this is a bit confusing.

Cheers,

SG.

View 2 Replies View Related

Field Too Small; Cannot Use Text Or NText; Ugh, Little Help.

Sep 9, 2006

My Stored Proc runs through a loop and concats the contents of each field into one big nvarchar. Procedure works fine on a smaller scale but now it is being implemented on a very large table and the results of the sequel overflow the nvarchar limits. I looked into using text and ntext but both cannot be declared locally. Does anyone know how I can work aroudn this limitation?

Summary:The problem is that the temporary variable I am using (nvarchar) is too small to contain the robust size that the SQL is concating into it. The final field it winds up in is a text field and will be able to handle the amount of data, its just getting the data there is the issue..... Your thoughts please....

View 7 Replies View Related

NText Column Text Encoding/decoding

Sep 19, 2007

Hi,

I have a table column type as nText, however there are some Chinese character stored in that field and it is a messed up as it is not readable.

In my vb.net code, I did Convert to unicode by getting the byte of each character and encode it with UTF8 e.g:


Public Shared Function ConvertToUnicode(ByVal s As String) As String


' Convert To Unicode

Dim MyBytes As Byte() = Encoding.Default.GetBytes(s)

Dim GBencoding As Encoding = System.Text.UTF8Encoding.UTF8

Return GBencoding.GetString(MyBytes)

End Function


This works well but ,the problem is that it slows down the process quite alot, and I wonder are there any text encoding method I can use in SQL that can run when i do the SELECT Statement?


SELECT
Convert(MyNTEXTColumn)
....
something like that?

Thanks.
Jon

View 4 Replies View Related

Using Text Data Type In Dynamic Sql Sproc

Jul 20, 2005

hi allthis is my first post to this group, so pls bear with me while i tryto make some sense.i am trying to create a sproc which uses dynamic sql to target aparticuar table eg. '[dbo].[' + @tableID + '_articles']' and performsome actions.i am using @tableID [int] as a passes parameter for the sproc.everything seems to work fine until i try and manipulate a parameterwhich is of text data type.the error occurs when i try to build the dynamic sql string and appendthe text type variable.eg.CREATE PROCEDURE [procArticlesInsert](@siteID [int],@strShortTitle [varchar](40),@strLongTitle [varchar](60),@strShortContent [text],@strLongContent [text],@intSectionID [int],@intTemplateID [int],@intStatusID [int])ASDECLARE @strSQL varchar (1000)DECLARE @strSiteID varchar (10)SET @strSiteID = CAST(@siteID AS varchar)SET @strSQL = ('INSERT INTO [' + @strSiteID + '_articles] ' +'( [dateEntered], ' +'[shortTitle], ' +'[longTitle], ' +'[shortContent], ' +'[longContent], ' +'[sectionID], ' +'[templateID], ' +'[statusID]) ' +'VALUES ' +'(' + CAST(GETDATE() AS VARCHAR) + ', ' +'''' + @strShortTitle + ''', ' +'''' + @strLongTitle + ''', ' +''''@strShortContent , ' +'@strLongContent , ' +CAST(@intSectionID AS VARCHAR) + ', ' +CAST(@intTemplateID AS VARCHAR) + ', ' +CAST(@intStatusID AS VARCHAR) + ')')GOi could cast the text fields (@strShortContent , @strLongContent) tovarchar, but the restriction of 8000 characters will not go down sonicely.if anyone has any ideas or alternatives to what i am trying toachieve, i would love to hear from you.thanksadrian.

View 5 Replies View Related

How To Handle Text, Ntext Data Types Of SQL Server

Jan 8, 2004

i have created asp.net page, one feild of this page text area.
when i insert some text through this page in "text feild" of SQL server on few words of this feild cut and inserted to text feild of SQL server but all text that i have written in text area feild.

can u please help me how to handle so that i can all text in text feild of SQL server data type text/next.

View 2 Replies View Related

Text, Ntext, Or Image Node Does Not Exist Error

Apr 17, 2006

Hai Friends...

I am using sql server 2000. i have one table with columns

empid notnull
empfname not null
empmname null
emplname not null

while i am writing a simple procedure to get the employee middel name(empmname) it is giving the error message like this


Server: Msg 7105, Level 22, State 6, Procedure emp_statistics, Line 11
Page (1:70), slot 18 for text, ntext, or image node does not exist.

Connection Broken



plase help me Urgent

Thanks and Regards

Krishna Mangamuri

View 8 Replies View Related

Replicate Ntext Text Image In Transaction Replication

May 16, 2007

Hi!

i have a problem with my replication.

I have two SQL 2005 SP2 Server and want to use the transaction replication with updateable subscriber. Now the problem is that i can do any changes on the master server. But if i want to change a record on the subscriber which contains a ntext, text, oder image column - then i geht the error that the field will be NULL on the master.



Is there any solution to fix this problem? I dont wan`t to change the datatype vom ntext to varchar(max) !!



greetings

Holger

View 3 Replies View Related

Manipulating Text,nText Data Types Filed In Tsql

Jul 12, 2006

I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app.  Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this..
EXEC sp_executesql @Statement,N'@param1 varchar(3),@param2 varchar(1)',@param1,@param2,GO
As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement".
DECLARE @Statement varbinary(16)SELECT @Statement = TEXTPTR(Statement)FROM table1 READTEXT table1.statement @Statement 0 16566
So far so good, the issue is how to convert @Statment varbinary to nText to get it passed in sp_executesql.
Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called.
I would appreciate if any body respond to this.
 
 
 

View 2 Replies View Related

SQL ERROR - I Need DISTINCT But Can Use It With Image,ntext, Text - How To Work Aroud???

Oct 10, 2006

SQL ERROR - I need DISTINCT but can use it with image,ntext, text - How To work around???  ! more - How to Work around - MIN() I cant use it when having text in SELECT statement  The text, ntext, or image data type cannot be selected as DISTINCT.



Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: The text, ntext, or image data type cannot be selected as DISTINCT.

Source Error:





An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of
the exception can be identified using the exception stack trace below. 

View 2 Replies View Related

Converting CHAR/VARCHAR/TEXT Into NCHAR/NVARCHAR/NTEXT!!!

Jan 28, 2005

Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.

After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.

PLEASE HELP ME!!!

Thanks,

Firoz Ansari

View 2 Replies View Related

Using Inserted / Deleted Tables With Text / NText / Image Data Type

Oct 6, 2004

Hi folks,

Table:

a int,
b int,
c int,
d text

I need to change my AFTER - Trigger from this (example!):

select * into #ins from inserted

to something like

select *(without Text / nText / image -columns) into #ins from inserted.

So I tried to build a string like this: (using INFORMATIONSCHEMES)

select @sql = 'select a,b,c into #ins from inserted'
exec(@sql)

a,b,c are not of Text, nText or Image datatype.

After executing the trigger, I get an error, that inserted is unknown.

Does anyone know how to solve this ?

Thx.

View 5 Replies View Related

Page (1:404399), Slot 5 For Text, Ntext, Or Image Node Does Not Exist.

Apr 7, 2008

I wonder if anyone can help.

I do not understand the error message but I do know it is associated with a specific table and one of its indexes. If I try to drop the index that is the error message I get.

I have copied the data from the table to a new table and rebuilt the indexes on the new table and everything is working fine.

The bad table is now renamed to myTable_BAD. I now want to delete it but can't as everytime I enter
drop table myTable_BAD

I get Page (1:404399), slot 5 for text, ntext, or image node does not exist.

So here is my question

How can I delete this Bad table?

View 9 Replies View Related

SQL Server 2005 - Transactional Replication Involving Depricated Text And Ntext Data Types

Dec 2, 2005

I'm currently trying out transactional replication with updatable subscriptions across two 2005 servers.

View 3 Replies View Related

Function That Replaces Ntext And Compares Ntext With Nvarchar

Nov 28, 2007

I am running this query to an sql server 2000 database from my aspcode:"select * from MyTable whereMySqlServerRemoveStressFunction(MyNtextColumn) = '" &MyAdoRemoveStressFunction(MyString) & "'"The problem is that the replace function doesn't work with the ntextdatatype (so as to replace the stresses with an empty string). I hadto implement the MySqlServerRemoveStressFunction, i.e. a function thattakes a column name as a parameter and returns the text contained inthis column having replaced some letters of the text (the letters withstress). Unfortunately, I could not do that because user-definedfunctions cannot return a value of ntext.So I have the following idea:"select * from MyTable whereCheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &"')"How can I implement the CheckIfTheyAreEqualIngoringTheStessesfunction? (I don't know how to combine these functions to do what Iwant: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)

View 2 Replies View Related

Can't Pass Search Text Into Stored Procedure

Jun 7, 2007

I am trying to inject dynamically generated text into a Sql2000 stored procedure.  What am I doing wrong?A code behind routine generates the following string value based on a visitor entering 'sail boats' in TextBox1.  The routine splits the entry and creates the below string.Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%'
I am trying to place this string result in the WHERE statement of a Sql2000 Stored Procedure using parameter @VisitorKeywords. 
PROCEDURE dbo.KWsearchAS SELECT DISTINCT Companies.L_Name, Companies.L_ID, Companies.L_EnabledWHERE ( @visitorKeywords ) AND (Companies.L_Enabled = 1)ORDER BY Companies.L_Name
I am wanting the resulting WHERE portion to be:
 WHERE ( Companies.L_Keywords LIKE '%sail%' AND Companies.L_Keywords LIKE '%boats%' ) AND (Companies.L_Enabled = 1)
 Thank you
 

View 10 Replies View Related

How Do I Pass A Search Text Parameter To FreeTextTable?

Sep 13, 2007

I have an issue trying to pass a search text parameter to FREETEXTTABLE via Dataset.
The following code works fine if you hardcode the search word/text as shown:
SELECT KEY_TBL.RANK, FT_TBL.FaqQuestion, FT_TBL.FaqAnswer, FT_TBL.SearchFROM  faq_table AS FT_TBL INNER JOIN          FREETEXTTABLE(faq_table, Search, 'cool') AS KEY_TBL ON FT_TBL.FaqID = KEY_TBL.[KEY]ORDER BY KEY_TBL.RANK DESC
Now, I want to do this:
SELECT KEY_TBL.RANK, FT_TBL.FaqQuestion, FT_TBL.FaqAnswer, FT_TBL.SearchFROM  faq_table AS FT_TBL INNER JOIN          FREETEXTTABLE(faq_table, Search, @Search) AS KEY_TBL ON FT_TBL.FaqID = KEY_TBL.[KEY]ORDER BY KEY_TBL.RANK DESC
The error I'm getting is @Search is not declared.  How am I suppose to pass in a value?
I have searched almost everywhere and nobody seemed to ask this precise question.  I'm sure this is a huge problem.
Can anyone help me please?

View 3 Replies View Related

How To Pass Text Parameter To Stored Procedure?

May 16, 2008

Hello,
I was wondering if you could help resolve a simple question - namely how to input a type text value as a parameter to a stored procedure, which expects that type of input.

Text type variables are not allowed and casting to varchar in this case will not work as the input will be far longer than 8000 characters.

Thanks!

View 3 Replies View Related

How To Pass The Value Of A Text Box Inside A FORMVIEW To A SQL Stored Procedure

May 28, 2006

Hi,
I'm new to ASP.NET 2.0. I have a sqldatasource and a formview controls on a web page and inside the formview control I have  two textboxes. When I click the UPDATE button in the formview, I'd like to be able to retrieve the values of the textboxes inside the formview control and pass these values to a 'Update' SQL stored procedure defined in the Sqldatasource.
Does anyone know how I can do this? Hope my question is clear.
Thanks in advance.
hakl

View 1 Replies View Related

Converting Text Data To Ntext Data

Mar 27, 2006

Hi all,

My organization have a web-based application and needs it to support multilingual so we will be adapting our app to use unicode. However, one of our problems is to convert existing data from text to ntext. I couldn't find anything that document this. What is the best way to do that? I would like to be able to migrate the data from an existing text column to another ntext column in the table.


I brief you about my system, I used List manager system to store the messages and distribute to all members. Right now,by design the Lyris system keep the message in the text field which mean it 's not support multilanguage directly because of unicode field. We needs to create new Db which has the data structure as same as Lyris but just one difference is keep the message in unicode format (ntext) which we need the sql script to automatically update the new record get from Lyris to new DB.


If I can't do that, what are the options? If it's possible, I would like to be able to do this in sql script.


Thanks a mil, in advance.

Eddie

View 2 Replies View Related

SQLDataSource Cntrl - FormView Cntrl - UPD Sproc And Sproc Debugger. I Dare Anyone To Figure This One Out.

Feb 13, 2007

I have attached the results of checking an Update sproc in the Sql database, within VSS, for a misbehaving SqlDataSource control in an asp.net web application, that keeps telling me that I have too many aurguments in my sproc compared to what's defined for parameters in my SQLdatasource control.....
No rows affected.
(0 row(s) returned)
No rows affected.
(0 row(s) returned)
Running [dbo].[sp_UPD_MESample_ACT_Formdata]
( @ME_Rev_Nbr = 570858
, @A1 = No
, @A2 = No
, @A5 = NA
, @A6 = NA
, @A7 = NA
, @SectionA_Comments = none
, @B1 = No
, @B2 = Yes
, @B3 = NA
, @B4 = NA
, @B5 = Yes
, @B6 = No
, @B7 = Yes
, @SectionB_Comments = none
, @EI_1 = N/A
, @EI_2 = N/A
, @UI_1 = N/A
, @UI_2 = N/A
, @HH_1 = N/A
, @HH_2 = N/A
, @SHEL_1 = 363-030
, @SHEL_2 = N/A
, @SUA_1 = N/A, @SUA_2 = N/A
, @Cert_Period = 10/1/06 - 12/31/06
, @CR_Rev_Completed = Y ).
 
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[sp_UPD_MESample_ACT_Formdata].
The program 'SQL Debugger: T-SQL' has exited with code 0 (0x0).
And yet every time I try to update the record in the formview online... I get
Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Procedure or function sp_UPD_MESample_ACT_Formdata has too many arguments specified.Source Error:




An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I have gone through the page code with a fine tooth comb as well as the sproc itself. I have tried everything I can think of, including creating a new page and resetting the fields, in case something got broken that I can't see.
Does anyone have any tips or tricks or info that might help me?
 
Thanks,
SMA49

View 3 Replies View Related

EXEC Of A Sproc Within Another Sproc

Apr 23, 2004

I'm sorta new with using stored procedures and I'm at a loss of how to achieve my desired result.

What I am trying to do is retrieve a value from a table before it is updated and then use this original value to update another table. If I execute the first called sproc in query analyzer it does return the value I'm looking for, but I'm not really sure how to capture the returned value. Also, is there a more direct way to do this?

Thanks,
Peggy



Sproc that is called from ASP.NET:

ALTER PROCEDURE BP_UpdateLedgerEntry
(
@EntryLogID int,
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2)
)
AS
DECLARE@OldCategoryID int

EXEC @OldCategoryID = BP_GetLedgerCategory @EntryLogID

UPDATE
BP_EntryLog
SET
ProjectID = @ProjectID,
CategoryID = @NewCategoryID,
Expended = @Expended

WHERE
EntryLogID = @EntryLogID

EXEC BP_UpdateCategories @ProjectID, @NewCategoryID, @Expended, @OldCategoryID



Called Sprocs:

*********************************************
BP_GetLedgerCategory
*********************************************
ALTER PROCEDURE BP_GetLedgerCategory
(
@EntryLogID int
)
AS

SELECT CategoryID
FROM BP_EntryLog
WHERE EntryLogID = @EntryLogID

RETURN


*********************************************
BP_UpdateCategories
*********************************************
ALTER PROCEDURE BP_UpdateCategories
(
@ProjectID int,
@NewCategoryID int,
@Expended decimal(10,2),
@OldCategoryID int
)
AS

UPDATE
BP_Categories
SET CatExpended = CatExpended + @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @NewCategoryID


UPDATE
BP_Categories
SET CatExpended = CatExpended - @Expended
WHERE
ProjectID = @ProjectID
AND
CategoryID = @OldCategoryID

View 2 Replies View Related

Exec Sproc In Sproc

Jan 20, 2004

create procedure dbo.GetZipID( @City varchar(30), @State char(2), @Zip5 char(6))
as
DECLARE @CityID integer
declare @StateID integer
declare @ZipID integer
set @ZipID=2
set @Zip5=lTrim(@Zip5)
if @Zip5<>''
SET @ZIPID = (select Min(lngZipCodeID) AS ZipID from ZipCodes where strZipCode=@Zip5)
if @ZipID is null
set @CityID= EXEC GetCityID(@City);
set @StateID= EXEC GetStateID(@State);
insert into ZipCodes(strZipCode,lngStateID,lngCityID) values(@Zip5,@StateID,@CityID)
if @@ERROR = 0
SET @ZIPID = @@Identity
select @ZIPID


GetCityID and GetStateID are two stored procs, how do I execute those two stored procs
in the above stored proc? I mean what is the syntax??

Tks

View 2 Replies View Related

How To Pass Pass The Parameter In SQL Command In SSIS Package

Jul 31, 2006

Hi

   We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?

 

Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )



 

Query 2: (look up )

 


SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')  AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')



 

please anyone give the sample control flow  and how to pass the parameter?

 

Thanks & regards

Jeyakumar.M

 

View 1 Replies View Related

Using NTEXT

Dec 22, 2003

I wonder if I should do something to the data I store on ntext fields

so far it looks like it has no problem holding special chracters (as it should B for unicode)
anyway - is there some combinations I should avoid ?

Should I use something like on old ASP Server.URLEncode() or Server.HTMLEncode() ?

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved