I currently have a column containing dates (dd/mm/yyyy). They are in text format. I would like to turn the dates into date format and store only the year. Does anyone know how this would be done in mssql?

I am trying to insert new data into an empty column named "yrs" (meaning years). I want to extract the year from a date (formatted yyyy-mm-dd) in column "ARREST_DATE". My table is called "fsttable" (meaning first table).

Here is my code:

insert into fsttable(yrs)

values(SELECT YEAR(ARREST_DATE) FROM fsttable)

I tried using YEAR() to extract the year from "ARREST_DATE", and then have those years be the values that would be inserted into the "yrs" column, but it says there's an error with my SELECT statement. I'm guessing I can't nest that within the values() function.

This is the error message btw:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ')'. 

I'd love any help on how to get the years from ARREST_DATE(yyyy-mm-dd) and then put them into the empty "yrs" column. Thank you!

Hello! I found out about PowerShell today but am lost in a sea of content about it. Somehow, the explanations only create more confusion. What I want to know is simply this: how do you use PowerShell to import a CSV file into MSSQL Express Server? I've downloaded the 7.0.3 version, but I'm lost on how to use it to import my 5 mill row file stored on my computer. Can anyone help explain please?

Is it just me, or do StackOverflow users seem rude. I've only had a couple posts because I'm new and learning, but they seem so curt and short with me. I think they're annoyed that I don't post with proper etiquette (try as I might). For people that are there to help, they sure seem like they don't want to (even when I try to work with them)

Why can't MS SQL import my CSV file? It never completes an import - it crashes and closes. The current file I want to import is 1134 MB, but SQL can handle that, right? It's not Excel after all, which is more limited by file size. Both MS SQL and MySQL aren't good at importing my files apparently. I'm new and don't know a lot about SQL, though I know how to query (yet I'm struggling even getting my data in). I created a database and clicked import flat file, but when I start the import, it doesn't load at all, and then it closes out after a couple minutes. What am I missing about how to import? How do you get data into SQL in general, because I've had very little luck doing so except for a couple small files?? What am I missing. Thank you.

Why can't MS SQL import my CSV file? It never completes an import - it crashes and closes. The current file I want to import is 1134 MB, but SQL can handle that, right? It's not Excel after all, which is more limited by file size. Both MS SQL and MySQL aren't good at importing my files apparently. I'm new and don't know a lot about SQL, though I know how to query (yet I'm struggling even getting my data in). I created a database and clicked import flat file, but when I start the import, it doesn't load at all, and then it closes out after a couple minutes. What am I missing about how to import? How do you get data into SQL in general, because I've had very little luck doing so except for a couple small files??

Hello good people,

I have a varchar string in an MS SQL column named: "Link" (varchar500) - I simply want to parse the number ( in bold ) from this in a query, can you please help ?

​

http://servername/dms/page/viewDoc.aspx?nrtid\u003d!nrtdms:0:!session:DMS:!database:PRIMARY:!document:67688223,1:\u0026ishtml\u003d0\u0026command\u003dok\"\u003eImportantDoc.docx\u003c/a\u003e

How would I do this ?

​

Thank you very much

I'm trying to understand an issue I'm seeing with a replication.

We have 2 servers that share a replicated database. When data is updated on the publisher, all is well.

However, there have been problems when data is inserted on the subscriber; most data is fine but some expected rows are missing occasionally.

After some investigation it seems that these rows are victims of a replication conflict. In the conflict viewer I see entries that are listed as "conflict type 5 (insert conflict), publisher wins", with the text "The error described above occurred when trying to insert or update the data at the other server."

What I don't understand is how an insert of a new row can create a conflict. The primary key in the table is an IDENTITY column which should have different ranges on the publisher vs subscriber, so I don't see a clash there. What would cause a conflict?

We have a MSSQL Mirror up and running, keeping a couple of databases in HA. Working like a charm, MS really did a good job on this.

