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

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
Moving data to the cloud? Find out if you’re ready
LVL 2
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

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
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
Learn how to optimize MySQL for your business need
LVL 2
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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
Hi everyone,

I have 3 SQL servers 2014 Enterprise (Windows Servers 2012 R2) in a cluster AlwaysOn on 1 availability group and that are configured as follows:
 - Availability Group 1
     Replicas:
    - DB1 : primary server (Availability mode : synchronous commit, failover mode : automatic) located in DC1
    - DB2 : secondary server ( Availability mode : asynchronous commit, failover mode : manual) located in DC2
    - DB3 : secondary server (Availability mode : synchronous commit, failover mode : automatic) located in DC1

 - Availability databases
    - databaseNumber1, databaseNumber2, databaseNumber3

I have to shutdown my main server DB1 because I need to replace 1 memory module which is in a critical state.

In this architecture, there is a problem with the failover of the databaseNumber2.
If DB1 goes down, all databases except databaseNumber2 (certainly a problem of structure of the database) are moved to DB3.

So I will put DB3 in availibity mode : asynchronous, failover mode : manual and when I stop DB1, all databases will stay on this server and won't move to DB3.

My problem :
If DB1 crashes before the change of the memory module, all the databases except databaseNumber2 will move to DB3 (it is currently in synchronous automatic).
If it is impossible to restart DB1, what should I exactly need to do to make the databaseNumber2 working on DB3 ?

Thank you in advance for your help.
0
I have a table with the following columns - Id, Acct_Date, App_No, Seq_no. One Id can have several App_No. And one App_no for an Id can have several Seq_no.

I need to pull all Id, Acct_date, Max App_no and Max Seq_o for that App_no. Basically this will be one record per Id listing the maximum App No and the maximum Seq No for that application number

Thanks for your help
0
Dear Team,

I'm attempting to configure my first failover cluster in windows server 2016 for sql purposes across sites (different subnets). When the failover cluster goes through the validation in SITE A (DR-SQL1) the validation fails with the following errors:

 * Node DR-SQL2 is not reachable from node DR-SQL1. It is necessary that each cluster node can
    communicate each other cluster node by a minimum of one network path (though multiple paths
    are recommended to avoid a single point of failure). Please verify that existing networks
    are configured properly or add additional networks.
 * Node DR-SQL1 is not reachable from node DR-SQL2. It is necessary that each cluster node can
    communicate each other cluster node by a minimum of one network path (though multiple paths
    are recommended to avoid a single point of failure). Please verify that existing networks
    are configured properly or add additional networks.

Although when I run the same validation test from SITE B (DR-SQL2), they all pass just fine.

Any ideas where I should start looking, the firewall rules should allow all traffic to pass through between these 2 hosts.

Thank you.
0
Hello

I'm busy on a project where i have read only access to an oracle database.
Got a running webserver (LAMP) with a running OCI8 connection.
I'm trying to get a select query result from the following:

TableA
ID
DOC_ID
Date
Type

TableB
ID
DOC_ID (references tableA)
FILE_NAME
FILE_ID
FILE_TYPE

TableB can contain more then one result for the same DOC_ID.

The goal is to get the data from TableA appended with the results from TableB where the DOC_ID is the same and get them into a JSON format.
Looking like

{
	"data": [{
		"ID": "123456",
		"DOC_ID": "789456",
		"Date": "2/1/2016",
		"Type": "PI",
		"FILE_NAME": [
			"File1.pdf", "File2.docx"
		],
		"FILE_TYPE": [
			"PDF", "DOCX"
		],
		"FILE_ID": [
			"453215", "654687"
		]
	}, {
		"ID": "544569",
		"DOC_ID": "001223",
		"Date": "2/8/2016",
		"Type": "PI",
		"FILE_NAME": [
			"File1.txt"
		],
		"FILE_TYPE": [
			"TXT"
		],
		"FILE_ID": [
			"453215"
		]
	}, {
		"ID": "2225599",
		"DOC_ID": "0148966",
		"Date": "2/1/2017",
		"Type": "PI",
		"FILE_NAME": [
			"File1.xml", "File2.xml", "File3.xml"
		],
		"FILE_TYPE": [
			"XML", "XML", "XML"
		],
		"FILE_ID": [
			"00147788", "00114455", "00113210"
		]
	}]
}

Open in new window


Any advise on how to best go about this is welcome.
0
I'm trying to use VBA to insert my SQL Developer query into the command text for a connection to an Oracle database in excel. My SQL code is:

