אינדקס אתרים - HH5
 
linked server- odd - hh5
CREATE TABLE #tempww (
    LoginName nvarchar(max),
    DBname nvarchar(max),
    Username nvarchar(max), 
    AliasName nvarchar(max)
)
 
 
--  get list all logoins ( user__ users__ ) for databases
INSERT INTO #tempww 
EXEC master..sp_msloginmappings 
 
-- display results
SELECT * 
FROM   #tempww 
where dbname like '%db740%'
ORDER BY dbname, username
 
-- cleanup
DROP TABLE #tempww

 

 

 

-- לבחון ולהחליט מה עושים עם זה ולאיפה מקטלגים את זה
-- How to see query history in SQL Server Management Studio
-- http://dba.stackexchange.com/questions/4043/can-i-see-historical-queries-run-on-a-sql-server-database
 
SELECT t.[text], *
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
--WHERE t.[text] LIKE N'%something unique about your query%';

 

 

 

-- sql server 

 

-- another one example --

EXEC master.dbo.sp_addlinkedserver

@server = N'LAPTOP3TSHB\DEV',

@srvproduct=N'SQL Server'

 

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'LAPTOP3TSHB\DEV',@useself=N'False',

@locallogin=NULL,

@rmtuser=N'av_nis',

@rmtpassword='abcd1234'

 

 

------------------------------------

----quick check linked server:

select * From openquery([LAPTOP3TSHB\DEV],'select getdate()')

------------------------------------

working also by SA user. 

 

 

 
 
-- another one example --
EXEC master.dbo.sp_addlinkedserver
@server = N'SQL05',
@srvproduct=N'SQL Server'
 
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'SQL05',@useself=N'False',
@locallogin=NULL,

@rmtuser=N'sa_admin',

@rmtpassword='nheruxupy2015'
              
 
 
------------------------------------
----quick check linked server:
select * From openquery([SQL05],'select getdate()')
------------------------------------
--working also by SA user. 
 
 

 

 

 

 

 

 

 

 

 

 

‏20/02/2012 12:14:10

Openquery__  \\\ open query  \\\ over server \\\ linked_server

מה שרואים פה שהוא עובד ישירות למול השם שרת וזאת מבלי כנראה לעבור בכלל דרך ה LINKED SERVER

 

create proc [dbo].[GlobeCon_CS_CUSTOMERS] as

truncate table GlobeCon..CS_CUSTOMERS_temp

insert into GlobeCon..CS_CUSTOMERS_temp select * from

openquery([IL-SQL2005-SQL2\SQL2],'select * from GlobeCon..CS_CUSTOMERS')

if (select count(1) from GlobeCon..CS_CUSTOMERS_temp) > 150

Begin

 begin tran

 delete from GlobeCon..CS_CUSTOMERS

 insert into GlobeCon..CS_CUSTOMERS select * from GlobeCon..CS_CUSTOMERS_temp

 commit

print 'Done'

end

else

 RAISERROR(14585, -1, -1) 

 

 

 

--***************************

דוגמא ל משיכה של 2 שדות או יותר מהשרת המרוחק --

כלומר למעשה מריץ  את הסלקט רגיל – לאחר ההגדרות הראשוניות --

select * from

openquery([IL-COMSERV-DB],'select @@servername as server_name, @@servername as zabi')

 

 

 

 

 

 

‏15/02/2012 10:03:08

Sp__  procedure__    get table   OPENROWSET__   \\\ select from sp \\\ select from procedure

 

זה עובד! למרות שבמקור זה נולד לתשאל SP ממקור אחר קרי מ LINKED SERVER   . אבל פה נותן לו את השרת שמולו עובד

בו כרגע קרי: IL-SQL2005-TEST

 

SELECT T.*

FROM OPENROWSET('SQLNCLI',

'Server=IL-SQL2005-TEST;Trusted_Connection=yes;',

'EXEC MSDB.dbo.sp_help_job') AS T

 

 

 

‏02/06/2013 16:35:46

 

Rename__  rename linked_server

 

 

There is no way to rename a linked server. Although there is a way point an
existing linked server to a different server, without renaming. 

 

 

 

 

 

 

 

 

 

 

‏29/02/2012 12:13:01  שלילי !   נכון ל כרגע  רק עם לינקט סרבר = זה עובד . זו כנראה שיטה להעברת משתנים  variables   ל לינקט סרבר ולא יותר .

אבל הוא אכן משתמש בלינק הקים .

 

 

 

 

 

 

 

 

 

‏23/02/2012 17:26:24

Linked server   \\\  Linked servers   \\\ linked__ 

 

הצג את רשימת כל הלינקט סרברז

tsql get list__ of linked servers  - הבא רשימה

 

http://jwcooney.com/2011/10/26/sql-server-how-to-list-the-full-properties-of-all-linked-servers/

 

