creating hyperlink in SQL Server2005 - Database Discussion Boards

Re: how to reduce the log file of a large database?

scottgp17-Mar-09 14:28

17-Mar-09 14:28

Do you know what recovery model you are using (simple vs full)? Are you backing up the database? What version of SQL Server is it?
Scott

Re: how to reduce the log file of a large database?

Aman786Singh17-Mar-09 19:41

Aman786Singh

17-Mar-09 19:41

run this if you are working in sql 2005 server.

but for this u need to make your database offline first and then run this code.

--INF: How to Shrink the SQL Server 7.0 Transaction Log
-- SQL7 http://support.microsoft.com/support/kb/articles/q256/6/50.asp?id=256650&SD
-- SQL7 http://www.support.microsoft.com/kb/256650
-- SQL2000 http://support.microsoft.com/kb/272318/en-us
-- SQL2005 http://support.microsoft.com/kb/907511/en-us
-- select db_name()
-- select * from sysfiles

-- THIS SCRIPT IS NOT INTENDED FOR SCHEDULED USAGE !! READ BOL and the urls !!

SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT

-- *** MAKE SURE TO CHANGE THE NEXT 3 LINES WITH YOUR CRITERIA. ***

SELECT @LogicalFileName = 'Test_Log', -- Use sp_helpfile to identify the logical file name that you want to shrink.
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 305 -- in MB

-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size -- in 8K pages
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)

-- Wrap log and truncate it.
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
-- Try an initial shrink.
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) -- the log has not shrunk
AND (@OriginalSize * 8 /1024) > @NewSize -- The value passed in for new size is smaller than the current size.
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') -- Because it is a char field it inserts 8000 bytes.
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END -- update
EXEC (@TruncLog) -- See if a trunc of the log shrinks it.
END -- outer loop
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
PRINT '*** Perform a full database backup ***'
SET NOCOUNT OFF

Re: how to reduce the log file of a large database?

ma.amer18-Mar-09 2:01

ma.amer

18-Mar-09 2:01

thanks for all,,, Smile | :)

i'am sorry the sql server recovery model is full and its version is sql server 2000
i have to reduce the size of this log file
when i deleted it the database don't work, it is suspect and didn't work

Checking positive number

Pankaj Garg17-Mar-09 8:40

Pankaj Garg

17-Mar-09 8:40

How should i check the positive value in sql server 2005 except the following?

declare @f varchar(2000)
set @f = '788'
print charindex( '-' , @f )

Re: Checking positive number

Rob Graham18-Mar-09 2:19

Rob Graham

18-Mar-09 2:19

It would be nice if we could actually understand what you are trying to accomplish.

Re: Checking positive number

Eddy Vluggen18-Mar-09 2:38

Eddy Vluggen

18-Mar-09 2:38

Check if it's larger than 0 Smile | :)

DECLARE @f INT
SET @f = 788

IF @f > 0
BEGIN
    PRINT 'It is positively positive!'
END
ELSE
BEGIN
    PRINT 'That''s A negative there, Toto'
END

I are troll Smile | :)

Granting rights

Aman786Singh17-Mar-09 2:23

Aman786Singh

17-Mar-09 2:23

i am using SQL 2000 server. i have created one user .
i want that this user have following rights:-

Select,Insert,Update,Delete on Tables. so i gave the user as DDLAdmin,Read acess and Write Excess.

this is ok with my tables part. but for Stored Procedure i got a problem.

I want that my user can execute and modify the Stored Procedure.

For the execution part i ran Grant exec on <sp_name> to <user_name>.
but please tell me How should i give the user rights to modify the procedure???

I don't want to make user as DB_Owner.

Kindly suggest any solution.

Thanks in Advance.

creating hyperlink in SQL Server2005

Saleem Tahiri17-Mar-09 0:51

Saleem Tahiri

17-Mar-09 0:51

I had a project in MS access 2003 now I converted that to SQL server where there is some fields
in each table which has the hyperlink link to a scanned pdf file. while converting the files
the database is running very well but I can not get to my scanned files and the hyperlink is not coming in SQL server to click to get to the specified scanned files.just the path is coming in text format only

could you anyone help me.

Re: creating hyperlink in SQL Server2005

Ashfield17-Mar-09 2:21

Ashfield

17-Mar-09 2:21

Saleem Tahiri wrote:
just the path is coming in text format only

Because that is what is stored. You cannot (as far as I know) store a hyperlink in sql, just text so you need to build the hyperlink when you retrieve the data.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: creating hyperlink in SQL Server2005

ed575627-Mar-09 6:01

ed5756

27-Mar-09 6:01

If you are still using the Access front-end, just pass the text as a parameter to the VBA FollowHyperlink function and it will open.

Issue with VS 2005

p_196016-Mar-09 22:39

p_1960

16-Mar-09 22:39

Hi,
When i installed Rational clearcasetool and restarted system..and when im trying to build my vc++ application in VS 2005 im able to see the below error..

error result returned from 'cl.exe'.

Please help me out..

Re: Issue with VS 2005

itsravie16-Mar-09 23:38

