First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
First of all, I've been a reader of swynk.com for quite sometime now, and I'd like to say 'thank you' to everyone who contributes.
Today, I'm the town moron.. haha I'm having issues with column level constraints. I have a varchar(50) where I want to keep *,=,#,/, .. etc, OUT OF the value input. I don't want to strip them. I simply want for sql to throw an error if the insert contains those (and other characters). The only characters that I want in the column are A-Z and 0-9. However, it's not a set number of characters per insert. It always varies... There has to be an easier way to do this than creating a constraint for every possibilty... Any help would be greatly appreciated.
Please help with a problem - I need to execute the following process:
(1) Create about 200 empty tables in a database (this MUST be done prior to step 2) (2) copy rows from a source database with identical tables into all of the empty tables, (3) copy more rows from a third database source (with some duplicate rows) into the tables from step 2.
The problem is that the first copy goes OK since the tables are empty, but the second copy fails as soon as a duplicate row triggers an error in the DTS tool. At that point, the DTS tool goes on to the next table until the problem is repeated.
In Oracle, I can set a flag for import such as IGNORE=YES which will ignore object or row creation errors.
How can I get DTS to ignore row creation errors, due to expected duplicate row constraints, and just carry on with the next row after each rejected row? In other words, set DTS to simply reject duplicate rows without aborting the entire table copy.
I want to perform column level and database level encryption/decryption.... Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms... I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database... Can anybody help me out... If you have Store procedure in SQL for the same then also it ll do... Thanks in advance
I have about 100 databases which yeterday had the wrong ddl for a column added, we have a simple cursor that loops through all databases on the server and runs the sql statement.
The following was run
ALTER TABLE salesrep ADD newCol bit DEFAULT 1
Problem is that it should have been this (and preferably have had a named constraint):
ALTER TABLE salesrep ADD newCol bit NOT NULL DEFAULT 0
Problem is now i dont know the names of the contraints they have random hex on the end "The object 'DF__SALESREP__newCol__5BCD9859' is dependent on column..."
How do i just drop this column with ANY constraints on it?,
I really dont want to go through the databases 1 by 1 and look up the constraint name then drop the constraint, then drop the column so i can add it in correctly.
I wish the following would just work.
ALTER TABLE salesrep ALTER COLUMN newCol bit NOT NULL Default 0
I am in the "Check Constraints" window trying to add a constraint that during inserts only allow distinct Column2 insertions. What is the Expression for that? I tried UNIQUE(Column2) it give me an error.
Here is my table definition: Column Name Data Type Allow Nulls -------------------------------------------------------------------------------------------- Column1(PrimaryKey) int No Column2 nvarchar(MAX) No
Greetings:I have to do a one-off forceful change of some data in a database. Ineed to disable some FK constraints, make the data change, and thenre-enable the constraints.My process will be:ALTER TABLE TABLE1 NOCHECK CONSTRAINT FK_TABLE1_TABLE2UPDATE TABLE1 SET COLUMN1=2 WHERE COLUMN2='Problem row'ALTER TABLE TABLE1 CHECK CONSTRAINT FK_TABLE1_TABLE2I wanted to get some evidence that the constraints were back up after Idid the 'check constraint' statements, so I looked in sysobjects.There's a status column for the constraints, but I can't make muchsense of the numbers. Most of the FK constraints that I have notchanged are of status 0, but when I NOCHECK a constraint, it goes to2304. When I CHECK it again, it's 2408.Can someone explain this, and suggest a way to double-check that myconstraints are in fact enabled once I run the CHECK statement?Thanks!!
can you have constraints as such [CreateBy] [nvarchar](30) NOT NULL DEFAULT (suser_sname()),on a table that has a column store index in SQL Server 2012,2014, or 2016?
I have created a simple package to load an Excel Spreadsheet into a SQL Server table. There is a one to one relationship between the columns in the .xls file and the columns in the DB record. I am getting integrity constraint errors when I try to load all numeric data from the spreadsheet (defined as Category General in excel, not defined as numeric but consisting of all numeric characters) into a column defined as (nvarchar(20), not null) in SQL Server Management Studio. There are no constraints on the column.
I have been able to temporarily bypass the offending rows, but I do need to load them into SQL Server. The problem column has a mixture of data, two examples would be: N255, 168050. It's the 168050 value that's causing the Task to bomb. How can I get this loaded into my table ?
I am running the package from within MS Visual Studio 2005 Version 8, Excel is version 2003 (11.8120.8122) SP2
I know this is probably a flick of a switch but I cannot figure out which switch. Setup is SQL Server / Stored Procedures / DAL / BLL(skipped for testing) / PL. The stored procedure queries from only one table and two columns are ignored because they are being phased out. I can run the stored procedure and preview the data in the DAL but when I create a page with an ODS linked to the DAL and a GridView I get this error. I checked every column that does not allow nulls and they all have values. I checked unique columns (ID is the only unique and is Identity=Yes in the table definition). I checked foreign-key columns for values that are not in the foreign table and there are none. Any ideas why do I get this? Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
Hi, I am getting the above error when trying to load a report into my Web Application, I have tracked the error down to one specific field in my database. Even though this field is a NVarChar field and is of size 30 it would seem that there is an issue returning the value from the field. I can write it into the database no problems but when I try to get it out of the database it returns the above error. e.g MOB 401.908.804 - Fails 0401.907.324 - okay 8239 9082 (pager) - fails Anyone got an idea on how to fix this???? Regards.. Peter.
Is it possible to have a corruption in one of the columns in a table at a specific row? If yes, what could be the possible causes for such corruption? Why I am asking? Because it happened that I wasn't able to read the value of a specific column but I was able to update it with other value! it sounds weird if you are able to write but not to read!! **I wasn't able to read but after I updated it with another value...it was readable... Please advise...??
(I AM REPOSTING THIS IN SQL FROM VB LANGUAGE BECAUSE I RECEIVED NO RESPONSES...ANY HELP WOULD BE APPRECIATED)
This is for a telephony application using Last In Wins concurrency...
Basically, we want to update only the columns that have changed in a row, not the entire row with the few changed columns. We are using TableAdapters exclusively.
We need concurrency down to the column level...that is to say, if a concurrency exception is thrown, we don't just want the new row to be UPDATED and overwrite the old row in the datasource...we only want the new columns to be UPDATED to the datasource...consider the following sequence...
- Fill table A and table B identically
- change column1 value in table A
- Update table A
- change column2 value in table B
- Update table B - this will generate a concurrency error.
Most of the help centers around using the merge command, to update table B, but in the situation above, we would lose the change to column1 (which we don't want to loose). Merge preserves the changes in table B (column2), but it would have the old value for column1, thus we would loose the value in column 1 when we updated the new merged table.
So, we need a way to build a datatable made up of the values in the datasouce plus the changes in the table B, which we will then update.
I have played endlessly with for/next type of constructs to build the new table of new column values using row version data, but I cannot get it to happen.
Upon exception for UPDATING table B, how do we build a temporary table from the datasource (so we get the change to column 1) and the new changes in table B, so we can update the temporary table and not loose any data.
Hey, I have 3 columns in a table Ex: Select Column1,Column2,Column from TableName
No. 1. Person A should have permission to read values only (Column1, Column3 of the table) - 2. Person B, should have permission to read only (Column 2).
Here my question is , I have to write one single stored procedure to statisfy both conditions. Which means, if person A execute this stored procedure , he shoud get only column 1 & 3 values . similarly other person b should get column 2 value. Ex: Column 1 - Empid Column 2 - SSN (Only for Top user display) Column 3 - Join Date Person A & B as a SQL or Windows login
I am trying to find the best way to implement a complex column based security. Basically i need to secure data among lot of tables with millions of rows, so that some users may see some columns and others see diferent ones... Whats the best way to implement this kind of procedure securely and flexible?
With Item as( Select 1 as ItemId,'ItemName1' as ItemName,100 as position union all Select 2 as ItemId,'ItemName2' as ItemName,200 as position union all Select 3 as ItemId,'ItemName3' as ItemName,300 as position union all Select 4 as ItemId,'ItemName4' as ItemName,400 as position union all Select 5 as ItemId,'ItemName5' as ItemName,500 as position union all Select 6 as ItemId,'ItemName6' as ItemName,600 as position union all Select 7 as ItemId,'ItemName7' as ItemName,700 as position),
Mapping as ( Select 1 as Parent, 2 as child union all Select 1 as Parent, 3 as child union all Select 1 as Parent, 4 as child union all Select 5 as Parent, 6 as child union all Select 5 as Parent, 7 as child )Expected Result:
When I try to execute a package I get this following error. I have a bunch of similar packages which runs fine on the same source(sybase) and destination(sqlserver 2005), just different tables. Only few of them fails and all of them have the same error of "Unable to resolve column level collations. Bulk-copy cannot continue". I checked for the dtatatype and length between source and destination, both are same. The user have all the required rights on the objects in both source and destination.
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to resolve column level collations. Bulk-copy cannot continue.".
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: Failed to open a fastload rowset for "testdb..tbl2". Check that the object exists in the database.
On further trial and error I found that if I remove the fast load option, it works without glitch.
Hi, I have developed a Smart Device Application using VB.NET 2003 on top of Windows Mobile 2003 platform which is running on Symbol PPT 8846, a model used in our construction sites for gathering data bar-coded on Pipes, trucks...etc. This application uses SQL CE as its back-end database and since its difficult to cover our sites with WI-FI coverage, most of the time the application is running offline. As for that, on a daily basis, synchronization is done in the offices before the system is released into sites. I have been doing several implementations in several projects and till now everything is running fine but until I got a native exception on exporting the data back into the server database. The server database or main database is using Fox Pro and this is what led me to build a Web Service for communication between SQL CE and Fox Pro. Anyways, going back to that mysterious exception, I did some of my intensive debug on what caused it. As a start, I went through the code line by line to check if there was a memory leak from P/Invokes; but I was shocked that the exception was raised from "SQLCEDataAdapter.Fill(Dataset)" function. First thought came to mind was to hard reset and to re-install the application but no luck. Since it was about SQLCE, I moved towards checking the data found in the SQLCE database and tried to de-fragment the data found under the table needed for export. What I mean by de-fragmenting is that I grouped the data under different classes or families and created different tables depending on these classes. Then tried exporting again and Bingo the data was exported except one record...That was so weird!! huh? (The database had 515 records) So I went for the Query Analyzer to try to view that record but no hope, it was taking too long to view the record, I just left it for more than 15 minutes and still no response from the Analyzer...it was so strange since there was only one record in that table!!!?!!! As going further, I was so curious why this record causing that native and inability to be viewed by the Analyzer?? I did testing on the column-level of that record and found out that you can view all the columns except two : nvarchar(200) and nvarchar (20)....I was able to resolve this problem with an SQL statement to update both columns with empty or dummy values...!!!!! But after this resolution, I really don't know how it could be controlled later on!! and why it happened?? I would be glad if anyone can give me any advise on why such problem popped out and especially on the column-level in SQL CE?? And how to overcome it?
Thanks for your time and effort,
Looking forward to hear from as soon as possible...
Please provide me a column-level merge replication in SQL Server 2005. In the SQL Sever books online, I could able to get very few informaiton about this topic. Please provide me a best practices for using this kind of conflict resolution in Merge-replication.
I have to implement a complex algorithm that processes each row and each column.I have bunch complex steps at the end of which a table gets populated with the processed columns.
My question is as to what is the best way to do this?CLR integration or T-SQL? Also I would appreciate any ideas as to how to go about using either approaches.
One of the steps of processing involved per column is as follows:- 1)For each column,find successive invalid values from the start of the column.Invalid value= any value <0 2)find the invalid value depth of each column(no of successive invalid values from start) 3)If after these invalid vlaues,there is a valid value and another invalid value,replace current invalid value with valid value. ie replace invalid value only if it has a valid value above it. 4)find the column with maximum invalue value depth and delete that many rows from the table.
Here's am example:- Suppose there are 2 columsn colA and ColB.The columns have different datatypes eg decimal,int,string etc. For simplicity colA and colB are ints. RowID is keeping track of the row number.
Step1)successive invalid values from start=0,-5,-3 Step2)Invalid value depth=3(because there are 3 rows from step 1) Step3)0,-5,-3 do not have any valid value above them.But -9 has a valid value 4 above it.So replace -9 with 4.
so colA after the algo will look as follows RowID ColA ------------ 1 0 2 -5 3 -3 4 1 5 4 6 4(replaced -9 with 4) 7 5 8 8
Now do the next column colB RowID ColA ------------ 1 -6 2 0 3 0 4 -7 5 4 6 8 7 -5 8 -8
Step1)successive invalid values from start=-6,0,0,-7 Step2)depth of invalid values=4 Step3)Next invalid value -5 occurs at RowID 7 and has a valid value 8 above it. Replace -5 with previous valid vlaue ie 8.
RowID 8 has invalid value -8 .Its previous invalid value(-5) got replaced by a valid value 8.So replace RowID8 also with value of RowID 7 ie 8
Output at the end of these steps RowID ColA ------------ 1 -6 2 0 3 0 4 -7 5 4 6 8 7 8(replaced -5 with 8) 8 8(replaced -8 with 8)
Step4:Get the maximum invalid value depth In this case ColB had depth=4 which is greater than ColA which had dept=3.so delete 4 rows from the beginning of the table So the table will be
RowID colA colB ---------------------------------------- 5 4 4 6 4(replaced -9 with 4) 8 7 5 8 (replaced -5 with 8) 8 8 8(replaced -8 with 8)
I need to create a function that will check data inputted by a user into a column anc check for special charaters. If any of these exist then block the insert.
I have visual studio 2005 with XP home. I'm trying to find out which service pack my SQL server is running as I've been told that Service pack 2 has a few bugs.
For example ADO Applications Report a Syntax Error When Column Names Contain Spaces. This appears to be an error i am recieving. I just wanted to confirm that it is a microsoft bug rather then something Im doing wrong. Microsoft meantions the bug here: http://support.microsoft.com/kb/264687
I've been looking everywhere. I do not have the "query analyzer window" where you can find this out as I have VS 2005. How would I find this out.
When I try to execute a package I get this following error. I have a bunch of similar packages which runs fine on the same source(sybase) and destination(sqlserver 2005), just different tables. Only few of them fails and all of them have the same error of "Unable to resolve column level collations. Bulk-copy cannot continue". I checked for the dtatatype and length between source and destination, both are same. The user have all the required rights on the objects in both source and destination.
Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Unable to resolve column level collations. Bulk-copy cannot continue.". Error at Data Flow Task For Test1 - test_tbl_job [OLE DB Destination [16]]: Failed to open a fastload rowset for "testdb..tbl2". Check that the object exists in the database.
I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!' CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs' CREATE SYMMETRIC KEY ERKEY WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
I have 2 higher level column groupings of month name and year above my actual date groups. It looks a little weird aligning them left but there is no guarantee that centering them will even allow them to show until I've scrolled right to the middle of the cell width that they occupy.
Is there a feature that comes with, or a well known trick for making them center in the area that is being viewed instead of the potentially very wide cell that they occupy?
When you utilize transactions in ADO.NET are the locks put on the entire TABLE used or at the row level? For instance if you do a SELECT within a transaction if you only pull 5 rows out of a 1000 row table can you just make it lock the rows that have been pulled? It seems like it locks the entire table?
Hi, Can anybody please explain me, what is low level and high level locking in SQL Server 2005 database. Also what is the name of process which converts low level locking into high level locking and vise versa. -Sanjeev
