Listing Table Data Types Using TSQL
Jun 17, 2008I can find out the data types used in my table by navigating the SSMS and right clicking the table and selecting modify.
How do I view that same output using T-SQL?
Thank you
Goldmember
ADVERTISEMENT
Manipulating Text,nText Data Types Filed In Tsql
Jul 12, 2006I have to run a dynamic sql that i save in the database as a TEXT data type(due to a large size of the sql.) from a .NET app. Now i have to run this sql from the stored proc that returns the results back to .net app. I am running this dynamic sql with sp_executesql like this..
EXEC sp_executesql @Statement,N'@param1 varchar(3),@param2 varchar(1)',@param1,@param2,GO
As i can't declare text,ntext etc variables in T-Sql(stored proc), so i am using this method in pulling the text type field "Statement".
DECLARE @Statement varbinary(16)SELECT @Statement = TEXTPTR(Statement)FROM table1 READTEXT table1.statement @Statement 0 16566
So far so good, the issue is how to convert @Statment varbinary to nText to get it passed in sp_executesql.
Note:- i can't use Exec to run the dynamic sql becuase i need to pass the params from the .net app and Exec proc doesn't take param from the stored proc from where it is called.
I would appreciate if any body respond to this.
How To Find Differences In Column Data Types Between Tables In Two Different Databases Using TSQL
Apr 21, 2008
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
Reporting Services :: Table Data Types For Data Driven Subscriptions
Jun 11, 2015I am trying to find a reference for a client that lists the fields available to be substituted into a data driven subscription from the query, along with the expected data types.  For example, the field on whether or not to include a link to the report seems to be expecting a bit data type.I have searched and can't seem to find anything.  I guess I could walk through the interface and try different data types, but if  a list exists, that would be better.Â
View 4 Replies View RelatedTSQL Statement Extracting Data From One Table Through Another Table
Nov 17, 2007Hi,
I have 2 tables,
MembersTemp and Organisations
I'm trying to extract the organisation Name from the organisations table but am unsure of the sql statement to do this.
Initiallt I only have the ExecID for the MembersTemp table
MembersType table:
ExecID 3013
OrganisationID 4550
Organisation table:
ID 4550 (PK)
Name "Microboff"
Any ideas??
CURSOR And TABLE As Data Types?
Oct 1, 2013I consider CURSOR and TABLE as data types but some one has argued that these are database objects.
View 7 Replies View RelatedOne Or More Columns Do Not Have Supported Data Types, Or Their Data Types Do Not Match.
Oct 20, 2007
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.
Table Data Types In SQL Server 2000
Jan 24, 2005Hi,
Can anybody give me some information on Table data types of SQL2K.
How and where can it be used. Does it make the queries faster, in case there are many users using ?
Please could anybody give me these details, to make me decide whether I can use it or not ?
Thanx and Regards,
Sushma
Multiple Data Types In One Table With Nulls
Sep 25, 2013Our database stores vehicle data in one table, but 3 different types of data are stored in the one table. The table contains all the columns for all 3 types so when you query the table you get at least 3 rows back with null values for all the columns that don't apply to that record. The data is imported to the table when it's updates so there's a possibility that they're updated at different times so they have a different BATCH like:
BATCH TYPE ID RATING INSURANCE SAFETY
300 SAFE 123 NULL NULL A
300 INS 123 NULL YES NULL
250 RATE 123 A NULL NULL
What I'd like returned is:
ID, RATING, INSURANCE, SAFETY
123 A YES A
I'm trying to do a case statement to pull the data down, but I keep ending up with multiple rows because of all the nulls. I tried doing a SUM of the case statement with an ISNULL(SAFETY,0) but I can't SUM char values. I can probably do this with 3 temp tables to load the data that I want for each TYPE into them and then select and join them together, but is there a better way to do this?
SQL Server 2000 Table Data Types
Jul 20, 2005Hi,Can anybody tell me if it is possible to return a table data type (asopposed to a temporary table) from a stored procedure.I am currently studying for 70-229 using the Thomas Moore book but itdoesn't really make it clear, as the text suggests you can't returnthem but some of his sample questions suggest you can.I can't make it work in Query Analyser so I am assuming that you can'treturn them. Is that correct?Thanks in advance for any help,Simon.
View 1 Replies View RelatedCopying Data From One Table To A New One With Some Different Data Types
Mar 30, 2007Is 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 4 Replies View RelatedFlat File Data Types - Defined From Table?
Jul 3, 2007This question is around how we can get the data types and lengths populated into the flat file source columns.
In Connection Manager, you have your flat file defined. You can choose "Suggest Types...", and the minimum lengths and correct data types will be returned from within the data in the flat file.
Is there some way to automate this data type definition, but coming from the other direction (coming from the destination table that we are loading)?
For example, you have mapped the columns that will be loaded. Can you then reverse engineer the data types and lengths for the columns in the flat file from the destination table?
TIA
Binary Data Types (Images) In Matrix/Table Items In Reports
Jul 11, 2007I am trying to produce a matrix (crosstab) report in SQL Server 2005 Reporting Services Report Designer, where the column headers contain a binary data type storing a png image.
By just simply using the report wizard and assigning the binary (image) data value to the column headers, and then previewing the report, I get following error:
An error occurred during local report processing.An error has occurred during report processing.The Group expression used in grouping 'matrix1_COMPETITOR_EMBLEM' returned a data type that is not valid.
Is there any way to include binary data types, or images per se from the database into a matrix or even table item in a report ?
Updating Datetime Data Types In A Table To Show Just Day, Month, Year
Apr 2, 2008I have a table with a datetime field 'TheDate'. Currently dates are stored as 'mm-dd-yyyy 00:00:00'. Is there a way to get just the month, day and year parts, '01/01/2008' into the field without changing the field data type to varchar? I'm asking because when I do this:
declare @MyDate as datetime
set @MyDate = '04/02/2008 18:00:00'
select substring(convert(varchar,@MyDate,101),1,10)
I get '04/02/2008', but when I do this:
update TheTable
set TheDate = substring(convert(varchar,TheDate,101),1,10)
I'm still getting a date in the format 'mm-dd-yyyy 00:00:00' stored in the table. I'd like to be able to lose the time portion, but I'd like to be able to keep the datetime datatype for date math purposes. Can it be done?
Reseeding Temporary Tables Or Table Data Types With Identity Column
Feb 17, 2006Hi,
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
Creating Clustered Index On View With Table Containing XML Data Types Takes Forever And Causes Timeouts
Apr 21, 2007I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?
The table definition is displayed below.
CREATE TABLE [dbo].[AuditLogDetails](
[ID] [int] IDENTITY(1,1) NOT NULL,
[RecordID] [int] NOT NULL,
[TableName] [varchar](64) NOT NULL,
[Modifications] [xml] NOT NULL,
CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The view definition is displayed below.
ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING
AS
SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications
FROM dbo.AuditLogParent P
INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1
The definition for UDF f_GetModification
ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )
returns xml
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The definition for UDF f_GetIfModificationExist
ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )
returns Bit
with schemabinding
as
begin
declare @pidstr varchar(100)
SET @pidstr = LOWER(CONVERT(varchar(100), @PID))
return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')
end
The Statement to create the index is below.
CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]
(
[ID] ASC,
[RecordID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Listing Data
Mar 30, 2008sample Table and records.
id uname punchdate punchtime
1 Â A Â Â Â Â 1/1/2007Â Â 7:00am
1 Â A Â Â Â Â 1/2/2007Â Â 8:00am
1 Â A Â Â Â Â 1/4/2007Â Â 7:30am
1 Â A Â Â Â Â 1/6/2007Â Â 7:40am
let say i want to get a result which punchdate is from 1/1/2007 to 1/8/2007, how can i get a result like this one.?
1 Â Â AÂ Â 1/1/2007 Â Â 7:00am
1 Â Â AÂ Â 1/2/2007 Â Â 8:00am
1 Â Â AÂ Â 1/3/2007 Â Â <null>
1 Â Â AÂ Â 1/4/2007 Â Â 7:30am
1 Â Â A Â Â 1/5/2007 Â Â <null>
1 Â Â A Â Â 1/6/2007 Â Â 7:40am
1 Â Â A Â Â 1/7/2007Â Â <null>
1 Â Â A Â Â 1/8/2007 Â Â <null>
listing all data even if theres no punchdate and time in the table.
SQL ODBC Table Listing Is Not Showing
Nov 8, 2006I have created a System DSN using the SQL Server ODBC driver and configured it to point to my server and database.
When I access the newly created DSN, it should show the tables listing. However, I get the following screen:
See it here
Why am I getting this instead of the tables listing and how do I fix it?
Thank you!
Data Warehousing :: Listing Out Checklist From DBA Perspective For BI Implementation Project
Apr 16, 2015We are in Designing phase Data Modeling of PDW/APS Implementation Project.
I need making a checklist from a DBA perspective.
Like what are things ill be needing at a time of implementation/Deployment.
Creating A Common Table Expression--temporary Table--using TSQL???
Jul 23, 2005Using SQL against a DB2 table the 'with' key word is used todynamically create a temporary table with an SQL statement that isretained for the duration of that SQL statement.What is the equivalent to the SQL 'with' using TSQL? If there is notone, what is the TSQL solution to creating a temporary table that isassociated with an SQL statement? Examples would be appreciated.Thank you!!
View 11 Replies View RelatedMapping Of SQL Server Data Types To Integration Services Data Type
Oct 14, 2005Does anyone know of any cross-references between SQL Server data types and the new data types introduced with SQL Server Integration Services?
View 6 Replies View RelatedReporting Services :: SSRS Export To Excel Showing Data Type As General For All Data Types
Sep 16, 2015One of my report has different data types like decimal,percentage and integer values.
When I exported the report to excel , all the values are showing as "general" data type.
How to get excel data type same as ssrs report data type by default when exported to excel?
Data Access :: Validation For Length Of The Character Data Types
Jun 10, 2015I Have a table with #Sample like below
=================================
#Sample
id int,
SSN varchar(20),
State varchar(2)
Â
Sample Data:
ID SSN STATE
1 999-000-000 AB
2 979-000-000Â BC
3 995-000-000Â CD
=================================
We used filter logic based on the SSN & State.
We are passing these values through variables like
Declare @State varchar(2)
Declare @SSN varchar(20)
While run time these values are lets suppose @SSN = '999-000-000' & @State='ABC'
Now the Result is displayed with the state data Like 'AB' only.
Output: 1 999-000-000 AB
instead it should give system generated error.
Here I have 2 Questions:
1. Why it is taking 1st 2 Charecters?
2. Why it does not have any system generated for length?
I can do validation with Length function for these 2 variables however if have 100 variables then it should not feasible case. So, what is the reason behind?Â
T-SQL (SS2K8) :: Load Data From Flat File Source Into OleDB Destination By Changing Data Types In SSIS
Apr 16, 2014I have an source file and i have to load it into the data base by changing datatype of the columns in ssis
View 1 Replies View RelatedTSQL - Trim String Containing Both Data And Value Into 2 Separate Data Fields
Aug 20, 2007Hi!
Need help with this one:
I have a column with a string composed by several data. After using REPLACE several times, I get something like the data below, which has (in most of cases) a value and a date.
378 9/05
388 9/05
4/05
1/06 606
1/06 646
76 5/05
100 1/05
118 8/05
129 8/05
9/05 342
05/3 123
1/07
4/06 164
The problem is that I need to get each value alone (to separate columns), in example:
Value Date
378 09/2005
388 09/2005
0 04/2005
...
606 01/2006
and so on...
In addittion you can see that sometimes the Value come first or alone, and sometimes the Date come first or alone.
I will appreciate any good ideas,
Thanks in advance,
Aldo.
Modifying Data Column Names And Data Types
Mar 13, 2008I'm in the process of converting a rather huge VSAM database into a set of SQL tables.
I am using the same data names from the mainframe (like XDB-NAME to RDB-NAME).
I load the files using Import Export Data and it makes the tables with such column names as col001, col002, col003, etc... and always sets the data types to varchr(255).
And I have to cut and paste the data names from the manframe side to the server side (and the data types to.)
So, is there an easier way to do this? Or am I doomed to cut-n-paste my days away...
Thanks for any help.
Data Access :: Insert Rows To MS Access 2013 Without Listing Column Names
Nov 12, 2015We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.
Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?
Sql Types - Simple SQL Server Queries/handling Variable Types
May 26, 2005SQL Server 2000, ASP.Net 1.1
I've been writing this stuff for a while, and can't seem to come to the
conclusion of how I should be retrieving data and assigning this data
to variables.
Since i'm using SQL Server, I'm convinced that I should be using the
datareaders GetSqlDouble (or whatever) function, but this would mean i
need my local variables to be one of the SQL types. The problem
with that is, that there will have to be lots of conversions done by me
to be able to use a SQL type in my application.
For instance, I have a class where i'm retrieving dates. In order
to retrieve them correctly (Null values included), I need to retrieve
them with GetSqlDateTime(), then when it comes time to display the date
in a table, i must first check for nulls, then convert to a
string. This seems to be very cumbersome. Would I be better
off just using GetDateTime(), and the .ToString method, and ignoring
Sql Types all together?
so, basically, how are you guys using your sql server data? with
the supplied sql types, and doing all of the post-processing work
manually? I feel like i'm having trouble conveying my
issue...hopefully someone knows what i mean....i'd just like some
direction to save trouble in the long run, since i feel like there's
got to be a better way...
Confused!
Thanks,
JJ
TSQL - If Using INTO , Where Should I Look For The Table?
Sep 6, 2007Hi guys,
If I use the script below, where can I find the table "myTable"?
(I need to see the results of the query)
Thanks in advance,
Aldo.
Code Snippet
SELECT MyTable.MyField
INTO MyTable
FROM MyTable
Substract The Sum Of Two Doc Types In A Table
Jun 4, 2006I need to subtract the values of the totals of two document types in a table. So far I have the following with the results as shown.
SELECT TOP 100 PERCENT { fn MONTHNAME(DOCDATE) } AS [MONTH], DOCID, SUM(DOCAMNT) AS TOTAL
FROM dbo.tblSales
WHERE (DOCID = 'RTD') OR
(DOCID = 'INV')
GROUP BY DOCID, { fn MONTHNAME(DOCDATE) }
ORDER BY DOCID DESC
This results in the following:
Month DOCID TOTAL
MAY RTD 165752.87
MAY INV 18149416.37
I need one value showing the difference of the totals.
Thanks.
SQL Data Types
Dec 29, 2004Hello,
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!
Data Types
Aug 29, 2001I 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
SQL 7 Data Types
Oct 2, 2000I'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