Query Syntax

48K

Solutions

19K

Contributors

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

I have a user getting the attached error when trying to access a SQL server reporting web page.  

I login to the SQL server and launch the web page as an administrator.  I see the user already has System User access in the site permission.  

Our SQL admin is not available and I need to resolve this issue.  If you know where I should check, please advise the steps.    

Thanks in advance.
ssrs-error.png
0
Free Tool: IP Lookup
LVL 9
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hello - we're setting up our application on SQL Svr 2012 and are looking for quick help with the best-practice performance settings for SQL.  For example - we know with "Max Degrees of Parallelism" its recommended to set that at "0" for auto-detect, correct?  What else should we look at?

Thanks for your help.
Damian
0
Hi experts, I have a weird situation in my SQL table where values are being placed two different ways.(Please view the picture attached). This data is being inserted by two PHP pages, one performing an insert statement and another performing a modify.

The insert statement is the one displaying the values as centered, but if I used my modify page, the centered value is then aligned to the left.

I have tried to understand what the difference between the two are but have not found out online. The data should be centered, not aligned to the left.
EX_EE_VALUES.PNG
0
I have two tables with related data in them. They can be joined on DocNbr (which is a varchar(10) column). I am trying to audit deletes from these two tables by sending a simple email when a record is deleted. The problem becomes, I am trying to include data from both tables in the email body and I'm not able to, as trying to join the two tables together on the DocNbr field isn't possible any longer (as one or both tables have now had their record deleted).

What are my alternative options here?
0
Hi Team,

         I am using PostgreSQL using PGadmin 4 for Backup &restore. I can able to take backup easily using backup.And also restore the backup file to new database.

       But I can't able to overwrite database restore.If i try new database restore successfully completed.If i restore backup file to old database i get a error.I was using pg admin and lot of command also for restore.Anyone can you please help me for this issue. I am using windows 7 32 Bit.
0
Jul 27, 2017 3:53:05 PM org.apache.catalina.core.AprLifecycleListener init
INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path: C:\Program Files\Java\jre7\bin;C:\Windows\Sun\Java\bin;C:\Windows\system32;C:\Windows;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\;c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files\Microsoft SQL Server\100\Tools\Binn\;c:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\;C:\Program Files\Java\jdk1.7.0_79\bin\;C:\Program Files\nodejs\;C:\Users\khushboo.sharma\AppData\Roaming\npm;.
Jul 27, 2017 3:53:05 PM org.apache.tomcat.util.digester.SetPropertiesRule begin
WARNING: [SetPropertiesRule]{Server/Service/Engine/Host/Context} Setting property 'source' to 'org.eclipse.jst.j2ee.server:ALNTUI' did not find a matching property.
Jul 27, 2017 3:53:05 PM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["http-bio-8082"]
Jul 27, 2017 3:53:06 PM org.apache.coyote.AbstractProtocol init
INFO: Initializing ProtocolHandler ["ajp-bio-8009"]
Jul 27, 2017 3:53:06 PM org.apache.catalina.startup.Catalina load
INFO: Initialization processed in 1911 ms
Jul 27, 2017 3:53:06 PM org.apache.catalina.core.StandardService startInternal
INFO: Starting service Catalina
Jul 27, 2017 …
0
My bosses have recently asked me to reassess our current server environment.

We currently have a DC, exchange, file server, SQL server and archive server. The servers are approaching 7 years in age so the possibility of a possible refresh is very much welcome.

Our environment is rather basic, but a system failure would result in weeks of downtime due to waiting periods for server hardware. So my task is simple, I need to reduce downtime and maintain production even during a failure.

Now I'm thinking this shouldn't be too much of a head ache to achieve and given how hardware performance has improved since installing the current servers I'm thinking that we could do more with less. Dual CPU servers or servers with 64GB+ memory shouldn't be required, but then again I haven't played around with any new server hardware in years so I could be very wrong.

So here's what I'm thinking, the PDC can run on an entry level 1U server, I can add another as BDC. File server can also be entry level; I say entry level in the sense of CPU and RAM requirements, I will always run RAID arrays on my servers. SQL and Exchange; here I need some guidance.  The SQL server is a joke, it's an i7, 32GB RAM with 6 SSDs in RAID; it runs ok but has recently shown signs of wanting to kick the bucket, this will have to change to proper hardware.

Our Exchange is a dual Xeon with 80GB RAM, 10 SAS drives in various RAID arrays; I'm thinking this can be reduced to a single CPU system with 64GB RAM,…
0
hi,

it seems that MS only release SQL server data tools for VS 2015 so that we can admin and develop even SSIS project within VS 2015, how about VS 2017?

it seems we don't have this option to develop SSIS package inside VS2017, am I right?
0
Hi Experts,


I want to execute  the SQL Agent Job (list of stored procedures) in a loop  to process in a batch for historical loads.

For example , I want to do historical load for one year , I want to implement this logic in SQL Agent Job in small batches of 4 days for 92 iterations (365/4)

