How To Create A Query To Pull That Specific Row And All 50 Columns

Feb 7, 2012

I have a very large SQL Server table and want to pull all 50 columns that are in a certain row because something in that row has invalid varchar and is causing runtime errors. It is row 9054378701 and I am not sure how to create a query to pull that specific row and all 50 columns.

View 3 Replies


ADVERTISEMENT

Query To Pull Data From Multiple Columns (Three Email Fields)

Mar 12, 2015

I need a query to pull the data from Sql server. my requirement is i need to pull the data from multiple columns, in that there are three email fields are there like email1, email2, email3. i need query to retreive the data from table first it search for email in the above 3 fields if any one of the fields contains the record the it display as Main mail id.

View 6 Replies View Related

Updating Specific Columns If That Column Is Equal To A Value In One Query?

Dec 8, 2006

I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?

here is an example:

account | contact_id_1 | contact_id_2 | contact_id_3
433 | 67 | 23 | 67

so I want to set any contact_id_N = 0 where contact_id_N is = 67

so in the end, the table will look like this:

account | contact_id_1 | contact_id_2 | contact_id_3
433 | 0 | 23 | 0

is there a way to do it in one statement?

View 2 Replies View Related

T-SQL (SS2K8) :: Create Index For A Specific Query That Is Used A Lot?

Jul 28, 2014

I am trying to create an index for a specific query that is used a lot.

Unfortunately I am keep getting a clustered index scan. (4.6 mil rows)

Googling "AND" and "OR" is tricky, but did lead met to many articles about filtered indexes.

None of which gave me a clear answer.

How can I make an index for this query?

Here's the code setup

