Microsoft SQL Server

158K

Solutions

187

Articles & Videos

48K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

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

Sign up to Post

Hello experts,


I have a table with id, k, v (id is is, k is key, and v is value)

like :

id           k            v                        <--- columns in a select * fron tempTable
------------------------
A            k1           1
A            k2           2
A            k3           3
B            k1           1
B            k2           2
B            k3           3

SO what I want to do is 

Return the data as a crosstable/PIVOR/something else:

id         k1       k2       k3
-------------------------------------     
A          1         2       3
B          1         2       3

Open in new window


PIVOT not working on earlier versions of oracle database and I want a simple still reliable solution.
I really don't want to use ms access to make a crosstable for this :D
0
PeopleSoft Has Never Been Easier
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

I am trying to import a file that is using a "Unit Separator" ... ASCII Value 31 ... as its delimiter.  I am struggling with how to specify that value in a format file.  I have tried many different values but none seem to work.  Anyone run into this before and have a solution?  My last attempt is shown below:


11.0
10
1       SQLCHAR             2       50      "\0x1F"   1     ContextID                                        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             2       50      "\0x1F"   2     ContextName                                      SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             2       50      "\0x1F"   3     ContextParentContextID                           SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             2       50      "\0x1F"   4     ClientRecordNumberID                             SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             2       50      "\0x1F"   5     PatientID                                        SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR             2       75      "\0x1F"   6     CustomFieldName                                  SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR             2       50      "\0x1F"   7     CustomFieldType                                  SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR             2       125     "\0x1F"   8     CustomFieldValue                                 SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR             2       50      "\0x1F"   9     …
0
I have been asked to migrate/update an Access/SQL Application so it can run in a VM Environment on a true SQL Server; I have a general understanding to get started, but this is my first VM project, so step one is to plan for the unexpected. I request that you treat me as a novice when making suggestions.

Rather than write a novel about the function of this Access/SQL Application and to minimize suppositions, I would rather respond to questions about it.

To start this off, here is the current configuration ...
Standalone Workstation
Windows 10 64 Bit
Access Pro Plus 2013 (Front End)
SQL 2016 Express (Back End)

What would be the best approach and what pitfalls should be anticipated?
0
Hi,

I've just been told I'm now in charge of and managing two SQL databases and have never used SQL before.

One of them we can remove as it is a backup and is no longer required. How do I safely back this up and then delete it so we can, if required, restore it?

Also: The db has a total size of 6,927Mb. 6021Mb of which is transaction log space. How do I go about reducing this log space? We are nowhere near using up the space allocated to logs but we are short on disk space on the server.

I've tried backing it up by doing a full backup and also a transaction log backup but no reduction in transaction log space.

So I'm looking for 3 things:

1) Reducing transaction log space
2) How to create a full backup that we can restore from
3) How to take the db offline and then delete it from Studio (assume Tasks > Offline, then delete)

Thanks and apologies for the noob questions but this has literally landed on my desk.
0
can any one write a code for me in php... for file uploding and files uploading
0
I want to return the Top 5 vCount for/in each vGroup in this query, but I'm having trouble figuring out where to put it ?

		/****** Region ******/		
		SELECT	vGroup, vType, count(*) AS vCount
		FROM (
		SELECT        'Region visits' AS vGroup, Districts.name AS vType
		FROM             (v_Visits INNER JOIN Customers ON v_Visits.customerId = Customers.id) INNER JOIN Districts ON Customers.districtId = Districts.id
		WHERE        visitTime  between @periodFrom and dateadd(ms, -3, dateadd(day, 1, cast(@periodTo as datetime))) AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
		) resSub
		GROUP BY  vGroup, vType

		/****** Brand ******/
		UNION ALL
		SELECT	vGroup, vType, count(*) AS vCount
		FROM(
		SELECT        'Brand visits' AS VGroup, Products.brandName as vType
		FROM            v_Visits INNER JOIN Visits_r_Products ON v_Visits.id = Visits_r_Products.visitId INNER JOIN Products ON Visits_r_Products.productId = Products.id
		WHERE        visitTime  between @periodFrom and dateadd(ms, -3, dateadd(day, 1, cast(@periodTo as datetime))) AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
		) resSub
		GROUP BY  vGroup, vType

