Recently Indexed Messages:-



SQL 2012 :: DBCC CheckDB Output File With Date Stamp

Jul 8, 2015

I 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"

View 5 Replies

SQL 2012 :: Replication - Distribution Database Doesn't Appear Under System Databases

Oct 30, 2014

I 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

View 1 Replies

T-SQL (SS2K8) :: Programmatically Generate A Number Sequence?

Sep 3, 2014

I'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?

View 9 Replies

Reporting Services :: Insert Row Number For Zone Wise (group By Zone Column) In SSRS Report

Jul 19, 2015

How 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

View 4 Replies

T-SQL (SS2K8) :: Update Multiple Columns In A CTE?

Sep 29, 2015

Can 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]....

View 7 Replies

How To Stop Week Hours To Continue To Next Week

Jan 29, 2014

I 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...

View 4 Replies

Three Table Join - Display Newest Comments Left On Photos Created By Members

Jul 6, 2014

I 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

View 4 Replies

Reporting Services :: Date And Partial Time Expands In Vs Preview But Not On Server

May 13, 2015

We 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.

View 8 Replies

Master Data Services :: Excel Add In Apply Rules Does Not Work

Feb 9, 2014

I 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 Replies

SQL Server 2008 :: String Break Into First Rows And Then Columns Based On Special Characters?

Jul 1, 2015

Part 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.

View 2 Replies

Integration Services :: Combining Duplicate Data

Nov 4, 2015

I 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]...

View 8 Replies

Deleting Duplicate Rows

Feb 27, 2014

In 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

View 2 Replies

Use Select Distinct Statement

Nov 14, 2014

I 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

View 6 Replies

SQL 2012 :: How To Deploy SSRS Report Into Share Point Server

Jun 3, 2014

I 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.

View 0 Replies

Transact SQL :: How To Get Date Difference Between 2 Dates In DAYS Excluding Weekends

Oct 14, 2015

Here I have 2 Dates. CreatedDttm & ModifiedDttm.

I want  - DATEDIFF(Day,CreatedDttm,ModifiedDttm)  and I have to exclude the Weekend days from that query result.

View 10 Replies



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