SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi All,

Can you please give an idea why am I getting this error when sending test mail in SQL Server 2012. And can I use mail.sql.com as smtp server?

 the requested name is valid but no data of the requested type was found

Thanks and best regards.
0
Database Backup and Recovery Best Practices
LVL 2
 Database Backup and Recovery Best Practices

Join Percona’s, Architect, Manjot Singh as he presents Database Backup and Recovery Best Practices (with a Focus on MySQL) on Thursday, July 27, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7). In the case of a failure, do you know how long it will take to restore your database?

Hi,

Can you help, I have a powershell script that will query a SQL file given the list of servers if the Events exist or not or if it is running or stopped. I already have the code but not getting what my expected format in csv. See the code below:

$QueryPath=”C:\DBA\EE\ExtnddEvent.sql”
$OutputFile=”C:\DBA\EE\Output.csv”

$ExecuteQuery= Get-Content -path $QueryPath | out-string

“Results — > `r`n`r`n” > $OutputFile

FOREACH($server in GC “CC:\DBA\EE\Server_List.txt”)
{
$server 
“——————————-” >> $OutputFile
$server >> $OutputFile
“——————————-” >> $OutputFile
invoke-sqlcmd -ServerInstance $server -query $ExecuteQuery -querytimeout 60000 | ft -autosize | out-string -width 4096 >> $OutputFile
}

Open in new window


Actual Result:
result.PNG
Preferred Result:
result_expected.PNG
Note: Can you also remove in the output the words Result(Results — > ) and the ServerName on the Actual Result since it is already showing the ServerName(Server1\Instance1)
result.PNG
result_expected.PNG
0
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'

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

}

Open in new window


TIA!
0
Hi Experts,

I have successfully deployed my website on azure but when I try to login I received an error which when debugged produces:-

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 52 - Unable to locate a Local Database Runtime installation. Verify that SQL Server Express is properly installed and that the Local Database Runtime feature is enabled.)

The application works well on my local machine and connection string seems fine.

Just wondering if there are extra steps to be taken when deploying an application written in code first approach to azure?

I have followed numerous article and can't proceed further. I am no the azure portal>>application setting> connection string screen.

In here I have populated the name and value boxes with this: .

NAME---- DbConnectionString
VALUE--- Data Source=(l@@db)\$$$LocalDB;Initial Catalog=@@@inApp;Integrated Security=SSPI;

Any pointers will be greatly apprieciated.

Kind Regards

Mike

but still no luck
0
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''
				) 
			 )' 

Open in new window

