|
Try this
declare @tbl table(person varchar(50),name varchar(50),value int)
insert into @tbl
select 'xyz','a',10 union all select 'xyz','b',5 union all
select 'xyz','a',15 union all select 'xyz','b',4 union all
select 'xyz','c',1 union all select 'xyz','a',5 union all
select 'xyz','d',10 union all select 'xyz','a',10 union all
select 'abc','a',0 union all select 'abc','b',0 union all
select 'abc','c',0 union all select 'abc','d',0
;with cte as
(
select person,name,sum(value) as total from @tbl group by person,name
)
select person,'others' as name , SUM(total) as total from cte where name not in('a')
group by person
union all
select person,'a' as name , SUM(total) as total from cte where name in('a')
group by person
order by person desc
Niladri Biswas
|
|
|
|
|
Good Day All
i have a Following Query that does the Bulk insert
BULK INSERT dbo.TBL_CMPS FROM C:\\UNISA_IMPORT\\Final_Import\\Campuses.csv WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );
i get an Error
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'C'.
and when i add the Quoates in the path like this
BULK INSERT dbo.TBL_CMPS FROM 'C:\\UNISA_IMPORT\\Final_Import\\Campuses.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );
i get the Following Error
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\\UNISA_IMPORT\\Final_Import\\Campuses.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).
Thank you
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
This might work;
BULK INSERT dbo.TBL_CMPS FROM 'C:\UNISA_IMPORT\Final_Import\Campuses.csv' WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ); The first one doesn't work because of the quotes, the second one has escape-characters in the path.
I are Troll
|
|
|
|
|
Good Day Eddy
The Error is still there
Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "C:\UNISA_IMPORT\Final_Import\Campuses.csv" could not be opened. Operating system error code 3(The system cannot find the path specified.).
ASP, Admin, Everyone , has read and Write access to this File
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Operating system error code 3(The system cannot find the path specified.)
These things don't tend to lie, however they can be misleading.
Are you sure the user running this process has access to this file? If so, why are you sure? Are you doubly sure? If so, are you triplly sure the path is correct?
|
|
|
|
|
yes , this is Happening in my Development machine and i have local admin Rights, the ASP account has access to this File and Everyone has access to this File.
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
The Problem is that the SQL Server is in another Machine and am in my Developement Machine. So the Path is not in the Machine. the Solution is to Add the File to the Server and after that access the File after am done with the Import and Delete the File. Now i have one Question. in my database table i have an Identity Column
BULK INSERT dbo.TBL_CMPS FROM 'C:\UNISA_IMPORT\Final_Import\Campuses.csv' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' );
How can i make the second Column as the First one. Because it tries to add records in the identity field and i get the Following Errors
ulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (ID).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (ID).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (ID).
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 1 (ID).
Msg 4864, Level 16, State 1, Line 1
Bu
Thank you
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Vuyiswa Maseko wrote: The Problem is that the SQL Server is in another Machine and am in my Developement Machine. So the Path is not in the Machine
Told you that error doesn't lie!
Vuyiswa Maseko wrote: How can i make the second Column as the First one. Because it tries to add records in the identity field and i get the Following Errors
Have you tried the documentation on BULK INSERT[^]? The bit you're after is:
FORMATFILE = 'format_file_path'
The docs say: Specifies the full path of a format file. A format file describes the data file that contains stored responses created by using the bcp utility on the same table or view. The format file should be used if:
* The data file contains greater or fewer columns than the table or view.
|
|
|
|
|
hi.
how to delete field value in row
e.g;
emp_id overtime
5 50
i want to subtract value in overtime 50-10,
emp_id overtime
5 40
hope u undersand my question.
thanks in advance
jawadkhatri
|
|
|
|
|
UPDATE TableName
SET overtime = 40
WHERE emp_id = 5
or
UPDATE TableName
SET overtime = overtime - 10
WHERE emp_id = 5
I don't speak Idiot - please talk slowly and clearly
I don't know what all the fuss is about with America getting it's first black president. Zimbabwe's had one for years and he's sh*t. - Percy Drake , Shrewsbury
Driven to the arms of Heineken by the wife
|
|
|
|
|
Thanks I have done
UPDATE TableName
SET overtime = overtime-10
WHERE emp_id = 5
|
|
|
|
|
hi
how i can backup my entire database using pl/sql developer Tool
ver 7.1 ?
(i work with oracle 10g)
thank's in advance
|
|
|
|
|
i want to know how can I install sql express 2005 from commandline and include all features and components except the management studio and also speceficy instance TICSSQL not SQLEXPRESS?
|
|
|
|
|
Here is the full guide how to do that.
Life is a stage and we are all actors!
|
|
|
|
|
How does one add an .rdl report created in SQL Server Business Intelligence Studio to a Visual Studio project and view it in a reportviewer?
I've tried to rename the file from .rdl to .rdlc and then added it to my project. I've also recreated the dataset that the report uses in my project, but in the 'ReportViewer Tasks smart tags panel', in 'Choose Data Sources', there is no datasets to choose from to connect to my report. I don't know how to hook my report up to the dataset.
Any help would be appreciated.
|
|
|
|
|
Hi all,
How to insert values to a temptable by executing a storedprocedure...
Ex :-
INSERT INTO TempYearEndBal exec BLYearEndLeaveTcktBalRep;
Getting error while trying to insert the values to "TempYearEndBal" by executing the SP "BLYearEndLeaveTcktBalRep"...
can anybody help me to come out from this issue...
Thanks in Advance...
|
|
|
|
|
Not sure about Oracle but you cannot do this in SQL Server (and I would be surprised if Oracle supports this).
In SQL Server I would change your temptable to a global temp table and do the insert in the BLYearEndLeaveTcktBalRep proc.
|
|
|
|
|
U can do this in SQL server....
Inside the SP itself create the temp table and try to insert like :
INSERT INTO #TempYearEndBal exec BLYearEndLeaveTcktBalRep
this will work....
But in Oracle,what is the equlent way to do this?...
|
|
|
|
|
rs1.Open "SELECT Count(1)FROM (SELECT DISTINCT mt1.TestCaseName, mt1.TestScriptName, " & _
"mt1.FinalStatus FROM (Metrics_TestCases " & _
"AS mt1 INNER JOIN MaxScriptQuery as mt2 ON( mt1.TestCaseName = mt2.TestCaseName And " & _
mt1.TestScriptName = mt2.TestScriptName And mt1.ReleaseVersion = mt2.ReleaseVersion and " & _
"mt1.TestScriptID = mt2.maxTestScriptID) ) INNER JOIN Release_chart as tr ON mt1.ReleaseVersion = tr.ReleaseVersion) " & _
" ", oConnection, adOpenStatic, adLockReadOnly
This is the query i used to get some data from access table.
MaxScriptQuery is a inner query i used to join .
MaxScriptQuery - SELECT DISTINCT TestCaseName, TestScriptName, ReleaseVersion, MAX(TestScriptID) AS MaxTestScriptID
FROM Metrics_TestCases GROUP BY TestCaseName, TestScriptName, ReleaseVersion;
This query worked fine some week before. But to my surprise now the same query with same set of data is taking too much time. I dont know why the query is suddenly slowed up. CPU usage is also very high while running this query
|
|
|
|
|
Access is a dog - I would compress & repair the database and see if it improves the response. Look into adding any required indexes.
|
|
|
|
|
Thanks for your reply Holmes.
But dont know how to compact and repair without exiting the application in VBA.
Normally the compact and repair is used while exiting the application . How to apply it for a running code?
|
|
|
|
|
Hi,
Don't use Distinct key word its will reduce the performance. Without distinct also given the same result because you are using group by class. So no need distinct key word. Just remove and check it.
|
|
|
|
|
Well, I posted an entry explaining this yesterday but it seems to have disappeared. Not sure what happened there. I was having a few problems accessing the site yesterday.
I can't be bothered writing it all again, but basically you cannot compact a database while it is open. If you want to run the compact from code, you must run it from a different database. Then you can use DBEngine.CompactDatabase, making sure that the data file is closed and is not in use before you do the compact.
|
|
|
|
|
need help to solve this error. this error occours while i m sending mails to the other server..for local serevr its working fine.
|
|
|
|
|
Gentleman,
I have been using MSSQL 2000 for 6 years with no issues, but now am being required to use version 2005. I installed a new factory version on a new server using Win 2003 Server and am receiving the following error:
"The SQL Server Client cannot connect to the server. This error could occur because the firewall on the sever has refused the connection."
Now....this is a connection on the local host of the machine used with just Windows Authentication. The MSFT guide suggests that firewall on the server instance needs to be configured to accept connections. Since this is all on the same machine, I do not understand this, nor can I find where this can be done in the server windows. I have looked in the Configuration Manager and the Management Studio. Where do I go from here? Many Thanks in advance, Pat
|
|
|
|
|