------------------------------------------------------------------------------------------
-- Setup
------------------------------------------------------------------------------------------
IF OBJECT_ID('FilterTbl') IS NOT NULL
DROP TABLE FilterTbl
CREATE TABLE FilterTbl
(
SurroIDInt IDENTITY PRIMARY KEY,
CompIDSmallInt,

[code]....

View 3 Replies View Related

Transact SQL :: How To Convert Row Specific Values Into Columns In Join Query

Aug 18, 2015

I am using stored procedure to load gridview,i want to show row specific values in coloumns , as i an working on daily timetable of college and There are three tables Week_Day,Daily_Timetable & Subject.Daily_Timetable has data which has week_day,class_id,Subject_id,Period_No.

Each day has 6 periods and each period is mapped with subject in daily timetable.From below sql i am getting 6 rows of monday.

But i want to show in a row weekname,period1_subject_id(Period_No=1),period2_subject_id(Period_No=2),period3_subject_id.......upto
period6_subject_id.

Please see my query below:-

SELECT     Week_Day.Week_Day_name, Subject.Subject_Code,  Daily_Timetable.Period_No
FROM         Week_Day LEFT JOIN
                      Daily_Timetable ON Week_Day.Week_Day_Id = Daily_Timetable.Week_Day_Id and Daily_Timetable.Class_Id=6  LEFT JOIN
                      Subject ON Daily_Timetable.Subject_Id = Subject.Subject_Id order by  Week_Day.Week_Day_Id ,Daily_Timetable.Period_No

View 4 Replies View Related

SQL Server 2012 :: Create A New Column By Dividing 2 Columns In The Query?

Mar 12, 2015

I have the following query that displays 2 values. I want to add a column with the percentage ([Providers With Security]

/ProviderTotal) * 100
SELECT (SELECT COUNT(DISTINCT NPI) FROM HS140_Rpt_Tmp_ForSummary WHERE Market = s.Market) AS ProviderTotal,COUNT(DISTINCT NPI) AS [Providers With Security]
FROM HS140_Rpt_Tmp_ForSummary s
WHERE s.[Security] = 'Yes'
GROUP BY Market

How can I do this?

View 1 Replies View Related

DISTINCT Only Certain Columns In On Table And Pull Their IDs

Mar 9, 2007

Please help. (I am using ASP, VB, SQL)I have a table with Office address information and it's ID. There could be a lot of offices in one city. But I would like to display only unique cities with certain names they start with and their id's.

View 5 Replies View Related

Select Statement To Pull Columns From Same Table For Different Ids

Oct 9, 2007

I'm wondering if there is a single statement I can write to pull my data. Let's say my Order table has one field for userId and one field for supervisorId (among other fields) both of which are foreign keys into the Users table where their name, address, etc. are stored. What I'd like to do is to pull all the rows from Order and have a join that pulls the user name and supervisor name from the User table all in one go. Right now I pull all the Orders with just user name joined, and then go back over the objects to add the supervisor name as a separate query.

The reason I'd like to do this is to simplify the objects I'm passing to the GridView by doing a single fetch instead of multiples. I'm using SQL Server, .NET 2.0 and VS.NET 2005.

Thanks

View 1 Replies View Related

Ssis Cannot Pull Columns From Stored Procedure?

Sep 6, 2007



hi,

i have the following stored proc which returns a resultset at the end, i have an SSIS package that calls this stored proc and outputs the result to a file. However, the package fails because it cannot pull the columns for the schema because of the return lines in the middle of the stored proc. If i remove the it works fine, pulls hte columns as normal. but if i leave them in ssis cannot get the columns. what can i do to get around this?



ALTER PROCEDURE [dbo].[uspCreateBrightPointFile]

AS

SET nocount ON

IF EXISTS (SELECT TOP 1 *

FROM brightpointfile)

TRUNCATE TABLE brightpointfile

-- Get the order information from the database where vendorconfirmationID = 0

INSERT INTO brightpointfile

SELECT o.orderid,

o.requestid,

'295193' AS araccountnumber,

o.orderdate,

'PRIORITY' AS shipmethod,

'Asurion Dobson' AS billname,

'PO Box 110808' AS billaddress1,

'Attn Account Receivable' AS billaddress2,

' ' AS billaddress3,

'Nashville' AS billcity,

'TN' AS billstate,

'37222' AS billzip,

c.fullname AS shipname,

Replicate(' ',100) AS shipaddress1,

Replicate(' ',100) AS shipaddress2,

' ' AS shipaddress3,

Replicate(' ',40) AS shipcity,

' ' AS shipstate,

Replicate(' ',10) AS shipzip,

'1' AS linenumber,

ve.sku AS itemcode,

o.quantity AS qty,

r.typeid,

r.customerid,

0 AS addressfound

FROM [order] o WITH (NoLock)

JOIN request r WITH (NoLock)

ON o.requestid = r.requestid

JOIN customer c WITH (NoLock)

ON r.customerid = c.customerid

JOIN (SELECT [subequipid],

[subid],

[clientequipid],

[serialno],

[statusid],

[startdate],

[enddate],

[createdate],

[createuserid]

FROM subequip s1 WITH (NoLock)

WHERE s1.statusid = 1

AND s1.startdate = (SELECT MAX(s2.startdate)

FROM subequip s2 WITH (NoLock)

WHERE s2.statusid = 1

AND s2.subid = s1.subid)) se

ON r.subid = se.subid

JOIN vendorequip ve WITH (NoLock)

ON se.clientequipid = ve.clientequipid

JOIN clientequip ce WITH (NoLock)

ON ce.clientequipid = se.clientequipid

WHERE vendorconfirmationid IS NULL -- order was never sent to CellStar

AND ve.typeid IN (1) -- only pull direct fulfillment (not store fulfillment)

AND ve.vendorid IN (2) -- only pull vendor = CellStar Insurance

AND o.orderdate > '2007-08-01' -- only pull orders after 08/01/2007

--IF @@ERROR <> 0

--RETURN 1

--First use the address types of 2

UPDATE brightpointfile WITH (ROWLOCK)

SET shipaddress1 = b.address,

shipaddress2 = b.address2,

shipcity = b.city,

shipstate = b.stateid,

shipzip = b.zipcode,

addressfound = 1

FROM customeraddress a WITH (NOLOCK),

address b WITH (NOLOCK)

WHERE a.customerid = brightpointfile.customerid

AND b.addressid = a.addressid

AND a.typeid = 2

--IF @@ERROR <> 0

--RETURN 2

--Update the rest where the address type is 1 and there is no type 2

UPDATE brightpointfile WITH (ROWLOCK)

SET shipaddress1 = b.address,

shipaddress2 = b.address2,

shipcity = b.city,

shipstate = b.stateid,

shipzip = b.zipcode

FROM customeraddress a WITH (NOLOCK),

address b WITH (NOLOCK)

WHERE a.customerid = brightpointfile.customerid

AND b.addressid = a.addressid

AND a.typeid = 1

AND brightpointfile.addressfound = 0

--IF @@ERROR <> 0

--RETURN 3

--Select all the records from the temp table, plus union in 2 extra records required by the client

SELECT *

FROM (SELECT orderid,

requestid,

araccountnumber,

orderdate,

shipmethod,

billname,

billaddress1,

billaddress2,

billaddress3,

billcity,

billstate,

billzip,

shipname,

shipaddress1,

shipaddress2,

shipaddress3,

shipcity,

shipstate,

shipzip,

1 AS linenumber,

itemcode,

qty,

0 AS additional

FROM Brightpointfile WITH (nolock)

UNION ALL

SELECT orderid,

requestid,

araccountnumber,

orderdate,

shipmethod,

billname,

billaddress1,

billaddress2,

billaddress3,

billcity,

billstate,

billzip,

shipname,

shipaddress1,

shipaddress2,

shipaddress3,

shipcity,

shipstate,

shipzip,

brightpointdefaultsku.linenumber,

brightpointdefaultsku.sku,

1,

1 AS additional

FROM Brightpointdefaultsku WITH (nolock),

Brightpointfile WITH (nolock)) a

ORDER BY orderid,

additional

IF @@ERROR <> 0

RETURN 1

RETURN 0

View 3 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related

If I Had Any Hair Left I'd Pull It Out. How Do I Create A SSIS Package Which Creates A Table That Never Expires?

Mar 6, 2007

Hello everyone,

I'm not at all comfortable with SSIS so please forgive me if I overload you all with information here:

I need to create a data table using SSIS which does not delete the previous days data. So far all the data tables we use to write reports in Visual Studio are constructed in SSIS as follows.

1 - Excecute SQL Task - DELETE FROM STOCK
2 - Data Flow Task
3 - Data Reader Source - SELECT * FROM ODBCDATASOURCE
4 - OLE DB Destination (Creates table STOCK)

The data tables which are created this way are stored in a data warehouse and scheduled to refresh once a day, which means that any data from yesterday is lost when the updates run. So, I tried to create a table which never has its previous days' data deleted by using just the last three steps above - and it worked great in Visual Studio, no problem at all. However, when I added this SSIS Package to the Update Job in SQL Server Management Studio, the job totally rejected the packed with the message: "The command line parameters are invalid. The step failed".

I thought I could work around this problem by asking the job step to excecute a simple SQL query to insert the data from table1 into table2 (and would thus negate the need for a SSIS Packege at all), but it threw me a curve ball with some message about not being able to use proxy accounts to run T-SQL Scripts.

If anyone knows how to create a SSIS package in which the data never expires please could you impart some wisdom my way. I only need to do this once for a specific report.

Please, when answering, bear in mind that I'm a simple fellow with little understanding of the inner workings of SQL Server and its various components, so please use short sentences and simple words.

Thanks in advance,

Chris

View 25 Replies View Related

Need A Way To Switch Specific Data From Columns

Jul 13, 2005

Basically I have 635k records in a table with a person's first name, and date of birth (other stuff but it's not relavent). I imported all the data from excel files, but somehow a bunch of records got the first name and date of birth mixed up, so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort. Now records are in fact repeated so another possible but more time taking solution is to write a stored procedure that I give the date of birth and it does the switching around for the respective date of birth when it's found inside the First name. Any suggestions? All the code I've written has proved useless :/

