Convert Multiple Accounts Into A Range

Mar 6, 2006

Hi,

I'm trying to make the select query attached neater by removing the list of accounts and simply providing a range. For the life of me, I can't seem to figure out how to do it. So hopefully one of you can help me out with this.

View 7 Replies


ADVERTISEMENT

Do Managed Local Accounts Remove Need For Multiple Domain Accounts

Aug 12, 2015

I cannot get a consistent answer as to how many domain accounts would be suggested in a SQL Server 2014 installation. Previously the recommendation was a separate account for each service to provide isolation and minimum permissions for each account. It seems from what I've read that a single domain account would have something added to make it unique from SQL Server's perspective. Several still advocate multiple accounts. I don't know if they are doing so because that's the way it's always been done or if there is still some compelling reason to do so. I don't want to create unnecessary accounts simply because something is "ideal."

View 8 Replies View Related

Convert SQL Account To Windows Accounts + EXEC On ALL SPs

Apr 8, 2007

I am no DBA, but this is my task.I have an SQL Server 2000 Database that has an "SQL Account" that hasexecute permission on all Stored procedures. it is what was used bythe company. This one account is used by "all workstations".I want to fix this and use Windows Accounts, and get rid of that SQLAccount. How do I go about adding that Windows Account permission toall the Stored Procedures?What I want to do is to just add several windows account then go aboutremoving the permission where necessary on an account by accountbasis.Any suggestions would be greatly appreciated!

View 2 Replies View Related

Multiple Accounts

Jun 18, 2008

The following error keeps being reported in the Domain Controller Logs:

"There are multiple accounts with name MSSQLSvc/....."

View 1 Replies View Related

How To Send Notification To Multiple Email Accounts?

Sep 26, 2007


I am in process of setting up job failure notification and one of the requirements that I have is to send notification to multiple users. I would appreciate any suggestion. One of the possible ways would be to create some sort of group account on exchange server with all users added as members. Is there anyway though where I could use users' individual accounts and based on that forward notification to them?

Thanks

View 5 Replies View Related

Convert Month Name, Year Range To Mm/dd/yy

Jul 23, 2005

Using SQL 2000. I need to select rows based on a date range. Both thebeginning date and ending date of the range will be entered in themm/dd/yy format. There are 2 columns in the table called MonthOfEvaland YearOfEval, both varchar(9) and both with data like "August" and"2005-2006". I think I need to use these 2 columns to create a date inthe mm/dd/yy format, then use that newly created date to see if it'swithin the date range. How would I go about doing this or is there abetter way?Thanks for any help or advice.

View 1 Replies View Related

Default NT Accounts Even If We Have Proper Service Accounts In Server?

Jul 23, 2015

Do we still need the below service accounts in SQL 2008+ version even if we have proper SQL service accounts added in the logins?

[NT AUTHORITYSYSTEM]
[NT ServiceMSSQLSERVER]
[NT SERVICEReportServer]
[NT SERVICESQLSERVERAGENT]
[NT SERVICESQLWriter]
[NT SERVICEWinmgmt]

View 0 Replies View Related

Convert Date Range Into Week Days

Nov 3, 2006

Hi,

I don't know if this can be done, but hopefully one of the experts in this forum cna help me out.

I have a TimeOff table which contains 5 fields: timeoffID, employeename, startdate, enddate, and timofftype. I need to create a report which shows how many people take sick day (one of the timeofftypes) on each week day (i.e. Monday, Tuesday, Wednesday, and etc.) The problem is that I don't know how to convert the date range (from startdate to enddate) into indvidual week day for each timeoff record.

For example, on the table,

TimeoffID employeename startdate enddate timeofftype
1 Andy 11/02/2006 11/03/2006 Sick Day
2 Bill 11/03/2006 11/03/2006 Sick Day

The report needs to show:

Monday Tuesday Wednesday Thursday Friday Saturday Sunday
0 0 0 1 2 0 0


Thank you in advance!

View 4 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

I have a table with single row like below

 _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0    | Value1    | Value2    | Value3    |  Value4  |

Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below

_ _ _ _ _ _ _ _
Column0 | Value0
 _ _ _ _ _ _ _ _
Column1 | Value1
 _ _ _ _ _ _ _ _
Column2 | Value2
 _ _ _ _ _ _ _ _
Column3 | Value3
 _ _ _ _ _ _ _ _
Column4 | Value4
 _ _ _ _ _ _ _ _

View 6 Replies View Related

Help On Multiple Date Range On Sql Statement

Jul 20, 2005

Using SQLServer ver 7.0, two tables:TableA = contains all inventory dataTableB = contains four fields: ID, source, date_from, date_toThis is where multiple range of dates are populated.Sample 1:1,'A','9/1/2004','9/30/2004'Sample 2:2,'A','1/1/2003','3/31/2003'3,'A','10/1/2004','10/31/2004'Data populated on TableB varies.Sample SQL for Sample 1:SELECT *FROM TableAWHERE inventory_date BETWEEN (select DATE_FROM from TableB) AND (selectDATE_TO from TableB)Problem: How to approach sql statement based on Sample 2 above?

