T-SQL (SS2K8) :: Actual Text Value Of Null

Jun 24, 2015

I have an instance where a process is trying to insert a Customer into the Customers table and CustomerLastName is a non nullable field. Customer's last name is Null. Should this be the reason this Customer is never in the end table?

View 5 Replies


ADVERTISEMENT

How Can I Get Actual Operation Cost From Actual Execution Plan?

Jul 25, 2006

I have a view in SQLServer 2005. It took 30 sec. to finish. Then I deleted 4500 records from one table that is used in view. It took 90 sec. to finish now. I did a comparison on Actual Execution Plan between before I deleted data and after I deleted data, they are almost same, only different is Actual Number Rows become less after deleted data. So, I wonder why data become less but time become more. When I look closely on the Actual Execution Plan, the ridiculous thing is, there are only Estimated Operation Cost on each step, no Actual Operation Cost. I guess there are something wrong with optimizer because reuse same Execution Plan, but how can I tell which step wrong without Actual Operation Cost.

Thanks!

Henry

View 2 Replies View Related

T-SQL (SS2K8) :: Insert Text In A Text Field

Jul 12, 2014

CREATE TABLE [dbo].[instructions](
[site_no] [int] NOT NULL,
[instructions] [text] NULL
)
Select top 3 * from instructions

Output

Site_noInstructions
20Request PIN then proceed
21Request PIN if wrong request name
22Request PIN allowed to use only numbers

All text instructions start with “Request PIN” but after that the text are different for every site_no

I need insert in all site_no rows and after the “Request PIN” the text “and codeword” keeping the current rest of text

Desired output

Site_noInstructions
20Request PIN and codeword then proceed
21Request PIN and codeword if wrong request name
22Request PIN and codeword allowed to use only numbers

View 3 Replies View Related

Power Pivot :: How To Convert Measures As Text To Actual Measures

Jul 5, 2015

I have an old model that unfortunately had to be re-establish. 

In order to save time, I thought that I can export all my measures and paste it as measures in my new model. 

I used the following technique to export the measures from the old file: [URL] ....

How to use the output and create the identical measures in my new model, without the need to manually write each one of them?

View 7 Replies View Related

T-SQL (SS2K8) :: Always Set A Field To NULL

Jan 26, 2015

I would like to force a column to be null all the time. I cannot alter the table structure or alter the code that inserts data.