View 1 Replies View Related

BCP Bulk Insert Into Specific Columns

May 20, 2008

I am trying to do a BULK insert using BCP and a XML format file.

I have a file with 32 columns, and a table with 34 columns that i want to bulk insert

i have read here:

http://msdn.microsoft.com/en-us/library/ms179250.aspx

to try and do the bulk insert. but it doesn't seem to insert the data into the correct columns.

here is the SQL to create the table.


SQL Code:






Original
- SQL Code





CREATE TABLE [dbo].[prep_SomeTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Last_Update] [smalldatetime] null,
[ClientID] [varchar](10) NOT NULL DEFAULT (''),
[pollSystem] [varchar](10) NOT NULL DEFAULT(''),
[vin] [varchar](17) NOT NULL DEFAULT(''),
[year] [varchar](4) NOT NULL DEFAULT(''),
[make] [varchar](50) NOT NULL DEFAULT(''),
[model] [varchar](50) NOT NULL DEFAULT(''),
[trim] [varchar](50) NOT NULL DEFAULT(''),
[mileage] [varchar](10) NOT NULL DEFAULT(''),
[factColor] [varchar](100) NOT NULL DEFAULT(''),
[intFactColor] [varchar](100) NOT NULL DEFAULT(''),
[doors] [varchar](10) NOT NULL DEFAULT(''),
[newused] [varchar](10) NOT NULL DEFAULT(''),
[stockno] [varchar](10) NOT NULL DEFAULT(''),
[cpo] [varchar](10) NOT NULL DEFAULT(''),
[certType] [varchar](50) NOT NULL DEFAULT(''),
[certid] [varchar](10) NOT NULL DEFAULT(''),
[otherprice] [varchar](10) NOT NULL DEFAULT(''),
[webprice] [varchar](10) NOT NULL DEFAULT(''),
[invprice] [varchar](10) NOT NULL DEFAULT(''),
[msrp] [varchar](10) NOT NULL DEFAULT(''),
[mktClass] [varchar](50) NOT NULL DEFAULT(''),
[options1] [varchar](8000) NOT NULL DEFAULT(''),
[instock] [varchar](50) NOT NULL DEFAULT(''),
[manfModelNum] [varchar](50) NOT NULL DEFAULT(''),
[trans] [varchar](100) NOT NULL DEFAULT(''),
[enginedesc] [varchar](100) NOT NULL DEFAULT(''),
[drivetrain] [varchar](100) NOT NULL DEFAULT(''),
[overviewtext] [varchar](8000) NOT NULL DEFAULT(''),
[options2] [varchar](8000) NOT NULL DEFAULT(''),
[chromestyleid] [varchar](10) NOT NULL DEFAULT(''),
[photourls] [varchar](8000) NOT NULL DEFAULT(''),
[photosupdated] [varchar](10) NOT NULL DEFAULT(''),
CONSTRAINT [PK_prepSomeTable] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON [PRIMARY]






 CREATE TABLE [dbo].[prep_SomeTable](    [ID] [int] IDENTITY(1,1) NOT NULL,    [Last_Update] [smalldatetime] NULL,    [ClientID] [varchar](10) NOT NULL DEFAULT (''),    [pollSystem] [varchar](10) NOT NULL DEFAULT(''),    [vin] [varchar](17) NOT NULL DEFAULT(''),    [year] [varchar](4) NOT NULL DEFAULT(''),    [make] [varchar](50) NOT NULL DEFAULT(''),    [model] [varchar](50) NOT NULL DEFAULT(''),    [TRIM] [varchar](50) NOT NULL DEFAULT(''),    [mileage] [varchar](10) NOT NULL DEFAULT(''),    [factColor] [varchar](100) NOT NULL DEFAULT(''),    [intFactColor] [varchar](100) NOT NULL DEFAULT(''),    [doors] [varchar](10) NOT NULL DEFAULT(''),    [newused] [varchar](10) NOT NULL DEFAULT(''),    [stockno] [varchar](10) NOT NULL DEFAULT(''),    [cpo] [varchar](10) NOT NULL DEFAULT(''),    [certType] [varchar](50) NOT NULL DEFAULT(''),    [certid] [varchar](10) NOT NULL DEFAULT(''),    [otherprice] [varchar](10) NOT NULL DEFAULT(''),    [webprice] [varchar](10) NOT NULL DEFAULT(''),    [invprice] [varchar](10) NOT NULL DEFAULT(''),    [msrp] [varchar](10) NOT NULL DEFAULT(''),    [mktClass] [varchar](50) NOT NULL DEFAULT(''),    [options1] [varchar](8000) NOT NULL DEFAULT(''),    [instock] [varchar](50) NOT NULL DEFAULT(''),    [manfModelNum] [varchar](50) NOT NULL DEFAULT(''),    [trans] [varchar](100) NOT NULL DEFAULT(''),    [enginedesc] [varchar](100) NOT NULL DEFAULT(''),    [drivetrain] [varchar](100) NOT NULL DEFAULT(''),    [overviewtext] [varchar](8000) NOT NULL DEFAULT(''),    [options2] [varchar](8000) NOT NULL DEFAULT(''),    [chromestyleid] [varchar](10) NOT NULL DEFAULT(''),    [photourls] [varchar](8000) NOT NULL DEFAULT(''),    [photosupdated] [varchar](10) NOT NULL DEFAULT(''),    CONSTRAINT [PK_prepSomeTable] PRIMARY KEY CLUSTERED         (            [ID] ASC        )) ON [PRIMARY]



