Hi, I'm new to SQL. I have to fix a table as follows:
update recruit.cvs_2 set cv_id = (cv_id + 11000) where change_date <> null
I'm trying to change the value of cv_id e.g. 2 +11000= 11002. cv_id is defined as nvarchar. What am I doing wrong?
John
I have a table named PERSON and a field named PERSON_ID. How can I set this field to Autonumber? I know I have to use the IDENTITY command, but when I view this field in "design" view, all the IDENTITY options are grayed out.
How can I set this field with the IDENTITY properties?
Thanks
I need to change the name of a field in a db hoy can I do so
View 1 Replies View RelatedI had a profile element named "Country", however, I want it to be named "CountryId". I can change this in the web.config with no problems, but then I need to update the code manually any place Profile.Country is referenced. Also, it seems I would have to write some SQL to update all the values in the profile table from Country to CountryId. Is there an easier way to do this? I'm using Visual Studio 2005 and SQL Server Express 2005.Thanks,Wes
View 5 Replies View RelatedI have a database on sql2000. I want to change the length of a field in Table A, but it is related to another field in Table B. When I tried to change the length of the field in Table A via
ALTER TABLE table ALTER COLUMN field varchar(5).The query analyzer generates the following error.
Server: Msg 5074, Level 16, State 8, Line 1
The object 'UQ__table__77BFCB91' is dependent on column 'field'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN field failed because one or more objects access this column.While when I tried the same query on the field in TABLE B, the query completed without any errors.
What is the best way of converting datatime field value 29/03/2005 08:58:27 to 29/03/2005.
I only want to remove Time from date and I am using Sql Server 2000.
Thanks
Arvind
Is it possible, using VB code, to retain a user on a particular page
until the status of a certain field in a database is changed?
The scenario I am working on is that a person makes a reservation and
has to wait until that reservation has been accepted or rejected by the
administration.
Dim dsResv As DataSet
dsResv = objResv.DALgetResvStatus(resvId)
While (dsResv.Tables(0).Rows(0).Item("resvStatus") = "pending")
dsResv = objResv.DALgetResvStatus(resvId)
If
dsResv.Tables(0).Rows(0).Item("resvStatus") = "denied" Then
failure.Visible = True
LinkButton1.Visible = True
ElseIf dsResv.Tables(0).Rows(0).Item("resvStatus") = "reserved" Then
success.Visible = True
LinkButton1.Visible = True
End If
End While
This is how I tried to do it, but it doesnt seem to work.
Any suggestions?
Hi,
I have to change a due_date field with a varchar(10) to a datetime. I created a new table with datetime but when I imported data into the table it errored out due to the datatype. A varchar(10) going into a datetime(8).
Is there way to resolve this without losing data?. or how do you result this problem?.
Thanks for your help,
Jeff.A.
The title says it all. I've used ALTER DATABASE, and ALTER TABLE...ALTER COLUMN to change all my character fields from 'Compatibility_42_409_30003' to the default I want ('Latin1_General_CI_AI') on databases converted from SQL7. Now I just have these ntext fields to change...
Al
I have to modify field length in the table, which is part of publication. I tried to use an Enterprise Mgr and received an error message because that table is scheduled for replication.
Is any way around this beside temporary stopping and restarting replication? And actually how to do this to be sure that after restart the data will be again in sync.
We have SQL Server 2000 Enterprise Edition and Windows 2000 Advanced Server.
Hey guys,
I have a content table with a field "[Content_ID] [int] IDENTITY(1,1) NOT NULL". I need to change the content id of some of the content because it was leaked into an xml feed and we want to stop people from accessing the content, is it possible to change/issue a new Id for a field that is set as "IDENTITY"?
Hey guys,
I have a content table with a field "[Content_ID] [int] IDENTITY(1,1) NOT NULL". I need to change the content id of some of the content because it was leaked into an xml feed and we want to stop people from accessing the content, is it possible to change/issue a new Id for a field that is set as "IDENTITY"?
Hi all,
I tried to change the size of a field of my table using the query :-
ALTER TABLE test MODIFY id varchar(50)
Initially the size of id was set to 30 .Is there any other way or any error in my query.Please help me soon.
joshymraj
Hi,
My table has more columns than this but I will just use the ones here tht are important.
I have two columns in a table these are "ExpiryDate" and "Archived".
When the row is created the "Archived" field will be null but the user will have entered a expiry date. When this date is reached I would like the "Archived" data to change to "1". Is this possible? Would I have to do it through some kind of script or can I set the database to do this automatically?
Thank you, Mark
My value is approx 50 characters wide, the parameter drop down is only displaying about 10 characters, although I have plenty of room on the screen.
Also, in a multi-parameter environment, are there any controls on where the parameters are being displayed?
thanks
I use Chinese Windows XP+SQL 2005 express+Microsoft Visual Web Developer 2005 Express Edition
In aspnet_Profile table , the Collation of PropertyValuesString field is <database default>
I hope to replace <database default> with SQL_Latin1_General_CP1_CI_AS ,but I get the following error, why?
The following error was encountered while changing the collation:
Setting for Length must be from 16 to 16.
I would like to pull data from two seperate columns based on the vaule for MakeFlag. So if MakeFlag = 0 I would like the description to show but anything else I would like catalog description to show up.
DECLARE @MyVari varchar(20)
SELECT [ProductID]
,[prod].[Name]
,[ProductNumber]
,[MakeFlag]
[Code] ....
I have a Table "Customer" which contain a Field "CreditLimit",
Now I want a Mail alert on change of this filed with both value old credit limit value and New credit limit value.
Is its possible without trigger??
Hi,I use view to join difference table together for some function. However,when the "real" table fields changed (e.g. add/delete/change field). Theview table still use the "old fields".Therefore everytimes when I change the real table, I also needed open theview table and save it by SQL enterprise manager manually for update theview table field.Can we use a SQL command or other method to update it directly?Regards,Silas
View 4 Replies View Relatedchange the subscription subject on the report to Total or a field.
On the subscription Data Driven Subscription does not exist.
Is there any other way to fix it?
I have a table called PJLabDet.Total_Hrs which is the sum of hours of 7 days.
I would like to get a running total for this field which would reset when my group (called PJLabDat.pjt_entity) changes.
Trying to determine what the minimum permissions i can grant to a user so they can see the change tracking data
View 1 Replies View RelatedI'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
We ran into weird/interesting issue with below details.
Version: Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200):
We are using SQLCMD to run DDL script on our product database in below order. That script has below content.
step # 1 - database collation change (case -sensitive) statement as very first statement of the script
step # 2 - Actual DDL SQL statements
step # 3 - database collation change back to original (case insensitive)
When we execute all above 3 steps in single script using SQLCMD on our test_server#1 , it is successful but when same is being implemented on test_ server#2 , it is failing.We ensured that there is no other user accessing the db and setting on both the server are all default/basic. Separating out all 3 steps in 3 different script working fine. This is only problem when we combine them into single script and fire it using SQLCMD. If it is something related to session/transaction then we should hit same issue on our test_server#1 server as well but that is not the case.test_server#1 and test_server#2 has exact same database/data, just two different physical machine & SQL Server instance.
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View RelatedI need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?
Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn
View 2 Replies View RelatedDECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect
Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen
View 1 Replies View RelatedI have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.
It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:
Inserted Value -> Result in table
123456-8 -> 123456-8 *Correct
1234567-9 -> 1234567- *Wrong
123456789 -> 123456789 *Correct
1234567#9 -> 1234567# *Wrong
1234567a9 -> 1234567a9 *Correct
Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?
I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.