select * from
 (SELECT *
  FROM PS_JOB
  WHERE PS_JOB.EFFDT =
    (SELECT MAX(J.EFFDT)
    FROM PS_JOB J
    WHERE PS_JOB.EMPLID = J.EMPLID
    AND PS_JOB.EMPL_RCD = J.EMPL_RCD
    AND J.EFFDT        <= SYSDATE
    )
  AND PS_JOB.EFFSEQ =
    (SELECT MAX(J1.EFFSEQ)
    FROM PS_JOB J1
    WHERE PS_JOB.EMPLID = J1.EMPLID
    AND PS_JOB.EMPL_RCD = J1.EMPL_RCD
    AND J1.EFFDT        = PS_JOB.EFFDT
    )
  ) pj

but if I enter this into my VBA procedure, I get a syntax error.

Can someone advise how I can enter this code in VBA?
0
I need to call one procedure defined in service program from SQL. I prepared UDF also for this as below
 CREATE OR REPLACE  FUNCTION  &LIBRARY/SAMPLE1
   (IHSPC   CHAR(3),                          
    IHSPN   NUMERIC(3,0) ,                    
    ICHARGE# NUMERIC(9,0),                    
    ISERVICEDATE NUMERIC(8,0))                
                                               
 RETURNS TABLE                                
   (OBILLINGDESC  CHAR(24) ,                  
    OTOTALAMOUNT NUMERIC(11,2),                
    OHOSPAMOUNT NUMERIC(11,2),                
    OHOSPPERCENT NUMERIC(3,2),                
    OHCPCS  CHAR(8),      
  ODELETEFLG CHAR(1),                      
  OMODIFIER1  CHAR(2),                    
  OBILLFLAG   CHAR(1),                    
  OPRICEEFFE  NUMERIC(8,0),                
  OPRICEFLAG  CHAR(1) ,                    
  OHCPCSEFFEC NUMERIC(8,0))                
                                           
 LANGUAGE RPGLE                            
 SPECIFIC NMHHPLIBR/SAMPLE1                
 NOT DETERMINISTIC                        
 CALLED ON NULL INPUT                      
 NOT FENCED                                
 ALLOW PARALLEL                            
 EXTERNAL NAME 'PALIBR/NC0350(GETCDMINFO)';

But when i called this from STRSQL then its keep executing without showing any result.
0
Hi,
I have created user and given permssion to  db_owner for Test database.
But user complaining user can't create stored procedure or View.
I have given grant using following command but still user can't create store procedure.
What iam missing

use [App_Test]
GRANT EXECUTE ON SCHEMA::[dbo] TO [user1]
GRANT EXECUTE ON DATABASE:: App_Test  TO [user1]

USE  [App_Test]
GRANT CREATE PROCEDURE TO [user1]    
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::dbo TO [user1]
0
Windows Server 2016: All you need to know
LVL 1
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Hi All,

Current SQL memory settings :
Min Memory: 64000 MB
Max Memory setting : 216000 MB.
User DB size: 540 GB
In this DB server we are running SSIS & SSIS as well.

1. On a server running SQL Server 2014 with 256 GB RAM what is best practice for setting the page file size?
2. Can we resize page file as minimum 16 MB & 2000 MB?
3. If we resize page file as mentioned above, is there any impact on SQL Server performance?

The current page file settings are given below.
page file
0
Hello All,

We have SQL 2014 with Ent edition and our DB size is 340 GB. Can you please review and suggest below SQL memory settings are correct or do I need to change min or max memory settings.

Physical memory : 256GB
Min memory: 64000 MB
Max memory: 216000

We have dedicated DB (VM machine) and on same DB we are running SSIS, SSRS.

In DB server-->task manager-->we always see sql server management studio.exe, the memory utilization is 98-99%. Is there an way to reduce this value by memory settings?

Thanks in advance.
0
I have an MS SQL database that I created using Microsoft SQL Server Management Studio 17. I went ahead and uploaded it to my webhost provider and even made an aspx.net bv.net page to read back one of the tables just to be sure I had the right connection to the database. All that works but now to save me coding time I wanted to create a GridView to display, delete and update specific records to the database.

I start the process of dragging a GridView to the aspx page in Microsoft Visual Studio 2017 then it asks for me to choose a datasource so I choose SQL and the data source defaults to SqlDataSource1 which I modify to be SqlDataSource2018 because I already have another 7 year old database that uses the name SqlDataSource1

The next step is to to specify the Data Connection which is available to me on the drop down menu.
This then makes the "Next" button at the bottom of the dialog box be available for clicking.

This where I run into a brick wall with the same message everytime.
I've embedded an image that displays the error message and I've also attached the file in case you cant see the embedded file (im new at this...)
error message
From what I can decipher the computer I think is stuck in trying to find the database locally within my computer but it needs to go to the database server's i.p. address!
Any suggestions to get me and my computer out of this rut?
Thank you for your help!
0
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
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

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.