SQL Server 2008 :: Storing Column Value To A Column

Sep 9, 2015

I just have a question regarding storing values to a column in ms sql 2008.

Why is it that the value I inserted at the column is truncated when selected in a query.

The column for this is created to accept max. values.

-> Message VARCHAR(MAX) NULL

The string which I need to insert is a combination of characters with a length of 14,720.

According to some forums, the max value that a column can hold is 8000 chars. only (Is this true? even though I set it to MAX?)

View 7 Replies


ADVERTISEMENT

SQL Server 2008 :: Display A Column Alias As Part Of The Result Set Column Labels?

Feb 26, 2015

Is there a way to display a column alias as part of the result set column labels?

View 9 Replies View Related

SQL Server 2008 :: Create Table / Set Default Column Value To Value Of Another Column?

Mar 11, 2015

when creating a new table. How can I set the default value of the column to equal the value of another column in the same table?

View 5 Replies View Related

SQL Server 2008 :: Error - The Column Delimiter For Column Was Not Found

Mar 26, 2010

I am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.

View 9 Replies View Related

SQL Server 2008 :: How To Divide Value In Row 1 By The Value In Row 2 In Same Column

Mar 4, 2015

I try to take the value from row 1 divided by the value from row 2 but it is not working, here is the following:

Here is my table called Trans

ID Period Sales Profit
1 Current 20 5
2 Previous 40 20

I want to take 20 divided by 40(20/40), 5 divided by 20(5/20)

Here is the result I want:

ID Period Sales Sales_Per Profit Profit_Per
1 Current 20 50% 5 25%
2 Previous 40 50% 20 25%

Here is my query:

Select
(t1.Sales/NULLIF(t2.Sales,0) * 100) as Sales_Per
From Trans t1
INNER JOIN Trans t2
on t1.Id = t2.ID - 1

View 2 Replies View Related

SQL Server 2008 :: Extract Value From XML Column

Mar 12, 2015

I have table Called as ‘DC_BIL_ActivityLog’ and XML column name is ‘ActivityDescription’ in SQL Server 2012.

The following information stored on that Column. I want to read cancellation date (12/23/2015) using select statement.

<ActivityDescription>
<text value="PCN was initiated for Policy ^1 on 12/07/2015. Cancellation Date is: 12/23/2015. Amount needed to rescind PCN is: $XX.80." />
<link id="1" linkText="GLXXXP2015 12/02/2015 - 12/02/2016" linkType="policy">
<linkId parm="1" value="1140" />
</link>
</ActivityDescription>

View 8 Replies View Related

SQL Server 2008 :: How To Concatenate Column Value

Jul 29, 2015

I have a column name Classname and I would like to to Concatenate value.

ID ClassName
1 Class A
2 Class B
3 Class C
4 Class D

I need a output in ssrs report with title like (Class A, Class B, Class C, Class D) .

Can I do in SSRS as well ?

I tried join function in ssrs and I am getting #error join(Field!classname,",")

View 5 Replies View Related

SQL Server 2008 :: Getting Formula Of A Computed Column

May 25, 2011

I'm trying to write a query that will display the formula for a computed column in SQL Server 2008R2.

I have looked here: [URL] ....

and it say (at least I think) that I can look at the Formula property of COLUMNPROPERTY like this:

SELECT COLUMN_NAME ,
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsComputed'),
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'Formula'),
COLUMNPROPERTY(OBJECT_ID(TABLE_NAME),COLUMN_NAME,'IsDeterministic')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Event' AND COLUMN_NAME = 'CurrentAttendance'

I know the column is computed and I can see the formula in SSMS. I wanted to do this in T-SQL. How to get this value?

View 8 Replies View Related

SQL Server 2008 :: Export To Excel - Column With CR / LF

Jan 30, 2015

I have a request to export some table data to excel and the "notes" column (varchar 255) contains multiple lines separated by CR/LF. when I export to excel, the first record with CR/LF messes up the column alignment in excel, throwing off the format from that point on. how can i export to excel so that it preserves these CR/LF. or if not, how can I remove these characters so that excel can handle it?

See attached example

View 3 Replies View Related

SQL Server 2008 :: Get XML Text From Varchar Column

Jan 30, 2015

create table tblxmldata
(id int, xmltext varchar(max))
insert into tblxmldata values(1,'<associatedText><value type="PO">GTT taken</value></associatedText>')
insert into tblxmldata values(1,'<associatedText><value type="PO">Check sugar today please</value></associatedText>')

I want the output as

GTT taken
Check sugar today

