Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Currently, I have to manully create RS 2005 reports which I export into an Excel later. Is there a way to create a SSIS package that could automate this somehow?
Hello, I cannot find out to create enum data type in SQL Server 2005 Express. Can I easily create the enum type just like the MySQL does.(please the MySQL example below)
CREATE TABLE myTable ( myid INT UNSIGNED NOT NULL, myclass ENUM('FIRST','SECOND','THIRD') DEFAULT 'FIRST' PRIMARY KEY(myid) );
Why does SQL add 4 zeros at the end of a money data type? I have to format my strings once they are retrieved because of this. I am not sure if I did something wrong, but shouldn't it only have 2 trailing zero's?
I am trying to input UK(for example: 25/09/2007 ) format datetime in sql server. Is there any change that I can format my column to UK datetime. At the moment there is in US format(09/25/2007).
I have a table, one of the column has type datetime.
I use the insert command and the data i want to into is '25/09/2007'
It come back with error that the date is out of range. I change that to 09/09/2007 then it work. The currently date and time format is MM/DD/YYYY. I don't know how can i define the format to be DD/MM/YYYY
Thank you very much for your help i really do appreciate that.
I have a table which stores date-of-birth in varchar 19861231(yyyymmdd). A view takes this data. I want to store this date as mmddyyyy in the view. How can we achieve this?
I like to define my procedure parameter type to match a referenced table colum type, similar to PL/SQL "table.column%type" notation. That way, when the table column is changes, I would not have to change my stored proc. Any suggestion?
hi all , does SQL server able to input the date that is originally from 2/6/2004 11:07:46 AM to DD-MM-YY 06-02-04 and store in the DB. For example , this is the SQL insert query
INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,'2/6/2004 11:07:46 AM')
the date will automatically converted to 06-02-04 and store in DB .
Does it need to use SQL Rule or user-defined data type.
First time poster here, basically I have a second year university module on database design and for our coursework we have to model and create a database. One of the questions asks us to create a table that has a constraint on how many rows it can contain. I now that this is possible in some other databases, however I haven't seen a constraint that I could use on create table to limit the number of rows.. Does anyone now if this is possible?
i have to derive a column from an existing column in my data source with a data type of DATE. to illustrate my point lets assume that:
i have dt00 as my current column with its data type of date and now i have to create another column dt01 with data type int. i need to format the date from dt00 to the following format yyyyMMddhhmm, where
yyyy - year MM- moth in two digits (e.g: 03, 12) dd - days in two digits (e.g: 03, 12) hh - hours in two digits (e.g: 03, 12) mm - minutes in two digits (e.g: 03, 12)
and allocate this value to this new column dt01.
could someone give me some clue on how to do this using the SCRIPT TASK?
I'm designing a database that stores personal details of students and teachers for a school. I, wish to know if a there is away of formatting the bit data type to male/female. Cause i am only able to achieve 0 and 1 in this data type. So is there any way to change it, or is there any other data type suitable to provide only two options Also the database should be protected in a way that students may not be able to view or change. Only teachers/admin have the right to access/modify the data.
I've a text file which having a datetime column value like YYYY-MM-DD-HH.MM.SS.XXXXXX. I cannot convert this is to datetime format from text file using BCP utility.Â
Presence of hypen "-" between DD and HH, the SQL server does not accept this is as Datetime.
Is there any option to covert the date value in format file in BCP.
Hi,I'm trying to create some tables dynamically based on the content of another table in the same database. I found a post that does what I want to do, but I can't get my code (that is similar to the post) to work.Given below is my code: 1 DECLARE @deptCode varchar(50), @numberOfDept int, @tableName varchar(MAX), @columnName varchar(MAX) 2 DECLARE @lengthDeptCode int, @lengthTableName int, @lengthColumnName int 3 4 SELECT @numberOfDept = COUNT(DISTINCT DeptCode) 5 FROM tbl_Department; 6 7 WHILE (@numberOfDept >=0) 8 BEGIN 9 SELECT @deptCode = DeptCode, @lengthDeptCode = LEN(DeptCode) 10 FROM tbl_Department; 11 12 SET @tableName = 'tbl_ProjectNumber'+@deptCode 13 SET @lengthTableName = LEN(@tableName) 14 SET @columnName = 'ProjectNumber'+@deptCode 15 SET @lengthColumnName = LEN(@columnName) 16 17 CREATE TABLE CAST(@tableName as char(@lengthTableName)) 18 ( 19 CAST(@columnName as char(@lengthColumnName)) int IDENTITY(1,1) NOT NULL 20 ) 21 22 SET @numberOfDept = @numberOfDept - 1 23 END This is actually my first time using SQL programatically so I'm guessing there are alot of problems with it. I just don't know what exactly. The error I get is:Msg 102, Level 15, State 1, Line 18Incorrect syntax near '@tableName'. Thanks.
Hi there,I am trying to generate tables names on the fly depending on another table. So i am creating a local variable containing the table names as required. I am storing the tables in a local variable called@TABLENAME VARCHAR(16)and when i say SELECT * FROM @TABLENAMEit is giving me an error and I think I cannot declare @TABLENAME as a table variable because I do not want to create a temp table of sorts.I hope I am clear.thanks,Murthy here
Hi All, DECLARE @MyTableVar table( EmpID nvarchar(10) ) select login_id from cpm into @MyTableVar Above syntax is not working.... Actually i want to table variable and store the result returned by stored procedure. How to do that... Thanks and reagards A
I'm working on building my database tables, and I've ran into a bit of a situation.I have one table called 'Entities' that will hold information about people, places, and events. I would like to have the user be able to add other properties to these entities ad hoc.For example, a person might have a birthdate they want to record. So I was going to setup a DateProperties table that would have be setup something like this:DatePropertyIDEntityIDName (this would be 'Birthdate' in this situation)Value (the DateTime value)In that same way, I would also want users to be able to add something like Population to the places. So I was going to setup a NumberProperties table that would have something like this:DatePropertyIDEntityIDNameValueI noticed the structures for the table is basically the same; the only thing that is changing is the Data Type for the Value. I was thinking there has to be an easier/better way to do this sort of thing, perhaps all in a single table. I'm still trying to learn all the latest stuff from .NET 3.5. I was hoping to use LINQ to SQL, but I am willing to try anything.Any suggestions?
Hello friendsI want to have a trigger for creating table on dialy basis.for e.g. I have a table say Data and on each day a new table is created like Data24Oct05 and so on. please help for writing the trigger for the same.thanks
Warning: The table 'test' has been created but its maximum row size (11864) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
I've got a problem with table creation in stored procedures (SQL Server 2000). We've got an application where the user login only has rights to execute stored procedures. The problem is that a stored proc is dynamically creating a table and so the owner of that table is being assigned to whatever login the application is using instead of dbo. It's causing numerous issues. Is there any way that this can be avoided or changed without granting the user sa privileges?
I am starting to create a new database table based on an existing dbtable. My existing table has a list of ID's and a date range for each ID.
For example: TableSource ID Start DateTime End datetime Y10012 01-12-12 13:00:00 01-19-12 13:00:00
So for this ID, I need my SQL statement to read this table, then create a new table and insert a new row for every second starting from the start date to the end date. I have several id's that span a week at a time. So I am expecting millions and millions of records once I am done.
Then once it completes reading S1001, it moves on to the next ID and appends it to the table. The date ranges are different for each ID- so it can't be hard coded.
Can you create a table on one line and then update it on the next i.e. in the same query.
i.e.
create table test as select * from original update test set test.a = .......
I know that they both work individually but whne i try to run them in one file i get "sql command not properly ended" - when i put a comma after 'original' i then get "invalid table name".
Suppose I have a table named table1 which has a field question_Id.There are many values for this field say 100,101,102. Now I want to make a table with the field name 100,101,102 but the problem is that it is not fixed how many values are there for question_id.Here in this example I mentioned three(100,101,102) but it may be anything.How can I make a table with the field names this way? Subhasish
CREATE DATABASE Dummy ON -- Primary file contains Startup information of the database PRIMARY ( NAME = PrimaryLog, FILENAME = 'D:primary.mdf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ), -- Holds The Data of LookUPTables,TPProfile,CRM. ( NAME = Data, FILENAME = 'D:Data.ndf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ), LOG ON -- Stores The Log Information used To Recover The Database ( NAME = Log, FILENAME = 'D:Log.ldf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ) Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
CREATE DATABASE Dummy ON -- Primary file contains Startup information of the database PRIMARY ( NAME = PrimaryLog, FILENAME = 'D:primary.mdf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ), -- Holds The Data of LookUPTables,TPProfile,CRM. ( NAME = Data, FILENAME = 'D:Data.ndf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ), LOG ON -- Stores The Log Information used To Recover The Database ( NAME = Log, FILENAME = 'D:Log.ldf', SIZE = 5MB, MAXSIZE = 500MB, FILEGROWTH = 20MB ) Go
After this I want to create table on Data .
CREATE TABLE Sample (
No INT ,Name VARCHAR(30) , Department VARCHAR(4000) NULL
I have to display the data in the below said formats..Current sample Data in the table and the data type is numeric(23,10)
50.00 0.50 0.00 0.00
To be displayed in the below format
1.25 0.75 0 0 1
I have to map this column in teh report and should dipslay like above.I think if 0.00 is available then it should display as 0..If 1.0 is available then it should display 1.Any value that has postive number after the decimal should display all the values  example : 2.25,3.75,5.06, So in general the solution to display values like 1.75,1,0 we should not dispaly 0 as 0.00 and 1 as 1.00 and 2 as 2.00 and so on...Any Solutions in terms of SQL query  or SSRS expression.
I'm wondering if there is a control available for creating/modifying db tables through a web interface. I want for users to be able to add/remove, rename, and change the datatype of certain fields in a database table. I've been searching all day online if such a control exists in asp.net but haven't found anything.
I have a table I am trying to create and one of the columns needs to have a decimal that appears as 00.00 and does not round because the data example is 87.09, 86.50, 98.55 etc. I have tried every type of decimal formatting when creating I can find and nothing has worked. When I create the table and upload the data it keeps rounding like this: 87.00, 87.00, 99.00
The only way it does not round is if I do it as a char but that does not seem right when dealing with numerics.
I have to create one table and attach the trigger to the server given by the user. Assuming I have all sorts of permissions, how can I do this using CLR.
I tried this using data base project in c# and deploying it manually, it worked successfully but I am not able to get how can I provide this at run time to the user.
If I write one assembly on the client side for the same than how to run it on the back end (i.e., SQL Server).
