|
Very simple.
You should try this my query.and dont use temp Table #MyTestCounts
UPDATE #MyTestBase
SET TestID = (SELECT COUNT(1) NumOfPoint
FROM #MyTestBase AS mtb
WHERE mtb.ExampleID = #MyTestBase.ExampleID
GROUP BY mtb.ExampleID)
|
|
|
|
|
The number of accounts an agent may have is allocated by a manager, thus the table #MyTestCounts.
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
Hi!
Im kinda out of my dept here. Im upgrading an old C++/CLI project to Visual Studio 2013, so I have a SQL Server Mobile database file (.sdf) that needs to be upgraded to current version (whatever that is). Im not sure what version it has now, but VS says I need to upgrade it with SqlCeEngine.Upgrade(). I´ve got SQL Server Pro 2014 installed on my computer, and when I tried to install Sql Server Compact 4.0 it said I have a later version installed already. Im totally lost here. Could someone please push me in the right direction.
modified 6-Feb-15 14:55pm.
|
|
|
|
|
In the end I solved it myself. It turned out the SQL Server Mobile file (.sdf) was created by version 3.5, while I tried to access it from Visual Studio 2013 with version 4.0. I solved it entering References under project properties. Then removing the reference to System.Data.SqlServerCe.dll (v4.0). After that I added a new reference to the System.Data.SqlServerCe.dll v3.5. I had to Browse my hard drive in order to find it. Problem solved.
|
|
|
|
|
A small note after some further research:
There is an add-in to Visual Studio called "SQL Server Compact/SQLite Toolbox" which give you som really nice tools for working with those databases from within Visual Studio. One of those features is to update the SQL Server CE database from v.3.5 to v.4.0.
|
|
|
|
|
Full Text Search sqlserver
|
|
|
|
|
Documentation[^].
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi
I have a huge database and my drive is full.
I have compressed ndf files with page .
Then I Shrink The file The Result Is About 215MB But ndf fileSize in filesysytem is 1.5 GB
please help me
|
|
|
|
|
Help you with what, you have not asked a question?
|
|
|
|
|
I just installed Oracle 11g Express. I then installed Oracle Developer. When I try to make a new connection, it wants a User Name and Password.
What User Name and Password is it expecting??? I didn't create a User Name!!
[UPDATE]
It was looking for 'system' as a user name. Google is your friend.
If it's not broken, fix it until it is
|
|
|
|
|
how can i transfer data from sql server 2008 to oracle using script
|
|
|
|
|
Create a linked server in sql server pointing to the oracle database
write the script to transfer the data and run it against the linked server connection
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I did connection with oracle using linked server . it works fine. and when i insert record from sql to oracle .it only transfer one record .i need to transfer more than one.
|
|
|
|
|
you can also look into Sql Server Integration Services (SSIS) if you have high data volume for better performance plus its a great tool to learn as well if you are interested in ETL and related working.
|
|
|
|
|
MY manager wants me to do using linked server
|
|
|
|
|
Proceed accordingly then  and best of luck.
|
|
|
|
|
Hi All,
I have a database table which I want to modify the computed expression of a column, Computed expression of the column was as below
(case when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NULL then (0) when [AnnualFederalPovertyLevel] IS NOT NULL AND [MonthlyFederalPovertyLevel] IS NULL then [AnnualFederalPovertyLevel] when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NOT NULL then [MonthlyFederalPovertyLevel] when [UseMonthlyIncome] IS NOT NULL AND [UseMonthlyIncome]=(1) AND [AnnualFederalPovertyLevel]>[MonthlyFederalPovertyLevel] then [MonthlyFederalPovertyLevel] else [AnnualFederalPovertyLevel] end)
Now I want to change it to
(case when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NULL then (0)
when [AnnualFederalPovertyLevel] IS NOT NULL AND [MonthlyFederalPovertyLevel] IS NULL then
(CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END)
when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NOT NULL then
(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END)
when [UseMonthlyIncome] IS NOT NULL AND [UseMonthlyIncome]=(1) AND [AnnualFederalPovertyLevel]>[MonthlyFederalPovertyLevel] then
(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END)
else (CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END) end)
I have tried in the below way, but it didn't work gave me the following error
ALTER TABLE dbo.ApplicationData DROP COLUMN ReportingFpl;
GO
ALTER TABLE dbo.ApplicationData ADD ReportingFpl AS (case when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NULL then (0)
when [AnnualFederalPovertyLevel] IS NOT NULL AND [MonthlyFederalPovertyLevel] IS NULL then
(CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END)
when [AnnualFederalPovertyLevel] IS NULL AND [MonthlyFederalPovertyLevel] IS NOT NULL then
(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END)
when [UseMonthlyIncome] IS NOT NULL AND [UseMonthlyIncome]=(1) AND [AnnualFederalPovertyLevel]>[MonthlyFederalPovertyLevel] then
(CASE WHEN [MonthlyFederalPovertyLevel] > 0 THEN [MonthlyFederalPovertyLevel] ELSE 0 END)
else (CASE WHEN [AnnualFederalPovertyLevel] > 0 THEN [AnnualFederalPovertyLevel] ELSE 0 END) end);
Error
Msg 5074, Level 16, State 1, Line 3
The index 'IX_ApplicationData_ApplicationId-ReportingFpl_PriorityPoints-ContractorPriorityPoints' is dependent on column 'ReportingFpl'.
Msg 4922, Level 16, State 9, Line 3
ALTER TABLE DROP COLUMN ReportingFpl failed because one or more objects access this column.
Msg 2705, Level 16, State 5, Line 1
Column names in each table must be unique. Column name 'ReportingFpl' in table 'dbo.ApplicationData' is specified more than once.
Thanks & Regards,
Abdul Aleem Mohammad
St Louis MO - USA
|
|
|
|
|
As the first error says, you have an index on the table called IX_ApplicationData_ApplicationId-ReportingFpl_PriorityPoints-ContractorPriorityPoints which uses the computed column, so you can't drop the column without dropping the index first.
- Find any indexes which use the column;
- Generate both a drop and a create script for each affected index;
- Run the index drop scripts;
- Drop your column;
- Add the new version of the column;
- Then run the index create scripts.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Hello,
I don't think it will allow you to change Computer column expression on the Fly... Instead add new column with new expression and name it as newColumn. Delete you old column and then rename newColumn to Original column name using below command.
sp_RENAME 'Table_First.newColumn', 'YourOriginalColumnName' , 'COLUMN'
|
|
|
|
|
If you had the opportunity to re-design a large system's DB layer, that beside pure data contains a fairly large amount (hundred of thousands every year) of files, would you use BLOBs or external storage?
The facts are known about those files:
1. Most of them are PDF, DOC(X) and image (JPEG, TIFF, BMP)
2. No search inside the files are required
3. Files are acceded mostly (95% of the time) directly
4. History of files are important
Skipper: We'll fix it.
Alex: Fix it? How you gonna fix this?
Skipper: Grit, spit and a whole lotta duct tape.
|
|
|
|
|
Blobs put heavy weight on the tables, the bigger they are the slower the operations. Also if anything corrupts on one file you'd have to restore a DB table instead of a single file or directory.
Also it would be more scalable as it would allow you to use different backend servers to store the files and have a rough load balancing/availability service without heavily involving the DB server, all at the advantage of performance.
Geek code v 3.12
GCS d--- s-/++ a- C++++ U+++ P- L- E-- W++ N++ o+ K- w+++ O? M-- V? PS+ PE- Y+ PGP t++ 5? X R++ tv-- b+ DI+++ D++ G e++>+++ h--- r++>+++ y+++*
Weapons extension: ma- k++ F+2 X
|
|
|
|
|
I would go for external storage.
You can set up a series of meta tables with locations/mappings of drives and folders and store the files outside of the database.
You could also create a series of stored procedures to check that the files are in the locations held in the database, every day or so, reporting back on discrepancies - just to catch the rare occasion where a file has been moved or deleted.
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
What are you most comfortable with?
I would personally go for BLOBs.
Pros:
- You get one point of backup.
- Lowered chance of tampered or missing files.
- You only need to set permissions in one place
Cons:
- If badly implemented performance will suffer
- If your database is stored on a filesystem you will get an overhead
- No direct access to files
There's a paper[^] from MS on the subject
|
|
|
|
|
Just to confuse matters, you might also want to look at file tables: 
FileTables (SQL Server)[^]
SQL Server 2012 FileTable: My first experience[^]
Using FileTables in SQL Server 2012[^]
In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
