Modifying Data Column Names And Data Types

One Or More Columns Do Not Have Supported Data Types, Or Their Data Types Do Not Match.
 

Hi,
 
I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:
 
at least one  mapping between a column from available input columns ans a column  from available lookup columns must be defined on the  columns page.
 
So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:
 
The following columns cannot be mapped:
[Department, DEP_CLEGALCODE]
One or more columns do not have supported data types, or their data types do not match.
 
The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.
 
The DataFlow is: ExcelSource --> Derived Column -->  Lookup --> Flat file destination
 
thanks.
 

View Replies !   View Related

---

Bcp Data Extractions With The Column Names!
Ok, i am finally giving in on this one and asking for some help! I am trying to set up a T-SQL Statement that will extract data from all the tables in the current database to a csv file including Column names!

I know that bcp can not handle the column names, so i tried to get around this with an append of the column names from a select, but unfortunatly the select gives me the names in Alpha order and not the order of the fields.

I have tried putting in an order by on the select, but this does not seem to have any effect. I have included the snippet of my script that is causing the problem here :

-- set up the echo command
select @colcommand= 'exec master..xp_cmdshell'
+ ' '''
+ 'echo ' + @names + ' >> c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt'
+ ''''
from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@TABLE

and just in case you are interested in the rest of the script, the full monster is included at the bottom of the post. Also if you can see any more efficient ways of doing what i am trying to do, please let me know!

Thank you for your help in advance.

Mark

---------------------------------------------------------

-- Script to create a csv file of data from all tables inside current database

-- declare all variables
declare @command varchar(200) -- command used for bcp
declare @fetch_status int-- variable for fetch status in cursor

declare @TABLE varchar (200)-- Variable to hold table name
declare @colcommand varchar (200)-- Variable to hold column creation command
declare @count int-- Variable used to determine first itteration of Column loop
declare @names varchar(100) -- variable used for the column names
declare @delimiter varchar(10)-- variable used for delimiter in column names

SET @delimiter = ','-- set up the delimiter to comma
select @count=0-- initialises the COUNT variable

-- setup cursor to create the bcp command to backup the data files to csv format
declare bcpcommand cursor READ_ONLY FOR
select 'exec master..xp_cmdshell'
+ ' '''
+ 'bcp'
+ ' '
+ TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ ' out'
+ ' c:cp'
+ TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME
+ '.txt' + ' -c -t,' + ' -T'
+ ' -S' + @@servername
+ ''''from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

-- setup cursor to pick up all the tables in the given database (used for column names section)
declare dbtables cursor READ_ONLY FOR
select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'

open bcpcommand

select @fetch_status=0

while @fetch_status=0
begin
fetch next from bcpcommand into @COMMAND
select @fetch_status=@@fetch_status
if @fetch_status<>0
begin
continue
end

-- print 'Command to be run : ' + @COMMAND
EXEC (@COMMAND)
end

-- close and tidy up
close runme
deallocate runme

-- now create the fieldname files and then echo the 2 files together!

open dbtables
select @fetch_status=0
while @fetch_status=0
begin
fetch next from dbtables into @TABLE
select @fetch_status=@@fetch_status

if @fetch_status<>0
begin
continue
end

SELECT @names = COALESCE(@names + @delimiter, '') + name
FROM syscolumns where id = (select id from sysobjects where
name=@TABLE)

-- due to the concatonation used, the second itteration onwards has a , attached to the front of the line
-- this section removes the first char
if @count <> 0
begin
Select @names=SUBSTRING(@names,2,198)
end

-- set up the echo command
select @colcommand= 'exec master..xp_cmdshell'
+ ' '''
+ 'echo ' + @names + ' >> c:cp' + TABLE_CATALOG + '.' + TABLE_SCHEMA + '.' + TABLE_NAME + '.txt'
+ ''''
from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@TABLE

-- print 'COMMAND : ' + @colcommand

exec (@colcommand)

-- reset @names variable for next itteration, and set count to 1 to trigger IF above
select @names=''
select @count=1
end

-- close and tidy up
close dbtables
deallocate dbtables

View Replies !   View Related

---

Column Names In Data Warehouse
Hi all,

I'm working on my first data warehouse and I'm not sure how I should name the columns in the database.

The first phase of the data warehouse is to store a bunch of data from one third party source. The source contains over 100 pieces of data and the business user doesn't even know what some of the fields are but he wants to store everything. The third party refers to the each field with a somewhat cryptic short name and a longer description. The short name isn't always cryptic.

My question is am I better off naming my columns the same as the source system's short name so that I can easily debug problems later? Should I instead try to shorten their definition into something meaningful? On a side note, I'm 100% positive that we'll never populate the tables in questions with data from an additional source.

Thanks!

View Replies !   View Related

---

How To Find Differences In Column Data Types Between Tables In Two Different Databases Using TSQL
 
I have Two Database that exist on Two seperate servers. The two database contain same schema and contains tables and columns of same name.  Some tables have slight differences in terms of data types or Data type lenght.
 
For example if a Table on ServerA has a column named - CustomerSale with Varchar (100, Null) and a table on ServerB has a column named CustomerSale with Varchar (60, Null), how can i find if other columns have similar differences in all tables with the same name and columns in the two servers.
 
I am using SQL Server 2005.  And the Two Servers are Linked Servers 
 
What Script can i use to accomplish this task. Thanks
 
 

 
 

 

View Replies !   View Related

---

Reseeding Temporary Tables Or Table Data Types With Identity Column
Hi,

I have a indexing problem.  I have a sequence that needs to has a index number. I want to use a table data type and have a working sample BUT I cannot reseed the table when needed. How do I do this.

This works only for the first ExitCoilID then I need to RESEED.

 

Here is my code:

 

DECLARE

@EntryCoilCnt AS INT,

@ExitCoilID AS INT,

@SubtractedFromEntyCoilCnt AS INT

DECLARE

@ExitCoilID_NotProcessed TABLE

(ExitCoilID int)

 

INSERT INTO @ExitCoilID_NotProcessed

SELECT DISTINCT ExitCoilID

FROM

dbo.TrendEXIT

where

ExitCoilID is not null and

ExitCnt is null

order by

ExitCoilID

 

DECLARE

@ExitCoilID_Cnt_Index TABLE

(ExitCoilID int, ExitCnt int IDENTITY (1,1))

IF @@ROWCOUNT > 0

BEGIN

DECLARE ExitCoilID_cursor CURSOR FOR

SELECT ExitCoilID FROM @ExitCoilID_NotProcessed

ORDER BY ExitCoilID

OPEN ExitCoilID_cursor

FETCH NEXT FROM ExitCoilID_cursor

INTO @ExitCoilID

WHILE @@FETCH_STATUS = 0

BEGIN

INSERT INTO @ExitCoilID_Cnt_Index

SELECT ExitCoilID

FROM dbo.TrendEXIT

WHERE

ExitCoilID = @ExitCoilID

ORDER BY

EntryCoilID, Cnt

select * from @ExitCoilID_Cnt_Index

--truncate @ExitCoilID_Cnt_Index

--DBCC CHECKIDENT ('@ExitCoilID_Cnt_Index', RESEED, 1)

FETCH NEXT FROM ExitCoilID_cursor

INTO @ExitCoilID

END

CLOSE ExitCoilID_cursor

DEALLOCATE ExitCoilID_cursor

select * from @ExitCoilID_Cnt_Index

END --IF @@ROWCOUNT <> 0

View Replies !   View Related

---

Replicating Data To Tables Of Different Column Names
hi,

is it possible to replicate data from one table to another which have different names,different schemas and different column names.

 

Im trying this using transactional replication.

i achieved this for different schema, different table names.but it is failing for different column names.

 

till now i haven't got a proper reply for my problem.

 

 

 

 

regards

Baji Prasad.B

View Replies !   View Related

---

How To Append Data To A Destination Flat File Without Appending The Column Names Again
Hello guys,

here is my issue.


I created a ssis package which exports the data from oledb source to flat file (csv format). For this i have OLEDB source and Flat File as destination. I generate the file and filename dynamically with the column names in the first row.  So if the  dynamically generated file name already  exists , then  i want to append  the  data  in  the same  existing file.  But I dont want to append the column names again. I just want to append the rows to the existing rows.

so lets say first time i generate a file called File1_3132008.csv.

Col1, Col2
1,2
3,4


After some days if my ssis package generates the same file name i.e. File1_3132008.csv, this time i just want to append the rows to the existing file. So the file should look like this-
Col1, Col21,23,45,67,8



But instead my file looks like this if i set Overwrite propery to false

Col1,Col2
1,2
3,4
Col1,Col2
5,6
7,8

Can anyone help me to get the file as shown in the highlighed



Any help would be appreciated .

Thanks

View Replies !   View Related

---

Excel Destination Data Flow Component Shows No Sheet Name Or Output Column Names For Mappings
 

I have a data flow that consists of

OLE DB source which calls a stored proc that returns a result set

data conversion

Excel destination
I am in design mode in Business Intelligence studio.  My excel destination (with an Excel Connection) shows no sheet name though I  have an execute SQL task before the data flow to create the excel table called SHEET1.  Needless to say, there are no output columns visible to do any mappings.  I did go to the ExcelConnection to set the OpenRowset Property to SHEET1 but it seems to have no effect.
 
I can do the export in SQL Server Management studio and that works fine, but it is basic and does not meet my requirements.   I have to customize the package to allow dynamic Excel filenames based on account names and have to split my result set into multiple excel sheets because excel 2003 has a max of 65536 rows per sheet.  Also when I use the export wizard, I have the source as a table and eventually the source has to be a stored proc with input parms.
 
What am I missing or doing wrong?  Thanks in advance  

View Replies !   View Related

---

Mapping Of SQL Server Data Types To Integration Services Data Type
Does anyone know of any cross-references between SQL Server data types and the new data types introduced with SQL Server Integration Services? 

View Replies !   View Related

---

Copying Data From One Table To A New One With Some Different Data Types
Is it possible to easily copy data from one table to another if the data types don't match.   I know you can do a INSERT INTO table1(col1,col2)  SELECT (col2,col7) FROM table2 if the data types match but is there a way to do this if they don't. I'm not trying to copy date times into bit fields or anything.  I just have an old table that I built when I really didn't know what I was doing now I at leastthink I have a better understanding of what data types to use, so I was wanting to move the data in the orignal table to my new one.  Most of the fields in the olddatabase are text datatypes and the new database is nvarchar(50) data types.  Thanks for any suggestions. 

View Replies !   View Related

---

Modifying Data
I am trying to modify data in a tble using the Stored Procedure below. It doesnt work properly. I seem to only be getting one character. I think it has something to do with me using "nchar" with the variables. The value I am changing is actually a string.


Alter Procedure usp_UpdateJobName

@JobNameOld nchar, @JobNameNew nchar

As

UPDATE
JobName
SET
JobName=@JobNameNew

FROM
tblRMADATA

WHERE
tblRMADATA.JobName=@JobNameOld

View Replies !   View Related

---

Modifying View Data
Hi,

I was asking how can I make updating and deleting for data  through database views in Microsoft SQL Server 2005




Best Regards,

View Replies !   View Related

---

Modifying Existing Data
Hi Guys,

I am new to sql server 2000, and need a little help.

I have a table called CMRC_Products with various columns, there is one column called Product Images that has the name of every image in my catalog over 4000, I want to add to each row in that particular column .jpg without loosing what is already in there

I have tried:

UPDATE CMRC_Products
SET ProductImage = ProductImage&' .jpg'

But I get an error,

any help would be much appreciated, cheers

View Replies !   View Related

---

Modifying SQL Data Thru Cold Fusion
what do i need to change in my SQL database so that someone can access the database to add and modify data using COld Fusion as an interface?

View Replies !   View Related

---

Modifying Data From Extract Before Import
Hi all,



Having some fun with an excel import.



I have a excel sheet that has data that needs to be UpSert'ed into 2
different tables. In addition, I need to use a value in the spreadsheet
to determine the PK from a reference data table, for one of the UpSert
oprations.



That is all working now.



The thing I'm struggling with is something I am sure is quite simple -
but I'm not seeing a solution from attempts, googling or BOL.



2 of the columns I receive have either nothing, or X in them. The columns they go into are defined as BIT, NOT NULL.



So, in SQL it would be something relatively simple like:



CASE

  When IsAvailable = 'X' then 1

  When IsAvailable is null then 1

  ELSE 0

end



But I don't know how to do this to data that was in a spreadsheet, and
now is a resultset being handed from a task to another task.



to outline my current solution:

---- table 1 = this all works -------------

Excel Source --> MultiCast (For Table 1)-->Data conversion for
table1-->:Sort for Table1--> Merge Join for table 1 (left Outer
join) as 'left' leg

Table1 Source --> Sort Table1 --> Merge Join for table 1 (left Outer join) as 'right' leg



Merge Join for table 1 --> Conditional Lplit for table1

Conditional Split for table1 (table1 source PK is null) -->Insert Into Table1 Destination

Conditional Split for table1 (table1 source PK is not null) -->Update Table1 OLE DB Command

---- table 2 = this needs to be able to convert X/NULL to BIT -------------

MultiCast (For Table 2)-->Copy Column for Table2 -->Data
Conversion for Table 2-->table3 lookup to get FK-->Sort for
Table2 merge-->Merge Join for table 2 (left outer join) as 'left' leg

Table2 Source --> Sort Table2 --> Merge Join for table 2 (left outer join) as 'right' leg



Merge Join for table 2 --> Conditional split for table 2

Conditional split for table 2(table2 source PK in null) -->insert into table 2

Conditional split for table 2(table2 source PK in not null) -->update table 2 ole db command

-----------------------------------------



Now, if I correct the spreadsheet to have 0's and 1's in the two
column, then the process above works. But I cannot (yet) force business
to do that.

If tried to use SQL Command for the excel source, but there is limited
functionality on the command - I cannot do SQL coalese, isnull or case
statements, which would allow me to resolve that data at source.

I've tried to use derived columns to alter the columns. I think that
the REPLACE (IsAvailable, VariableContainingX,VariableContaining1)
might work to change X's to 1, but that doesn't resolve the NULL issue.

I've tried to use a script component to handle the conversion - which
REALLY feels like a bad way to do this - the .Net script is wrote was:

-------------.net script  code-------------

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.SqlServer.Dts



Public Class ScriptMain

    Inherits UserComponent



    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

        If Not (Row.EndOfRowset) Then

            If
(Row.IsDotComVanEnabled_IsNull) Or (Row.IsDotComVanEnabled.Equals("X"))
Then

                Row.IsDotComVanEnabled = "1"

            End If

            If
(Row.IsStoreCollectionEnabled_IsNull) Or
(Row.IsStoreCollectionEnabled.Contains("X")) Then

                Row.IsStoreCollectionEnabled = "1"

            End If

            Row.NextRow()

        End If



    End Sub



End Class

------------------------------------------

I also tried it like this:

----------------1st draft .Net script code--------

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports Microsoft.SqlServer.Dts



Public Class ScriptMain

    Inherits UserComponent



    Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

            If
(Row.IsDotComVanEnabled_IsNull) Or (Row.IsDotComVanEnabled.Equals("X"))
Then

                Row.IsDotComVanEnabled = "1"

            End If

            If
(Row.IsStoreCollectionEnabled_IsNull) Or
(Row.IsStoreCollectionEnabled.Contains("X")) Then

                Row.IsStoreCollectionEnabled = "1"

            End If



    End Sub



End Class





-------------------------------------------------



Those both threw the following error;



-------------error information-----------------

Script Component has encountered an exception in user code:

PROJECT NAME: ScriptComponent_a333140d269b413bb1bddef390da7e16

Object reference not set to an instance of an object.



   at ScriptComponent_a333140d269b413bb1bddef390da7e16.ScriptMain.Input0_ProcessInputRow(Input0Buffer Row)

   at ScriptComponent_a333140d269b413bb1bddef390da7e16.UserComponent.Input0_ProcessInput(Input0Buffer Buffer)

   at
xScriptComponent_a333140d269b413bb1bddef390da7e16.UserComponent.ProcessInput(Int32
InputID, PipelineBuffer Buffer)

   at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

-----------------------------------------------



Any ideas?

View Replies !   View Related

---

Excel Destination Error: Column&&"xx&&" Cannot Convert Between Unicode And Non-unicode String Data Types
Good afternoon

View Replies !   View Related

---

SQL Deny Data Reading, Allow Modifying The Schema
Hi,
I want to create a User who can create tables, views or modify them but not able to view the data from Query Analyzer or any tool on the SQL Server.
I tried giving the user db_owner, Db_Denydatareader but by doing this I was not able to view data, but while modifying table or creating new table I am getting the error that the user is not having permissions on the sysobjects, sysindexes. Please help !!!
Thanks
chitra

View Replies !   View Related

---

Dynamically Modifying The Data Flow Task
In my SSIS package, I have a data flow task
I am loading a CSV file into a SQL table (OLE DB destination)

I have a couple of CSV files to be loaded
Instead of creating a separate task for each file , can I combine them together into a single task

I was thinking about using a ForEach container

This approach works if the number of columns in all the CSV files is same
But in my case it is not

So what I want is a script task that dynamically modifies the mappings

Can I do this?

I was browsing the net and I found certain code which uses IDTSExternalMetadataColumn90, MapOutputColumn etc.
But the code was creating a new package for each mapping

I couldn't understand the code

So can you please help me with this?

My script task should modify the mappings in my data flow task
For e.g.
If  I have 3 columns in my CSV and 3 columns in DB, they should be mapped in the same order


P.S. I do not want to use configurations

View Replies !   View Related

---

Help With Modifying A Data Source's Query At Runtime
Please help figure out what is wrong with my code.  The script is supposed to load a package (from file).  The loaded package already has everything set up to run a query against a local server and output the results to an Excel file.  The reason for the outer script is because I need to change the query based on a global variable.  When the query changes, though, I think the existing dataflow Path is no longer valid, so I should remove it and re-create another one with the new input mappings.  Here is my code, which runs and throws an exception at the AcquireConnections call.
 
The error is

Error: 0x2 at Script Task: The script threw an exception: Exception from HRESULT: 0xC020801B
 
I pieced together this code from the examples in the online books, but I am not sure what to do. 
 
 

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

 

 

Public Class ScriptMain

 
Public Sub Main()

'

Dim app As Microsoft.SqlServer.Dts.Runtime.Application = New Application()

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _

app.LoadPackage("c:systimeExcelOutExcelOutExcelOutDo.dtsx", Nothing)

 
Dim pkgVars As Variables = package.Variables

Dim gsVar As Variable = pkgVars("User::gsExcelFile")

Dim currVars As Variables = Dts.Variables

Console.WriteLine(Dts.Variables("User::gsExcelFile").Value)

gsVar.Value = Dts.Variables("User::gsExcelFile").Value

pkgVars("User::gsQuery").Value = Dts.Variables("User::gsQuery").Value

pkgVars("User::gsCreateTable").Value = Dts.Variables("User::gsCreateTable").Value

 
Dim e As Executable = package.Executables("ExcelOutTask")

Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _

CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

 
' Get the source component.

Dim SourceComponent As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Local Source")

Dim srcDesignTime As CManagedComponentWrapper = SourceComponent.Instantiate()

srcDesignTime.ProvideComponentProperties()

 
' Reinitialize the metadata.

srcDesignTime.AcquireConnections(vbNull)

srcDesignTime.ReinitializeMetaData()

srcDesignTime.ReleaseConnections()

 
' Get the destination component.

Dim destination As IDTSComponentMetaData90 = _

dataFlowTask.ComponentMetaDataCollection("Excel Destination")

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate()

destDesignTime.ProvideComponentProperties()

 

' Create the path.

dataFlowTask.PathCollection.RemoveAll()

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New()

path.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection(0), _

destination.InputCollection(0))

 
'Console.WriteLine(dataFlowTask.PathCollection.Count)

Dim ret As DTSExecResult

ret = package.Execute()

Console.WriteLine(ret.ToString)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class
 
 
 

View Replies !   View Related

---

Modifying Shared Data Source At Runtime
Hi all,
I have several reports using single shared datasource. I want to change at a runtime database that is used by that datasource. Can this be achieved? If not what are the other solutions €“ I guess that using not shared datasource for each report may be the solution (is it?) but it is not the best solution for me. My goal is to allow users to run the same set of reports, viewed in ReportViewer control, but using different databases (connection string dependant).
 
Thanks in advance for any suggestions

View Replies !   View Related

---

Modifying Sysxlogins Domain Names In SQL2000, Strange Result..
It seens that a simple script:Update sysxlogins set name = 'AA001' + substring(name, 9, LEN(name)-8)where name like 'ILLINOIS%'Will replace the SQL2000 domain name correctly in sysxlogins:ILLINOISJonesP becomes AA001JonesPBut for some strange reason via ILLINOISJonesP can still logon viaQueryAnalyzer although he is no longer in the sysxlogins tableanymore? SQL has been stop/started, server even rebooted, yet BOTHthe new and old logins seem to both allow QA login, any thought howthe old one is getting thru SQL security?thanks in advance for any help...

View Replies !   View Related

---

Newbie: Modifying Table = Wrong Data In View?
Hi there,

Completely new to the world of databases. I'm a designer who works primarily in Flash. In any case, I'm trying to manage an application that uses MS SQL and learn about the wonderful world of databases.

Ok, I modified a table (e.g. I added a column called "Rate") that had associated views (created by another developer). Noticed that my application went a little wonky as some of my variables within my app took on the value of the data in the "Rate" column. I checked one of the views and noticed that a column within the view (e.g. TutorID) was assuming the values in the "Rate" column. Note: The column TutorID had been blank before the change to the table. I'm completely lost as to why this is happening. Do I need to rebuild the view? Can I just reset the original view?

Thanks.

Oh yeah, I'm using SQL4X Manager J from Mac Guru (if that helps).

View Replies !   View Related

---

SQL Data Types
Hello,

I have a really dumb question regarding SQL data types. I have a couple columns in a table that are specified as MONEY. These columns are being read from my web app and displayed on the website for reference when filling in other information.

My problem is that when it is displayed on the website it give four decimal places instead of two. For example I want it to report $33,000.29 but what is actually displayed is $33,000.2965.

How can I set up either SQL or my web app so that it only displays the two decimal places? I've looked into changing the datatype already but SMALLMONEY and MONEY have the same type of decimal values.

Thanks!

View Replies !   View Related

---

Data Types
What is the best data type to use within SQL server in a field forstoring currency?Regards,Ciarán

View Replies !   View Related

---

Data Types
Hello,I'm using DTS to update a SQL database with data from an ODBC Database.The update is going fine for most of the data, but not for decimal fields.I get an error about datatype number_..Probably i have to choose another datatype, but none of them works. How canmatch thedatatype of the field in the source database with the one in the destinationdatabase in SQL ?Is there a way to see which one to choose ?Thanks

View Replies !   View Related

---

Data Types
what is the equivalent data type of autonumber of access in sql server?

View Replies !   View Related

---

Data Types Help?
Which data type do I need to assign to my field to let users enter characters such as +=-_| etc....pretty much every single character from a standard keyboard....

thanks

View Replies !   View Related

---

Help With Appropriate Data Types..
hi, i'm trying to build an online forum using sql server 2000 and coldfusion mx.

could anybody help me as to what datatypes i should use for my database fields?

i'll be using 2 simple tables to start with, ie TOPICS and POSTS. each will contain fairly basic information such as name of poster, time, data, title, message etc.

any help would be greatly appreciated.
thanx, zaffi.

View Replies !   View Related

---

SQL 7 Data Types
I'm just getting used to using SQL 7 Database and am looking for some recommendations on what DATA TYPES to use when designing tables.

The site I am working on now uses cold fusion to enter, update, and insert mainly text and articles. Now the problem with the articles is that some of them are pretty big. And when inserting or updating them, if they go over the limit of characters I get an error.

I know in access I used to use the data type "MEMO" and be able to put a large piece of info in that field.

What's the equivalent for SQL server databe?

What are the most used/common or recommended data types that should be used when putting tables together?

Thanks

George

View Replies !   View Related

---

Data Types
I am looking for a chart of SQL Server data types and information about them, such as usage, constraints, etc. Could anyone point me in the right direction?

Thanks

View Replies !   View Related

---

Different Data Types
I have successfully made a couple of SSIS packages to read data from a legacy AS400 to SQL Server 2005.  These are working fine.  I am now working on one that is trying to pull some financial data and I keep getting the following error for the decimal columns:

"The component ciew is unavailable. Make sure the component view has been created. The input column "input column AMT1 (2870) cannot be mapped to "external metadata column AMT1(2852) because they have different data types."

Now I made sure that the data sitting on the AS400 is of type decimal and the SQL server column is the same.  What else do I need to look for?

Thanks for the information.

View Replies !   View Related

---

Sql Data Types Vs Is Data Types
Hi,

I want to store a decimal value which I get from an execute sql task into a package variable. Which data type should I specify when creating the package variable?

Thanks,
Tom

View Replies !   View Related

---

Data Types
Hello, I am following this article about building an ASP.Net application which uses SQL Express 2005.  The document calls for building a couple of tables.  One column is identified with data type of "Byte" which are is available for selection within the Visual Studio 2005 interface.   The following values show in the drop down of data type in VS2005:

Bigint
Binary(5)
Bit
Char(10)
DateTime
Decimal (18,0)
Float
Image
Int
Money
NChar(10)
NText
Numeric(18,0)
Nvarchar(50)
Nvarchar(max)
Real
SmallDateTime
SmallInt
SmallMoney
Sql_Variant
Text
Timestamp
TinyInt
UniqueIdentifier
Varbinary(50)
VarBinary(max)
varchar(10)
Varchar(max)
XML

I choose Char with a size of 1.  Would this be correct, or am I missing something?

 

Thanks in advance for your assistance!!

View Replies !   View Related

---

SQL Data Types
Is there some type of resource that tells you what all the Data types are and what they are used for. For example, whats the diffrence between Nvarchar and Char? Things like that. Thanks!

View Replies !   View Related

---

About Data Types
What is the difference between int and numeric data type in sql server 2000. When I should use which?

View Replies !   View Related

---

DATA TYPES
Im comparing values. if i add the values which is numeric or monetary in the column and if it is greater than zero it will insert to a table, but the datatype of my column([Day 30 AMT] etc. is char. i want to implement like these like these because it cant return a result if i query..

FROM tbl1 a LEFT OUTER JOIN tb2 b

ON a.ID = b.ID

WHERE b.ID IS NULL AND

a.[Day 30 AMT] + a.[Day 60 AMT] + a.[Day 90 AMT] + a.[Day 120 AMT] + a.[Day 150 AMT] + a.[Day 180 AMT] + a.[Day 210+ AMT]) > 0

View Replies !   View Related

---

Need Help With Data Types
Hi,

I am pretty new with SQL and was dabbling in a test database that is linked with a software application.

I made a change to a table - Lets call this (Table1).
Table1 has a multiple columns.

Column1 is defined as numeric(9)
Column2 is defined as varchar(20)

In this case, I wanted to make all values in Column2 = Column1 that did not have a value defined so I ran the following query:

update Table1 set Column2 = Column1 where Column2 = ''

This took effect but the particular software application doesn't recognize these values because they are the wrong data type.

How can I convert all data in a column to varchar(20) if it is another data type (i.e. in this case numeric(9))?

I've tried in Enterprise manager to redesign the table and adjusted the length to 21 and then back and saved it but this did not work. I was looking at Convert and Cast functions but don't know how to write one to accomplish what I need to do here.

Half of the data in Column2 is varchar(20) and half is numeric(9).
I want all to be varchar(20).

Thanks,

asyncd

View Replies !   View Related

---

Data Types And Its Flaws?
Hey guys,
I have a table with the following information.
A1,A2,A3,A10,A11,A12,A14,A20,A21B1,B2,B3,B10,B11,B12,B14,B20,B21C1,C2,C3,C10,C11,C12,C14,C20,C21Now if I assign this field as a char,varchar,text field then when sorting it turns outA1,A10,A11,A12,A14,A2,A20 etc. So in order to go around this issue, I placed a blank ' ' (space) before the items that are less than 10 so that it'll sort as' A1', ' A2', ' A3' , 'A10' etc. However, now the problem is since the B and C series also have this space it actually sorts as' A1', ' A2', ' A3', ' B1', ' B2' ... 'A11','A12' etc.  Now, I know why it is doing that. I'm trying to figure out if there is a solution to this issue. I've considered having two fields instead, one for the letter and another for the number, However, I need to display them together and I also need to do some reporting on these figures, so even if i concatenate the two fields together, it'll be a string field and sort by the same way. Any ideas?

View Replies !   View Related

---

Converting Data Types
Having a slight problem. For whatever reason I had a column setup for nvchar but the only data i have in there is dates (MM/DD/YY) and a whole bunch of NULLs. I wanted to change the datatype to datetime but it gave me an error. I thought it was due to all the NULLs, so I removed all the nulls. It still doesn't work. I get the error : Conversion failed when converting datetime from character string.
I don't belive its because I have the data in mm/dd/yy format. I created another table and populated it with mm/dd/yy data as nvchar (50) and then converted to datetime and it worked fine.
 any ideas?

View Replies !   View Related

---

Converting Data Types
I've inherited a monster database that needs updating. It has a field called Birthdays that has been a text field and I want to convert it to datetime. The data entry people have been sloppy from time to time and some of the values generate errors when you try and change datatypes. Is there a way to change the datatype and have the oddball values left blank without bombing out the conversion process?

Thanks

View Replies !   View Related

---

Number Data Types...
Hello all,I'm kind of struggling with numeric data types...I have read alot of material, and think I have a 'decent' grasp on thevarious number data types and the effects of the scale and precision.However, I want to store a value of '1.0', and every variation I trystill truncates the value.Is there a way to store 1.0 in a number data type?Thanks so muchWarren M

View Replies !   View Related

---

Changing Data-types
Hi All,I have a varchar(255) column for storing text in english and inhebrew.It was a stupid mistake to set the data type to be varchar, becausenow I need to store text in german and francais too.Question: Is it safe to change the data type from varchar to nvarchar,without damaging the data that's already present?(I have more than 1000000 records stored...)Thanks,Danny

View Replies !   View Related

---

Checksums And Data Types
I'm creating a checksum column in a table that is to be calculated over several columns within the table.

One of the columns to be included in the checksum formula has a data type ntext.

But on trying to complete this new table design (or similarly using alter table in QA) - both return an error stating that the data type is invalid for the checksum function.

This happens for both ntext and text data types.

Can anyone tell me if there is a way round this without having to change the data type - or the valid data types that can be used for the checksum funciton?

Also reasons why would be helpful!

Thanks

View Replies !   View Related

---

Data Types In SQL Server
I'm new to SQL Server, but could someone explain how to set up a field with an auto number/increment that can be used within an Access DB?

View Replies !   View Related

---

I Need Help Regarding Sql Data Types And Pointers.
hello, i have a few questions here which i hope anyone can help me.

1. how do i go about using the image data type?

2. how to use pointers to point to a specific file? for example, if i want to point to a music/image file, how do i go about doing that?

i'd appreciate if anyone can help me.

thx! :) .

View Replies !   View Related

---

Sql Server && Col Data Types
I am trying to write a generic stored procedure to return the data type of a given col in a given table in any database.

I would like to place this sp in master.

input parms are @TableName sysname and @ColName sysname
ouput parm is @data_type varchar(128)

set @data_type=''
select @data_type = data_type from information_schema.columns where table_name = @TableName and column_name = @ColName

when debugging this I always get 0 rows (@data_type remains '')

I have a thought that I can add an input parm for the database, say @DbName sysname
But I am having trouble updating the select stmt to be something like
...from @DbName +'.' + information_schema.columns.

Any Ideas?
Thanks for your help.

View Replies !   View Related

---

Data Types Conflict, But I Know They Don't
Please save my sanity. Here's what I'm doing:

INSERT INTO qtQuote (qtRev, qtQN, qtQuoteNum, qtDate, qtCustID, qtCoID,
qtSalesID, qtInvoice, qtFile, qtPN, qtToolref, qtStenref, qtDueD,
qtDueT, qtDelD, qtChxA, qtChxB, qtFloppy, qtChxC, qtChxD, qtOther,
qtStatus, qtType, qtWdth, qtLgth, qtMat, qtTypeO, qtMat1, qtMat1C,
qtMat2, qtMat2C, qtMat3, qtMat3C, qtClmps, qtClip, qtPins, qtHold,
qtSky, qtRot, qtCust1, qtCust1C, qtUnit, qtProgH, qtProgM, qtMCH,
qtMCM, qtMCC, qtAssH, qtAssM, qtAssC, qtOthH, qtOthM, qtOthC, qtPrem,
qtDisc, qtNotes) VALUES ('', '222q0005', '5', getdate(), '3', '222',
'1', '', '', '', '', '', '', '', '1', '', 'wwww', '', '', 'some crazy text',
'1', '', '3', '54', '54', '59.00', '', '', '$0.00', '', '$0.00', '',
'$0.00', '0', '0', '0', '0', '0', '0', '0', '$0.00', '1', '0', '0',
'0', '0', '$60.00', '0', '0', '$60.00', '0', '0', '$0.00', '0', '0',
'');


but then I get this error:

Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'some crazy text' to a column of data type bit.

but I KNOW qtOther (and qtFloppy, which this also happens to) are varchar types.... why is it telling me they are not??!?

using SQL Server 2000 and I'm a big newbie...


thanks!

View Replies !   View Related

---

TEXT Data Types
I have a table which has 4 columns, 2 text 2 varchar I want to concatenate them together and copy to another table, obvioulsy can't cast all to varchar and cant use + to concatenate Text columns. Is teh only solution to write a messy sp that utilises writetext?

View Replies !   View Related

---