create table dbo.tblCustomer
(
CID int IDENTITY(1,1) not null,
Fnamevarchar(20) not null,
Lnamevarchar(20) not null,
Extravarchar(20) null

[Code] ....

So when this is executed the field Extra is always NULL

INSERT INTO tblCustomer (Fname, Lname, Extra)
VALUES ('bob', 'smith', 'ignore'), ('jane', 'doe', 'empty')
update dbo.tblCustomer set Extra = 'something'

If I've understood After triggers correctly the data will be written and the trigger will fire and overwrite. To avoid 2 writes

I could create an INSTEAD OF trigger

CREATE TRIGGER TR_I_Customer
ON tblCustomer
INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblCustomer
(Fname, Lname, Extra)
SELECT Fname, Lname, NULL
FROM Inserted
END

This will not write the "extra" field twice. However if a new Nullable column were added; it would be v.easy to forget to update the Instead Of trigger. Everything would still work OK but I would be effectively ignoring the new column as well.

What I would like in the instead of trigger is do something like this...

UPDATE INSERTED SET Extra=NULL

Continue with insert without supplying column /value list.

What would be the best way of achieving this, trigger is the only way I could think of?

View 6 Replies View Related

T-SQL (SS2K8) :: Get Last Non Null Value From Column

Apr 14, 2015

I have to populate a table of exchange rates which is easy enough however because rates are held on Fridays but I need to make calculations on weekends or holidays I need to populate the Friday rate on non weekends and holidays. I have a sample table below with null values on the weekends for the last 90 days but need a script that will show the Friday exchange rate on Saturday and Sunday

Here was my latest attempt

;with cte as
(
select currxdate, [from], [TO], CurrXRate
from dbo.CurrXchange
)
select a.CurrXDate, a.[From],a.[To]
, isnull(a.CurrXRate, b.currxrate) as 'CurrXRate'

[Code] ....

View 8 Replies View Related

T-SQL (SS2K8) :: Select One Of Three Values That Are Not NULL?

May 6, 2014

I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.

DECLARE @ColorList TABLE
(
CarID float
, ColorID int
)
INSERT INTO @ColorList
SELECT 1.55948815793043E+15, 9 UNION ALL
SELECT 1.55948815793043E+15, 27 UNION ALL

[code]....

So this would be the resultset:

1.55948815793043E+15, 27
1.62851796905743E+15, 27
1.51964586107807E+15, 9
1.55948815793043E+15, 27
1.47514023011517E+15, 5
1.64967408641916E+15, 27
1.51964586107807E+15, 9
1.56103326128036E+15, 27
1.49856249351719E+15, 9
1.5736407022847E+15, 6
1.64664602022073E+15, 27
1.51964244007807E+15, 27

View 3 Replies View Related

T-SQL (SS2K8) :: Select If Null Passed Else By INT

May 14, 2014

I'd like to be able to offer the option of selecting a project by the ProjectID number, and if a projectID is not supplied then the default result set would be select * from tbl_Projects.

ALTER PROCEDURE [USP_SelectProject]
-- Add the parameters for the stored procedure here
@ProjectNumber as int
AS
BEGIN

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Change A Selected Value To Null?

Dec 1, 2014

I am emailing data using this:

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
USE R4W_001
SET @xml = CAST(( SELECT ITEM_ID AS 'td','',"td/@align" = 'right', replace(convert(varchar,convert(Money, AVAILABLE),1),'.00','') AS 'td','',

[Code] ....

The result displays:

ITEM_ID Available DESC EXPCT DELV
AC 16,015Apples 01/18/2015
ORG12 2,908 Oranges 12/30/2014
AHA 3,355Apricots 01/01/1753

Our systems stores 01/01/1753 when the date field is blank. In the above TSQL, how can I replace 01/01/1753 with null or blank in teh results?

View 5 Replies View Related

T-SQL (SS2K8) :: Field Has No Value But Is Not NULL Or Empty?

Aug 13, 2015

I added a new field to an existing ETL process which uses SSIS to ingest a CSV file. The new field in the file, Call_Transaction_ID, will not always be populated for every record and so can be NULL or empty for certain records.

Here's the problem:After the file is extracted into a staging table, the Call_Transaction_ID field is showing blank or empty when it has no ID for that particular record. The problem is when I try to then ETL this data into a fact table - I'm trying to set the Call_Transaction_ID field to -1 if it is NULL or empty, however SQL Server doesn't see the field as empty even though there is no value in the field so -1 will NEVER return.

Using a WHERE DATALENGTH(Call_Transaction_ID) = 0 returns 0 records, again because SQL Server doesn't see the field as empty or NULL.

What do I do now to get around this? How do I fix it?

View 5 Replies View Related

T-SQL (SS2K8) :: How To Modify Procedure For Input Is Null Or Zero

Apr 30, 2014

I am creating web application for state and dist wise map. I've the tables like

create table ind_state
(
ind_stat_id int,
ind_state_name varchar(50)
)
insert into ind_state values ('1','Pondi')

[Code] .....

My output is depends on the dist selection so made procedure

alter procedure LAT_TEST_DYNAM0
@dist_id int
as
begin
create table #temp (pincode varchar(10) ,latitude numeric(18,10),longitude numeric(18,10) ,descp varchar(5000))
if @dist_id!=0

[Code] ....

Myself doubt is when @dist_id is null or "0" how to show default value ?

Otherwise above my code is correct?

View 4 Replies View Related

T-SQL (SS2K8) :: Check If Variable Is Empty Or Null?

Sep 9, 2014

declare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd

The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.

View 9 Replies View Related

T-SQL (SS2K8) :: List When Both Column Values Are Null

Feb 24, 2015

I have the following tables:

tbl_Person (personID, first_name, lastname)
tbl_student (studentID)
tbl_stupar (personID, StudentID, relationship)
tbl_person_Phone (personID, phone)

I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.

View 4 Replies View Related

T-SQL (SS2K8) :: Filtered Index With IS NULL Predicate

May 2, 2009

I believe query optimizer can leverage the predicate used to define the filtered index. But I came to a situation that's not true:

USE tempdb;
GO

IF OBJECT_ID('Employees') IS NOT NULL
DROP TABLE Employees;
GO

CREATE TABLE Employees

[Code] ....

If we take a look at the query plan. we'll find an unnecessary Nested Loop and Key Lookup operator there with the IS NULL predicate.

Ironically, If I change the IS NULL to IS NOT NULL in both the index and Query, everything goes fine.

View 9 Replies View Related

T-SQL (SS2K8) :: Comparison In The Merge Statement About Null Values?

Aug 22, 2012

I use the merge statement in a sproc to insert, update and delete records from a staging table to a production table.

In the long sql, here is a part of it,

When Matched and

((Student.SchoolID <> esis.SchoolID
OR
Student.GradeLevel <> esis.GradeLevel
OR
Student.LegalName <> esis.LegalName
OR
Student.WithdrawDate <> esis.WithdrawDate
Student.SPEDFlag <> esis.SPEDFlag
OR
Student.MailingAddress <> esis.MailingAddress)

Then update

Set Student.Schoolid=esis.schoolid,
.....

My question is how about if the column has null values in it.for example

if schoolID is null in production table is null, but in staging table is not null, will the <> return true.or if either side of <> has a null value, will it return true.

I don't want it to omit some records and causing the students records not get updated.If not return true, how to fix this?

View 9 Replies View Related

T-SQL (SS2K8) :: Find Null Values Between 3 Datasets In One Table

Mar 21, 2014

I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.

I have aSQL Fiddle that will allow you to visualize the schema.

The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.

select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)

[Code]...

View 2 Replies View Related

T-SQL (SS2K8) :: Inserting Multiple Records Each Containing Null Value For One Of Fields

Apr 11, 2014

I'm trying to insert multiple records, each containing a null value for one of the fields, but not having much luck. This is the code I'm using:

Use InternationalTrade
Go
CREATE TABLE dbo.ACCTING_ADJUST
(
stlmnt_instr_id varchar(20) null,
instr_id varchar(20) not null,

[Code] ....

View 5 Replies View Related

T-SQL (SS2K8) :: How To Quickly Update XML Column In A Table To NULL

Mar 9, 2015

I have a table with hundreds of millions of records and need to update an xml column to null. I do something like this:

UPDATE [Mydb].[MySchema].[MyTable]
SET [XMLColumn] = null

Currently it is taking around 6 hours.

Is there a quicker way to do this?

View 1 Replies View Related

T-SQL (SS2K8) :: OUTER JOIN Not Producing Non Existent Record With IS NULL

Aug 17, 2015

I have this View and want to also see Clubs that do not have current memberships.I have the IS NULL but not seeing the Clubs that do NOT have memberships. attribute.PersonMembership is a SQL table that has membership information.

SELECT dbo.v060ClubOfficersPresOrNot.ClubNo, dbo.v060ClubOfficersPresOrNot.SortName, dbo.v060ClubOfficersPresOrNot.ClubName,
dbo.v060ClubOfficersPresOrNot.BSProgram, dbo.v060ClubOfficersPresOrNot.ClubSection, dbo.v060ClubOfficersPresOrNot.Code,
RTRIM(attribute.PersonMembership.InvoiceNumber) AS InvNo, dbo.v060ClubOfficersPresOrNot.President, dbo.v060ClubOfficersPresOrNot.Email,

[Code]...

The "PersonMembership" has all the membership records from 2015 through 2019 of membertypeid 1-4 for the sampling.Since the syntax used in Access do not carry over without modifications to SQL, SQL syntax to make it work in SQL.And if you know the proper SQL syntax for "Between Year(Date())+IIf(Month(Date())>=7,1,0) And Year(Date())+IIf(Month(Date())>=7,1,0)+4" instead of what I currently have in SQL, that would be wonderful.

View 9 Replies View Related

T-SQL (SS2K8) :: Trigger With Text Type?

Aug 14, 2014

Dealing with 3rd party app that is somehow deleting records. Was going to put a delete trigger on the table so we can at least be notified of the delete and save the record. Great idea until I found out the table has a text data type for one of the fields which are'nt allowed on the delete or update portion of the trigger. Unfortunately the text data contains what we really need to retain. Tried doing a convert varchar and that would not work.

View 7 Replies View Related

T-SQL (SS2K8) :: CLR / RTF To Plain Text Not Working?

Sep 23, 2014

Trying to troubleshoot an CLR/ RTF to Plain Text issue I am having.

I have 3 instances on one SQL server. Only one of the Instances is not working. Even tried deleting everything and resetting it up.

Here is the code I am testing with:

USE Test_Database
GO
DECLARE @RTF varchar(max)
SET @RTF = '{
tfansiansicpg1252uc1deff0deflang1033{fonttbl{f0 Calibri;}{f1 Arial;}}{colortbl
ed0green0blue0 ;
ed255green255blue255 ;}viewkind4paperw12240paperh15840margl1425margr1425margt1425margb1425sectdpgwsxn12240pghsxn15840
marglsxn1425margrsxn1425margtsxn1425margbsxn1425pardfs21sl276slmult1sa180{f1fs21
Test New Progress Notes - Praveen}par}';
SELECT dbo.clr_fn_ConvertRTF2PlainText(@RTF)

Here is what I am getting back:

The operation completed successfully

Not sure what to check as the other two instances are working fine.

I have CLR enabled in sp_Configure

clr enabled 0 1 11

View 2 Replies View Related

T-SQL (SS2K8) :: FULL TEXT Search With CONTAINS

Oct 27, 2014

Example

Select *
from TableName Where Contains(ColumnToBeSearched, 'testtosearch')

QUESTION: Can the 'testtosearch' be another table field like ''' + <AnotherTBLField> + '''Is there anyway to make the population of 'testtosearch' to be based on another table field (say when you join to it)?If not what else can I do, another function maybe?

View 0 Replies View Related

T-SQL (SS2K8) :: First Occurrence Of The String From Text

Sep 4, 2015

I have text column .I want to find out first occurance of string based on logic.I defiend Text with examples and also mentioned expected result.I coloured the text in word document,due to some reasons not displaying same here.Attached as image below texts to understand more clear.

TEXT 1: 'ABNAGENDRACSURENDRADJITHENDRAXNARENDRABVEERNDARAXDRMNDRAXRVINDRABNAGENDRACSURENDRADJITHEN'

From the above text1, I want to get “AXNARENDR”.

Based on logic defined below:

First I have to search for string “A” Then next to ‘A’ it should not be “B” or “C” or “D”.It can be anything other thing these three.Combination of “A” otherthan “B” or “C” or “D”

In the example text I defined “A”,”X” defined three times .I want to capture few characters from the first occurrence of the string

i.e AXNARENDR (TEXT1 I defined “A” with 4th occur

TEXT 2:

'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABN
AGENDRACSURENDRADJITHENAYENDGHRABVEERNDARAXDRMNDRABNAGENDRACSURENDRADJITHENAYRVINDR'

From the above text2, I want to get “AYENDGHR”.

TEXT 3:

'ABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDR
ADJITHENABNAGENDRACSURENDRADJITHABNAGENDRACSURENDRADJITHENDRABNAGENDRACSURENDRADJITHEN
ABNAGENDRACSURENDRADJITHENABNAGENDRACSURENDRADJITHAZENIVKHRABVEERNDARAXDRMNDRAYRVINDR AZNHKLMN'

From the above text3, I want to get “AZENIVKHR”.

View 9 Replies View Related

T-SQL (SS2K8) :: Selecting Text Between Set Characters

Nov 2, 2015

I have a table of data with lines of various lengths. An example is

A Smith - Give #12345# Sydney City
B Jones and S Jones - Give #876543# Washington

I am trying to work out how to create a new column with just the number between the # symbols. The number would be between 5 and 9 numbers.

View 9 Replies View Related

T-SQL (SS2K8) :: Full-Text Search Over XML Columns

Jun 5, 2014

Are the XML tags searched, as well as, the contents within the tags, when an xml column is included in a full-text index? Or is it only the contents within the tags? Would I need to combine full-text search with XPath/XQuery to get at the actual tags?

View 0 Replies View Related

T-SQL (SS2K8) :: Using Bulk Insert With Text Qualifiers?

Aug 19, 2014

The files have pipe delimters and double quotes as text qualifiers. I can get the file to import with a bulk insert statement, but it brings in the double quotes in as well. What setting is it that can be set to indicate what the text qualifiers are?

Here is are a few sample lines of data:

"id"|"system_id"|"system"|"last_modified_on"|"status"
"1"|"30101"|"H1"|"2013-05-16 09:33:19"|"1"
"2"|"30100"|"H1"|"2013-05-16 16:22:32"|"1"
"3"|"30103"|"H4"|"2013-05-16 16:22:32"|"1"
"4"|"30104"|"H3"|"2013-05-05 01:26:20"|"1"

View 0 Replies View Related

T-SQL (SS2K8) :: Parsing Text Field Into 4 Fields

Sep 5, 2014

I have a text field that I need to parse out into 4 fields.

30.125x23.625-18.875x25.375
6.1875X19.375-2.4375x15.625
0X0-0x0
26.875X11.375-11.125x22.875
0X0-0x0
0X0-0x0
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
26.625X14.875-11.125x22.875
26.625X14.875-11.125x22.875

I need to 26.625X14.875-11.125x22.875 should be 26.625 then 14.875 then 11.125 then 22.875

View 5 Replies View Related

Full-Text CONTAINS Null

Jul 16, 2006

I have a Full-text search that is being performed on a variable (@Description)  see part of querie below:
WHERE (CONTAINS([Description], @Description)
This search only seems to work when a text fo 3 or greater characters is used Ball, but not for "an" or "a". it also does not search on part of a word i.e. "Gard" of "Garden"
Two things:
1) How do I perform the CONTAINS search for part of a word or "a".
2) How do I perform a search that returns all values, when I leave the input feild blank it returns no records.
Many thanks in advance

View 5 Replies View Related

T-SQL (SS2K8) :: Find And Replace Text For All Sprocs On A Server

Apr 24, 2014

There are plenty of scripts to do this on a per-DB level, but any that will allow me to generate a script for all DB's at once? Mine are split across dozens and it would be much easier to do a loop (using MS_ForeachDB ? )

View 1 Replies View Related

Converting NULL To A Text String

Aug 15, 2007

I'm using UPDATE as follows"

UPDATE Dubai
SET DB = 'D'
WHERE DB = NULL

but nothing happens. What am I doing wrong?

Thanks.

View 3 Replies View Related

T-SQL (SS2K8) :: Import To Table From Varying Tab Delimited Text Files

Feb 10, 2014

I need to import data to a MSSql table from massive (read: a million and a half rows, every single day) logs that come in .txt format separated in tabs with a ";" symbol and then have some stored procedures analyze that data to generate some reports in an excel file with that info. The text files include the column headers in the first row and the data starts on the second one.

The challenge is that the text files differ in column order and count every single day.

The analysis that I need to do only needs about 15 columns from the nearly 90-120 that those files include, and those columns sadly happen to be in a different order in those files.

View 8 Replies View Related

T-SQL (SS2K8) :: Extract Quoted Text Elements From Varchar Column

Nov 18, 2014

I need to extract specific text elements from a varchar column. There are three keywords in any given string: "wfTask," "wfStatus" and "displayReportFromWorkflow." "wfTask" and "wfStatus" can appear multiple times, but always as a pair and will each be followed by by "==" (with or without surrounding spaces). "displayReportFromWorkflow" is always followed by "(" and there can be spaces on either side. The text elements will be between a pair of double quotes, and following one of keywords. For each row, I need to return the task, status and report name.

declare @t table (rowID int, textValue varchar(1024))
insert @t
(rowID, textValue)
values

[Code] ....

Output:
rowID, Task, Status, ReportName
----- --------- ------- ------------------------
1, Issuance, Issued, General Permit
2, Issuance, Issued, Capacity Letter Type III
2, Review, Denied, Capacity Letter Type III

I started with a string splitter using the double quote character, referencing elements "i" and "i+1" where the text like '%wfTask%' or '%wfStatus%' or '%displayReportFromWorkflow%', but the case of multiple task/status in a row has confounded me so far.

Unfortunately, CLR is not an option.

View 1 Replies View Related

T-SQL (SS2K8) :: Script To Update Existing Full Text Catalog?

Mar 17, 2015

Have installed SQL Server 2008 R2 Express (includes SSMS tool) on Windows server 2008 R2 sp1 without any issues.Database created with no issues, full text catalog created via the wizard also with no issues but cannot run the process as a scheduled task of updating the catalog because the SQL agent is not available in the express version.

The full text index information is already being populated and updated by a third party application so this leaves just the catalog to be updated as and when new full text information is available.

I have a third party SQL scheduler which will run SQL scheduled tasks but requires a script to run the full text catalog update process

Is it possible to extract a script from the existing full text catalog to run the update process or how to create a script from scratch to do the same update catalog process in the third party scheduler?

View 1 Replies View Related







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