What considerations do I need to take into account if trying to enlist a server into a master server when (a) both instances are on different physical servers and the SQL Server and SQL Server Agent services run under different domain accounts?
The reason I ask is when attempting to create a master server through the SSMS wizard, I receive these errors:
Enlist TSX Progress
- Create MSXOperator (Success)
* Checking for an existing MSXOperator.
* Updating existing MSXOperator.
* Successfully updated MSXOperator.
- Make sure the Agent service for 'MYMSXINSTANCE' is running (Error)
Messages
* An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)
- Ensure the agent startup account for 'MYTSXINSTANCE' has rights to login as a target server (Error)
* Checking to see if the starup account for 'MYTSXINSTANCE' already exists.
Messages
* An exception occurred in SMO while trying to manage a service. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)) (mscorlib)
- Enlist 'MYTSXINSTANCE' into 'MYMSXINSTANCE' (Error)
* Enlisting target server 'MYTSXINSTANCE' with master server 'MYMSXINSTANCE'.
* Using new enlistment method.
Messages
* MSX enlist failed for JobServer 'MYTSXINSTANCE'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=MSX+enlist+JobServer&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'MYMSXINSTANCE'.) (Microsoft SQL Server, Error: 22026)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=22026&LinkId=20476
Now, the first two errors are what are puzzling me. I'm certain I can address the third and last error by making the accounts the SQL Server Agent services run as the necessary SQL permissions on each instance.
I've tried making NT AuthoritySYSTEM accounts on both servers, no luck. Any suggestions?
I've got a tool that accesses syslogins to pick up some information.When I run the tool, I get the error message that sasys that my logindoes not have sufficient permissions to read syslogins. If I run undermy admin ID, everything is fine. HOWEVER, the intention is that thistool will be used by non-DBA staff members so we have a generic idcreated to run this tool specifically (non-DBA account).OK, so I go to Enterprise Manager and open up the master database, goto syslogins and add my non-DBA id to the permissions list as havingSELECT access, and click on EXECUTE.BOOM! No sign of my permissions being set.Am I missing something? If so, what do I need to do to set permissionsagainst this view?
My company is new to MDS. I am trying to set an attritube in an entity to read only so the users can't change the value in that field. When I did that, the whole model disappeared. I thought I had deleted it by accident so I created a test model and tried to do the same. The test model disappeared. This time, before saving the new settings I took a snapshot. After saving I took another snapshot. You can see that the whole model is gone (zz_RN_Permissions_Test). I tried every other coworker with admin rights and nobody shows it on the Models list. The behavior on the Excel add-in is correct. I can't change any values on that column. But I need to keep the models available.
whenenver i run cold fusion, it automatically gets my tables out of the master database instead of the new one i have created. i've added a user that has a default to the new database instead of the master, but i need to know how to use that new user correctly, and what i will need to do once i start adding more databases so that cold fusion will go to the appropriate database and not to the master.
I need to connect to a SQL server thats running in say abc.trident.com and also sits in a shared environment..
I have couple of questions
1) That SQL server is accessible from my network, yet when I swtich on my enterprise manager I am unable to view that in the list of running SQL servers in the populated list.
Is it because its sitting in a shared environment I am unable to view that?
2) What is the connection striing I should use to connect to the server..When I try to configure a SQL server registrations it normally asks for SQL Server name along with the user authentication
Should I mention fill the Server: field as
abc.trident.com
or
// abc.trident.com/abc.trident.com
to connect to the clustered server.
Can some one tell me the proper connection string if both aforementioned ones are incorrect?
Step 1. First I am filtering the Sales Order information and inserting it in my Sales Orders table. Step 2.Then I am filtering the details from the sales Order and inserting them in the respective table.
My Problem is that the Sales Order File does not contain the Sales Order key (ID), this is generated by the SQL Server. How can get it in order to use it in the second step? I need it because it is a foreign key in the details table.
Hi Folks, I have a slight problem designing two drop down lists accessing my database as some clients are listed twice because they are located in multiple citys I have one table called Clients with the following columns Client City F_name L_name ID I am using SELECT DISTINCT Client FROM Clients for my first Dropdownlist with a postback etc. e.g. Brown Black The Second Dropdownlist must select the different Distinct City Values where they are located on selecting Dropdownlist one e.g. Brown > London, New York or Black > Singapore and Boston What SQL Code can i place so the second dropdownlist will select the correct values on selecting the first Dropdownlist.
I need some explanantion on the following query that I'm trying to run:
if exists (select * from sysobjects where id = object_id ('slice_trace') and sysstat & 0xf = 3) delete from slice_trace where control_seq_number = 130 and claim_number = 7912450
When I run this query within a database where the slice_trace table does NOT exist, it still seems to execute the delete statement and hence fails because the object is not there. Essesntially I need this statement to execute only in databases where the table exists.
I new it was a mistake changing from mysql to mssql! I'm having difficulty with the following and would really appreciate any help you could offer.
I have two tables.
The first [users] has personal details: [id] [business_name] [title] [surname] [firstname]
The second [orders] has their orders: [order_id] [user_id] [order_date] [order_price]
I need a query that returns the latest record from [orders] for each member, with member details, but only where the latest order was within (now)-425.
I have received a table of data that has a field containing dateinformation. Unfortunately it was derived from a MainFrame dump andoriginated as a txt file and was then ported into an Access MDB filebefore it became an SQL table. The date format is vchar(50) andactually is comprised of 6 charecters ie: 010104 for Jan 1 2004. Ineed to run a select statement for a range of dates such as 010104thru 030104. Unfortunately being a charecter field this returnsincorrect results under a majority of cases. Back in my dBase daysthere was a VAL() that could be used in this case but I have beenunable to find anything comperable in SQL. Can anyone help me please?Thanks in advanceSteve
Error: "A connection was successfully established with the server, but an error occurred during the pre-login handshake. (provider: SSL Provider, error:0 - The certificate chain was issues by an authority that is not trusted.) (Microsoft SQL Server)
I am running SQL Server 2005 Developer ed. Windows XP SP2 Trying to connect over the internet to a SQL Server 2005 Workgroup ed. SP1 on Windows Small Business Server 2003 SP1 I have had success doing this before. I can terminal sevice in to the box and confirm my credentials work "Force Enycrption" has not been enabled on either the server or the client The Certificate tab is clear under "Protocols for MSSQLServer", but the server does have certs I can see them in the "Certificate" dropdown.
I have been working for some time on this T-SQL statement and getting it to work the way I would like it to work. I have been reading through books and everything, but am still having trouble. I am pretty new to T-SQL so I am probably making some beginners mistakes.
Basically I am working on a SQL 2005 server and I have a single database with two tables. Below are the tables and the fields that I am using from each. The table name is in bold and the fields are plain.
Users username (PK, varchar(7), not null) full_name (varchar(50), not null)
call_history queue (varchar(6), not null) update_date (datetime, null) status (char(1), not null)
Ok, now that you have the gist of the tables and fields. What I am trying to do is the impossible I think. Basically I work in a call center that supports software. The queue field is the identifier of what specialist is handling any call within the table. It can be linked to the "username" on the Users table. One thing I just noticed is that the varchar has a difference. Will that affect my below select statement?
SELECT u.full_name AS 'Specialist Name', COUNT (*) FROM dbo.call_history c LEFT OUTER JOIN dbo.users u ON c.queue = u.username WHERE (c.status = 'P') AND (DATEDIFF(dayofyear, update_date, getdate()) >= 6) AND (DATEDIFF(dayofyear, update_date, getdate()) <= 9) AND queue IN('alatif', 'AWILLI', 'AYOUNG', 'BPRING', 'CSKINN', 'DALDEN', 'DBACCH', 'DGIZZI', 'DKUSSA', 'DMCCUE', 'EKEPFE', 'GBACKH', 'GJONES', 'HESTAL', 'JBANKS', 'JCRICH', 'JDELGA', 'JFOLCH', 'JGRAVE', 'JHARRI', 'JLI', 'JMYERS', 'JPOPPE', 'JRICHA', 'JRIMME', 'JTHOMP', 'JWELLS', 'KDUKHI', 'KSTANL', 'LCHAMP', 'LGABOR', 'LHARVE', 'LMONTG', 'LSHORT', 'LTOM', 'MBECK', 'MJONES', 'MVANDE', 'NBROWN','NTOMPK', 'PELLIS', 'RATTAR', 'RDODGE', 'TANDRO', 'TBROWN', 'TDAVIS', 'TNDREX', 'TNORRI', 'YSOSA', 'YWILLI') GROUP BY full_name ORDER BY full_name
So here is my dilemma: I am trying to emulate a spreadsheet that was given to me by my boss. This is pretty much going to determine whether or not I can get a job as the Web Developer. So the spread sheet displays the number of calls that have not been updated in X amount of days for all of our reps. I have been trying so many different varieties from nested SELECT statements now to joins. To put it simply enough, do I need to do a query that populates column by column?
Here is what it should look like: http://i135.photobucket.com/albums/q129/tico1177/CropperCapture1.jpg
As you can see, I have to include everyone even if they have zero. That is where the problem comes in. When I use the above statement, the list shows up but takes away people from the list instead of keeping them and placing a null for the count. I have tried placing a HAVING statement at the bottom of the query to compensate for COUNT(*) = 0, but I get an error. I think because I have a WHERE statement.
I am trying to see if this whole thing can be done with a SQL statement to avoid having loop though in code. I basically want to populate a datagridview with the information that I gather.
I'm looking for a way to list the target servers associated with a master server. The reason is that we're moving to another master server, and I'd prefer not to move the targets manually.
I've got most of the T-SQL already (sp_msx_enlist, sp_add_jobserver), but I'd like a scripted solution instead of a wizard.
Sql Server 7 Standard EditionMy Server was renamed and the normal steps of reinstallingand running sp_dropserver and sp_addserver were followed.Everything seems to work fine but jobs from the master server.I did defect the server as a target server since it appeared as offlineanyway.On trying to re establish the status of the server as a target serverFailed to make "serverName" a target serverEnlist into Master Server "MasterServerName" Failed(Error 14262)Your insight is appreciatedVincento
I am trying to build a related article display. I have a SQLDataSource that I want to be able to select information (Category) from the table (Articles) based on a querystring (ID). SELECT [Category] FROM [Articles] WHERE ([ArticleID] = @ID) which for an example, lets say ID = 1, and it results as Category = Health.That is easy enough, but how would I then select all columns from the table WHERE Category = Result of first SELECT? SELECT * FROM [Articles] WHERE ([Category] = ??? Result of prior select) Can this be done in 1 select command, or would a store procedure need to be written? I am a little lost, sincr I am using a SQLDataSource, and it will be displayed with a Repeater. Thanks!
I'm using SQL 2000 and would like to send a generated email using this stored procedure: select Libraryrequest.LoanRequestID, Titles.Title, requestors.fname+ ' ' + requestors.lname as [Name], libraryrequest.requestdate,libraryrequest.shipdate,libraryrequest.duedatefrom libraryrequestjoin requestors on requestors.requestorid=libraryrequest.requestoridjoin Titles on Titles.Titleid = Libraryrequest.titleidwhere duedate < DATEADD(day, DATEDIFF(day, '20010101', CURRENT_TIMESTAMP), '20010101') I know I need to go to Management, SQL Server Agent, Jobs, New Job. Do I put the stored procedure in the descriptions part? After that I'm lost what do I do.
I need to write a query to provide MaxDate of each Exam and still show the ID of that Exam. My table is ID Exam Date 1 FMS 1/1/2006 2 FMS 1/1/2007 3 FMS 1/1/2008* 4 ECS 1/1/2006 5 ECS 1/1/2007 6 ECS 1/1/2008* My attempted query isSELECT ID, Exam, Max(Date) AS MaxOfDateFROM Table1 GROUP BY ID, Exam; My query actual results ID Exam Date 1 FMS 1/1/2006 2 FMS 1/1/2007 3 FMS 1/1/2008* 4 ECS 1/1/2006 5 ECS 1/1/2007 6 ECS 1/1/2008* My desired results ID Exam Date 3 ECS 1/1/2007 6 ECS 1/1/2007
I would appreciate any help that could be provided.
First off, here is my query:SELECT DeviationDist.DEVDN, DeviationDist.DEVDD, DEVDA, DEVCT, DEVST, DEVBG, DEVDV, DEVPS, DEVAT, DEVCN, DEVCF, DEVCP, DEVCEFROM DeviationDistINNER JOIN DeviationContact ON DeviationContact.DEVDN = DeviationDist.DEVDNWHERE DeviationDist.DEVDN = '200270'ORDER BY Deviationdist.DEVDN DESCI'm joining the deviationcontact table to the deviation dist table by DEVDN. This query works fine except when the DeviationContact table doesnt contain any records for the DEVDN that the DeviationDist table does contain. In my app, Deviationdist will always have an record for each DEVDN, but DeviationContact may not. I would like to still get the results back for what records exist in the DeviationDist table, even if DeviationContact has no associated records, but still show them if it does...
-- I want all sites even if they are not in the syslog table.
-- select count(gssites.sname) from gssites -- where gssites.rectype = 'S' = 67
-- this query returns 13 rows, I want all 67
declare @start int, @end int set @start = 1 set @end = 0 selectgssites.sname'SyncSite' ,syslog.resultcode'RES' ,rtrim(convert(varchar(2),datepart(month,SYSLOG.ON DATE)) +'-'+convert(varchar(2),datepart(day,SYSLOG.ONDATE )) +'-'+convert(varchar(4),datepart(year,SYSLOG.ONDATE)) )'SyncDate'
from Syslog left outer join gssites on gssites.sname = substring(syslog.siteid,9,8)
wheregssites.rectype = 'S' AND (SYSLOG.RECTYPE='G') AND (SYSLOG.ONDATE Between (GetDate()-@start) And GetDate()-@end) AND (SYSLOG.SITEID<>'') AND (SYSLOG.RESULTCODE='SUC')
I have a need to renumber or resequence the line numbers for each unique claim number. For background, one claim number many contain many line numbers. For each claim number, I need the sequence number to begin at 1 and then increment, until a new claim number is reached, at which point the sequence number goes back to 1. Here's an example of what I want the results to look like:
Hopefully this will be painless for you guys/gals - however due to my lack of skills/knowledge I need some clarification.
I have table_X which I have a trigger on INSERT setup. This trigger updates Field_2 = '1' and inserts some rows in another table.
Is there some way that I can restrict this trigger to only run when Field_1 = "BLAH" So essentially I am trying to find out how I can pull information/data from the record that fired the trigger and use this in the trigger? (ie to check if Field_1 = "BLAH" and to use Field_3 to further restrict the underlying triggers' updates and inserts)
Hopefully I have given enough information on this one - if not please let me know any points that I should need to clarify.
I realize this query is inherently incorrect, but my issue is mainly syntax. The line, "WHEN a.order_id <> b.order_id THEN" is wrong. I want to ensure that a.order_id is not in the settlement table. So I was thinking something along the lines of "WHEN a.order_id not in (select order_id from settlement)" which I know will cause a slower response time, but I'm willing to deal with it. In any case, that syntax doesn't appear to work.
sum( CASE WHEN a.ready_to_pay_flag = 'Y' and a.deduction_type = 'E' and ( CASE WHEN a.order_id <> b.order_id THEN a.transaction_date ELSE b.delivery_date END) used_date datediff(d,used_date, ".$cutOffDate.") < 30) THEN a.amount END) earn_amount_rtp_curr,
I have a trigger that I created to log changes in one table to anothertable but it is horribly inefficient.I am hoping that someone with more experience than I can see a way tomake this trigger more efficient.------------ALTER TRIGGER tContacts_ChangeLogON dbo.ContactsFOR UPDATEASSET NOCOUNT ONDECLARE @tablename varchar(20),@record_id_column varchar(30),@colname varchar(30),@colvalue varchar(8000),@insertstmt varchar(1500),@username varchar(20)SELECT @tablename = 'Contacts'SELECT @record_id_column = 'ContactID'DECLARE columns_cursor CURSOR LOCAL FORSELECT COLUMN_NAMEFROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_NAME = @tablenameAND (POWER(2, (ORDINAL_POSITION-1) % 8) & CONVERT(INT,SUBSTRING(COLUMNS_UPDATED(), (ORDINAL_POSITION-1)/8 + 1, 1))) <> 0SELECT * INTO #del FROM deletedSELECT * INTO #ins FROM insertedSELECT @username = RIGHT(SYSTEM_USER, LEN(SYSTEM_USER) -CHARINDEX('',SYSTEM_USER))OPEN columns_cursorFETCH NEXT FROM columns_cursor INTO @colnameWHILE @@FETCH_STATUS = 0BEGINSELECT @insertstmt = 'INSERT INTO ' + @tablename + '_ChangeLog (recordid, fieldname, changedfrom, changedto, username, datetime ) ' +'SELECT d.' + @record_id_column + ', ''' + @colname + ''', d.' +@colname + ', i.' + @colname + ', ''' + @username + ''', GETDATE()' +'FROM #del d INNER JOIN #ins i ON d.' + @record_id_column + ' = i.' +@record_id_column + ' WHERE (i.' + @colname + ' <> d.' + @colname + ')'+' OR (i.' + @colname + ' IS NOT NULL AND ' + 'd.' + @colname + ' ISNULL) OR (i.' + @colname + ' IS NULL AND ' + 'd.' + @colname + ' IS NOTNULL)'-- INSERT INTO Debug (value) VALUES( @insertstmt )EXEC( @insertstmt )FETCH NEXT FROM columns_cursor INTO @colnameENDCLOSE columns_cursorDEALLOCATE columns_cursor
I am trying to pull some "notes" from a sql database.....the notes thatare put into the database come via the web and the user is entering itfor a certain task. they are stored in their own table and field andget assigned and incremental ID #.I want to be able to pull up the latest entry to the task, not all ofthe notes just the latest one.. The entry does get a timestamp in thefield so I am thinking I might be able to look at that fieldsomehow.... Right now my query shows all notes / entries for the task.I am an intermediate sql query guy so I hopefully expained enough toget assistance.Let me know if you need to know more.
I have a SQLAgent job created using isqlw to run the query and output the results to a file on the C drive. However, the job never completes, which is strange since the database is fairly new and when I run the query manually in a isqlw window, it completes and outputs the file in seconds.
I am trying to get a running total. I need the query to reset the running total for each year/id. Below is the sample query. Any help would be greatly appreciated.
SELECT v.k1, v.date, v.groupByThis, v.c1, RunningTotal=SUM(a.c1) FROM ( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank FROM #valueset ) v
CROSS JOIN
( SELECT k1, date, groupByThis, c1, RANK() OVER (PARTITION BY k1 ORDER BY groupbythis, date) as Rank FROM #valueset ) a WHERE a.Rank <= v.Rank AND a.groupByThis = v.groupByThis GROUP BY v.k1, v.date, v.groupByThis, v.c1 ORDER BY v.groupByThis, v.date
I am relatively new to the use of coplex queries. Here is a task that I am trying to accomplish.
Source table.
Address ID Workstation
Test-a 1 WS1
Test-b 2 WS1
Test-a 5 WS2
Test-d 3 WS2
Test-b 7 WS2
I am trying to write a query that will display this result into Excel.
Address
Duplicate
WS1
WS2
Test-a
Yes
1
5
Test-b
Yes
2
7
Test-d
No
Basically I am trying to identify if there is a duplicate address, if so mark it as such in the duplicate column and then placing the ID into a column under the Workstation. I only want to see the duplicated address once (Distinct?) but mark that it is indeed a duplicate and mark the ID's that it has under the workstations.
Any ideas? I have created a query that does pull the data in the first example that is doing a DTS export to excel. However I need to format this to show the second example.
I am just starting to use SQL server, and I currently make use of a DB set up by my ISP (brinkster). I have been making use of the DB via management studio express. I now want to start using Integration services, but it does not look like that is available with my local copy of visual studio. I am a bit confused on what tool I would use to make use of SSIS when my SQL server db is provided by my ISP and I only have express editions of the SQL tools on my local compter.
Ultimately, I am trying to take a flat file I receive everyday and extract data into a structtured format, inserting it into my SQL server DB provided by my ISP.
I know this may be very newbie, but I wasn't really seeing this in the FAQ. Any help is greatly appreciated! I am hoping there is a solution that wouldn't cost me more than $100!
I have two tables, USER and ROTATION. What I would like to display is the Maximum Start_Date and Maximum End_Date from the ROTATION Table along with the First_Name and Last_Name that is associated with that max entry.
When I just create a select statement that asks for the max value of the Start_Date and End_Date, I get the value I see. However when I try to add the First_Name and Last_Name to the mix, I get the Max Start and End Date Values of all the people in the User Table. I only want one result returned. I'm probably missing something very simple here. Any assistance would be appreciated.
The SQL code I am currently using:
Code SnippetSELECT [USER].FIRST_NAME, [USER].LAST_NAME, MAX(ROTATION.ONCALL_START_DATE) AS Expr1, MAX(ROTATION.ONCALL_END_DATE) AS Expr2 FROM ([USER] INNER JOIN ROTATION ON [USER].USER_ID = ROTATION.USER_ID) GROUP BY [USER].FIRST_NAME, [USER].LAST_NAME
Example of my desired result:
First_Name Last_Name OnCall_Start_Date OnCall_End_Date John Doe 8/10/08 8/17/08
John Doe should be the only result returned because he would have the highest OnCall_Start and OnCall_End in the ROTATION Table.
In my ASP.NET app, I'm executing a stored procedure via a SQLCommand the searches a customer database. I believe the default timeout is 90 seconds. I'm curious of what happens to the SQL Server Stored Procedure after timing out from the ASP.NET application. Does it timeout at the same time or do you have to set up a value in SQL Server?
I have a Sybase Adaptive Server Enterprise server which I need to set up as a linked server in SQL Server 2005. The Sybase server is version 12.5.2, and the Sybase ODBC driver version is 4.20.00.67. I have already installed the Sybase client software on the server.
I also created a SystemDSN on the SQL Server to connect to the Sybase server. I tested the connection and it was able to connect.
I ran the following code to create the linked server:
I then ran sp_tables_ex to make sure I could view the tables in the Sybase database. Here is the error message I get:
<code>
OLE DB provider "MSDASQL" for linked server "LinkedServerName" returned message "[DataDirect][ODBC Sybase Wire Protocol driver]Error parsing connect string at offset 13. ".
Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "LinkedServerName".
