Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

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

Hey guys,

Need some help with my reports. I only know  a little with SQL since I'm currently in the networking field.
I have this problem with SSRS - Report Builder where I have a column name PublicIP which have

sample values :
10.10.1.232 (saaskhsk2121)
122.233.55.42
32.55.10.12 (sasa)

Now, i need to remove the characters with parenthesis () and retain only the IP address

I tried below command and it perfectly removed the parenthesis and retain the IP address
=LEFT(Fields!PublicIP.Value, InStrRev(Fields!PublicIP.Value, "(") -1)

sample result :
10.10.1.232


Now the problem lies in my iif statement since there are also values which doesn't have any parenthesis () and doesn't need to be trimmed down.
I tried this expression:

=Iif((Fields!PublicIP.Value Like "*(*") ,LEFT(Fields!PublicIP.Value, InStrRev(Fields!PublicIP.Value, "(") -1),Fields!PublicIP.Value)

Result:
10.10.1.232
#Error
32.55.10.12

As you can see, the result shows #Error for those values with no parenthesis.
Need advise. Thanks

Lester
0
Microsoft Certification Exam 74-409
LVL 1
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

I have been using Access for a very long time and have some, but not a lot of experience working with SQl Server as a backend.

I am working on a conversion of legacy system data using Access 2013.  I had all of my conversion logic written and tested using a subset of the data provided by the client.  However, when I got the full set of data problems began.  I am reading a fixed length .txt file in my Access application and writing the output to a SQL table.  The input file has over 200k records but my logic will never get past processing 35,000 or so.  The record size of the input record is over, 14,000 characters.  One of the fields is 14,000 characters long.

After I got the 3183 I tried including BeginTrans and CommitTrans in the loop but the 3183 is still thrown after about 35,000 records.

The exact message I am getting is:
Error
MY system has 32GB of Ram and over 80GB of disk space free.  My Accdb is only about 60MB when I get this message.

In all my many years of working with Access I have never seen this error before and don't know how to resolve this situation to be able to process the entire file.

Here is my code:
Public Sub readFixedLengthTextFile()

'On Error Resume Next

clearSQLTable_Sproc "SQCS_CostFile_Import"
' output file
Set db = getCurrentDbC
Dim rsOut As DAO.Recordset
Set rsOut = db.OpenRecordset("SQCS_CostFile_Import", dbOpenDynaset, dbSeeChanges)


Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText 

Open in new window

0
We have a bit of an odd issue.  We have a 2012 server that is in a sql cluster.  Most of the drives are functioning normally; however we have found there are four drives that the local permissions do not seem to be taking affect and cannot be accessed when locally in an admin’s session on the server itself.  
First some background, all of these drives are iSCSI connected back to a SAN, they are all in the SQL cluster and so far everything appears to be operating from a production standpoint.  SQL does not appear to have issues accessing the data contained in the drives, and the logs show no errors.    
Now the issue, it seems the four drives that have a share on them, when logged in as an admin account on the server, those drives are unable to be accessed locally within the admin’s session.  The share access works fine using the UNC path.  
For example, I login under admin1 through RDP, attempt to open up the X: drive, which is an iSCSI attached drive to the server, and I get an access denied error message.  However, if I use that same account to access the share (or even administrative $ share) I can get in fine.  Admin1 is a member of both the domain admins, and the local admins on the server.  If I look at the security properties of drive X: (after being prompted to grant it access), both domain admins and local admins have full control.  So it I should not get an access denied message.
I did find if I grant the user account explicit full control it resolves the issue,…
0
Hi,

I have a SQL database $SQLServer in which a table "db_rdsstatistics" to add rds sessions (number of sessions and timestamp). Goal is to see at hour interval how many users there were.

The querying of the database via PS works (with input below, I also have data to input = $NumberofSessions and $Date).

On SQL (allow nulls = not selected) this is the design:
NumberOfusers int
Date datetime2(7)

---
This is what I would like to get into the database, how do I do this?

