|
SQL Server
Deleting Rows in Result Sets[^]
But, that will probably not work because:
Before a cursor can be used by a positioned UPDATE or DELETE statement, the SELECT statement in the cursor declaration must contain the FOR BROWSE option. (The Microsoft SQL Server 2000 FOR BROWSE option is similar to the FOR UPDATE option in other SQL databases, but you must use SQL Server syntax.) To use the FOR BROWSE option, the table must have both a unique index and a timestamp column.
You could do this:
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_MyTestingTable
(
TestCol varchar(50) NULL
) ON [PRIMARY]
INSERT INTO dbo.Tmp_MyTestingTable (TestCol)
SELECT DISTINCT TestCol FROM dbo.MyTestingTable TABLOCKX
DROP TABLE dbo.MyTestingTable
EXECUTE sp_rename 'dbo.Tmp_MyTestingTable', 'MyTestingTable'
COMMIT
-----------------------------
LATER NOTE: I tried the cursor anyway...and it DOES work.
DECLARE @cCol varchar(50)
DECLARE MyCursor CURSOR FOR
SELECT testcol
FROM MyTestingTable
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @cCol
WHILE @@FETCH_STATUS = 0
BEGIN
if(SELECT COUNT(*) FROM MyTestingTable WHERE TestCol = @cCol)>1
BEGIN
DELETE FROM MyTestingTable
WHERE CURRENT OF MyCursor
END
FETCH NEXT FROM MyCursor INTO @cCol
END
CLOSE MyCursor
DEALLOCATE MyCursor
GO
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
There has to be a unique ID (referential Intergrity 101) for you be able to delete the name. Otherwise with a general delete syntax you will delete all instances of the name. Hope this helps.
PS: You may want to add an ID column to your table so you can identify the exact record you want to delete.
Greg
Coding makes the world go round!!!
|
|
|
|
|
I am trying to install SQL 2005 Express edition on Windows XP, with VS.NET 2003, with .NET framework 2.0. When installing, i am getting following error logged on the error log file:
Microsoft SQL Server 2005 Setup beginning at Tue Aug 22 18:30:08 2006
Process ID : 1896
c:\10394006888baccbee\setup.exe Version: 2005.90.1399.0
Running: LoadResourcesAction at: 2006/7/22 18:30:8
Complete: LoadResourcesAction at: 2006/7/22 18:30:8, returned true
Running: ParseBootstrapOptionsAction at: 2006/7/22 18:30:8
Loaded DLL:c:\10394006888baccbee\xmlrw.dll Version:2.0.3604.0
Complete: ParseBootstrapOptionsAction at: 2006/7/22 18:30:8, returned false
Error: Action "ParseBootstrapOptionsAction" failed during execution. Error information reported during run:
Could not parse command line due to datastore exception.
Source File Name: utillib\persisthelpers.cpp
Compiler Timestamp: Fri Jul 29 01:13:55 2005
Function Name: writeEncryptedString
Source Line Number: 124
----------------------------------------------------------
writeEncryptedString() failed
Source File Name: utillib\persisthelpers.cpp
Compiler Timestamp: Fri Jul 29 01:13:55 2005
Function Name: writeEncryptedString
Source Line Number: 123
----------------------------------------------------------
Error Code: 0x80070002 (2)
Windows Error Text: The system cannot find the file specified.
Source File Name: cryptohelper\cryptsameusersamemachine.cpp
Compiler Timestamp: Mon Jun 13 14:30:00 2005
Function Name: sqls::CryptSameUserSameMachine::ProtectData
Source Line Number: 50
2
Could not skip Component update due to datastore exception.
Source File Name: datastore\cachedpropertycollection.cpp
Compiler Timestamp: Fri Jul 29 01:13:49 2005
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
----------------------------------------------------------
Failed to find property "InstallMediaPath" {"SetupBootstrapOptionsScope", "", "1896"} in cache
Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Fri Jul 29 01:13:50 2005
Function Name: SetupBootstrapOptionsScope.InstallMediaPath
Source Line Number: 44
----------------------------------------------------------
No collector registered for scope: "SetupBootstrapOptionsScope"
Running: ValidateWinNTAction at: 2006/7/22 18:30:8
Complete: ValidateWinNTAction at: 2006/7/22 18:30:8, returned true
Running: ValidateMinOSAction at: 2006/7/22 18:30:8
Complete: ValidateMinOSAction at: 2006/7/22 18:30:8, returned true
Running: PerformSCCAction at: 2006/7/22 18:30:8
Complete: PerformSCCAction at: 2006/7/22 18:30:8, returned true
Running: ActivateLoggingAction at: 2006/7/22 18:30:8
Error: Action "ActivateLoggingAction" threw an exception during execution. Error information reported during run:
Datastore exception while trying to write logging properties.
Source File Name: datastore\cachedpropertycollection.cpp
Compiler Timestamp: Fri Jul 29 01:13:49 2005
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
----------------------------------------------------------
Failed to find property "primaryLogFiles" {"SetupStateScope", "", ""} in cache
Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Fri Jul 29 01:13:50 2005
Function Name: SetupStateScope.primaryLogFiles
Source Line Number: 44
----------------------------------------------------------
No collector registered for scope: "SetupStateScope"
00DCCFC4Unable to proceed with setup, there was a command line parsing error. : 2
Error Code: 0x80070002 (2)
Windows Error Text: The system cannot find the file specified.
Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Fri Jul 29 01:13:50 2005
Function Name: SetupBootstrapOptionsScope.InstallMediaPath
Source Line Number: 44
Class not registered.
Failed to create CAB file due to datastore exception
Source File Name: datastore\cachedpropertycollection.cpp
Compiler Timestamp: Fri Jul 29 01:13:49 2005
Function Name: CachedPropertyCollection::findProperty
Source Line Number: 130
----------------------------------------------------------
Failed to find property "HostSetup" {"SetupBootstrapOptionsScope", "", "1896"} in cache
Source File Name: datastore\propertycollection.cpp
Compiler Timestamp: Fri Jul 29 01:13:50 2005
Function Name: SetupBootstrapOptionsScope.HostSetup
Source Line Number: 44
----------------------------------------------------------
No collector registered for scope: "SetupBootstrapOptionsScope"
Message pump returning: 2
Any help would be greatly appreciated.
Shafik
|
|
|
|
|
Do you or have you had installed any beta or evalution versions of this software?
When was the last time you poured some wine for you and your sweetie and went out on the front porch to watch the geometry frolic on the lake?--Rebecca M. Riordan, Designing Effective Database Systems
|
|
|
|
|
I had installed this about six months ago, and had problems, so i uninstalled using Add/Remove programs. Then i installed MSDE 2000. Two weeks ago i had problem with VS.NET 2003, and so i uninstalled Visual Studio, and MSDE. I have re-installed VS, but would like to give SQL 2005 Express another try. I think that i have removed everything with SQL using Add/Remove programs. Now when installing SQL 2005, i started getting this error now.
Any assistance is appreciated.
Shafik
|
|
|
|
|
Due to format like below, we are not able to save this information in SQL server 2000
Migración Líneas 1&2
Above information is saved in SQL SERVER 2000 as
Migración sistema control Abelló Linde Alcalá
Can someone help me in this regard?
Thanks
Balasaheb
Software Developer
Platform: Asp.net,vb.net
Database: SQL Server 2000
|
|
|
|
|
Are you using nvarchar columns or just varchar ? I suspect you are using varchar and you would get it to work using nvarchar .
|
|
|
|
|
Thanks for ur reply
Yes I'm using Varchar in my table
still I would like to know, is there any way to convert such word in some proper format?
-- modified at 5:08 Thursday 24th August, 2006
Balasaheb
Software Developer
Platform: Asp.net,vb.net
Database: SQL Server 2000
|
|
|
|
|
BalasahebK wrote: Yes I'm using Varchar in my table
still I would like to know, is there any way to convert such word in some proper format?
So, did you try changing it to nvarchar ?
|
|
|
|
|
Your source data appears to be UTF-8. Varchar columns in SQL Server 2000 are Windows-1252 if you're using the Modern_Spanish or Mexican_Trad_Spanish collations.
Somewhere along the line, your UTF-8 data is being interpreted as Windows-1252. If you're using ADO or ADO.NET, all strings are internally Unicode (UTF-16) in the runtime, before being passed to the database, so you should check whatever process is passing the strings to ADO. For example, if reading from a file, you should check that the file is set to UTF-8 mode. Visual Basic 6.0 cannot do this - it always reads files using the thread's current code page.
Bulk Insert also does not understand UTF-8. You will have to preprocess your file into something it does understand, either Windows-1252 or UTF-16.
|
|
|
|
|
This is my original question
Due to format like below, we are not able to save this information in SQL server 2000
1) Migración Líneas 1&2
Above information is saved in SQL SERVER 2000 as
2) Migración sistema control Abelló Linde Alcalá
Thanks for ur help for above message, still I have one question
I would now like to convert the following statement in original format again
means
3) Migración sistema control Abelló Linde Alcalá (From database)
into
4) Migración Líneas 1&2 (on browser)
The below statement is saved properly in sql server 2000
Derivé Chloré PARC SUD
but on browser, it is showing like below
Deriv頃hlor驠PARC SUD
-- modified at 7:11 Thursday 24th August, 2006
-- modified at 7:13 Thursday 24th August, 2006
Balasaheb
Software Developer
Platform: Asp.net,vb.net
Database: SQL Server 2000
|
|
|
|
|
Does spaces in parameter names cause problems
e.g. new parameter("@New Param",OledbType.Double,0,"New Param") ??
If so will square brackets fix it? "@[New Param]" ?? or "[@New Param]"
TIA
|
|
|
|
|
|
|
I'm using SQL Server 2005. What is the statement(s) to insert a bitmap file into an Image column ?
(eg. Northwind.dbo.Categories table Picture column)
Thanks for replying...
Pedestrian, Penang.
|
|
|
|
|
You shouldn't. Instead of image data types you should use varbinary(max).
When was the last time you poured some wine for you and your sweetie and went out on the front porch to watch the geometry frolic on the lake?--Rebecca M. Riordan, Designing Effective Database Systems
|
|
|
|
|
I have a stored procedure that outputs a string based on an integer input. Each string corrresponds to records returned by another procedure. As such, The following is what im trying to do,
SELECT users.fname, users.lname, (EXEC dbo.listOfMothers users.motherID)[maternal_heiarchy]
FROM dbo.users
Where uid = @uid
listOfMothers outputs a string of the persons mother and their mother and so on. This is what the data should look like.
fname__________lname__________maternal_heiarchy
-----------------------------------------------
jason__________foobar_________edna, margret, olga
sarah__________ishot__________hotterMom, hotForAGrama
Of course, this does not work. I hope this illustrates the principle of my efforts. I would be greatly appreciative of anyone who can help me figure out what i'm trying to do. Thanks.
-- modified at 14:36 Wednesday 23rd August, 2006
|
|
|
|
|
upon advice from seventhnight from the sqlteam.com board I used a function instead.
Select users.fname, users.lname, [maternal_heirarchy] = dbo.listOfMothers(users.motherId)
from dbo.users
Where uid = @uid
worked brilliantly!
|
|
|
|
|
I have a table consisting of 3 columns: Parent varchar(50), Child varchar(50), Pop int.
The table is setup as follows:
Parent Child Pop
----------------------------------
Europe France 0
France Paris 1
New York New York City 10
North America United States 0
North America Canada 0
United States New York 0
United States Washington 0
Washington Redmond 200
Washington Seattle 100
World Europe 0
World North America 0
This is just some sample data modified a tiny bit from an example of a hierachical print out sample that is a stored procedure that allows me to pass any place and see all of that place's children/grandchildren.
I need to figure out how to write a query to show me cumulative sums (ROLLUP?) of the whole tree. So the output should basically be something like this (it can include parent and child columns too):
World Null 311
World Europe 1
Europe France 1
France Paris 1
World North America 310
North America United States 310
North America Canada 0
United States New York 10
United States Washington 300
New York New York City 10
Washington Redmond 200
Washington Seattle 100
Hopefully you understand what i'm looking for. I've tried using WITH ROLLUP and I also tried using an Inner Join but I'm not really sure what I need to do to pull this off. I seem to only be able to get it to work 1-2 levels deep but not through the whole tree.
Any help/ideas would be appreciated! Thank you.
|
|
|
|
|
Make a function:
CREATE FUNCTION dbo.GetPopTotal(@cParent varchar(50))
RETURNS int AS
BEGIN
declare @nTotal int
declare @nGrandTotal int
declare @cChild varchar(50)
set @nTotal = 0
DECLARE childcursor CURSOR FOR
SELECT DISTINCT child,pop
FROM travel
WHERE parent = @cParent
OPEN childcursor
FETCH NEXT FROM childcursor INTO @cChild, @nGrandTotal
WHILE @@FETCH_STATUS = 0
BEGIN
select @nGrandTotal = @nGrandTotal + @nTotal + dbo.GetPopTotal(@cChild)
FETCH NEXT FROM childcursor INTO @cChild, @nTotal
END
CLOSE childcursor
DEALLOCATE childcursor
return isnull(@nGrandTotal,0)
END
Call it like this:
select parent,child, dbo.GetPopTotal(child) + pop as PopTotal from travel
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hello there,
ich have an interrogation from a database on my screen. Now I will put it into a Report. It works but now I will make these data connection within the runtime. So I make a blank Report and my code is like this:
<br />
oleDbConnection1.Open();<br />
string strConn = "Provider=SQLOLEDB;Data + <br />
+ Source=AD061978NB\\WINCC;InitialCatalog=Northwind;Trusted_Connection=Yes;";<br />
<br />
string strSQL = "SELECT CustomerID, CompanyName, City FROM Customers";<br />
<br />
OleDbDataAdapter daCr = new OleDbDataAdapter(strSQL,strConn);<br />
DataSet dsCr = new DataSet();<br />
daCr.Fill(dsCr);<br />
oleDbConnection1.Close();<br />
<br />
CrystalReport1 myDataReport = new CrystalReport1();<br />
myDataReport.SetDataSource(dsCr);<br />
crystalReportViewer1.ReportSource = myDataReport;
So but the Report is empty. Sure, because there is no connection to a database or dataset how it exits if I use the Wizzard.
I cant add a field which declare the position of an column. I hope you know what I mean.
Does anybody can help me?
Thank you.
|
|
|
|
|
Now I have another code, a litte bit changed. But it doesn´t work too:
<br />
oleDbConnection1.Open();<br />
string strConn = "Provider=SQLOLEDB;Data Source=AD061978NB\\WINCC;Initial Catalog=Northwind;Trusted_Connection=Yes;";<br />
string strSQL = "SELECT CustomerID, CompanyName, City FROM Customers";<br />
OleDbDataAdapter daCr = new OleDbDataAdapter(strSQL, strConn);<br />
DataSet dsCr = new DataSet();<br />
daCr.Fill(dsCr, "Data");<br />
oleDbConnection1.Close();<br />
<br />
<br />
ReportDocument doc = new ReportDocument();<br />
doc.Load(@"C:\Documents and Settings\ADBLDA1\My Documents\Visual Projekte\Print DataGrid\CrystalReport1.rpt");<br />
doc.SetDataSource(dsCr);<br />
crystalReportViewer1.ReportSource = doc;<br />
<br />
I was searchin in the hole net, but any code looks like mine.
I am wondering, that nobody has an idea
|
|
|
|
|
OK, another Question. Is there any opportunity to show a filled report without using these stupid Wizzard? I mean I want to make it by myself lik in the upper code. But it doesnt work. If I use the Wizzard to connect to the dataSet an the tables and then I fill it with the code, it work. But I don´t understand this. Because, I filled the Dataset with the wizzard already. Why I must use my code?
The important question: Is thera any way to use the reports without the wizzard? (a blank report is OK)
|
|
|
|
|
hi
can anyone please help me
I have
declare @Z decimal
set @Z=0.1
select @Z
the out put comes to be 0 WHY ? it shud be 0.1
Thanx
|
|
|
|
|
You haven't specified the precision or scale parameters of the decimal type. Precision specifies the total number of decimal digits that can be stored, while scale specifies the number of digits to the right of the decimal point. The maximum number of digits to the left of the decimal point is then (precision - scale).
If you don't specify these parameters, the default scale is 0 - so anything to the right of the decimal point is discarded and not stored.
To store a number between -9.9 and 9.9, with one decimal place, you would say
declare @Z decimal(2,1) -- modified at 6:08 Wednesday 23rd August, 2006 (forgot closing bold tag)
|
|
|
|