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

This morning, I downsize the memory of a SQL 2008 R2 standard VM from 96 to 64GB and reallocate the CPU arrangement from 8 socket X 2 Cores to 4 sockets X 4 Cores.  

RAM arrangement because our version of SQL can see only up to 64GB.  CPU rearrangement to make SQL see 16 CPUs comprare to only 8 previously.

The WEB app that connects to this SQL is showing slowness.  Is there something in the SQL side I need to do to accommodate the changes?

Please advise if someone has an idea or suggestion.  

Thanks.
0
What does it mean to be "Always On"?
LVL 4
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Please see the attached error message
The sql connection just isn't being made
I have tried all combinations possible
If I log onto the server with the sql instance I can login to the sql management studio with no problem with the same credentials
Surface area has been configured to allow connections
no firewalls are active
no ports blocked
I can ping the sql server from the machine which is trying to make the connection
I can get into all the shares on the machine
Capture.PNG
0
Our customers use a handheld scanner using the Windows CE app, and their app has a SQL .sdf file with all their tables.  I assume this is a special version of SQL for the compact environment--although I don't know that for sure.  If I want to look at data in a table on the scanner, I can use a built-in SQL query program on the scanner.

But I would like to copy the .sdf file to my Windows PC and be able to view that table data.  What program can I download or purchase that can access this type of file?
0
Hello,

I need to create a script that can automatically create username1 and username2 but also add sysadmin role to the NT AUTHORITY\SYSTEM username. It keeps saying that I do not have access to do it. If I manually add sysadmin role to NT AUTHORITY\SYSTEM and run the script it works fine and creates both usernames and their policies and roles but I have not been able to automate the sysadmin assignment. Below is my script:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [NT AUTHORITY\SYSTEM]
GO
CREATE LOGIN username1 WITH PASSWORD = 'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
EXEC sp_addsrvrolemember 'username1', @rolename = N'sysadmin';
GO
EXEC sp_addsrvrolemember 'username1', @rolename = N'dbcreator';
GO
CREATE LOGIN username2 WITH PASSWORD = 'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
EXEC sp_addsrvrolemember 'username2', @rolename = N'sysadmin';
GO
EXEC sp_addsrvrolemember 'username2', @rolename = N'dbcreator';

Thank you in advance!
0
Hi All,

New to SQL and wondering what's the best way to organize my data in the following format.

                       1/1/2016 | 2/1/2016 | 3/1/2016 | 4/1/2016
Territory 1          500            1000            750             500
Territory 2          350             900             820             700
Territory 3          600             800             800             200
etc.

My existing code right now:

