Is there a way for me to set up CDC so that all the processing (SQL Agent, etc) happens on the machine receiving the data? I'd like to move as much of the processing as possible to the destination.
View 3 Repliescan any one tell me what are the sql server dba activities?
i'm a junnior dba in hyderabad, my organization is product based company.
i need the general activities, so that i can become master in atleast some aspects.
generally what is the expectations of big organizations from DBA's? especially i'm plannning for H1.
Today when I opened current activity window on my SQL6.5 server, I could not able to see any activities listed. What will be the problem?
But sp_who works fine.
Srini
Someone had changed the SA password on one of my servers. I need to find out who did this. Can you tell me if there is any historical information kept on any of the system tables that can tell me who (what machine name) and when (date and time)this was done?
Does anyone have a 3rd party or inhouse developed task/procedure to report this kind of security issues?
Hi, I've a table named Attività there I can have many type "IDAttivitaTipo" field:T TelefonataV VisitaP PreventivoC Chiuso CREATE TABLE [dbo].[Attivita]( [IDAttivita] [int] IDENTITY(1,1) NOT NULL, [IDAttivitaStato] [varchar](1) COLLATE Latin1_General_CI_AS NULL, [IDAttivitaTipo] [varchar](2) COLLATE Latin1_General_CI_AS NULL, [IDAnagrafica] [int] NULL, [Data] [datetime] NULL CONSTRAINT [DF_Attivita_Data] DEFAULT (getdate()), [Descrizione] [varchar](max) COLLATE Latin1_General_CI_AS NULL, CONSTRAINT [PK_Attivita] PRIMARY KEY CLUSTERED ( [IDAttivita] ASC)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY] with "IDAttivitaTipo":T TelefonataV VisitaP PreventivoC Chiuso with "IDAttivitaStato" E Eseguita (held)D Da Eseguire (not held)I would like to see the state of each IDAnagrafica (company) creating a query with X rows (X companies ... X IDAzienda) and 4 fields 1/0 (true/false ....bit) that tell me if there is or there isn't an activity (IDAttivita) in T V P C type for each IDAzienda (company) .... with IDAttivitaStato E (held)T -> 1/0V -> 1/0P -> 1/0C -> 1/0I've created a SP with a parameter "IDAttivitaTipo" that allow me to view all campanies (IDAnagrafica) stopped in the "IDAttivitaTipo"if I use 'V' parameter in the SPI want to see all the companies stopped in V (IDAttivitaStato E -> Held) ...that don't have activities in P and C state .... I'm not interested if there are activities in T stateif I use 'P' parameter in the SP
I want to see all the companies stopped in P (IDAttivitaStato P ->
Held) ...that don't have activities C state .... I'm not interested if there are activities in T or V state
if I use 'C' parameter in the SP
I want to see all the companies stopped in C (IDAttivitaStato C ->
Held) ...that don't have activities after C .... I'm not interested if there are activities in T or V or P state
I've tried with these query but the result insn't right ... I can't to see some activieswhere is it my error??IF(@IDAttivitaTipo= 'V') BEGIN SELECT
(case when exists (SELECT 1 FROM Attivita WHERE (Attivita.IDAnagrafica
= Anagrafica.IDAnagrafica) AND (IDAttivitaTipo='T') AND
(IDAttivitaStato='E') AND (Privato = 0)) then 1 else 0 end) as
Telefonata, (case when exists (SELECT 1 FROM Attivita WHERE
(Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='V') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Visita, (case when exists (SELECT 1 FROM Attivita
WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='P') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Preventivo, (case when exists (SELECT 1 FROM
Attivita WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='C') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Chiuso FROM Anagrafica WHERE ('V' in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND ('P' not in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND ('C' not in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) ENDIF(@IDAttivitaTipo = 'P') BEGIN SELECT
(case when exists (SELECT 1 FROM Attivita WHERE (Attivita.IDAnagrafica
= Anagrafica.IDAnagrafica) AND (IDAttivitaTipo='T') AND
(IDAttivitaStato='E') AND (Privato = 0)) then 1 else 0 end) as
Telefonata, (case when exists (SELECT 1 FROM Attivita WHERE
(Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='V') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Visita, (case when exists (SELECT 1 FROM Attivita
WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='P') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Preventivo, (case when exists (SELECT 1 FROM
Attivita WHERE (Attivita.IDAnagrafica = Anagrafica.IDAnagrafica) AND
(IDAttivitaTipo='C') AND (IDAttivitaStato='E') AND (Privato = 0)) then
1 else 0 end) as Chiuso FROM Anagrafica WHERE ('P' in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND ('C' not in (SELECT IDAttivitaTipo FROM Attivita WHERE Attivita.IDAnagrafica = Anagrafica.IDAnagrafica)) AND END
We are having blocking issues on our server. Recently, I noticed that we no longer have any thing under Current Activity. When I click the plus sign beside it, it shows no items - that is, no process info, locks/process ID, or the locks/object. Does anybody know why and how I can reset it?? Thanks for your help.
View 3 Replies View RelatedWe are finding ourselves editing data within a sql database using tools such as MS Query, Access or VB. Is there anyway to log these edits? Auditing is set up within the application to log changes made by the users but not by third pary applications. ANy thoughts?
Thanks,
David
Sybase and DB2 both have the capability of tracking user activities ata number of levels: invalid access attempts to databases, table, etc.;creation/deletion/modification of database objects/users/groups,grants/revokes.For MS SQLServer, the only setting that I've seen in the documentationis access attempts (none, fail only, etc.)The monitor program has the capability of tracking the events that Iwant to be monitored, but it seems as though these settings persistonly while the monitor program is running.I'd like these settings to persist permanently and the event records tobe sent to the system log.I can't seem to find the right term to get this information out of theMS Books On LIne.Help!
View 5 Replies View RelatedAm I able to do this in SS Express -like a backup or something or do I need to purhcase a SQL Server Agent (I believe I saw a 3rd party company always advertising this in SQL Server Central.com). It's cheap -- just curious if I really need it.
View 1 Replies View RelatedHi Gurus,
I have a database server running on a sql server 7.0. Some external app is accessing this db server, reading and updating some tables. For some reason, some updating actions didn't work. I just want to track those updating actions, and see if there're some logs reporting failure reason. Is there some tool in sql server 7.0 that could help me do that? Thank you.
My data is in the following form:
CREATE TABLE Temp (ORG_NAME VARCHAR(20), START_TIME datetime, END_TIME datetime)
INSERT INTO Temp VALUES('Org Name', '2014-06-20 14:25:00.000', '2014-06-20 15:25:00.000') - AND many more like these with different START_TIME and END_TIME.
The Task:
- I need to calculate the duration of the activities PER HOUR.
i.e. in the example above, if I want to see the productivity for 2PM (i.e. activity duration from 2-3PM), I should only get 35 mins (as the activity started at 14:25). In the same way, if I see the productivity for 3PM (i.e activity duration from 3-4 PM), I should only see 25 minutes (as activity ended at 15:25).
There would technically be many activities with overlapping times - for example, there might be 5 activities starting at 14:30 and ending at 15:10. In this case, if I were to see the productivity for 2PM, I'd see 150 minutes (as each activity starts at 14:30, so 30 min per activity = 150 min). In the same way, if I saw the productivity for 3PM for those 5 activities, I'd see 50 minutes.
On the other database types, there is an audit capability in that yourecord such items asfailed login attemptsattemtped access to tables user is not authroized tochanges to databse schemachanges to permissionschanges to logins (add, delete, lock, unlock, passwrod reset)All I can find in the SQLServer documentation is the reference totracking failed logins when you set up a database, plus the Profiler'sactivities.Yes, I'm taking voer my first SQLServer database and have been asked tomake sure that this database is closely monitored for inappripriateactivity.Questions:1) Does SQLServer have this capability? (Sybase has this, which iswhere I'm coming from)2) Does SQLServer do this automatically or do I have to set up theevents to be tracked as happens with Sybase?3) What commands are there for setting up these events to be tracked?Thanks in advance!
View 2 Replies View RelatedThe requirement is to customize database admin activities by creating new user group.
Need to create a group of user / dbauser1 which will have restriction in seeing the data but they should be able to alter database - add / remove the data file , increase or decrease the data file space when required.
This requirement came we wanted to create a new dba group they should not be able to any user data / any table but increase / decrease / add / modify space etc.
I have to make server trigger to monitor the actitites if sysadmin members. i need to get the login name,hostname and query which they are running.
View 3 Replies View RelatedHelp!
I am using Script Transformation to output a new column as image[DT_IMAGE]
field to store serialized object. In the VB script, the sample code as
Row.serializedobject.AddBlobData ( binaryArrayReturnedFromC#dll )
The package always runs fine on my developing machine and will halt on other
machine at AddBlobData after certain number row records were processed. I am
stuck here. Anyone has any suggestion?
What I need is reading data from mutiple tables in one database and writing
into a single table in another datable. In order preserve all the columns
data, I use input column fields to construct a new object and then serialize
it, and store the serialize data into detination db table. (The object and
serialization function is coming from c# dll.)
Dim b As BusinessLicense = New BusinessLicense()
b.ApprovalDate = Row.approvaldate
b.BusinessId = Row.busid
b.BusinessName = Row.busname
b.NaicsCode = Row.naicscode
b.NaicsDescription = Row.naicsdescr
b.OwnerName = Row.ownername
b.Phone = Row.phone
b.Pkey = Row.pkey
b.RenewalDate = Row.renewaldate
b.StartDate = Row.startdate
b.Suite = Row.suite
Row.serializedobject.AddBlobData(Serializer.Serialize(b)) '''----This is blocking line
Row.infoType = BusinessLicense.TYPE
Both machine is xp with sp2. and standard SQL Server 2005 - 9.00.1399.06
Thanks!
Hi All,
I have enterprise version software CD1 and CD2 of SQL server 2005
when i try to run setup.exe..the following error pops -up
The image file Setup.exe is valid, but is for a machine type other than the current machine
MY OS version is windows 2003 32bit Operating system...
I am suspecting the binarie may be of 64bit.. How do we check the binaries are 32 bit and 64 bit software
Thanks for your Help
Mohan
Hi to everyone,
I'm facing a problem that drives me crazy. I've a web application that has the following problem. When I test the application on my developement machine all runs fine, but when I put into production server there is a problem in connection pooling. Look at the following image, the blue line is the number of connection in the pool and the violet line is the number of connection reclaimed. From the image it is clear that connection are returned on the pool only in block, maybe when the garbage collection pass and reclaim the object. The strange thing is that on my developement machine all is good, and also I'm using Enterprise Library and the connection are managed internally.- Thanks to everyone for any help.
Hullo all
I have two machines,
One is a standalone machine and the other is on a domain network. How can I run a stored procedure/job on the standalone machine from the domain machine ?
running the procedure as a Domain user results in a failed job/stored proc.
also creating an sql login and attempting to run it as that user also fails. How can I solve this problem ?
please e-mail me at wayde@sunnygrp.com if you have any thoughts...
Hi All;We are going to change our application server. We will copy all of ourDatabase from Mic 2000 Server OS to Mic 2003 Server OS. I found anarticle that how to move all the folders from same OS. My question isthat Would the 2003 OS be a problem when we copy all of the MC SQL 2000folders to New OS ? Has anybody done this before? Could you gimme yoursuggestions please?ThanksASA
View 1 Replies View RelatedHi,
here goes the question:
i have 1 sqlserver 2k5 machine (machine A) and sqlserver 2k (machine b)
how do i build a query to select data from 2k machine in a query of 2k5?
i mean something like
select *
from login:passw@machineb.dabatase.dbo.customers
thanks!
Hi,
here goes the question:
i have 1 sqlserver 2k5 machine (machine A) and sqlserver 2k (machine b)
how do i build a query to select data from 2k machine in a query of 2k5?
i mean something like
select *
from loginassw@machineb.dabatase.dbo.customers
thanks!
Hi,
If I use GetDate() function from client machine (using "\ServerMachineSQLExpress" instance from client machine "ClientMachine"), then from where I will get the DateTime value ?
From "ServerMachine" or from "ClientMachine"
Thanks
Hi Guys,
I was wondering if there was any white papers on the migration part from microsoft?
Apparently the situation is like this
Currently installed MOSS 2007 in a testing pc as application server. This application server will refer another testing database server which running SQL Server 2005.
Now, wanted to move this 2 temporary server into another 2 new servers.
Correct me if I'm wrong, it cannot be done by using image like Norton Ghost due to difference hardware spec.
So, if I install another fresh copy of MOSS2007 & SQL server 2005 in that 2 new servers, how can I migrate the contents, documents, etc from the testing database server?
It would be great if someone can show me the links to kb article. Thanks
Hi there,
I am writing an SQL stored procedure that returns a dataset as well as
a return value. When I execute the stored proc, all seems to
work, except the return value comes back as null.
Code:
// Create Instance of Connection and Command Object
SqlConnection
connection = new SqlConnection(ApplicationConfig.ConnectionString);
SqlCommand
command = new SqlCommand("SoundLeaf_GetPagedProductsByCategory",
connection);
// Mark the Command as a SPROC
command.CommandType = CommandType.StoredProcedure;
// Add Parameters to SPROC
SqlParameter
parameterID = new SqlParameter("@CategoryID", SqlDbType.Int);
parameterID.Value = categoryID;
command.Parameters.Add(parameterID);
SqlParameter
parameterPageIndex = new SqlParameter("@PageIndex", SqlDbType.Int);
parameterPageIndex.Value = pageIndex;
command.Parameters.Add(parameterPageIndex);
SqlParameter
parameterRowCount = new SqlParameter("@RowCount", SqlDbType.Int);
parameterRowCount.Value = rowCount;
command.Parameters.Add(parameterRowCount);
SqlParameter
parameterPageCount = new SqlParameter("@PageCount", SqlDbType.Int);
parameterPageCount.Direction = ParameterDirection.Output;
command.Parameters.Add(parameterPageCount);
// Open the connection and execute the Command
connection.Open();
SqlDataReader
dr = command.ExecuteReader(CommandBehavior.CloseConnection);
pageCount =
(int)command.Parameters["@PageCount"].Value; // Null Reference Exception
}
SQL:
CREATE PROCEDURE GetPagedProductsByCategory]
(
@CategoryId int = 0,
@PageIndex int = 1,
@RowCount int = 10,
@PageCount int OUTPUT
)
AS
Declare @TotalRows int, @StartPosition int
Declare @PK int
DECLARE @tmpTable TABLE (
PK int NOT NULL PRIMARY KEY
)
SELECT @StartPosition = (((@PageIndex - 1) * @RowCount) + 1)
Set @TotalRows = @StartPosition + @RowCount
Set RowCount @TotalRows
DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
select a.ProductId
from
products a,
categoryProducts b
where
b.categoryid = @CategoryID and
b.productid = a.productid
Order By a.productid
Open PagingCursor
Fetch Relative @StartPosition From PagingCursor Into @PK
while (@RowCount <> 0) And (@@Fetch_Status = 0)
begin
Insert Into @tmpTable (PK)
Values (@PK)
Fetch Next From PagingCursor Into @PK
Set @RowCount = @RowCount - 1
end
Close PagingCursor
Deallocate PagingCursor
Select Products.* From Products
Join @tmpTable temp ON Products.ProductID = temp.PK
Order By Products.ProductID
SELECT @PageCount = COUNT(*) / @RowCount FROM Products // value is never returned
Set RowCount 0
GO
Any thoughts?
JR
I have a scenario whereby I will have 200+ clients putting messages onto a service broker queue. This message will go through a pipes and filters based messaging system, and ultimately the message will pop out the other end.
Here's the question: what is a good way of making sure the same client gets the response to the message he received. Is there anyway I can selectively receive messages from a queue, i.e., pass a correlation id in with the message, and then filter messages based on that id.
Or if someone knows a better way to do it altogether i'd really appreciate it.
Many thanks,
Paul
Is it possible to receive from a queue by a conversation handle? In the documentation there is an example that show you how to do it. Yet, if you "read" the whole document it says that the conversation handle can not be an expression.
The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id. The search condition may not contain any of the other columns in the queue. The conversation_handle or conversation_group_id may not be an expression.
Here is what I'm trying to do:
;RECEIVE TOP(1) @MsgBody = CAST(message_body as XML)
FROM ProcessingLetters
WHERE conversation_handle = @Conversation_Handle
It doesn't seem to matter if I use RECEIVE or SELECT. It will return nothing.
I've even tried this:
where cast(Conversation_Handle as varchar(100)) = cast(@Conversation_Handle as varchar(100))
Why am I doing this? I've put something into the queue to let me know that something is processing. When it is done I want to pull it out and end the conversation.
So is the WHERE conversation_handle = @Conversation_Handle supposed to work?
Thanks.
I am right now looking at the new functionality in SSIS to cover some of the ways we can streamline proceses in the company. I know that SSIS has SendMail tasks, however I was wondering if there is any functionality for going out and receiving mail and parsing those messages in? Would this have to be done as custom code within SSIS and if so can anyone direct me to what would be needed to do so? Thanks.
View 4 Replies View Relatedhi friends, i want to store an image in DB. but most of my friends told that, to store an image in web server then store tat location in a DB and access it anywhere. this is for asp.net in C#-code behind. how to do this? i've a table with a text field. then .......? waiting for ur reply............ note: i need coding. not by using controls. pls...
View 1 Replies View RelatedI created a new column and modified my businesslogic file and the stored procedure. My program uses the SQL helpder class to execute the stored procedure which inserts the article into the database. All of the other previous columns are getting written to, however the new column I created is getting NULL when I open the database to inspect. Why does this happen? I made sure the db column name matches exactly with the params elsewhere.Did I miss something? It's not throwing an error anymore. it's not taking the data I enter into the form and putting it into the db column.
View 4 Replies View Related
I have just had SQL Server 2005 installed on my machine. I did have SQL Server 2000 on my machine previously and can still access parts of 2000.
In 2005 I do not have the option schema option under security for any of the databases; therefore, I tried creating the schema manually as you will see below.
In 2005 I execute the following
CREATE SCHEMA TEST
GO
The message returned is "COMMAND COMPLETED SUCCESSFULLY"
I, then, execute the following
CREATE TABLE TEST.SalesPeople
(
SalesPersonId INT,
SalesPersonName VARCHAR(50)
)
GO
The message returned is
Msg 2760, Level 16, State 1, Line 1
Specified owner name 'TEST' either does not exist or you do not have permission to use it.
Can you give me the correct syntax on how to create the schema manually? Do you know why this is not showing up under security for the databases?
Thanks in advance for your help.
Tara
If the service is defined with multiple contracts is there a way to receive a message with a specific contract?
View 5 Replies View RelatedI've done a bit of work with the External Activator but I think it may be a bit overkill for what I need to do (which is RECEIVE messages from a single queue and process them with managed code). I've tried creating a Service Broker Interface service that retrieves messages from this queue, but I notice that if I set the timeout to -1 to watch for messages indefinitely, the Service never completes the OnStart code.
I notice if I change the service's timeout to something greater than 0, the message is retrieved, but this defeats the purpose of using a Windows Service app, which I want to continuously monitor the queue. I noticed the External Activator spawns a thread to start monitoring an EventNotification queue, which I can bypass since I want to monitor the notification's target queue.
Rushi, can you point me in the right direction to create a Windows Service that constantly monitors a queue? Also, I'd like the ability to monitor multiple databases (the queue name would be the same) as well, so if that is not feasible from a Windows Service please let me know.
Also, am I sacrificing scalability by NOT using the External Activator and switching to a Windows Service (I believe the External Activator will spawn multiple instances of the processing executable)?
Thanks,
Chris
Hello,
I am attempting to backup a database that is receiving log shipments and is presently in standby / readonly mode. What is the best way to do this?
Thanks in advance,
Bob