SELECT SRV_NAME = srv.name,

        SRV_PROVIDERNAME    = srv.provider,

        SRV_PRODUCT         = srv.product,

        SRV_DATASOURCE      = srv.data_source,

        SRV_PROVIDERSTRING  = srv.provider_string,

        SRV_LOCATION        = srv.location,

        SRV_CAT             = srv.CATALOG

      FROM master.sys.servers srv

      --WHERE is_linked = 1

 

 

 

‏20/05/2012 08:43:59

תשאול מלינקט סרבר מוכן   select

 

SELECT * from [DEV11I]..APPS.BENBV_YR_PERD_V

בדיקה__ מהירה של הלינקט סרבר:

select * From openquery([il-comserv-db],'select getdate()')

select * From openquery([system01sql2008\sql2008],'select getdate()') 

select * From openquery([sql05],'select getdate()') 

aavr comment: you must be logged in by the user of linked server :: SA

 

--also in 2000

select * From openquery([il-comserv-db],'select top 10 * from Extranet_INT.dbo.Users')

select * From openquery([il-tlv-sql1],'select getdate()')

select * From openquery([il-tlv-sql1],'select * from dbo.sysdatabases')

select * From [IL-TLV-BMOLOKA1\IL_BMOLOKA_2000].[a0_admin].[dbo].[check_full_backup_history_av_v]

select * From openquery([DAILY],'select * from dual')

 

 

 

 

‏23/02/2012 17:32:28  תשאול כללי   

 

 

create table #tmp (

SRV_NAME varchar(128) not null,

SRV_PROVIDERNAME varchar(128) not null,

SRV_PRODUCT varchar(255) null,

SRV_DATASOURCE varchar(255) null,

SRV_PROVIDERSTRING varchar(255) null,

SRV_LOCATION varchar(255) null,

SRV_CAT varchar(255) null)

go

insert #tmp exec sp_linkedservers

go

select * from #tmp

go

drop table #tmp

go

 

 

 

 

 

בדיקה__   בדוק__ 

‏23/02/2012 17:36:04

Check status__  of remote sql servers \\\ test connection  \\\  connection__ 

בדוק סטאטוס__  של ה לינקט סרבר  בדיקה__

http://ask.sqlservercentral.com/questions/29512/check-status-of-remote-sql-server-and-database-usi.html

http://msdn.microsoft.com/en-us/library/ms189809.aspx

 

 

--Tests the connection to a linked server.

--If the test is (un-successful) the procedure

--raises an exception with the reason of the failure.

USE master;

GO

EXEC sp_testlinkedserver [IL-TLV-SQL1]

GO

 

 

פה בודקים את ה לינקט סרבר : אם הוא תקין =  מחזיר Command(s) completed successfully.

.

                                                   אם לא תקין   =  מחזיר את סוג השגיאה .       פה הלינקט הנבדק הוא : [IL-TLV-SQL1]

 

 

SELECT * from [DEV11I]..APPS.BENBV_YR_PERD_V

בדיקה__ מהירה של הלינקט סרבר:

select * From openquery([il-comserv-db],'select getdate()')

 

--also in 2000

select * From openquery([il-comserv-db],'select top 10 * from Extranet_INT.dbo.Users')

select * From openquery([il-tlv-sql1],'select getdate()')

select * From openquery([il-tlv-sql1],'select * from dbo.sysdatabases')

 

 

 

 

בנייה של לינקט סרבר /// הוספה (ישן)

 

‏01/02/2012 11:06:40

 

--build linked server

EXEC master.dbo.sp_addlinkedserver @server = N'OR-PORT-VORA11G',

@srvproduct=N'OraOLEDB',

@provider  =N'OraOLEDB.Oracle',

@datasrc   =N'ORCL'

 

--show all of linked servers

EXEC sp_linkedservers

 

--show all registered servers

SELECT * FROM sysservers

 

http://msdn.microsoft.com/en-us/library/ms189809.aspx

-- TEST if xe linked is ok

sp_testlinkedserver XE

exec sys.sp_testlinkedserver @servername = N'XE'

 

 

 

‏01/02/2012 10:53:16

Linked___  linked_server   by tsql

 

sp_testlinkedserver

 

 

 

EXEC master.dbo.sp_addlinkedserver

@server    = N'IL-TLV-SQL1',

@srvproduct=N'SQL Server'

 

עוד עותק

EXEC master.dbo.sp_addlinkedserver

@server    = N'IL-TLV-SQL1',

@srvproduct=N'SQL Server'

 

 

 

--base line

EXEC master.dbo.sp_addlinkedserver

@server = N'IL-SQL2005-TEST',

@srvproduct=N'SQL Server'

 

--login

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'IL-SQL2005-TEST',

@useself=N'False',@locallogin=NULL,

@rmtuser=N'av_nis',@rmtpassword='########'

GO

 

‏02/02/2012 14:48:06

 

עוד אחד :

קוד :

 

EXEC master.dbo.sp_addlinkedserver

@server = N'IL-TLV-SQL1',

@srvproduct=N'SQL Server'

 

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'IL-TLV-SQL1',

@useself=N'False',

