Need to change column properties...

I have an MSDE database filled with data. I use Web Data Admin to config
the db. In my short-sightedness I defined a column of vchar as only 12
characters when now I need 15. However since there's data in the db, the
edit option is not available for the columns. What's the best way to go
about making the change I need?
hi Terry,
Terry Olsen wrote:
> I have an MSDE database filled with data. I use Web Data Admin to
> config the db. In my short-sightedness I defined a column of vchar
> as only 12 characters when now I need 15. However since there's data
> in the db, the edit option is not available for the columns. What's
> the best way to go about making the change I need?
http://msdn.microsoft.com/library/de...aa-az_3ied.asp
SET NOCOUNT ON
USE tempdb
GO
CREATE TABLE dbo.test (
ID int NOT NULL PRIMARY KEY ,
data varchar(12) NOT NULL
)
GO
INSERT INTO dbo.test VALUES ( 1 ,'Andrea789012' )
SELECT * FROM dbo.test
GO
ALTER TABLE dbo.test
ALTER COLUMN data varchar(15)
GO
UPDATE dbo.test
SET data = 'Andrea789012345'
WHERE ID = 1
SELECT * FROM dbo.test
GO
DROP TABLE dbo.test
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Need to access INFORMATION_SCHEMA on linked server

Hi,
I'm working with MSSQL2K+SP3a, Standard Edition. I defined linked server (MSDE).
On the local server, I can do
select * from [testDB].[INFORMATION_SCHEMA].[TABLES] -- local

select * from [testSRV].[testDB_far].[dbo].[sysobjects] -- linked

but not

select * from [testSRV].[testDB_far].[INFORMATION_SCHEMA].[TABLES]

How can I access the INFORMATION_SCHEMA on the linked server ?

Thanks,
Helenacreate a view in linkedserver on DB which u want to see the table information and access that view from ur local server.
Note: give permission if its required to access that view.

-- create view script--
create view dbo.vINFORMATION_SCHEMATABLES
as
SELECT *
FROM INFORMATION_SCHEMA.TABLES
go

-- select statement from local server
select * from [testSRV].[testDB_far].[dbo].[vINFORMATION_SCHEMATABLES]|||Cheat?EXECUTE [testSrv].[testDB_far].dbo.sp_executesql
N'SELECT * FROM INFORMATION_SCHEMA.TABLES'-PatP|||Thanks for the fast replay .
Mallier - I cant change anything in the remote db.
Pat - you solution works perfect .

Thanks to all
Helena

Need some help with MSDE 2000

I have to create an application using MSDE, I install the software on my pc
and it is working fine but my problem is that I have to store the database
on the J drive of my LAN and must be able to access if from my local PC or
any other pc connected to the Lan. When I put the database on my pc I can
access the the tables, but when I put the database on the J drive of the LAN
the MSDE client on my PC does not find the databases. Is there a way to tell
the MSDE client on my PC to go to the J drive on the lan and find the
databases? The data must be store on the J drive of the Lan. I would really
appreciate some help here as I am very new to MSDE. In ms access I just had
to set the path to the database and that was it.
thanks in advance.
On Sat, 12 Jun 2004 01:37:34 -0400, Patrick Blackman
<caribsoft@.videotron.ca> wrote:

