Re: Validation for Range of Values - Database Discussion Boards

Luka Grabarevic28-Mar-07 3:05

28-Mar-07 3:05

the problem is that I'm using this database via a WCF-Service.
so multiuser accessibility is the main problem.
If 2 users are saving their date at the same, who would this be solved with assigning the IDs?
so I've tryed this with MAX().

I really don't know how it would act with the IDs.
I would be grateful if someone could tell me if this could work like you say.

greetz

pdluke

Re: MAX () function with INSERT INTO?

andyharman28-Mar-07 5:01

andyharman

28-Mar-07 5:01

Accoring to this [^] mySQL support sequence numbers.
Regards
Andy

Re: MAX () function with INSERT INTO?

DQNOK28-Mar-07 7:41

DQNOK

28-Mar-07 7:41

I use the following construct in my system:

INSERT INTO myTable(ID, Employee, Project, Date, ...)
SELECT MAX(ID)+1, @Employee, @Project, @Date, ...
FROM myTable;

and it works fine.

Hope it helps.

Creating View in a Stored Procedure

gauthee27-Mar-07 21:57

gauthee

27-Mar-07 21:57

Hi,

Iam creating a stored procedure, in this stored procedure i want a view to be created whenever that store procedure is being executed,

Can i have a view created inside a stored procedure?

Thanks!

Gautham

Re: Creating View in a Stored Procedure

Pete O'Hanlon28-Mar-07 4:38

Pete O'Hanlon

28-Mar-07 4:38

Given it was running with the right permissions, you could. I don't see why you'd want to however. Given that you could accomplish the same through the use of temporary tables/in-memory tables, what are you trying to do here?

Deja View - the feeling that you've seen this post before.

Validation for Range of Values

Sophia Rekhi27-Mar-07 20:56

Sophia Rekhi

27-Mar-07 20:56

Hi all,

how to validate the range of values in a table.No duplicate entries are accept.

ex : In a table, having cards serial number from 10 to 20, but again i try to insert 11 to 19 serial number cards, it won't be accept.how to validate this type of range validation.

table fields are :
vndrCode :v001
serialFrom:10
SerialTo:20
IssueDate:23-mar-2007

pls help me.

Re: Validation for Range of Values

Krish - KP27-Mar-07 22:50

Krish - KP

27-Mar-07 22:50

to validate data before inserting to a column can be done using CHECK constraint.

but, you are looking for comparing data from multiple columns and then validate the same. I've not sure whether is possible using constraint. however you can write a TRIGGER for validating.

Regards
KP

Re: Validation for Range of Values

Tirthadip27-Mar-07 22:53

Tirthadip

27-Mar-07 22:53

I am posting the code.....please check if it works or not......here the logic is........
check whether duplicate record exist or not before inserting records in table.....
i am only posting the code to check the duplicate record in table.....
this SQL will find record if the number ,you are going to insert, exist in table between fromSerial and toSerial

Dim fS as Integer = 10 // Retrieve this value from wherever you want<br />
 Dim tS as Integer = 20<br />
 Dim myConnection As System.Data.OleDb.OleDbConnection<br />
 Dim ds As DataSet<br />
 Dim da As OleDbDataAdapter <br />
<br />
 Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" <br />
                        & myDatabaseath & ";Persist Security Info=False"	<br />
<br />
 Dim sqlDuplicate As String = "SELECT vndrCode  FROM myTable WHERE <br />
                        "serialFrom > " & fS & " " & _<br />
                        "And serialFrom < " & tS & "  " & _<br />
                        "Or serialTo > " & fS & "  " & _<br />
                        "And serialTo < " & tS & "  " & _<br />
                        "Or serialFrom < " & fS & "  " & _<br />
                        "And serialTo > " & tS & "  " & _<br />
                        "Or serialFrom < " & fS & "  " & _<br />
                        "And serialTo > " & tS & "  " & _<br />
                        "Or serialFrom < " & fS & "  " & _<br />
                        "And serialTo > " & fS & "  " & _<br />
                        "Or serialFrom < " & tS & "  " & _<br />
                        "And serialTo > " & tS & " )"<br />
<br />
 	<br />
        // Set up connection<br />
<br />
            If Not myConnection Is Nothing Then<br />
                If myConnection.State = ConnectionState.Open Then <br />
                myConnection.Close()<br />
                myConnection = New OleDbConnection(strConn)<br />
                myConnection.Open()<br />
            Else<br />
                myConnection = New OleDbConnection(strConn)<br />
                myConnection.Open()<br />
            End If<br />
       <br />
       // Bring records<br />
 <br />
            da = New OleDbDataAdapter(sqlDuplicate , myConnection)<br />
            ds = New DataSet<br />
            da.Fill(ds, "Tablename")<br />
<br />
        // Check if duplicate record exist       <br />
<br />
        If ds.Tables(0).Rows.Count = 0 Then : Return 0 //Duplicate Record doesn't exist<br />
        Else : Return 1 //Duplicate Record exists<br />
        End If

Last of all I dont think this code is very smart .....looking for better answer

Tirtha

"A man can ride on your back only when it is bent....."

SA authentication

gunanaresh27-Mar-07 20:20

gunanaresh

27-Mar-07 20:20

