Microsoft SQL Server

162K

Solutions

49K

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

How to convert a string to money in the Data Conversion Transformation process in SSIS?

My Source file is an Excel File. I added the Data Conversion Transformation to convert the Price column to the Data Type "currency[DT_CY]".

Source value : $50,240.30
Expected result : 50240.30
SQL Destination Table has the column Price with the Data Type : Money

However, conversion fails with the following error :

The "Data Conversion.Outputs[Data Conversion Output].Columns[Price]" failed because error code 0xC020907F occurred, and the error row disposition on "Data Conversion.Out

Open in new window

0
The 7 Worst Nightmares of a Sysadmin
LVL 1
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

I have a process in which I'm trying to determine which is the shortest ship transit time from one location versus another.

For example if I'm shipping a package to first 3 digits of zip from zipcode 38133 it is 3 days but if shipping from zipcode 02189 it is 1 day I would want to select the transit day for 02189 at 1 day. For this example I have a table (have attached an example of the rows and values and I want to first select the transit days from the origin to the zip3 and then compare to all the origin transit days to the same zip3 and return the fastest (smallest vale).

Zip3      DestinationState      Zone      TransitDays      TransitType      OriginZip      Carrier
011      MA      2      1      FXG      02189      FedEx
011      MA      6      3      FXG      38133      FedEx
011      MA      8      5      FXG      97214      FedEx
011      MA      8      5      FXG      98108      FedEx

in the above the origin is 38133 with a transit of 3 and i'd want to compare to the resulting 02189 with 1 the shortest transit days.
Book2.xlsx
0
Visual Studio 2015, SSRS

I am having a problem working on RDL files locally.  I was sent some RDL file to review.   When I use  "Add Existing"  to add the reports to a report project, I can see the report design when I right-click and select "View Designer"

However, when I select "View Code", I only see XML.  What am I doing wrong?

Thanks
0
Experts,

I have an SSRS server setup and we are currently scheduling nightly delivery (via EMAIL) of reports to clients.

The issue is that I would like to "dynamically" change the "FROM EMAIL" address for the out-going emails that contain the reports to a "Special EMAIL Address" for certain CLIENTS.
So for example, if I schedule a nightly report delivery via email for Customer "A", then I want EMAIL ADDRESS "A" to be included within the body of the email as the FROM email.

If I schedule a nightly report delivery via email for Customer "B", then I would like EMAIL ADDRESS "B" to be included within the body of the email as the FROM EMAIL.

Can someone help with HOW TO DO THIS?

Let me know if you have any questions.

Thanks
M
0
Hello Experts

I'm trying to merge the attached tables using the following SQL query:

D_LC_PRODUCTTYPEHIST as T1 Right Join D_LC_PRODUCT as T2 
       on T1.COUNTRY_ID=T2.COUNTRY_ID AND T1.PK_PRODUCT=T2.PK_PRODUCT) AS A 
       LEFT JOIN D_LC_PRODUCTTYPE AS T3 ON A.COUNTRY_ID=T3.COUNTRY_ID AND A.PK_PRODUCT=T3.PK_PRODUCT) AS B 
       LEFT JOIN D_LC_PRODGROUP AS T4 ON B.COUNTRY_ID=T4.COUNTRY_ID AND B.FK_PRODUCTGROUP=T4.PK_PRODUCTGROUP AND B.AK_PRODGIFT=T4.AK_PRODGIFT) AS C 
       LEFT JOIN D_LC_PRODGIFT AS T5 ON C.COUNTRY_ID=T5.COUNTRY_ID AND C.FK_PRODUCTGROUP=T5.FK_PRODUCTGROUP AND C.AK_PRODGIFT=T5.AK_PRODGIFT

Open in new window

However, I keep on getting error here
D_LC_PRODUCTTYPEHIST as T1 Right Join D_LC_PRODUCT as T2

Open in new window

wrong syntax

Can someone please let me know where I'm going wrong?

Thanks
LCMS_MRD_Delta_ProductTypeHist.csv
LCMS_MRD_Full_Product.csv
0
Hi, Just dropping this question again with some more detail to see if i can get a work around.

I am in the process of converting a large number of Crystal Reports to SSRS. I have hit a snag with a number of Running Total issues. we have over 25 reports that need to been converted but they all have a running total as a grand total, typically evaluating on group, outside of the group. In some cases these running values are used in other calculations. It is a nightmare.

