SQL 2012 :: Automate DB Users To Get Data For Every Month On All Servers
Aug 13, 2015I need to automate the db users to get the data for every month on all servers ....
View 9 Replies
ADVERTISEMENT
SQL Server 2012 :: Query To Get The Month Wise Data?
Sep 13, 2015I have the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201406 xxxxxx 27 1
In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.
Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201403 xxxxxx 28 0
201404 xxxxxx 28 0
201405 xxxxxx 28 0
201406 xxxxxx 27 1
SQL Server 2012 :: Comparing Data For A Certain Day Of Week By Month For Every Year
Oct 21, 2014I need to build a report that compares a count on a certain day of the week by month by year by stacks. That is,for first Monday in October 2012 against first Monday in October 2013 for stack DM1 against first Monday in October 2014 stack DM1, same for second Monday, first Tuesday, second Tuesday, ect. Attached is a sample dataset and what I want to achieve.
View 9 Replies View RelatedSQL 2012 :: Grant Access To Sensitive Data To Users
May 12, 2015I have a large table with email column. I need to grant select access to this email column to many users but the actual email should not be visible to those users. I thought of following options:
1.Create an indexed view with that column encrypted and then grant access to users.While searching by email, their search text will also be encrypted and then matched with view column.Problem with this is that I have to create indexed view because most searches will be on Email column and table size is pretty big,20 m records. I don't prefer indexed view in general.
2. Add another column to table with encrypted email and grant access to that column to users. Problem with this is that it will increase table size and i'll need one more index.
SQL Server 2012 :: Select Query - Get Result As Month And Values For All Months Whether Or Not Data Exists
Jul 27, 2015I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.
The Similar table would be-
create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)
But in result i want the table as -
Month Value
Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull
Auditing Users On Various SQL Servers
Jul 19, 2007We have about 50 SQL Servers (most 2000 but some 2005) in different domains and behind firewalls and we implementing the PCI rules for the use of credit card data. One thing we need to do is to audit users and their rights. I have a stored proc that can run each night and record users and permissions.
My main problem is trying to bring all that data together on a central box and then parse through it to see if I have any offenders. The parsing is not the issue, but the polling is. I could use the SA account or create a audit account with SA rights but both of those solutions go against the PCI mantra.
Can someone who has done this give me some guidance as to what you did. I know that there are some third party tools that would probably do this, but my department is on a shallow budget and my overtime is free.
Any suggestions would be greatly appreciated. Thank you!
Jim Youmans
St. Louis
Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?
Nov 9, 2015I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.
I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount
My Desired output
My current query
SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]
[Code] ....
Transact SQL :: Displaying Sales Data In A Month By Month Grid
Aug 11, 2015Most of the data is in one table.Â
Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264
I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.
 SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January',Â