Note : Each Iteration , I can load only 4 days of data , as the data size is massive (~130 Million records for 4 days). So I want to process historical loads in small chunks of 4 days and batches.

In this example below , how to execute this logic in loop , auto increment the values of  @historical_begin_date && @historical_end_date for each iteration and execute the stored procedures in a loop.

Example
Declare @historical_begin_date date time
Declare @historical_end_date date time

while @historical_begin_date <@historical_end_date  
begin
Exec Stored procedure 1
Exec Stored procedure 2
end

Please walk me through the solution with code .

Thanks,
Sreekanth.
0
aI have super simple car table (see attached picture)
and I want to use sql query to output like below.

Can you show me how it is generated by using sql script?

<xml>
<Vehicles>
  <Vehicle>
	<Make>Acura</Make>
	<Model>CL</Model>
	<Year>2001</Year>	
  </Vehicle>
</Vehicles>
</xml>

Open in new window

0
The Orion Papers
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Hi,

I'm using SQL Server 2014.  I'm trying to build a dynamic WHERE clause based on an input parameter to my stored procedure.  If a user passes in a value of 1 for the "@active" parameter, I want to to add the SQL "AND e.date_posting_expires GTE getDate()"

I have this code snippet but it doesn't quite work.

      
         SELECT c.company_id, company_name
        FROM CompanyTable c
         WHERE c.company_id = @company_id

	IF (@active = 1 ) 
		BEGIN
		AND j.date_posting_expires GTE getDate()
	END

Open in new window

0
Hi Experts,
I have the following function to  check for a date if its within current week.
Public Function sThisWeekOpening(sDate As String) As String
    sThisWeekOpening = " DateDiff(ww, DateAdd(d, -2, IsNull([" & sDate & "], DateAdd(wk, 1, GetDate()))), DateAdd(d, -2, GetDate())) > 0"
End Function

Open in new window

However this ends of being very complicated in terms of debugging and maintaining.
just to give an example how its being used..
    OpenTPSQL = "((" & sThisWeekOpening(IIf(sTable = "view_Openings", "DayStart", "Day"))
    OpenTPSQL = OpenTPSQL & " or (" & sThisWeekOpening("StatusDate") & " or StatusDate is null))"
    OpenTPSQL = OpenTPSQL & " or " & IIf(sTable = "view_Openings", "DayStart", "Day") & " < getdate()-1)"
    OpenTPSQL = OpenTPSQL & " and InActiveStatus is null"

Open in new window


Therefore I'm looking for a way to simplify it. (perhaps just converting it to a SQL UDF..)

Also since this is using GetDate() which is getting the system date, how can I change it to test it on a diff date,
for example the following is checking if its pass Monday and there are open records for the coming week.
    OpenTPSQL = "((" & sThisWeekOpening(IIf(sTable = "view_Openings", "DayStart", "Day"))
    OpenTPSQL = OpenTPSQL & " or (" & sThisWeekOpening("StatusDate") & " or StatusDate is null))"
    OpenTPSQL = OpenTPSQL & " or " & IIf(sTable = "view_Openings", "DayStart", "Day") & " < getdate()-1)"
    OpenTPSQL = OpenTPSQL & " and InActiveStatus is null"

Open in new window

How can I test this without having to wait for the next Tue?

Thanks in advance.
0
Hi Experts,

I'm trying to setup an application that should allow users adding/editing/deleting a single row at a time.

Now for security purposes, I would like to have these users not being able to view more then one record at a time.

Was thinking of designing a stored procedure that will accept a parameter and display just that one record selected.

However I'm stuck with the following
A- A stored procedure is probably read only in Access.
B- In order to have users select a record, I need a drop down displaying all records, how can I have it just avail to the combo box record source while in database container it should not appear?

PS. for deleting single record I came up with this function that will perform it, so I dont give this user access to delete anything in SQL BE, and just have a button in Access calling the following
Public Function DeleteRecord(sTable As String, lID As Long) As Integer

    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cmdString As String
    Dim i As Integer
    
    Set cnn = New ADODB.Connection
    cnn.ConnectionString = "driver={SQL Server};" & _
          "server=tcp:ServerName\Instance;uid=MyUser;pwd=MyPWD;database=MyDB"
    cnn.Open


    cmdString = "delete from " & sTable & " where id = " & lID
 
    cnn.Execute (cmdString), i
    cnn.Close
    DeleteRecord = i

End Function

Open in new window

So basically I'm looking to deploy the same idea for adding/editing records.

Would like to know what is the simplest way to accomplish it.

Thanks in Advance.
0
this query work in postgresql and console SQL of sc, but no work in SQL of grid...why?

      SELECT
          cpccod,
          lpad(cpcdes,cast(60+(4*cpcnvl) as int), ' '),
          cpccla,
          cpcslm
      FROM
          "public".sccmpc
           where trim(ccodemp)='1' and cempre='E02'
           order by cpccla
0
If the Address_Email field contains  :   johnDoe@email.com janeDoe@emailProvider.com'    or any multiple address entries in this field, then only retain the first email address.  
 
Need to handle any spaces or special characters that might separate the email addresses, which may include nothing between them.
 