the format file:


Code:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="11" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="12" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="13" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="14" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="15" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="16" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="17" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="18" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="19" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="20" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="21" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="22" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="23" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="24" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="25" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="26" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="27" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="28" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="29" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="30" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="31" xsi:type="CharTerm" TERMINATOR="|" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS" />
<FIELD ID="32" xsi:type="CharTerm" TERMINATOR="
" MAX_LENGTH="8000" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="ClientID" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="pollSystem" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="3" NAME="vin" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="year" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="5" NAME="make" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="6" NAME="model" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="7" NAME="trim" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="8" NAME="mileage" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="9" NAME="factColor" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="10" NAME="intFactColor" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="11" NAME="doors" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="12" NAME="newused" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="13" NAME="stockno" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="14" NAME="cpo" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="15" NAME="certType" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="16" NAME="certid" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="17" NAME="otherprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="18" NAME="invprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="19" NAME="webprice" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="20" NAME="msrp" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="21" NAME="mktClass" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="22" NAME="options1" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="23" NAME="instock" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="24" NAME="manfModelNum" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="25" NAME="trans" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="26" NAME="enginedesc" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="27" NAME="drivetrain" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="28" NAME="overviewtext" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="29" NAME="options2" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="30" NAME="chromestyleid" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="31" NAME="photourls" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="32" NAME="photosupdated" xsi:type="SQLVARYCHAR"/>
</ROW>
</BCPFORMAT>