$UserSessions = Get-RDUserSession -ConnectionBroker $Broker
$NumberOfUsersessions = $UserSessions.count
$Date = get-date -Format 'dd-MM-yyy HH:mm'

---


  #region sql query
  $SQLServer = “oursqlserver”
  $Database = “db_rdsstatistics”

  $connectionString = “Server=$SQLServer;Database=$database;Integrated Security=SSPI;”
  $connection = New-Object System.Data.SqlClient.SqlConnection
  $connection.ConnectionString = $connectionString
  $connection.Open()

 $query =   "SELECT TOP 1000 [NumberOfUsers]
      ,[Date]
  FROM [db_rdsstatistics].[dbo].[RDS_NumberOfUsers]
  "

  $command = $connection.CreateCommand()
  $command.CommandText = $query


  #Run the query
  $command = $connection.CreateCommand()
  $command.CommandText = $query
  $reader = $command.ExecuteReader()
  $table = new-object “System.Data.DataTable”
  $table.Load($reader)
 
  $connection.Close()

  $table
0
Hi,

I have a requirement to make a manual work to automation. Here is the complete details about the automation plan.

1. Need to prepare a Java script by combining the SQL db as back end. These JavaScript can be scheduled in a 15 minutes once.
Once this Job got scheduled, it will run the java script and get the details from the SQL database(Select script which is ready with me!)
2. My condition in the java code should if the customer Which we are passing having any values in that time frame, it should pick and prepare a spreadsheet if there is no transactions it can be ignored for that run.
3. Also another requirement is to pull the logs from the servers based on the customer ID passed in the code. If there is a way to do that. We can implement the same.

Please share your thoughts and suggestions if any.

Thanks
Venkatesh.
0
Dear Experts,

I have some queries which needs to be created as a Stored Procedure but also by that changing to contain the database information after the FROM clause.

Briefly for example query in such format:
SELECT TOP 10
	AD.LFA1.LIFNR
FROM
	AD.LFA1

Open in new window


will be need to be re-formatted as (SAP_DATA added so the database name):

SELECT TOP 10
	AD.LFA1.LIFNR
FROM
	SAP_DATA.AD.LFA1

Open in new window


Could you please advise regarding the best practice, how to add database information to the FROM clause relatively easily?

Actually my only idea is to replace text 'AD.' with 'SAP_DATA.AD.'  in Excel in the code

Thanks in advance,
0
Hi ,
i have installed sccm on our one site and have created a sccm server for our another site which is connected with different domain, trusted relationship though.
can anyone please tell me if i need to configure and install the second site same as my first site with all the pre-requisites and sql in it??
0
HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row;
            for(int i=1; i<=sheet.getLastRowNum(); i++){
                row =  sheet.getRow(i);
                int id = (int) row.getCell(0).getNumericCellValue();
                String name = row.getCell(1).getStringCellValue();
                String address = row.getCell(2).getStringCellValue();
                String sql = "INSERT INTO test VALUES('"+id+"','"+name+"','"+address+"')";
                pstm = (PreparedStatement) con.prepareStatement(sql);
                pstm.execute();
                System.out.println("Import rows "+i);
            }
i got error in declarating id, name and address
0
Hi Team,

MS sql server is showing CPU 98% and Memory 99%. This is sql server 2016.

I didn't some testing. When i disable application user, the cpu and memory is coming down to just 5-10%, but at the moment when i enable applicaiton user, within 5-10 mins CPU and going to 99%. when when i run SP_who2, i found lot of blocking.
Please go thourgh the screenhot and suggest us, how to resolve.

els01blocking.JPG
0
Hi Experts.

I'm having a sql file about 3.5GB with some tables about 7 millions records , and a Ubuntu 16.04 server configured to run on a VPS with 8 vCPU, 32GB RAM. (RAM Disk)
The RAM Disk mount command is:
# sudo mount -t tmpfs -o size=25G tmpfs /opt

When importing that sql file using default MySQL configuration, its took about 3 hours and a half to finish.
After using this optimized MySQL configuration, it's took only about 20 minutes:
innodb_buffer_pool_size = 8G
innodb_log_buffer_size = 256M
innodb_log_file_size = 1G
innodb_write_io_threads = 16
innodb_flush_log_at_trx_commit = 0