@locallogin=NULL,

@rmtuser=N'abuser',

@rmtpassword='abuser'

GO

 

 

 

 

 

‏27/06/2012 13:24:02

בנייה של לינקט סרבר /// הוספה

הוספת שרת מקושר SQL עם יוזר אפליקטיבי . סקל סרבר

 

 

-- another one example --

EXEC master.dbo.sp_addlinkedserver

@server = N'il-sql2005-dev',

@srvproduct=N'SQL Server'

 

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'il-sql2005-dev',@useself=N'False',

@locallogin=NULL,

@rmtuser=N'ComserveOne_user',

@rmtpassword='aeeauhe7e3hh'

 

 

 

-- another one example -- 2008

EXEC master.dbo.sp_addlinkedserver

@server = N'il-comserv-db',

@srvproduct=N'SQL Server'

 

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'il-comserv-db',

@useself=N'False',

@locallogin=NULL,

@rmtuser=N'ComserveOne_user',

@rmtpassword='aeeauhe7e3hh'

 

 

 

 

 

בנייה של לינקט סרבר /// הוספה

הוספת שרת מקושר SQL עם יוזר AD . סקל סרבר

 

-- be made using the logins current security context

 

/****** Object:  LinkedServer [IL-TLV-BMOLOKA1\IL_TLV_BMOLOKA11]    Script Date: 06/27/2012 13:29:35 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'il-comserve-db', @srvproduct=N'SQL Server'

 

 /* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'il-comserve-db',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

 

GO

 

 

 

 

 

--another one --

/****** Object:  LinkedServer [US-DATACEN-BB]    Script Date: 10/14/2012 16:45:26 ******/

EXEC master.dbo.sp_addlinkedserver @server = N'US-FOREFRONT', @srvproduct=N'SQL Server'

 

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'US-FOREFRONT',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

 

 

-- also in 2000

 

EXEC master.dbo.sp_addlinkedserver @server = N'IL-TLV-SQLTEST1', @srvproduct=N'SQL Server'

 

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'IL-TLV-SQLTEST1',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

 

GO

 

 

 

 

 

 

 

‏14/10/2012 16:53:43

 

תקלה__   תקלות__

 

אתה מנסה לבנות

Named Pipes Provider: No process is on the other end of the pipe.

 

פיתרון:

בצע ENABLE לפרוטוקול  בשרת שאליו אתה מנסה להתחבר .

 

 

 

 

 

 

 

 

 

 

‏29/08/2012 14:44:12

 

בנייה של לינקט סרבר /// הוספה

הוספת שרת מקושר ORACLE עם יוזר אפליקטיבי . אורקל

 

 

EXEC master.dbo.sp_addlinkedserver @server = N'PROD11I',

                                   @srvproduct=N'ORACLE',

                                   @provider=N'OraOLEDB.Oracle',

                                   @datasrc=N'PROD11I',

                                   @provstr=N'PROD11I'

 

 

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'PROD11I',

                                     @useself=N'False',

                                     @locallogin=NULL,

                                     @rmtuser=N'comserve',

                                     @rmtpassword='comse453hdsi4qw'

 

GO

 

 

-- another one

 

 

EXEC master.dbo.sp_addlinkedserver @server = N'DAILY',

                                   @srvproduct=N'ORACLE',

                                   @provider=N'OraOLEDB.Oracle',

                                   @datasrc=N'DAILY',

                                   @provstr=N'DAILY'

 

 

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'DAILY',

                                     @useself=N'False',

                                     @locallogin=NULL,

                                     @rmtuser=N'apps',

                                     @rmtpassword='clone'

 

GO

 

 

 

 

 

 

‏12/07/2012 11:53:20

בנייה של לינקט סרבר /// הוספה

הוספת שרת מקושר SQL עם יוזר : Be made by using the login’s current security content

 

 

 

/****** Object:  LinkedServer [IL-SQL2005-3] 

Script Date: 07/12/2012 11:51:08 ******/

EXEC master.dbo.sp_addlinkedserver

@server = N'IL-SQL2005-TEST\DEV',

