|
Hi Devvvy,
i have a legacy code in VB.Net 1.1 and Sybase ASE 12.5, system is working fine as i took over from previous developer. Now client want to upgrade to ASE 15.7. we created a totally new environment and installed DB and ASE Client on new machines.
I was getting Connection Error while connecting with new DB.
I modified connection string in web.config
Previous:
<add key="connectionString"
value="Provider=Sybase.ASEOLEDBProvider.2;Server Name=DBDV01;Initial Catalog=RLDB;Password=db_user;User ID=db_user;Server Port Address=4100;Persist Security Info=True" >
</add>
New:
<add key="connectionString"
value="Provider=ASEOLEDB;Server Name=DBDV01;Initial Catalog=RLDB;Password=db_user;User ID=db_user;Server Port Address=4100;Persist Security Info=True" >
</add>
Login works fine however just after Login when system tries to run any other query in database I get following error message:
[Message Class: 14][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 924] [ASEOLEDB]Database 'RLDB' is already open and can only have one user at a time.
[Message Class: 11][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 4001][ASEOLEDB]Cannot open default database 'RLDB'.
[Message Class: 10][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 5704][ASEOLEDB]Changed client character set setting to 'iso_1'.
[Message Class: 10][Message State: 2][Transaction State: 1][Server Name: DBDV01]
[Native Code: 5701][ASEOLEDB]Changed database context to 'master'.
[Message Class: 10][Message State: 1][Transaction State: 1][Server Name: DBDV01]
[Native Code: 5703][ASEOLEDB]Changed language setting to 'us_english'.
I am also getting similar error if i try to open both SYBASE CENTRAL and SQL ADVANTAGE at the same time.
any help will be welcome.
|
|
|
|
|
Good afternoon all.
the developers on my team are having an ongoing discussion about null values being represented as another value where applicable. The reason for this discussion is we are going through a system re-write and we need to know the best way to do the following in SQL server 2005 and ASP.NET
Meaning, due to the nature of our business, we can have situations where numeric or date values can actually be represented as a character value. For example, if a numeric mortgage rate is not present on a mortgage application document, the field is considered to be "not applicable". However the data type for the mortgage rate field is numeric in the SQL database.
The argument here is how do we store the "not applicable" for a null numeric field? We have talked about having child tables to store the field name and its character field representation while keeping the null in the parent table with its true datatype.
We have also discussed having an XML field in the parent table to store the XML character representation for that field. So for example if the mortgage rate for a record is "not applicable" null, we would store null in the Mortgage_Rate field in the database, but store <mortgage_rate>Not Applicable in the XML field for that record.
Does anyone have any other types of recommendations for storing this kind of data?
Thanks for any tips in advanced.
- AGD
Software Developer
|
|
|
|
|
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
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
