After Copying A Record With An SP All Textfields Has Max Lenght

Jul 20, 2005

Dear All,

After copying a record using an Stored procedure all textfields (nvarchar)
has max lenght !


See VBA-code and SP below

VBA-code tos execute SP

Dim objcommand As ADODB.Command
Dim intReturnParam As Long
Set objcommand = New ADODB.Command
With objcommand
.CommandType = adCmdStoredProc
.CommandText = "FB_CopyOrder"
.Parameters.Append .CreateParameter("return_value", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("ORD_ID", adInteger,
adParamInput, , Me.ORD_ID)
.Parameters.Append .CreateParameter("ORD_P_ID", adInteger,
adParamInput, , Me.ORD_P_ID)
.Parameters.Append .CreateParameter("ORD_PHTI_ID", adInteger,
adParamInput, , Me.ORD_PHTI_ID)
.Parameters.Append .CreateParameter("ORD_NAME", adWChar,
adParamInput, 50, Me.ORD_NAME)
.Parameters.Append .CreateParameter("ORD_CLIENT_CODE", adWChar,
adParamInput, 50, Me.ORD_CLIENT_CODE)
.Parameters.Append .CreateParameter("ORD_INTERNAL_NOTE", adWChar,
adParamInput, 1024, Me.ORD_INTERNAL_NOTE)
.Parameters.Append .CreateParameter("ORD_REQUESTED_DELIVERY_DATE",
adDate, adParamInput, , Me.ORD_REQUESTED_DELIVERY_DATE)
.Parameters.Append .CreateParameter("ORD_REQUESTED_QUANTITY",
adInteger, adParamInput, , Me.ORD_REQUESTED_QUANTITY)
.Parameters.Append .CreateParameter("ORD_AVAILABLE_QUANTITY",
adInteger, adParamInput, , Me.ORD_AVAILABLE_QUANTITY)
.ActiveConnection = CurrentProject.Connection
.Execute
intReturnParam = .Parameters(0).Value
End With

Stored procedure

Alter Procedure FB_CopyOrder

--List of parameters to be added to the parametercollection of the
ADO-commandobject before executing the command

@SourceOrderID int,

@ORD_P_ID int,

@ORD_PHTI_ID int,

@ORD_NAME nvarchar(50),

@ORD_CLIENT_CODE nvarchar(50),

@ORD_INTERNAL_NOTE nvarchar(1024),

@ORD_REQUESTED_DELIVERY_DATE datetime,

@ORD_REQUESTED_QUANTITY int,

@ORD_AVAILABLE_QUANTITY int

as

declare @err int

declare @NewOrderid int

begin tran

-- add new order values = command-parameters

insert into [ORDER] (ORD_P_ID, ORD_PHTI_ID, ORD_NAME, ORD_CLIENT_CODE,
ORD_CREATION_DATE, ORD_INTERNAL_NOTE, ORD_REQUESTED_DELIVERY_DATE,
ORD_REQUESTED_QUANTITY, ORD_AVAILABLE_QUANTITY)

values (@ORD_P_ID, @ORD_PHTI_ID, @ORD_NAME, @ORD_CLIENT_CODE,
convert(varchar,getdate(),101), @ORD_INTERNAL_NOTE,
convert(varchar,@ORD_REQUESTED_DELIVERY_DATE,101), @ORD_REQUESTED_QUANTITY,
@ORD_AVAILABLE_QUANTITY)

set @err = @@Error

select @NewOrderID =SCOPE_IDENTITY()

etc...........................................

View 1 Replies


ADVERTISEMENT

How To Set Record Lenght For The Flatfiles In SSIS

Aug 27, 2007

I am trying to transfer the data from OLEDB source to Flatfile destination. My Client need the flatfile of Record Length 80 Bytes. What does this means? Does it mean the output width or does it mean the flatfile format should be fixed length? please explain?

View 11 Replies View Related

Lenght Cannot Be Less Than Zero. Parameter Name : Lenght

Jul 17, 2007

I'm using Visual Studio 2005, and when i try to drop a table from a data source( SQL Mobile database), to a new or existing form, always occors the error ""lenght cannot be less than zero. Parameter name : lenght"" , my project has more than one forms.
If i make the same steps on a new project with only one form, i can drop the same table without problems.
Anyone can help with this problem?


best regards,

Pedro Nogueira

View 3 Replies View Related

Copying A Record Within The Same Table...

May 3, 2007

Hi. I would like to copy some records within the same table. what happens is we get very similar new cases in our office and we want to apply the same information from one client to the new client.

i looked at:
INSERT INTO ClientSpecs (ClientID, Spec1, Spec2, etc.)
SELECT Spec1, Spec2 from ClientSpecs WHERE ClientID=1234
but i want to use the new client id in place of the old client id. is there a way to do this? does this make sense? we're basically copying one client's file to a new client's file, except we need to use the new client's id.

View 3 Replies View Related

Copying A Record From One Table To Another

Jul 20, 2005

What is the easiest way to copy a record from one table to another (withidentical structure)?Regards

View 1 Replies View Related

Lenght

Oct 8, 2007

hi all...

mycount = (cast(d.sss_er as varchar)).len

sss_er is a datatype money

error : Cannot call methods on varchar.

thanks

RON
________________________________________________________________________________________________
"I won't last a day without SQL"

View 3 Replies View Related

Lenght Fo A Column Value

Sep 5, 2001

I need to find the lenght of data like

column name data is 'SELVAM'

I NEED LENGTH OF 'SELVAM' THAT IS 6 IS REQUIRED

View 2 Replies View Related

The Lenght Of The Result Get's Cut Off

Feb 7, 2008

Greetigns!

I've a simple question that I could not find an answere for.

When I do a select in sql server 2000 and 2005 the result from the table I am interrested in get's cut off in lenght so I do not get it all. It also gets cut off when I select the result to a file. The lenght of a row is 8000 and it gets cut of after maybe 100 or so.. but I need the enitre row.

Now I belive someone who is not here today made some configuration with exception on a few tables to speed things up.

I wonder, where I I add a table to such an exception list? (I was not here when they did it and not even sure if that's how they did it)

How do I make a configuration in my sql serve management studio 2005 to not have the result cut off?

Thanks a lot for help! :-)

View 2 Replies View Related

Maximum SQL Query Lenght

Mar 6, 2006

Hi all!

Does anybody know what is subject?

Thanks!

View 1 Replies View Related

Linited Lenght For Parameters In Stored Procedures

Apr 14, 2005

I use EXEC statement to execute query in NVARCHAR format.
Query is constructed using WHILE loop. Lenght of parameter @query in limited on 4000 ch. But my parameter is longer.
Data types ntext and text can't be used for parameters.
Any idea?

View 1 Replies View Related

Changing Field Lenght Via Query Analyzer

Jul 17, 2004

Hi
My DB is on the web so but I want change Field Lenght of one of my Fileds on my DB

its 70 rights now I want change it to 120 its a nvarchar


how can I do it , I am using a ASP page


Thanks

View 1 Replies View Related

OLEDB Data Source Limit Lenght Of String To 4000

Feb 5, 2008



Hello,
I have 2 table, "table1" is the source one and the other one "table2" is the destination.
Columns in Table1 and in Table2 are nvarchar(max).
Data loaded from table1 is performed by SSIS OLEDB data flow source, I have found out that opening "Data flow Path Editor" in the Metadata, columns are as: DT_WSTR with lenght 4000.

First question:
Why SSIS limit the column to 4000.

Then I get some error for this issue, with the error:
input column "col1" (xxxx) and reference column named (coln) have incompatible data type.

As written before both columns are string, the problem is that SSIS limit the lenght of the string to 4000.

How can I solve this issue?

Thank

View 4 Replies View Related

TOUGH INSERT: Copy Sale Record/Line Items For Duplicate Record

Jul 20, 2005

I have a client who needs to copy an existing sale. The problem isthe Sale is made up of three tables: Sale, SaleEquipment, SaleParts.Each sale can have multiple pieces of equipment with correspondingparts, or parts without equipment. My problem in copying is when I goto copy the parts, how do I get the NEW sale equipment ids updatedcorrectly on their corresponding parts?I can provide more information if necessary.Thank you!!Maria

View 6 Replies View Related

How To Create An Copy Of A Certain Record Except One Specific Column That Must Be Different && Insert The New Record In The Table

Sep 1, 2006

Hi
I have a table with a user column and other columns. User column id the primary key.

I want to create a copy of the record where the user="user1" and insert that copy in the same table in a new created record. But I want the new record to have a value of "user2" in the user column instead of "user1" since it's a primary key

Thanks.

View 6 Replies View Related

Ways To Make This Work: Several Selectable Related Record For One Main Record.

Apr 6, 2007

Hey all!



Sorry for the less then descriptive post title but I didn't find a better way to describe it. I'm developing an app in the express editions of VB and SQLserver. The application is a task/resource scheduler. The main form will have a datepicker or weekly overview and show all tasks scheduled per day. The problem is, I've got one or more people assigned to tasks and I wonder what's the best way to design this. Personally, I'd go for one Task table, a People table and a table that provides a link between them (several record per task, one for each person assigned linking TaskID and PplID). However, I don't see a nice way of showing this data to the end user, allowing him to edit/add etc on ONE screen.

To fix that the only way I see is just add columns to the Task table for every person with select boxes. This way everything can be done on one simple screen. This obviously does present some future issues.

On top of this, which people are available on a day varies and there should be an option to allow a user to set who is available on a specific day. Which would lead me to my first idea and add another table that would provide this. but then I'm having design issues again for the form.



I'm kinda stuck atm, can anyone shed some light on this. I'm sure there is an elegant way of doing this but I'm failing at finding it.



Thanks in advance,

Johan

View 5 Replies View Related

Query Timeouts When Updating A Record Retrieved Through A Websphere JDBC Datasource - Possible Record Locking Problem

Apr 7, 2008

Hi,

We're running a Sage CRM install with a SQL Server 2000 database at the back end. We're using the Sage web services API for updating data and a JDBC connection to retrieve data as it's so much quicker.

If I retrieve a record using the JDBC connection and then try and update the same record through the web services, the query times out as if the record is locked for updates. Has anyone experienced anything similar or know what I'm doing wrong? If I just use DriverManager.getConnection() to establish the connection instead of the datasource, and then continue with the same code I don't get these record locking problems. Please find more details below.

Thanks,
Sarah

The JDBC provider for the datasource is a WebSphere embedded ConnectJDBC for SQL Server DataSource, using an implementation type of 'connection pool datasource'. We are using a container managed J2C authentication alias for logging on.

This is running on a Websphere Application Server v6.1.

Code snippet - getting the record thru JDBC:


DataSource wsDataSource = serviceLocator.getDataSource("jdbc/dsSQLServer");
Connection wsCon = wsDataSource.getConnection();


// wsCon.setAutoCommit(false); //have tried with and without this flag - same results

Statements stmt = wsCon.createStatement();


String sql = "SELECT * FROM Person where personID = 12345";
ResultSet rs = stmt.executeQuery(sql);


if(rs.next()){
System.out.println(rs.getString("lastName"));
}

if (rs != null){
rs.close();
}
if (stmt != null) {

stmt.close();
}
if (wsCon != null) {

wsCon.close();
}

View 1 Replies View Related

SSIS: Multi-Record File Extract With 9 Record Types

Feb 26, 2008

I am attempting to create a multi-record file (as described in my last thread) and have found the following set of instructions very helpful:
http://vsteamsystemcentral.com/cs21/blogs/steve_fibich/archive/2007/09/25/multi-record-formated-flat-file-with-ssis.aspx

I have been able to create a sample file with two of my record types.

I now need to build on this further, because I have 9 record types in total that need to be extracted to a single flat file.

does anyone have any ideas how I might extend the example above to include more record types or know of another means of achieving this?

Thanks in advance for any help you might be able to provide.


View 3 Replies View Related

Add Date To Record In SQL Server Each Time Record Is Added

Mar 1, 2006

Hi
 
Can anyone advise me as to how I can add the date and time to 2 columns in the sql server database for each record that is added. I'd prefer not to use the webform. Can sql server add the date automatically to the row?
thanks

View 6 Replies View Related

Restrict Inserting Record If Record Already Exist In Table

Apr 17, 2014

Is that possible to restrict inserting the record if record already exist in the table.

Scenario: query should be

We are inserting a bulk information of data, it should not insert the row if it already exist in the table. excluding that it should insert the other rows.

View 2 Replies View Related

Delete Record Based On Existence Of Another Record In Same Table?

Jul 20, 2005

Hi All,I have a table in SQL Server 2000 that contains several million memberids. Some of these member ids are duplicated in the table, and eachrecord is tagged with a 1 or a 2 in [recsrc] to indicate where theycame from.I want to remove all member ids records from the table that have arecsrc of 1 where the same member id also exists in the table with arecsrc of 2.So, if the member id has a recsrc of 1, and no other record exists inthe table with the same member id and a recsrc of 2, I want it leftuntouched.So, in a theortetical dataset of member id and recsrc:0001, 10002, 20001, 20003, 10004, 2I am looking to only delete the first record, because it has a recsrcof 1 and there is another record in the table with the same member idand a recsrc of 2.I'd very much appreciate it if someone could help me achieve this!Much warmth,Murray

View 3 Replies View Related

Switch Record Background Color With Each Record In Report

Jan 14, 2008

Hi Everyone-

i have a matrix report
and i want to switch the record background color with each record in the value column in that matrix report
e.g 1st record background color is gray and next record background color is white
and then the next record background color is gray ... and so on

can anyone help?

thanx
Maylo

View 8 Replies View Related

Joining Record With The Most Recent Record On Second Table

Apr 23, 2008

Could anybody help me with the following scenario:

Table 1 Table2

ID,Date1 ID, Date2

I would like to link the two tables and receive all records from table2 joined on ID and the record from table1 that has the most recent date.

Example:

Table1 data Table2 Data

ID Date1 ID Date2
31 1/1/2008 31 1/5/2008
34 1/4/3008 31 4/1/2008
31 3/2/2008


The first record in table2 would only link to the first record in table1
The second record in table2 would only link to the third record in table1

Any help would be greatly appreciated.
Thanks

View 4 Replies View Related

How To Return First Record Child Record And Count

Jan 31, 2006

I've been looking for examples online to write a SPROC to get some data. Here are the tables.

Album_Category
AlbumCategoryID (PK, int, not null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

Albums
AlbumID (PK, int, not null)
AlbumCategoryID (int, null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)

I need to return:
-[Album_Category].[AlbumCategoryID]
-[Album_Category].[Caption]
-[Albums].[Single AlubmID for each AlbumCategoryID]
-[Count of Albums in each AlbumCategory]

I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.

View 3 Replies View Related

SQL Challenge - How To Return A Record Set Starting At A Particular Record?

Feb 25, 2007

I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.

View 4 Replies View Related

Column Locked In Record Even Though No One Accessing Record

Mar 18, 2008



Hello. I have a database with a record that has two columns locked. descrip1 and descrip2. they are both nvarchar(max) columns. These are the only two columns of the record that remain locked. I am certain no user is accessing the record. I have even moved a backup of the database to my testing computer and the lock still exists. How do I remove the lock from these two columns on that particular record.

I can edit these two columns on other records. I have researched "Unlock" on MSDN but it doesn't seem to apply to t-sql. Any help would be greatly appreciated.

Thanks. Gary.

View 4 Replies View Related

Update A Record Based Of A Record In The Same Table

Aug 16, 2006

I am trying to update a record in a table based off of criteria of another record in the table.

So suppose I have 2 records

ID owner type

1 5678 past due

2 5678 late

So, I want to update the type field to "collections" only if the previous record for the same record is "past due". Any ideas?

View 5 Replies View Related

Keep Track Of When And By Who A Record Was Created And Also When And By Who The Record Was Last Updated

May 26, 2008

I have not yet succeeded in getting an aswer to this in a previous post, so I'll try again and rephrase the question

I have 2 fields, 1 called 'Created' the other 'Updated'

I would like to create a function, user procedure ,whatever in SQL something along the lines of...

FUNCTION myUID()
RETURN Date(Today)+Time(Now)+USERNAME
END FUNCTION


so that the value returned by the above pseudo code is something like '20080526T21:01:05.620SamL' where the date part is in yyyy0m0d format

I have got this working fine for the default 'Created' field but the calculated (and persisted) field comes up with something about being non-deterministic and refuses to play

Clearly something along these lines is a very straightforward requirement (I have it working fine in my 20+ year old database) , but have spent a week trying to get an answer (even from this forum) without success, so would be grateful for any help that you can provide me with to get round this barrier

View 10 Replies View Related

Lookup && Update Record && Insert Record

Mar 26, 2008

Hi All,

I am trying to create package something like that..

1- New Customer table as OleDB source component
2- Lookup component - checks customer id with Dimension_Customer table
3- And if same customer exist : I have to update couple fields on Dimension_Customer table
4- if it does not exist then I have insert those records to Dimension_Customer table

I am able to move error output from lookup to Dimension_Customer table using oledb destination
but How can I update the existing ones?
I have tried to use oledb command but somehow it didnt work
my sql was like this : update Dimension_Customer set per_X='Y', per_Y= &Opt(it should come from lookup)

I will be appreciated if you can help me...

View 3 Replies View Related

Multiple Record Insertion For Each Record From Source

Jan 10, 2008



Hi,

How can we insert multiple records in a OLEDB destination table for each entry from the source table.
To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record.
how is this achieved.

thanks.

View 4 Replies View Related

Grabbing First Record Rather Than The Record I Am Trying To Find.

Mar 24, 2007

I tried checking to see if the point at which the reader was, that if it was the record I am looking for to go ahead and add the table data to a label. But for some reason it's only taking the first record in the database and not the one I  thought I was at.[CODE]     public void UpdateMaleHistLbl()    {        SqlConnection conn = new SqlConnection("Server=localhost\SqlExpress;Database=MyFamTree;" + "Integrated Security=True");        SqlCommand comm = new SqlCommand("SELECT * FROM FatherHistTable, MotherHistTable, UsersTable WHERE UsersTable.UserName = @usrnmeLbl ", conn);        comm.Parameters.AddWithValue("@usrnmeLbl", usrnmeLbl.Text);        conn.Open();        SqlDataReader reader = comm.ExecuteReader();        while (reader.Read())        {            string usr = reader["username"].ToString();            usr = usr.TrimEnd();            string pss = reader["password"].ToString();            pss = pss.TrimEnd();            if (usrnmeLbl.Text == usr)            {                if (hiddenpassLbl.Text == pss)                {                    maleHistLbl.Text = reader["GG_Grandfather"] + " > ";                    maleHistLbl.Text += reader["G_Grandfather"] + " > ";                    maleHistLbl.Text += reader["Grandfather"] + " > ";                    maleHistLbl.Text += reader["Father"] + " > ";                    maleHistLbl.Text += reader["Son"] + " > ";                    maleHistLbl.Text += reader["Grandson"] + " > ";                    maleHistLbl.Text += reader["G_Grandson"] + " > ";                    maleHistLbl.Text += reader["GG_Grandson"] + "<br /><br />";                }            }            break; //exit out of the loop since user found        }        reader.Close();        conn.Close();     }}[/CODE]Thanks in advance

View 1 Replies View Related

Check Values Record By Record

Nov 14, 2007

Consider this scenario.
I have two database in the sql server and consider that i have a query which has 4 tables inner joined.
When i execute the query in the database1 , the query is returning rows, But when  i execute the same query in the database2, the query is not retuning rows . I know that the
no rows are returned because of missing data in the database2. But have no idea how to trace what values are missing in the database2. Please note the tables is having a huge
list of records by which manually comparison is painfull. Please consider i dont have any background idea of the values in the tables but just using it. Any help would be
appericated.
 

View 3 Replies View Related

Looping Record By Record And Processing

Mar 8, 2005

I have 2 tables they are identical what i need to do is when i make a update on one table the other table needs to reflect that same table. so lets say i insert into table A i need to look in table B if the record exist if it doesnt insert it if it does update it and if it exist in table B and doesnt exist in table A delete the record but i have to do this record by record..can anyone help me with this

View 2 Replies View Related

Loop Through Each Record And Then Each Field Within Each Record

Dec 15, 2005

I need to essentially do 2 loops. One loops through each record and then inside each record row, I want to perform an insert on each column.

Something like this maybe using a cursor or something else:

For each record in my table (I'll just use the cursor)
For each column in current record for cursor
perform some sql based on the current column value
Next
Next

So below, all I need to do is figure out how to loop through each column for the current record in the cursor


AS

DECLARE Create_Final_Table CURSOR FOR

SELECT FieldName, AcctNumber, Screen, CaseNumber, BKYChapter, FileDate, DispositionCode, BKUDA1, RMSADD2, RMSCHPNAME_1, RMSADDR_1,
RMSCITY_1, RMSSTATECD_1, RMSZIPCODE_1, RMSWORKKPHN, BKYMEETDTE, RMSCMPNAME_2, RMSADDR1_2, RMSCITY_2, RMSSTATECD_2,
RMSZIPCODE_2, RMSHOMEPHN, BARDATE, RMSCMPNAME_3, RMSADD1_2, RMSADD2_3, RMSCITY_3, RMSZIPCODE_3, RMSWORKPHN_2
FROM EBN_TEMP1

OPEN Create_Final_Table

FETCH FROM Create_Final_EBN_Table INTO @FieldName, @AcctNumber, @Screen, @CaseNumber, @BKYChapter, @FileDate, @DispositionCode, @BKUDA1, @RMSADD2, @RMSCHPNAME_1, @RMSADDR_1,
@RMSCITY_1, @RMSSTATECD_1, @RMSZIPCODE_1, @RMSWORKKPHN, @BKYMEETDTE, @RMSCMPNAME_2, @RMSADDR1_2, @RMSCITY_2, @RMSSTATECD_2,
@RMSZIPCODE_2, @RMSHOMEPHN, @BARDATE, @RMSCMPNAME_3, @RMSADD1_2, @RMSADD2_3, @RMSCITY_3, @RMSZIPCODE_3, @RMSWORKPHN_2

WHILE @@FETCH_STATUS = 0
BEGIN

@Chapter = chapter for this record

For each column in current record <---- not sure how to code this part is what I'm referring to

do some stuff here using sql for the column I'm on for this row

Next

Case @Chapter
Case 7

Insert RecoverCodeRecord
Insert Status Code Record
Insert Attorney Code Record

Case 13

Insert Record
Insert Record
Insert Record

Case 11

Insert Record
Insert Record
Insert Record

Case 12

Insert Record
Insert Record
Insert Record

END

close Create_Final_Table
deallocate Create_Final_Table

View 2 Replies View Related







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