Datatype problem: development vs production servers

This is driving me nuts: On my development machine the code runs finebut generates an error on the production server. Both are running SQLServer 2000 and ASP.NET 1.1

The datatype of the field in question isdatetime.
The webform has a calendar for a user to select and automaticallyinsert the date into the textbox. The update command in the webform is:
cmdInsert.Parameters.Add("@.citation_date", CDate(txtDate.Text))

This works without a hitch on my development system, but on the production server it generates the following error:
Cast from string "19-12-1997" to type 'Date' is not valid.

WHY?
It has to do with the locale information (country, language, etc.) for the computer. Check to make sure the server is set to whatever you're using on your local development PC. I'm not familiar with setting/changing these since I only use U.S. format and English.|||

jcasp wrote:

It has to do with the locale information (country,language, etc.) for the computer. Check to make sure the serveris set to whatever you're using on your local development PC. I'mnot familiar with setting/changing these since I only use U.S. formatand English.

You are right. I'm inputting U.S format of date for the time being until I've figured a way around it. Thanks!|||Use YYYY-MM-DD format, then it doesn't matter what culture you are in.

DataType Issue

Hi

Using SQLserver 2000 I have to enter a huge data (nearly 8000 charcters) into a field, which data type is suitable for that and what is the method to store data.

You might need to use a VARCHAR(8000) or a text datatype; however, there is not enough information.

Is your data divisible into different definite pieces? Do you need to support unicode? Is your data numeric? binary? Is your data XML Is there additional data that needs to be stored with the 8000 characters?

datatype

does any know how sqlserver 2000 'text' datatype is mapped in sqlserver JDBC driver? where can i find documentation of for data mapping between sqlserver datatypes and jdbc datatypes?
neo wrote:

> does any know how sqlserver 2000 'text' datatype is mapped in sqlserver JDBC driver? where can i find documentation of for data mapping between sqlserver datatypes and jdbc datatypes?
Hi. The simple test would be to query a text column and do a getObject() and see
what sort of object you got. However, it is simple just to tell the driver what
sort of object you want, by doing a getString() if you want a string, getAsciiStream()
if you want a stream, etc.
Joe Weinstein at BEA
|||The document from "SQLServer 2000 driver for JDBC User's Guide and
Reference" - 3 SQL Server 2000 Driver for JDBC - Data Types have this
information.
"neo" <anonymous@.discussions.microsoft.com> bl
news:19024BF8-645E-45E9-B014-DD475462204D@.microsoft.com g...
> does any know how sqlserver 2000 'text' datatype is mapped in sqlserver
JDBC driver? where can i find documentation of for data mapping between
sqlserver datatypes and jdbc datatypes?

Database_suspect

OK, I am new to sqlserver. Anyway, I was trying to open up one of my db and it had database_suspect next to it and no items listed under it.
How do I resolve this to bring the db available once again. Thanks.Try this code:
sp_resetstatus [ @.DBName = ] 'database'

If you you on BOL and type in Suspect it will outline the steps that you need to take.

Thanks

Lystra|||...I don't think you want to reset the databases status (ti can be dangerous). you should look to restore the database...|||i got same prob few days ago. the below failed to restore in my case but it is said that its success is about 90%

good luck

*********************************************
-- Important!!!
-- There are 3 levels of repair that can be done.
-- You try step 1 = REPAIR_FAST first, if it does not work (db is still suspect in Enterprice Manager)
-- then you should stop service and start service again before continue with next step.
-- Next step 2 = REPAIR_REBUILD. If that does not work (still suspect) then you stop and start service.
-- Do NOT skip the stop and start service step between each time you try a higher level of repair. SQL needs to be reset.
-- Step 3 if the other 2 do not work is REPAIR_ALLOW_DATA_LOSS. There has been very few times when this one has not been sufficient.
-- It is normally not actual data that dissapears if REPAIR_ALLOW_DATA_LOSS can fix the problem but the other steps cannot
-- but rather primary keys, foreign keys, default constraints or indexes. There is no guarantee though.

-- This is step 1 - run this in master in query analyser and replace 'ips' with your database name
-- Note: this is name as seen in Enterprice Manager and not physical file name
dbcc checkdb ('ips',REPAIR_FAST)