@srvproduct=N'SQL Server'

 

 

 /* For security reasons the linked server remote

 logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname=N'IL-SQL2005-TEST\DEV',

@useself=N'True',

@locallogin=NULL,

@rmtuser=NULL,

@rmtpassword=NULL

GO

 

 

 

-- another one example --

EXEC master.dbo.sp_addlinkedserver

            @server = N'il-extranet-db,1733',

            @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin

            @rmtsrvname=N'il-extranet-db,1733',

            @useself=N'True',@locallogin=NULL,

            @rmtuser=NULL,@rmtpassword=NULL

GO

 

 

-- another one example --

EXEC master.dbo.sp_addlinkedserver

            @server = N'US-DATACEN-BB',

            @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin

            @rmtsrvname=N'US-DATACEN-BB',

            @useself=N'True',@locallogin=NULL,

            @rmtuser=NULL,@rmtpassword=NULL

GO

 

 

 

‏27/06/2012 13:40:53

drop user from linked server

http://msdn.microsoft.com/en-us/library/ms186218.aspx

 

EXEC sp_droplinkedsrvlogin 'Accounts', 'Mary'

 

 

 

 

‏17/05/2012 14:43:58

 

בנייה של לינקט סרבר /// הוספה

סקריפט – סגור ליצירת לינקט סרבר . שלב א' = יצירה . שלב ב' = הוספת לוגאין .

 

 

EXEC master.dbo.sp_addlinkedserver

@server    = N'DAILY',

@srvproduct=N'microsoft',

@provider  =N'MSDAORA',

@datasrc   =N'DAILY',

@provstr   =N'DAILY'

 

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname =N'DAILY',

@useself    =N'False',

@locallogin =NULL,

@rmtuser    =N'apps',

@rmtpassword='clone'

 

GO

 

 

/****** Object:  LinkedServer [DAILY]    Script Date: 05/17/2012 04:52:13 ******/

EXEC master.dbo.sp_addlinkedserver

@server    = N'DAILY',

@srvproduct=N'DAILY',

@provider  =N'OraOLEDB.Oracle',

@datasrc   =N'DAILY',

@provstr   =N'DAILY'

 

 

 EXEC master.dbo.sp_addlinkedsrvlogin

 @rmtsrvname    =N'DAILY',

 @useself       =N'False',

 @locallogin    =NULL,

 @rmtuser       =N'apps',

 @rmtpassword   ='clone'

 

 

 

 

 

 

 

 

 

 

‏07/03/2012 09:50:21

linked serever to excel__

 

http://support.microsoft.com/kb/306397

עובד!!

 

 

 

 

DECLARE @RC         int

DECLARE @server     nvarchar(128)

DECLARE @srvproduct nvarchar(128)

DECLARE @provider   nvarchar(128)

DECLARE @datasrc    nvarchar(4000)

DECLARE @location   nvarchar(4000)

DECLARE @provstr    nvarchar(4000)

DECLARE @catalog    nvarchar(128)

-- Set parameter values

SET @server     = 'XLTEST_SP'

SET @srvproduct = 'Excel'

SET @provider   = 'Microsoft.Jet.OLEDB.4.0'

SET @datasrc    = 'd:\book1.xls'

SET @provstr    = 'Excel 8.0'

EXEC @RC = [master].[dbo].[sp_addlinkedserver] @server, @srvproduct, @provider,

@datasrc, @location, @provstr, @catalog

                       

 

 

 

‏08/03/2012 13:09:44

--After you configure an Excel data source as a linked server,

--to retrieve in Sheet1

 

SELECT * FROM XLTEST_SP...Sheet1$

 

 

 

 

 

 

‏08/03/2012 13:01:10

 

Excel versions

 

http://www.cpearson.com/excel/versions.htm

http://en.wikipedia.org/wiki/Microsoft_Excel

 

 

 

‏02/06/2013 16:38:02

Rename__   rename_linked_server

http://www.developmentnow.com/g/113_2004_8_0_0_432082/Renaming-a-linked-server.htm

http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/a6add992-8e24-4b4e-9fd6-db33f77ea5cd

 

There is no way to rename a linked server. Although there is a way point an  existing linked server to a different server, without renaming.

 

 

 

 

 

 

 

 

 

 

‏15/10/2012 11:08:00

 

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

 

 

‏16/10/2012 16:47:40  מצאנו את הבעיה:

http://dbaspot.com/ms-sqlserver/415733-linked-server-login-failed-user-nt-authority-anonymous-logon.html

 

 

Run this:

 

select auth_scheme

from sys.dm_exec_connections

where session_id=@@spid   

 

if it says NTLM that's your problem. NTLM won't allow more than one hop where KERBEROS will allow several and the hop from your client to the server counts. A linked server setup to use the security context of the calling login will work if Kerberos is setup.

 

אכן כאשר התוצאה הייתה NTLM = הוא לא עבד לי . השאלה אם ניתן לשנות את זה בכלל .

 

 

 

‏11/11/2012 14:29:28

מיקומו ב GUI של ה 2000

 

 

 

 

 

 

 

 
 
 
sp_who  \\\ sp_who2 
 
DECLARE @Table TABLE
(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,
        LastBatch VARCHAR(MAX),
        ProgramName VARCHAR(MAX),
        SPID_1 INT,
        REQUESTID INT
)
 
INSERT INTO @Table EXEC sp_who2
 
SELECT  *
FROM    @Table
WHERE LOGIN like '%xxx%'

 

 

 

 

 

 

טיפול בגרש__   גרשיים__   גרש עילי__   Apostrophe superscript

print ''''
 
-- bring: '
 
print 'asa''sasas'
-- bring: asa'sasas
 
select 'zbi' + ''''
--bring: zbi' 

------------------------------------------------------------------

-- 10/11/2016   export data from sql server - to excel - csv ready to load 
 