If there are three or more email addresses, also only return the first.  

Code:
-- If there are more than one email address in a field, then only use the first one.
	-- Only do this for common extensions.   .com   .net   .edu    .org   .mil   .gov

	Set @SQL = '
	Update ' + @Source_Table_Full + ' Set ' + @Source_Column + ' = 
	 ( Case when reverse(substring(reverse( ' + @Source_Column + '),1,4)) in (''.com'', ''.net'', ''.gov'', ''.mil'', ''.org'') 
	then rtrim(substring( ' + @Source_Column + ', 1, cast(  charindex(''.'', ' + @Source_Column + ',1)+3  as int)  ))
	else ' + @Source_Column + ' 
	end  )
	From ' + @Source_Table_Full + ' ; '


	Print @SQL

	Exec(@SQL)

Open in new window

0
The below script created a user SQL\srvSSISAcc on all my databases and granted datawriter and datareader to all databases but the use is not created in the security login, I need this name to be created on Security login before granting user mapping to all datbases

USE master

GO

DECLARE @DatabaseName VARCHAR(32)   
DECLARE @SQL NVARCHAR(max)
DECLARE @User VARCHAR(64)
SET @User = '[SQL\srvSSISAcc]' --Your User

DECLARE Grant_Permission CURSOR LOCAL FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN Grant_Permission  
FETCH NEXT FROM Grant_Permission INTO @DatabaseName  
WHILE @@FETCH_STATUS = 0  
BEGIN  

    SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'CREATE USER ' + @User + 
    'FOR LOGIN ' + @User + '; EXEC sp_addrolemember N''db_datareader'', 
    ' + @User + '; EXEC sp_addrolemember N''db_datawriter'', ' + @User + ''

    EXEC sp_executesql @SQL
    PRINT @SQL

FETCH NEXT FROM Grant_Permission INTO @DatabaseName  
END  
CLOSE Grant_Permission  
DEALLOCATE Grant_Permission

Open in new window

0
I created the below Table and insert some values in it.

What I need to do is if Field1 = 2 and Field2 = 2 Then use the Date in Field3 Else Use '1/1/3000'.
CREATE Table #Test(
	Field1	int,
	Field2	int,
	Field3	datetime
)

INSERT INTO #Test
(Field1, Field2, Field3)
VALUES
(2, 1, '1/1/2017'),
(2, 2, '1/2/2017'),
(2, 1, '1/3/2017'),
(2, 2, '1/4/2017'),
(2, 2, '1/1/2017')

SELECT * FROM #Test

DROP TABLE #Test

Open in new window

0
I set up a Linksys LRT224 router about a year ago
it does some pass-throughs ti the following
-security camera NVR only two users can access

-Windows Server 2012 running an SQL database about 10 users

-VPN connection so users can get to all servers and appliances
This was suppose to be for only two users, but now they want all 10 users to be able to use VPN

Question?
should I continue to use this router? will is start to degrade in speed
some users say the VPN is starting to slow down

any suggestions are appreciated
0
The company that I am working for does not have a version of office that includes Access, but it does have Excel.  Can you write an app that populates one of the Sheets and treat it like an SQL table ?
Can I make SQL type statements that query the fields or would I have loop through each record.

The table probably won't have more than  1,000 records, so even if I had to loop, wouldn't be horrible.

This would only be a first step, if the project worked I would build it again on an SQL server.

Any help is appreciated.
0
Free Tool: Port Scanner
LVL 9
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi experts,

I'm using sql server 2008.

I have a table with a column called Phones1 that holds phone numbers.
Some phone numbers are 7 digits and some are 10 digits.

I have a select query that gets all the columns from my table

if a phone number is 7 digits I want to format it like this:  123-4567
if a phone number is 10 digits I want to format it like this:  (204)123-4567

What's the best way to format this column like this  in my query?
0
I am trying to figure out how to build a query to select data from a specific table that is tied to two other tables.

The schema can be broken down to the following tables:
dbo.Orders
ID and OrderNumber

dbo.OrdersASIDocumentLink
ID, OrderID

dbo.ASIDocuments
ID, DocumentContents

What I need to do is to be able to select all rows in ASIDocuments that match the OrderNumber from Orders.  Both tables are joined by OrdersASIDocumentLink.

How can I do this?  I have a SQL Fiddle with the schema already created with some sample data.  Here is a link to a SQL Fiddle.
0
I want to limit the options of a secondary combo box (Combo297) to the selection in a primary list box (Directors) please.

I am using VBA code in the AfterUpdate event:

Private Sub Directors_AfterUpdate()
Dim strSQL As String
    strSQL = "Select " & Me!Directors
    strSQL = strSQL & " from Category"
    Me!Combo297.RowSourceType = "Table/Query"
    Me!Combo297.RowSource = strSQL
End Sub

Open in new window


However, the RowSource of the secondary combo box (Combo297) appears blank. (Needless to say, the combo box offers no options either.)

With many thanks!
0
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
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

Query Syntax

48K

Solutions

19K

Contributors

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.