Is there away to get the SQL FK constrain to trust old data when the FK is created with NoCheck.?
Some things to ponder
- Assume the table where the FK is created is too large and cant afford a significant down time, but still need the query optimizer to trust the data in the FK for optimal query plans.
- As pre-deploy script will validate the older data in the FK columns for consistency
I need to insert data into a table..please advise SQL Syntax on how to disable and enable constraints and triggers on the table before and after the inserts!
Thanks..
SQL CE does not seem to support WITH NOCHECK constraint when altering the table to add a foreign key. How can I work around this?
View 3 Replies View RelatedIs a NOCHECK constraint supported in CE 3.5? The keyword is listed in the 3.5 BOL but when attempting to ALTER TABLE using WITH NOCHECK the statement fails.
If it is not supported then is there a work around for it?
TIA
Hi.I'm getting errors like this when I try to run an upgrade script I'm trying towrite/test:altering labels to length 60Server: Msg 5074, Level 16, State 4, Line 5The object 'ALTPART_ANNOT_ANNOTID_FK' is dependent on column 'label'.I used this to bracket my script:sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"gosp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"go/* updates here */sp_msforeachtable @command1="print '?'",@command2="ALTER TABLE ? CHECK CONSTRAINT all"gosp_msforeachtable @command1="print '?'",@command2="ALTER TABLE ? ENABLE TRIGGER all"goI guess the alter table nocheck constraint isn't disabling the fk'scompletely?Is there a way around this, or do I manually have to do the constraintdropping/recreating?ThanksJeff Kish
View 3 Replies View RelatedI wanted to turn off 'Enforce Foreign Key Constrain' using SQL. However, it did not work on the SQL Server 2005 database.
For example, running following SQL in Management Studio, and the result showed that 'Enforce Foreign Key Constrain' property for the newly created constraint was still enabled.
ALTER TABLE [dbo].[Tags] WITH NOCHECK ADD CONSTRAINT [FK_Tags_ChannelID] FOREIGN KEY([ChannelID]) REFERENCES [dbo].[Channels] ([ID])
Does anybody know any way to get around with this problem. It is quite important for my software.
Ps. with the SP1, problem remains the same.
I have a
[Project].[Project Status]
there are 4 statuses
Open
Cancelled
Closed Complete
Closed Failed
I want to create a member that groups the closeds..Can you use case statements? case when 'open' then 'open' when 'Cancelled' then 'Cancelled' else 'Closed' end.
We have 2 SQL 2012 servers. Our application has 2 databases. We are creating an AlwaysOn cluster. Is it good to create 2 AlwaysOn clusters to have 1 database primary on one of the servers and the other database primary on the other server?
I have been asked if it is possible to have one database running on one server and the other database on the other server. Is this possible without creating 2 separate AlwaysOn clusters?
I am working with the SP tuning. I want to know clearly about the temp tables.Instead of Select * into #table, it is always better to create temp table structure and insert the data later.Is it true in the case, if the data is small? insert into #table (select colmn1, column 2 from TableA join Table B on JoinC on joinD on..If they use several joins, in that situation, which way of creating temp table would be better?
View 9 Replies View RelatedThe view I am trying to create (crvKCLPRInsJob) is using another view (brvPRInsJob). I can provide a sample data, that's easy. I couldn't figure out how to get all the other info required.
I created the view below.
I created the following custom fields.
IncCode723Hours
IncCode748Hours
GrossEarn723
GrossEarn748
[Code]....
I am looking to create an upload process that will check for an Excel in a specific directly, if exists, upload the file and populate the database. I have never done this before (only data extracts). How to do the upload process, is this something that can be done with SQL Server 2012?
View 2 Replies View RelatedSQL Server 2012 Ent. Two node cluster with SP2.
Getting the attached error while creating login.
Login gets created even though the error pops up.
I have a ssis package in the DW environment. How can I run this package in the production environment by creating the job and run remotely.
View 7 Replies View RelatedI have an area of a report that id like to use on other reports, its just a few images with some data on top.
how id save it as a control so I could use it as an add on?
I have 2 tables that I need to merge let me explain. I have a range of product ID's that have a product grouping of * meaning all product groups. So I have a table with products and one with around 100 groups
ProdID ProdGrp
-------- ---------
11 *
12 *
ProdGrp ProdGrpDesc
--------- ---------------
A Prod Group A
B Prod Group B
C Prod Group C
I need a table which looks like the below but I have no joining mechanism
ProdID ProdGrp
-------- ---------
11 A
11 B
11 C
12 A
12 B
12 C
I am try to put together options in regard to creating a test environment for our Dynamics NAV system. The environment will be mainly used to test new releases / changes ahead of applying them to production.The 2 options I am considering are…
1.Create a second Test instance on our Production SQL Server to host a test database
2.Purchase a set of SQL developer licences and having a totally separate server for our test environment.
My preference would be option 2. However I need to build a convincing case that this is the best way forward. I wondered if I could tap into the thoughts of the SQL Central community and see how other approach this.
I have a fact table with few flag columns.
What is the best way to bring them to dimension?
Do I need to create dimension(dummy) from fact table for each flag or all flags in single dimension?
We have a sorted data that looks like the first 3 columns below, and fourth colum is what I want to create)
Shift_start meal_break shift_endShift ID
1 0 0 1
0 0 0 1
0 0 1 1
1 0 0 2
0 0 0 2
0 0 1 2
I need to find a method to assign unique Shift IDS to rows that correspond to a single shift. For instance, the first shift would begin on the first row when shift_start flag is turned on, and end on the third row when shift_end flag is turned on.
Can I do this in SQL ?...some kind of grouping ?
What I am trying to recreate is:
<value version="5" type="database">
<name>master</name>
<server>servername</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
[Code] ....
with this query:
SELECT
'version="5" type="database"' AS 'value',
'master' AS 'name',
LTRIM(RTRIM(([Server Name]))) AS 'server',
'True' AS 'integratedSecurity',
[Code] ....
BUt my output is not correct, it is creating this:
<value>
<value>version="5" type="database"</value>
<name>master</name>
<server>ServerName</server>
<integratedSecurity>True</integratedSecurity>
[Code] .....
So my question is how to I get <value>version="5" type="database"</value> as the first 'value' node?
I've tried multiple ways, but no success.
I need to ensure some naming standards to users while creating tables.
All table name should be in same pattern
(ie., ) TeamName_EmpId_tablename
I need a way to restrict user to follow the naming standard. If any user tries to create table without the above naming standard system should not allow.
Discuss the following sql query with respect to performance in an applicaiton involving more number of concurrent users creating and deleting records. The objective is to create continuous primary key integer values.
Table name: SitePage
Column DataType
--------- -----------
PageID BigINT
PageName nchar(10)
Query to insert new record
DECLARE @intFlag INT
SET @intFlag = 0
WHILE (@intFlag =0)
BEGIN
BEGIN TRY
[Code] ....
We don't want to use auto increment integer value for primary key because of the following reason
[URL] .....
We also don't want to use SEQUENCE as we have to create 50 sequence for 50 tables
We can't do trace flag 272
I am having trouble figuring out why the following code throws an error:
declare
@cols nvarchar(50),
@stmt nvarchar(max)
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
select @stmt = '
select *
[Code] ...
The issue that I am having is:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '[' to data type int.
I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not).
We are running SQL Server 2012 on Windows 2008 Server. I created a credential with a proxy account. In creating the credential, it asked for an Indentity and Secret. I used my windows login and password. Now, I have tested the credential and proxy account by executing a Job which calls a SSIS Package. What is the 'best practice' to use when creating a credential? Should the credential be created with another windows login, created with the same abilities as my windows login, with a non-expiring password? Should that new windows login be used as the owner of my job with the Agent?
View 8 Replies View RelatedWe are running SQL Server 2012 on Windows 2008 Server. In one database, we would like to create a view which access objects in another database without giving the user permissions to the underlying base tables in the other database. The ownership chain is broken in this case. Can this be accomplished (considering the ownership chain is broken)? If so, what is the easiest method to accomplish this task? Or
Example 1 (Works):
In DB1:
--UserA selects from Schema1.View1 (which access tables in DB2).
In DB2:
--UserA exists with select permissions on the base tables accessed by Schema1.View1 (in DB1).
Example 2 (trying to accomplish):
In DB1:
--UserA selects from Schema1.View1 (which access tables in DB2).
In DB2:
--UserA exists (or may not exists) with NO permissions on the base tables accessed by Schema1.View1 (in DB1).
I have a cube where i would like to define some actions. I have done report Actions but they are limiting in terms of what i am trying to achieve.I want a popup window from the action from relevant cells at the moment i can use the urlaction type to open the link in a new window which is ok but i want this as a popup.
View 0 Replies View RelatedI am looking for a way to create a temp column who's value would take the value of another column and prepend a value like this to it "domain". This is the Select statement I currently have:
SELECT Nalphakey,[Last Name],[First Name],[User Name],[E-mail Address],[User Name]
FROM SkywardUserProfiles
I understand how to create an Alias for an existing column, but not sure how to do what I am wanting. I also understand that the following will do the concatenation that I need, but I have only used it in an UPDATE query, and I'm not sure how to use it within a Select statement or if that's even possible:
domainName=CONCAT('domain',User Name);
I have 2 tables (#raw1 & #raw2). Each has a year (yr) and month (mnth) and a count (cnt1 / cnt2) field. I need a table created as follows:
2013 2014 2015
cnt1 cnt2 cnt1 cnt2 cnt1 cnt2
jan 5 77 77 8 88
etc....
Normally, I would ask about a pivot but since it is months down one side and year and columns on the top, I don't know if a pivot will work.
create table #raw1 ([yr] int, [mnth] int, [cnt1] int)
insert into #raw1 values
(2013, 1, 5)
, (2013, 2, 5)
, (2013, 3, 5)
, (2013, 4, 5)
, (2013, 5, 5)
, (2013, 6, 5)
[code]....
Recently I've been stumbling with a way to properly produce a result that provides me a count of sales orders that fall in a range of sales order totals.
I want to take the Sales Order Total and then count how many times it falls into a specific bucket.
I've included the script where I've left with sample data so you can run in into a tempdb.
I hope to have the end result look like the below summary if you use my sample data.
Count Less Than 5K: 5
Count Greater Than 5K Less Than 10K: 0
Count Greater Than 10K Less Than 25K: 3
Count Greater Than 25K: 2
Here is the script to set up the tempdb
use tempdb
go
/*
drop table #salesinvoice;
drop table #salesinvoicedetail;
[Code] ....
Below is my query, I change it to perform a count on the summarized totals.
select
si.OrderID as OrderNum
,sum(case when sid.LineAmount < 5000 then 1 else 0 end) as LT5K
,sum(case when sid.LineAmount >=5000 and sid.LineAmount < 10000 then 1 else 0 end) as GT5kLT10k
,sum(case when sid.LineAmount >=10000 and sid.LineAmount < 25000 then 1 else 0 end) as GT10kLT25k
[Code] ....
Here are the order totals.
SI201 = 41450
SI202 = 11600
SI203 = 1210
SI204 = 16001
SI205 = 2750
SI206 = 12500
SI207 = 25450
SI208 = 4500
SI209 = 3850
SI210 = 2575
And the desired result would tell me the below summary. The final query can be column based. I just write this in rows for clarity.
Count Less Than 5K: 5
Count Greater Than 5K Less Than 10K: 0
Count Greater Than 10K Less Than 25K: 3
Count Greater Than 25K: 2
******/
I used following query to identify missing indexes:
SELECT mid.statement , mid.included_columns, mid.equality_columns, mid.inequality_columns,
migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure,
'CREATE INDEX [NCIX_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
+ '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
+ ' ON ' + mid.statement
[Code] ....
I think I need to only create few if an index is covering all columns then I do not need to create more indexes for separate columns or should I create separate index as suggested?
Similarly:
CREATE INDEX [NCIX_20187_20186_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([SerialNo],[StationaryStatus]) GO
CREATE INDEX [NCIX_20189_20188_TL_SRV_Stationary_Stock_Transact] ON [TL_SRV_Stationary_Stock_Transaction] ([StationaryStatus]) INCLUDE ([SerialNo]) GO
[Code] ....
Should I create all indexes above or use minimum number of indexes which covers all columns as mentioned in above create index statements?
I am attempting to create a Test db from a full backup of the production db. With 2012, I cannot do it the the way i had done it in previous versions (and now i understand why because of Logical names).
The Test db runs in the same instance as Prod db.
I attempted to run this but come up with errors. This is what i executed:
RESTORE DATABASE TEST FROM DISK = 'E:<path>FULL.BAK'
WITH REPLACE, RECOVERY,
MOVE 'PROD' TO 'E:<path>TEST.MDF';
The errors are all cannot execute due to PROD is in use.
I have the following query that will serve as a basis for SSRS report
SELECT TOP (1000) d.Project_Name, d.Status, d.Country, d.Region, p.Period, p.Quarter, p.Year, d.Brand, d.Store_Opens_Actual, d.DA, d.DPN, d.StoreNumber,
CONVERT(VARCHAR(10), CASE WHEN ISDATE(d .Store_Opens_Actual) = 1 THEN d .Store_Opens_Actual WHEN ISDATE(d .Store_Opens_Forecast) = 1 AND
ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Forecast WHEN ISDATE(d
[Code] ....
This returns a dataset, that I need to convert into a PIVOT table that should look like the attached spreadsheet.
Having trouble writing the PIVOT table query. I feel like I am missing something conceptually as I am not doing any summing or aggregation. I don't know if dynamics SQL is the solution here or which route to take. I don't know if there is such things as PIVOTING without aggregation. CROSS TAB came to my mind as well.
How to estimate the size of datafile while creating the database?
View 2 Replies View RelatedI want to create and drop the global temporary table in same statement.
I am using below command but I am getting below error
Msg 2714, Level 16, State 6, Line 11
There is already an object named '##Staging_Temp' in the database.
if object_id('Datastaging..##Staging_Temp') is not null
begin
drop table ##Staging_Temp
end
CREATE TABLE ##Staging_Temp(
[COL001] [varchar](4000) NULL,
[Code] ....