and the SQL:


SQL Code:






Original
- SQL Code





BULK INSERT prep_SomeTable
FROM '\devd_driveftproot7415_519.dat'
WITH ( FIRSTROW = 2, FORMATFILE = '\devd_driveformat_files est.fmt' )






 BULK INSERT prep_SomeTableFROM '\devd_driveftproot7415_519.dat' WITH ( FIRSTROW = 2, FORMATFILE = '\devd_driveformat_files est.fmt' ) 



error message:

Quote: Msg 295, Level 16, State 3, Line 1
Conversion failed when converting character string to smalldatetime data type.

I am not sure why it is trying to insert into the Last_Update Column, which is the only smalldatetime field in the table. but in the format file, i do not have any data going into that column.

it looks like everything is set correctly in the format file

in the SQL Server Library Link, section "Using an XML Format File" 2nd example, it looks to be the same thing as I am?

not sure what I am doing wrong?

View 1 Replies View Related

Forcing Specific Distinct Columns....

Dec 6, 2006

Hi Guys,

I have a slight problem, a query that i have written produces data with 2 primary keys the same... however, DINSTINCT wont work in this case as the rows are still different...

Is their a way to force 1 column to always be unique?

Heres the query:


SELECT TOP 5 ORDER_ITEM.ItemID AS 'Item ID', ITEM.ItemName AS 'Item Name',
(SELECT SUM(OrdItem2.ItemQuantity) FROM ORDER_ITEM OrdItem2
WHERE OrdItem2.ItemID = ORDER_ITEM.ItemID
) AS Total_Purchased, SUM(ORDER_ITEM.ItemQuantity) AS 'Customer Purchased',
CUSTOMER.customerForename AS 'Customer Forename',
CUSTOMER.customerSurname AS 'Customer Surname'
FROM ITEM, ORDER_ITEM, ORDER_T, CUSTOMER
WHERE ITEM.ItemID = ORDER_ITEM.ItemID
AND ORDER_ITEM.OrderID = ORDER_0510096.OrderID
AND ORDER_T.CustomerID = CUSTOMER.CustomerID
GROUP BY ORDER_ITEM.ItemID, ITEM.ItemName,
CUSTOMER.customerForename, CUSTOMER.customerSurname
ORDER BY Total_Purchased DESC