SELECT Territory_ID, SUM(CAST(Total_Quantity as DECIMAL(18,2)) AS 'Total Quantity by Territory'
FROM dbo.database
WHERE Total_Quantity is not null
GROUP BY Territory_ID

I have a column named "Period" in this database that contains 13 different values 1/1/2016 - 1/1/2017 by month, is there a way to break these 13 individual dates in "Period" and spread them across columns to break my data up. Because right now with my current code, the output just displays Territory_ID and then the Total_Quantity for respective Territory.

Any help would be greatly appreciated, thank you!
0
Hi

I use the following code to dynamically edit a SQL table in a Windows Form DataGridView.
Most of the time things work but around one out of every 5 times I get the error shown in the image below.
Why would this be? Is there a better way to dynamically update a SQL table based on changes made by the user?

    Sub oDynamic_SQL_Save()
        Try

            Dim builder As New SqlCommandBuilder(dbadp2)

            builder.QuotePrefix = "["
            builder.QuoteSuffix = "]"

            dbadp2.UpdateCommand = builder.GetUpdateCommand()

            dbadp2.Update(dTable2)
            dTable2.AcceptChanges()

        Catch ex As Exception
            MsgBox(Err.Description & " mmm")
        End Try
    End Sub

Open in new window



Image1
0
I am having issues with TMG 2010 trying to install SQL server 2008. TMG 2010 server is associated with SQL Express. How do I unlink the SQL Express and link the new SQL server 2008? How do I keep a copy of the ISA logs on TMG 2010 and SQL server 2008? I have read most of the Microsoft TechNet files on the TMG 2010.
0
I have an ODBC connection to a Cache data to append to a table in Microsoft Access and that is working just fine. But what I would also like to do is to take that same recordset and display it in Access like an Access query using VBA. I found some examples using qrydefs but I can't seem to get it to work with an ADO connection. Is there another way of doing this using ADO?

Below is an example of my connection string that pulls the recordset I need. Any suggestions would be greatly appreciated.

Thank you in advance,

Ken


Public Sub RunLVPendingQuery()
On Error GoTo PROC_ERR
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strVar As String

    Set cnn = New ADODB.Connection
    Set rst = New ADODB.Recordset
 
  DoCmd.Hourglass False


  cnn.ConnectionString = SQCon
  cnn.Open
   
           
strVar = "SELECT DISTINCT SITE.U_DPL_View.AN, SITE.U_DPL_View.PtNumber, " & _
             "SITE.U_DPL_View.PtName, SITE.U_DPL_View.CollectDateODBC, " & _
             "SITE.U_DPL_View.CollectTimeODBC, SITE.U_DPL_View.PriorityCodes, " & _
             "SITE.U_DPL_View.BatTstCode, SITE.U_DPL_View.HID, SITE.U_DPL_View." & _
             "ReceiveDateODBC, SITE.U_DPL_View.ReceiveTimeODBC, SITE.U_DPL_View." & _
             "WorkSheetID, SITE.U_DPL_View.WkshtCode, SITE.U_DPL_View." & _
             "DeleteEvent, SITE.U_DPL_View.PtLoc, SITE.U_DPL_View.TestCode, " & _
             "SITE.U_DPL_View.CID_1, SITE.U_DPL_View.CID_2 " & _
             …
0
I had this question after viewing SQL How do I INSERT INTO two tables at once and pass the ID from the first to the second.
Dealing with two tables: product and product_volume.
I have a dynamic form, how do i insert into the product table and use the ID created to populate product_volume in one query (in a php function). I need to create multiple rows for 1 product ID which will have different volumes and prices.
0
Hello,

I want to add a csv file to database. There is a column in csv file called as merchant_id same as column in SQL database. I want to update the row in database if the "merchantid" exists in database or if it doesn't exist , we need to add that row from csv file .


Here  

"rs" contains all the columns of the CSV file and its values.

"rsAdd" contains all the columns of Database table(Merchants) and its values.

Set rs = GetRecordSet("C:\upload\new\" & TheNewFileName2)
    'Response.Write "No Errors"
    Set cn = Server.CreateObject("ADODB.Connection")
    cn.ConnectionString="DSN=REWC"
    cn.Open
    i = 0
		TheSql = "SELECT * FROM Merchants"
        Set rsAdd = Server.CreateObject("ADODB.RecordSet")
        rsAdd.CursorType=1
        rsAdd.LockType=3
        rsAdd.Open TheSql, cn
        
    While not rs.EOF
				
			rsAdd.AddNew            
            For Each col in rs.Fields				
                rsAdd(Replace(col.Name,"_","")) = col.Value
            Next		
			
        rsAdd.Update
        rsAdd.Move 0        
        rs.MoveNext
        i = i + 1
        If (i mod 100)=0 Or i=1 Then
            Response.Write i & ","
            If (i mod 2500) = 0 Then
                Response.Write "<br />"
            End If
        End If
    WEND
	
    rsAdd.Close
    Set rsAdd = Nothing	
	End If

Open in new window

0
Free Tool: Path Explorer
LVL 9
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Hello,

IIS consuming 99% CPU
I’m running in a SharePoint 2013 Farm
4 Servers Win 2012, SQL DB 2012 (Database, Front, Index, Workflow)
The IIS on the Front server consuming 99% of CPU

How to solve it ?
how to get more information what cussing this issue ?

Any suggestion ?

Regards,
David Dotan Sofer
0
Is there a SQL command that ignores errors and instructs it to move on to the next statement?

example:

begin
INSERT INTO OPERATION_MASTER (OPM_AUTO_KEY,SYSUR_AUTO_KEY,OPERATION_ID,DESCRIPTION,BER_THRESHOLD,FLAT_RATE,PARTS_FLAT_PRICE,LABOR_FLAT_PRICE,MISC_FLAT_PRICE,FLAT_RATE_LABOR,DEFAULT_REPAIR,TURN_AROUND_TIME,IN_CAPABILITIES,CONSOLIDATE,TEMPLATE_TYPE,YIELD_PERC,MAX_LOT_SIZE,PART_COST,LABOR_COST,FO_COST,VO_COST,OSV_COST,COSTING_METHOD,FIXED_UNIT_COST,SEQUENCE,ACTIVE_FLAG,MISC_COST,MAIN_ASSY,HISTORY_FLAG,LABOR_FIXED_COST,DO_COST_UPDATE,ACTIVE_VERSION,GLOBAL_DESIGN,CONVERTED,BATCH_JOB,SYSCM_AUTO_KEY,BLOCK_FLAG,BGM_AUTO_KEY,PNM_AUTO_KEY,WWT_AUTO_KEY) VALUES(G_OPM_AUTO_KEY.NEXTVAL,1,'233N3223-1027 STD BIC','233N3223-1027 STD BIC',0,'F',0,0,0,'F','F',0,'F','F','W',0,0,0,0,0,0,0,'A',0,0,'F',0,'F','F',0,'F','F','F','T','F',1,'F','',38249,'');
INSERT INTO OPERATION_MASTER (OPM_AUTO_KEY,SYSUR_AUTO_KEY,OPERATION_ID,DESCRIPTION,BER_THRESHOLD,FLAT_RATE,PARTS_FLAT_PRICE,LABOR_FLAT_PRICE,MISC_FLAT_PRICE,FLAT_RATE_LABOR,DEFAULT_REPAIR,TURN_AROUND_TIME,IN_CAPABILITIES,CONSOLIDATE,TEMPLATE_TYPE,YIELD_PERC,MAX_LOT_SIZE,PART_COST,LABOR_COST,FO_COST,VO_COST,OSV_COST,COSTING_METHOD,FIXED_UNIT_COST,SEQUENCE,ACTIVE_FLAG,MISC_COST,MAIN_ASSY,HISTORY_FLAG,LABOR_FIXED_COST,DO_COST_UPDATE,ACTIVE_VERSION,GLOBAL_DESIGN,CONVERTED,BATCH_JOB,SYSCM_AUTO_KEY,BLOCK_FLAG,BGM_AUTO_KEY,PNM_AUTO_KEY,WWT_AUTO_KEY) VALUES(G_OPM_AUTO_KEY.NEXTVAL,1,'233N3223-1016 STD BIC','233N3223-1016 STD 

Open in new window

0
Good Afternoon,

Is there someone that can tell me what is wrong with this command.

Thanks,

UPDATE A_TEMP_R INNER JOIN PROJET INNER JOIN
      CORRESPONDANCE ON PROJET.NO_PROJET = CORRESPONDANCE.No_Projet
             ON A_TEMP_R.NoOrdreTravail = CORRESPONDANCE.No_Job
                  SET A_TEMP_R.NoProjet = [CORRESPONDANCE].[No_Projet]
WHERE (((A_TEMP_R.Journee)>='8/1/2010'));
0
public DataSet GetNotStartedSearchCompliance()
        {
            SqlParameter[] param = new SqlParameter[7];
            param[0] = new SqlParameter("@UserName", UserName);
            param[1] = new SqlParameter("@ActID", ActID);
            param[2] = new SqlParameter("@UnitID", UnitID);
            param[3] = new SqlParameter("@Month", Month);
            param[4] = new SqlParameter("@Year", Year);
            param[5] = new SqlParameter("@Search", Search);
            param[6] = new SqlParameter("@CountryID", CountryID);
            return objHelper.GetDataSet("USP_GetNotStarted_Search_ComplianceAssessment", param);
        }
 
Getting the resultset from the procedure and having in a dataset and then directly binding to the Gridview.
When the resultset gives some 200/300 records ,where in the Gridview 10 records per page will be appeared and so we would get 20 pages(for 200 records) which is taking huge amount of time .Any alternate solution like binding only 10 records in the first page and clicking on the second record should fetch another 10 records which will increase our performance.
 
0
I am trying to use the Import 32 Bit Data Wizard as I need to connect to a Visual Fox Pro Database, is this Possible, I was able to do this using SQL 2008, we have since upgraded our SharePoint Infrastructure and need to be able to read that data in?

John
0
I need help improving the query potentially combining the query without using the UNION. And help improving the query execution

SELECT F.* 
FROM ( 
SELECT T.*,
 case WHEN T.availSeats = 1 THEN (SELECT (CONVERT(int, CONVERT(varchar, T.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, U.dateOfBirth, 112)))/10000 
 FROM users U 
 inner join sessionMap SM2 on SM2.userKey = U.userKey WHERE SM2.sessionKey = T.sessionKey) 
 WHEN T.repost = 1 THEN (SELECT (CONVERT(int, CONVERT(varchar, T.sessionStart, 112)) - CONVERT(int, CONVERT(varchar, U.dateOfBirth, 112)))/10000 
 FROM users U inner join sessionMap SM3 on SM3.userKey = U.userKey 
 WHERE SM3.sessionKey = T.sessionKey and SM3.repost = 1) ELSE 0 END as age 
 FROM ( 
 SELECT S.SessionKey, S.locationKey, S.locationKeyList, S.officeStateKey, SU.sessionStart, SU.sessionEnd, L.name AS locationName, L.address1, L.address2, L.city, L.state, L.zip, I.gender,
  ( SELECT TOP 1 UIL.licenseCd FROM userInstructorLicense UIL WHERE UIL.userKey = I.instructorKey and UIL.expirationdt is not NULL )as badgeNum,
   ( SELECT COUNT(1) FROM sessionMap SM WHERE S.sessionKey = SM.sessionKey ) AS seatCount, S.Seats, 
   IsNull(SU.btwSeatsOverride, S.Seats) - ( SELECT COUNT(1) FROM sessionMap SM WHERE S.sessionKey = SM.sessionKey and sm.userKey <> 0 ) AS availSeats,
    LS.btwScheduleThreshold, LS.availStateCode, ( SELECT top 1 isNull(SM.repost,0)
	 FROM SessionMap SM 
	 WHERE SM.sessionKey = S.sessionKey
	  ORDER BY isNull(SM.repost,0) desc ) as 

Open in new window

0
Team, I need some help diagnosing this issue, asked by one of our DBA's, server is part of a cluster, the cluster was manually failed over to the other node around the time this issue/error occurred?
errorSQL.png
Message
Login failed for user 'XXX\WINXXXX$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: XX.XXX.XX.XX] - Any ideas where/how I diagnose what the issue is here?

Many thanks
0
I asked this same question earlier and I thought it was working with the suggestion I received but I am not getting the data I need.

We moved data from a set of fields in a table to another set of fields in the same table.  

These are the old field names:
QWife1Child1 , QWife1Child2,  QWife1Child3
 QWife2Child1 , QWife2Child2,  QWife2Child3
 QHusb1Child1 , QHusb1Child2,  QHusb1Child3
 QHusb2Child1 , QHusb2Child2,  QHusb2Child3

These are the new field names:
Child1, Child2, Child3 ... Child10

If there is a name in the old field then we copy it to a new field.  Not every couple has a child together.  They could each have children from previous marriages.  Keeping that in mind Child1 doesn't always  = QWife1Child1.  Child1 could = QHusb2Child1.

I wrote the query to complete this task and I believe that it ran like I wanted.  I just need to make sure.  

I need to write a MSSQL query to make sure that every child from the old fields are listed in the new fields.  Below is what I have so far but it is not working correctly. As a note  the WHERE clause is only

I orignially used OR but I changed the 'or' to 'and' because when my results came back there were a lot of them.  I went into a few records and things matched.  There was nothing wrong.  That's when I changed the 'or' to 'and'.  Again I did random spot checks and I found two records that had data in QHusb1Child1 and Qhusb1Child2 but nothing in Qchild1 or Qchild2.  These are the types of records I am …
0
I am running a small subnet which  has several servers performing their varied tasks, one of which is a a PostGres SQL box. This small group has a management server which has two nics - one on the primary net and the other on the "internal".

The two servers in question we will call "Jump" and "PG"

Jump has an ip address of 10.10.10.10 for the primary net and an address of 192.168.20.10 for the internal net.
BG has an ip address of 192.168.20.40

My iptables rules are as follows:

    iptables -A INPUT -p tcp -m state --state NEW,ESTABLISHED -m tcp --dport 5432 -j ACCEPT

    iptables -A FORWARD -d 192.168.20.40/32 -p tcp -m tcp --dport 5432 -m comment --comment "Forward inbound PGAdmin traffic to postgres server" -j ACCEPT

     iptables -t nat -A PREROUTING -i eth0 -p tcp --dport 5432 -j DNAT --to-destination 192.168.20.40:543

     iptables -t nat -A POSTROUTING -s 192.168.26.44 -o eth0 -j MASQUERADE

PGAdmin is configured to address the Jump server on port 5432 and when attempting contact, the traffic arrives to the PG server and returns to my workstation but with a hitch.
     Watching tcpdump on the linux boxes and Wireshark on the Windows box, I am able to see the tcp stream initiate at my workstation travel through the jump box and arrive at the PG box.  When the packet has arrived at the PG box, the source is my workstation.
          IP 10.10.10.11.54564 > 192.168.20.40.5432: Flags [S]
     The PG box sends a reply to my workstation:
         …
0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

I have a query that shows many fields/columns.

There are 6 LEFT OUTER JOINS in this query.

Everything displays and calculates perfect... except for one field.  I have one field in one table that just shows NULL.

It is the only field in that table that I am trying to show.

What would be preventing the field from displaying?
0
A client changed his SQL server 2008 R2 SA Password. He doesn't remember what the old password was. Now his Access application doesn't work.

We have a good backup. Is there a set of files/ folder we can restore from backup to get the old PW back? I'd really not go through the pain of doing a complete system restore.

Thanks!
0
Greetings,

We have a virtual server with 48GB of RAM and 2 Processors. The performance according to SPOTLIGHT is terrible and the getting a lot of Latching and I/O alerts. What SQL Server Database configuration setting should I change to get rid of this problem or should I add more CPU Processors and more RAM? Please advise.
0
Hi
I am new to Microsoft sql server management studio tips 11.0.210 version connecting to the database using this client IDE.

How to search on the stored proc, function etc names.
what are tips in using it and best practices. please provide good links, resources on it
0
If you check a linked server within SQL Server, and go to the security tab, and check the security tab, if the “be made using this security context” is checked, and a username/password entered – does this mean any account with access to this instance can query the remote instance under the security context of the pre-populated remote login, or will it prompt them to answer that password each time?

My concern is some linked servers are set to “be made using this security context” with remote login credentials pre-populated. And in some cases the account used for access to the remote instance are quite powerful.

If you wanted to limit who can make use of this linked server to only a trusted few, how could you achieve this within the linked server security options?
0
I am currently using the following code to pull data directly from Outlook into access:

SELECT *
FROM Inbox IN 'C:\Temp'[Exchange 4.0;MAPILEVEL=me@mycompany.com|];

Open in new window


Instead of getting the email from my personal Inbox, however, I want this code to reference a team email box that is shared by colleagues so we can all use this tool.

The name of the account is different than just my email address so I think the syntax is more difficult to figure out:

PGHCC Support [JRDUS]

I tried to do different variations like this and I keep getting errors:

SELECT *
FROM Inbox IN 'C:\Temp'[Exchange 4.0;MAPILEVEL='PGHCC Support [JRDUS]'|];

Open in new window


I know it is probably the brackets that are causing the issue... how can I make sure access knows the brackets are part of the inbox name?

Thanks for your help.

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