SQL Server 2014 :: How To Add Script Into A Table Row
Apr 10, 2015
I have one environment where we get number of changes everyday. It takes time to take backup of database and then apply the script. I am thinking to automate it as
1. SQL Script (Hot-fix) will put in Shared folder accessible by SQL server.
2. I am writing a SP which will have parameters. Database name, SQL script, HF number.
SP will first take copy_only and compressed (all db's are 2008R2) backup of database. It will record SQL HF script from table into one row and it will apply that script to database using executesql. I am not able to take that SQL script in row.
View 2 Replies
ADVERTISEMENT
Jun 10, 2015
Here is my table:
My question is: How can I insert a row for each unique TemplateId. So let's say I have templateIds like, 2,5,6,7... For each unique templateId, how can I insert one more row?
View 0 Replies
View Related
Jun 21, 2015
Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.
View 3 Replies
View Related
Dec 23, 2013
We have two tables with names X and Y.
X has a,b columns. And Y has c,d columns.
I want to update b column in X table with the values from d column in Y table on condition X.a=Y.c.
View 3 Replies
View Related
Jul 29, 2014
table1
id value
1 11
2 12
3 13
4 14
table2
id1 value1
1 21
2 22
1 31
2 32
in need output as follows
id value id1 value1
1 11 1 21
2 12 2 22
3 13 null null
4 14 null null
1 11 1 31
2 12 2 32
3 13 null null
4 14 null null
View 9 Replies
View Related
Jan 12, 2015
I have a scenario where I would need to add +4 IDs with the existing IDs, below is an example:
IDWorkloadUnits
1EXO 3
7SPO 4
15LYO 10
Desired output should be as follows:
IDWorkloadUnits
1EXO 3
2
3
4
5
7SPO 4
8
9
10
11
15LYO 10
16
17
18
19
I am not worried about other attributes in the same table.
View 6 Replies
View Related
Jul 21, 2015
We have a database on a 2005 box, which we need to keep in sync with one on a 2014 box (until we can turn off the one on 2005). The 2005 database is still being updated with changes that must be applied to the 2014 database, given the nature of the data (medical documents) we need to ensure updates are applied to the 2014 database in very near real time (these changes are - for example - statuses, not the documents themselves).
Cunning plan #1, ulgy - not at all a fan of triggers - but use an after update trigger to run a sp on the remote box via a linked server in this format, with a SQL Server login for the linked server with permissions to EXEC the remote proc.
CREATE TRIGGER [dbo].[SourceUpdate] ON [dbo].[SourceTable]
AFTER UPDATE
AS
SET XACT_ABORT ON;
SET NOCOUNT ON;
IF UPDATE(ColumnName)
[Code] ....
However, while the sp can be run against the linked server as a standalone query OK, when running it in a trigger it's throwing
OLE DB provider "SQLNCLI" for linked server "WIBBLE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Procedure TheAfterUpdateTrigger, Line 19
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "WIBBLE" was unable to begin a distributed transaction.
Whether it actually possible to call a proc on a remote box via a trigger and if so what additional hoops need to be jumped through (like I said, it'll run OK called via SSMS)?
View 3 Replies
View Related
Jun 19, 2014
I found loads of things but nothing seems to work...
I'm trying to get a link with XML data inside the page into a table but I can't find anything
View 9 Replies
View Related
Jan 8, 2015
I am trying to bcp a log file into a table. Fields are "tab" separated and rows are "crlf" separated.
The rows of the file are uneven, in the sense, one row might have 35 tabs and the next one might have 10.
The table i am trying to insert to has 40 columns (maximum no.of columns i calculated from the log file).
I tried both these commands
BCP.EXE TEST.dbo.LOG IN E:syslog.TXT -c -e error.txt -T -S
BCP.EXE TEST.dbo.LOG IN E:syslog.TXT -c -t -r
-e error.txt -T -S
(I also tried using a format file from table)
When I run this, the table is loaded with data but not in the intended way.This is what I have from the table
If the 1st line in the text file has 35 columns and the row ends after it, in the table the 1st row has correct info until the 35th column and instead of going to the next row for the next line in file, it continues to use the next 5 columns in table before it goes to the next row. I think its not getting the row delimiter.
How do I force it to next line.
View 4 Replies
View Related
Mar 21, 2015
Select statement joining file1 to file2. File 1 may have 0, 1, or many corresponding rows in file2. I need to count the corresponding rows in table2. Table2 also has a Boolean column and I need to count the number of rows where it is true. So I need to count the total number of matching rows and the count of those that are set to true. This is an example of what I have so far. I had to add each column being selected into a Group by to make it work, but I do not know why. Is there some other way this should be set up.
SELECT c.CarId, c.CarName, c.CarColor, COUNT(t.TrailerId) as trailerCount, (add count of boolian, say t.TrailerFull is true)
FROM Car c
LEFT JOIN Trailer t on t.CarId = c.CarId
GROUP BY c.CarId, c.CarName, c.CarColor
View 3 Replies
View Related
Jun 4, 2015
I'm looking to partition a big table into years.
I have a big table with data going back to 2001.
I need to partition this into file groups each for the last 5 years, and the rest into its own file group
I was going to convert the inherent date time field into the format int(yyyymmdd) and partition on that field.
Is this an acceptable way to achieve this? Is there a better way to partition on date? [not time]
View 4 Replies
View Related
Jul 5, 2015
I have the below table :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/2015NULL 23:00.0 NULL
10xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
11xxxxx@gmail.com 15/06/2015NULL 00:00.0 NULL
12xxxxx@gmail.com NULL 15/06/2015 NULL 00:00.0
13xxxxx@gmail.com 14/06/2015NULL 00:00.0 NULL
14xxxxx@gmail.com NULL 14/06/2015 NULL 00:00.0
Then i need to replace second row with value with first row with null and so on :
IDName Date_StartDate_End Time_StartTime_End
9xxxxx@gmail.com 13/06/201514/06/2015 23:00.0 00:00.0
10xxxxx@gmail.com NULL NULL NULL NULL
11xxxxx@gmail.com 15/06/201515/06/2015 00:00.0 00:00.0
12xxxxx@gmail.com NULL NULL NULL NULL
13xxxxx@gmail.com 14/06/201514/06/2015 00:00.0 00:00.0
14xxxxx@gmail.com NULL NULL NULL NULL
View 3 Replies
View Related
Jul 18, 2015
I was trying to create temporal table feature which gets introduced in SQL Server 2016 but I am getting this error while trying to create it; Cannot enable compression for object 'Department_History'. Only SQL Server Enterprise Edition supports compression
I have installed SQL Server CTP 2.0 on my system.
Microsoft SQL Server 2016 (CTP2.0) - 13.0.200.172 (Intel X86) May 21 2015 11:16:44 Copyright (c) Microsoft Corporation Express Edition on Windows NT 6.2 <X86> (Build 9200: )
View 3 Replies
View Related
Aug 24, 2015
I am new in SQL Server, What to check ,what action do i need to take while adding a new column to a table.
View 5 Replies
View Related
Sep 18, 2015
I have a table with some rows and columns what i want is i want to Show sum of particular column in the last row. This is my code.
SELECT DISTINCT Cluster.ClusterName, Gruppe.GruppeName, Arbeitspaket.ArbeitspaketName, BMWProjekt, AnzahlAP, Abgerechnet, InBearbeitung, Billanz FROM Bestellung
INNER JOIN Cluster ON Bestellung.Cluster = Cluster.rowid
INNER JOIN Arbeitspaket ON Bestellung.Arbeitspaket = Arbeitspaket.rowid
INNER JOIN Gruppe ON Bestellung.Gruppe = Gruppe.rowid
WHERE Projekt ="EA-284-Nxx" AND AnzahlAP <> 0 AND Abgerechnet is 1 AND InBearbeitung is NULL AND Billanz is NULL;
View 4 Replies
View Related
Feb 2, 2015
I have multiple linked servers and trying to find if a table (approximate table name) exists in one of the linked servers.
I have a code to find it in local DB....how can I modify to include all linked servers as well:
ALTER PROCEDURE [dbo].[FindTable]
@TableName VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
[code]....
View 3 Replies
View Related
Jun 18, 2014
I have a view which select some few columns from multiple tables with where clause and have 5 unions of different category of data.
For the best performance i need to change this to physical table or any other options which can increase my performance.
View 2 Replies
View Related
Jul 29, 2014
I need to load the following data into a SQL table. This is how the vendor is able to provide it to us.
CRCorp Daily Report,,,,,,
,,,,,,
Facility,Location,Purchase Order #,Vendor,Inventory #,Date Ordered,Extended Cost
09-Mowtown 495 CRST,09-402A Women's Imaging,327937,"BARD PERIPHERAL VASCULAR, INC.",113989,7/25/2014,650
09-Mowtown 495 CRST,09-402A Women's Imaging,327936,"WB MASON CO., INC.",112664,7/25/2014,8.64
01-Mowtown 499 CRST,01-302B Oncology,327947,McKesson General Medical,n/a,7/25/2014,129.02
[Code] ....
I have attempted to bulk insert it into this table with no luck.
CREATE TABLE POMaster
(Facility VARCHAR(75),
Location VARCHAR(75),
PONum INT,
VendorNm INT,
INVENTORYNUM VARCHAR(25),
orderDte DATE,
ExtendedPrice NUMERIC(10,2)
)
GO
It does not like the double quotes. How to make this format work? Do I need a format file?
View 2 Replies
View Related
Aug 9, 2014
how to take backup of indexes for a particular table.
View 1 Replies
View Related
Aug 9, 2014
How to take backup of indexes for a particular table.
View 5 Replies
View Related
Aug 13, 2014
Recently, I partitioned one of my largest tables into multiple monthly field groups. For the current month, it is attached to my "Active' table. The older records are kept in the "historical" table. I need an efficient way to pull records when have a date range that can be spread across both tables.
View 9 Replies
View Related
Oct 20, 2014
I have a stored procedure in that attempts to perform a WHERE NOT EXISTS check to insert new records. If the table is empty, the procedure will load the table. However, an insert does not occur when a change to one or more source fields occurs against an existing record. The following is my code:
declare
@Created_By nchar(50)
,@Created_Date datetime
,@Updated_By nchar(50)
,@Updated_Date datetime
select @Created_By = system_user
[code]....
I expected that when one of the source values of any field in the second WHERE clause changes, that the procedure would insert a new record. Why is this not happening? One other note: I am not 'allowed' to use MERGE.
View 1 Replies
View Related
May 1, 2015
I have a T-SQL Statement Task to create table every time the package run. It doesn't return error but it doesn't drop the table either. The data is appended every time. The code is working fine in SQL server query window.
IF OBJECT_ID (N'M020_Vendor', N'U') IS NOT NULL
DROP TABLE M020_Vendor
GO
SET ANSI_NULLS ON
GO
[code]....
View 7 Replies
View Related
May 5, 2015
I save Table size and recs. no every day. and check it some days.
...
insert into @t
exec sp_msforeachtable 'exec sp_spaceused ''?'''
...
But Today I saw sudden increase size in a table. about 128 MB in a day. (Average Growth fro this table was 4 or 5 MB in a day)This growth was for Only 4222 Records. While for more number of records (about 7000) in yesterday we had only 2 MB GRowth!
This Table information (Now):
sp_spaceused 'Table1'
Result:
name ---Rows --reserved --data
Table1--1021319--460328 KB --283104 KBI Try to gess The reason. I copy These new records to another table.But The result was more strange : on new table the size of these record was : < 1 MB I copied All records to another table . The size was : 148 MB (while this is 283 MB in my real database)
View 4 Replies
View Related
May 8, 2015
I have a performance issue with one of the views when I join the view with a temp table
I have 2 Views - View1 and View2.
There is a third view - view_UNION where the
view_UNION =
SELECT * FROM View1
UNION ALL
SELECT * FROM View2
If I have a query like -
Select view_UNION.* FROM
view_UNION INNER JOIN #TMP ON #TMP.ID = view_UNION.ID
the execution is too slow.
But if I execute the views separately, I get good performance.
How to improve the performance of the view_Union
View 7 Replies
View Related
May 28, 2015
I have the following table
Table A
Col1
0118F
0118R
5678
0118F
0118R
5678
5678
5678
0118F
0118R
I want to insert only distinct values of Col1 from table A to another table
Insert into TableB
(
UserName, ProjectName, processdate, Recordprocess, Comments, RecordProcessExt
)
values(@UserName, 'Test Project', getDate(), distinct Col1 from Table1, 'Test Comments', distinct col1+ 'TR' from TableA)
How can I accomplish the above. I need to insert distinct column from TableA to RecordProcess and col1+'Tr' to recordprocessExt.I can do it with cursor. I don't know any other way.
Also, there are other columns in Table A. I am using sql server 2005.
View 1 Replies
View Related
Jun 8, 2015
I'm having problems with the following code:
--DROP MASTER KEY
--GO
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass@word1';
GO
USE master;
[code]....
What am I missing? What do I have to do if I get in a situation where I need to back out and start over?
[URL]
View 9 Replies
View Related
Jun 18, 2015
I have two table in two different database. I have to found count for both table and put into other table.
example : Database --> A , table is T1
Database --> B , table is T2
I want count of both table and put into T3 table in database A or B .
View 7 Replies
View Related
Jun 30, 2015
I'm getting a replication error that an object (Table) was not found. Any script that can capture this information?
View 3 Replies
View Related
Sep 18, 2015
I have two tables for insertion in one transaction scope. Table one have 10 rows. After first table insert statement (not yet committed) if I run select on first table from other session, it holds table until my insert is committed or rolled back and from (SSMS), it display 10 rows and then wait for transaction scope till finished. My question is do I need to use no lock hint in this situation. Or there is something wrong with isolation level. One saying that in this situation table should not hols select while insert is in transaction scope.
View 5 Replies
View Related
Oct 14, 2015
I'm being asked to create multiple filegroups for a new database based on the table type, transaction, lookup, misc... From what i'm reading this doesn't make sense. I'm reading either large tables get file groups, nonclustered indexes when they are about the same size of the data, or a few other reasons...
First of all, we are talking about the same disk (please don't ask me about how it is configured) and I'm not sure yet if restoring separate file groups is even going to be necessary.
So here are my questions (beyond, the test and see what happens) because in the end I'm going to probably have to do what i'm told. So this is for my professional knowledge.
1. Does file groups separated by table type make sense?
2. Should you put tables that are queried often together in the same or different file groups.
3. I'm pretty sure you can't restore single file group for write access, am I correct?
View 0 Replies
View Related
Jul 8, 2014
I have created a table name "tblPopulation" with fields 'Country', 'State' etc....
When i query it with table name as tblpopulation with fields 'country', 'state' then i get error as invalid object name.
View 9 Replies
View Related
Jul 29, 2014
I am doing a performance testing for In-memory option is sql server 2014. As a part I want to insert 500 million rows of records into a in-memory enabled test table I have created.
I need a sample script to insert 500 million records into a table ....
View 9 Replies
View Related
