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.
Any help will be appreciated!
Paul
query that checks to see if a variable contains special characters; except for hyphens, periods, and accents?
View 3 Replies View RelatedI need to encrypt some column level data in multiple tables in SQL server 2014. I've never tried encryption in SQL server 2014. How can I achieve it?
View 4 Replies View RelatedI 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 was using an Hp ex470 single disk WHS when I started to recieve the data corruption bug. (http://www.wegotserved.co.uk/forums/index.php?showtopic=1872).
The data entered into the corrupted machine will insert unrecognized characters(square boxes) into an ntext column. (example : Coder213[][] is having big[][] problems.) It looks like everytime a character return is hit in the application the [][] show up.
Since the system had only been up for a week, I decided to move everything onto a new server 2003 box.(I already had an instance of the database before the box...so no problems) I had the workers copy and paste the data from the old app into the new app via the web interface. Guess what? [][] on the new box. I thought the unrecognized charters were copied from the interface..but now even new data being entered in is showing up with [][]. What is going on? I'm hoping one of you admin gurus can tell me to change a setting and everything will be ok.
The app runs fine and nothing really seems to be bothered by the blocks..but I don't like them. ..makes me nervous
Should I be concerned?
I installed SQL 7.0 on a test server a few days ago. I want to test an application that currently runs on SQL 6.5. I have run a test database through the upgrade wizard without errors. Is there any way to see the compatibility level that the db is running on. I know I can use the sp_dbcmptlevel procedure to make it run at the 6.5 level. I want to see if it is running at that level currently, or if it is at the 7.0 level.
Any help would be appreciated.
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval
replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx
My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,
I am trying to create a inline function which is listed below.
USE [Northwind]
SET ANSI_NULLS ON
GO
CREATE FUNCTION newIdentity()
RETURNS TABLE
AS
RETURN
(SELECT ident_current('orders'))
GO
while executing this function in sql server 2005 my get this error
CREATE FUNCTION failed because a column name is not specified for column 1.
Pleae help me to fix this error
thanks
Purnima
Hi all, I am not over familiar with SQL, I am a VB programmer, simply I need to achieve the following within Enterprise Manager.
I have 2 tables, different designs, different number of rows, I simply need to check whether the contents of a column in the first table is in a column in the second table, just simply a table/column to table/column data check for the same data content.
Easy Peasy for you guys, any help would be appreciated.
Hi all--I've got a derived column transformation where I am adding a field called Import_Date. I'm telling it to add as a new column and use the function "GetDate()" to populate the field. When I run the package, it returns NULL as the data value for all rows. Any idea why this might be happening?
View 5 Replies View RelatedI am using SQL Server 2008. In one of my table, one column has values like
"MFY RLHH CSQÉ"
"Aamj Gxmolwn Slf Yytrzgan Hiwd Fnlmyw"
So to fetch the data having only special characters in it, I used below query
Select * From Table Where Column Like '%[^0-9a-zA-Z]%' Escape ' '. Its returning both the records. Here I would like to fetch records for those Unicode characters only which are not within 00201 - 0070E [URL].
I want to remove special characters from a string in sql like <?> in a column value in a table.
View 1 Replies View RelatedI have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.
View 5 Replies View RelatedHello, there,I am trying to find out if a table is used by any of the storedprocedures or functions.I can generate all the scripts and look for it. But is there an easyway?THXJohn
View 4 Replies View RelatedHi all,
My SQL database has varchar that store names with special caracter like "René". The problem is when I try to get the data back from the database, the .Read() command from the SqlDataReader object is not working. In extra, it does not Throw any error????????
Please help me,
JR
Im working on a db library and I have everything working, what Im wanting to do is though is add a method that can check a connection string to make sure it is actually working. Right now, I have it doing a simple select * query to a particular table and returning true or false if an exception is caused. But I want to make the library as generic as possible, so is there another way to test teh connection string and tell if its working or not?Thanks,Cedric
View 1 Replies View RelatedThis function, F_TEMP_TABLE_EXISTS, checks for the existence of a temp table (## name or # name), and returns a 1 if it exists, and returns a 0 if it doesn't exist.
The script creates the function and tests it. The expected test results are also included.
This was tested with SQL 2000 only.
if objectproperty(object_id('dbo.F_TEMP_TABLE_EXISTS'),'IsScalarFunction') = 1
begin drop function dbo.F_TEMP_TABLE_EXISTS end
go
create function dbo.F_TEMP_TABLE_EXISTS
( @temp_table_name sysname )
returns int
as
/*
Function: F_TEMP_TABLE_EXISTS
Checks for the existence of a temp table
(## name or # name), and returns a 1 if
it exists, and returns a 0 if it doesn't exist.
*/
begin
if exists (
select *
from
tempdb.dbo.sysobjects o
where
o.xtype in ('U')and
o.id = object_id( N'tempdb..'+@temp_table_name )
)
begin return 1 end
return 0
end
go
print 'Create temp tables for testing'
create table #temp (x int)
go
create table ##temp2 (x int)
go
print 'Test if temp tables exist'
select
[Table Exists] = dbo.F_TEMP_TABLE_EXISTS ( NM ),
[Table Name] = NM
from
(
select nm = '#temp' union all
select nm = '##temp2' union all
select nm = '##temp' union all
select nm = '#temp2'
) a
print 'Check if table #temp exists'
if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
print '#temp exists'
else
print '#temp does not exist'
print 'Check if table ##temp4 exists'
if dbo.F_TEMP_TABLE_EXISTS ( '##temp4' ) = 1
print '##temp4 exists'
else
print '##temp4 does not exist'
go
-- Drop temp tables used for testing,
-- after using function F_TEMP_TABLE_EXISTS
-- to check if they exist.
if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1
begin
print 'drop table #temp'
drop table #temp
end
if dbo.F_TEMP_TABLE_EXISTS ( '##temp2' ) = 1
begin
print 'drop table ##temp2'
drop table ##temp2
end
Test Results:
Create temp tables for testing
Test if temp tables exist
Table Exists Table Name
------------ ----------
1 #temp
1 ##temp2
0 ##temp
0 #temp2
(4 row(s) affected)
Check if table #temp exists
#temp exists
Check if table ##temp4 exists
##temp4 does not exist
drop table #temp
drop table ##temp2
CODO ERGO SUM
Hi all,
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
Hello,
I am running this query
"delete from ims_domains where id=61"
and got the error
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
Please let me know what should be the reason?
Thanks,
Ravi
Hi,
I face this error when i try to run my store procedure.
The sample of store procedure as following:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_addUserAccess
with encryption
AS
SET NOCOUNT ON
DECLARE @COUNTER INT
SET @COUNTER = 0
DECLARE @i_compId INT
BEGIN
DECLARE C1 SCROLL CURSOR FOR
SELECT i_compId
FROM ltd_cms_company WHERE (i_owner = 176 or i_owner = 268) AND ti_recStatus = 1
END
OPEN C1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ltd_cms_userAccess ( i_loginId, i_groupId, i_compId, ti_updComp, ti_updLog, ti_updAccess, ti_owner, ti_acctMgr, ti_updContact, ti_updEvent )
VALUES ( 124, 0, @i_compId, 1, 1, 1, 1, 1, 1, 1)
SET @COUNTER = @COUNTER + 1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
END
CLOSE C1
DEALLOCATE C1
SET NOCOUNT OFF
anyone can help me identify this error?
Thanks
Regards,
Jojomay
Hi all, I get this message when trying to update a tabel i have whichhas nested hierarchies.The current hierarchies beginning from root = 1 are up to the level 5.Before going into details and sample data with all the sql queries andprocedures, this limitation from Microsoft for nested levels .. isthere any way or trick to increase the level in generic?
View 1 Replies View RelatedI have created a delete trigger in Table1 and Table2. Once I delete a certain record in Table1 it will also delete that record in Table2 or vice versa. But once i delete certain record either in Table1 or Table2 it will create an error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". Can you help me on this?
View 4 Replies View RelatedHey everyone!
I'm doing an export from SQL into excel spreadsheet and then am going to clean out certain parts of the data with global search/replace. The problem is that the SQL data is full of special characters such as |'s and the little box looking characters.
How do I export without these characters?
I know its possible, I did it about 2 years ago and remember I did some crazy file conversion (make wk3 or something) but I no longer remember
Any help would be much appreciated!
Thanks,
Geoff
PS, attached is a screenshot of the data to give you an idea of what I'd like to strip!
I have a table with several columns of information that I wish to set up some form of schedule to go through this data and remove any special characters that may interfere with other code processes.
Mainly the coma's and the apostrophes. It really messes with my asp pages and scripts when retrieving this information and trying to do other things with it, so I need to figure out how to remove these from the tables so it does not cause these issues.
Knowing this, I cannot figure out how to keep the data in the row/column and just extract the special characters from that data. The other problem is, everything I try requires me to insert either a coma or apostrophe as part of the code string which in lies my issue.
How can I parse through my data, leave the data as-is, but just get rid of coma's, apostrophes, and double quotes?
Does anyone have a basic example that I can use to expand on?
When importing data into MS SQL Server 2000 from a MyODBC (v3.51) datasource using Data Transformation Services, special characters like öäüéàèare not imported correctly. However, when the MyODBC data source is used inany other program like Access, Excel etc. it works fine.Does anyone have any experience with this? Any hint to solve this problemwould be very much appreciated!Matthias Haldimann
View 1 Replies View RelatedThere is a ">" character (right-angle bracket) inside my SQL Server password. When I supplied this password to the bcp utility, the ">" character was treated as an output redirection symbol. So the password was truncated at ">" and the bcp output got sent to a file with a name consisting of the rest of the password after ">". I'm using SQL Server 2012. I cannot use Windows authentication due to company policy. Is there a way to resolve this without changing the password?
View 7 Replies View RelatedI have to figure out the items that Legal Name implies individual but Legal Entity Structure indicates a incorporation type. In this sample, you can see Alexander, Justin N. is my target. But my problem is how should I use a query to figure out which one is a individual's name? How should I write a function to check the name format (Last, First Middle)?
Legal Name ////////////////////////////////////// Legal_Entity_Struct
S & H Farm Supply, Ltd.////////////////////////////Company
F.M.Abbott Power Equipment,Co.///////////////Company
Ray's Dixie Chopper, Inc.////////////////////////// Company
Alexander, Justin N. ///////////////////////////////// Company
Alameda Power Equipment, Inc.//////////////// Company
[Code] .....
I have a source sql 2005 with the database collation SQL_Latin1_General_CP1_CI_AS and destination with sql 2012 with the same collation.
But the SQL server llvel collation is different, sql 2005 uses Latin1_General_CI_AI and sql 2012 uses "SQL_Latin1_General_CP1_CI_AS"
Now when i load the data from 2005  for one table to sql 2012 i could see special characters in one column. And i dont see that in the source database. Is there a way to avoid that or is it something we need to manually fix.
Hi, guys
I try to add some error check and transaction and rollback function on my insert stored procedure but I have an error "Error converting data type varchar to smalldatatime" if i don't use /*error check*/ code, everything went well and insert a row into contract table.
could you correct my code, if you know what is the problem?
thanks
My contract table DDL:
************************************************** ***
create table contract(
contractNum int identity(1,1) primary key,
contractDate smalldatetime not null,
tuition money not null,
studentId char(4) not null foreign key references student (studentId),
contactId int not null foreign key references contact (contactId)
);
My insert stored procedure is:
************************************************** *****
create proc sp_insert_new_contract
( @contractDate[smalldatetime],
@tuition [money],
@studentId[char](4),
@contactId[int])
as
if not exists (select studentid
from student
where studentid = @studentId)
begin
print 'studentid is not a valid id'
return -1
end
if not exists (select contactId
from contact
where contactId = @contactId)
begin
print 'contactid is not a valid id'
return -1
end
begin transaction
insert into contract
([contractDate],
[tuition],
[studentId],
[contactId])
values
(@contractDate,
@tuition,
@studentId,
@contactId)
/*Error Check */
if @@error !=0 or @@rowcount !=1
begin
rollback transaction
print ‘Insert is failed’
return -1
end
print ’New contract has been added’
commit transaction
return 0
go
Please consider enhancing SQL 2005 Reporting Services to provide the ability to disable Clickthrough Reports (Live Links) at the Report and Export Function level. Today you can only enable or disable Clickthrough Reports at the View/Model Level for all Report Builder reports.
View 1 Replies View RelatedHello all.
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.
tia,
Jeremy
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...??
Using VS05 pro SP1...
(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.
Thanks!
Bob