-- use sql09  -- add apostrophe for EXCEL 
 
use db069
go
select 
      --[ms_kartis_ashrai_decrypt] as credit_card_number, 
      --[ms_kartis_ashrai_decrypt] as return_token 
      
 '''' + [ms_kartis_ashrai_decrypt] as credit_card_number, 
      '''' + [ms_kartis_ashrai_decrypt] as return_token 
 
  FROM [db069].[dbo].[grc_tmp_crd_card_unique]
 
  /*
  credit_card_number return_token
  '0000000062810146 '0000000062810146
  '4580120742577725 '4580120742577725
 
  */

 

 

 

 

 

 

 

last__ time reboot__  restart__   uptime__   BARZEL

U:\>systeminfo | find /i "Boot Time"
System Boot Time:          04/10/2016, 15:55:46
 

Last__ time reboot__  restart__   uptime__   SQL INSTANCE

SELECT sqlserver_start_time FROM sys.dm_os_sys_info

 

 

 

--sql09
-- take this HFFA :: TA__muni__ TA_muni   -linked__ server - create by code .
--after creation remove users you dont need .  
--
USE [master]
GO
 
EXEC master.dbo.sp_addlinkedserver @server = N'system01sql2008\sql2008', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'system01sql2008\sql2008',@useself=N'False',@locallogin=N'db026',@rmtuser=N'db026',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'system01sql2008\sql2008',@useself=N'False',@locallogin=N'db115',@rmtuser=N'db115',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'system01sql2008\sql2008',@useself=N'False',@locallogin=N'db351',@rmtuser=N'db351',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'system01sql2008\sql2008',@useself=N'False',@locallogin=N'db650_p',@rmtuser=N'db650_p',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'system01sql2008\sql2008',@useself=N'True',@locallogin=N'DOM001\sqlserver',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'system01sql2008\sql2008',@useself=N'False',@locallogin=N'sa',@rmtuser=N'sa',@rmtpassword='########'
 
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'collation compatible', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'data access', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'dist', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'pub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'rpc', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'rpc out', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'sub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'connect timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'collation name', @optvalue=null
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'lazy schema validation', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'query timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'use remote collation', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'system01sql2008\sql2008', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
 
 

---check : EXEC('SELECT @@SERVERNAME;') AT [SQL07\PREPRODOP];   --  בדיקה

 

 

--09/11/2016

-- linked server for INT1

