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 Replieswrite 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
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 RelatedToday we were trying to use restore wizard (recovery advisor) to restore a database which is over 80 GB, but the wizard won't load or start. However the wizard dialog box opens without any problems with all other databases stored in the instance. Is there any problem or reason to why the restore wizard won't come up?
This is SQL 2012 enterprise version we are running.
1.can we restore the db if it is offline?
2.restore information stores in table 'msdb..restorehistory' if we restore the db when the db is offline?
I am on SQL 2012 standard version and I am writing a script to restore database from .bak files on a network.
ALTER DATABASE DB1
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE
----Restore Database
RESTORE DATABASE DB1
FROM DISK = 'N:SQLBackupDailyDB1_backup_2015_06_22_194002_0500494.bak'
WITH REPLACE
ALTER DATABASE db1 SET MULTI_USER
GO
Since I have to restore about 100 databases , I am planning to put the script in a cursor. However my problem is how can I get the bak file name dynamically .
Sometime during the night last night some user account permissions were "lost". Am I right to think that restoring the master database would be the way to go? We have a 2 node 2012 cluster and I stop the cluster resource and start the db in single user mode from the active node. Somehow the sharepoint farm is still trying to connect so I can't get logged in single user. What method could I use to stop users from connecting when I don't have access to the sharepoint farm.
View 8 Replies View RelatedI'm working on a project where I need to build a small database and then copy it to a server at the client's site. I can't connect directly, so I have to use a VPN connection and use Remote Desktop, copy the database backup from my machine to the cloud, then download it to the client machine. The project is still in the early stages, and the client is still sending me data in CSV files and Excel spreadsheets. I'm periodically needing to do a complete refresh of the database at the client. I've hacked my way through it a couple of times, but I need to know the proper way to do it. I get errors on the restore step, telling me the file is in use.
View 8 Replies View RelatedIs it possible to restore two databases simultaneously in same sql instance?
View 3 Replies View RelatedIf one is regularly taking backups of system databases, when does it become necessary to rebuild the master database. I am looking for a situation where rebuilding the master is preferred to restoring it from backup.
View 1 Replies View RelatedI am having issues with Restoring the Backup of same Database on to the same server , as i know like many of you will be asking y i need to restore on same server.. Well the need came in that way , now i think i know the problem (i.e) The Orginla DB is there and also i am restoring the same DB again on that server, so .mdf and .ldf will be same .
View 8 Replies View RelatedI'm using SQL Server 2012 R2 and am working on configuring vendor access to a particular DB. I have a test db & (what will eventually be) the production DB. I've configured security for the test DB and want to back that up, then restore it (including all settings) to the prod one, renaming it to the prod DB name.
View 3 Replies View RelatedWe have one database with Filestream enabled. There is one table "dbo.files" which uses Filestream.
We created a filestream filegroup Filegroup1 and added 3 data containers to it. (3 filestream data containers within the same filegroup.)
We have three LUNs F:, G:, H: each with a capacity of 2TB (That is the limitation). F: and G: are almost full. So, I restricted their growth so inserts do not happen into these data containers. Inserts are now going into H: drive which has lots of free space. Our application code prevents any sort of deletes or updates to this table. So data in the growth restricted containers will never change.
Now the database is around 6 TB in size and backups is a challenge. We are contemplating on migrating storage to netAPP and use their snapmanager console which is much faster.
However, until then, we need a solution with native SQL backups. We tried partial backups and piecemeal restore.
WE tried this on a test server :
1) Partial backup only the read-only data containers first, (F: and G:) (The plan is to back these up just once a month as this data never changes).
2) Partial backup the primary filegroup plus the third data container in the Filestream filegroup which is subject to inserts (H:)
While restoring, we tried the online restore, First, I restored the backup obtained from step 2 above with recovery option. Then I restored the backup obtained from step 1 with recovery. I see that the database was brought online. However, when I try to query the dbo.files table, I get an error stating that some files of the filestream filegroup are offline.
What the BUFFERCOUNT option does in a RESTORE DATABASE command?
View 5 Replies View RelatedI need to create a procedure with Restore Database command in many applications in differente platforms. But I can't "use master". How do I do it?
View 1 Replies View RelatedWe have a bunch of SQL 2012 databases which use SQl Server authentication (essentially local dev instances). Is it possible to take a backup of one of these database and then push them onto a (central) server which uses Integrated security (based on active directory authentication) using a script to change and map the authentication model in the process?
View 1 Replies View RelatedI have backed up databases from a 2008 server and now I would need to restore them to a 2012 , the only issue is that I need a script bcuz I have over a hundred databases.
View 9 Replies View RelatedIn last week my database was crashed and some how i managed to restore it back on SQL2K12 but after restoration all the relationships are removed and sql server is showing below message when i open diagram of the database.Table(s) were removed from the diagram because privileges were removed to these table(s) or the table(s) were dropped.how to get back all the relationships of the tables.
View 2 Replies View RelatedI have a process that restores a production DB, overwriting the existing copy each night. I'd like to keep the solution "up" for as long as possible. And this'll be more important if I want to update it in the day (where there are more queries) too. The nature of queries thrown at the system is that there are about 20 per hour, it's underpinning a reporting system, it's not an OLTP system.
It seems to me I could restore the fresh DB copy into a holding DB, then rename it to the production DB name at the end of the process. The rename process should be pretty much instant.
But I need to think about detecting and waiting for queries to complete on the prod DB, before removing/demoting it (actually, I though to rename it, then reusing it as the next copy to update).
What I want to do is :
- restore a backup of a 3rd party database onto one of our servers
- this has no users that I can use
- there is some ETL processing so we're using Control-M to manage the process
- create a database user and grant it db_reader.
I'd like to do this without granting any users elevated privileges if possible.
What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.
The refresh is completing, but Control-M is not able to log onto the database to create the user.
What is the best way to accomplish this task without granting the control-m user sysadmin rights?
Would I be able to do it if I used a SQL Agent job for the restore and user creation?
Scenerio:
Its 3pm and a user comes to me and says, she's deleted an invoice with many associated items. I know the affected tables (foreign keys) and I have last nights backup of the db. However I don't want restore the entire db back from last night just the deleted invoice record/s. What is the best practice procedure for accomplishing this?
i have been doing tests and i cannot manage to do this. here is what i have done and am trying to do:
I created a full backup of the adventureworks database
I made some updates to tables.
I created a differential backup.
I done an update update to another table, dropped a table, and done another update.
Lets say in a production envrionment i just realised the table was dropped and i wanted to go to the transaction just before the table was dropped. is this possible, or are all transactions in the log going to be lost? All i have found remotely related to this are marked transactions, but they are not what i am looking for. i want a list of transactions in the log and be able to select one as the backup point ( via SSMS or TSQL). i am not looking for a point in time restore.
Thanks all
In SQL 2008, there is a way to backup a database, but specify the tables backed up, and, during the restore on the destination server, restore only those tables in the database?
View 2 Replies View RelatedHi!
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
Hi!
Is there any way I can resore a specified set of deleted rows from a table? Most of these rows were created during the course of the day, but at the end of it a user deleted them accidentally. My latest full backup is from last night, but if I restore it, it will overwrite many other tables that already have correct informaiton entered during the day. Is there any to roll back only the transaction with which the rows were deleted?
Mnay thanks for your help!
av
Is there a way to query all the columns in a database that have the data type as Integer.
View 9 Replies View RelatedHi all,
does anyone know of a way in which you can backup and restore objects that belong to a specific schema?
So in this example, I€™d want to only back up and be able to restore the tables that belong to schema 1, leaving Schema2 stuff alone.
--DB
----Schema1.Table1
----Schema1.Table2
----Schema2.Table1
----Schema2.Table2
Thanks in advance,
Andy.
EDIT: I forgot to add that I want to backup the tables and the data contained within.
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 RelatedI 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?
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
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"
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 View RelatedI 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