|
hi guys... i have a table that references itself!
so i came up with the following query
SELECT tc1.TCat_ID_SI, tc1.TCat_Name_VC30, tc1.TCat_Rate_D, tc1.TCat_Normal_B, tc2.TCat_Name_VC30
FROM TimeCategories tc1
JOIN TimeCategories tc2
ON tc1.TCat_AlternateCategory_SI = tc2.TCat_ID_SI
that returnes only 1 / 5 rows because only the returned row has a value in TCat_AlternateCategory_SI the rest of the entries has NULL which is suposed to be like that
any ideas or sugestions on how to get them all returned (incl rows with NULL in TCat_AlternateCategory_SI)?
thanx
[UPDATE]
i now have this as my query
SELECT tc1.TCat_ID_SI, tc1.TCat_Name_VC30, tc1.TCat_Rate_D, tc1.TCat_Normal_B, tc2.TCat_Name_VC30
FROM TimeCategories tc1
JOIN TimeCategories tc2
ON tc1.TCat_AlternateCategory_SI = tc2.TCat_ID_SI
UNION
SELECT TCat_ID_SI, TCat_Name_VC30, TCat_Rate_D, TCat_Normal_B, 'N/A'
FROM TimeCategories
WHERE TCat_AlternateCategory_SI = null
but now the line WHERE TCat_AlternateCategory_SI = null isnt working!
any sugestions?
thanx
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
modified on Friday, February 15, 2008 9:57 AM
|
|
|
|
|
Replace "JOIN" in your first query with "LEFT OUTER JOIN".
|
|
|
|
|
it worked!
thanx man, o u one
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
|
|
|
|
|
Hi
I want to add a extra lump of text to a varchar field within my sql server database without first having to fetch the data
i want to do something like the below, but its not working
update mytable set col1=col1+'Sometext'
where id=1
Can any advise
ta
Simon
|
|
|
|
|
si_69 wrote: update mytable set col1=col1+'Sometext'
where id=1
I believe that this as a query should work. 
si_69 wrote: but its not working
Do you get any error message?
What happens when you try to execute a select query to check for the data?
select * from mytable where id=1
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
It seems that you have to increase the length of your field
|
|
|
|
|
Hi guys
is there any way that i can monitor connections to a sql database?
thanx
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
|
|
|
|
|
SQL Server 2000: In Enterprise Manager's tree, go to your server, Management, Current Activity, Process Info. To update the view right-click Current Activity and select Refresh.
SQL Server 2005: In Management Studio, in the Object Explorer tree go to Management and double-click Activity Monitor. You can filter to only show the database you're interested in.
From a command-line tool or Query Analyzer, run the sp_who2 stored procedure.
If you want to make your applications easier to pick out, you can influence the value in the ProgramName column by using the Application Name parameter in the connection string. If you don't set this your .NET applications will simply show as '.Net SqlClient Data Provider'. Applications using OLE DB (incl. ADO) or ODBC use the product name from the resources of the main EXE, if I recall correctly.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
thanx a mill dude!
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
|
|
|
|
|
hi guys
this is strange, hav a look at the following code...
for (int i = 0; i < reader.FieldCount; i++)
{
MessageBox.Show("" + reader[i].ToString());
}
txtName.Text = reader[1].ToString();
txtPassword.Text = reader[2].ToString();
txtConfirmPassword.Text = reader[3].ToString();
the messagebox displays the correct data, but when i get to txtName.Text = reader[1].ToString() i get a null reference exception.
what is up with that?
Thanx
[Sollution]
all that was happening in a 2nd constructor of the form. i never did InitializeComponent();!!!
so the null reference was the text box that hasnt been created yet 
Harvey Saayman - South Africa
Junior Developer
.Net, C#, SQL
think BIG and kick ASS
you.suck = (you.passion != Programming)
modified on Friday, February 15, 2008 4:57 AM
|
|
|
|
|
Good Morning All
i have the Following View
<br />
ALTER VIEW [dbo].[VW_Toi_Deeds_Vuyiswa]<br />
<br />
AS<br />
<br />
SELECT ID, PROPERTY_KEY, LIS_KEY, CASE <br />
WHEN Isnumeric(substring(FUNCTION_KEY,6,4)) = 1 --If Its Numeric on last digit of the <br />
<br />
--GEOSS part(4 Digits) <br />
<br />
AND SUBSTRING(FUNCTION_KEY, 6, 1) = 0 --Making sure that there is a Digit After the Funckey,and its "0"<br />
<br />
THEN SUBSTRING(FUNCTION_KEY, 1, 5) + SUBSTRING(FUNCTION_KEY, 7, 3) <br />
<br />
ELSE FUNCTION_KEY <br />
<br />
END AS FUNCTION_KEY<br />
<br />
, TOWN_NAME, PARCEL_NUMBER, PORTION_NUMBER, FARM_NAME, REGISTRATION_DIVISION, FARM_NUMBER, FARM_PORTION,<br />
SECTIONAL_SCHEME_NAME, SECTIONAL_SCHEME_NUMBER, SECTIONAL_SCHEME_UNIT_NUMBER, BUYER_NAME, BUYER_ID, <br />
REGISTRATION_DATE, PURCHASE_DATE, PURCHASE_PRICE, <br />
REASON_NO_PRICE, NEW_TITLE_DEED_NUMBER, OLD_TITLE_DEED_NUMBER,<br />
STATUS, SHARES,DATE_CAPTURED,HISTORY_ID<br />
FROM DEEDS.DEEDS_ADMIN.TSHWANE_OWNER_INFORMATION<br />
<br />
This View Extract Data from a tabe Tshwane_Owner_Information table, and i dont understand why the certain part of Concatinaing use substring 7,3, because the view in this case, it goes to a field named "Functionkey" and in that Fied there will be character dataype that looks like this
"GEOSS001" or "GEOSS0001" as you can see some have 4 digits after the GEOSS string other have 3, and now the First part of the String is "GEOSS"
And as you can see above its been concatinated with the Digits.
<br />
SUBSTRING(FUNCTION_KEY, 1, 5) + SUBSTRING(FUNCTION_KEY, 7, 3) <br />
ok, the First part i understand and we are Concatinating the String "GEOSS"+ Something like "0001" or "001", but now, the Substring says move to 7th Posistion and start counting 3 from the Current Posistion, dont you think it missed the Digit? is it not Supposed to be like this ?
<br />
SUBSTRING(FUNCTION_KEY, 1, 5) + SUBSTRING(FUNCTION_KEY, 6, 4)<br />
Correct me if am Wrong, we have been unable to get some Records through this view, I did not write it, i found it there, am trying to get it fixed.
Help
Thanks
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
Hi folks,
Actually in my table i have (lru_name,part_no) as the primary keys. But the problem that i am facing is i have some values in part_no like'121232 with 2323' ,'121212 or 12123' and '121212 & 12121' ,this words like with ,or ,& is creating problems as the database in not able to take the part_no values . So what could be the problem.Please help
skd
|
|
|
|
|
in the stored procedure (sql server 2000) can i check whether the value is a string or it is a decimal.
Yesterday is a canceled check. Tomorrow is a promissory note. Today is the ready cash. USE IT.
|
|
|
|
|
Welcome back Sonia
One suggestion:
You can use the IsNumeric function to check if it is a number.
You can then use CharIndex to find if there is a '.' in the number.
You always pass failure on the way to success.
|
|
|
|
|
SQL Server:
Is there a way to extract a value/result when you exec a sql string?
DECLARE @PaperCost money<br />
declare @PaperCategories nvarchar(30)<br />
set @PaperCategories = '3,7'<br />
<br />
DECLARE @sqlcmd varchar(255)<br />
<br />
select @sqlcmd = ' SELECT sum(amounts) FROM tbl_CostInfo<br />
where CatID in (' + RTRIM(@PaperCategories) + ') '<br />
<br />
SELECT @PaperCost = exec( @sqlcmd )
This sql block will reside inside of a stored proc, and the idea is to have a string passed in for the category values ('3,7' in this example) and return the corresponding cost.
However, sql doesn't like the last line. Is there a way to extract the resulting value from an exec command?
|
|
|
|
|
Why not pass the categories as XML, use OpenXML to turn them into data, and do a normal query ?
Christian Graus - Microsoft MVP - C++
"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
|
|
|
|
|
You could try:
create table #temp1 (
SumAmounts money
)
set @SQL = 'SELECT sum(amounts) FROM tbl_CostInfo .....'
exec(@SQL)
select @PaperCost = SumAmounts from #temp1
drop table #temp1 However, a better way of acheiving this would be to use a UDF to turn your @PaperCategories string into a list of values. The resulting SQL would look a bit like:
select @PaperCost = SUM(Amounts) from tbl_CostInfo
where CatId in (
select Element from dbo.udf_split(@PaperCategories))You should be able to find the source code for a similar UDF using google.
Regards
Andy
|
|
|
|
|
I want to use xp_cmdshell to export the contents of a table to an xml file. Here's the sql I'm trying to use:
use MyDatabase
declare @dtStart datetime;
declare @dtEnd datetime;
declare @publisher varchar(50);
declare @outpath varchar(255);
declare @sql varchar(512);
declare @params varchar(512);
set @publisher = 'SomePublisher';
set @dtStart = '2008-01-01 00:00:00.000';
set @dtEnd = '2008-01-01 11:59:59.999';
set @sql = 'select * from MyDatabase.dbo.downloads where endtimestamp between ''' +
Convert(varchar(50), @dtStart, 121) + ''' and ''' +
Convert(varchar(50), @dtEnd, 121) + ''' and publishername=''' + @publisher +
''' order by endtimestamp, serialnumber';
set @outpath = '\\SERVERNAME\program files\appfolder\' + @publisher + '_January01.xml';
set @params = 'bcp "' + @sql + '" queryout "' + @outpath + '" -T -c -r -t -x -S SERVERNAME\SQLSERVER'
execute master..xp_cmdshell @params;
When I try to run this code, I get the following error
<br />
SQLState = HY000, NativeError = 0<br />
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file<br />
Everything I've found on google says that the permissions for the specified path may not be sufficient, but sql server is running under the "network services" account. Doesn't that account have sufficient permissions to write a file?
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
NT AUTHORITY\NETWORK SERVICE has very few privileges and is a member of very few security groups. I'm not even sure it's a member (transitively) of the Users group. You'll have to add it to the ACL for that folder, or make SQL Server run as a different user. Microsoft recommend running SQL Server under a domain user account rather than one of the built-in accounts.
NETWORK SERVICE differs from LOCAL SERVICE in that it is allowed to authenticate on the network, using the computer's domain account (COMPUTERNAME$). LOCAL SERVICE cannot authenticate but can use null, unauthenticated sessions (e.g. where Everyone is permitted).
You have actually shared the Program Files folder on \\SERVERNAME? You're using UNC syntax there.
My colleagues would normally do this kind of task with DTS/Integration Services or by writing a custom program.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Mike Dimmick wrote: My colleagues would normally do this kind of task with DTS/Integration Services or by writing a custom program
I kinda did that too - I just let the service I'm writing do it. It queries the database and writes the returned dataset to an XML file.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
I want to use xp_cmdshell to export the contents of a table to an xml file. Here's the sql I'm trying to use:
use MyDatabase
declare @dtStart datetime;
declare @dtEnd datetime;
declare @publisher varchar(50);
declare @outpath varchar(255);
declare @sql varchar(512);
declare @params varchar(512);
set @publisher = 'SomePublisher';
set @dtStart = '2008-01-01 00:00:00.000';
set @dtEnd = '2008-01-01 11:59:59.999';
set @sql = 'select * from MyDatabase.dbo.downloads where endtimestamp between ''' +
Convert(varchar(50), @dtStart, 121) + ''' and ''' +
Convert(varchar(50), @dtEnd, 121) + ''' and publishername=''' + @publisher +
''' order by endtimestamp, serialnumber';
set @outpath = '\\program files\appfolder\' + @publisher + '_January01.xml';
set @params = 'bcp "' + @sql + '" queryout "' + @outpath + '" -T -c -r -t -x'
execute master..xp_cmdshell @params;
When I try to run this code, I get the following errors
<br />
SQLState = 08001, NativeError = 2 <br />
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2]. <br />
<br />
SQLState = HYT00, NativeError = 0<br />
Error = [Microsoft][SQL Native Client]Login timeout expired <br />
<br />
SQLState = 08001, NativeError = 2<br />
Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.<br />
NULL
If I've logged into the sql server with Management Studio, and am running this from a query window, why do I get this error?
[EDIT]
I had to add "-S sqlservername" to the commandline
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
modified on Thursday, February 14, 2008 12:12 PM
|
|
|
|
|
I have a table that links 0 - many reports to a form, called ReportLinks. I'm wondering how best to do an atomic update after a web user changes the reports linked to the form, i.e., deselects and selects reports as linked to the form. Please can somebody suggest an elegant way of doing this update.
|
|
|
|
|
Hi guys,
I got a datatable from a excel file, and try to update this table onto sql server, in other words, the users can change the table in sql server based on excel file.
I use SqlDataAdapter to update the table, but the problem is no change happen on my database.
<br />
'...<br />
<br />
Dim cmdSql As SqlCommand = New SqlCommand<br />
cmdSql.Connection = connSql<br />
cmdSql.CommandText = "UPDATE pfSchool SET notes = @notes WHERE ID = @id"<br />
<br />
cmdSql.Parameters.Add(New SqlParameter("@notes", SqlDbType.NText))<br />
cmdSql.Parameters("@notes").SourceColumn = "notes"<br />
<br />
cmdSql.Parameters.Add(New SqlParameter("@id", SqlDbType.Int))<br />
cmdSql.Parameters("@id").SourceColumn = "ID"<br />
<br />
' modify the data for testing<br />
dtData.Rows(0)("notes") = "test2"<br />
<br />
Dim daSql As SqlDataAdapter = New SqlDataAdapter<br />
daSql.UpdateCommand = cmdSql<br />
daSql.UpdateCommand.Connection.Open()<br />
<br />
daSql.Update(dtData)<br />
<br />
'...<br />
Above is a piece of codes which i made for testing.
Any suggestion would be appreciated!
Ming
|
|
|
|
|
Bluebamboo wrote: cmdSql.CommandText = "UPDATE pfSchool SET notes = @notes WHERE ID = @id"
I believe that the problem lies in here. You'll have to pass values with the variable enclosed between Quotes and Ampersand '" & notes & "'
Having said that,you might want to restructure your code as it is vulnerable to Sql Injections[^].
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
Thanks for your reply
I don't think that causes the problem, you can check the solution provided by Micsoft support
http://support.microsoft.com/kb/308055[^]
and the solution declare a SqlCOmmand with such a query
DAUpdateCmd = New SqlCommand("Update CustTest set CustName = @pCustName where CustId = @pCustId", da.SelectCommand.Connection)<br />
which is actually perform same function as what I did
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