View 9 Replies View Related

SQL Server 2008 :: Query A Column Of XML Files?

Feb 12, 2015

I have a table with lots of xml files in one column(more than 1000), like this

1. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...
2. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...
3. <content xmlns:xsi="http://www.w3.org/2001/XMLSchema...each is big

I want to query some values for all to see the duration but now I can only query one of them

declare @bp xml
select @bp=xml
from bloodpressureohneschema
;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type)
select * from(
select
m.c.value('(./bp:time/bp:value)[1]','date') as time,
m.c.value('(./bp:data/bp:items[1]/bp:value[1]/bp:magnitude)[1]','int') as value
from @bp.nodes('/bp:content/bp:data/bp:events') as m(c)
)m

is there somewhere I can make better?

View 5 Replies View Related

SQL Server 2008 :: How To Get The Column Type And Length

Feb 23, 2015

Below is a SQL statement that shows what columns belong to a certain view. My next question is how can I get a third column that shows the column type ?

VARCHAR(100), or UNIQUEIDENTIFIER or INTEGER or TEXT or NVARCHAR(256) or ........????

select top 100 a.name, b.name, from sys.views a inner join sys.columns b
on a.object_id = b.object_id where a.name like '%Case_Times%'

View 4 Replies View Related

SQL Server 2008 :: Add Calculated Row Item In Same Column

Mar 12, 2015

I need to add a calculated column item in the same column. Please see SQL Codes for both existing data and desired outcome.

Product O is added according to:

for 201501 Product O= sum of en_count for product Y,W,N when yrmnth=201501
for 201502 Product O= sum of sum of en_count for product Y,W,N when yrmnth=20150

SQL:

--Existing Data
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Table1') IS NOT NULL DROP TABLE #Table1

