Appframe Knowledge Base


5 hits
1

How to execute SQL from SQLCMD window

How to run a large script (typically from project-upgrader). Add USE <your db> in the top of the script and then it can be executed via: sqlcmd -S <server\instance> -U <username> -P <password> -i <pathToFile>

sql · Perma link post comment Posted by: Nils Arne Ramsvik (24-apr-2013)

1

How do you truncate all tables in a database using TSQL?

/*What is the best way to remove all the data from all the tables using TSQL? Are there system stored procedures, views, etc. that can be used? I do not want to manually create and maintain truncate table statements for each table - I would prefer it to be dynamic.

http://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql#156813
*/

/*
It would make sense to add filters to i.e. just clean up tables starting with atbl_ and/or those that contains the Domain field. But this can easily be added.
*/

exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'  
exec sp_MSforeachtable 'DELETE FROM ?'  
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'  
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL' 
exec sp_MSforeachtable 'IF NOT EXISTS (SELECT *
    FROM SYS.IDENTITY_COLUMNS
    JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
    WHERE SYS.TABLES.Object_ID = OBJECT_ID(''?'') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL)
    AND OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1
    DBCC CHECKIDENT (''?'', RESEED, 0) WITH NO_INFOMSGS'

sql · Perma link post comment Posted by: Trygve Haaland (08-nov-2012)

1

Use CTE and ROW_NUMBER() to mark the først record in a given sortorder

WITH CTE
AS
(SELECT *,
ROW_NUMBER() OVER(Partition by PersonID ORDER BY StartDato) AS Ranking
FROM atbl_CRM_Medlemskap M
WHERE ISNULL(M.Avsluttet,GETDATE()+1) > '2011-12-30')

UPDATE CTE
SET Tellende=1, CUT=0
WHERE Ranking =1

sql · Perma link post comment Posted by: Sigmund Lunde (01-okt-2012)

1

How to enable service broker on database

--If you have two databases on the same server (i.e. backup of dev as test). The databases will have the same 
--broker identifier. Then run this statement before enabling the broker:
ALTER DATABASE <DATABASE> SET NEW_BROKER WITH ROLLBACK IMMEDIATE

--Enable Service Broker (run separately to ensure db is in single user mode)
ALTER DATABASE <DATABASE> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE <DATABASE> SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE
ALTER DATABASE <DATABASE> SET MULTI_USER WITH ROLLBACK IMMEDIATE

sql · Perma link post comment Posted by: Jan Leon Sandslett (03-sep-2012)

1

Multiple recipients in sstp_Mail_MessageSendImmediateSmtp

-- sstp_Mail_MessageSendImmediateSmtp supports multiple recipients 
-- by using the XML parameters.

DECLARE @ToRecipientsXML NVARCHAR(MAX)

SET @ToRecipientsXML = '<Recipients>' 
  + (SELECT Name, EMail FROM atbl_Blah_Blah FOR XML RAW) 
  + '</Recipients>'
  
EXEC sstp_Mail_MessageSendImmediateSmtp  
  @FromLogin = 'user@somewhere.com',
  @ToXML = @ToRecipientsXML,
  @Subject = 'Example',
  @TextHTML = 'This is a sample text'

sql · Perma link post comment Posted by: Torgeir Fredriksen (17-aug-2012)