Open in new window

0
Hi EE,

I am sure this question would have been asked and answered here. But here is my requirements.

Drop all foreign key constraints in my db
Truncate all tables

Recreate all foreign key constraints. I have been trying to use https://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx but somehow i am unable to make it work.

Can you please help?

regards
0
Since we upgraded to SQL 2008 from SQL 2000 i have faced the following problem: I can run the VB Script that runs a DTS from the SQL Server, however running the same VB Script to to execute the DTS from a client PC has the above error message. I have tried, installing MS SQL studio on the client connecting to the DB instance and tried running the VB Script but it still wont work. im lost, please help

Regards,
Grand
0
Are there any list of requirements that must be in place for SSIS to show up in the program application list below. I would assume either a database must be present or is it a stand alone product in the list provide that I can point to a specific database to work on.

One of the members said Development licenses. I cannot afford one at the moment is there a trial or a way to get a free temporary license?
I think I just need as simple as I can get. I have weeks before I would start if I do get this opportunity. I think it will be time enough for me to catch up and get back into the swing of things. Reporting is what I do anyway. This is just a tool to clean up the data enhance performance and produce a result set to report off of.
SQL-Server-2016.jpg
0
I was thinking maybe Northwinds DB but there may be better. Any thoughts or links appreciated.
0
Major Incident Management Communications
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Currently we windows server 2008 R enterprise cluster. I would like to replace them with two new nodes Windows server 2016 standard. I have tried to added them to the existing cluster but failed.

Test result:
Validate operating system version ..warning
Network... failed
System configuration... failed
List Network Metric Order... failed
Walidate require services... failed

Regards
0
Dear Experts,

My client is using Symantec Backup Exec 2012

They have three servers to do the backup set at Highest (SQL) , High (Exchange) and Medium (Apps)

All servers are to backup at 11pm everyday. Mon to Thurs (incremental), Friday (Full)

The backup for Exchange and Apps server are successful.

But the SQL server is still running the backup but there is no action.
CKG-Backup1.JPG
CKG-Backup2.JPG
CKG-Backup3.JPG
CKG-Backup4.JPG
CKG-Backup5.JPG
0
Hi Experts,

I had this code working fine in a DSN based connection.
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "Provider=MSDASQL;DSN=PlacementNP;Initial Catalog=PlacementNP;User Id=MyUser;Password=MyPWD"
objConn.Open

Open in new window


However now that we changed to a DSN-less connection I can't get it to work, tried the following
Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "ODBC;DRIVER=sql server;Server=tcp:sqlserver1\exp08;Database=PlacementNP;UId=MyUser;PWD=MyPWD;Trusted_Connection=No"
objConn.Open

Open in new window

And I get the attached error, when all the credentials are still the same.

Any idea what's wrong here?
Untitled.png
0
I have a person_name field with the value of eg, Smith, John but search method does not work using like. Is there any method?
I used this query but nothing is returned

I'm using this store proc with parameter @pname.

      SELECT personID,PERSONNAME FROM [dbo].[VW_UNION]
      WHERE ,PERSONNAME  LIKE UPPER(RTRIM(LTrim(@pname + '%')))
Because, the person name is split by "," , it there to execute a query and look for the first string before the  first ','  ?
0
SQL server sends an email to a customer using Office365.
Message Trace from Microsoft shows the message being accepted by Google.
Google sends a message to the sender saying the message is being delayed.
After days of delays the message fails with this error