As you can imagine I can not get this to work as if I write a running value expression on group it is outside of the group scope and does not work, and if i set the scope to "dataset1" the value is not correct as it needs to evaluate on group.

I have read through many links but i can't see how they will work, but i might just have a skill gap, none of them cover where the running total evaluating on group are used as a total only.

I have attached an example of one of the reports that has a running total, it is only one of the possible layouts and ways the previous reports used the running totals. but does highlight the issue i am hitting. If possible i would rather do this within the ssrs report but i am at a loss on how to get it to work.

We are on SQL 2014 but are about to migrate to 2016

Any ideas on a way to get this to work?

Example-of-Running-total-in-Report.JPG
0
I have an application that has the ability to create databases.   Basically, its a case management application.  When the department opens a new case in the application, a database by that case name is generated.  Here is my problemo.    Every once in awhile i will see a new case that is created and it will not be part of my backup plan.  I use ssms maintenance plans.  My question is this.  Can i set up maintenance plan to backup all databases, save the maintenance plan and then when a new database is added, it is automatically added to the maintenance plan?  

2  some sort of alert that a database was created on my database?  Does anyone know how to configure that?
0
Date question:
I need a where clause for a datetime field called "ExpirationDate"
2008-08-01 00:00:00.000
Formated as above.
This is a field that holds dates for future expirations


I need records that will always  be including todays date  >=    
and also 3 months prior from any given today date  ?


this where caluse is not working ?

where [ExpirationDate] >= GETDATE()
 
  or [ExpirationDate] <=DATEADD(MONTH, -3, getdate())
 
Thanks
fordraiders
0
I have a report from a database with 400 odd names
I would like to be able to email idividually all the people with the individual report, but do not know If it is possible, and if so, how to set this up

Gordon
0
I am running SQL Server 2017 Developer Edition with CU8 installed with SSMS 17.9.1.

I am running a script that goes through all the backup files on a network share and collects the header data for each file using the "RESTORE HEADERONLY" command.

I have run into some files that might be corrupt, and they take over an hour to return an error, whereas a successful run of the command is completed in 1-2 seconds.

Is there a way I can limit the execution time of the "RESTORE HEADERONLY..." command so that it runs for a maximum of 60 seconds?

I am reading files from network shares on other servers.

I have already tried limiting execution time to 60 seconds with this:

use master
go
exec sp_configure 'remote query timeout', 60;
go
reconfigure;
go

Open in new window


But the RESTORE HEADERONLY command still kept running for a long time on the suspected corrupt file.

Any help would be greatly appreciated!
0
IT Pros Agree: AI and Machine Learning Key
LVL 1
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Looking for SQL help to find clients who are not recent customers.
Here is the code - which nicely returns all entries made (with details) where the event is more than 6 months from today.
What I need to do is EXCLUDE any of those ClientID who have a record within the last 6 months.
As an extra layer I would also like it to return that client only once - at the moment it returns every instance of that client in the record.
So the end view would show a list of clients who have made entries, but not in the last 6 months - and only one instance of them in the list.
SELECT
dbo.tblShow.ShowStartDate,
dbo.tblEntry.EntryResultsMark,
dbo.tblClient.ClientSurName,
dbo.tblClient.ClientEmail,
dbo.tblClient.ClientForeName
FROM
dbo.tblClass
JOIN dbo.tblEntry
ON dbo.tblClass.ClassID = dbo.tblEntry.ClassID 
JOIN dbo.tblShow
ON dbo.tblShow.ShowID = dbo.tblClass.ShowID 
JOIN dbo.tblClient
ON dbo.tblClient.ClientID = dbo.tblEntry.RiderID
WHERE NOT
dbo.tblShow.ShowStartDate > DATEADD(month, -6, GETDATE()) AND [dbo].[tblEntry].[EntryResultsMark] is not NULL

Open in new window

Many thanks in advance
0
Hi Experts,

I have a SQL view that is being linked to my Access app.
That view has a column named SNVNum, defined as follows
CAST(REPLACE(dbo.Skilled_Nursing_Visit_Note.SNV_ID, 'snv', '') AS int)

Open in new window

Now when I open the view and sort by that column it works perfectly fine in db container.
it also works fine when running the following in SSMS.
Select * from vw_SkilledVisitNotesFilterFrm Order by SNVNum

Open in new window