Can some one let me know what would be the best way to authenticate a client that runs on a different AD domain from the domain where the SQL server runs..
Is there some way to use Trusted connection in this scenario

NaReN

Re: SA authentication

kubben28-Mar-07 1:28

kubben

28-Mar-07 1:28

If you are talking about a web app, yes there is. You set the identity of the app pool your web app is running in as a domain user account that has the correct rights in sql server. Then your connection string for your web app can use trusted connection.

If you are talking about a windows app, I am not sure if it is possible or not.

Hope that helps.
Ben

urgent

sirig27-Mar-07 19:58

sirig

27-Mar-07 19:58

how to run a program in sqlserver automatically without human intervention

Re: urgent [modified]

Parwej Ahamad27-Mar-07 20:06

Parwej Ahamad

27-Mar-07 20:06

When u would to run and which kind of program.

Parwej Ahamad

g_parwez@rediffmail.com

Re: urgent

sirig27-Mar-07 20:48

sirig

27-Mar-07 20:48

My actual requirement is to send automatically mails regarding birthday wishes to employees by checking dob from database without human intervention

Re: urgent [modified]

Parwej Ahamad27-Mar-07 22:33

Parwej Ahamad

27-Mar-07 22:33

You can make a service
and Write there business login for sending mail

Start one time service on server.

Or Write and CLR stored procedure and Schedule in sql server job scheduler.

Parwej Ahamad
g_parwez@rediffmail.com

Re: urgent

Krish - KP27-Mar-07 23:01

Krish - KP

27-Mar-07 23:01

for Scheduled Jobs ...
in SQL Server Enterprise Manager
Managenemt -> SQL Server Agent - > Jobs.
To my knowledge it is possible to schedule and run SQL commands only.

for executing other applications on scheduled interavals ...
1. write an application and schedule it in windows scheduler so that it
executes on regular intervals
or
2. write a service and configure for automatic startup.

Regards
KP

Copying records between two tables in same database?

kbalias27-Mar-07 19:40

kbalias

27-Mar-07 19:40

Hi
In my database I have two tables with the same structure (columns, data types, etc).
How would I copy or move specific records from the one table to the other one using Transact-SQL in a stored procedure?

Thanks.

Kobus

Re: Copying records between two tables in same database?

sirig27-Mar-07 20:45

sirig

27-Mar-07 20:45

do u mean to copy the data from one table to another table ?or u want to move only specific records?

Re: Copying records between two tables in same database?

kbalias27-Mar-07 21:14

kbalias

27-Mar-07 21:14

I would just like to be able to copy/move specific records, for example those records where UserIndex = 123.

Kobus

Re: Copying records between two tables in same database?

Krish - KP27-Mar-07 22:35

Krish - KP

27-Mar-07 22:35

INSERT INTO tblA SELECT * FROM tblB WHERE userIndex = 123

Regards
KP

Re: Copying records between two tables in same database?

kbalias27-Mar-07 23:30

kbalias

27-Mar-07 23:30

Thanks KP

Exactly what I was looking for.

Kobus

Re: Copying records between two tables in same database?

Krish - KP28-Mar-07 0:23

Krish - KP

28-Mar-07 0:23

Regards
KP

Re: Copying records between two tables in same database?

Krish - KP27-Mar-07 22:22

Krish - KP

27-Mar-07 22:22

INSERT INTO <table1> SELECT * FROM <table2> [WHERE <condition>]

this will allow to select data from one table and insert into another.

Regards
KP

Small square in text file

brianjperry27-Mar-07 13:59

brianjperry

27-Mar-07 13:59

I get several EDI Files from vendors that have small square at the en of the file on the last line, if I don’t delete them before running my stored procedure I get unexpected end of file error. Anyone know how to make the procedure ignore this?

Brian

Re: Small square in text file

andyharman28-Mar-07 0:14

andyharman

28-Mar-07 0:14

Hi Brian

Most text editors show a small square to indicate that there is a non-printable control character.

If you can identify what the control-character is (I normally use a Unix hex dump) then you can use

REPLACE(MyColumn, CHAR(n), '')

to get rid of it (where "n" is the character number). The most likely control-characters are CHAR(0) [ASCII NUL] and CHAR(26) [ASCII EOF].

Hope that helps.

Re: Small square in text file

crisp196728-Mar-07 3:56

crisp1967

28-Mar-07 3:56

Thanks for the help but Im not sure how I use the replace statement in this content this is the code that fails due to the control character. Can I put the replace in with the bulk insert and how would I do this?
Thanks

declare @File varchar(1000)
declare @cmd varchar(2000)
declare @dealermark money

create table ##Import (prodnum varchar(20), lblabr varchar(4), lbl varchar(20), vendorname varchar(20), catnum varchar(20),config varchar(2),artist varchar(50),title varchar(50),mcat1 int,mcat2 int,mcat3 int,vidrate varchar(5),sellprice money,listprice money,UPC varchar(20),oldupc varchar(12),oldprod varchar(20),rlsdate datetime,disdate datetime, lastret datetime,specord varchar(1), modflag varchar(1), bioinfo varchar(1000))

select @File = @pFileName
select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (MAXERRORS = 1000, FIELDTERMINATOR=''","'''
select @cmd = @cmd + ',ROWTERMINATOR = ''' + char(10) + ''')'
truncate table ##Import
-- import the data
exec (@cmd)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.