write error: generic::failed_precondition: write error (0): error
0
In MS Access 2010 a user is encountering the message "ODBC Call Failed" when trying to preview a report. The data source for the report is a linked table from SQL Server 2008 R2. Another user using this same machine can execute the report preview without any issue. Guessing here that there is a SQL Server security issue with the user that is having difficulty. However I can find no obvious difference in the SQL Security between the two. The ODBC bridge is set up the same for both users as a Trusted Connection. Any idea what the issue might be here ? Not sure how to troubleshoot this. Thanks.
0
Am a bit clueless as to how to code this, please help.
we need to first convert the special characters to code before sending out some email address to a third party vendor.
The characters that need to be encoded and their translations are as follows:
Character      Encoded
!      %21
#      %23
$      %24
%      %25
&      %26
'      %27
*      %2A
+      %2B
/      %2F
=      %3D
?      %3F
^      %5E
`      %60
{      %7B
\      %7C
}      %7D
However, there might be more characters that need encoding. Instead of replacing these characters manually, a better method would be to replace all characters that are not 0-9, a-z, or A-Z with their hex value. You can retrieve this value using SELECT CONVERT(VARBINARY(MAX), '!') = 0x21 ( = %21), for example.
with an email address such as email+address@domain.com, the + becomes a space character when clicking on the URL.
Instead, the + (and other valid characters) needs to be encoded to properly pass the email address to the page.
In the above example, the + should be %2B, making it email%2Baddress@domain.com.
0
Hi Expert,

One of my user requested me to access on the integration services and i gave him access on MSDB database db_ssisadmin  but he is getting the error. Error is attached. I suggested him to run SSMS as an administrator but didn't work for him.

Please help!
_error.jpg
0
I am new to SQL server, I have a Stored procedure that returning only almost 1733 rows but taking long time to execute.Please find the stored procedure below and help me to get increase the performance I am using MS SQL Server 2012 I am unable to attache test result and execution plan
TEST_SP.txt
0
What is SQL Server and how does it work?
LVL 1
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

I am attempting to do an Insert Into sql statement in access.  Done as a select statement, it pulls up the expected result, done as an Insert Into, it returns an error "Record Deleted".

Thoughts?

Select: (works)
SELECT dbo_tblMonthlyServiceFees.SeqTransactionID, getwinuser() AS Expr1, dbo_tblMonthlyServiceFees.BatchDateTime, dbo_tblAllLoans.SeqLoanNumber, dbo_tblMonthlyServiceFees.GLoanNumber, dbo_tblMonthlyServiceFees.LoanDate, dbo_tblMonthlyServiceFees.PLLoanNumber, "DSF Houston" AS AllocTo, 1 AS RankFeePct, 1 AS AdjfeePct, 0 AS MCredit, "170101  104404" AS OfcAcctCode, dbo_tblMonthlyServiceFees.ServiceFeeAmount, dbo_tblMonthlyServiceFees.ServiceFeeRate, dbo_tblMonthlyServiceFees.FreddieMacServiceFeeRate, dbo_tblMonthlyServiceFees.ReducedServiceFeeRatio, dbo_tblMonthlyServiceFees.ReducedServiceFeeAmount, dbo_tblMonthlyServiceFees.TransactionDate, dbo_tblMonthlyServiceFees.PaymentDueDate, dbo_tblMonthlyServiceFees.PaymentNumber, dbo_tblMonthlyServiceFees.InterestRate, 0 AS ProdCalcFee, 0 AS ProdAdjCalc, dbo_tblMonthlyServiceFees.ServiceFeeAmount, dbo_tblMonthlyServiceFees.ServiceFeeAmount, dbo_tblAllLoans.CreatedByUser, dbo_tblAllLoans.CreatedDate, 1 AS NoFeeSplit, 1 AS NoProdAlloc
FROM dbo_tblMonthlyServiceFees LEFT JOIN dbo_tblAllLoans ON dbo_tblMonthlyServiceFees.PLLoanNumber = dbo_tblAllLoans.PLLoanNumber
WHERE (((dbo_tblMonthlyServiceFees.BatchDateTime)=[Forms]![frmImport]![txtBatchDateTime]) AND ((dbo_tblAllLoans.SeqLoanNumber) Is Null));

Open in new window


Insert: (Returns error)

Open in new window

0
Hi all,

I am very new to working on XML data or files. I need to load data from below XML to a table in sql server. But i am unable to even parse it, i have tried few different methods like xquery and OpenXML but no luck. I am getting blank columns, i think i am not giving proper nodes to navigate to data. I am struggling with this since last 2 days :-(

XML:

 
 <?xml version="1.0" encoding="UTF-8" ?> 
- <Document xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:iso:std:iso:20022:tech:xsd:pain.008.001.02">
- <CstmrDrctDbtInitn>
- <GrpHdr>
  <MId>999</MId> 
  <CDT>2017-06-01T04:59:46</CDT> 
  <NTxs>16</NTxs> 
  <CS>345.00</CS> 
- <InitgPty>
  <Nm>Ter Elst BVBA</Nm> 
- <Id>
- <PrvtId>
- <Othr>
  <Id>000000</Id> 
  </Othr>
  </PrvtId>
  </Id>
  </InitgPty>
  </GrpHdr>
- <PmtInf>
  <PmtInfId>317-0</PmtInfId> 
  <PmtMtd>DD</PmtMtd> 
  <NTxs>16</NTxs> 
  <CS>345.00</CS> 
- <PmtTpInf>
- <SvcLvl>
  <Cd>PA</Cd> 
  </SvcLvl>
- <LclInstrm>
  <Cd>RE</Cd> 
  </LclInstrm>
  <SeqTp>RCUR</SeqTp> 
  </PmtTpInf>
  <ReqdColltnDt>2017-06-01</ReqdColltnDt> 
- <Cdtr>
  <Nm>Ter Elst BVBA</Nm> 
  </Cdtr>
- <CdtrAcct>
- <Id>
  <IBAN>0000</IBAN> 
  </Id>
  <Ccy>EUR</Ccy> 
  </CdtrAcct>
- <CdtrAgt>
- <FinInstnId>
  <BIC>LXXX</BIC> 
  </FinInstnId>
  </CdtrAgt>
  <ChrgBr>SLEV</ChrgBr> 
- <CdtrSchmeId>
- <Id>
- <PrvtId>
- <Othr>
  <Id>000000</Id> 
- <SchmeNm>
  <Prtry>PA</Prtry> 
  </SchmeNm>
  </Othr>
  </PrvtId>
  </Id>
  </CdtrSchmeId>
- <DrctDbtTxInf>
- <PmtId>
  

Open in new window

0
Dear Team,

I am getting error in my sql server windows nt - 64 bit cpu utilization 100 % And Memory 80 % but i have memory in my c drive and 4 Gb ram.

Thanks
0
I created a link to a table in SQL Server 2008 R2 from Access 2010.  One field does not appear but is visible in SQL Server Management Studio.  The field appears to be an integer type (int, not null) and the values in it are that of a four digit year such as 2017.  If I create a query in SQL Server Management Studio the field appears.  I tried refreshing and recreating the link but no improvement.  Any suggestions on what I should try next?
0
I have an old IS package that was created back in SQL Server 2008.  I'm moving it to SQL 2014 and so far there are no issues, but I have something that I can't figure out.  The package outputs a data to a flat file.  I'm using Variables to set the file name, but what is puzzling me is how the name is being generated.  The name is as follows: filename-6-19-2017.csv.  I can see the variable for the word "filename", but for the life of me I can't find where that is being appended with the date and even how the .csv is being added.  I have looked in Expression Builder for the date code, but there's nothing in there that would append the date to the filename.  Any idea where I should be looking to find how the data is being appended?
0
here is my query and it returns way too many rows because of table  [ASSETMANAGEMENT_water].[METERREADING]    E

Table e has many entries based on the compkey.   a compkey can have hundreds of reads on it.  These reads have an Added Date column.  I need my query to only return 1 hit on the inner join listed below and that 1 hit needs to be the most current Added Date.  I would think rank would be used, but I am unsure.  Can someone rewrite this sucker or help?  the actual column name is going to be e.Readddttm that the rank needs to take place on


select b.accountnumber, b.accountstatus, b.MOVEINDATE,d.unitid, *  from [CORE_BATCHPROCESSING].[BATCHPROCESSINGLOG] a
inner join billing.account b
on a.entitykey = b.accountkey
inner join [BILLING].[BILLRUN] c
on a.SCHEDULEKEY = c.SCHEDULEKEY
inner join [ASSETMANAGEMENT_WATER].[COMPWMTR] d
on b.addrkey = d.addrkey
inner join [ASSETMANAGEMENT_water].[METERREADING] e
on d.compkey = e.compkey
where c.BILLRUNKEY = '1155'
0

Microsoft SQL Server

158K

Solutions

187

Articles & Videos

48K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.