When importing, import speed goes around 1MiB/s to 7MiB/s. In the end, the average importing speed with this new parameters is 2.5 MiB/s.
My question is, which parameter should i optimize to increase average importing speed, in order to reduce import time from 20 minutes to about 10-12 minutes?

Hope to see experts's answers soon, thank you.
0
Restore individual SQL databases with ease
LVL 1
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Hello,

I have a need to select two columns from a list of views that may change over time, but the
view names all have the same 5 characters - FType.  Below is the code I'm trying to use, but
I get an error stating that the @varTN variable needs to be declared.  Can someone provide
a few hints on what I need to change to make this work - or perhaps suggest an alternate approach?

DECLARE @IDTable TABLE
(
  FID int ,
  IIN int
)
DECLARE @varTN varchar(50);
DECLARE TestCur CURSOR FOR select name from sys.Views where name like 'FType%';
OPEN TestCur
FETCH NEXT FROM TestCur INTO @varTN;
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @IDTable(FID,IIN) Select ID_A,ID_B from @varTN
    FETCH NEXT FROM TestCur INTO @varTN;
END
SELECT * FROM @IDTable

Thanks,
Carl
0
The SQL below detects dupes or records with the same serial & lian numbers.

How can I add a remarks field - the serial & lian number would be embedded with other comments:
Remarks field example: 13 HP motor 2 week for delivery 45515-6656 (serial-lian)

Logic: If the serial & lian number match another record in the remarks field add to the dupecount

SELECT * , (select count(*) 
FROM    materials dupe
where  dupe.Serial = t.Serial
and    dupe.lian = t.lian
		   
		) AS DupeCount
from   materials t

Open in new window

0
Hello,

I have a command button on a MS Access form.  I want the click event to run an SQL query and show the results in an excel spreadsheet.  I want the user to have the option of saving the spreadsheet or just closing the spreadsheet out.  Is there a way to do this every method I've tried prompts you to save the spreadsheet first to a file location.  Example:

DoCmd.OutputTo objecttype:=acOutputQuery, objectname:="Parent and Child Benefit Rules", outputformat:=acFormatXLS

Thanks
0
Hi

Currently I have the following columns
Analog1
Analog2
Analog1Raw
Analog2Raw

I now need to add upto 60 more analog columns, and have decided to switch to one column to store the analog data as

[    
    {
        "an1":2103,
        "an1r":2220
    },

    {
        "an2":5999,
        "an2r":6000
    },
etc..
]

Open in new window


The data is only ever queried by 1 report, but I need the report to be very fast

Therefore I need help with the following
1. what data type should i store the data as in sql server
2. is sql any good at querying this type of JSON data direct

I'll want to do stuff like
select jsonColumn.an1 
where mydate between 2 dates

Open in new window


i'm using .net framework 4.5 so can do other logic here to pull out data, but was hoping to query it directly if its performant

I'm using sql server 2014 on live, 2016 on dev, and we are planning an upgrade to 2017 on both environments.
0
I am wondering what happens to the SQL full backup and differential backup relationship in one backup product if another backup product is also doing full backups on a daily basis.

We are using one backup solution to protect the sql server locally on a daily basis and this solution performs a full SQL backup.  We also have a second backup solution that backups up the SQL DB offsite to the cloud and it performs a full backup once a week and a differential backup everyday.  What happens to the Full backup Differential backup relationship of the cloud solution if another solution is creating full backups.  Which system keeps track of the backup LSN numbers, is it SQL or the backup solutions?

For example if I try to do a restore of the cloud backup solution will the differential backup restore work or will it be linked to the on premise full backup as apposed to the cloud full backup?
0
I have another question   here     open explaining the situation.

That question is asking how to transfer this table directly to SQL server using ms Access 2013.  At this point there has been no resolution so I am going to asking the question specific to SQL Server.

How can I import this very large, fixed length field .txt file directly into a SQL Server table.