--===== Create the test table with
CREATE TABLE #Table1
(
product char(100),
yrmnth varchar(6),
en_count int,

[Code] ....

--Desired Outcome

IF OBJECT_ID('TempDB..#Table2') IS NOT NULL DROP TABLE #Table2

--===== Create the test table with
CREATE TABLE #Table2
(
product char(100),
yrmnth varchar(6),

[Code] ....

View 9 Replies View Related

SQL Server 2008 :: How To Update Certain Column From All Tables Within DB

Mar 19, 2015

I have a query, I am trying to update a certain column in my query you can see that is hard coded. The column that I am trying to update is "O_Test" I used a select statement trying to figure out how many records that accounts for with the entire database of all the tables. I got 643 records. So I am wondering if there is a way I can update all the columns without looking up each table and updating each one. This Update statement wont work because I am accounting for all records in the DB of all tables associated of what I hard coded

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%O_Test%'
ORDER BY schema_name, table_name;

View 5 Replies View Related

SQL Server 2008 :: Update Binary Column

Mar 25, 2015

The last two columns in one table is [StarText](varchar(20)) and [Star] (binary). It stored data like below:

StarTest---Star
***
**
Null
*****

How to write a update code to insert star image at column [Star]?

For example, at column [Star]
row1 insert 3 stars
row2 insert 2 stars
row3 keep null
row4 insert 5 stars

View 2 Replies View Related

SQL Server 2008 :: How To Compare The Date Column

Mar 30, 2015

I need to compare the date column. The date column is 2012-01-22 in this format.

So I need to convert getdate() into this format yyyy-mm-dd.

What is the style number I have to use?

I used the link [URL] but I didn't see this format.

View 5 Replies View Related

SQL Server 2008 :: Identity Column Insert

Apr 28, 2015

I have two tables having one row identifier column each of int datatype. Both these columns are part of the respective primary keys. Now as a part of my process, i'm inserting one small part of data from one table to another table. This was working fine but suddenly started getting error like

Violation of PRIMARY KEY constraint 'PK_TargetTable'. Cannot insert duplicate key in object 'DW.TargetTable'. The duplicate key value is (58544748).First I checked with DBCC CHECKIDENT with NORESEED and found that there is difference in the current identity value and current column value. I fixed it by running DBCC CHECKIDENT. But to my surprise again got the same issue. interesting thing is that the error comes after inserting 65466 records.

View 4 Replies View Related

SQL Server 2008 :: Finding Column Within All Tables In DB

May 1, 2015

I am trying to find a way where I can search for a column that is associated in all tables of the database. I have created a query but is not executing correctly.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t8
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Status%'
ORDER BY schema_name, table_name;

View 3 Replies View Related

SQL Server 2008 :: Trigger To Encrypt A Column?

Jul 23, 2015

Can I accomplish encrypting a column with a trigger similar to below, but without the 'SSN' column?

I added the SSN column just so I could pass an UNencrypted value. Seems round-about & clunky.

This works:

Insert into TestSSNs(UserID, SSN, Name) VALUES (NEWID(), '890-12-0007', 'Gina')

---------------------------------------------
CREATE TABLE TestSSNs (
UserIDuniqueidentifier,
SSNVarchar(11) , --plain text, should always be null
eSSN VarBinary(128), --encrypted value
[Name]Varchar(50)
);
CREATE TRIGGER dbo.IU_TestSSNs ON dbo.TestSSNs

[code]....

View 3 Replies View Related

SQL Server 2008 :: Concatenate Column Name With Values

Sep 17, 2015

I want to concatenate the column_names with the respective values.

Create table #test1 (Id int,Name varchar(10),Country varchar(10))

insert into #test1 values ( 1,'JOHN','USA'),
(2,'SAM','CANADA'),
(3,'HO','CHINA'),
(4,'RAM','INDIA')

select * from #test1

I have temp table with columns (Id,Name, Country). I want to concatenate column_name with their respective values; means i want column_header with every value for the column and then i want to concatenate multiple columns. I am looking for something like below:

ID values
1,NAME-john-COUNTRY-USA
2,NAME-SAM-COUNTRY-CANADA
3,NAME-HO-COUNTRY-CHINA
4,NAME-RAM-COUNTRY-INDIA

Note: This is just a sample .i am looking something dynamic because i have around 50 tables.i can do with query below but since i don't have static columns and table i am looking for something dynamic

select ID, (a+'-'+B) as Value from (
select ID,'NAME'+'-'+NAME as a,'Country'+'-'+Country as b from #test1

View 2 Replies View Related

SQL Server 2008 :: Split Values In The Column?

Oct 15, 2015

I've a table that has salescode(124!080) and salesamount(125.65!19.25) and I need to split the columns. Salesman(124) has commission(125.65). Here is the DDL:

USE tempdb;
GO
DECLARE @TEST_DATA TABLE
(
DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
, InvNoVARCHAR(10) NOT NULL
, SalesCode NCHAR(80) NOT NULL
, Amount NCHAR(80) NOT NULL

[code]....

View 6 Replies View Related

SQL Server 2008 :: Processes That Update A Certain Column

Oct 26, 2015

I have a table and a specific column inside this table. I know this table is being updated, by using sys.dm_db_index_usage_stats, I was able to determine this, by some process (stored procedure / SQL Job / etc), but the problem is, I am not sure what process is doing it.

How would I search our SQL Server 2008 database to find any process that manipulates this table / column (I only care about Inserts / Updates and Deletes, but do not really care for SELECT).

View 8 Replies View Related

SQL Server 2008 :: Redirect Insert Data Into Another Column

Feb 10, 2015

I was wondering if there was a way to redirect an insert to another column...

Example:
Original Insert Statement:
INSERT INTO [table] ([columnA], [columnB])
SELECT '2015-01-01 00:00:00', 99.99

We have changed [columnB] from a decimal(19,9) to a computed column. So instead, we added another column [ColumnC] to take [ColumnB]'s insert data. I thought we could've used a trigger instead of insert, but that fails with the message "cannot be modified because it is either a computed column or is the result of a UNION operator".

This is the trigger I was using:
CREATE TRIGGER [Trigger] ON [table]
INSTEAD OF INSERT
AS
INSERT INTO [table] ([columnA], [columnC])
SELECT [dataA], [dataB]
FROM Inserted

View 4 Replies View Related

SQL Server 2008 :: IPAddress - How To Split One Column Into Two Columns

Mar 6, 2015

I have a ipaddress column is there where i need to split the column into two columns because of

values like below

172.26.248.8,Fe80::7033:acba:a4bd:f874
172.26.248.8,Fe80::7033:acba:a4bd:f874

172.26.248.8,Fe80::7033:acba:a4bd:f874

I have written the below query but it will throw some error.

select SUBSTRING(IPAddress0, 1, CHARINDEX(',', IPAddress0) - 1) as IPAddress0
from IPADDRESS

error:

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

View 1 Replies View Related

SQL Server 2008 :: Converting Datatype Of A Computed Column?

Mar 10, 2015

I have a computed column that I want to cast as decimal.

The two columns it calculates from are both varchar.

Why can I cast the column as INT, but when I try to cast as decimal, I get the following error?

Arithmetic overflow error converting varchar to data type numeric.

what this error means and why I get it only when I want to cast to decimal.

View 8 Replies View Related

SQL Server 2008 :: Time Difference With Dates In Same Column

Mar 17, 2015

How do I find the time difference when the dates are in one column? I need to find hours and minutes between each row.

CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)
INSERT INTO #Time (TimeStamp)
VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')
, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')
DROP TABLE #Time

View 6 Replies View Related

SQL Server 2008 :: How To Return 1 Particular Column From Another Table In Join

Apr 25, 2015

I'm creating a sql stored procedure inside this proc it returns some information about the user, i.e location, logged in, last logged in, etc I need to join this on to the photos table and return the photo which has been set as the profile picture, if it hasn't been set then return the first top 1 if that makes sense?

The user has the option to upload photos so there might be no photos for a particular user, which I believe I can fix by using a left join

My photos table is constructed as follows:

CREATE TABLE [User].[User_Photos](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[PhotoId] [varchar](100) NOT NULL,
[IsProfilePic] [bit] NULL,

[Code] ....

Currently as it stands the proc runs but it doesn't return a particular user because they have uploaded a photo so I need to some how tweak the above to return null if a photo isn't present which is where I'm stuck.

View 3 Replies View Related

SQL Server 2008 :: Query A Column Of XML Files In A Table

Apr 30, 2015

I want to query a column of xml files in a table,

use mysql1
declare @bp xml
select @bp=xml
;WITH XMLNAMESPACES('http://schemas.openehr.org/v1' as bp,'http://www.w3.org/2001/XMLSchema-instance' as xsi,'OBSERVATION' as type)
select * from (
select
m.c.value('(./bp:data/bp:items[1]/bp:value[1]/bp:magnitude)[1]','int') as systolisch
from
BloodpressureMitSchema cross apply
@bp.nodes('/bp:content/bp:data/bp:events') as m(c))m

But with this "cross apply" I can only query all the values in one xml and repeat them. Is there something wrong at "declear"

View 2 Replies View Related

SQL Server 2008 :: Column Datatype Update Not Usable

May 26, 2015

I ran a db update script to update the datatype for a column from a tinyint to a smallint:

ALTER TABLE MyTable
ALTER COLUMN Age smallint

Then I ran another db update script to update the value in these 2 columns:

update MyTable
set Age = Age * 12,

However, the second db update script returns the following error:

Msg 220, Level 16, State 2, Line 1
Arithmetic overflow error for data type tinyint, value = 1440.
The statement has been terminated.

SSMS shows the column with the new data type of smallint. I even restarted SSMS but I still get the error above. 1440 is out of range for a tiny int but should be in range for a smallint. Both scripts need to be executed together in sequence by the release manager.

View 1 Replies View Related

SQL Server 2008 :: Where Clause - Decrypt Column Or Hash?

Jul 16, 2015

We're encrypting a column of Social Security numbers. Developer needs to execute a query -->similar<-- to:

Select <bunch of columns>
From dbo.People p
Where p.SSN = @Input_SSN_Param --I know this won't work.

If we write the where clause like

Where DecryptByKey(p.SSN) = @Input_SSN_Param

Won't every SSN in the table be decrypted? Seems like a bad idea, performance-wise.

Should I/we add a column to hold a hash of the SSN; and use the hash for comparisons?

View 3 Replies View Related

SQL Server 2008 :: Removing 00-00-00 From Varchar Column In Table?

Jul 29, 2015

I've look at several different methods for removing leading zero's from a column but I need to remove trailing data from a VARCHAR column. For some reason, the old database saved the time along side the date in my client's app.

For example:

The old database format "2015-07-28 00:00:00"

I need the data in this column in the new database to only be the date "2015-07-28", there are alot of rows with this issue.

Is there a query I can run to remove the 00-00-00 from all of the rows? Some of the fields actually have a time in there like this: 2015-07-28 12:15:35, with this one, I don't think it's going to be easy but if I could at least remove the 00-00-00 from all the rows that have it, that would be a good start.

View 9 Replies View Related

SQL Server 2008 :: Table And Column Name Where Given Text Is Matching

Jul 31, 2015

I need a script which will tell me Table name and column name where a given text is matching.

Example: search string = "I want to search something"

Output: Table T1 , Column C3.

DB: Microsoft SQL Server 2005 / 2008

View 1 Replies View Related

SQL Server 2008 :: Query To Read From Each Column Of XML Tags?

Jul 31, 2015

The below query will read the data in XML format but any query to read from each column of XML tags easily?

SELECT CAST(record AS XML), record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved