Check in [`sys.servers`][1]:

    if not exists(select * from sys.servers where name = N&#39;myserver\devdb_1&#39;)

  


  [1]: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-servers-transact-sql?view=sql-server-2017


    IF NOT EXISTS ( SELECT TOP (1) * FROM sysservers WHERE srvname = &#39;myserver\devdb_1&#39; )

I&#39;m at the point where I need to write unit tests for a REST API written using CakePHP 1.3. The API supports GET, POST and PUT requests for querying and manipulating data.

Is there any established way to test the correct input/output of an API simulating an HTTP request, using **fixtures**? I do not want to run actual POST/PUT requests against the live (dev) database. How can I best mock out the system to use temporary models, yet test the rest of the stack as-is?

----------

Testing GET requests is easy enough with [controller tests][1]. However, for data manipulation the API uses HTTP headers quite extensively and also parses raw XML and JSON POST/PUT data. The controller unit test methods only mock POST data by setting `$this-&gt;data` in the controller, which does not allow me to properly test the API.

  [1]: http://book.cakephp.org/view/1210/Testing-controllers

How to unit test your API?

We use Visual Studio, which generates lots of `bin` and `obj` directories. When I use **P4V**&#39;s &quot;Reconcile Offline Work&quot; feature, these appear in the &quot;Local files not in depot&quot; list.

I&#39;d like to exclude them. I found [this question][1], but that talks about files, and when I try what it suggests (adding the following to my workspace view), **it doesn&#39;t work**:

    //depot/Foo/... //Client/Foo/...
    -//depot/Foo/.../*.user //Client/Foo/.../*.user
    -//depot/Foo/.../bin/... //Client/Foo/.../bin/...
    -//depot/Foo/.../obj/... //Client/Foo/.../obj/...

It doesn&#39;t actually seem to work for files, either: the foo.csproj.user files are still displayed in the list.

Can I exclude directories from P4V? If so, how? What am I doing wrong?


  [1]: https://stackoverflow.com/questions/55449/can-you-ignore-a-file-in-perforce

Can you &quot;ignore&quot; a directory in P4V?

I want to be able to programmatically (in T-SQL) check if a specific linked server already exists for my current server and database (so that if the link doesn&#39;t exist yet, I can create it). I tried stuff like this:

    IF OBJECT_ID(&#39;myserver\devdb_1&#39;) IS NULL
    BEGIN
      PRINT &#39;Does not exist, need to create link&#39;
      EXEC master.dbo.sp_addlinkedserver @server = N&#39;myserver\devdb_1&#39;, 
                                         @srvproduct=N&#39;SQL Server&#39;
    END
    ELSE
      PRINT &#39;Link already exists&#39;

But the OBJECT_ID test always returns null, even if the link already exists.  Any way to do this check in T-SQL, so that the rest of my code can assume the link always exists?

Is there an &quot;IF EXISTS&quot; test for a linked server?

I want to be able to programmatically (in T-SQL) check if a specific linked server already exists for my current server and database (so that if the link doesn't exist yet, I can create it). I tried stuff like this:
<pre><code class="hljs language-sql">IF OBJECT_ID('myserver\devdb_1') IS NULL
BEGIN
 PRINT 'Does not exist, need to create link'
 EXEC master.dbo.sp_addlinkedserver @server = N'myserver\devdb_1', 
 @srvproduct=N'SQL Server'
END
ELSE
 PRINT 'Link already exists'
</code></pre>
But the OBJECT_ID test always returns null, even if the link already exists. Any way to do this check in T-SQL, so that the rest of my code can assume the link always exists?

I have a deleted file archive database that stores the ID of the file that was deleted,  I want the admin to be able to restore the file (as well as the same ID for linking files).  I do not want to take identity_insert off the entire table, as the increment by one works great.  In my insert to `TBL_Content` store procedure I have something like this
   

    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    SET IDENTITY_INSERT tbl_content ON
    GO
    
    ALTER procedure [dbo].[spInsertDeletedIntoTBLContent]
    @ContentID int, 
    ...insert command...
    SET IDENTITY_INSERT tbl_content OFF

But I keep getting the same error:

&gt; Cannot insert explicit value for identity column in table
&gt; &#39;TBL_Content&#39; when IDENTITY_INSERT is set to OFF.

Any help?



IDENTITY_INSERT is set to OFF - How to turn it ON?

I can see how to turn off row level and page level locking in SQL Server, but I cannot find a way to force SQL Server to use row level locking. Is there a way to force SQL Server to use row level locking and NOT use page level locking?

Is it possible to force row level locking in SQL Server?

Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?

If not, what is the alternative in MS SQL Server?

Is there a Boolean data type in Microsoft SQL Server like there is in MySQL?

The following query does not update the datetime field:

    update table
    SET EndDate = &#39;2009-05-25&#39;
    WHERE Id = 1

I also tried it with no dashes, but that does not work either.

How do you update a DateTime field in T-SQL?

Is there a SQL Server command to connect a **user** of a single database to a **login** for the database server of the same name?

For example:

&gt;Database Server - Default Instance  
Database: TestDB

&gt;Server Login - TestUser  
Existing user on TestDB - TestUser

If I try to make the `TestUser` login a user of the `TestDB` database, the &quot;User, group, or role&quot; already exists.

Does anybody know of an easy way to assign the DB **user** to the server **login**?

How to connect an existing SQL Server login to an existing SQL Server database user of same name

I wrote a T-SQL Statement similar like this (the original one looks different but I want to give an easy example here):

    SELECT first_name + 
        CASE last_name WHEN null THEN &#39;Max&#39; ELSE &#39;Peter&#39; END AS Name
    FROM dbo.person

This Statement does not have any syntax errors but the case-clause always chooses the ELSE-part - also if the last_name is null. But Why?

What I want to do is to unite first_name and last_name, but if last_name is null the whole name becomes null:

    SELECT first_name +
       CASE last_name WHEN null THEN &#39;&#39; ELSE &#39; &#39; + last_name END AS Name 
    FROM dbo.person

Do you know where the problem is?

T-SQL CASE Clause: How to specify WHEN NULL

Could someone give me a quick overview of the pros and cons of using the following two statements:

    TRUNCATE TABLE dbo.MyTable

vs

    DELETE FROM dbo.MyTable

It seems like they both do the same thing when all is said and done; but are there must be differences between the two.



Pros &amp; Cons of TRUNCATE vs DELETE FROM

    select *
    from table
    where date &gt; &#39;2010-07-20 03:21:52&#39;

which I would expect to not give me any results... EXCEPT I&#39;m getting a record with a datetime of `2010-07-20 03:21:52.577`

how can I make the query ignore milliseconds?

SQL Server remove milliseconds from datetime

While in Management Studio, I am trying to run a query/do a join between two linked servers.
Is this a correct syntax using linked db servers:

    select foo.id 
    from databaseserver1.db1.table1 foo, 
         databaseserver2.db1.table1 bar 
    where foo.name=bar.name

Basically, do you just preface the db server name to the db.table ?

SQL Server Linked Server Example Query

I have two SQL Servers (running SQL Server 2008) named `DATA01` and `DATA02`. `DATA02` has a linked server definition `LINK`, that points at `DATA01`, with suitable user mapping set up. On `DATA01` there is a database `MyDatabase` containing these two tables:

    CREATE TABLE T_A (
        Id int
    )
    
    CREATE TABLE T_B (
        Id int,
        Stuff xml
    )

When I run this command from `DATA02`, I get data returned as expected:

    SELECT Id FROM LINK.MyDatabase.dbo.T_A;

However, when I run this command from `DATA02`, I get an error:

    SELECT Id, Stuff FROM LINK.MyDatabase.dbo.T_B;

The error is

&gt; Xml data type is not supported in distributed queries. Remote object &#39;DATA02.MyDatabase.dbo.T_B&#39; has xml column(s).

And strangely, this command:

    SELECT Id FROM LINK.MyDatabase.dbo.T_B;

also gives the same error, **even though I&#39;m not `SELECT`ing the xml column!** What&#39;s going on?


Why do I get the error &quot;Xml data type is not supported in distributed queries&quot; when querying a linked server for non-xml data?

I get this weird error on SQL Server. And I cannot find solution in older posts. 

I have this procedure:

    create proc _upJM_SyncAll_test
    as
    begin
	    DECLARE @SQLString nvarchar(max)

	set @SQLString = N&#39;
	DELETE FROM OPENQUERY([LOCAL_MYSQL],&#39;&#39;SELECT acSubject FROM _utjm_setitemprices&#39;&#39;) where acSubject not in (select acSubject from _uvJM_SetSubj)
	DELETE FROM OPENQUERY([LOCAL_MYSQL],&#39;&#39;SELECT acSubject FROM _utjm_setsubj&#39;&#39;) where acSubject not in (select acSubject from _uvJM_SetSubj)

	update a
	set acName2 = b.acName2,
		acName3 = b.acName3,
		acAddress = b.acAddress,
		acPost = b.acPost,
		acPostName = b.acPostName, 
		acCountry = b.acCountry, 
		acVATCodePrefix = b.acVATCodePrefix,
		acCode = b.acCode, 
		anDaysForPayment = b.anDaysForPayment
	from OPENQUERY([LOCAL_MYSQL],&#39;&#39;SELECT * FROM _utjm_setsubj&#39;&#39;) a join _uvJM_SetSubj b on (a.acSubject = b.acSubject)
	where 1=1
	and (	isnull(a.acName2,&#39;&#39;&#39;&#39;) &lt;&gt; isnull(b.acName2,&#39;&#39;&#39;&#39;) OR 
			isnull(a.acName3,&#39;&#39;&#39;&#39;) &lt;&gt; isnull(b.acName3,&#39;&#39;&#39;&#39;) OR 
			isnull(a.acAddress,&#39;&#39;&#39;&#39;) &lt;&gt; isnull(b.acAddress,&#39;&#39;&#39;&#39;) OR 
			isnull(a.acPost,&#39;&#39;&#39;&#39;) &lt;&gt; isnull(b.acPost,&#39;&#39;&#39;&#39;) OR 
			isnull(a.acPostName,&#39;&#39;&#39;&#39;) &lt;&gt; isnull(b.acPostName,&#39;&#39;&#39;&#39;) OR 
			isnull(a.acCountry,&#39;&#39;&#39;&#39;) &lt;&gt; isnull(b.acCountry,&#39;&#39;&#39;&#39;) OR 
			isnull(a.acVATCodePrefix,&#39;&#39;&#39;&#39;) &lt;&gt; isnull(b.acVATCodePrefix,&#39;&#39;&#39;&#39;) OR 
			isnull(a.acCode,&#39;&#39;&#39;&#39;) &lt;&gt; isnull(b.acCode,&#39;&#39;&#39;&#39;) OR 
			isnull(a.anDaysForPayment,&#39;&#39;&#39;&#39;) &lt;&gt; isnull(b.anDaysForPayment,&#39;&#39;&#39;&#39;)
	)

	insert into OPENQUERY([LOCAL_MYSQL],&#39;&#39;SELECT * FROM _utjm_setsubj&#39;&#39;) (acSubject, acName2, acName3, acAddress, acPost, acPostName, acCountry, acVATCodePrefix, acCode, anDaysForPayment)
	select b.acSubject, b.acName2, b.acName3, b.acAddress, b.acPost, b.acPostName, b.acCountry, b.acVATCodePrefix, b.acCode, b.anDaysForPayment
	from OPENQUERY([LOCAL_MYSQL],&#39;&#39;SELECT * FROM _utjm_setsubj&#39;&#39;) a right join _uvJM_SetSubj b on (a.acSubject = b.acSubject)
	where a.acSubject is null &#39;

	EXECUTE sp_executesql @SQLString;
    end


When I run procedure in management studio like this:

      exec dbo._upJM_SyncAll_test

everything is OK. I get no error, sync is working just fine. 

But when I put execute in trigger like this:

    create trigger _utrJM_SetSubj on tHE_SetSubj after insert, update, delete
    as
    begin
	    exec dbo._upJM_SyncAll_test
    end

I get this error:

&gt; Msg 8501, Level 16, State 3, Procedure _upJM_SyncAll_test, Line 54  
&gt; MSDTC on server &#39;server&#39; is unavailable.

Procedure _upJM_SyncAll_test has only 39 lines... 


Content Type	Original Author	Original Content on Stackoverflow
Question	Ogre Psalm33	View Question on Stackoverflow
Solution 1 - Sql Server	Remus Rusanu	View Answer on Stackoverflow
Solution 2 - Sql Server	Jorge Ferreira	View Answer on Stackoverflow

Is there an "IF EXISTS" test for a linked server?

Sql Server Problem Overview

Sql Server Solutions

Solution 1 - Sql Server

Solution 2 - Sql Server

Can you "ignore" a directory in P4V?

How to unit test your API?

Attributions