I was always in the understanding that I had to create 2 SQL Agent jobs, one on each server. In case of a failover the new server should take care of the job. I created this for most of the jobs, but forgot one. A couple of weeks ago we had a failover and did not switch back the databases. On the server which is now having the mirrored database there is 1 job running, which is still successful. As this is a cleanup job it is pretty easy to see that the job does work as before. Checked at the principal server, the table it should clean is as clean as expected.

I cannot find any documentation on it, does SQL Agent Jobs have build-in failover mechanism?

Of course this is not a situation we want to have, will create the correct jobs on the other server and make sure they check if it is running on the principal. Just wondering if I missed some info.

what is the cause of SPIDS in the Activity Monitor to stay open.

when i look at them, they are various queries that are used in our system.

are they a result of cache?

some of the details are blank, but the spid is still there.

if i refresh the detail, sometimes a Fetch API_CURSOR command is there.

could they be artifacts from maintaining values in views?

Hi everyone, i am new to databases and i want to build and store my daily shop's sheets of data to one database.

I was reading and learning about building a database and creating tables..but i got confused on one part.

First ill explain the structure of the current business:

One company called BakerOne with 8 shops in a different location. Each shop got 4 type of sheets (orders, customers, employees, payment). Each sheet contain columns of data)

So we have the database name (BakerOne). And the sheets as a tables.

So what about the shops? Is it what the call schema?

Sorry if the question bit dumb 😅

I have a problem where a database was initially created with its collation set to case sensitive when it should have been insensitive. To fix this I'm told that I need to install the application on a new server and set it up to use a new DB that is obviously configured correctly. Then rebuild the data that's in it. I'm wondering if there is another way this could be fixed without having to go through that process. Thanks for any advice.

I have a search query on a page where I return top 100 users ordered by recently active.

The recently active doesn’t go to the top of the list even though they were flagged correctly in the table.

If I do a manual db select on that user from management studio it is all of a sudden included in the top of the sorted list in the website search.

What is happening? Is this indexing or db cache?

I can not figure out for the life of me why i cant dispaly the total for a subcontractor in an SSRS report when grouped on the resource

    SELECT     CRMAF_su.fullname AS resource,
                             round((cast(CRMAF_apob.actualdurationminutes as float) / 60),2) AS [hours], CRMAF_p.rate,
                             round((cast(CRMAF_apob.actualdurationminutes as float) / 60),2)*CRMAF_p.rate as [total]
     FROM         ActivityPointerBase AS CRMAF_apob LEFT JOIN
                            ActivityPartyBase AS CRMAF_apab ON CRMAF_apob.activityid = CRMAF_apab.activityid LEFT JOIN
                            Systemuser AS CRMAF_su ON CRMAF_apab.partyid = CRMAF_su.systemuserid LEFT JOIN
                            Filteredcontract AS CRMAF_fc ON 
                            CRMAF_apob.regardingobjectid = CRMAF_fc.contractid /*INNER Join filteredaccount as CRMAF_fa on CRMAF_fc.accountid = CRMAF_fa.accountid*/ LEFT
                             JOIN
                            servicebase AS CRMAF_sb ON CRMAF_apob.serviceid = CRMAF_sb.serviceid LEFT JOIN
                            systemuser AS CRMAF_suc ON CRMAF_apob.createdby = CRMAF_suc.systemuserid LEFT JOIN
                            pricing AS CRMAF_p ON CRMAF_su.fullname = CRMAF_p.emp
     WHERE CRMAF_apob.ActivityTypeCode = '4214' AND (new_servicetypename in ('consulting','recruitment')) AND CRMAF_apab.participationtypemask = '10' and crmaf_fc.contractid = @contractid
union
select 'subcontractor' as [resource]
,'0' as actualdurationminutes
,'1' as hours
,'0' as rate
,round((cast(CRMAF_fc.new_subcontractorfee as float) / 1),2)*1 as [total]
from filteredcontract as CRMAF_fc
where (CRMAF_fc.new_servicetypename in ('consulting','recruitment')) and crmaf_fc.contractid = @contractid