-- This is step 2 - Do the same as step 1 (don't forget to stop and start service before)
dbcc checkdb ('ips',REPAIR_REBUILD)

-- Now starts step 3. Actual data CAN be lost with this command but it usually doesn't happen. Make sure you have copy
-- of the physical files (BOTH .mdf and .ldf!) before you run it. To only have copy of .mdf is sometimes enough, sometimes not, have BOTH!
-- To be able to run level 3, database must be in single user mode
-- Use this command in query analyser to set db as Single User mode. You should use it Directly after you have stopped and restarted
-- the SQL service so that the query analyser session you execute it in is the only user.
sp_dboption 'ips', 'single user', 'TRUE'

-- Step 3 - replace name the same as step 1
dbcc checkdb ('ips',REPAIR_ALLOW_DATA_LOSS)

-- If the repair works, then you can set back single user mode with this command
-- You can also go to database properties in Enterprice Manager
sp_dboption 'ips', 'single user', 'false'

-- resets the suspect status bit. Normally doesn't work but can always try. This command forces the suspect bit to off
-- stop and start service after and look in enterprice manager. If SQL cannot read database, then it willl set back
-- the suspect bit automatically.
exec sp_resetstatus 'ips'

-- Some more misc scripts that can be useful
/*
EXEC sp_detach_db 'inbound_mike', 'true' -- true and false apply to option 'skipchecks', loses replication!!!
EXEC sp_detach_db 'inbound', 'true' -- true and false apply to option 'skipchecks', loses replication!!!

-- after attach, users in that database will be un-usable. Run sp_dropuser 'ips' after and go into Enterprise Manager and re-create.
sp_dropuser 'ips'

EXEC sp_attach_db @.dbname = N'inbound_mike',
@.filename1 = N'd:\mssql7\data\inbound_Data_mike.mdf',
@.filename2 = N'd:\mssql7\data\inbound_Log_mike.ldf'

EXEC sp_attach_db @.dbname = N'SODUAT',
@.filename1 = N'e:\mssql7\data\SODUAT_Data.mdf',
@.filename2 = N'e:\mssql7\data\SODUAT_Log.ldf'

RESTORE DATABASE ips FROM DISK = 'd:\ipsdatabasedump.bak' -- must drop rep. regardless
WITH MOVE 'ips' TO 'g:\mssql7\data\ips.mdf',
MOVE 'ips_log' TO 'c:\mssql7\data\ips_log.ldf'

ALTER DATABASE Tempdb Modify file
( NAME = 'tempdev', FILENAME = 'e:\mssql7\data\ips_.mdf')

ALTER DATABASE Tempdb Modify file
( NAME = 'templog', FILENAME = 'h:\mssql7\data\tempdb.ldf')

exec xp_cmdshell 'net start "pcANYWHERE Host Service"'
exec xp_cmdshell 'net stop "pcANYWHERE Host Service"'
exec xp_cmdshell 'dir d:\mssql7\backup'
exec xp_cmdshell 'dir c:\mssql7\data'
exec xp_cmdshell 'del c:\mssql7\devtools\samples\backup\*.* /q' -- q = quiet, don't ask for confirmation when *.*
*/

Database user and Corresponding login

Hi everyone,

I have a simple question regarding the database users on sqlserver 2005.

When i run a 'sp_helpuser' stored procedure in a database to know the usernames and their corresponding loginsName, i find there are some usernames with corresponding loginName as null.I know for sure that for 'Guest' username the corresponding loginName will be Null, but i find many normal database users with Null login names.

Can anyone throw some light on this scenario?.and what about 'dbo' user in a database? can dbo user have a 'NULL' login name or 'sa' login by default?. Please help me

Thanks in advance.

Regards

Arvind L

You can use sp_change_users_login 'Report' to find out if there are orphaned users. (users with no login)

There are users like 'Guest', 'dbo', 'INFORMATION_SCHEMA' that will not have logins associated because these are defined by the system, but if there are users like 'George', etc. then these are most likely users that were created at one time by users or admins. The above command will help you to know which users are orphaned and can be fixed by creating logins and associating them with the user.

When a sysadmin user logs in, they do not necessarily have a user for every database because they can query any database. Those with specific permissions db_datareader or db_datawriter, etc. will need to have a user in the database that they are trying to query because they are limited to permissions assigned.

HTH.

|||

One possible senario is, this database might be restored from another sql server instance and that server may be having these logins-users mapping. Once the database is shifted from one server to another the user-login mapping breaks though the logins are there in the new instance. you need to remap the orphaned user with the logins using sp_change_users_login

Madhu

|||Thanks Ben and Madhu..for the useful piece of info!!!