/****** Object:  LinkedServer [10.11.50.41]    Script Date: 11/09/2016 11:57:43 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'10.11.50.41', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'10.11.50.41',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL
 
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'collation compatible', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'data access', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'dist', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'pub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'rpc', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'rpc out', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'sub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'connect timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'collation name', @optvalue=null
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'lazy schema validation', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'query timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'use remote collation', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.41', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO
 
 
 
 

-- linked server from INT1 to DOM001

 

USE [master]
GO
 
/****** Object:  LinkedServer [10.9.70.1,1477]    Script Date: 12/24/2019 5:29:05 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'10.9.70.1,1477', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'10.9.70.1,1477',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'10.9.70.1,1477',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL
 
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'collation compatible', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'data access', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'dist', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'pub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'rpc', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'rpc out', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'sub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'connect timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'collation name', @optvalue=null
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'lazy schema validation', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'query timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'use remote collation', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.9.70.1,1477', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO
 
 

 

 

--09/11/2016

-- linked server NOSAF for INT1-- עבור הנטוויון הישן NETVISION

/****** Object:  LinkedServer [10.11.50.32,1411]    Script Date: 11/09/2016 11:59:25 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'10.11.50.32,1411', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'10.11.50.32,1411',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'10.11.50.32,1411',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL
 
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'collation compatible', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'data access', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'dist', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'pub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'rpc', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'rpc out', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'sub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'connect timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'collation name', @optvalue=null
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'lazy schema validation', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'query timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'use remote collation', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.11.50.32,1411', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO
 
 

 

 

 

 

 

 
 
 
--31122018  
-- this is work also for LISTENER
-- linked server for INT1
 
/****** Object:  LinkedServer [10.111.50.9,1444]    Script Date: 11/09/2016 11:57:43 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'10.111.50.9,1444', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'10.111.50.9,1444',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL
 
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'collation compatible', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'data access', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'dist', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'pub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'rpc', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'rpc out', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'sub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'connect timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'collation name', @optvalue=null
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'lazy schema validation', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'query timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'use remote collation', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.9,1444', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO
 
 

 

 
 
--20122018
 
-- linked server for INT1
 
-- from AZURE to AZURE -- it is NOT Cross Domain
 
/****** Object:  LinkedServer [10.111.50.5]    Script Date: 11/09/2016 11:57:43 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'10.111.50.5', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'10.111.50.5',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL
 
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'collation compatible', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'data access', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'dist', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'pub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'rpc', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'rpc out', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'sub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'connect timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'collation name', @optvalue=null
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'lazy schema validation', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'query timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'use remote collation', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.111.50.5', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO
 
 

 

 

-- 31122018

USE [master]
GO
 
/****** Object:  LinkedServer [SQL09]    Script Date: 31/12/2018 11:25:49 ******/
 
 
EXEC master.dbo.sp_addlinkedserver @server = N'SQL09', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL09',@useself=N'False',@locallogin=NULL,@rmtuser=N'db851',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL09',@useself=N'False',@locallogin=N'db851',@rmtuser=N'db851',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL09',@useself=N'False',@locallogin=N'sa',@rmtuser=N'sa',@rmtpassword='########'
 
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'collation compatible', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'data access', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'dist', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'pub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'rpc', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'rpc out', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'sub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'connect timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'collation name', @optvalue=null
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'lazy schema validation', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'query timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'use remote collation', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'SQL09', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
 
 
 
 
-- for boris MBT servers from : 10.91.70.18  -- MBT-SQL01  --   MBT-SQL01\PREPROD01
/****** Object:  LinkedServer [BIDWHPROD01]    Script Date: 01/02/2019 07:53:58 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'BIDWHPROD01', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BIDWHPROD01',@useself=N'False',@locallogin=NULL,@rmtuser=N'db832_p',@rmtpassword='########'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'BIDWHPROD01',@useself=N'False',@locallogin=N'sa',@rmtuser=N'sa',@rmtpassword='########'
 
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'collation compatible', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'data access', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'dist', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'pub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'rpc', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'rpc out', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'sub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'connect timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'collation name', @optvalue=null
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'lazy schema validation', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'query timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'use remote collation', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'BIDWHPROD01', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO
 
 

add user to linked server :

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL07\PREPRODOP',@useself=N'False',@locallogin=N'sa_admin',@rmtuser=N'sa_admin',@rmtpassword='nheruxupy2015'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQL07\PREPRODOP',@useself=N'True',@locallogin=N'DOM001\taldortest',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'pbxqa-uccx',@useself=N'True',@locallogin=N'DOM001\taldortest',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'pbxqa-uccx',@useself=N'True',@locallogin=N'DOM001\sqlserver',@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'pbxqa-uccx',@useself=N'False',@locallogin=N'db760_ppr',@rmtuser=N'db760_ppr',@rmtpassword='h#649760'
 

 

 

 

 

notice: 

all the windows user are impersonate

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO db760_ppr

 

 

 

 

01/02/2016

--- this for cloud of משרד התחבורה  -- SQL AZURE__

----------------------------------------------------------------

 
USE [master];
GO
 
EXEC master.dbo.sp_MSset_oledb_prop N'SQLNCLI11', N'AllowInProcess', 1;
GO
 
-- https://blogs.msdn.microsoft.com/sqldw/2016/04/12/connecting-a-linked-server-to-azure-sql-data-warehouse/
 
------------------------------------------------------------------

USE [master]

GO
 
/****** Object:  LinkedServer [CLOUDDW]    Script Date: 01/02/2017 17:09:32 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'CLOUDDW', @srvproduct=N'SQLDW', @provider=N'SQLNCLI11', @datasrc=N'mobidb.database.windows.net,1433', @provstr=N'Server=mobidb.database.windows.net,1433;Database=TelAvivMuni;Pooling=False', @catalog=N'TelAvivMuni'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CLOUDDW',@useself=N'False',@locallogin=NULL,@rmtuser=N'TelAvivReadOnlyLogin',@rmtpassword='########'
 
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'collation compatible', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'data access', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'dist', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'pub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'rpc', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'rpc out', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'sub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'connect timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'collation name', @optvalue=null
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'lazy schema validation', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'query timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'use remote collation', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'CLOUDDW', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO
 
 

-- AAVR ulimate__ linked server base__ for linked server   sa__

USE [master]
GO
 
/****** Object:  LinkedServer [10.1.70.223,1433]    Script Date: 10/10/2019 3:28:43 PM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'10.1.70.223,1433', @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'10.1.70.223,1433',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'10.1.70.223,1433',@useself=N'True',@locallogin=N'sa',@rmtuser=NULL,@rmtpassword=NULL
 
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'collation compatible', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'data access', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'dist', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'pub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'rpc', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'rpc out', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'sub', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'connect timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'collation name', @optvalue=null
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'lazy schema validation', @optvalue=N'false'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'query timeout', @optvalue=N'0'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'use remote collation', @optvalue=N'true'
GO
 
EXEC master.dbo.sp_serveroption @server=N'10.1.70.223,1433', @optname=N'remote proc transaction promotion', @optvalue=N'false'
GO
 
 

 

 

 

 

 

 

 

 

-- find current database's transaction level
 
use mdb
go 
 
SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID

 

 

 

יורי__ דוח להדס  מי משתמש ב SSRS   reporting  

 
 
 
USE ReportServer
GO
 
SELECT top 1000[InstanceName]
      --,[ReportPath]
      ,[ReportName]
      ,[UserName]
      ,[RequestType]
      --,[Format]
      --,[Parameters]
      --,[ReportAction]
      ,[TimeStart]
      --,[TimeEnd]
      --,[TimeDataRetrieval]
      --,[TimeProcessing]
      --,[TimeRendering]
      --,[Source]
      --,[Status]
      --,[ByteCount]
      --,[RowCount]
      --,[AdditionalInfo]
  FROM [ReportServer].[dbo].[ExecutionLog2]
  where [TimeStart] > '20160701' 
  ORDER BY [TimeStart]desc
  
 
 
 
 
 

 

 

 

 

 

 

ERRORLOG__

http://blog.sqlauthority.com/2015/03/24/sql-server-where-is-errorlog-various-ways-to-find-its-location/

USE MASTER
GO
EXEC xp_readerrorlog 0, 1, N'Logging SQL Server messages in file'
GO
 

 

 

 

 

 
tsq get all activity__ done by specific__ user  -- עבור אורן קדושים - קדימה - צורן
 
 
 
select sys.dm_exec_sessions.session_id,
sys.dm_exec_sessions.host_name,
sys.dm_exec_sessions.program_name,
sys.dm_exec_sessions.client_interface_name,
sys.dm_exec_sessions.login_name,
sys.dm_exec_sessions.nt_domain,
sys.dm_exec_sessions.nt_user_name,
sys.dm_exec_connections.client_net_address,
sys.dm_exec_connections.local_net_address,
sys.dm_exec_connections.connection_id,
sys.dm_exec_connections.parent_connection_id,
sys.dm_exec_connections.most_recent_sql_handle,
(select text from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as sqlscript,
(select db_name(dbid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as databasename,
(select object_id(objectid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as objectname
from sys.dm_exec_sessions inner join sys.dm_exec_connections
on sys.dm_exec_connections.session_id=sys.dm_exec_sessions.session_id
where login_name='DOM001\x5544895'
 
 

 

 

 
--select * from sys.dm_exec_sessions     -- login_time
--select * from sys.dm_exec_connections  -- connect_time
 
--SELECT *
--  FROM [a0_admin].[dbo].[T_tmp1_elampse_time]
--  where DATEPART(HOUR, last_execution_time) between 3 and 5 
 
 
 
 
select h.*
from 
(
select sys.dm_exec_sessions.session_id,
sys.dm_exec_sessions.login_time,
sys.dm_exec_sessions.host_name,
sys.dm_exec_sessions.program_name,
sys.dm_exec_sessions.client_interface_name,
sys.dm_exec_sessions.login_name,
sys.dm_exec_sessions.nt_domain,
sys.dm_exec_sessions.nt_user_name,
sys.dm_exec_connections.client_net_address,
sys.dm_exec_connections.local_net_address,
sys.dm_exec_connections.connection_id,
sys.dm_exec_connections.parent_connection_id,
sys.dm_exec_connections.most_recent_sql_handle,
(select text from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as sqlscript,
(select db_name(dbid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as databasename,
(select object_id(objectid) from master.sys.dm_exec_sql_text(sys.dm_exec_connections.most_recent_sql_handle )) as objectname
from sys.dm_exec_sessions inner join sys.dm_exec_connections
on sys.dm_exec_connections.session_id=sys.dm_exec_sessions.session_id
 
 
--where DATEPART(HOUR, sys.dm_exec_sessions.login_time) between 10 and 11    
--and login_name in 'DOM001\x5544895'
--order by sys.dm_exec_sessions.login_time desc
)h
--where h.databasename ='db940'
 

 

 

סרגיי sergey FW: ODBC informix

עברית__  הפיכה ג'יבריש לעברית 

USE [a0_admin]

GO
 
/****** Object:  UserDefinedFunction [dbo].[UTF8_TO_NVARCHAR]    Script Date: 08/09/2016 09:40:38 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
 
 
 
CREATE FUNCTION [dbo].[UTF8_TO_NVARCHAR](@in VarChar(MAX))
   RETURNS NVarChar(MAX)
AS
BEGIN
   DECLARE @out NVarChar(MAX), @i int, @c int, @c2 int, @c3 int, @nc int
 
   SELECT @i = 1, @out = ''
    
   WHILE (@i <= Len(@in))
   BEGIN
      SET @c = Ascii(SubString(@in, @i, 1))
 
      IF (@c < 128)
      BEGIN
         SET @nc = @c
         SET @i = @i + 1
      END
      ELSE IF (@c > 191 AND @c < 224)
      BEGIN
         SET @c2 = Ascii(SubString(@in, @i + 1, 1))
          
         SET @nc = (((@c & 31) * 64 /* << 6 */) | (@c2 & 63))
         SET @i = @i + 2
      END
      ELSE
      BEGIN
         SET @c2 = Ascii(SubString(@in, @i + 1, 1))
         SET @c3 = Ascii(SubString(@in, @i + 2, 1))
          
         SET @nc = (((@c & 15) * 4096 /* << 12 */) | ((@c2 & 63) * 64 /* << 6 */) | (@c3 & 63))
         SET @i = @i + 3
      END
 
      SET @out = @out + NChar(@nc)
   END
   RETURN @out
