I have a table as part of a replicated database where i need to change the allow nulls value (set it to not allow nulls) but when i try to save the table i get this error.
'CONTACTS' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot drop the table 'dbo.CONTACTS' because it is being used for replication.
will i have to stop replication to make the change or is it worse and i have to delete the publication and start all over again?
When i do a select on my emplee table for rows with null idCompany i dont get any records
I then try to modify the table to not allow a null idCompany and i get this error message:
'Employee (aMgmt)' table - Unable to modify table. Cannot insert the value NULL into column 'idCompany', table 'D2.aMgmt.Tmp_Employee'; column does not allow nulls. INSERT fails. The statement has been terminated.
is there an elegant way to use one equals sign in a where clause that returns true when both arguments are null, and returns true when neither is null but both are equal and returns false when only one is null?
I have two SSIS packages that import from the same flat file into the same SQL 2005 table. I have one flat file connection (to a comma delimited file) and one OLE DB connection (to a SQL 2005 Database). Both packages use these same two Connection Managers. The SQL table allows NULL values for all fields. The flat file has "empty values" (i.e., ,"", ) for certain columns.
The first package uses the Data Flow Task with the "Keep nulls" property of the OLE DB Destination Editor unchecked. The columns in the source and destination are identically named thus the mapping is automatically assigned and is mapped based on ordinal position (which is equivalent to the mapping using Bulk Insert). When this task is executed no null values are inserted into the SQL table for the "empty values" from the flat file. Empty string values are inserted instead of NULL.
The second package uses the Bulk Insert Task with the "KeepNulls" property for the task (shown in the Properties pane when the task in selected in the Control Flow window) set to "False". When the task is executed NULL values are inserted into the SQL table for the "empty values" from the flat file.
So using the Data Flow Task " " (i.e., blank) is inserted. Using the Bulk Insert Task NULL is inserted (i.e., nothing is inserted, the field is skipped, the value for the record is omitted).
I want to have the exact same behavior on my data in the Bulk Insert Task as I do with the Data Flow Task.
Using the Bulk Insert Task, what must I do to have the Empty String values inserted into the SQL table where there is an "empty value" in the flat file? Why & how does this occur automatically in the Data Flow Task?
From a SQL Profile Trace comparison of the two methods I do not see where the syntax of the insert command nor the statements for the preceeding captured steps has dictated this change in the behavior of the inserted "" value for the recordset. Please help me understand what is going on here and how to accomplish this using the Bulk Insert Task.
hi all im really have a problem in my project. i have server and client side each side contain SQL Server DB. and i have excel file on the server side this excel file conected with another server, this file changed data in continuosly each less than 1 sec by data feed.
now i need to read each changed data cell from this file to save it on server DB and Client DB (just changed data).
my problem :
u know changed event is not fired when change cell by data feed or not edit manually, just calc event is rised . but calc event do not specify the changed cell range (address).
so i do this to know changed cell range: when run the program saved all excel tabel into SQL server table. and then check row by row between excel and sql if any change , when i get any change, i update the excel row insted this DB row.
and rise event to send this row to client by socket over internet to update the row in client side too.
but i tell u that the excel file updated each less than 1 sec, and i noted that many changed excel data missed until checked row by row for whole excel sheet with DB tabel and updated change.
this is my problem ( please help me as soon as posible coz i have dead line to Delivered this program)
and if u recomended me for another techneque to be easy or quickly to solve this problem i will thaks so much for u)
Maybe somebody has asked this question, but I could not find it....
I used SSIS to transfer data from Oracle database to SQL Server 2005 (with Microsoft OLE DB Provider for Oracle). It worked fine with the TNS alias name said oracle1. Then I changed the TNS alias name to oracle2. I could connect to Oracle database with oracle2 using Oracle's tool. But SSIS could not find (or solve) oracle2, if I changed the name back to oracle1 in SSIS, then SSIS worked.
I have developed a report based on a stored procedure which will generate an excel file. There is a requirement that this file needs to be sent to an external domain with the name changed accordingly i.e., a date is to be suffixed to the file after the name has been changed based on their naming convention.
I would appreciate, if anybody has done so and can give me some steps for me to work out.
Can someone tell me what to do if several NULLS are showing up within tables. Is there an easy step to locate all NULLS and delete them within the table?
Can someone provide detailed infromation on how to delete NULLS within tables. I'm new o the SQL side so I hope I have explained what it is that I'm looking to do. Our in house programmer said that the NULLS maybe causing a problem with the software communicating with the SQL Server software.
Hi i am new to SQL, and i have a data base thats got NULLS in it is there a query i can run, which can take the NULL value out ? anything will be greatly appreciated.
Hi,I don't have any specific details as I do not really understand whythis is happening but we have a customer database that we have beenusing and queries on text fields have begun returning empty stringvalues instead of NULL.So for example:SELECT TAB1.DESCRIPTIONFROM TABLE1 TAB1WHERE TAB1.DESCRIPTION IS NOT NULLORDER BY 1may have returned 50 records in the past (purely an example). It isnow returning an additional record first that appears just to be ''.Now I am not too sure where to begin looking into this. ODBCconnections, collation settings? I just am not sure where to begin.The column will not have had '' inserted into it; therefore it shouldbe NULL.I have posted this mainly so I can see if anybody else has seen thissort of behaviour - I cannot see why this has suddenly happened.The collation setting on our server is different to that of thecustomer DB, but this shouldn't make a difference should it?If anybody has any ideas then I can post some more information.Thanks,Paul
create table t1(c1 int, c2 varchar(10))insert t1 values(1,'Hello')insert t1 values(2,'')insert t1 values(3,NULL)select *from t1c1c21Hello23NULLselect *from t1where c2 = ' 'c1c22select *from t1where ltrim(rtrim(c2)) is nullc1c23NULLThe last query should have result as following. However sql server2000 does no list row c1 = 2.c1c223NULL
HiI am probably going to regret asking this because I'm sure you are going totell me my design is bad 8-) ah well we all have to learn....anywayI often use Nulls as a marker to see if certain tasks have been completed.A typical example would be a column say invoice_valuewhen new work is entered and a new record is appended, I leave theinvoice_value column as NULL, so if I want a View for uninvoiced work Icheck for the invoice_value being equal to NULL, and alternatively if I wantinvoiced work, I check for not null. I did it this way to save putting inanother column that needed to be set to TRUE or FALSE. (I do similar thingselsewhere aswell)so far everything seems to work OK but reading some old stuff on Google Iget the feeling that NULLS should be left alone, have I done wrong?many thanksAndy
I have a page that uses an SQLDataSource web control to populate a GridView control. The SQLDataSource control uses three parameters, one of which is a text string (varchar(50)). I get timeout expired exceptions when I run the page with two specific string values, yet when I run the stored procedure in SqlSvr Management Studio, the procedure runs in 5 seconds and under 1 seconds. Being rnew to ASP.NET 2.0 and .Net in general, I have no idea where this lag is occurring. I would like to know two things: 1. What is causing the lag that causes the timeout in ASP.NET 2.0. 2. What can be done to adjust the timeout period SQLSVR, IIS, ASP.NET). The underlying query is running as desired (considering the data it has to chew on). For all other values, data is returned almost instantanious. I can provide a copy of the query id need be. Truly appreciate any insight you can give me!
i changed my state table from char 20 to varchar 20 and it still fills it with empty space at the end of the statename I tried entering it with the trim command still no good i looked a the sql command made in vs and it says that the variables are varchar is there something i am missing with varchar does it only work properly if it is over 50 or does it not change completely when you change from char to varchar
i have table Table1 with column lastname ,firstname ect.The Data type is varchar. I have inserted values. I have selected records from this table and assign this records to the TDC control of the HTML .The query returns 130 records.The TDC control has the Unicode delimiter. Whene we select top 7 ,seven records are displayed ,when it is top 8 it does not show any records. If we delete the Lastname and Firstname values for this 8th record then all the 130 records are displayed. Why is this happning.Any great minds can help me in this pls
First of all, let me apoligize if this is a stupid question, but just a few days ago I decided to change my FE/BE database from Ms Access to SQL Server Express. Now I have a FE in ACC2002 and a BE in SQL SERVER (the two connected via ODBC). Anyway, this is my question:
When everything was in Access I had a form retreiving a series of records based on a filter - for understanding purpose let's say that a field must have been 'Not Null'. The operator then filled that field with data. With Access everything was static, and the operator could see all the records, the ones already changed and the ones not changed. With SQL now: everytime the operator fills in the field, the record changes to #Deleted. I assume that that is happening because the query - someway - is "alive" and is "filtering data alive"... or is updating records instantly. Just to make things more clear: is the record changed immediately so it is not considered part of the filtered data by the query, thus being change to #Deleted? What could I do to prevent this?
CREATE TABLE TEMP1 (REFID INT, REVISION INT, FIELDNAM VARCHAR(10), VALUE VARCHAR(10)); INSERT INTO TEMP1 VALUES(1001, 0, 'A', 'A2'); INSERT INTO TEMP1 VALUES(1001, 0, 'C', 'C2'); INSERT INTO TEMP1 VALUES(1001, 0, 'E', 'E2'); INSERT INTO TEMP1 VALUES(1002, 0, 'A', 'A3'); INSERT INTO TEMP1 VALUES(1002, 0, 'B', 'B2'); INSERT INTO TEMP1 VALUES(1002, 0, 'E', 'E3'); INSERT INTO TEMP1 VALUES(1001, 1, 'A', 'A4'); INSERT INTO TEMP1 VALUES(1001, 1, 'E', 'E4');
Here based on latest revision and refid I should get the fieldnam and value. Expected output: REFID FIELDNAM VALUE REVISION 1001 A A4 1 1001 E E4 1 1002 B B2 0 1001 C C2 0
I want to import via DTS to big table only the records that changed last day. can i do it without a time field? because this is a key table for DWH and not a fact table. THX Inon
First I want to query everyone with a date greater than today in the field EXPIRATION DATE. I know I can say > 2006-03-19 but I always want it to be TODAY.
Next, Is there a system field that I can search for the date a record was last updated?? I make frequent changes, and I want others to see what date a record was last updated. If not, can I tell the system to mark a field "YES" when I make changes?
I use phpmyadmin. I do not have server access, just through PHP.
Hi experts,I have been trying to limit the table rows in the following situation,any suggestions will be appreciated.we have table called tempTb has columns id, c_id, c_name, rating, datecolumns.id is an identity column.date is a datetime column, the rest are varchar datatype.Here is the table structure with sample data,idc_idc_nameratingdate1aoamer onli11/1/20022aoamer onli13/1/20023aoamer onli16/1/20024aoamer onli39/1/20025aoamer onli312/1/20026aoamer onli33/1/20037aoamer onli36/1/20038aoamer onli39/1/20039aoamer onli212/1/200310aoamer onli16/1/200411aoamer onli112/1/200412xyxabs yasd11/1/200213xyxabs yasd23/1/200214xyxabs yasd26/1/200215xyxabs yasd29/1/200216xyxabs yasd112/1/200217xyxabs yasd13/1/200318xyxabs yasd36/1/200319xyxabs yasd39/1/200320xyxabs yasd212/1/200321xyxabs yasd16/1/200422xyxabs yasd112/1/2004[color=blue]>From this table I need to select the rows with rating changes only,[/color]i.e if two or three consecutive rows have same rating only the firstrow should be selected.the selection should look like...idc_idc_nameratingdate1aoamer onli11/1/20024aoamer onli39/1/20029aoamer onli212/1/200310aoamer onli16/1/200412xyxabs yasd11/1/200213xyxabs yasd23/1/200216xyxabs yasd112/1/200218xyxabs yasd36/1/200320xyxabs yasd212/1/200321xyxabs yasd16/1/2004I was trying to do this by self-joining the table like....select t1.* from tempTb t1, tempTb t2where t1.id!=t2.id,t1.c_id=t2.c_id,t1.c_name=t2.c_name,t1.rating!=t2.rating.But this is generating cartesian products,I have tried some other combinations after where clause with date colmnwtc,but none seems to give the required result.so if anybody can guide me in the right direction I would appreciateit.Thanks alot,Remote
I have a stored procedure that inserts values into five columns of atable. I need another stored procedure that will allow the user to passone or more of those parameters and update only the column for the datathat was passed. In other words, the update may only have one or two ofthe parameters that was originally provided in the insert. Therefore, Ido not want to update the columns that did not change. What is theproper way to handle this situation?
I need to create a table that would be the result set of a comparisonbetween table a and table b? Table a and b first 2 fields will always bethe same (CustomerName and CustomerNumber). But if the Address1 fieldchanges in table a, I would like to throw that whole row into mycomparison table. Almost like a Select Into with a sub query that wouldinclude a WHERE TableA.field <> TableB.field. I would need to do thiscomparison for about 8 fields. Help appreciated for my syntax is prettybad. Thanks.Steve*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