> the MSDE client on my PC does not find the databases. Is there a way to
> tell the MSDE client on my PC to go to the J drive on the lan and find
> the
> databases?
You should install MSDE on the machine where it runs, not on another PC
into a mapped drive. That's just what my feeling tells me. There might ne
network installation guides on msdn.microsoft.com.
Furthermore, if you need to access MSDE over the net, make sure
authentication (hint: SECURITYMODE=SQL, for instance) is set up properly
as well as "firewalls" are disabled or at least let the appropriate ports
through (I can't recall the MSDE listening port).
HTH,
Stefan
Give a man fire and he is warm for a day.
Set him on fire and he is warm for the rest of his life.

Need some causes for database corruption.

We have been seeing a few of our systems using MSDE have database
corruption. These are unattended 24/7 systems, and we are not sure what is
going on.
I was under the impression that MSDE aka SQLServer was fairly fault
tolerant. Could someone explain some of the reasons for database
corruption?
An example of one of the errors that we have seen when this occurs is as
follows:
IO Error 23 "Data Error" Cyclic Redundancy Check Detected During Read at
Offset 0x00000c150000 in the file D:\sqslsrvdata\data\dvrevent40_data.mdf
Any useful insight on the cause of something like this would be very
helpful.
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
hi Ken,
Ken Varn wrote:
> We have been seeing a few of our systems using MSDE have database
> corruption. These are unattended 24/7 systems, and we are not sure
> what is going on.
SQL Server is usually as "tolerant" as your system is..
often this kind of problems is consequence of bad I/O activity, say hard
disk problem and the like...
did you check your disk(s) subsystem?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

NEED HELP! - Unable to connect to MSDE Database through Web Matrix Project

I'm new to asp.net and databases and I'm currently working my way through SAMs Teach Yourself ASP.
I am up to working with databases and have successfully installed MSDE using the sql2kdesksp3.exe file. However, when I try to create a new database through the web matrix project I get an 'unable to connect' error. I have tried removing and re-installing the database and I've also re-installed it using the latest file from the MS web site but I still get the same error. I've checked the obvious...that the msde service is running, password is correct, and that checks out ok.
Has anyone had a similar problem and resolved it?
Thanks.

Try this link and download the eval edition of SQL Server. Hope this helps.
http://www.microsoft.com/sql/evaluation/trial/default.mspx

|||Works a treat.
Many thanks!

Need help with xp_cmdshell and proxy accout

I am tracking down a problem using xp_cmdshell on SQLServer 2000 (MSDE). Th
e
DB user is not a sysadmin, but does have exec rights for xp_cmdshell. I als
o
have an agent proxy account (Win2K account) set which is a member of “User
s”
group. This scheme has been working fine until I made some changes recently.
The changes I made are: Applied Win2K SP4, applied a host of hotfixes, and
made several changes in user rights and other security-related settings.
I’ve been testing this from OSQL logged in as the same user that my
application uses when it attaches to SQLServer. I get this error:
“xpsql.cpp: Error 1385 from LogonUserW on line 488”. Another issue whic
h I
think is related is that if I run xp_sqlagent_proxy_account to set the proxy
account, I get this error: “Specified user can not login”. Interestingl
y, if
I make the proxy account a member of the Administrators group, then I can se
t
the proxy account and I can execute xp_cmdshell.
Does anyone know what rights (Win2K rights) are required for SQLServer to
run xp_cmdshell, and what rights are required for the proxy account? By
rights I mean things like: Impersonate another user, logon as a batch job,
logon locally, logon as a service, and so on. This almost certainly has
something to do with rights, but I haven’t been able to isolate it yet. C
an
anyone suggest anything else to try that might help identify the problem?
Thanks,
CraigFor anyone interested, I have found the answer. The proxy account must have
the logon right "Log on as a batch job".
"Craig Daniel" wrote:

> I am tracking down a problem using xp_cmdshell on SQLServer 2000 (MSDE).
The
> DB user is not a sysadmin, but does have exec rights for xp_cmdshell. I a
lso
> have an agent proxy account (Win2K account) set which is a member of “Us
ers”
> group. This scheme has been working fine until I made some changes recentl
y.
> The changes I made are: Applied Win2K SP4, applied a host of hotfixes, and
> made several changes in user rights and other security-related settings.
> I’ve been testing this from OSQL logged in as the same user that my
> application uses when it attaches to SQLServer. I get this error:
> “xpsql.cpp: Error 1385 from LogonUserW on line 488”. Another issue wh
ich I
> think is related is that if I run xp_sqlagent_proxy_account to set the pro
xy
> account, I get this error: “Specified user can not login”. Interestin
gly, if
> I make the proxy account a member of the Administrators group, then I can
set
> the proxy account and I can execute xp_cmdshell.
> Does anyone know what rights (Win2K rights) are required for SQLServer to
> run xp_cmdshell, and what rights are required for the proxy account? By
> rights I mean things like: Impersonate another user, logon as a batch job,
> logon locally, logon as a service, and so on. This almost certainly has
> something to do with rights, but I haven’t been able to isolate it yet.
Can
> anyone suggest anything else to try that might help identify the problem?
> Thanks,
> Craig
>