END
 
 
GO
 
 

 

 

 

 

/*
-- time zone__ || UTC__ \\ GMT__ CRM__ \\BORIS \\SHAY 
http://stackoverflow.com/questions/14996306/getutcdate-function
*/
 
 
DECLARE @local_time DATETIME;
DECLARE @gmt_time DATETIME;
SET @local_time = GETDATE();
 
SET @gmt_time = GETUTCDATE();
SELECT 'Server local time: '
   + CONVERT(VARCHAR(40),@local_time);
SELECT 'Server GMT time: '
   + CONVERT(VARCHAR(40),@gmt_time);
SELECT 'Server time zone: '
   + CONVERT(VARCHAR(40),
      DATEDIFF(hour,@gmt_time,@local_time));
GO

 

 

 
-- get all permissions__ of user on this database 
 
select  
        DB_NAME() AS [Current Database]
,       princ.name
,       princ.type_desc
,       perm.permission_name
,       perm.state_desc
,       perm.class_desc
,       object_name(perm.major_id)
from    sys.database_principals princ
left join
        sys.database_permissions perm
on      perm.grantee_principal_id = princ.principal_id
where name like '%DOM001\Db_dev_db793%'
 
select DB_NAME() AS [Current Database]
 
-- revoke__ user from select table -- deny__ 
 
  USE db793
  go   
 