View 3 Replies View Related

Search By Multiple Date Range Options

Nov 1, 2007

I inherited a system and am moving our SQL2000 input data into a new 2005 database for web searching by clients. The conversion went pretty well, but I'm looking for suggestions on how best to index and query the data.



Details: We have almost 500,000 records in a pretty wide history table, and will be adding more records daily. The original table design has numerous sets of dates(first/last of different types), some small fields, and one large text field that is used for full-text catalog. The input dates are all stored as datetimes set to midnight by an insert trigger.


...

FirstPub as datetime

LastPub as datetime

FirstSale as datetime

LastSale as datetime

Name as varchar(50)

Addr as varchar(50)

DetailText as text....



I need to create a new asp.net page that can accept any specific date, or a range of dates (first/last publish date or range, first/last sale date or range) along with any of the optional 10 text fields. All input is optional, and any combination of fields can be entered. Result set has to include the primary key, key dates, and the large text field.


I've read many posts and articles, but find many different opinions about what works best. I found a similar solution that used a series of case statements with text operators indicating if the date was input or not. Other guys say no - just pass the dates as null without any additional control fields.



I found solutions saying it's best to pass dates as strings, and others that say to use datetime everywhere and just format the presentation. Some say convert dates to formatted dates and then compare them. Others say that causes table scans and should be avoided.



Questions:

1) What is the best way to get a compare dates that are entered through an asp.net page and stored procedures?

2) How to query either a specific date, as well as a range between the first and last date specified? Can it be done efficiently without needing additional screen fields for the range specification? If tried checking nulls, but am having trouble selecting multiple sets of date ranges and/or specific dates with one proc. There is always the old method of storing the selected keys in temp table, and then returning the distinct rows. Most of my experience has been in SQL 7.0 and some 2000, so I'm wondering if there are new ways to tackle the problem.

3) Better to create an index for each date type that might be selected, or one index that has all the main dates in one place?

4) Should we split the large text field to another table in our search database? It would seem to make sense to split them as we add records from the input system. Two tables linked with the same primary key seems like a good improvement from original table design. How would that affect the performance of returning all selected rows and including the large text field?

View 3 Replies View Related

Reporting Services :: Multiple Data Per Row On Range Bar Chart

Jul 23, 2015

I'm using Reporting Services 2008 R2, version 10.50.2550.0, and Visual Studio 2008, version 9.0.30729.4462 QFE.  Not sure if the SQL version matters, since I haven't gotten the report out of the development environment.I've read lots of messages, including URL.... that show you can have multiple data entries per row in a Range Bar Chart, simply by turning DrawSideBySide to False.My data set basically contains four pieces of data, LineNumber, Equipment, StartTime and EndTime.  I have data like:

1, 'CLM1', 08:00, 08:30
1, 'CLM1', 09:00, 09:15
2, 'SS1', 08:05, 08:35
2, 'SS1', 09:05, 09:35

(I actually have a date with my datetime entries, but that should give you the idea.)  My chart is set up with Values of EndTime and StartTime.  If I set my chart up with only Equipment in the Category Groups entry, my chart will show CLM1 from 08:00-08:30, and SS1 from 08:05 to 08:35.  No sign of the other entries per equipment, although I added a tablix to retrieve the same data and it all shows up.Changing DrawSideBySide between True, False and Auto has no effect on the data displayed on my chart.  If I add StartTime in the Category Groups, I do show all the data, but one row for each piece of data, which isn't what the customer wants.From what I can tell from various sites, I'm set up right, but it just isn't working.

View 2 Replies View Related

Convert Single MDF To Multiple MDF + Ndf + Ndf

Feb 12, 2008



I have a 100GB db that is all in a single MDF file.

I would like to spread this across a few (or several) files, with the data distributed evenly (or close to it).

In other words, I would like to have, say 10 files in [PRIMARY] each with 10GB.

Any thoughts on how to make this happen?

View 3 Replies View Related

Hw To Convert Row Data Into Multiple Columns??

Apr 4, 2008

Hello,

Table - 1 Format:-

Line_No Entity_Id Mid
1157530
2157540
3157550
4157560
5157570
6157580
7157590
81575100

Now i need the data into below Format :-

Line_NoEntity_Id Ticker Mid1Mid2 Mid3 Mid4 Mid5 Mid6 Mid7 Mid8
11575 COFP 30 40 50 60 70 80 90 100

Can any one help me out?

Thanks
Prashant Hirani

View 1 Replies View Related

Convert Access With Multiple Columns?

Nov 14, 2014