The query is supposed to select the TOP 5 Products sold as well as selecting the customer that purchased the greatest amount of that item and the amount they purchased.

Currently, i will get 2 duplicate rows (except for customers name and the items the purchased. Like this:

ItemID
83630Mathew Smith
8 366Tony Wattage

Which is kinda annoying.... is there anyway i can prevent this?

And also apart from the Where Joins... is there a more efficient way of writing this?

thx for reading :-)

--Philkills

View 14 Replies View Related

Simple SQL To Update Specific Columns

Aug 31, 2005

Kairn writes "I have created a table and imported data from another table to it. I need to update the existing data with values from another import but only need specific columns and am assuming a stored procedure is the way to go...IF I create a source table as well as destination(?). I have to compare the value in 6 columns to find the matching record in the destination, then update 5 of the other columns where source=dest. Finally, I need to sum, by row, the value in 3 of the columns and update another column to reflect it. All rows are unique in the destination. The source is a duplicate of them. Basically, the rows are identical aside from the values in the columns I want transferred. The rows must remain unique. I am a newbie and have no idea how to do this. Would you please submit a basic outline and I can fill in the rest?

Thank-you very much for your time and expertise,
Kairn"

View 1 Replies View Related

SQL 2012 :: Restore Specific Columns From A Database

Dec 15, 2014

I have an SQL .bak file and I would only like to restore specific columns as one of the columns is a free text field and is substantially increasing the size of the file. I can't restore it due to disk space constraints so dropping the column isn't possible if I can't get the table into a database locally.

View 1 Replies View Related

Duplicate Data In Table For Specific Columns

Nov 27, 2015

I have an table with duplicate data. I need to delete the duplicate records based on the RequestId.

I want to delete the records based on the RequestID 1001, Delete the duplicates in 1002, 1003 .

DECLARE @table TABLE ([Employee_SID] [int] NOT NULL,
[CalculatedTotalSalesYear] [numeric](19, 2) NULL,
[CalculatedTotalSalesYearAnnualized] [numeric](19, 2) NULL,
[RequestID] [int] NOT NULL)

[Code] ....

Expected Result

select * from @table

View 4 Replies View Related

RDA Pull Problem: Command=PULL Hr=80040E4D Login Failed For User 'test'

Apr 25, 2007

Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:






Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))

{

engine.CreateDatabase();

}

serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))

{

rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");

}





Everythink works fine, when I use 'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I found following line:




Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456



The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.



So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?



Please help.



Thank you.

Fipil.



View 10 Replies View Related

How To Create This Table With Specific Heirarchy

Feb 20, 2007

Hi i need to create this query. I have a table here named "Subject" from the database "Filteredtask". I want the output to show in this way:

Subject Quantity

Application Recieved count of application recieved
Process Application count of process application
Screen Application count of screen application
Initial Interview count of initial interview
Technical Exam and Interview count of technical exam and interview