However when I assign the form the following SQL as record source.
        Me.RecordSource = "Select * from vw_SkilledVisitNotesFilterFrm  Order by SNVNum"

Open in new window

it gives me the error attached.
Untitled.png
0
I just discovered the SQLSERVER Module for Powershell and would like to use the.
get-ChildItem2 -Path w:\ -File -Recurse | Select  DirectoryName,Name,CreationTime,LastWriteTime,LastAccessTime,Extension,Length,Mode | Write-SqlTableData -ServerInstance 'sandboxhost' -DatabaseName 'NETAPP_INVENTORY' -SchemaName dbo -TableName 'files' -force

Open in new window

to write to a table.  The data structure is 11 TB with over 100 million files.  

From my initial testing of small directory structures it works well.  My fear is that the system will not be able to hold all the data before writing to the SQL server.  Is there a way to chunk this up or make it more efficient?

Here is what I am doing to help or not:

$Dir = 'W:\'
$DirLists = (Get-ChildItem2 -path $dir -Directory).FullName

Foreach ($dirlist in $DirLists)
{
Write-Host (Get-Date)
Write-Host $dirlist
get-ChildItem2 -Path $dirlist -File -Recurse  | Select  DirectoryName,Name,CreationTime,LastWriteTime,LastAccessTime,Extension,Length,Mode | Write-SqlTableData -ServerInstance 'sandboxhost' -DatabaseName 'NETAPP_INVENTORY' -SchemaName dbo -TableName 'DUMP' -force
Write-Host
}

Open in new window

0
We have a Microsoft SQL Server database server that has 5 instances running on it. They all work just fine for our needs but one database on one instance is a little performance sensitive. The virtual machine has 4 CPU cores and 64 GB of RAM allocated to it. I'm wondering if there's a way to optimize performance on one instance so it hogs maybe half of all the available resources and let the other 4 share the rest.

I should point out that this is a development and not a production environment. I wouldn't consider loading a server up with 5 instances in a production setting.
0
I have changed the recovery model to Full since the DB configured for mirroring. The DB size is 200GB. After changing to Full recovery model users feels the slowness during transaction and reports.

SQL Server version: 2017 standard

Does the recovery model change create performance issue?  I have scheduled only full back every day around 11 pm.  No transaction log back up.
0
I'm a system administrator for Report Manager.  I can see the users my folders, but cannot see the reports in the folder.  I need to be able to see all the reports created in report builder in the user's my folder.
0
I want to merge the query into one

select id
				from dbo.table1 with(nolock)
				where cid = 622
if recordfromabove neq form.value then proceed the follow code				
update table1
					set id = 22
					where cid = 	622		

Open in new window

0
We have an AZURE SQL DB that is a replicated copy of data from our point of sales system vendor. We are starting to use that AZURE SQL DB replicated copy to run reporting. Our vendor has provided us with a full replication so that we have all historical data in the DB but need to turn on a 90 day filter which will then write over everything on our replicated DB. I created a copy of the current DB so that we do have all historical data. I need some assistance in configuring a process that would archive data every 90 days to the db copy so that we have all data for reporting/queries in one database.
0
I need to create a report based on a sharepoint online list. When our sharepoint server was in house, this was not a problem but I cannot get the connection string to work for sharepoint online. I do not know power BI and frankly don't have time to learn it. If I can get SSRS to connect and I can get my report done in 1/2 hour. Anyone know the connection string to make this work?
0
10 Tips to Protect Your Business from Ransomware
LVL 1
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

I am trying to write a ssrs report based on a sharepoint online list. I have tried about 100 different variations of the connection string with no luck. Can anyone point me in the right direction?
0
Hello Experts,
I am stuck in a package which was running fine before, but hangs now.  I have about 15 tasks to run in the package.  Task 1 thru 4 runs just fine.  They are just copy the Flat file to local directory, Backup and Delete it etc.  The 5th one is insert the data into the database table where it hangs.

FYI, after 1 thru 4, if I try to execute the 5th task independently, it runs without any problem.  But fails if I run the whole package.

I added an Event Handler, but it does not show me any errors.

Any idea what else could be going on?   Please try to help.

Thank you very much in advance.
0
I need to construct a query to display a folder name only from the network path table, ie the table name is "networkpath"

I have a network path field with the data like below

