Database

11-Apr-07 23:30

how do we count stored proc in database
how do we count system difined stored proc and userdifined stored proc

gauthee12-Apr-07 0:11

gauthee

12-Apr-07 0:11

The stored procedures in sql are objects so whenever a stored procedure is created its information is stored in the system table sysobjects, and as it is a sp the type would be 'p'

Thus to know the number of stored procedures for a database the query would be

select count(*) from sysobjects where type='p'

Gautham

Shuaib wasif khan12-Apr-07 0:32

12-Apr-07 0:32

thanks but now i have another problem
in a database i do i find how many feilds name=id in database

gauthee12-Apr-07 0:46

gauthee

12-Apr-07 0:46

I didnt get that???

Gautham

Shuaib wasif khan12-Apr-07 0:51

12-Apr-07 0:51

i mean that in a database there are three table
emp1
emp2
emp3
all table have a column name id
how to count number of table with column id Smile | :)

gauthee12-Apr-07 1:06

gauthee

12-Apr-07 1:06

select * from sysobjects t1 join syscolumns t2 on t1.id=t2.id where t2.name="columnname"

i guess, not sure though!

Gautham

Using interopt object, get incorrect cell's formated

Elina Blank12-Apr-07 4:05

Elina Blank

12-Apr-07 4:05

This will show all tables where there is a column named id

SELECT TABLE_NAME
FROM YourDataBase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Id';

Here you are counting how many such cases are in your db.

SELECT COUNT(COLUMN_NAME)
FROM YourDataBase.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'Id'

;

Note: This is correct for SQL 2005

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

kokain11-Apr-07 22:44

kokain

11-Apr-07 22:44

Hi all,

I'm using interopt object to handle data in an excel file, like that:

Excel.Application ExcelObj = new Excel.Application();<br />
<br />
Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(FilePath, 0, true, 5,<br />
"", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null);<br />
<br />
Excel.Sheets sheets = theWorkbook.Worksheets;<br />
Excel.Worksheet worksheet = (Excel.Worksheet)sheets.get_Item(SheetIndex);<br />
<br />
Excel.Range range = worksheet.get_Range(BeginCol + BeginRow.ToString(), EndCol + BeginRow.ToString());<br />
System.Array myvalues = (System.Array)range.Cells.Value2;<br />

With cells formated as DateTime datatype, this method didn't get the correct format...Ex: 04/11/2007, it'll get as 39183.00...

How can I get the correct value? Please help me.

Thank you.

sqlserver database role operations...

Member 387988111-Apr-07 21:31

Member 3879881

11-Apr-07 21:31

Hi i have doubt in roles using in vb.net...,
in my sqlserver for a paricular database,i created two application roles..and i given permission for those roles also..,
Now how to tell the roles in vb.net..,
(i.e) how to interact with those roles from vb.net codes...,
I dont know how to interact with those roles...,
so plz give me some idea..,if any sample codes will be there...,
it will be more usefull for me...,

Magi

datetime problem

nikhil123411-Apr-07 21:19

nikhil1234

11-Apr-07 21:19

i have one column datatype as datetime
i want to reaplce year of all date to 2030

can i use repalce and left functionbut its showing error plz help

Re: datetime problem

Krish - KP11-Apr-07 21:33

Krish - KP

11-Apr-07 21:33

REPLACE and LEFT functions take character expressions.
hope you might have used datetime data directly in the functions.

convert datatime to character and then use it in replace & left

Regards
KP

Re: datetime problem

WoutL11-Apr-07 23:20

WoutL

11-Apr-07 23:20

You could use:
Column = DateAdd(yy, 2030 - Year(Column), Column)

Wout Louwers

How to synchronize local server with remote server using SQL SERVER 2005

zxc8911-Apr-07 19:33

zxc89

11-Apr-07 19:33

Can any let me know how to synchronize local server with remote server using SQL SERVER 2005.

SQL Server 2005 will be installed on two PC's. Now i need to synchronize these two PC's.
Which version of SQL Server 2005 should be used, as i use the SQL server 2005 Express edition!

Thanks!!

To calculate no of sundays in a month

