Hi Experts!

Need your help updating LINQ query.

The LINQ query should return a distinct record of locationId and phonenumber.
Then which need to extract locationId and phonenumber and store it in local variables.

SQL Query:

SELECT DISTINCT  
       personlocation.LocationID,
       phone.PhoneNumber
FROM dbo.personProducts pp
     INNER JOIN dbo.personPhonesLocations phoneLocation ON pp.personLocationRecID = phoneLocation.personLocationRecID
     INNER JOIN dbo.personLocations personlocation ON pp.personLocationRecID = personlocation.personLocationRecID
     INNER JOIN dbo.Phones phone ON phoneLocation.PhoneID = phone.PhoneID
     INNER JOIN dbo.PhoneTypes PhoneTypes ON pl.PhoneTypeID = PhoneTypes.PhoneTypeID
WHERE pp.PersonNumber = 'ABC123'
      AND PhoneTypes.PhoneTypeName = 'Contact Number'

Select all Open in new window

C# method:

var personPhone = (from pp in dataContext.personProducts
from phoneLocation in dataContext.personPhonesLocations
from personLocation in dataContext.personLocations
from phone in dataContext.Phones
from phoneType in dataContext.PhoneTypes
where pp.PersonNumber == "ABC123" && phoneType.PhoneTypeName == "Contact Number"
select new
{
LocationId = personLocation.LocationID,
phonenumber = phone.PhoneNumber
}).Distinct();


if (personPhone != null)
{
    string locationId = ???; <-- need help
    string phonenumber = ??  <-- need help

}

Select all Open in new window

TIA!

Receiving an error when trying to add this record. It errors out stating that the "conversion failed when converting date and/or time form character string." It will work properly when I manually key in a date for the @SQLWorkDate parameter (e.g. '2017-07-26'). Any idea how I can correct this?

ALTER PROCEDURE [dbo].[AutomaticUpdates_RouteBookDataPreProcessDE]
   @RequestUID TowerRequestUID,
   @SQLWhere PCSTowerReportFilter = NULL,
   @RouteWorkDate DateTime = NULL,
   @WorkDate DateTime = NULL
AS

	DECLARE @SQLRoutes VarChar(MAX)
	DECLARE @SQLCommand VarChar(MAX)
	DECLARE @SQLWorkDate DateTime
	