Sp_Login: Transferring Users/SIDs Between Servers
Jan 2, 2001My question is, why did I have to use the allegedly optional named argument @DefLanguage when using sp_AddLogin? I had often tried to use the @SID parameter of sp_AddLogin to synchronize users betwixt our servers. Previous efforts had failed.
Per Transact-SQL Help, here’s the syntax for AddLogin:
sp_addlogin [@loginame =] 'login'
[,[@passwd =] 'password']
[,[@defdb =] 'database']
[,[@deflanguage =] 'language']
[,[@sid =] 'sid']
[,[@encryptopt =] 'encryption_option']
We have over a dozen User DBs, and I’d written a cursor to add the login and then GRANT access to the appropriate DBs. I used the following variables:
DECLARE @Loginame VarChar(25) , @Passwd VarChar(25) , @DefDB VarChar(50)
DECLARE @SID VarBinary(16)
Then I SET the variables:
SET @Loginame = 'MyUserName'
SET @Passwd = 'UsersPwd'
SET @DefDB = 'UsersDB'
SET @SID = 0x6370170BDAAFF640AD5CEB586EA87C2C
-- UserSID for MyUserName from sister server
Then I EXECuted the proc.
EXEC sp_AddLogin @Loginame , @Passwd , @DefDB , @SID
This generated the following error (instead of ‘?’, I saw squares, as in unprintable characters):
Server: Msg 15033, Level 16, State 1, Line 0
'????????' is not a valid official language name.
I didn’t understand this at all at first, but out of desparation, I DECLAREd & SET the @DefLanguage argument...And it worked! That is:
DECLARE @Loginame VarChar(25) , @Passwd VarChar(25) , @DefDB VarChar(50)
DECLARE @SID VarBinary(16)
DECLARE @DefLanguage SysName
Then added:
SET @DefLanguage = NULL
-- per sp_Configure, RunValue = 0
-- Master..SysLanguages reveals that language ID = 0 corresponds to ‘us_english’
Then I EXECuted:
EXEC sp_AddLogin @Loginame , @Passwd , @DefDB , @DefLanguage , @SID
Success!
Again, my question is, why did I have to use the allegedly optional named argument @DefLanguage?
Moving Users And Permissions Between Servers--urgent
Nov 15, 2000Is there any easy way to move users between SQL Servers for a particular database?
We have tests and development servers I would like to make sure the user lists are in sync for that db.
Thanks
How Do I Restrict Access To The Report Servers Header From Users
Jun 16, 2007Hi
I'm looking to deploy some SQL Server reports and I want to restrict the access that the users have. Currently when connecting to the reports site they have access to a lot of functionality through the header bar, for example
- Properties
- New Folder
- New Data Source
- My Subscriptions
- Site Settings
- Search
etc.
How can I disbale or hide all these options so that all the user sees is the list of reports?
Thanks in advance
Mark
SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View
Jul 29, 2015My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
Can I Automate Data Loading Through DTS?
Sep 19, 2000Using SQL Server 7.0, I need to watch for a file to be placed in a directory and then load it automatically. What is the best way to do this? I have the Bulk Insert process set up in DTS and would like to just add another process if possible.
View 2 Replies View RelatedHow To Automate Cleaning The Data In Repldata Folder
Feb 23, 2006hi everyone,
can any one suggest me how to clean the 'REPLDATA' folder created during snapshot replication. i want to automate the process.
can anyone suggest any undocumented sp like
'sp_MSDelete_publisherdb_trans'
thanks
Can We Automate The Data Mining Models Management?
Jun 12, 2006Hi, all here,
I am having a question about automating data mining models managements. As we know in many businesses, patterns vary very frequently, therefore, the mining models created will need to be created again afterwards according to new rules appearing in the data. But can we make all these process automated like automatically assessing the mining model accuracy and automatically recreate the mining models based on predifined specifications? Would please any one here give me any idea about that?
Thanks a lot for any guidance and help for that.
With best regards,
Parameter Selection Of Month, Showing Selected Month And Sum Up To That Month In Another Row
Apr 5, 2008Hello what I'd like to display the following in a matrix report:
Parameter selected: 3 (March), 2008 (Year)
Monthly TO Summed up
ArtNo March <=March
1210 20,500 50,900
1220 21,200 64,000
1230 15,400 40,300
... ... ...
So, in the rows I have the articles and in the column the selected month via parameter. In another column I need to sum up all monthly values up to the selected month, meaning in this example the sum of jan, feb and mar per article.
Integration Services :: Automate Process Using SSIS To Create Tables In Corresponding Database And Load Data
Oct 6, 2015We are using SQL Server 2014 and SSDT-BI 2013. We have a reporting environment where business users create objects which need to be persisted for fiscal year reporting. Let's say for instance SQLSERVER1SRVR1 they create table objects like below in the reporting environment.
Accounting2014, Accounting2015 in AccountingDB;Â
Sales2014, Sales2015 in SalesDB;Â
Products2014, Products2015 in ProductsDB;Â
Inventory2014, Inventory2015 in InventoryDB etc....
These tables are persisted for auditing in a different environment SQLSERVER2SRVR2 for finance & audit folks.We would want to automate this process using SSIS to create tables in corresponding database and load data. I tried using For Each Loop container but the catch is I could loop the source or destination but how do we loop on Source & Destination at the same time (i.e when source is in AccountingDB destination to be AccountingDB, source SalesDB then destination SalesDB so on etc....
SQL Server 2012 :: Second Last Work Day Of Month
May 1, 2014I would like to get the second last work day of the month.
View 8 Replies View RelatedSQL Server 2012 :: Add Month To A Date
Jun 3, 2014I am trying to add month to a date. Here is my code
declare @CollectionDate date='10-28-2014'
select @CollectionDate
;WITH CTemp AS (
SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1
UNION all
[Code] ....
It is working fine. But when I am giving date '10-30-2014' it shows me the error
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
I can understand the problem that it is for the month of February. But How do I overcome the situation?
SQL 2012 :: Job Fails On First Wednesday Of Every Month?
Jun 26, 2014Got a job here that fails on the first Wednesday of every month. It's a complex job (66 steps at last count) that takes roughly 7 hours a night. It starts at 1am, and should be finished by 8am.
The first step normally takes under 5 minutes and is one of the quicker steps However, on the first Wednesday of every month, the first step never finishes; it'll still be running when I come in at 8.20 (ish...). Kill the job, start it again, and no matter what it will not get past step 1. The only solution is to restart the service (not the server!), and then restart the job. When we do this it then goes through in the usual less than 5 minutes.
According to all the monitoring I'm doing, the actual step is not doing anything; there doesn't appear to be any processing, no CPU usage, no reads, no writes etc. It just will not process it
I've tried clearing the cache, both ad-hoc and proc (the step runs a SP). Although I have to confess I've not tried running the SP in step 1, seeing if that works, and then run the job from step 2. Part of the issue is we need to get the process running as quickly as possible, and I don't generally have time to experiment.
Ops and Tech Support say there is nothing running on the server (servers - as it's a cluster) - no AV downloads or anything. The fact I only have to restart the service, rather than the server would imply it's a SQL thing.
SQL Server 2012 :: Get Latest Occurrence Of X Month?
Jan 17, 2014I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.
At the moment I'm using this:
DECLARE @Date DATE = '20131217'
SELECT CONVERT(DATE, CAST(DATEPART(YEAR,
IIF(
--If we're at the start of a year
--we'll need to go back a year
DATEPART(MONTH, @Date) IN (1,2,3),
DATEADD(YEAR, - 1, @Date),
@Date
)) AS VARCHAR(4)) + '0401')
SQL Server 2012 :: How To Show Only First Friday Of Every Month
Jul 2, 2015I am having one requirement where I want to show only first Friday of every month of 2014,2015 and 2016 year.
View 9 Replies View RelatedSQL Server 2012 :: Calculate Customers Age And Value By Month And Year?
Sep 22, 2014What's the best way to calculate a customers age and value by month and year?
I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:
select fh.contact_number
, concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year
, cast(fh.transaction_date as date) as transaction_date
, sum(fh.amount) as ttl_amount_in_month
[Code] .....
It seems to work, but any better way to achieve this?
SQL Server 2012 :: Just Get Month And Date Only In A Query Without Year?
Mar 18, 2015This is what I have for a query but i would like to take the year off. is there a way i can just have the month and day without the year attached?
View 3 Replies View RelatedSQL 2012 :: How To Sync DB Between 2 Servers
Apr 23, 2014I have 2 server PROD n DR server all the PROD server DB is synched to DR server through log shipping. Now to chk whether the DR server is working or not in case of any disaster we shut down the PROD server now all the app request is handled by DR server,it is working fine. For 1 week we work in DR server now its time to move back to PROD server so that all the 1 week data in DR server should be replicate back to PROD server. Log shipping is deleted once the DR Server is up. What r the options other than Backup? It is SQL 2012
View 4 Replies View RelatedSQL 2012 :: Secure Login To Database For Web Users?
Feb 21, 2014We build up a new website in .net mvc 4.0 on a w2012 server with MSSQL 2012 database.We use windows autentification and normal anonymous access through NT AUTHORITYIUSR who is also a login in the database.But we make extra login possibility for users who are registered and they can insert and modify data in some tables in the database. And I'am afraid of giving NT AUTHORITYIUSR insert and update for some column in tables.I think of set up a new login for the database. Run a new connection string on login and set up special permission for that extra user on the database, and let the user become a member of the ordinary user on the database.
View 0 Replies View RelatedSQL 2012 :: Contained Users Password Hash
Mar 13, 2014I would like to perform an audit of weak passwords, which is well documented for sql users. Using the same methodology, I should be able to audit weak passwords for contained users. To accomplish this, I must be able to find the location of the password hashes for the contained users.
I have looked at sys.syslogins and sys.sql_logins, which both have the password hash for server level users, but not contained users. I was able to find sys.sysusers, which does contain contained users, but no password hash.
What is the location of the password hashes for contained users?
SQL 2012 :: Finding Out What Users Are Getting Access Denied
Jun 3, 2014Is there a query that I can run which will give me a list of users that have tried to use SQL resources for which they do not have permissions?
View 1 Replies View RelatedSQL Server 2012 :: Converting Year And Month Varchar To Date
Jan 31, 2014I have a table with Month , Year as varchar. I realized it was a big mistake. Since its getting too complicated to query this way.
Year Month Productname
2013 11 ACB
2013 11 CDE
I would now like to add another column called date and store these Year Month as a date to my existing table
Year Month ProductName Date
2013 11 ACB 2013-11-01
2013 11 CDE 2013-11-01
Is there a way I can do it for all the columns of the existing table ??
SQL 2012 :: Using LEAD Function To Show Previous Month Difference?
Mar 25, 2014I have 12 month report and I need show volume and difference between current and prev month volume, what is the smart way to do this, do I need to put prev month value onto same row horizontally? I think should be some other smart way, I heard about LEAD function?
This what I think for now, It should be listed per ClientID also, in my example I have single ClientID for simplicity.
I tried to do LEAD but with not success..
/*
IF OBJECT_ID('tempdb..#t') is not null drop table #T;
WITH R(N) AS
(SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N <= 12 )
SELECT N as Rn,
10001 ClientID,
DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())) AS [Month],
[code]....
SQL Server 2012 :: Calculate Total On-peak And Off-values For A Month
Nov 25, 2014SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.
On a daily basis i store the hourly values of the meter in a SQL table.
On-Peak
Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)
off-Peak
Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak
Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak
Here is the DB Table Structure:
Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)
SQL 2012 :: Split Database Across Servers
Apr 4, 2014I would like to know if it is possible to split a database across different servers, in the same manner you can split it over multiple drives on the same server We are trying to balance the load cause we are finding that the current server can't handle the load
View 4 Replies View RelatedSQL 2012 :: Compare Database Across Servers
May 28, 2014I want to compare the database structures(columns,datatypes..etc) across same databases located in different servers.
View 9 Replies View Related