I have used this code. My problem is that i put it in descending order to obtain this output, but if for example Screen application had more count it will ruin the hierarchy of the subject.

SELECT subject, COUNT(subject) AS Expr1
FROM dbo.FilteredTask
WHERE (subject = N'application received') OR
(subject = 'process application') OR
(subject = 'screen application') OR
(subject = N'initial interview') OR
(subject = 'profiles assessment') OR
(subject = 'technical exam and interview')
GROUP BY subject
ORDER BY expr1 DESC

__________________________________________________
Your future is made by the things you are presently doing.

Andrew

View 4 Replies View Related

Select Columns For Specific Data From All Tables In DataBase

Oct 8, 2007

Hi friends,I need to  select columns for specific  data  from all tables in DataBasePls give me reply asap Regards,M.Raj  

View 4 Replies View Related

SQL 2012 :: How To Find Unique Rows Considering Only Specific Columns

Apr 18, 2014

write a query which retrieves only unique rows excluding some columns.

IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19393Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19396New YCustomer One 3732.0 21-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14

In the above table project number and id shouldn't be considered and I should get the unique rows considering rest of columns and sorted based on date. Expected result is

IdStatusmanager Team Comments Proj number Date
19391New XUnassigned One 3732.0 16-Apr-14
19392Can YCustomer Two 3732.0 17-Apr-14
19394Can YCustomer One 3732.0 18-Apr-14
19395New YCustomer One 3732.0 19-Apr-14
19397New ZCustomer One 3732.0 20-Apr-14
19396New YCustomer One 3732.0 21-Apr-14

View 4 Replies View Related

Transact SQL :: How To Count Number Of Specific Columns Of Table

Jul 11, 2015

I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.

i.e. EmployeeID: 001

week1: 40
week2: 24
week3: 24
week4: 39

This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?

View 3 Replies View Related

DB Design :: Copy Specific Columns For Source To Destination

Nov 4, 2015

I am trying to Import data into SQL server 2008 using management studio.  The source data is an Access dbase.  I am trying to do this with queries as the tables do not match but I do need to copy specific columns for the source to the destination. Any brief example selecting a column from the source table, and just entering a dummy value for other columns(other column of data for destination table does not exist in source table). 

For the example

Source access dbase, just two columns but no primary key, T2dbase, Employee

New table.

First Name, Description
 Tom          , manager

Destination dbase SQLServer (T1dbase,Employee table), note 4 columns
Primary key NameID, FirstName nvarchar (20), LastName nvarchar (20), Description nvarchar(20)

View 5 Replies View Related

Updating Specific Columns Through A Stored Procedure At Runtime

Feb 25, 2008

I have a question. I know its possible to create a dynamic query and do an exec @dynamicquery. Question: Is there a simpler way to update specific columns in a table at run time without doing if else for each column to determine which is null and which has a value because i'm running into a design dilemna on how to go about it.

FYI: All columns in the table design are set to null by default.

Thanks in advance,

Dimeji

View 4 Replies View Related

Question - Replicating Schema Without Data For Specific Columns

Aug 30, 2007

Good morning,

I have a table that I am including in replication. However, I do NOT want the data for one of its columns to be included in the replication. Meaning, I want all of the schema and all of the data EXCEPT for a single column.

How do I do this?

I have searched the forum for some ideas, but did not find any.

Thanks in advance...

View 13 Replies View Related

BULK INSERT From A Text (.csv) File - Read Only Specific Columns.

Apr 23, 2008

I am using Microsoft SQL 2005, I need to do a BULK INSERT from a .csv I just downloaded from paypal. I can't edit some of the columns that are given in the report. I am trying to load specific columns from the file.

bulk insert Orders
FROM 'C:Users*******DesktopDownloadURL123.csv'
WITH
(
FIELDTERMINATOR = ',',
FIRSTROW = 2,

ROWTERMINATOR = ''
)