I am converting some access to SQL at work and I am having a problem with a query that uses the access FIRST function

My query looks like this in access

SELECT
TBL1.ID
, FIRST(TBL1.Code) AS FirstCode
FROM
TBL1
WHERE
TBL1.Marker = 'X'
AND Left(ID,1) IN('A','B','C','D','E')
GROUP BY
ID

I have tried to convert it using a slightly different method. So instead of picking the first value of the Code field that appears in the select, (which is what access would do and SQL can’t do as far as I’m aware). I first select the ID and code with the highest value associated with them. The below script is what I did. I would’ve thought they would produce the same number of records but they don’t.

SELECT
ID
, Code FirstCode
FROM
(
SELECT
ID
, Code
, MAX(Value) MaxValue
FROM
TBL1
WHERE
TBL1.Marker = 'X'
AND LEFT(ID,1) IN('A','B','C','D','E')
GROUP BY
ID
, Code
) T1

View 3 Replies View Related

Query Info Between Time Range & Date Range

Aug 16, 2006

I am attempting to write a SQL query that retrieves info processed between two times (ie. 2:00 pm to 6:00 pm) during a date range (ie. 8/1/06 to 8/14/06)... I am new to SQL and am perplexed... I have referenced several texts, but have not found a solution. Even being pointed in the right direction would be greatly appreciated!!

View 6 Replies View Related

T-SQL (SS2K8) :: Convert Multiple Rows And Columns Into Single Row

Apr 25, 2014

I have this query

SELECT
'Type'[Type]
,CASE WHEN code='09' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='10' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='11' THEN SUM(Amt/100) ELSE 0 END
,CASE WHEN code='12' THEN SUM(Amt/100) ELSE 0 END
FROM Table1 WHERE (Code BETWEEN '09' AND '12')
GROUP BY Code

and the output

Column 1 Column 2 Column 3 Column 4
Type 14022731.60 0.00 0.00 0.00
Type 0.00 4749072.19 0.00 0.00
Type 0.00 0.00 149214.04 0.00
Type 0.00 0.00 0.00 792210.10

How can I modify the query to come up with output below,

Column 1 Column 2 Column 3 Column 4
Type 14022731.60 4749072.19 149214.04 792210.10

View 9 Replies View Related

T-SQL (SS2K8) :: Convert Multiple Rows In Single Column

Sep 4, 2014

Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.

Below is sample of data:

create table #Temp(depID int, Name varchar(50),LocationHead varchar(50))
insert into #temp values(1,'test','head1')
insert into #temp values(1,'test','head2')
insert into #temp values(1,'test','head3')
insert into #temp values(2,'test1','head1')
insert into #temp values(2,'test1','head2')

Required output

depID Name LocationHead
1test *head1,*head2,*head3
2test1 *head1,*head2

View 2 Replies View Related

SQL Server 2012 :: Convert Multiple Rows To One Row Separated By Delimiter?

Jun 2, 2014

I am running sqlserver 2012.

My output data is like

COL1
aaa
bbb
ccc

Now, i want to convert the output to

aaa;bbb;ccc

How can i do this?

View 2 Replies View Related

Transact SQL :: Convert Multiple Lines With Different Values In Value Columns In 1 Line

Dec 1, 2015

I have this query:

SELECT
          ID1,
          ID2,
          type,
          (case when type = '1' then sum(value) else '0' end) as Value1,
          (case when type = '3' then sum(value) else '0' end) as Value2,
          (case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3
FROM table1 WHERE ID1 = 'x' and ID2= 'y' 
GROUP BY ID1, ID2, Type

Which returns:
ID1     ID2        Type     Value1     Value2     Value3
005    11547    0          0.00         0.00        279.23
005    11547    1          15.23       0.00        0.00
005    11547    3          0.00         245.50    0.00

And I want to obtain this result:
ID1     ID2        Value1     Value2     Value3
005    11547     15.23       245.50    279.23

View 5 Replies View Related

Integration Services :: SSIS Script Task To Convert XLSX Files With Multiple Sheets To CSV File

Dec 11, 2014

I have been strunggling to find solution to convert XLSX files with multiple sheets to csv file.

Requirements
>> Convert XLSX file with multiple sheets to CSV file
>> CSV file names : XLSX filename + '_' + sheet name
>> scirpt has to be in VB as i am using ssis 2005
>> I started develping scirpt using Micorosoft.office.interop.Excel.dll . this dll is referenced to script task.
>> found web link as useful.. [URL] ....

View 4 Replies View Related

Select Rows Where Value In A Range And Below A Range?

Nov 26, 2014

I'm running a pre-defined script which used to work fine on a file which was resupplied every month. below is the script used and the error message. looking at the error I assume that there is a rouge record within the file but have looked in Textpad and cannot find it.

UPDATE [matching].[dbo].[hot_nov] SET [AOV] = (CAST([Demand] AS DECIMAL)/CAST([Orders] AS INT)) WHERE [Demand] <> '';

UPDATE [matching].[dbo].[hot_nov] SET [POST2] = left([PostCode], PATINDEX('%[0-9]%', [PostCode] + '1') - 1) ;
UPDATE [matching].[dbo].[hot_nov] SET [POST4] = left([PostCode],LEN([PostCode])-4);
UPDATE [matching].[dbo].[hot_nov] SET [POST6] = left([PostCode],LEN([PostCode])-2);

error message

(1000 row(s) affected)
Msg 245, Level 16, State 1, Line 181
Conversion failed when converting the varchar value '2014-09-03 00:00:00' to data type int.

View 5 Replies View Related

SQL Server 2008 :: Query To Select Date Range From Two Tables With Same Date Range

Apr 6, 2015

I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

The table B stores the item process time. The structure is as below

Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000

I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.

Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.

The result expected is

Table A

Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000

Scenario 2 expected result

Table A

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000

View 8 Replies View Related

What Does [dbo].[Accounts] Mean?

Jul 23, 2005

What does the "[dbo]." mean in the following sql script stmts?use [IBuyAdventure]GOif exists (select * from dbo.sysobjects whereid = object_id(N'[dbo].[Accounts]')and OBJECTPROPERTY(id,N'IsUserTable') = 1)drop table [dbo].[Accounts]GOand if you please, what does the "N" in N'IsUserTable' mean?thanks,-Steve

View 2 Replies View Related

SELECT Where Value In A Range And Below A Range

Jul 29, 2013

I have a need to select rows where values in a column are within a range AND ALSO the first row that is highest (MAX) but just UNDER the range. For example:

NAME VAL
Jim 4
John 5
Tom 6
Julie 7
Rich 8
Rob 9

RANGE TO SELECT is 6-8 (inclusive)

Rows with a value in the range are returned (6, 7, & 8) as well as the row with a highest value that is JUST BELOW the range (5)

NAME VAL
John 5
Tom 6
Julie 7
Rich 8

View 2 Replies View Related

Service Accounts

Aug 2, 2000

Can anyone tell me the purpose to using service accounts in SQL Server rather than just having the services start as a system account.

Thanks

John Shurer
john.shurer@gte.net

View 2 Replies View Related

Returning Top 20 Accounts?

Mar 1, 2001

Hi,

How can i code a SQL statement that will return the top 20 accounts from a huge client table?

Thanks

View 1 Replies View Related

Accounts To Use For Replication

Jan 26, 2012

I am setting up Replication and have a question about what's considered best practice for the accounts that will be running the replication agents. Microsoft says, "Run each replication agent under a different Windows account, and use Windows Authentication for all replication agent connections." What they don't say is whether these accounts are local accounts or domain accounts.

Which should I use/create, domain accounts or local accounts?

View 1 Replies View Related

SQL Accounts In Mirror

Jan 26, 2007

Im pretty new to DBA world

We have a SQL2005 Standard setup with mirror and witness

I create a Database in the Principle, create a SQLLogon account and give it permission to the database. All works.

I then fail the databse over to SQL2 and the database is there, it has the SQLAccount I create at the database level, but a logon does not work. I notice there is not login account at the database level and If I attempt to create one, I am told there is one already. I try to assign permission to that account for the database and it again replys that there is already on.



Is this refered to as an orphaned logon?

I was a post on Moving logins from on server to another, is that what I must do?

THank you





View 7 Replies View Related

Creating Login Accounts

Sep 15, 2000

When creating a login account, it is associated with a default database.

Is it then necessary to grantdbaccess to the default database?

View 1 Replies View Related

Sql Mail Service Accounts

Jun 7, 2005

I just had a question,

Is it possible to have a different account for the accoutn that starts the MSSQLServer service and the account tied to the Mail profile on the server?

We had created an account to start the SQLServer but we are in a network where we have a 1 way trust with another domain, we trust them but they dont trust us, and our exchange is on their domain.

WE currently use Windows authentication so our account used to start SQL Server would not be trusted by exchange.

Our thoughts on a solution were to have them create a service account that we would have access to the mailbox and would also start the SQL Server but thats it.

I was just wondering if anyone else had any other suggestions.

Thanks.

View 1 Replies View Related

Creating User Accounts

Jan 17, 2005

Hi,
how do you create a username and password for a database in SQL.

Thanks

View 3 Replies View Related

How To Get Service Accounts For 150 Servers

Aug 18, 2006

Hi Everyone. I have 150 SQL servers (2000 MSDE). They all run using various domain accounts as their service logins. Is there an automated way to find out those service logins? Maybe a query I could run on each server? I really do not want to go to each of those 150 servers and look at their properties manualy! :S Any help would be greatly appreciated! Thank you.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved