|
Hello.
I'm trying to insert a record into Oracle table via Command.Execute"Insert
Into..Values" query. The table has several DATE fields and some of them have
to have NULL value.
The problem is that when I specify NULL in my insert query, the DATE fields
are getting value of "30-DEC-99" instead of NULL. CHAR fields are getting
NULL value without any issues.
I've tried both Microsoft and Oracle providers with the same result.
Please, let me know what I'm missing or should I use different ways to
achieve what I need (stored procedures or queries w/o values NULL fields).
Thanks
Valeriy Raygorodetskyy
|
|
|
|
|
Hi all,
I'm wondering if there's a limit/recommendation in the number of records
that it can be inserted in an Access 2003 table before the performance is
affected considerably. I know that in Access 2000 the limit was around 100,000 records but I'd like to know it for Access 2003.
thanks in advance,
Marc Soleda
... she said you are the perfect stranger she said baby let's keep it like this... Dire Straits
|
|
|
|
|
In my opinion, performance in Access is more dependent on whether or not tables are properly indexed, and whether or not the database is being used as a desktop/single-user database or in multi-user situations.
I've seen Access tables with a million rows perform very well. I've seen much smaller tables, improperly indexed, perform very poorly.
Keep in mind too - Because of Access' easy-to-use datagrid views, it is tempting to always have forms based on complete table recordsets, which can often kill performance. Again, I've seen well-designed Access applications which force the user to limit the number of records being displayed; even with million-row tables, this can perform very well.
As for a strict limit - in Access 2003, I think 2 Gigs (minus space for system tables) is the largest a table can be.
|
|
|
|
|
Hi all
Using ADO & SQL Server & VB6
Assume that some changes on different tables are made within a BeginTrans-CommitTrans pair. Like below:
Dim Con As ADODB.Connection
'...
With Con
.BeginTrans
.Execute("Insert Into table1 (field1, field2) Values (1,2)")
.Execute("Update table2 Set field1 = 3")
'...
.Execute("Delete From tableX Where fieldY = Z")
'...
.CommitTrans
End With
I'm looking for a way for getting the names of the changed tables right after the CommitTrans?
Thanks
Good Day
|
|
|
|
|
hi,
i have a datagrid which is filled from database with edit, delete options. i have used inline coding. how to write a stored procedure for filling a datagrid. can anyone help me with the code
regards,
boon
|
|
|
|
|
david boon wrote: how to write a stored procedure for filling a datagrid
A stored procedure won't exactly fill a datagrid for you. You would write a stored procedure to execute database code and optionally return a result set; if you do return data from the stored procedure, then you can use the .Fill() method of a DataAdapter object (OleDbDataAdapter, SqlDataAdapter, etc. depending on the brand of database you are working with) to populate a DataSet object. The DataSet object could then serve as the source for your DataGrid.
If your database system supports stored procedures, you can likely use the CREATE PROCEDURE statement - here's a very simple example:
CREATE PROCEDURE GetEmployees
(
@startOfLast varchar(128)
)
AS
BEGIN
SELECT EmployeeID, LastName, FirstName, Department
FROM MyEmployeesTable
WHERE LastName LIKE @startOfLast + '%'
END Specific syntax will still depend on your brand of database system.
|
|
|
|
|
How can i determine all the currently logged users of my SQL Server dabase using ADO.NET
|
|
|
|
|
I think you can query master.dbo.sysprocesses to derive who is logged in at any given moment in Sql Server. With that, you could create a class kind of like this:
using System.Data;
using System.Data.SqlClient;
public class SqlServerUsers
{
private static SqlConnection GetConnection()
{
string sCon = "Server=(local);Database=master;Trusted_Connection=True;";
SqlConnection con = new SqlConnection(sCon);
con.Open();
return con;
}
public static DataSet GetLoggedInUsers()
{
string sql = "SELECT hostname, loginame, cmd, program_name "
+ " FROM master.dbo.sysprocesses "
+ " WHERE hostname != ''";
using (SqlConnection con = GetConnection())
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandText = sql;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
}
}
|
|
|
|
|
Hello all. I need to know if that's possible.
I have a database with many tables, but other person is updating it, so there are tables that have changes: new fields, new relationships...
I have the backup with the new version and I have to update my database, but I don't want to lose the tables information. Is it possible to do that automatically? There are many tables to do it manually.
Regards,
Diego F.
|
|
|
|
|
Diego F. wrote: I have a database with many tables, but other person is updating it, so there are tables that have changes: new fields, new relationships...
I have the backup with the new version and I have to update my database, but I don't want to lose the tables information. Is it possible to do that automatically? There are many tables to do it manually.
Don't quite follow you. What are you updating? This other person should be working with you in synchronization so you both are on the same page.
PC
|
|
|
|
|
The thing is that I'm working with a large database with about 300 tables. Other person is working updating that db, adding fields and relationships to some tables. The problem is that I only have a backup with the new version and not the scripts with the changes.
So I need a way to "upgrade" my database with the new version without losing data.
I think it must be something like creating a new database restoring the backup and moving the data from the old database, but I don't know how to move that data.
Regards,
Diego F.
|
|
|
|
|
Sounds like you two should be in close communication of what is going on with the database. I am assuming you are using some flavor SQL. The person doing the changes to the tables by adding fields and relationships should provide you with a script containing ALTER commands reflecting the new changes to the database that have been made. At this point, you would run the script and your backup database would be structurally the same as the updated database. No data loss, no moving of data, or anything like that.
http://www.w3schools.com/sql/[^] has a nice reference of SQL commands if you are not familiar with them.
Hope this gives you some ideas,
Paul
|
|
|
|
|
Hi experts..im new to sql and i wrote this stored procedure for number to character conversion..
intially i have taken one numeric parameter and i have converted into varchar to display in words..
But i want to do one more modifications to this code..if i entered 500 then it shuld be treated
as 500 dollors and should be multplied with 45 and the result should be = 500 * 45.
But im returning varchar value..how to multiply with '45'. im getting data type conversion error..
Here im sending my code upto ten thousand only.. and return statement..
pls solve the problem..and thanks in advance..
@p_Number numeric(18,2) --> Parameter...
begin
declare @Num varchar(20)
declare @Dec varchar(3)
declare @Return varchar(2000)
set @Return = convert (varchar(2000),@p_Number)
set @Dec = substring(convert(varchar(20),@p_Number),len(convert(varchar(20),@p_Number))-2,3)
set @Num = substring(convert(varchar(20),@p_Number),1,len(convert(varchar(20),@p_Number))-3)
declare @Hundred Char(8)
declare @HundredAnd Char(12)
declare @Thousand Char(9)
declare @Lakh Char(5)
declare @Lakhs Char(6)
declare @Crore Char(6)
declare @Crores Char(7)
set @Hundred = 'Hundred '
set @Thousand = 'Thousand '
set @Lakh = 'Lakh '
set @Lakhs = 'Lakhs '
set @Crore = 'Crore '
set @Crores = 'Crores '
set @HundredAnd = 'Hundred and '
if len(@Num) = 1 -- One
begin
set @Return = testuser.fn_GetTextValue_arif(@Num)
end
else
if len(@Num) = 2 -- Ten
begin
set @Return = testuser.fn_GetTextValue_arif(@Num)
end
else
if len(@Num) = 3 -- Hundred
begin
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Hundred
if substring(@Num,2,2) <> '00'
set @Return = @Return + 'And '
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,2,2))
if substring(@Num,2,2) = '00'
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Hundred
end
else
if len(@Num) = 4 -- thousand
begin
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Thousand
if substring(@Num,2,1) <> '0'
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,2,1)) + @Hundred
if substring(@num,3,2) <> '00'
set @Return = @Return + 'And '
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,3,2))
if substring(@num,2,1) = '0' and substring(@num,3,2) = '00'
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,1)) + @Thousand
end
else
if len(@Num) = 5 -- Ten Thousand
begin
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,2)) + @Thousand
if substring(@Num,3,1) <> '0'
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,3,1)) + @Hundred
if substring(@num,4,2) <> '00'
set @Return = @Return + 'And '
set @Return = @Return + testuser.fn_GetTextValue_arif(substring(@Num,4,2))
if substring(@num,3,1) = '0' and substring(@num,4,2) = '00'
set @Return = testuser.fn_GetTextValue_arif(substring(@Num,1,2)) + @Thousand
end
if @Dec <> '.00'
set @Return = @Return + 'And ' + testuser.fn_GetTextValue_arif(substring(@Dec,2,2)) + 'Paise '
select '$' + @Return + 'Only' as 'MoneyInWords' ---> Retrun value...
|
|
|
|
|
I'm running msde2000, what I need to do is subtract data from the tables into a c.s.v file, I don't really want to make code changes and install updates of my app everywhere, so what I want to do is just create a script file or some statement that I can run through osql,
Any ideas?
Sql not my area of experties.
|
|
|
|
|
Hi All
I want to run a stored proc to query a dynamic field set by a parameter. When I run the query I Get no results and I know the data exists.. Can anyone see if I am doing something incredibly dumb
'''My sproc
CREATE PROCEDURE SearchOrders @Field nvarchar(20), @SearchStr nvarchar(20)
SELECT *
FROM SorDetail
WHERE (@Field=@SearchStr)
thanks y' all
|
|
|
|
|
here is the solution, i was not able to figure this one out my girlfriend was good enuff to get one of the propellor heads to figure it out at here work:
CREATE PROCEDURE SearchOrders @Field nvarchar(20), @SearchStr nvarchar(20) AS
declare @Query nvarchar(4000)
set @Query = 'SELECT * FROM SorDetail WHERE [' + @Field + ']=''' + @SearchStr + ''''
exec sp_executesql @Query
|
|
|
|
|
thedom2 wrote: one of the propellor heads to figure it out
So we're all just "propellor heads" to you. I see....
Anyway, this "expert" failed to mention the need to defend against incorrect or malicious input into the stored procedure.
You may like to consider putting the following code at the start of your stored procedure:
IF NOT EXISTS(SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='SorDetail'
AND SCHEMA_NAME = 'dbo'
AND COLUMN_NAME = @Column)
BEGIN
-- Column does not exist.
RETURN 0;
END
ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?
|
|
|
|
|
Anybody know how to dump a database to SQL? (Yup, all tables, procedures, etc...)
I only read CP for the articles.
Code-frog System Architects, Inc.
|
|
|
|
|
Hi,
Take the enterprice manager and just select what all the objects you want to script and then COPY and take the query analiser or any editor and PASTE it..
|
|
|
|
|
If you are using SQL Server, select the database from the tree in left pane of Entrprise manager. Right click, select "All Tasks"->"Generate SQL Script". The resulting dialog will allow you to select whatever you want to script, and will generate scripts for all the schema. To get data in the tables, you will need to use somthing else, like BCP, or roll your own.
Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke
-- modified at 17:41 Friday 3rd March, 2006
|
|
|
|
|
Okay, this may be a really stupid question, but sitting here and thinking about it, followed by a large number of Google attempts has left me unenlightened. Here goes:
Let's say I have an ASP.Net web application that displays a report to users using a datagrid. The query for the report is fairly complex and involves several joins using either poorly- or non-indexed tables containing hundreds of thousands of rows each. Suffice it to say, the query is long-running, which is not the greatest thing to have in a web application, but that's not really the point of my question.
Now, let's say an impatient user signs in to the application and requests the slow report described above. The server-side application logic opens a connection, generates a command for the report, and executes it against the database server. The user drums his fingers on his desk for a while, whistles for a few minutes after that, and finally says "Something must be wrong!" and stabs the 'Submit' button to generate the report again (causing a new request against the web server, causing another command to be created and executed etc.).
A minute passes and the query has still not returned...
The user, now completely disgusted, closes his/her browser and goes to lunch.
Now the question:
What happens to the two commands - in fact two *connections* - that are presumably executing at the database server? They were both created under the main process of the ASP.Net worker account, and the process has not disappeared, however there is no longer anyone listening for the command to complete and return (either the browser is closed or the user has browsed away).
Do the queries complete and return (to nowhere)? (no idea)
Is the database server somehow signalled that the commands have been 'orphaned'? (doubt it)
Do the commands, now presumably with a broken communication pipe the the client that requested their execution, sit until timeout occurs?
Is the end nigh, and should I try to find a cool, dry place to watch Armageddon?
The reason I ask is that I've observed similar issues in cases where a user posts a request for data from a long-running command, then closes his/her browser thinking that opening a new browser will make things go faster, only to find out that the DB server is almost completely unresponsive. I have also observed it from the DB server side, where there are large numbers of processes, alive and taking up CPU time, but never seeming to complete.
Thanks for any input.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
-- modified at 17:10 Thursday 2nd March, 2006
|
|
|
|
|
This is my grand theory (im quite sure on this as i have SQLprofiled it in test environment)
Once the browser is closed the connection to Sql is lost and then sql svr aborts the process...
Prove me wrong People =)
|
|
|
|
|
That's intuitively what I would expect, but I'm not certain it's what always happens. I should clarify somewhat by pointing out that my observations have been made with Oracle 9i, not SQL Server.
The reason I'm not sure orphaned connections are always cleaned up neatly and efficiently is based on two main points:
1. I've watched from the DB side as connections were created from ASP.Net applications, during the execution of which the client browser was either closed or directed to a different (and unrelated) URL. The connections have not always dropped gracefully.
2. This is more of a gap in my knowledge than anything, but it seems as if there's a point in the execution of a SQL command from any application client where control is passed to the DB server to complete the work and return results across the pipe. In ASP.Net, where the (OS) process creating the connection is tied to the application, not the client browser, and is not terminated merely because the browser closes. In such cases, it appears as if the message pipe might be broken with regard to returning results to the end client, but the process under which the work is being performed is alive and well.
Thanks for your input. If possible, could you provide some information on how you structured your tests? I'll be doing some of my own, so I'd be curious to see what you did.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
I have a form that has a ComboBox and 5 Text box controls. All controls contain text field databinding to a dataset, except for the combobox, which has its datamember and datasource properties set to the same dataset. With this being said, I have a Binding Manager Base object that I'm using its methods to update the dataset, and then the tables. When the user selects a value in the combo box it fills the textbox controls. The problem I'm having is the combobox is populated with the existing Primary_Key fields for the table. I'm attempting to add records to the table. So when using my binding manager base function addnew() and populating the text property of the cbo box, it doesn't send the input text with the insert command. I attempted to bind the text field of the cbo to the dataset and that failed as well. However, I can hide a label (which is bound to the primary_key) and assign the text value of the cbo to the label when the cbo looses focus, and that works. Any ideas on how to get the cbo text value to work without having to pass it to a label? Thanks for your help!
|
|
|
|
|
I'm trying to build a dynamic stored procedure. The parameter "@Column" is used to determine which column it's going to update. The problem I'm having is when i try to use an Int data type. Here's the code.
set ANSI_NULLS ON<br />
set QUOTED_IDENTIFIER ON<br />
go<br />
ALTER PROCEDURE [dbo].[MON_UpdateAppMsgPubStats]<br />
@Status char(1),<br />
--@StatusDTTM datetime,<br />
@PubId int,<br />
@Instance char(3),<br />
@Env char(5),<br />
@Node varchar(15),<br />
@Chnl varchar(30),<br />
@Column varchar(30)<br />
AS<br />
BEGIN<br />
DECLARE @sqlString varchar(8000)<br />
--SET @StatusDTTM = convert(char(30),getdate(),1)<br />
SET NOCOUNT ON<br />
SET @sqlString = 'UPDATE MON_AppMsgPubStatistics SET '+@Column+'='+@Status+', '<br />
+@Column+'DTTM='+convert(char(20),getdate())+'WHERE PUBID='+@PubId+<br />
' AND Instance='+@Instance+' AND Environment='+@Env+<br />
' AND PUBNODE='+@Node+' AND CHNLNAME='+@Chnl<br />
<br />
EXECUTE(@sqlString)<br />
END
And the Error Message...
Msg 245, Level 16, State 1, Procedure MON_UpdateAppMsgPubStats, Line 20<br />
Syntax error converting the varchar value 'UPDATE MON_AppMsgPubStatistics SET MsgParsed=T, MsgParsedDTTM=Mar 2 2006 12:44PM WHERE PUBID=' to a column of data type int.
Any help would be appreciated. Thanks!
|
|
|
|
|