This is the layout of the table:

CREATE TABLE [dbo].[SQCS_CostFile_Import](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CostName] [nvarchar](30) NULL,
	[MuniCode] [nvarchar](4) NULL,
	[LotBlock] [nvarchar](17) NULL,
	[TaxType] [nvarchar](1) NULL,
	[MuniCode02] [nvarchar](4) NULL,
	[ControlNumber] [nvarchar](7) NULL,
	[TaxType02] [nvarchar](1) NULL,
	[CostDetail01_100] [ntext] NULL,
	[GRBNumnber] [nvarchar](30) NULL,
	[GDNumber] [nvarchar](30) NULL,
	[Remarks] [nvarchar](76) NULL,
	[upsize_ts] [timestamp] NULL,
 CONSTRAINT [aaaaaSQCS_CostFile_Import_PK] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Open in new window


And this is the layout of each record in the .txt file
                                 Col  Lgth
Cost Name               1   30      ShortText
MuniCode               31  4       ShortText
LotBlock                  35  17     ShortText
TaxType                  52  1       ShortText
MuniCode02          54   4      ShortText
ControlNumber    57    7     ShortText
TaxType02             64   1      ShortText
CostDetail              65   14000          Long Text
GRBNumber    14065  30     ShortText
GDNumber      14095  30      SHortText
Remarks          14125   75      ShortText

Can someone explain how to  import the file 'I:\My Documents\Access_Databases\JordanDelinq\TestDirectory\CostSheet\TQCSFILE.txt' into the SQL table?

This is someting that will have to be done more than once so it would be best if everything needed was contained in a Stored Procedure.
0
Queries that use a join always baffle me so I could use some expert advice. Thanks in advance...

How to I write a ColdFusion query where:

I have one database called "Visitors"

I have 2 tables in that database:
"Users" is a table with my entire membership listing
"EmailSent" is a table listing every newsletter sent to each member

The common field is "UserID"

Within EmailSent is a field called "TemplateUsed" which is a different template for each version of the newsletter.

I need a query that will output the UserID's of those members who did not yet receive a particular template called "ImportedCareerCoach5.cfm"

But they may have received other newsletters.

Hope that makes sense.

 I'm running CF 10 and MS SQL
0
We are using MS SQL 2012 (Azure). When our web application saves text into an nvarchar field (Max) if the user has a Korean keyboard the Korean characters are replaced by question marks.   ???

Any idea how to prevent this in order to accept Korean characters?
0
I just installed SQL Server 2016 Developer on a Windows 10 Professional laptop.  I set it up to use Windows authentication.  I'm having trouble with what seems to be very simple DB permissions.

I am the administrator for the laptop and have sa rights for the instance of SQL Server.  

Under the SECURITY > Logins tree of the instance my login is shown (i.e. MYLAPTOP\CalBob).  If I right-click on the properties of my login I have user mappings to the db (i.e. ANALYTICS) I need access to.  However, when I go to the db, ANALYTICS  >  Security, my User name/account is not listed, and when I try to add it, I get one of two error messages telling me:

1. If I try to add MYLAPTOP\CalBob I get message saying it is invalid because it contains invalid characters.
2. If I try  to add CalBob it says CalBob is an invalid login or I don't have permissions.

Ultimately the problem is if I want to give myself permissions to a table in that database I'm told the user does not exist.  For example.

GRANT SELECT on tablename1 TO CalBob

I receive an error indicating the user doesn't exist or I don't have permissions.  And I understand that CalBob doesn't exist as a User in the database, but I can't add CalBob either.

This seems simple but I'm obviously missing something.  Any ideas?

Thanks.
0
Get quick recovery of individual SharePoint items
LVL 1
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

We currently have a DPM server with version 4.2.1553.0 installed. We are trying to install rollup 12 version 4.2.1589.0. When attempting the install, initially we had problems with services not stopping during install and it would return 'Microsoft System Center 2012 R2 Data Protection Manager Update - KB3209592'. It would then rollback however we seem to get round this by allowing permissions to the C:\Program Files\Microsoft System Center 2012 R2\DPM\DPM\agents\RA\4.2.1553.0. After adding these permissions the services could then be stopped and started successfully by the installation.