ahmedalisha11-Apr-07 19:30

ahmedalisha

11-Apr-07 19:30

Hi,
i need to calculate no of sundays in a month,is there any function in sql server/crystal reports(crystal syntax) to calculate the same? very urgent requirement.

Confused | :confused:

Re: To calculate no of sundays in a month

Shuaib wasif khan11-Apr-07 23:41

Re: To calculate no of sundays in a month

11-Apr-07 23:41

use datename(dw,date) and use for loop to increment the day value
and count where dw=sunday
i hope this will be help full for you Laugh | :laugh:

Krish - KP13-Apr-07 2:02

Krish - KP

13-Apr-07 2:02

hope this is what you are looking for

declare @mth varchar(4), @yr varchar(4), @dt datetime
set @mth = '4'
set @yr = '2007'
set @dt = convert(datetime, '01-' + @mth + '-' + @yr, 103)
-- to get first sunday of the month
select cast(case when ((7 - datepart(dw, @dt)) + 2) = 8 then 1 else ((7 - datepart(dw, @dt)) + 2) end as varchar(5)) + '-' + @mth + '-' + @yr
-- to get number of sundays
select case when (21 + (case when ((7 - datepart(dw, @dt)) + 2) = 8 then 1 else ((7 - datepart(dw, @dt)) + 2) end)) between 24 and 28 then 4 else 5 end

Regards
KP

Re: To calculate no of sundays in a month

ahmedalisha13-Apr-07 2:54

ahmedalisha

13-Apr-07 2:54

hi kp,

thanq u very much,exactly iam searching for the same.
once again thanquuuuu. Big Grin | :-D

can connet to sql 2005 from VS 2005 express C#,C+ [modified]

mhp13011-Apr-07 15:38

mhp130

11-Apr-07 15:38

Hi,

I have installed VS 2005 express for C#,C++ and SQL 2005 express. I am having problem to connect to SQL express database from C# and C+

From IDE, data connect -> Add Connection -> choose Data Source -> Microsoft SQL Database File -> Browse -> Select say model.mdf(or any .mdf in the Data directoy)

I always get :

Unable to open "c:\program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf". Operatiing System error 32:"32The process cannot access the file beacuse it is being used by another process.)"
An attempt to attach an auto-named database for file "c:\program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\model.mdf" failed. A database with the same name exists,or specified file can not be opened, or is is located on UNC share.

I can access the same database from Microsoft SQL Server Management studio Express. I have also SQL 2000 installed inb the same machine but I don't think that matter - they have different directory,

Can anyone help?

Thanks

-- modified at 22:58 Wednesday 11th April, 2007

Re: can connet to sql 2005 from VS 2005 express C#,C+

Jerry Hammond11-Apr-07 17:47

Jerry Hammond

11-Apr-07 17:47

"file:///c:/program%20Files/Microsoft%20SQL%20Server/MSSQL.2/MSSQL/Data/model.mdf"

“Some have an idea that the reason we in this country discard things so readily is because we have so much. The facts are exactly opposite - the reason we have so much is simply because we discard things so readily. We replace the old in return for something that will serve us better.”--Alfred P. Sloan

encryption in MySQL

shabonaa11-Apr-07 9:38

shabonaa

11-Apr-07 9:38

i want to encrypt the password fied in my database.
i use MySQL Server
is there anyway to do it by the database or i should make it in my code?

Dave Kreskowiak11-Apr-07 10:06

Dave Kreskowiak

11-Apr-07 10:06

It should be done in your code. Add some salt, encrypt it, then store the encrypted byte stream in the database. When you want to check a users password, add the same salt to the attempted password, encrypt it the same way, then retrieve the password from the database and compare the two byte streams.

Dave Kreskowiak
Microsoft MVP
Visual Developer - Visual Basic
2006, 2007

neon198111-Apr-07 10:07

neon1981

11-Apr-07 10:07

of course it should be done in the code...
for example md5() function in php...
and then it's easy to check passwords during authorization...

shabonaa11-Apr-07 21:14

shabonaa

11-Apr-07 21:14

is there any equifilant function in C#