0
I have a web site that sends out emails, and in that email, the user can click a link that confirms an order and comes back to our site and gets logged as a visit (there's more processing going on, the visit log is just a small piece of it). what I want to do is create a query that'll return each email address along with the number of times they clicked the links (or display zero if they haven't clicked it). I came up with the following query, but it's returning the total count for ALL emails that clicked the link for that particular order, and I'm having trouble narrowing it down:

  select distinct( c.email ), 
  (   SELECT Count( rr.OrderItemID )
		From clpResponses rr
			INNER JOIN OrderItems oi ON rr.OrderItemID = oi.OrderItemID
			inner join Contacts as c on (r.ContactID = c.ContactID) and (c.AdminID = 1426)
		Where oi.OrderID = 1093484 )
  from OrderItems as i 
	  left outer join clpResponses as r on (i.OrderItemID = r.OrderItemID) and (r.AdminID = 1426)
	  left outer join Contacts as c on (r.ContactID = c.ContactID) and (c.AdminID = 1426)
  where i.AdminID = 1426 and i.OrderID = 1093484

Open in new window


here is an example piece of data from the clpResponses table:
SysID	AdminID	OrderItemID	ContactID	UserID   	        UserIDRespondent	DateOfResponse
105426	1426	         2068699	521 	        474771   	4888360	                        2009-09-29 17:14:40.467

Open in new window


and the OrderItems table:
OrderID       AdminID     OrderItemID
1093484      1426            2068699

Open in new window


and finally the contacts table:
ContactID	AdminID	UserID	FirstName	LastName	Email	                       Active	RemoveRequested
521                 1426                474771   NULL             NULL              me@abc.com	                1              0

Open in new window

0
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

Open in new window


Then I try to execute
EXEC NewPendingUser '7/25/2017 13:48:29', 'John', 'Doe','john@doe.com'

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'

Open in new window


What am I missing?
0
I am trying to understand whats going on here.

I am using the sys.dm_db_index_operational_stats on a sql server 2012 instance and I am trying to do analysis on the Singleton_lookup_count column provided by this sql function.

I understand that when all the parameters are NULL SQL looks at all the tables in the given database or I can pass DB ID and object_id for a specific table to get data for that table.

In theory that sounds great but the singleton_Lookup_counts i am getting are off by 150 Billion. That makes no sense something has to be wrong there.  

I would like to understand questions above so i can ultimately  compare Singleton_Lookup_counts between Index and Heap.

Thank you

Here is sample code
SELECT singleton_lookup_count,* FROM sys.dm_db_index_operational_stats(DB_ID(), 1542973269, NULL, NULL) 

select 
    ops.object_id         as [Object Name]
    , sum(ops.range_scan_count)         as [Range Scans]
    , sum(ops.singleton_lookup_count)   as [Singleton Lookups]
    , sum(ops.row_lock_count)           as [Row Locks]
    , sum(ops.row_lock_wait_in_ms)      as [Row Lock Waits (ms)]
    , sum(ops.page_lock_count)          as [Page Locks]
    , sum(ops.page_lock_wait_in_ms)     as [Page Lock Waits (ms)]
    , sum(ops.page_io_latch_wait_in_ms) as [Page IO Latch Wait (ms)]
from sys.dm_db_index_operational_stats(null,null,NULL,NULL) as ops
  inner join sys.indexes as idx on idx.object_id = ops.object_id and idx.index_id = ops.index_id
  inner 

Open in new window

0
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 )
0
Hi,

I've been using the below code for sometime, all works well, except when there is an insert or edit query - I get a blank page.
It could be because it's trying to add/edit a field that may not exist in the database or an SQL error - but I do not get feedback on screen - the page usually just loads blank.  Should I be using different code, alter it, or something completely different?

$db_host = "ip";
$db_user = "admin";
$db_name = "database";
$db_pass = "pass";

$link = mysqli_connect($db_host, $db_user, $db_pass) or die(mysql_error());
mysqli_select_db($link,$db_name) or die(mysql_error());

Open in new window


QUERY
$sql = mysqli_query($link,"SELECT * FROM table") or die(mysql_error());
$row = mysqli_fetch_array($sql);
$total = mysqli_num_rows($sql);

Open in new window


INSERT
mysqli_query($link,"INSERT INTO table (test) VALUES ('1')");

Open in new window


UPDATE
$result = mysqli_query($link,"UPDATE table SET test='1',WHERE id = '1'") or die(mysql_error());

Open in new window


Or is it a PHP setting i need to turn on to properly show me the SQL error?

Thank you
0
NFR key for Veeam Backup for Microsoft Office 365
LVL 1
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

We have an internal application that is setup to track all actions and changes to the system and logs it to a separate SQL database. As you can imagine it generates a lot of data and grows quickly.

We are looking for a way to rotate this audit data. We would like to have the database delete off data that is older than a specific period of time.

I am no SQL expert but I did some digging and ran into table partitioning in SQL, where you can set it up to break up this data into smaller chunks and then truncating off chunks of data as it gets old. I think this might be doable but it seemed complicated. Is there a better/simpler way to be handling this type of data in SQL Server?
0
Failed to create SQL server certificate Error on SCCM installation
sql_error.png
0
According to Microsoft the timestamp data type has been depreciated.  I use MS Access as a front end to SQL and I've always used timestamp columns in tables where a yes/no (binary) field exists.  What data type is most correct?  Are timestamp columns still needed?

Thanks.
0
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
0
Using Winforms C# SQL Server

Can a dataGridView be used unbound to enter int values for calculation purposes and then insert those values into a database table after?
I need to enter package data such as Pieces, Length, Height, Width, DimFactor on either one row or multiple rows. e.g. Length * Width * Height / Dim Factor.

All the example I found refer to bound data.

Is a dataGridView the best option for this?
0
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
0
Hi experts
I have two datagridview and one button.my data has been shown in the first datagridview from sql. I want some code to do bellow but i don't know how can I write it:
When I clicking on the button,30 rows of first datagrideview choose and transfer to second one
Please help me
0
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,

Open in new window

0
hi

See the attached file as an example of my table, you can see the column A and B can be the same for a lot but Column C is unique and I ONLY want 3 rows of column C where the A&B is the same, can anyone help please

Thank you
0
Percona Live Europe 2017 | Sep 25 - 27, 2017
LVL 2
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

What do you find is the best solution to mask data for both SQL server and iseries DB2?
0
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
0
I having the issue migrate one of the website from dreamhost to Cpanel .In the xxxx_Options SQL database I unable to see the content because to let in work I have to change the path for public_html.I am able to search public_html and one match but can not read .I has migrate from CPanel to Cpanel befire there is no problem.Please advice.
old.PNG
new.PNG
0
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?
0
Hello,

I've been playing around with the Always_on technology in SQL Server 2014 Enterprise.  I've got a production database and I've created a secondary database using this technology to use as a "Real Time" reporting database.  I've got the databases setup using Always_on and I am using Crystal Reports XI to try to connect to the "Synchronized" database but I am unable to connect.  In fact when I try to create an ADO connection to the database I put in the "sa" user and password and the database doesn't even show up in the database dropdown.  I've got the secondary database set to "Readable"

Any help would be greatly appreciated.
0
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??
0

SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.