We have now hit another error during the install when the install is on the stage 'executing sql strings', when reviewing the log file the following error appears

MSI (s) (44:D4) [10:01:12:505]: Executing op: ActionStart(Name=ExecuteSqlStrings,Description=Executing SQL Strings,)
MSI (s) (44:D4) [10:01:12:573]: Executing op: CustomActionSchedule(Action=ExecuteSqlStrings,ActionType=25601,Source=BinaryData,Target=**********,CustomActionData=**********)
MSI (s) (44:B0) [10:01:12:575]: Invoking remote custom action. DLL: C:\Windows\Installer\MSIFAE4.tmp, Entrypoint: ExecuteSqlStrings
MSI (s) (44!28) [10:01:29:150]: Transforming table Error.

MSI (s) (44!28) [10:01:29:150]: Transforming table Error.

MSI (s) (44!28) [10:01:29:150]: Note: 1: 2262 2: Error 3: -2147287038
MSI (s) (44!28) [10:01:29:150]: Transforming table Error.

MSI (s) (44!28) [10:01:29:151]: Transforming table …
0
Windows Server 2012 R2
SQL server 2012

We are moving our data systems from SQL 2008 to 2012.  In Sql 2008 (on Windows 2008) I import:

- .dbf IV files using the Visual FoxPro driver
- Access 2007 files using the Microsoft Office 12.0 Access Database OLE DB Provider

Testing on the new Sql Server 2012 I do not see either of these 2 drivers in the Data Source list (Import wizard).  

On our Windows 2012 R2 server, I downloaded the Visual FoxPro driver and installed it.  I can now see it on the list, but as soon as I choose "Properties", the wizard crashes and asks to be closed

For Access, I downloaded and installed the AccessDatabaseEngine driver for both 2007 and 2010 (32 bit).  The install seemed to go fine and I see both in "Add Remove programs", but I DO NOT see it in the SQL Server Data Source list (Import wizard).

HELP!  As I understand, if I can get the "Microsoft Office 12.0 Access Database OLE DB Provider" to show properly, I might be able to import both my Access 2007 tables AND my dBase 4 files.  Why can't I see it on my Import wizard Data Source list?

Max
0
When you host a website that pulls data from a SQL server, I believe you have to create an account in IIS that has the privileges to access the SQL server.

If this is correct, where in IIS can I find this information?
0
I'm getting a connection error when trying to render a report using SSRS 2016 Report Manager.  Here is my setup:

For testing purposes, I installed SQL Server 2016 Developer on a Windows 10 Professional laptop.  As part of the SQL Server installation I installed SSRS 2016, on the same laptop.  Then I configured SSRS 2016 and everything seems good to that point.

I created a new data source (DataSource1) and a test report in Visual Studio.  The report previews perfectly while in Visual Studio, so I deployed both the data source and the report to the Report Manager.  

I see the report and data source when I open Report Manager web page.  However, when I run the report I get the following error:

"...Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection).  A network-related or instance-specific error occurred while establishing a connection to SQL Server.  The server was not found or was not accessible..."


When I test the data source in Visual Studio it works.  When I test the data source from the Report Manager web page I get the same error as above.  

It seems simple enough but I can't figure out what the issue is.  I am using Windows authentication and I'm the admin for the laptop as well as the SQL instance.

Any help would be appreciated.  Thanks.
0
We had a SQL admin to leave the company, and he created a SQL server for our hosted Help Desk system.

We are trying to get into the SQL server with the SA account, but no one knows the password.

Is there a way to reset the SA password, without having an account to access the SQL server?
0
I have 56 SSIS reports that are setup to use the Excel connection manager to pick up file extracts that are xls file format.
The file format of the extracts were changed to xlsx format and the reports have not updated with current data since.
I inherited these reports and time does not permit re-writing and redeploying these reports.
Any help with this issues will be much appreciated.
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.