Database

Re: Defining stored procedure parameters

19-Aug-07 23:48

Yes you can used that.

(
@In_Out int out, --this parameter it can set and get data
@In int --this parameter it can set data just
)

Rami Abd Alhalim

Steven J Jowett19-Aug-07 23:51

Steven J Jowett

19-Aug-07 23:51

Simple when you know how.

Thanks

Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'

sorting fixed length file in sql server

S Palip19-Aug-07 23:35

S Palip

19-Aug-07 23:35

hi,

I'm having a fixed length file hvaing rows of data and some/all of the records can be grouped together using a rowid.

for ex:

xx1|uiy2jof7giu4|0012000|2007-06-29|AAX
xx3|uiy1jofh3iud|0012000|2007-06-29|AAX
xx4|uiygjofhgiud|0020100|2007-06-29|CUY
xx1|uiyg3vb4giud|0212000|2007-06-30|1HU
xx1|uiygjofhgiud|0002000|2007-06-21|ZXX
xx4|ui2ygj7fhgi9|0052000|2007-07-29|559

Is it possible to read the file into sql as xmldata type and sum a particular value in the row with same id and send to the front end.

Can any one help ?

Thanks and Rgds,

PLS

Steven J Jowett19-Aug-07 23:41

Steven J Jowett

19-Aug-07 23:41

I am no expert, but I believe you will need to look at DTS (Data Transformation Services)

Steve Jowett
-------------------------
Sometimes a man who deserves to be looked down upon because he is a fool, is only despised only because he is an 'I.T. Consultant'

Rami Said Abd Alhalim19-Aug-07 23:50

19-Aug-07 23:50

you want read XML file and saving on SQL server ??

S Palip19-Aug-07 23:55

S Palip

19-Aug-07 23:55

no the source file is a fixed length text file. the data should be read into sql server as xml datatype and sorted and a particular numeric field should be added there and returned to a dataset.

TomGarth21-Aug-07 13:47

TomGarth

21-Aug-07 13:47

There are better methods for diong this, but maybe this will help some.

***********************

declare @cmd varchar(300), @file varchar(255)
set @file = 'C:\temp.txt'
set @cmd = 'type ' + @file

create table #temp (f_All varchar(255))
create table #temp2 (f1 varchar(10), f2 varchar(50), f3 varchar(10), f4 datetime, f5 varchar (10) )

insert into #temp
EXEC master..xp_cmdshell @cmd

insert into #temp2
select
substring(f_all, 1, 3),
substring(f_all, 5, 12),
substring(f_all, 18, 7),
substring(f_all, 26, 10),
substring(f_all, 37, 3)
from #temp
where f_all is not null

select * from #temp2
-- GROUP, SORT, SUMMARIZE, AND USE IT HOWEVER YOU NEED IT.

drop table #temp
drop table #temp2

**********************

Tom Garth
Developer
R. L. Nelson and Associates, Inc., Virginia

Auto increment

mimimimilaw19-Aug-07 21:15

mimimimilaw

19-Aug-07 21:15

I am using auto increment for the primary key in a table. For example,
table - (userID, UserName). Now the auto increment of last userId = 10.
If I would like to insert a userId = 111, I found that the next auto increment userId must be in 112, but it is not 11.
Is there any setting to change back the UserId to 11?

Thanks! Smile | :)

Rami Said Abd Alhalim19-Aug-07 21:53

19-Aug-07 21:53

hi mimimimilaw
you must go to design table ,Set Identity Seed = 10 for column auto increment

mimimimilaw19-Aug-07 22:12

mimimimilaw

19-Aug-07 22:12

It maybe 10, 20, 45 or any number. How can I using mssql statement to do this? Smile | :)

Rami Said Abd Alhalim19-Aug-07 22:37

19-Aug-07 22:37

Hi mimimimilaw
You can use this statment

CHECKIDENT ('tablename',RESEED,10)

best Regard

Rami Abd Alhalim

Senu Gandhi19-Aug-07 22:30

Senu Gandhi

19-Aug-07 22:30

Hi,

Try this

DBCC CHECKIDENT ('tablename',RESEED,110)

If u execute the above statement then the next number will be 111.
But you cannot set it back to 10, because already 111 exists which is greater than 10.
May I know the situation why you need the userid to be 111?

Senthil

Rami Said Abd Alhalim19-Aug-07 22:39

19-Aug-07 22:39

If you need anything i am ready

RAmi

Re: Error in SQL Server 2005 [modified]

mimimimilaw20-Aug-07 21:19

mimimimilaw

20-Aug-07 21:19

Thanks All,
I try to do what I want in other methods.;)

Error in SQL Server 2005

ksaw12319-Aug-07 20:24

ksaw123

19-Aug-07 20:24

I got this error and I don;t know how to solve it even thought my parameters are matching.

'DHS_CostCenter' table saved successfully
'DHS_Employee' table
- Unable to create relationship 'FK_DHS_Employee_DHS_CostCenter'.
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_DHS_Employee_DHS_CostCenter". The conflict occurred in database "DHS2", table "dbo.DHS_CostCenter", column 'CostCenterID'.

User canceled out of save dialog
(MS Visual Database Tools).

Thanks for your help.

Blue_Boy19-Aug-07 20:47

Blue_Boy

19-Aug-07 20:47

Check datatypes fields in relations tables

I Love SQL

Re: Error in SQL Server 2005

ksaw12319-Aug-07 21:33

ksaw123

19-Aug-07 21:33

Thank you for your answer.

However, I am sure I am right unless I don't understand what do you mean.
This is what I have:
I have a table called DHS_CostCenter that has these columns:
1- CostCenterID (Primary Key) int Not Null.
2- CostCenterName varchar(70) Not Null.

The other table called DHS_Employee that has these columns:
1- BadgeNum int Not Null.
2- Name varchar(50) Not Null.
3-CostCenterID int Not Null.
4- DateFrom smalldatetime Not Null.
5- DateTo smalldatetime Not Null.
I made a relationship between them (the primary key is CostCenterID that is in DHS_table and the Foreign Key is in DHS_Employee).

After that when I want to save it, I got this error.

I am looking for an answer.

Re: Error in SQL Server 2005

Rami Said Abd Alhalim19-Aug-07 22:05