|
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)
|
|
|
|
|
I am developing a system using ASP.NET with VB script.
I have a function to import CSV file into my SQL server database. As i found out the comma will effect my data placement in my tables, so i think of to develop a function to convert the CSV file to Tab Delimiter file.
Is it possible to develop the function?
As i found out, there are many converter outside that need to buy for the converter, it is not convenient for me to buy it.
Please give me any suggestion or solution, thank you.
|
|
|
|
|
Well, your problem here is that your import data is ambiguous. You don't know whether a comma represents a field separator or a digit grouping symbol. The best thing to do is to change the import data format, for example to put quotes around numeric fields that can have a digit grouping symbol in them, or indeed to put quote marks around all fields. It's then traditional to double quote marks that actually appear within a field.
Bulk insert is typically easiest to handle with fixed field widths rather than the use of separators.
There's what looks to be a good CSV file reader class at http://www.codeproject.com/cs/database/CsvReader.asp[^] (disclaimer: I haven't used it). You could use this to write a convertor to tab-separated format once you've sorted out the ambiguity, if you can't get the source data generated in a usable format.
|
|
|
|
|
hi i have one table which contains employeename, employeeID and ManangerID
but manager is also an employee.
now i want to retrive employeeID and employeename based on managerID
|
|
|
|
|
Is this what you're after?
select e.employeeID,e.employeename,m.employeeID as ManagerID, m.employeename as ManagerName
from Employees e left join Employees m on e.ManagerID = m.employeeID
--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
|
|
|
|
|
how to configure a SQL Server 2000 that syncronize their data with the SQL Server 2000.i.e how to use Replication through Microsoft SQL Server Enterprise Manager.
I am a fresher so,please help me. 
Thankyou
Vartika
-- modified at 8:10 Thursday 24th August, 2006
Vartika
-- modified at 8:14 Thursday 24th August, 2006
|
|
|
|
|
While i use the bulk insert to import the csv file into my SQL server database, i am facing problem as the csv file have column with money data such as 1,111,111.00.
As i set the FIELDTERMINATOR =',', it will separate out the value 1,111,111.00 to few of columns as the value have comma in it.
Any suggestion to solve this problem?
Thank You.
|
|
|
|
|
I have installed and configured MS SQL 2005 Express. Both SQL Server and SQL Server Browser login set to "Local System", Shared Memory, Named Pipes and all the stuff set correctly.
If I want to connect to database created in the SQL Server Management Studio, everything is OK, no User ID or Password needed, because of the trusted connection.
Then I've added a database in my project and set Windows Authentication in the "Modify Connection...". After running the program, I've got exception number 4060:
Cannot open database "AddressBook" requested by the login. The login failed. Login failed for user '<my system="" user="" name="">'.
This is the connection string I've used:
SqlConnection myConnection = new SqlConnection(
"server=.\\SQLEXPRESS;" +
"Trusted_Connection=yes;" +
"database=AddressBook;");
Connection test in VS is also OK, only accessing the database (.mdf) programatically using SqlConnection goes wrong.
Maybe it's something easy to fix, but I tried books, Google and MSDN without any progress, so I feel desperate from this
|
|
|
|
|
It sounds like you're trying to work with a detached file, one that isn't a permanently configured database. If that's what you're trying to do, you need to specify the filename in the connection string, using the AttachDBFilename keyword.
For more information, see the documentation for SqlConnection.ConnectionString.
|
|
|
|
|
I'm just looking for a bit of education here. I was reviewing an old piece of code and found the something like the following:
SELECT NAME NAME
FROM TABLE
Where NAME would be a field name.
This doesn't throw an error and returns a single column of data (just NAME).
I'm just wondering why? And why doesn't it fall over? I had a quick search under books online, but (as you can probably see from the thread title), i'm not sure how to phrase that search..
-- modified at 9:16 Tuesday 22nd August, 2006
Just figured it out - second NAME becomes an alias for the first NAME, d'oh!
|
|
|
|
|
Paddy Boyd wrote: I'm just wondering why?
You can create aliases for column names. I tend to write them explicitly so that anyone maintaining the code knows what I was doing. e.g.
SELECT RealColumnName AS AliasName
FROM TableName
The "AS" is optional and can be left out. As you can probably tell, it makes the code much easier to read if the "AS" is left is because it reduces the ambiguity, especially if someone was not aware of aliases, or that they can be declared without the "AS".
Why the guy put "NAME NAME"? No idea, but it just adds to the confusion, doesn't it? Perhaps he wanted to waste the time of those that came after him. Perhaps he was just an idiot. That is something I guess we'll never know. 
|
|
|
|
|
Colin Angus Mackay wrote: I tend to write them explicitly so that anyone maintaining the code knows what I was doing.
Mmm. More used to that. All for unambiguity...
Colin Angus Mackay wrote: Why the guy put "NAME NAME"? No idea, but it just adds to the confusion, doesn't it?
I like to give the benefit of the doubt that it was a simple copy paste error.
|
|
|
|
|
Paddy Boyd wrote: second NAME becomes an alias for the first NAME, d'oh!
 Just noticed you message modification. Oh, well.... I hope it helps others too.
|
|
|
|
|
Hi all,
I got a table with trigger. this trigger will raise an error when certain validation is not met. but i'm not able to catch this error in stored produre when i try to insert an record to this table. Need your help. any idea on this?
Thanks in advance! 
|
|
|
|
|
@@ERROR[^]
--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
|
|
|
|
|
Folks,
I have a DTS package which loads a text file into a table on SQL server 2000 database. The source column is numbers but occaisionally has "N.A."
The target column is FLOAT and nullable. How do I get DTS to translate "N.A." to NULL?
Code that does the transfer is:-
Public Sub CustomTask_Translate_BidPrice(ByVal oCustomTask1 As Object, ByVal ColumnPosition As Integer)
Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.Transformations.New("DTSPump.DataPumpTransformCopy")
oTransformation.Name = "DTSTransformation__Bidprice"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4
Set oColumn = oTransformation.SourceColumns.New("PX_BID", 1)
oColumn.Name = "PX_BID"
oColumn.Ordinal = ColumnPosition
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing
Set oColumn = oTransformation.DestinationColumns.New("BidPrice", 1)
oColumn.Name = "BidPrice"
oColumn.Ordinal = 4
oColumn.Flags = 8
oColumn.Size = 50
oColumn.DataType = DBDataType.DBTYPE_NUMERIC
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True
oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing
'oTransformation.
Set oTransProps = oTransformation.TransformServerProperties
Set oTransProps = Nothing
oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing
End Sub
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
