Single Field Duplication Problem
I have a table with many fields but there is a single field that I do not want duplicates. If I index this specific field preventing duplicates, the entire record does not append. (The field in question is not keyed).
Thanks
Bill Howard
View Complete Forum Thread with Replies
Related Forum Messages:
Concatenation Of Suboptions Into Single Field
I have two tables which are linked on OptionID fields: Table1: ID (PK), Name, OptionID Table2: OptionID (PK), OptionName So every record in Table1 has several (from zero to many) corresponding records in Table2. I need a query which returns all Table1 records with all respective OptionNames being cocantenated in single text field. E.g.: 1, 'Name 1', 'Option1 Option2 Option3' 2, 'Name 2', 'Option2 Option6' ... I wonder if it's possible to do this with one query without stepping through Table1 line by line. I have SQL Server 7 SP3. Any suggestions which may help me do it the most efficient way are appreciated. andyp.
View Replies !
Same Field From Several To Seperate Fields Of A Single Row
Hi, I'm struggling with this. I'd like to perform a joined query from two or more tables and take the same field from several rows of one table into seperate fields of a single row in a new table. Like this: table 1 uidunameuloc 1mehere 1methere 2youhere 2 youthere table 2 uidulocuparam#uparamval 1here1a 1here2b 1here3c 1there1d 1there2e 1there3f 2here1g 2here2h 2here3i 2there1j 2there2k 2there3l result in table 3 uidunameuloc uparval1 uparval2 uparval3 1mehere a b c 1methere d e f 2youhere g h i 2 youthere j k l uparam# field in table 2 always the same sequence Any ideas???? Thanks...
View Replies !
Select Single Row With Duplicate Value In Particular Field
Hi, I have several row records in which name email --------- --------- name1 abc@abc.com name2 abc@abc.com name3 abc@abc.com name4 123@123.com name6 123@123.com How should I write the query which can return records with unique email address with any name attached? The expected record set will be name email --------- --------- <any> abc@abc.com <any> 123@123.com I was thinking to select the distinct of email and just stuck at here. I encountered this problem few times and still cannot solve it. Any help will great appreciated. Thanks in advance.
View Replies !
Joining Two Fields To Single Field
If I have a database table with the following columns: ID Other_ID Description And I want to join the two ID fields to one field in another table that contains the following fields: ID Name How would i do that? Here is some sample data and what I would like returned TABLE1 ID Other_ID Description row 1 1 2 Number1 row 2 3 1 Number2 TABLE2 ID Name row 1 1 John row 2 2 Bob row 3 3 Bill I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like: John Bob Number1 The only way around it now is that I store Other_Name in Table1. Thanks.
View Replies !
Multiple Rows Into A Single Field
Hi I have aproble with stored procedure.I want to take the Data from a table with multiple rows,In the same select statement for the others select statemet.My store Proc is like this.. CREATE procedure spr_Load_TR_AccidentReport_Edit_VOwner ( @Crime_No varchar(20), @Unit_ID int ) as begin DECLARE @AD_Driver int,@AC_Cas int,@AV_Owner int,@A_Witness int DECLARE @Defect_ID varchar(100) select @AV_Owner=Vehicle_Owner from TBL_TR_ACCIDENT_VEHICLE where Crime_No =@Crime_No and Unit_ID = @Unit_ID SELECT TBL_TR_Person_Details.Person_ID,TBL_TR_Person_Details.Person_Name, dbo.TBL_TR_Person_Details.Address1, dbo.TBL_TR_Person_Details.Address2, dbo.TBL_TR_Person_Details.City_Id, dbo.TBL_TR_Person_Details.State_Id, dbo.TBL_TR_Person_Details.Nationality_id, dbo.TBL_TR_Person_Details.EMail, dbo.TBL_TR_Person_Details.Phone, dbo.TBL_TR_Person_Details.zip, dbo.TBL_TR_Person_Details.sex, dbo.TBL_TR_Person_Details.D_O_B, dbo.TBL_TR_Person_Details.Age, dbo.TBL_TR_Person_Details.Occupation_ID, dbo.TBL_TR_Person_Details.Person_Type, TBL_TR_ACCIDENT_VEHICLE.Registration_Number, TBL_TR_ACCIDENT_VEHICLE.Crime_No, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Type, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Vanoeuvre, TBL_TR_ACCIDENT_VEHICLE.vehicle_Make, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Model, TBL_TR_ACCIDENT_VEHICLE.Unit_ID, TBL_TR_ACCIDENT_VEHICLE.RowID, TBL_TR_ACCIDENT_VEHICLE.UserID, TBL_TR_ACCIDENT_VEHICLE.Vehicle_Color, TBL_TR_ACCIDENT_VEHICLE.HP, TBL_TR_ACCIDENT_VEHICLE.Seating_Capacity, TBL_TR_ACCIDENT_VEHICLE.Class_Of_Vehicle, TBL_TR_ACCIDENT_VEHICLE.Unladen_Weight, TBL_TR_ACCIDENT_VEHICLE.Registered_Laden_Weight, TBL_TR_ACCIDENT_VEHICLE.Skid_Length, (select TBL_TR_Person_OutsideDetails.OutSide_state from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_state], (select TBL_TR_Person_OutsideDetails.OutSide_City from TBL_TR_Person_OutsideDetails,TBL_TR_ACCIDENT_VEHICLE where TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner = TBL_TR_Person_OutsideDetails.Person_id and TBL_TR_ACCIDENT_VEHICLE.RowID =TBL_TR_Person_OutsideDetails.RowID)[OutSide_City] ---here I faced the problem- /*For the above Select only return one rows.But this select willreturn multiple row .I wnat to put that multiple data into a single field with comma*/ (SELECT @Defect_ID = COALESCE(@Defect_ID + ',','') + CAST(TBL_TR_VEHICLE_DEFECT.Defect_ID AS varchar(5)) FROM TBL_TR_VEHICLE_DEFECT,TBL_TR_ACCIDENT_VEHICLE WHERE TBL_TR_VEHICLE_DEFECT.Registration_Number =TBL_TR_ACCIDENT_VEHICLE.Registration_Number) select @Defect_ID FROM tbl_TR_Accident_report,TBL_TR_Person_Details,TBL_TR_ACCIDENT_VEHICLE where tbl_TR_Accident_report.Crime_No=@Crime_No and tbl_TR_Accident_report.Unit_ID=@Unit_ID AND TBL_TR_ACCIDENT_VEHICLE.Crime_No=@Crime_No AND TBL_TR_Person_Details.Person_ID = TBL_TR_ACCIDENT_VEHICLE.Vehicle_Owner end GO
View Replies !
Best Practice For Updating Single Field In N Rows
Hi I haven't included DDL etc as this is theoretical at this time. I can rustle up an illustrative example if required.Following applies to disconnected environment. In general, most edits are broad (i.e. n fields affected) against a single record. However, there are certain circumstances where a single field will be edited against a deep (n) set of records. So business request:User needs to access and edit n records at a time (for arguments sake n is unlikely to be enourmous - say 50 max) but only editing one single field, and always the same field. The values for this edited field will differ for each record. Initially retrieving data for the app is no problem, nor is identifying those records that have been edited. What is the best means of updating the table? There are, to my mind, three ways of dealing with the latter senario - 1) Client calls the server n times editing a record at the time.2) Client creates a csv string and passes to sproc. Sproc parses string using some UDF split function (probably chucking into table variable) and updates using a single set set based operation.3) Client creates some flat file or other that is BULK inserted (or similar) by SQL Server. 3 - I think would only be an option if n was a very large number and or having the changes immediately reflected in the data is not priority.1 - would create a chatty app and presumably put the most load on the server. So I am left thinking 2 is (depending on circumstances) the best method. Is this fair? Are there any particular considerations gotchas I should be aware of? I know where to get hold of as many TSQL split functions as I could want so I'm not looking for code just opinions. OR - am I just plain wrong? Is there a better alternative or am I dismissing the other two methods prematurely? Thanks in advance
View Replies !
Updating Single Quote In Varchar Field
hi! I am working on a database where i have to update a few things.The following is the table AgentId Disposition (VarChar) (Varchar) Agnt112 Don't Call Agnt113 Answer Agnt114 Don't Call Already there are single quotes in the Disposion Column. now my Query is:- Update table1 set Disposition='donot call again' where disposition='don't call' now this throws a error as there is a single quote in the column. Kindly help thnx.
View Replies !
Creating Trigger For A Single Column/Field?
Hi all, My code below creates a trigger that fires whenever a change occurs to a 'myTable' row, but this is not what I want. I only want to log changes made to a single field called 'Charges', when that field is changed I want to log it, can anyone tell me how to modify my code to do this, thanks Create Trigger dbo.myTrigger ON dbo.[myTable] FOR UPDATE AS Declare @now DATETIME Set @now = getdate() BEGIN TRY Insert INTO dbo.myAuditTable (RowImage,Charges,ChangeDate,ChangeUser) SELECT 'BEFORE',Charges,@now, suser_sname() FROM DELETED Insert INTO dbo.myAuditTable (RowImage,Charges,ChangeDate,ChangeUser) SELECT 'AFTER',Charges,@now, suser_sname() FROM INSERTED END TRY BEGIN CATCH ROLLBACK TRANSACTION END CATCH
View Replies !
Duplication
How can I achieve the following... I have a Membership No. field which comes from a bookings table, so multiple membership no. do exist. What I want to achieve is a list of membership No.s with no duplication. Sorry to be so dumb, but we all have to start somewhere.
View Replies !
Duplication Error
i have taple translatio witch have theses coloumn ID,TypeID,Status,ComID,RecordID,Translator in this table we assign a certain company with a certain typeID to a certain translator . so next time when the translator log he goes to the company that he assigned to it when he log to the company page, there is another company page n arabic that is transalted by the translators , in the arabic page there is a button when you can send this company to transaltion. but a duplicate have been happen made because user send the same documnet to the translation table so many time so translator transalte the same company profile more than one time witch is not good. we handle the duplicate of the send to translation button put still there is duplicate record in the database witch is more taht 3000 record how ican to remove thsi record from the DB without make ant other erroe
View Replies !
Report Job Duplication
HI I have created a job to run a reporting services job which then named it in the job scheduler 354EEF12-404F-46BD-B54F-708B5027837F. I then renamed the job to Rpt ETL log. However it I was surpised to see two emails come with reference to this report. It seems to have created another one with the long job names. Is there any way to stop this as I would really like to name to schduled rpt jobs without it duplicating. Many Thanks Robert
View Replies !
Prevent Duplication On UPDATE
Hello I noticed a spelling mistake in the data in a column of several tables, I used the following syntax to alter the spelling: UPDATE [dbo].[Prod_Cat] SET [ProdName]=N'merseyside' WHERE ProdName = 'mmserseyside' The above code correctly updated the spelling error, but it also inserted a new row with the corrected data. So I found myself with two Identical rows containing the corrected information. I had to manually delete the extra row. Because if I had put in a DELETE statement, I would have then lost both rows. What do I need to do to prevent this happening next time. As I find that I need to update the names of some products, but I don't want to duplicate them. Thanks Lynn
View Replies !
Track Duplication Of Records
Hello, I have a table which consists of 27,000 of records. Among these records, there is one record which is a duplication of another record. Is there any way to track this record from the same table by the SQL statement ? I have been advised to use the following statement but it does not help: Select count(*) As Duplicate, columnname from tablename group by columnname Scrolling 27,000 lines of records with bare eyes is very painful. Any help is appreciated. Cheers
View Replies !
Duplication In The Primary Key Column
Hi I have a set of excel files that i need to export to sql2005 using ssis. Now the issue is that i have no idea about he data ie it may have duplication in the primary key column. If i export it as it is to sql server, it will cause me problems. Is there any way i can filter out the rows which have duplication in the primary key column? Umer
View Replies !
De-Duplication Performance Issue
Hi All, Scenario: De-duplication logic should pick one record from source and check with all the destination records and insert if not duplicate. Else raise error. There are average 8 to 10 lookup check for each logic path. Key fields used for de-duplication check are FirstName, LastName, DOB, Gender, SSN. Issues: · Since picking row by row and processing the performance is constrained. · Since 8-10 comparison is done using lookup performance downstream. (Lookup is used without caching, if cashing is used the package is failing after sometimes as if memory is failing. Can we handle this memory problem?) Please give some suggestion to improve the performance. Current performance is around 2500 records per hour, but there are 8 lac records in total to process. I am looking for guidance on this issue. If someone can guide me on how to do it better it would help me a lot. Thanks, S Suresh
View Replies !
How To Avoid PrimaryKey Duplication?
Hi, I having a problem with my query... I want to copy data from 4 different database to 1 database... but if the destination database have already the same Primary Key the copying stops/terminated and not copying others that is not yet in the destination... I don't have knowledge in T-SQL like IF...ELSE my database is SQL Server 2000 but i'm using SQL 2005 Express Management for the query... What i'm doing is like this: Use osa (Destination Database) Go DELETE FROM tblFaculty (*I'll delete first the datas to avoid duplication) INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode) (SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM cislucena.dbo.tMasFaculty) INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode) (SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM amapn.dbo.tMasFaculty) INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode) (SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM abe.dbo.tMasFaculty) INSERT INTO tblFaculty (FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode) (SELECT FacultyID, LastName, FirstName, MiddleName, Rank, DeptCode FROM aclc.dbo.tMasFaculty) My problem is if the facultyID (PrimaryKey) which i'm copying is already on the destination which is osa, the copying stops/terminated regardless whether there is more to copy. On the 4 source database, there might data that other database also has. That's why the copying is terminated. All i want to do is to check first each FacultyID if it is already on the destination before copying it to avoid error or duplication of Primary Key so it won't terminate the copying. How is this possible sir? Anyone care to help? Thanks in advance! More Power! Best Regards
View Replies !
I Need This To Be Done Using Only Single Stored Procedure For Binding Field Value To DropDownBox And For Adding Income. Plz Tell Me How To Do This?
My task is to add income by taking few variables from webpage. I had take User ID(From database), Field value by selecting it from DropDownBox( Which value is once again taken from database), Income Description, Date, Amount . I had completed this task successfully by binding DropDownBox to database by query string and added income using stored procedure as below. I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this? ASPX.CS file protected void Page_Load(object sender, EventArgs e) { SqlConnection con = null; con = DataBaseConnection.GetConnection(); DataSet ds = new DataSet(); SqlDataAdapter da = new SqlDataAdapter("select PA_IFName from PA_IncomeFields where PA_UID=@PA_UID", con); da.SelectCommand.Parameters.Add("@PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"]; da.Fill(ds); IFDdl.DataSource = ds; IFDdl.DataTextField= ds.Tables[0].Columns[0].ToString(); IFDdl.DataValueField = ds.Tables[0].Columns[0].ToString(); IFDdl.DataBind(); } protected void IncAddBtn_Click(object sender, EventArgs e) { SqlConnection con = null; try { con = DataBaseConnection.GetConnection(); SqlCommand cmd = new SqlCommand("AddIncome", con); cmd.CommandType = CommandType.StoredProcedure; //SqlCommand cmd = new SqlCommand("",con); //cmd.CommandText = "insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt)"; cmd.Parameters.Add("PA_UID", SqlDbType.Int).Value = (int)Session["PA_UID"]; cmd.Parameters.Add("@PA_IFName", SqlDbType.VarChar, 10).Value = IFDdl.SelectedValue; cmd.Parameters.Add("@PA_IDesc", SqlDbType.VarChar, 50).Value = IFDescTB.Text; cmd.Parameters.Add("@PA_IDate", SqlDbType.DateTime).Value = Convert.ToDateTime(IFDateTB.Text); cmd.Parameters.Add("@PA_IAmt", SqlDbType.Money).Value = Convert.ToDecimal(IFAmtTB.Text); cmd.ExecuteNonQuery(); IFLabelMsg.Text = "Income Added Successfully!"; } // end of try catch (Exception ex) { IFLabelMsg.Text = "Error : " + ex.Message; } finally { con.Close(); } } Stored Procedure ALTER PROCEDURE dbo.AddIncome (@PA_UID int,@PA_IFName varchar(10),@PA_IDesc varchar(50),@PA_IDate datetime,@PA_IAmt money) /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ AS /*SET NOCOUNT ON*/ insert into PA_Income values(@PA_UID,@PA_IFName,@PA_IDesc,@PA_IDate,@PA_IAmt) ASPX File <%@ Page Language="C#" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="AddIncome.aspx.cs" Inherits="AddIncome" Title="Untitled Page" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"> <h2> Add Income </h2> <br /> <table> <tr> <td> Select Income Field</td> <td> <asp:DropDownList ID="IFDdl" runat="server" Width="247px" > </asp:DropDownList> <a href="addincomefield.aspx">Add Income Field</a> </td> </tr> <tr> <td> Enter Income Amount </td> <td> <asp:TextBox ID="IFAmtTB" runat="server" Width="96px"></asp:TextBox> Date <asp:TextBox ID="IFDateTB" runat="server" Width="93px"></asp:TextBox>(MM/DD/YY)</td> </tr> <tr> <td> Enter Income Description </td> <td> <asp:TextBox ID="IFDescTB" runat="server" Width="239px"></asp:TextBox></td> </tr> </table> <br /> <asp:Button ID="IncAddBtn" runat="server" Text="Add Income" OnClick="IncAddBtn_Click" /><br /> <br /> <asp:Label ID="IFLabelMsg" runat="server"></asp:Label> </asp:Content> I need this to be done using only single Stored Procedure for binding Field Value to DropDownBox and for adding income. Plz tell me how to do this?
View Replies !
SSRS Removes Spaces And Line Breaks From Single Field Report
I am storing formatted data (including spaces and line breaks) in a single field in a table. When I run a report on that field, the preview of the report is automatically removing all the extra spaces and line breaks, making the report unreadable. When exporting the report to PDF or printing it, it shows the line breaks and spaces as expected. Does anyone know how to make the report preview show the spaces and line breaks?
View Replies !
Form Field Returns Name With Double Quotes Instead Of Single Quote During Update Process.
I've a weird problem in my application. In of the pages, while trying to update the text box "Name", when I enter Linda's test, it gets saved as Linda''s test. I'm not sure if this is a problem due to SQL server. When I look at the stored procedure, I don't anything different. Also, when I update the table directly in SQL Server, the result is displayed in single quote. But if I update the field thro' the application, the returned name is with double quotes instead of single quote. Has any of you faced problems like this? What am I missing? What do I need to do to get the name saved the way I entered (with single quotes) instead of double quotes?
View Replies !
Error Catching On Data Duplication In A Sql2005 Db
Hello, everyone. I am having problems catching a data duplication issue. I hope I can get an answer in this forum. If not, I would appreciate it if someone can direct me to the right forum. I am working on a vs2005 app which is connected to a sql2005 db. Precisely, I am working on a registration form. Users go to the registration page, enter the data, ie. name, address, email, etc. and submit to register to the site. The INSERT query works like a charm. The only problem is that I am trying to reject registrations for which an email address was used. I put a constraint on the email field in the table and now if I try to register using an e-mail address that already exists in the database I get a violation error (only visible on the local machine) on the sql's email field, which is expected. How can I catch that there is already an email address in the database and stop the execution of the code and possibly show a message to the user to use a different address? Thank you for all your help. Antonio
View Replies !
Insert Statement Results In Data Duplication Using MDAC 2.8
I am using Remote Data service to Query an Sql Server Database using MDAC. The Os in which server is loaded in Window 2003 and the MDAC 2.8 version is installed. Now I create a table X with identity column. Then when I try to insert records in that table using Insert into X select * from Y statement. The statement gets executed by when i check the X table I find that the duplicate records are present with different identity no's. Even when i truncate and retry the same thing occurs. What could be the reason for the same?? Regards Pranali.cons
View Replies !
Fix Legacy Data - Missing Primary Key + Duplication Record + Large Table
We have a large table which is very old and not much ppl take care about, recently there is a performance problem from the report need to query to this table. Eventally we find that this table have primary key missing and there is duplicate data which make "alter table add primary key" don't work Besides the data size of this table require unacceptable time to execute something like "insert into new_table_with_pk from select distinct * from old table" Do you have any recommendation of fixing this? As the application run on oracle , sybase and sql server, is that cross database approace will work?
View Replies !
Trying To Return A Single Record For Each Client From Child Table Based Upon A Field Of Date Type In Child Table
I have table "Clients" who have associated records in table "Mailings" I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting. I'm trying to return records containing the next upcoming mailing for each client. The closest I can get is below: I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date' SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..
View Replies !
Combine Data In Single Row From Single Table
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date Code: Employee No Date SALARY 1 10/30/2006 500 1 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 4 11/01/2006 8000 Should Appear Code: EmployeeNo Date1 OLDSALARY Date2 NEWSALARY 1 10/30/2006 500 11/30/2006 1000 2 10/25/2006 800 3 10/26/2006 900 4 10/28/2006 1000 11/01/2006 800 PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT. THANKS IN ADVANCE
View Replies !
Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string. I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2))) option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value. With refresh_shipping_sched .ActiveConnection = CurrentProject.Connection .CommandText = "spRefresh_shipping_sched" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue) .Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option) .Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "") .Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "") Set rs_refresh_shipping_sched = .Execute End Please help
View Replies !
Informix Date Type Field To SQL Server Datetime Field Error
I am trying to drag data from Informix to Sql Server. When I kick off the package using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP errors on two fields from Informix which are date data ....no timestamp part I tried a couple of things: Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed. Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them as datetime but it failed. What options do I have that will work?
View Replies !
Odbc - Binding Sql Server Binary Field To A Wide Char Field Only Returns 1/2 The Daat
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
View Replies !
Retrieving The Description Field Or SQL Comment Field From A View
Hi, I have managed to use the INFORMATION_SCHEMA.TABLES to retrieve a list of all my views. I was wondering if it was possible to retrieve the Description or SQL Comment field from a view via ADO.NET, which can be found in the Enterprise Manager Design View properties (right click - properties) I want to hopefully utilize this field for dynamic page titling of a datagrid bound view. Many Thanks in anticipation!
View Replies !
Multiple Foreign Keys On Same Field, Based On Other Field
I have a table called BidItem which has another table calledBidAddendum related to it by foreign key. I have another table calledBidFolder which is related to both BidItem and BidAddendum, based on acolumn called RefId and one called Type, i.e. type 1 is a relationshipto BidItem and type 2 is a relationship to BidAddendum.Is there any way to specify a foreign key that will allow for thedifferent types indicating which table the relationship should existon? Or do I have to have two separate tables with identical columns(and remove the type column) ?? I would prefer not to have multipleidentical tables.
View Replies !
Create Date Field From Substring Of Text Field
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View Replies !
Separating One Field Into Two Fields Based On A Character In The Field
I know there has to be a way to do this, but I've gone brain dead. Thescenario..a varchar field in a table contains a date range (i.e. June 1,2004 - June 15, 2004 or September 1, 2004 - September 30, 2004 or...). Theusers have decided thats a bad way to do this (!) so they want to split thatfield into two new fields. Everything before the space/dash ( -) goes intoa 'FromDate' field, everything after the dash/space goes into the 'ToDate'field. I've played around with STRING commands, but haven't stumbled on ityet. Any help at all would be appreciated! DTS?
View Replies !
Access Memo Field To SQL Server Text Field
Hi, I'm importing an Access database to SQL Server 2000. The issue I ran into is pretty frustrating... All Memo fields that get copied over (as Text fields) appear to be fine and visible in SQL Server Enterprise Manager... except when I display them on the web via ASP - everything is blank (no content at all). I didn't have that problem with Access, so I ruled out the possibility that there's something wrong with the original data. Is this some sort of an encoding problem that arose during database import? I would appreciate any pointers.
View Replies !
Export Access Memo Field To SQL Text Field
Hi, Can anyone point me any solution how to export a MEMO field from an Access database to a TEXT field from an MS SQL Server 2000. The import export tool from SQL server doesn't import these fields if they are very large - around 9000 characters. Thanks.
View Replies !
Grouping By One Field And Combine Data Of Another Field In SSRS
Hi all experters, Please suggest how to build the report in below case: Raw data: ID Member Functions 1 Alan A 1 Alan B 2 Tom A 2 Tom B 2 Tom C 3 Mary D 3 Mary E Report Shows: ID Member Functions 1 Alan A,B 2 Tom A,B,C 3 Mary D,E I group the data by the column ID, but would like to show the functions data by join all functions' values by the same ID. Any good suggestion? Thanks in advance, Steve Wang 2008/4/2
View Replies !
MS Access Memo Field To SQL Server Text Field
Hi all, i've a reasonable amount of experience with MS Access and less experience with SQL Server. I've just written an .NET application that uses an SQL Server database. I need to collate lots of data from around the company in the simplest way, that can then be loaded into the SQL Server database. I decided to collect the info in Excel because that's what most people know best and is the quickest to use. The idea being i could just copy and paste the records directly into the SQL Server database table (in the same format) using the SQL Server Management Studio, for example. Trouble is, i have a problem with line feed characters. If an Excel cell contains a chunk of text with line breaks (Chr(10) or Chr(13)) then the copy'n'paste doesn't work - only the text up to the first line break is pasted into the SQL Server database cell. The rest is not pasted for some reason. I've tried with MS Access too, copying and pasting the contents of a memo field into SQL Server database, but with exactly the same problem. I've tried with 'text' or 'varchar' SQL Server database field formats. Since i've no experience of using different types of databases interacting together, can someone suggest the simplest way of transferring the data without getting this problem with the line feeds? I don't want to spend hours writing scripts/programs when it's just this linefeed problem that is preventing the whole lot just being cut'n'pasted in 5 seconds! cheers Dominic
View Replies !
Convert Field From VarChar To Int With Speical Characters In Field
Hello, I have a table with a column that is currently a varchar(50), but I want to convert it into an int. When I try to just change the type in design mode I get an error that conversion cannot proceed. When I look at the field it appears some of the entries have special characters appended at the end, I see a box after the value. How can I remove all speical characters and then convert that field to an int? Also I tried the following query which did not work as well, same error about conversion. UPDATE myTable SET field = CAST(field AS int)
View Replies !
|
TRACKER