SET NOCOUNT ON

	SET @WorkDate = (Select DateValue From W_ReportParam Where RequestUID = @RequestUID AND ParamName = 'RouteWorkDate')
	SET @SQLWorkDate = CONVERT(date, @WorkDate)
	SET @SQLRoutes = REPLACE(REPLACE(@SQLWhere, ')', ''), '{Route.Route} IN (', '')
	
	SET @SQLCommand = 'INSERT INTO [dbo].[zRouteBookDataDE] Select ''' + convert(nvarchar(max),@RequestUID) + ''', * From [dbo].[Report_vw_RouteBookDE] Where ServiceDate = ''' + @SQLWorkDate + ''' AND Route IN (' + @SQLRoutes + ') 
		AND NOT(NewStart = 0 AND RouteBookOrder = 0) AND
		NOT( RouteBookOrder = 1 
			And ( 
				ServiceHistoryStatus IN (2,3,4,5,10) Or
				CustomerStatus = 2 Or
				SiteStatus <> ''AP''
				) 
			 )' 

Select all Open in new window

Using MS SQL 2016
Trying to create an account signup and use a stored procedure to enter the pending new user in the database.

The procedure I'm using is as follows:

CREATE PROCEDURE [dbo].[NewPendingUser]
	@Password NVARCHAR(200),
	@RecordTimeStamp DATETIME,
	@Email NVARCHAR(75),
	@FirstName NVARCHAR(25),
	@LastName NVARCHAR(25)
AS
BEGIN
	SET NOCOUNT ON;
	
	INSERT PendingUsers (
		DateEntered, 
		FirstName, 
		LastName, 
		Email, 
		[Password]
	) VALUES (
		@RecordTimeStamp, 
		@FirstName,
		@LastName,
		@Email,
		@Password
	)

END

Select all Open in new window

Then I try to execute

EXEC NewPendingUser '7/25/2017 13:48:29', 'John', 'Doe','john@doe.com'

Select all Open in new window

But am greeted with an unwelcome message:
Msg 8114, Level 16, State 5, Procedure NewPendingUser, Line 0 [Batch Start Line 0]
Error converting data type varchar to datetime.

I've also tried this with no success (same error)

EXEC NewPendingUser '2017-07-25 13:48:29', 'John', 'Doe','john@doe.com'

Select all Open in new window

What am I missing?

Is there some trick to getting the translation working? "AS" in the query below fails...

Here is the Query:

SELECT c.FirstName AS first_name,
             c.LastName AS last_name,
             c.Company AS company_name,
             r.RepID AS rep_id,
             r.RepName AS rep_name,
             c.CustomerID AS om_customer_id
FROM Customers c
LEFT JOIN RepManufacturer r ON r.RepID = c.Integer5
WHERE (c.FirstName LIKE '%todd test%' OR c.LastName LIKE '%todd test%' OR c.FullName LIKE '%todd test%' OR c.Email LIKE '%todd test%' OR c.Phone LIKE '%todd test%' OR c.Company LIKE '%todd test%')

Which gives me this array result on our Unix Ubuntu web server.. so basically it just totally ignores the ‘AS’ and retains the orginal name

Array ( [0] => Array ( [FirstName] => Todd testing [LastName] => Sprind testing [Company] => OurCompany [RepID] => 658 [RepName] => Dan The Man [CustomerID] => 2171 ) [1] => Array ( [FirstName] => todd TEST [LastName] => sprind TEST [Company] => ourcompany.com [RepID] => [RepName] => [CustomerID] => 2172 ) [2] => Array ( [FirstName] => TEST Todd [LastName] => TEST Sprind [Company] => [RepID] => [RepName] => [CustomerID] => 2242 ) [3] => Array ( [FirstName] => test todd [LastName] => test spri [Company] => residential [RepID] => [RepName] => [CustomerID] => 2702 )

Failed to create SQL server certificate Error on SCCM installation
sql_error.png

Hi Experts, the title may seem a bit cryptic, but its exactly what I'm trying to do.

I have a requirement for remote sites to capture job data locally in an SQL database.

These data periodically are 'syncd' with a mater database at a head office.

There will be up to 15 remote sites.

I'm looking at the data design, and the only thing I can think of is to use strings for the primary keys to ensure they are different to the remotes so there isn't a clash when they are merged in the master. IE I can have a job No 1 and all remotes will have this id, but using Remote1Job1 could be unique.

Its also for dropdown values, as they are added to one remote when syncd the data will have to be pushed to the remotes.

This I know is very inefficient, but the database wont be massive, so forgoing pure design against a working application could work.

Does anyone have any better ideas?

Andy

hi i have the folling function which is replacing value innstead of converting varchar to number am having issue with the logic the function pass varchar and number data typein this case when i pass vaarchar its replacing the varchar and leave the number whereas it must go to second sql when data not found
function12.txt
reallscript.txt
function12.txt
crttable.txt
insert321.txt
insertts.txt

I'm looking at examples and I need to insert the result of this CTE into another table.

I created a temp table to insert the results into but I don't know where the insert statement goes.

I tried it right after the CTE query , before it but it didn't work.

How can I do this? This is SQL 2016

;with cte as
(
SELECT  n.c.query('.') entirequery,
       n.c.value('(//num/node())[1]','varchar(max)') ChapterNumber,
	   isnull(n.c.value('(//heading/node())[1]','varchar(max)'),'') AS ChapterName,
       n.c.value('num[1]','varchar(max)') AS 'Subchapter',
	 	stuff (' '+n.c.query('(heading)').value('.', 'varchar(max)'),1, 1, '') subchapterTitle,  

		--section num
	
		stuff (' '+n.c.query('(section[1]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum1,  
		stuff (' '+n.c.query('(section[2]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum2,
		stuff (' '+n.c.query('(section[3]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum3,
		stuff (' '+n.c.query('(section[4]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum4,
		stuff (' '+n.c.query('(section[5]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum5,
		stuff (' '+n.c.query('(section[6]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum6,
		stuff (' '+n.c.query('(section[7]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum7,
		stuff (' '+n.c.query('(section[8]/num)').value('.', 'varchar(max)'),1, 1, '') SectionNum8,
		stuff (' '+n.c.query('(section[9]/num)').value('.', 'varchar(max)'),1,

Select all Open in new window

…

Hi,
I am having an issue with my dev server. In my dev server SQL 2014, SQL 2106 is installed and also both reporting service is installed too. I have created a couple of reports by using SQL server data tool 2015. but I want to publish that report in a native mode of SQL Server Reporting Service 2016. Once I try to configure reporting service in "SQL Server 2016 Reporting Service configuration manager" I am getting this message "Unable to connect to the Report Server VA1-HGF8374 ".

Need some good answer.

Thanks,
Rashed Hossen

I need to modify how products are displayed in the layered navigation and search results. I have many products that have the same attribute value so I want to add a GROUP BY so only one of each products displays if they have the same attribute value. What page and functions do I modify to add this?

I have a connection in excel to an Oracle database. One of the tables in the database has millions of records which I am filtering for based on and Active/Inactive filter, and a max effective date for each record, but I also need to filter some additional columns for a max value - the code I have which works for the max effective date is:

select PS_OWNER.PS_JOB.EMPLID, PS_OWNER.PS_JOB.EFFDT, PS_OWNER.PS_JOB.EMPL_RCD, PS_OWNER.PS_JOB.EFFSEQ, PS_OWNER.PS_JOB.HR_STATUS
from PS_OWNER.PS_JOB
where
PS_OWNER.PS_JOB.HR_STATUS = 'A'
and
(PS_OWNER.PS_JOB.EMPLID, PS_OWNER.PS_JOB.EFFDT) in (
    select PS_OWNER.PS_JOB.EMPLID, max(PS_OWNER.PS_JOB.EFFDT)
    from PS_OWNER.PS_JOB
    group by PS_OWNER.PS_JOB.EMPLID
)

so my question is how/where should I insert the code for only showing the max EMPL_RCD and EFFSEQ??