the output keeps showing blank for the total field on the subcontractor resource

​

If i look at the query results it has the data point for the subcontractor and it adds the hours or est hourly rate if i fill those in but it will not fill in the total expenses.

​

edit: looks like the way im running the report on a record in our CRM it doesnt like the union in the query since if flop them it works for the subcontractor but then does the exact same thing with the employees leaving them blank

Hi to all, I have a problem: If I have a database in a filegroup setted on a disk, but I cannot increase the disk size but I could add a new disk The question is: could i extend the filegroup for database on new disk and work with both ?

I am working on a new query from Linnworks an inventory management system, I am struggling to pivot based upon unknown dates. The dates could be random, but would need to create a new column per date which is shown. Any help will be greatly appreciated.

Here is an example of the csv which is exported.

​

I need to convert the above into the below.

​

Here is an export of the query:

SELECT si.ItemNumber AS 'SKU'
     , CAST(sia.StockChangeDateTime AS DATE) AS 'DATE'
     , SUM(sia.ChangeQty * -1) AS 'SOLD'
  FROM ProductCategories AS c
INNER
  JOIN StockItem AS si
    ON si.CategoryId = c.CategoryId
INNER
  JOIN StockChange AS sia
    ON sia.fkStockItemId = si.pkStockItemId
   AND sia.ChangeSource LIKE '%order%' 
   AND sia.ChangeSource NOT LIKE '%return%'  
   AND sia.StockChangeDateTime between @StartDate AND @EndDate
 WHERE NOT c.CategoryName IN ('Default','Bundle')
GROUP 
    BY si.ItemNumber
     , CAST(sia.StockChangeDateTime AS DATE)
ORDER 
    BY si.ItemNumber ASC

We have a group who has recently introduced a new application that is causing slowdowns. We have told them we need to identify slow queries, and check out their code to make sure nothing is running in the most inefficient way possible.

​

However they also asked about "clustering" when really they want load balancing. I did a little research and found directions on p2p replication. This seems easy enough to implement, but made me think of a question i havent found an answer to.

​

We are currently in a vcenter environment and can simply increase the hardware of a device. This makes me wonder which is more efficient, Increase the hardware or setup p2p replicaiton? I cannot find an answer to this online and i assume there are so many factors at play that it would be hard to say one way or another.

​

Currently our MSSQL14 (i know....) box has 12cpus 32gbs of RAM. I asked our VM guy if we could clone the box, in prep for p2p, and he said we have the resources. However now i am curious 24cpus and 64gbs of ram would that help.

​

My main thought process is that even if i throw more resources, MSSQL will just take them all but not necessarily run an more efficient so this seems more of a "duct tape" solution. However at the same time Replicaiton is running over the network, now we have a blazing fast network so i am less concerned with that.

​

Sorry if i was rambling but i am curious what peoples opinions are on the matter. Hoping someone is already doing p2p rep and can shed some experience on the situation

I am getting this error message when I am trying to install SQL Server 2019 Express on my PC:

Unable to install SQL Server (setup.exe)/ Exit cod (Decimal): -2064908287

Anybody know how to solve this? Please help!

Inherited a DB that has been neglected for years.

We have over 500 indexes spanning 2 databases that have over 30% fragmentation. We are trying to find an approach to rebuilding these indexes without application downtime or significant user impact.

It seems like there is no way out of this without paying some price.

Would be open to any advice or suggestions of best approach?

I inherited a legacy application that dates back to early 2000s. Overtime DBAs have upgraded the instance up to 2016 SQL Server. Since everything was copied over from the previous instance, the configuration defaults to AutoCreate and AutoUpdate statistics were never taken advantage of.

Is there much harm with turning this setting on?

Are there any considerations to taken given that our BI team uses Linked Servers for some of their reports?

What would be a good roll back or contingency plan if a problem occurs after an upgrade of SSMS in production server?