\\testsql3\document\ICS\2018\201834567\abc.doc
\\testsql3\document\ICS\200834567\abc.doc
\\testsql3\document\ICS\2007\201834567\abc.doc
....etc

What I want from the output

1. only the folder names right after ICS
2. If the folder names  right after ICS is more than 4 digit like 2018, 2017...etc skip entire row selection, I only want the folder name more than 4 digit like 201834567

In above example

The result is only 200834567 shown
0
Need help translating the following T SQL(SQL Server) to PL\SQL(Oracle). Receiving errors when running it in SQl Developer.

SELECT xxx.RC_NUMBER, xxx.DISP_CODE, SUM(DELAY)

FROM (SELECT DISTINCT cur.RC_NUMBER RC_NUMBER, cur.DISP_CODE DISP_CODE, cur.TIME_STAMP TIME_STAMP,

CASE WHEN cur.RC_NUMBER = previous.RC_NUMBER THEN DATEDIFF(second,cur.TIME_STAMP,previous.TIME_STAMP)/CAST( 86400 AS float) 
WHEN cur.RC_NUMBER <>previous.RC_NUMBER THEN DATEDIFF(second,cur.TIME_STAMP,GETDATE())/CAST( 86400 AS float) END DELAY


FROM (SELECT ROW_NUMBER() OVER ( ORDER BY RC_NUMBER, TIME_STAMP ASC) row , RC_NUMBER, DISP_CODE, TIME_STAMP
FROM RC_AUDIT_LOG 
WHERE TIME_STAMP>= DATEADD(wk,DATEDIFF(wk, 6, GETDATE()), 0)-10*7 ) cur
LEFT OUTER JOIN (SELECT ROW_NUMBER() OVER ( ORDER BY RC_NUMBER, TIME_STAMP ASC) row , RC_NUMBER, DISP_CODE, TIME_STAMP
FROM RC_AUDIT_LOG
WHERE TIME_STAMP>= DATEADD(wk,DATEDIFF(wk, 6, GETDATE()), 0)-10*7) previous
ON cur.row = previous.row- 1) xxx

GROUP BY xxx.RC_NUMBER, xxx.DISP_CODE

Open in new window

0
I have a native C++ application that uses SQL Server 2012 with filestream.  It uses win32 API calls for streaming access e.g. OpenFileStream() to Save/Load the data from the database(s)/filestream.  In the past we have granted sysadmin privileges to SQL logins as we control the SQL instance.  
I would now like to restrict the SQL privileges given to the SQL logins to the least possible.  I have tried SQL Authentication and also Application Roles but neither allow the use of the Win32 API calls to filestream.  Can anyone suggest how I can continue to use the API streaming calls and restrict the SQL access?  My users need to be able a read/write to the application databases and also create, delete, backup, restore user databases.  Many thanks for any help on this....
0
I want to write a vba macro that does the following

link a table from sql server to access database, embed/program and execute access query in vba and remove the linked table

My code so far is this.


Sub Update()
Dim Con       As ADODB.Connection
Dim RS        As ADODB.Recordset
Dim myCommand As ADODB.Command



Dim AccessFile As String


Set Con = CreateObject("ADODB.Connection")

Sheets(1).Select
ActiveSheet.Select

'AccessFile = Range("b3").Text & "\" & Range("b4").Text & ".accdb"
AccessFile = Range("b3").Text & "\" & Range("b4").Text & ".mdb"

With Con
  .Open "Provider = Microsoft.ACE.OLEDB.12.0; Data Source=" & AccessFile
End With

'cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & Conn

Set myCommand = CreateObject("adodb.command")

''************ link table dbo_production_values from a sql database somewhere on the network                 <-----------------------

With myCommand
    .ActiveConnection = Con
    .CommandType = adCmdStoredProc
    '.CommandText = "qry_Daily_Production"
    .CommandText = "SELECT AC_PROPERTY.propnum, Prod.client_day, Prod.allocated_oil_production, Prod.allocated_water_production, Prod.allocated_gas_production, _
    Prod.api FROM AC_property INNER JOIN dbo_production_values AS Prod ON AC_property.api=prod.api WHERE prod.client_day >= DateAdd ("d", -300, Date());""
   '.Parameters.Append .CreateParameter("paramDateInput", adDate, adParamInput, , Sheets("DataEntry").Range("D5").Value)
    Set RS…
0

Microsoft SQL Server

162K

Solutions

49K

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.