So where would I state what column names (from row #1 on the .csv file) would be used into what specific column in the table.



I saw this on one of the sites which seemed to guide me towards the answer, but I failed.. here you go, it might help you:

FORMATFILE [ = 'format_file_path' ]

Specifies the full path of a format file. A format file describes the data file that contains stored responses created using the bcp utility on the same table or view. The format file should be used in cases in which:

The data file contains greater or fewer columns than the table or view.


The columns are in a different order.


The column delimiters vary.


There are other changes in the data format. Format files are usually created by using the bcp utility and modified with a text editor as needed. For more information, see bcp Utility.

View 12 Replies View Related

Checking Specific Columns Were NULL On Previous Insert (Using If EXISTS)

Aug 3, 2007

Thanks for your time:

Background: After Insert Trigger runs a sproc that inserts values into another table IF items on the form were
populated. THose all work great, but last scenario wont work: Creating a row insert based on Checking that all 22 other items from the prior insert (values of i.columns) were NULL:

IF EXISTS(select DISTINCT i.notes, i.M_Prior, i.M_Worksheet, ...
from inserted i
WHERE i.notes IS NOT NULL AND i.M_Prior = NULL AND i.M_Worksheet = NULL AND...)


BEGIN

Insert into dbo.Items2Fix ...

From inserted i

END

View 1 Replies View Related

How To Create Logs Of Users And The Specific Transaction He Made?

Mar 4, 2008

hi..i need help on how to create user logs with a specific transaction he made. for example, he added new item in the database, the grid wud contain user id, date, time and transaction. help po.. tnx!

View 6 Replies View Related

Integration Services :: How To Create CSV File At A Specific Location To Export To

Oct 30, 2015

Problem: I need to export data from a table in a database. I have an SSIS that converts data to this table. To export the data using the SSIS the file needs to be already created (from my understanding). This export location is on another server and the folder location is empty.

Question: How do I create an empty CSV file at this location using either a Script Task, SQL Query, or Space Magic? I have been searching all over for about 4 hours now to no avail.

View 3 Replies View Related

Create Output File In A Specific Layout - Extra Spaces Between Fields

Mar 17, 2014

I'm trying to create an output file in a specific layout. For some reason my output file is adding an extra 10 spaces between the Account Number and the Check Number in the statement below. The rest of the output file looks fine. Where the extra 10 spaces are coming from? I need 1 Filler Space between these fields.

SELECT DISTINCT
CASE p.PaymentMethodID WHEN 10 THEN 'I' WHEN 60 THEN 'V' WHEN 50 THEN 'S' ELSE 'I' END
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(CHAR(20), ba.AccountNumber))
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 18 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(18), p.CheckNumber))

[Code] .....

View 2 Replies View Related

Help Needed: Granting Create Table Permisions On Specific Schema Options

Sep 6, 2007

Having some trouble getting my head around setting access to specificschemas- here's my problem:I've created a specific schema that I only want certain users tocontrolProblem: Even though I give them full access....the cannot createtables under that schema...my code is below (flyer is the schema,eflyerAdmin is the role, and eflyer is the user):GRANTALTER,CONTROL,DELETE,EXECUTE,INSERT,REFERENCES,SELECT,TAKE OWNERSHIP,UPDATE,VIEW DEFINITIONON SCHEMA::flyerTO eflyerAdminGO-- Add an existing user to the roleEXEC sp_addrolemember N'eflyerAdmin', N'eflyer'

View 1 Replies View Related

Reporting Services :: How To Create Multi-valued Parameter For Only One Specific Condition

Jul 21, 2015

I have report where i should create a report which is multivalued parametr report,but here my condition is i want to see only one county name  in my output that is Ex: Asia Specific (CountryName),I dnt want to see the other Country names,So how should we create for this condition .

View 5 Replies View Related

Query To Pull Lastest Date Record

Apr 16, 2008

How do I pull only the latest shipdate record from a multiple record key?

I tried the following, but it doesn't pull only the lastest date record:

Select catnum, Max(shipdate) as shipdate from table_a group by catnum, shipdate

Thanks in advance.

View 7 Replies View Related







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