itsravie

16-Mar-09 23:38

This error is due to improper setup of VS or OS update problem.

It also comes when u install windows update patch KB925902. try uninstalling, if u hav one.

Ravie Busie
Coding is my birth-right and bugs are part of feature my code has!

ms access connection string

vishakpb16-Mar-09 21:32

vishakpb

16-Mar-09 21:32

plz giv me the connection string to connect ms access database in asp.net web page

Re: ms access connection string

Eddy Vluggen16-Mar-09 21:55

Eddy Vluggen

16-Mar-09 21:55

Look here[^] for connection-strings.

plz = Please
giv = give

I are troll Smile | :)

Re: ms access connection string

itsravie16-Mar-09 23:41

itsravie

16-Mar-09 23:41

Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath(".\northwind.mdb") & ";"

Ravie Busie
Coding is my birth-right and bugs are part of feature my code has!

saving data

neoghy16-Mar-09 4:08

neoghy

16-Mar-09 4:08

Hi everybody

Actually i created a table in sql+, and inserting data using sql+ itself.But after i log out off the screen and log in again i see the data gone. How can i prevent the data from getting vanished without writing commit or set autocommit commands on everytime.

skd

For Oracle

David Mujica16-Mar-09 4:57

David Mujica

16-Mar-09 4:57

During interactive usage with sqlplus, Oracle also supports an AUTOCOMMIT option. With this option set to ON each individual SQL statement is treated as a transaction an will be automatically commited right after it is executed. A user can change the AUTOCOMMIT option by typing
SET AUTOCOMMIT ON or
SET AUTOCOMMIT OFF

whereas by typing
SHOW ALL
a user can see the current setting for the option (including other ones).

This was taken from the website:

http://infolab.stanford.edu/~ullman/fcdb/oracle/or-nonstandard.html[^]

Re: saving data

RussellT5-Jun-09 10:07

RussellT

5-Jun-09 10:07

neoghy wrote:
How can i prevent the data from getting vanished without writing commit or set autocommit commands on everytime.

This is like asking how I can eat my cake without opening my mouth.

Cheers,
Russ

Sql Restore Error

kankeyan16-Mar-09 3:53

kankeyan

16-Mar-09 3:53

Dear Friends,
Client database is in the form of "database.Dat" they don't which version they are using, i want to restore it i sqlserver 2000 with service pack 4
Following error occured

Error:3169 The backed up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database. Restore filelist is terminating abnormally.

Please give a solution

Thanks & Regards

JK

Re: Sql Restore Error

RyanMorris16-Mar-09 6:02

RyanMorris

16-Mar-09 6:02

Hi JK,

It looks to me like you client is using SQL 2005, which isn't backwards compatible with SQL 2000.

Ryan

Re: Sql Restore Error

kankeyan16-Mar-09 19:22

kankeyan

16-Mar-09 19:22

is it possible to convert sql 2005 database to sql 2000 sp4

Re: Sql Restore Error

RyanMorris16-Mar-09 22:57

RyanMorris

16-Mar-09 22:57

Just using the file you have, not as far as I know. If you had access to the database, I'm told that you could export the data from SQL 2005 and then import it into SQL 2000, but I haven't done this before and so I can't really tell you exactly how to go about it.

When MSAccess Readonly database with password is opened error 0xfffff88f "Not a valid password". Who knows why?

Grebeshkova16-Mar-09 0:48

Grebeshkova

16-Mar-09 0:48

For open database *.mdb I use ADO-construction
try
{
hr = m_pConnection->Open(_bstr_t(lpstrConnection), _bstr_t(lpstrUserID),
_bstr_t(lpstrPassword), NULL);
return hr == S_OK;
}
catch(_com_error &e)
{
}
When mdb-file has not attribute "ReadOnly", Open returns S_OK.
But when I set this attribute, exception is generated with
ErrorNumber = 0x80004005 and NativeError = 0xfffff88f ("Not a valid password").
I don't undenstand why. Pls., explain me this situation.

And other question: what are error codes concerned with error password (for ODBC and SQL-Server)? Or in other words: How may I detected that in connection string invalid password for opened database?

Thanks

SQL Question

Lars Fisker15-Mar-09 13:51

Lars Fisker

15-Mar-09 13:51

Can someone answer why the following isnt returning anything?

string strGetPeriodeSQL = String.Format("SELECT * FROM {0} WHERE SDate BETWEEN {1} AND {2} ORDER BY SDate", strTableName, dtStart.ToShortDateString(), dtEnd.ToShortDateString());

I left out the rest of the funktion, just the SQL part is here.

Lars

Resistance is futile - Data (ST:First Contact)

Re: SQL Question

Mycroft Holmes15-Mar-09 14:05

Mycroft Holmes

15-Mar-09 14:05

Are you saying strGetPeriodeSQL does not get populated?
Does the SQL string not execute?
Is no data returned from SQL?

I suspect the latter so:
Take the content of strGetPeriodeSQL to Management Studio and see what the results are.

If you get results then there is a problem with your connection.

Never underestimate the power of human stupidity
RAH

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