Recently Indexed Messages:-
SQL 2012 :: DBCC CheckDB Output File With Date Stamp
Jul 8, 2015I would like to know if it is possible to date stamp the output file for the DBCC integrity check?
The following cmd works fine however I tried different ways to date stamp the output file with no luck.
sqlcmd -U backup -P Password -S Server -Q"DBCC CHECKDB('DatabaseName') WITH ALL_ERRORMSGS" -o"G:LOGSDBCCResults.txt"
SQL 2012 :: Replication - Distribution Database Doesn't Appear Under System Databases
Oct 30, 2014I just finished installing SQL Server 2012 with the SQL Server Replication feature component checked. On configuring transaction replication, i notice the distribution database wasn't there. I ran the below query
EXEC sp_get_distributor And the value for distribution_db_installed is 0 as shown below
installed distribution server distribution db installed is distribution publisher has remote distribution publisher
0NULL000
T-SQL (SS2K8) :: Programmatically Generate A Number Sequence?
Sep 3, 2014I'm trying to do a simple insert into a table, something like this:
insert into sometable (ID, somecolumn)
select 'Task-ID', somevalue from SomeOtherTable
where something = 'someothervalue'
(or something to that effect)
So, the SELECT would generate something that looks like this:
ID somecolumn
-- ----------
Task-ID somevalue1
Task-ID somevalue2
Task-ID somevalue3
(etc.)
Here's where my problem comes in: ID is a PK, and needs to be unique. What I need it to do is this:
ID somecolumn
-- ----------
Task-ID.1 somevalue1
Task-ID.2 somevalue2
Task-ID.3 somevalue3
(etc.)
What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.
Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.
What I'm hoping to do is rewrite my SQL like this:
insert into sometable (ID, somecolumn)
select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable
where something = 'someothervalue'
Is there an easy way to do this?
Reporting Services :: Insert Row Number For Zone Wise (group By Zone Column) In SSRS Report
Jul 19, 2015How to insert a row number for a zone wise(ie group by zone column) in ssrs report in zone column i should get zone1 only once (should not get Zone1,zone1, zone1 -3 times)
sl.no Zone District no.of.region
1 hyd 24
2 ZONE1 chn 12
3 bang 2
1 raj 4
2 ZONE2 vizag 3
3 bbb 34
T-SQL (SS2K8) :: Update Multiple Columns In A CTE?
Sep 29, 2015Can you update data from multiple tables in the same UPDATE statement, by joining those tables in a CTE ?
For example, this fails:
DECLARE @UPDCATE_COUNT AS int = 100000;
WITH COMBINED_TABLES AS (
SELECT TOP (@UPDATE_COUNT) T.UpdateID, T.IS_UPDATED, U.[Description]
FROM dbo.Table1 AS U
INNER JOIN dbo.Table2 AS T
[code]....
How To Stop Week Hours To Continue To Next Week
Jan 29, 2014I am adding week hrs using while loop , but it can continue next week hrs also.
I can get every week start hrs while update @tem1 table
employeeidreportdatereportatleftattoaccountwrhrn
12902014-01-29 00:00:00.00009:3019:1556001
12902014-01-28 00:00:00.00009:0018:4555802
12902014-01-27 00:00:00.00009:0018:455585583
12902014-01-25 00:00:00.00008:0010:0012004 -- week end
12902014-01-24 00:00:00.00009:1718:4554105
12902014-01-23 00:00:00.00009:1918:4654606
12902014-01-22 00:00:00.00009:1718:4754507
12902014-01-21 00:00:00.00009:1618:3552608
12902014-01-20 00:00:00.00009:1818:555435439
My loop statement
while(select MAX(wrh) from @tem1 where wrh = 0) < 1
begin
update @tem1
set wrh = (select toaccount from @tem1
where reportdate = (select min(reportdate) from @tem1 where wrh = 0))+(select max(wrh) from @tem1)
where wrh = (select max(wrh) from @tem1 where wrh = 0 )
and reportdate = (select min(reportdate) from @tem1 where wrh = 0)
end
this is the result while executing loop statement .
employeeidreportdatereportatleftatdehdrhwehwrh
129029 Jan 201409:3019:15008:0009:20024:00065:54
129028 Jan 201409:0018:45008:0009:18016:00056:34
129027 Jan 201409:0018:45008:0009:18008:0009:18
129025 Jan 201408:0010:00005:0002:00045:00047:16 -- week end
129024 Jan 201409:1718:45008:0009:01040:00045:16
129023 Jan 201409:1918:46008:0009:06032:00036:15
129022 Jan 201409:1718:47008:0009:05024:00027:09
129021 Jan 201409:1618:35008:0008:46016:00018:04
129020 Jan 201409:1818:55008:0009:03008:0009:03
How to update only that week hrs , don't continue next week...
Three Table Join - Display Newest Comments Left On Photos Created By Members
Jul 6, 2014I am trying to tweak some code which is used to display the newest comments left on photos created by my members.
The existing code is this:
SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC
So the latest comment left was for photo #210879 from user "Cla" (redacted user names). The 2nd newest comment would be for photo #211072 from a member named "mo". pdate is a date field
However for the script I have coded I don't want all of the photo comments to show up. This is because I use access levels based on the type of location (higher levels mean more restricted galleries). I check the access levels as I go through the recordsets.
I use this method to get the top 15 comments:
SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC
Now I have to use two other tables to determine the access level. Since PHOTOCOMMENTS is just a list of photo #'s and the people who left comments for those photos, I need to:
a) determine what location the photo is from and
b) determine the access level of that location
I use: select creator,access from locations where id=(select dir from photos where id="&pnumber&")"
This is a two step process as you can see. The first part is:
select dir from photos where id=(pnumber)
ID is the same value as pnumber seen in PHOTOCOMMENTS. That is to say PHOTOS.ID = PHOTOCOMMENTS.PNUMBER
If I haven't confused you yet, the executed code for the first example would be:
select dir from photos where id=210879
which would get me a value for DIR. DIR is the location number which would be:
select creator,access from locations where id=(dir value)
Just to simplify it a bit....
There are three tables (shown below)
PHOTOCOMMENTS
PHOTOS
LOCATIONS
I need to: SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC (first table shown)
but then also
select creator,access from locations (The last table shown)
where id=(select dir from photos where id="&pnumber&")"
So the first table PHOTOCOMMENTS has to also join PHOTOS table where PHOTOS.DIR = PHOTOCOMMENTS.PNUMBER in order to get the value of "DIR" and then DIR is joined to the LOCATIONS tables where PHOTOS.DIR = LOCATIONS.ID
Here is the actual code, which I am trying to make into a single SQL command
strSQL = "SELECT top 15 pnumber,pcomment,puser FROM photocomments order by pdate DESC"
set ors = oconn.Execute(strSQL)
tl = 0
do until ors.eof or tl > 15
' until we have 15 results because not every recordset will be of the proper security level
[Code] ....
Bonus points if you can also get it to select from LOCATIONS only WHERE userlevel >= 2
Reporting Services :: Date And Partial Time Expands In Vs Preview But Not On Server
May 13, 2015We run std 2008 r2. I downloaded an rdl whose 2 date/partial time cols used to expand on the server to accommodate times with two digits in hr. When I preview in vs 2008, everything is ok. But when I copy back to the server, these longer date strings now wrap to next line. Why is this? Only change was to a param's drop down ds query. expression for one of the cols is
=Format(Fields!LoginDt.Value, "MM/dd/yy" &
" " &
"h:mm tt")
height is currently allowed to increase.
Master Data Services :: Excel Add In Apply Rules Does Not Work
Feb 9, 2014I have the most recent version of the MDS Excel plug in installed in Excel 2013, and am running SQL Server 2012 Enterprise. The Apply Rules button does not perform validation as it should. If I "Publish" data that does not conform to the business rules I have set up, it does return the proper errors indicating what rules have been violated; just using the "Apply Rules" button does not. I have twice gotten it to work momentarily by turning Show Status on and off several times in succession, but I have not been able to identify any consistent sequence that caused it.
View 7 RepliesSQL Server 2008 :: String Break Into First Rows And Then Columns Based On Special Characters?
Jul 1, 2015Part 1: When there is ~ (tilde) and has any value after it then it goes into a new row and duplicating the other columns like the facility in the screenshot attached and new column having the sequence.
Part 2: When there is ^ (Caret) its a new column irrespective of a value present or not
CREATE TABLE [dbo].[Equipment](
[EQU] [VARCHAR](50) NOT NULL,
[Notes] [TEXT] NULL,
[Facility] [VARCHAR](50) NULL)
INSERT INTO [dbo].[Equipment] ([EQU] ,[Notes] ,[Facility])
SELECT '1001','BET I^BOBBETT,DAN^1.0^REGULAR^22.09^22.090~BET II^^^REGULAR^23.56^0~','USA' union
SELECT '998','BET I^JONES, ALANA^0.50^REGULAR^22.09^11.0450~BET II^^^REGULAR^23.56^0~','Canada' UNION
select '55','BET I^SLADE,ADAM F.^1.5^REGULAR^27.65^41.475~','USA'
SELECT * FROM dbo.Equipment
I created the table in excel and attached the screenshot for a clear picture as to what is required. I use text to Columns in excel to achieve this not sure if there is anything similar in sql.
Integration Services :: Combining Duplicate Data
Nov 4, 2015I have two tables that I UNION to retrieve data for users. A combination of these should have only one employee in the table. The problem is there is a unique id created for the position of instructors. In the other table, it holds all employees with an employee number. Some data such as username, email address, etc., does not change. So even though UNION should remove duplicates, I still have duplicates because of usernames is what I'm filtering on, it is the same in each table. In the combined table I'm only selecting specific employees based on Job class and Job code. For employee id in the first table it is preceeded with 'B', and the second by 'T' (this is only to identify which table the data is taken from). Here is what I am getting when I Union both tables.
query
SELECT
distinct 'B-'+ Employee_ID
as Employee_ID
, Username
,Email
[code]...
Deleting Duplicate Rows
Feb 27, 2014In my database, I have a table "tbl_c_extract" that consists of 4 columns that look the following. I'm looking at a daily batch of around 4000 records, of which 150 are likely to be duplicates.
Emp_No varchar(255), Proprietary_ID varchar(255), LeaveDateActual datetime
123456, E123456, 2014-09-27 00:00:00.000
213832, E123456, 2099-12-31 00:00:00.000
213836, E123456, 2014-01-31 00:00:00.000
In the example above, I need to remove 2 of the entries, leaving only the one that with the maximum leave date. In this case, those without a leave date have the 2099 entry.
Using CTE works exactly as I want it to, however SQL Server Agent doesn't seem to like the use of CTE..
Code:
WITH CTE (Proprietary_ID, LeaveDate, RN)
AS
(
SELECT Proprietary_ID, LeaveDate,
ROW_NUMBER() OVER(PARTITION BY Proprietary_ID ORDER BY Proprietary_ID, LeaveDate) AS RN
FROM tbl_c_extract
)
DELETE
FROM CTE
WHERE RN > 1
Use Select Distinct Statement
Nov 14, 2014I am new to SQL and am trying to use the Select Distinct statement and am having some issues getting it to work. When I use it on only 1 column it is working fine, but I want to use it on 2 columns it does not do what I expect it to.here is my code:
SELECT *
INTO #NEWTEMP
FROM DBO.REPORTDATA;
ALTER TABLE #NEWTEMP
ALTER COLUMN UTCTime DATE
[code]....
In the results table I have 3 entries for each signalid with the same UTCTime, I only want to have 1
SQL 2012 :: How To Deploy SSRS Report Into Share Point Server
Jun 3, 2014I am trying to deploy the report into share point server through Visual Studio by using the [URL]..... But I am getting errors like [URL] .... could not be found.
I configured the the URL credentials like :
Target Datasource Folder: DEV/MyReports
Target Dataset Folder: DEV/MyReports
Target Report Folder: DEV/MyReports
Target Server URL: http://bigweb03
How can I deploy the reports in Sharepoint server to view the reports in online.
Transact SQL :: How To Get Date Difference Between 2 Dates In DAYS Excluding Weekends
Oct 14, 2015Here I have 2 Dates. CreatedDttm & ModifiedDttm.
I want - DATEDIFF(Day,CreatedDttm,ModifiedDttm) and I have to exclude the Weekend days from that query result.