|
Hi all,
How can i make the change to user the @@servername variable so when I run the out of a different server I don’t have to change the code. I want to make change so I don't have to put the server name (such as @@servername = testserver) how can i define that?
CREATE FUNCTION fIsProduction ()
returns bit
AS
begin
declare @Prod bit
SELECT @Prod =case when @@servername = 'testServer'
then 1 else 0 end
return @Prod
end
|
|
|
|
|
Why not put it in a configuration file that you read at startup?
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
|
|
|
|
|
Does anyone have any horror stories (or success stories) about moving a number of SQL Server 2000 databases over to SQL Server 2005? I couldn't see any such articles on the MSDN site, but specifically I want to know what needs to be looked out for.
|
|
|
|
|
I would like to show the varchar2 size in a information display and am simply not finding where to get the value. I assume it is in the column properties. Any suggestions? I am using the OracleClient/OracleDataReader in ODP version 9i to read the DataTypeName and the display is in a C# app.
Thanks
"Simplicity is more complicated than you think. But it’s well worth it” (Ron Jeffries)
|
|
|
|
|
You can get the info from the user_tab_columns or the all_tab_columns view
the sql would look like:
select column_name, data_type, data_length
from user_tab_columns
where table_name = 'YOUR_TABLE_NAME'
or if it is not your schema but you have permissions to it....
select column_name, data_type, data_length
from all_tab_columns
where table_name = 'YOUR_TABLE_NAME'
and owner = 'SCHEMA_OWNER'
remember the SCHEMA_OWNER and YOUR_TABLE_NAME are case-sensitive searches and should be ALL CAPS.
hth
Al
|
|
|
|
|
Al Ortega wrote: from user_tab_columns
Thanks, Just what I was missing!
"Simplicity is more complicated than you think. But it’s well worth it” (Ron Jeffries)
|
|
|
|
|
Hello friends,
I want to create 1 stored procedure in Oracle. I have written it & when trying to execute, its giving some error message...i am using Ora 8i.
While creating SP, Oracle gave me error msg :- "Warning: Procedure created with compilation errors."
Will anybody please help me in solving the issue??? Here I am giving that SP...
Looking forword to see your reply..  Thanks in advance...
Regards,
Rohan
CREATE OR REPLACE PROCEDURE usp_Security_IsAuthorisedUser
(strUserName IN VARCHAR2(40),
strPassword IN VARCHAR2(16),
blnIsValidUser OUT BOOLEAN,
strInfo OUT VARCHAR(10))
AS
BEGIN
DECLARE intCount NUMBER;
intCount := 0;
SELECT COUNT(*) INTO intCount
FROM USERS
WHERE UserID = strUserName AND Password = strPassword;
--If count = 1 then only user information is correct
IF intCount = 1 THEN
BEGIN
blnIsValidUser := TRUE;
intCount := 0;
strInfo := 'SP';
SELECT COUNT(*) INTO intCount
FROM UnitRights
WHERE HoU = strUserName;
IF intCount = 1 THEN
strInfo := 'UH';
EXIT;
ELSE
SELECT COUNT(*) INTO intCount
FROM GENCODES
WHERE FLD_NAME = 'MARKET_REG' AND UDF_ST3 = strUserName;
--If count = 1 then this is Region Manager
IF intCount = 1 THEN
strInfo := 'RM';
EXIT;
END IF
END IF
END;
ELSE
BEGIN
blnIsValidUser := FALSE;
SELECT COUNT(*) INTO intCount
FROM USERS
WHERE CODE = strUserName;
IF intCount = 1 THEN
strInfo := 'PI';
ELSE
strInfo := 'UU';
END IF
END;
END IF
END;
|
|
|
|
|
Here is the corrected code. Obviously, I cannot check whether it is doing what it is supposed to do. I just corrected obvious syntax errors. One more thing, when you get "Warning: Procedure created with compilation errors." , it means there are some errors in the stored procedure and you need to correct it before start using it.
CREATE OR REPLACE PROCEDURE usp_Security_IsAuthorisedUser
(
strUserName IN VARCHAR2,
strPassword IN VARCHAR2,
blnIsValidUser OUT BOOLEAN,
strInfo OUT VARCHAR2)
AS
intCount NUMBER;
BEGIN
intCount := 0;
SELECT COUNT(*)
INTO intCount
FROM USERS
WHERE UserID = strUserName
AND Password = strPassword;
--If count = 1 then only user information is correct
IF intCount = 1 THEN
BEGIN
blnIsValidUser := TRUE;
intCount := 0;
strInfo := 'SP';
SELECT COUNT(*)
INTO intCount
FROM UnitRights
WHERE HoU = strUserName;
IF intCount = 1 THEN
strInfo := 'UH';
--EXIT; Invalid. Should be used on in the loop
RETURN;
ELSE
SELECT COUNT(*)
INTO intCount
FROM GENCODES
WHERE FLD_NAME = 'MARKET_REG'
AND UDF_ST3 = strUserName;
--If count = 1 then this is Region Manager
IF intCount = 1 THEN
strInfo := 'RM';
--EXIT; Invalid. Should be used on in the loop
RETURN;
END IF;
END IF;
END;
ELSE
BEGIN
blnIsValidUser := FALSE;
SELECT COUNT(*)
INTO intCount
FROM USERS
WHERE CODE = strUserName;
IF intCount = 1 THEN
strInfo := 'PI';
ELSE
strInfo := 'UU';
END IF;
END;
END IF;
END;
regards
|
|
|
|
|
I am looking for a sofware package which is developed uisng VB.NET technology. Something like ebay clone.
if you know of any please post or send me a email
functions will include
1. User logon
2. User will be able to enter ads (text & graphic)
3. To be able to setup multiple categories (Auto, personals, Garden, Boats, ...)
4. search functons
5. Admin functions
6. to be able to Accept credit card payments
7. Take banner ads
8 .....
Thanks
|
|
|
|
|
system stored procedure/coding in vb.net
|
|
|
|
|
Do u mean that u want to know a sql store procedure to list all SQL servers?
<< >>
|
|
|
|
|
list all regestered database servers on a LAN
priyank
|
|
|
|
|
Which database servers?
if SQL-Server, you can use following code in .NET 2.0:
<br />
DataTable servers = SqlDataSourceEnumerator.Instance.GetDataSources();<br />
foreach (DataRow src in servers.Rows){<br />
foo();<br />
}<br />
The object returned has the following fields:
* ServerName
* InstanceName
* IsClustered
* Version
André
'A programmer ist just a tool which converts caffeine into code'
|
|
|
|
|
ok i continue here my questions:
1)if i want to use an existing mysql database for my application how can install it on the final user machine?
2)if the user has not mysql server installed on its pc, i've to install it before?
3)Have i to configure something too?
4)If my application uses microsoft access database instead, i can create it during the installation on its pc, the have i to configure something like DSN or others?
|
|
|
|
|
I data hadata set with multiple table and i want to bind that to single data set
I tried
DataGrid1.DataSource = Mydataset; // with 3 tables .. THIS SHOWS ! TABLE ONLY
// This sdoesn't work also shows 1st table
DataGrid1.DataSource = Mydataset.Tables["table1"].DefaultView ;
DataGrid1.DataSource = Mydataset.Tables["table2"].DefaultView;
DataGrid1.DataSource = Mydataset.Tables["table3"].DefaultView ;
DataGrid1.DataBind();
// when i do individual work fine but not all three
Thanks
|
|
|
|
|
I have some questions about licensing :
- is there any patent for OleDb or Odbc?
- is there any patent for dBase V?
- using an database server for commercial applications involves the paying of fees to the company/developpers; but, givving the application maximum flexibility by leting the user to set it's own odbc connection string (like in j2ee), let's you escape the patents fees?
Thanks in advance...
I hope I understand...because is a rough world out there...
|
|
|
|
|
AFAIK - there is no fees for ODBC.
By forcing the end user to select or enter a valid ODBC datasource name or connection string - you have placed the licensing legalities on them. And really - they will be unable to create a valid Oracle, SQL Server - etc - odbc datasource if they don't have the RDBMS installed and licensed.
-J
|
|
|
|
|
Currently I have a huge insert method and from running OSQL it takes a few seconds to execute and inserts the many number of entries I require.
However, having to use that query on C#, it times out and nothing gets inserted. This also happens when I try to use the Stored Procedure as well for the insertion (the procedure works on OSQL as well).
Has anyone got ideas of how best to handle this because it shouldnt be taking longer from C# than from running OSQL.....
|
|
|
|
|
You need to change the CommandTimeOut. That might work.
1.Public Function ExecuteSQLCommand(ByVal strSql As String) As Integer
2. InitializeConnection()
3. If con.State = ConnectionState.Open Then con.Close()
4. con.Open()
5. cmd = New SqlCommand()
6. cmd.Connection = con
7. cmd.CommandTimeout = 60
8. cmd.CommandType = CommandType.Text
9. cmd.CommandText = "delete from d where id > 10"
10. Return cmd.ExecuteNonQuery()
11. End Function
|
|
|
|
|
Thats not what I wanted, I know it will run but its extremely slow of doing an Insertion by selects with nested inner joins.
Doing it on OSQL seems to be in 5 seconds, doing it in C# is for 2 minutes 10 seconds, thats like 2 minutes and 5 seconds of something going on that I cant see. Is ExecuteNonQuery on SQL Command doing something to the text or the string, besides executing it? Cause its taking quite a long time to do it....
|
|
|
|
|
Can you show us C# code that execute the huge insert.
If you use the same stored procedure to insert big number of rows, you can use sqlCommand.Prepare(), where
sqlCommand = new SqlCommand("MyStoredProcedure", myConnection)
|
|
|
|
|
I figured it out why it was slow, it was due to SQL Transactions....
Now gotta work out how to speed it up......
Basically what I did that slowed it down was an INSERT SELECT TO Statement, and no I didnt use stored procedures since I also tried that with the same outcome.
Basically in PSEUDO form was more of a
INSERT INTO table1
SELECT [blah blah blah]
FROM #a INNER JOIN #b ON 1 = 1
INNER JOIN table2 ON a.[field] = table2.[field]
AND [etc].
WHERE something = something
|
|
|
|
|
This is my multi word search SQL, but I also need to find the selling price of each item according to what market ID they have and then lookup their price for that market id . The first SELECT finds the Market ID which is then used by the 2nd SELECT to find the market_price.
Can these 2 Select statements be placed within the large SQL in order to find the market price? If so, can someone tell me how to do it?
strSQL = "Select Market_ID from CUSTOMER where ID = @CustID"
strSQL = "Select * from MARKET_PRICE where PART_ID = @PartID and MARKET_ID = @MarketID"
strSQL = "SELECT DISTINCT dbo.PART.ID as ID,
'strSQL = "SELECT dbo.PART.ID as ID,
strSQL = strSQL & "dbo.PART.DESCRIPTION, "
strSQL = strSQL & "dbo.Part.PRODUCT_CODE, "
strSQL = strSQL & "UNIT_PRICE AS Price, "
strSQL = strSQL & "dbo.PART.COMMODITY_CODE, "
''strSQL = strSQL & "dbo.part_model.model_number, "
strSQL = strSQL & "dbo.PART.STOCK_UM "
''strSQL = strSQL & "dbo.PART_CROSSREF.CrossrefID "
strSQL = strSQL & "FROM dbo.PART "
strSQL = strSQL & "INNER JOIN "
strSQL = strSQL & "dbo.PART_CROSSREF ON dbo.PART.ID = dbo.PART_CROSSREF.ID "
strSQL = strSQL & "INNER Join "
strSQL = strSQL & "dbo.PART_MODEL ON dbo.PART.PRODUCT_CODE = dbo.PART_MODEL.PRODUCT_CODE "
strSQL = strSQL & " WHERE ((PART.ID like '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
For CountLoopsForSQL = 0 To UBound(KeywordsForSearch)
strKeyWord = KeywordsForSearch(CountLoopsForSQL)
If strKeyWord <> "" Then
' Search for the words in the "ID" field
strSQL = strSQL & " OR (dbo.PART_CROSSREF.crossrefID LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
strSQL = strSQL & " OR (dbo.PART_CROSSREF.ID LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
' Search for the words in the "Product_code" field
strSQL = strSQL & " OR (dbo.Part.Product_code LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
' Search for the words in the "Product_code" field
strSQL = strSQL & " OR (dbo.Part.description LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
' Search for the words in the "Commodity_code" field
strSQL = strSQL & " OR (dbo.Part.Commodity_code LIKE '%" & KeywordsForSearch(CountLoopsForSQL) & "%')"
End If
Next
strSQL = strSQL & " and (UNIT_PRICE is not null) and (Part.User_9 = 'Y'))"
Thanks
|
|
|
|
|
Select * from MARKET_PRICE where PART_ID = @PartID and MARKET_ID =(Select Market_ID from CUSTOMER where ID = @CustID) ????
|
|
|
|
|
I appreciate the feedback but I had never used a Select within a Select and so not only did I not know what syntax was acceptable, I also didn't know where I could put the Selects. I have since found that I can place them almost anywhere I want.
However, your example did show me a way to use a Select in a way I didn't know it could be done. That will be useful info for the future.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