REVOKE SELECT ON OBJECT::[dbo].[DocStoreProjectsList] FROM [dom001\db_dev_db793];  
 
 
DENY SELECT ON OBJECT::[dbo].[DocStoreCategoriesPermissions] TO [dom001\db_dev_db793]; 
DENY SELECT ON OBJECT::[dbo].[DocStoreMetadata] TO [dom001\db_dev_db793];  
DENY SELECT ON OBJECT::[dbo].[DocStoreProjectsList] TO [dom001\db_dev_db793];   
 
GO  

 

 

 
 
-- hide__ table from user 
 
EXEC sp_addextendedproperty 
@name = N'microsoft_database_tools_support', 
@value = '<Hide? , sysname, 1>', 
@level0type ='schema', 
@level0name ='dbo',  --give the schema name here. if it is dbo give 'dbo'
@level1type = 'table', 
@level1name = 'hafi'  --give the table name which you want to hide.
 
 -- http://avinashily.blogspot.co.il/2011/05/how-to-hide-table-in-sql-server.html
 

 

 

 

 -מיקי- hilay -- הילי - יפתח - מערכת תלמידים -- יורי-נקודת יחוס -
https://www.w3schools.com/sql/func_dateadd.asp
 
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/50c3ff6b-df3e-4801-9bcc-fec81e7806b9/convert-number-to-a-readable-date-format?forum=transactsql
 
הילי/אבנר – בוקר טוב,
 
בבקשה, תשלחו אלי את הפרוצדורה או סקריפט לתרגום שדות מספר כלשהו לתאריך (בפיענוח בסיס נתוני השפ"ח)
 
לדוגמא  -  בטבלת תלמידים - Card2Tblpsychology  בסיס נתונים db942_mk\sql08\devop
 
שם שדה                                   סוג שדה           ערך מוצג          מה התרגום שלו (ב- yyyymmdd)
[DataCardCreated]           int           1316410039
[DataCardLastUpdated]      int           1485763463
 
 
 
  SELECT DATEADD(ss, 1485763463, '19700101')
  SELECT DATEADD(ss, 1316410039, '19700101')
 
 
--results:
-----------------------
2017-01-30 08:04:23.000
 
(1 row(s) affected)
 
 
-----------------------
2011-09-19 05:27:19.000
 
(1 row(s) affected)
 

--  This time format is the UNIX epoch and it represents the number of seconds since january first, 1970 in UTC time. 

 

 

 

 

בניית אתר בחינם

במידה ואתה רוצה לבנות אתר בחינם, תוכל להתנסות באחת מהמערכות שלנו לבניית אתרים בחינם, בחר עיצוב והתחל לבנות אתר לעסק שלך
 
מצד שמאל תוכלו לראות מגוון עיצובים שתוכלו לבחור לאתר הסלולרי החדש שתבנו בזופ בדקות הקרובות
 
לאחר שתכנסו למערכת הניהול תחשפו לאפשרויות רבות לניהול התוכן באתר, תקבלו קוד QR ייחודי לאתר שלכם, תוכלו לנהל דפי תוכן, מפות, טפסים ועוד..
 
לחץ כאן לבניית אתר בחינם

החבילות של זופ

זופ מציעה שירותי פרימיום, לניהול אתר המובייל שלך.  שימוש בדומיין שלך, אחסון ברמה גבוהה , קידום באינדקס אתרים שלנו ועוד...

 

 
לחץ כאן לצפייה בחבילות של זופ